Transpozycja danych oznacza zmianę orientacji tablicy poprzez konwersję jej wierszy na kolumny lub odwrotnie. W tym samouczku podzielimy się z Tobą pięcioma różnymi metodami zmiany orientacji tablicy i osiągnięcia pożądanego rezultatu.

Przełącz kolumny na wiersze za pomocą Wklej specjalnie

Transponuj i łącz dane ze źródłem

• Z funkcją TRANSPONUJ
• Z funkcjami POŚR, ADRES, KOLUMNA i WIERSZ
• Za pomocą opcji Wklej specjalnie i Znajdź i zamień
• Z Power Query

Pobierz przykłady danych transpozycji (.xlsx)

Wideo: transponowanie danych w programie Excel

Przełącz kolumny na wiersze za pomocą Wklej specjalnie

Krok 1: Skopiuj zakres do transpozycji

Zaznacz zakres komórek, w których chcesz przełączyć wiersze i kolumny, a następnie naciśnij Ctrl + C skopiować zakres.

Krok 2: Wybierz opcję Wklej transpozycję

Kliknij prawym przyciskiem myszy pierwszą komórkę zakresu docelowego, a następnie kliknij przycisk Transponować ikona (Pod Opcje wklejania) z menu po kliknięciu prawym przyciskiem myszy.

Wynik

Voila! Zakres jest transponowany w mgnieniu oka!

Transponuj i łącz dane ze źródłem

Aby dynamicznie zmienić orientację zakresu (przełączyć kolumny na wiersze i odwrotnie) i połączyć przełączony wynik z oryginalnym zbiorem danych, możesz użyć dowolnego z poniższych podejść:

• Funkcja TRANSPOSE (Łatwy w użyciu, ale wyników nie można edytować)
• Funkcje POŚREDNIE, ADRESOWE, KOLUMNOWE i WIERSZOWE (Duża formuła, ale umożliwia wprowadzanie zmian w wynikach)
• Wklej specjalnie i znajdź i zamień (Skomplikowane, ale łatwe do zrozumienia)
• Power Query (Łatwe z wynikami sformatowanymi w tabeli)

Zamień wiersze na kolumny za pomocą funkcji TRANSPONUJ

Aby zamienić wiersze na kolumny i odwrotnie za pomocą TRANSPONOWAĆ funkcji, wykonaj następujące czynności.

Krok 1: Wybierz tę samą liczbę pustych komórek, co oryginalny zestaw komórek, ale w innym kierunku

Wskazówka: Pomiń ten krok, jeśli używasz programu Excel 365 lub Excel 2021.

Załóżmy, że oryginalna tabela jest w zasięgu A1: C4, co oznacza, że ​​tabela ma 4 wiersze i 3 kolumny. Tak więc transponowana tabela będzie miała 3 wiersze i 4 kolumny. Oznacza to, że należy wybrać 3 wiersze i 4 kolumny pustych komórek.

Krok 2: Wprowadź formułę TRANSPONUJ

Wpisz poniższą formułę na pasku formuły, a następnie naciśnij Ctrl + Shift + Enter aby uzyskać wynik.

Wskazówka: Naciśnij przycisk Wchodzę jeśli jesteś użytkownikiem programu Excel 365 lub Excel 2021.

=TRANSPOSE(A1:C4)

Wynik

Wiersze są zamieniane na kolumny, a kolumny na wiersze.

Obracaj dane za pomocą funkcji POŚR, ADRES, KOLUMNA i WIERSZ

Chociaż powyższa formuła jest dość łatwa do zrozumienia i użycia, jej wadą jest to, że nie można edytować ani usuwać żadnych komórek w obróconej tabeli. Przedstawię więc formułę za pomocą POŚREDNI, ADRES, KOLUMNA i RZĄD Funkcje. Załóżmy, że oryginalna tabela jest w zasięgu A1: C4, aby wykonać przekształcenie kolumna-wiersz i zachować połączenie obróconych danych ze źródłowym zbiorem danych, wykonaj poniższe czynności.

Krok 1: Wprowadź formułę

Wprowadź poniższą formułę w lewej górnej komórce zakresu docelowego (A6 w naszym przypadku) i naciśnij Wchodzę:

=INDIRECT(ADDRESS(COLUMN(A1)-COLUMN($A$1)+ROW($A$1),ROW(A1)-ROW($A$1)+COLUMN($A$1)))

Krok 2: Skopiuj formułę do prawej i poniżej komórek

Zaznacz komórkę formuły i przeciągnij jej uchwyt wypełniania (mały zielony kwadrat w prawym dolnym rogu komórki) w dół, a następnie w prawo, do wymaganej liczby wierszy i kolumn.

Wynik

Kolumny i wiersze są natychmiast przełączane.

Konwertuj kolumny na wiersze za pomocą funkcji Wklej specjalnie i Znajdź i zamień

Kilka dodatkowych kroków z metodą Wklej specjalnie wraz z funkcją Znajdź i zamień pozwala na transpozycję danych podczas łączenia komórek źródłowych z komórkami transponowanymi.

Krok 1: Skopiuj zakres do transpozycji

Zaznacz zakres komórek, które chcesz transponować, a następnie naciśnij Ctrl + C skopiować zakres.

Krok 2: Zastosuj opcję Wklej łącze

1. Kliknij prawym przyciskiem myszy pustą komórkę, a następnie kliknij wklej specjalnie.

   

2. Kliknij na wklej link.

   

Otrzymasz wynik, jak pokazano poniżej:

Krok 3: Znajdź i zamień znaki równości (=) z wyniku wklejenia łącza

1. Wybierz zakres wyników (A6: C9) i naciśnij Ctrl + H, a następnie wymień = w @EO (lub dowolne znaki, które nie istnieją w wybranym zakresie) w pliku Znajdowanie i zamienianie dialog.

   

2. Kliknij na Zamień wszystko, a następnie zamknij okno dialogowe. Poniżej pokazano, jak będą wyglądać dane.

   

Krok 4: Transponuj zastąpiony wynik wklejenia łącza

Wybierz zakres (A6: C9) i naciśnij Ctrl + C aby to skopiować. Kliknij prawym przyciskiem myszy pustą komórkę (tutaj wybrałem A11) i wybierz plik Transponować ikona od Opcje wklejania , aby wkleić transponowany wynik.

Krok 5: Uzyskaj znaki równości (=), aby połączyć transponowany wynik z oryginalnymi danymi

1. Zachowaj transponowane dane wynikowe A11: D13 wybrany, a następnie naciśnij Ctrl + Hi wymień @EO w = (przeciwieństwo krok 3).

   

2. Kliknij na Zamień wszystko, a następnie zamknij okno dialogowe.

Wynik

Dane są transponowane i łączone z oryginalnymi komórkami.

Transponuj i łącz dane ze źródłem Power Query

Power Query to potężne narzędzie do automatyzacji danych, które pozwala łatwo transponować dane w programie Excel. Możesz wykonać zadanie, wykonując poniższe czynności:

Krok 1: Wybierz zakres do transpozycji i otwórz Power Query redaktor

Wybierz zakres danych do transpozycji. A potem na Dane zakładka, w Pobierz i przekształć dane grupa, kliknij Z tabeli/zakresu.

Krok 2: Konwertuj kolumny na wiersze za pomocą Power Query

1. Idź do Przekształcać patka. w Użyj pierwszego wiersza jako nagłówków z menu rozwijanego wybierz Użyj nagłówków jako pierwszego wiersza.

   

2. Kliknij na Transponować.

   

Krok 3: Zapisz transponowane dane w arkuszu

Na filet tab, kliknij Zamknij i wczytaj aby zamknąć okno Power Editor i utworzyć nowy arkusz roboczy do załadowania transponowanych danych.

Wynik

Transponowane dane są konwertowane do tabeli w nowo utworzonym arkuszu.

Powiązane artykuły

• Jak szybko transponować zakresy i pomijać puste komórki w programie Excel?
• Gdy wkleimy transponowany zakres, puste komórki również zostaną wklejone. Czasami jednak chcemy po prostu transponować zakresy, ignorując puste komórki, jak pokazano na poniższym zrzucie ekranu. Czy są jakieś sposoby na szybką transpozycję zakresów i pomijanie spacji w programie Excel?
• Jak przetransponować co 5 lub n wierszy z jednej kolumny do wielu kolumn?
• Przypuśćmy, że masz długie dane w kolumnie A, a teraz chcesz transponować co 5 wierszy z kolumny A do wielu kolumn, na przykład transponować A1: A5 do C6: G6, A6: A10 do C7: G7 itd. następujący zrzut ekranu. Jak poradzić sobie z tym zadaniem bez wielokrotnego kopiowania i wklejania w programie Excel?
• Jak przetransponować / przekonwertować kolumny i wiersze na jedną kolumnę?
• Podczas korzystania z arkusza programu Excel czasami napotykasz ten problem: jak przekonwertować lub transponować zakres danych do jednej kolumny? (Zobacz poniższe zrzuty ekranu :) Teraz przedstawiam trzy szybkie sztuczki, aby rozwiązać ten problem.
• Jak przetransponować / przekonwertować kolumny i wiersze na jeden wiersz?
• Jak połączyć wiele wierszy i kolumn w jeden długi wiersz? Być może wydaje ci się to łatwe, ponieważ możesz kopiować je jeden po drugim i ręcznie łączyć w rząd. Jednak może to być czasochłonne i żmudne, jeśli istnieją setki wierszy i kolumn. Tutaj omówię kilka szybkich sztuczek, aby go rozwiązać.