Eu tenho uma planilha que contém vendas por vendedores de forma 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
Gostaria de uma tabela baseada em fórmula em F mostrando os n (digamos 3) vendedores por total de vendas com os títulos ID Nome TotalSales($)
Posso fazer isso facilmente com uma tabela dinâmica e depois filtrar os n principais, mas, neste caso, gostaria de fazer isso com uma fórmula.
Editar: produto removido da tabela de resultados.
Responder1
Com as novas fórmulas de matriz dinâmica do Excel, você pode obter o resultado sem construir uma tabela dinâmica, mas requer algumas células auxiliares. Considere a seguinte captura de tela:
A fórmula em F2 é =UNIQUE(B2:B14)e NÃO é copiada.
A fórmula em G2 =SUMIFS($D$2:$D$14,$B$2:$B$14,UNIQUE(B2:B14))NÃO é copiada.
Os três principais vendedores classificados pelo total de vendas podem então ser derivados com a fórmula em I2, =INDEX(SORTBY(F2#,G2#,-1),{1;2;3})também NÃO copiada.
Matrizes dinâmicas "derramam" tanto quanto a fórmula exige e referenciar a célula da fórmula de uma matriz dinâmica herdará esse derramamento. Isso significa que se houver novos vendedores na próxima vez, você não precisará atualizar a lista de nomes exclusivos na coluna F. A fórmula fará isso automaticamente.
Responder2
para obter as maiores vendas em ordem, use esta fórmula de matriz:
=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))
Sendo uma fórmula de matriz, deve ser confirmada com Ctrl-Shift-Enter em vez de Enter ao sair do modo de edição.
Para obter o ID e o nome, podemos aproveitar isso com outra fórmula de matriz:
=INDEX(A:A,MATCH($I2,SUMIFS($D:$D,$B:$B,$B$2:$B$14),0)+1)
Também é necessário Ctrl-Shift-Enter e copiar para cima e para baixo.
