Zhrnutie
Ak chcete zobraziť zoznam mien zoradených podľa číselnej hodnoty, môžete pomocou funkcie TEXT použiť skupinu vzorcov založených na LARGE, INDEX, MATCH. V zobrazenom príklade je vzorec v G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
A vzorec v G10 je:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
kde klient (B5: B17) dátum (C5: C17) a suma (C5: C17) sú pomenované rozsahmi.
Poznámka: jedná sa o maticové vzorce a okrem Excel 365 sa musia zadávať pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Tento príklad je kvôli prehľadnosti nastavený na dve časti: (1) vzorec na určenie najvyšších 3 súm pre každý mesiac a (2) vzorec na získanie názvu klienta pre každú z 3 najvyšších súm na mesiac.
Upozorňujeme, že v zdrojových dátach nie je skutočné poradie. Namiesto toho používame funkciu LARGE na priamu prácu s čiastkami. Ďalším prístupom by bolo pridať hodnotenie do zdrojových údajov pomocou funkcie RANK a použitie hodnoty hodnotenia na získanie mien klientov.
Časť 1: Získajte 3 najvyššie sumy každý mesiac
Ak chcete načítať najvyššie 3 sumy za každý týždeň, vzorec v G5 je:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
Pri práci zvnútra najskôr používame funkciu TEXT na získanie názvov mesiacov pre každý dátum v pomenovanom rozsahu dátumov :
TEXT(date,"mmmm") // get month names
Vlastný číselný formát „mmmm“ vráti pre každé meno v pomenovanom rozsahu dátumov reťazec ako „apríl“, „máj“, „jún“ . Výsledkom je pole mien mesiacov, ako je tento:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Funkcia TEXT dodáva toto pole do funkcie IF, ktorá je nakonfigurovaná na filtrovanie dátumov v danom mesiaci testovaním názvu mesiaca oproti hodnote v G4 (zmiešaná referencia, takže vzorec je možné kopírovať dole a naprieč):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Prežijú iba čiastky v apríli, ktoré sa dostanú cez IF; všetky ostatné hodnoty sú FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Nakoniec funkcia LARGE použije hodnotu v F5 (tiež zmiešanú referenciu) na vrátenie „n“ najväčšej hodnoty, ktorá zostáva. V bunke G5 vráti LARGE 18 500, čo je „prvá“ najväčšia hodnota. Keď je vzorec kopírovaný nadol a do tabuľky, funkcia LARGE vráti najvyššie 3 sumy v každom z troch mesiacov.
Teraz, keď poznáme najvyššie 3 hodnoty v každom mesiaci, môžeme tieto informácie použiť ako „kľúč“ na získanie názvu klienta pre každú z nich.
Časť 2: Získanie mien klientov
Poznámka: Toto je príklad použitia indexov INDEX a MATCH s viacerými kritériami. Ak je tento koncept pre vás nový, tu je základný príklad.
Na získanie názvu spojeného s tromi najvyššími hodnotami v G5: I7 používame INDEX a MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, s výnimkou Excel 365.
Pri práci zvnútra von je funkcia MATCH nakonfigurovaná na použitie logickej logiky takto:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Vyhľadávacia hodnota je 1 a vyhľadávacie pole je skonštruované s týmto výrazom:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Výraz, ktorý vytvára vyhľadávacie pole, používa logickú logiku na „odfiltrovanie“ množstiev, ktoré (1) nie sú v apríli a (2) nie hodnoty v G5 (18 500). Výsledkom je pole 1s a 0s, ako je tento:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
S vyhľadávacou hodnotou 1 a nulou pre typ zhody (pre vynútenie presnej zhody) MATCH vráti 3 priamo do funkcie INDEX:
=INDEX(client,3) // returns "Janus"
INDEX vráti tretiu hodnotu v pomenovanom rozsahu klienta „Janus“.
Keď sa vzorec skopíruje nadol a cez tabuľku, vráti 3 najlepších klientov v každom z troch mesiacov.
