Funkcja Excel TRANSPOSE

Co robi funkcja transpozycji w programie Excel?

Funkcja transpozycji w programie Excel służy do obracania wartości lub przełączania wartości z wierszy na kolumny i kolumny na wiersze, ponieważ ta funkcja przyjmuje tablicę komórek jako argument, więc aby ją wykonać, musimy nacisnąć CSE lub Control + Shift + Enter, najpierw wybierz dokładny rozmiar zakresu, który ma zostać przetransponowany, a następnie użyj formuły transpozycji.

Składnia

Poniżej znajduje się formuła TRANSPOSE w programie Excel.

Argumenty

tablica: wymagana. Zakres komórek, które chcesz przetransponować.

Jest to funkcja tablicowa .

Jak korzystać z funkcji TRANSPOSE w programie Excel? (z przykładami)

Możesz pobrać ten szablon programu Excel z funkcją TRANSPOSE tutaj - szablon programu Excel z funkcją TRANSPOSE

Przykład 1

Oryginalne dane, które chcesz przetransponować, znajdują się w formacie A3: B8.

  • Krok 1: Wybierz zakres, w którym chcesz transponować wartość, tutaj, D6: I7.

  • Krok 2: Teraz wpisz formułę TRANSPOSE w wybranym regionie i naciśnij CTRL + SHIFT + ENTER (lub COMMAND + SHIFT + ENTER na Macu).

= TRANSPONUJ (A3: B7)

Otrzymasz transponowane wyjście w D6: I7, jak pokazano poniżej.

W tym przypadku D3: I4 jest wyjściem transponowanym w trybie kopiuj-wklej, a D6: I7 jest wyjściem transpozycji.

Zauważ, że funkcja Excel TRANSPOSE to

{= TRANSPOSE (A3: B7)}

Przedstawione powyżej nawiasy klamrowe {} wskazują, że funkcja została wprowadzona jako formuła tablicowa.

Teraz, jeśli musisz zmienić jakąkolwiek wartość w oryginalnych danych, powiedzmy B8 = SUMA (B3: B7) transponowane wyjście, w którym użyłeś TRANSPOSE, zostanie automatycznie zaktualizowane, ale nie w przypadku, gdy użyłeś kopiowania- opcja wklejania.

Można go również używać razem z innymi funkcjami, aby uzyskać żądane wyjście. Zobaczmy kilka przykładów.

Przykład nr 2

Załóżmy, że masz listę uczniów i ich oceny, jak pokazano poniżej. Część uczniów nie zdawała egzaminów. Dlatego kolumna ocen jest pusta dla tych uczniów.

Chcesz przetransponować te dane, a gdy komórka jest pusta, chcesz umieścić w niej „Brak”.

Możesz to zrobić za pomocą Transpose wraz z funkcją IF w programie Excel. W tym celu możesz użyć następującej formuły TRANSPOSE w programie Excel:

= TRANSPONUJ (JEŻELI (B3: D10 = ””, „NIEOBECNY”, B3: D10))

„JEŻELI (B3: D10 =” „,„ NIEOBECNY ”” oznacza, że ​​jeśli jakakolwiek komórka z zakresu B3: D10 jest pusta, zamiast tego umieści „Brak”. Przeniesie podany zakres.

Załóżmy, że na tej liście, jeśli którykolwiek uczeń uzyskał mniej niż 70 punktów lub nie pojawił się na egzaminie, jest uważany za FAIL, możesz zmienić Formułę TRANSPOSE na:

= TRANSPONUJ (JEŻELI (B3: D10 <70; „NIEPOWODZENIE”; B3: D10)

a otrzymasz ostateczny wynik, jak pokazano poniżej.

Przykład nr 3

Czasami zachodzi potrzeba dodania kilku znaków do istniejących wraz z transpozycją. Załóżmy, że masz listę identyfikatorów w B4: B7, jak pokazano poniżej.

Chcesz przetransponować identyfikatory i dodać do niego przedrostek „ID”. Możesz to zrobić za pomocą Formuły w programie Excel:

= TRANSPONUJ („ID” & B4: B7)

Spowoduje to transpozycję danych i dodanie przedrostka do każdej zawartości komórki.

Możesz również dodać dowolną wartość jako sufiks, używając powyższego wzoru TRANSPOSE. Może to być pomocne, gdy używasz funkcji Transpose do łączenia słów podanych w różnych wierszach w pojedynczą komórkę. Aby to zrobić, możesz wykonać następujące czynności:

  • Wpisz składnię: CONCATENATE (TRANSPOSE (B4: B7 & ”,”). Nie naciskaj klawisza Enter.
  • Wybierz funkcję transpozycji programu Excel, jak pokazano poniżej.

  • Naciśnij klawisz F9.

  • Usuń {} z formuły

  • Naciśnij enter.

Połączy komórki w jednej komórce.

Przykład 4

Załóżmy, że masz dane biblioteczne, w których kilka przedmiotów jest rozmieszczonych na półce i są różne półki, jak pokazano poniżej.

Możesz chcieć pobrać lokalizację któregokolwiek z badanych (podaną w H3: H11), korzystając z tych danych (B4: E6).

Możesz to zrobić za pomocą składni

= INDEKS ($ B $ 4: $ B 6 $, MATCH (1, MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (KOLUMNA ($ C $ 4: $ E $ 6) ^ 0)), 0))

Zobaczmy, jak działa ta formuła TRANSPOSE w programie Excel.

  • - (4 $ C $: 6 $ E $ =)

Kiedy jest podane w H3, będzie to - (C $ 4: $ E $ 6 = H3).

Tworzy to tablicę 1 i 0, aby wskazać obecność lub brak wartości. Dla Biology in H3 utworzy tablicę jako: {1,0,0; 0,0,0; 0,0,0}

  • TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0))

Tworzy tablicę 3 wierszy w kolumnie, a potęga 0 zapewnia, że ​​liczby są konwertowane na 1. Wynik tej funkcji to {1,1,1}

  • MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (KOLUMNA ($ C $ 4: $ E $ 6) ^ 0))

MMULT w programie Excel mnoży wynik A i B.

MMULT ({1,0,0; 0,0,0; 0,0,0}, {1,1,1}) daje wynik {1; 0; 0}

  • PODAJ.POZYCJĘ (1; MMULT (); 0)

Dopasuje wyjście C z 1. MATCH (1, {1; 0; 0}, 0) zwraca pozycję 1.

  • INDEKS ($ B $ 4: $ B $ 6, PODAJ.POZYCJĘ (, 0))

Zidentyfikuje wartość komórki, dla której funkcja dopasowania określiła pozycję. INDEKS ($ B $ 4: $ B $ 6, 1) zwróci A1.

Podobnie w przypadku tematu „Geografia” będzie to:

  1. - ($ C $ 4: $ E $ 6 = D6) zwraca {0,0,0; 0,0,0; 0,1,0}
  2. TRANSPOSE (COLUMN (4 $ C $: $ E $ 6) ^ 0)) wraca {1,1,1}
  3. MMULT ({0,0,0; 0,0,0; 0,1,0}, {1,1,1}) zwraca {0; 0; 1}
  4. MATCH (1; {0; 0; 1}; 0) zwraca 3
  5. INDEX ($ B $ 4: $ B $ 6, 3) zwraca A3.

Rzeczy do zapamiętania

  1. Opcja kopiuj-wklej tworzy duplikaty.
  2. Ta funkcja łączy dane ze źródłem.
  3. Jeśli liczba wybranych wierszy i kolumn nie jest równa liczbie kolumn i wierszy danych źródłowych, wystąpi błąd #VALUE
  4. Po wprowadzeniu nie można zmienić żadnej pojedynczej komórki wchodzącej w skład tej funkcji.
  5. Naciśnij klawisze CTRL + SHIFT + ENTER, aby wprowadzić formułę w programie Excel.