„VLOOKUP“, „HLOOKUP“ ir „INDEX-MATCH“ yra žinomi ir paplitę būdai ieškoti verčių „Excel“ lentelėje. Tačiau tai nėra vieninteliai būdai ieškoti „Excel“ verčių. Šiame straipsnyje mes išmoksime naudoti funkciją OFFSET kartu su funkcija MATCH programoje „Excel“. Taip, jūs perskaitėte teisingai, mes naudosime liūdnai pagarsėjusią „Excel“ funkciją OFFSET, kad surastume tam tikrą „Excel“ lentelės vertę.
Bendra formulė,
| = OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0)) |
Atidžiai perskaitykite tai:
„StartCell“:Tai yra pradinė paieškos lentelės ląstelė. Tarkime, jei norite ieškoti diapazone A2: A10, tada „StartCell“ bus A1.
RowLookupValue: Tai yra paieškos vertė, kurią norite rasti eilutėse po„StartCell“.
„RowLookupRange“:Tai yra diapazonas, kuriame norite ieškoti „RowLookupValue“. Tai diapazonas žemiau „StartCell“ (A2: A10).
ColLookupValue: Tai yra paieškos vertė, kurią norite rasti stulpeliuose (antraštėse).
ColLookupRange:Tai yra diapazonas, kuriame norite ieškoti ColLookupValue. Tai diapazonas dešinėje „StartCell“ pusėje (kaip B1: D1).
Taigi, užteks teorijos. Pradėkime nuo pavyzdžio.
Pavyzdys: Ieškokite pardavimų naudodami funkciją OFFSET ir MATCH Iš „Excel“ lentelės
Čia pateikiame pavyzdinę pardavimo lentelę, kurią skirtingi mėnesiai atliko skirtingi darbuotojai.
Dabar mūsų viršininkas prašo, kad birželio mėn. Parduotume ID 1004. Yra daug būdų tai padaryti, bet kadangi mes mokomės apie OFFSET-MATCH formulę, mes juos naudosime norimai vertei surasti.
Mes jau turime bendrąją formulę aukščiau. Mes tiesiog turime nustatyti šiuos kintamuosius šioje lentelėje.
„StartCell“ čia yra B1. RowLookupValue yra M1. „RowLookupRange“ yra B2: B1o (diapazonas žemiau pradžios elemento).
ColLookupValue yra M2 ląstelėje. ColLookupRange yra C1: I1 (antraščių diapazonas „StartCell“ dešinėje).
Mes nustatėme visus kintamuosius. Įtraukime juos į „Excel“ formulę.
Parašykite šią formulę langelyje M3 ir paspauskite įvesties mygtuką.
| = OFFSET (B1, MATCH (M1, B2: B10,0), MATCH (M2, C1: I1,0)) |
Paspaudus įvesties mygtuką, rezultatas bus gautas nedelsiant. Birželio mėnesį parduotas ID 1004 yra 177.
Kaip tai veikia?
Funkcijos OFFSET tikslas yra pereiti nuo nurodytos pradžios ląstelės prie nurodytos eilutės ir stulpelių ir tada grąžinti vertę iš tos ląstelės. Pavyzdžiui, jei parašysiu OFFSET (B1,1,1), funkcija OFFSET pereis į langelį C2 (1 langelis žemyn, 1 langelis į dešinę) ir grąžins jo vertę.
Čia mes turime formulęPAKLAIDA (B1, Rungtynės (M1, B2: B10,0), Rungtynės (M2, C1: I1,0)).
Pirmoji MATCH funkcija grąžina B2: B10 diapazono M1 (1004) indeksą, kuris yra 4. Dabar formulė yra:PAKLAIDA (B1,4, ATITIKTI (M2, C1: I1,0)).
Kita funkcija MATCH grąžina M2 indeksą („Jun“) diapazone C1: I1, kurį i 7. Dabar formulė yraPAKLAIDA (B1,4,7).
Dabar OFFSET funkcija tiesiog perkelia 4 langelius žemyn į langelį B1, kuris perkelia jį į B5. Tada OFFSET funkcija perkeliama į 7 kairę į B5, o tai perkelia į I5. Viskas. Funkcija OFFSET grąžina reikšmę iš ląstelės I5, kuri 177.
Šios paieškos technikos privalumai?
Tai greita. Vienintelis šio metodo privalumas yra tai, kad jis yra greitesnis nei kiti metodai. Tą patį galima padaryti naudojant funkciją INDEX-MATCH arba VLOOKUP. Bet gerai žinoti kai kurias alternatyvas. Kažkada tai gali praversti.
Taigi taip, vaikinai, taip galite naudoti funkciją OFFSET ir MATCH, kad surastumėte reikšmes „Excel“ lentelėse. Tikiuosi, kad tai buvo pakankamai aiškinama. Jei turite kokių nors abejonių dėl šio straipsnio ar kitos su „Excel“/VBA susijusios temos, paklauskite manęs komentarų skiltyje žemiau.
Naudokite INDEX ir MATCH, kad surastumėte vertę:INDEX-MATCH formulė naudojama dinamiškai ir tiksliai ieškoti vertės lentelėje. Tai yra alternatyva funkcijai VLOOKUP ir ji pašalina VLOOKUP funkcijos trūkumus.
Suma pagal OFFSET grupes eilutėse ir stulpeliuose: Funkcija OFFSET gali būti naudojama dinamiškai susumuojant ląstelių grupes. Šios grupės gali būti bet kurioje lapo vietoje.
Kaip atkurti kiekvieną „N“ reikšmę diapazone „Excel“: Naudodami „Excel“ funkciją OFFSET, mes galime nuskaityti vertes iš besikeičiančių eilučių ar stulpelių. Ši formulė naudoja funkciją ROW, kad būtų galima keisti eilutes, ir funkciją COLUMN, kad kaitaliotų stulpelius.
Kaip naudoti „OFFSET“ funkciją „Excel“: Funkcija OFFSET yra galinga „Excel“ funkcija, kurią daugelis vartotojų nepakankamai įvertina. Tačiau ekspertai žino OFFSET funkcijos galią ir tai, kaip mes galime naudoti šią funkciją kai kurioms stebuklingoms užduotims atlikti „Excel“ formulėje. Čia yra funkcijos OFFSET pagrindai.
Populiarūs straipsniai:
50 „Excel“ nuorodų, skirtų produktyvumui padidinti | Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis „Excel VLOOKUP“ funkcija| Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama norint ieškoti vertės iš įvairių diapazonų ir lapų.
Kaip naudotis „Excel“ Funkcija COUNTIF| Naudodami šią nuostabią funkciją, suskaičiuokite reikšmes su sąlygomis. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Funkcija COUNTIF yra būtina norint paruošti prietaisų skydelį.
Kaip naudoti „SUMIF“ funkciją „Excel“ | Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.