Расширенный фильтр в excel диапазон условий

Создание таблицы с условиями отбора

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

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

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

Автофильтр и расширенный фильтр в Excel

Это простая таблица, не отформатированная и не объявленная как список. Вы можете включить автоматический фильтр через главное меню.

1. Выберите любую ячейку в пределах диапазона с помощью мыши. Перейдите на вкладку «Данные» и нажмите на кнопку «Фильтр».
2. Рядом с заголовками таблиц появляются стрелки для открытия списков автофильтров.

Если вы отформатируете диапазон данных как таблицу или объявите его как список, автоматический фильтр будет добавлен немедленно.

пользоваться автофильтром просто: нужно выбрать запись с нужным значением. Например, показать доставку в магазин №4. Поставьте галочку рядом с соответствующим условием фильтра:

Мы сразу видим результат:

Особенности инструмента:

1. Автофильтр работает только в не преломляющей области. Различные таблицы на одном листе не фильтруются. Даже если у них одинаковый тип данных.
2. Инструмент обрабатывает верхнюю строку как заголовки столбцов — эти значения не включаются в фильтр.
3. Вы можете использовать несколько условий фильтрации одновременно. Но каждый предыдущий результат может скрывать записи, необходимые для следующего фильтра.

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

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

Варианты фильтрации данных

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

1. При выборе параметра «Числовые фильтры» появятся следующие параметры фильтрации: равно, больше, меньше, Top 10… Top 10… и т д.
2. Когда вы выбираете параметр «Текстовые фильтры» в контекстном меню, вы можете отметить, что параметр фильтра содержит…, начинается с.. и т д.
3. Если вы выберете параметр «Фильтровать по дате», параметры фильтрации: завтра, на следующей неделе, в прошлом месяце и т д.
4. Во всех перечисленных выше случаях контекстное меню содержит пункт Пользовательский фильтр… Пользовательский…, с помощью которого можно указать одновременно два условия отбора, относящиеся к отношению И И — одновременное выполнение 2-х условий, ИЛИ Или – выполнение хотя бы одного условия.

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

Отмена фильтрации

Чтобы отменить фильтрацию диапазона данных, просто нажмите кнопку «Фильтр» еще раз.

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

Чтобы быстро снять фильтрацию со всех столбцов, выполните команду Удалить на вкладке Данные 

Скрытие и группировка данных

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

Если нет полной уверенности, что данные не пригодятся для расчетов, то такие столбцы можно скрыть стандартной операцией контекстного меню Excel (предварительно следует выделить один или несколько столбцов).

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

Сгруппированные столбцы могут быть открыты через интерфейс структуры листа. Объекты структуры включают кнопки для скрытия/показа уровня целиком (кнопки с цифрами в левом верхнем углу) или каждой группы в отдельности (кнопки /).

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

Сортировка по нескольким столбцам в Excel

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

1. Выбираем любую ячейку в таблице, которую нужно подвергнуть сортировке.
2. Находим на вкладке «Данные» кнопку «Сортировка» и кликаем по ней.
3. Нажимаем кнопку «Добавить уровень» в меню открывшегося окна, чтобы добавить дополнительную строку для условий.
4. Нажимаем кнопку «Параметры». В появившемся окошке нужно выбрать пункт «Столбцы таблицы», потом кликаем по кнопке «ОК».
5. Заполняем несколько форм для постановки условий и жмем кнопку «ОК». Столбцы таблицы построятся в указанном порядке.

Файл

В приложении к статье файл multilevelgroups.xls с решением задачи. Работает во всех версиях Excel, начиная с 2000 (10.0) (по идее Excel 2011 for Mac тоже должно работать, пока нет возможности проверить). Для автоматического обновления таблицы данных по выбору из списка необходимо подключить макросы Excel.

В файле 2 таблицы, первая — справочник многоуровневых групп, вторая — пример таблицы с данными (Группа-Продукт). Для фильтрации групп использовано поле со списком.

Можно добавлять данные в середину справочника групп через копирование строк целиком. Также можно менять названия и подчиненность. Помните, что при переименовании родительской группы, значения в подчиненных группах (поле Parent) автоматически не меняется. Для переименования можно использовать замену данных, либо привязать подчиненную группу через формулу со ссылкой на название родителя. Справочник групп не содержит никаких дополнительных полей для ввода, кроме названия и ссылки на родительсий элемент. В скрытых столбцах содержатся формулы, позволяющие решить задачу фильтрации данных без программирования. Для показа скрытых столбцов проще всего нажать на кнопку структуры документа.

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

Но она позволяет выстроить элементы фильтра в правильном иерархическом порядке, что важно с точки зрения пользовательского интерфейса

В таблице с условными данными в заголовке поля Group располагается элемент управления типа раскрывающийся список. Взят за основу контрол из коллекции Forms, а не ActiveX Combobox — для возможности использования в Excel for Mac. Переделать пример на ActiveX Combobox не представляет особого труда. В свойствах контрола указан источник значений списка ($D$2:$D$19) и служебная ячейка записи номера выбранного элемента ($H$1)

Где в Excel фильтры — их виды

Найти фильтры в этой программе легко — нужно открыть главное меню или просто зажать клавиши Ctrl+Shift+L.

Как задать фильтр в Excel

Основные функции фильтрации в Excel:

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

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

Использование расширенного фильтра в Эксель

Допустим, мы имеем табличку с информацией, к которой необходимо применить фильтрацию.

1

Подробная инструкция выглядит так:

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

2

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

3

3. После заполнения дополнительной таблички переходим к следующему этапу. Наводим указатель мышки на абсолютно любую ячейку исходной или же дополнительной таблиц. В верхней части интерфейса табличного редактора находим раздел «Данные» и щелкаем по нему ЛКМ. Находим блок команд под названием «Фильтр» и выбираем элемент «Дополнительно».

4

4. На экране появилось небольшое специальное окошко, имеющее наименование «Расширенный фильтр». Здесь можно произвести разнообразные настройки расширенной фильтрации.

5

5. У этого инструмента существует две вариации использования. Первый вариант – «Скопировать результаты в другое место», а второй – «Фильтровать список на месте». Эти функции реализуют различный вывод отфильтрованных данных. 1-я вариация выводит отфильтрованную информацию в другом месте книги, заранее указанном пользователем. 2-я вариация выводит отфильтрованную информацию в основной табличке. Производим выбор необходимого элемента. В нашем конкретном примере ставим отметку около надписи «Фильтровать список на месте». Переходим к следующему шагу.

6

6. В строчку «Диапазон списка» нужно ввести адрес таблички вместе с заголовками. Реализовать эту несложную процедуру можно двумя способами. Первый способ – написание координат таблички при помощи клавиатуры. Второй– после нажатия на иконку, находящуюся рядом со строчкой для ввода диапазона, необходимо выделить табличку путем зажатия левой клавиши мышки. В строчку «Диапазон условий» аналогичным методом вбиваем адрес дополнительной таблички вместе с заголовками и строчками с условиями. Щёлкаем на «ОК», чтобы подтвердить внесенные изменения.

7

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

8

8. Вернемся на несколько шагов назад. Если пользователь выберет вариацию «Скопировать результаты в другое место», то итоговый показатель будет отображен в заданном им месте, а основная табличка никак не изменится. В строчку «Поместить результат в диапазон» необходимо будет вбить адрес того места, в котором будет отображен результат. Здесь можно ввести одно поле, которое в итоге и станет началом координат для новой дополнительной таблички. В нашем конкретном примере это ячейка, имеющая адрес А42.

9

9. Нажав на «ОК», новая дополнительная табличка с указанными настройками фильтрации вставится ячейку А42 и растянется на прилегающую область.

10

Пример 1. Фильтрация данных по одному критерию (регион)

Предположим, у вас есть набор данных, показанный ниже, и вы хотите отфильтровать все записи только для США.

Ниже приведена формула ФИЛЬТРА, которая сделает это:

= ФИЛЬТР ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "США")

В приведенной выше формуле набор данных используется в качестве массива, а условие — $ B $ 2: $ B $ 11 = «США».

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

Кроме того, в этом примере у меня есть исходные данные и отфильтрованные данные на одном листе, но вы также можете иметь их на отдельных листах или даже в книгах.

Функция фильтра возвращает результат, который является динамическим массивом (что означает, что вместо возврата одного значения она возвращает массив, который распространяется на другие ячейки).

Чтобы это работало, вам нужно иметь область, где результат будет пустым. В любой из ячеек в этой области (E2: G5 в этом примере) что-то уже есть, функция выдаст вам ошибку #SPILL.

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

В приведенной выше формуле я жестко запрограммировал значение региона, но вы также можете поместить его в ячейку, а затем ссылаться на эту ячейку, которая имеет значение региона.

Например, в приведенном ниже примере у меня есть значение региона в ячейке I2, и оно затем упоминается в формуле:

= ФИЛЬТР ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)

Это делает формулу еще более полезной, и теперь вы можете просто изменить значение региона в ячейке I2, и фильтр автоматически изменится.

У вас также может быть раскрывающийся список в ячейке I2, где вы можете просто сделать выбор, и он мгновенно обновит отфильтрованные данные.

Как пользоваться расширенным фильтром в Excel?

Чтобы отменить действие расширенного фильтра, поставим курсор в любом месте таблицы и нажмем сочетание клавиш Ctrl + Shift + L или «Данные» — «Сортировка и фильтр» — «Очистить».

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

В таблицу условий внесем критерии. Например, такие:

Программа в данном случае будет искать всю информацию по товарам, в названии которых есть слово «Набор».

Для поиска точного значения можно использовать знак «=». Внесем в таблицу условий следующие критерии:

Excel воспринимает знак «=» как сигнал: сейчас пользователь задаст формулу. Чтобы программа работала корректно, в строке формул должна быть запись вида: =»=Набор обл.6 кл.»

После использования «Расширенного фильтра»:

Теперь отфильтруем исходную таблицу по условию «ИЛИ» для разных столбцов. Оператор «ИЛИ» есть и в инструменте «Автофильтр». Но там его можно использовать в рамках одного столбца.

В табличку условий введем критерии отбора: =»=Набор обл.6 кл.» (в столбец «Название») и =»

Обратите внимание: критерии необходимо записать под соответствующими заголовками в РАЗНЫХ строках. Результат отбора:

Результат отбора:

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

Отбор строки с максимальной задолженностью: =МАКС(Таблица1).

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

Срезы

Программа Microsoft Excel позволяет прикрепить к таблицам интерактивные элементы для сортировки и фильтрации – срезы. После выхода версии 2013-о года появилась возможность подключать срезы к обычным таблицам, а не только к сводным отчетам. Разберемся, как создать и настроить эти опции.

Создание срезов

1. Кликаем по одной из ячеек таблицы – на панели инструментов появится вкладка «Конструктор», которую нужно открыть.

2. На вкладке следует найти кнопку «Вставить срез» – кликаем по ней и переходим к новому открывшемуся окну.
3. Отмечаем галочками все поля, которые следует добавить в срез, и кликаем по кнопке «ОК».

Срезы выглядят, как диалоговые окна со списками кнопок. Названия пунктов зависят от того, какие элементы таблицы были выбраны при создании среза. Чтобы отфильтровать данные, нужно кликнуть по кнопке в одном из списков. Фильтрация по нескольким диапазонам данных возможна, если нажать кнопки в нескольких срезах.

Форматирование срезов

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

1. Открываем вкладку «Параметры» и находим раздел «Стили срезов». В нем находятся темы для срезов разных цветов. Выбираем любую из них – цвет не повлияет на эффективность работы элемента. Сразу после клика по стилю срез приобретет указанные цвета.
2. Также возможно изменить положение срезов на экране. Воспользуемся кнопками «Переместить вперед» и «Переместить назад» в разделе «Упорядочить». Необходимо выбрать один из срезов и нажать кнопку на панели инструментов. Теперь при перемещении по экрану срез будет оказываться поверх всех срезов или попадет под них.
3. Удаление срезов – несложная операция. Выберите лишнее окно и нажмите клавишу «Delete» на клавиатуре. Срез исчезнет с экрана и перестанет влиять на фильтрацию данных в таблице.

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

Альтернативный способ расширенной фильтрации

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

1. Для начала создадим дубликат шапки таблицы вверху. Он будет использоваться для указания критериев сортировки. Между скопированной шапкой и оригинальной таблицей должна находиться быть хотя бы одна строка.
2. Условия для сортировки указываем в строке под дубликатом шапки. Необходимо отобразить позиции со стоимостью выше 50, относящиеся к категории «Овощи». Заполняем ячейки, указав соответствующие параметры в качестве их значений.
3. Теперь необходимо сделать так, чтобы эти условия применялись к основной части документа. Для этого нужно выделить любую ячейку исходной таблицы. Затем нужно перейти во вкладку «Данные» и открыть пункт «Дополнительно».
4. Откроется окно, в котором нужно указать несколько параметров. Сначала задаем место фильтрации. Удобнее всего выбрать вариант «Список на месте», чтобы не копировать результат сортировки. Исходный диапазон заполняется автоматически — это вся таблица. Нужно задать только диапазон условий. Здесь надо указывать адреса ячеек, в которых прописаны условия. Проще выделить всю скопированную шапку вместе с прописанными под ней критериями.
5. Настроив расширенный фильтр, нажимаем OK. В результате будут отображены все ячейки, удовлетворяющие заданным требованиям.

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

Отмена расширенной фильтрации в Эксель

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

1. Передвигаемся в раздел под наименованием «Главная».
2. Находим блок команд «Фильтр».
3. Кликаем на кнопку «Очистить».

Второй метод, отменяющий расширенную фильтрацию:

1. Передвигаемся в раздел под наименованием «Главная».
2. Жмем левой клавишей мышки на элемент «Редактирование»
3. На следующем этапе раскрываем небольшой перечень «Сортировка и фильтр».
4. В возникшем контекстном меню кликаем ЛКМ на элемент под названием «Очистить».

Важно! Если дополнительная табличка с расширенной фильтрацией располагается в новом месте, то процедура, реализуемая через элемент «Очистка», не поможет. Необходимо будет произвести все манипуляции вручную.

Автофильтр

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

1. Нужно открыть меню в нужной колонке и кликнуть по пункту с одним из этих названий. Откроется список фильтров, которые можно использовать для таблицы.
2. Выбираем один из фильтров, заполняем поля в окне «Пользовательский автофильтр» и жмем «ОК». Настройки будут применены к диапазону ячеек.

Рассмотрим опцию «Настраиваемый фильтр». С ее помощью пользователи могут самостоятельно установить нужные настройки фильтрации.

1. Снова открываем текстовые или числовые фильтры. В конце списка находится нужный пункт, по которому следует кликнуть.
2. Заполняем поля – можно выбрать любой тип фильтра, походящий по формату ячеек, из списка и значения, существующие в диапазоне ячеек. После заполнения нажимаем кнопку «ОК». Если заполнение было непротиворечивым, настройки будут применены автоматически.

Стоит обратить внимание на пункты И/ИЛИ в окне настройки автофильтра. От них зависит то, как будут применены настройки – вместе или частично

3 — Excel расширенный фильтр нескольких критериев одного столбца

Станьте Excel Pro: Присоединяйтесь к нашему курсу!

Поднимите свои навыки от новичка до героя с помощью нашего курса Excel 365, предназначенного для того, чтобы сделать вас опытными в нескольких сессиях.

Зарегистрируйтесь здесь

Чтобы иметь возможность использовать фильтр Excel более чем для 2-х критериев, вам нужно создать другой столбец с тем же заголовком, что и столбец, который вы хотите фильтровать, — в нашем примере — столбец — имя — и поместите один критерий поиска по строке ниже. Все критерии отбора будут применяться как OR — значение, результаты будут соответствовать любому из них.

Затем выберите столбец, который вы хотите фильтровать, выберите в меню «Сортировка и фильтр» меню «Данные» => «Дополнительно», щелкните значок «Диапазон критериев» и выберите ячейки критериев — не весь столбец, только данные критериев, начинающиеся с того же заголовка столбца как данные для фильтрации.

И вуаля! В приведенном ниже примере список отелей был отфильтрован по всем отелям из Holiday Inn, Crowne Plaza, Radisson или Novotel с расширенным фильтром Excel 2013, в результате чего в пользовательском автофильтре Excel было более 2 критериев, также называемых расширенным Excel. Отфильтруйте несколько критериев в одном столбце, так как разные критерии применяются одновременно к одному и тому же столбцу.

Специальный пользовательский фильтр Excel, содержащий более 2 критериев, приведет к отображению только элементов, содержащих любой из отображаемых фильтров.

Ниже приведен результат с использованием нескольких фильтров Excel.

Фильтрация и сортировка данных — Использование Microsoft Excel — Руководства по исследованиям

Описание примеров

Примеры к статье построены на основе демонстрационной базы данных, которую можно скачать с сайта Microsoft

Выгруженный из этой базы данных набор записей сформирован при помощи Microsoft Query.

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

Файл nwdata_interface.xls используется для версий Excel 2000-2003. Упакован в zip-архив.

Файл nwdata_interface.xlsx имеет некоторые отличия и используется для версий Excel 2007-2010.

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

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

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

Одно условие для одного столбца

Например, нужно из существующей таблицы выделить записи (строки) людей с ростом больше 180 см:

Сначала создаем Диапазон условий. Например:

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

Выбираем – скопировать результат в другое место, Исходный диапазон – оставляем (если мы предварительно выделили всю исходную таблицу), Диапазон условий – $D$24:$D$25, Поместить результат в диапазон – указываем любую ячейку в свободной области рабочего листа (где-нибудь ниже основной таблицы и диапазона условий):

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

Результат фильтрации:

Несколько условий для одного столбца (логическое ИЛИ)

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

В диалоговом окне Расширенного фильтра указываем нужный диапазон условий (D38:D41) и новое место для отфильтрованной таблицы:

В результате получится таблица, содержащая только указанные фамилии:

Для числовых столбцов поступаем аналогично. Например, надо отобрать людей с ростом меньшим 160 см или большим 180 см. Для этого нужно задать следующий диапазон условий:

Несколько условий (логическое И)

Пусть необходимо отобрать людей с ростом, попадающим в диапазон от 160 см до 180 см, фамилия которых начинается на букву С. Для этого зададим следующий диапазон условий:

Таким же образом можно составлять более сложные условия отбора, комбинируя логические операции И и ИЛИ. Например:

Вычисляемые условия

В качестве условия можно использовать значение, являющееся результатом вычисления по формуле. При создании условия отбора с помощью формулы не используйте заголовок столбца в качестве заголовка столбца условий (либо оставьте условие отбора без заголовка, либо используйте произвольный текст).
Например, следующий диапазон условий используем для отбора строк, которые содержат в столбце Рост значения, превышающее среднее значение в столбце:

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

Результат фильтрации:

« Назад

Вперед »

Аннотация к презентации

Интересует тема «Фильтрация данных в MS Excel»? Лучшая powerpoint презентация на эту тему представлена здесь! Данная презентация состоит из 33 слайдов. Средняя оценка: 3.3 балла из 5. Также представлены другие презентации по информатике для 11 класса. Скачивайте бесплатно.

Фильтрация данных в Ms Excel с помощью команды Расширенный фильтр

Слайд 2

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

Слайд 3

Автофильтр

Команда Автофильтр – используется в том случае, если условия фильтрации (критерии) задаются по одному полю (меню «Данные»  Фильтр  Автофильтр).

Слайд 4

Возвращает данные после фильтра Условия фильтра записываются с помощью >, Выводит MIN и MAX значения, % от количестваэлементов Задача:Поле 1 =условие Автофильтр Расширенный фильтр Меню Данные, команда Фильтр Значения поля Все Условие Первые 10 Фильтрация данных

Слайд 5

Слайд 6

Слайд 7

Слайд 8

Отобразить данные о литературе 3 издательств : «М.: Триумф», «К.: Диалектика», «М.: Лучшие книги»

Слайд 9

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

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

Слайд 10

Слайд 11

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

Исходная таблица Пустая ячейка Диапазон записанных условий

Слайд 12

Условия отбора расширенного фильтра Задача Задача Задача

Слайд 13

Отобразить данные о литературе 3 издательств : «М.: Триумф», «К.: Диалектика», «М.: Лучшие книги»

Слайд 14

Слайд 15

Слайд 16

Слайд 17

Задачи

2. Отобразить на экране литературу, издательств «М.: Триумф» и «СПб.: Питер», 2009 года издания с кодом до 258000.

Слайд 18

Слайд 19

Слайд 20

Слайд 21

Слайд 22

Слайд 23

Слайд 24

Слайд 25

Слайд 26

Слайд 27

Слайд 29

Слайд 30

Рекомендуемая литература

Д.Н Златопольский.1700 заданий по Microsoft Excel — СПб: БХВ-Петербург, 2003-544 с. Н.Б.Культин. Excel 2007. Самое необходимое – СПб: БХВ-Петербург, 2007 – 192 с: + Видеокурс (СD-Rom) Мультимедийный учебник «Microsoft Excel 2000» (базовый курс).

Фильтрация данных
Текстом является любая последовательность, состоящая из цифр, пробелов и нецифровых символов, например приведенные ниже записи интерпретируются как текст: 10АА109, 127ФЧН, 12-976, 2084675. Введенный текст выравнивается в ячейке по левому краю.

Заключение

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

Смотри также

» Фильтр по многоуровневым группам

Еще один пример, расширяющий грани возможного в Excel.Требуется создать фильтр, отбирающий записи в таблице сгруппированных по определенному полю. Главное условие — группы могут быть вложены одна в другую. Задача по описанию проста, встречается повсеместно, но реализовать нормальную работу в электронных таблицах на практике достаточно проблематично. В программных комплексах для решения этой задачи используются служебные таблицы соответствий «родитель-потомок» (все ко всем), либо аналогичные по функциональности структуры в оперативной памяти. В Excel можно использовать автофильтр с множественной выборкой подчиненных групп вручную. Этот же алгоритм можно попытаться автоматизировать, используя программную проверку элементов фильтра. Решение вполне реализуемо, но по факту смотрится не очень красиво, так как в…

» Условное форматирование

Форматирование ячеек Excel предназначено для представления информации в удобном наглядном виде, что повышает уровень понимания информации. Какие-то простые одноразовые вычисления можно производить в файлах, совсем не используя форматы ячеек – результат от этого не изменится. Если же предполагается показывать или передавать отчет другим пользователям, то без форматирования не обойтись.Стандартное простое форматирование ячеек электронной таблицы Excel с использованием цвета фона, шрифта и рамок осваивается пользователями очень быстро. На этом уровне прогресс в оформлении отчетов в большинстве случаев останавливается. Зачастую даже опытные пользователи Excel, способные написать сложную формулу, вообще не используют интерфейсное средство «Условное форматирование». Между тем, оно, с нашей точки зрения,…

» Обработка больших объемов данных. Часть 3. Сводные таблицы

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

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