Резюме
Чтобы отобразить список имен, упорядоченный по числовому значению, вы можете использовать набор формул, основанный на НАИБОЛЕЕ, ИНДЕКС, ПОИСКПОЗ, с помощью функции ТЕКСТ. В показанном примере формула в 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) являются именованными диапазонами.
Примечание. Это формулы массива, которые необходимо вводить с помощью клавиш Ctrl + Shift + Enter, кроме Excel 365.
Объяснение
Этот пример состоит из двух частей для ясности: (1) формула для определения трех крупнейших сумм за каждый месяц и (2) формула для получения имени клиента для каждой из трех основных ежемесячных сумм.
Обратите внимание, что в исходных данных нет фактического ранга. Вместо этого мы используем функцию НАИБОЛЬШИЙ для работы непосредственно с суммами. Другой подход - добавить ранг к исходным данным с помощью функции RANK и использовать значение ранга для извлечения имен клиентов.
Часть 1: получайте первые 3 суммы каждый месяц
Чтобы получить 3 верхних суммы за каждую неделю, формула в G5:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Работая изнутри, мы сначала используем функцию ТЕКСТ, чтобы получить названия месяцев для каждой даты в названном диапазоне date :
TEXT(date,"mmmm") // get month names
Пользовательский формат чисел «мммм» вернет строку типа «апрель», «май», «июнь» для каждого имени в указанном диапазоне дат . Результатом является массив названий месяцев, подобных этому:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Функция ТЕКСТ доставляет этот массив в функцию ЕСЛИ, которая настроена на фильтрацию дат в данном месяце, проверяя имя месяца по значению в G4 (смешанная ссылка, поэтому формулу можно копировать вниз и поперек):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Только суммы в апреле выживают и проходят через IF; все остальные значения - ЛОЖЬ:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Наконец, функция НАИБОЛЬШИЙ использует значение в F5 (также смешанную ссылку) для возврата n-го наибольшего оставшегося значения. В ячейке G5 функция НАИБОЛЬШИЙ возвращает 18 500, «1-е» наибольшее значение. Поскольку формула копируется вниз по таблице, функция НАИБОЛЬШИЙ возвращает 3 верхних суммы за каждый из трех месяцев.
Теперь, когда мы знаем три основных значения за каждый месяц, мы можем использовать эту информацию как «ключ» для получения имени клиента для каждого из них.
Часть 2: получение имен клиентов
Примечание. Это пример использования INDEX и MATCH с несколькими критериями. Если эта концепция для вас нова, вот простой пример.
Чтобы получить имя, связанное с тремя верхними значениями в G5: I7, мы используем INDEX и MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Примечание: это формула массива, и ее необходимо вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Работая изнутри, функция ПОИСКПОЗ настроена на использование такой логической логики:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Значение поиска равно 1, и массив поиска создается с помощью этого выражения:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Выражение, которое создает поисковый массив, использует логическую логику для «фильтрации» сумм, которые (1) не относятся к апрелю и (2) не соответствуют значению в G5 (18 500). Результатом является массив из единиц и нулей, например:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
При поисковом значении 1 и нуле для типа соответствия (для обеспечения точного совпадения) ПОИСКПОЗ возвращает 3 непосредственно в функцию ИНДЕКС:
=INDEX(client,3) // returns "Janus"
ИНДЕКС возвращает третье значение в клиенте именованного диапазона, «Янус».
Поскольку формула копируется вниз и поперек таблицы, она возвращает трех основных клиентов за каждый из трех месяцев.
