Сложная фильтрация расширенный фильтр

Что такое электрический фильтр и каким он бывает?

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

Диапазон частот, который пропускает фильтр, называется полосой пропускания, диапазон частот в котором сигнал задерживается, называется полосой задержки. Граничная частота, разделяющая полосу пропускания и полосу задержки, называется частотой среза фильтра. В зависимости от предъявляемых требований фильтры имеют различные характеристики и признаки. По основным четырём признакам фильтры разделяются:

  • По частотным диапазонам: фильтры нижних частот (ФНЧ), фильтр высоких частот (ФВЧ), полосовой фильтр (ПФ), заграждающий фильтр (ЗФ, “фильтр пробка”);
  • По структуре : т — образные самые распространённые, п — образные и г — образные есть и более сложные структуры;
  • По составу элементов: реактивные LC фильтры самая распространённая группа, пассивные RC фильтры, активные фильтры содержащие активные элементы (транзисторы, операционные усилители и другие электронные компоненты);
  • По типу: фильтры типа “k” и фильтры типа “m”.

Условные обозначения фильтров приведены на рисунке ниже.

Поскольку электрический фильтр является четырёхполюсником, то для его описания применяют функции четырехполюсника и в первую очередь комплексный коэффициент передачи по напряжению Ku = U2/U1 , где U2 – выходное напряжение, U1 – входное напряжение фильтра. Построение АЧХ (амплитудно – частотной характеристики), как основной характеристики любого электрического фильтра, полностью зависит от возможности получения значений этого коэффициента. АЧХ определяется как модуль — комплексный коэффициент передачи фильтра: |Ku| = |U2|/|U1|, по значениям Ku = |Ku| можно судить о подавлении или пропускании сигнала.

Выше, на рисунке 1, изображены виды типовых АЧХ реальных фильтров: ФНЧ – фильтра низких частот, ФВЧ – фильтра высоких частот, ПФ – полосового фильтра, ЗФ – заграждающего фильтра.

На рисунках 1а) и 1б) изображена граничная частота fгр, для которой значение обычно принимают как единица деленная на корень с двух, что равно 0,707 раз. Граничную частоту обычно считают границей полосы пропускания фильтра. Следует отметить, что на практике для определения граничных частот полос пропускания и задерживания не всегда используют уровень 0,707. Кроме того , иногда вводятся дополнительные граничные частоты. Например, на рисунке 1а) изображена дополнительно частота fз, определяющая границу полосы задерживания фильтра.

На рисунках 1в) и 1г), для ПФ и ЗФ, изображены центральные резонансные частоты fр полос пропускания и задержки соответственно, обозначенных в свою очередь символами П.

Чем ближе форма АЧХ к прямоугольной, тем лучше избирательные свойства фильтра. Например, вторая АЧХ (рисунок 1б)) принадлежит фильтру изготовленному более качественно, чем фильтр с первой АЧХ.

Выше мы рассмотрели типовые виды АЧХ электрических фильтров. Но в некоторых случаях необходимо иметь сведения и о ФЧХ (фазо-частотной характеристике фильтра). Например, в современных системах связи, используется так называемая угловая модуляция, когда информация содержится в изменениях частоты и фазы сигнала и где требуется знать фазо-частотную характеристику фильтра. Фазо-частотная характеристика (ФЧХ) представляет зависимость разности фаз между выходным и входным сигналами от частоты сигнала, то есть функция, выражающая (описывающая) эту зависимость, а также график этой функции.

Как же выглядят ФЧХ типовых электрических фильтров? Покажем это на рисунках ниже в виде диаграмм Боде, названных в честь выдающегося инженера Хенрика Боде, включающих в себя амплитудно-частотную и фазо-частотную характеристики:

Звенья электрических фильтров обычно выполняют как Г-, П- или Т – конструкции, схематически показанные ниже, на рисунке 3. Если в этих схемах использовать одинаковые сопротивления Z1 и Z2, то все три фильтра будут иметь примерно одинаковые полосы пропускания.

Для получения необходимых характеристик, разработчики часто используют каскадные включения из нескольких звеньев фильтров, изображённых на рисунке 3 , такие фильтры называют многозвенными. Например, П- или Т-звено можно получить каскадным соединением двух Г-звеньев. Ниже, на рисунках 4 и 5 изображены простые и довольно распространённые схемы электрических фильтров.

Как упоминалось в самом начале статьи, электрические фильтры
ещё подразделяются и по типам, на фильтры “k” и фильтры “m”.

Достоинства фильтров “k”:

  • простота построения (например, катушка с конденсатором);
  • надёжность.

Основные недостатки фильтров “k”:

  • значительное изменение характеристического сопротивления в полосе пропускания (от максимума и почти до нуля). Это обстоятельство весьма затрудняет согласование фильтра с нагрузкой;
  • плавное изменение коэффициента затухания в районе частоты среза, что приводит к пропусканию фильтром “нежелательных” частот.

Эти основные недостатки фильтров “k” устраняются применением фильтров “m”. Такой фильтр можно получить из звена фильтра “k”, путём построения дополнительного звена в поперечной ветви. В результате этого образуется дополнительный резонансный контур и “подбором” весового коэффициента m, который влияет на характеристическое сопротивление, можно добиться необходимых характеристик, устранив вышеуказанные недостатки фильтра “k”. Но при применении фильтров “m” появляется новый недостаток, а именно, коэффициент затухания при стремлении частоты к бесконечности стремится к нулю, а надо, чтобы он, как и в фильтре “k” стремился к
бесконечности. Поэтому для устранения этого недостатка на практике применяют каскадные соединения звеньев фильтров типа “k” и типа “m”. Друг за другом ставят два звена, у одного коэффициент затухания стремится к бесконечности, другое обеспечивает крутизну характеристики, и поставленная задача решена, хотя всё на самом деле может быть
и не совсем так просто, так как наверняка могут потребоваться довольно сложные математические расчёты и знания из области ТОЭ (теоретических основ электротехники).

Ниже, на рисунке, показано как выглядят некоторые схемы реактивных фильтров “m” типа, собранных на L, C — элементах , где m – весовой коэффициент.

Материал данной статьи построен на примерах реактивных LC-фильтров содержащих только катушки индуктивности и конденсаторы, пассивных RC,RL- фильтров, а также активных фильтров на операционных усилителях. Но есть ещё и другие разновидности фильтров, такие как: кварцевые, электромеханические, фильтры на коаксиальных линиях передачи, фильтры на поверхностных акустических волнах, на переключаемых конденсаторах, активные фильтры на транзисторах и другие. Для упрощения теоретического анализа, как правило, все разновидности используемых на практике фильтров сводят к LC-фильтрам, при этом конструктивные элементы реальных фильтров замещают их электрическими аналогами в виде конденсаторов, катушек и резисторов.

Источник

Сложная фильтрация (расширенный фильтр)

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

Данные, организованные в список, называются базой данных. При этом строки таблицы – записи базы данных, а столбцы – поля записей. Чтобы превратить таблицу Excel в список, необходимо присвоить столбцам имена, которые будут использоваться как имена полей записей. При создании базы данных необходимо выполнять следующие правила:

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

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

3. В заголовках столбцов списка данных не следует объединять ячейки.

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

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

Сортировка данных в таблице

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

Для проведения сортировки необходимо выделить ячейку таблицы и выбрать команду Данные, Сортировка. Excel автоматически выделит весь список. Если в первой строке находятся имена полей, то они не будут включены в сортировку. Надо иметь в виду, что итоговая строка исходного списка также будет включена в сортировку, поэтому иногда бывает целесообразнее вручную выделить диапазон данных для сортировки.

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

Для быстрой сортировки можно воспользоваться кнопками «Сортировка по возрастанию» и «Сортировка по убыванию» стандартной панели инструментов (в этом случае ключом сортировки является столбец с текущей ячейкой).

Если в результате сортировки вы не добились ожидаемого результата, отмените ее. Для этого щелкните на кнопке «Отменить» стандартной панели инструментов.

Фильтрация данных в списке

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

Читайте также:  Что мы знаем о топливных фильтрах AMD

Автофильтр

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

Кнопки, раскрывающие список фильтра, создаются в первой строке диапазона. Если в столбце встречаются незаполненные ячейки, список дополняется категориями «пустые» и «непустые». В любом случае верхняя строка диапазона не рассматривается как объект для фильтрации и в список не включается. Даже если часть строк не попала в выделение, принцип фильтрации будет распространяться и на них, включая итоговую строку. После того, как диапазон выделен выбираем команду Данные/ Фильтр / Автофильтр.

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

Чтобы удалить критерий фильтрации для отдельного столбца, надо выбрать параметр «Все» в раскрывающемся списке. Чтобы отобразить все скрытые в списке строки, надо выбрать Данные/ Фильтр/Отобразить все.

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

Чтобы задать пользовательский критерий надо выбрать параметр Условие в раскрывающемся списке, а затем, в появившемся окне «Пользовательский автофильтр» задать необходимые критерии: Показывать только те строки, значения которых… – указать нужные критерии.

Для удаления Автофильтра необходимо повторно выбрать команду Данные/Фильтр/Автофильтр.

Первые 10. Этот метод имеет смысл только для полей с числовыми данными, в том числе и с датами. Чтобы воспользоваться этим методом, нужно выбрать в списке вариант «Первые 10…». В появившемся диалоговом окне следует указать, сколько наибольших или наименьших элементов следует отображать. Например, в БД имеется поле «Оклад» для хранения размера оклада сотрудника. Организация имеет возможность оказать материальную помощь 5 сотрудникам с наименьшим окладом. Для решения этой задачи можно воспользоваться методом отбор «Первые 10…» для поля «Оклад»: задать количество отображаемых записей с наименьшим значением.

Сложная фильтрация (расширенный фильтр)

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

Расширенный фильтр позволяет:

1. сразу копировать отфильтрованные записи в другое место рабочего листа.

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

3. показывать в отфильтрованных записях не все столбцы таблицы, а только указанные

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

5. для одного столбца объединять операторами И, ИЛИ более двух условий.

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

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

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

Таким образом, для выполнения команды Расширенный фильтр следует:

· сформировать в свободном месте рабочего листа таблицу критериев

· сформировать шапку выходного документа

· выделить область списка в исходном документе

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

Задание условий с использованием логической операции ИЛИ:

Чтобы задать условия в диапазоне критериев логической операцией ИЛИ, нужно эти условия расположить на разных строках: например:

1) Отобразить записи о менеджерах с фамилией «Кислов» или о менеджерах, продающих «Хлеб»:

2) Получить информацию о клиентах, чьи фамилии начинаются на букву В и Т:

Задание условий с использованием логической операции И:

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

1) Найти информацию о менеджерах с фамилией «Петрова», продавших товар более чем на 50 единиц:

2) Найти информацию о товарах, цена которых больше 30 и меньше 80 рублей:

Задание условий с одновременным использованием логических операций И, ИЛИ:

Например, выбрать из списка записи о клиентах с фамилией на букву В и с видом сделки «поставка», а также клиентов с фамилией на С с видом сделки «продажа»:

Источник



Расширенный фильтр в MS EXCEL

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

Алгоритм создания Расширенного фильтра прост:

  • Создаем таблицу, к которой будет применяться фильтр (исходная таблица);
  • Создаем табличку с критериями (с условиями отбора);
  • Запускаем Расширенный фильтр .

Пусть в диапазоне A 7:С 83 имеется исходная таблица с перечнем товаров, содержащая поля (столбцы) Товар , Количество и Цена (см. файл примера ). Таблица не должна содержать пустых строк и столбцов, иначе Расширенный фильтр (да и обычный Автофильтр ) не будет правильно работать.

Задача 1 (начинается. )

Настроим фильтр для отбора строк, которые содержат в наименовании Товара значения начинающиеся со слова Гвозди . Этому условию отбора удовлетворяют строки с товарами гвозди 20 мм , Гвозди 10 мм , Гвозди 10 мм и Гвозди .

Табличку с условием отбора разместим разместим в диапазоне А 1 :А2 . Табличка должна содержать также название заголовка столбца, по которому будет производиться отбор. В качестве критерия в ячейке А2 укажем слово Гвозди .

Примечание : Структура критериев у Расширенного фильтра четко определена и она совпадает со структурой критериев для функций БДСУММ() , БСЧЁТ() и др.

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

ВНИМАНИЕ! Убедитесь, что между табличкой со значениями условий отбора и исходной таблицей имеется, по крайней мере, одна пустая строка (это облегчит работу с Расширенным фильтром ).

Теперь все подготовлено для работы с Расширенным фильтром:

  • выделите любую ячейку таблицы (это не обязательно, но позволит ускорить заполнение параметров фильтра);
  • вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
  • в поле Исходный диапазон убедитесь, что указан диапазон ячеек таблицы вместе с заголовками ( A7:С83 );
  • в поле Диапазон условий укажите ячейки содержащие табличку с критерием, т.е. диапазон А1:А2 .

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

Нажмите кнопку ОК и фильтр будет применен — в таблице останутся только строки содержащие в столбце Товар наименования гвозди 20 мм , Гвозди 10 мм , Гвозди 50 мм и Гвозди . Остальные строки будут скрыты.

Номера отобранных строк будут выделены синим шрифтом.

Чтобы отменить действие фильтра выделите любую ячейку таблицы и нажмите CTRL+SHIFT+L (к заголовку будет применен Автофильтр , а действие Расширенного фильтра будет отменено) или нажмите кнопку меню Очистить ( Данные/ Сортировка и фильтр/ Очистить ).

Задача 2 (точно совпадает)

Настроим фильтр для отбора строк, у которых в столбце Товар точно содержится слово Гвозди . Этому условию отбора удовлетворяют строки только с товарами гвозди и Гвозди ( Регистр не учитывается). Значения гвозди 20 мм , Гвозди 10 мм , Гвозди 50 мм учтены не будут.

Читайте также:  Замена салонного фильтра Хендай Санта Фе Hyundai Santa Fe фото и видео инструкция

Табличку с условием отбора разместим разместим в диапазоне B1:В2 . Табличка должна содержать также название заголовка столбца, по которому будет производиться отбор. В качестве критерия в ячейке B2 укажем формулу .

Теперь все подготовлено для работы с Расширенным фильтром:

  • выделите любую ячейку таблицы (это не обязательно, но позволит ускорить заполнение параметров фильтра);
  • вызовите Расширенный фильтр ( Данные/ Сортировка и фильтр/ Дополнительно );
  • в поле Исходный диапазон убедитесь, что указан диапазон ячеек таблицы вместе с заголовками ( A7:С83 );
  • в поле Диапазон условий укажите ячейки содержащие табличку с критерием, т.е. диапазон B1:B2 .
  • Нажмите ОК

Применять Расширенный фильтр с такими простыми критериями особого смысла нет, т.к. с этими задачами легко справляется Автофильтр . Рассмотрим более сложные задачи по фильтрации.

Если в качестве критерия указать не =»=Гвозди» , а просто Гвозди , то, будут выведены все записи содержащие наименования начинающиеся со слова Гвозди ( Гвозди 80мм , Гвозди2 ). Чтобы вывести строки с товаром, содержащие на слово гвозди , например, Новые гвозди , необходимо в качестве критерия указать =»=*Гвозди» или просто * Гвозди, где * является подстановочным знаком и означает любую последовательность символов.

Задача 3 (условие ИЛИ для одного столбца)

Настроим фильтр для отбора строк, у которых в столбце Товар содержится значение начинающееся со слова Гвозди ИЛИ Обои .

Критерии отбора в этом случае должны размещаться под соответствующим заголовком столбца ( Товар ) и должны располагаться друг под другом в одном столбце (см. рисунок ниже). Табличку с критериями размести в диапазоне С1:С3 .

Окно с параметрами Расширенного фильтра и таблица с отфильтрованными данными будет выглядеть так.

После нажатия ОК будут выведены все записи, содержащие в столбце Товар продукцию Гвозди ИЛИ Обои .

Задача 4 (условие И)

Произведем отбор только тех строк таблицы, которые точно содержат в столбце Товар продукцию Гвозди , а в столбце Количество значение >40. Критерии отбора в этом случае должны размещаться под соответствующими заголовками (Товар и Количество) и должны располагаться на одной строке . Условия отбора должны быть записаны в специальном формате: и =»>40″ . Табличку с условием отбора разместим разместим в диапазоне E1:F2 .

После нажатия кнопки ОК будут выведены все записи содержащие в столбце Товар продукцию Гвозди с количеством >40.

СОВЕТ: При изменении критериев отбора лучше каждый раз создавать табличку с критериями и после вызова фильтра лишь менять ссылку на них.

Примечание : Если пришлось очистить параметры Расширенного фильтра ( Данные/ Сортировка и фильтр/ Очистить ), то перед вызовом фильтра выделите любую ячейку таблицы – EXCEL автоматически вставит ссылку на диапазон занимаемый таблицей (при наличии пустых строк в таблице вставится ссылка не на всю таблицу, а лишь до первой пустой строки).

Задача 5 (условие ИЛИ для разных столбцов)

Предыдущие задачи можно было при желании решить обычным автофильтром . Эту же задачу обычным фильтром не решить.

Произведем отбор только тех строк таблицы, которые точно содержат в столбце Товар продукцию Гвозди , ИЛИ которые в столбце Количество содержат значение >40. Критерии отбора в этом случае должны размещаться под соответствующими заголовками (Товар и Количество) и должны располагаться на разных строках . Условия отбора должны быть записаны в специальном формате: =»>40″ и . Табличку с условием отбора разместим разместим в диапазоне E4:F6 .

После нажатия кнопки ОК будут выведены записи содержащие в столбце Товар продукцию Гвозди ИЛИ значение >40 (у любого товара).

Задача 6 (Условия отбора, созданные в результате применения формулы)

Настоящая мощь Расширенного фильтра проявляется при использовании в качестве условий отбора формул.

Существует две возможности задания условий отбора строк:

  • непосредственно вводить значения для критерия (см. задачи выше);
  • сформировать критерий на основе результатов выполнения формулы.

Рассмотрим критерии задаваемые формулой. Формула, указанная в качестве критерия отбора, должна возвращать результат ИСТИНА или ЛОЖЬ.

Например, отобразим строки, содержащие Товар, который встречается в таблице только 1 раз. Для этого:

  • введем в ячейку H2 формулу =СЧЁТЕСЛИ(Лист1!$A$8:$A$83;A8)=1
  • в Н1вместо заголовка введем поясняющий текст, например, Неповторяющиеся значения. Поясняющий текст НЕ должен совпадать ни с одним заголовком столбца таблицы! В противном случае фильтр будет работать неправильно.

Применим Расширенный фильтр, указав в качестве диапазона условий ячейки Н1:Н2 .

Обратите внимание на то, что диапазон поиска значений введен с использованием абсолютных ссылок , а критерий в функции СЧЁТЕСЛИ() – с относительной ссылкой. Это необходимо, поскольку при применении Расширенного фильтра EXCEL увидит, что А8 — это относительная ссылка и будет перемещаться вниз по столбцу Товар по одной записи за раз и возвращать значение либо ИСТИНА, либо ЛОЖЬ. Если будет возвращено значение ИСТИНА, то соответствующая строка таблицы будет отображена. Если возвращено значение ЛОЖЬ, то строка после применения фильтра отображена не будет.

СОВЕТ: Для проверки работоспособности формулы можно создать дополнительный столбец рядом с таблицей (например в F) и ввести указанную выше формулу в ячейку F8, а затем скопировать ее вниз. Будет сформирован столбец со значениями ИСТИНА/ЛОЖЬ, который поможет определить как работает ваша формула.

Примеры других формул из файла примера :

  • Вывод строк с ценами больше, чем 3-я по величине цена в таблице. =C8>НАИБОЛЬШИЙ( $С$8:$С$83 ;5) В этом примере четко проявляется коварство функции НАИБОЛЬШИЙ(). Если отсортировать столбец С (цены), то получим: 750; 700; 700 ; 700; 620, 620, 160, … В человеческом понимании «3-ей по величине цене» соответствует 620, а в понимании функции НАИБОЛЬШИЙ() – 700 . В итоге, будет выведено не 4 строки, а только одна (750);
  • Вывод строк с учетом РЕгиСТра =СОВПАД(«гвозди»;А8) . Будут выведены только те строки, в которых товар гвозди введен с использованием строчных букв;
  • Вывод строк, у которых цена выше среднего =С8>СРЗНАЧ($С$8:$С$83) ;

ВНИМАНИЕ! Применение Расширенного фильтра отменяет примененный к таблице фильтр ( Данные/ Сортировка и фильтр/ Фильтр ).

Задача 7 (Условия отбора содержат формулы и обычные критерии)

Рассмотрим теперь другую таблицу из файла примера на листе Задача 7 .

В столбце Товар приведено название товара, а в столбце Тип товара — его тип.

Задача состоит в том, чтобы для заданного типа товара вывести товары, у которых цена ниже средней. То есть у нас 3 критерия: первый критерий задает Товар, 2-й — его Тип, а 3-й критерий (в виде формулы) задает цену ниже средней.

Критерии разместим в строках 6 и 7. Введем нужные Товар и Тип товара. Для заданного Тип товара вычислим среднее и выведем ее для наглядности в отдельную ячейку F7. В принципе, формулу можно ввести прямо в формулу-критерий в ячейку С7. Поясняющий текст в ячейке над формулой (С6) НЕ должен совпадать ни с одним заголовком столбца таблицы! В противном случае фильтр будет работать неправильно.

Далее действуем как обычно: выделяем любую ячейку таблицы, вызываем Расширенный фильтр (Advanced Filter) и указываем диапазон с критериями.

Будут выведены 2 товара из 4-х (заданного типа товара).

В файле примера для удобства использовано Условное форматирование : выделяются строки удовлетворяющие первым 2-м критериям (подробнее см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке ).

Задача 7.1. (Совпадают ли 2 значения в одной строке?)

Есть таблица, в которой указаны Год выпуска и Год покупки автомобиля.

Требуется вывести только те строки, в которых Год выпуска совпадает с Годом покупки. Это можно сделать с помощью элементарной формулы =В10=С10 .

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

Задача 8 (Является ли символ числом?)

Пусть у нас есть таблица с перечнем различных типов гвоздей.

Требуется отфильтровать только те строки, у которых в столбце Товар содержится Гвозди 1 дюйм , Гвозди 2 дюйма и т.д. товары Гвозди нержавеющие, Гвозди хромированные и т.д. не должны быть отфильтрованы.

Проще всего это сделать если в качестве фильтра задать условие, что после слова Гвозди должно идти цифра. Это можно сделать с помощью формулы =ЕЧИСЛО(—ПСТР(A11;ДЛСТР($A$8)+2;1))

Формула вырезает из наименования товара 1 символ после слова Гвозди (с учетом пробела). Если этот символ число (цифра), то формула возвращает ИСТИНА и строка выводится, в противном случае строка не выводится. В столбце F показано как работает формула, т.е. ее можно протестировать до запуска Расширенного фильтра .

Задача 9 (Вывести строки, в которых НЕ СОДЕРЖАТСЯ заданные Товары)

Требуется отфильтровать только те строки, у которых в столбце Товар НЕ содержатся: Гвозди, Доска, Клей, Обои .

Для этого придется использовать простую формулу =ЕНД(ВПР(A15;$A$8:$A$11;1;0))

Функция ВПР() ищет в столбце Товар каждой строки наименования товаров, указанных в диапазоне А8:А11 . Если эти товары НЕ найдены, ВПР() возвращает ошибку #Н/Д, которая обрабатывается функцией ЕНД() — в итоге, формула возвращает ИСТИНА и строка выводится.

Читайте также:  Фильтр для горячей воды металлический корпус

Вывод уникальных строк

О выводе уникальных строк с помощью Расширенного фильтра можно прочитать в этой статье .

Источник

Сложная фильтрация (расширенный фильтр)

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

Данные, организованные в список, называются базой данных. При этом строки таблицы – записи базы данных, а столбцы – поля записей. Чтобы превратить таблицу Excel в список, необходимо присвоить столбцам имена, которые будут использоваться как имена полей записей. При создании базы данных необходимо выполнять следующие правила:

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

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

3. В заголовках столбцов списка данных не следует объединять ячейки.

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

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

Сортировка данных в таблице

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

Для проведения сортировки необходимо выделить ячейку таблицы и выбрать команду Данные, Сортировка. Excel автоматически выделит весь список. Если в первой строке находятся имена полей, то они не будут включены в сортировку. Надо иметь в виду, что итоговая строка исходного списка также будет включена в сортировку, поэтому иногда бывает целесообразнее вручную выделить диапазон данных для сортировки.

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

Для быстрой сортировки можно воспользоваться кнопками «Сортировка по возрастанию» и «Сортировка по убыванию» стандартной панели инструментов (в этом случае ключом сортировки является столбец с текущей ячейкой).

Если в результате сортировки вы не добились ожидаемого результата, отмените ее. Для этого щелкните на кнопке «Отменить» стандартной панели инструментов.

Фильтрация данных в списке

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

Автофильтр

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

Кнопки, раскрывающие список фильтра, создаются в первой строке диапазона. Если в столбце встречаются незаполненные ячейки, список дополняется категориями «пустые» и «непустые». В любом случае верхняя строка диапазона не рассматривается как объект для фильтрации и в список не включается. Даже если часть строк не попала в выделение, принцип фильтрации будет распространяться и на них, включая итоговую строку. После того, как диапазон выделен выбираем команду Данные/ Фильтр / Автофильтр.

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

Чтобы удалить критерий фильтрации для отдельного столбца, надо выбрать параметр «Все» в раскрывающемся списке. Чтобы отобразить все скрытые в списке строки, надо выбрать Данные/ Фильтр/Отобразить все.

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

Чтобы задать пользовательский критерий надо выбрать параметр Условие в раскрывающемся списке, а затем, в появившемся окне «Пользовательский автофильтр» задать необходимые критерии: Показывать только те строки, значения которых… – указать нужные критерии.

Для удаления Автофильтра необходимо повторно выбрать команду Данные/Фильтр/Автофильтр.

Первые 10. Этот метод имеет смысл только для полей с числовыми данными, в том числе и с датами. Чтобы воспользоваться этим методом, нужно выбрать в списке вариант «Первые 10…». В появившемся диалоговом окне следует указать, сколько наибольших или наименьших элементов следует отображать. Например, в БД имеется поле «Оклад» для хранения размера оклада сотрудника. Организация имеет возможность оказать материальную помощь 5 сотрудникам с наименьшим окладом. Для решения этой задачи можно воспользоваться методом отбор «Первые 10…» для поля «Оклад»: задать количество отображаемых записей с наименьшим значением.

Сложная фильтрация (расширенный фильтр)

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

Расширенный фильтр позволяет:

1. сразу копировать отфильтрованные записи в другое место рабочего листа.

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

3. показывать в отфильтрованных записях не все столбцы таблицы, а только указанные

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

5. для одного столбца объединять операторами И, ИЛИ более двух условий.

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

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

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

Таким образом, для выполнения команды Расширенный фильтр следует:

· сформировать в свободном месте рабочего листа таблицу критериев

· сформировать шапку выходного документа

· выделить область списка в исходном документе

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

Задание условий с использованием логической операции ИЛИ:

Чтобы задать условия в диапазоне критериев логической операцией ИЛИ, нужно эти условия расположить на разных строках: например:

1) Отобразить записи о менеджерах с фамилией «Кислов» или о менеджерах, продающих «Хлеб»:

2) Получить информацию о клиентах, чьи фамилии начинаются на букву В и Т:

Задание условий с использованием логической операции И:

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

1) Найти информацию о менеджерах с фамилией «Петрова», продавших товар более чем на 50 единиц:

2) Найти информацию о товарах, цена которых больше 30 и меньше 80 рублей:

Задание условий с одновременным использованием логических операций И, ИЛИ:

Например, выбрать из списка записи о клиентах с фамилией на букву В и с видом сделки «поставка», а также клиентов с фамилией на С с видом сделки «продажа»:

Источник