Резюме
Щоб відобразити список імен, ранжированих за числовим значенням, ви можете використовувати набір формул на основі 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))
де клієнт (В5: В17) Дата (С5: С17) і кількість (С5: С17) називаються діапазони.
Примітка: це формули масиву і їх потрібно вводити за допомогою control + shift + enter, за винятком Excel 365.
Пояснення
Цей приклад складається з двох частин для наочності: (1) формула для визначення 3-х основних сум для кожного місяця та (2) формула для отримання імені клієнта для кожної з 3-х щомісячних основних сум.
Зверніть увагу, що у вихідних даних немає фактичного рангу. Натомість ми використовуємо функцію 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
Спеціальний формат чисел "мммм" повертає такий рядок, як "квітень", "травень", "червень" для кожного імені в названій даті діапазону . Результат - масив назв місяців, подібних до цього:
("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
Лише суми в квітні виживають і пробиваються за рахунок IF всі інші значення 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"
INDEX повертає третє значення в названому клієнті діапазону, "Janus".
Коли формула копіюється вниз і по таблиці, вона повертає 3 найкращих клієнта за кожен з трьох місяців.
