5 лучших клиентов с общими взносами

Question

Я хочу получить 5 лучших значений из списка данных, но есть одна загвоздка. Допустим, в списке есть имена клиентов и причитающиеся с них суммы. Но имена клиентов повторяются. Итак, мне нужны 5 лучших клиентов из этого списка. Итак, сначала мы должны добавить, а затем использовать большую формулу, чтобы получить топ-5. Но есть ли формула, с которой мы можем сделать это в одной формуле? В настоящее время я поворачиваю его, а затем использую большую функцию, чтобы сделать это.

Случай уникальных клиентов:

Случай повторяющихся клиентов (проблема):

Нажмите на изображения выше, чтобы увеличить

Ссылка на файл:

https://drive.google.com/open?id=1J2on7BHYTfbMFp0mRbTElCA2vT7tw3QQ

Answer 1

Сводная таблица - хороший способ сделать это, но впоследствии вам не нужна большая формула. Просто получите сводную таблицу, чтобы сделать сортировку для вас, чтобы получить топ-5.

В раскрывающемся списке в верхней части сводной таблицы выберите «Дополнительные параметры сортировки», затем выполните сортировку по сумме причитающейся суммы.

Answer 2

Вы можете создать следующий рейтинг данных, используя только формулы:

предварительная обработка

Данные в столбцах A и B являются необработанными данными, а не формулами.

В D4 - получите уникальные имена:

=LOOKUP(2,1/(COUNTIF($D$3:D3,$A$4:$A$22)=0),$A$4:$A$22)

Убедитесь, что два экземпляра $ A $ 4:$ A $ 22 заменены на достаточно большие массивы, чтобы охватить все ваши данные, и еще больше, если ваш набор данных будет расти.

Перетащите вниз достаточно далеко, чтобы было более чем достаточно строк, чтобы перечислить каждое уникальное имя.

В Е4 - сумма сумм, причитающихся каждому человеку:

=SUMIFS($B$4:$B$22,$A$4:$A$22,D4)

Опять же, убедитесь, что два массива достаточно велики, чтобы охватить все ваши данные, и больше, если ваш набор данных будет расти.

Перетащите вниз рядом со столбцом D.

В F4 - ранг без пропуска значений:

=IF(E4="","",RANK(E4,$E$4:$E$19)+COUNTIF($E$4:E4,E4)-1)

Убедитесь, что диапазон $ E $ 4:$ E $ 19 относится ко всему диапазону данных соседнего столбца, и перетащите вниз рядом с столбцом E.

Результаты

В H4: набраны необработанные числа H8, формул нет.

В I4 - index-match для поиска имен в порядке ранжирования:

=INDEX($D$4:$D$19,MATCH(H4,$F$4:$F$19,0))

В J4 - index-match для поиска соответствующих сумм:

=INDEX($E$4:$E$19,MATCH(H4,$F$4:$F$19,0))

Эти два утащили вниз рядом с 1-5, перечисленными в колонке H.

Answer 3

Я предложил это решение на основе набора данных, связанного с OP. Проверьте снимок экрана.

NB:

Диапазон данных, который я использовал, A282:C296, так что вы настраиваете по мере необходимости.

Я отсортировал имена клиентов вместе с их топ-5.

Формула, которую я использую, находится в ячейке I282, завершите ее с помощью Ctrl+Shift+Enter и, наконец, перетащите ее вниз.

{=MAX((B282:B296=G282)*C282:C296)}

Ячейка G282 - это имя клиента A в OUTPUT.

Обратите внимание, что в вашем списке имен есть DUPLICATE, но я использовал пять названий, выбранных вами, чтобы получить их TOP AMOUNT. Like Name A имеет 2 значения & TOP VALUE 5,35,454. Вот почему список моих (в красном цвете) имеет несколько разных значений.

Надеюсь, это поможет вам.