Функция СМЕЩ в Excel используется, когда вы хотите получить ссылку, которая смещается на указанное число строк и столбцов от начального положения.
- Что возвращает функция
- Синтаксис
- Аргументы функции
- Основной принцип работы функции
- Примеры использования функции СМЕЩ в Excel
- Пример 1. Ищем последнюю заполненную ячейку в колонке
- Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных
- Дополнительная информация
- Альтернативы функции OFFSET (СМЕЩ) в Excel
Что возвращает функция
Возвращает ссылку, которая смещается на заданное количество ячеек.
Синтаксис
=OFFSET(reference, rows, cols, [height], [width]) — английская версия
=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]) — русская версия
Аргументы функции
- reference (ссылка) — ссылка на ячейку, от которой вы хотите сделать смещение. Это может быть ссылка на ячейку или диапазон смежных ячеек;
- rows (смещ_по_строкам) — количество строк для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение строк ниже, если отрицательное — выше;
- cols (смещ_по_столбцам) — количество колонок для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение колонок вправо, если отрицательное число, то влево;
- [height] ([высота]) — количество строк в указанном диапазоне функции;
- [width] ([ширина]) — количество колонок в указанном диапазоне функции.
Основной принцип работы функции
Функция СМЕЩ, пожалуй, самая запутанная функция в Excel.
Давайте разберем ее работу на простом примере игры в шахматы. В шахматах есть фигура Ладья.
Источник фото: Wikipedia
По правилам игры в шахматы, Ладья может ходить только вправо, влево, вниз и вверх. Фигура не может передвигаться по диагонали.
Теперь давайте представим, что нашей Ладье нужно переместиться не строго влево или вправо, а на ячейку, находящуюся по диагонали от изначальной позиции. Что мы будем делать этом случае?
Правильно, мы будем использовать несколько шагов, для того чтобы привести Ладью к цели. Тот же принцип действует и в функции OFFSET (СМЕЩ).
Рассмотрим перемещение Ладьи на примере в Excel. Мы хотим начать с ячейки D5 (где находится ладья), а затем перейти на две строки вниз и два столбца вправо и извлечь значение из ячейки. Для этого будем использовать формулу:
=OFFSET(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) — английская версия
=СМЕЩ(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) — русская версия
Как вы видите формула по нашему примеру выглядит так:
=OFFSET(D5,2,2) — английская версия
=СМЕЩ(D5;2;2) — русская версия
Функции задан аргумент старта отсчета с ячейки «D5», затем смещение на две строки вниз, после этого на две колонки вправо. Так мы переместимся с ячейки «D5» на ячейку «F7». По завершении перемещения функция выдает значение ячейки «F7».
На примере выше мы рассмотрели функцию OFFSET (СМЕЩ) с тремя аргументами. Но есть еще два необязательных аргумента, которые можно использовать.
Давайте рассмотрим простой пример:
Предположим, вы хотите использовать ссылку на ячейку «A1» (желтую), и хотите сослаться на весь диапазон, выделенный синим (C2:E4) в формуле.
Как бы вы это сделали с помощью клавиатуры? Сначала нужно перейти к ячейке C2, а затем выбрать все ячейки в диапазоне «C2:E4».
Теперь посмотрим, как это сделать, используя формулу OFFSET (СМЕЩ):
=OFFSET(A1,1,2,3,3) — английская версия
=СМЕЩ(A1;1;2;3;3) — русская версия
Если вы используете эту формулу в ячейке, она вернет #VALUE! Но если вы перейдете в режим редактирования, выберете формулу и нажмите клавишу «F9», вы увидите, что она возвращает все значения, выделенные синим цветом.
Надеюсь, теперь у вас есть базовое понимание использования функции OFFSET (СМЕЩ) в Excel.
Примеры использования функции СМЕЩ в Excel
Пример 1. Ищем последнюю заполненную ячейку в колонке
Представим, что у вас есть данные в колонке. Для того чтобы отобразить последнее значение в колонке используйте формулу:
=OFFSET(A1,COUNT(A:A)-1,0) — английская версия
=СМЕЩ(A1;СЧЁТ(A:A)-1;0) — русская версия
Эта формула предполагает, что кроме указанных значений нет никаких других, и в этой колонке нет пустых ячеек. Функция работает, подсчитывая общее количество заполненных ячеек и соответствующим образом смещает ячейку «A1».
Например, в указанном примере есть 8 значений, поэтому функция COUNT(A:A) или СЧЁТ(A:A) возвращает 8. Мы смещаем ячейку «A1» на 7, чтобы получить последнее значение.
Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных
Вы можете использовать принцип из Примера 1 для создания динамического выпадающего списка с автоматическим дополнением новых данных. Например, вы создали выпадающий список и хотите, чтобы при добавлении новых строк, значения автоматически подгружались в выпадающий список.
Обратите внимание, что на примере выше, значения автоматически появляются и исчезают из выпадающего списка, как только вы вносите изменения в диапазон ячеек, указанный для выпадающего списка.
Это происходит, поскольку формула, которая используется для создания раскрывающегося списка, является динамической и определяет любое добавление или удаление и соответствующим образом корректирует диапазон.
Как сделать такой список:
- Выберите ячейку, в которой вы хотите создать выпадающий список;
- Нажмите на вкладку Data => Data Tools => Data Validation;
- В диалоговом окне Data Validation, в разделе Настройки выберите List из выпадающего списка;
- В параметрах Source укажите формулу =OFFSET(A1,0,0,COUNT(A:A),1) или =СМЕЩ(A1;0;0;СЧЁТ(A:A);1)
- Нажмите ОК
Как эта формула работает:
Первые три аргумента функции OFFSET (СМЕЩ) A1, 0, 0. Это означает что начальное значение в ячейке «A1», которое не смещается ни по строкам и по колонкам (0, 0);
Четвертый аргумент функции указывает на высоту, и здесь функция COUNT (СЧЁТ) возвращает суммарное количество ячеек в диапазоне данных для выпадающего списка. Главное условие — отсутствие пустых ячеек в диапазоне.
Пятый аргумент функции “1”, обозначает ширину диапазона данных, которая в нашем случае равна одной колонке.
Дополнительная информация
- Функция OFFSET (СМЕЩ) — волатильная функция. Она пересчитывается каждый раз, как только вы открываете Excel файл. Работа этой функции может сильно сказываться на скорости работы всего файла.
- Если значения высоты и ширины не указаны, функция учитывает только первые три аргумента;
- Если значения аргументов rows (смещ_по_строкам) и cols (смещ_по_столбцам) отрицательны, то смещение будет происходить в обратную сторону.
Альтернативы функции OFFSET (СМЕЩ) в Excel
Ввиду некоторых ограничений функции, многие из вас рассматривают альтернативные методы:
- Функция INDEX (ИНДЕКС) также может использоваться для возврата ссылки на ячейку.
- Excel таблицы: если вы используете структурированные ссылки в таблице Excel, вам не нужно беспокоиться о добавлении новых данных и необходимости корректировки формул.
Спасибо, помогло.
класс, отличное объяснение с шахматами, спасибо, здорово ! 🙂