Santrauka
Norėdami rodyti vardų sąrašą, suskirstytą pagal skaitinę vertę, galite naudoti formulių rinkinį, pagrįstą LARGE, INDEX, MATCH, naudodamiesi funkcija TEXT. Parodytame pavyzdyje formulė G5 yra:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
G10 formulė yra:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
kur kliento (B5: B17) data (C5: C17) ir suma (C5: C17) įvardijami diapazonai.
Pastaba: tai yra masyvo formulės, kurias reikia įvesti paspaudus „Control + Shift + Enter“, išskyrus „Excel 365“.
Paaiškinimas
Šis pavyzdys aiškumo sumetimais yra sudarytas iš dviejų dalių: (1) formulė, skirta nustatyti 3 didžiausias kiekvieno mėnesio sumas, ir (2) formulė, skirta gauti kliento vardą kiekvienai iš 3 geriausių mėnesio sumų.
Atkreipkite dėmesį, kad šaltinio duomenyse nėra faktinio rango. Vietoj to, mes naudojame LARGE funkciją, kad galėtume tiesiogiai dirbti su sumomis. Kitas būdas būtų pridėti reitingą prie šaltinio duomenų naudojant funkciją RANK ir naudoti rango vertę kliento vardams gauti.
1 dalis: gaukite 3 didžiausias sumas kiekvieną mėnesį
Norėdami gauti 3 didžiausias kiekvienos savaitės sumas, formulė G5 yra tokia:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“, išskyrus „Excel 365“.
Dirbdami iš vidaus į išorę, pirmiausia naudojame TEXT funkciją, kad gautume kiekvienos datos, pavadintos diapazono data, mėnesio pavadinimus :
TEXT(date,"mmmm") // get month names
Pasirinktinis skaičių formatas „mmmm“ pateiks eilutes, tokias kaip „Balandis“, „Gegužė“, „Birželis“ kiekvienam vardui, nurodytam diapazono datai . Rezultatas yra tokių mėnesių pavadinimų masyvas:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Funkcija TEXT pateikia šį masyvą funkcijai IF, kuri sukonfigūruota filtruoti datas tam tikram mėnesiui, išbandant mėnesio pavadinimą pagal G4 reikšmę (mišri nuoroda, todėl formulę galima nukopijuoti žemyn ir kitur):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Išgyvena tik balandį gautos sumos ir jas paverčia per IF; visos kitos vertės yra NETIESA:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Galiausiai LARGE funkcija naudoja F5 reikšmę (taip pat mišrią nuorodą), kad grąžintų „n-ą“ didžiausią likusią vertę. G5 langelyje LARGE pateikia 18 500, tai yra „1“ didžiausia vertė. Kai formulė nukopijuojama žemyn ir per visą lentelę, funkcija LARGE pateikia tris didžiausias sumas per tris mėnesius.
Dabar, kai žinome 3 didžiausias kiekvieno mėnesio vertes, galime naudoti šią informaciją kaip „raktą“, kad gautume kiekvieno kliento vardą.
2 dalis: Gaukite kliento vardus
Pastaba: Tai yra INDEX ir MATCH naudojimo su keliais kriterijais pavyzdys. Jei ši koncepcija jums yra nauja, čia yra pagrindinis pavyzdys.
Norėdami gauti pavadinimą, susietą su trimis svarbiausiomis G5: I7 reikšmėmis, naudojame INDEX ir MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Pastaba: tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“, išskyrus „Excel 365“.
Dirbant iš vidaus, MATCH funkcija sukonfigūruota naudoti tokią loginę logiką:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Paieškos vertė yra 1, o paieškos masyvas sukonstruotas naudojant šią išraišką:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Išraiška, sukurianti paieškos masyvą, naudoja loginę logiką, kad „filtruotų“ sumas, kurios (1) nėra balandžio mėn., Ir (2) nėra G5 vertės (18 500). Rezultatas yra 1s ir 0s masyvas, panašus į šį:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Esant atitikties tipui 1 ir nulio paieškos vertei (norint priversti tikslią atitiktį), MATCH grąžina 3 tiesiai į funkciją INDEX:
=INDEX(client,3) // returns "Janus"
INDEX grąžina trečią pavadinto kliento reikšmę „Janus“.
Kai formulė nukopijuojama žemyn ir per visą lentelę, ji grąžina 3 geriausius klientus per tris mėnesius.
