Фильтрация списков

Фильтрация  списков

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

В табличном процессоре есть два  инструмента для фильтрации списков  – Фильтр, предназначенный для фильтрации списков по простым условиям, и Расширенный фильтр, выполняющий фильтрацию для усложненных условий.

Для выполнения заданий, приведенных  ниже, создайте таблицу в Microsoft Excel по образцу (не менее 20 записей). Данные произвольны.

 

Таблица 1. Операции банка со счетами клиентов

№ операции	№ счета	Фамилия клиента	Дата операции	Приход	Расход
1	1	Петров	03.01.02	1000
2	2	Разумовская	03.01.02	2000
3	1	Петров	03.01.02		500
4	2	Разумовская	03.01.02		2000
5	3	Наумов	03.01.02	1500
6	4	Крутиков	03.01.02	1300
7	5	Павлова	04.01.02	1050

 

 

Задание 1. Требуется определить, какова максимальная сумма прихода по счету 1.

Решение

Выполните команду меню Данные/Фильтр.

Раскройте список условий в столбце с названием № счета и выберете в нем значение 1. Для этого снимите галочку в пункте «Выделить все» и поставьте в пункте «1». Нажмите ОК.

Раскройте список условий в столбце с названием Приход и выберете в нем элемент Числовые фильтры/Первые 10.

В раскрывшемся окне диалога Наложение условия по списку в группе Показать установите значения наибольших и 1.

Нажмите на кнопку ОК.

Сохраните результат фильтрации для  отчета.

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

 

Задание 2. Требуется определить, сколько было приходных операций с суммами величиной от 1000 до 2500 руб. включительно по счету 1.

Включите инструмент Фильтр (Данные/ Сортировка и фильтр/ Фильтр).

Нажмите левой кнопкой мыши на стрелке в столбце с названием № счета и в раскрывшемся списке выберете элемент со значением 1.

Раскройте список в столбце с названием Приход и выберете в нем элемент Числовые фильтры/Настраиваемый фильтр – откроется диалоговое окно Пользовательский автофильтр.

В диалоговом окне Пользовательский автофильтр в верхнем левом списке выберете элемент Больше или равно, а в поле правого списка введите число 1000, в нижнем левом списке выберете элемент Меньше или равно и в поле правого списка введите число 2500, установите переключатель с надписью И.

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

Сохраните результат для отчета

 

При выборе операций отношения И (ИЛИ) следует руководствоваться следующим:

– если из всего множества записей  в исходном списке нужно отобрать подмножество, значения которого определены правой и левой границами (a, b) (замкнутое подмножество), то следует установить операцию отношения И;

– если из всего множества записей  в исходном списке нужно отобрать подмножества, значения которых ограничены только слева и справа, то следует устанавливать операцию отношения ИЛИ.

В полях диалогового окна Пользовательский автофильтр можно использовать заменители символов – знаки «?» и «*». Заменители символов применяются традиционным образом: знак «?» заменяет один любой символ, а «*» – все следующие символы.

 

Задание 3. Требуется получить сведения по операциям клиентов, фамилии которых начинаются с буквы Р.

Решение

Включите инструмент Пользовательский автофильтр (Данные/Фильтр/Текстовые фильтры/Настраиваемый фильтр) для столбца Фамилия клиента.

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

Сохраните результат фильтрации для отчета.

 

Инструмент Расширенный фильтр (Данные/Сортировка и фильтр/Дополнительно) имеет гораздо больше возможностей. Он позволяет:

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

Формирование диапазона  условий

Условия фильтрации для инструмента Расширенный фильтр формируются в соответствии с правилами:

• в верхней строке диапазона (строке заголовков) записываются названия столбцов списка, на значения которых накладываются условия фильтрации (чтобы избежать ошибок эту операцию лучше выполнять копированием);
• в ячейках строк, расположенных ниже строки заголовков, записываются выражения условий с использованием знаков сравнения (=, <=, >=, <>);
• считается, что выражения  условий, записанные в одной строке, соединены отношением И, а записанные в разных строках – отношением ИЛИ;
• диапазон условий не должен содержать пустых строк или нулевых столбцов.

 

Задание 4. Требуется составить выражение условий для фильтрации исходного списка в соответствии с условиями: дата операции – 10-20 января включительно, номера счетов – 5-14 включительно. Вы можете произвольно изменить параметры фильтрации для того, чтобы освоить рабу инструмента для своей таблицы данных.

Решение

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

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

 

Дата операции	Дата операции	№ счета	№ счета
>=10.01.2002	<=20.01.2002	>=5	<=14

Выполнение фильтрации

Если выражение условий записано, то можно приступить к фильтрации таблицы. Для этого нужно:

– установить курсор в область  исходного списка и выполнить команду меню Данные/Сортировка и фильтр/Дополнительно – откроется окно диалога Расширенный фильтр;

– в группе Обработка диалогового окна установить соответствующий переключатель Фильтровать список на месте или Скопировать результат в другое место (рекомендуется);

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

– в поле Диапазон условий ввести адрес диапазона, содержащего условия;

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

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

– щелкнуть на кнопке ОК, и в указанном для вывода диапазоне будет получен результат фильтрации.

Сохраните результат фильтрации для отчета.

Замечание. В диапазоне условий можно ввести любое количество условий.

 

Задание 5. Требуется отфильтровать записи исходной таблицы, которые отвечают условиям: дата операции – 10-20 января включительно, номера счетов – с 5-го по 14-й, сумма прихода – от 1000 до 2500 включительно, а так же сведения обо всех расходных операциях в период с 24 по 29 января с суммой больше 1100 по клиентам, фамилии которых начинаются с букв «Я» или «Л».

Решение

Сформируйте строку заголовка для условия фильтрации. Из условия задачи следует, что условия должны быть наложены на данные столбцов исходного списка с именами Дата операции, № счета, Приход, Расход и Фамилия клиента, поэтому на свободном участке рабочего листа в ячейки диапазона, например Н3:О3, введите эти названия.

В строках, расположенных ниже строки заголовка, запишите условия, исходя из следующих соображений: условия «дата операции 10-20 января включительно, номера счетов с 5-го по 14-й, сумма прихода от 100 до 2500 включительно» выполняются совместно, к ним применима операция отношения И, поэтому запишите их в одной строке под строкой заголовка; условия «дата операции 10-20 января включительно, номера счетов с 5-го по 14-й, сумма прихода от 1000 до 2500 включительно, фамилии которых начинаются с буквы «Я» связано с первым условием отношением ИЛИ, поэтому в диапазоне условий запишите его в строке ниже; условия «дата операции 10-20 января включительно, номера счетов с 5-го по 14-й, сумма прихода от 1000 до 2500 включительно, фамилии которых начинаются с буквы «Л» связано с предыдущими условиями отношением ИЛИ, поэтому в диапазоне условий запишите его в следующей строке.

 

Дата операции	Дата операции	№ счета	№ счета	Приход	Приход	Расход	Фамилия клиента
>=10.01.02	<=20.01.02	>4	<15	>=1000	<=2500
>=24.01.02	<=29.01.02					>1100	Я*
>=24.01.02	<=29.01.02					>1100	Л*

Создание вычисляемых  условий

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

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

– в формулах адресные ссылки на ячейки, находящиеся мне исходного списка, должны быть абсолютными;

– адресные ссылки на ячейки в списке должны быть относительными.

 

Задание 6. Требуется создать таблицу, содержащую сведения об операциях, отвечающих условиям: сумма операций прихода больше среднего значения всех таких операций.

Решение

В любой свободной ячейке рабочего листа введите формулу для вычисления среднего значения всех операций расхода (например, в ячейке Н1) = СРЗНАЧ(Е2:Е25).

Сформируйте условие для фильтрации: в ячейку, например Н3, введите любое слово или сочетание, которое не совпадает с названиями столбцов исходного списка, например «Выборка», а в ячейку Н4 вычисляемое условие: = E2>$H$1. Следует обратить внимание на то, что адресная ссылка в условии должна быть сделана на ячейку первой строки столбца (в нашем случае E2). Если формула записана правильно, то после окончания ввода в ячейке появится значение ИСТИНА или ЛОЖЬ.

 

 

Вывод в итоговую таблицу  только нужных граф

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

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

– в диалоговом окне Расширенный фильтр в поле Поместить результат в диапазон указать адрес диапазона, в который введены названия столбцов.

 

Задание 7. Требуется вычислить итоговую сумму по всем расходным операциям с номерами счетов 4-15 включительно, проведенные в период с 12 по 14 января.

Решение

Запишите выражение условия в свободном диапазоне рабочего листа (здесь, H3:K4).

№ счета	№ счета	Дата операции	Дата операции
>=4	<=15	>=12.01.02	<24.01.02

 

В свободную ячейку введите формулу: = БДСУММ(A3:F25;F3;H3;K3).

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

 

 

Анализ данных с применением  технологий консолидации

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

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

Консолидация выполняется с  помощью инструмента Данные/Работа с данными/Консолидация.

Для выполнения консолидации необходимо выполнить следующие операции:

• установить курсор в ячейку рабочего листа, в которой будет размещаться левая верхняя ячейка результирующей таблицы;
• включить инструмент Консолидация;
• в диалоговом окне Консолидация, в списке Функция выбрать функцию для выполнения необходимых вычислений;
• в поле Ссылка указать адрес диапазона или всего списка, данные которого предполагается консолидировать. Если есть еще списки для консолидации, щелкнуть на кнопке Добавить, ввести следующий диапазон и т.д.;
• если консолидация выполняется по категории, в группе Использовать в качестве имен установить флажок Подписи верхней строки, при консолидации по расположению – флажок Значения левого столбца;
• для установления связи с исходными списками установить флажок Создавать связи с исходными данными;
• Щелкнуть на кнопке ОК.