 |
Создание таблици ограничений на ввод данных
|
|
|
|
Задание
Создать книгу «Организация», содержащую данные о начислениях выплат сотрудникам и установить ее защиту.
Технология выполнения в среде MSExcel 2010
Задание 1. Создать книгуMSExcel, содержащую данные о сотрудниках организации.
Создание таблици ограничений на ввод данных
1. Создать на диске рабочую папку, например, D:\Petrov.
2. Открыть приложение MSExcel. Создать новую книгу, сохранить ее в рабочую папку с именем Организация.
3. Переименовать Лист1 в «Отчет», Лист2 –в «Ведомость», Лист3– в «Справочники». Для этого щелкнуть правой кнопкой мыши по ярлычку листа и выбрать команду Переименовать из контекстного меню.
4. Перейти на лист «Справочники». Создать таблицу «Данные о подразделениях»: выделить диапазон ячеек A1:C5, выделить границы диапазона (вкладка Главная, группа Шрифт/Границы 
). Объединить ячейки A1:C1 (выделить диапазон A1:C1, выполнить команду Объединить и поместить в центре 
на вкладке Главная, группа Выравнивание). Ввести в объединенную ячейку текст заголовка таблицы: «Данные о подразделениях». В ячейки A2, B2 и С2 соответственно ввести текст заголовков столбцов таблицы: «Код подразделения», «Наименование», «Руководитель».
5. Задать ограничения на ввод данных в столбец «Код подразделения»:
· Выделить интервал ячеек A3:A5, перейти на вкладку Данные и выполнить команду Проверкаданных 
(в группе Работа с данными),
· В окне команды Проверка вводимых значений на вкладке Параметры выбрать Тип данных: Список, в строку Источник набрать: 1;2;3 (через точку с запятой) – рис.7.
· Для выхода из окна команды нажать ОК. После этого в таблицу можно заносить данные, выбирая их из списка.
Рис.7. Задание ограничений на ввод данных в столбец «Код подразделения»
|
|
|
6. Заполнить данными таблицу «Данные о подразделениях» (пример – на рис.8).
7. В диапазоне ячеек A7:D21 создать таблицу «Данные о сотрудниках»: ячейки A7:D7 – заголовок таблицы (объединить ячейки), ячейки A8:D8 – заголовки столбцов («Фамилия», «Дата поступления», «Разряд», «Подразделение»), ячейки A9:D21 – данные.
Рис.8. Пример данных на листе «Справочники»
8. Задать ограничения на ввод данных в столбец «Разряд»:
· Выделить интервал ячеек С9:С21, на вкладке Данные в группе Работа с данными выполнить команду Проверкаданных ,
· В окне команды Проверка вводимых значений на вкладке Параметры выбрать Тип данных: Целое число, Значение: между, в строке Минимум набрать: 10, в строке Максимум – 17 – рис.9.
· На вкладке Сообщение об ошибке окна команды в строке Заголовок: ввести текст Несуществующий разряд, а в строке Сообщение: Разряд от 10 до 17.
· Для выхода из окна команды нажать ОК.
Рис.9. Задание ограничений на ввод данных в столбец «Разряд»
9. Задать ограничения на ввод данных в столбец «Подразделение»:
· Выделить интервал ячеек D9:D21, выполнить команду Проверкаданных,
· В окне команды Проверка вводимых значений на вкладке Параметры выбрать Тип данных: Список, перейти в строку Источник, а затем, не выходя из окна команды, выделить на листе «Справочники» диапазон ячеек A3:A5 таблицы «Данные о подразделениях», содержащий коды подразделений – ссылка на диапазон будет занесена в строку Источник – рис.10.
· Для выхода из окна команды нажать ОК.
10. Заполнить данными таблицу «Данные о сотрудниках». Сохранить изменения. Таблицы на листе «Справочники» с данными представлены на рис.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), Интервальный_просмотр: ложь. Вид функции ВПР в конструкторе приведен на рис.11. Нажать OК.
· Результирующий вид функции ВПР в ячейке B3:
=ВПР(A3;Справочники!$A$9:$D$21;4;ЛОЖЬ)
Рис.11. Задание параметров функции ВПР в конструкторе
· Скопировать ячейку B3 на весь диапазон данных столбца «Код подразделения» таблицы «Сводная ведомость» (B4:B15) – для этого подвести указатель мыши к правому нижнему углу ячейки B3 так, чтобы указатель принял вид черного крестика, затем нажать левую кнопку мыши и не отпуская ее растащить ячейку на весь диапазон B4:B15.
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) приведен на рис.12.
Рис.12. Пример данных на листе «Ведомость»
|
|
|
18. Перейти на лист«Отчет». В диапазоне ячеек A1:B7 создать таблицу «Сводные данные по подразделениям». В ячейки A2:A7 занести текст «Подразделение», «Наименование», «Руководитель», «Общая численность сотрудников», «Сумма премий», «Всего начислено».
19. Ячейку В2 для выбора кода подразделения выделить цветом (вкладка Главная, группа Шрифт, цвет заливки 
).
20. Задать ограничения на ввод данных в ячейку B2 – значения выбираются из списка 1;2;3 (аналогично п.5, рис.7).
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) приведен на рис.13.
26. Сохранить изменения в книге Организация
Рис.13. Пример данных на листе «Отчет»
Задание 3. Установить защиту книги таким образом, что:
· Несанкционированные пользователи не могут просматривать данные книги;
· Пользователи, знающие пароль нижнего уровня, могут просматривать только статистические (итоговые) данные о сотрудниках фирмы, исключая суммы начислений;
· Сотрудники, знающие пароль нижнего и среднего уровней, могут просматривать итоговые и частные данные о сотрудниках, за исключением сумм начислений;
· Сотрудники, знающие пароль верхнего уровня и остальные пароли, могут как просматривать, так и изменять все данные.
|
|
|
