Защита информации: Методические указания по выполнению лабораторных работ в среде Windows XP, MS Office 2007, страница 7

·  Для выхода из окна команды нажать ОК.

9.  Заполнить данными таблицу «Данные о сотрудниках». Сохранить изменения. Таблицы на листе «Справочники» с данными представлены на рис.9.

Рис.8. Определение ограничений на ввод данных в столбец «Подразделение»

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

Задание 2. Выполнить расчет начислений сотрудникам, итоговых значений.

Выполнение расчетов в таблицах, подсчет итогов

10.  Перейти на лист «Ведомость». В диапазоне ячеек A1:F15 создать таблицу «Сводная ведомость»: ячейки A1:F1 – заголовок таблицы, ячейки A2:F2 – заголовки столбцов («Фамилия», «Код подразделения», «Стаж, в годах», «Оклад», «Премия», «Начислено»), ячейки A3:F15 – данные.

11.  Продублировать в диапазоне A3:A15 листа «Ведомость» данные из столбца «Фамилия» таблицы «Данные о сотрудниках» (диапазон A9:A21 листа «Справочники») –выделить диапазон A3:A15 листа «Ведомость», в строке формул нажать знак =, перейти на лист листа «Справочники», выделить диапазон A9:A21, а затем ввести сформированную формулу как формулу массива, для чего следует одновременно нажать клавиши CTRL+SHIFT+ENTER.

12.  Заполнить столбец «Код подразделения» таблицы «Сводная ведомость»:

·  Выделить ячейку B3 листа «Ведомость», щелкнув на ней мышью. Перейти на вкладку Функции и выбрать функцию ВПР в группе Библиотека функций/Ссылки и массивы ;

·  В окне конструктора функции ВПР выбрать Искомое_значение: ячейка этой же строки, содержащая фамилию (A3), Таблица: выделить область данных таблицы «Данные о сотрудниках» с листа «Справочники», ссылку преобразовать в абсолютную, нажав кнопку F4 (Справочники!$A$9:$D$21), Номер_столбца: ввести номер столбца «Подразделение» по счету в таблице «Данные о сотрудниках» (4), Интервальный_просмотр: ложь. Вид функции ВПР в конструкторе приведен на рис.10. Нажать OК.

·  Результирующий вид функции ВПР в ячейке B3:

=ВПР(A3;Справочники!$A$9:$D$21;4;ЛОЖЬ)

·  Скопировать ячейку B3 на весь диапазон данных столбца «Код подразделения» таблицы «Сводная ведомость» (B4:B15) – для этого подвести указатель мыши к правому нижнему углу ячейки B3 так, чтобы указатель принял вид черного крестика, затем нажать левую кнопку мыши и не отпуская ее растащить ячейку на весь диапазон B4:B15.

Рис.10. Задание параметров функции ВПР в конструкторе

13.  Заполнить столбец «Стаж, в годах» таблицы «Сводная ведомость»:

·   Задать числовой формат данных для столбца «Стаж, в годах», для этого: выделить диапазон ячеек C3:C15, щелкнуть правой кнопкой мыши, выбрать из контекстного меню пункт Формат ячеек, на вкладке Число выбрать тип Числовой и нажать ОК.

·  Сформировать в ячейке С3 формулу для вычисления стажа: для получения даты поступления из справочника используется аналогичная предыдущему заданию функция ВПР, данные о дате берутся из 2 столбца справочной таблицы; для вычисления текущей даты используется функция СЕГОДНЯ (группа Дата и время ), для перевода временного интервала из дней в годы – функция ДОЛЯГОДА (группа Дата и время). Результирующий вид формулы в ячейке C3:

=ДОЛЯГОДА(ВПР(A3;Справочники!$A$9:$D$21;2;ЛОЖЬ);СЕГОДНЯ())

Примечание: Если функция ДОЛЯГОДА не доступна стаж в годах можно вычислить как отношение разности между текущей датой (функция СЕГОДНЯ) и даты поступления на работу к 365 (числу дней в году).

·  Скопировать ячейку C3 на весь диапазон данных столбца «Стаж, в годах» таблицы «Сводная ведомость» (C4:C15).

14.  Заполнить столбец «Оклад» таблицы «Сводная ведомость»:

·  Примем базовую ставку равной 7000 руб., надбавку за каждый разряд – 500 руб. Тогда начисления по окладу будут рассчитываться по следующей формуле:

Оклад = 7000 + 500 * (Разряд – 10).