Резиме
Да бисте приказали листу имена рангираних нумеричком вредношћу, можете да користите скуп формула на основу ЛАРГЕ, ИНДЕКС, МАТЦХ, уз помоћ функције ТЕКСТ. У приказаном примеру, формула у Г5 је:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
А формула у Г10 је:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
где клијент (Б5: Да Б17) датум (Ц5: Ц17) и износ (Ц5: Ц17) се зове опсега.
Напомена: ово су формуле низа и морају се унети са цонтрол + схифт + ентер, осим у програму Екцел 365.
Објашњење
Овај пример је постављен из два дела ради прегледности: (1) формула за одређивање 3 главна износа за сваки месец и (2) формула за преузимање имена клијента за сваки од 3 најбоља месечна износа.
Имајте на уму да у изворним подацима нема стварног ранга. Уместо тога, користимо функцију ЛАРГЕ да бисмо директно радили са износима. Други приступ би био додавање ранга изворним подацима помоћу функције РАНК и употреба вредности ранга за дохватање имена клијената.
1. део: дохватите највише 3 износа сваког месеца
Да бисте преузели прва 3 износа за сваку недељу, формула у Г5 је:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер, осим у програму Екцел 365.
Радећи изнутра, прво користимо функцију ТЕКСТ да бисмо добили називе месеци за сваки датум у именованом датуму опсега :
TEXT(date,"mmmm") // get month names
Прилагођени формат броја "мммм" вратиће низ као што су "Април", "Маи", "Јуне" за свако име у именованом датуму опсега . Резултат је низ имена месеца попут овог:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Функција ТЕКСТ испоручује овај низ функцији ИФ која је конфигурисана за филтрирање датума у датом месецу тестирањем имена месеца према вредности у Г4 (мешовита референца, тако да се формула може копирати надоле и уназад):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Само износи у априлу опстају и пролазе кроз АКО; све остале вредности су ФАЛСЕ:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Коначно, функција ЛАРГЕ користи вредност у Ф5 (такође мешовита референца) за враћање „н-те“ највеће вредности која остаје. У ћелији Г5, ЛАРГЕ враћа 18.500, „1.“ највећу вредност. Како се формула копира надоле и преко табеле, ЛАРГЕ функција враћа прва 3 износа у сваком од три месеца.
Сада када знамо највише 3 вредности сваког месеца, можемо да користимо ове информације као „кључ“ да бисмо дохватили име клијента за сваки.
2. део: преузимање имена клијената
Напомена: Ово је пример употребе ИНДЕКС-а и МАТЦХ-а са више критеријума. Ако вам је овај концепт нов, ево основног примера.
Да бисмо пронашли име повезано са три главне вредности у Г5: И7, користимо ИНДЕКС и МАТЦХ:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Напомена: ово је формула низа и мора се унети са цонтрол + схифт + ентер, осим у програму Екцел 365.
Радећи изнутра, функција МАТЦХ је конфигурисана да користи логичку логику попут ове:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Вредност претраживања је 1, а низ претраживања конструисан је са овим изразом:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Израз који ствара низ претраживања користи логичку логику за „филтрирање“ износа који (1) нису у априлу, а (2) нису вредности у Г5 (18.500). Резултат је низ од 1 и 0 као што је овај:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Са траженом вредношћу 1 и нулом за тип подударања (да би се форсирало тачно подударање), МАТЦХ враћа 3 директно у функцију ИНДЕКС:
=INDEX(client,3) // returns "Janus"
ИНДЕКС враћа трећу вредност у именованом клијенту опсега, "Јанус".
Како се формула копира надоле и преко табеле, враћа 3 најбоља клијента у сваком од три месеца.
