VLOOKUP, HLOOKUP a INDEX-MATCH sú známe a bežné spôsoby vyhľadávania hodnôt v tabuľke programu Excel. Nie sú to však jediné spôsoby, ako vyhľadávať hodnoty v programe Excel. V tomto článku sa naučíme používať funkciu OFFSET spolu s funkciou MATCH v programe Excel. Áno, čítate dobre, na vyhľadanie určitej hodnoty v tabuľke programu Excel použijeme neslávnu funkciu OFFSET.
Generický vzorec,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Pozorne si prečítajte toto:
StartCell:Toto je počiatočná bunka vyhľadávacej tabuľky. Povedzme, že ak chcete vyhľadávať v rozsahu A2: A10, potom StartCell bude A1.
RowLookupValue: Toto je vyhľadávacia hodnota, ktorú chcete nájsť v riadkoch podStartCell.
Rozsah vyhľadávania riadkov:Toto je rozsah, v ktorom chcete vyhľadať RowLookupValue. Je to nižšie uvedený rozsah StartCell (A2: A10).
ColLookupValue: Toto je vyhľadávacia hodnota, ktorú chcete nájsť v stĺpcoch (hlavičkách).
Rozsah ColLookup:Toto je rozsah, v ktorom chcete vyhľadať ColLookupValue. Je to rozsah na pravej strane StartCell (ako B1: D1).
Takže dosť bolo teórie. Začnime s príkladom.
Príklad: Vyhľadajte predaj pomocou funkcie OFFSET a MATCH z tabuľky programu Excel
Tu uvádzame vzorovú tabuľku tržieb vykonaných rôznymi zamestnancami v rôznych mesiacoch.
Teraz nás náš šéf žiada, aby sme poskytli tržby uskutočnené podľa Id 1004 v júnovom mesiaci. Existuje mnoho spôsobov, ako to urobiť, ale keďže sa učíme o vzorci OFFSET-MATCH, použijeme ich na vyhľadanie požadovanej hodnoty.
Vyššie uvedený generický vzorec už máme. Potrebujeme len identifikovať tieto premenné v tejto tabuľke.
StartCell je tu B1. RowLookupValue je M1. RowLookupRange je B2: B1o (rozsah pod štartovacou bunkou).
ColLookupValue je v bunke M2. ColLookupRange je C1: I1 (Dosah hlavičky vpravo od StartCell).
Identifikovali sme všetky premenné. Poďme ich vložiť do vzorca programu Excel.
Napíšte tento vzorec do bunky M3 a kliknite na tlačidlo Enter.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Keď stlačíte tlačidlo Enter, výsledok sa dostaví okamžite. Uskutočnený predaj ID 1004 v mesiaci jún je 177.
Ako to funguje?
Funkciou OFFSET je presunúť sa z danej počiatočnej bunky do daného riadka a stĺpcov a potom vrátiť hodnotu z tejto bunky. Ak napríklad napíšem OFFSET (B1,1,1), funkcia OFFSET prejde do bunky C2 (1 bunka nadol, 1 bunka doprava) a vráti jej hodnotu.
Tu máme vzorecOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Prvá funkcia MATCH vracia index M1 (1004) v rozsahu B2: B10, ktorý je 4. Teraz vzorec je,OFFSET (B1,4, MATCH (M2, C1: I1,0)).
Nasledujúca funkcia MATCH vráti index M2 („jún“) v rozsahu C1: I1, ktorý i 7. Teraz je vzorecOFFSET (B1,4,7).
Teraz funkcia OFFSET jednoducho presunie 4 bunky nadol do bunky B1, čím sa presunie do B5. Potom sa funkcia OFFSET presunie na 7 vľavo na B5, čím prejde na I5. Toto je to. Funkcia OFFSET vracia hodnotu z bunky I5, ktorá 177.
Výhody tejto vyhľadávacej techniky?
Toto je rýchle. Jedinou výhodou tejto metódy je, že je rýchlejšia ako ostatné metódy. To isté je možné vykonať pomocou funkcie INDEX-MATCH alebo funkcie VLOOKUP. Je však dobré poznať niektoré alternatívy. Jedného dňa sa to môže hodiť.
Takže áno, chlapci, takto môžete použiť funkciu OFFSET a MATCH na VYHĽADÁVANIE hodnôt v tabuľkách programu Excel. Dúfam, že to bolo dostatočne vysvetľujúce. Ak máte akékoľvek pochybnosti týkajúce sa tohto článku alebo akejkoľvek inej témy súvisiacej s programom Excel/VBA, opýtajte sa ma v sekcii komentárov nižšie.
Na vyhľadanie hodnoty použite INDEX a MATCH:Vzorec INDEX-MATCH sa používa na dynamické a presné vyhľadávanie hodnôt v danej tabuľke. Je to alternatíva k funkcii VLOOKUP a prekonáva nedostatky funkcie VLOOKUP.
Suma podľa skupín OFFSET v riadkoch a stĺpcoch: Funkciu OFFSET je možné použiť na dynamické sčítanie skupín buniek. Tieto skupiny môžu byť kdekoľvek v hárku.
Ako získať každú n -tú hodnotu v rozsahu v programe Excel: Pomocou funkcie OFFSET v Exceli môžeme získať hodnoty zo striedajúcich sa riadkov alebo stĺpcov. Tento vzorec využíva funkciu ROW na striedanie riadkov a funkciu COLUMN na striedanie v stĺpcoch.
Ako používať funkciu OFFSET v programe Excel: Funkcia OFFSET je výkonná funkcia programu Excel, ktorú mnohí používatelia podceňujú. Odborníci však poznajú silu funkcie OFFSET a ako môžeme túto funkciu použiť na vykonávanie magických úloh vo vzorci programu Excel. Tu sú základy funkcie OFFSET.
Populárne články:
50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať funkciu Excel VLOOKUP| Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať Excel Funkcia COUNTIF| Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia COUNTIF je potrebná na prípravu hlavného panela.
Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.