Лабораторная работа по дисциплине Информатика

Лабораторная работа 3.

Задание :

• Создать справочные таблицы:

Таблица 1 - Ведомость учета отработанного времени

Таблица 2 - Справочник распределения рабочих по цехам и разрядам

Таблица 3 - Справочник тарифов

• Создать итоговую таблицу:

Таблица 4 - Ведомость начисления зарплаты

• Заполнить справочники для 15 служащих.

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

• Привести круговую диаграмму распределения сумм зарплаты по цехам, автоматически корректируемую при изменении данных в исходной таблице.

• Определить разряд с максимальной суммарной зарплатой.

Ход работы:

Шаг № 1. Справочник распределения рабочих по цехам и разрядам

Запустим программу Microsoft Excel. Для этого нажимаем кнопку пуск находящуюся на рабочем столе, тем самым попадаем в Главное меню операционной системы Windows. В главном меню находим пункт «Программы» и в открывшемся подменю находим программу Microsoft Excel. Нажимаем и запускаем программу.

На рабочем листе Microsoft Excel размечаем таблицу под названием "Справочник распределения рабочих по цехам и разрядам". Таблица размещается, начиная с ячейки "A1" по ячейку "D17" Эта таблица содержит четыре столбца: "Табельный номер", "ФИО", "Разряд", "Цех" и семнадцать строк: первая - объединённые четыре ячейки в одну с названием таблицы, вторая - название столбцов, последующие пятнадцать для заполнения данными. Рабочая область таблицы имеет диапазон "A3:D17".

Рис 1. Распределение рабочих по цехам и разрядам

Созданную таблицу заполняем данными.

Рис 2. Данные таблицы Распределение рабочих по цехам и разрядам

Шаг № 2. Справочник тарифов

Создаём таблицу "Справочник тарифов". Таблица располагается на рабочем листе с ячейки "A19" по ячейку "B26". Таблица состоит из двух столбцов и восьми строк. Аналогично таблице, созданной ранее, в первой строке имеет название, во второй название столбцов, а рабочая область таблицы с диапазоном "A21:B26" данные соотношения разряда к тарифной ставке.

Рис 3. Справочник тарифов

Заполняем созданную таблицу исходными данными.

Рис 4. Данные таблицы Справочник тарифов

Шаг № 3. Ведомость учёта отработанного времени

По аналогии с таблицей "Справочник распределения рабочих по цехам и разрядам" создаём таблицу "Ведомость учёта отработанного времени". Таблица располагается на рабочем листе в диапазоне ячеек "F1:H17". В таблице три столбца: "Табельный номер", "ФИО" и "Отработанное время, (час)". Таблица служит для определения количества отработанного времени для каждого рабочего персонально.

Рис 5 Ведомость учета отработанного времени

Заполняем созданную таблицу исходными данными. Так как первые два столбца идентичны таблице "Справочник распределения рабочих по цехам и разрядам", то для эффективности используем ранее введённые данные. Для этого перейдём в первую таблицу, выделим диапазон ячеек "A3:B17", данные которого соответствуют списку из табельных номеров и фамилий работников, и скопируем область в буфер обмена, нажав соответствующую кнопку на панели инструментов.

Рис 6. Копирование данных из одной таблицы в другую

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

Рис 7. Результаты копирования данных из одной таблицы в другую

Теперь заполним третий столбец таблицы в соответствии с исходными данными.

Рис 8. Данные таблицы Ведомость учета отработанного времени

Шаг № 4. Ведомость начислений зарплаты

Эта таблица так же имеет два столбца идентичных предыдущей таблице. По аналогии создаём таблицу "Ведомость начислений зарплаты".

Рис 9. Ведомость начисления зарплаты

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

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

Теперь заполним третий столбец таблицы. Данные третьего столбца должны рассчитываться из исходных данных предыдущих таблиц и интерактивно меняться при изменении какого-либо значения. Для этого столбец должен быть заполнен формулами расчёта по каждому работнику. Начисленная зарплата рассчитывается исходя из разряда рабочего, количества отработанного им времени. ЗП = ТАРИФ * ЧАСЫ. Для расчёта воспользуемся функцией Microsoft Excel "ВПР".

Рис 10. Расчет начисленной зарплаты

В ячейку "H21" вводим формулу:

"=ВПР(ВПР(F21;A3:D17;3);A21:B26;2)*ВПР(F21;F3:H17;3)"

В первом множителе функция ВПР:

(ВПР(ВПР(F21;A3:D17;3);A21:B26;2)) определяет тариф работника из таблицы "Справочник тарифов" (диапазон "A21:B26"). Для этого, нам приходится пользоваться вложением функции ВПР (ВПР(F21;A3:D17;3)). Тут функция возвращает нам тариф данного работника из таблицы "Справочник распределения рабочих по цехам и разрядам" (диапазон "A3:D17") и подставляет это значение как искомое для первой функции ВПР.

Во втором множителе (ВПР(F21;$F$3:$H$17;3)) функция ВПР определяет отработанное работником время из таблицы "Ведомость начислений зарплаты" (диапазон "F3:H17").

Для того чтобы применить автозаполнение к заполнению результирующего столбца введём формулу с абсолютными ссылками: "=ВПР(ВПР(F21;$A$3:$D$17;3);$A$21:$B$26;2)*ВПР(F21;$F$3:$H$17;3)".

Применим автозаполнение.

Рис 10. Применение абсолютной адресации и результаты подсчета зарплаты

Получили заполненный столбец результирующих данных.

Шаг № 5. Определение цеха с заработной платой

После начисления зарплаты нужно ее распределить по цехам. Для этого в программе Microsoft Excel используем формулу «ЕСЛИ».

1. При помощи формулы «ЕСЛИ» распределяем зарплату по цехам, для этого записываем в ячейку J21 формулу =ЕСЛИ(I21=$J$20;H21;0)

Если цех соответствует выбранному цеху, то выбирается зарплата, а иначе ноль.

После использования функции «ЕСЛИ» считаем суммарное значение начислений по цехам, используя функцию «СУММ». Например, для 1 цеха формула такая:

=СУММ(J20:J35), получаем суммарное значение начислений по первому цеху.

Рис 11. Определение цеха с заработной платой и суммарное значение начислений по цехам

Для построения диаграммы приписывается строка с разрядами, например, Цех 1, Цех 2 и т.д. Диаграмма строится по данным с суммарными значениями начислений.

Рис 11. Диаграмма «Распределение зарплаты по цехам»

Шаг № 6. Определение разряда с максимальной суммарной заработной платой

Распределяем зарплату по разрядам ( с 1 по 6) используя формулу:

=ЕСЛИ(M21=$N$20;H21;0).

Если разряд соответствует выбранному разряду, то выбирается зарплата, а иначе ноль.

Находим суммарное значение начислений по разрядам:

=СУММ(N20:N35).

Рис 12. . Определение разряда с заработной платой и суммарное значение начислений по разрядам

Для построения графика приписывается строка с разрядами, например, Разряд 1, Разряд 2 и т.д.

Гистограмма строится по данным с суммарными значениями начислений «N20:N35».

Рис 13. Диаграмма «Распределение зарплаты по разрядам.

Делаем выводы….