Vynikat

Jak vyhledat první a poslední zápas

How Lookup First

Jedním z více matoucích aspektů vyhledávacích funkcí v aplikaci Excel je porozumět tomu, jak získat první nebo poslední shodu v sadě dat s více než jednou shodou. Důvodem je, že chování aplikace Excel se mění v závislosti na dvou faktorech: zda provádíte přesnou nebo přibližnou shodu a zda jsou data seřazeny nebo ne.





Řekněme například, že používáme VLOOKUP k vyhledání ceny za „zelené“ v níže uvedených datech. Jakou cenu dostaneme?

VYHLEDAT, který zápas získáme?





Přečtěte si odpověď a další zajímavé příklady.

Poznámky:



Přesná shoda = první

Když provedete přesnou shodu, vždy získáte první zápas, tečku. Nezáleží na tom, zda jsou data tříděna nebo ne. Na níže uvedené obrazovce je vyhledávací hodnota v E5 „červená“. The Funkce VLOOKUP , v režimu přesné shody vrací cenu za první shodu:

 
= VLOOKUP (E5,data,2,FALSE)

Přesná shoda VLOOKUP najde první shodu

Všimněte si, že poslední argument ve VLOOKUP je FALSE, aby vynutil přesnou shodu.

Přibližná shoda = poslední

Pokud provádíte přibližnou shodu, a data jsou řazena podle vyhledávací hodnoty , dostanete poslední zápas. Proč? Protože během přibližné shody Excel prohledává hodnoty, dokud není nalezena hodnota větší než vyhledávaná hodnota, pak se 'vrátí zpět' k předchozí hodnotě.

Na obrazovce níže je VLOOKUP nastaven na přibližný režim shody a barvy jsou seřazeny. VLOOKUP vrací cenu za poslední 'zelenou':

jak kliknout na vstup do buňky aplikace Excel
 
= VLOOKUP (E5,data,2,TRUE)

VLOOKUP přibližná shoda najde poslední shodu

Všimněte si, že poslední argument ve VLOOKUP je PRAVDA pro přibližnou shodu.

Přibližná shoda + netříděná data = nebezpečí

Při standardním přibližném vyhledávání shody data musí být řazena podle vyhledávací hodnoty . U netříděných dat můžete vidět normálně vypadající výsledky, které jsou zcela nesprávné. Tento problém je pravděpodobnější u VLOOKUP, protože Výchozí hodnota VLOOKUP je přibližná shoda když není zadán čtvrtý argument.

Pro ilustraci tohoto problému viz níže uvedený příklad. Data jsou netříděné a VLOOKUP, bez čtvrtého argumentu, výchozí pro přibližnou shodu. Všimněte si, že neexistuje žádná „červená“ s cenou 17,00 $, přesto VLOOKUP šťastně vrací tento neplatný výsledek:

 
= VLOOKUP (E5,data,2)

Příklad přibližného shody VLOOKUP s nesprávným výsledkem

Z tohoto důvodu doporučuji vždy nastavit poslední argument pro VLOOKUP explicitně: TRUE = přibližná shoda, FALSE = přesná shoda. Argument je nepovinné, ale s uvedením hodnoty vás donutí přemýšlet o tom a v budoucnu vám to vizuálně připomene.

Níže se podíváme, jak překonat problém posledního zápasu a netříděných dat.

„Normální“ přibližná shoda

V tuto chvíli se můžete cítit trochu zmatení a dezorientovaní z představy, že přibližný zápas může v některých případech vrátit poslední zápas. Pokud ano, nebojte se. Použití přibližné shody k získání posledního zápasu není 'normální' případ. Obvykle uvidíte přibližnou shodu použitou k přiřazení hodnot podle nějakého druhu měřítka. Klasickým příkladem je použití VLOOKUP v režimu přibližné shody k přiřazování známek, což funguje skvěle:

jak odstranit duplicitní řádky v aplikaci Excel
 
= VLOOKUP (E5,key,2,TRUE)

VLOOKUP přibližná shoda pro přiřazení známek

V takových případech vyhledávací tabulka záměrně neobsahuje duplicitní hodnoty, takže celá myšlenka „poslední odpovídající hodnoty“ je irelevantní. Více podrobností o tomto vzorci zde .

Výše uvedené informace mají poskytnout pozadí a kontext toho, jak funguje shoda v Excelu, aby níže popsané přístupy dávaly smysl.

Praktické aplikace

Jak můžeme výše popsané chování použít v praktické situaci? Jedním z běžných scénářů je vyhledání položky „nejnovější“ nebo „poslední“ u položky. Níže například používáme VLOOKUP v režimu přibližné shody, abychom našli nejnovější cenu sandálů. Data oznámení jsou seřazeny podle položky, poté podle data, takže poslední cena pro danou položku se zobrazí jako poslední:

 
= VLOOKUP (F5,data,3,TRUE)

VLOOKUP přibližná shoda + seřazená data = nejnovější cena

INDEX a MATCH

Tímto způsobem lze použít i další vyhledávací funkce. Níže používáme ekvivalent INDEX a ZÁPAS vzorec najít nejnovější cenu se stejnými údaji. Všimněte si, že MATCH je nakonfigurován na přibližnou shodu pro položky seřazené vzestupně nastavením třetího argumentu na 1:

 
= INDEX (price, MATCH (F5,item,1))

INDEX a MATCH se přibližují k nalezení nejnovější ceny

Funkce LOOKUP

The Funkce LOOKUP lze v tomto případě také použít. VZHLÉDNOUT vždy provádí přibližnou shodu, takže funguje dobře ve scénářích „posledního zápasu“. Vzorec je celkem jednoduchý:

 
= LOOKUP (F5,item,price)

Funkce LOOKUP pro vyhledání nejnovější ceny

Poslední shoda s netříděnými daty

Co když chcete poslední shodu, ale data nejsou seřazeny podle hodnoty vyhledávání? Jinými slovy, chcete použít kritéria k nalezení shody a jednoduše chcete poslední položku v datech, která odpovídá vašim kritériím? Jedná se vlastně o případ, kdy svítí funkce LOOKUP, protože LOOKUP dokáže operace s poli zpracovávat nativně, bez ovládání + shift + enter. To znamená, že můžeme dynamicky vytvářet vyhledávací pole k vyhledání požadovaných dat pomocí jednoduchých logických výrazů.

Podívejte se například na vzorec níže:

 
= LOOKUP (2,1/(item=F5),price)

Funkce LOOKUP k vyhledání poslední shody s netříděnými daty

Tento vzorec najde nejnovější cenu pro Sandály v netříděné data.

Možná jste takový vzorec ještě neviděli, pojďme si ho tedy rozebrat v krocích. Při práci zevnitř ven nejprve použijeme kritéria jednoduchým logickým výrazem:

 
item=F5

Výsledkem je pole hodnot PRAVDA a NEPRAVDA, kde PRAVDA odpovídá položkám, které jsou „sandály“ a NEPRAVDA odpovídá všem ostatním hodnotám:

 
{FALSETRUEFALSETRUEFALSEFALSETRUEFALSE}

Dále rozdělíme číslo 1 tímto polem. Během dělení se TRUE stane 1 a FALSE se stane nulou, takže byste měli vizualizovat operaci takto:

 
1/{01010010}

Jedna dělená jednou je jedna a jedna dělená nulou je #DIV/0, takže výsledkem je další pole, toto obsahující pouze 1 s a #DIV/0 chyb:

 
{#DIV/0!1#DIV/0!1#DIV/0!#DIV/0!1#DIV/0!}

Nebojte se, na toto šílenství existuje metoda :)

Jak přidám dny k datu v aplikaci Excel

Nyní jste si možná všimli, že vyhledávací hodnota je číslo 2. To se může zdát matoucí. Jak někdy LOOKUP najde číslo 2 v poli, které obsahuje pouze 1 s a chyby? Nebude. Jako vyhledávací hodnotu používáme 2, abychom donutili LOOKUP skenovat do konec dat .

Funkce LOOKUP bude chyby automaticky ignorovat, takže jediné, co zbývá, jsou 1 s. Bude skenovat 1 s hledáním 2, které nikdy nebudou nalezeny. Když dosáhne konce pole, vrátí se na poslední platnou hodnotu - poslední 1 - která odpovídá poslední shodě na základě zadaných kritérií.

Verze pole INDEX a MATCH

Krása Funkce LOOKUP je schopen zvládnout operaci pole popsanou výše nativně, aniž byste museli zadávat jako maticový vzorec s ovládáním + shift + enter. Pokud však chcete, můžete určitě použít maticový vzorec. Zde je ekvivalentní vzorec INDEX a MATCH, který je třeba zadat pomocí Ctrl + Shift + Enter:

 
{= INDEX (price, MATCH (2,1/(item=F5),1))}

Poslední prázdná buňka

Výše uvedený přístup se ukazuje jako opravdu užitečný. Například trochou doladění logiky můžeme například vyhledat poslední neprázdnou buňku ve sloupci:

 
= LOOKUP (2,1/(B:B''),B:B)

Pomocí LOOKUP najdete poslední neprázdnou buňku

Tento vzorec je zde podrobněji popsáno .

Vyhledat n -tý zápas? Všechny zápasy?

Pokud jste se dostali až sem, možná si říkáte, jak byste našli druhý nebo třetí zápas, nebo jak byste získali všechny zápasy? Zde je několik odkazů pro vás:

Všimnete si, že se takové vzorce komplikují. V roce 2019 přichází do Excelu několik skvělých nových funkcí, díky nimž budou tato řešení mnohem jednodušší. Zůstaňte naladěni. Mezitím na to nezapomeňte Kontingenční tabulky jsou skvělý způsob, jak prozkoumat data bez vzorců .

Co bude dál?

Níže jsou uvedeny průvodce, jak se dozvědět více o vzorcích Excelu. Nabízíme také online video školení .

  • Základy formule - pokud právě začínáte
  • 500 příkladů vzorců s úplným vysvětlením
  • 101 důležitých funkcí Excelu
  • Průvodce všemi funkcemi aplikace Excel (nedokončená práce)
  • Kritéria vzorce - 50 příkladů
  • Vzorce pro podmíněné formátování
Autor Dave Bruns


^