✅ Jak szybko transponować dane w programie Excel (przewodnik krok po kroku)

Spisie treści

Transpozycja danych za pomocą funkcji Wklej specjalnie
Transpozycja danych za pomocą funkcji Wklej specjalnie oraz Znajdź i zamień
Transpozycja danych za pomocą funkcji Excel TRANSPOSE
Transpozycja danych za pomocą Power Query

Obejrzyj wideo - jak transponować dane w programie Excel

Jeśli masz zestaw danych i chcesz go transponować w programie Excel (co oznacza konwersję wierszy na kolumny i kolumn na wiersze), zrobienie tego ręcznie jest całkowitym NIE!

Transpozycja danych za pomocą funkcji Wklej specjalnie

Wklej specjalnie może zrobić wiele niesamowitych rzeczy, a jedną z takich rzeczy jest transpozycja danych w programie Excel.

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

Te dane zawierają regiony w kolumnie i ćwiartki z rzędu.

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

Wybierz zestaw danych (w tym przypadku A1:E5).
Skopiuj zestaw danych (Control + C) lub kliknij prawym przyciskiem myszy i wybierz kopiuj.
Teraz możesz wkleić transponowane dane w nowej lokalizacji. W tym przykładzie chcę skopiować w G1: K5, więc kliknij prawym przyciskiem myszy komórkę G1 i wybierz wklej specjalnie.
W specjalnym oknie dialogowym wklejania zaznacz opcję transpozycji w prawym dolnym rogu.
Kliknij OK.

Spowodowałoby to natychmiastowe skopiowanie i wklejenie danych, ale w taki sposób, aby zostały przetransponowane. Poniżej znajduje się demo pokazujące cały proces.

Powyższe kroki kopiują wartość, formułę (jeśli istnieje) oraz format. Jeśli chcesz tylko skopiować wartość, wybierz „wartość” w specjalnym oknie dialogowym wklejania.

Zwróć uwagę, że skopiowane dane są statyczne i jeśli wprowadzisz jakiekolwiek zmiany w oryginalnym zestawie danych, zmiany te nie zostaną odzwierciedlone w transponowanych danych.

Jeśli chcesz, aby te transponowane komórki były połączone z oryginalnymi komórkami, możesz połączyć moc funkcji Znajdź i zamień z funkcją Wklej specjalnie.

Transpozycja danych za pomocą funkcji Wklej specjalnie oraz Znajdź i zamień

Korzystanie z funkcji Wklej specjalnie daje dane statyczne. Oznacza to, że jeśli oryginalne dane ulegną zmianie i chcesz, aby transponowane dane również zostały zaktualizowane, musisz ponownie użyć polecenia Wklej specjalnie, aby je transponować.

Oto 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 zbiór danych, jak pokazano poniżej:

Oto kroki, aby transponować dane, ale zachować linki w stanie nienaruszonym:

Wybierz zestaw danych (A1:E5).
Skopiuj go (Control + C lub kliknij prawym przyciskiem myszy i wybierz kopiuj).
Teraz możesz wkleić transponowane dane w nowej lokalizacji. W tym przykładzie chcę skopiować w G1: K5, więc kliknij prawym przyciskiem myszy komórkę G1 i wybierz wklej specjalnie.
W oknie dialogowym Wklej specjalnie kliknij przycisk Wklej łącze. To da ci ten sam zestaw danych, ale tutaj komórki są połączone z oryginalnym zestawem danych (na przykład G1 jest połączone z A1, a G2 jest połączone z A2 i tak dalej).
Po wybraniu tych nowych skopiowanych danych naciśnij Control + H (lub przejdź do Strona główna -> Edycja -> Znajdź i wybierz -> Zamień). Spowoduje to otwarcie okna dialogowego Znajdź i zamień.
W oknie dialogowym Znajdź i zamień wykonaj następujące czynności:
- W Znajdź co: =
- W Zamień na: !@# (pamiętaj, że używam !@#, ponieważ jest to unikalna kombinacja znaków, która prawdopodobnie nie będzie częścią twoich danych. Możesz użyć dowolnego takiego unikalnego zestawu znaków).
Kliknij Zamień wszystko. Zastąpi to równanie z formuły i będziesz miał !@#, a następnie odwołanie do komórki w każdej komórce.
Kliknij prawym przyciskiem myszy i skopiuj zestaw danych (lub użyj Control + C).
Wybierz nową lokalizację, kliknij prawym przyciskiem myszy i wybierz wklej specjalnie. W tym przykładzie wklejam go do komórki G7. Możesz go wkleić w dowolnym miejscu.
W oknie dialogowym Wklej specjalnie wybierz Transponuj i kliknij OK.
Skopiuj i wklej nowo utworzone transponowane dane do tego, z którego zostały utworzone.
Teraz ponownie otwórz okno dialogowe Znajdź i zamień i zamień !@# na =.

W ten sposób uzyskasz połączone dane, które zostały przetransponowane. Jeśli wprowadzisz jakiekolwiek zmiany w oryginalnym zestawie danych, transponowane dane zostaną automatycznie zaktualizowane.

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

Transpozycja danych za pomocą funkcji Excel TRANSPOSE

Funkcja TRANSPONUJ Excela - jak sama nazwa wskazuje - może służyć do transpozycji danych w Excelu.

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

Oto kroki, aby to transponować:

Zaznacz komórki, w których chcesz transponować zestaw danych. Pamiętaj, że musisz wybrać dokładną liczbę komórek jako oryginalne dane. Na przykład, jeśli masz 2 wiersze i 3 kolumny, musisz wybrać 3 wiersze i 2 kolumny komórek, w których chcesz transponować dane. W takim przypadku, ponieważ jest 5 wierszy i 5 kolumn, musisz wybrać 5 wierszy i 5 kolumn.
Wprowadź =TRANSPOSE(A1:E5) w aktywnej komórce (która powinna być lewą górną komórką zaznaczenia i naciśnij klawisz Control Shift Enter.

Spowodowałoby to transpozycję zbioru danych.

Oto kilka rzeczy, które musisz wiedzieć o funkcji TRANSPONUJ:

Jest to funkcja tablicowa, więc musisz użyć Control-Shift-Enter, a nie tylko Enter.
Nie możesz usunąć części wyniku. Musisz usunąć całą tablicę wartości zwracanych przez funkcję TRANSPONUJ.
Funkcja transpozycji kopiuje tylko wartości, a nie formatowanie.

Transpozycja danych za pomocą Power Query

Dodatek Power Query to zaawansowane narzędzie, które umożliwia szybkie transponowanie danych w programie Excel.

Dodatek Power Query jest częścią programu Excel 2016 (Pobierz i przekształć na karcie Dane), ale jeśli korzystasz z programu Excel 2013 lub 2010, musisz zainstalować go jako dodatek.

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

Oto kroki umożliwiające transponowanie tych danych za pomocą dodatku Power Query:

W programie Excel 2016

Wybierz dane i przejdź do Dane -> Pobierz i przekształć -> Z tabeli.
W oknie dialogowym Utwórz tabelę upewnij się, że zakres jest poprawny i kliknij OK. Spowoduje to otwarcie okna dialogowego Edytor zapytań.
W oknie dialogowym Edytor zapytań wybierz kartę „Przekształć”.
Na karcie Przekształć przejdź do Tabela -> Użyj pierwszego wiersza jako nagłówków -> Użyj nagłówków jako pierwszego wiersza. Dzięki temu pierwszy wiersz (zawierający nagłówki: Q1, Q2, Q3 i Q4) również będzie traktowany jako dane i transponowany.
Kliknij Transpozycja. Spowodowałoby to natychmiastową transpozycję danych.
Kliknij Użyj pierwszego rzędu jako nagłówków. W ten sposób pierwszy wiersz transponowanych danych byłby teraz nagłówkiem.
Przejdź do Plik -> Zamknij i załaduj. Spowoduje to zamknięcie okna programu Power Editor i utworzenie nowego arkusza zawierającego transponowane dane.

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