Лабораторная№8 по Excel 2010
.pdfЗадание №8. Расчет платежной ведомости
Задание Рассчитать платежную ведомость по алгоритму (1)-(9) (лекции) для сотрудников
отдела.
Порядок выполнения расчетов.
8.1Открыть (книгу) Excel.
8.2Оформить таблицу и заполнить данными диапазон ячеек A7:AG11 в соответствии с образцом (рис. 2.31).
8.3Произвести расчеты по учету использования дней месяца сотрудниками по образцу формулы (9)(лекции).
Действия:
•В ячейку АН7 ввести формулу для расчета количества больничных дней (б/л)
сотрудника
=ЕСЛИ(СЧЁТЕСЛИ(С7:АG7;"б/л")=0;"";СЧЁТЕСЛИ(С7:АG7;"б/л"))
•Формулой ячейки АН7 заполнить диапазон АН8:АН11
•В ячейку AI7 ввести формулу для расчета количества дней без сохранения содержания
(б/с) сотрудника
=ЕСЛИ(СЧЁТЕСЛИ(С7:АG7;"б/с")=0;"";СЧЁТЕСЛИ(С7:АG7;"б/с"))
•Формулой ячейки AI7 заполнить диапазон АI8:АI11
•По аналогии с действиями п.8.3 произвести расчеты количества сверхурочно отработанных (с/у) дней в диапазоне AJ7:AJ11.
•По аналогии с действиями п.8.3 произвести расчеты количества рабочих (Я) дней в диапазоне АК7:АК11.
8.4Присвоить листу имя Турд.
Результат представлен на рис. 2.31.
Рис. 2.1 Таблица Турд
8.5 Перейти на чистый лист книги, оформить таблицу табельной ведомости, заполнив данными все, за исключением диапазона D10:G14.
1
8.6 Присвоить диапазонам D10:D14, E10:E14, F10:F14, G10:G14 значения
соответствующие расчетным результатам таблицы Турд.
Действия:
•В ячейку D10 ввести формулу =Турд!АК7 и заполнить ею диапазон D11:D14.
•В ячейку Е10 ввести формулу =ЕСЛИ(Турд!АН7=””; 0; Турд!АН7) и заполнить ею диапазон Е11:Е14.
•В ячейку F10 ввести формулу =ЕСЛИ(Турд!АI7=””; 0; Турд!АI7) и заполнить ею диапазон F11:F14.
•В ячейку G10 ввести формулу =ЕСЛИ(Турд!АJ7=””; 0; Турд!АJ7) и заполнить ею диапазон G11:G14.
8.7Присвоить имя листу Тбв (сокращенная аббревиатура слов Табельная ведомость).
Образец таблицы представлен на рис. 2.32
Рис. 2.2 Таблица Тбв
8.8Перейти на чистый лист книги и оформить таблицу платежной ведомости, оставив пустыми столбцы для расчетных граф.
8.9Произвести расчеты по алгоритму (1) - (8) (лекции).
2
Действия:
• В ячейку В4 ввести формулу
=TБB!C10*T6B!D10+T6B!$B$3*T6B!C10*T6B!G10+T6B!C10*T6B!B10*T6в!Е10/100
•Нажать клавишу <Enter>. Произойдет расчет по алгоритму (2)—(4) (лекции)
•Формулой ячейки В4 заполнить диапазон ячеек В5:В8
•В ячейку С4 ввести формулу
=T6B!C10*T6B!F10+T6B!$B$1*(B4+D4)+T6B!$B$4*(B4+D4)
•Нажать клавишу <Enter>. Произойдет расчет по алгоритму (6)—(8) (лекции)
•Формулой ячейки С4 заполнить диапазон ячеек С5:С8
•В ячейку D4 ввести функцию, используя Мастер функции:
=ЕСЛИ(Тбв!D10+Тбв!G10-Тбв!$В$2>0;(Тбв!D10*Тбв!С10+Тбв!G10*Тбв!С10*
*Тбв!$В$3)*Тбв!$В$5;0)
Произойдет расчет по формуле (5)
•Формулой ячейки D4 заполнить диапазон ячеек D5:D8
•В ячейку Е4 ввести формулу: =B4+D4-C4-T6B!H10
Произойдет расчет по формуле (1)
•Формулой ячейки Е4 заполнить диапазон ячеек Е5:Е8
8.10Произвести расчеты итоговых сумм по столбцам.
8.11Переименовать стандартное имя листа на имя Плв (сокращенная аббревиатура слов (Платежная ведомость).
Образец таблицы с результатами расчетов представлен в таблице (рис.2.33).
Рис. 2. 3 Таблица Плв
В дальнейшем, при любых изменениях исходных данных в таблице Турд произойдет автоматический пересчет в таблицах Тбв и Плв.
Использование массива формул
Использование массивов формул рекомендуется применить в самостоятельной работе
3
задания №8 для расчетов в таблице платежной ведомости. Создавая массив формул, надо
помнить следующее:
•Форма и размер диапазона, в которой помещаются результаты, должны повторять форму и размер диапазонов, содержащих исходные данные.
•Массив формул заключается в фигурные скобки, но не надо их вводить, Excel сделает это автоматически.
•Построение массива формул нельзя завершать нажатием клавиши <Enter>. Следует использовать вместо нее комбинацию клавиш — <Shift+Ctrl+Enter>.
Ниже демонстрируется технология выполнения задания на примере создания массива формул для расчета графы Сумма начислено в таблице Платежная ведомость.
Порядок выполнения расчетов:
1.Добавьте новый лист. Дайте имя листу Плв2. Скопируйте таблицу Плв на лист Плв2. В
скопированной таблице очистить блок ячеек В4:Е8 от содержимого по схеме Выделить
блок►Главная►Редактирование►Очистить►Очистить содержимое.
2.Перейти на лист Тбв и выбрать команду Формулы►Определенные имена►Присвоить имя. Появится окно Создание имени.
3.В поле Имя ввести СумНач, затем перейти в поле Диапазон и щелкнуть на правой кнопке с красной стрелкой. Окно уменьшится до размеров поля Создание имени-
Диапазон.
4.Выделить первый диапазон ячеек С10:С14, на которую ссылается формула (смотреть пункт предыдущих расчетов для ячейки В4) и задать знак операции умножения «*».
Выбрать второй диапазон ячеек D10:D14, на которую ссылается формула и задать следующий знак операции сложения «+». Выбрать третий диапазон ячеек и т.д.,
действовать по аналогии для получения всей формулы расчета для этой графы.
Одновременно в поле Диапазон появляется отображение массива формул. Ниже представлен окончательный вид массива формул с именем СумНач.
=TБB!$C$10:$C$14*TБB!$D$10:$D$14+TБB!$B$3*TБB!$C$10:$C$14*TБB!$G$10:$G$14+TБ
B!$C$10:$C$14*TБB!$B$10:$B$14*TБB!$E$10:$E$14/100
5. Нажать комбинацию клавиш <Shift+Ctrl+Enter>, чтобы сообщить Excel о том, что вводится не обычная формула, а массив формул.
В ответ Excel заключит формулу в фигурные скобки {}.
4
6. Использовать полученное имя для расчетов.
Действия:
•Перейти на лист Плв2 и выделить диапазон ячеек В4:В8.
•Выбрать команду Формулы►Определенные имена►Использовать в формуле.
•В окне списка имен выбрать имя СумНач.
•После появления в ячейке В4 имени вставляемого диапазона =СумНач, нажать комбинацию клавиш <Shift+Ctrl+Enter>.
В итоге, в диапазоне В4:В8 появятся результаты расчета (рис.2.33), а в окне ввода формул будет отображаться имя массива формул в виде {=СумНач}.
Для выполнения расчетов по остальным графам таблицы Плв2 действовать по аналогии с действиями пунктов 2-6. Имена для массивов формул придумать самостоятельно и, в
итоге, Вы должны получить вид таблицы Плв2 совпадающую с видом таблицы Плв (рис.
2.33).
Самостоятельная работа.
1.Добавить две позиции начислений:
−районный коэффициент 20%,
|
3 |
|
|
|
|
|
|
|
Y5 Kф3 Yi , |
г де Kф3 |
20% ; |
|
|
|
|||
|
i 1 |
|
|
|
|
|
|
|
− выслуга, |
|
|
|
|
|
|
|
|
|
K |
ф4 |
0%, |
если n4к |
1, |
|
||
|
|
ф4 |
10%, |
если 1 n4к |
3, |
|||
3 |
K |
|||||||
Y6 Kф4 Yi , |
г де |
|
20%, |
если 3 n4к |
7, |
|||
i 1 |
Kф4 |
|||||||
|
K |
ф4 |
30%, |
если n |
4к |
7, |
||
|
|
|
|
|
|
n4k - количество отработанных лет.
2.Добавить позицию удержаний:
−профсоюзный взнос, если работник состоит в профсоюзной организации,
N
X 4 m4 /100 * Y (i), г де m4 1%.
i 1
5