Допустим, вам нужно отфильтровать данные по десяти филиалам, когда в таблице их несколько десятков или даже сотни. Конечно, в таком случае можно обойтись и обычным фильтром, но выбирать в выпадающем списке десять-двадцать значений из сотен — мучительно. Проще сделать список в отдельных ячейках, добавить к нему заголовок из исходной таблицы и с помощью расширенного фильтра получить результат.
Что, если мы хотим отфильтровать данные только по юрлицам (ООО и ОАО, но не ИП)?
Можно воспользоваться символами подстановки. Напомним: звездочка заменяет любой текст от 0 до бесконечности символов, а знак вопроса — это один любой символ.
Вот такое условие позволит отфильтровать по условию «ячейка начинается с буквы О, любого символа и затем снова буквы О», которому будут соответствовать и ООО, и ОАО:
Следующие условия — это продажи курсов и консультаций ООО и ОАО.
А если в примере с филиалами вам нужны модели только одного бренда, можно добавить условие на названия товаров — бренд, окруженный звездочками (то есть любой текст + название бренда + любой текст).
Функции баз данных
Функции баз данных (Database functions) — это обычные функции рабочего листа Excel, используемые в ячейках. Особенность этой категории функций в том, что они работают с такими же условиями, как те, что задаются в расширенном фильтре.
Это диапазон ячеек, в первой строке которого должны быть названия тех полей (столбцов), на которые накладываются условия. Одна строка под этими заголовками — это один набор условий. Если строк более одной — значит, будут подсчитываться все варианты.
Эти функции удобны, когда нужно проанализировать данные по большому списку филиалов, фамилий, рекламных кампаний и любых других признаков, чтобы не использовать много функций типа СУММЕСЛИМН / SUMIFS (по функции на каждый набор условий), а сразу получить результат.
Синтаксис функций баз данных на примере ДСРЗНАЧ / DAVERAGE (вычисление среднего значения):
• данные (database) = исходная таблица с заголовками;
• столбец (field) = ссылка на заголовок или текст с заголовком того столбца, по которому нужно вести расчет (суммировать/усреднять/извлекать значение, вычислять максимальное или минимальное значение и так далее);
• критерии (criteria) = ссылка на диапазон с условиями.
Если нам нужна сумма остатков по списку филиалов и одному бренду (из последнего примера), сработает такая функция:
Второй аргумент может быть в виде текста заголовка, а не ссылки на него:
А если нужно количество сделок с двумя продуктами и условием на сумму сделки по одному из них (один из наших примеров), понадобится функция БСЧЁТ / DCOUNT или БСЧЁТА / DCOUNTA (первая считает числа, вторая — любые значения):
Вот список всех функций баз данных, отличаются они типом вычисления, а синтаксис у всех схожий.
Функции для поиска и извлечения значений
Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — подтянуть данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции):
У нее есть два режима работы — интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, если нужно конвертировать оценку из одной системы в другую и так далее, то используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
В большинстве случаев мы связываем таблицы по текстовым ключам, в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» (range_lookup) равным нулю (или ЛОЖЬ / FALSE). Только тогда функция будет корректно работать с текстовыми значениями.
У ВПР есть существенный недостаток: ключ (искомое значение) обязан быть в первом столбце таблицы с данными. Все, что левее этого столбца, через ВПР подтянуть невозможно.
В реальных условиях структура таблиц бывает разной и не всегда возможно изменить порядок столбцов. Поэтому важно уметь работать с любой структурой.