Топ-100

Функция OFFSET (СМЕЩ) в Excel. Как использовать?

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

Что возвращает функция

Возвращает ссылку, которая смещается на заданное количество ячеек.

Синтаксис

=OFFSET(reference, rows, cols, [height], [width]) – английская версия

=СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]) – русская версия

Аргументы функции

  • reference (ссылка) – ссылка на ячейку, от которой вы хотите сделать смещение. Это может быть ссылка на ячейку или диапазон смежных ячеек;
  • rows (смещ_по_строкам) – количество строк для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение строк ниже, если отрицательное – выше;
  • cols (смещ_по_столбцам) – количество колонок для смещения от изначальной позиции. Если вы укажете положительное число, то произойдет смещение колонок вправо, если отрицательное число, то влево;
  • [height] ([высота]) – количество строк в указанном диапазоне функции;
  • [width] ([ширина]) – количество колонок в указанном диапазоне функции.

Основной принцип работы функции

Функция СМЕЩ, пожалуй, самая запутанная функция в Excel.

Давайте разберем ее работу на простом примере игры в шахматы. В шахматах есть фигура Ладья.

Функция OFFSET (СМЕЩ) в Excel. Как использовать?
Источник фото: Wikipedia

По правилам игры в шахматы, Ладья может ходить только вправо, влево, вниз и вверх. Фигура не может передвигаться по диагонали.

Функция OFFSET (СМЕЩ) в Excel. Как использовать?

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

Функция OFFSET (СМЕЩ) в Excel. Как использовать?

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

Рассмотрим перемещение Ладьи на примере в Excel. Мы хотим начать с ячейки D5 (где находится ладья), а затем перейти на две строки вниз и два столбца вправо и извлечь значение из ячейки. Для этого будем использовать формулу:

=OFFSET(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) – английская версия

=СМЕЩ(стартовая позиция, на сколько строк сместиться вниз, на сколько столбцов сместиться вправо) – русская версия

Функция OFFSET (СМЕЩ) в Excel. Как использовать?

Как вы видите формула по нашему примеру выглядит так:

=OFFSET(D5,2,2) – английская версия

=СМЕЩ(D5;2;2) – русская версия

Функции задан аргумент старта отсчета с ячейки “D5”, затем смещение на две строки вниз, после этого на две колонки вправо. Так мы переместимся с ячейки “D5” на ячейку “F7”. По завершении перемещения функция выдает значение ячейки “F7”.

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

Давайте рассмотрим простой пример:

OFFSET - СМЕЩ - в Excel

Предположим, вы хотите использовать ссылку на ячейку “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 (СМЕЩ) в Excel

=OFFSET(A1,COUNT(A:A)-1,0) – английская версия

=СМЕЩ(A1;СЧЁТ(A:A)-1;0) – русская версия

Эта формула предполагает, что кроме указанных значений нет никаких других, и в этой колонке нет пустых ячеек. Функция работает, подсчитывая общее количество заполненных ячеек и соответствующим образом смещает ячейку “A1”.

Например, в указанном примере есть 8 значений, поэтому функция COUNT(A:A) или СЧЁТ(A:A) возвращает 8. Мы смещаем ячейку “A1” на 7, чтобы получить последнее значение.

Пример 2. Создаем динамический выпадающий список с автоматическим дополнением новых данных

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

OFFSET (СМЕЩ) функция в Excel

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

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

Как сделать такой список:

  • Выберите ячейку, в которой вы хотите создать выпадающий список;
  • Нажмите на вкладку 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, вам не нужно беспокоиться о добавлении новых данных и необходимости корректировки формул.
Понравилась статья? Поделиться с друзьями:
Комментариев: 1
  1. ADiergintejery

    Torsion bras de quelqu’un est comment poupe votre sang pousse contre les parois de vos arteres lorsque votre coeur essence pompe le sang. Arteres sont les tubes qui transportent prendre offre sang loin de votre coeur. Chaque set votre determination bat, il pompe le sang a tous egards vos arteres a la prendre facilement de votre corps.
    https://www.cialispascherfr24.com/cialis-pas-cher-inde-king/

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Освойте ТОР 5 лайфхаков
для работы в Excel!

Введите ваш E-mail адрес и мы бесплатно пришлем вам ТОР 5 лучших приемов работы в Excel!

Ваше имя и E-mail

Введите ваше имя и e-mail и мы бесплатно пришлем к вам на почту ТОР 5 лайфхаков для работы в Excel

Спасибо!

 

Мы отправили лайфхаки к вам на почту!