Jak szybko transponować dane w Excelu (krok po kroku)

Zobacz film – Jak transponować dane w Excelu

Jeśli masz zbiór danych i chcesz je transponować w Excelu (co oznacza zamianę wierszy na kolumny i kolumn na wiersze), robienie tego ręcznie jest kompletnie NIE!

Transpozycja danych za pomocą Paste Special

Paste Special może zrobić wiele niesamowitych rzeczy, a jedną z takich rzeczy jest transpozycja danych w Excelu.

Załóżmy, że masz zbiór danych, jak pokazano poniżej:

Dane te mają regiony w kolumnie i kwartały w wierszu.

Teraz, z jakiegoś powodu, jeśli musisz transponować te dane, oto jak możesz to zrobić za pomocą wklejania specjalnego:

• Wybierz zestaw danych (w tym przypadku A1:E5).
• Kopiuj zbiór danych (Control + C) lub kliknij prawym przyciskiem myszy i wybierz kopiuj.
• Teraz możesz wkleić transponowane dane w nowym miejscu. W tym przykładzie chcę skopiować dane z komórki G1:K5, więc kliknij prawym przyciskiem myszy na komórkę G1 i wybierz polecenie Wklej Specjalnie.
• W oknie dialogowym Wklej Specjalnie zaznacz opcję transpozycji w prawym dolnym rogu.
• Kliknij OK.

To spowoduje natychmiastowe skopiowanie i wklejenie danych, ale w taki sposób, że zostały one przetransponowane. Poniżej znajduje się demo pokazujące cały proces.

Kroki pokazane powyżej kopiują wartość, formułę (jeśli istnieje), jak również format. Jeśli chcesz skopiować tylko wartość, wybierz 'wartość' w oknie dialogowym wklejania specjalnego.

Zauważ, że skopiowane dane są statyczne i jeśli dokonasz jakichkolwiek zmian w oryginalnym zestawie danych, zmiany te nie zostaną odzwierciedlone w transponowanych danych.

Jeśli chcesz, aby te transponowane komórki były powiązane z oryginalnymi komórkami, możesz połączyć moc funkcji Znajdź & Zamień z funkcją Wklej Specjalnie.

Transponuj dane za pomocą funkcji Wklej Specjalnie i Znajdź & Zamień

Samo użycie funkcji Wklej Specjalnie daje statyczne dane. Oznacza to, że jeśli oryginalne dane ulegną zmianie i chcesz, aby transponowane dane również zostały zaktualizowane, musisz ponownie użyć funkcji Paste Special, aby je transponować.

Jest fajna sztuczka, której możesz użyć, aby transponować dane i nadal mieć je połączone z oryginalnymi komórkami.

Załóżmy, że masz zestaw danych, jak pokazano poniżej:

Oto kroki, które należy wykonać, aby przetransponować dane, ale zachować nienaruszone linki:

• Wybierz zestaw danych (A1:E5).
• Kopiuj go (Control + C, lub kliknij prawym przyciskiem myszy i wybierz kopiuj).
• Teraz możesz wkleić transponowane dane w nowym miejscu. W tym przykładzie chcę skopiować dane w komórkach G1:K5, więc kliknij prawym przyciskiem myszy na komórkę G1 i wybierz polecenie Wklej Specjalnie.
• W oknie dialogowym Wklej Specjalnie kliknij przycisk Wklej łącze. W ten sposób otrzymasz ten sam zestaw danych, ale tutaj komórki są powiązane z oryginalnym zestawem danych (na przykład G1 jest powiązany z A1, a G2 jest powiązany z A2, i tak dalej).
• Z zaznaczonymi nowymi skopiowanymi danymi, Naciśnij Control + H (lub przejdź do Home -> Edycja -> Znajdź & Wybierz -> Zamień). Spowoduje to otwarcie okna dialogowego Znajdź & Zamień.
• W oknie dialogowym Znajdź & Zamień zastosuj następujące elementy:
  • W Find what: =
  • W Replace with: !@# (zauważ, że używam !@#, ponieważ jest to unikalna kombinacja znaków, która najprawdopodobniej nie będzie częścią twoich danych. Możesz użyć dowolnego takiego unikalnego zestawu znaków).
• Kliknij na Replace All. Spowoduje to zastąpienie równych do z formuły i w każdej komórce pojawi się !@#, a następnie odwołanie do komórki.
• Kliknij prawym przyciskiem myszy i skopiuj zestaw danych (lub użyj Control + C).
• Wybierz nową lokalizację, kliknij prawym przyciskiem myszy i wybierz opcję wklejania specjalnego. W tym przykładzie, wklejam go do komórki G7. Możesz ją wkleić gdziekolwiek chcesz.
• W oknie dialogowym Wklej Specjalnie wybierz opcję Transpozycja i kliknij OK.
• Kopiuj i wklej nowo utworzone dane transponowane do tych, z których zostały utworzone.
• Teraz otwórz ponownie okno dialogowe Znajdź i zamień !@# na =.

W ten sposób otrzymasz połączone dane, które zostały transponowane. Jeśli dokonasz jakichkolwiek zmian w oryginalnym zestawie danych, transponowane dane zostaną automatycznie zaktualizowane.

Uwaga: Ponieważ nasze oryginalne dane mają A1 jako puste, musiałbyś ręcznie usunąć 0 w G1. 0 pojawia się, gdy wklejamy linki, ponieważ link do pustej komórki nadal zwróci 0. Ponadto trzeba będzie sformatować nowy zestaw danych (można po prostu skopiować wkleić format tylko z oryginalnego zestawu danych).

Transpozycja danych za pomocą funkcji TRANSPOSE w Excelu

Funkcja TRANSPOSE w Excelu – jak sama nazwa wskazuje – może być używana do transpozycji danych w Excelu.

Załóżmy, że masz zbiór danych, jak pokazano poniżej:

Oto kroki, które należy wykonać, aby dokonać transpozycji:

• Wybierz komórki, w których chcesz dokonać transpozycji zbioru danych. Zauważ, że musisz wybrać dokładnie taką samą liczbę komórek jak oryginalne dane. Więc na przykład, jeśli masz 2 wiersze i 3 kolumny, musisz wybrać 3 wiersze i 2 kolumny komórek, w których chcesz przetransponować dane. W tym przypadku, ponieważ istnieje 5 wierszy i 5 kolumn, musisz wybrać 5 wierszy i 5 kolumn.
• Wpisz =TRANSPOSE(A1:E5) w aktywnej komórce (która powinna być lewą górną komórką zaznaczenia i naciśnij Control Shift Enter.

To spowoduje transpozycję zbioru danych.

Jest kilka rzeczy, które musisz wiedzieć o funkcji TRANSPOSE:

• Jest to funkcja tablicowa, więc musisz użyć Control-Shift-Enter, a nie tylko Enter.
• Nie można usunąć części wyniku. Musisz usunąć całą tablicę wartości, którą zwraca funkcja TRANSPOZYCJA.
• Funkcja TRANSPOZYCJA kopiuje tylko wartości, nie formatowanie.

Transpozycja danych za pomocą Power Query

Power Query jest potężnym narzędziem, które umożliwia szybką transpozycję danych w Excelu.

Power Query jest częścią Excela 2016 (Pobierz & Transform w zakładce Dane), ale jeśli używasz Excela 2013 lub 2010, to musisz zainstalować go jako dodatek.

Załóżmy, że masz zestaw danych, jak pokazano poniżej:

Oto kroki, aby przetransponować te dane za pomocą Power Query:

W Excelu 2016

• Wybierz dane i przejdź do Data -> Pobierz & Przekształć -> Z tabeli.
• W oknie dialogowym Utwórz tabelę upewnij się, że zakres jest prawidłowy i kliknij przycisk OK. Spowoduje to otwarcie okna dialogowego Edytor zapytań.
• W oknie dialogowym Edytor zapytań wybierz zakładkę 'Przekształć'.
• W zakładce Transform, przejdź do Table -> Use First Row as Headers -> Use Headers as First Row. Dzięki temu pierwszy wiersz (zawierający nagłówki: Q1, Q2, Q3 i Q4) również będzie traktowany jako dane i transponowany.
• Kliknij na Transponuj. Spowoduje to natychmiastową transpozycję danych.
• Kliknij na Use First Row as Headers. To spowoduje, że pierwszy wiersz transponowanych danych zostanie użyty jako nagłówki.
• Go to File -> Close and Load. Spowoduje to zamknięcie okna Power Editora i utworzenie nowego arkusza zawierającego transponowane dane.

Zauważ, że ponieważ lewa górna komórka naszego zestawu danych była pusta, otrzymuje ona ogólną nazwę Column1 (jak pokazano poniżej). Możesz usunąć tę komórkę z transponowanych danych.

W Excelu 2013/2010

W Excelu 2013/10 musisz zainstalować Power Query jako dodatek.

Kliknij tutaj, aby pobrać wtyczkę i uzyskać instrukcje instalacji.

Po zainstalowaniu Power Query, przejdź do Power Query -> Excel Data -> Z tabeli.

To otworzy okno dialogowe tworzenia tabeli. Teraz wykonaj te same kroki, jak pokazano dla programu Excel 2016.

You May Also Like the Following Excel Tutorials:

• Mnożenie w Excelu przy użyciu funkcji Paste Special.
• KONCENTROWANIE Zakres Excela (z separatorem i bez separatora).
• Czyszczenie danych w arkuszach Excela.
• Porównywanie kolumn w Excelu
• Tekst w Excelu do kolumn
• Funkcja TRIM w Excelu
• Łączenie tabel w Excelu

.