Jak transponovat buňky v jednom sloupci na základě jedinečných hodnot v jiném sloupci?
Předpokládejme, že máte rozsah dat, který obsahuje dva sloupce, nyní chcete transponovat buňky v jednom sloupci do vodorovných řádků na základě jedinečných hodnot v jiném sloupci, abyste získali následující výsledek. Máte nějaké dobré nápady, jak tento problém vyřešit v aplikaci Excel?
Transponujte buňky v jednom sloupci na základě jedinečných hodnot pomocí vzorců
Transponujte buňky v jednom sloupci na základě jedinečných hodnot pomocí kódu VBA
Transponujte buňky v jednom sloupci na základě jedinečných hodnot pomocí Kutools pro Excel
Transponujte buňky v jednom sloupci na základě jedinečných hodnot pomocí vzorců
Pomocí následujících vzorců pole můžete extrahovat jedinečné hodnoty a transponovat jejich odpovídající data do vodorovných řádků, postupujte takto:
1. Zadejte tento vzorec pole: = INDEX ($ A $ 2: $ A $ 16, MATCH (0, COUNTIF ($ D $ 1: $ D1, $ A $ 2: $ A $ 16), 0)) do prázdné buňky, například D2, a stiskněte Shift + Ctrl + Enter společně získáte správný výsledek, viz screenshot:
Poznámka: Ve výše uvedeném vzorci, A2: A16 je sloupec, ze kterého chcete vypsat jedinečné hodnoty, a D1 je buňka nad touto buňkou vzorce.
2. Poté přetáhněte úchyt výplně dolů do buněk a extrahujte všechny jedinečné hodnoty, viz screenshot:
3. A pak pokračujte v zadávání tohoto vzorce do buňky 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)a nezapomeňte stisknout Shift + Ctrl + Enter klávesy pro získání výsledku, viz screenshot:
Poznámka: Ve výše uvedeném vzorci: B2: B16 jsou data sloupce, která chcete transponovat, A2: A16 je sloupec, na kterém chcete transponovat hodnoty založené na, a D2 obsahuje jedinečnou hodnotu, kterou jste extrahovali v kroku 1.
4. Poté přetáhněte úchyt výplně napravo od buněk, u kterých chcete zobrazit transponovaná data, dokud se nezobrazí 0, viz screenshot:
5. A pak pokračujte v přetahování popisovače výplně dolů do rozsahu buněk, abyste získali transponovaná data, jak ukazuje následující snímek obrazovky:
Transponujte buňky v jednom sloupci na základě jedinečných hodnot pomocí kódu VBA
Může to být tím, že vzorce jsou složité, abyste pochopili, zde můžete spustit následující kód VBA a získat požadovaný výsledek, který potřebujete.
1. Podržte ALT + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.
2, klikněte Vložit > Modula vložte následující kód do Modul Okno.
Kód VBA: Transponujte buňky v jednom sloupci na základě jedinečných hodnot v jiném sloupci:
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. Pak stiskněte tlačítko F5 klíč ke spuštění tohoto kódu a zobrazí se okno s výzvou, které vám připomene výběr rozsahu dat, který chcete použít, viz screenshot:
4. A pak klikněte na tlačítko OK tlačítko, vyskočí další okno s výzvou, které vám připomene, abyste vybrali buňku, která má dát výsledek, viz screenshot:
6, klikněte OK tlačítko a data ve sloupci B byla provedena na základě jedinečných hodnot ve sloupci A, viz screenshot:
Transponujte buňky v jednom sloupci na základě jedinečných hodnot pomocí Kutools pro Excel
Pokud máte Kutools pro Excel, kombinace Pokročilé kombinování řádků a Rozdělit buňky nástroje, můžete tento úkol rychle dokončit bez jakýchkoli vzorců nebo kódu.
| Kutools pro Excel : s více než 300 praktickými doplňky Excel, můžete si je vyzkoušet bez omezení do 30 dnů. |
Po instalaci Kutools pro Excel, postupujte následovně:
1. Vyberte rozsah dat, který chcete použít. (Pokud si chcete uchovat původní data, nejprve je zkopírujte a vložte do jiného umístění.)
2. Pak klikněte na tlačítko Kutools > Sloučit a rozdělit > Pokročilé kombinování řádků, viz screenshot:
3. V Kombinujte řádky na základě sloupce V dialogovém okně proveďte následující operace:
(1.) Klikněte na název sloupce, podle kterého chcete data transponovat, a vyberte Primární klíč;
(2.) Klikněte na jiný sloupec, který chcete transponovat, a klikněte na Kombinovat pak vyberte jeden oddělovač k oddělení kombinovaných dat, jako je mezera, čárka, středník.
4. Pak klikněte na tlačítko Ok tlačítko, data ve sloupci B byla kombinována společně v jedné buňce na základě sloupce A, viz screenshot:
5. Potom vyberte spojené buňky a klikněte Kutools > Sloučit a rozdělit > Rozdělit buňky, viz screenshot:
6. V Rozdělit buňky dialogové okno vyberte Rozdělit na sloupce pod Styl možnost a poté vyberte oddělovač, který odděluje vaše kombinovaná data, viz screenshot:
7. Pak klikněte na tlačítko Ok Tlačítko a vyberte buňku, do které chcete rozdělený výsledek umístit do vyskakovacího dialogového okna, viz screenshot:
8, klikněte OK, a získáte výsledek, jak potřebujete. Viz screenshot:
Stáhněte si zdarma zkušební verzi Kutools pro Excel!
Ukázka: Transponujte buňky v jednom sloupci na základě jedinečných hodnot pomocí Kutools pro Excel
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
