Команда Расширенный фильтр

ОБРАБОТКА СПИСКОВ

ОСНОВНЫЕ ПОНЯТИЯ

 

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

 

Термины "база данных" и "список" фактически являются синонимами. Однако далее будем придерживаться принятого в Microsoft соглашения и называть структурированные таблицы в документах MS Excel списками. Базой данных будем называть файлы таблиц, созданные другими системами обработки данных, такими как Microsoft Access, dBase или FoxPro.

 

Каждая таблица имеет строки, которые также называются записями, и столбцы, которые называются полями. Каждое поле заполнено данными одного типа, поэтому все записи в списке имеют одинаковую структуру.

 

Работа со списками в MS Excel, как и работа с любыми наборами данных, сводится к некоторому набору стандартных операций:

 

1. Поддержание данных в актуальном состоянии 1.1. Добавление новых записей (строк), 1.2. Удаление записей.

1.3. Корректировка (внесение изменений) в уже существующие записи.

 

2. Сортировка записей по какому- либо признаку.

3. Отбор записей по какому-либо критерию.

 

4. Подведение итогов.

 

ФОРМИРОВАНИЕ СПИСКА

 

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

1. Каждый столбец должен содержать однородную информацию.

 

2. Одна или две верхние строки в списке должны содержать метки, описывающие назначение соответствующего столбца.

3. Необходимо избегать пустых строк и столбцов внутри списка.

 

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

 

Правило 2 обеспечивает присвоение имен полям. Эти имена постоянно используются при обработке списков.

 

Правило 3 обеспечивает возможность работы со списком как с единым целым. В идеале на рабочем листе не должно быть ничего, кроме списка. Если это невозможно, то список нужно отделить от других данных по крайней мере одной пустой строкой и пустым столбцом.

 

На рис.6.1 приведен список из 10 столбцов.

 

Новые данные можно добавлять непосредственно в конец списка. Весьма удобным может оказаться построчное редактирование списка с помощью стандартной экранной формы.

Чтобы присвоить имя списку:

 

1. Выделите весь список, включая заголовки столбцов;

2. Напечатайте в области ссылок (перед строкой формул)

 

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

Присвоение списку имени и его автоматическое переопределение по мере расширения списка удобно во многих случаях.

 

Если всему списку было присвоено имя База_данных, то при добавлении строк с помощью стандартной экранной формы (по нажатию кнопки Добавить) они (строки) автоматически включаются в определение имени База_данных.

 

Рис.6.1.

 

СОРТИРОВКА СПИСКОВ

 

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

 

Пример.

 

Продемонстрируем сортировку на списке рис.6.1. Нужно отсортировать список по столбцу Бригада. Для этого:

1) выделите одну ячейку (не интервал) в этом списке;

2) выполните команду Сортировка ( Вкладка Данные, группа

 

Сортировка и фильтр);

3) откроется диалоговое окно Сортировка (рис.6.2);

 

4) выберите поле, по которому нужно сортировать (в этом примере -

 

Бригада).

 

Рекомендуется сразу же проверять результат сортировки. Если результат не устраивает, воспользуйтесь командой Отменить и восстановите предыдущий порядок строк в списке. Для восстановления исходного порядка строк в списке после различных сортировок, необходимо до сортировки создать столбец с номерами строк.В нашем примере этостолбец №пп. Это позволяет восстановить первоначальный порядок строк, отсортировав список по этому столбцу.

 

Пользуясь командой Сортировка..., можно сортировать список по нескольким столбцам. Для этого необходимо использовать вкладку Добавить уровень в диалоговом окне Сортировка (рис.4.2).

 

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

 

 

Рис. 6.2 6.4. АНАЛИЗ СПИСКА С ПОМОЩЬЮ ФИЛЬТРОВ

 

Отфильтровать список − это значит скрыть все строки кроме тех, которые удовлетворяют заданным критериям. Excel предоставляет две команды фильтрации: Автофильтр − для простых критериев, и Расширенный фильтр −для более сложных критериев.

 

КОМАНДА АВТОФИЛЬТР

 

Для применения обычного или автофильтра нужно выполнить следующую последовательность действий:

 

1) выделите какую-либо ячейку в списке;

 

2) нажать кнопку Фильтр в группе Сортировка и фильтр

 

Справа от каждого заголовка столбца появиться кнопка

 

"Раскрывающийся список" (со стрелкой вниз). Если щелкнуть по этой кнопке, то раскроется список уникальных значений данного столбца, которые можно использовать для задания критерия фильтра. На рис 6.3 показан результат фильтрации по столбцу Бригада, выбраны только те строки, где значение Бригада равно 21.

 

Номера строк, не удовлетворяющие критериям команд Фильтр (Автофильтр) и Расширенный фильтр, MS Excel просто скрывает. Номера отфильтрованных строк выводятся контрастным цветом, а в строке состояния появляется сообщение Найдено записей.

 

 

Рис 6.3

 

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

 

Критерий заданного фильтра на рис.6.3 фактически состоит из одного равенства Бригада =21. Можно создать автофильтр с более сложным критерием, состоящим из одного или двух условий с любыми знаками сравнения. Например, следует отфильтровать значения, находящиеся в некотором интервале. Чтобы создать пользовательский автофильтр, раскройте список критериев и выберите элемент Настраиваемый фильтр...

 

(рис.6.4.а).

 

Откроется диалоговое окно Пользовательский автофильтр (рис 6.4.б). В этом диалоговом окне можно создать два условия, соединяемое логическими условиями И или ИЛИ.

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

 

Предположим, что в нашем списке требуется посмотреть только записи со значением столбца Бригада, лежащим в интервале от 9 до 14. Т.е. значение Бригада больше или равно 9 И меньше или равно 14. Эти

 

неравенства заданы в окне рис 4.5.б, условия соединены знаком логической операции И.

 

Р

 

Рис. 6.4.а

 

 

Рис. 6.4.б

 

ПОИСК ТЕКСТОВЫХ ЗНАЧЕНИЙ

 

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

 

В диалоговом окне Пользовательский автофильтр следует создать два условия, соединенные логическим И. Например, если нам нужно выбрать работников с фамилиями начинающимися на С и Т, нужно установить фильтр по столбцу ФИО, задав в диалоговом окне два условия:

 

больше или равно С

 

И

меньше или равно Т

 

Т.к. отдельных букв С и Т в раскрывающемся меню нет, их надо просто ввести в соответствующие поля диалогового окна (рис. 6.4.б).

Та же задача может быть решена другим способом: по полю ФИО в диалоговом окне Пользовательский автофильтр следует создать два условия, соединенные логическим ИЛИ

 

начинается с С

	ИЛИ
	начинается с	Т
При создании критериев	можно использовать два символа шаблона:
звездочка (*) и вопросительный знак (?).
Символ * используется	для	представления	любой
последовательности символов.
Символ? - для представления любого отдельного символа.
В таблице 6.1 даны	примеры использования символов	шаблона,
удовлетворяющих таким критериям (пропускаемых фильтром):
Допустимы любые комбинации символов шаблона.
						Таблица 6.1
	Фильтр	Пропускаемые значения
	=П?пов	Попов, Пупов
	=С??оров	Сидоров, Суворов, Створов
	=Б*в	Беляев, Белов, Бобров

 

УДАЛЕНИЕ АВТОФИЛЬТРОВ

 

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

 

КОМАНДА РАСШИРЕННЫЙ ФИЛЬТР

 

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

 

создавать критерии с условиями по нескольким столбцам, связанным по правилу ИЛИ.

 

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

 

создавать вычисляемые критерии.

 

Кроме того, команда Расширенный фильтр дает возможность автоматически извлекать строки и помещать их копии в другое место текущего рабочего листа.

 

ИНТЕРВАЛ КРИТЕРИЕВ

 

Команда Расширенный фильтр в отличие от команды Автофильтр

 

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

 

Интервал критериев должен состоять не менее чем из двух строк. В первой строке размещаются заголовки столбцов, а во второй и в последующих строках - соответствующие критерии фильтра. Если не используются вычисляемые критерии, то заголовки в интервале критериев должны точно совпадать с заголовками столбцов списка. Для этогоможно выделить заголовки столбцов списка и скопировать их в первую строку интервала с помощью команд Копировать и Вставить из группы

 

Буфер обмена вкладки Главная.

Пример критерия с двумя условиями

Пусть из списка персонала, приведенного на рис 6.1, нужно выделить как работников с тарифом (столбец Тариф) выше 40, так и работников, отработавших более 100 час (столбец Отработано час.).

Выполним следующие действия:

 

Вставим несколько строк для интервала критериев выше списка.

Создадим интервал критериев в первых трех строках, как показано на рис. 6.5.

В диалоговом окне Расширенный фильтр ( кнопка Дополнительно из группы Сортировка и фильтр) введем параметры в соответствии с рис

.6.5

Убедитесь, что установлен переключатель фильтровать список на месте,щелкните ОК

 

Рис.6.5

 

Результат фильтрации приводится на рис.6.6.

 

По команде Расширенный фильтр, также как и по команде Автофильтр скрываются все строки,не удовлетворяющие критериямфильтра, выводятся номера строк в контрастирующем цвете, а в строке состояния выводится сообщение Фильтр: отбор.

 

На рис.6.6 в ячейках А1:В3 критерии записаны в виде обыкновенных меток. Под заголовком Тариф введено >40, под заголовком Отработано час. введено>100.

Обратите внимание, что критерии в данном примере располагаются на отдельных строках.

 

Интервал критериев может содержать любое число условий, которые интерпретируются следующим образом:

 

Условия на одной строке объединяются по правилу И Условия на отдельных строках объединяются по правилу ИЛИ.

 

 

$A$1: $B$3	-
	Результаты
интервал
		фильтрации списка
критериев
		на месте

 

 

Рис.6.6

 

Пример критерия с условиями, объединяемыми по правилам И и ИЛИ одновременно.

 

На рис.6.7 показан критерий и результат фильтрации для решения следующей задачи. Показать всех работников, фамилия которых начинается на букву “П“, у которых Сумма больше 3000 или Разряд выше 4.

 

Буква П присутствует в критерии в обеих строках поскольку записи, удовлетворяющие как условию Сумма> 3000, так и условию Разряд >4, должны одновременно удовлетворять критерию на фамилию (первая буква -

 

П).

 

 

Рис.6.7

 

ТЕКСТОВЫЕ КРИТЕРИИ

 

Интерпретация текстовых критериев не так очевидна, как может показаться. Обработка таких критериев выполняется Excel по следующим правилам:

 

Если задана одна буква, то по равенству (=) будут найдены все начинающиеся на эту букву значения. Например, по критерию =М будут найдены Мария, Максим, М.Шолохов и т.п.

 

По условию больше (>) или меньше (<), будут найдены значения, которые располагаются в алфавитном порядке соответственно после или до заданного значения. Например, по критерию > М в столбце ФИО будут выделены работники с фамилиями, начинающимися с букв от М до Я. Противоположный критерий <М позволяет отобрать фамилии, начинающиеся с букв от А до Л.

 

По критерию =“=текст” выделяются значения, точно совпадающие с заданным выражением текст. Например, чтобы найти записи с фамилией Иванов, следует задать =“=Иванов”. Если задать не формулу, а просто Иванов, то будут выделены записи с фамилиями Иванов, Иванова, Ивановский и т.п.

 

Символы шаблона обрабатываются так же, как в автофильтре.

 

ВЫЧИСЛЯЕМЫЕ КРИТЕРИИ

 

Вычисляемыми являются критерии, включающие более сложные операции, чем простое сравнение значения столбца с константой. Если для поиска работников с зарплатой более 3000р. достаточно простого критерия, то для поиска работника с зарплатой, большей медианы (средней ), требуется вычисляемый критерий.

 

Отметим три правила применения вычисляемых критериев:

 

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

 

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

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

3. Ссылки на ячейки внутри списка как правило относительны.