wszyscy inaczej patrzymy na dane. Niektórzy ludzie tworzą arkusze kalkulacyjne Excel z głównymi polami poziomymi. Inne preferują dane pionowo w kolumnach. Czasami te preferencje prowadzą do scenariusza, w którym chcesz przetransponować dane programu Excel. Firma Microsoft musiała przewidzieć ten problem, ponieważ oferuje kilka metod przełączania kolumn i wierszy.,
zawartość artykułu
problem z układem
niedawno otrzymałem duży arkusz kalkulacyjny Microsoft Excel, który zawierał informacje o ocenie dostawcy. Informacje były przydatne, ale ze względu na sposób, w jaki dane zostały zorganizowane, nie mogłem użyć narzędzi takich jak Auto Filter. Miałbym również problemy, gdybym potrzebował zaimportować informacje do bazy danych. Prosty przykład przedstawiono poniżej.,
zamiast tego chcę, aby nazwy firm wyświetlały się pionowo w kolumnie A, A atrybuty danych wyświetlały się poziomo w wierszu 1. To ułatwi mi przeprowadzenie analizy. Na przykład, nie mogę łatwo filtrować dla dostawców z Kalifornii.
Co to jest funkcja transpozycji Excela
w prostych słowach funkcja transpozycji zmienia orientację kolumn (zakres pionowy) i wierszy (zakres poziomy)., Moje oryginalne wiersze danych staną się kolumnami, a moje kolumny staną się wierszami. Rozwiązuje mój problem z układem powyżej, bez konieczności ponownego wpisywania oryginalnych danych.
funkcja transpozycji jest dość wszechstronna. Może być używany w formułach Excel lub z opcjami wklejania. Istnieją pewne różnice w zależności od metody, której używasz.
Konwertuj kolumny&wiersze za pomocą Wklej& Transpozycja
kroki opisane poniżej zostały wykonane przy użyciu Microsoft Office 365, ale najnowsze wersje programu Microsoft Excel będą działać.,
- otwórz arkusz kalkulacyjny, który musisz zmienić. Możesz również pobrać przykładowy arkusz na końcu tego samouczka.
- kliknij pierwszą komórkę swojego zakresu danych, taką jak A1.
- Shift-kliknij ostatnią komórkę zakresu. Twój zestaw danych powinien się wyróżnić.
- z zakładki Strona główna Wybierz opcję Kopiuj lub wpisz Ctrl + c.
- wybierz nową komórkę, w której chcesz skopiować transponowane dane.
- kliknij prawym przyciskiem myszy w tej komórce i wybierz ikonę TRANSPONUJ w obszarze Wklej.,
informacje po najechaniu kursorem na Opcje wklejania widać zmianę układu danych.
powinieneś teraz zobaczyć swoje dane z przełączonymi kolumnami i wierszami. Możesz zmienić rozmiar kolumn do własnych potrzeb.
te dwa zbiory danych są niezależne. Możesz usunąć komórki z górnego zestawu i nie wpłynie to na transponowany zestaw.
podczas korzystania z tej metody oryginalne formatowanie jest zachowywane. Na przykład, jeśli dodam żółte tło do moich oryginalnych komórek B1: G1, kolor zostanie zastosowany. To samo jest prawdą, jeśli użyłem czerwonego tekstu na komórce E: 5.
korzystając z funkcji Transpozycja w Formule
Jak wspomniałem, Excel ma wiele sposobów przełączania kolumn na wiersze lub odwrotnie. Ten drugi sposób wykorzystuje formułę i tablicę. Wynik jest taki sam, z wyjątkiem oryginalnych danych i nowych przetransponowanych danych są połączone. Możesz stracić część oryginalnego formatowania., Na przykład napotkano kolorowy tekst i style, ale nie kolory wypełnienia komórek.
- otwórz arkusz Excela.
- kliknij pustą komórkę, w której chcesz przekonwertować dane. Używam A7.
- Type =transpose.
- Zakończ formułę dodając a (i podświetlając zakres komórek, które chcemy zamienić.,
- Typ), aby zamknąć zakres.
- naciśnij Enter.
Info Notice how Excel provides a tooltip showing for Transpose – „Converts a vertical range of cells to a horizontal range and vice versa”.
Uwaga: Jeśli nie używasz Microsoft 365, prawdopodobnie będziesz musiał nacisnąć CTRL + SHIFT + Enter
różnice w transpozycji
podczas gdy te dwie metody dają podobne wyniki, istnieją różnice. W pierwszej metodzie wklejania, jakiekolwiek działanie jakie podejmuję na komórce jest niezależne od transponowanej wersji. Mogłem usunąć oryginalne wartości i nic się nie stanie z kolumnami, które zamieniłem.,
natomiast wersja Formuły Transpozycyjnej jest powiązana z oryginalnymi danymi. Jeśli zmienię wartość w B2 z 1200 na 1500, nowa wartość automatycznie zaktualizuje się w B8. Jednak odwrotnie nie jest prawdą. Jeśli zmienię jakiekolwiek transponowane komórki, oryginalny zestaw się nie zmieni. Zamiast tego dostanę błąd # SPILL i moje transponowane dane znikną.,
Transpozycja Formuły & puste komórki
kolejną różnicą w użyciu transpozycji formuły jest to, że konwertuje puste komórki na „0”s.
poprawką dla tego dziwactwa jest użycie instrukcji If do formuły, która zachowuje komórki spacji jako spacje.,
=TRANSPOSE(IF(A1:G5="","",A1:G5))
Alternatywnie możesz wyszukać i zastąpić zera.
