VLOOKUP, HLOOKUP и INDEX-MATCH са известни и често срещани начини за търсене на стойности в таблица на Excel. Но това не са единствените начини за търсене на стойности в Excel. В тази статия ще научим как да използваме функцията OFFSET заедно с функцията MATCH в Excel. Да, прочетохте го правилно, ще използваме скандалната OFFSET функция на 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).
Така че стига с теорията. Нека започнем с пример.
Пример: Търсене на продажби с помощта на OFFSET и MATCH функция от Excel таблица
Тук имаме примерна таблица на продажбите, направени от различни служители през различни месеци.
Сега шефът ни иска да дадем продажбите, направени от Id 1004 през месец юни. Има много начини да го направите, но тъй като научаваме за формулата OFFSET-MATCH, ще ги използваме за търсене на желаната стойност.
Вече имаме общата формула по -горе. Просто трябва да идентифицираме тези променливи в тази таблица.
StartCell тук е В1. RowLookupValue е М1. RowLookupRange е B2: B1o (диапазон под началната клетка).
ColLookupValue е в клетка М2. ColLookupRange е C1: I1 (Диапазон на заглавието вдясно от StartCell).
Идентифицирахме всички променливи. Нека ги поставим във формула на Excel.
Напишете тази формула в клетка М3 и натиснете бутона за въвеждане.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Когато натиснете бутона за въвеждане, резултатът се получава незабавно. Продажбата, извършена ID 1004 през месец юни, е 177.
Как работи?
Работата на функцията OFFSET е да се отдалечи от дадената начална клетка към даден ред и колони и след това да върне стойността от тази клетка. Например, ако напиша OFFSET (B1,1,1), функцията OFFSET ще отиде в клетка C2 (1 клетка надолу, 1 клетка вдясно) и ще върне стойността.
Тук имаме формулаOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Първата функция MATCH връща индекса на M1 (1004) в диапазон B2: B10, който е 4. Сега формулата е,ОФСЕТ (B1,4, МАТЧ (M2, C1: I1,0)).
Следващата функция MATCH връща индекса на M2 ('Jun') в диапазон C1: I1, който i 7. Сега формулата еОФСЕТ (B1,4,7).
Сега функцията OFFSET просто премества 4 клетки надолу до клетката B1, която я отвежда до B5. След това функцията OFFSET се премества на 7 наляво към B5, което я отвежда до I5. Това е. Функцията OFFSET връща стойност от клетка I5, която 177.
Предимства на тази техника за търсене?
Това е бързо. Единственото предимство на този метод е, че той е по -бърз от другите методи. Същото може да се направи с помощта на функцията INDEX-MATCH или функцията VLOOKUP. Но е добре да знаете някои алтернативи. Може някой ден да е полезно.
Така че да, момчета, ето как можете да използвате функцията OFFSET и MATCH, за да търсите стойности в Excel таблици. Надявам се, че беше достатъчно обяснително. Ако имате някакви съмнения относно тази статия или друга тема, свързана с Excel/VBA, попитайте ме в секцията за коментари по -долу.
Използвайте INDEX и MATCH за търсене на стойност:Формулата INDEX-MATCH се използва за динамично и точно търсене на стойност в дадената таблица. Това е алтернатива на функцията VLOOKUP и тя преодолява недостатъците на функцията VLOOKUP.
Сума по групи OFFSET в редове и колони: Функцията OFFSET може да се използва за динамично сумиране на група клетки. Тези групи могат да бъдат навсякъде в листа.
Как да извлечете всяка N -та стойност в диапазон в Excel: Използвайки функцията OFFSET на Excel, можем да извличаме стойности от редуващи се редове или колони. Тази формула използва функцията ROW за редуване през редове и функцията COLUMN за редуване в колони.
Как да използвате функцията OFFSET в Excel: Функцията OFFSET е мощна функция на Excel, която се подценява от много потребители. Но експертите знаят силата на функцията OFFSET и как можем да използваме тази функция за извършване на някои магически задачи във формулата на Excel. Ето основите на функцията OFFSET.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията COUNTIF е от съществено значение за подготовката на вашето табло за управление.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.