VLOOKUP, HLOOKUP ja INDEX-MATCH on tuntud ja levinud viisid Exceli tabeli väärtuste otsimiseks. Kuid need pole ainsad viisid Exceli väärtuste otsimiseks. Selles artiklis õpime, kuidas kasutada funktsiooni OFFSET koos Exceli funktsiooniga MATCH. Jah, lugesite õigesti, kasutame Exceli kurikuulsat funktsiooni OFFSET, et otsida Exceli tabelist teatud väärtus.
Üldine valem,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Lugege seda hoolikalt:
StartCell:See on otsingu tabeli alguslahtrid. Oletame, et kui soovite otsida vahemikus A2: A10, on StartCell A1.
RowLookupValue: See on otsinguväärtus, mille soovite leida ridade allStartCell.
RowLookupRange:See on vahemik, kust soovite otsida RowLookupValue. See on allpool olev vahemik StartCell (A2: A10).
ColLookupValue: See on otsinguväärtus, mille soovite leida veergudest (päistest).
ColLookupRange:See on vahemik, kust soovite otsida ColLookupValue. See on StartCelli paremal pool olev vahemik (nagu B1: D1).
Niisiis, piisab teooriast. Alustame näitega.
Näide: Otsige müük, kasutades funktsiooni OFFSET ja MATCH Exceli tabelist
Siin on meil näidiste tabel müügist, mille on teinud erinevad töötajad erinevatel kuudel.
Nüüd palub meie ülemus meil anda juunikuus Id 1004 tehtud müük. Selleks on palju viise, kuid kuna me õpime OFFSET-MATCH valemit, kasutame neid soovitud väärtuse leidmiseks.
Meil on ülaltoodud üldine valem juba olemas. Peame tabelis need muutujad lihtsalt kindlaks tegema.
StartCell kas siin on B1. RowLookupValue on M1. RowLookupRange on B2: B1o (vahemik stardiraku all).
ColLookupValue on M2 lahtris. ColLookupRange on C1: I1 (päisevahemik StartCelli paremal).
Oleme tuvastanud kõik muutujad. Paneme need Exceli valemisse.
Kirjutage see valem lahtrisse M3 ja vajutage sisestusklahvi.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Kui vajutate sisestusklahvi, saate tulemuse kiiresti. Juunikuu ID 1004 müük on 177.
Kuidas see töötab?
Funktsiooni OFFSET ülesanne on liikuda antud alguslahtrist antud reale ja veergudele ning seejärel selle lahtri väärtus tagastada. Näiteks kui kirjutan OFFSET (B1,1,1), läheb funktsioon OFFSET lahtrisse C2 (1 lahter alla, 1 lahter paremale) ja tagastab selle väärtuse.
Siin on meil valemNIIRE (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Esimene funktsioon MATCH tagastab indeksi M1 (1004) vahemikus B2: B10, mis on 4. Nüüd on valem järgmine:NIIRE (B1,4, MATCH (M2, C1: I1,0)).
Järgmine funktsioon MATCH tagastab indeksi M2 ('Jun') vahemikus C1: I1, mille i 7. Nüüd on valemNIIRE (B1,4,7).
Nüüd nihutab funktsioon OFFSET lihtsalt 4 lahtrit alla lahtrisse B1, mis viib selle lahtrisse B5. Seejärel liigub funktsioon OFFSET 7 -le vasakule B5 -le, mis viib selle I5 -le. See on kõik. Funktsioon OFFSET tagastab väärtuse lahtrist I5, mis 177.
Selle otsingu tehnika eelised?
See on kiire. Selle meetodi ainus eelis on see, et see on kiirem kui teised meetodid. Sama saab teha funktsiooni INDEX-MATCH või funktsiooni VLOOKUP abil. Kuid on hea teada mõningaid alternatiive. See võib ühel päeval kasuks tulla.
Nii et jah, poisid, nii saate funktsiooni OFFSET ja MATCH kasutada väärtuste otsimiseks Exceli tabelitest. Loodan, et see oli piisavalt selgitav. Kui teil on selle artikli või mõne muu Exceli/VBA -ga seotud teema osas kahtlusi, küsige seda allpool kommentaaride osas.
Väärtuse otsimiseks kasutage INDEX ja MATCH:Valemit INDEX-MATCH kasutatakse antud tabeli väärtuse dünaamiliseks ja täpseks otsimiseks. See on alternatiiv funktsioonile VLOOKUP ja see kõrvaldab funktsiooni VLOOKUP puudused.
Summa OFFSET rühmade kaupa ridades ja veergudes: Funktsiooni OFFSET saab kasutada lahtrirühma dünaamiliseks summeerimiseks. Need rühmad võivad olla lehel kõikjal.
Kuidas Excelis vahemikus iga n -ö väärtus alla laadida: Exceli funktsiooni OFFSET abil saame väärtusi hankida vahelduvatest ridadest või veergudest. See valem kasutab ridade vaheldumiseks funktsiooni ROW ja veergudes vaheldumiseks funktsiooni COLUMN.
Funktsiooni OFFSET kasutamine Excelis: Funktsioon OFFSET on võimas Exceli funktsioon, mida paljud kasutajad alahindavad. Kuid eksperdid teavad funktsiooni OFFSET võimsust ja seda, kuidas me saame seda funktsiooni kasutada Exceli valemis mõne maagilise ülesande täitmiseks. Siin on funktsiooni OFFSET põhitõed.
Populaarsed artiklid:
50 Exceli otseteed tootlikkuse suurendamiseks | Saa oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada funktsiooni Excel VLOOKUP| See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Kuidas Excelit kasutada Funktsioon COUNTIF| Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Funktsioon COUNTIF on teie armatuurlaua ettevalmistamiseks hädavajalik.
Funktsiooni SUMIF kasutamine Excelis | See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.