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

Автофильтр в EXCEL

Для нормальной работы автофильтра требуется «правильно» спроектированная таблица. Правильная с точки зрения MS EXCEL — это таблица без пустых строк/ столбцов, с заголовком, с однотипными данными в столбце (т.е. не нужно смешивать в одном столбце данные разных форматов, например текст и числа или числа и даты). Подробнее см. статью Советы по построению таблиц . Будем использовать именно такую, правильную таблицу (см. файл примера ).

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

Команда Фильтр также доступна на вкладке Главная ( Главная/ Редактирование/ Сортировка и фильтр/ Фильтр )

В строке заголовка появятся стрелки раскрытия фильтра.

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

Отбор по столбцу с текстовыми значениями

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

Сначала отобразим только те строки, которые содержат в столбце Товар слово Гвозди ( Регистр букв не имеет значения). Сделаем это 2-мя способами.

Способ 1. Нажмем стрелку раскрытия фильтра. Затем нажмем на значение (Выделить все) — снимутся все галочки. Затем установите галочку напротив значения гвозди .

Нажмите ОК. В итоге отобразятся только те строки, которые содержат в столбце Товар значение Гвозди (т.е. строки со значениями Лучшие Гвозди или Гвозди 10 мм отобраны не будут). Понять, что применен фильтр очень просто: стрелка раскрытия фильтра изменит свой вид (на ней появится маленькая воронка), а номера отобранных строк станут синими. Если подвести курсор к стрелке раскрытия фильтра столбца, в котором используется фильтрация, отобразится всплывающая подсказка с фильтром, который используется в данном столбце, например, Товар : «Равно Гвозди». В строке состояния (внизу листа) отображается информация о примененном фильтре: «Найдено записей: 13 из 76».

Снять примененный фильтр можно несколькими способами:

• Нажмите стрелку раскрытия фильтра. Выберите пункт Снять фильтр с «Товар» или;
• Нажмите стрелку раскрытия фильтра, затем нажмите на значение (Выделить все) или;
• Выберите команду Очистить( Данные/ Сортировка и фильтр/ Очистить ) или;
• Выберите команду Очистить , но в другом меню ( Главная/ Редактирование/ Сортировка и фильтр/Очистить ) или;
• Нажмите сочетание клавиш CTRL+SHIFT+L (должна быть выделена любая ячейка таблицы). При этом фильтр будет выключен.

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

В результате получим тот же результат.

Примечание: Если в столбце содержится и текстовые значения и числа (пример «неправильной» таблицы), то MS EXCEL будет отображать меню Числовые фильтры только в том случае, если количество чисел в столбце больше чем текстовых значений. В противном случае будут отображено меню Текстовые фильтры .

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

Для этого нажмем стрелку раскрытия фильтра. Выберем Текстовый фильтр Начинается с.. .

Введем значение Гвозди, в результате получим:

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

Более того, в диалоговом окне Настраиваемый фильтр возможно использование Подстановочных знаков , которые могут сделать настройку фильтра очень гибкой. Но, как правило, сложные критерии фильтра не используются, т.к. их невозможно сохранить, в отличие от Расширенного фильтра . Единственное, что можно сделать — это использовать отмену последнего действия ( CTRL+Z ), который запоминает настройки фильтра. Например, применив последовательно 3 условия фильтрации можно используя комбинации CTRL+Z и CTRL+Y отменять и заново применять условия фильтрации (не выполняйте при этом никаких других действий кроме настройки фильтра!).

СОВЕТ : Т.к. условия отбора записей (настройки автофильтра) невозможно сохранить, то чтобы сравнить условия фильтрации одной и той же таблицы в разных случаях, скопируйте лист с исходной таблицей, затем примените нужные фильтры для оригинала таблицы и ее копии. Для сравнения результатов фильтрации используйте 2 окна (для каждой из таблиц). Для этого создайте новое окно ( Вид/ Окно/ Новое окно ), перейдите в новый лист, затем выберите требуемый вид отображения окон ( Вид/ Окно/ Упорядочить все/ Рядом ).

Отбор по столбцу с числовыми значениями

Если столбец содержит числа, то меню фильтра будет иметь другой вид. Рассмотрим на примере столбца Количество .

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

На картинке ниже показано, что отобраны только строки со значениями в столбце Количество, которые больше среднего (больше 59,5).

Числовой фильтр Первые 10 отберет 10 наибольших значений. Настройка этого фильтра позволяет отобрать и нужное количество наименьших значений.

Настроим фильтр для отбора 9 наибольших значений в столбце Количество . Для наглядности отсортируем таблицу по столбцу Количество .

Что значит 9 наибольших значений? В человеческом понимании это: 450; 300; 200; 100; 90; 87; 50; 40; 30. В понимании MS EXCEL — это 450; 300; 300; 300; 300; 200; 200; 200; 100, т.е. повторы также учитываются. Но это еще не все. Применим фильтр для отбора 9 наибольших значений в столбце Количество .

Вопреки ожиданиям будет отобрано не 9 значений, а 11. Дело в том, что 9-м наибольшим является число 100, которое повторяется еще 2 раза, т.е. в таблице 3 строки со значением 100 в столбце Количество . MS EXCEL естественно не знает какой из трех строк отдать предпочтение, поэтому отбирает все три! В итоге к 9 наибольшим добавляется еще 2 повтора, т.е. всего отбирается 11 строк.

Отбор по столбцу с датами

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

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

Отбор по цвету

Если ячейки в таблице отличаются цветом шрифта или ячейки, то фильтр также можно настроить и по цвету.

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

Сортировка

Через меню фильтра также доступна сортировка таблицы. Текстовые значения могут быть отсортированы по алфавиту (от A до Z или от А до Я) или в обратном порядке. Числовые значения — по убыванию или возрастанию.

Проясним несколько моментов важных для осуществления правильной сортировки.

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

Выделим только 2 заголовка 5 столбцовой таблицы и применим автофильтр. Для наглядности строки таблицы выделим разными цветами.

После сортировки столбца Товары от Я до А таблица будет разрушена! Это видно на рисунке ниже.

2. При сортировке столбцов скрытые строки не перемещаются. Перед сортировкой данных отобразите скрытые строки. Строки могут быть скрыты как через меню Главная/ Ячейки/ Формат/ Скрыть или отобразить так и после применения Автофильтра — в любом случае они не примут участия в сортировке!

3. Отменить сортировку таблица нельзя! Для отмены результатов сортировки нужно сразу выполнить команду Отмена последнего действия ( CTRL+Z ). Можно еще закрыть файл без сохранения, но есть риск потери других изменений.

СОВЕТ : Другой способ возвращения к первоначальной сортировке: заранее перед сортировкой создать дополнительный столбец с порядковыми номерами строк (вернуть прежнюю сортировку можно потом, заново отсортировав по нему).

4. Сортировку можно также можно осуществить с помощью меню Данные/ Сортировка и фильтр/ Сортировка . После нажатия кнопки меню будет отображено диалоговое окно.

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

1. Выделите ячейку таблицы.
2. На вкладке Данные в группе Сортировка и фильтр выберите команду Сортировка (та же команда доступна через меню ( Главная/ Редактирование/ Сортировка и фильтр/ Настраиваемая сортировка. ).
3. В диалоговом окне Сортировка выберите столбец по которому нужно произвести сортировку и порядок сортировки от А до Я.
4. В диалоговом окне Сортировка нажмите кнопку Параметры .
5. В диалоговом окне Параметры сортировки установите флажок Учитывать регистр .
6. Дважды нажмите кнопку ОК .

Как видно из рисунка ниже значения гвозди (строчные буквы) располагаются выше, чем значения Гвозди (первая буква Прописная).

Если данные в таблице изменились

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

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

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

Теперь попытаемся отфильтровать только те строки, которые содержат значения Цемент и гвозди в столбце Товар . Но у нас ничего не получится, т.к. в автофильтре нет значения Цемент !

Значения Цемент нет в меню автофильтра, т.к. в качестве таблицы MS EXCEL рассматривает только строки 6-9, а строки 11 и 12 — это уже другая таблица, т.к. под таблицей MS EXCEL понимает диапазон ячеек окруженный пустыми строками и столбцами (исключение составляют таблицы в формате EXCEL 2007 ).

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

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

Теперь для правильного установления фильтра необходимо сперва выделить ячейки заголовка, а только затем нажать сочетание клавиш CTRL+SHIFT+L ). Это не всегда удобно, поэтому над строкой заголовков всегда располагайте пустую строку, которую можно скрыть.

Кнопка для отключения фильтров в Power BI

Один клик вместо десяти – создаем кнопку в Power BI, которая отключает ВСЕ фильтры на срезах.

Благодаря срезам отчеты Power BI становятся удобнее. С помощью этих «галочек» мы можем настраивать разные представления данных. Но после того, как настроенная фильтрация больше не требуется, придется тратить время, чтобы отключить её отдельно на каждом срезе.

А можно сделать одну кнопку, которая будет «сбрасывать» все срезы за один клик. Настроить такую кнопку просто: потребуется сама кнопка, закладка и пара настроек.

Разбираем, как это всё работает =) Чтобы было удобнее читать, скачайте файл Power BI с примером.

Шаг 1. Создайте закладку

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

Предварительный фильтр в отчёте «Электронная таблица»

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

Сотрудникам разных подразделений часто может быть нужна информация, которая структурно полностью совпадает с тем, что нужно их коллегам, но с минимальными отличиями. Отсюда и следует логика создания этого фильтра: сначала создать один общий LINQ-запрос → пользователь сам определит, что именно ему нужно (фильтр) → LINQ-запрос + пользовательский фильтр выводит в отчёт для пользователя только то, что ему нужно здесь и сейчас.

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

Функциональность предварительного пользовательского фильтра доступна только в случае, если в качестве источника для отчёта выступает LINQ-запрос.

Если отчет еще не создан (не сохранен впервые), то пункт «Задать параметры отбора данных» не отображается.

По нажатию на пункт «Задать параметры отбора данных» открывается окно «Параметры отбора данных для отчета с настроенным LINQ-источником».

Рядом с «Реквизитами» кнопка «Добавить» → откроется окно «Список реквизитов», где вы можете выбрать реквизиты, ранее созданные в системе.

Для выбора доступны только реквизиты следующих типов: Строка, Число, Дата, Процесс, Классификатор (в т.ч. объектный).

«Название реквизита» – полное название реквизита.
«Тип» – тип реквизита.

«Обязательный» – по умолчанию для всех вновь добавленных реквизитов опция выключена (чекбокс не отмечен).

«Название параметра для LINQ-источника*» – по умолчанию пустое значение. Содержит название параметра, под которым LINQ-запросу будет передаваться значение соответствующего реквизита. Обязательное поле.

«Значение по умолчанию» – разные элементы интерфейса указания значения реквизита в зависимости от его типа. В точности те же компоненты, что используются в формах сбора отчетности или карточке объекта:

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

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

«Действия» – кнопка «Удалить». Удаляет реквизит со страницы «Параметры отбора данных для отчета с настроенным LINQ-источником».

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

При удалении реквизита или его значения (в случае с реквизитом типа «Классификатор» или «Процесс») он также удаляется со страницы «Параметры отбора данных для отчета с настроенным LINQ-источником».

Сохранение изменений на странице «Параметры отбора данных для отчета с настроенным LINQ-источником» происходит независимо от того, будет ли сохранен сам отчет.

Вывод в отчет значений фильтра

На отдельную вкладку отчёта «Электронная таблица» можно вывести настроенные параметры LINQ-источников.

В панели параметров отчета «Электронная таблица» → вкладка «Данные» → раздел «LINQ».

Все данные выводятся на отдельный лист «Parameters».

Значение даты и времени запроса
Начальная дата Excel-отчета и Конечная дата Excel-отчета
Название периода
Перечень реквизитов и их параметры.

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

Если Excel-отчет не сохранить, то лист удаляется.

Предварительный фильтр и дашборды

Если отчет типа «Электронная таблица» с сохраненными параметрами отбора данных является источником данных для дашборда, то отчет строится на основе указанных в реквизитах значений по умолчанию. Если значение не указано, то берется значение «null», даже для обязательных к заполнению реквизитов.

Использование фильтра

Чтобы проверить, что фильтр работает, заново откройте отчёт.
Вместо загрузки самого отчёта «Электронная таблица» должен отобразиться портлет, содержащий:

название вашего отчёта;

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

все сохраненные реквизиты на странице «Параметры отбора данных для отчета с настроенным LINQ-источником» в преднастроенном порядке.

По нажатию кнопки «Показать отчет» открывается отчет, в котором у каждого листа соответствующего LINQ-запроса отображаются отфильтрованные данные.

По нажатию кнопки «Экспорт в XLS»:

отчет не открывается, а сразу же скачивается в формате .xls;
отчет содержит лист (п.2.3) с указанием переданных параметров и их значений.

Страница указания параметров отбора данных для отчета не закрывается. Указав новые значения реквизитов и нажав кнопку «Экспорт в XLS», можно заново скачать отчет с учетом новых данных.

Calc.Автофильтр и сортировка: Часто задаваемые вопросы

Да, это проблема. Пока она не решена, рекомендуем Вам:

• пользоваться автофильтром после ввода всех данных
• включить необходимые столбцы в именованный диапазон данных Данные— Определить диапазон

Принять участие в проекте!
Общие вопросы
Форматирование ответов
Вопросы использования
Популярные разделы:
Writer: Часто задаваемые вопросы
Calc: Часто задаваемые вопросы
Орфография и грамматика
Полезности и секреты
Главная страница

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

Вставка данных в скрытые фильтром строки — в настоящий момент одна из нерешённых проблем ООо. См. Глоссарий#Список нерешённых проблем ООо

В MS Excel в автофильтре можно было искать текст по параметру «содержит». Если ли такой же в OOo Calc?

Есть. В версии OOo 2.2.1 — это параметр автофильтра Contains

В версии ООо 2.3.0 Pro — название параметра уже переведено — Содержит

Где искать: Автофильтр — Настраиваемый — Условие или Данные — Фильтр — Стандартный фильтр — Условие

Ранее в условии автофильтра использовалась звёздочка, чтобы получить выборку по всем фразам, начинающимся с какой-то общей части. Какой значок используется в ООо?

Вместо * надо использовать .*, вместо ? — . (и в диалоге фильтра указать Дополнительно — Регулярное выражение). Или выбрать из списка предлагаемых условий фильтра параметр Начинается на

Неправильно выполняется сортировка в столбце

Сортировать данные следует с помощью команды меню Данные — Сортировка

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

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

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

Лучше для сортировки использовать пункт меню Данные — Сортировка.

Перейти к разделу: Writer | Calc | Общее | UI | Глоссарий | Принятые сокращения | Полезности | Содержание