Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

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

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

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

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

Столбцы нужны не все, а только те, на которые будут накладываться условия фильтрации. В нашем примере — «Продукт» и «Откуда».

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

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

Рассмотрим подробнее диалоговое окно расширенного фильтра.

Запись условий для расширенного фильтра

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

В критериях можно использовать те же символы подстановки, что и в функциях СУММЕСЛИМН / SUMIFS, СЧЁТЕСЛИМН / COUNTIFS, СРЗНАЧЕСЛИМН / AVERAGEIFS, — звездочку * (ноль или более любых символов), знак вопроса ? (один любой символ).

Рассмотрим несколько примеров условий.

Пустое условие

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

Числовое условие

На столбцы с числами можно накладывать условия в виде неравенств, как в функциях СУММЕСЛИМН / SUMIFS.

Например, в следующем случае мы фильтруем строки с курсами и консультациями с суммой строго выше 15 000.

А в следующем — строки с продажами курсов от 14 000 и продажами консультаций на любую сумму.

Условие «не равно»

Если вам нужно исключить определенные значения, можно использовать знаки «больше» и «меньше» — это значит «не равно». Например, следующим условием мы исключаем курсы: <>Курс

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

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

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

Символы подстановки

Что, если мы хотим отфильтровать данные только по юрлицам (ООО и ОАО, но не ИП)?

Можно воспользоваться символами подстановки. Напомним: звездочка заменяет любой текст от 0 до бесконечности символов, а знак вопроса — это один любой символ.

Вот такое условие позволит отфильтровать по условию «ячейка начинается с буквы О, любого символа и затем снова буквы О», которому будут соответствовать и ООО, и ОАО:

О?О*

Следующие условия — это продажи курсов и консультаций ООО и ОАО.

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

Функции баз данных

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

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

Эти функции удобны, когда нужно проанализировать данные по большому списку филиалов, фамилий, рекламных кампаний и любых других признаков, чтобы не использовать много функций типа СУММЕСЛИМН / SUMIFS (по функции на каждый набор условий), а сразу получить результат.

Синтаксис функций баз данных на примере ДСРЗНАЧ / DAVERAGE (вычисление среднего значения):

=ДСРЗНАЧ(данные; столбец; критерии)

• данные (database) = исходная таблица с заголовками;

• столбец (field) = ссылка на заголовок или текст с заголовком того столбца, по которому нужно вести расчет (суммировать/усреднять/извлекать значение, вычислять максимальное или минимальное значение и так далее);

• критерии (criteria) = ссылка на диапазон с условиями.

Если нам нужна сумма остатков по списку филиалов и одному бренду (из последнего примера), сработает такая функция:

=БДСУММ(A1:C500;C1;E2:F17)

Второй аргумент может быть в виде текста заголовка, а не ссылки на него:

=БДСУММ(A1:C500;"остаток, шт.";E2:F17)

А если нужно количество сделок с двумя продуктами и условием на сумму сделки по одному из них (один из наших примеров), понадобится функция БСЧЁТ / DCOUNT или БСЧЁТА / DCOUNTA (первая считает числа, вторая — любые значения):

=БСЧЁТА(Сделки[#Все];Сделки[[#Заголовки];[Компания]];I4:J6)

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