Встраивание формул в ячейки таблицы, работа с фиксированной ячейкой

Цель выполнения работы: изучить правила создания формул для обработки табличных данных в списках, освоить приёмы встраивания стандартных формул для работы с наборами данных, научиться использовать фиксированные ячейки с данными для выполнения вычислений в массивах.

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

Задание 1. Подготовить ведомость выплаты заработной платы сотрудникам организации с учётом добавленных процентов из премиального фонда. В таблице отобразить для каждого сотрудника: величину премии в виде доли от общего фонда, суммарные выплаты, величину реальной выплаты с учётом вычете подоходного налога.

Выполнение, поставленного задания включает несколько этапов, основой будет являться таблица в Excel, созданная ранее, которую следует открыть.

Этап 1. Сформируем на новом листе таблицу, которая будет называться – «Ведомость оплаты». Для этого скопируем из существующей таблицы столбцы: список с фамилиями сотрудников (Фамилия) и список с окладами (Оклад). Новому листу дадим наименование «Ведомость». Напоминаем, что при копировании списков, которые относятся к таблице и находятся не по порядку, сначала выделяется один список, а затем выделяются другие списки с удержанием левой клавиши Ctrl. На рис. 1 показана таблица «Ведомость оплаты», которую должны получить после выполнения задания, как видно из рисунка, в таблицу добавлены заголовки списков: «Премия», «Всего», «На руки», а затем были выполнены определённые действия.

Рис. 1. Конечный вид таблицы с заполненными столбцами и строками, в которых использовались формулы

Этап 2. Подсчитать общую сумму окладов сотрудников организации. Для облегчения вычислений в Excel существует библиотека стандартных формул и наиболее часто встречающихся функций, перечень которых находится на вкладке «Формулы». Чтобы осуществить вычисления суммы элементов столбца или строки, достаточно выделить столбец, например, с окладами сотрудников, а затем воспользоваться командой для формулы – сумма. Этот значок находится на вкладках «Главная» и «Формулы», по нему достаточно щёлкнуть левой клавишей мыши. Попробуйте установить курсор в ячейке, где должна быть сумма окладов (в рассматриваемом примере, это ячейка С13), и щёлкните по значку , установите курсор на заголовке списка, а затем щёлкните по значку , посмотрите, как будет реагировать система.

Этап 3. Вычислить премию для каждого сотрудника. Для этого необходимо знать суммарную величину средств (премиальный фонд), отпущенных на премии сотрудникам, и установить правила выдачи премии каждому сотруднику. Предположим, что в текущем месяце на премии было выделено 160 тыс. руб., что отмечено в ячейке H4 на рис. 1. Правило деления премии между сотрудниками выберем самое простое – каждый получит долю, пропорциональную окладу, т.е. формулу можно описать как:

Премия i-ого сотрудника = (Оклад i-ого сотрудника / Сумму всех окладов) * Премиальный фонд

Эта формула в Excel будет выглядеть, как указание на координаты элементов таблицы, которые участвуют в вычислениях по создаваемой формуле. На рис. 2 представлена строка формул, в которой записана, принятая формула для правила вычисления премии, и строка таблицы с ячейкой D4, в которую эта формула записана.

Рис. 2. Правила записи формулы в ячейку таблицы

Следует обратить особое внимание, что в формуле использованы относительная ссылка на ячейку С4 и абсолютные ссылки на ячейки C13 и H4. Символ $ перед именем столбца и номером строки показывает, что эта ячейка является фиксированной (её адрес абсолютный, т.е. не изменяется). Для того, чтобы узнать, какую премию должен получить следующий сотрудник в списке, достаточно воспользоваться режимом автозаполнения, т.е. выделить ячейку D4, а затем ухватить указателем мыши в правом нижнем углу ячейки за маркер, и растянуть на весь список, созданную формулу. Обратите внимание, что в этом случае в последующих ячейках в формуле будут изменяться только адреса тех ячеек, в которых отсутствует знак $.

Этап 4. Получить окончательные результаты для ведомости оплаты. Очевидно, потребуется создать формулу для заполнения списка «Всего». Эта формула позволяет найти сумму всех доплат к основному окладу (в данном примере доплатой является премия). Кроме того, создадим формулу для вычисления величины выдачи на руки, которая включает сумму всех выплат за минусом подоходного налога 13%, в нашем случае эта формула будет выглядеть так: =0,87*E4 (для строки с первой фамилией).

Сохраните файл с результатами, попробуйте изменить величину премии.

Задание 2. Представьте дополнительные столбцы в таблице, в которых отражена информация: суммарная величина отчислений в пенсионный фонд (18%) и в страховой фонд (14%) по организации, и величины отчислений за каждого сотрудника.

Вопросы для самоконтроля

1. Что подразумевают под формулой в Excel?

2. Как правильно вводить формулу в ячейку таблицы?

3. Чем отличаются абсолютные ссылки от относительных ссылок?

4. В каких случаях целесообразно использовать абсолютную адресацию в ячейках таблицы?