Cum se concatenează rândurile într-o singură celulă pe baza grupului în Excel?
Iată o gamă de două coloane, una este lista de clase, iar cealaltă este lista cu numele studenților. După cum vedeți, unii elevi fac parte din aceeași clasă, alții nu. Acum vreau să concatenez elevii din aceeași clasă într-o singură celulă ca în imaginea de mai jos, cum o pot rezolva rapid în Excel?
Grupați și concatenați cu formule și funcția de filtrare
Grupați și concatenați cu codul VBA
Grupați și concatenați cu rânduri avansate de combinare
Grupați și concatenați cu formule și funcția de filtrare
În Excel, puteți aplica formule rândurilor concatenate pe baza unei coloane, apoi utilizați funcția Filtru pentru a afișa numai rezultatul.
notițe: Trebuie să vă sortați datele după clasă înainte de a urma pașii.
1. Într-o celulă goală de lângă intervalul de date, de exemplu, C13, tastați această formulă =IF(A13=A12,C12&", "&B13,B13), presa Enter tasta și completați formula până la celule cu glisarea mânerului de umplere.
În formulă, A13 este prima dată din coloana „Clasă”, B13 este prima dată din coloana „Nume”, „,” este separatorul pentru a delimita conținutul concatenat.
2. Apoi, în coloana următoare, D13, tastați această formulă =IF(A13<>A14,"Last","") și trageți mânerul de umplere în jos pentru a aplica formula celulelor de care aveți nevoie.
3. Acum selectați tot intervalul de date, inclusiv formule și faceți clic Date > Filter pentru a adăuga Filter icons la date.
4. Faceți clic pe Filter icon în ultimul antet al formulei, verificați Last bifați numai din lista derulantă și faceți clic pe OK.
Acum rezultatul este prezentat mai jos, puteți elimina ultima coloană de asistență dacă nu aveți nevoie de ea vreodată.
Grupați și concatenați cu codul VBA
Iată un cod VBA care poate gestiona și acest job.
1. presa Alt + F11 tastele pentru a activa Microsoft Visual Basic for Applications fereastră.
2. Apoi, în fereastră, faceți clic pe Tools > References pentru a permite References dialog și bifați Microsoft Scripting Runtime. Vedeți captura de ecran:
3. clic OK, și faceți clic pe Insert > Module în fereastra VBA și copiați și lipiți sub codul VBA în Module scenariu. Vedeți captura de ecran:
VBA: concatenează rândurile într-o singură celulă pe baza grupului
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. presa F5 și selectați intervalul de date pe care îl utilizați în caseta de dialog popping.
5. clic OK pentru a selecta coloana cheie pe care doriți să o grupați pe baza.
6. clic OK, acum rezultatul este prezentat după cum urmează:
Grupați și concatenați cu rânduri avansate de combinare
Iată un utilitar în Kutools pentru Excel, Advanced Combine Rânduri, care poate combina rânduri sau face calcule pe baza unei coloane cheie din Excel.
| Kutools pentru Excel, cu mai mult de 300 funcții la îndemână, vă face munca mai ușoară. |
După instalare Kutools pentru Excel, faceți următoarele:（Descărcați gratuit Kutools pentru Excel acum!)
1. Selectați intervalul de date pe care îl utilizați și faceți clic Kutools > Merge & Split > Rânduri combinate avansate.
2. În Advanced Combine Rows fereastra, alegeți coloana pe care doriți să combinați rândurile pe baza și faceți clic pe Primary Key pentru a o seta ca coloană cheie.
3. Selectați coloana pe care trebuie să o combinați, faceți clic pe Combineși alegeți un delimitator pe care îl utilizați pentru a separa conținutul combinat.
 |
 |
 |
4. clic Ok. Rezultatul este prezentat astfel:
notițe: Înainte de a aplica utilitarul, ar fi bine să aveți o copie a datelor originale.
Demo
Cele mai bune instrumente de productivitate de birou
Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp. Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...
Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară
- Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
- Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ferestrele noi.
- Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
