excelpogodzinach-logo
Transponowanie danych - Excel Po Godzinach

3 sposoby na transponowanie danych w Excelu

Niekiedy praca w arkuszu kalkulacyjnym może wymagać zamiany zgromadzonych danych z wierszy do kolumn lub z kolumn do wierszy. Transponowanie danych, bo o nim mowa, nie jest czynnością skomplikowaną.

Warto jednak wiedzieć, że w zależności od zastosowanej metody przekształcone dane mogą mieć charakter statyczny lub dynamiczny. Sposoby przekształcania danych różnią się również ilością czynności jakie należy wykonać, aby osiągnąć zamierzony efekt.

Na potrzeby niniejszego artykułu posłużę się przykładowym zakresem danych zawierającym poziom sprzedaży czterech wybranych marek samochodów w poszczególnych kwartałach roku. Oczywiście zaprezentowane dane są fikcyjne.
 
Transponowanie danych - Excel Po Godzinach

Sposób 1: Transponowanie danych – Wklej specjalnie

Zapewne nie raz miałeś okazję korzystać z polecenia Wklej specjalnie, które oferuje bardzo wiele ciekawych rozwiązań jak chociażby wklejanie wartości, formatów komórek czy komentarzy. Jeśli dobrze przyjrzysz się opcjom tego polecenia to zauważysz, że wśród nich znajduje się również transpozycja danych.

Obrócenie danych z wierszy do kolumn lub na odwrót przy użyciu polecenia Wklej specjalnie wymaga wykonania raptem kilku czynności opisanych poniżej.

KROK 1: Zaznacz zakres danych, który chcesz obrócić (w naszym przykładzie będzie to zakres A1:E5).
KROK 2: Naciśnij prawy przycisk myszy, a następnie wybierz polecenie Kopiuj. Możesz zrobić to szybciej używając skrótu klawiaturowego Ctrl + C.
 
Transponowanie danych - Excel Po Godzinach
KROK 3: Przejdź do miejsca w arkuszu gdzie chcesz wkleić transponowane dane (tutaj komórka A7), naciśnij prawy przycisk myszy, a z menu podręcznego wybierz polecenie Wklej specjalnie. Możesz również użyć kombinacji klawiszy Ctrl + Alt + V.
 
Transponowanie danych - Excel Po Godzinach
KROK 4: W oknie dialogowym Wklejanie specjalne zaznacz opcję Transpozycja.
 
Transponowanie danych - Excel Po Godzinach
KROK 5: Kliknij przycisk OK i gotowe!

Zawartość komórek źródłowych (wartości liczbowe, tekst oraz formuły), jak również ich formatowanie zostało skopiowane, a następnie przekształcone (obrócone) i wklejone do docelowego miejsca w arkuszu.
 
Transponowanie danych - Excel Po Godzinach
Zaletą tej metody jest szybkość wykonania. Niestety nie jest ona pozbawiona wad. Największą z nich jest to, iż transponowane dane mają charakter statyczny, co oznacza, że zmiana zawartości którejkolwiek z komórek wchodzących w skład zakresu źródłowego nie ma żadnego wpływu na przekształcony zakres danych.

Na szczęście istnieje prosty i skuteczny trik, który pozwala rozwiązać ten problem. Jest nim sposób numer 2.

Sposób 2: Transponowanie danych – Znajdź i zamień

Osiągnięcie zamierzonego celu w postaci przekształconych danych, które automatycznie aktualizują się wraz ze zmianą wartości zakresu źródłowego wymaga wykonania małej excelowej akrobacji.

Tak naprawdę jest to sposób zaprezentowany powyżej tyle tylko, że wzbogacony o kilka dodatkowych kroków.

KROK 1: Zaznacz zakres danych, który chcesz obrócić (w naszym przykładzie będzie to zakres A1:E5).
KROK 2: Naciśnij prawy przycisk myszy, a następnie wybierz polecenie Kopiuj. Możesz zrobić to szybciej używając skrótu klawiaturowego Ctrl + C.
KROK 3: Przejdź do miejsca w arkuszu gdzie chcesz wkleić transponowane dane (tutaj komórka A7), naciśnij prawy przycisk myszy, a z menu podręcznego wybierz polecenie Wklej specjalnie. Możesz również użyć kombinacji klawiszy Ctrl + Alt + V.
KROK 4: W oknie dialogowym Wklejanie specjalne kliknij przycisk Wklej łącze.
 
Transponowanie danych - Excel Po Godzinach
Excel wklei dane w takim samym układzie co dane źródłowe z tą różnicą, że skopiowane komórki zawierają odwołania do wartości komórek źródłowych. Dla przykładu komórka A7 odnosi się do komórki A1, B7 do B1 itd.
 
Transponowanie danych - Excel Po Godzinach
KROK 5: Mając zaznaczony nowo skopiowany zakres danych wybierz polecenie Zamień, które znajdziesz na karcie Narzędzia główne w grupie Edytowanie pod przyciskiem Znajdź i zaznacz. Możesz również użyć skrótu klawiaturowego Ctrl + H.
 
Transponowanie danych - Excel Po Godzinach
KROK 6: W oknie dialogowym Znajdowanie i zamienianie wprowadź odpowiednio znak równości (=) do pola Znajdź oraz znak hashtag (#) do pola Zamień na. Możesz także zastosować inny znak zwracając uwagę na to, aby nie występował w zaznaczonym obszarze komórek. W przeciwnym razie opisywana procedura może wywołać błędy.
 
Transponowanie danych - Excel Po Godzinach
KROK 7: Kliknij przycisk Zamień wszystko, aby uzyskać łańcuchy znaków zaczynające się od znaku hashtag (#), po którym wystąpią odwołania do komórek źródłowych.
KROK 8: Zaznaczony zakres danych (A7:E11) skopiuj w sposób opisany w drugim punkcie.
KROK 9: Wskaż nową lokalizację (tutaj komórka A13), a następnie użyj polecenia Wklej specjalnie.
KROK 10: W oknie dialogowym Wklejanie specjalne zaznacz opcję Transpozycja.
 
Transponowanie danych - Excel Po Godzinach
KROK 11: Kliknij przycisk OK.
KROK 12: Mając zaznaczony zakres danych ponownie wywołaj okno dialogowe Znajdowanie i zamienianie, a następnie zamień wszystkie wystąpienia znaku hashtag (#) na znak równości (=). Kliknij przycisk Zamień wszystko.
 
Transponowanie danych - Excel Po Godzinach
Przedstawiony sposób pozwala, korzystając z polecenia Wklej specjalnie, przekształcić dane oraz połączyć je z zakresem źródłowym. Dzięki temu każdorazowa zmiana dowolnej komórki źródłowej spowoduje taką samą zmianę w przekształconym zakresie.
 
Transponowanie danych - Excel Po Godzinach
Oczywiście, aby nie zostawiać zbyt dużego bałaganu sugeruję usunięcie wierszy 7:11, które zawierają krok pośredni i nie są już do niczego potrzebne.

Ponadto zauważ, że mamy dane dynamiczne, ale brakuje odpowiedniego formatowania komórek. Nie jest to jednak powód do zmartwień. Możesz je dodać albo wklejając specjalnie format danych źródłowych, albo używając Malarza formatów.

Sposób 3: Transponowanie danych – TRANSPONUJ()

Wiesz już w jaki sposób obrócić dane z wierszy do kolumn i na odwrót korzystając z polecenia Wklej specjalnie. Potrafisz również kopiowane dane połączyć z zakresem źródłowym, dzięki czemu będą się one dynamicznie zmieniały wraz z modyfikacją wartości pierwotnych.

Teraz chcę Ci zaproponować ostatni, trzeci już sposób na przekształcenie danych wymagający użycia funkcji TRANSPONUJ().

Funkcja ta ma za zadanie skonwertować pionowy zakres komórek do zakresu poziomego lub na odwrót. Jej składnia jest bardzo prosta i wymaga podania tylko jednego argumentu, którym jest tablica, czyli zakres komórek w arkuszu, który ma podlegać transpozycji.

TRANSPONUJ(tablica)

Jak transponować dane przy użyciu formuły? Otóż wystarczy, że wykonasz następujące czynności.

KROK 1: Zaznacz zakres komórek, w których chcesz dokonać konwersji zakresu źródłowego. Pamiętaj, aby zaznaczyć prawidłową liczbę komórek. Dla przykładu, jeśli zakres początkowy miałby wymiary 5 wierszy na 7 kolumn, to musiałbyś zaznaczyć zakres obejmujący 7 wierszy i 5 kolumn, czyli obszar zajęty przez przekształcony zakres.
KROK 2: W bieżącej komórce (powinna to być komórka umieszczona w lewym górnym rogu zaznaczenia) wprowadź funkcję =TRANSPONUJ(A1:E7), a następnie naciśnij kombinację klawiszy Ctrl + Shift + Enter.
 
Transponowanie danych - Excel Po Godzinach

Gotowe! Nie zajęło to dużo czasu, a w arkuszu masz już przekształcony zakres danych.
 
Transponowanie danych - Excel Po Godzinach
Tym razem do osiągnięcia zamierzonego celu wykorzystałeś formułę tablicową, stąd konieczność użycia kombinacji klawiszy Ctrl + Shift + Enter przy zatwierdzaniu wprowadzonej formuły. Niestety samo naciśnięcie klawisza Enter nie jest wystarczające i w tym przypadku wywołałoby błąd argumentu #ARG!.

Zastosowanie funkcji TRANSPONUJ pozwala obrócić dane, czyniąc je dynamicznymi, ale nie kopiuje formatowania komórek. To zadanie musisz wykonać dodatkowo. Ponadto warto wiedzieć, że korzystając z tak utworzonej tablicy nie możesz ani skasować ani zmodyfikować dowolnej jej części. Excel dopuszcza tylko działanie na całości. Wtedy usuwanie lub modyfikacja jest możliwa.

W tym miejscu chciałbym wspomnieć o jeszcze jednej istotnej rzeczy. Otóż we wszystkich trzech sposobach przekształcaliśmy zakresy danych, które nie były tabelami Excela.

Okazuje się, że w przypadku tabeli (utworzona skrótem klawiaturowym Ctrl + T lub za pomocą polecenia Tabela, które znajduje się na karcie Wstawianie w grupie Tabele) działa tylko sposób trzeci, czyli funkcja TRANSPONUJ.

Dla niektórych użytkowników może to być pewne ograniczenie, które oczywiście można obejść. Wystarczy przed skopiowaniem zakresu źródłowego skonwertować tabelę na zakres, a po wykonaniu wszystkich czynności z powrotem utworzyć tabelę.

A jak jest w Twoim przypadku? Korzystasz w ogóle z transponowania danych w Excelu? Którą metodę znałeś, z której korzystasz? A może znasz jeszcze inne rozwiązanie? Jeśli tak, to zapraszam do umieszczania komentarzy pod artykułem.

4 myśli na temat “3 sposoby na transponowanie danych w Excelu

  1. Dobry wieczór p. Michale, jak zawsze dobry temat wzięty z życia. Bardzo praktyczny i przydatny w codziennej pracy. Treść artykułu omówiona rewelacyjnie i do tego wysoki poziom zaprezentowany i aby więcej takich tematów się pojawiało.

    Można tylko autorowi pozazdrościć posiadanej wiedzy zakresu Excela, ale również widać w temacie dużą praktykę pisaną przez duże „P”. Co można jeszcze napisać na temat przedmiotowego artykułu, jak dla Mnie rewelacja i jeszcze raz powtórzę „rewelka”.

    W moim przypadku Używam drugiego sposobu opisanego w artykule Zauważyłem, że Pan Michał zawsze w swoich artykułach podaje przeważnie trzy sposoby rozwiązywania danego problemu i to chodzi. Nigdy nie jest tzw.” sztampowo”, inni jeszcze mówią ile osób tyle rozwiązań w matematyce, coś w tym jest.

    Chciałbym ewentualnie, aby częściej pojawiały w miarę możliwości tak perfekcyjnie opisane tematy częściej na tym portalu, choćby dwa razy w miesiącu, a tak trzeba czekać na okazanie się kolejnego tematu w październiku, a dopiero jest to początek września wielka szkoda i oczekiwanie z utęsknieniem na nowy temat.

    Trzymam kciuki za dalsze prowadzenie pańskiego bloga z poradami i trikami dot. Excela, ponieważ daje Pan dobre lekcje w Internecie gratis i nadal chcę Pan pomagać innym użytkownikom w zgłębianiu i rozwiązywaniu ich problemów z tym programem.

    Proszę ewentualnie krótką informację na pańskim blogu jaki temat będzie przygotowywany na następny miesiąc.

    Nadal będę czekał na mój temat dot. drukowania z wszystkimi opcjami /taki mini poradnik /dla każdego. Jak również na inne podane przez czytelników pańskiego bloga, które też na pewno są interesujące i warte do zaprezentowania przez Pana.

    Chętnie bym podał jakie tematy mnie interesują, ale trzeba przystopować z uwagi, iż są inni i też czekają na opublikowanie ich tematów, a więc trzeba czekać na swoją kolejkę.
    Pozdrawiam
    Jankesd

    1. Witaj Jankes!

      Dziękuję za miłe słowa. Cieszy mnie fakt, że prezentowane na blogu treści przydają się moim Czytelnikom w ich zmaganiach z Excelem. W końcu po to został stworzony. 🙂 Co do „3 rozwiązań”, to kiedyś wspomniałem na jednym z moich szkoleń. W rzeczywistości gdybyśmy się uparli, to w bardzo wielu przypadkach moglibyśmy zwiększyć tą liczbę, ale faktycznie większość operacji można wykonać na różne sposoby i to jest super! Jedna osoba operuje tylko skrótami klawiaturowymi, a druga nie wyobraża sobie pracy bez użycia myszki. Prezentując różniące się od siebie sposoby uzyskania tego samego rezultatu gwarantują, że każdy znajdzie coś dla siebie.

      Propozycje tematów zawsze można wysłać korzystając z formularza kontaktowego http://excelpogodzinach.pl/kontakt. Zresztą cały czas Czytelnicy podsyłają bardzo ciekawe pytania. Staram się opracowywać w pierwszej kolejności te, którymi zainteresowana będzie większość osób.

      Pozdrawiam

  2. Dzień Dobry,
    świetny artykuł. Dziękuję.
    Ja na swoje potrzeby potrzebuję skorzystać ze sposobu nr 3, ale mam problem – ta funkcja nie działa, jeśli chcę te dane transponować do scalonych komórek :/
    Szukam jakiejś alternatywy i pomocy 🙂

    Pozdrawiam

    1. Witaj Paulino!

      Dziękuję za komentarz. Ze scalonymi komórkami zawsze jest problem. One nie działają poprawnie z poleceniami Wytnij lub Kopiuj i Wklej. Pamiętaj, że scalone komórki są przydatne przy prezentowaniu danych, ale gorzej radzą sobie z przetwarzaniem danych. Dlatego jeśli tylko możesz, unikaj scalania komórek.

      Pozdrawiam

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *