Создание таблиц и ограничений на ввод данных

1. Создать на диске рабочую папку, например, D:\Petrov.

2. Открыть приложение MS Excel. Создать новую книгу, сохранить ее в рабочую папку с именем Организация.

3. Переименовать Лист1 в «Отчет», Лист2 – в «Ведомость», Лист3 – в «Справочники». Для этого щелкнуть правой кнопкой мыши по ярлычку листа и выбрать команду Переименовать из контекстного меню.

4. Перейти на лист «Справочники». Создать таблицу «Данные о подразделениях»: выделить диапазон ячеек A1:C5, выделить границы диапазона (вкладка Главная, группа Шрифт/ Границы ). Объединить ячейки A1:C1 (выделить диапазон A1:C1, выполнить команду на вкладке Главная, группа Выравнивание). Ввести в объединенную ячейку текст заголовка таблицы: «Данные о подразделениях». В ячейки A2, B2 и С2 соответственно ввести текст заголовков столбцов таблицы: «Код подразделения», «Наименование», «Руководитель».

5. Задать ограничения на ввод данных в столбец «Код подразделения»:

· Выделить интервал ячеек A3:A5, перейти на вкладку Данные и выполнить команду Проверка данных (в группе Работа с данными),

· В окне команды Проверка вводимых значений на вкладке Параметры выбрать Тип данных: Список, в строку Источник набрать: 1;2;3 (через точку с запятой) – рис.6.

· Для выхода из окна команды нажать ОК. После этого в таблицу можно заносить данные, выбирая их из списка.

Рис.6. Задание ограничений на ввод данных в столбец

«Код подразделения»

6. Заполнить данными таблицу «Данные о подразделениях».

7. В диапазоне ячеек A7:D21 создать таблицу «Данные о сотрудниках»: ячейки A7:D7– заголовок таблицы (объединить ячейки), ячейки A8:D8 – заголовки столбцов («Фамилия», «Дата поступления», «Разряд», «Подразделение»), ячейки A9:D21 – данные.

8. Задать ограничения на ввод данных в столбец «Разряд»:

· Выделить интервал ячеек С9:С21, на вкладке Данные в группе Работа с данными выполнить команду Проверка данных ,

· В окне команды Проверка вводимых значений на вкладке Параметры выбрать Тип данных: Целое число, Значение: между, в строке Минимум набрать: 10, в строке Максимум – 17 – рис.7.

· На вкладке Сообщение об ошибке окна команды в строке Заголовок: ввести текст Несуществующий разряд, а в строке Сообщение: Разряд от 10 до 17.

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

Рис.7. Задание ограничений на ввод данных в столбец «Разряд»

9. Задать ограничения на ввод данных в столбец «Подразделение»:

· Выделить интервал ячеек D9:D21, выполнить команду Проверка данных,

· В окне команды Проверка вводимых значений на вкладке Параметры выбрать Тип данных: Список, перейти в строку Источник, а затем, не выходя из окна команды, выделить на листе «Справочники» диапазон ячеек A3:A5 таблицы «Данные о подразделениях», содержащий коды подразделений – ссылка на диапазон будет занесена в строку Источник – рис.8.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

· Сформировать в ячейке D3 формулу для вычисления оклада: для получения разряда из справочника используется аналогичная предыдущему заданию функция ВПР, данные о разряде берутся из 3 столбца справочной таблицы. Результирующий вид формулы в ячейке D3:

=7000+500*(ВПР(A3;Справочники!$A$9:$D$21;3;ЛОЖЬ)-10)

· Скопировать ячейку D3 на весь диапазон данных столбца «Оклад» таблицы «Сводная ведомость» (D4:D15).

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

· Сформировать в ячейке E3 формулу для вычисления размера премии: используется функция ЕСЛИ(группа Логические ). Результирующий вид формулы в ячейке E3:

=ЕСЛИ(C3>7;D3*0,5;0)

· Скопировать ячейку E3 на весь диапазон данных столбца «Премия» таблицы «Сводная ведомость» (E4:E15).

17. Заполнить столбец «Начислено» таблицы «Сводная ведомость»: в ячейку F3 поместить формулу =D3+E3, затем скопировать ячейку F3 на весь диапазон данных столбца «Начислено» таблицы «Сводная ведомость» (F4:F15). Результирующий вид таблицы «Сводная ведомость» (текущая дата 29.01.2008) приведена на рис.11.

18. Перейти на лист «Отчет». В диапазоне ячеек A1:B7создать таблицу «Сводные данные по подразделениям». В ячейки A2:A7 занести текст «Подразделение», «Наименование», «Руководитель», «Общая численность сотрудников», «Сумма премий», «Всего начислено».

Рис.11. Данные таблицы на листе «Ведомость»

19. Ячейку В2 для выбора кода подразделения выделить цветом (вкладка Главная, группа Шрифт, цвет заливки ).

20. Задать ограничения на ввод данных в ячейку B2 – значения выбираются из списка 1;2;3 (аналогично п.5, рис.5).

21. В ячейке B3 сформировать формулу для поиска в справочной таблице «Данные о подразделениях» наименования подразделения (функция ВПР):

=ВПР(B2;Справочники!A3:C5;2;ЛОЖЬ)

22. В ячейке B4 сформировать формулу для поиска в справочной таблице «Данные о подразделениях» фамилии руководителя подразделения (функция ВПР):

=ВПР(B2;Справочники!A3:C5;3;ЛОЖЬ)

23. В ячейке B5 сформировать формулу для подсчета общей численности сотрудников выбранного отдела по таблице «Сводная ведомость» (этот итог можно также подсчитать по таблице «Данные о сотрудниках») с помощью функции СЧЕТЕСЛИ (группа Другие функции/Статистические):

=СЧЁТЕСЛИ(Ведомость!B3:B15;B2)

24. В ячейке B6 сформировать формулу для подсчета суммы премий сотрудников выбранного отдела по таблице «Сводная ведомость» (функция СУММЕСЛИ, группа Математические ):

=СУММЕСЛИ(Ведомость!B3:B15;B2;Ведомость!E3:E15)

25. В ячейке B7 сформировать формулу для подсчета суммы начислений сотрудников выбранного отдела по таблице «Сводная ведомость» (функция СУММЕСЛИ):

=СУММЕСЛИ(Ведомость!B3:B15;B2;Ведомость!F3:F15)

Результирующий вид таблицы на листе «Отчет» (текущая дата 29.01.2008) приведена на рис.12.

26. Сохранить изменения в книге Организация

Рис.12. Данные таблицы на листе «Отчет»

Задание 3. Установить защиту книги таким образом, что:

· Несанкционированные пользователи не могут просматривать данные книги;

· Пользователи, знающие пароль нижнего уровня, могут просматривать только статистические (итоговые) данные о сотрудниках фирмы, исключая суммы начислений;

· Сотрудники, знающие пароль нижнего и среднего уровней, могут просматривать итоговые и частные данные о сотрудниках, за исключением сумм начислений;

· Сотрудники, знающие пароль верхнего уровня и остальные пароли, могут как просматривать, так и изменять все данные.

Защита книги и ее элементов

27. Придумать три пароля. Пусть пароль нижнего уровня для открытия книги – low, пароль среднего уровня для изменения структуры книги – medium, пароль верхнего уровня для изменения данных на листах книги – high.

28. Ограничить доступ к книге «Организация», установив пароль для открытия:

· щелкнуть значок Кнопка Microsoft Office , затем выбрать команду Подготовить/Зашифровать документ.

· В окне команды Шифрование документа ввести пароль low и нажать ОК. В появившемся окне Подтверждение пароля ввести пароль low еще раз и нажать ОК.

· Сохранить книгу. Закрыть ее и открыть заново, введя при открытии пароль low.

29. На листе «Ведомость» скрыть столбцы с данными о начислениях («Оклад», «Премия», «Начислено»):

· Перейти на лист «Ведомость». Выделить столбцы D, E, F, щелкнув мышью на заголовках столбцов при удерживаемой клавише SHIFT.

· Выполнить команду Скрытьиз контекстного меню (для вызова контекстного меню щелкнуть правой клавишей мыши в области выделения).

30. Защитить от изменений лист «Ведомость»:

· Находясь на листе «Ведомость», выполнить команду Защитить лист на вкладкеРецензирование,

· В окне команды ввести пароль high, установить разрешения для защищенного листа: выделение заблокированных ячеек, выделение незаблокированных ячеек (установлены по умолчанию), нажать ОК, ввести пароль high еще раз в окне подтверждения пароля, нажать ОК.

· Проверить действие установленной защиты листа «Ведомость»: выделить столбцы С и G, смежные со скрытыми столбцами, и проверить, что невозможно отобразить скрытые столбцы (команда Отобразить в контекстном меню недоступна), невозможно внесение изменений в ячейки листа.

31. Перейти на лист «Справочники». Защитить лист «Справочники» с паролем high.

32. Скрыть лист «Справочники»: щелкнуть правой кнопкой мыши на ярлыке листа «Справочники» и выполнить команду Скрыть из контекстного меню.

33. Скрыть лист «Ведомость».

34. Защитить структуру книги «Организация»:

· На вкладке Рецензирование выбрать группу Защитить книгу и выполнить команду Защита структуры и окон. В окне команды установить флажок структуру и ввести пароль medium, нажать ОК. Ввести пароль medium еще раз в окне подтверждения и нажать ОК.

· Проверить действие защиты. Убедиться, что невозможно отобразить скрытые листы, невозможно удалить лист «Отчет» (в контекстном меню листа «Отчет» команды Отобразить, Удалить недоступны), нельзя добавить в книгу новый лист.

35. На листе «Отчет» сделать доступным выбор кода подразделения в ячейке B2 даже после установки защиты. Для этого:

· Выделить ячейку B2, в контекстном меню или на вкладке Главная в группе Ячейки/Формат выполнить команду Формат ячеек,

· В окне команды Формат ячеек перейти на вкладку Защита, снять флажок параметра Защищаемая ячейка и нажать ОК.

36. Скрыть текст формул на листе «Отчет». Для этого:

· Выделить диапазон ячеек B3:B7, содержащие формулы, выполнить командуФормат ячеек,

· В окне команды Формат ячеек перейти на вкладку Защита, установить флажок параметра Скрыть формулы и нажать кнопку ОК. (Скрытие формул начнет действовать только после установки защиты листа).

37. На листе «Отчет» скрыть строки, содержащие данные о суммах премий и общих начислений: выделить любые две ячейки в строках с номерами 6 и 7. На вкладке Главная в группе Ячейки/Формат выбрать группу Видимость/Скрыть или отобразить и выполнить команду Скрыть строки.

38. Установить защиту листа «Отчет» с паролем high, оставив в окне команды лишь флажки, установленные по умолчанию (выделение заблокированных и незаблоки­рованных ячеек).

39. Проверить возможность внесения изменений в ячейку B2 и отсутствие такой возможности для остальных ячеек листа «Отчет». Проверить невозможность отображения скрытых строк на листе «Отчет» (команда Отобразить строки на вкладке Главная в группе Ячейки/Формат/Видимость/ Скрыть или отобразитьне доступна).

40. Сохранить изменения в книге «Отчет», закрыть книгу.

Задание 4. Создать открытую копию книги, для которой снять установленную ранее защиту от просмотра и редактирования книги и всех ее элементов.