Combine linhas duplicadas e some os valores no Excel (truques simples)
No Excel, é comum encontrar um conjunto de dados com entradas duplicadas. Freqüentemente, você pode se deparar com uma série de dados em que o principal desafio é combinar com eficiência essas linhas duplicadas e, ao mesmo tempo, somar os valores em uma coluna correspondente, conforme mostrado a seguir na captura de tela. Nesse contexto, nos aprofundaremos em vários métodos práticos que podem ajudá-lo a consolidar dados duplicados e agregar seus valores associados, aumentando a clareza e a utilidade de suas pastas de trabalho do Excel.
Combine linhas duplicadas e some os valores
Combine linhas duplicadas e some os valores com a função Consolidar
O Consolidar é uma ferramenta útil para consolidarmos múltiplas planilhas ou linhas no Excel, com este recurso podemos combinar linhas duplicadas e somar seus valores correspondentes de forma rápida e fácil. Siga as seguintes etapas:
Etapa 1: selecione uma célula de destino
Escolha onde deseja que os dados consolidados apareçam.
Passo 2: Acesse a Função Consolidar e configure a consolidação
- Clique Data > Consolide, veja a captura de tela:
- No Consolide caixa de diálogo:
- (1.) Selecione Soma da função lista suspensa;
- (2.) Clique para selecionar o intervalo que deseja consolidar no Referência caixa;
- (3.) Verifique Linha superior e Coluna esquerda da Use rótulos em opção;
- (4.) Por fim, clique em OK botão.
Resultado:
O Excel combinará todas as duplicatas encontradas na primeira coluna e somará seus valores correspondentes nas colunas adjacentes, conforme mostrado a seguir:
- Se o intervalo não incluir uma linha de cabeçalho, certifique-se de desmarque a linha superior do Use rótulos em opção.
- Com esse recurso, os cálculos só podem ser consolidados com base na primeira coluna (a mais à esquerda) dos dados.
Combine linhas duplicadas e some os valores com um recurso poderoso – Kutools
Se você instalou Kutools for Excel, as TIC Combinar linhas avançadas O recurso permite combinar facilmente linhas duplicadas, fornecendo opções para somar, contar, calcular a média ou executar outros cálculos em seus dados. Além disso, esse recurso não está limitado a apenas uma coluna-chave, ele pode lidar com várias colunas-chave, tornando muito mais fáceis tarefas complexas de consolidação de dados.
Depois de instalar Kutools for Excel, selecione o intervalo de dados e clique em Kutools > Unir e dividir > Combinar linhas avançadas.
No Combinar linhas avançadas caixa de diálogo, defina as seguintes operações:
- Clique no nome da coluna na qual você deseja combinar duplicatas, aqui, clicarei em Produto e selecionarei Chave primária da lista suspensa no Divisão de coluna;
- Em seguida, selecione o nome da coluna na qual deseja somar os valores e selecione Soma da lista suspensa no Divisão de coluna;
- Quanto às demais colunas, você pode escolher a operação que deseja, como combinar os valores com um separador específico ou realizar um determinado cálculo; (esta etapa pode ser ignorada se você tiver apenas duas colunas)
- Por fim, você pode visualizar o resultado combinado e clicar OK botão.
Resultado:
Agora, os valores duplicados na coluna-chave são combinados e outros valores correspondentes são resumidos conforme a captura de tela mostrada a seguir:
- Com este recurso útil, você também pode combinar linhas com base no valor da célula duplicada, conforme mostrado a seguir:
- Este recurso suporta Desfazer, se quiser recuperar seus dados originais, basta pressionar Ctrl + Z.
- Para aplicar este recurso, por favor baixe e instale o Kutools para Excel em primeiro lugar.
Combine linhas duplicadas e some os valores com a Tabela Dinâmica
A Tabela Dinâmica no Excel fornece uma maneira dinâmica de reorganizar, agrupar e resumir dados. Essa funcionalidade se torna extremamente útil quando você se depara com um conjunto de dados cheio de entradas duplicadas e precisa somar os valores correspondentes.
Etapa 1: Criando uma Tabela Dinâmica
- Selecione o intervalo de dados. E então, vá para o inserção guia e clique em Mesa giratória, veja a captura de tela:
- Na caixa de diálogo exibida, escolha onde deseja que o relatório da Tabela Dinâmica seja colocado; você pode colocá-lo em uma nova planilha ou em uma planilha existente conforme necessário. Então clique OK. Veja a imagem:
- Agora, uma Tabela Dinâmica é inserida na célula de destino selecionada. Veja a captura de tela:
Etapa 2: Configurando a Tabela Dinâmica:
- No Campos da Tabela Dinâmica painel, arraste o campo que contém duplicatas para o Linha área. Isso agrupará suas duplicatas.
- A seguir, arraste os campos com os valores que deseja somar para o Valores área. Por padrão, o Excel soma os valores. Veja a demonstração abaixo:
Resultado:
A Tabela Dinâmica agora exibe seus dados com duplicatas combinadas e seus valores resumidos, oferecendo uma visão clara e concisa para análise. Veja a captura de tela:
Combine linhas duplicadas e some os valores com o código VBA
Se você estiver interessado em código VBA, nesta seção forneceremos um código VBA para consolidar linhas duplicadas e somar os valores correspondentes em outras colunas. Siga as seguintes etapas:
Etapa 1: abra o editor do módulo de planilha VBA e copie o código
- Mantenha pressionada a ALT + F11 chaves no Excel para abrir o Microsoft Visual Basic para Aplicações janela.
- Clique inserção > Móduloe cole o seguinte código no Módulo Janela.
Código VBA: Combine linhas duplicadas e some os valoresSub CombineDuplicateRowsAndSumForMultipleColumns() 'Update by Extendoffice Dim SourceRange As Range, OutputRange As Range Dim Dict As Object Dim DataArray As Variant Dim i As Long, j As Long Dim Key As Variant Dim ColCount As Long Dim SumArray() As Variant Dim xArr As Variant Set SourceRange = Application.InputBox("Select the original range:", "Kutools for Excel", Type:=8) If SourceRange Is Nothing Then Exit Sub ColCount = SourceRange.Columns.Count Set OutputRange = Application.InputBox("Select a cell for output:", "Kutools for Excel", Type:=8) If OutputRange Is Nothing Then Exit Sub Set Dict = CreateObject("Scripting.Dictionary") DataArray = SourceRange.Value For i = 1 To UBound(DataArray, 1) Key = DataArray(i, 1) If Not Dict.Exists(Key) Then ReDim SumArray(1 To ColCount - 1) For j = 2 To ColCount SumArray(j - 1) = DataArray(i, j) Next j Dict.Add Key, SumArray Else xArr = Dict(Key) For j = 2 To ColCount xArr(j - 1) = xArr(j - 1) + DataArray(i, j) Next j Dict(Key) = xArr End If Next i OutputRange.Resize(Dict.Count, ColCount).ClearContents i = 1 For Each Key In Dict.Keys OutputRange.Cells(i, 1).Value = Key For j = 1 To ColCount - 1 OutputRange.Cells(i, j + 1).Value = Dict(Key)(j) Next j i = i + 1 Next Key Set Dict = Nothing Set SourceRange = Nothing Set OutputRange = Nothing End Sub
Etapa 2: execute o código
- Depois de colar este código, pressione F5 chave para executar este código. Na caixa de prompt, selecione o intervalo de dados que deseja combinar e somar. E então, clique OK.
- E na próxima caixa de prompt, selecione uma célula onde você irá gerar o resultado e clique OK.
Resultado:
Agora, as linhas duplicadas foram mescladas e seus valores correspondentes foram somados. Veja a captura de tela:
Combinar e somar linhas duplicadas no Excel pode ser simples e eficiente. Escolha entre a função consolidar fácil, os Kutools avançados, as tabelas dinâmicas analíticas ou a codificação VBA flexível para encontrar uma solução que atenda às suas habilidades e necessidades. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais, por favor clique aqui para acessá-los. Obrigado por ler e esperamos fornecer mais informações úteis no futuro!
Artigos relacionados:
- Combine várias linhas em uma com base em duplicatas
- Talvez você tenha um intervalo de dados, na coluna A do nome do produto, existem alguns itens duplicados e agora você precisa remover as entradas duplicadas na coluna A, mas combinar os valores correspondentes na coluna B. Como poderia realizar esta tarefa no Excel ?
- Vlookup e retorna vários valores sem duplicatas
- Às vezes, você pode querer vlookup e retornar vários valores correspondentes em uma única célula de uma vez. Mas, se houver alguns valores repetidos preenchidos nas células retornadas, como você poderia ignorar as duplicatas e apenas manter os valores exclusivos ao retornar todos os valores correspondentes conforme a captura de tela a seguir mostrada no Excel?
- Combine linhas com o mesmo ID/nome
- Por exemplo, você tem uma tabela conforme a captura de tela mostrada abaixo e precisa combinar as linhas com os IDs do pedido, alguma ideia? Aqui, este artigo apresentará duas soluções para você.
Melhores ferramentas de produtividade de escritório
Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo. Clique aqui para obter o recurso que você mais precisa...
Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!