То есть слово между звездочек может встречаться в любом месте в ячейке. Если бы было нужно, например, искать ячейки с «Роулинг» только в конце, то условие выглядело бы так:
"*Роулинг"
Здесь все заканчивается на фамилии автора — после нее уже не предполагается никаких символов.
Что, если мы хотим выяснить сумму продаж или среднее значение по книгам с названиями из определенного количества символов? Например, только из четырех, как «Дюна» или «1984».
Названия в нашей таблице в кавычках-«елочках» — этим можно воспользоваться. Но звездочку внутрь них в условии помещать бесполезно, ведь это текст любой длины. То есть следующее условие:
*«*»*
это любой текст, в котором встречаются кавычки-«елочки» с любым же текстом внутри.
Поэтому тут нам понадобится знак вопроса — это один любой символ. А значит, нам подойдет следующий шаблон:
*«????»*
Это любой текст + четыре любых символа внутри кавычек-«елочек» + любой текст.
Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~):
~* — поиск звездочки;
— ? — поиск знака вопроса;
~~ — поиск самой тильды.
В следующем примере суммируем продажи всех книг, в названии которых есть звездочка:
=СУММЕСЛИМН(C: C;A: A;"*~**")
Google Таблица с примером: COUNTUNIQUEIFS
https://mif.to/I0T9W
В Google Таблицах есть функции для подсчета уникальных значений: COUNTUNIQUE вычисляет количество уникальных значений в диапазоне. Например, мы можем вычислить, сколько городов представлено в следующей таблице.
COUNTUNIQUEFS позволяет делать это с условиями — например, посчитать, сколько клиентов приобретали у нас консультации.
Минимальные и максимальные значения с условиями
Функции МИН и МАКС
Для вычисления минимальных и максимальных значений есть функции с простыми названиями МИН / MIN и МАКС / MAX и таким же простым синтаксисом — в качестве аргумента (аргументов) указываются один или несколько диапазонов.
Вычисляем минимальную величину сделки в таблице
Файл с примерами: МИНЕСЛИ и МАКСЕСЛИ.xlsx
Начиная с Excel 2016, можно вычислять минимальное и максимальное значение по условиям: например, максимальную сделку не вообще, а с определенным типом товара. Синтаксис функций такой же, как у функций СУММЕСЛИМН, СРЗНАЧЕСЛИМН:
=МАКСЕСЛИ(максимальный_диапазон; диапазон_условия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 и продажами консультаций на любую сумму.
Условие «не равно»
Если вам нужно исключить определенные значения, можно использовать знаки «больше» и «меньше» — это значит «не равно». Например, следующим условием мы исключаем курсы: <>Курс
В некоторых ситуациях расширенный фильтр может пригодиться, даже если условие накладывается только на один столбец.
Допустим, вам нужно отфильтровать данные по десяти филиалам, когда в таблице их несколько десятков или даже сотни. Конечно, в таком случае можно обойтись и обычным фильтром, но выбирать в выпадающем списке десять-двадцать значений из сотен — мучительно. Проще сделать список в отдельных ячейках, добавить к нему заголовок из исходной таблицы и с помощью расширенного фильтра получить результат.
Что, если мы хотим отфильтровать данные только по юрлицам (ООО и ОАО, но не ИП)?
Можно воспользоваться символами подстановки. Напомним: звездочка заменяет любой текст от 0 до бесконечности символов, а знак вопроса — это один любой символ.
Вот такое условие позволит отфильтровать по условию «ячейка начинается с буквы О, любого символа и затем снова буквы О», которому будут соответствовать и ООО, и ОАО:
О?О*
Следующие условия — это продажи курсов и консультаций ООО и ОАО.
А если в примере с филиалами вам нужны модели только одного бренда, можно добавить условие на названия товаров — бренд, окруженный звездочками (то есть любой текст + название бренда + любой текст).
Функции баз данных
Файл с примерами: Расширенный фильтр и функции БД.xlsx
Функции баз данных (Database functions) — это обычные функции рабочего листа Excel, используемые в ячейках. Особенность этой категории функций в том, что они работают с такими же условиями, как те, что задаются в расширенном фильтре.
Это диапазон ячеек, в первой строке которого должны быть названия тех полей (столбцов), на которые накладываются условия. Одна строка под этими заголовками — это один набор условий. Если строк более одной — значит, будут подсчитываться все варианты.
Эти функции удобны, когда нужно проанализировать данные по большому списку филиалов, фамилий, рекламных кампаний и любых других признаков, чтобы не использовать много функций типа СУММЕСЛИМН / SUMIFS (по функции на каждый набор условий), а сразу получить результат.