Tengo una hoja de cálculo que tiene las ventas realizadas por el personal de ventas de modo que
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
Me gustaría una tabla basada en fórmulas en F que muestre los n principales vendedores (digamos 3) por ventas totales con encabezados ID Nombre TotalSales($)
Puedo hacer esto fácilmente con una tabla dinámica y luego filtrar los n superiores, pero en este caso me gustaría hacerlo con una fórmula.
Editar: Producto eliminado de la tabla de resultados.
Respuesta1
Con las nuevas fórmulas de matriz dinámica de Excel, puede obtener el resultado sin crear una tabla dinámica, pero requiere algunas celdas auxiliares. Considere la siguiente captura de pantalla:
La fórmula en F2 es =UNIQUE(B2:B14)y NO está copiada.
La fórmula en G2 =SUMIFS($D$2:$D$14,$B$2:$B$14,UNIQUE(B2:B14))NO se copia.
Los tres vendedores principales clasificados por sus ventas totales se pueden derivar con la fórmula en I2, =INDEX(SORTBY(F2#,G2#,-1),{1;2;3})que tampoco se copia.
Las matrices dinámicas se "desbordan" hasta donde la fórmula lo requiere y hacer referencia a la celda de fórmula de una matriz dinámica heredará ese derrame. Eso significa que si hay nuevos vendedores la próxima vez, no necesitará actualizar la lista de nombres únicos en la columna F. La fórmula lo hará automáticamente.
Respuesta2
Para obtener las mejores ventas, utilice esta fórmula matricial:
=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))
Al ser una fórmula matricial, se debe confirmar con Ctrl-Shift-Enter en lugar de Enter al salir del modo de edición.
Para obtener el ID y el nombre, podemos aprovecharlo con otra fórmula matricial:
=INDEX(A:A,MATCH($I2,SUMIFS($D:$D,$B:$B,$B$2:$B$14),0)+1)
También necesito Ctrl-Shift-Enter y luego copiar hacia arriba y hacia abajo.
