rezumat
Pentru a afișa o listă de nume, clasificate după o valoare numerică, puteți utiliza un set de formule bazate pe LARGE, INDEX, MATCH, cu ajutorul funcției TEXT. În exemplul prezentat, formula din G5 este:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Iar formula din G10 este:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
unde clientul (B5: B17) data (C5: C17) și suma (C5: C17) sunt denumite intervale.
Notă: acestea sunt formule matrice și trebuie introduse cu control + shift + enter, cu excepția Excel 365.
Explicaţie
Acest exemplu este configurat în două părți pentru claritate: (1) o formulă pentru a determina primele 3 sume pentru fiecare lună și (2) o formulă pentru a recupera numele clientului pentru fiecare dintre primele 3 sume lunare.
Rețineți că nu există un rang real în datele sursă. În schimb, folosim funcția LARGE pentru a lucra direct cu sume. O altă abordare ar fi adăugarea de rang la datele sursă cu funcția RANK și utilizarea valorii de rang pentru a prelua numele clienților.
Partea 1: preluați primele 3 sume în fiecare lună
Pentru a prelua primele 3 sume pentru fiecare săptămână, formula din G5 este:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter, cu excepția Excel 365.
Lucrând din interior spre exterior, folosim mai întâi funcția TEXT pentru a obține numele lunilor pentru fiecare dată din data intervalului numit :
TEXT(date,"mmmm") // get month names
Formatul numeric personalizat „mmmm” va returna un șir ca „Aprilie”, „Mai”, „Iunie” pentru fiecare nume din data intervalului desemnat . Rezultatul este o serie de nume de luni ca acesta:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Funcția TEXT oferă această matrice funcției IF, care este configurată pentru a filtra datele dintr-o lună dată testând numele lunii în raport cu valoarea din G4 (o referință mixtă, astfel încât formula să poată fi copiată în jos și peste):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Numai sumele din aprilie supraviețuiesc și ajung la IF; toate celelalte valori sunt FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
În cele din urmă, funcția LARGE folosește valoarea din F5 (de asemenea, o referință mixtă) pentru a returna cea de-a "-a" cea mai mare valoare care rămâne. În celula G5, LARGE returnează 18.500, „prima” cea mai mare valoare. Pe măsură ce formula este copiată în jos și peste masă, funcția LARGE returnează primele 3 sume în fiecare dintre cele trei luni.
Acum, că cunoaștem primele 3 valori în fiecare lună, putem folosi aceste informații ca o „cheie” pentru a prelua numele clientului pentru fiecare.
Partea 2: preluați numele clienților
Notă: Acesta este un exemplu de utilizare a INDEX și MATCH cu mai multe criterii. Dacă acest concept este nou pentru dvs., iată un exemplu de bază.
Pentru a recupera numele asociat cu primele trei valori din G5: I7, folosim INDEX și MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter, cu excepția Excel 365.
Funcționând din interior spre exterior, funcția MATCH este configurată pentru a utiliza logica booleană astfel:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Valoarea de căutare este 1, iar matricea de căutare este construită cu această expresie:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Expresia care creează matricea de căutare folosește logica booleană pentru a „filtra” sumele care (1) nu sunt în aprilie și (2) nu sunt valoarea din G5 (18.500). Rezultatul este o matrice de 1 și 0 ca aceasta:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Cu o valoare de căutare de 1 și zero pentru tipul de potrivire (pentru a forța o potrivire exactă) MATCH returnează 3 direct la funcția INDEX:
=INDEX(client,3) // returns "Janus"
INDEX returnează cea de-a treia valoare din intervalul client numit, „Janus”.
Pe măsură ce formula este copiată în jos și peste masă, aceasta returnează primii 3 clienți în fiecare dintre cele trei luni.
