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

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

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

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

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

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

О?О*

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функции для поиска и извлечения значений

Файл с примерами. "Функции поиска"

ФУНКЦИЯ ВПР / VLOOKUP

Функция ВПР / VLOOKUP (вертикальный просмотр) нужна, чтобы связать несколько таблиц — подтянуть данные из одной в другую по какому-то ключу (например, названию товара или бренда, фамилии сотрудника или клиента, номеру транзакции):

=ВПР (что ищем; таблица с данными, где "что ищем" должно быть в первом столбце; номер столбца таблицы, из которого нужны данные; [интервальный просмотр])

У нее есть два режима работы — интервальный просмотр и точный поиск.

Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, если нужно конвертировать оценку из одной системы в другую и так далее, то используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).

В большинстве случаев мы связываем таблицы по текстовым ключам, в таком случае нужно обязательно явным образом указывать последний аргумент «интервальный_просмотр» (range_lookup) равным нулю (или ЛОЖЬ / FALSE). Только тогда функция будет корректно работать с текстовыми значениями.

ФУНКЦИИ ПОИСКПОЗ / MATCH И ИНДЕКС / INDEX

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

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