Como concatenar linhas em uma célula com base no grupo no Excel?
Aqui está um intervalo de duas colunas, uma é a lista da turma e a outra é a lista dos nomes dos alunos. Como você pode ver, alguns alunos estão na mesma classe, outros não. Agora eu quero concatenar os alunos da mesma classe em uma célula, conforme a captura de tela mostrada abaixo, como posso lidar com isso rapidamente no Excel?
Agrupe e concatene com fórmulas e função de filtro
Agrupe e concatene com o código VBA
Agrupe e concatene com linhas combinadas avançadas
Agrupe e concatene com fórmulas e função de filtro
No Excel, você pode aplicar fórmulas a linhas concatenadas com base em uma coluna e, em seguida, usar a função Filtro para exibir apenas o resultado.
Note: Você precisa classificar seus dados por classe antes de seguir as etapas.
1. Em uma célula em branco ao lado do intervalo de dados, por exemplo, C13, digite esta fórmula =IF(A13=A12,C12&", "&B13,B13), pressione Enter e preencha a fórmula para células arrastando a alça de preenchimento.
Na fórmula, A13 é o primeiro dado na coluna “Classe”, B13 é o primeiro dado na coluna “Nome”, “,” é o separador para delimitar o conteúdo concatenado.
2. Em seguida, na próxima coluna, D13, digite esta fórmula =IF(A13<>A14,"Last","") e arraste a alça de preenchimento para baixo para aplicar a fórmula às células necessárias.
3. Agora selecione todo o intervalo de dados, incluindo fórmulas e clique em Data > Filter adicionar Filter icons aos dados.
4. Clique na Filter icon no último cabeçalho da fórmula, verifique Last caixa de seleção apenas na lista suspensa e clique em OK.
Agora o resultado é mostrado conforme abaixo, você pode remover a última coluna auxiliar se não precisar dela nunca.
Agrupe e concatene com o código VBA
Aqui está um código VBA que também pode lidar com esse trabalho.
1. Pressione Alt + F11 chaves para habilitar o Microsoft Visual Basic for Applications janela.
2. Em seguida, na janela, clique em Tools > References Para habilitar References caixa de diálogo e verificar Microsoft Scripting Runtime. Veja a imagem:
3. Clique OKE clique Insert > Module na janela do VBA, e copie e cole o código do VBA abaixo no Module roteiro. Veja a imagem:
VBA: concatenar linhas em uma célula com base no grupo
Sub ConcatenateCellsIfSameValues()
'UpdatebyExtendoffice20180201
Dim I As Long
Dim J As Long
Dim xRg As Range
Dim xRgKey As Range
Dim xRgVal As Range
Dim xStr As String
Dim xDic As New Dictionary
On Error Resume Next
Set xRg = Application.InputBox("Select data range", "KuTools for Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
Set xRgKey = Application.InputBox("Select key column", "KuTools for Excel", xRg.Columns(1).Address, , , , , 8)
If xRgKey Is Nothing Then
MsgBox "Key column cannot be empty", vbInformation, "KuTools for Excel"
End If
Set xRgVal = xRg(1).Offset(, 1).Resize(xRg.Rows.Count, xRg.Columns.Count - 1)
For I = 1 To xRgKey.Count
If I > xRgKey.Count Then Exit For
xStr = ""
For J = 1 To xRgVal.Columns.Count
xStr = xStr & " " & xRgVal(I, J)
Next
If xDic.Exists(xRgKey(I).Text) Then
xDic(xRgKey(I).Text) = xDic(xRgKey(I).Text) & xStr
xRgKey(I).EntireRow.Delete
I = I - 1
Else
xDic.Add xRgKey(I).Text, xStr
End If
Next
For I = 1 To xRgVal.Count
xRgVal(I).Value = xDic(xRgKey(I).Text)
Next
End Sub
4. Pressione F5 e selecione o intervalo de dados que você usa na caixa de diálogo pop-up.
5. Clique OK para selecionar a coluna-chave na qual deseja agrupar.
6. Clique OK, agora o resultado é mostrado como abaixo:
Agrupe e concatene com linhas combinadas avançadas
Aqui está um utilitário em Kutools for Excel, Advanced Combine Linhas, que pode combinar linhas ou fazer cálculos com base em uma coluna-chave no Excel.
| Kutools for Excel, com mais de 300 funções úteis, tornam seus trabalhos mais fáceis. |
Depois de instalar Kutools para Excel, faça o seguinte:（Baixe gratuitamente o Kutools para Excel agora!)
1. Selecione o intervalo de dados que você usa e clique em Kutools > Unir e dividir > Combinar linhas avançadas.
2. No Advanced Combine Rows janela, escolha a coluna na qual deseja combinar as linhas e clique em Primary Key para defini-la como coluna-chave.
3. Selecione a coluna que você precisa combinar, clique Combinee escolha um delimitador usado para separar o conteúdo combinado.
 |
 |
 |
4. Clique Ok. O resultado é mostrado assim:
Note: Antes de aplicar o utilitário, é melhor você ter uma cópia dos dados originais.
Demo
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!
