Смещение в Excel, как использовать

Обновлено: 20.11.2024

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

В финансовом анализе Анализ финансовой отчетности Как выполнить анализ финансовой отчетности. Это руководство научит вас выполнять анализ финансового отчета отчета о прибылях и убытках, мы часто используем сводные таблицы и диаграммы. Функцию OFFSET можно использовать для создания динамического именованного диапазона для сводных таблиц или диаграмм, чтобы убедиться, что исходные данные всегда актуальны.

Формула

= OFFSET(ссылка, строки, столбцы, [высота], [ширина])

Функция OFFSET использует следующие аргументы:

Ссылка (обязательный аргумент) — это диапазон ячеек, который необходимо сместить. Это может быть как одна ячейка, так и несколько ячеек.
Строки (обязательный аргумент). Это количество строк от начала (вверху слева) предоставленной ссылки до начала возвращаемого диапазона.
Cols (обязательный аргумент) – количество столбцов от начала (верхний левый угол) предоставленной ссылки до начала возвращаемого диапазона.
Высота (необязательный аргумент). Указывает высоту возвращаемого диапазона. Если он опущен, возвращаемый диапазон имеет ту же высоту, что и указанный аргумент ссылки.
Ширина (необязательный аргумент). Указывает ширину возвращаемого диапазона. Если он опущен, возвращаемый диапазон имеет ту же ширину, что и предоставленная ссылка.

Как использовать функцию OFFSET в Excel?

Как функцию рабочего листа, функцию СМЕЩ можно ввести как часть формулы в ячейку рабочего листа. Чтобы понять использование функции, рассмотрим несколько примеров:

Пример 1

Допустим, нам дан недельный доход за 5 недель ниже:

Теперь, если мы вставим формулу =СМЕЩ(A3,3,1) в ячейку B1, она даст нам значение 2500, которое находится на 3 строки ниже в правом столбце, как показано ниже:

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

Пример 2

В продолжение того же примера предположим, что нам нужен доход за четверг за все недели. В этом случае мы будем использовать формулу .

Мы получаем следующие результаты:

В приведенном выше примере:

Поскольку результаты функции СМЕЩ должны занимать более одной ячейки, необходимо ввести функцию в виде массива. Это видно по скобкам, окружающим формулу в строке формул.
В данной формуле, поскольку ширина возвращаемого диапазона больше, чем ширина эталонного диапазона, мы указали аргументы высоты и ширины.

Пример 3

Продолжая тот же пример, предположим, что нам нужна общая сумма доходов за неделю 3. Мы используем формулу =СУММ(СМЕЩ(G6,1,-2,5)).

Мы получаем следующие результаты:

В приведенном выше примере:

Массив значений, возвращаемый функцией СМЕЩ, напрямую вводится в функцию СУММ, которая возвращает одно значение. Следовательно, нам не нужно вводить его как формулу массива.
Высота диапазона смещения больше высоты контрольного диапазона, поэтому аргумент [высота] вводится как значение 5.
Ширина диапазона смещения такая же, как ширина опорного диапазона, поэтому аргумент [ширина] был исключен из функции.

Несколько замечаний о функции OFFSET:

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свое финансовое моделирование.Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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

<р>1. Приведенная ниже функция OFFSET возвращает ячейку, которая находится на 3 строки ниже и на 2 столбца справа от ячейки A2. Функция СМЕЩ возвращает ячейку, так как высота и ширина равны 1.

<р>2. Приведенная ниже функция OFFSET возвращает диапазон 1 x 2, который находится на 8 строк ниже и на 1 столбец справа от ячейки A2. Функция СУММ вычисляет сумму этого диапазона.

Последние 2 аргумента функции OFFSET являются необязательными. Когда высота и ширина опущены, новая ссылка имеет ту же высоту и ширину, что и начальная ссылка (первый аргумент). Как всегда, мы будем использовать простые примеры, чтобы было понятнее.

<р>3. Приведенная ниже функция OFFSET возвращает ячейку, расположенную на 12 строк ниже и на 0 столбцов справа от ячейки A2.

<р>4. Приведенная ниже функция OFFSET возвращает диапазон, который находится на 4 строки ниже и на 0 столбцов справа от диапазона B2:C2. Функция СУММ вычисляет сумму этого диапазона.

<р>5. Приведенная ниже функция OFFSET возвращает диапазон, который находится на 4 строки ниже и на 0 столбцов справа от диапазона B2:C2. На этот раз без функции СУММ.

Примечание. Прежде чем вставлять эту формулу, выберите диапазон E12:F12 (или другой диапазон того же размера). Завершите, нажав CTRL + SHIFT + ENTER. Строка формул указывает, что это формула массива, заключая ее в фигурные скобки <>. Чтобы удалить эту формулу массива, выберите диапазон E12:F12 и нажмите клавишу DELETE.

<р>6.Если у вас Excel 365 или Excel 2021, просто выберите ячейку E12, введите функцию OFFSET и нажмите Enter. Прощай, фигурные скобки.

Примечание: функция OFFSET, введенная в ячейку E12, заполняет несколько ячеек. Ух ты! Такое поведение в Excel 365/2021 называется сбросом. Наконец, если вы хотите вернуть ячейку или диапазон ячеек, которые находятся на указанное количество строк выше или столбцов слева, введите отрицательное число.

Функция СМЕЩ в Excel возвращает ссылку на диапазон, составленный из пяти входных данных: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцах. СМЕЩ удобен в формулах, требующих динамического диапазона.

ссылка – начальная точка, указанная в виде ссылки на ячейку или диапазона.
rows — количество строк, на которое нужно сместить начальную ссылку.
столбцы — количество столбцов, смещенных справа от начальной ссылки.
height – [необязательно] высота возвращаемой ссылки в строках.
width – [необязательно] ширина возвращаемой ссылки в столбцах.

Функция Excel OFFSET возвращает динамический диапазон, построенный с пятью входными данными: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцах.

Начальной точкой (аргументом ссылки) может быть одна ячейка или диапазон ячеек. Аргументы rows и cols — это количество ячеек, которые нужно «сместить» от начальной точки. Аргументы высоты и ширины являются необязательными и определяют размер создаваемого диапазона. Если высота и ширина не указаны, по умолчанию они равны высоте и ширине ссылки.

Например, для ссылки C5, начинающейся с A1, ссылка — это A1, строки – 4, а столбцы – 2:

Чтобы сослаться на C1:C5 из A1, ссылка — это A1, строки — 0, столбцы — 2, высота — 5, а ширина — 1:

Примечание: ширину можно не указывать, так как по умолчанию она равна 1.

Часто можно увидеть, что OFFSET заключен в другую функцию, которая ожидает диапазон. Например, для СУММ C1:C5, начиная с A1:

Основная цель OFFSET – позволить формулам динамически корректироваться в соответствии с доступными данными или пользовательским вводом. Функцию OFFSET можно использовать для создания динамического именованного диапазона для диаграмм или сводных таблиц, чтобы гарантировать, что исходные данные всегда актуальны.

Примечание. В документации Excel указано, что высота и ширина не могут быть отрицательными, но отрицательные значения отлично работают с начала 1990-х годов. Функция СМЕЩ в Google Таблицах не допускает отрицательного значения аргументов высоты или ширины.

Примеры

В приведенных ниже примерах показано, как можно настроить OFFSET для возврата различных типов диапазонов. Эти экраны были сняты с помощью Excel 365, поэтому СМЕЩ возвращает динамический массив, когда результатом является более одной ячейки. В более ранних версиях Excel вы можете использовать клавишу F9 для проверки результатов, возвращаемых функцией OFFSET.

На приведенном ниже экране мы используем OFFSET, чтобы вернуть третье значение (март) во втором столбце (запад). Формула в H4:

На приведенном ниже экране мы используем OFFSET, чтобы вернуть последнее значение (июнь) в третьем столбце (север). Формула в H4:

Ниже мы используем OFFSET для возврата всех значений в третьем столбце (север). Формула в H4:

Ниже мы используем OFFSET для возврата всех значений за май (пятая строка). Формула в H4:

Ниже мы используем OFFSET, чтобы получить значения апреля, мая и июня для западного региона. Формула в H4:

Ниже мы используем OFFSET, чтобы получить значения апреля, мая и июня для запада и севера. Формула в H4:

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

СМЕЩЕНИЕ – это встроенная функция рабочего листа, относящаяся к категории функций поиска/справок в Excel. Цель функции СМЕЩЕНИЕ Excel — вернуть ссылку на одну ячейку или диапазон ячеек на основе строк и столбцов, заданных в аргументах функции, из ячейки или диапазона ячеек.

Оглавление

Синтаксис

Аргументы:

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

строки — это обязательный аргумент, который указывает Excel, хотите ли вы перемещаться вниз или вверх по строкам, начиная со значения в ссылочном аргументе. Значение в аргументе rows может быть положительным или отрицательным. Положительное значение заставит Excel двигаться вниз, а отрицательное значение заставит Excel двигаться вверх от ячейки или диапазона в

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

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

Важные характеристики функции OFFSET

Примеры функции СМЕЩ

Давайте рассмотрим несколько примеров, чтобы просто посмотреть, что происходит, когда вы выполняете функцию OFFSET. Если вы предпочитаете, сделайте эти примеры рядом с электронной таблицей, чтобы получить хорошее представление о функции OFFSET.

1. Базовый пример функции OFFSET

Простая версия функции OFFSET

Введите ячейку B2 в качестве значения аргумента ссылки, 4 в качестве аргумента строк и 0 в качестве аргумента столбцов. С помощью этой формулы СМЕЩ вы указываете Excel возвращать значение в ячейке, которая находится на 4 строки вниз и на 0 столбцов вправо (т. е. в том же столбце), начиная со ссылочной ячейки B2. Посмотрите, сможете ли вы вручную обнаружить эту ячейку, а затем проверьте, что вернула функция. Функция возвращает значение «250» из ячейки B6.

Вы правильно поняли? Если нет, попробуйте следующий.

2. Возврат, когда ячейка не имеет значения

Давайте попробуем.

Введите ячейку C7 в качестве аргумента ссылки, 1 в качестве аргумента строк и -2 в качестве аргумента столбцов. Вот что вы только что поручили Excel: найти значение в ячейке, расположенной на 1 строку ниже и на 2 столбца левее ячейки C7.

Пройдитесь по этой инструкции и посмотрите, к какому значению вы пришли. Вы оказались в пустой камере? Ну и Эксель тоже. Поэтому он вернет «0».

3. Ссылки на ячейки для значений в аргументах строк и столбцов

Введите ячейку B2 в качестве значения для аргумента ссылки, ячейку A3 в качестве аргумента строк и 0 в качестве аргумента столбцов. Поскольку значение в ячейке A3 равно 2, Excel вернет значение на 2 строки ниже ячейки B2, что в нашем примере равно «150».

4. Используйте необязательные аргументы высоты и ширины

Ввод необязательных аргументов позволит вам получить диапазон ячеек. Давайте используем ячейку A1 в качестве значения для аргумента ссылки, 1 для аргумента строк и столбцов, 3 для необязательного аргумента высоты и 1 для необязательного аргумента ширины.

Вот что происходит, когда вы указываете Excel вернуть ячейку, которой нет на вашем листе.

Пример функции OFFSET + SUM

Мы возьмем ту же формулу, которую использовали при вводе необязательных аргументов высоты и ширины, и встроим ее в функцию СУММ, например так:

Вот что происходит при использовании этой формулы: формула СМЕЩ возвращает диапазон B2:B6, а затем функция СУММ суммирует значения в диапазоне (50 + 100 + 150 + 200 + 250). и верните значение '750'.

Пример функции OFFSET + AVERAGE, MIN и MAX

Допустим, вы пытаетесь отслеживать свои среднемесячные расходы. Вам нужна скользящая средняя за предыдущие 3 месяца, чтобы вы могли составить соответствующий бюджет. Для этого мы будем использовать комбинацию из 3 функций:

Функция СМЕЩЕНИЯ,
функция COUNTA или функция COUNT и
Функция СРЕДНИЙ, МИН., МАКС.

Итак, давайте быстро рассмотрим, как работает функция COUNTA или COUNT, встроенная в функцию OFFSET. Как только мы закончим, мы увидим, где функция СРЗНАЧ вписывается в уравнение.

Вы уже знаете, как суммировать диапазон, извлекая его с помощью функции OFFSET. С помощью функции COUNT наша цель состоит в том, чтобы автоматически включать только что добавленные точки данных, чтобы функция OFFSET возвращала соответствующий диапазон. Затем мы усредним этот диапазон, чтобы получить окончательное значение. Если вы не можете понять это, попробуйте решить это на этом примере:

Давайте разберем эту формулу. По сути, функция COUNT работает как аргумент rows в функции OFFSET. COUNT(B:B) вернет 5, то есть количество числовых точек данных в столбце B. Из этого мы вычтем значение в ячейке E4 (которое представляет собой количество точек данных, которые мы хотим суммировать из столбца B, начиная с последней точки данных — в нашем случае это 3). Затем мы добавим 1, потому что мы хотим включить 3 точки данных, исключение +1 вернет только последние 2 точки данных, поскольку вверху есть ячейка заголовка.

Если вы выберете функцию COUNTA, вам не нужно добавлять +1, потому что эта функция также будет подсчитывать нечисловую ячейку заголовка.

Итак, давайте посмотрим, как выглядит вся наша функция в тот момент, когда функция СЧЕТ возвращает 3 (т. е. СЧЕТ(B:B) = 5, – 3 и + 1):

В качестве следующего шага мы рассмотрим, что вернет функция OFFSET. Из нашего предыдущего примера мы знаем, что задание аргумента высоты возвращает диапазон. Здесь возвращаемый диапазон будет включать значения 150, 200 и 250 или диапазон B4:B6.

Это упрощает нашу функцию до:

Это вернет 200 [(150 + 200 + 250)/3].

Отлично, оставшаяся часть этого примера совсем несложная. Вам нужно только немного обновить формулу. Но сначала давайте посмотрим, почему нам это нужно.

Возможно, вместо усреднения расходов за последние 3 месяца вы решите просмотреть месяц, в котором вы потратили максимальную или минимальную сумму за последние 3 месяца.

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

Единственная разница, как видите, заключается в использовании функции MAX или MIN вместо функции среднего.

Это так просто.

Пример функции СМЕЩ + ВПР

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

Чтобы выполнить поиск влево, мы объединим функцию OFFSET с функциями MATCH и ROWS, например:

= OFFSET (table_range, MATCH (row_lookup_value, OFFSET (table_range,0,col_lookup_offset, ROWS (table_range),1),0)-1,col_return_offset,1,1)

Формула не такая сложная, если вы знаете аргументы. Вот для чего предназначен каждый аргумент:

table_range: этот аргумент определяет таблицу, в которой должна искать формула.
row_lookup_value: этот аргумент представляет собой значение, которое вы хотите сопоставить.
col_lookup_offset: этот аргумент указывает, сколько столбцов находится между столбцом подстановки и начальным столбцом.
col_return_offset: этот аргумент указывает, сколько столбцов находится между столбцом, из которого мы хотим получить возврат, и начальным столбцом.

Если у вас все еще есть небольшая проблема, быстро просмотрите функции ПОИСКПОЗ и РЯДЫ, и все будет готово.

Вот как это должно выглядеть на вашем листе:

Я поменял местами столбцы и использовал указанную формулу. Было бы невозможно получить эти выходные данные с помощью функции ВПР.

Здесь формула ищет в диапазоне table_range (т. е. A2:B6) значение row_lookup_value, указанное в ячейке E4 (т. е. 4). Поскольку нам нужно будет искать это значение row_lookup_value в столбце B, мы установим col_lookup_offset равным «1» для перемещения на 1 столбец вправо, начиная с 1-го столбца таблицы (т. е. I). Кроме того, поскольку значение, которое мы хотим, чтобы формула возвращала, находится в первом столбце, мы установим col_return_offset равным «0».

После выполнения формулы она возвращает «200», значение, которое находится слева от row_lookup_value «4».

Ограничения функции СМЕЩЕНИЯ в Excel

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

Вот 2 ограничения функции OFFSET:

Функция OFFSET требует больших ресурсов. Это не имеет большого значения, если вы работаете с довольно простой электронной таблицей с небольшим количеством формул. Однако, когда ваш лист загружен формулами, перерасчет формулы OFFSET, вызванный изменениями в исходных данных, может занять некоторое время в Excel.
Формулы OFFSET могут быть сложными для отладки. В частности, вложенные формулы OFFSET, которые возвращают динамические ссылки, могут вызвать у вас головокружение.

Альтернативы функции OFFSET

Подобно тому, как мы использовали функцию СМЕЩ для работы с ограничивающими факторами функции ВПР, существуют и другие функции Excel, которые могут изящно заменить функцию СМЕЩ. Вот 2 такие функции:

ИНДЕКС – это энергонезависимая функция, поэтому Excel не будет замедляться во время перерасчетов. Хотя функция ИНДЕКС не совсем похожа на функцию СМЕЩ, она способна создавать ссылки динамического диапазона.

ДВССЫЛ — это динамическая функция Excel, которая может ссылаться на другие листы или книги. Кроме того, он позволяет создавать ссылки на динамические диапазоны с использованием нескольких источников, таких как именованные диапазоны, значения ячеек (с помощью оператора «&») и текстовые строки.

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

Читайте также: