Cum se transpun celulele într-o coloană pe baza valorilor unice dintr-o altă coloană?
Presupunând că aveți o serie de date care conține două coloane, acum doriți să transpuneți celulele într-o coloană pe rânduri orizontale pe baza valorilor unice dintr-o altă coloană pentru a obține următorul rezultat. Aveți idei bune pentru a rezolva această problemă în Excel?
Transpuneți celulele într-o singură coloană pe baza valorilor unice cu formule
Transpuneți celulele într-o singură coloană pe baza valorilor unice cu codul VBA
Transpuneți celulele într-o singură coloană pe baza valorilor unice cu Kutools pentru Excel
Transpuneți celulele într-o singură coloană pe baza valorilor unice cu formule
Cu următoarele formule de matrice, puteți extrage valorile unice și transpune datele lor corespunzătoare în rânduri orizontale, procedați după cum urmează:
1. Introduceți această formulă matrice: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) într-o celulă goală, D2, de exemplu, și apăsați Shift + Ctrl + Enter tastele împreună pentru a obține rezultatul corect, consultați captura de ecran:
notițe: În formula de mai sus, A2: A16 este coloana din care doriți să enumerați valorile unice și D1 este celula de deasupra acestei celule formula.
2. Apoi trageți mânerul de umplere în jos în celule pentru a extrage toate valorile unice, vedeți captura de ecran:
3. Și apoi continuați să introduceți această formulă în celula E2: =IFERROR(INDEX($B$2:$B$16, MATCH(0, COUNTIF($D2:D2,$B$2:$B$16)+IF($A$2:$A$16<>$D2, 1, 0), 0)), 0), și nu uitați să apăsați Shift + Ctrl + Enter tastele pentru a obține rezultatul, vezi captura de ecran:
notițe: În formula de mai sus: B2: B16 sunt datele coloanei pe care doriți să le transpuneți, A2: A16 este coloana pe care doriți să transpuneți valorile pe baza și D2 conține valoarea unică pe care ați extras-o la pasul 1.
4. Apoi trageți mânerul de umplere la dreapta celulelor pe care doriți să le afișați datele transpuse până când se afișează 0, vedeți captura de ecran:
5. Și apoi continuați să trageți mânerul de umplere în jos în intervalul de celule pentru a obține datele transpuse după cum se arată în următoarea captură de ecran:
Transpuneți celulele într-o singură coloană pe baza valorilor unice cu codul VBA
Poate că formulele sunt complexe pentru a înțelege, aici puteți rula următorul cod VBA pentru a obține rezultatul dorit de care aveți nevoie.
1. Țineți apăsat butonul ALT + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
2. Clic Insera > Moduleși lipiți următorul cod în Module Fereastră.
Cod VBA: transpuneți celulele într-o coloană pe baza valorilor unice dintr-o altă coloană:
Sub transposeunique()
'updateby Extendoffice
Dim xLRow As Long
Dim i As Long
Dim xCrit As String
Dim xCol As New Collection
Dim xRg As Range
Dim xOutRg As Range
Dim xTxt As String
Dim xCount As Long
Dim xVRg As Range
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("please select data range(only two columns):", "Kutools for Excel", xTxt, , , , , 8)
Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
If xRg Is Nothing Then Exit Sub
If (xRg.Columns.Count <> 2) Or _
(xRg.Areas.Count > 1) Then
MsgBox "the used range is only one area with two columns ", , "Kutools for Excel"
Exit Sub
End If
Set xOutRg = Application.InputBox("please select output range(specify one cell):", "Kutools for Excel", xTxt, , , , , 8)
If xOutRg Is Nothing Then Exit Sub
Set xOutRg = xOutRg.Range(1)
xLRow = xRg.Rows.Count
For i = 2 To xLRow
xCol.Add xRg.Cells(i, 1).Value, xRg.Cells(i, 1).Value
Next
Application.ScreenUpdating = False
For i = 1 To xCol.Count
xCrit = xCol.Item(i)
xOutRg.Offset(i, 0) = xCrit
xRg.AutoFilter Field:=1, Criteria1:=xCrit
Set xVRg = xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible)
If xVRg.Count > xCount Then xCount = xVRg.Count
xRg.Range("B2:B" & xLRow).SpecialCells(xlCellTypeVisible).Copy
xOutRg.Offset(i, 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
Next
xOutRg = xRg.Cells(1, 1)
xOutRg.Offset(0, 1).Resize(1, xCount) = xRg.Cells(1, 2)
xRg.Rows(1).Copy
xOutRg.Resize(1, xCount + 1).PasteSpecial Paste:=xlPasteFormats
xRg.AutoFilter
Application.ScreenUpdating = True
End Sub
3. Apoi apăsați F5 pentru a rula acest cod și va apărea o casetă de prompt pentru a vă reaminti să selectați intervalul de date pe care doriți să îl utilizați, consultați captura de ecran:
4. Apoi faceți clic pe OK buton, o altă casetă de prompt va apărea pentru a vă reaminti să selectați o celulă pentru a pune rezultatul, vedeți captura de ecran:
6. Clic OK , iar datele din coloana B au fost transpuse pe baza valorilor unice din coloana A, consultați captura de ecran:
Transpuneți celulele într-o singură coloană pe baza valorilor unice cu Kutools pentru Excel
Dacă aveţi Kutools pentru Excel, combinând Rânduri combinate avansate și Celule divizate utilitare, puteți finaliza rapid această sarcină fără formule sau cod.
Kutools pentru Excel : cu mai mult de 300 de programe de completare Excel la îndemână, gratuit pentru a încerca fără limitări în 30 de zile. |
După instalare Kutools pentru Excel, vă rugăm să faceți următoarele:
1. Selectați intervalul de date pe care doriți să îl utilizați. (Dacă doriți să păstrați datele originale, vă rugăm să copiați și să lipiți datele într-o altă locație mai întâi.)
2. Apoi apasa Kutools > Merge & Split > Rânduri combinate avansate, vezi captura de ecran:
3. În Combinați rândurile pe baza coloanei caseta de dialog, vă rugăm să efectuați următoarele operații:
(1.) Faceți clic pe numele coloanei pe care doriți să transpuneți datele și selectați Cheia principala;
(2.) Faceți clic pe o altă coloană pe care doriți să o transpuneți și faceți clic pe Combina apoi alegeți un separator pentru a separa datele combinate, cum ar fi spațiu, virgulă, punct și virgulă.
4. Apoi apasa Ok butonul, datele din coloana B au fost combinate împreună într-o singură celulă pe baza coloanei A, vezi captura de ecran:
5. Și apoi selectați celulele combinate și faceți clic Kutools > Merge & Split > Celule divizate, vezi captura de ecran:
6. În Celule divizate fereastră de dialog, selectați Împărțiți în coloane în temeiul Tip , apoi alegeți separatorul care separă datele combinate, vedeți captura de ecran:
7. Apoi apasa Ok și selectați o celulă pentru a pune rezultatul împărțit în caseta de dialog pop-out, consultați captura de ecran:
8. Clic OK, și veți obține rezultatul după cum aveți nevoie. Vedeți captura de ecran:
Descărcați și proba gratuită Kutools pentru Excel acum!
Demo: transpuneți celulele într-o singură coloană pe baza valorilor unice cu Kutools pentru Excel
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!