Jak transponować komórki w jednej kolumnie na podstawie unikatowych wartości w innej kolumnie?
Przypuśćmy, że masz zakres danych zawierający dwie kolumny, teraz chcesz transponować komórki w jednej kolumnie do poziomych wierszy na podstawie unikalnych wartości w innej kolumnie, aby uzyskać następujący wynik. Czy masz jakieś dobre pomysły na rozwiązanie tego problemu w programie Excel?
Transponuj komórki w jednej kolumnie na podstawie unikatowych wartości za pomocą formuł
Transponuj komórki w jednej kolumnie na podstawie unikalnych wartości z kodem VBA
Transponuj komórki w jednej kolumnie na podstawie unikalnych wartości za pomocą Kutools for Excel
Transponuj komórki w jednej kolumnie na podstawie unikatowych wartości za pomocą formuł
Za pomocą poniższych formuł tablicowych możesz wyodrębnić unikalne wartości i transponować odpowiadające im dane do poziomych wierszy, wykonaj następujące czynności:
1. Wprowadź tę formułę tablicową: = INDEKS (A $ 2: $ 16 $, PODAJ.POZYCJĘ (0, LICZ.JEŻELI (1 $ D1: $ 2 $: $ 16 $), 0)) do pustej komórki, na przykład D2, i naciśnij Shift + Ctrl + Enter klucze razem, aby uzyskać poprawny wynik, patrz zrzut ekranu:
Note: W powyższym wzorze, A2: A16 to kolumna, z której chcesz wyświetlić unikatowe wartości, i D1 to komórka powyżej tej komórki z formułą.
2. Następnie przeciągnij uchwyt wypełniania w dół do komórek, aby wyodrębnić wszystkie unikalne wartości, patrz zrzut ekranu:
3. A następnie wprowadź tę formułę do komórki 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 pamiętaj, aby nacisnąć Shift + Ctrl + Enter klucze, aby uzyskać wynik, patrz zrzut ekranu:
Note: W powyższym wzorze: B2: B16 to dane z kolumny, które chcesz przetransponować, A2: A16 to kolumna, na podstawie której chcesz transponować wartości, i D2 zawiera unikatową wartość wyodrębnioną w kroku 1.
4. Następnie przeciągnij uchwyt wypełniania na prawo od komórek, które chcesz wyświetlić transponowane dane, aż wyświetli się 0, patrz zrzut ekranu:
5. Następnie kontynuuj przeciąganie uchwytu wypełniania w dół do zakresu komórek, aby uzyskać transponowane dane, jak pokazano na poniższym zrzucie ekranu:
Transponuj komórki w jednej kolumnie na podstawie unikalnych wartości z kodem VBA
Być może formuły są skomplikowane do zrozumienia, tutaj możesz uruchomić następujący kod VBA, aby uzyskać pożądany wynik.
1. Przytrzymaj ALT + F11 klawisze, aby otworzyć Microsoft Visual Basic for Applications okno.
2. Kliknij wstawka > Modułi wklej następujący kod w Moduł Okno.
Kod VBA: Transponuj komórki w jednej kolumnie na podstawie unikalnych wartości w innej kolumnie:
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. Następnie naciśnij F5 klucz do uruchomienia tego kodu, a pojawi się okno zachęty przypominające o wyborze zakresu danych, którego chcesz użyć, patrz zrzut ekranu:
4. A następnie kliknij OK przycisk, pojawi się kolejne okno zachęty przypominające o wybraniu komórki do umieszczenia wyniku, patrz zrzut ekranu:
6. Kliknij OK przycisk, a dane w kolumnie B zostały transponowane na podstawie unikalnych wartości w kolumnie A, patrz zrzut ekranu:
Transponuj komórki w jednej kolumnie na podstawie unikalnych wartości za pomocą Kutools for Excel
Jeśli Kutools dla programu Excel, łącząc Zaawansowane wiersze łączenia i Podział komórki narzędzi, możesz szybko zakończyć to zadanie bez żadnych formuł ani kodu.
| Kutools dla programu Excel : z ponad 300 poręcznymi dodatkami Excela, które można wypróbować bez ograniczeń w ciągu 30 dni. |
Po zainstalowaniu Kutools dla programu Excelwykonaj następujące czynności:
1. Wybierz zakres danych, którego chcesz użyć. (Jeśli chcesz zachować oryginalne dane, najpierw skopiuj i wklej dane w innej lokalizacji).
2. Następnie kliknij Kutools > Połącz i podziel > Zaawansowane wiersze łączeniazobacz zrzut ekranu:
3, w Połącz wiersze na podstawie kolumny w oknie dialogowym, wykonaj następujące operacje:
(1.) Kliknij nazwę kolumny, na podstawie której chcesz transponować dane, i wybierz Główny klucz;
(2.) Kliknij inną kolumnę, którą chcesz transponować, i kliknij Połączyć następnie wybierz jeden separator, aby oddzielić połączone dane, na przykład spację, przecinek, średnik.
4. Następnie kliknij Ok przycisk, dane w kolumnie B zostały połączone w jedną komórkę na podstawie kolumny A, patrz zrzut ekranu:
5. Następnie wybierz połączone komórki i kliknij Kutools > Połącz i podziel > Podział komórkizobacz zrzut ekranu:
6, w Podział komórki okno dialogowe, wybierz Podziel na kolumny pod Rodzaj Nieruchomości opcja, a następnie wybierz separator oddzielający połączone dane, patrz zrzut ekranu:
7. Następnie kliknij Ok i wybierz komórkę, aby umieścić wynik podziału w wyskakującym oknie dialogowym, zobacz zrzut ekranu:
8. Kliknij OK, a otrzymasz taki wynik, jakiego potrzebujesz. Zobacz zrzut ekranu:
Pobierz i bezpłatną wersję próbną Kutools dla programu Excel teraz!
Demo: Transponuj komórki w jednej kolumnie na podstawie unikalnych wartości za pomocą Kutools for Excel
Najlepsze narzędzia biurowe
Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas. Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...
Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
