· Сформировать в ячейке D3 формулу для вычисления оклада: для получения разряда из справочника используется аналогичная предыдущему заданию функция ВПР, данные о разряде берутся из 3 столбца справочной таблицы. Результирующий вид формулы в ячейке D3:
=7000+500*(ВПР(A3;Справочники!$A$9:$D$21;3;ЛОЖЬ)-10)
· Скопировать ячейку D3 на весь диапазон данных столбца «Оклад» таблицы «Сводная ведомость» (D4:D15).
15. Заполнить столбец «Премия» таблицы «Сводная ведомость»:
· Сформировать в ячейке E3 формулу для вычисления размера премии: используется
функция ЕСЛИ (группа Логические 
). Результирующий вид формулы в ячейке E3:
=ЕСЛИ(C3>7;D3*0,5;0)
· Скопировать ячейку E3 на весь диапазон данных столбца «Премия» таблицы «Сводная ведомость» (E4:E15).
16. Заполнить столбец «Начислено» таблицы «Сводная ведомость»: в ячейку F3 поместить формулу =D3+E3, затем скопировать ячейку F3 на весь диапазон данных столбца «Начислено» таблицы «Сводная ведомость» (F4:F15). Результирующий вид таблицы «Сводная ведомость» (текущая дата 29.01.2008) приведена на рис.11.
17. Перейти на лист «Отчет». В диапазоне ячеек A1:B7 создать таблицу «Сводные данные по подразделениям». В ячейки A2:A7 занести текст «Подразделение», «Наименование», «Руководитель», «Общая численность сотрудников», «Сумма премий», «Всего начислено».
Рис.11. Данные таблицы на листе «Ведомость»
18.
Ячейку В2 для выбора кода
подразделения выделить цветом (вкладка Главная, группа Шрифт, цвет
заливки 
).
19. Задать ограничения на ввод данных в ячейку B2 – значения выбираются из списка 1;2;3 (аналогично п.5, рис.5).
20. В ячейке B3 сформировать формулу для поиска в справочной таблице «Данные о подразделениях» наименования подразделения (функция ВПР):
=ВПР(B2;Справочники!A3:C5;2;ЛОЖЬ)
21. В ячейке B4 сформировать формулу для поиска в справочной таблице «Данные о подразделениях» фамилии руководителя подразделения (функция ВПР):
=ВПР(B2;Справочники!A3:C5;3;ЛОЖЬ)
22. В ячейке B5 сформировать формулу для подсчета общей численности сотрудников выбранного отдела по таблице «Сводная ведомость» (этот итог можно также подсчитать по таблице «Данные о сотрудниках») с помощью функции СЧЕТЕСЛИ (группа Другие функции/Статистические):
=СЧЁТЕСЛИ(Ведомость!B3:B15;B2)
23.
В ячейке B6 сформировать формулу для подсчета суммы премий
сотрудников выбранного отдела по таблице «Сводная ведомость» (функция СУММЕСЛИ,
группа Математические 
):
=СУММЕСЛИ(Ведомость!B3:B15;B2;Ведомость!E3:E15)
24. В ячейке B7 сформировать формулу для подсчета суммы начислений сотрудников выбранного отдела по таблице «Сводная ведомость» (функция СУММЕСЛИ):
=СУММЕСЛИ(Ведомость!B3:B15;B2;Ведомость!F3:F15)
Результирующий вид таблицы на листе «Отчет» (текущая дата 29.01.2008) приведена на рис.12.
25. Сохранить изменения в книге Организация
Рис.12. Данные таблицы на листе «Отчет»
Задание 3. Установить защиту книги таким образом, что:
· Несанкционированные пользователи не могут просматривать данные книги;
· Пользователи, знающие пароль нижнего уровня, могут просматривать только статистические (итоговые) данные о сотрудниках фирмы, исключая суммы начислений;
· Сотрудники, знающие пароль нижнего и среднего уровней, могут просматривать итоговые и частные данные о сотрудниках, за исключением сумм начислений;
· Сотрудники, знающие пароль верхнего уровня и остальные пароли, могут как просматривать, так и изменять все данные.
Защита книги и ее элементов
26. Придумать три пароля. Пусть пароль нижнего уровня для открытия книги – low, пароль среднего уровня для изменения структуры книги – medium, пароль верхнего уровня для изменения данных на листах книги – high.
27. Ограничить доступ к книге «Организация», установив пароль для открытия:
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.