Технологическая карта №1 База данных Сотрудники. Работа со списком в MS Excel

Технологическая карта №1

База данных Сотрудники. Работа со списком в MS Excel

Задание 1. Структура базы данных

Создайте или получите у преподавателя книгу Excel, состоящую из двух рабочих листов:

·        лист Сотрудники (рис. 1) – таблица с полями Фамилия, Имя, Отчество, Табельный_номер, Дата_приема_на_работу, Код, Пол, Дата_рождения - содержит информацию для 100 сотрудников некоторой условной фирмы;

·        лист Должность (рис. 2) –таблица с полями Код, Должность, Коэффициент, Отдел.

Рисунок 1 - Фрагмент листа Сотрудники

Рисунок 2 - Лист Должность

Задание 2. Редактирование базы данных

1. На листе Должность после столбца Коэффициент добавьте пустой столбец с заголовком Оклад. Подсчитайте оклад, умножив коэффициент на МРОТ (на рис. 2 данные на 1.01.2009).

Комментарии:

·        поставьте курсор на поле Отдел, на ленте Главная в группе Ячейки команда Вставить: Вставить столбцы на лист;

·        в верхней ячейке нового столбца наберите Оклад;

·        во второй строке столбца Оклад (D2) запишите формулу: =C2*$C$19

·        при необходимости измените формат ячейки на Денежный (на ленте Главная в группе Число список типов данных).

·        скопируйте формулу во все ячейки столбца Оклад.

2. На листе Сотрудники после столбца Дата_приема добавьте пустой столбец с заголовком Стаж. Подсчитайте стаж работы сотрудника на данном предприятии.

Комментарии:

·        в верхней ячейке столбца Стаж (F2) запишите формулу:

=ДОЛЯГОДА(СЕГОДНЯ();E2;3)

·        при необходимости измените формат ячейки на Числовой (на ленте Главная в группе Число список типов данных);

·        скопируйте формулу во все ячейки столбца Стаж.

3. На листе Сотрудники после столбца Код добавьте три пустых столбца с заголовками Должность, Оклад, Отдел. Заполните новые столбцы данными, расположенными на листе Должность (рис. 2), где каждому коду сопоставлена определенная должность, оклад и отдел.

Комментарии:

·        в верхней ячейке столбца Должность на листе Сотрудники (Н2) запишите формулу:

=ВПР(G2;Должность!$A$1:$E$17;2)

·        аналогично запишите формулы для определения оклада и отдела;

·        скопируйте все три формулы в соответствующие столбы;

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

4. На листе Сотрудники после столбца Дата_рождения добавьте столбец Возраст, в котором подсчитайте примерный возраст каждого сотрудника (функции ОКРУГЛВНИЗ, ДОЛЯГОДА (базис 3), СЕГОДНЯ).

5. Готовую таблицу скопируйте в отчет.

Задание 3. Структура базы данных

Определите формат полей базы данных (табл. 1), задав ограничения на ввод данных (на ленте Данные в группе Работа с данными выбрать команду Проверка данных).

Таблица 1 - Структура списка Сотрудники

Задание 4. Форматирование списка

Оформите список согласно требованиям:

1. Осуществите подгонку ширины столбцов.

2. Отформатируйте заголовки полей по центру, используя полужирное начертание.

3. Отформатируйте по центру значения всех столбцов, кроме столбцов Фамилия, Имя, Отчество, Должность.

4. Добавьте границы для всей таблицы.

5. Закрепите «шапку» (строка 1) и «боковик» (столбец А) таблицы (на ленте Вид в группе Окно в списке Закрепить области последовательно выберите команды Закрепить верхнюю строку, Закрепить первый столбец).

6. Выделите всю таблицу и присвойте ей имя База_данных (на ленте Формулы в группе Определенные имена или в контекстном меню выберите команду Присвоить имя).

Задание 5. Сортировка

1. Отсортируйте список по полю Табельный_номер (на ленте Данные в группе Сортировка и фильтр команда Сортировка).

2. Отсортируйте список по полю Отдел так, чтобы отделы шли в порядке АОП, ФЭО, ИВЦ, ИТО, МПО. Для этого создайте свой список сортировки.

3. Отсортируйте список по двум ключам: по полу - сначала женщины, потом мужчины, а затем по стажу.

Комментарии: чтобы отсортировать список по нескольким критерия, надо задать первый уровень сортировки, а потом в окне Сортировка, выбрать Добавить уровень и задать критерий.

4. Отсортируйте список по ключам: по отделам, внутри отделов – сначала мужчины, потом женщины и, наконец, по фамилиям, именам и отчествам.

5. Скопируйте полученную таблицу в отчет работы.

Задание 6. Формирование новой таблицы

Скопируйте данные из столбцов Фамилия, Имя, Отчество, Оклад на отдельный лист, дав ему название Ведомость. Поместите полученную таблицу в отчет работы.

Примечание. Выделить несмежные диапазоны можно с помощью клавиши CTRL.

Задание 7. Трехмерные ссылки

На отдельном листе День_рождения составьте график празднования дней рождения сотрудников (рис. 3), сформировав его на основе таблицы Сотрудники, используя только формулы.

Комментарии:

·        добавьте в книгу новый лист и переименуйте его в День_рождения;

·        обратите внимание, что столбец А листа День_рождения состоит из значений трех столбцов исходной таблицы, разделенных пробелом, поэтому для формирования столбца используйте функцию СЦЕПИТЬ:

=СЦЕПИТЬ(Сотрудники!A1;"";Сотрудники!B1;"";Сотрудники!C1)

Рисунок 3 - Фрагмент листа День_рождения

·        добавьте формулы в столбцы В и С, а затем скопируйте их на нужное количество строк.

·        отсортируйте список по отделам, внутри отделов – по дате рождения;

·        поместите полученную таблицу в отчет.

·