Фильтрация данных в Excel с использованием расширенного фильтра

Содержание

Почему в excel не работает фильтр

О проблемах, например, с регистрацией пишите сюда – alarforum@yandex.ru, проверяйте папку спам! Обязательно пройдите активизацию e-mail, а тут можно восстановить пароль.

Регистрация
Поиск по форуму
Расширенный поиск

Здесь нужно купить рекламу за 20 тыс руб в месяц! ) пишите сюда – alarforum@yandex.ru
Без учёта ботов – 20000 человек в день, 350000 в месяц.

Ваша тема закрыта, почему это могло произойти? Возможно,
Нет наработок или кода, если нужно готовое решение – создайте тему в разделе Фриланс и оплатите работу.
Название темы включает слова – “Помогите”, “Нужна помощь”, “Срочно”, “Пожалуйста”.
Название темы слишком короткое или не отражает сути вашего вопроса.
Тема исчерпала себя, помните, один вопрос – одна тема
Прочитайте правила и заново правильно создайте тему.
24.10.2007, 17:55 #1

Есть база данных в которой есть фильтры (допустим – выбрать договор такой то) и из кучи сточек выводилась только одна или первых 10
Так же есть возможность добавлять в фильтр какие то параметры – допустим моно в список фильтра добавить еще какой то договор.

В общем перестали работать эти фильтры и все. Как можно решить эту проблему?

Говорили что можно скопировать в новую книгу – не помогает =(

Источник: www.programmersforum.ru

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

Чтобы отсеять ненужные данные, при помощи фильтра, необходимо задать условия.

Для этого используются настройки фильтрации, где можно сделать следующее:

  • Настроить значения.
  • Если требуется, то можно сделать фильтрациюданных по цвету или значку ячейки, а также цвету шрифта.
  • Также для работы с данными задаётся фильтр подате, числовой или текстовый. Числовой фильтр позволяет выбрать один илинесколько дополнительных типов фильтрации: больше, меньше и т.д. Если выбираетсятекстовый, то появится несколько вариантов, для отсеивания информации:«содержит…», «начинается с…» и пр. При фильтрации дате можно выбратьопределённые дни, например, завтра, на прошлой или следующей неделе и т.д. Всеэти варианты фильтрации имеют ещё один пункт «Настраиваемый фильтр», с егопомощью можно настроить несколько условий отбора необходимой информации. При этомесли задаётся два условия для отбора, то для связи отношений используется «И».Чтобы было выполнено хотя бы одно условие «ИЛИ».

Одновременная фильтрация и сортировка

Если внимательней присмотреться, то во вкладке «Данные» слева от кнопки «Фильтр» располагается «Сортировка». С её помощью в таблице осуществляется распределение данных в определённом, заданном порядке.

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

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

Автофильтр

Для запуска автофильтра на панели инструментов нужно найти вкладку «Редактирование» и нажать на «Сортировка и фильтр» или перейти на вкладку «Данные» и кликнуть по «Фильтр».

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

Для отмены ранее установленной фильтрации, в разделе«Редактирование» или «Данные» кликаете по «Фильтр». Если требуется снятиефильтра лишь с одного столбца, то в этом случае:

  • Потребуется кликнуть по кнопке со стрелкой,размещенной в верхней части таблицы на нужном столбце.
  • Затем отмечается «Удалить фильтр из столбца» ина «ОК».

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

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

Чтобы воспользоваться расширенным фильтром потребуется:

  • Создать новую таблицу для указания условийотбора диапазона данных. Лучше это делать на другом листе, т.к. иначе могут неотображаться некоторые заданные условия.
  • Далее производится копирование «шапки» основнойтаблицы.
  • Следующим этап является прописывание условийфильтрации. Чтобы какой-то столбец соответствовал нескольким критериям отбораинформации, его заголовок прописывается повторно и в этом же столбцеуказывается второе условие.
  • Теперь нужно поработать со вкладкой «Данные».Ищите раздел «Сортировка и фильтр», где выбирается строка «Дополнительно».
  • В появившемся окне, потребуется выбрать один извариантов фиксации результатов. Стоит обратить внимание на то, что при выборе«Скопировать результат…», появиться активная строка — «Поместить результат…».Здесь нужно указать курсором ячейку диапазона, где будут размещаться результатыфильтрации. Также нельзя забывать и про «Исходный диапазон», для указаниякоторого выделяется вся таблица, в том числе и её заголовки. Для указания«Диапазона условий», отмечается весь требуемый диапазон данных, совместно сячейками, где указаны заголовки столбцов. Последние два диапазона отмечаются ив первом и во втором варианте фиксации данных. При необходимости исключениядублирующихся записей нужно отметить «Только уникальные записи».

Срезы

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

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

Создание срезов, позволяет также фильтровать информацию, размещённую в таблицах, в программе Excel, но только в несколько ином формате.

Чтобы создать «Срез» для таблицы, потребуется:

  • Сначала выделить ячейку, расположенную в таблице и найти раздел «Конструктор» и нажать на «Вставить срез». Также можно посмотреть такую же кнопку в разделе «Анализ» или «Вставка».
  • Далее отмечаются поля, которые должны быть включены в «Срез» и для сохранения, перед выходом из диалогового окна нажимается «ОК».

Чтобы произвести форматирование «Среза» во вкладке «Конструктор» имеется несколько стилей. Также, при желании, пользователь может создать индивидуальный стиль.

Кстати в некоторых версиях программы, эта возможность предусмотрена во вкладке «Параметры» в разделе «Стили срезов». Для удаления среза, его необходимо выделить и нажать на клавиатуре на «Delete».

Вывод

Чтобы решить вопрос о том, как создать фильтр в Excel по столбцам, как можно увидеть, имеется несколько способов.

Каждый из них имеет свои особенности и может быть использован в зависимости от версии, установленной на вашем ПК программы.

Как создать фильтр в Excel, видео

Уроки по работе в Эксель

Как сделать в Эксель, чтобы текст помещался в ячейке

Как сделать в Эксель, чтобы шапка таблицы не двигалась

Эксель неправильно считает сумму, не вычитает, причины

Как в таблице Эксель найти слово или фразу, инструкция

Как в Эксель отсортировать числа по возрастанию

Как поставить

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

Для начала работы необходимо выбрать одну, любую, ячейку внутри таблицы, открыть вкладку «Данные».

Затем нажать кнопку «Фильтр».

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

В таблице

Пошаговая инструкция: как поставить фильтр в эксель-таблице.

1. Нажать на значок в заголовке столбца.

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

2. Убрать лишние галочки, стоящие у параметров, которые пользователя не интересуют. Галочки останутся только у тех параметров, по которым необходимо провести поиск. После чего нажать «Ок».

3. Просмотреть результат — останутся только строки, соответствующие заданному параметру.

В диапазоне

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

Например, в рассматриваемом файле столбцы В и С имеют числовой отсев.

Остальные — текстовый.

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

Например, в случае с числовым поиском этот порядок действий выглядит так:

1. Выбираем вид отсева.

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

3. Ввести цифру, которая и станет границей для отсева, — выведены будут все значения больше нее.

4. Нажать «Ок» и оценить результат. В выбранном столбце останутся только значения, превышающие установленную границу.

Стандартный фильтр и сортировка по цвету в Excel

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

Таблица
Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

Стандартный фильтр
Из недостатков данного способа фильтрации можно отметить невозможность отфильтровать диапазон по нескольким цветам.

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

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):

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

Сортирование диапазона по красной заливке
Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Параметры настраиваемой сортировки

Как сделать расширенный фильтр в Excel?

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

  1. задать более двух критериев отбора;
  2. скопировать результат фильтрации на другой лист;
  3. задать условие любой сложности с помощью формул;
  4. извлечь уникальные значения.

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

  1. Делаем таблицу с исходными данными либо открываем имеющуюся. Например, так: filtr-sortirovka4-1.png
  2. Создаем таблицу условий. Особенности: строка заголовков полностью совпадает с «шапкой» фильтруемой таблицы. Чтобы избежать ошибок, копируем строку заголовков в исходной таблице и вставляем на этот же лист (сбоку, сверху, снизу) или на другой лист. Вносим в таблицу условий критерии отбора. filtr-sortirovka4-2.png
  3. Переходим на вкладку «Данные» — «Сортировка и фильтр» — «Дополнительно». Если отфильтрованная информация должна отобразиться на другом листе (НЕ там, где находится исходная таблица), то запускать расширенный фильтр нужно с другого листа.

filtr-sortirovka4-3.png

В открывшемся окне «Расширенного фильтра» выбираем способ обработки информации (на этом же листе или на другом), задаем исходный диапазон (табл. 1, пример) и диапазон условий (табл. 2, условия). Строки заголовков должны быть включены в диапазоны. filtr-sortirovka4-4.pngЧтобы закрыть окно «Расширенного фильтра», нажимаем ОК. Видим результат.

filtr-sortirovka4-5.png

Верхняя таблица – результат фильтрации. Нижняя табличка с условиями дана для наглядности рядом.

Фильтрация данных в диапазоне или таблице

В этом курсе:

Используйте автофильтр или встроенные операторы сравнения, такие как “больше чем” и “первые 10”, в Excel, чтобы отобразить нужные данные и скрыть остальные. После фильтрации данных в диапазоне ячеек или таблице можно либо повторно применить фильтр, чтобы получить актуальные результаты, либо очистить фильтр, чтобы заново отобразить все данные.

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

msn_video_widget.gif

Фильтрация диапазона данных

Выберите любую ячейку в диапазоне данных.

Выберите фильтр> данных .

5f3ec3cb-3409-4582-bd5b-69b31f4a90c7.jpg

Щелкните стрелку 06d649fc-4eb9-4fac-a115-95482d4c6126.jpgв заголовке столбца.

Выберите текстовые фильтры или Числовые фильтры, а затем выберите Сравнение, например между.

35615e05-8e47-45ff-91e8-010a7b5350a3.jpg

Введите условия фильтрации и нажмите кнопку ОК.

65203112-ec23-45bb-b150-32916d0907c4.jpg

Фильтрация данных в таблице

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

cc9a7349-187f-427d-9c6b-d30dabec1708.jpg

Щелкните стрелку 74a58824-2e40-473b-a207-162b2dc22733.gifв заголовке столбца, содержимое которого вы хотите отфильтровать.

Снимите флажок (выделить все) и выберите поля, которые нужно отобразить.

e8545b7f-b641-4d77-ba2e-cb000a388d24.jpg

Нажмите кнопку ОК.

Стрелка заголовка столбца 74a58824-2e40-473b-a207-162b2dc22733.gifпревращается в 6a494e5a-2253-43ab-9abf-fa4fae9f9397.gifзначок фильтра . Щелкните этот значок, чтобы изменить или очистить фильтр.

Статьи по теме

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

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

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

Два типа фильтров

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

Повторное применение фильтра

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

74a58824-2e40-473b-a207-162b2dc22733.gifстрелка раскрывающегося списка означает, что фильтрация включена, но не применяется.

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

Кнопка фильтра 6a494e5a-2253-43ab-9abf-fa4fae9f9397.gifозначает, что фильтр применен.

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

При повторном применении фильтра выводятся различные результаты по следующим причинам.

Данные были добавлены, изменены или удалены в диапазон ячеек или столбец таблицы.

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

Не используйте смешанные типы данных

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

Фильтрация данных в таблице

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

Выделите данные, которые нужно отфильтровать. На вкладке Главная нажмите кнопку Форматировать как таблицу и выберите команду Форматировать как таблицу.

ac614706-1e55-4552-88d5-9e80036c0828.png

В диалоговом окне Создание таблицы вы можете выбрать, есть ли в таблице заголовки.

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

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

e5f36df7-3eda-4685-8dea-eea5077a4f04.png

Нажмите кнопку ОК .

Чтобы применить фильтр, щелкните стрелку в заголовке столбца и выберите параметр фильтрации.

Фильтрация диапазона данных

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

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

На вкладке ” данные ” нажмите кнопку ” Фильтр“.

Параметры фильтрации для таблиц и диапазонов

Можно применить общий фильтр, выбрав пункт Фильтр, или настраиваемый фильтр, зависящий от типа данных. Например, при фильтрации чисел отображается пункт Числовые фильтры, для дат отображается пункт Фильтры по дате, а для текста — Текстовые фильтры. Применяя общий фильтр, вы можете выбрать для отображения нужные данные из списка существующих, как показано на рисунке:

e534652d-6ff1-4e57-b1c2-91d358f093b1.png

Выбрав параметр Числовые фильтры вы можете применить один из перечисленных ниже настраиваемых фильтров.

7ca07827-4ad6-4f22-b4f0-deaaf94befd6.png

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

975ab25f-e3d7-4525-a677-87d177132a3b.png

Вот как это сделать.

Щелкните стрелку фильтра в ячейке со словом “Март”, выберите пункт Числовые фильтры и условие Меньше и введите значение 6000.

a0a8efb4-1f33-44d1-8385-74d3fff5d897.png

Нажмите кнопку ОК.

Excel в Интернете применяет фильтр и отображает только регионы с продажами ниже $6000.

694f17e5-b956-435a-8706-01301b8a42f4.png

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

Удаление фильтра из столбца

Нажмите кнопку фильтр 6a494e5a-2253-43ab-9abf-fa4fae9f9397.gifрядом с заголовком столбца, а затем выберите команду очистить фильтр из .

Удаление всех фильтров из таблицы или диапазона

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

Будут удалены все фильтры из всех столбцов в таблице или диапазоне и отображаются все данные.

Как задать несколько параметров

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

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

Теперь необходимо дополнительно отделить товары стоимостью 100 рублей, для этого применяем отсев в разделе «Цена».

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

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

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

Расширенный фильтр – это как раз пример такого инструмента. Особенно если его немного усовершенствовать.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: support.office.com

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

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

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

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

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

И еще немного о фильтрах

Есть еще один способ фильтрации данных. Немного преобразуем нашу таблицу:

image26.png

Как видите, мы ее раскрасили.

Теперь, например, нам необходимо найти все строки с Красоткиным. Щелкните правой кнопкой на ячейке с данным человеком и в появившемся меню выберите пункт «Фильтр». В новом меню есть несколько новых опций. В данном примере нам нужен пункт «Фильтр по значению…».

image27.png

Если выбрать условие «Фильтр по цвету…», то в таблице останутся строки с ячейками того же цвета, что и активная ячейка (желтая заливка).

Если щелкнуть на «Фильтр по цвету шрифта…», то в нашей таблице останутся только ячейки с красным или черным шрифтом, в зависимости от того, какая ячейка активна в данный момент.

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

Администрация сайта CADELTA.RU выражает благодарность автору Александр Царев за подготовку материала.

Проблемы, которые приводят к существенной потере функциональности

При сортировке листа книги использовано более трех условий сортировки. В более ранних версиях Excel эта информация будет потеряна.

Что это означает. В Excel 2007 или более поздней версии можно применить режимы сортировки с условиями сортировки до 64 для сортировки данных, но в Excel 97-2003 поддерживаются только три условия сортировки. Чтобы исключить потерю сведений об условиях сортировки в Excel 97–2003, может потребоваться сократить количество условий сортировки до трех. В Excel 97–2003 данные также можно сортировать вручную.

Однако все сведения о состоянии сортировки остаются доступными в книге и применяются при повторном открытии книги в Excel 2007 или более поздней версии, если только сведения о состоянии сортировки не редактируются в Excel 97-2003.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти данные, отсортированные с использованием более трех условий, и сократите число условий сортировки до трех или менее.

При сортировке листа книги использовано условие сортировки с настраиваемым списком. В более ранних версиях Excel эта информация будет потеряна.

Что это означает. В Excel 2007 или более поздней версии можно выполнять сортировку по настраиваемому списку. Для получения таких же результатов сортировки в Excel 97–2003 можно сгруппировать нужные данные, а затем отсортировать их вручную.

Однако все сведения о состоянии сортировки остаются доступными в книге и применяются при повторном открытии книги в Excel 2007 или более поздней версии, если только сведения о состоянии сортировки не редактируются в Excel 97-2003.

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

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

Что это означает. В Excel 2007 или более поздней версии можно сортировать данные по определенному формату, такому как цвет ячейки, цвет шрифта или наборы значков. В Excel 97–2003 сортируется только текст.

Однако все сведения о состоянии сортировки остаются доступными в книге и применяются при повторном открытии книги в Excel 2007 или более поздней версии, если только сведения о состоянии сортировки не редактируются в Excel 97-2003.

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

Как установить фильтр в Excel — видео

Microsoft Excel — это повсеместно распространенный и удобный инструмент для работы с электронными таблицами.

Широкие функциональнее возможности делают эту программу второй по популярности после MS Word среди всех офисных программ.

Используют её экономисты, бухгалтера, ученые, студенты и представители других профессий, которым необходимо обрабатывать математические данные.

Одной из самых удобных функций в этой программе является фильтрация данных. Рассмотрим, как настроить и использовать MS excel фильтры.

a96262203476366698e0c9b9bec38eb7.jpg

Наложение нескольких фильтров на таблицу Эксель

Фильтры в таблице Эксель можно комбинировать. Наложение нескольких фильтров происходит по принципу логического «И». Что это значит, давайте посмотрим на примере.

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

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

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

Теперь посмотрите видеоролик, в котором я показываю реальный пример фильтрации данных в прайс листе более чем из 15 000 строк.

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

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

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

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

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

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

cdf2ba334ad2ee1c691038af933442dc.jpg

Как сделать фильтр в Excel по строкам?

Стандартными способами – никак. Программа Microsoft Excel отбирает данные только в столбцах. Поэтому нужно искать другие решения.

Приводим примеры строковых критериев расширенного фильтра в Excel:

  1. Преобразовать таблицу. Например, из трех строк сделать список из трех столбцов и к преобразованному варианту применить фильтрацию.Отгрузки за 2014.
  2. Использовать формулы для отображения именно тех данных в строке, которые нужны. Например, сделать какой-то показатель выпадающим списком. А в соседнюю ячейку ввести формулу, используя функцию ЕСЛИ. Когда из выпадающего списка выбирается определенное значение, рядом появляется его параметр.

Чтобы привести пример как работает фильтр по строкам в Excel, создадим табличку:

Пример4.

Для списка товаров создадим выпадающий список:

Выпадающий список.

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

Формула.

Рядом с выпадающим списком ячейку введем следующую формулу: Ее задача – выбирать из таблицы те значения, которые соответствуют определенному товару

Формула2.Результат5.

Скачать примеры расширенного фильтра

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

Функция «Фильтр»

24 сентября 2018 года была представлена новая функция, которая также умеет фильтровать данные. Появилась она совместно с инновационным обновлением, которое добавило динамические массивы в Excel. Эта функция, аналогично шести остальным, доступна тем пользователям, которые пользуются последней версией Microsoft Office 365.

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

Синтаксис функции следующий.

=ФИЛЬТР(массив; включить; [если_пусто])

Разберем основные аргументы:

  1. Массив. Это диапазон (как представленный в виде ссылки, так и именованный), который будет использоваться для фильтрации данных.
  2. Включить. Это массив переменных, имеющих такую же ширину и высоту, как и массив. Простыми словами, это условия, при которых должны отображаться конкретные значения.
  3. Если_пусто. Это дополнительный аргумент, который задает значение, которое возвращается при обнаружении, что все значения включенного массива пустые. 

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

  1. Она задействует динамические массивы. Учитывая это, результат всегда будет касаться и смежных ячеек. 
  2. Если необходимо задать фильтру несколько условий, необходимо использовать знак умножения во втором аргументе, а сами условия взять в скобки.
  3. Для использования оператора ИЛИ нужно использовать знак суммирования вместо знака умножения, а сами критерии также взять в скобки.
  4. Если окажется, что в ячейке, в которую будет возвращаться значение, уже есть какие-то значения, то появится ошибка #ПЕРЕНОС! Чтобы избавиться от нее, нужно очистить данные, содержащиеся в соответствующей ячейке.

Таким образом, тот сложный функционал расширенного фильтра можно реализовать с помощью одной простой функции Excel. Главное – немного потренироваться и иметь последнюю версию Microsoft Office.

Подведём итоги

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

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

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

Уникальная метка публикации: 70827467-CA45-0D8B-92CB-220C2578B456
Источник: //artemvm.info/information/uchebnye-stati/microsoft-office/kak-sdelat-filtr-v-excel/

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

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

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

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

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

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

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

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

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

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

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...