Как сделать горизонтальный фильтр в excel

Как сделать горизонтальный фильтр в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Закрепление областей окна для блокировки первой строки или первого столбца в Excel для Mac

Закрепить неактивна, выберите Обычный на вкладке Вид .

Закрепление верхней строки

На вкладке Вид выберите

Закрепить верхнюю строку.

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

Закрепление первого столбца

На вкладке Вид выберите

Закрепить первый столбец.

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

Закрепление верхней строки и первого столбца

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

Закрепить области.

Закрепление неограниченного количества строк и столбцов

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

Чтобы закрепить несколько строк или столбцов, выполните приведенные далее действия.

1. Выберите строку под последней строкой или справа от последнего столбца, который нужно заморозить.
2. На вкладке Вид выберите

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

1. Выберите ячейку, например D5.
2. На вкладке Вид выберите

Каждый раз при закреплении строк и столбцов нижняя граница последней закрепленной строки и правая граница последнего закрепленного столбца становятся немного темнее (в данном случае это нижняя граница строки 4 и правая граница столбца C).

Снятие закрепления строк и столбцов

Хотите снять закрепление строк или столбцов? На вкладке Вид выберите

Разморозить области.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В табличку условий введем критерии отбора: =»=Набор обл.6 кл.» (в столбец «Название») и (в столбец «Цена»). То есть программа должна отобрать те значения, содержащие ТОЧНО информацию о товаре «Набор обл.6 кл.» ИЛИ информацию по товарам, цена которых ””;МАКС($A$1:A1)+1)’ >

Рядом с выпадающим списком ячейку введем следующую формулу: МАКС($B$1:$O$1);»»;ГПР(СТОЛБЕЦ(A:A);$B$1:$O$33;ПОИСКПОЗ($A$12;$A$4:$A$9;)+3))’ > Ее задача – выбирать из таблицы те значения, которые соответствуют определенному товару

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Горизонтальная фильтрация столбцов в Excel

Если вы не совсем начинающий пользователь, то, должно быть, уже заметили, что на 99% всё в Excel устроено для работы именно с вертикальными таблицами, где по столбцам идут параметры или атрибуты (поля), а в строчках располагается информация об объектах или событиях. Сводные таблицы, промежуточные итоги, копирование формул двойным щелчком — всё заточено именно под такой формат данных. Однако же, нет правил без исключений и с достаточно регулярной периодичностью меня спрашивают что делать если в работе попалась таблица с горизонтальной смысловой ориентацией, либо таблица, где строки и столбцы имеют одинаковый по смыслу вес:
И если сортировать по горизонтали Excel ещё умеет (командой Данные — Сортировка — Параметры — Сортировать столбцы), то с фильтрацией всё обстоит хуже — встроенных инструментов для фильтрации столбцов, а не строк в Excel просто нет. Так что, если перед вами встала такая задача, придется придумывать обходные пути разной степени сложности.

Способ 1. Новая функция ФИЛЬТР

Если вы работаете на новой версии Excel 2021 или в подписке Excel 365, то можно воспользоваться недавно появившейся функциейФИЛЬТР(FILTER), умеющей фильтровать исходные данные не только по строчкам, но и по столбцам. Для работы эта функция требует вспомогательный горизонтальный одномерный массив-строку, где каждое значение (ИСТИНА или ЛОЖЬ) определяет показываем ли мы или, наоборот, скрываем очередной столбец в таблице. Добавим такую строку над нашей таблицей и пропишем в ней статус каждого столбца:

• Допустим, мы всегда хотим отображать первый и последний столбцы (заголовки и итоги), поэтому для них в первой и последней ячейках массива зададим значение =ИСТИНА.
• Для остальных столбцов содержимое соответствующих ячеек будет формулой, которая проверяет нужное нам условие с помощью функций И (AND) или ИЛИ (OR) . Например, что итог находится в интервале от 300 до 500.

После этого останется лишь использовать функцию ФИЛЬТР для отбора столбцов, над которыми в нашем вспомогательном массиве есть значение ИСТИНА:

Аналогичным образом можно отфильтровать столбцы и по заданному списку. В этом случае поможет функция СЧЁТЕСЛИ (COUNTIF) , проверяющая количество вхождений очередного названия столбца из шапки таблицы в разрешённый список:

Способ 2. Сводная таблица вместо обычной

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

• иметь «правильную» однострочную строку заголовка без пустых и объединенных ячеек — иначе не получится построить сводную таблицу;
• не содержать дубликатов в подписях строк и столбцов — они «схлопнутся» в сводной в список только уникальных значений;
• содержать только числа в области значений (на пересечении строк и столбцов), т.к. сводная таблица обязательно применит к ним какую-то агрегирующую функцию (сумму, среднее и т.д.) и с текстом это не сработает

Если все эти условия выполняются, то для построения сводной, внешне похожей на нашу исходную таблицу, её (исходную) нужно будет развернуть из кросс-таблицы в плоскую (нормализовать). А это проще всего сделать с помощью надстройки Power Query — мощного инструмента преобразования любых данных, встроенного в Excel начиная с 2016-й версии.

1. Преобразуем таблицу в «умную» динамическую командой Главная — Форматировать как таблицу (Home — Format as Table) .
2. Загружаем в Power Query командой Данные — Из таблицы/диапазона (Data — From Table/Range).
3. Фильтруем строку с итогами (в сводной будут свои итоги).
4. Щёлкаем правой кнопкой мыши по заголовку первого столбца и выбираем Отменить свёртывание других столбцов (Unpivot Other Columns) . Все невыделенные столбцы преобразуются в два — имя сотрудника и значение его показателя.
5. Фильтруем столбец с итогами, который ушёл в колонку Атрибут.
6. Строим сводную таблицу по получившейся плоской (нормализованной) таблице командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close & Load — Close & Load to. ) .

Теперь можно пользоваться имеющейся в сводных таблицах возможностью фильтрации столбцов — привычными галочками напротив имён и пунктами Фильтры по подписи (Label Filters) или Фильтры по значению (Value Filters) :

И само собой при изменении данных нужно будет обновить наш запрос и сводную сочетанием клавиш Ctrl + Alt + F5 или командой Данные — Обновить всё (Data — Refresh All) .

Способ 3. Макрос на VBA

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

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

Как и в первом способе, сначала реализуем вспомогательный диапазон-строку, где в каждой ячейке формулой будет проверяться наш критерий и выводиться логические значения ИСТИНА или ЛОЖЬ для видимых и скрытых столбцов соответственно:

Затем добавим простой макрос. Щёлкните правой кнопкой мыши по ярлычку листа и выберите команду Исходный код (Source code) . В открывшееся окно скопируйте и вставьте следующий VBA-код:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next cell End If End Sub

Логика его работы следующая:

• В общем и целом — это обработчик события Worksheet_Change, т.е. этот макрос будет автоматически запускаться при любом изменении любой ячейки на текущем листе.
• Ссылка на изменённую ячейку всегда будет лежать в переменной Target.
• Сначала мы проверяем, что пользователь изменил именно ячейку с критерием (A4) — это делает оператор if.
• Затем запускается цикл For Each. для перебора серых ячеек (D2:O2) со значениями-индикаторами ИСТИНА / ЛОЖЬ для каждого столбца.
• Если значение очередной серой ячейки равно ИСТИНА (true), то столбец не скрывается, в противном случае — скрываем его (свойство Hidden).

Ссылки по теме

• Функции динамических массивов из Office 365: ФИЛЬТР, СОРТ и УНИК
• Сводная по таблице с многострочной шапкой с помощью Power Query
• Что такое макросы, как их создавать и использовать