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.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__2.jpg.webp)
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)
Przykład 1
Oryginalne dane, które chcesz przetransponować, znajdują się w formacie A3: B8.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__3.jpg.webp)
- Krok 1: Wybierz zakres, w którym chcesz transponować wartość, tutaj, D6: I7.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__4.jpg.webp)
- Krok 2: Teraz wpisz formułę TRANSPOSE w wybranym regionie i naciśnij CTRL + SHIFT + ENTER (lub COMMAND + SHIFT + ENTER na Macu).
= TRANSPONUJ (A3: B7)
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__5.jpg.webp)
Otrzymasz transponowane wyjście w D6: I7, jak pokazano poniżej.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__6.jpg.webp)
W tym przypadku D3: I4 jest wyjściem transpozycji kopiuj-wklej, a D6: I7 jest wyjściem transpozycji.
Zauważ, że funkcja Excel TRANSPOSE to
(= TRANSPONUJ (A3: B7))
Przedstawione powyżej nawiasy klamrowe () wskazują, że funkcja została wprowadzona jako formuła tablicowa.
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ś kopii- opcja wklejania.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__7.jpg.webp)
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 ocen, jak pokazano poniżej. Część uczniów nie zdawała egzaminów. W związku z tym kolumna ocen jest pusta dla tych uczniów.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__8.jpg.webp)
Chcesz przetransponować te dane, a gdy komórka jest pusta, chcesz umieścić w niej „Brak”.
Możesz to zrobić używając 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.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__9.jpg.webp)
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.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__10.jpg.webp)
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.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__11.jpg.webp)
Chcesz przetransponować identyfikatory i dodać do niego przedrostek „ID”. Możesz to zrobić za pomocą Formuły w programie Excel:
= TRANSPONUJ („ID” & B4: B7)
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__12.jpg.webp)
Spowoduje to transpozycję danych i dodanie przedrostka do każdej zawartości komórki.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__13.jpg.webp)
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.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__14.jpg.webp)
- Naciśnij klawisz F9.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__15.jpg.webp)
- Usuń () z formuły.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__16.jpg.webp)
- Naciśnij enter.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__17.jpg.webp)
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.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__18.jpg.webp)
Możesz chcieć pobrać lokalizację któregokolwiek z badanych (podaną w H3: H11), korzystając z tych danych (B4: E6).
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__19.jpg.webp)
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.
![](https://cdn.know-base.net/4901601/excel_transpose_function_how_to_use_transpose_formula__20.jpg.webp)
Podobnie w przypadku tematu „Geografia” będzie to:
- - ($ C $ 4: $ E $ 6 = D6) zwraca (0,0,0; 0,0,0; 0,1,0)
- TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) 0)) retuns (1,1,1)
- MMULT ((0,0,0; 0,0,0; 0,1,0), (1,1,1)) zwraca (0; 0; 1)
- PODAJ.POZYCJĘ (1; (0; 0; 1); 0) zwraca 3
- INDEX ($ B $ 4: $ B $ 6, 3) zwraca A3.
Rzeczy do zapamiętania
- Opcja kopiuj-wklej tworzy duplikaty.
- Ta funkcja łączy dane ze źródłem.
- Jeśli liczba wybranych wierszy i kolumn nie jest równa liczbie kolumn i wierszy danych źródłowych, zostanie wyświetlony błąd #VALUE.
- Po jej wprowadzeniu nie można zmienić żadnej pojedynczej komórki, która jest częścią tej funkcji.
- Naciśnij CTRL + SHIFT + ENTER, aby wprowadzić formułę w programie Excel.