Hogyan lehet egy oszlop celláit átültetni egy másik oszlop egyedi értékei alapján?
Tegyük fel, hogy van egy adattartománya, amely két oszlopot tartalmaz. Most az egyik oszlop celláit át szeretné helyezni egy másik oszlop egyedi értékei alapján vízszintes sorokba, hogy a következő eredményt kapja. Van valami jó ötlete a probléma megoldására az Excel programban?
Transzponálja a cellákat egy oszlopba egyedi értékek és képletek alapján
Transzponálja a cellákat egy oszlopba, egyedi értékek alapján, VBA kóddal
A Kutools for Excel segítségével egyedi értékek alapján egyetlen oszlopba helyezze át a cellákat
Transzponálja a cellákat egy oszlopba egyedi értékek és képletek alapján
A következő tömbképletekkel kivonhatja az egyedi értékeket és átültetheti azok megfelelő adatait vízszintes sorokba, kérjük, tegye a következőket:
1. Írja be ezt a tömbképletet: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) egy üres cellába, például D2, és nyomja meg a gombot Shift + Ctrl + Enter gombokat együtt a helyes eredmény eléréséhez, lásd a képernyőképet
Megjegyzések: A fenti képletben A2: A16 az az oszlop, amelyből fel akarja sorolni az egyedi értékeket, és D1 az e képlet fölötti cella.
2. Ezután húzza le a kitöltő fogantyút a cellákra az összes egyedi érték kibontásához, lásd a képernyőképet:
3. Ezután adja meg ezt a képletet az E2 cellába: =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), és ne felejtsen el megnyomni Shift + Ctrl + Enter gombokat az eredmény eléréséhez, lásd a képernyőképet:
Megjegyzések: A fenti képletben: B2: B16 az átültetni kívánt oszlopadatok, A2: A16 az az oszlop, amelyen át szeretné ültetni az értékeket, és D2 tartalmazza az 1. lépésben kivont egyedi értéket.
4. Ezután húzza a kitöltő fogantyút jobbra azon celláktól, amelyekbe fel szeretné sorolni az átültetett adatokat, amíg a 0 meg nem jelenik, lásd a képernyőképet:
5. Ezután folytassa a kitöltő fogantyú lefelé húzását a cellák tartományába az átültetett adatok megszerzéséhez, az alábbi képernyőkép szerint:
Transzponálja a cellákat egy oszlopba, egyedi értékek alapján, VBA kóddal
Lehet, hogy a képletek összetettek, hogy megértsék, itt futtathatja a következő VBA kódot a kívánt eredmény eléréséhez.
1. Tartsa lenyomva a ALT + F11 billentyűk megnyitásához Microsoft Visual Basic for Applications ablak.
2. Kattints betétlap > Modulok, és illessze be a következő kódot a Modulok Ablak.
VBA kód: Transzponálja a cellákat egy oszlopba egy másik oszlop egyedi értékei alapján:
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. Ezután nyomja meg a gombot F5 kulcs a kód futtatásához, és megjelenik egy prompt doboz, amely emlékezteti Önt a használni kívánt adattartomány kiválasztására, lásd a képernyőképet:
4. Kattintson a gombra OK gombra, egy újabb prompt doboz jelenik meg, hogy emlékeztesse Önt, hogy válasszon egy cellát az eredmény megadásához, lásd a képernyőképet:
6. Kattints OK gombra, és a B oszlop adatait átültettük az A oszlop egyedi értékei alapján, lásd a képernyőképet:
A Kutools for Excel segítségével egyedi értékek alapján egyetlen oszlopba helyezze át a cellákat
Ha van Kutools az Excel számára, kombinálva a Haladó kombinált sorok és a Hasított sejtek segédprogramok segítségével, gyorsan elvégezheti ezt a feladatot képletek és kód nélkül.
| Kutools az Excel számára : több mint 300 praktikus Excel-bővítménnyel, ingyenesen, korlátozás nélkül, 30 nap alatt kipróbálható. |
Telepítése után Kutools az Excel számára, kérjük, tegye a következőket:
1. Válassza ki a használni kívánt adattartományt. (Ha meg akarja őrizni az eredeti adatokat, akkor először másolja és illessze be az adatokat egy másik helyre.)
2. Ezután kattintson Kutools > Egyesítés és felosztás > Haladó kombinált sorok, lásd a képernyőképet:
3. Az Kombinálja a sorokat az oszlop alapján párbeszédpanelen tegye a következőket:
(1.) Kattintson arra az oszlopnévre, amely alapján adatokat szeretne átültetni, majd válassza a lehetőséget Elsődleges kulcs;
(2.) Kattintson egy másik oszlopra, amelyet át szeretne ültetni, majd kattintson a gombra Kombájn majd válasszon egy elválasztót az egyesített adatok, például szóköz, vessző, pontosvessző elválasztásához.
4. Ezután kattintson Ok gombra, a B oszlop adatait egy cellába egyesítettük az A oszlop alapján, lásd a képernyőképet:
5. Ezután válassza ki az egyesített cellákat, majd kattintson Kutools > Egyesítés és felosztás > Hasított sejtek, lásd a képernyőképet:
6. Az Hasított sejtek párbeszédpanelen válassza ki Oszlopokra osztás alatt a típus opciót, majd válassza ki az elválasztót, amely elválasztja a kombinált adatokat, lásd a képernyőképet:
7. Ezután kattintson Ok gombra, és válasszon egy cellát az osztott eredmény elhelyezéséhez a felbukkanó párbeszédpanelen, lásd a képernyőképet:
8. Kattints OK, és megkapja az eredményt, amire szüksége van. Lásd a képernyőképet:
Töltse le és ingyenes próbaverziója a Kutools for Excel programhoz most!
Demo: A Kutools for Excel segítségével egyetlen oszlopba helyezze át a cellákat egyedi értékek alapján
A legjobb irodai hatékonyságnövelő eszközök
Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz. Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...
Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját
- Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
- Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
- 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
