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

ИНДЕКС +ПОИСКПОЗ

Мне кажется, это вообще самая популярная комбинация. И запросов о ней больше всего. Да и в объявлениях на ХХ, как правило, в перечислении навыков есть именно эти формулы.

Давайте разбираться.

Эта комбинация позволяет нам перенести значения из столбца одной таблицы в столбец другой таблицы по совпадению значений в ячейках обеих столбцов.

Функция ИНДЕКС возвращает значения на пересечении заданного номера столбца и заданного номера строки в диапазоне.

=ИНДЕКС(где искать; номер строки; номер столбца)

Например, нам надо найти значение из столбца 1 в третьей строке

Тогда пишем =ИНДЕКС(таблица; 3; 1)

ищи в таблице в строке 3 столбца 1

Функция ПОИСКПОЗ возвращает номер строки или столбца, в котором содержится «название» элемента.

=ПОИСКПОЗ(что ищем; где ищем; степень совпадения)

Например, мы хотим узнать в какой строке указана фамилия «Васильев»

Тогда пишем =ПОИСКПОЗ(«Васильев»; B2:B4; 0)

ищи в таблице в диапазоне B2:B4 фамилию Васильев и верни номер строки, в котором она указана

Есть 3 типа сравнения:
0 — ищет точное совпадение. Это самый частый и надёжный вариант для кодов, артикулов, ИНН.
1 — ищет наибольшее значение, которое меньше или равно искомому. При этом диапазон должен быть отсортирован по возрастанию.
-1 — ищет наименьшее значение, которое больше или равно искомому. Здесь диапазон должен быть отсортирован по убыванию.

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

Итак, нам нужно заполнить данные столбца Клиент таблицы 1 в соответствие с данными из таблицы 2.Конечно, это можно сделать вручную. Но когда строк становится несколько тысяч, а то и десятков тысяч, ручное заполнение — мука.

Для задачи используем комбинирование формул =ИНДЕКС($K$2:$K$9;ПОИСКПОЗ(B2;$J$2:$J$9;0)). Когда я готовилась к одному из собеседований, то нашла очень интересное объяснение, которым пользуюсь на автомате до сих пор:

=ИНДЕКС(что ходим видеть в результате;ПОИСКПОЗ(что ищем;где ищем;насколько точно ищем))

возьми код клиента в ячейке из таблицы 1, найди точно такой же в столбце КодКлиента в таблице 2 и запиши в поле Клиент таблицы 1 значение из поля Клиент таблицы 2.

Если вы работаете с выделенным диапазоном не забывайте закреплять его знаками $.

Но гораздо удобнее с умными таблицами.

Для создания такой выделите таблицу, перейдите на вкладку данные и нажмите «Таблица» (можете дать ей любое удобное для работы имя).

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

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

В этом подходе ОБЯЗАТЕЛЬНО давайте максимально понятые наименования таблицам, чтобы быстрее на них ссылаться

СУММЕСЛИМН

Эта формула считает сумму в выделенном диапазоне при соответствии множествам условиям. Если условие у вас только 1, можно обойтись формулой СУММЕСЛИ.

=СУММЕСЛИМН(что суммируем; диапазон значений для условия 1; условие 1; диапазон значений для условия 2; условие 2)

Посчитаем сумму продаж клиенту 1001 за январь.// Для этого в строке формул записываем =СУММЕСЛИМН(G2:G31;C2:C31;1001;B2:B31;1)

сложи все значения в столбце Сумме, где в столбце Месяц указано 1, а в столбце КодКлиента указано 1001.

Результат — 22600.

Я сталкивалась максимум с 5 условиями, что очень упростило мне жизнь.

СЧЕТЕСЛИМН

Формула очень похожа на предыдущую, только здесь считается не сумма, а количество строк, соответствующих заданному условию.

=СЧЁТЕСЛИМН(где смотрим 1; что ищем 1; где смотрим 2; что ищем 2)

Теперь посчитаем для того же клиента 1001 количество продаж в январе. Для этого запишем =СЧЁТЕСЛИМН(C2:C31;C2;B2:B31;B2). Здесь указывала значения немного иначе (так тоже можно, но не забывайте закреплять ячейки, если собираетесь протягивать.

посчитай количество строк, где в диапазоне C2:C31 указано значение как в ячейке C2, в диапазоне B2:B31 — как в B2.

Результат — 2.

ЕСЛИ + СЧЁТ

Не знаю, почему, но это моя любимая формула. Я пользуюсь ей на регулярной основе. Она позволяет проверить, если ли указываемое значение и диапазоне.

Например, мы хотим проверить, была ли отгрузка у клиентов. Опять же, когда клиентов мало, это можно сделать и вручную, но, предположим, клиентов у нас 1000. Каждого из них копировать и вставлять в фильтр таблицы 1 — идея так себе. Для этого используем следующее:

=ЕСЛИ(СЧЁТЕСЛИ(где ищем; что ищем)>0; что выводить, если нашли; что выводить, если не нашли)

То есть в строке формул мы запишем =ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$25;E2)>0;«Да»;«Нет»). И результат будет следующим:

Почему-то сначала мне было сложновато запомнить эту формулу и ее постоянно гуглила. Но сейчас отскакивает от зубов)))

ЕСЛИОШИБКА

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

С помощью функции ЕСЛИОШИБКА мы можем заменить значение ошибки на любое другое.

=ЕСЛИОШИБКА(применяемая функция; значение, которое надо выводить на месте ошибки)

В этом примере я решила вывести на месте ошибки «Не найдено». Для удобства можете также использовать условное форматирование, чтобы пользователь отчета мог ярче видеть не найденные значения.

В этой формуле на месте значения, которое на надо выводить в случае ошибки можно использовать и пустоту =ЕСЛИОШИБКА(ИНДЕКС($D$2:$D$9;ПОИСКПОЗ(K10;$A$2:$A$9;0));»»).

Заключение

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