Офсетная Формула в Microsoft Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: tips

VLOOKUP, HLOOKUP и INDEX-MATCH — известные и распространенные способы поиска значений в таблице Excel. Но это не единственные способы поиска значений в Excel. В этой статье мы узнаем, как использовать функцию СМЕЩЕНИЕ вместе с функцией ПОИСКПОЗ в Excel. Да, вы правильно прочитали, мы будем использовать печально известную функцию СМЕЩЕНИЯ Excel для поиска определенного значения в таблице Excel.

Общая формула

=OFFSET(StartCell,MATCH(RowLookupValue,RowLookupRange,0),MATCH(ColLookupValue,ColLookupRange,0))

Прочтите внимательно:

StartCell: это начальная ячейка таблицы поиска. Скажем, если вы хотите найти в диапазоне A2: A10, тогда StartCell будет A1.

RowLookupValue: это значение подстановки, которое вы хотите найти в строках ниже StartCell. RowLookupRange: это диапазон, в котором вы хотите найти RowLookupValue. Это диапазон ниже StartCell (A2: A10). ColLookupValue: это значение поиска, которое вы хотите найти в столбцах (заголовках).

ColLookupRange: это диапазон, в котором вы хотите найти ColLookupValue. Это диапазон в правой части StartCell (например, B1: D1).

Итак, достаточно теории. Начнем с примера.

Пример: поиск продаж с использованием функции СМЕЩЕНИЕ и СОВПАДЕНИЕ из таблицы Excel Здесь у нас есть примерная таблица продаж, выполненных разными сотрудниками в разные месяцы.

Теперь наш босс просит нас предоставить данные о продажах по Id 1004 за июнь. Есть много способов сделать это, но поскольку мы изучаем формулу OFFSET-MATCH, мы будем использовать их для поиска желаемого значения.

У нас уже есть общая формула выше. Нам просто нужно указать эти переменные в этой таблице.

StartCell здесь B1. RowLookupValue — M1. RowLookupRange — это B2: B1o (диапазон ниже начальной ячейки).

ColLookupValue находится в ячейке M2. ColLookupRange — это C1: I1 (диапазон заголовков справа от StartCell).

Мы определили все переменные. Давайте представим их в формуле Excel.

Запишите эту формулу в ячейку M3 и нажмите кнопку ввода.

=OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0))*

Как только вы нажмете кнопку ввода, вы быстро получите результат. Сделано ID 1004 в июне Месяц составляет 177.

Как это работает?

Работа link: / counting-the-offset-function-in-excel [OFFSET function] заключается в перемещении от заданной начальной ячейки к заданной строке и столбцам, а затем возвращению значения из этой ячейки. Например, если я напишу OFFSET (B1,1,1), функция OFFSET перейдет в ячейку C2 (1 ячейка вниз, 1 ячейка вправо) и вернет ей значение.

Здесь у нас есть формула

OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0))

Первая link: / lookup-formulas-excel-match-function [MATCH function] возвращает индекс M1 (1004) в диапазоне B2: B10, который равен 4.

Теперь формула

OFFSET(B1,4,MATCH(M2,C1:I1,0))

Следующая функция ПОИСКПОЗ возвращает индекс M2 (‘Jun’) в диапазоне C1: I1, который I7. Теперь формула СМЕЩЕНА (B1,4,7). Теперь функция СМЕЩЕНИЕ просто перемещает 4 ячейки вниз в ячейку B1, которая переводит их в ячейку B5. Затем функция СМЕЩЕНИЕ переместится на 7 влево до B5, что приведет к I5. Вот и все. Функция СМЕЩЕНИЕ возвращает значение из ячейки I5, которое составляет 177.

Преимущества этого метода поиска?

Это быстро. Единственное преимущество этого метода в том, что он быстрее, чем другие методы. То же самое можно сделать с помощью функции ИНДЕКС-ПОИСКПОЗ или функции ВПР. Но неплохо знать альтернативы.

Когда-нибудь это может пригодиться.

Вот несколько примечаний относительно использования формулы смещения в Excel.

Примечания:

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

Надеюсь, эта статья о том, как использовать формулу смещения в Microsoft Excel, носит пояснительный характер. Дополнительные статьи о значениях поиска и связанных формулах Excel можно найти здесь. Если вам понравились наши блоги, поделитесь ими с друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].

Статьи по теме:

link: / lookup-formulas-use-index-and-match-to-lookup-value [Использовать ИНДЕКС и ПОИСКПОЗ для поиска значения]: Формула ИНДЕКС-ПОИСКПОЗ используется для динамического и точного поиска значения в данной таблице. Это альтернатива функции VLOOKUP, которая устраняет недостатки функции VLOOKUP.

link: / tips-sum-by-offset-groups-in-rows-and-columns [Сумма по группам OFFSET в строках и столбцах]: Функция OFFSET может использоваться для динамического суммирования групп ячеек. Эти группы могут находиться в любом месте листа.

link: / lookup-formulas-retrieving-every-nth-value-in-a-range [Как получить каждое N-е значение в диапазоне в Excel]: Используя функцию СМЕЩЕНИЕ в Excel, мы можем извлекать значения из чередующихся строк или столбцы. Эта формула использует функцию СТРОКА для чередования строк и функцию СТОЛБЕЦ для чередования столбцов.

link: / counting-the-offset-function-in-excel [Как использовать функцию OFFSET в Excel]: Функция OFFSET — это мощная функция Excel, которая недооценивается многими пользователями. Но эксперты знают мощь функции СМЕЩЕНИЕ и как мы можем использовать эту функцию для выполнения некоторых волшебных задач в формуле Excel. Вот основы функции СМЕЩЕНИЕ.