Ce face funcția Transpose în Excel?
Funcția de transpunere în Excel este utilizată pentru a roti valorile sau pentru a comuta valorile de la rânduri la coloane și coloane la rânduri, deoarece această funcție ia o serie de celule ca argument, astfel încât să o executăm trebuie să apăsăm CSE sau Control + Shift + Enter, mai întâi selectați dimensiunea exactă a intervalului care trebuie transpus și apoi utilizați formula de transpunere.
Sintaxă
Mai jos este formula TRANSPOSE în excel.
Argumente
matrice: Obligatoriu. O gamă de celule pe care doriți să le transpuneți.
Este o funcție matrice .
Cum se utilizează funcția TRANSPOSE în Excel? (cu exemple)
Exemplul nr. 1
Datele originale pe care doriți să le transpuneți sunt în format A3: B8.
- Pasul 1: Selectați intervalul în care doriți valoarea transpusă, aici, D6: I7.
- Pasul 2: Acum, tastați formula TRANSPOSE în regiunea selectată și apăsați CTRL + SHIFT + ENTER (sau COMMAND + SHIFT + ENTER în Mac).
= TRANSPUNE (A3: B7)
Veți obține ieșirea transpusă în D6: I7, așa cum se arată mai jos.
În aceasta, D3: I4 este ieșirea transpusă copiere-lipire, iar D6: I7 este ieșirea transpusă.
Observați că funcția Excel TRANSPOSE este
(= TRANSPOSE (A3: B7))
Acoladele () arătate mai sus indică faptul că funcția a fost introdusă ca o formulă de matrice.
Dacă trebuie să modificați orice valoare din datele originale, spuneți B8 = SUM (B3: B7), ieșirea transpusă, unde ați utilizat TRANSPOSE, va fi actualizată automat, dar nu și în cazul în care ați utilizat copia- opțiunea de lipire.
De asemenea, poate fi utilizat împreună cu alte funcții pentru a obține ieșirea dorită. Să vedem câteva exemple în acest sens.
Exemplul nr. 2
Să presupunem că aveți o listă de studenți și notele lor, așa cum se arată mai jos. Unii dintre studenți nu au dat examene. Prin urmare, coloana de note este lăsată necompletată pentru acei studenți.
Doriți să transpuneți aceste date și, atunci când celula este goală, doriți să puneți „Absent” în acea celulă.
Puteți face acest lucru folosind Transpose împreună cu funcția IF în excel. Puteți utiliza următoarea formulă TRANSPOSE în Excel pentru a face acest lucru:
= TRANSPUNE (IF (B3: D10 = ””, ”ABSENT”, B3: D10))
„IF (B3: D10 =” “,“ ABSENT ”” înseamnă că, dacă orice celulă din intervalul B3: D10 este goală, va plasa „Absent” în loc. Va transpune intervalul dat.
Să presupunem că în această listă, dacă un student a obținut mai puțin de 70 de note sau nu a participat la examen este considerat FALS, puteți schimba Formula TRANSPOSE astfel:
= TRANSPOSE (IF (B3: D10 <70, „FAIL”, B3: D10)
și veți obține rezultatul final, așa cum se arată mai jos.
Exemplul nr. 3
Uneori este nevoie să adăugați câteva caractere la cele existente împreună cu transpunerea. Să presupunem că aveți o listă de ID-uri în B4: B7, așa cum se arată mai jos.
Doriți să transpuneți ID-urile și să adăugați prefixul „ID” la acesta. Puteți face acest lucru folosind Formula în excel:
= TRANSPUNE („ID” și B4: B7)
Aceasta va transpune datele și va adăuga prefixul la fiecare conținut al celulei.
De asemenea, puteți adăuga orice valoare ca sufix folosind formula TRANSPOSE de mai sus. Acest lucru poate fi util atunci când utilizați funcția Transpose pentru a concatena cuvintele date în rânduri diferite într-o singură celulă. Pentru aceasta, puteți urma următorii pași:
- Tastați sintaxa: CONCATENATE (TRANSPOSE (B4: B7 & ”,”). Nu apăsați Enter.
- Selectați funcția de transpunere excel, așa cum se arată mai jos.
- Apăsați tasta F9.
- Eliminați () din formulă.
- Apasa Enter.
Va concatena celulele într-o singură celulă.
Exemplul # 4
Să presupunem că aveți o bibliotecă de date în care mai multe subiecte sunt aranjate pe un raft și că există rafturi diferite, așa cum se arată mai jos.
Poate doriți să recuperați locația oricăruia dintre subiecți (menționată în H3: H11) folosind aceste date (B4: E6).
Puteți face acest lucru folosind sintaxa
= INDEX ($ B $ 4: $ B $ 6, MATCH (1, MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (CULOANĂ ($ C $ 4: $ E $ 6) 0)), 0))
Să vedem cum funcționează această formulă TRANSPOSE în excel.
- - ($ C $ 4: $ E $ 6 =)
Când este dat în H3, va fi - ($ C $ 4: $ E $ 6 = H3).
Aceasta formează o matrice de 1 și 0 pentru a indica prezența sau absența valorii. Pentru Biologie în H3, va forma matricea ca: (1,0,0; 0,0,0; 0,0,0)
- TRANSPUNE (CULOANĂ ($ C $ 4: $ E $ 6) 0))
Se creează o matrice de 3 rânduri într-o coloană, iar o putere de 0 asigură faptul că numerele sunt convertite în 1. Ieșirea acestei funcții este (1,1,1)
- MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPUNE (CULOANĂ ($ C $ 4: $ E $ 6) 0))
MMULT în excel înmulțește ieșirea lui A și B.
MMULT ((1,0,0; 0,0,0; 0,0,0), (1,1,1)) dă ieșirea (1; 0; 0)
- MATCH (1, MMULT (), 0)
Se va potrivi cu ieșirea lui C cu 1. MATCH (1, (1; 0; 0), 0) returnează poziția 1.
- INDEX ($ B $ 4: $ B $ 6, MATCH (, 0))
Acesta va identifica valoarea celulei pentru care funcția de potrivire a specificat poziția. INDEX ($ B $ 4: $ B $ 6, 1) va returna A1.
În mod similar pentru „Geografie” ca subiect, va fi:
- - ($ C $ 4: $ E $ 6 = D6) returnează (0,0,0; 0,0,0; 0,1,0)
- TRANSPUNE (CULOANĂ ($ C $ 4: $ E $ 6) 0)) repune înapoi (1,1,1)
- MMULT ((0,0,0; 0,0,0; 0,1,0), (1,1,1)) returnează (0; 0; 1)
- MATCH (1, (0; 0; 1), 0) returnează 3
- INDEX ($ B $ 4: $ B $ 6, 3) returnează A3.
Lucruri de amintit
- Opțiunea de copiere-lipire creează duplicate.
- Această funcție leagă datele de sursă.
- Dacă numărul de rânduri și coloane selectate nu este egal cu coloanele și rândurile datelor sursă, va da o eroare #VALUE.
- Odată ce a fost introdus, orice celulă individuală, care face parte din această funcție, nu poate fi modificată.
- Apăsați CTRL + SHIFT + ENTER pentru a introduce formula în excel.
