Iii. работа с функциями базы данных

Лабораторная работа №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, пол женский;

· количество телефонов у референтов;

· должность сотрудника, имеющего минимальную заработную плату;

· телефон самого пожилого сотрудника;

· должность самого молодого сотрудника.