Лабораторная работа №2.

Цель работы: получить практические навыки по использованию функций ЕСЛИ(), СЧЕТЕСЛИ(), СУММЕСЛИ(), МАКС(), МИН(), СРЗНАЧ(), И(). Ознакомится с работой формул массивов.

Задание 1. Сформировать таблицу вида:

СВЕДЕНИЯ О СОТРУДНИКАХ ПРЕДПРИЯТИЯ:

Подоходный налог рассчитывается так:

А) Если годовой доход < =12 МРОТ, то 0% (МРОТ – минимальный размер оплаты труда. Его записать в отдельную ячейку вне таблицы – 100 руб.);

Б) Если12*МРОТ< годовой доход< =20000, то 12%;

В) Если 20000< годовой доход< 50000, то подоходный налог равен 2400+20% от суммы, превышающей 20000;

Г) Если годовой доход > 50000, то подоходный налог =4000+45% от суммы, превышающей 50000.

Порядок работы.

Введем произвольные фамилии (не менее 10). Должности будем выбирать из списка: секретарь, экономист, юрист, инженер, лаборант. Колонку таблицы “Годовой доход” заполним произвольным образом, но с учетом должности. Для заполнения колонки “Подоходный налог” нам придется использовать функцию ЕСЛИ().

В Microsoft Excel определено большое количество стандартных формул, именуемых функциями. Функции используются для различной сложности вычислений и текстовых операций. Функции выполняют вычисления по входным данным (задаваемым величинам) – аргументам – в указанном порядке, в соответствии с правилами синтаксиса. Список аргументов может состоять из чисел, текста, логических величин, массивов, ссылок. Кроме того, аргументы могут быть как константами, так и формулами. Эти формулы в свою очередь, могут содержать другие функции (до 7 уровней вложения). При вводе значений аргументов необходимо следить за соответствием типов аргументов.

Ввод функции начинается с указания имени функции, затем открывается круглая скобка, указываются аргументы, разделяемые точками с запятой, а затем вводится указывающая скобка. Если ввод формулы начинается с функции, перед именем функции вводится знак равенства (=). В процессе создания формулы, содержащей функцию, можно использовать строку формул или мастер функций.

Общий синтаксис функции следующий:

= Имя_функции(аргумент 1; аргумент 2; …)

Синтаксис функции ЕСЛИ:

= ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)

Функция ЕСЛИ() проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.

В нашем задании функция ЕСЛИ() будет выглядеть следующим образом (предположим, что мы будем вводить ее в ячейку D3):

ЕСЛИ(С3> 50000; (С3-50000)*0, 45+4000; ЕСЛИ(С3> 20000; (С3-20000)*0, 2+2400;

ЕСЛИ(С3> 12*МРОТ; С3*0, 12; 0)))

Здесь МРОТ – поименованная ячейка, содержащая минимальный размер оплаты труда (см. условие задания).

После правильного ввода функции ЕСЛИ() в ячейку D3 остается размножить эту формулу для оставшихся членов списка (см. рис. 4).

Рис. 4.

Задание 2. На 2-ом листе рассчитать таблицу на основе таблицы задания 1.

Порядок работы.

Для подсчета количества человек, занимающих определенную должность, нам понадобится функция СЧЕТЕСЛИ(). Эта функция подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию.

Синтаксис функции следующий:

СЧЕТЕСЛИ(диапазон; критерий)

В нашем случае эта функция, например, для определения числа секретарей, будет выглядеть следующим образом: =СЧЕТЕСЛИ(Лист1! В3: В15; “секретарь”).

Обратите внимание на тот факт, что диапазон В3: В15 содержит явное указание на то, что он находится на первом листе.

Далее нам необходимо подсчитать “Средний доход” для каждой должности. Здесь нам понадобятся формулы массива.

Формула массива выполняет одновременно несколько вычислений и возвращает либо несколько значений, либо одно значение. Обычно формулы массива обрабатывают два и более наборов аргументов, называемых аргументами массива. Каждый аргумент массива должен иметь соответствующий номер строки и столбца. При этом все аргументы массива должны иметь одинаковое количество строк и столбцов. Чтобы формула возвращала несколько значений, она должна быть введена в несколько ячеек.

Формула массива создается так же, как и простая формула, но с нюансами:

– выделить ячейку или группу ячеек, в которых необходимо создать формулу массива;

– ввести формулу;

– ввести комбинацию клавиш < CTRL + SHIFT + ENTER>. Excel заключит формулу массива в фигурные скобки.

Для значения Excel может понадобиться выполнить несколько действий. Например, следующая формула вычисляет среднее значение только тех ячеек, принадлежащих диапазону С3: С15 на 1-ом листе для которых в ячейках диапазона В3: В15 содержится слово “секретарь”. Для этого используется функция ЕСЛИ, которая в диапазоне В3: В15 находит ячейки, содержащие слово “секретарь”, и возвращает значения ячеек в диапазоне С3: С15 только из этих строк функции СРЗНАЧ, которая затем уже вычисляет их среднее значение (см. рис. 5). В данном задании наша формула для секретарей будет выглядеть следующим образом:

{=СРЗНАЧ(ЕСЛИ(Лист1! В3: В15=”секретарь”; Лист1! С3: С15))}

Аналогично Среднему доходу производится расчет в колонке Максимальный доход., только вместо функции СРЗНАЧ надо воспользоваться функцией МАКС:

{=МАКС(ЕСЛИ(Лист1! В3: В15=”секретарь”; Лист1! С3: С15))}

Здесь опять пришлось воспользоваться формулой массива.

Другой способ получения среднего дохода для каждой должности – использовать функции СУММЕСЛИ() и СЧЕТЕСЛИ(), поделив их результаты.

Обращение к функции СУММЕСЛИ имеет вид:

СУММЕСЛИ (диапазон1; критерий; диапазон_суммирования)

Она позволяет получить сумму значений тех ячеек диапазона суммирования, которым соответствуют ячейки диапазона, удовлетворяющие условию. В нашем случае можно получить суммарный годовой доход работников заданной должности. Для этого в ячейку С2 листа 2 занесем формулу:

=СУММЕСЛИ(ЛИСТ1! В3: В15; ”секретарь”; Лист1! С3: С15)/

СЧЕТЕСЛИ(ЛИСТ1! В3: В15; ”секретарь”).

В этом примере в качестве критерия мы указали константу: ”секретарь”. Решение будет более гибким, если в качестве критерия мы укажем адрес ячейки второго листа, содержащего соответствующую должность, а именно, В2. Тогда можно будет скопировать эту формулу в ячейки, соответствующие другим должностям (С3 –С6). Для того, чтобы копирование проходило корректно (т.е. не изменялись адреса диапазонов) нужно в адресации диапазонов указать абсолютные адреса. В ячейку С2 листа 2 надо записать формулу:

= СУММЕСЛИ(ЛИСТ1! $В$3: $В$15; В2; Лист1! $С$3: $С$15)/

СЧЕТЕСЛИ(ЛИСТ1! $В$3: $В$15; В2).

Копируем эту формулу в ячейки С3 – С6 и получаем средний доход по указанным должностям.

Задание 3. Определить количество инженеров, чей годовой доход превышает 20000.