Resumo
Para exibir uma lista de nomes, classificados por um valor numérico, você pode usar um conjunto de fórmulas baseadas em LARGE, INDEX, MATCH, com ajuda da função TEXT. No exemplo mostrado, a fórmula em G5 é:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
E a fórmula em G10 é:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
onde cliente (B5: B17), data (C5: C17) e quantidade (C5: C17) são intervalos nomeados.
Observação: essas são fórmulas de matriz e devem ser inseridas com control + shift + enter, exceto no Excel 365.
Explicação
Este exemplo é configurado em duas partes para maior clareza: (1) uma fórmula para determinar os 3 principais valores para cada mês e (2) uma fórmula para recuperar o nome do cliente para cada um dos 3 principais valores mensais.
Observe que não há classificação real nos dados de origem. Em vez disso, estamos usando a função LARGE para trabalhar diretamente com os valores. Outra abordagem seria adicionar classificação aos dados de origem com a função RANK e usar o valor de classificação para recuperar nomes de clientes.
Parte 1: recupere os 3 principais valores a cada mês
Para recuperar os 3 principais valores de cada semana, a fórmula em G5 é:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter, exceto no Excel 365.
Trabalhando de dentro para fora, primeiro usamos a função TEXT para obter os nomes dos meses para cada data no intervalo de data nomeado :
TEXT(date,"mmmm") // get month names
O formato de número personalizado "mmmm" retornará uma string como "abril", "maio", "junho" para cada nome na data do intervalo nomeado . O resultado é uma matriz de nomes de meses como este:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
A função TEXT entrega essa matriz para a função IF, que é configurada para filtrar datas em um determinado mês, testando o nome do mês em relação ao valor em G4 (uma referência mista, de modo que a fórmula pode ser copiada para baixo e cruzando):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Apenas quantias em abril sobrevivem e passam pelo IF; todos os outros valores são FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Finalmente, a função LARGE usa o valor em F5 (também uma referência mista) para retornar o "enésimo" maior valor restante. Na célula G5, LARGE retorna 18.500, o "primeiro" maior valor. Conforme a fórmula é copiada para baixo e na tabela, a função LARGE retorna os 3 principais valores em cada um dos três meses.
Agora que sabemos os 3 principais valores de cada mês, podemos usar essas informações como uma "chave" para recuperar o nome do cliente de cada um.
Parte 2: recuperar nomes de clientes
Nota: Este é um exemplo de uso de INDEX e MATCH com vários critérios. Se este conceito é novo para você, aqui está um exemplo básico.
Para recuperar o nome associado aos três valores principais em G5: I7, usamos INDEX e MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter, exceto no Excel 365.
Trabalhando de dentro para fora, a função MATCH é configurada para usar a lógica booleana como esta:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
O valor de pesquisa é 1 e a matriz de pesquisa é construída com esta expressão:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
A expressão que cria a matriz de pesquisa usa a lógica booleana para "filtrar" os valores que (1) não são em abril e (2) não são o valor em G5 (18.500). O resultado é uma matriz de 1s e 0s como esta:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Com um valor de pesquisa de 1 e zero para o tipo de correspondência (para forçar uma correspondência exata), MATCH retorna 3 diretamente para a função INDEX:
=INDEX(client,3) // returns "Janus"
INDEX retorna o terceiro valor no cliente de intervalo nomeado, "Janus".
Conforme a fórmula é copiada para baixo e na tabela, ela retorna os 3 principais clientes em cada um dos três meses.
