Как использовать функцию Excel OFFSET (плюс советы)

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

Что такое СМЕЩЕНИЕ в Excel?

СМЕЩЕНИЕ в Excel — это функция, которая возвращает ячейку или диапазон ячеек на основе опорной точки. Пользователи вводят контрольную точку и направления для программы, чтобы отойти от контрольной точки. Когда программа достигает новой точки, формула указывает, какие окружающие ячейки следует вернуть. Формула выглядит следующим образом: =СМЕЩ(ссылка, строки, столбцы, [height], [width]).

Вот определения для каждого из аргументов:

• Ссылка. Ссылка – это ячейка или диапазон ячеек, выступающий в качестве отправной точки формулы.

• Ряды: Ряды — это количество строк, на которые формула перемещается вверх или вниз от начальной точки.

• Cols: Cols — это количество столбцов, на которые формула перемещается влево или вправо от начальной точки.

• Высота: Высота определяет высоту возвращаемого диапазона.

• Ширина: Ширина указывает, насколько широким будет возвращаемый диапазон.

Когда использовать Excel OFFSET

Многие люди используют функцию OFFSET, когда хотят получить самые последние результаты динамического отчета. Например, они могут рассчитать средние продажи за последние шесть месяцев. Функция СМЕЩ позволяет им включить самые последние данные в формулу СРЗНАЧ, чтобы завершить этот расчет. Пользователи также могут интегрировать функцию OFFSET в другие формулы, которые могут обрабатывать диапазоны, некоторые из наиболее распространенных включают:

Как использовать функцию Excel СМЕЩ

Вот как использовать функцию OFFSET:

1. Вставьте функцию OFFSET

Щелкните ячейку, в которую вы хотите, чтобы функция возвращала значение или значения. Введите «=OFFSET» и нажмите «Enter» на клавиатуре. Либо нажмите на вкладку «Формулы» и перейдите в группу «Библиотека функций». Нажмите «Поиск и ссылка» и нажмите «СМЕЩЕНИЕ».

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

А**В**С**112015=СМЕЩЕНИЕ(214035
313045
416025
5**15055

2. Введите аргумент ссылки

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

А**В**С**112015=СМЕЩЕНИЕ(A1,214035
313045
416025
5**15055

3. Введите аргумент строк

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

А**В**С**112015=СМЕЩЕНИЕ(A1,3,214035
313045
416025
5**15055

4. Введите аргумент cols

Начните с контрольной точки и подсчитайте, сколько столбцов потребуется, чтобы добраться до столбца ячейки, которую вы хотите, чтобы функция возвращала. Это число является аргументом столбцов, который вы можете ввести после аргумента строк. Введите запятую, чтобы закрыть аргумент. В этом примере аргумент cols равен 1, потому что ячейка, которую вы хотите, чтобы функция возвращала (B4), находится на расстоянии одного столбца от опорной точки (A1).

А**В**С**112015=СМЕЩЕНИЕ(A1,3,1,214035
313045
416025
5**15055

5. Введите аргументы высоты и ширины

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

А**В**С**112015=СМЕЩЕНИЕ(А1,3,1,1,1)214035
313045
416025
5**15055

6. Нажмите «Ввод».

После написания формулы закройте скобки и нажмите «Enter». Функция возвращает значение на основе предоставленных вами инструкций. В этом примере функция начинается с A1 и считает в обратном порядке три строки и одну строку. Он приземляется на ячейку B4 и возвращает диапазон одной ячейки на одну ячейку. Это приводит к тому, что функция отображает значение 25, которое было таким же значением в ячейке B4.

А**В**С**11201525214035
313045
416025
515055
Связанный:[How To Search in Excel in 5 Steps (Plus Tips)**](https://buom.ru/career-advice/career-development/how-to-search-in-excel)

Советы по использованию этой функции

Примите во внимание следующие советы при использовании функции СМЕЩЕНИЕ в Excel:

Создание ссылок на несколько ячеек

Хотя аргумент ссылки часто представляет собой одну ячейку, он также может состоять из нескольких ячеек. Введите функцию OFFSET как обычно и введите желаемый диапазон в качестве ссылочного аргумента, завершив его запятой. Вы также можете щелкнуть мышью и перетащить ее через нужный диапазон. Например, если вы хотите сослаться на ячейки A1 и B1, аргументом ссылки будет «A1:B1».

Если вы хотите, чтобы функция выводила значения B4, вы должны сдвинуть исходный контрольный диапазон на три строки вниз и на один столбец. Окончательная функция СМЕЩЕНИЯ будет выглядеть так: “=СМЕЩЕНИЕ(A1:B1,3,1,1,1)”. Обратите внимание, что эта функция технически помещает новый эталонный диапазон в B4 и C4, но возвращает только значение B4, поскольку высота и ширина ограничивают функцию B4.

Вернуть несколько значений

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

Чтобы перейти от A1 к C4, вы должны пройти три строки вниз и пройти через два столбца. Рассмотрим C4 в качестве отправной точки для диапазона возврата. Укажите, что вы хотите, чтобы функция возвращала значения C4 и C5, указав высоту как 2 и ширину как 1. Если вы ввели функцию «=СМЕЩЕНИЕ(A1,3,1,2,1)» в D1, это отобразит значения C4 и C5 в D1 и D2. Если бы возвращаемый диапазон был строкой, а не столбцом, функция отображала бы значения по горизонтали, а не по вертикали.

А**В**С**Г**1120151211214035101531304513
41602511
51505515

Оставьте аргументы высоты и ширины пустыми.

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

Используйте отрицательные числа для аргументов rows и cols

Положительные числа в качестве аргументов rows и cols указывают функции двигаться вниз или вправо от контрольной точки. Если вы хотите переместиться вверх или влево от контрольной точки, вы можете использовать отрицательные числа. Например, представьте, что вы назначаете ссылочный аргумент как C5. Если вы хотите, чтобы функция возвращала значение B3, используйте функцию “=СМЕЩЕНИЕ(C5,-2,-1,1,1)”.

A**B**C**112015122140351031304513416025115*1505515Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с Indeed.*