Обобщение
За да покажете списък с имена, подредени по числова стойност, можете да използвате набор от формули, базирани на LARGE, INDEX, MATCH, с помощта на функцията TEXT. В показания пример формулата в G5 е:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
И формулата в G10 е:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
където клиентът (B5: B17) датата (C5: C17) и сумата (C5: C17) са наречени диапазони.
Забележка: това са формули на масив и трябва да се въвеждат с control + shift + enter, с изключение на Excel 365.
Обяснение
Този пример е съставен от две части за яснота: (1) формула за определяне на първите 3 суми за всеки месец и (2) формула за извличане на името на клиента за всяка от трите най-големи месечни суми.
Обърнете внимание, че в изходните данни няма действителен ранг. Вместо това използваме функцията LARGE, за да работим директно със суми. Друг подход би бил да се добави ранг към изходните данни с функцията RANK и да се използва стойността на ранга за извличане на имена на клиенти.
Част 1: извличайте топ 3 суми всеки месец
За да получите най-добрите 3 суми за всяка седмица, формулата в G5 е:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter, освен в Excel 365.
Работейки отвътре навън, първо използваме функцията TEXT, за да получим имена на месеци за всяка дата в посочената дата на диапазона :
TEXT(date,"mmmm") // get month names
Форматът на персонализирания номер "mmmm" ще върне низ като "април", "май", "юни" за всяко име в посочената дата на диапазона . Резултатът е масив от имена на месеци като този:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Функцията TEXT доставя този масив на функцията IF, която е конфигурирана да филтрира дати на даден месец, като тества името на месеца спрямо стойността в G4 (смесена препратка, така че формулата може да бъде копирана надолу и обратно):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Само сумите през април оцеляват и се издържат АКО; всички останали стойности са FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
И накрая, функцията LARGE използва стойността във F5 (също смесена препратка), за да върне „n-тата“ най-голяма стойност, която остава. В клетка G5 LARGE връща 18 500, "1-вата" най-голяма стойност. Докато формулата се копира надолу и в цялата таблица, функцията LARGE връща първите 3 суми за всеки от трите месеца.
Сега, когато знаем най-добрите 3 стойности за всеки месец, можем да използваме тази информация като „ключ“, за да извлечем името на клиента за всеки.
Част 2: извличане на имена на клиенти
Забележка: Това е пример за използване на INDEX и MATCH с множество критерии. Ако тази концепция е нова за вас, ето основен пример.
За да извлечем името, свързано с първите три стойности в G5: I7, използваме INDEX и MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Забележка: това е формула на масив и трябва да се въведе с control + shift + enter, освен в Excel 365.
Работейки отвътре навън, функцията MATCH е конфигурирана да използва логическа логика по следния начин:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Справочната стойност е 1 и масивът за справка е конструиран с този израз:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Изразът, който създава справочния масив, използва логическа логика за „филтриране“ на суми, които (1) не са през април и (2) не са стойността в G5 (18 500). Резултатът е масив от 1s и 0s като този:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Със справочна стойност 1 и нула за типа на съвпадението (за налагане на точно съвпадение) MATCH връща 3 директно към функцията INDEX:
=INDEX(client,3) // returns "Janus"
ИНДЕКС връща третата стойност в клиента с име на диапазона, "Janus".
Докато формулата се копира надолу и в таблицата, тя връща 3-те най-добри клиента през всеки от трите месеца.
