Лабораторная работа №10
Создание запросов. Поиск и фильтрация данных
Цель работы
Освоить возможности фильтрации табличных данных и работы с функциями базы данных в организованных списках.
Задание на выполнение и методические указания
I. Фильтрация записей с помощью авто-фильтра
1. Для выполнения задания скопируйте таблицу «Ведомость» из лабораторной работы 1 в новую книгу.
2. Активируйте Авто-фильтр из меню Данные - Фильтр. Выбирая соответствующие должности, заполните таблицу записями о новых сотрудниках. Штат должен содержать троих менеджеров, четырех инженеров, двух секретарей, двух референтов, одного водителя, директора и заместителя директора
(с одинаковыми окладами для одинаковых должностей).
3. Применяя Авто-фильтр и функцию Промежуточные итоги, которая вызывается при помощи кнопки 
на панели инструментов Стандартная, определите:
· сколько в фирме женщин и каков их средний заработок;
· список троих самых пожилых;
· какой максимальный оклад имеет сотрудница, не получающая надбавку;
· кто из мужчин живет в центральном районе (телефоны начинаются от 310... до 315...);
· каков суммарный заработок у менеджеров и инженеров;
· сколько в фирме работает Ивановых, и каков их суммарный оклад;
· сколько сотрудников получают больше 9000 руб. или меньше 5000 руб., кто из них не получает надбавки;
· список трех самых молодых инженеров.
II. Расширенная Фильтрация
1. Вызовите справочную систему Excel, познакомьтесь со справочным материалом по этой теме, рассмотрите примеры.
2. Для выполнения задания скопируйте таблицу с листа «Табель» лабораторной работы 1, расположите ее в начале чистого листа, добавьте в таблицу столбец «Средний балл».
|
|
3. Определите область критериев справа от таблицы.
4. Извлеките данные о студентах, имеющих:
· средний балл меньше 4;
· средний балл больше, чем 3,5 и оценку по математике больше 3;
· 4 и 5 по всем предметам;
· средний балл больше, чем 3,5, но меньше 4 (реализацию логической функции И для данных одного столбца осуществляйте повторным размещением в области критериев заголовка данного столбца);
· средний балл не меньше, чем 4,5 или меньше 4, но по биологии - 5
5. Определив область для извлечения данных под таблицей, найдите студентов, имеющих:
· тройки по математике;
· тройки по математике, но четверки по физике;
· двойки по математике, но пять по физике и средний балл больше 3,5;
· тройки по математике или тройки по физике;
· двойку по любому предмету (хотя бы одну).
6. Извлеките только фамилии студентов:
· не имеющих двоек;
· не имеющих двоек и средний балл не меньше 4;
· имеющих хотя бы одну двойку.
Для выполнения этого задания в ячейку, с которой будет начинаться область для извлечения данных, введите название заголовка столбца «Фамилия».
III. Работа с функциями Базы данных
1. Для выполнения задания скопируйте таблицу со списком сотрудников из лабораторной работы 1 в новую книгу.
2. Добавьте в этой таблице еще один столбец с названием «Заработная плата», заполнив его с помощью функции 
, и отформатируйте данный столбец как денежный.
3. Добавьте критерий в данную таблицу.
4. С помощью соответствующей функции Работы с базой данных определите и выведите в свободную ячейку листа «Отчет» следующий параметр:
|
|
· суммарную заработную плату секретарей;
· фамилию сотрудника, получающего максимальную заработную плату;
· фамилию сотрудника год рождения 1936, пол женский;
· количество телефонов у референтов;
· должность сотрудника, имеющего минимальную заработную плату;
· телефон самого пожилого сотрудника;
· должность самого молодого сотрудника.