VLOOKUP, HLOOKUP in INDEX-MATCH so znani in pogosti načini iskanja vrednosti v tabeli Excel. Vendar to niso edini načini iskanja vrednosti v Excelu. V tem članku se bomo naučili uporabljati funkcijo OFFSET skupaj s funkcijo MATCH v Excelu. Da, prav ste prebrali, z zloglasno funkcijo OFFSET v Excelu bomo poiskali določeno vrednost v Excelovi tabeli.
Splošna formula,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Pazljivo preberite tole:
StartCell:To je začetna celica iskalne tabele. Recimo, če želite poiskati v območju A2: A10, bo StartCell A1.
RowLookupValue: To je iskalna vrednost, ki jo želite najti v vrsticah podStartCell.
RowLookupRange:To je obseg, v katerem želite poiskati vrednost RowLookupValue. To je spodnji razpon StartCell (A2: A10).
ColLookupValue: To je iskalna vrednost, ki jo želite najti v stolpcih (glavi).
ColLookupRange:To je obseg, v katerem želite poiskati vrednost ColLookupValue. To je obseg na desni strani StartCell (na primer B1: D1).
Torej dovolj teorije. Začnimo s primerom.
Primer: Poiščite prodajo s funkcijo OFFSET in MATCH iz Excelove tabele
Tukaj imamo vzorčno tabelo prodaje, ki so jo v različnih mesecih opravili različni zaposleni.
Zdaj nas šef prosi, naj v mesecu juniju damo prodajo, ki jo je opravil Id 1004. Obstaja veliko načinov za to, ker pa se učimo o formuli OFFSET-MATCH, jih bomo uporabili za iskanje želene vrednosti.
Zgornjo generično formulo že imamo. Te spremenljivke moramo samo identificirati v tej tabeli.
StartCell je tukaj B1. RowLookupValue je M1. RowLookupRange je B2: B1o (razpon pod začetno celico).
ColLookupValue je v celici M2. ColLookupRange je C1: I1 (obseg glave na desni strani StartCell).
Opredelili smo vse spremenljivke. Postavimo jih v formulo Excel.
To formulo zapišite v celico M3 in pritisnite gumb za vnos.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Ko pritisnete gumb za vnos, boste takoj dobili rezultat. Prodaja ID 1004 v mesecu juniju je 177.
Kako deluje?
Funkcija OFFSET je, da se premakne od dane začetne celice v podano vrstico in stolpce in nato vrne vrednost iz te celice. Če na primer napišem OFFSET (B1,1,1), bo funkcija OFFSET prešla v celico C2 (1 celica navzdol, 1 celica desno) in vrnila vrednost.
Tukaj imamo formuloOFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Prva funkcija MATCH vrne indeks M1 (1004) v območju B2: B10, ki je 4. Zdaj je formula,OFFSET (B1,4, UJEMA (M2, C1: I1,0)).
Naslednja funkcija MATCH vrne indeks M2 ('junij') v območju C1: I1, kar je 7. Zdaj je formulaOFFSET (B1,4,7).
Zdaj funkcija OFFSET preprosto premakne 4 celice navzdol v celico B1, ki jo popelje v B5. Nato se funkcija OFFSET premakne na 7 levo na B5, kar preusmeri na I5. To je to. Funkcija OFFSET vrne vrednost iz celice I5, ki 177.
Prednosti te tehnike iskanja?
To je hitro. Edina prednost te metode je, da je hitrejša od drugih. Enako lahko storite s funkcijo INDEX-MATCH ali funkcijo VLOOKUP. Vendar je dobro poznati nekaj alternativ. Morda bo kdaj prišel prav.
Torej, fantje, tako lahko uporabite funkcijo OFFSET in MATCH za iskanje vrednosti v Excelovih tabelah. Upam, da je bilo dovolj razlagalno. Če dvomite o tem članku ali kateri koli drugi temi, povezani z excelom/VBA, me vprašajte v spodnjem razdelku za komentarje.
Za iskanje vrednosti uporabite INDEX in MATCH:Formula INDEX-MATCH se uporablja za dinamično in natančno iskanje vrednosti v dani tabeli. To je alternativa funkciji VLOOKUP in premaga pomanjkljivosti funkcije VLOOKUP.
Vsota po skupinah OFFSET v vrsticah in stolpcih: Funkcijo OFFSET lahko uporabite za dinamično seštevanje skupin celic. Te skupine so lahko kjer koli na listu.
Kako v Excelu pridobiti vsako n -to vrednost v obsegu: S funkcijo OFFSET v Excelu lahko pridobimo vrednosti iz izmeničnih vrstic ali stolpcev. Ta formula uporablja funkcijo ROW za zamenjavo vrstic in funkcijo COLUMN za izmenjavo v stolpcih.
Kako uporabljati funkcijo OFFSET v Excelu: Funkcija OFFSET je močna Excelova funkcija, ki jo številni uporabniki podcenjujejo. Toda strokovnjaki poznajo moč funkcije OFFSET in kako lahko to funkcijo uporabimo za čarobne naloge v formuli Excel. Tu so osnove funkcije OFFSET.
Priljubljeni članki:
50 bližnjic v Excelu za večjo produktivnost | Poskrbite, da bo vaša naloga hitrejša. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati funkcijo Excel VLOOKUP| To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati Excel COUNTIF funkcija| Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija COUNTIF je bistvena za pripravo vaše nadzorne plošče.
Kako uporabljati funkcijo SUMIF v Excelu | To je še ena bistvena funkcija armaturne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.