Excel

Kako poiskati prvo in zadnjo tekmo

How Lookup First

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?

VLOOKUP, katero tekmo bomo dobili?





Preberite odgovor in več zanimivih primerov.

Opombe:



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)

VLOOKUP natančno ujemanje najde prvo ujemanje

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)

VLOOKUP približno ujemanje najde zadnjo tekmo

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)

Primer napačnega rezultata približnega ujemanja VLOOKUP

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)

Približno ujemanje VLOOKUP za dodelitev ocen

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)

Približno ujemanje VLOOKUP + razvrščeni podatki = zadnja cena

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))

INDEX in MATCH sta približni, da najdete najnovejšo ceno

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)

Funkcija LOOKUP za najnovejšo ceno

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)

LOOKUP funkcija za iskanje zadnjega ujemanja z nerazvrščenimi podatki

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)

Z LOOKUP poiščite zadnjo celico, ki ni prazna

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:

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
Avtor Dave Bruns


^