Задание 1. Структура базы данных
Скопируйте в свою папку книгу Excel БАЗА ДАННЫХ.xlsx, состоящую из двух рабочих листов:
· лист Сотрудники (рис. 1) – таблица с полями Фамилия, Имя, Отчество, Табельный_номер, Дата_приема_на_работу, Код, Пол, Дата _ рождения - содержит информацию для 100 сотрудников некоторой условной фирмы.
· лист Должность (рис. 2) –таблица с полями Код, Должность, Коэффициент, Отдел.
Рис. 1. Фрагмент листа Сотрудники
Рис. 2. Лист Должность
Задание 2. Редактирование базы данных
1. На листе Должность после столбца Коэффициент добавьте пустой столбец с заголовком Оклад. Подсчитайте оклад, умножив коэффициент на МРОТ (на рис. 2 данные на 1.01.2009).
2. На листе Сотрудники после столбца Дата_приема добавьте пустой столбец с заголовком Стаж. Подсчитайте стаж работы сотрудника на данном предприятии.
Комментарии:
· в верхней ячейке столбца Стаж (F2) запишите формулу: = ДОЛЯГОДА (СЕГОДНЯ(); E2; 3);
· при необходимости измените формат ячейки на Числовой (на ленте Главная в группе Число список типов данных);
· скопируйте формулу во все ячейки столбца Стаж.
3. На листе Сотрудники после столбца Код добавьте три пустых столбца с заголовками Должность, Оклад, Отдел. Заполните новые столбцы данными, расположенными на листе Должность (рис. 2), где каждому коду сопоставлена определенная должность, оклад и отдел.
Комментарии:
· в верхней ячейке столбца Должность на листе Сотрудники (Н2) запишите формулу: ВПР (G2; Должность!$ A $ 1:$ E $ 17; 2), где G2 – код;!$ A $ 1:$ E $ 17 – диапазон, из которого нужно взять данные из 2- го столбца.
· аналогично запишите формулы для определения оклада и отдела;
· скопируйте все три формулы в соответствующие столбы;
· проконтролируйте, чтобы у каждого отдела был заведующий отделом и только один.
4. На листе Сотрудники после столбца Дата_рождения добавьте столбец Возраст, в котором подсчитайте примерный возраст каждого сотрудника (функции ОКРУГЛВНИЗ, ДОЛЯГОДА(базис3), СЕГОДНЯ).
5. Готовую таблицу скопируйте в отчет (документ MS Word).
Задание 3. Структура базы данных
Определите формат полей базы данных (табл. 1), задав ограничения на ввод данных (на ленте Данные в группе Работа с данными выбрать команду Проверка данных – теория, п.2.3). Таблица 1.Структура списка Сотрудники
Задание 4. Форматирование списка
Оформите список согласно требованиям:
1. Осуществите подгонку ширины столбцов.
2. Отформатируйте заголовки полей по центру, используя полужирное начертание.
3. Отформатируйте по центру значения всех столбцов, кроме столбцов Фамилия, Имя, Отчество, Должность.
4. Добавьте границы для всей таблице.
5. Закрепите «шапку» (строка 1) и «боковик» (столбец А) таблицы (на ленте Вид в группе Окно в списке Закрепить области последовательно выберите команды Закрепить верхнюю строку, Закрепить первый столбец).
6. Выделите всю таблицу и присвойте ей имя База_данных (на ленте Формулы в группе Определенные имена или в контекстном меню выберите команду Присвоить имя).
Задание 5. Сортировка
1. Отсортируйте список по полю Табельный_номер (на ленте Данные в группе Сортировка и фильтр команда Сортировка).
2. Отсортируйте список по полю Отдел так, чтобы отделы шли в порядке АОП, ФЭО, ИВЦ, ИТО, МПО. Для этого создайте свой список сортировки (теория, п. 2.4).
3. Отсортируйте список по двум ключам: по полу - сначала женщины, потом мужчины, а затем по стажу.
Комментарии: чтобы отсортировать список по нескольким критерия, надо задать первый уровень сортировки, а потом в окне Сортировка, выбрать Добавить уровень и задать критерий.
4. Отсортируйте список по ключам: по отделам, внутри отделов – по полу сначала мужчины, потом женщины и, наконец, по фамилиям, именам и отчествам.
5. Скопируйте полученную таблицу в отчет работы.
Задание 6. Формирование новой таблицы
Скопируйте данные из столбцов Фамилия, Имя, Отчество, Оклад на отдельный лист, дав ему название Ведомость. Поместите полученную таблицу в отчет работы.
Примечание. Выделить несмежные диапазоны можно с помощью клавиши CTRL.