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.
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.
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 zaznacz opcję Transpozycja.
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.
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.
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.
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.
KROK 6: W oknie dialogowym Znajdowanie i zamienianie wprowadź odpowiednio znak równości (=) do pola Znajdź oraz znak hash (#) 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.
KROK 7: Kliknij przycisk Zamień wszystko, aby uzyskać łańcuchy znaków zaczynające się od znaku hash (#), 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.
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 hash (#) na znak równości (=). Kliknij przycisk Zamień wszystko.
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.
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.
Gotowe! Nie zajęło to dużo czasu, a w arkuszu masz już przekształcony zakres danych.
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.