Como criar uma lista dinâmica dos 10 melhores ou N no Excel?
Em muitos projetos e processos de negócios, é frequentemente necessário classificar indivíduos, organizações, produtos ou outras entidades com base em seu desempenho ou valores numéricos. Uma "lista dos melhores" serve para destacar as entradas de maior desempenho, como os melhores alunos por nota, os melhores vendedores ou departamentos com a maior receita. Por exemplo, você pode ter uma tabela de notas de alunos e desejar extrair dinamicamente os 10 melhores pontuadores para prêmios, análise ou monitoramento de resultados educacionais, conforme ilustrado na captura de tela abaixo. Criar uma lista dinâmica dos 10 melhores ou dos N melhores no Excel permite que você veja os resultados atualizados automaticamente à medida que seus dados mudam, economizando tempo e reduzindo erros de classificação manual. Este guia introduz várias soluções práticas - incluindo fórmulas, tabelas dinâmicas e macros VBA - para ajudá-lo a construir uma lista dinâmica dos 10 ou N melhores para atender a uma variedade de necessidades de análise de dados de forma eficiente.
Criar uma lista dinâmica dos 10 melhores no Excel
- Fórmulas para criar uma lista dinâmica dos 10 melhores
- Fórmulas para criar uma lista dinâmica dos 10 melhores com critérios
Criar uma lista dinâmica dos 10 melhores no Office 365
- Fórmula para criar uma lista dinâmica dos 10 melhores
- Fórmula para criar uma lista dinâmica dos 10 melhores com critérios
Criar uma lista dinâmica dos 10 melhores com uma Tabela Dinâmica
Criar uma lista dinâmica dos 10 melhores no Excel
No Excel 2019 e versões anteriores, criar uma lista dinâmica dos 10 melhores (ou dos N melhores) envolve combinar fórmulas para extrair simultaneamente os valores mais altos e seus nomes ou IDs associados. Essa solução é amplamente utilizada e adequada para situações em que você deseja que a lista seja atualizada automaticamente conforme seus dados mudam. As seguintes operações descrevem como alcançar isso usando fórmulas clássicas do Excel. Essas fórmulas oferecem flexibilidade e não exigem suplementos especiais do Excel, mas o processo de configuração é um pouco mais complexo em comparação com algumas funções modernas de matriz dinâmica.
Fórmulas para criar uma lista dinâmica dos 10 melhores
1. Para começar, você precisa extrair os 10 valores mais altos da sua faixa de valores. Insira a seguinte fórmula em uma célula em branco (por exemplo, célula G2). Após inserir a fórmula, arraste a alça de preenchimento para baixo para gerar sua lista dinâmica dos 10 valores mais altos. Veja a captura de tela:
2. Em seguida, para exibir os nomes (ou IDs) correspondentes a esses valores mais altos, insira a seguinte fórmula na célula F2. Esta é uma fórmula de matriz, então após inseri-la, pressione Ctrl + Shift + Enter para confirmar. Esta fórmula encontra os nomes correspondentes aos valores mais altos que você acabou de extrair:
- A2:A20 é a faixa de onde retirar os nomes;
- B2:B20 é a faixa de notas ou valores;
- G2 é o valor mais alto da fórmula acima;
- B1 é o cabeçalho da lista de valores e usado para deslocamento nos cálculos de LIN.
Essa fórmula vincula dinamicamente os valores mais altos aos seus nomes. Se a sua faixa de valores contiver duplicatas, CONT.SE garante que cada nome correspondente apareça apenas uma vez com sua pontuação.
3. Após extrair o primeiro resultado, selecione a fórmula na célula F2 e arraste a alça de preenchimento para baixo para copiar a fórmula para quantas linhas forem necessárias. Isso expandirá seu resultado para exibir dinamicamente os nomes de todas as entradas principais, correspondendo aquelas pontuações. Veja a captura de tela:
Desbloqueie a Magia do Excel com o Kutools AI
- Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
- Codificação VBA: Escreva e implemente código VBA sem esforço.
- Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
- Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.
Fórmulas para criar uma lista dinâmica dos 10 melhores com critérios
Em algumas tarefas de análise, você pode precisar de uma lista dos melhores que só exiba entradas que atendam a certos critérios - como limitar os melhores resultados a um grupo específico, equipe ou categoria. Por exemplo, você pode querer identificar as 10 melhores notas apenas para a "Turma 1" de uma planilha de dados total contendo notas de várias turmas. Aqui está como você pode usar fórmulas para este cenário:
1. Comece extraindo os 10 valores mais altos que atendem ao seu critério especificado (por exemplo, "Turma 1") do conjunto de dados. Insira esta fórmula na célula de destino (por exemplo, J2):
2. Após inserir a fórmula, pressione Ctrl + Shift + Enter para confirmar como uma fórmula de matriz e depois arraste a alça de preenchimento para baixo para preencher outras células. A fórmula retornará os 10 valores mais altos que correspondem à sua condição escolhida (por exemplo, todas as notas da "Turma 1").
3. Para listar os nomes correspondentes a esses valores mais altos sob seus critérios, copie e cole a fórmula abaixo na célula I2 e pressione Ctrl + Shift + Enter como uma fórmula de matriz. Em seguida, preencha para baixo conforme necessário para gerar a lista completa de nomes.
Certifique-se de ajustar as faixas nas fórmulas para corresponder à sua configuração de dados real. Tenha em mente que o uso de grandes faixas de dados com fórmulas de matriz pode diminuir o desempenho. Se valores duplicados aparecerem nos seus 10 melhores, a fórmula lidará adequadamente com pontuações repetidas e dará múltiplos nomes de estudantes se suas notas forem iguais.
Criar uma lista dinâmica dos 10 melhores no Office 365
Embora versões anteriores do Excel exijam combinar várias funções com fórmulas de matriz, o Office 365 (e o Excel 2021) introduz funções de matriz dinâmica como ÍNDICE, ORDENAR, SEQUÊNCIA e FILTRAR que simplificam muito o fluxo de trabalho. Essas funções facilitam a construção de listas dinâmicas dos 10 melhores, reduzem erros e são especialmente úteis para tabelas que crescem ou mudam frequentemente. Se você opera em um ambiente com dados constantemente atualizados, essas funções podem agilizar sua análise e permitir decisões de negócios mais rápidas.
Fórmula para criar uma lista dinâmica dos 10 melhores
Para extrair e exibir uma lista dinâmica dos 10 melhores usando o Office 365, insira a fórmula abaixo na célula de saída desejada. Tudo o que você precisa fazer é ajustar as faixas e números com base nas suas necessidades, e a fórmula mostrará automaticamente os últimos resultados dos 10 melhores sempre que seus dados mudarem.
Basta pressionar a tecla Enter. A lista completa dos 10 melhores aparece instantaneamente e permanecerá dinâmica, de modo que dados adicionais ou pontuações modificadas são refletidos imediatamente na sua classificação.
Função ORDENAR:
=ORDENAR(matriz, [índice_ordenar], [ordem_ordenar], [por_col])
- matriz: A faixa que você deseja ordenar.
- [índice_ordenar]: Número da coluna para ordenar. Para uma tabela típica de notas, geralmente é a segunda coluna.
- [ordem_ordenar]: Use 1 para ordem crescente ou -1 para ordem decrescente. Para obter as melhores notas, use -1.
- [por_col]: Se deve ordenar por colunas (VERDADEIRO) ou por linhas (FALSO ou omitido).
Por exemplo: ORDENAR(A2:B20,2,-1) ordena A2:B20 pela segunda coluna em ordem decrescente.
Função SEQUÊNCIA:
=SEQUÊNCIA(linhas, [colunas], [início], [incremento])
- linhas: Número de linhas a retornar, por exemplo, 10 para uma lista dos 10 melhores.
- [colunas]: (Opcional) Número de colunas a retornar.
- [início]: (Opcional) Valor inicial.
- [incremento]: (Opcional) Valor para incrementar.
SEQUÊNCIA(10) gera os números 1 até 10, permitindo que ÍNDICE escolha os 10 melhores resultados ordenados.
Combinando isso, =ÍNDICE(ORDENAR(A2:B20,2,-1),SEQUÊNCIA(10),{1,2}) fornece uma lista dinâmica de duas colunas dos 10 melhores.
Fórmula para criar uma lista dinâmica dos 10 melhores com critérios
Se você precisar extrair os 10 melhores para um grupo específico, como "Turma 1", essas funções avançadas do Office 365 podem criar uma lista dos N melhores enquanto inclui apenas aquelas linhas que atendem aos seus critérios. Coloque a fórmula abaixo no local desejado e ajuste as faixas e a célula de critérios conforme necessário:
Após inserir a fórmula, basta pressionar a tecla Enter. A lista dos 10 melhores filtrada e classificada apenas para o critério especificado será exibida imediatamente, atualizando sempre que você modificar seus dados ou critério.
Função FILTRAR:
=FILTRAR(matriz, incluir, [se_vazio])
- matriz: A faixa de células para filtrar.
- incluir: A condição (por exemplo, igual a uma classe específica) para inclusão.
- [se_vazio]: (Opcional) O que exibir se nenhum resultado atender aos critérios.
=FILTRAR(A2:C25,B2:B25=F2) retorna apenas aquelas linhas onde a coluna B corresponde ao valor em F2.
Criar uma lista dinâmica dos 10 melhores com uma Tabela Dinâmica
Tabela Dinâmica: Exibir automaticamente os N melhores resultados de forma interativa
Uma forma alternativa de criar uma lista dinâmica dos N melhores é utilizando o recurso de Tabela Dinâmica do Excel. Esse método é especialmente adequado para grandes conjuntos de dados, análise interativa (como alterar rapidamente o número de itens principais ou aplicar filtros) ou quando você deseja evitar fórmulas complexas. As tabelas dinâmicas são fáceis de usar e se atualizam automaticamente quando os dados mudam, tornando-as excelentes para dashboards ou relatórios compartilhados com outros.
Para criar uma lista dinâmica dos N melhores usando uma tabela dinâmica:
- Clique em qualquer lugar dentro da sua tabela de dados, depois vá em Inserir > Tabela Dinâmica.
- Na caixa de diálogo da Tabela Dinâmica, escolha onde deseja que a tabela dinâmica seja colocada e clique em OK.
- Arraste o campo "Nome" (ou identificador semelhante) para a área Linhas.
- Arraste a coluna "Pontuação" (ou coluna de valor) para a área Valores. Ela geralmente padrão para "Soma de" ou "Contagem de" — para listas principais, você geralmente deseja "Soma" ou "Máx". Altere o cálculo do campo de valor, se necessário, clicando com o botão direito e escolhendo Resumir Valor Por.
- Classifique a coluna "Pontuação" em ordem decrescente clicando com o botão direito em um valor e selecionando Classificar > Classificar do Maior para o Menor.
- Para limitar aos N melhores resultados, clique na seta suspensa em Rótulos de Linha, selecione Filtros de Valor > Top 10..., defina o número (por exemplo, Top 10) e o campo para filtrar, depois clique em OK.
Sua tabela dinâmica agora mostra os 10 melhores (ou qualquer N que você especificar) de forma dinâmica. Para alterar o Top N, basta revisitar as configurações de filtro. Se seus dados mudarem, atualize a tabela dinâmica para refletir instantaneamente as novas classificações.
Os benefícios dessa abordagem incluem configuração rápida, classificação fácil e ajuste interativo. No entanto, as tabelas dinâmicas não podem adicionar automaticamente linhas correspondentes de outras colunas a menos que incluídas na área Linhas ou Valores. Usuários avançados podem personalizar ainda mais relatórios agrupando, criando segmentações ou incorporando o filtro Top N em dashboards.
Criar uma lista dinâmica dos 10 melhores usando VBA
Macro VBA: Gerar e atualizar automaticamente uma lista dos N melhores
Usar uma macro VBA é adequado para usuários que lidam com dados extensos ou frequentemente atualizados onde é necessário automatizar a extração e atualização de uma lista dinâmica dos N melhores. Macros são ideais para reduzir tarefas repetitivas e garantir consistência. Você pode criar uma rotina que classifique seus dados e copie apenas as N melhores linhas para um local específico toda vez que ela for executada.
Para usar uma macro VBA para criar uma lista dinâmica dos N melhores, siga estas etapas:
- Clique em Desenvolvedor > Visual Basic para abrir o editor VBA. (Se você não vir a guia Desenvolvedor, vá para Arquivo > Opções > Personalizar Faixa de Opções e habilite "Desenvolvedor".)
- Na janela VBA, clique em Inserir > Módulo para adicionar um novo módulo.
- Cole o seguinte código VBA no módulo:
Sub ExtractTopNList()
'Updated by Extendoffice 2025/7/24
Dim DataRange As Range
Dim OutputRange As Range
Dim N As Integer
Dim ws As Worksheet, tempWS As Worksheet
Dim xTitleId As String
Dim LastCol As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set DataRange = Application.InputBox("Select the full data range to analyze (including headers)", xTitleId, ws.UsedRange.Address, Type:=8)
Set OutputRange = Application.InputBox("Select the top-left cell of the output area", xTitleId, "", Type:=8)
N = Application.InputBox("How many top items to extract? (Enter a positive integer)", xTitleId, 10, Type:=1)
If DataRange Is Nothing Or OutputRange Is Nothing Or N < 1 Then Exit Sub
' Create a temporary worksheet to avoid sorting original data
Set tempWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
DataRange.Copy tempWS.Range("A1")
' Determine last column for sorting key
LastCol = DataRange.Columns.Count
' Sort in temporary sheet
tempWS.UsedRange.Sort Key1:=tempWS.Cells(1, LastCol), Order1:=xlDescending, Header:=xlYes
' Copy headers and top N rows to output
tempWS.Rows(1).Copy Destination:=OutputRange
tempWS.Range("A2").Resize(N, LastCol).Copy Destination:=OutputRange.Offset(1, 0)
' Optional: Delete temporary sheet
Application.DisplayAlerts = False
tempWS.Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub4. Para executar a macro, certifique-se de que seus dados estão organizados corretamente em uma tabela com cabeçalhos. Pressione F5 ou clique no 
botão no editor VBA. Você será solicitado a:
- Selecionar sua faixa de dados (incluindo cabeçalhos para classificação adequada).
- Selecionar a célula de saída para colar os resultados.
- Insira o número N (por exemplo, 10 para os 10 melhores).
A macro copiará as N melhores entradas (incluindo cabeçalhos) para o local que você especificou.
É aconselhável usar isso em uma cópia ou backup da sua pasta de trabalho ao testar pela primeira vez. Se ocorrerem erros (como selecionar uma faixa incorreta), execute novamente e certifique-se de que suas faixas e layout de dados estão corretos.
Essa solução é ideal para automatizar tarefas de relatório repetitivas, criar dashboards ou atualizar rapidamente relatórios dos N melhores sem fórmulas ou classificação manuais. Você pode personalizar ainda mais o script VBA para lógica de classificação mais complexa, como classificar por uma coluna específica ou exportar resultados para outra pasta de trabalho.
Solução de problemas: Se a macro não funcionar conforme o esperado, verifique se sua tabela de dados inclui cabeçalhos adequados, corrija os tipos de dados para evitar problemas de classificação e certifique-se de que as referências de células sejam selecionadas corretamente em cada prompt. Sempre salve seu trabalho antes de executar macros para evitar alterações de dados acidentais.
Em resumo, o Excel suporta vários métodos para gerar e manter uma lista dinâmica dos N melhores — desde fórmulas tradicionais até poderosas funções do Office 365, tabelas dinâmicas para análise interativa e macros VBA para automação avançada. Escolha o método que melhor atende ao seu fluxo de trabalho e à escala de dados. Usar fórmulas é eficaz para a maioria das análises manuais, funções do Office 365 oferecem a maior simplicidade e poder, tabelas dinâmicas são excelentes para resumos rápidos e flexíveis, e o VBA é especialmente útil para automatizar grandes tarefas de classificação repetitivas. Sempre verifique a integridade de suas fórmulas ou código e adapte as referências de células para corresponder a quaisquer alterações na estrutura de seus dados à medida que seu projeto evolui.
Melhores Ferramentas de Produtividade para Office
Impulsione suas habilidades no Excel com Kutools para Excel e experimente uma eficiência incomparável. Kutools para Excel oferece mais de300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para acessar o recurso que você mais precisa...
Office Tab traz interface com abas para o Office e facilita muito seu trabalho
- Habilite edição e leitura por abas no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie múltiplos documentos em novas abas de uma mesma janela, em vez de em novas janelas.
- Aumente sua produtividade em50% e economize centenas de cliques todos os dias!
Todos os complementos Kutools. Um instalador
O pacote Kutools for Office reúne complementos para Excel, Word, Outlook & PowerPoint, além do Office Tab Pro, sendo ideal para equipes que trabalham em vários aplicativos do Office.
- Pacote tudo-em-um — complementos para Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Um instalador, uma licença — configuração em minutos (pronto para MSI)
- Trabalhe melhor em conjunto — produtividade otimizada entre os aplicativos do Office
- Avaliação completa por30 dias — sem registro e sem cartão de crédito
- Melhor custo-benefício — economize comparado à compra individual de add-ins