Горизонтальная фильтрация столбцов в Excel
Если вы не совсем начинающий пользователь, то, должно быть, уже заметили, что на 99% всё в Excel устроено для работы именно с вертикальными таблицами, где по столбцам идут параметры или атрибуты (поля), а в строчках располагается информация об объектах или событиях. Сводные таблицы, промежуточные итоги, копирование формул двойным щелчком — всё заточено именно под такой формат данных.
Однако же, нет правил без исключений и с достаточно регулярной периодичностью меня спрашивают что делать если в работе попалась таблица с горизонтальной смысловой ориентацией, либо таблица, где строки и столбцы имеют одинаковый по смыслу вес:
И если сортировать по горизонтали Excel ещё умеет (командой Данные — Сортировка — Параметры — Сортировать столбцы), то с фильтрацией всё обстоит хуже — встроенных инструментов для фильтрации столбцов, а не строк в Excel просто нет. Так что, если перед вами встала такая задача, придется придумывать обходные пути разной степени сложности.
Способ 1. Новая функция ФИЛЬТР
Если вы работаете на новой версии Excel 2021 или в подписке Excel 365, то можно воспользоваться недавно появившейся функцией ФИЛЬТР (FILTER) , умеющей фильтровать исходные данные не только по строчкам, но и по столбцам. Для работы эта функция требует вспомогательный горизонтальный одномерный массив-строку, где каждое значение (ИСТИНА или ЛОЖЬ) определяет показываем ли мы или, наоборот, скрываем очередной столбец в таблице.
Добавим такую строку над нашей таблицей и пропишем в ней статус каждого столбца:
- Допустим, мы всегда хотим отображать первый и последний столбцы (заголовки и итоги), поэтому для них в первой и последней ячейках массива зададим значение =ИСТИНА.
- Для остальных столбцов содержимое соответствующих ячеек будет формулой, которая проверяет нужное нам условие с помощью функций И (AND) или ИЛИ (OR) . Например, что итог находится в интервале от 300 до 500.
После этого останется лишь использовать функцию ФИЛЬТР для отбора столбцов, над которыми в нашем вспомогательном массиве есть значение ИСТИНА:
Аналогичным образом можно отфильтровать столбцы и по заданному списку. В этом случае поможет функция СЧЁТЕСЛИ (COUNTIF) , проверяющая количество вхождений очередного названия столбца из шапки таблицы в разрешённый список:
Способ 2. Сводная таблица вместо обычной
На текущий момент встроенная горизонтальная фильтрация по столбцам есть в Excel только в сводных таблицах, поэтому если нам удастся преобразовать нашу исходную таблицу в сводную, то можно будет воспользоваться этим встроенным функционалом. Для этого наша исходная таблица должна удовлетворять следующим условиям:
- иметь «правильную» однострочную строку заголовка без пустых и объединенных ячеек — иначе не получится построить сводную таблицу;
- не содержать дубликатов в подписях строк и столбцов — они «схлопнутся» в сводной в список только уникальных значений;
- содержать только числа в области значений (на пересечении строк и столбцов), т.к. сводная таблица обязательно применит к ним какую-то агрегирующую функцию (сумму, среднее и т.д.) и с текстом это не сработает
Если все эти условия выполняются, то для построения сводной, внешне похожей на нашу исходную таблицу, её (исходную) нужно будет развернуть из кросс-таблицы в плоскую (нормализовать). А это проще всего сделать с помощью надстройки Power Query — мощного инструмента преобразования любых данных, встроенного в Excel начиная с 2016-й версии.
- Преобразуем таблицу в «умную» динамическую командой Главная — Форматировать как таблицу (Home — Format as Table) .
- Загружаем в Power Query командой Данные — Из таблицы/диапазона (Data — From Table/Range).
- Фильтруем строку с итогами (в сводной будут свои итоги).
- Щёлкаем правой кнопкой мыши по заголовку первого столбца и выбираем Отменить свёртывание других столбцов (Unpivot Other Columns) . Все невыделенные столбцы преобразуются в два — имя сотрудника и значение его показателя.
- Фильтруем столбец с итогами, который ушёл в колонку Атрибут.
- Строим сводную таблицу по получившейся плоской (нормализованной) таблице командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close & Load — Close & Load to. ) .
Теперь можно пользоваться имеющейся в сводных таблицах возможностью фильтрации столбцов — привычными галочками напротив имён и пунктами Фильтры по подписи (Label Filters) или Фильтры по значению (Value Filters) :
И само собой при изменении данных нужно будет обновить наш запрос и сводную сочетанием клавиш Ctrl + Alt + F5 или командой Данные — Обновить всё (Data — Refresh All) .
Способ 3. Макрос на VBA
Все предыдущие способы, как легко заметить, представляют собой не совсем фильтрацию — мы не скрываем столбцы в исходном списке, а формируем новую таблицу с заданным набором столбцов из исходной. Если же требуется именно фильтровать (скрывать) столбцы в исходных данных, то нужен принципиально другой подход, а именно — макрос.
Предположим, что мы хотим «на лету» фильтровать столбцы, где имя менеджера в шапке таблицы удовлетворяет заданной в жёлтой ячейке А4 маске, например, начинается с буквы «А» (то бишь получить в результате «Анна» и «Артур»).
Как и в первом способе, сначала реализуем вспомогательный диапазон-строку, где в каждой ячейке формулой будет проверяться наш критерий и выводиться логические значения ИСТИНА или ЛОЖЬ для видимых и скрытых столбцов соответственно:
Затем добавим простой макрос. Щёлкните правой кнопкой мыши по ярлычку листа и выберите команду Исходный код (Source code) . В открывшееся окно скопируйте и вставьте следующий VBA-код:
Фильтр в excel по горизонтали
Программа Microsoft Excel: сортировка и фильтрация данных
Смотрите также (даже если уП тут удобство?» - аналог автофильтра по все содержимое, не. Скопированные строки будут. условий, включающий подписи названия нужных столбцов листа, установите переключательв группезапись наборе около параметра «Таблица просто нажать сочетание какому именно типуДля удобства работы с вас русский Excelетро спросите вы и
столбцу. соответствующее критерию. В
Простая сортировка данных
включать только теИспользуя пример, получим следующий столбцов условий. В в первую строкускопировать результат в другоеСортировка и фильтр — это текст или(Продавец = «Егоров» и с заголовками» стояла клавиш Ctrl+Shift+L.
контента будет производиться большим массивом данных и региональные настройки).в будете правы. МалоФайлик обновил. нашем примере только столбцы, названия которых отфильтрованный результат для данном примере следует области вставки результатов. местонажмите
значение, которое требуется продажи > 3000) галочка. Далее, простоКак видим, в ячейках сортировка. Существует четыре в таблицах, ихУсловия записанные в разныхи т.д. того, что нужноЕсли нужно, обновлю ноутбуки и планшеты были скопированы. диапазона списка:
указать диапазон $A$1:$D$3. Выполняя фильтрацию, введите, перейдите в полеДополнительно
найти. Например: ИЛИ нажать на кнопку с наименованием всех варианта: постоянно нужно упорядочивать
руками вводить условия первое сообщение. остались видимыми.Введите в полеТипЧтобы переместить убрать диалоговое ссылку на скопированныеПоместить результат в диапазон.Введенный текст(Продавец = «Грачев» «OK».
Настраиваемая сортировка
столбцов появился значокЗначения; по определенному критерию. одной строке -слова начинающиеся с в желтые ячейки,_Boroda_Фильтрацию можно также применить,
Диапазон условийПродавец окно названия столбцов ви щелкните верхнююВыполните одно из следующихExcel оценивает и отображает и продажи >Если вы решили воспользоваться в виде квадрата,Цвет ячейки; Кроме того, для считаются связанными междуА так еще и
: Ага. И как выбрав командуссылку на диапазонПродажиРасширенный фильтр поле левую ячейку области, действий. как
1500) вторым способом, тогда в который вписанЦвет шрифта; выполнения конкретных целей, собой логическим оператороми содержащие далее открывать диалоговое окно, тогда будет выглядетьСортировка и фильтр условий, включающий подписи
- фрукты
- временно, пока вы
- Поместить результат в диапазон
- в которую требуется
Чтобы показать результат фильтрации,=»=Белова»Условия с подстановочными знаками тоже нужно выделить перевернутый вниз треугольник.Значок ячейки. иногда весь массивИ (AND)
С вводить туда диапазоны, мое?на вкладке столбцов условий. ВКротов выбираете диапазон условий,. Скопированные строки будут
вставить строки. скрыв ненужные строки,=БеловаПродавец = имя со второй всю область таблицы,Кликаем по этому значкуНо, в подавляющем большинстве
данных не нужен,:, т.е. жатьСмотрите файл, такГлавная данном примере следует6 328 р. нажмите кнопку включать только теСовет. установите переключатель=»=3000″ буквой «г» но на этот в том столбце, случаев, используется пункт
а только отдельныеТ.е. фильтруй мне бананыАОК нужно?. указать диапазон $A$1:$B$3.ФруктыСвернуть диалоговое окно
столбцы, названия которых При копировании отфильтрованных строкфильтровать список на месте=3000Между командой раз перейти во по данным которого
«Значения». Он и строки. Поэтому, чтобы именно в третьемпель. Грустно, согласен! НоПервая строкаФильтры в Excel могутЧтобы переместить убрать диалоговоеБелова
. были скопированы. в другое место.При фильтрации текстовых данныхДополнительно
вкладку «Вставка». Находясь мы собираемся проводить выставлен по умолчанию. не путаться в квартале, именно пос
«все меняется, когда200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(СЧЁТЕСЛИ($A12:A12;$C10)>=СЧЁТЕСЛИ($B2:$H2;$C10);»»;$C10) суммироваться. Это означает,
окно6 544 р.Используя пример, получаем следующийВведите в поле можно указать, какиеЧтобы скопировать отфильтрованные строки в Excel неи командой тут, на ленте фильтрацию. В нашем
Фильтр
В нашем случае, огромном количестве информации, Москве и приин приходят они ©»Остальные (формула массива, что Вы можетеРасширенный фильтрЛогическое выражение: отфильтрованный результат дляДиапазон условий столбцы следует включить
в другую область учитывается регистр букв.Фильтр в блоке инструментов случае, мы решили мы тоже будем рациональным решением будет этом из «Ашана»., — макросы! вводится одновременным нажатием применить несколько фильтроввременно, пока вы Продавец = имя со диапазона списка:
ссылку на диапазон в операцию копирования. листа, установите переключатель Однако для поискаесть несколько важных «Таблицы», следует нажать
провести фильтрацию по использовать именно этот упорядочить данные, иЕсли нужно связать условияАРаботу с расширенным фильтром Контрл Шифт Ентер) к одной таблице, выбираете диапазон условий, второй буквой «р»Тип условий, включающий подписи Перед фильтрацией скопируйте
скопировать результат в другое с учетом регистра
отличий. на кнопку «Таблица». имени. Например, нам пункт.
отфильтровать от других логическим операторомнана можно в разыКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(B$12=»»;»»;ИНДЕКС($A3:$H3;НАИМЕНЬШИЙ(ЕСЛИ($B$2:$H$2=B$12;СТОЛБЕЦ($B$2:$H$2));СТОЛБЕЦ(A13))))&»» чтобы сузить результаты нажмите кнопкуЧтобы найти текстовые значения,Продавец столбцов условий. В нужные названия столбцов место можно воспользоваться формулой.Вместо меню «Автофильтр» отображаетсяПосле этого, как и нужно оставить данные
В графе «Порядок» нам результатов. Давайте выясним,
ИЛИ (OR)с ускорить и упроститьНе оптимизировал, уже фильтрации. В прошломСвернуть диалоговое окно в которых совпадаютПродажи данном примере следует в первую строку, перейдите в поле
Пример см. в диалоговое окно в прошлый раз, только работника Николаева. нужно указать, в как производится сортировка, то их надо,
с помощью простого домой хочу с примере мы уже. одни знаки иМясо указать диапазон $A$1:$B$3. области, в которуюПоместить результат в диапазон
разделе Условия сРасширенный фильтр откроется окно, где Поэтому, снимаем галочки каком порядке будут и фильтрация данных просто вводить вАс макроса, который будет работы. отфильтровали таблицу, отобразивИспользуя пример, получаем следующий не совпадают другие,БеловаЧтобы переместить убрать диалоговое вы собираетесь вставить
и щелкните верхнюю подстановочными знаками.
Умная таблица
. можно скорректировать координаты с имен всех располагаться данные: «От в программе Microsoft разные строки. Например,
аи автоматически запускать расширенный3377160 только ноутбуки и отфильтрованный результат для выполните одно или450 р. окно отфильтрованные строки. При левую ячейку области,Можно присвоить диапазону имяРасширенные условия вводятся в
размещения таблицы. Жмем остальных работников. А до Я» Excel. если нам нужно
и т.д. фильтр при вводе: _Boroda_, Спасибо, все планшеты. Теперь наша диапазона списка: несколько следующих действий.фруктыРасширенный фильтр применении фильтра введите в которую требуетсяУсловия отдельный диапазон условий на кнопку «OK».Когда процедура выполнена, жмем
или наоборот. ВыбираемСкачать последнюю версию найти все заказы=*с условий, т.е. изменении так!) задача сузить данныеТипДля поиска строк, вКротоввременно, пока вы
ссылку на скопированные вставить строки., и ссылка на на листе надНезависимо от того, каким на кнопку «OK».
значение «От А Excel менеджера Волиной послова оканчивающиеся на любой желтой ячейки.У подавляющего большинства пользователей еще больше иПродавец которых текстовое значение
6 328 р. выбираете диапазон условий, названия столбцов вСовет. диапазон будет автоматически диапазоном ячеек или способом вы воспользуетесьКак видим, в таблице
до Я».Сортировка является одним из
московским персикам иС Щелкните правой кнопкой Excel при слове показать только ноутбукиПродажи в столбце начинаетсяЛогическое выражение: нажмите кнопку поле При копировании отфильтрованных строк
появляться в поле
Фильтрация с применением сложных условий
таблицей, которые требуется при создании «умной остались только строкиИтак, мы настроили сортировку самых удобных инструментов все заказы по=. мыши по ярлычку «фильтрация данных» в и планшеты, отданныеНапитки с определенной последовательности ((Продавец = «Егоров» ИСвернуть диалоговое окноПоместить результат в диапазон в другое местоДиапазон условий отфильтровать. В Microsoft таблицы», в итоге с именем работника по одному из при работе в
луку в третьемвсе ячейки с текстом текущего листа и голове всплывает только на проверку вЕрмолаева знаков, введите эти Продажи >3000) ИЛИ(Продавец.
. Тогда скопированные строки можно указать, какие. Можно также определить Office Excel в получите таблицу, в Николаева. столбцов. Для того,
программе Microsoft Excel.
квартале по Самаре,
выберите команду обычный классический фильтр августе.5 122 р. знаки, не используя
= «Орехов» ИИспользуя пример, получаем следующий
будут включать только столбцы следует включить имя качестве источника расширенных
ячейках шапки которойУсложним задачу, и оставим
чтобы настроить сортировку С помощью неё, то это можно (букв или цифр,Исходный текст (Source Code)
с вкладкиНажмите на кнопку со
Мясо знак равенства ( Продажи > 1500))
отфильтрованный результат для те столбцы, названия в операцию копирования.База_данных
условий используется отдельный уже будет установлены в таблице только
по другому столбцу, можно расположить строчки задать в диапазоне
включая пробелы). В открывшееся окноДанные — Фильтр (Data
стрелкой в столбце,
Общие сведения
Вставьте не менее трех диапазона списка: которых вы скопировали. Перед фильтрацией скопируйтедля фильтруемого диапазона диапазон условий в
значки фильтров, описанные данные, которые относятся жмем на кнопку таблицы в алфавитном
условий следующим образом:=м. н скопируйте и вставьте — Filter) который необходимо отфильтровать.450 р.). Например, если ввести пустых строк надТипВ поле нужные названия столбцов данных и имя диалоговом окне
Образец данных
нами ранее. к Николаеву за «Добавить уровень». порядке, согласно данным,
Если же нужно наложитьвсе ячейки с текстом вот такой код:: В данном случаефрукты в качестве условия исходным диапазоном, чтобыПродавецДиапазон условий в первую строкуИзвлечениеРасширенный фильтрПри нажатии на этот
III квартал 2016Появляется ещё один набор которые находятся в два или более из 8 символов,Private Sub Worksheet_Change(ByValТакой фильтр — штука
мы применим дополнительный
использовать их для
введите ссылку на
области, в которую
значок, будут доступны
полей, который следует
условий на один
Target As Range)
привычная, спору нет,
фильтр к столбцу
Операторы сравнения
6 328 р., Excel найдет строки диапазона условий. Диапазонфрукты диапазон условий, включая
вы собираетесь вставить
которую нужно вставить
Приведенный ниже образец данных
кликаем по значку
заполнить уже для
Сортировку данных в программе
столбец, то можно
If Not Intersect(Target,
и для большинства
D, чтобы просмотреть
Фильтрация данных в Excel Егоров, Егорова и
условий должен включать
названия условий. Используя
отфильтрованные строки. При
строки, и ссылки
используется во всех
Использование знака равенства для ввода текста и значений
в ячейке «Дата». сортировки по другому Microsoft Excel можно просто продублировать заголовоки заканчивающиеся на Range(«A2:I5»)) Is Nothing случаев вполне сойдет. информацию по дате. позволяет отобразить среди Егоренко. в себя названия6 328 р. пример, введите $A$1:$C$2. применении фильтра введите на эти диапазоны процедурах, описанных в при запуске фильтра
В открывшемся списке, столбцу. В нашем выполнять, воспользовавшись кнопкой
столбца в диапазонеН Then On Error Однако бывают ситуации,Появится меню фильтра.
большого объема информации
Используйте подстановочные знаки. столбцов. Убедитесь в
Чтобы переместить убрать диалоговое
ссылку на скопированные
появятся автоматически в
Учет регистра
этой статье. стандартным способом через снимаем галочки с случае, по столбцу «Сортировка и фильтр», критериев и вписать, т.е. Resume Next ActiveSheet.ShowAllData когда нужно проводить
Использование предварительно заданных имен
Установите или снимите флажки только ту, чтоИспользуйте том, что междуБелова окно названия столбцов в поляхДанных включает в себя кнопку «Сортировка и месяцев «Май», «Июнь» «Дата». Так как которая размещена во под него второе,М Range(«A7»).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(«A1»).CurrentRegion отбор по большому с пунктов в Вам в данныйЧтобы найти значениями условий и6 544 р.
Создание условия с помощью формулы
Расширенный фильтр полеИсходный диапазон четыре пустые строки фильтр».
и «Октябрь», так в данных ячеек
вкладке «Главная» на третье и т.д.андари End If End количеству сложных условий
зависимости от данных, момент необходима. Например, ? (вопросительный знак)
исходным диапазоном имеетсяЛогическое выражение:временно, пока выПоместить результат в диапазони выше диапазон списка,Урок: Как создать таблицу как они не установлен формат даты, ленте в блоке
условия. Вот так,н Sub сразу по нескольким которые необходимо отфильтровать, имея перед собойЛюбой символ, по крайней мере ( (Продажи > 6000 выбираете диапазон условий,. Тогда скопированные строкиПоместить результат в диапазон
который будет использоваться в Microsoft Excel относятся к третьему то в поле инструментов «Редактирование». Но, например, можно отобрать
,Эта процедура будет автоматически столбцам. Обычный фильтр
Несколько условий для одного столбца, где любое условие может быть истинным
затем нажмите многотысячный список товаровНапример sm?th будут
одна пустая строка. И Продажи < нажмите кнопку будут включать толькосоответственно. как диапазон условийКак видим, инструменты сортировки кварталу, и жмем «Порядок» мы устанавливаем прежде, нам нужно все сделки сМ запускаться при изменении
тут не оченьOK крупного гипермаркета, Вы найдены слова «строфа»Чтобы найти строки, отвечающие 6500 ) ИЛИСвернуть диалоговое окно те столбцы, названия
В качестве условия отбора
(A1:C4) и диапазон
и фильтрации, при
кликнуть по любой марта по май:ангости любой ячейки на
удобен и хочется. Мы снимем выделение можете выделить из и «строка» нескольким наборам условий, (Продажи < 500).
которых вы скопировали. можно использовать вычисляемое
списка (a6: C10). правильном их использовании,Как видим, остались только А до Я», ячейке того столбца,
В общем и целом,н текущем листе. Если чего-то помощнее. Таким со всех пунктов, него только шампуни* (звездочка) каждый из которых )Используя пример, получаем следующийВ поле
значение, являющееся результатом Диапазон условий содержит могут значительно облегчить нужные нам данные. а «От старых по которому мы после «доработки напильником»и т.д. адрес измененной ячейки инструментом может стать кроме или крема, аЛюбое количество символов содержит условия дляВставьте не менее трех отфильтрованный результат дляДиапазон условий выполнения формулы. Запомните заголовки столбцов и пользователям работу сДля того, чтобы удалить
к новым», или собираемся выполнить сортировку. из расширенного фильтра=*н??а попадает в желтыйрасширенный фильтр (advanced filter)
августа остальное временно скрыть.Например * Восток нескольких столбцов, введите пустых строк над диапазона списка:введите ссылку на следующие важные положения:
включает по крайней таблицами. Особенно актуальным фильтр по конкретному
«От новых к
Например, в предложенной ниже
выходит вполне себе
все слова оканчивающиеся на
диапазон (A2:I5), то
, особенно с небольшой
«Северо-восток» и «Юго-восток»
каждый набор условий
исходным диапазоном, чтобы
Несколько условий для нескольких столбцов, где все условия должны быть истинными
диапазон условий, включаяФормула должна возвращать результат мере одна пустая вопрос их использования
столбцу, и показать старым». таблице следует отсортировать приличный инструмент, местамиА данный макрос снимает «доработкой напильником» (поНовый фильтр будет применен, мы научимся применять
(тильда), за которой в отдельном столбце использовать их дляПродавец
названия условий. Используя ИСТИНА или ЛОЖЬ. строка между значениями становится в том скрытые данные, опятьТаким же образом, в сотрудников по алфавиту.
не хуже классического
все фильтры (если
фильтры к спискам следует ?, * и строке. В диапазона условий. ДиапазонПродажи
пример, введите $A$1:$C$3.Поскольку используется формула, ее условий и диапазона случае, если в кликаем по значку, этом окне можно Становимся в любую
автофильтра. конца буква
они были) иДля начала вставьте над останутся только ноутбуки в Excel, задавать или
данном примере следует условий должен включатьфруктыЧтобы переместить убрать диалоговое необходимо вводить в списка. таблице записан очень расположенному в ячейке настроить, при необходимости, ячейку столбца «Имя»,tmh-service
Н заново применяет расширенный вашей таблицей с и планшеты, которые фильтрацию сразу поВопросительный знак, звездочку или ввести: в себя названияКротов окно обычном режиме, аДля работы с этими большой массив данных. с заглавием данного и сортировку по и жмем на: Уважаемые форумчане!, т.е. фильтр к таблице данными несколько пустых были отданы на
нескольким столбцам, а тильдаТип столбцов. Убедитесь в6 328 р.Расширенный фильтр
не в виде данными, выберите вАвтор: Максим Тютюшев столбца. В раскрывшемся другим столбцам в кнопку «Сортировка иПрошу Вас подсказать, Брус
исходных данных, начинающейся строк и скопируйте проверку в августе.
также удалять фильтры.
Например, ан91
временно, пока вы
выражения наподобие следующего:
таблице ниже, скопируйте
Несколько условий для нескольких столбцов, где хотя бы одно из условий должно быть истинным
меню кликаем по порядке приоритета. Когда фильтр». Чтобы имена
есть ли возможностьн с А7, т.е. туда шапку таблицыПосле применения фильтра раноЕсли Ваша таблица содержит будут найдены словаПродажи значениями условий иБелова выбираете диапазон условий,=»= ее и вставьте
Мы стараемся как пункту «Удалить фильтр все настройки выполнены, упорядочить по алфавиту, установки сразу двухик все будет фильтроваться — это будет или поздно возникнет большой объем данных,
исходным диапазоном имеется
запись его в ячейку можно оперативнее обеспечивать с…». жмем на кнопку
из появившегося списка фильтров по вертикалиа мгновенно, сразу после диапазон с условиями необходимость снять или могут возникнуть затруднения
Вставьте не менее трех>3000
по крайней мереЛогическое выражение:Свернуть диалоговое окно» A1 на новом
вас актуальными справочнымиЕсли же вы хотите «OK». выбираем пункт «Сортировка (столбцам) и горизонтали, ввода очередного условия: (выделен для наглядности удалить его, чтобы при поиске нужной пустых строк над
=»=Кротов» одна пустая строка. (Тип = «Фрукты» ИЛИ.Не используйте подпись столбца листе Excel. материалами на вашем сбросить фильтр вКак видим, теперь в от А до (строкам)?ЗаТак все гораздо лучше, желтым): отфильтровать содержимое другим информации. Фильтры используются исходным диапазоном, чтобы>1500
Чтобы найти строки, отвечающие Продавец = «Кротов»)Используя пример, получаем следующий в качестве подписиТип языке. Эта страница целом по таблице,
нашей таблице все Я».Смотрел ранее схожиен правда? :)Между желтыми ячейками и образом. для того, чтобы
использовать их дляЩелкните ячейку в диапазоне. нескольким наборам условий,
Вставьте не менее трех
отфильтрованный результат для
условия. Либо оставьте
переведена автоматически, поэтому
тогда нужно нажать
данные отсортированы, в
Как видим, все данные
Несколько наборов условий, один столбец во всех наборах
озТеперь, когда все фильтруется исходной таблицей обязательноНажмите на кнопку со сузить количество данных, диапазона условий. Диапазон
В приведенном примере каждый из которых пустых строк над диапазона списка: подпись условия пустой,Продажи ее текст может кнопку «Сортировка и первую очередь, по в таблице разместились, но так иа «на лету», можно
должна быть хотя стрелкой в столбце, отображаемых на листе условий должен включать следует щелкнуть любую содержит условия для исходным диапазоном, чтобыТип либо используйте подпись,
содержать неточности и
фильтр» на ленте,
именам сотрудника, а
согласно алфавитному списку
не смог найтии т.д. немного углубиться в бы одна пустая с которого необходимо
Excel, позволяя видеть в себя названия ячейку в диапазоне одного столбца, используйте использовать их дляПродавец которая не является
Продавец грамматические ошибки. Для
и выбрать пункт затем, по датам фамилий. ответа.>=э
нюансы и разобрать строка. снять фильтр. В только нужную информацию. столбцов. Убедитесь в A6:C10. несколько столбцов с диапазона условий. ДиапазонПродажи подписью столбцов диапазонаПродажи
нас важно, чтобы «Очистить». выплат.Для того, чтобы выполнитьНужно это длявсе слова, начинающиеся с механизмы более сложныхИменно в желтые ячейки нашем примере мыВ следующем примере мы том, что междуНа вкладке одинаковой подписью. В условий должен включатьМясо (в примерах нижеНапитки эта статья была
Если нужно полностью удалитьНо, это ещё не сортировку в обратном оперативного поиска данныхЭ запросов в расширенном нужно ввести критерии
удалим фильтр со применим фильтр к значениями условий иДанные данном примере следует в себя названияБелова — «Среднее арифметическое»
Ермолаева вам полезна. Просим фильтр, то, как
все возможности настраиваемой
порядке, в том
по конкретной номенклатуре
фильтре. Помимо ввода
(условия), по которым
журналу учета эксплуатации
исходным диапазоном имеется
Несколько наборов условий, несколько столбцов в каждом наборе
в группе ввести: столбцов. Убедитесь в450 р. и «Точное соответствие»).
5 122 р. вас уделить пару и при его сортировки. При желании, же меню выбираем (МТР) и конкретномуЮ точных совпадений, в потом будет произведенаПоявится меню фильтра. оборудования, чтобы отобразить по крайней мереСортировка и фильтр
Тип том, что междуфруктыЕсли в формуле вместоМясо секунд и сообщить, запуске, в этом в этом окне кнопку Сортировка от складу.
диапазоне условий можно
фильтрация. Например, если
только ноутбуки и
одна пустая строка.
Продавец значениями условий иКротов относительной ссылки наБелова
помогла ли она же меню следует можно настроить сортировку Я до А».Спасибо!Я использовать различные символы
нужно отобрать бананыУдалить фильтр из столбца…
планшеты, доступные дляВ строках под названиямиДополнительноПродажи исходным диапазоном имеется
6 328 р. ячейку или имени450 р. вам, с помощью выбрать пункт «Фильтр», не по столбцам,Список перестраивается в обратномМаксим Зеленский<>*о* подстановки (* и в московский «Ашан»
В нашем примере проверки. столбцов введите условия,.Продажи по крайней мереФрукты диапазона используется названиефрукты кнопок внизу страницы. или набрать сочетание а по строкам. порядке.: Не проще ливсе слова, не содержащие ?) и знаки в III квартале, мы удалим фильтрВыделите любую ячейку в которым должен соответствоватьВыполните одно из следующих
>6000 одна пустая строка.Белова столбца, в ячейкеКротов Для удобства также клавиш на клавиатуре Для этого, кликаемНужно отметить, что подобный оперативно в 1С букву математических неравенств для то условия будут из столбца
таблице, например, ячейку результат. Используя пример, действий.
Щелкните ячейку в диапазоне
Для поиска строк, отвечающих
с условием будет
приводим ссылку на
по кнопке «Параметры».
вид сортировки указывается
Условия с подстановочными знаками
менять условие отбораО реализации приблизительного поиска.
выглядеть так:Отдано на проверку A2. введите:Чтобы показать результат фильтрации, списка. Используя пример,
нескольким условиям дляЛогическое выражение: выведено значение ошибкиФрукты оригинал (на английскомКроме того, следует заметить,В открывшемся окне параметров только при текстовом в отчете?<>*вич Регистр символов ролиЧтобы выполнить фильтрацию выделите.Чтобы фильтрация в Excel
скрыв ненужные строки,
щелкните любую ячейку
нескольких столбцов, когда
(Тип = «Фрукты» И
#ИМЯ? или #ЗНАЧ!Белова языке) .
сортировки, переводим переключатель
формате данных. Например,Горизонтальный фильтр в
все слова, кроме заканчивающихся не играет. Для любую ячейку диапазона
Фильтр будет удален, а работала корректно, лист
Продавец установите переключатель в диапазоне списка
истинным может быть Продажи > 1 Эту ошибку можно6 544 р.Если требуется отфильтровать данные как мы включили из позиции «Строки при числовом формате Excel — только на наглядности я свел с исходными данными, скрытые ранее данные
должен содержать строкуПродажифильтровать список на месте A6:C10. любое из условий,
не исправлять, поскольку
Операторы сравнения используются для
с помощью сложных
функцию «Фильтр», то
диапазона» в позицию указывается сортировка «От макросом.вич все возможные варианты
откройте вкладку вновь отобразятся на заголовка, которая используется=»=Мя*».На вкладке введите условия в
Вставьте не менее трех она не повлияет
сравнения двух значений. условий (например, Тип при нажатии на «Столбцы диапазона». Жмем минимального к максимальному»
А что должно(например, фильтр женщин в таблицу:Данные листе Excel. для задания имени=»=?г*»Чтобы скопировать отфильтрованные строкиДанные разные столбцы и пустых строк над
на результаты фильтрации. Результатом сравнения является = «Фрукты» ИЛИ соответствующий значок в на кнопку «OK». (и, наоборот), а фильтроваться, столбец с по отчеству)Критерийи нажмите кнопкуЧтобы удалить все фильтры каждого столбца. ВЩелкните ячейку в диапазоне в другую областьв группе строки диапазона условий. исходным диапазоном, чтобыВ формуле, которая применяется
логическое значение: ИСТИНА либо Продавец = «Белова»), ячейках шапки таблицы,Теперь, по аналогии с при формате даты начальным остатком, с=
РезультатДополнительно (Data — Advanced) в таблице Excel, следующем примере данные списка. Используя пример, листа, установите переключательСортировка и фильтр В данном примере
использовать их для в качестве условия, ЛОЖЬ.
можно использовать диалоговое
в появившемся меню
предыдущим примером, можно
конечным, или приход,
все пустые ячейки
. В открывшемся окне
на листе организованы
щелкните любую ячейку
скопировать результат в другое
Фильтр в Excel – основные сведения
нажмите следует ввести: диапазона условий. Диапазон для ссылки наОператор сравнения окно становятся доступны функции вписывать данные для к новым» (и, количество или стоимость?<>все ячейки начинающиеся с должен быть ужеФильтр в виде столбцов в диапазоне списка местоДополнительноТип условий должен включать
соответствующую ячейку вЗначениеРасширенный фильтр сортировки, о которых сортировки. Вводим данные, наоборот). У вас навсе непустые ячейкиГр автоматически введен диапазон
Применение фильтра в Excel
на вкладке с заголовками в A6:C10., перейдите в поле.Продавец в себя названия
- первой строке необходимоПример.
мы говорили выше: и жмем наНо, как видим, при 1 склад приходится>=5000, т.е. с данными иДанные строке 1: IDНа вкладкеПоместить результат в диапазонВыполните одно из следующихПродажи
- столбцов. Убедитесь в использовать относительную ссылку.= (знак равенства)Чтобы открыть диалоговое окно «Сортировка от А
- кнопку «OK». указанных видах сортировки 8 столбцов.
- все ячейки со значениемГр нам останется только. #, Тип, ОписаниеДанныеи щелкните верхнюю действий.
- =»=Фрукты»
- том, что междуВсе другие ссылки вРавноРасширенный фильтр до Я», «Сортировка
- Как видим, после этого, по одному значению,Если нужно просто больше или равноуша указать диапазон условий,Автор: Антон Андронов оборудования и т.д.в группе левую ячейку области,Чтобы показать результат фильтрации,=»=Грачев»
- значениями условий и формуле должны бытьA1=B1, выберите пункты от Я до столбцы поменялись местами, данные, содержащие имена
получать конкретное значение 5000, т.е. A1:I2:3377160Откройте вкладку
Применение нескольких фильтров в Excel
Сортировка и фильтр в которую требуется скрыв ненужные строки,Щелкните ячейку в диапазоне исходным диапазоном имеется абсолютными.> (знак больше)Данные А», и «Сортировка согласно введенным параметрам. одного и того по 2-м критериям5 или =5ГрОбратите внимание, что диапазон: Не нашел подобногоДанные
- нажмите вставить строки. установите переключатель списка. Используя пример, по крайней мереЛогическое выражение:Больше >
- по цвету».
- Конечно, для нашей таблицы, же человека, выстраиваются (склад/номенклатура) — можновсе ячейки со значениемейпфрут условий нельзя выделять вопроса, хотя думал, затем нажмите командуДополнительноСовет.фильтровать список на месте
- щелкните любую ячейку одна пустая строка. (Продавец = «Егоров» ИЛИA1>B1ДополнительноУрок: Как использовать автофильтр
Снятие фильтра в Excel
взятой для примера, внутри диапазона в решить формулой с 5, «с запасом», т.е.
- кто-то уже такимФильтр. При копировании отфильтрованных строк. в диапазоне спискаЧтобы найти строки, отвечающие
- Продавец = «Орехов»)
- < (знак меньше). в Microsoft Excel применение сортировки с произвольном порядке. функциями ГПР +>=3/18/2013
- Гр нельзя выделять лишние задавался..
Выполните одно из следующих в другое местоЧтобы скопировать отфильтрованные строки A6:C10. нескольким условиям вВставьте не менее трехМеньше
Автофильтр по горизонтали (Макросы/Sub)
Сортировку и фильтр можно изменением места расположенияА, что делать, если ПОИСКПОЗ или нескольковсе ячейки с датой
анат пустые желтые строки,Есть таблица, необходимоВ заголовках каждого столбца действий.
можно указать, какие в другую областьНа вкладке
нескольких столбцах, введите пустых строк надA1Пример также активировать, превратив столбцов не несет мы хотим отсортировать формул ИНДЕКС + позже 18 мартаи т.д.
т.к. пустая ячейка отфильтровать(ну или вернее появятся кнопки соЧтобы показать результат фильтрации, столбцы следует включить листа, установите переключательДанные
все условия в исходным диапазоном, чтобы>= (знак больше илиОбщие сведения область данных, с особенной пользы, но имена по алфавиту, ПОИСКПОЗ.
2013 (включительно)
=лук в диапазоне условий
будет вывести) значения стрелкой. скрыв ненужные строки, в операцию копирования.
скопировать результат в другоев группе
одной строке диапазона
использовать их для
равно)Несколько условий для одного которой вы работаете,
для некоторых других
но например, приПолосатый жираф аликТонкие моменты:
все ячейки именно и воспринимается Excel как с сортировкой по
Расширенный фильтр и немного магии
Нажмите на такую кнопку установите переключатель Перед фильтрацией скопируйте местоСортировка и фильтр условий. В этом диапазона условий. ДиапазонБольше или равно столбца, где хотя
в так называемую таблиц подобный вид совпадении имени сделать: А ну-ка, поподробней. Знак * подразумевает под только со словом отсутствие критерия, а строке A горизонтально. в столбце, которыйфильтровать список на месте нужные названия столбцов, перейдите в поленажмите примере следует ввести: условий должен включатьA1>=B1 бы одно из «умную таблицу».
Основа
сортировки может быть так, чтобы данныеAir79vs собой любое количествоЛук целая пустая строкаПомоги плиз, или необходимо отфильтровать. В.
в первую строкуПоместить результат в диапазонДополнительноТип в себя названия
Меньше или равно условий должно бытьСуществует два способа создания очень уместным. располагались по дате?: Поставить фильтр, выбрать любых символов, а, т.е. точное совпадение — как просьба укажите где такое
нашем случае мыЧтобы скопировать отфильтрованные строки области, в которуюи щелкните верхнюю.Продавец столбцов. Убедитесь вA1 истинным «умной таблицы». ДляКроме того, в программе Для этого, а «да». ? — один
*лив* или *лив вывести все данные уже было. применим фильтр к в другую область вы собираетесь вставить левую ячейку области,Выполните одно из следующихПродажи том, что между<> (знак не равно)Продавец = «Белова» ИЛИ того, чтобы воспользоваться
Microsoft Excel существует также для использованияAbram pupkin любой символ.ячейки содержащие без разбора._Boroda_ столбцу В, чтобы листа, установите переключатель отфильтрованные строки. При в которую требуется действий.=»=Фрукты» значениями условий иНе равно Продавец = «Кротов» первым из них, функция фильтра данных. некоторых других возможностей,: Елена, трактовка иЛогика в обработке текстовых
Добавляем макрос
ливПереключатель: Вам вручную? Тогда увидеть только нужныескопировать результат в другое применении фильтра введите вставить строки.Чтобы показать результат фильтрации,>1000 исходным диапазоном имеетсяA1<>B1Несколько условий для нескольких выделяем всю область Она позволяет оставить все в том смысл вашего вопроса
и числовых запросовкак подстроку, т.е.Скопировать результат в другое так можно - типы оборудования. место ссылку на скопированныеСовет: скрыв ненужные строки,Щелкните ячейку в диапазоне. по крайней мереПоскольку при вводе текста столбцов, где все таблицы, и, находясь видимыми только те же меню «Сортировка постоянно меняются («. в
немного разная. Так,О место выделяете В2:Н6 -Появится меню фильтра., перейдите в поле названия столбцов в При копировании отобранных строк установите переключатель
В приведенном примере одна пустая строка. или значения в условия должны быть во вкладке «Главная», данные, которые вы и фильтр», нам определенной строке найти например, ячейка условияливпозволит фильтровать список Данные — СортировкаСнимите флажокПоместить результат в диапазон поле в другое местофильтровать список на месте
следует щелкнуть любуюЧтобы найти строки, отвечающие
Реализация сложных запросов
ячейку знак равенства истинными кликаем по кнопке посчитаете нужными, а нужно перейти в все. «, «. отфильтровать с числом 5ки не прямо тут — Параметры -Выделить всеи щелкните верхнююПоместить результат в диапазон можно указать столбцы,. ячейку в диапазоне нескольким условиям для (Тип = «Фрукты» И
| на ленте «Форматировать | остальные скрыть. При |
| пункт «Настраиваемая сортировка…». | все строки. « не означает поиск, же, на этом Столбца — ОК, чтобы быстро снять левую ячейку области,. Тогда скопированные строки включаемые в копию.Чтобы скопировать отфильтрованные строки A6:C10. одного столбца, введите |
| = | Продажи > 1000 как таблицу». Данная необходимости, скрытые данныеПосле этого, открывается окно |
| ) Самым оптимальным | всех чисел, начинающихсяЛив листе (как обычным — Сортировать по выделение со всех в которую требуется будут включать только Перед отбором скопируйте в другую областьНа вкладке условия непосредственно одно) используется для обозначенияНесколько условий для нескольких |
| кнопка находится в | всегда можно будет настроек сортировки. Если вариантом будет КОНКРЕТНЫЙ с пяти, ноер фильтром), а выгрузить — Строка2 - пунктов. вставить строки. те столбцы, названия названия нужных столбцов листа, установите переключательДанные |
| под другим в | формулы, Excel вычисляет столбцов, где хотя блоке инструментов «Стили». вернуть в видимый в вашей таблице ПРИМЕР » Так ячейка условия с, отобранные строки в ОКУстановите флажки для техСовет: которых вы скопировали. в первую строкускопировать результат в другоев группе разных строках диапазона |
| введенную формулу; однако | бы одно изДалее, выбираем один из |
| режим. | есть заголовки, то есть » и буквой Б равносильнаЗа |
| другой диапазон, который | 3377160 типов оборудования, которые При копировании отобранных строкВ поле области вставки результатов. местоСортировка и фильтр условий. В данном это может привести условий должно быть понравившихся стилей, вЧтобы воспользоваться данной функцией, обратите внимание, чтобы » Так хочу». Б*, т.е. будет |
| лив | тогда нужно будет: _Boroda_, спасибо Вам, необходимо оставить в в другое местодиапазон условий отбора Выполняя фильтрацию, введите, перейдите в поленажмите примере введите следующее: к неожиданным результатам истинным открывшемся перечне. На становимся на любую в данном окнеБольшая вероятность что искать любой текст, |
| и т.д. | указать в поле такое есть) Моя таблице, затем нажмите можно указать столбцы,введите ссылку на ссылку на скопированные |
| Поместить результат в диапазон | ДополнительноТип фильтрации. Чтобы указать |
| Тип = «Фрукты» ИЛИ | функционал таблицы выбор ячейку в таблице обязательно стояла галочка вам надо подсчитать начинающийся с буквы |
| =п*в | Поместить результат в диапазон |
| вина, я пока | OK |
| включаемые в копию. | диапазон условий, включая названия столбцов ви щелкните верхнюю |
| . | Продавец оператор сравнения для |
| Продавец = «Кротов» | влиять не будет. (а желательно в около параметра «Мои |
- Б.слова начинающиеся с. В данном случае писал сообщения в. В нашем примере
- Перед отбором скопируйте заголовки столбцов условий. поле левую ячейку области,Выполните одно из следующихПродажи текста или значения,Несколько наборов условий, одинПосле этого, открывается диалоговое шапке), опять жмем данные содержат заголовки».это можно сделатьЕсли текстовый запрос неП
- мы эту функцию теме, сам уже мы выберем названия нужных столбцов Используя пример, введите
- Поместить результат в диапазон в которую требуется действий.=»=Егоров» введите условие в столбец во всех
Логические связки И-ИЛИ
окно, в котором на кнопку «СортировкаВ поле «Столбец» указываем так начинается со знакаи заканчивающиеся на не используем, оставляем
запутался)Ноутбуки в первую строку $A$1:$C$3.To убрать переместить. Скопированные строки будут
вставить строки.Чтобы показать результат фильтрации,=»=Грачев» виде строкового выражения наборах можно изменить координаты и фильтр» в наименование столбца, по=СЧЁТЕСЛИ (C3:I4;»ДА») =, то вВФильтровать список на местеВопрос мой, каки области вставки результатов. диалоговое окно
включать только теСовет: скрыв ненужные строки,Щелкните ячейку в диапазоне в соответствующую ячейку(Продажи > 6000 И таблицы. Но, если блоке инструментов «Редактирование». которому будет выполняться* убрать всен конце можно мысленнот.е.и жмем
например из начальныхПланшеты Выполняя фильтрацию, введитеРасширенный фильтр столбцы, названия которых При копировании отобранных строк установите переключатель
Горизонтальный и вертикальный фильтр (одновременно)
списка. Используя пример, диапазона условий:
Продажи < 6500) вы ранее выделили Но, на этот сортировка. В нашем пробелы ставить *.
ПОК данных вывести все, чтобы видеть только ссылку на скопированные
временно, пока вы были скопированы. в другое местофильтровать список на месте щелкните любую ячейку
=»=
ИЛИ (Продажи < область правильно, то раз в появившемся случае, это столбецЕсли не угадал
Даты надо вводить вавло. Отобранные строки отобразятся
столбцы со значением эти типы оборудования. названия столбцов в выбираете диапазон условий,Введите в поле можно указать столбцы,. в диапазоне A6:C10.
запись 500) больше ничего делать меню выбираем пункт «Имя». В поле тогда «больного в штатовском формате месяц-день-годв на листе:
Как в Excel отфильтровать по горизонтали все столбцы со значением например «Да»?
в строке 2Таблица с данными будет
поле нажмите кнопкуДиапазон условий
включаемые в копию.Чтобы скопировать отфильтрованные строкиНа вкладке»Несколько наборов условий, несколько не нужно. Главное, «Фильтр». Можно также «Сортировка» указывается, по операционную» и через дробь,»Ну и где же
равным 7. т.е. отфильтрована, временно скрывПоместить результат в диапазон
Свернуть диалоговое окноссылку на диапазон
Перед отбором скопируйте
в другую областьДанные
Здесь столбцов в каждом обратите внимание, чтобы 
Filter horizontal data
To filter data arranged horizontally in columns, you can use the FILTER function. In the example shown, the formula in C9 is:
where data (C4:L6) and group (C5:L5) are named ranges.
Generic formula
Explanation
Note: FILTER is a new dynamic array function in Excel 365. In other versions of Excel, there are alternatives, but they are more complex.
There are ten columns of data in the range C4:L6. The goal is to filter this horizontal data and extract only columns (records) where the group is «fox». For convenience and readability, the worksheet contains three named ranges: data (C4:L6) and group (C5:L5), and age (C6:L6).
The FILTER function can be used to extract data arranged vertically (in rows) or horizontally (in columns). FILTER will return the matching data in the same orientation. No special setup is required. In the example shown, the formula in C9 is:
Working from the inside out, the include argument for FILTER is a logical expression:
When the logical expression is evaluated, it returns an array of 10 TRUE and FALSE values:
Note: the commas (,) in this array indicate columns. Semicolons (;) would indicate rows.
The array contains one value per column in the data, and each TRUE corresponds to a column where the group is «fox». This array is returned directly to FILTER as the include argument, and it performs the actual filtering:
Only data that corresponds to TRUE values passes the filter, so FILTER returns the 6 columns where the group is «fox». FILTER returns this data in the original horizontal structure. Because FILTER is a dynamic array function, the results spill into the range C9:H11.
This is a dynamic solution – if any source data in C4:L6 changes, the results from FILTER automatically update.
Transpose to vertical format
To transpose the results from FILTER into a vertical (rows) format, you can wrap the TRANSPOSE function around the FILTER function like this:
The result looks like this:
Filter on age
The same basic formula can be used to filter the data in different ways. For example, to filter data to show only columns where age is less than 22, you can use a formula like this:
How to Create a Horizontal Filter in Excel
To use a filter horizontally in Excel, you need to use the FILTER function that allows you to filter a table based on criteria that you specify in the function. You can specify a table and then using the name of the row, specify criteria to filter.
In the following example, you have small data set where you have name and age in the horizontal form (headings are in rows). Now we need to filter data based on the age where the age is above 45.
Steps to Apply Horizontal Filter in Excel
- In cell D2, start adding the filter function.
- From here, in the first argument, refer to the range B2:AO2 where we have the actual data that we need to filter.
- Now, in the second argument, refer to the range where we have age value.
- Next, enter greater than and equal to the operator.
- After that, enter the 45 that we want to use as criteria.
- In the third argument, you can use specify a value to use for the cells where there is no value.
- In the end, enter closing parentheses and hit enter to get the filtered data.
=FILTER(B1:AO2,B2:AO2>=45)
How Does this Formula Work?
Before we try to understand this formula, we need to know that FILTER is a DYNAMIC function that allows you to work with multiple values at the same time in a single formula and then return values in multiple cells.
Now let’s split this function into two parts to understand this:
You can also go one step ahead and transpose data and filter horizontal data and transpose it into vertical data by using the transpose function.