Kopsavilkums
Lai parādītu vārdu sarakstu, kas sakārtots pēc skaitliskās vērtības, varat izmantot formulu kopu, kuras pamatā ir LARGE, INDEX, MATCH, izmantojot funkciju TEXT. Parādītajā piemērā formula G5 ir:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
G10 formula ir:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
kur klienta (B5: B17) datums (C5: C17) un summa (C5: C17) ir nosaukti diapazoni.
Piezīme: tās ir masīvu formulas, un tās jāievada ar vadības taustiņu + Shift + Enter, izņemot Excel 365.
Paskaidrojums
Šis piemērs skaidrības labad ir izveidots divās daļās: (1) formula, lai noteiktu 3 lielākās summas katram mēnesim, un (2) formula, lai izgūtu klienta vārdu katrai no 3 lielākajām mēneša summām.
Ņemiet vērā, ka avota datos nav reāla ranga. Tā vietā mēs izmantojam LARGE funkciju, lai strādātu tieši ar summām. Vēl viena pieeja būtu avota datiem pievienot rangu ar funkciju RANK un izmantot rangu vērtību, lai izgūtu klientu vārdus.
1. daļa: katru mēnesi iegūstiet 3 lielākās summas
Lai iegūtu katras nedēļas trīs lielākās summas, formula G5 ir šāda:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter, izņemot Excel 365.
Strādājot no iekšpuses, vispirms izmantojam funkciju TEXT, lai iegūtu mēnešu nosaukumus katram datumam nosauktajā diapazona datumā :
TEXT(date,"mmmm") // get month names
Pielāgotais skaitļa formāts "mmmm" katram nosauktajam diapazona datumam atgriezīs virkni, piemēram, "Aprīlis", "Maijs", "Jūnijs" . Rezultāts ir šādu mēnešu nosaukumu masīvs:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Funkcija TEXT piegādā šo masīvu funkcijai IF, kas ir konfigurēta datumu filtrēšanai noteiktā mēnesī, pārbaudot mēneša nosaukumu pret vērtību G4 (jaukta atsauce, lai formulu varētu kopēt uz leju un pāri):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Tikai aprīļa summas izdzīvo un tiek caur IF visas pārējās vērtības ir FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Visbeidzot, funkcija LARGE izmanto vērtību F5 (arī jauktu atsauci), lai atgrieztu "n" lielāko atlikušo vērtību. Šūnā G5 LARGE atgriež 18 500, kas ir "1." lielākā vērtība. Formulu nokopējot uz leju un pāri tabulai, funkcija LARGE katrā trīs mēnesī atgriež 3 labākās summas.
Tagad, kad mēs zinām katra mēneša 3 labākās vērtības, mēs varam izmantot šo informāciju kā "atslēgu", lai izgūtu katra klienta vārdu.
2. daļa: izgūt klientu vārdus
Piezīme: Šis ir INDEX un MATCH izmantošanas piemērs ar vairākiem kritērijiem. Ja šī koncepcija jums ir jauna, šeit ir pamatpiemērs.
Lai izgūtu nosaukumu, kas saistīts ar trim galvenajām vērtībām G5: I7, mēs izmantojam INDEX un MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter, izņemot Excel 365.
Strādājot no iekšpuses, MATCH funkcija ir konfigurēta tā, lai tā izmantotu Būla loģiku:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Uzmeklēšanas vērtība ir 1, un uzmeklēšanas masīvs tiek veidots ar šo izteicienu:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Izteiksme, kas izveido uzmeklēšanas masīvu, izmanto Būla loģiku, lai "filtrētu" summas, kas (1) nav aprīlī, un (2) nav vērtība G5 (18 500). Rezultāts ir šāds 1 un 0 masīvs:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Ar atbilstības veida uzmeklēšanas vērtību 1 un nulle (lai piespiestu precīzu atbilstību) MATCH atgriež 3 tieši funkcijai INDEX:
=INDEX(client,3) // returns "Janus"
INDEX atgriež nosaukto diapazona klienta trešo vērtību "Janus".
Formulu kopējot uz leju un pāri tabulai, tā atgriež trīs labākos klientus katrā no trim mēnešiem.
