Check-moscow.ru

Финансы и учет
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как динамически фильтровать данные с одного листа на другой в Excel?

Объединяем выбор на разных срезах в DAX: Динамический фильтр «ИЛИ»

В стандартном режиме несколько срезов в Power BI работают по принципу «И», то есть пересечения примененных фильтров. На одном срезе мы выбираем «Яблоки», а на другом — «Красные», и получаем измерения только для красных яблок. Зеленые яблоки будут проигнорированы.

Обычный срез: красные яблоки

Нормальная обрезка: красные яблони

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

Более практичный пример: товарная позиция может быть помечена в базе как складская (за это отвечает свойство «Складская»), одновременно она помечена как плановая (за это отвечает другое свойство, «Плановая»). Для расчетов нас интересуют позиции, которые могут являться, к примеру, складскими ИЛИ плановыми (то есть у них может быть установлено либо одно из этих свойств, либо оба). Но при использовании двух обычных срезов отбор по свойству «Складская» = «Да» приведет к тому, что прочие строки будут отфильтрованы, даже если у них свойство «Плановая» тоже установлено срезом в значение «Да».

Фильтры отчетов

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

Перетащите область из строк в фильтры в сводной таблице.

Фильтры отчетов

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

Космический фильтр

Вы увидите это.

В строках показаны значения продавца.

Значения месяца выводятся в столбцах.

По умолчанию в фильтре регионов выбрано ВСЕ.

Значение суммы — это сумма суммы заказа.

Читайте так же:
Как заполнить результаты поиска Google на листе в Excel?

По словам продавца, общая сумма отображается в столбце Сумма заказа.

Итого отображается сумма заказа по месяцам.

Щелкните по стрелке в поле справа от области фильтра.

Список ценностей продавца появляется в строках.

В колонках представлены значения месяцев.

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

Значение суммы — это сумма заказа.

В колонке «Общая сумма», в зависимости от продавца, вы можете найти сумму заказа.

В строке «Итого» показана сумма заказов за каждый месяц.

Нажмите на стрелку в поле справа от области фильтра.

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

Выберите несколько предметов

По умолчанию все флажки установлены. Снимите флажок («Все»). Все флажки будут сняты.

Затем установите флажки — Юг и Запад и нажмите ОК.

Снять флажок

Приведем только краткие данные по южным и западным регионам.

Данные, относящиеся

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

Решение1

Числовые столбцы можно легко решить, если они не содержат повторяющиеся значения:

  • Отсортируйте числовой столбец с помощью функции NATURAL() (см. статью Сортированный список (ЧИСЛОВЫЕ ЗНАЧЕНИЯ));
  • Выберите значения в текстовом столбце по соответствующему числовому значению с помощью функции ВПР() или комбинации функций INDEX()+Search().

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

Поэтому механизм сортировки должен быть реализован иначе.

Читайте так же:
Как добавить точки в конец содержимого ячейки в Excel?

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

В столбцах D и E разместите таблицу, которая будет динамически отсортирована,

Вот как вырезается массив:

Аналогичную формулу можно записать для вывода значений в столбце Фрукты = INDEX(Фрукты;OKCircle(. ))

В файле примера та же часть формулы, т.е. все, что находится в функции OKRUL(), помещена в отдельный столбец J для ускорения вычислений (см. ниже). Таким образом, окончательные формулы в отсортированной таблице выглядят следующим образом: =INDEX(Фрукты;J7) и =INDEX(Продажи;J7)

Изменив функцию NEIGHTH() в формуле массива на LOWEST(), мы также получаем сортировку по возрастанию.

Для наглядности значения в столбце «Объем продаж» выделены с помощью условного форматирования (Пуск / Стили / Условное форматирование / Гистограммы). Как вы можете видеть, сортировка работает.

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

У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные — Фильтр (Data — Filter) :

advanced-filter1.png

Такой фильтр — штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой "доработкой напильником" (по традиции).

Основа

Диапазон с условиями (выделенный желтым для ясности) будет находиться в конце нескольких пустых строк над вашей таблицей данных:

advanced-filter2.png

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

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:

Читайте так же:
Как добавить линию ряда в диаграмму в Excel?

advanced-filter3.png

Для фильтрации выберите ячейку в области данных, откройте вкладку Данные и нажмите Данные — Дополнительно. В открывшемся окне диапазон данных уже введен автоматически, и вам нужно только указать диапазон условий, т.е. A1:I2:

advanced-filter5.png

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

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

advanced-filter6.png

Добавляем макрос

"Ну и где же тут удобство?" — спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" — макросы!

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

Эта процедура будет выполнена автоматически, если ячейка текущего листа будет изменена. Если адрес измененной ячейки находится в желтом диапазоне (A2:I5), этот макрос удалит все фильтры (если таковые имеются) и снова применит расширенный фильтр к исходной таблице данных, начиная с A7, т.е. все будет отфильтровано мгновенно, как только будет введено следующее условие:

Читайте так же:
Как выбрать непустые ячейки в диапазоне в Excel?

Так все гораздо лучше, правда? 🙂

Реализация сложных запросов

Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:

КритерийРезультат
гр* или грвсе ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д.
=луквсе ячейки именно и только со словом Лук, т.е. точное совпадение
*лив* или *ливячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д.
=п*вслова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д.
а*сслова начинающиеся с А и содержащие далее С , т.е. Апельсин, Ананас, Асаи и т.д.
=*сслова оканчивающиеся на С
=.все ячейки с текстом из 4 символов (букв или цифр, включая пробелы)
=м. нвсе ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостини т.д.
=*н??авсе слова оканчивающиеся на А , где 4-я с конца буква Н , т.е. Брусника, Заноза и т.д.
>=эвсе слова, начинающиеся с Э , Ю или Я
<>*о*все слова, не содержащие букву О
<>*вичвсе слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству)
=все пустые ячейки
<>все непустые ячейки
>=5000все ячейки со значением больше или равно 5000
5 или =5все ячейки со значением 5
>=3/18/2013все ячейки с датой позже 18 марта 2013 (включительно)
  • Знак * подразумевает любое количество символов, а ? — любой символ.
  • Логика обработки текстовых и числовых запросов немного отличается. Например, условная ячейка, содержащая число 5, не будет искать все числа, начинающиеся на пять, а условная ячейка, содержащая букву B, эквивалентна B*, то есть будет искать любой текст, начинающийся на букву B.
  • Если текстовый запрос не начинается со знака =, вы можете мысленно поставить * в конце.
  • Даты следует вводить в формате месяц-день-год-стат и с дробями (даже если у вас русский Excel и региональные настройки).
Читайте так же:
Как выделить ячейки между двумя датами в Excel?

Логические связки И-ИЛИ

Условия, записанные в разных ячейках, но в одной строке, считаются связанными при помощи логического оператора AND:

advanced-filter3.png

Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".

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

advanced-filter7.png

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

advanced-filter8.png

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector