Функция смещения Excel (пример + видео)
Когда использовать функцию смещения Excel
Функцию СМЕЩЕНИЕ в Excel можно использовать, когда вы хотите получить ссылку, которая смещает указанное количество строк и столбцов от начальной точки.
Что это возвращает
Он возвращает ссылку, на которую указывает функция OFFSET.
Синтаксис
= СМЕЩЕНИЕ (ссылка; строки; столбцы; [высота]; [ширина])
Входные аргументы
- Справка - Ссылка, от которой вы хотите выполнить компенсацию. Это может быть ссылка на ячейку или диапазон соседних ячеек.
- ряды - количество строк для смещения. Если вы используете положительное число, оно смещается к строкам ниже, а если используется отрицательное число, оно смещается к строкам выше.
- cols - количество столбцов для смещения. Если вы используете положительное число, оно смещается к столбцам справа, а если используется отрицательное число, оно смещается к столбцам слева.
- [высота] - это число, которое представляет количество строк в возвращаемой ссылке.
- [ширина] - это число, которое представляет количество столбцов в возвращаемой ссылке.
Понимание основ функции смещения Excel
Функция СМЕЩЕНИЕ в Excel, возможно, одна из самых запутанных функций в Excel.
Возьмем простой пример шахматной партии. В шахматах есть фигура, называемая ладьей (также известная как башня, маркиз или ректор).
[Изображение предоставлено: Прекрасная Википедия]
Теперь, как и все другие шахматные фигуры, ладья имеет фиксированный путь, по которому она может перемещаться по доске. Он может идти прямо (вправо / влево или вверх / вниз по доске), но не может идти по диагонали.
Например, предположим, что у нас есть шахматная доска в Excel (как показано ниже), в данном поле (в данном случае D5) ладья может двигаться только в четырех выделенных направлениях.
Теперь, если я попрошу вас перевести ладью из ее текущего положения в положение, выделенное желтым, что вы скажете ладье?
Вы попросите его сделать два шага вниз и два шага вправо… не так ли?
И это очень близко к тому, как работает функция СМЕЩЕНИЕ.
Теперь давайте посмотрим, что это означает в Excel. Я хочу начать с ячейки D5 (где находится Ладья), а затем пройти две строки вниз и два столбца вправо и получить значение из этой ячейки.
Формула будет такой:
= СМЕЩЕНИЕ (с чего начать, сколько строк вниз, сколько столбцов вправо)
Как видите, формула в приведенном выше примере в ячейке J1 имеет вид = СМЕЩЕНИЕ (D5,2,2).
Он начинался с D5, затем шел на две строки вниз и на два столбца вправо и доходил до ячейки F7. Затем он вернул значение в ячейке F7.
В приведенном выше примере мы рассмотрели функцию СМЕЩЕНИЕ с 3 аргументами. Но есть еще два необязательных аргумента, которые можно использовать.
Давайте посмотрим на простой пример:
Предположим, вы хотите использовать ссылку на ячейку A1 (желтым цветом) и хотите сослаться на весь диапазон, выделенный синим (C2: E4) в формуле.
Как бы вы это сделали с помощью клавиатуры? Сначала вы должны перейти к ячейке C2, а затем выбрать все ячейки в C2: E4.
Теперь давайте посмотрим, как это сделать с помощью формулы СМЕЩЕНИЕ:
= СМЕЩЕНИЕ (A1,1,2,3,3)
Если вы используете эту формулу в ячейке, она вернет # ЗНАЧ! ошибка, но если вы войдете в режим редактирования, выберите формулу и нажмите F9, вы увидите, что она возвращает все значения, выделенные синим цветом.
Теперь посмотрим, как работает эта формула:
= СМЕЩЕНИЕ (A1,1,2,3,3)
- Первый аргумент - это ячейка, с которой он должен начинаться.
- Второй аргумент - 1, который сообщает Excel, что нужно вернуть ссылку, которая была СМЕЩЕНА на 1 строку.
- Третий аргумент - 2, который указывает Excel вернуть ссылку, которая была СМЕЩЕНА на 2 столбца.
- Четвертый аргумент - 3. Он указывает, что ссылка должна занимать 3 строки. Это называется аргументом высоты.
- Пятый аргумент - 3. Он указывает, что ссылка должна охватывать 3 столбца. Это называется аргументом ширины.
Теперь, когда у вас есть ссылка на диапазон ячеек (C2: E4), вы можете использовать ее в другой функции (например, SUM, COUNT, MAX, AVERAGE).
Надеюсь, вы хорошо разбираетесь в использовании функции СМЕЩЕНИЕ в Excel. Теперь давайте посмотрим на несколько практических примеров использования функции СМЕЩЕНИЕ.
Функция СМЕЩЕНИЕ в Excel - Примеры
Вот два примера использования функции СМЕЩЕНИЕ в Excel.
Пример 1 - Поиск последней заполненной ячейки в столбце
Предположим, у вас есть данные в столбце, как показано ниже:
Чтобы найти последнюю ячейку в столбце, используйте следующую формулу:
= СМЕЩЕНИЕ (A1; СЧЁТ (A: A) -1,0)
Эта формула предполагает, что нет значений, кроме показанных, и в этих ячейках нет пустых ячеек. Он работает, подсчитывая общее количество заполненных ячеек, и соответственно смещает ячейку A1.
Например, в этом случае имеется 8 значений, поэтому COUNT (A: A) возвращает 8. Мы смещаем ячейку A1 на 7, чтобы получить последнее значение.
Пример 2 - Создание динамического раскрывающегося списка
Вы можете расширить концепцию, показанную в Примере 1, для создания динамических именованных диапазонов. Это может быть полезно, если вы используете именованные диапазоны в формулах или при создании раскрывающихся списков, и вы, вероятно, добавляете / удаляете данные из ссылки, которая составляет именованный диапазон.
Вот пример:
Обратите внимание, что раскрывающийся список настраивается автоматически при добавлении или удалении года.
Это происходит потому, что формула, которая используется для создания раскрывающегося списка, является динамической и определяет любое добавление или удаление и соответствующим образом корректирует диапазон.
Вот как это сделать:
- Выберите ячейку, в которую вы хотите вставить раскрывающийся список.
- Перейдите в Data -> Data Tools -> Data Validation.
- В диалоговом окне «Проверка данных» на вкладке «Настройки» выберите «Список» в раскрывающемся списке.
- В источнике введите следующую формулу: = OFFSET (A1,0,0; COUNT (A: A), 1)
- Щелкните ОК.
Посмотрим, как работает эта формула:
- Первые три аргумента функции СМЕЩЕНИЕ - это A1, 0 и 0. Это, по сути, означает, что она вернет ту же ссылочную ячейку (то есть A1).
- Четвертый аргумент предназначен для высоты, и здесь функция COUNT возвращает общее количество элементов в списке. Предполагается, что в списке нет пробелов.
- Пятый аргумент - 1, что указывает на то, что ширина столбца должна быть равна единице.
Дополнительные замечания:
- СМЕЩЕНИЕ - непостоянная функция (используйте с осторожностью).
- Он пересчитывается всякий раз, когда открыта книга Excel или когда на листе запускается вычисление. Это может увеличить время обработки и замедлить вашу книгу.
- Если значение высоты или ширины опущено, оно принимается как эталонное.
- Если ряды и cols отрицательные числа, то направление смещения меняется на противоположное.
Альтернативы функции смещения Excel
Из-за некоторых ограничений функции СМЕЩЕНИЕ в Excel вы многие захотите рассмотреть альтернативы ей:
- ИНДЕКС Функция: функция ИНДЕКС также может использоваться для возврата ссылки на ячейку. Щелкните здесь, чтобы увидеть пример создания динамического именованного диапазона с помощью функции ИНДЕКС.
- Таблица Excel: если вы используете структурированные ссылки в таблице Excel, вам не нужно беспокоиться о добавлении новых данных и необходимости корректировки формул.
Функция смещения Excel - видеоурок
- Функция ВПР в Excel.
- Функция Excel HLOOKUP.
- Функция ИНДЕКС Excel.
- Функция НЕПРЯМАЯ в Excel.
- Функция ПОИСКПОЗ в Excel.
