Eden bolj zmedenih vidikov iskalnih funkcij v Excelu je razumevanje, kako priti do prvega ali zadnjega ujemanja v nizu podatkov z več kot enim ujemanjem. To je zato, ker se vedenje Excela spreminja glede na dva dejavnika: ali izvajate natančno ali približno ujemanje in ali so podatki razvrščeni ali ne.
Recimo, da z uporabo VLOOKUP -a za iskanje cene za „zeleno“ v spodnjih podatkih. Kakšno ceno bomo dobili?
Preberite odgovor in več zanimivih primerov.
Opombe:
- Spodnji primeri uporabljajo imenovanih obsegov (kot je zapisano na slikah), da so formule preproste.
- Referenčne povezave do funkcij: VLOOKUP , KAZALO , UJEMA , in POGLEJ GOR .
Natančno ujemanje = prvo
Pri natančnem ujemanju boste vedno dobili prvo ujemanje, piko. Ni pomembno, ali so podatki razvrščeni ali ne. Na spodnjem zaslonu je iskalna vrednost v E5 'rdeča'. The VLOOKUP funkcija , v načinu natančnega ujemanja vrne ceno za prvo ujemanje:
= VLOOKUP (E5,data,2,FALSE)
Upoštevajte, da je zadnji argument v VLOOKUP FALSE za vsiljevanje natančnega ujemanja.
Približno ujemanje = zadnje
Če se približujete, podatki pa so razvrščeni po iskalni vrednosti , dobili boste zadnjo tekmo. Zakaj? Ker Excel med približnim ujemanjem pregleda vrednosti, dokler ne najde vrednosti, ki je večja od iskalne vrednosti, se potem 'vrne' na prejšnjo vrednost.
Na spodnjem zaslonu je VLOOKUP nastavljen na približen način ujemanja, barve pa so razvrščene. VLOOKUP vrne ceno zadnjega 'zelenega':
razlika med dvema datumoma v excelu
= VLOOKUP (E5,data,2,TRUE)
Opazite, da je zadnji argument v VLOOKUP -u TRUE za približno ujemanje.
Približno ujemanje + nerazvrščeni podatki = nevarnost
S standardnimi približnimi iskanji ujemanja, podatke je treba razvrstiti po iskalni vrednosti . Pri nerazvrščenih podatkih boste morda videli povsem normalne rezultate, ki so videti nepravilno. Ta težava je verjetnejša pri VLOOKUP -u, ker VLOOKUP privzeto ustreza približnemu ujemanju kadar ni naveden četrti argument.
Za ponazoritev te težave si oglejte spodnji primer. Podatki so nerazvrščeno in VLOOKUP, brez podanega četrtega argumenta, privzeto določa približno ujemanje. Upoštevajte, da ni 'rdečega' s ceno 17,00 USD, vendar VLOOKUP z veseljem vrne ta neveljaven rezultat:
= VLOOKUP (E5,data,2)
Zato priporočam, da za VLOOKUP vedno izrecno nastavite zadnji argument: TRUE = približno ujemanje, FALSE = natančno ujemanje. Argument je neobvezno, vendar o zagotavljanju vrednosti pomislite in o tem v prihodnosti vizualno opomnite.
Spodaj bomo pogledali, kako premagati težavo zadnje tekme in nerazvrščene podatke.
'Običajno' približno ujemanje
Na tej točki se morda počutite nekoliko zmedeni in dezorientirani glede ideje, da lahko približno ujemanje v nekaterih primerih vrne zadnjo tekmo. Če je tako, ne skrbite. Uporaba približnega ujemanja za zadnjo ujemanje ni 'običajen' primer. Običajno boste videli približno ujemanje, ki se uporablja za dodelitev vrednosti glede na nekakšno lestvico. Klasičen primer je uporaba VLOOKUP v načinu približnega ujemanja za dodeljevanje ocen, kar lepo deluje:
vstavljanje prelomov strani v Excelu 2010
= VLOOKUP (E5,key,2,TRUE)
V takih primerih iskalna tabela namerno ne vključuje podvojenih vrednosti, zato je celotna zamisel o 'zadnji ujemajoči se vrednosti' nepomembna. Več podrobnosti o tej formuli tukaj .
Zgornji podatki naj bi zagotovili ozadje in kontekst za delovanje ujemanja v Excelu, da bodo spodaj opisani pristopi smiselni.
Praktične aplikacije
Kako lahko zgoraj opisano vedenje uporabimo v praktični situaciji? No, en pogost scenarij je iskanje 'najnovejšega' ali 'zadnjega' vnosa za element. Spodaj na primer uporabljamo VLOOKUP v načinu približnega ujemanja, da poiščemo najnovejšo ceno za Sandale. Podatki o obvestilih so razvrščeni po postavkah, nato po datumu, zato je zadnja cena za določeno postavko prikazana zadnja:
= VLOOKUP (F5,data,3,TRUE)
INDEX in MATCH
Na ta način lahko uporabite tudi druge funkcije iskanja. Spodaj uporabljamo enakovreden KAZALO in UJEMA formula poiščite najnovejšo ceno z istimi podatki. Opomba MATCH je konfiguriran tako, da približuje ujemanje elementov, razvrščenih v naraščajočem vrstnem redu, tako da tretji argument nastavi na 1:
= INDEX (price, MATCH (F5,item,1))
Funkcija LOOKUP
The Funkcija LOOKUP se lahko uporabi tudi v tem primeru. POGLEJ GOR nenehno izvede približno ujemanje, zato dobro deluje v scenarijih 'zadnjega ujemanja'. Formula je precej preprosta:
= LOOKUP (F5,item,price)
Zadnje ujemanje z nerazvrščenimi podatki
Kaj pa, če želite zadnjo ujemanje, vendar podatki niso razvrščeni po iskalni vrednosti? Z drugimi besedami, želite uporabiti merila za iskanje ujemanja in preprosto želite zadnji element v podatkih, ki ustreza vašim merilom? To je v resnici primer, ko funkcija LOOKUP zasije, ker lahko LOOKUP ročno upravlja z matričnimi operacijami, brez nadzora + shift + enter. To pomeni, da lahko z enostavnimi logičnimi izrazi dinamično zgradimo iskalno matriko za iskanje želenih podatkov.
Na primer, poglejte spodnjo formulo:
= LOOKUP (2,1/(item=F5),price)
Ta formula najde najnovejšo ceno za Sandale v nerazvrščeno podatkov.
Morda še niste videli takšne formule, zato jo razčlenimo po korakih. Pri delu od znotraj navzven najprej uporabimo merila s preprostim logičnim izrazom:
item=F5 To ima za posledico matriko vrednosti TRUE in FALSE, kjer TRUE ustreza postavkam, ki so 'sandale', FALSE pa vsem ostalim vrednostim:
{FALSETRUEFALSETRUEFALSEFALSETRUEFALSE}
Nato razdelimo številko 1 s tem nizom. Med delitvijo TRUE postane 1, FALSE pa nič, zato morate operacijo vizualizirati tako:
1/{01010010}
Eno, deljeno z enim, je eno, eno, deljeno z ničlo, pa je #DIV/0, zato je rezultat druga matrika, ta vsebuje samo 1s in #DIV/0 napak:
{#DIV/0!1#DIV/0!1#DIV/0!#DIV/0!1#DIV/0!}
Ne skrbite, obstaja metoda za to norost :)
kako spremeniti oznake vodoravne osi v
Morda ste opazili, da je iskalna vrednost številka 2. To se morda zdi zmedeno. Kako bo LOOKUP kdaj našel številko 2 v matriki, ki vsebuje samo 1 in napake? Ne bo. Uporabljamo 2 kot iskalno vrednost, da prisilimo LOOKUP, da skenira v konec podatkov .
Funkcija LOOKUP bo samodejno prezrla napake, zato se lahko ujemajo samo 1. Skeniral bo skozi enote in iskal 2, ki jih nikoli ne bo mogoče najti. Ko pride do konca matrike, se bo 'odmaknil' na zadnjo veljavno vrednost - zadnjo 1 - kar ustreza zadnjem ujemanju na podlagi podanih meril.
Različica matrike INDEX in MATCH
Lepota Funkcija LOOKUP ali zmore zgoraj opisano operacijo niza, ne da bi morali vnesti kot formula matrike s tipko control + shift + enter. Vsekakor pa lahko uporabite formulo matrike, če želite. Tu je enakovredna formula INDEX in MATCH, ki jo je treba vnesti s tipkama control + shift + enter:
{= INDEX (price, MATCH (2,1/(item=F5),1))}
Zadnja celica, ki ni prazna
Zgornji pristop se izkaže za zelo uporabnega. Na primer, če malo spremenimo logiko, lahko naredimo stvari, kot je najti zadnjo prazno celico v stolpcu:
= LOOKUP (2,1/(B:B''),B:B)
Ta formula je podrobneje opisano tukaj .
Iskanje n -te tekme? Vse tekme?
Če ste prišli tako daleč, se morda sprašujete, kako bi našli drugo ali tretjo ujemanje ali kako bi pridobili vse tekme? Tu je nekaj povezav za vas:
- Kako do n -te tekme z VLOOKUP -om
- Kako do n -te tekme z INDEX in MATCH
- Kako do vseh ujemanj s pomožnim stolpcem
Opazili boste, da se takšne formule zapletejo. V Excelu v letu 2019 prihajajo nekatere nove nove funkcije, ki bodo te rešitve poenostavile. Ostani na vezi. Medtem na to ne pozabite Vrtilne tabele so odličen način za raziskovanje podatkov brez formul .
Kaj je naslednje?
Spodaj so navodila za več informacij o Excelovih formulah. Ponujamo tudi spletno video usposabljanje .
- Osnove formule - če šele začenjate
- 500 primerov formule s popolnimi pojasnili
- 101 pomembnih Excelovih funkcij
- Vodnik po vseh funkcijah programa Excel (dela v teku)
- Merila formule - 50 primerov
- Formule za pogojno oblikovanje
