Материал: 5442
21
числовом порядке. Например, с помощью пользовательского порядка сортировки можно отсортировать список по месяцам календаря: январь, февраль, март, апрель и т.д.
Для выполнения такой сортировки необходимо использовать один из стандартных списков. Увидеть эти списки можно в диалоговом окне Параметры (команда Сервис – Параметры) на вкладке Списки (рисунок 12).
Рисунок 12 – В поле Списки содержатся списки для сортировки данных
Кроме того, пользователь может создавать свои собственные списки.
Для создания своего списка на вкладке Списки диалогового окна Параметры в поле Списки необходимо выбрать значение НОВЫЙ СПИСОК.
В поле Элементы списка ввести все элементы по порядку. Щёлкнуть на кнопке Добавить. После этого созданный список появится в поле Списки. На рисунке 12 в поле Списки можно увидеть список: Цемент, Шпаклевка, Шпакрил, Алебастр. Этот список был создан для сортировки по наименованию стройматериалов.
Элементы списка необязательно вводить вручную. Можно импортировать элементы из диапазона ячеек. Для этого в поле Импорт из списка ячеек указать диапазон, который содержит элементы нового списка. Затем щёлкнуть на кнопке
Импорт.
22
Фильтрация списков
Фильтрация списка – это отбор только тех записей списка, которые удовлетворяют заданному условию. В отфильтрованном списке будут отображены строки, которые отвечают условиям отбора. Все остальные строки будут скрыты.
В Excel для фильтрации списков можно использовать два средства:
Автофильтр для простых условий отбора;
Расширенный фильтр для более сложных условий отбора.
В отличие от сортировки при фильтрации порядок записей в списке не изменяется. При фильтрации временно скрываются строки, которые не требуется отображать. Строки, отобранные при фильтрации, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядок строк и не перемещая их.
Автофильтр
Для выполнения Автофильтра щёлкнуть на любой ячейке списка. Затем выбрать команду Данные – Фильтр – Автофильтр. После этого Excel добавит в строку заголовков кнопки раскрытия списка значений, как показано на рисунке 13.
Рисунок 13 – К таблице применён режим Автофильтр
В режиме Автофильтр можно быстро отобрать нужные записи. Автофильтр предполагает использование критериев поиска типа “сравнение”. Существует два типа сравнений:
1)по точному или шаблонному значению;
2)по условию отбора.
23
Точное значение для сравнения выбирается из раскрывающегося списка для указанного поля. Например, для того чтобы отобразить записи об отдельном материале, надо щёлкнуть на кнопке автофильтра, расположенной в заголовке Наименование товара. В раскрывшемся списке выбрать одно значение из этого перечня. В списке останутся только те элементы, у которых значение данного поля совпадает с выбранным. Кроме того, изменится цвет кнопки автофильтра в поле Наименование товара. По этому признаку можно определить, что список отфильтрован по значениям этого столбца.
Чтобы отменить фильтр и снова отобразить на экране весь список, щёлкнуть на изменившей цвет кнопке автофильтра и в раскрывшемся списке выбрать значение Все. Это значение расположено первым в списке условий автофильтра.
Если в списке условий автофильтра выбрать значение Условие, появится диалоговое окно Пользовательский автофильтр, в котором можно задать более сложные условия для фильтрации. При выборе сравнения по условию задается критерий отбора, состоящий из двух предложений, связанных между собой логической функцией И или ИЛИ.
Для задания условия в диалоговом окне Пользовательский автофильтр выбрать один из операторов в первом слева поле (рисунок 14). В соседнем поле выбрать из списка или ввести вручную нужное значение. Чтобы добавить ещё одно условие, выбрать переключатель И, если нужно, чтобы выполнялись оба условия, или кнопку ИЛИ, если достаточно, чтобы выполнялось одно из условий фильтрации. Затем указать оператор и значение во второй строке. Для числовых значений в условиях используются следующие операторы: равно, не равно,
больше, больше или равно, меньше, меньше или равно. Для отбора текстовых строк можно использовать операторы: начинается с, не начинается с, заканчи-
вается на, не заканчивается на, содержит или не содержит.
Рисунок 14. Диалоговое окно Пользовательский автофильтр
24
Для отбора текстовых строк можно использовать символы подстановки: ?, *, или ~ (символ ~ (тильда) используется в тех случаях, когда текст содержит символ ?).
Отбор по наибольшему или наименьшему значению можно использовать для столбцов, содержащих числа. Для этого щёлкнуть на кнопке автофильтра и выбрать значение (Первые 10...). Откроется диалоговое окно Наложение условия по списку (рисунок 15). В первом поле указать количество записей, во втором – выбрать один из вариантов: наибольших или наименьших. В третьем поле вы-
брать одно из значений: элементов списка или % от количества элементов
(например, можно отобрать 5 записей с наибольшим значением или 5% наибольших элементов поля).
Рисунок 15 – Использование критерия Первые 10…
Отбор пустых и непустых ячеек. Если столбец содержит пустые ячейки, то в конец списка значений автофильтра добавляются ещё два условия: Пустые и Непустые. При выборе значения Пустые на экране отобразятся только строки с пустыми ячейками в данном столбце. При выборе значения Непустые будут отобраны строки с непустыми ячейками в данном столбце.
Начиная с версии Excel 2003 в раскрывающемся списке значений авто-
фильтра имеются еще две опции: Сортировка по возрастанию и Сортировка по убыванию. Выбор этих опций приводит к сортировке списка по данному полю по возрастанию или убыванию соответственно.
В режиме автофильтра можно фильтровать записи по нескольким столбцам. Задавая условия по значениям в нескольких столбцах, все условия объединяются функцией И, то есть, если данные уже отфильтрованы по одному из столбцов, при использовании автофильтра для другого столбца будут предложены только те значения, которые видны в отфильтрованном списке.
Для быстрого отображения всех записей на рабочем листе выполнить команду Данные – Фильтр – Отобразить все. Чтобы отключить режим автофиль-
25
трации, необходимо выбрать команду Данные – Фильтр – Автофильтр. После отключения автофильтра список восстановит исходные значения и исчезнут кнопки автофильтра в строке заголовков.
Автоматическая фильтрация имеет ограничения. В списке условий автофильтра отображаются только первые 999 различных значений. На рабочем листе автофильтр можно применить только к одному списку (правила организации списков пункт 3).
Расширенный фильтр
Для фильтрации списка или базы данных по более сложным критериям, включая вычисляемый критерий с использованием любой функции Excel, а также для получения результатов фильтрации в другом месте рабочего листа, вне исходного списка, применяется команда Расширенный фильтр.
Для выполнения команды Расширенный фильтр, необходимо сформировать диапазон условий, который можно разместить в любом месте текущего рабочего листа, на другом листе открытой книги или даже в другой книге Excel. Целесообразно поместить его над или под исходным списком. Диапазон условий должен содержать не менее двух строк. Первая строка диапазона условий содержит названия всех или некоторых полей списка. Остальные строки диапазона условий содержат условия для фильтрации.
Необходимо учитывать, что значения условий фильтрации, размещённые в одной строке диапазона (области) критериев, объединяются логической функцией И, а значения условий, заданные в разных строках области критериев, связываются функцией ИЛИ. Диапазон условий должен отделяться от исходного списка по крайней мере одной пустой строкой.
В диапазон условий команды Расширенный фильтр можно ввести вычисляемые критерии, однако при этом следует придерживаться определённых правил.
При задании условий для текстовых строк необходимо учитывать, что при сравнении текстовых строк регистр символов не учитывается. Кроме того, в условиях для текстовых строк можно использовать подстановочные символы ? и
*.
При составлении условий часто используются такие операторы, как равно (=), больше или равно (>=) и др. В таблице 2 перечислены операторы, которые