Funkcja transpozycji w programie Excel

Od czasu do czasu użytkownik Excela może mieć za zadanie przekształcenie zakresu danych o strukturze poziomej w pionową. Ten proces nazywa się transpozycją. To słowo jest nowe dla większości ludzi, ponieważ w normalnej pracy z komputerem nie musisz korzystać z tej operacji. Jednak ci, którzy muszą pracować z dużą ilością danych, muszą wiedzieć, jak to zrobić. Dzisiaj porozmawiamy bardziej szczegółowo o tym, jak to wykonać, z jaką funkcją, a także szczegółowo przyjrzymy się innym metodom.

Funkcja TRANSPOSE – transponuj zakresy komórek w Excelu

Jedną z najciekawszych i najbardziej funkcjonalnych metod transpozycji tabel w Excelu jest funkcja TRANSP. Za jego pomocą możesz zmienić poziomy zakres danych na pionowy lub wykonać operację odwrotną. Zobaczmy, jak z nim pracować.

Składnia funkcji

Składnia tej funkcji jest niezwykle prosta: TRANSPONUJ(tablica). Oznacza to, że musimy użyć tylko jednego argumentu, którym jest zestaw danych, który należy przekonwertować na widok poziomy lub pionowy, w zależności od tego, jaki był pierwotnie.

Transpozycja pionowych zakresów komórek (kolumn)

Załóżmy, że mamy kolumnę o zakresie B2:B6. Mogą zawierać zarówno gotowe wartości, jak i formuły zwracające wyniki do tych komórek. Nie jest to dla nas takie ważne, transpozycja jest możliwa w obu przypadkach. Po zastosowaniu tej funkcji długość wiersza będzie taka sama jak długość oryginalnej kolumny zakresu.

Kolejność etapów korzystania z tego wzoru jest następująca:

1. Wybierz linię. W naszym przypadku ma długość pięciu komórek.
2. Następnie przesuń kursor na pasek formuły i wprowadź tam formułę =TRANSP(B2:B6).
3. Naciśnij kombinację klawiszy Ctrl + Shift + Enter.

Oczywiście w Twoim przypadku musisz określić zakres, który jest typowy dla Twojego stołu.

Transpozycja poziomych zakresów komórek (wierszy)

W zasadzie mechanizm działania jest prawie taki sam jak w poprzednim akapicie. Załóżmy, że mamy ciąg znaków ze współrzędnymi początkowymi i końcowymi B10:F10. Może również zawierać zarówno bezpośrednio wartości, jak i formuły. Zróbmy z tego kolumnę, która będzie miała takie same wymiary jak oryginalny rząd. Sekwencja działań jest następująca:

1. Wybierz tę kolumnę za pomocą myszy. Możesz także użyć klawiszy na klawiaturze Ctrl i strzałki w dół, po kliknięciu najwyższej komórki tej kolumny.
2. Następnie piszemy formułę =TRANSP(B10:F10) na pasku formuły.
3. Piszemy to jako formułę tablicową za pomocą kombinacji klawiszy Ctrl + Shift + Enter.

Transpozycja za pomocą funkcji Wklej specjalnie

Inną możliwą opcją transpozycji jest użycie funkcji Wklej specjalnie. Nie jest to już operator używany w formułach, ale jest to również jedna z popularnych metod zamiany kolumn w wiersze i odwrotnie.

Ta opcja znajduje się na karcie Strona główna. Aby uzyskać do niego dostęp, musisz znaleźć grupę „Schowek”, a tam znaleźć przycisk „Wklej”. Następnie otwórz menu znajdujące się pod tą opcją i wybierz element „Transponuj”. Wcześniej musisz wybrać zakres, który chcesz wybrać. W rezultacie otrzymamy ten sam zakres, tylko przeciwne jest lustrzane.

3 sposoby transpozycji tabeli w programie Excel

Ale w rzeczywistości istnieje znacznie więcej sposobów przekształcania kolumn w wiersze i odwrotnie. Opiszmy 3 metody, za pomocą których możemy transponować tabelę w Excelu. Omówiliśmy dwa z nich powyżej, ale podamy jeszcze kilka przykładów, abyś miał lepszy pomysł na uXNUMXbuXNUMXbjak przeprowadzić tę procedurę.

Metoda 1: Wklej specjalnie

Ta metoda jest najprostsza. Wystarczy nacisnąć kilka przycisków, a użytkownik otrzymuje transponowaną wersję tabeli. Podajmy mały przykład dla większej jasności. Załóżmy, że mamy tabelę zawierającą informacje o tym, ile produktów jest aktualnie w magazynie, a także ile łącznie kosztują. Sam stół wygląda tak.

Widzimy, że mamy nagłówek i kolumnę z numerami produktów. W naszym przykładzie nagłówek zawiera informacje o tym, jaki produkt, ile kosztuje, ile jest w magazynie i jaki jest łączny koszt wszystkich produktów związanych z tym towarem, które są w magazynie. Koszt otrzymujemy według wzoru, w którym koszt mnożymy przez ilość. Aby przykład był bardziej wizualny, ustawmy nagłówek w kolorze zielonym.

Naszym zadaniem jest upewnienie się, że informacje zawarte w tabeli znajdują się poziomo. Oznacza to, że kolumny stają się rzędami. Sekwencja działań w naszym przypadku będzie następująca:

1. Wybierz zakres danych, które musimy obrócić. Następnie kopiujemy te dane.
2. Umieść kursor w dowolnym miejscu na arkuszu. Następnie kliknij prawym przyciskiem myszy i otwórz menu kontekstowe.
3. Następnie kliknij przycisk „Wklej specjalnie”.

Po wykonaniu tych kroków musisz kliknąć przycisk „Transponuj”. Zamiast tego zaznacz pole obok tego elementu. Nie zmieniamy innych ustawień, a następnie klikamy przycisk „OK”.

Po wykonaniu tych kroków pozostaje nam ta sama tabela, tylko jej wiersze i kolumny są inaczej ułożone. Zauważ również, że komórki zawierające te same informacje są podświetlone na zielono. Pytanie: co się stało z formułami, które były w oryginalnym zakresie? Zmieniła się ich lokalizacja, ale oni sami pozostali. Adresy komórek po prostu zmieniły się na te, które powstały po transpozycji.

Prawie te same czynności należy wykonać, aby transponować wartości, a nie formuły. W takim przypadku również musisz użyć menu Wklej specjalnie, ale wcześniej wybierz zakres danych zawierający wartości. Widzimy, że okno Wklej specjalnie można wywołać na dwa sposoby: poprzez specjalne menu na wstążce lub menu kontekstowe.

Metoda 2. Funkcja TRANSP w programie Excel

W rzeczywistości ta metoda nie jest już używana tak aktywnie, jak na samym początku pojawienia się tego programu do arkuszy kalkulacyjnych. Dzieje się tak, ponieważ ta metoda jest znacznie bardziej skomplikowana niż użycie Wklej specjalnie. Znajduje jednak zastosowanie w automatyzacji transpozycji tabel.

Ta funkcja jest również dostępna w programie Excel, więc konieczne jest, aby o niej wiedzieć, mimo że prawie nie jest już używana. Wcześniej zastanawialiśmy się nad procedurą, jak z nią pracować. Teraz uzupełnimy tę wiedzę dodatkowym przykładem.

1. Najpierw musimy wybrać zakres danych, który zostanie użyty do transpozycji tabeli. Wystarczy wybrać obszar przeciwnie. Na przykład w tym przykładzie mamy 4 kolumny i 6 wierszy. Dlatego konieczne jest wybranie obszaru o przeciwnych cechach: 6 kolumn i 4 rzędy. Zdjęcie bardzo dobrze to pokazuje.
2. Następnie natychmiast zaczynamy wypełniać tę komórkę. Ważne jest, aby przypadkowo nie usunąć zaznaczenia. Dlatego formułę należy określić bezpośrednio na pasku formuły.
3. Następnie naciśnij kombinację klawiszy Ctrl + Shift + Enter. Pamiętaj, że jest to formuła tablicowa, ponieważ pracujemy jednocześnie z dużym zestawem danych, które zostaną przeniesione do innego dużego zestawu komórek.

Po wprowadzeniu danych wciskamy klawisz Enter, po czym otrzymujemy następujący wynik.

Widzimy, że formuła nie została przeniesiona do nowej tabeli. Utracono również formatowanie. Poeto

Wszystko to trzeba będzie zrobić ręcznie. Ponadto pamiętaj, że ten stół jest powiązany z oryginalnym. Dlatego też, gdy tylko niektóre informacje zostaną zmienione w pierwotnym zakresie, zmiany te są automatycznie wprowadzane do transponowanej tabeli.

Dlatego ta metoda jest odpowiednia w przypadkach, w których musisz upewnić się, że transponowana tabela jest połączona z oryginalną tabelą. Jeśli użyjesz specjalnej wkładki, taka możliwość już nie będzie.

tabela podsumowująca

Jest to całkowicie nowa metoda, która umożliwia nie tylko transpozycję tabeli, ale także wykonanie ogromnej liczby działań. To prawda, że ​​mechanizm transpozycji będzie nieco inny w porównaniu z poprzednimi metodami. Sekwencja działań jest następująca:

1. Zróbmy tabelę przestawną. Aby to zrobić, musisz wybrać tabelę, którą musimy transponować. Następnie przejdź do elementu „Wstaw” i poszukaj tam „Tabela przestawna”. Pojawi się okno dialogowe podobne do tego na tym zrzucie ekranu.
2. Tutaj możesz zmienić przypisanie zakresu, z którego zostanie wykonany, a także wprowadzić szereg innych ustawień. Interesuje nas teraz przede wszystkim miejsce tabeli przestawnej – na nowym arkuszu.
3. Następnie automatycznie zostanie utworzony układ tabeli przestawnej. Konieczne jest zaznaczenie w nim tych przedmiotów, których będziemy używać, a następnie należy je przenieść we właściwe miejsce. W naszym przypadku musimy przenieść pozycję „Produkt” do „Nazwy kolumn”, a „Cena za sztukę” do „Wartości”.
4. Następnie ostatecznie zostanie utworzona tabela przestawna. Dodatkowym bonusem jest automatyczne obliczenie wartości końcowej.
5. Możesz również zmienić inne ustawienia. Na przykład odznacz pozycję „Cena za sztukę” i zaznacz pozycję „Całkowity koszt”. W rezultacie będziemy mieli tabelę zawierającą informacje o tym, ile kosztują produkty. Ta metoda transpozycji jest znacznie bardziej funkcjonalna niż inne. Opiszmy niektóre zalety tabel przestawnych:

1. Automatyzacja. Za pomocą tabel przestawnych możesz automatycznie podsumowywać dane, a także dowolnie zmieniać położenie kolumn i kolumn. Aby to zrobić, nie musisz wykonywać żadnych dodatkowych czynności.
2. Interaktywność. Użytkownik może zmieniać strukturę informacji tyle razy, ile potrzebuje do wykonywania swoich zadań. Na przykład możesz zmienić kolejność kolumn, a także grupować dane w dowolny sposób. Można to zrobić tyle razy, ile potrzebuje użytkownik. I zajmuje to dosłownie mniej niż minutę.
3. Łatwe formatowanie danych. Bardzo łatwo jest zaaranżować stół przestawny tak, jak chce tego dana osoba. Aby to zrobić, wystarczy kilka kliknięć myszką.
4. Uzyskiwanie wartości. Ogromna liczba formuł używanych do tworzenia raportów znajduje się w bezpośrednim dostępie osoby i można je łatwo zintegrować z tabelą przestawną. Są to dane takie jak sumowanie, uzyskiwanie średniej arytmetycznej, określanie liczby komórek, mnożenie, znajdowanie największych i najmniejszych wartości w określonej próbce.
5. Możliwość tworzenia wykresów podsumowujących. Jeśli tabele przestawne zostaną ponownie obliczone, skojarzone z nimi wykresy zostaną automatycznie zaktualizowane. Możliwe jest stworzenie tylu wykresów, ile potrzebujesz. Wszystkie można zmienić na konkretne zadanie i nie będą ze sobą połączone.
6. Możliwość filtrowania danych.
7. Możliwe jest zbudowanie tabeli przestawnej na podstawie więcej niż jednego zestawu informacji źródłowych. Dlatego ich funkcjonalność stanie się jeszcze większa.

To prawda, że ​​podczas korzystania z tabel przestawnych należy wziąć pod uwagę następujące ograniczenia:

1. Nie wszystkie informacje można wykorzystać do wygenerowania tabel przestawnych. Zanim będą mogły być użyte do tego celu, komórki muszą zostać znormalizowane. W prostych słowach, zrób to dobrze. Wymagania obowiązkowe: obecność linii nagłówka, wypełnienie wszystkich linii, równość formatów danych.
2. Dane są aktualizowane półautomatycznie. Aby uzyskać nowe informacje w tabeli przestawnej, musisz kliknąć specjalny przycisk.
3. Stoły obrotowe zajmują dużo miejsca. Może to prowadzić do zakłóceń działania komputera. Z tego powodu plik będzie trudny do wysłania pocztą e-mail.

Ponadto po utworzeniu tabeli przestawnej użytkownik nie ma możliwości dodawania nowych informacji.