Transpozycja w Excelu: TRANSPONUJ (TRANSPOSE)

Aby zamienić wiersze z kolumnami w Excelu, wpisz =TRANSPONUJ(A1:D3) (po angielsku TRANSPOSE) tam, gdzie ma się zaczynać wynik: pierwszy wiersz A1:D3 staje się pierwszą kolumną wyniku. Wynik pozostaje połączony, więc zmiana liczby w źródle zmienia ją też w kopii. Zmień B2 na 50 i zobacz, jak B6 się dostosowuje. Tabela pokazuje formułę po angielsku, =TRANSPOSE(A1:D3), ale możesz w niej wpisywać formuły także po polsku, ze średnikami.

FormułyResetuj

A5fx

=TRANSPOSE(A1:D3)

	A	B	C	D
1	Rep	Q1	Q2	Q3
2	Ann	10	12	9
3	Ben	8	11	14
4
5	Rep	Ann	Ben
6	Q1	10	8
7	Q2	12	11
8	Q3	9	14

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =TRANSPONUJ(A1:D3)

Źródło ma 3 wiersze i 4 kolumny, więc wynik ma 4 wiersze i 3 kolumny. W Excelu 2021 i Microsoft 365 TRANSPONUJ się rozlewa: wpisujesz ją w jednej komórce i naciskasz Enter.

Transpozycja przez Wklej specjalnie

Gdy zamienioną tabelę potrzebujesz tylko raz, Wklej specjalnie jest szybsze i nie zostawia formuły:

1. Zaznacz zakres i skopiuj go (Ctrl+C, na Macu Cmd+C).
2. Kliknij lewą górną komórkę miejsca, w którym ma się znaleźć wynik, poza skopiowanym zakresem.
3. Na karcie Narzędzia główne kliknij strzałkę pod przyciskiem Wklej i wybierz Transpozycja albo naciśnij Ctrl+Alt+V (na Macu Cmd+Ctrl+V), zaznacz Transpozycja i kliknij OK.

W angielskim Excelu dla Windows ścieżka z klawiatury po skopiowaniu to Alt, H, V, T. Wklejona kopia zachowuje formatowanie, ale nie jest połączona: gdy źródło się zmieni, wklej ponownie. Excel dopasowuje formuły w skopiowanym zakresie do ich nowych miejsc; sprawdź te, które używają odwołań względnych, albo wklej wartości, jeśli potrzebujesz tylko liczb.

Składnia funkcji TRANSPONUJ

=TRANSPOSE(array)

Jedynym argumentem jest zakres albo tablica do odwrócenia. TRANSPONUJ jest w Excelu od dawna, ale przed Excelem 2021 to formuła tablicowa: najpierw zaznacz obszar docelowy o właściwym kształcie (4 wiersze na 3 kolumny dla przykładu powyżej), wpisz formułę i naciśnij Ctrl+Shift+Enter (na Macu Cmd+Shift+Enter). Excel pokazuje ją wtedy w nawiasach klamrowych, {=TRANSPONUJ(A1:D3)}. W Excelu 2021 i Microsoft 365 wystarczy zwykły Enter, a wartość stojąca na drodze daje #ROZLANIE! (po angielsku #SPILL!).

Arkusze Google też mają TRANSPOSE i rozlewają ją tak samo.

Zamiana kolumny w wiersz

Ta sama funkcja odwraca pojedynczą kolumnę w wiersz, na przykład listę imion w nagłówki kolumn:

FormułyResetuj

C2fx

=TRANSPOSE(A2:A6)

	A	B	C	D	E	F	G
1	Name
2	Ann		Ann	Ben	Cara	Dan	Eve
3	Ben
4	Cara
5	Dan
6	Eve

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =TRANSPONUJ(A2:A6)

C2 rozlewa imiona od Ann do Eve w C2:G2. Dopisz imię w A7, a nie zostanie uwzględnione, bo formuła czyta A2:A6; aby je dołączyć, rozszerz zakres do A2:A7 albo dalej.

Dlaczego TRANSPONUJ pokazuje 0 dla pustych komórek

Formuła wskazująca pustą komórkę zwraca 0, i TRANSPONUJ też. Poniżej marzec nie ma sprzedaży, więc zwykła wersja pokazuje 0 tam, gdzie źródło jest puste:

FormułyResetuj

D4fx

=TRANSPOSE(IF(A1:E2="","",A1:E2))

	A	B	C	D	E
1	Month	Jan	Feb	Mar	Apr
2	Sales	120	95		150
3
4	Month	Sales		Month	Sales
5	Jan	120		Jan	120
6	Feb	95		Feb	95
7	Mar	0		Mar
8	Apr	150		Apr	150

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =TRANSPONUJ(JEŻELI(A1:E2="";"";A1:E2))

B7 pokazuje 0 dla Mar. Druga formuła najpierw zamienia każdą pustą komórkę na pusty tekst funkcją JEŻELI (IF), więc E7 zostaje pusta; w polskim Excelu to =TRANSPONUJ(JEŻELI(A1:E2="";"";A1:E2)). Wpisz liczbę w D2, a obie formuły ją pokażą.

Transpozycja kilku wierszy w jedną kolumnę

Aby ułożyć całą siatkę w jedną kolumnę, użyj DO.KOLUMNY (TOCOL; Microsoft 365 i Excel 2024). Czyta zakres wiersz po wierszu; DO.WIERSZA (TOROW) robi to samo w jeden wiersz:

FormułyResetuj

F2fx

=TOCOL(B2:D3)

	A	B	C	D	E	F
1	Rep	Q1	Q2	Q3		All values
2	Ann	10	12	9		10
3	Ben	8	11	14		12
4						9
5						8
6						11
7						14

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =DO.KOLUMNY(B2:D3)

F2 wypisuje 10, 12, 9 z wiersza Ann, a potem 8, 11, 14 z wiersza Bena. Aby czytać w dół każdej kolumny, ustaw trzeci argument na PRAWDA: =DO.KOLUMNY(B2:D3;;PRAWDA). =DO.KOLUMNY(B2:D3;1) pomija puste komórki.

Ćwiczenie: miesiące w pionie zamiast w poziomie

FormułyResetuj

A5fx

	A	B	C	D	E
1	Month	Jan	Feb	Mar	Apr
2	Sales	120	95	80	150
3
4	Months down

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.

Twoja kolej: W A5 odwróć tabelę z A1:E2 tak, żeby miesiące szły w dół kolumny A, a sprzedaż w dół kolumny B.

SprawdźPokaż odpowiedź

TRANSPONUJ czy wyszukiwanie wzdłuż wiersza

Często transponuje się szeroką tabelę tylko po to, żeby użyć na niej WYSZUKAJ.PIONOWO. Nie jest to potrzebne: WYSZUKAJ.POZIOMO (HLOOKUP) i X.WYSZUKAJ przeszukują wiersz bezpośrednio. =X.WYSZUKAJ("Mar";B1:E1;B2:E2) zwraca sprzedaż z marca z szerokiej tabeli powyżej bez odwracania czegokolwiek. Transponuj wtedy, gdy musi się zmienić sam układ: dla wykresu, raportu albo systemu, który oczekuje jednego rekordu na wiersz.