1. Общая характеристика задачи
1. Построить таблицы по приведенным ниже данным.
2. В таблице для заполнения столбцов «Фамилия» и «Отдел» использовать функцию ПРОСМОТР().
. Для получения результата в столбце «Сумма по окладу», используя функцию ПРОСМОТР(), по табельному номеру найти соответствующий оклад, разделить его на количество рабочих дней и умножить на количество отработанных дней. Сумма по надбавке считается аналогично.
4. Сформировать ведомость зарплаты.
. Данные результатной таблицы отсортировать по номеру отдела и рассчитать итоговые суммы по отделам. По результатам вычислений построить круговую диаграмму.
Таблица 1
Данные о сотрудниках
Таб. номер
|
Фамилия
|
Отдел
|
Оклад, руб.
|
Надбавка, руб.
|
001
|
Иванов И. И.
|
Отдел кадров
|
6000,00
|
4000,00
|
002
|
Петров П. П.
|
Бухгалтерия
|
4500,00
|
3000,00
|
003
|
Сидоров С. С.
|
Отдел кадров
|
5000,00
|
4500,00
|
004
|
Мишин М.М.
|
Столовая
|
5500,00
|
3500,00
|
005
|
Васин В. В.
|
Бухгалтерия
|
6500,00
|
1000,00
|
006
|
Львов Л.Л.
|
Отдел кадров
|
5000,00
|
3000,00
|
007
|
Волков В. В.
|
Отдел кадров
|
3000,00
|
3000,00
|
Таблица 2
Данные об учете рабочего времени
Таб. номер
|
Количество рабочих дней
|
Количество отработанных дней
|
001
|
23
|
23
|
002
|
23
|
20
|
003
|
27
|
27
|
004
|
23
|
23
|
005
|
23
|
21
|
006
|
27
|
22
|
007
|
23
|
11
|
Таблица 3
Графы таблицы для заполнения ведомости зарплаты
Таб. Номер
|
Фамилия
|
Отдел
|
Сумма по окладу, руб.
|
Сумма по надбавке, руб.
|
Сумма зарплаты, руб.
|
НДФЛ, %
|
Сумма НДФЛ, руб.
|
Сумма выдаче, руб.
|
|
|
|
|
|
|
13
|
|
|
|
|
|
|
|
|
|
|
|
. Описание алгоритма решения задачи
. Запустить табличный процессор MS Excel.
. Лист 1 переименовать в лист с названием Сотрудники.
. На рабочем листе Сотрудники MS Excel создать таблицу Данные о сотрудниках.
. Заполнить таблицу Данные о сотрудниках исходными данными (рис. 4)
Таблица 4
Данные о сотрудниках
Данные о сотрудниках
|
Таб. Номер
|
Фамилия
|
Отдел
|
Оклад, руб.
|
Надбавка, руб.
|
1
|
Иванов И.И.
|
Отдел кадров
|
6 000,00
|
4 000,00
|
2
|
Петров П.П.
|
Бухгалтерия
|
4 500,00
|
3 000,00
|
3
|
Сидоров С.С.
|
Отдел кадров
|
5 000,00
|
4 500,00
|
4
|
Мишин М.М.
|
Столовая
|
5 500,00
|
3 500,00
|
5
|
Васин В.В.
|
Бухгалтерия
|
6 500,00
|
1 000,00
|
6
|
Львов Л.Л.
|
Отдел кадров
|
5 000,00
|
3 000,00
|
7
|
Волков В.В.
|
Отдел кадров
|
3 000,00
|
3 000,00
|
Расположение таблицы «Данные о сотрудниках» на рабочем листе Сотрудники MS Excel
. Лист 2 переименовать в лист с названием Рабочее время.
. На рабочем листе Рабочее время MS Excel создать таблицу, в которой будут содержаться данные об учете рабочего времени.
. Заполнить таблицу со списком данных об учете рабочего времени исходными данными (табл. 5).
Таблица 5
Данные об учете рабочего времени
Данные об учете рабочего времени
|
Таб. номер
|
Количество рабочих дней
|
Количество отработанных дней
|
1
|
23
|
23
|
2
|
23
|
20
|
3
|
27
|
27
|
4
|
23
|
23
|
5
|
23
|
21
|
6
|
27
|
22
|
7
|
23
|
11
|
Расположение таблицы со списком «Данные об учете рабочего времени» на рабочем листе Рабочее время
. Лист 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 и так далее для остальных ячеек.
. Теперь ведомость зарплаты сформирована и показана в табл. 6.
Таблица 6
Ведомость зарплаты
Ведомость зарплаты
|
Таб. номер
|
Фамилия
|
Отдел
|
Сумма по окладу, руб.
|
Сумма по надбавке, руб
|
Сумма зарплаты, руб
|
НДФЛ, %
|
Суммма НДФЛ, руб
|
Сумма к выдаче, руб
|
1
|
Иванов И.И.
|
Отдел кадров
|
6000
|
4000
|
10000
|
13%
|
1300
|
8700
|
2
|
Петров П.П.
|
Бухгалтерия
|
3913,043
|
2608,696
|
6521,739
|
|
847,8261
|
5673,913
|
3
|
Сидоров С.С.
|
Отдел кадров
|
5000
|
4500
|
9500
|
|
1235
|
8265
|
4
|
Мишин М.М.
|
Столовая
|
5500
|
3500
|
9000
|
|
1170
|
7830
|
5
|
Васин В.В.
|
Бухгалтерия
|
5934,783
|
913,0435
|
6847,826
|
|
890,2174
|
5957,609
|
6
|
Львов Л.Л.
|
Отдел кадров
|
4074,074
|
2444,444
|
6518,519
|
|
847,4074
|
5671,111
|
7
|
Волков В.В.
|
Отдел кадров
|
1434,783
|
1434,783
|
2869,565
|
|
373,0435
|
2496,522
|
Расположение таблицы «Ведомость зарплаты» на рабочем листе Ведомость MS Excel
. Данные результатной таблицы отсортировать по отделу:
активизировать любую ячейку списка;
выбрать в строке меню команды Данные/Сортировка;
в окне Сортировка диапазона в поле Сортировать по выбрать отдел.
20. Рассчитать итоговые суммы по отделам:
активизировать любую ячейку списка;
выбрать в строке меню команды Данные/Итоги;
в окне Промежуточные итоги задать параметры:
при каждом изменении в: Отдел;
операция: Сумма.
21. После описанных действий таблица Ведомость зарплаты будет выглядеть следующим образом:
Таблица 7
Ведомость зарплаты
Ведомость зарплаты
|
Таб. номер
|
Фамилия
|
Отдел
|
Сумма по окладу, руб.
|
Сумма по надбавке, руб
|
Сумма зарплаты, руб
|
НДФЛ, %
|
Суммма НДФЛ, руб
|
Сумма к выдаче, руб
|
2
|
Петров П.П.
|
Бухгалтерия
|
3913,043
|
2608,696
|
6521,739
|
|
847,8261
|
5673,913
|
5
|
Васин В.В.
|
Бухгалтерия
|
5934,783
|
913,0435
|
6847,826
|
|
890,2174
|
5957,609
|
|
|
Бухгалтерия Итог
|
|
|
|
|
|
11631,52
|
1
|
Иванов И.И.
|
Отдел кадров
|
6000
|
4000
|
10000
|
13%
|
1300
|
8700
|
3
|
Сидоров С.С.
|
Отдел кадров
|
5000
|
4500
|
9500
|
|
1235
|
8265
|
6
|
Львов Л.Л.
|
Отдел кадров
|
4074,074
|
2444,444
|
6518,519
|
|
847,4074
|
5671,111
|
7
|
Волков В.В.
|
Отдел кадров
|
1434,783
|
1434,783
|
2869,565
|
|
373,0435
|
2496,522
|
|
|
Отдел кадров Итог
|
|
|
|
|
|
25132,63
|
4
|
Мишин М.М.
|
Столовая
|
5500
|
3500
|
9000
|
|
1170
|
7830
|
|
|
Столовая Итог
|
|
|
|
|
|
7830
|
|
|
Общий итог
|
|
|
|
|
|
44594,15
|
Расположение таблицы «Ведомость зарплаты» на листе Ведомость MS Excel
) После этого построить круговую диаграмму результатов вычислений:
в строке меню выбрать команды Данные/Сводная таблица;
в окне Мастер сводных таблиц и диаграмм нажать кнопку Далее;
в окне Мастер сводных таблиц и диаграмм - Макет перетащить поля, как на рис. 1;
нажать кнопку ОК;
нажать кнопку Готово;
После этапов проделанной работы получилась диаграмма результатов вычислений, которая отображена на рис. 1.
Рис. 1. Создание Макета Сводной таблицы
Рис. 2. Диаграмма результатов вычислений
Рис. 3 Диаграмма
|
