VLOOKUP, HLOOKUP ja INDEX-MATCH ovat tunnettuja ja yleisiä tapoja hakea arvoja Excel-taulukosta. Mutta nämä eivät ole ainoita tapoja hakea arvoja Excelistä. Tässä artikkelissa opimme käyttämään OFFSET -toimintoa yhdessä MATCH -toiminnon kanssa Excelissä. Kyllä, luit oikein, käytämme Excelin pahamaineista OFFSET -toimintoa etsimään tietty arvo Excel -taulukosta.
Yleinen kaava,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Lue tämä huolellisesti:
StartCell:Tämä on hakutaulukon alkusolu. Oletetaan, että jos haluat etsiä alueelta A2: A10, StartCell on A1.
RowLookupValue: Tämä on hakuarvo, jonka haluat löytää rivin alapuoleltaStartCell.
RiviLookupRange:Tämä on alue, josta haluat hakea RowLookupValue -arvon. Se on alla oleva alue StartCell (A2: A10).
ColLookupValue: Tämä on hakuarvo, jonka haluat löytää sarakkeista (otsikoista).
ColLookupRange:Tämä on alue, josta haluat hakea ColLookupValue -arvon. Se on StartCellin oikealla puolella oleva alue (kuten B1: D1).
Teoriaa siis riittää. Aloitetaan esimerkillä.
Esimerkki: Hae myyntiä käyttämällä OFFSET- ja MATCH -toimintoa Excel -taulukosta
Tässä on esimerkkitaulukko eri työntekijöiden myynnistä eri kuukausina.
Nyt pomomme pyytää meitä antamaan Id 1004: n myynnin kesäkuun aikana. On monia tapoja tehdä se, mutta koska opimme OFFSET-MATCH-kaavaa, käytämme niitä halutun arvon etsimiseen.
Meillä on jo yllä oleva yleinen kaava. Meidän on vain tunnistettava nämä muuttujat tässä taulukossa.
StartCell tässä on B1. RowLookupValue on M1. RiviKatselualue on B2: B1o (alue aloituskennon alapuolella).
ColLookupValue on M2 -solussa. Everstihaun alue on C1: I1 (Otsikkoalue StartCellin oikealla puolella).
Olemme tunnistaneet kaikki muuttujat. Laitetaan ne Excel -kaavaan.
Kirjoita tämä kaava soluun M3 ja paina Enter -painiketta.
| = SIIRTÖ (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Kun painat Enter -painiketta, saat tuloksen nopeasti. Myynti tehty ID 1004 kesäkuussa on 177.
Kuinka se toimii?
OFFSET -toiminnon tehtävänä on siirtyä pois annetusta aloitussolusta tietylle riville ja sarakkeille ja palauttaa sitten arvo kyseisestä solusta. Jos esimerkiksi kirjoitan OFFSET (B1,1,1), OFFSET -toiminto siirtyy soluun C2 (1 solu alas, 1 solu oikealle) ja palauttaa sen arvon.
Tässä meillä on kaavaSIIRTYMÄ (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)).
Ensimmäinen MATCH -funktio palauttaa indeksin M1 (1004) alueella B2: B10, joka on 4. Nyt kaava on,SIIRTYMÄ (B1,4, MATCH (M2, C1: I1,0)).
Seuraava MATCH -funktio palauttaa M2 -indeksin ('Jun') alueella C1: I1, joka i 7. Nyt kaava onSIIRTO (B1,4,7).
Nyt OFFSET -toiminto siirtää vain 4 solua alas soluun B1, joka vie sen kohtaan B5. Sitten OFFSET -toiminto siirtyy kohtaan 7 vasemmalle kohtaan B5, joka vie sen kohtaan I5. Se on siinä. OFFSET -funktio palauttaa arvon solusta I5, joka 177.
Tämän hakutekniikan edut?
Tämä on nopeaa. Tämän menetelmän ainoa etu on, että se on nopeampi kuin muut menetelmät. Sama asia voidaan tehdä käyttämällä INDEX-MATCH- tai VLOOKUP-toimintoa. Mutta on hyvä tietää muutamia vaihtoehtoja. Siitä voi olla hyötyä jonain päivänä.
Joten kyllä kaverit, tällä tavalla voit käyttää OFFSET- ja MATCH -toimintoja Excel -taulukoiden arvojen etsimiseen. Toivottavasti se oli tarpeeksi selittävä. Jos sinulla on epäilyksiä tästä artikkelista tai muista excel/VBA -aiheista, kysy minulta alla olevasta kommenttiosasta.
Käytä INDEX- ja MATCH -hakuja arvon etsimiseen:INDEX-MATCH-kaavaa käytetään dynaamisen ja tarkan arvon etsimiseen annetusta taulukosta. Tämä on vaihtoehto VLOOKUP -toiminnolle ja se poistaa VLOOKUP -toiminnon puutteet.
Summa OFFSET -ryhmien mukaan riveillä ja sarakkeissa: OFFSET -toimintoa voidaan käyttää soluryhmän summaamiseen dynaamisesti. Nämä ryhmät voivat olla missä tahansa taulukossa.
Kuinka noutaa jokaisen n: n alueen arvo Excelissä: Excelin OFFSET -funktion avulla voimme hakea arvot vuorottelevista riveistä tai sarakkeista. Tämä kaava hyödyntää ROW -funktiota vuorotellen rivien läpi ja COLUMN -funktiota vuorotellen sarakkeissa.
OFFSET -toiminnon käyttäminen Excelissä: OFFSET -toiminto on tehokas Excel -toiminto, jota monet käyttäjät aliarvioivat. Mutta asiantuntijat tietävät OFFSET -toiminnon voiman ja kuinka voimme käyttää tätä toimintoa joidenkin maagisten tehtävien suorittamiseen Excel -kaavassa. Tässä on OFFSET -toiminnon perusteet.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi | Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
Excel VLOOKUP -toiminnon käyttäminen| Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja arkeilta.
Excelin käyttäminen COUNTIF -toiminto| Laske arvot olosuhteilla käyttämällä tätä hämmästyttävää toimintoa. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. COUNTIF -toiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä | Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.