Фильтрация данных и работа с функциями базы данных в организованных списках электронной таблицы

Цель работы

Освоить возможности фильтрации табличных данных и работы с функциями базы данных в организованных списках.

Подготовка к работе

По указанной литературе изучить возможности фильтрации табличных данных и работы с функциями базы данных в организованных списках.

Контрольные вопросы

1. Дать понятие фильтра.
2. Функция автофильтра.
3. Функция расширенного фильтра.
4. Понятие поля.
5. Понятие критерия.
6. Понятие базы данных.
7. Работа с функциями базы данных.

Задание на выполнение и методические указания

I. Фильтрация записей с помощью функции автофильтра

1. Для выполнения задания скопировать итоговый список сотрудников из "Сортировка и обработка списков связанных данных в электронной таблице" ( рис. 7.1) в новую книгу.
2. Установить автофильтр (меню "Данные"-"Фильтр") и, выбирая соответствующие должности, принять на работу новых сотрудников. Штат должен содержать троих менеджеров, четырех инженеров, двух секретарей, двух референтов, одного водителя, директора и заместителя директора (с одинаковыми окладами для одинаковых должностей).
3. Применяя автофильтр и функцию "ПРОМЕЖУТОЧНЫЕ. ИТОГИ", которая вызывается при помощи кнопки на панели инструментов "Стандартная", определить:
   • сколько в фирме женщин и каков их средний заработок;
   • список Совета Старейшин (трое самых великовозрастных, независимо от пола);
   • какой максимальный оклад имеет сотрудница, не получающая надбавку;
   • кто из мужчин живет в центральном районе (телефоны начинаются от 3.10... до 3.15...);
   • каков суммарный заработок у менеджеров и инженеров;
   • сколько в фирме работает Ивановых, и каков их суммарный оклад;
   • сколько сотрудников получают больше 9000 руб. или меньше 5000 руб., и кто из них не получает надбавки;
   • составить список трех самых молодых инженеров.

II. Расширенная Фильтрация

1. Вызвать справочную систему Excel, познакомиться со справочным материалом по этой теме, рассмотреть примеры.
2. Для выполнения задания скопировать "Ведомость оценок" ( рис. 7.2 из "Сортировка и обработка списков связанных данных в электронной таблице" ), расположить ее в начале чистого листа, добавить в таблицу столбец "Ср. балл".
3. Определить область критериев справа от таблицы.
4. Извлечь (на месте) данные о студентах, имеющих:
   • средний балл меньше 4;
   • средний балл больше, чем 3,5 и оценку по математике больше 3;
   • 4 и 5 по всем предметам;
   • средний балл больше, чем 3,5, но меньше 4 (реализацию логической функции И для данных одного столбца осуществлять повторным размещением в области критериев заголовка данного столбца.).
   • средний балл не меньше, чем 4,5 или меньше 4, но по биологии - 5
5. Определив область для извлечения данных под таблицей, найти студентов, имеющих:
   • тройки по математике;
   • тройки по математике, но четверки по физике;
   • двойки по математике, но пять по физике и средний балл больше 3,5;
   • тройки по математике или тройки по физике;
   • двойку по любому предмету (хотя бы одну).
6. Извлечь только фамилии студентов:
   • не имеющих двоек;
   • не имеющих двоек и средний балл не меньше 4;
   • имеющих хотя бы одну двойку.

Предъявить результаты преподавателю.

III. Работа с функциями Базы данных

1. Для выполнения задания скопировать итоговый список сотрудников из "Сортировка и обработка списков связанных данных в электронной таблице" ( рис. 7.1) в новую книгу.
2. Добавьте к этому списку еще один столбец с названием "Заработная плата" заполнив его с помощью функции^{1Для программы LibreOffice Calk: =Rand()*(25000-3,5)+3,5)} и отформатируйте данный столбец как денежный.
3. Добавьте критерий в данную таблицу. Рисунок 8.1.
   

   
   Рис. 8.1.
4. С помощью соответствующей функции работы с базой данных определить и вывести в свободную ячейку Листа "Отчет" следующий параметр:
   • Суммарную заработную плату секретарей;
   • Найти Фамилию сотрудника получающего максимальную заработную плату;
   • Найти фамилию сотрудника год рождения 1936, пол женский;
   • Подсчитать количество телефонов у референтов;
   • Найти занимаемую должность сотрудника имеющего минимальную заработную плату;
   • Телефон самого пожилого сотрудника;
   • Занимаемая должность самого молодого сотрудника.