Фильтрация списка позволяет выбрать из него те записи, которые удовлетворяют заданным условиям. Так как таких записей всегда бывает меньше, чем всего в полном списке, то их проще анализировать. Это не значит, что записи списка пропадают, просто они скрываются, после проведения анализа их опять можно открыть и работать с полным списком.
В отличие от сортировки, при фильтрации порядок записей в списке не изменяется. При фильтрации временно скрываются строки, которые не требуется отображать.
Строки, отобранные при фильтрации в Excel, можно редактировать, форматировать и выводить на печать, а также создавать на их основе диаграммы, не изменяя порядок строк и не перемещая их.
Для проведения несложной фильтрации используют Автофильтр, который не требует сложных критериев отбора записей. Если необходимо применить сложные критерии отбора, то используют Расширенный фильтр.
Чтобы отобрать строки из списка с использованием одного или двух условий отбора для одного столбца с использованием автофильтра, используется следующая процедура:
Установить курсор в любую ячейку списка, задать команду Данные►Фильтр, а затем выбрать пункт Автофильтр. В названиях столбцов появятся кнопки со стрелками;
Нажать кнопку со стрелкой в том столбце, по значениям которого требуется выбирать данные;
Выбрать любой элемент из раскрывающегося списка;
При использовании пункта Условие можно задавать до двух критериев одного столбца, выбирая из списка операторов и списка значений данного поля те значения, которые необходимы для используемого критерия. В качестве условий может быть как равенство, так и неравенство выбираемых значений. Задаваемые условия могут использовать жесткое правило одновременного выполнения, или более простое правило действия хотя бы одного из них, что определяют использованием операций И или ИЛИ (установить флажки в соответствующих полях открывшегося окна).
Чтобы отобразить строки, удовлетворяющие одному условию отбора, выбирается необходимый оператор сравнения в первом поле под надписью Показать только те строки, значения которых и значение для сравнения справа от него.
Чтобы отобразить строки, удовлетворяющие одновременно двум условиям отбора, вводится оператор и значение для сравнения в первой группе полей, для чего необходимо установить переключатель И, а затем ввести второй оператор и значение для сравнения во второй группе полей.
Чтобы отобразить строки, удовлетворяющие одному из двух условий отбора, вводится оператор и значение для сравнения в первой группе полей, нажимается кнопка Или, а затем вводится второй оператор и значение для сравнения во второй группе полей.
Завершив установку условий, подтвердить условия фильтрации нажатием клавиши Ок;
Список можно еще урезать, если последовательно применить условия отбора записей по другим столбцам;
Для восстановления записей необходимо использовать команду Данные►Фильтр►Показать все записи или в раскрывающемся списке столбца, в котором производилась фильтрация, выбрать пункт Все. Для отмены фильтрации повторно подать команду Данные►Фильтр►Автофильтр.
При фильтрации списка с использованием сложных критериев используется расширенный фильтр. При этом можно использовать несколько условий отбора к одному столбцу, либо несколько условий отбора к нескольким столбцам, либо условия отбора определять как результат выполнения формулы.
При использовании расширенного списка стрелки раскрытия списка автофильтра не отображаются. Вместо этого условия вводятся диапазон условий на листе. Для того чтобы изменить способ фильтрации данных, следует изменить значения в диапазоне условий отбора и выполнить фильтрацию еще раз.
Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки. На листе также должно быть не менее трех пустых строк сверху от списка. Эти строки будут использованы в качестве диапазона условий отбора.
Порядок использования расширенного фильтра:
Указать любую ячейку в списке, где необходимо произвести фильтрацию;
Подать команду Данные►Фильтр►Расширенный фильтр.;
Выделить заголовки фильтруемых столбцов списка и нажать кнопку Копировать;
Выделить первую пустую строку диапазона условий отбора и нажать кнопку Вставить. Между диапазоном условий отбора и списком должно находить не менее одной пустой строки;
Ввести в строки под заголовками условий требуемые критерии отбора;
Результат фильтрации может быть оставлен на месте исходной таблицы, для чего в ней скрываются ненужные строки, для чего необходимо установить переключатель Обработка раскрываемого окна Расширенный фильтр в положение Фильтровать список на месте;
Чтобы скопировать отфильтрованные строки в другую область листа, необходимо установить переключатель Обработка в положение Скопировать результаты в другое место, перейти в поле Поместить результат в диапазон, а затем указать верхнюю левую ячейку области вставки;
Ввести в поле Диапазон условий ссылку на диапазон условий отбора, включающий заголовки столбцов;
Чтобы убрать диалоговое окно Расширенный фильтр на время выделения диапазона условий отбора, следует нажать кнопку свертывания диалогового окна;
После установки параметров окна нажать кнопку Ок. выбранные данные будут размещены в указанной для них области.
Если присвоить диапазону имя Критерии, то ссылка на диапазон будет автоматически появляться в поле Диапазон условий. Можно также определить имя База_данных для диапазона фильтруемых данных и имя Извлечь для области вставки результатов, и ссылки на эти диапазоны будут появляться автоматически в полях Исходный диапазон и Поместить результат в диапазон соответственно.
Когда отобранные строки копируются в другое место, можно указать столбцы, включаемые в копию. Перед фильтрованием необходимо скопировать заголовки нужных столбцов в первую строку области вставки результатов, а при фильтровании ввести ссылку на скопированные заголовки столбцов в поле Поместить результат в диапазон. Скопированные строки будут включать только столбцы, заголовки которых были скопированы.
Чтобы отобрать строки, содержащие ячейки с заданным значением, необходимо ввести требуемые число, дату, текстовую или логическую константу в ячейку ниже названия столбца диапазона условий. Например, чтобы отобрать строки, в которых индекс отделения связи равен 119136, следует ввести в диапазоне условий число 119136 ниже заголовка «Индекс отделения связи».
Чтобы отобрать строки с ячейками, имеющими значения в заданных пределах, следует использовать оператор сравнения. Условие отбора с оператором сравнения следует ввести в ячейку ниже заголовка столбца в диапазоне условий. Например, чтобы отобразить строки, имеющие значения ячеек большие или равные 1000, следует ввести условие отбора >=1000 ниже заголовка «Количество».
При использовании текстовой константы в качестве условия отбора будут отобраны все строки с ячейками, содержащими текст, начинающийся с заданной последовательности символов. Например, при вводе условия Бел будут отобраны строки с ячейками, содержащими слова Белов, Беляков и Белугин. Чтобы получить точное соответствие отобранных значений заданному образцу, например Бел, следует ввести в ячейку условий отбора следующую формулу: =''=Бел''.
Чтобы отобрать строки с ячейками, содержащими последовательность символов, в некоторых позициях которой могут стоять произвольные символы, следует использовать знаки подстановки. Знак подстановки эквивалентен одному символу или произвольной последовательности символов (табл. 2).
В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых на несколько столбцов, а также условия, создаваемые как результат выполнения формулы.
Несколько условий для одного столбца. При наличии для одного столбца двух и более условий отбора эти условия отбора вводятся непосредственно друг под другом в отдельные строки.
Таблица 2
Используется |
Чтобы найти |
? (знак вопроса) |
Любой символ в той же позиции, где указан знак вопроса. Например, для поиска «барин» или «барон» следует ввести «бар?н». |
* (звездочка) |
Любое количество символов в той же позиции, где указана звездочка. Например, для поиска слов «северо-восток» и «юго-восток» следует указать «*восток». |
~ (тильда), за которой следует ?, * или ~ |
Знак вопроса, звездочку или тильду. Например, для поиска «ан91?» следует указать «ан~?». |
Одно условие для нескольких столбцов. Для того чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, все условия отбора вводятся в одну строку диапазона условий отбора. Это соответствует выполнению жесткого правила, когда все условия должны выполняться одновременно в каждой отбираемой записи.
Разные условия для разных столбцов. Для того чтобы найти данные, отвечающие одному условию в одном столбце, или отвечающие другому условию в другом столбце, условия отбора вводятся в разные строки диапазона условий отбора.
продавец
объем продаж
Белов
>10000
Бородин
>15000 |
Условия, создаваемые как результат выполнения формулы. В качестве условия отбора можно использовать вычисляемое значение, являющееся результатом выполнения формулы. При создании условия отбора с помощью формулы нельзя использовать заголовок столбца в качестве заголовка условия. Условие отбора следует оставить без заголовка. Либо можно использовать другой заголовок, не являющийся заголовком столбца на листе.
Формула, используемая для создания условия отбора, должна использовать для ссылки на подпись столбца (например, «Продажи») или на соответствующее поле в первой записи относительную ссылку. Все остальные ссылки в формуле должны быть абсолютными.
При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выводиться значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.
Когда возникает потребность принимать решение, требуется установить некоторые параметры, при которых решение задачи будет выполняться наилучшим образом. Такая задача оптимизации выполняется программой поиска решения. С ее помощью вычисляется значение некоторой ячейки путем подбора значений нескольких ячеек, при этом учитываются ограничения на используемые значения исходных и получаемых величин.
Отдельным используемым ячейкам присвоены названия.
Целевая ячейка содержит параметр, для которого требуется найти минимальное или максимальное значение.
Изменяемые ячейки – это ячейки, данные в которых в процессе поиска решения изменяются, и от них зависит значение оптимизируемого параметра. При поиске решения значения параметров в изменяемых ячейках меняются до тех пор, пока не будет найдено удовлетворительное решение.
Ограничения – условия, которые не должны превышаться в процессе поиска решения. Он могут быть наложены на данные любых ячеек таблицы, в том числе и на целевую ячейку.
Запустить команду Сервис►Поиск решения. Откроется диалоговое окно Поиск решения;
В поле Установить целевую ячейку этого окна ввести ссылку на ячейку, в которой хранится условие решения задачи (функция, которая определяет значение цели);
В поле Изменяя ячейки ввести ссылки на изменяемые ячейки (имена или адреса), которые содержат параметры, определяющие состояние решаемой задачи, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек. Можно изменяемые ячейки не указывать, а щелкнуть кнопку Предположить, тогда программа Поиска решения сама определит ячейки, влияющие на формулу, ссылка на которую дана в поле Установить целевую ячейку;
Для задания ограничений щелкнуть по кнопке Добавить. В открывшемся диалоговом окне в поле Ссылка на ячейку ввести адрес ячейки, где содержится первое ограничение на параметры задачи. Это условие обычно выражается в виде формулы, связывающей параметры задачи, которая не должна выходить из требуемых параметров.
Во втором поле этого окна выбрать оператор ограничения (<, >. < >= и т.д.). Оператор ограничения выбирается в окне, открывающемся нажатием кнопки с треугольником. В следующем поле Ограничения требуется ввести его значение. После нажатия кнопки Ок, происходит возврат в диалоговое окно Поиска решения, в поле Ограничения которого окажется записанным введенное ограничение.
Если есть другие ограничения, то требуется нажать кнопку Добавить и повторить ввод новых ограничений.
Изменять или отменять ограничения можно с помощью кнопок Изменить или Удалить;
Когда все ограничения будут введены, кнопкой Параметры можно задать ограничения на процесс решения: максимальное время решения, предельное число итераций, допустимое отклонение (неточность в подборе параметров решения), сходимость, метод поиска;
Если известно, что решаемая задача линейная, то следует включить режим Линейная, что позволит ускорить процесс решения. Завершить установку параметров следует нажатием кнопки Ок;
Для запуска программы поиска решения щелкнуть кнопку Выполнить. Полученные результаты будут выведены на рабочий лист.