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

работы нужно явно указать аргумент «режим_сопоставления» = 2. У функций ВПР и ПОИСКПОЗ они работают по умолчанию.

СРАВНЕНИЕ ФУНКЦИЙ ДЛЯ ПОИСКА ЗНАЧЕНИЙ

ПОИСК ПО НЕСКОЛЬКИМ КРИТЕРИЯМ

Файл с примером: ВПР по 2 критериям.xlsx

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

Самое простое решение: добавить вспомогательные столбцы в обеих таблицах, в которых создать уникальный ключ из двух значений. Можно объединить их с помощью амперсанда (&) или одной из текстовых функций (СЦЕП / CONCAT, СЦЕПИТЬ / CONCATENATE).

И далее использовать функцию ВПР как обычно.

А еще можно производить конкатенацию (объединение текстовых значений) прямо внутри формулы. Тогда можно избавиться от вспомогательного столбца в таблице с ВПР — будем соединять два значения там с помощью амперсанда в самой функции.

Можно пойти дальше и использовать формулу массива и объединять столбцы и в исходной таблице тоже прямо в формуле. Но тогда пригодится комбинация ИНДЕКС и ПОИСКПОЗ, потому что там используются отдельные столбцы и один из них можно сделать объединенным (а в ВПР в качестве аргумента используется таблица).

Здесь мы ищем объединенное значение (Курс + Формат, A2&C2) в столбце, сделанном в формуле из двух столбцов на листе «Ставки роялти» — B и C.

В старых версиях Excel не забудьте ввести такую формулу с помощью сочетания клавиш Ctrl + Shift + Enter (это формула массива, и до Excel 2019 включительно такие нужно вводить явным образом).

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

СУММЕСЛИМН / SUMIFS для поиска чисел

Если вы ищете числа, как в данном случае (ставки роялти — числовые значения), можно обойтись вовсе без ВПР или ИНДЕКС + ПОИСКПОЗ.

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

Плюс в том, что с этой функцией легко работать и при большом количестве условий.

Но и минус есть: если мы ищем текст, а не числа, то подойдут только предыдущие варианты, а не СУММЕСЛИМН.

ВПР С РАЗНЫХ ЛИСТОВ

Файл с примером: ВПР с разных листов.xlsx

Если вам нужно «подтягивать» данные с помощью ВПР / VLOOKUP с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью функции ДВССЫЛ / INDIRECT.

Обычная ссылка на другой лист выглядит так:

='Название_листа'!A: B

Нам нужно подставлять внутри апострофов названия разных листов.

Сначала берем апостроф (в кавычках), потом к нему добавляем название листа, справа еще один апостроф, восклицательный знак и диапазон:

="'" & ячейка с названием листа & "'!диапазон"

Чтобы превратить полученную текстовую строку в ссылку, используем функцию ДВССЫЛ / INDIRECT — она ровно для этого и используется:

=ДВССЫЛ ("'" & ячейка с названием листа & "'!диапазон")

И отправляем это внутрь ВПР'а как второй аргумент:

=ВПР(значение для поиска; ДВССЫЛ("'" & ячейка с названием листа & "'!диапазон"); номер столбца; 0)

Генерация случайных чисел и дат

В Excel и в Google Таблицах есть функции для генерации случайных чисел: СЛЧИС / RAND и СЛУЧМЕЖДУ / RANDBETWEEN (последняя появилась в Excel 2007).

СЛУЧМЕЖДУ генерирует целые числа — концы интервала задаются в двух аргументах функции. В следующем примере генерируем числа от 1000 до 6000.

СЛЧИС генерирует случайное число от 0 до 1. Аргументов у нее нет — это всегда число в этом диапазоне. В следующем примере к ним применен процентный формат.

Генерация дат и времени

Если мы вспомним, что дата в Excel — это целое число, то поймем, что можно сгенерировать и случайную дату.

Ну а раз время — это та часть (доля) дня, что уже прошла, то есть число от нуля до единицы, то случайное время можно генерировать с помощью СЛЧИС.

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

Выбор случайного значения из списка

Файл с примером: Жеребьевка (ИНДЕКС + СЛУЧМЕЖДУ).xlsx

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

Вспоминаем, что есть функция ИНДЕКС / INDEX, которая возвращает значение из массива по номеру строки (или номерам строки и столбца, если массив двумерный).

Если этот самый номер сделать случайным (то есть вычислять с помощью функции СЛУЧМЕЖДУ), то это и будет выбором случайного элемента из списка.

В Excel 2021 и Microsoft 365 также появилась функция СЛУЧМАССИВ / RANDARRAY, она позволяет генерировать сразу целый массив.

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

Какие еще функции в Excel являются волатильными:

ТДАТА / NOW;

СЕГОДНЯ / TODAY;

ДВССЫЛ / INDIRECT;

СМЕЩ / OFFSET.

Динамические массивы

Файл с примерами: Динамические массивы.xlsx

НОВЫЕ ПРАВИЛА РАБОТЫ С МАССИВАМИ

В Excel в рамках пакета Microsoft 365 и версии 2021 произошли существенные и принципиальные изменения в работе с формулами массивов: теперь одна формула/функция может выводить результат не только в одной ячейке, но и сразу в нескольких.

Обычные формулы мы вводим в одну ячейку и результат получаем тоже в одной ячейке. Каждая формула независима в том смысле, что мы