Практическая работа № 9. Изучение средств фильтрации данных.

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

1. Не все реальные задачи сводятся к электронным таблицам. Иногда не нужно выполнять никаких вычислений, а требуется вести небольшие базы данных, например, телефонных номеров, клиентов, заказов и т.п. Созданы специальные средства, которые позволяют успешно решать такие задачи. К их числу можно отнести СУБД MsAccess, MsFoxPro и др. Однако зачастую привлечение СУБД не является оправданным. В этом случае можно воспользоваться списками MsExcel (иногда говорят БД MsExcel). Для того, чтобы таблица могла быть представлена в виде списка необходимо чтобы:

• верхние строки таблицы содержали заголовки столбцов, которые было бы легко превратить в имена полей;

• каждый столбец содержал данные одного типа (поле записи);

• каждая строка представляла собой запись;

• не было пустых строк и столбцов (пустая строка/столбец отделяет список от остальной таблицы).

2. К средствам работы со списками относятся: Формы, Сортировка, Итоги и Фильтры.

3. Откройте рабочую книгу Задача 8.xls (структуризация таблиц), снимите защиту, если она установлена. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников. Выполните сортировку списка по отделам, а внутри отделов по фамилиям. Данная таблица полностью соответствует описанию списка Excel.

4. Сохраните рабочую книгу под именем Задача 12.xls.

5. Рассмотрите способы фильтрации данных. Фильтрация таблицы выводит на экран только те строки, которые удовлетворяют заданным условиям. Выделите «шапку» Вашей таблицы. Выполните команду вкладка Данные / раздел Сортировка и фильтр / кнопка Фильтр. В заголовке каждого столбца таблицы появится кнопка ▼ - кнопка раскрывающегося списка. Нажимая на эту кнопку, можно задать условие отбора записей в данном столбце. Если нужно задать сложное условие по столбцу, то в предложенном списке следует выбрать позицию Текстовый фильтр или Числовой фильт (в зависимости от типа данных в столбце) / Настраиваемый фильтр…. Затем в окне Пользовательский автофильтр (рис.39) можно сформулировать достаточно сложное условие отбора записей. В условиях отбора можно использовать шаблоны символов (* - любое количество любых символов, ? – один любой символ) и знаки отношений (=, <, >, <=, >=, <>). Условия, заданные в нескольких столбцах, будут объединяться по И.

6. 

   Рис. 33

   

   Рис. 40

   Найдите всех сотрудников транспортного отдела, которым выплачено больше 3000 (фильтрация в двух столбцах поочередно).

7. Найдите пятерых сотрудников с наибольшей зарплатой. Если нужно выбрать несколько наибольших/ наименьших значений в столбце, то в предложенном списке следует выбрать позицию Первые 10… и окне Наложение условия по списку выбрать необходимые условия.

8. Получите сведения о сотрудниках, фамилии которых начинаются с «И» или с «С».

9. Самостоятельно сформулируйте несколько запросов.

10. Отмените фильтр, выполнив команду вкладка Данные / раздел Сортировка и фильтр / кнопка Фильтр.

11. Допустим, что с рассматриваемой таблицей работает несколько пользователей и каждый имеет возможность заполнять ее данными, кроме того, каждый обладает своими особенностями внесения данных. Например, столбец Отдел может быть заполнен следующими вариантами слова «Бухгалтерия»: Бухгалтер, Бух, бухгалтерия, бухучет и т.д. В данной ситуации довольно сложно пользоваться автофильтром, трудоемкой будет работа по приведению всех схожих вариантов к одному виду Бухгалтерия. В таких случаях можно воспользоваться встроенной функцией MsExcel - Расширенный фильтр.

12. Расширенный фильтр. При работе расширенный фильтр опирается на три области: исходный диапазон (исходная таблица данных от названия первого столбца до последней ячейки последнего столбца), диапазон условий (создается из заголовков исходной таблицы и строк, в которых указаны условия поиска) и область размещения отобранных записей (может совпадать с областью данных). Все указанные области могут располагаться как на одном листе, так и на разных. Условия отбора, записанные в одной строке, объединяются по И, а в разных – по ИЛИ.

13. Таблица 5
    Отдел	Фамилия	Всего	К выдаче
    Бухгалтерия			>3000

    Создайте таблицу условий поиска: скопируйте на свободное пространство текущего листа имена полей нашего списка («шапку» таблицы). Оставьте в новой таблице только те ячейки, которые показаны в Таблице 5, а остальные удалите (команда вкладка Главная / раздел Ячейки / список / пункт Ячейки, со сдвигом влево). В следующей строке запишите условие отбора сотрудников Бухгалтерии, которым выплачено больше 3000 рублей.

14. Проведите фильтрацию данных на текущем листе: установите курсор на какую-либо ячейку Вашего списка (исходная таблица). Выполните команду вкладка Данные / раздел Сортировка и фильтр / кнопка …. В окне Расширенный фильтр выберите фильтрацию на месте, в первом поле укажите диапазон ячеек, содержащий список (исходная таблица данных), во втором поле - диапазон ячеек, содержащий условие фильтра (таблицу условий поиска), нажмите кнопку ОК. Проанализируйте полученный результат. Отмените фильтр (вкладка Данные / раздел Сортировка и фильтр / кнопка ).

15. Копирования отфильтрованных данных. Пусть отфильтрованные записи нужно скопировать на другой лист текущей рабочей книги. На текущем листе создайте таблицу условий поиска для отбора записей о сотрудниках, работающих в бухгалтерии ИЛИ о тех, которым выплачено больше 3000 рублей. Перейдите на новый лист и выполните вкладка Данные / раздел Сортировка и фильтр / кнопка . В окне Расширенный фильтр выберите режим копирования в другое место, в первом поле укажите диапазон ячеек, содержащий список (исходная таблица данных на листе), во втором поле - диапазон ячеек, содержащий условие фильтра (таблицу условий поиска, которая также находится на первом рабочем листе), в третьем поле укажите место размещения результата фильтрации – адрес левой верхней ячейки нового рабочего листа (на который осуществляется копирование) и нажмите кнопку ОК. Проанализируйте полученный результат.

16. Самостоятельно сформулируйте и выполните несколько запросов.

17. Сохраните рабочую книгу.

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

1. Как задать автофильтр?

2. Можно ли пользоваться ? (знаком вопроса) и * (звездочкой) для задания шаблона поиска записи в БД?

3. Как создать собственный фильтр для фильтрации данных?

4. Как снова отобразить все записи БД после фильтрации?

5. При каких условиях удобнее использовать Расширенный фильтр?

6. На какие три области опирается Расширенный фильтр?

7. Как задается условие ИЛИ в автофильтре, расширенном фильтре?

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

5