10 лучших продавцов Excel

Question

У меня есть электронная таблица, в которой указаны продажи по продавцам, например:

   A      B             C        D
1  ID    Name        Product  Sales($)   
2  1    John Smith   Toaster   250
3  2    Dina Caan    Kettle    450
4  1    John Smith   Kettle    450
5  1    John Smith   Kettle    250
6  2    Dina Caan    Toaster   250
7  3    Peter Hues   TV        5400
8  3    Peter Hues   Radio     480
9  2    Dina Caan    Radio     100
10 4    Ralph Do     TV        890
11 4    Ralph Do     Radio     100
12 5    Ian Poe      Watch     750
13 6    Tina Hood    TV        450 
14 7    Peter Maan   Watch      99

Мне нужна таблица на основе формулы в F, показывающая n (например, 3) лучших продавцов по общему объему продаж с заголовками ID Name TotalSales($)

Я могу легко сделать это с помощью сводной таблицы, а затем отфильтровать первые n, но в данном случае я хотел бы сделать это с помощью формулы.

Редактировать: Продукт удален из таблицы результатов.

Answer 1

С новыми динамическими формулами массива Excel вы можете получить результат без построения сводной таблицы, но для этого потребуется несколько вспомогательных ячеек. Рассмотрим следующий снимок экрана:

Формула в F2 есть =UNIQUE(B2:B14)и она НЕ копируется.

Формула в G2 =SUMIFS($D$2:$D$14,$B$2:$B$14,UNIQUE(B2:B14))НЕ копируется.

Затем с помощью формулы в I2 можно вывести трех лучших продавцов, ранжированных по общему объему продаж, =INDEX(SORTBY(F2#,G2#,-1),{1;2;3})также НЕ копируя их.

Динамические массивы «растекаются» настолько, насколько это требуется формулой, и ссылка на ячейку формулы динамического массива унаследует этот растекающийся поток. Это означает, что если в следующий раз появятся новые продавцы, вам не нужно будет обновлять список уникальных имен в столбце F. Формула сделает это автоматически.

Answer 2

Чтобы упорядочить самые популярные продажи, используйте следующую формулу массива:

=LARGE(MODE.MULT(IF(MATCH($B$2:$B$14,B:B,0)=ROW($B$2:$B$14),SUMIFS(D:D,B:B,$B$2:$B$14)*{1,1})),ROW(1:1))

Так как это формула массива, ее необходимо подтвердить сочетанием клавиш Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования.

Чтобы получить идентификатор и имя, мы можем использовать это с другой формулой массива:

=INDEX(A:A,MATCH($I2,SUMIFS($D:$D,$B:$B,$B$2:$B$14),0)+1)

Также необходимо нажать Ctrl-Shift-Enter, а затем скопировать вверх и вниз.