Секреты функции СМЕЩ в Excel: от простых к продвинутым задачам

Функция СМЕЩ в Excel (OFFSET в английской версии) является одной из наиболее мощных и гибких функций для работы с данными. Это мощный инструмент, который позволяет создавать динамические ссылки на ячейки. Она позволяет пользователям получать доступ к данным в таблице на основе относительного смещения от заданной начальной точки. Эта функция может быть использована для создания динамических диапазонов, которые автоматически адаптируются при добавлении или удалении строк или столбцов данных.

В этой статье вы узнаете:

• Как использовать функцию СМЕЩ для создания динамических ссылок.
• Как использовать ее для решения сложных задач.
• Секреты и приемы использования.

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

Функция Excel СМЕЩ – правила использования 

Формула СМЕЩ возвращает ссылку на диапазон, который смещен от начальной ячейки или диапазона ячеек на указанное число строк и столбцов.

Синтаксис СМЕЩ следующий:

СМЕЩ(ссылка, смещение_строк, смещение_столбцов, [высота], [ширина])

Первые 3 аргумента являются обязательными, а последние 2 – необязательными. Все аргументы могут быть ссылками на другие ячейки или результатами, возвращаемыми другими формулами.

Функция использует пять аргументов:

• Ссылка: Начальная точка, от которой будет производиться смещение.
• Смещение_строк: Количество строк, на которое следует сместиться от начальной точки.
• Смещение_столбцов: Количество столбцов, на которое следует сместиться от начальной точки.
• Высота: Необязательный аргумент, определяющий, сколько строчек в возвращаемом диапазоне.
• Ширина: Необязательный аргумент, определяющий количество столбцов в возвращаемом диапазоне. 

Аргументы высоты и ширины всегда должны быть положительными числами, согласно документации Microsoft. Если какой-либо из них опущен, по умолчанию используется высота или ширина ссылки. И не всегда они должны быть отрицательными, о чем мы подробнее поговорим ниже.

Примечание. СМЕЩ — волатильная функция, которая может замедлить работу вашего рабочего листа. Медлительность прямо пропорциональна количеству пересчитанных ячеек.

А теперь проиллюстрируем теорию на примерах.

Как работает формула СМЕЩ в Excel

Функция СМЕЩ в Excel позволяет смещать ссылку на ячейку или диапазон ячеек на определенное число строк и столбцов. Вот те шаги, которые мы должны выполнить:

• Определите начальную точку: Выберите ячейку, от которой будете двигаться. Например, A1.
• Укажите смещение по строкам: Определите, на сколько позиций вниз (положительное число) или вверх (отрицательное число) вы хотите сместиться от начальной точки.
• Укажите смещение по столбцам: Укажите, на сколько позиций вправо (положительное число) или влево (отрицательное число) вы хотите переместиться.
• Задайте высоту и ширину диапазона (не обязательно): Укажите сколько строк и столбцов в новом диапазоне, который вы хотите получить.

Вот пример простой формулы, которая возвращает ссылку на новую ячейку на основе указанной вами начальной точки, количества строчек и колонок для перемещения:

=СМЕЩ(A1;4;1)

Формула предписывает Excel взять ячейку A1 в качестве начальной точки (ссылка), затем переместить на 4 позиции вниз (смещение_строк) и на 1 позицию влево (смещение_столбцов). В результате возвращаем значение из ячейки B5.

На скриншоте ниже показан маршрут и действия, которые выполняет функция.

Далее скриншоте ниже показано, как можно использовать формулу СМЕЩ для реальных данных. Как видите, выражение СМЕЩ(A1;H1;H2) включает ссылки на ячейки (H1 и H2) в аргументах строк и столбцов. 

Из ячейки А1 мы переместились на 5 позиций вниз и 3 позиции вправо, закончили движение в D6 и затем вернули значение из нее в ячейку G2. 

Аргументы высота и ширина не указаны, поэтому возвращаем столько же значений, сколько было в первом аргументе ссылка. То есть, в данном случае – одну ячейку.

Далее на простом примере покажем, как можно вернуть диапазон значений. 

=СМЕЩ(A1;1;G1;9;1)

Начальная точка – A1. Перемещаемся на 1 позицию вниз, затем – на 3 вправо. Так мы попадаем в столбец 3-го месяца (марта), номер которого указан в ячейке G1. Возвращаем диапазон из одного столбца и девяти строк, как указано в формуле в аргументах высота и ширина.

То же самое мы можем выполнить чуть иначе. Для этого сразу укажем в первом аргументе не просто адрес начальной ячейки, а адрес диапазона. И будем смещать этот диапазон на 3 столбца вправо.

=СМЕЩ(A2:A10;0;G1)

Здесь мы не указывали высоту и ширину итогового диапазона, поэтому его размер будет точно таким же, как и у диапазона в аргументе ссылка.

Также мы можем получить все значения по нужной строке. Например, данные для белого шоколада. Формула в G2:

=СМЕЩ(A1;5;1;1;4)

Формула Excel СМЕЩ – что следует помнить

• В данном случае Excel фактически не перемещает ячейки или диапазоны, а просто возвращает ссылку.
• Когда формула возвращает диапазон ячеек, аргументы высота и ширина всегда относятся к левой верхней ячейке возвращаемого диапазона.
• Аргумент ссылка должен указывать на ячейку или диапазон ячеек, иначе ваша формула вернет ошибку#ЗНАЧ! .
• Если указанные строки и/или столбцы перемещают ссылку за край электронной таблицы, ваша формула вернет ошибку #ССЫЛКА! .
• Функцию СМЕЩ можно использовать в любой другой функции Excel, которая в качестве аргумента использует ссылку на ячейку или диапазон.
• В документации Excel говорится, что высота и ширина не могут быть отрицательными. Однако и отрицательные значения работают.

Например, формула =СМЕЩ(D11; -1; -1;-3;-3) возвратит ссылку на диапазон A8:С10. Сначала мы поднимаемся на 1 строку выше и на 1 столбец левее от начальной ячейки D11. Затем от D11 создаем диапазон на 3 строки выше и 3 столбца левее, то есть A8:С10.

Вы можете видеть это на скриншоте ниже.

• Если вы попытаетесь использовать формулы  СМЕЩ(D11; -1; -1;-3;-3) или СМЕЩ(A1;3;1;3;3) в Excel 2019 и более ранних версиях, которые не поддерживают динамические массивы, она выдаст ошибку #ЗНАЧ!.Это произойдет потому, что возвращаемый диапазон размерностью 3 строки и 3 столбца нельзя поместить в одну ячейку, в которой записана формула. Однако, если вы встроите эту формулу в функцию СУММ, например:

=СУММ(СМЕЩ(A1;3;1;3;3))

В результате получите сумму значений в диапазоне 3 строк и 3 столбцов, который находится на 3 строки ниже и 3 столбца правее от ячейки A1, т. е. сумму значений в ячейках B4:D7. Об этом мы более подробно поговорим ниже в этой статье.

Для чего может пригодиться функция СМЕЩ в Excel?

Теперь, когда вы знаете, что делает функция СМЕЩ, вы можете спросить себя: «Зачем ее использовать?». Почему бы просто не записать прямую ссылку, например B2:B10?

Формула Excel СМЕЩ отлично подходит для создания динамических диапазонов. Ссылки типа B1:C4 являются статическими, то есть они всегда относятся к заданному диапазону. 

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

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

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

Примеры формулы СМЕЩ 

Примеры, которые представлены ниже, демонстрируют использование СМЕЩ для суммирования, нахождения максимума или минимума, создания динамического диапазона, выпадающего списка, а также для поиска значений, который невозможно выполнить при помощи ВПР. 

1. Формула СУММА+СМЕЩ

Ранее мы уже видели, как можно получить диапазон значений при помощи формулы СМЕЩ. В ячейку G1 запишем номер месяца, который нас интересует. Затем при помощи СМЕЩ получим столбец данных за этот месяц. Передаем его в функцию СУММ и получаем итог продаж за март: 362.

=СУММ(СМЕЩ(A2:A10;0;G1))

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

2. Динамическая формула СУММА+СМЕЩ

При работе с постоянно обновляемыми таблицами вам может пригодиться формула СУММ, которая автоматически выбирает все вновь добавленные строки.

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

На скриншоте ниже вы видите, как в таблицу была добавлена новая строка, которые старые формулы суммирования не учитывают.

В целом есть два варианта: 

• обновлять диапазон в формуле СУММ каждый раз вручную, 
• использовать формулу СМЕЩ, которая сделает это за вас.

Поскольку первая ячейка суммируемого диапазона будет указана непосредственно в формуле СУММ, вам нужно только определиться с параметрами функции СМЕЩ, которая получит эту последнюю ячейку суммируемого диапазона:

• ссылка – ячейка, содержащая сумму, в нашем случае Е12.
• Смещение_строк – перемещаемся в ячейку прямо над суммой, для чего требуется отрицательное число -1.
• Смещение_столбцов – это 0, потому что вы не хотите менять столбец.

Итак, вот шаблон формулы динамического суммирования:

=СУММ( первая ячейка :(СМЕЩ( ячейка с итогом ; -1; 0)

С учетом приведенного выше примера формула выглядит следующим образом:

=СУММ(E2:СМЕЩ(E12;-1;0))

И, как показано на скриншоте выше, это работает отлично.

3. Сумма каждых N строк

Чтобы суммировать каждые N строк, вы можете использовать формулу, так же использующую СМЕЩ и СУММ.
В показанном примере формула в F1:

=СУММ(СМЕЩ($B$2;(СТРОКА(A1)-1)*3;0;3;1))

где n=3, потому что каждый квартал содержит 3 строки ежемесячных данных. Когда формула копируется вниз, она возвращает сумму следующих трех значений в диапазоне B5:B7, затем – B8:B10 и так далее.

Также можно использовать номера кварталов, чтобы последовательно перебирать данные по 3 строки:

=СУММ(СМЕЩ($B$2;(E1-1)*3;0;3;1))

Поясним эти расчеты. Начальная точка – В2. Поскольку все нужные данные находятся в этом же стролбе, аргумент смещение_столбца всегда равен 0. Нам нужно получить диапазон из 3 строк и одного столбца, начиная с начальной точки. Поэтому аргумент смещение_строки для первого квартала также должен быть 0.

Делаем это любым из двух способов: либо номер первой строки минус 1, либо номер первого квартала минус 1. 

Далее при копировании формулы вниз номер строки меняется на 2, ссылка на номер квартала будет вести в ячейку E2 с числом 2. В результате аргумент смещение_строки будет равен (2-1)*3, то есть 3. То есть, от ячейки В2 мы смещаемся на 3 строки вниз в ячейку В5 и от нее берем диапазон 3 строки и 1 столбец, то есть В5:В7. Передаем его в функцию СУММ.

И так далее при копировании формулы вниз по столбцу Е.

4. Сумма последних N строк

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

Для этой задачи мы будем использовать СМЕЩ в сочетании с функциями СУММ и СЧЕТ/СЧЕТЗ:

=СУММ(СМЕЩ(B1;СЧЁТ(B:B)-E1+1;0;E1;1))

или

=СУММ(СМЕЩ(B1;СЧЁТЗ(B:B)-E1;0;E1;1))

Функция СЧЕТ возвращает количество ячеек в столбце B, содержащих числа, из которых вы вычитаете последние N месяцев (число — это ячейка E1), и добавляете 1 (так как заголовок столбца – это текст).

Если вы выбрали функцию СЧЕТЗ, вам не нужно добавлять 1, поскольку эта функция подсчитывает все непустые ячейки, а строка заголовка с текстовым значением добавляет дополнительную ячейку, необходимую нашей формуле. 

Обратите внимание, что эта формула будет корректно работать только на такой структуре таблицы — одна строка заголовка, за которой следуют строки с числами. Для разных макетов таблиц может потребоваться внести некоторые изменения в формулу СМЕЩ/СЧЕТЗ.

• количество столбцов для смещения равно нулю (0).
• число строк для суммирования указано в E1.
• ширина - 1 столбец.

5. Создание динамического диапазона 

Функция СМЕЩ может использоваться с функцией СЧЁТ для создания динамического диапазона, который автоматически обновляется при добавлении новых данных.

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

=СМЕЩ(A1; 0; 0; СЧЁТ(A:A); 1)

Эта формула создаст диапазон, начинающийся с A1 и продолжающийся вниз до последней заполненной ячейки в столбце A.

Подробный пример вы можете посмотреть здесь: Динамический выпадающий список при помощи функции СМЕЩ.

Получение диапазона из начальной ячейки . Иногда вы можете не знать фактический адрес диапазона, хотя знаете, что он начинается с определенной ячейки. В таких сценариях использование СМЕЩ в Excel — правильный путь.

В ситуации, когда вы хотите использовать смещение от текущей ячейки, вы можете использовать функции ДВССЫЛ и АДРЕС в сочетании с СТРОКА() и СТОЛБЕЦ(), чтобы получить начальную ссылку. Например:

=СУММ(СМЕЩ(ДВССЫЛ(АДРЕС(СТРОКА(); СТОЛБЕЦ())); 1; 1; 3; 1))

Эта формула означает, что от текущей ячейки нужно перейти на 1 строку вниз и на 1 слолбец вправо, взять от этой точки диапазон в 3 строки и 1 столбец, затем сложить эти значения.

6. Копировать значение из каждого n-го столбца

Чтобы копировать значения или генерировать ссылки с шаблоном, например, каждый 3-й столбец, каждый 5-й столбец и т. д., вы можете использовать формулу, основанную на функциях СМЕЩ и СТОЛБЕЦ. В нашем примере формула в I4:

=СМЕЩ($B$4;0;(СТОЛБЕЦ(A4)*2)-1)

При копировании вправо мы можем получить каждое второе значение из строки 4, начиная со столбца В.

В Excel вы не можете легко создавать формулы, которые пропускают столбцы по определенному шаблону, потому что ссылки в формуле автоматически изменятся, чтобы поддерживать связь между исходной ячейкой и новой целевой ячейкой. Однако с небольшой работой можно построить ссылки на формулы, которые будут следовать определенному шаблону с помощью формулы СМЕЩ.

Функция СМЕЩ предназначена для создания ссылок с помощью "смещений" из начальной ячейки. В показанном примере начальная ячейка – В4, записанная в формуле в качестве абсолютной ссылки, поэтому она не изменится при копировании формулы.

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

(СТОЛБЕЦ(A4)*2)-1)

Мы используем A4 в функции СТОЛБЕЦ, чтобы вернуть 1 (поскольку A - первый столбец), затем умножать на N (что в данном случае 2, так как нужен каждый второй столбец), чтобы получить 2.

Когда формула копируется вправо, значение, возвращаемое СТОЛБЕЦ, увеличивается на 1. Умножение на 2 дает каждый второй столбец (последовательность 2, 4, 6 ….)

Если вы хотите начать копирование с первого значения, вы можете изменить формулу следующим образом:

=СМЕЩ($B$4;0;(СТОЛБЕЦ(A4)-1)*2)

Вычитая 1, мы устанавливаем нулевое смещение столбца в первой формуле. И затем при копировании вправо каждый раз добавляется 2.

Копировать в строки вместо столбцов

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

=СМЕЩ($B$4;0;(СТРОКА(A1))*2-1)

Скопируйте формулу вниз по столбцу, и результат вы видите на скриншоте ниже.

Здесь функция СТОЛБЕЦ была заменена функцией СТРОКА и ссылкой на первую строку в столбце, чтобы отсчет начинался с 1.

7. Копировать значения из каждой N-й строки

Чтобы копировать значения или создавать ссылки с шаблоном, например каждая 3-я строка и т. д., вы можете использовать формулу с использованием функций СМЕЩ+СТРОКА. 

В примере ниже формула в D2:

=СМЕЩ($B$2;СТРОКА(A1)*3-1;0)

При копировании формулы вниз мы получаем список значений из каждой 3-й строки в столбце B. 

Логика построения формулы здесь аналогична предыдущему примеру со столбцами. Начиная с ячейки B2, ссылку на которую мы зафиксировали в формуле, смещаемся вниз на каждую третью строку.

8. Использование с СРЗНАЧ, МАКС, МИН.

Точно так же, как мы рассчитали продажи за последние N месяцев , вы можете получить среднее значение за последние N дней, недель или лет, а также найти их максимальные или минимальные значения. Единственное различие между формулами — это используемая первая функция:

=СРЗНАЧ(СМЕЩ(B1;СЧЁТ(B:B)-E1+1;0;E1;1))

=МАКС(СМЕЩ(B1;СЧЁТ(B:B)-E1+1;0;E1;1))

=МИН(СМЕЩ(B1;СЧЁТ(B:B)-E1+1;0;E1;1))

Ключевое преимущество этих формул по сравнению с обычными СРЗНАЧ(B2:B13) или МАКС(B2:B13) заключается в том, что вам не придется обновлять формулу каждый раз, когда обновляется исходная таблица. 

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

9. Создание динамического диапазона

Использование функции СМЕЩ может быть очень полезным для создания динамических диапазонов, которые автоматически обновляются при изменении данных в таблице. Например, вы можете использовать ее в сочетании с функцией СЧЁТ, чтобы создать диапазон, который адаптируется к количеству заполненных ячеек в столбце.

Пример формулы динамического диапазона:

=СМЕЩ(A1; 0; 0; СЧЁТЗ(A:A); 1)

Эта формула создаст диапазон, начинающийся с A1 и продолжающийся вниз до последней заполненной ячейки в столбце A.

В основе этой формулы вы используете функцию СЧЕТЗ, чтобы получить количество непустых ячеек в целевом столбце. Это число передается в аргумент высоты, указывающий, сколько строк нужно вернуть.

Аргументы смещение_строк и ширина равны 0, так здесь мы используем только данные из текущего столбца.

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

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

Подробные пошаговые инструкции по созданию динамического раскрывающегося списка в Excel можно найти здесь: Создаем динамический выпадающий список в Excel при помощи формул.

10. СМЕЩ И ВПР

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

Пример 1. Формула СМЕЩ для левого ВПР в Excel

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

Более подробно об этой особенности функции ВПР вы можете прочитать здесь.

Чтобы извлечь данные, которые находятся слева от столбца поиска, вам нужна более универсальная функция, которой не важно, где находится возвращаемый столбец. Одним из возможных решений является использование комбинации функций СМЕЩ, ПОИСКПОЗ и СТРОКИ. Вот формула в общем виде: 

СМЕЩ(таблица_поиска; ПОИСКПОЗ(искомое_значение; СМЕЩ(таблица_поиска; 0; смещение_столбца_поиска; ЧСТРОК(таблица_поиска); 1);0) -1; смещение_столбца_результата ; 1; 1)

Где:

• Смещение_столбца_поиска  — количество столбцов, на которое нужно сместиться от начального столбца в таблице поиска, чтобы указать на столбец поиска.
• Смещение_столбца_результата — количество столбцов, на которое нужно сместиться от начального столбца в таблице поиска, чтобы указать на столбец, из которого будет извлекать значения формула.

Рассмотрим этот прием “левого ВПР” на простом примере. Найдем номер заказа по наименованию товара.

Формула поиска будет выглядеть так:

=СМЕЩ(A2:C9; ПОИСКПОЗ(E2; СМЕЩ(A2:C9; 0; 1; ЧСТРОК(A2:C9); 1);0) -1; 0; 1; 1)

Результат вы видите на скриншоте ниже.

В нашем примере таблица поиска — A2:C9, а искомое значение находится в ячейке Е2. 

Смещение_столбца_поиска — 1. Поясним: поскольку мы будем искать искомое значение во втором столбце (B) таблицы поиска, нам нужно сместиться на 1 столбец вправо от начала таблицы поиска. Если бы в нашем примере мы искали номер заказа по количеству, то нужно было бы указать число 2, так как искать мы собирались бы в столбце С, а это 2 шага от начала таблицы поиска.

Смещение_столбца_результата  равно 0, поскольку мы возвращаем значения из первого столбца (A) таблицы поиска.

Я понимаю, что формула выглядит немного громоздкой, но она работает :)

Если этот способ покажется вам слишком сложным, можете попробовать еще посмотреть здесь:  4 способа, как сделать левый ВПР в Excel.

Пример 2. Как выполнить поиск сверху в Excel

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

Если вам нужно извлечь значение из первой строки таблицы, а искать значение – во второй строке, формула СМЕЩ ПОИСКПОЗ может снова помочь. Но на этот раз вам придется расширить ее с помощью функции ЧИСЛСТОЛБ:

СМЕЩ(таблица_поиска; смещение_строки_результата ; ПОИСКПОЗ(искомое_значение ; СМЕЩ(таблица_поиска; смещение _строки_поиска ; 0; 1; ЧИСЛСТОЛБ(таблица_поиска)); 0) -1; 1; 1)

Где:

• Смещение_строки_поиска  — количество строк, на которое нужно сместиться от первой строки в таблице поиска, чтобы указать на строку поиска.
• Смещение_строки_результата — количество строк, на которое нужно сместиться от первой строки в таблице поиска, чтобы указать на строку, из которой будет извлекать значения формула.

В нашем примере таблица поиска находится в диапазоне B1:F2, а искомое значение находится в ячейке I1. Формула выглядит следующим образом:

=СМЕЩ(B1:F2; 0; ПОИСКПОЗ(I1; СМЕЩ(B1:F2; 1; 0; 1; ЧИСЛСТОЛБ(B1:F2)); 0) -1; 1; 1)

Вот результат ее работы: 

В нашем случае смещение строки поиска равно 1, поскольку наш диапазон поиска находится на 1 строку ниже первой строки таблицы поиска.  Смещение строки результата равно 0, поскольку мы возвращаем значения из первой строки таблицы.

Пример 3. Двусторонний поиск (по строке и столбцу)

Двусторонний поиск возвращает значение на основе совпадений одновременно в строке и в столбце. И вы можете использовать следующую формулу массива двойного поиска, чтобы найти значение на пересечении определенной строки и столбца:

=СМЕЩ(таблица_поиска; ПОИСКПОЗ(значение_поиска_строки ; СМЕЩ(таблица_поиска;0;0; ЧСТРОК(таблица_поиска);1);0)-1; ПОИСКПОЗ(значение_поиска_столбца ; СМЕЩ(таблица_поиска;0;0;1; ЧИСЛСТОЛБ(таблица_поиска));0)-1;1;1)

Рассмотрим эту большую формулу на простом примере.

Вот исходные данные:

• Таблица поиска: A1:E9 (включаем заголовки строк и столбцов).
• Значение, которое должно соответствовать нужной строке, находится в H2.
• Значение, которое должно соответствовать нужному столбцу, находится в H1.

Вы получите следующую формулу двумерного поиска:

=СМЕЩ(A1:E9; ПОИСКПОЗ(H2; СМЕЩ(A1:E9;0;0; ЧСТРОК(A1:E9);1);0)-1;  ПОИСКПОЗ(H1; СМЕЩ(A1:E9;0;0;1; ЧИСЛСТОЛБ(A1:E9));0)-1;1;1)

На скриншоте ниже вы можете видеть, как это работает.

Конечно, эта длинная формула — не единственный возможный способ выполнить поиск по строкам и столбцам в Excel. Тот же результат можно получить, используя функции ВПР и ПОИСКПОЗ, СУММПРОИЗВ или ИНДЕКС и ПОИСКПОЗ. 

Есть даже способ без формул — использовать именованные диапазоны и оператор пересечения (пробел). В следующем руководстве подробно описаны все альтернативные решения: 5 способов – поиск значения в массиве Excel по строкам и столбцам.

Функция СМЕЩ – особенности и ограничения

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

Наиболее критические ограничения заключаются в следующем:

• Как и другие изменчивые функции, она является ресурсоемкой функцией. Всякий раз, когда на рабочем листе происходят какие-либо изменения, ваши формулы пересчитываются, что заставляет Excel выполнять вычисления немного дольше. Это не проблема для одной формулы в небольшой электронной таблице. Но если в книге десятки или сотни формул, пересчет может занять довольно много времени.
• Формулы СМЕЩ сложны для понимания. Поскольку ссылки, возвращаемые функцией, являются динамическими, большие формулы (особенно с вложенными СМЕЩ) могут быть довольно сложными для отладки.
• Также следует избегать использования СМЕЩ в формулах массива, так как иногда это может привести к неожиданным результатам.

Альтернативы использованию СМЕЩ в Excel

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

1. Таблицы Excel

Начиная с Excel 2002 у нас появилась поистине замечательная возможность — полноценные таблицы Excel , в отличие от привычных диапазонов. Чтобы создать таблицу из структурированных данных, просто нажмите «Вставка» > «Таблица» на вкладке «Главная» или нажмите Ctrl + Т.

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

Более того, любая формула, которая ссылается на данные таблицы, автоматически корректируется, включая любые новые строки, которые вы добавляете в таблицу, или исключая строки, которые вы удаляете. Технически такие формулы работают со столбцами или строками таблицы, которые по своей природе являются динамическими диапазонами . Каждая таблица в книге имеет уникальное имя (по умолчанию — «Таблица1», «Таблица2» и т. д.).

Вместо формулы суммирования, которую мы рассмотрели ранее: СУММ(СМЕЩ(A2:A10;0;G1)) вы можете использовать сумму столбца таблицы:

=СУММ(Таблица1[Продажи])

Столбец [Продажи] является динамическим диапазоном и без всяких формул автоматически расширяется при добавлении в таблицу новых данных.

2. Функция ИНДЕКС

Хотя функция ИНДЕКС работает не совсем так же, как СМЕЩ, ее также можно использовать для создания ссылок на динамический диапазон. В отличие от СМЕЩ, функция ИНДЕКС не пересчитывается постоянно, поэтому она не замедлит работу Excel. Подробнее о ней читайте здесь: Функция ИНДЕКС в Excel — 6 примеров использования.

3. Функция ДВССЫЛ 

Используя функцию ДВССЫЛ, вы можете создавать ссылки на динамические диапазоны из многих источников, таких как значения ячеек, значения ячеек и текст, именованные диапазоны. Он также может динамически ссылаться на другой лист или книгу Excel. Вы можете найти все эти примеры формул в нашем руководстве по функции ДВССЫЛ.

Часто задаваемые вопросы

Функция СМЕЩ в Excel предлагает пользователям гибкость и мощь для работы с динамическими данными. Она идеально подходит для создания динамических отчетов и анализа данных.

Однако, как и любой инструмент, её следует использовать осторожно и с умом, чтобы избежать потенциальных проблем с производительностью.

Помните вопрос, заданный в начале этой статьи: Что такое СМЕЩ в Excel? Надеюсь, теперь вы знаете ответ :) 

Функция ВПР в Excel: пошаговая инструкция с 5 примерами

Формула ВПР в Excel для сравнения двух таблиц - 4 способа

Формула ВПР в Excel — 22 факта, которые нужно знать.

Почему не работает ВПР в Excel?

Вычисление номера столбца для извлечения данных в ВПР

ВПР с несколькими условиями: 5 примеров.

ВПР в нескольких таблицах Excel: простое руководство

4 способа, как сделать левый ВПР в Excel.