Работа с математическими и статистическими функциями. Расчёт зарплаты сотрудникам кафедры с помощью формул. Анализ данных. Автоматизация процесса подготовки документа, страница 2

Таблица №1:

Таблица №2:

Таблица №3:

Таблица №4:

Таблица №5:

Таблица №6:

Эти таблицы могут быть дополнены другими сведениями о сотрудниках и использованы для решения соответствующих задач.

3. Ввод формул в ячейки итоговой ведомости

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

Заполним ячейку В72 столбца «Оклад (по должности)». В ячейке В72 выясняется, какой оклад у Ахматова Ф.В. Запишем следующую сложную функцию:

=ЕСЛИ(ИНДЕКС(Сотрудники;ПОИСКПОЗ(A72;Фамилии);4)=$A$16;$B$16;ЕСЛИ(ИНДЕКС(Сотрудники;ПОИСКПОЗ(A72;Фамилии);4)=$A$17;$B$17;ЕСЛИ(ИНДЕКС(Сотрудники;ПОИСКПОЗ(A72;Фамилии);4)=$A$18;$B$18;ЕСЛИ(ИНДЕКС(Сотрудники;ПОИСКПОЗ(A72;Фамилии);4)=$A$19;$B$19;$B$20)))),

где «Сотрудники» - имя области А4:Н12, которая находится в таблице №1, «Фамилии» - имя области А4:А12, расположенной в таблице №1.

Результат: ячейка В72 покажет значение - 1000 руб.

Аналогично заполняются ячейки в столбцах «Добавка за степень», «Добавка за стаж» и «Ставка».

В ячейке Е72 столбца «Доплата за детей» пишется сложная функция:

=ИНДЕКС(Сотрудники;ПОИСКПОЗ(А72;ФИО);6)*83

Эта функция возвращает количество детей у сотрудника, а число 83 – это оплата за одного ребенка.

В ячейку F72 столбца «Число рабочих дней» пишем функцию:

=ИНДЕКС(Дни;ПОИСКПОЗ(А72;ФИО2);МЕСЯЦ(СЕГОДНЯ())+1)

«Дни» - название области А41:G49, расположенной в таблице №5 «Месячный табель», «ФИО2» - название списка фамилий сотрудников из таблицы №5.

Функция МЕСЯЦ() и вложенная в неё функция СЕГОДНЯ(), то есть МЕСЯЦ (СЕГОДНЯ()) возвращает текущий месяц.

Результат: 15 – число рабочих дней, отработанных Ахматовым Ф.В.

Аналогично заполняются ячейки в столбце «Часы».

Заполним ячейку I72 столбца «Начислено» (расчетную формулу см. в п.5 Расчёт заработной платы выполняется …) формулой:

=(B72*D72*F72/ИНДЕКС(месяц;2;МЕСЯЦ(СЕГОДНЯ())+C72)*1,25+G72*H72,

где функция ИНДЕКС(месяц;2; МЕСЯЦ(СЕГОДНЯ()) возвращает число рабочих дней в текущем месяце, «месяц» - название области D39:I40, расположенной в таблице №5 «Месячный табель».

Из столбца «Начислено» вычисляется 14% налоговых отчислений, и эта сумма помещается в столбец «Вычеты» в ячейку J72:

=I72*14/100

Заполним столбец «К выдаче» в ячейке K72, в котором от «Начислено» вычтем 14% налоговых отчислений и добавим доплату за детей.

=I72-J72+E72

В результате получилась следующая ведомость и она должна быть готова к печати:

3-й уровень помощи (подробный)

1. Зачем нужны формулы в EXCEL?

Если бы не формулы, электронную таблицу можно было бы создать с помощью текстового процессора. Формулы – это душа и сердце рабочего листа, и EXCEL предлагает богатые возможности для построения сложных формул. Вооружившись несколькими математическими операторами и правилами ввода значений в ячейки, можно превратить рабочий лист в мощный калькулятор.

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

Пример: =5+2*3

Формула также может включать следующие элементы:

§  Ссылки

§  Операторы

§  Константы

§  Функции

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

Оператор – знак или символ, задающий тип вычисления в формуле. Существуют математические (+, -, *,  /, %, ^), логические (=, <, >, <=, >=, <>) операторы и др.

Константа – постоянное (не вычисляемое значение). Например, число 2.

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