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

=МАКСЕСЛИ(максимальный_диапазон; диапазон_условия1; условие1; …)

Максимальный диапазон — диапазон, в котором мы ищем максимальное число.

В Google Таблицах эти функции тоже есть, названия у них там на английском при любом языке формул: MAXIFS, MINIFS. А вот функции МИН и МАКС будут иметь названия на русском, если у вас русскоязычные формулы.

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

Файл с примерами: Расширенный фильтр и функции БД.xlsx

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

Напомним, что обычный автофильтр можно включить на вкладке «Данные» → «Фильтр» (Data → Filter), а также с помощью сочетания клавиш Ctrl + Shift + L ( + ⇑ + F). Кроме того, при создании таблицы (Ctrl + T или Ctrl + L) кнопки фильтра тоже появятся.

Расширенный же фильтр (диалоговое окно с его настройками) находится справа от обычного: в русскоязычном Excel это кнопка «Дополнительно», а в англоязычном интерфейсе — Advanced.

Отличия расширенного фильтра от обычного фильтра на рабочем листе в следующем:

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

• условия задаются не в самом фильтре, а в отдельных ячейках;

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

Поясним последний пункт на примере. Допустим, у нас есть такие данные.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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