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

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

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

• Автофильтр - используется для простых условий отбора;

• Расширенный фильтр - применяется для сложных условий отбора.

Оба режима фильтрации вызываются с помощью команды Фильтр меню Данные.

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

Чтобы установить режим фильтрации данных в таблице с помощью Автофильтра, следует:

1. указать ячейку в фильтруемом списке;

2. выбрать в меню Данные пункт Фильтр, а затем команду Автофильтр;

3. в результате выполнения команды непосредственно в строку с метками столбцов помещаются кнопки раскрывающихся списков (кнопки со стрелкой).

Существует несколько способов фильтрации данных.

• Отображение только тех строк в таблице, которые соответствуют выбранному значению в определенном столбце. Например, чтобы отобразить только те строки в таблице, для которых значение в столбце Всего составляет 4000 кг, нужно выбрать для этого столбца в списке, который отображается при нажатии на стрелку, значение 400.

• Отображение определенного количества наибольших или наименьших элементов в режиме Первые 10.... При выборе этого пункта появляется диалоговое окно (см. рис. 22), в котором следует выбрать количество отображаемых элементов и требуемый вариант.

Рис. 22. Диалоговое окно команды Первые 10…

• Отображение только тех строк таблицы, которые удовлетворяют ограничениям, установленным в режиме Условие... Например, чтобы отобразить строки, для которых значение в столбце Всего больше или равно 400 кг и в то же время меньше или равно 520 кг, необходимо выполнить команду Условие..., после чего откроется диалоговое окно Пользовательский автофильтр (см. рис. 23). В первом поле под надписью Показать только те строки, значения которых выбрать оператор больше или равно и указать значение 400 справа от него, нажать кнопку И, во втором поле выбрать оператор меньше или равно и указать значение 520 (см. рис. 23).

Аналогичным образом можно осуществить фильтрацию и в других столбцах.

ПРИМЕЧАНИЕ. Если данные уже отфильтрованы по одному из столбцов, то при использовании фильтра для другого столбца будут предложены только те значения, которые видны в отфильтрованном списке.

Для возвращения исходного списка следует выбрать команду Данные|Фильтр|Отобразить все или Данные|Фильтр|Автофильтр.

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

Р ис. 23. Диалоговое окно команды Условие

Использование расширенного фильтра. Команда Данные|Фильтр|Расширенный фильтр позволяет отыскивать строки с помощью более сложных критериев, по сравнению с пользовательскими автофильтрами.

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

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

Например, выберем из списка (строки 6-14, см. рис. 24) записи, которые содержат имя Николай, отчество Васильевич и адрес г. Одесса.

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

ПРИМЕЧАНИЕ. Между значениями условий и списком должна находиться хотя бы одна пустая строка. Кроме того, формат заголовков списка должен отличаться от формата данных.

Рис. 24. Использование расширенного фильтра

2. Вводим под заголовками столбцов необходимые условия: Имя - Николай, Отчество - Васильевич и Адрес - г. Одесса.

3. Устанавливаем курсор в ячейку исходного списка.

4. В меню Данные выбираем пункт Фильтр, а затем - команду Расширенный фильтр, в результате чего будет открыто одноименное диалоговое окно (см. рис. 25).

5. Чтобы скопировать отфильтрованные строки в другую область листа, установим переключатель Обработка в положение скопировать результат в другое место.

6. В поле ввода Исходный диапазон указываем интервал (ссылки на таблицу), содержащий список, который следует отфильтровать. (Более подробно ссылки будут рассмотрены в следующей главе.)

7. В поле Диапазон условий указываем интервал ячеек с критериями отбора, включая заголовки.

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

9. Е сли требуется отобразить только неповторяющиеся строки, следует включить опцию Только уникальные записи.

Рис. 25. Окно диалога Расширенный фільтр

Работа с функциями и формулами

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