Давайте рассмотрим примеры использования функции СМЕЩЕНИЕ в Excel, которая возвращает смещение ссылки на указанное количество строк и столбцов от исходной ссылки.
На первый взгляд, функция СМЕЩЕНИЕ довольно сложна для понимания, что часто отговаривает пользователя от ее использования и поэтому незаслуженно редко используется.
Однако он может быть очень полезен (например, при создании динамических диапазонов) и при правильном использовании значительно упрощает вашу работу.
Ссылка (обязательный аргумент) — ссылка на соседнюю ячейку или диапазон ячеек;СМЕЩЕНИЕ (ref; line_offset; column_offset; [высота]; [ширина])
Возвращает ссылку на диапазон смещения от указанной ссылки для указанного количества строк и столбцов.
Давайте посмотрим на несколько примеров, чтобы понять, как работает функция СМЕЩЕНИЕ:
Например, формула = 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 возвращает ссылку, поэтому ее можно использовать с другими функциями, которые имеют ссылки между аргументами.
Поэтому теперь мы рассмотрим, как использовать эту формулу вместе с другими, используя стандартный типичный пример деятельности.
Предположим, у вас есть данные о ежедневных продажах компании и вы хотите определить продажи на основе определенного числа.
Мы используем функцию ПОИСКПОЗ, чтобы найти указанную дату (ячейка D2) в диапазоне дат (A2: A10).
Затем мы перемещаем начальную ячейку (в данном случае B2) вниз на рассчитанное значение минус один.
Мы также вычитаем единицу, поскольку мы показываем точное смещение относительно начальной ячейки, например, чтобы перейти от первой строки к шестой, мы перемещаем ровно пять строк.
В результате получаем следующий результат:
Идентичный результат можно получить с помощью функции ИНДЕКС: формула = ИНДЕКС (B2: B10; ПОИСК (D2; A2: A10; 0)) вернет точно такой же результат.
Возьмем начальные условия, как в предыдущем примере, а теперь посчитаем сумму продаж за последние 7 дней.
Вы можете использовать стандартную формулу СУММ (B4: B10), но при добавлении новых строк расчет становится неверным, и нам придется каждый раз менять формулу, поэтому мы выберем другой путь.
Используя функцию COUNT, мы находим последнюю вставленную дату (мы указываем интервал A2: A100, достаточно большой, чтобы можно было добавлять новые данные).
Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, затем, перемещая начальную ячейку (B2) на найденное значение и расширяя диапазон до размеров 7 на 1, мы получим данные последних 7 дней.
Обобщим их с помощью функции СУММ:
При добавлении новых данных в таблицу результат будет автоматически пересчитан:
Функция СМЕЩЕНИЕ имеет еще одну отличительную особенность: она непостоянна (пересчитывается).
В отличие от большинства других функций, которые пересчитываются только при изменении ячеек, являющихся их аргументами, СМЕЩЕНИЕ пересчитывается при изменении любой ячейки.
Следовательно, эта функция может замедлить работу книги, поэтому следует использовать формулу с осторожностью.
