Что возвращает функция
Возвращает ссылку со смещением на указанное количество ячеек.
Синтаксис
= OFFSET (ссылка, строки, столбцы, [высота], [ширина]) — английская версия
= OFFSET (link; line_offset; column_offset; [height]; [width]) — русская версия
Аргументы функции
- ссылка — ссылка на ячейку, от которой требуется смещение. Это может быть ссылка на ячейку или диапазон смежных ячеек;
- rows (row_offset) — количество строк для смещения от их исходной позиции. Если указать положительное число, строки переместятся вниз, если отрицательное — вверх;
- cols (column_offset) — количество столбцов для смещения от их исходной позиции. Если указать положительное число, столбцы сместятся вправо, если отрицательное — влево;
- [height] ([height]) — количество строк в указанном диапазоне функции;
- [ширина] ([ширина]) — количество столбцов в указанном диапазоне функции.
Основной принцип работы функции
Функция СМЕЩЕНИЕ, пожалуй, самая запутанная функция в Excel.
Давайте посмотрим, как это работает, на простом примере шахматной игры. В шахматах фигура ладьи.
Согласно правилам игры в шахматы ладья может двигаться только вправо, влево, вниз и вверх. Фигура не может двигаться по диагонали.
Теперь представим, что наша Башня должна двигаться не строго влево или вправо, а в ячейку, расположенную по диагонали от исходного положения. Что мы будем делать в этом случае?
Правильно, мы воспользуемся несколькими шагами, чтобы привести Ладью к цели. Тот же принцип применяется к функции СМЕЩЕНИЕ) .
Давайте посмотрим на перемещение Башни на примере в Excel. Мы хотим начать с ячейки D5 (где находится башня), а затем спуститься на две строки и два столбца вправо и извлечь значение из ячейки. Для этого воспользуемся формулой:
= СМЕЩЕНИЕ (начальная позиция, на сколько строк переместиться вниз, на сколько столбцов переместиться вправо) — английская версия
= СМЕЩЕНИЕ (начальная позиция, на сколько строк переместиться вниз, на сколько столбцов переместиться вправо) — Русская версия
Как видите, формула для нашего примера выглядит так:
= OFFSET (D5,2,2) — английская версия
= OFFSET (D5; 2; 2) — русская версия
Функция получает аргумент для начала отсчета от ячейки «D5», затем смещения на две строки вниз, а затем на два столбца вправо. Это переместит нас из ячейки «D5» в ячейку «F7». По завершении хода функция возвращает значение ячейки «F7”.
В приведенном выше примере мы рассмотрели функцию СМЕЩЕНИЕ с тремя аргументами. Но есть два других необязательных аргумента, которые вы можете использовать.
Давайте посмотрим на простой пример:
Предположим, вы хотите сослаться на ячейку «A1» (желтая) и хотите сослаться на весь выделенный синим цветом диапазон (C2: E4) в формуле.
Как бы вы это сделали с помощью клавиатуры? Сначала вам нужно перейти к ячейке C2, а затем выбрать все ячейки в диапазоне «C2: E4”.
Теперь посмотрим, как это сделать по формуле СМЕЩЕНИЕ) :
= OFFSET (A1,1,2,3,3) — английская версия
= OFFSET (A1; 1; 2; 3; 3) — русская версия
Если вы используете эту формулу в ячейке, она вернет # ЗНАЧ! Но если вы войдете в режим редактирования, выберите формулу и нажмите клавишу «F9», вы увидите, что она возвращает все значения, выделенные синим цветом.
Надеюсь, теперь у вас есть базовые представления об использовании функции СМЕЩЕНИЕ в Excel.
Примеры использования функции СМЕЩ в Excel
Пример 1. Ищем последнюю заполненную ячейку в колонке
Допустим, у вас есть данные в одном столбце. Чтобы отобразить последнее значение в столбце, используйте формулу:
= OFFSET (A1; COUNT (A: A) -1,0) — английская версия
= OFFSET (A1; COUNT (A: A) -1,0) — русская версия
Эта формула предполагает, что нет других значений, кроме указанных значений, и что в этом столбце нет пустых ячеек. Функция работает, подсчитывая общее количество заполненных ячеек и перемещая соответственно ячейку «A1”.
Например, в приведенном выше примере имеется 8 значений, поэтому функция COUNT (A: A) или COUNT (A: A) возвращает 8. Ячейка «A1» смещена на 7, чтобы получить последнее значение.
Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных
вы можете использовать принцип Примера 1 для создания динамического раскрывающегося списка с автоматическим добавлением новых данных. Например, вы создали раскрывающийся список и хотите, чтобы значения автоматически загружались в раскрывающийся список при добавлении новых строк.
Обратите внимание, что в приведенном выше примере значения автоматически появляются и исчезают из раскрывающегося списка, как только вы вносите какие-либо изменения в диапазон ячеек, указанный для раскрывающегося списка.
Это связано с тем, что формула, используемая для создания раскрывающегося меню, является динамической, обнаруживает любые добавления или удаления и соответствующим образом корректирует диапазон.
Как составить такой список:
- Выберите ячейку, в которой вы хотите создать раскрывающийся список;
- Щелкните вкладку Данные => Инструменты данных => Значение данных> = СМЕЩЕНИЕ (A1,0,0, СЧЁТ (A: A), 1) или = СМЕЩЕНИЕ (A1; 0; 0; СЧЁТ (A: A); 1)
- Нажмите ОК
Как работает эта формула:
Первые три аргумента функции СМЕЩЕНИЕ — это A1, 0, 0. Это означает, что начальное значение в ячейке «A1», которое не сдвигается ни по строке, ни по столбцу (0, 0);
Четвертый аргумент функции указывает высоту, и здесь функция СЧЁТ возвращает общее количество ячеек в диапазоне данных для раскрывающегося списка. Главное условие — в диапазоне не было пустых ячеек.
Пятый аргумент функции «1» указывает ширину диапазона данных, который в нашем случае равен одному столбцу.
Дополнительная информация
- Функция СМЕЩЕНИЕ — непостоянная функция. Он пересчитывается каждый раз при открытии файла Excel. Эта функция может существенно повлиять на скорость всего файла.
- Если высота и ширина не указаны, функция принимает только первые три аргумента;
- Если строки (row_offset) и столбцы (column_offset) отрицательны, смещение будет в противоположном направлении.
Альтернативы функции OFFSET (СМЕЩ) в Excel
Из-за некоторых ограничений функций многие из вас рассматривают альтернативные методы:
- Функция ИНДЕКС также может использоваться для возврата ссылки на ячейку.
- Электронные таблицы Excel: если вы используете структурированные ссылки в электронной таблице Excel, вам не нужно беспокоиться о добавлении новых данных и необходимости изменения формул.
СМЕЩ (функция СМЕЩ)
Эта функция возвращает ссылку на диапазон, который представляет собой указанное количество строк и столбцов из ячейки или диапазона ячеек. Возвращенная ссылка может быть одной ячейкой или диапазоном ячеек. Вы можете указать количество возвращаемых строк и столбцов.
Синтаксис
Аргументы функции СМЕЩЕНИЕ описаны ниже.
Требуется подключение. Ссылка, по которой рассчитывается смещение. Ссылка должна быть ссылкой на ячейку или диапазоном смежных ячеек, в противном случае СМЕЩЕНИЕ возвращает значение ошибки # ЗНАЧ!.
Offset_line Обязательно. Количество строк, которые необходимо подсчитать в большую или меньшую сторону, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку. Например, если вы укажете 5 в качестве значения line_offset, верхняя левая ячейка возвращаемой ссылки должна быть на пять строк ниже, чем та, на которую указывает ссылка. Значение line_offset может быть положительным (для ячеек ниже начальной ссылки) или отрицательным (выше начальной ссылки).
Column_offset Обязательный. Количество столбцов для подсчета слева или справа, чтобы верхняя левая ячейка результата ссылалась на нужную ячейку. Например, если вы укажете 5 в качестве значения для Column_offset, верхняя левая ячейка возвращаемой ссылки должна быть на пять столбцов справа от значения, указанного в аргументе Reference. Column_offset может быть положительным (для ячеек справа от начальной ссылки) или отрицательным (слева от начальной ссылки).
Высота Не обязательна. Высота (количество строк) возвращаемой ссылки. Высота должна быть положительным числом.
Ширина Не обязательна. Ширина (количество столбцов) возвращаемой ссылки. Ширина должна быть положительным числом.
Примечания
Если Row_offset и Column_offset перемещают ссылку за пределы рабочего листа, OFFSET возвращает значение ошибки #REF!.
Если высота или ширина опущены, предполагается, что используется та же высота или ширина, что и у ссылочного аргумента».
Функция СМЕЩЕНИЕ фактически не перемещает ячейки и не изменяет выделение; он возвращает только ссылку. Функцию OFFSET можно использовать с любой функцией, которая ожидает ссылочный аргумент. Например, формула СУММ (СМЕЩ (C2; 1; 2; 3; 1)) вычисляет общее значение диапазона из трех строк и одного столбца, одной строки ниже и двух столбцов справа от ячейки C2.
Пример
Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы просмотреть результаты формул, выберите их и нажмите F2, затем нажмите Enter. При необходимости измените ширину столбцов, чтобы увидеть все данные.
Функция СМЕЩ в Excel
Давайте рассмотрим примеры использования функции СМЕЩЕНИЕ в Excel, которая возвращает смещение ссылки на указанное количество строк и столбцов от исходной ссылки.
На первый взгляд, функция СМЕЩЕНИЕ довольно сложна для понимания, что часто отговаривает пользователя от ее использования и поэтому незаслуженно редко используется.
Однако он может быть очень полезен (например, при создании динамических диапазонов) и при правильном использовании значительно упрощает вашу работу.
Описание функции СМЕЩ
СМЕЩЕНИЕ (ref; line_offset; column_offset; [высота]; [ширина])
Возвращает ссылку на диапазон смещения от указанной ссылки для указанного количества строк и столбцов.
- Ссылка (обязательный аргумент) — ссылка на соседнюю ячейку или диапазон ячеек;
- Смещение по строкам (обязательно) и по столбцам (обязательно): количество строк и столбцов, результирующий диапазон которых смещен исходной ссылкой; Например, аргументов 4; 3 переместит ссылку на 4 строки вниз и на 3 столбца вправо. В этом случае оба параметра могут иметь разные значения: положительное (смещение по строкам / справа по столбцам), нулевое или отрицательное (смещение по строкам / слева по столбцам).
- Высота (необязательно) и ширина (необязательно) — высота (в строках) и ширина (в столбцах) возвращаемого диапазона, по умолчанию высота и ширина такие же, как у исходной ссылки; Например, аргументов 5; 2 расширит ссылку до диапазона из 5 ячеек по высоте и 2 ячеек по ширине.
Давайте посмотрим на несколько примеров, чтобы понять, как работает функция СМЕЩЕНИЕ:
Например, формула = OFFSET (A1; 0; 0; 5; 4) (выделена красным на рисунке) перемещает ячейку A1 (аргумент функции # 1) на (# 2) вниз, на (# 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 дней.
Обобщим их с помощью функции СУММ:
При добавлении новых данных в таблицу результат будет автоматически пересчитан:
Особенности применения
Функция СМЕЩЕНИЕ имеет еще одну отличительную особенность: она непостоянна (пересчитывается).
В отличие от большинства других функций, которые пересчитываются только при изменении ячеек, являющихся их аргументами, СМЕЩЕНИЕ пересчитывается при изменении любой ячейки.
Следовательно, эта функция может замедлить работу книги, поэтому следует использовать формулу с осторожностью.
Суммирование по «окну» на листе функцией СМЕЩ (OFFSET)
Бывают ситуации, когда заранее неизвестно, какие клетки на листе нужно подсчитывать. Например, предположим, что вам нужно реализовать небольшой калькулятор транспортировки в Excel для расчета расстояния от одной станции до другой:
В выпадающих списках в желтых ячейках F3 и F5 пользователь выбирает станции отправления и назначения, а в зеленой ячейке F7 должна быть вычислена сумма всех ячеек в «окне», указанном на листе. Чтобы проехать от Останкино до Ховрино, как, например, на рисунке, вам нужно будет просуммировать все ячейки диапазона, обведенные зеленым.
Как посчитать сумму понятно, но как определить диапазон ячеек, которые нужно добавить? Ведь при выборе станций она будет постоянно трансформироваться?
В такой ситуации может помочь функция СМЕЩЕНИЕ, которая может предоставить ссылку на «плавающее окно», диапазон определенного размера, расположенный в определенной точке на листе. Синтаксис функции следующий:
= СМЕЩЕНИЕ (Опорная_точка; Смещение вниз; Смещение вправо; Высота; Ширина)
Эта функция выводит ссылку на диапазон, сдвинутый от начальной ячейки (reference_point) на определенное количество строк вниз и столбцов вправо. Кроме того, размер интервала («окна») также можно задать параметрами Высота и Ширина.
В нашем случае, если мы возьмем за точку отсчета ячейку A1, то:
- Контрольная точка = A1
- Shift_down = 4
- Right_shift = 2
- Высота = 4
- Ширина = 1
Чтобы вычислить аргументы, необходимые для СМЕЩЕНИЯ, мы сначала используем функцию ПОИСКПОЗ, о которой мы говорили ранее, чтобы вычислить положения станций отправления и назначения:
И, наконец, мы используем функцию СМЕЩЕНИЕ, чтобы получить ссылку на желаемое «окно» на листе и просуммировать все ячейки из него:
Все, проблема решена
В отличие от большинства других функций Excel, СМЕЩЕНИЕ — это непостоянная или, как говорится, «непостоянная» функция. Обычные функции пересчитываются только при изменении ячеек и их аргументов. Летучие пересчитываются каждый раз при смене ячейки. Конечно, это негативно сказывается на производительности. На больших и тяжелых столах разница в книжной скорости может быть очень заметной (временами). В некоторых случаях быстрее заменить медленное СМЕЩЕНИЕ энергонезависимым ИНДЕКСОМ или другим подобным.
