Расширенный фильтр

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

Расширенный фильтр позволяет.

1. Определить более сложный критерий фильтрации.

2. Помещать результат отбора данных на новый рабочий лист.

3. Устанавливать вычисляемый критерий отбора.

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

Диапазон критериев – область рабочего листа, в которой формируется условие (условия) отбора. Диапазон критериев должен состоять, по крайней мере, из двух строк, первая из которых содержит все или некоторые названия полей списка.

Удобнее всего отвести для диапазона критериев область над списком. Названия полей, не используемых при фильтрации, можно не помещать в диапазон критериев. Но если в дальнейшем, в зависимости от обстоятельств, может понадобиться и другая информация из списка, то можно скопировать строку, содержащую названия полей списка, целиком.

Условия отбора следует вносить в пустые ячейки диапазона критериев. Условия, расположенные в ячейках одной строки, соединяются оператором И. Условия, расположенные в разных строках, соединяются оператором ИЛИ[7].

Критерии бывают двух типов.

1. Критерии вычисления – это критерии, которые являются результатом вычисления формулы. Например, диапазон критериев =F7>СРЗНАЧ($F$7:$F$21)выводит на экран строки, имеющие в столбце F значения намного больше, чем среднее значение величин в ячейках F7:F21. Формула должна возвращать логическое значение ЛОЖЬ или ИСТИНА.

2. Критерии сравнения – это набор условий для поиска, используемый для извлечения данных при запросах. Критерий сравнения может быть последовательностью символов (константой) или выражением (например, Цена > 700).

Область ячеек, содержащих критерии, должна отделяться от списка, по крайней мере, одной пустой строкой.

Команда Данные – Фильтр – Расширенный фильтр позволяет фильтровать данные с использованием диапазона критериев для вывода только тех записей, которые удовлетворяют определенным критериям. Значения переключателей и полей диалогового окна Расширенный фильтр (рис. 4.7).

Рис. 4.7. Диалоговое окно Расширенный фильтр

Фильтровать список на месте – переключатель, скрывающий строки, которые не удовлетворяют указанному критерию.

Скопировать результат в другое место – копирует отфильтрованные данные на другой рабочий лист или в другое место на этом же рабочем листе.

Исходный диапазон – поле, определяющее диапазон, который содержит список, подлежащий фильтрации.

Диапазон условий – поле, определяющее диапазон ячеек на рабочем листе, который содержит необходимые условия.

Поместить результат в диапазон – поле, определяющее диапазон ячеек, в который копируются строки, удовлетворяющие определенным условиям; это поле активно только в том случае, если выбран переключатель Скопировать результат в другое место.

Только уникальные записи – переключатель, который выводит только строки, удовлетворяющие критерию и не содержащие неповторяющихся элементов. Если диапазон критериев не определен, то в этом случае все строки списка, содержащие дубликаты, будут скрыты.

Таким образом, поиск с помощью расширенного фильтра предполагает следующее.

1. Подготовить диапазон критериев для расширенного фильтра, для этого:

а) первая строка должна содержать заголовки полей, по которым будет производиться отбор (точное соответствие заголовкам полей списка);

б) условия критерия записываются в пустые строки под подготовительной строкой заголовка.

2. Поместить указатель в список (или выделить весь список).

3. Выполнить команду Данные – Фильтр – Расширенный фильтр.

4. В диалоговом окне Расширенный фильтрзадать необходимые параметры.

5. Нажать на кнопку ОК.

4.3. Анализ данных [8]

ПрограммаMS Excel предоставляет широкие возможности для проведения анализа данных, находящихся в списке.

К средствам анализа относятся.

· Обработка списка с помощью различных формул и функций.

· Построение диаграмм и использование карт MS Excel.

· Проверка данных рабочих листов и рабочих книг на наличие ошибок.

· Структуризация рабочих листов.

· Автоматическое подведение итогов.

· Консолидация данных.

· Сводные таблицы.

· Специальные средства анализа выборочных записей и данных – подбор параметра, поиск решения и др.

В рамках данной главы рассмотрим создание сводных таблиц и приёмы работы с ними.

Сводная таблица – таблица итоговых данных, извлеченных или рассчитанных на основе информации, содержащейся в списках.

Сводные таблицы могут использоваться:

1) для обобщения большого количества однотипных данных;

2) реорганизации данных (с помощью перетаскивания);

3) отбора и группировки данных;

4) построения диаграмм.

Методика создания сводной таблицы [8]:

1. Выбрать место для сводной таблицы, т. е. установить указатель в необходимое место на рабочем листе.

2. Выполнить команду Данные – Сводная таблица.

3. В диалоговом окне Мастер сводных таблиц и диаграмм на первом шаге определить источник данных и вид создаваемого отчета (рис. 4.8) и нажать кнопку Далее.

Рис. 4.8. Диалоговое окно Мастер сводных таблиц и диаграмм

4. На втором шаге определить исходный диапазон (рис. 4.9) и нажать кнопку Далее.

Рис. 4.9. Окно определения исходного диапазона

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

6. В диалоговом окне Мастер сводных таблиц – Макет поля списка представлены в виде кнопок (рис. 4.11), которые нужно просто «перетащить» в соответствующие области макета таблицы и нажать кнопку ОК.

Страница – значения поля, помещенного в эту область, используется в качестве заголовков страниц сводной таблицы.

Строка – значение поля, помещенного в эту область, используется в качестве заголовков строк сводной таблицы.

Данные – поле (поля), для которого подводится итог в сводной таблице.

Рис. 4.10. Окно размещения сводной таблицы

Столбец – значения поля, помещенного в эту область, используется в качестве заголовков столбцов сводной таблицы.

По умолчанию при перетаскивании кнопки в область Данные будет применена функция Сумм, если поле содержит числовые значения, и функция Счет – в случае нечисловых значений. Если дважды щелкнуть по полю в области данных, функцию можно изменить [7].

Рис. 4.11 Диалоговое окно Мастер сводных таблиц – макет

7. После проведения всех подготовительных операций необходимо нажать кнопку ОК и затем Готово (рис. 4.10).

Лабораторная работа 4.1
Создание и работа со списками.
Сортировка данных [7, 8]

Цель:научиться создавать таблицу формата список и сортировать данные.