Функция СМЕЩ в Excel

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

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

Описание функции СМЕЩ

СМЕЩЕНИЕ (ref; line_offset; column_offset; [высота]; [ширина])
Возвращает ссылку на диапазон смещения от указанной ссылки для указанного количества строк и столбцов.

• Смещение по строкам (обязательно) и по столбцам (обязательно) — количество строк и столбцов, результирующий диапазон которых смещен исходной ссылкой; Например, аргументы 4; 3 переместит ссылку на 4 строки вниз и на 3 столбца вправо. В этом случае оба параметра могут иметь разные значения: положительное (смещение по строкам / справа по столбцам), нулевое или отрицательное (смещение по строкам / слева по столбцам).
• Высота (необязательно) и ширина (необязательно) — это высота (в строках) и ширина (в столбцах) возвращаемого диапазона, по умолчанию высота и ширина такие же, как размер исходной ссылки; Например, аргументы 5; 2 расширит ссылку до диапазона из 5 ячеек по высоте и 2 ячеек по ширине.

Давайте посмотрим на несколько примеров, чтобы понять, как работает функция СМЕЩЕНИЕ:

Например, формула = OFFSET (A1; 0; 0; 5; 4) (выделено красным на рисунке) перемещает ячейку A1 (аргумент функции # 1) на 0 (# 2) вниз, 0 (# 3) вправо, мы получаем диапазон A1 (одна ячейка), а затем расширяем его до размера 5 (# 4) на 4 (# 5), например, возвращаемая ссылка принимает форму A1: D5 (на рисунке также область выделена красным).
Точно так же формула = OFFSET (A1; 1; 2; 8; 3) (выделена синим) перемещает ячейку A1 вниз на 1, 2 вправо, мы получаем диапазон C2 и расширяем его до размера 8 для 3, например, соответственно, мы получаем ссылку C2: E9.

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

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

Пример использования функции СМЕЩ

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

Пример 1. Функция ПОИСКПОЗ

Предположим, у вас есть данные о ежедневных продажах компании и вы хотите определить продажи на основе определенного числа.

Мы используем функцию ПОИСКПОЗ, чтобы найти указанную дату (ячейка D2) в диапазоне дат (A2: A10).
Затем мы перемещаем начальную ячейку (в данном случае B2) вниз на рассчитанное значение минус один.
Мы также вычитаем единицу, поскольку мы показываем точное смещение относительно начальной ячейки, например, чтобы перейти от первой строки к шестой, мы перемещаем ровно пять строк.
В результате получаем следующий результат:

Идентичный результат можно получить с помощью функции ИНДЕКС: формула = ИНДЕКС (B2: B10; ПОИСК (D2; A2: A10; 0)) вернет точно такой же результат.

Пример 2. Функция СУММ

Возьмем начальные условия, как в предыдущем примере, а теперь посчитаем сумму продаж за последние 7 дней.
Вы можете использовать стандартную формулу СУММ (B4: B10), но при добавлении новых строк расчет становится неверным, и нам придется каждый раз менять формулу, поэтому мы выберем другой путь.
Используя функцию COUNT, мы находим последнюю вставленную дату (мы указываем интервал A2: A100, достаточно большой, чтобы можно было добавлять новые данные).
Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, затем, перемещая начальную ячейку (B2) на найденное значение и расширяя диапазон до размеров 7 на 1, мы получим данные последних 7 дней.
Обобщим их с помощью функции СУММ:

При добавлении новых данных в таблицу результат будет автоматически пересчитан:

Особенности применения

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