Работа в Windows (стр. 3 из 6)
3. Excel: поиск и выборка данных в соответствии с назначенным критерием
Выбор данных по заданному критерию (условию) – это фильтрация данных в списке. Осуществляется эта операция с помощью команды Данные, Фильтр.
Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. Фильтрация данных может осуществляться с помощью специальной формы, которая вызывается командой Данные, Форма.
3.1 Автофильтрация
Выделите любую ячейку в списке, выберите команду меню Данные, Фильтр, Автофильтр для каждого столбца строит список значений, и который используется для задания условий фильтрации (рис. 10). После этого в заглавной строке каждого столбца появятся кнопки-стрелки.
Щелчок на стрелке приведет к появлению списка, содержащего перечень всех элементов этого столбца.
Кроме этого в списке находятся команды, позволяющие показать все элементы списка, первые десять или задать свой собственный критерий.
Раскрыв список критериев в заголовке столбца, укажите критерий выборки.
Рисунок 10 – Список с автофильтром
Результат выборки показывается на месте списка.
Чтобы показать весь список после просмотра результатов выборки, выберите команду меню Данные>Фильтр>Отобразить все или Данные>Фильтр>Автофильтр.
По отдельному столбцу в списке критериев отбора предусматриваются варианты:
- все – выбираются все записи без ограничений;
- первые 10 – данный пункт позволяет во вновь появляющемся диалоговом окне «Наложение условия по списку» (рис. 11) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;
- значения – будут выбраны только те записи, которые в данном столбце содержат указанное значение;
Рисунок 11 – Диалоговое окно «Наложение условия по списку»
- условие – выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский фильтр» (рис. 12).
-
Рисунок 12 – Диалоговое окно «Пользовательский фильтр»
Условие для отбора записей по конкретным значениям в определенном столбце может состоять из двух самостоятельных частей, соединенных логической связкой И/ИЛИ.
Каждая часть условия включает:
- оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), < (меньше), <= (меньше или равно), начинается с, содержит и т.п.;
- значение, которое может выбираться из списка или содержать шаблонные символы *,?.
Пример. Для Кода предмета можно сформировать условия:
>=п* – отобрать все записи, которые содержат код предмета, начинающийся с буквы п;
>= п1 И <=п2 – отобрать все записи, которые содержат коды предметов п1 и п2;
<>п1 – отобрать все записи, которые не содержат кода предмета п1.
Можно задать условия отбора для нескольких столбцов независимо друг от друга. фильтрация записей выполняется по всем условиям одновременно. Все записи, не прошедшие через фильтр, будут скрыты.
Отфильтрованные записи можно выделить и скопировать в другое место, удалить. Отмена результата фильтрации и возврат к исходному состоянию списка производятся повторным вводом команды Данные, Автофильтр.
3.2 Расширенный фильтр
Расширенный фильтр позволяет выбирать данные из таблицы по нескольким критериям сразу. Фильтровать можно на месте, но лучше результат фильтрации расположить в другое место, так как при использовании расширенного фильтра на месте списка отфильтрованная информация уничтожается.
Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка: критерий сравнения; вычисляемый критерий. Обычно критерий фильтрации формируется в нескольких столбцах, и тогда его называют множественным критерием.
Важной особенностью этого режима является необходимость формирования заранее, до выполнения самой команды фильтрации, специального блока (области) для задания сложных поисковых условий, называемых областью критерия (диапазоном условия).
Технология использования расширенного фильтра состоит из двух этапов: этап 1 – формирование области критериев поиска; этап 2 – фильтрация записей списка.
Э т а п 1. Формирование диапазона условий для расширенного фильтра. Область критериев поиска содержит строку имен столбцов и произвольное число строк для задания поисковых условий. Рекомендуется скопировать первую строку с именами полей из области списка в область, где будет формироваться критерий отбора записей (на тот же или другой лист, в другую рабочую книгу). Далее ненужные имена столбцов из диапазона условий можно удалить.
Критерий сравнения формируется при соблюдении следующих требований:
- состав столбцов области критериев определяется столбцами, по которым задаются условия фильтрации записей;
- имена столбцов области критериев должны точно совпадать с именами столбцов исходного списка;
- ниже имен столбцов располагаются критерии сравнения типа:
- точного значения;
- значения, формируемого с помощью операторов отношения;
- шаблона значения, включающего символы * и (или)?.
Правила формирования множественного критерия:
1. Отдельно от списка создайте зону критериев. Если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И.
2. Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.
Пример. Условие выбора записей о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5 можно записать несколькими способами:
1‑й способ. Множественный критерий сравнения – все условия находятся в одной строке, связка И. Номер группы, код предмета заданы как точные значения, оценка – оператор сравнения со значением константы.
| Номер группы | Код предмета | Оценка |
| 133 | П1 | >3 |
2 й способ. Множественный критерий сравнения – все условия (точи значения полей) находятся в одной строке, столбец Оценка используется дважды, связка И.
| Номер группы | Код предмета | Оценка | Оценка |
| 133 | П1 | 4 | 5 |
3‑й способ. Множественный критерий сравнения – условия (точные значения полей) записаны в двух строках, связка ИЛИ.
| Номер группы | Код предмета | Оценка |
| 133 | п1 | 4 |
| 133 | п1 | 5 |
Вычисляемый критерий представляет собой формулу, записанную в строке области» условий, которая возвращает логическое значение ИСТИНА или ЛОЖЬ. Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (числе текст, дата, логическая константа), операторов отношения. Имя столбца, содержащего формулу вычисляемого критерия, должно отличаться от имени столбца в списке.
Пример. Выбрать записи о сдаче экзаменов студентами группы 133 с оценкой ниже общего среднего балла или записи с оценкой 5:
| Номер группы | Оценка1 |
| 133 | =ИЛИ (G2<=CP3HAЧ ($G$2:$G$I7); G2=5) |
В области критериев присутствуют столбцы с заголовками: Номер группы. Оценка1.
Последовательность действий по созданию данного вычисляемого критерия:
- присвоить отличное от имен полей списка новое имя столбцу, куда будет введен вычисляемый критерий;
- установить курсор в ячейку ввода;
- вызвать Мастер функций – команда Вставка, Функция, выбор категории – Логические и выбор функции – ИЛИ;
- ввод параметров функции ИЛИ:
Логическое!: G2<=CP3HAЧ ($G$2:$G$17)
(при вводе формулы использовать курсорный указатель на ячейки таблицы, вызов встроенной функции СРЗНАЧ, указание на абсолютные ссылки с помощью клавиши <F4>)
Логическое2: G2=5
После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ – результат применения сформированного вычисляемого критерия по отношению к первой записи списка; формулу критерия можно просмотреть лишь в строке формул. Этот же критерий можно было записать по-другому:
| Номер группы | Оценка1 |
| 133 | =G2<=CP3HAЧ ($G$2:$G$ 17) |
| 133 | =G2=5 |
либо в комбинированном виде:
| Номер группы | Оценка1 | Оценка |
| 133 | <J2<=CPЗHAЧ ($G$2:$G$17) | |
| 133 | 5 |
Э т а п 2. Фильтрация записей расширенным фильтром. После подготовки области критерия курсор устанавливается в список и выполняется команда Данные, Фильтр, Расширенный фильтр (рис. 13).