Исходные данные для расчета заработной платы организации представлены на рис. 1 и 2.
. Построить таблицы по приведенным ниже данным.
. В таблице на рис. 3 для заполнения столбцов «Фамилия» и «Отдел» использовать функцию ПРОСМОТР().
. Для получения результата в столбце «Сумма по окладу», используя функцию ПРОСМОТР (), по табельному номеру найти соответствующий оклад, разделить его на количество рабочих дней и умножить на количество отработанных дней. Сумма по надбавке считается аналогично. Данные берутся из графы «Надбавка».
. Сформировать ведомость зарплаты.
. Данные результатной таблицы отсортировать по номеру отдела и рассчитать итоговые суммы по отделам. По результатам вычислений построить круговую диаграмму.
Рис. 1. Данные о сотрудниках
| Табельный номер | Фамилия | Отдел | Оклад, руб | Надбавка, руб |
| Иванова И.И. | Отдел кадров | 7 000,00 | 4 000,00 | |
| Петрова П.П. | Бухгалтерия | 9 500,00 | 3 000,00 | |
| Сидорова С.С. | Отдел кадров | 6 000,00 | 4 500,00 | |
| Мишин М.М. | Столовая | 6 500,00 | 3 500,00 | |
| Васин В.В. | Бухгалтерия | 7 500,00 | 1 000,00 | |
| Львов Л.Л. | Отдел кадров | 4 000,00 | 3 000,00 | |
| Волков В.В. | Отдел кадров | 3 000,00 | 3 000,00 |
Рис. 2. Данные об учете рабочего времени
| Табельный номер | Количество рабочих дней | Количество отработанных дней |
Рис. 3. Графы таблицы для заполнения ведомости зарплаты
| Табельный номер | Фамилия | Отдел | Сумма по окладу, руб | Сумма по надбавке, руб | Сумма зарплаты, руб | НДФЛ, % | Сумма НДФЛ, руб | Сумма к выдаче, руб |
| Всего |
Описание алгоритма решения задачи
Исходные данные для расчета заработной платы организации представлены на рис. 1 и 2.
1. Построить таблицы по приведенным ниже данным.
. В таблице на рис. 3 для заполнения столбцов «Фамилия» и «Отдел»
использовать функцию ПРОСМОТР().
. Для получения результата в столбце «Сумма по окладу», используя функцию ПРОСМОТР(), по табельному номеру найти соответствующий оклад, разделить его на количество рабочих дней и умножить на количество отработанных дней. Сумма по надбавке считается аналогично. Данные
берутся из графы «Надбавка».
. Сформировать ведомость зарплаты.
. Данные результатной таблицы отсортировать по номеру отдела и рассчитать итоговые суммы по отделам. По результатам вычислений построить диаграмму.
Решение задачи
. Запустить табличный процессор MS Excel.
. Лист 1 переименовать в лист с названием Сотрудники.
. На рабочем листе Сотрудники MS Excel создать таблицу Данные о сотрудниках.
4. Заполнить таблицу Данные о сотрудниках исходными данными (рис. 7)
| Данные о сотрудниках | ||||
| Таб. Номер | Фамилия | Отдел | Оклад, руб. | Надбавка, руб. |
| Иванов И.И. | Отдел кадров | 6 000,00 | 4 000,00 | |
| Петров П.П. | Бухгалтерия | 4 500,00 | 3 000,00 | |
| Сидоров С.С. | Отдел кадров | 5 000,00 | 4 500,00 | |
| Мишин М.М. | Столовая | 5 500,00 | 3 500,00 | |
| Васин В.В. | Бухгалтерия | 6 500,00 | 1 000,00 | |
| Львов Л.Л. | Отдел кадров | 5 000,00 | 3 000,00 | |
| Волков В.В. | Отдел кадров | 3 000,00 | 3 000,00 |
Рис. 7 Расположение таблицы «Данные о сотрудниках» на рабочем листе Сотрудники MS Excel
. Лист 2 переименовать в лист с названием Рабочее время.
. На рабочем листе Рабочее время MS Excel создать таблицу, в которой будут содержаться данные об учете рабочего времени.
. Заполнить таблицу со списком данных об учете рабочего времени исходными данными (рис. 8).
| Данные об учете рабочего времени | ||
| Табельный номер | Количество рабочих дней | Количество отработанных дней |
Рис. 8. Расположение таблицы со списком «Данные об учете рабочего времени» на рабочем листе Рабочее время
. Лист 3 переименовать в лист с названием Зарплата.
. На рабочем листе Зарплата MS Excel создать таблицу, в которой будет содержаться ведомость зарплаты.
. Заполнить таблицу «Ведомость зарплаты» исходными данными.
. Заполнить графу Фамилия таблицы «Ведомость зарплаты», находящейся на листе Зарплата следующим образом:
а) занести в ячейку В3 формулу: =ПРОСМОТР (В3; Сотрудники!$A$3: $A$9; Сотрудники!$B$3:$B$9).
б) размножить введенную в ячейку В3 формулу для остальных ячеек (с В3 по В9) данной графы.
Таким образом, будет выполнен цикл, управляющий параметром которого является номер строки.
. Заполнить графу Отдел таблицы «Ведомость зарплаты» аналогичным образом, используя функцию ПРОСМОТР:
а) занести в ячейку С3 формулу: =ПРОСМОТР (С3; Сотрудники!$A$3: $A$9; Сотрудники! $B$3:$B$9).
б) размножить введенную формулу в ячейку С3 для остальных ячеек (с С3 по С9) данной графы.
. Заполнить графу «Сумма по окладу на листе» на листе Зарплата через функцию ПРОСМОТР(). Для этого D3/B3*C3.
. Графа «Сумма по надбавке» заполняется аналогично.
. Рассчитать сумму зарплаты. Для этого в ячейку F3 вводится формула: =D3+E3 и Enter, и так для каждой строки.
. Рассчитать сумму НДФЛ, вводится формула в ячейкуН3: =F3*G3 и подобным образом для других строк (=F4*G3).
. Рассчитать сумму к выдаче, используя следующую формулу в ячейке I3:=F3+H3 и так далее для остальных ячеек.
. Теперь ведомость зарплаты сформирована и показана на рис. 9.
. Данные результатной таблицы отсортировать по отделу:
· активизировать любую ячейку списка;
· выбрать в строке меню команды Данные / Сортировка;
· в окне Сортировка диапазона в поле Сортировать по выбрать отдел.
) Рассчитать итоговые суммы по отделам:
· активизировать любую ячейку списка;
· выбрать в строке меню команды Данные / Итоги;
· в окне Промежуточные итоги задать параметры:
· при каждом изменении в: Отдел;
| Ведомость зарплаты | ||||||||
| Таб. номер | Фамилия | Отдел | Сумма по окладу, руб. | Сумма по надбавке, руб | Сумма зарплаты, руб | НДФЛ % | Суммма НДФЛ, руб | Сумма к выдаче, руб |
| Иванов И.И. | Отдел кадров | 13% | ||||||
| Петров П.П. | Бухгалтерия | 3913,04 | 2608,696 | 6521,739 | 847,826 | 5673,91 | ||
| Сидоров С.С. | Отдел кадров | |||||||
| Мишин М.М. | Столовая | |||||||
| Васин В.В. | Бухгалтерия | 5934,78 | 913,0435 | 6847,826 | 890,217 | 5957,61 | ||
| Львов Л.Л. | Отдел кадров | 4074,07 | 2444,444 | 6518,519 | 847,407 | 5671,11 | ||
| Волков В.В. | Отдел кадров | 1434,78 | 1434,783 | 2869,565 | 373,044 | 2496,52 |
Рис. 9. Расположение таблицы «Ведомость зарплаты» на рабочем листе Ведомость MS Excel
) После описанных действий таблица Ведомость зарплаты будет выглядеть следующим образом:
| Ведомость зарплаты | |||||||||
| Таб. номер | Фамилия | Отдел | Сумма по окладу, руб. | Сумма по надбавке, руб | Сумма зарплаты, руб | НДФЛ, % | Суммма НДФЛ, руб | Сумма к выдаче, руб | |
| Петров П.П. | Бухгалтерия | 3913,04 | 2608,696 | 6521,739 | 13% | 847,826 | 5673,91 | ||
| Васин В.В. | Бухгалтерия | 5934,78 | 913,0435 | 6847,826 | 890,217 | 5957,61 | |||
| Бухгалтерия Итог | 11631,5 | ||||||||
| Иванов И.И. | Отдел кадров | 13% | |||||||
| Сидоров С.С. | Отдел кадров | ||||||||
| Львов Л.Л. | Отдел кадров | 4074,07 | 2444,444 | 6518,519 | 847,407 | 5671,11 | |||
| Волков В.В. | Отдел кадров | 1434,78 | 1434,783 | 2869,565 | 373,044 | 2496,52 | |||
| Отдел кадров Итог | 25132,6 | ||||||||
| Мишин М.М. | Столовая | 13% | |||||||
| Столовая Итог | |||||||||
| Общий итог | 44594,2 | ||||||||
Рис. 10. Расположение таблицы «Ведомость зарплаты» на листе Ведомость MS Excel
22) После этого построить круговую диаграмму результатов вычислений:
· в строке меню выбрать команды Данные / Сводная таблица;
· в окне Мастер сводных таблиц и диаграмм нажать кнопку Далее;
· в окне Мастер сводных таблиц и диаграмм - Макет перетащить поля, как на рис. 11;
· нажать кнопку ОК;
· нажать кнопку Готово;
После этапов проделанной работы получилась диаграмма результатов вычислений, которая отображена на рис. 12.
Рис. 11. Создание Макета Сводной таблицы
Рис. 12. Диаграмма результатов вычислений
Рис. 13. Круговая диаграмма
Список литературы
1. Акулов О.А, Медведев Н.В. Информатика: базовый курс. - М.: Омега - Л, 2005
2. Бройдо В.Л. Вычислительные системы, сети и телекоммуникации: Учебник для вузов. 2-е изд. - СПб.: Питер, 2006 - 703 с: ил.
. Информатика в экономике: Учебное пособие/ Под ред. Профессора Б.Е. Одинцова, профессора А.Н Романова. - М.: Вузовский учебник, 2008. - 478 с.
. Кагаловский М.Р. Энциклопедия технологий баз данных. - М.:Финансы и статистика, 2002. -800 с.
. Лабораторный практикум для студентов II курса всех специальностей. - М.: Вузовский учебник, 2006. - 94 с.
. Леонтьев В.П. Новейшая энциклопедия персонального компьютера 2003. - М.: ОЛМА - ПРЕСС, 2003. - 567 с.