photosdematures.com

Què fa la funció Transpose a Excel?

La funció de transposició a Excel s’utilitza per girar els valors o canviar els valors de files a columnes i columnes a files, ja que aquesta funció pren una matriu de cel·les com a argument, de manera que per executar-la hem de prémer CSE o Control + Maj + Retorn, primer seleccioneu la mida exacta de l'interval que cal transposar i després utilitzeu la fórmula de transposició.

Sintaxi

A continuació es mostra la fórmula TRANSPOSE en excel.

Arguments

matriu: Obligatori. Un rang de cel·les que voleu transposar.

És un funció de matriu.

Com s'utilitza la funció TRANSPOSE a Excel? (amb exemples)

Podeu descarregar aquesta plantilla Excel de funció TRANSPOSE aquí - Plantilla Excel de funció TRANSPOSE

Exemple 1

Les dades originals que voleu transposar es troben en format A3: B8.

• Pas 1: Seleccioneu l'interval on vulgueu que es transposi el valor aquí, D6: I7.

• Pas 2: Ara, escriviu la fórmula TRANSPOSE a la regió seleccionada i premeu CTRL + MAJÚS + ENTRAR (o ORDRE + MAJÚS + ENTRAR a Mac).

= TRANSPOSE (A3: B7)

Obtindreu la sortida transposada a D6: I7 com es mostra a continuació.

En això, D3: I4 és la sortida transposada de còpia-enganxa i D6: I7 és la sortida transposada.

Tingueu en compte que la funció Excel TRANSPOSE és

{= TRANSPOSE (A3: B7)}

Els claus {} que es mostren a dalt indiquen que la funció s'ha introduït com a fórmula de matriu.

Ara, si heu de canviar qualsevol valor de les dades originals, digueu B8 = SUM (B3: B7) la sortida transposada, on heu utilitzat el TRANSPOS, s'actualitzarà automàticament, però no en el cas que hàgiu utilitzat còpia- opció d'enganxar.

També es pot utilitzar juntament amb altres funcions per obtenir la sortida desitjada. Vegem alguns exemples d'això.

Exemple 2

Suposem que teniu una llista d’estudiants i les seves notes tal com es mostra a continuació. Alguns dels estudiants no van fer exàmens. Per tant, la columna de marques es deixa en blanc per a aquells estudiants.

Voleu transposar aquestes dades i, quan la cel·la estigui buida, voleu posar "Absent" a aquesta cel·la.

Podeu fer-ho utilitzant Transpose juntament amb la funció IF en excel. Per fer-ho, podeu utilitzar la fórmula TRANSPOSE següent:

= TRANSPOSE (SI (B3: D10 = "", "ABSENT", B3: D10))

"IF (B3: D10 =" "," ABSENT "'significa que si alguna cel·la de l'interval B3: D10 està buida, col·locarà" Absent "en el seu lloc. Transposarà l'interval donat.

Suposem que en aquesta llista, si algun estudiant ha obtingut menys de 70 notes o no ha aparegut a l'examen es considera FALLAT, podeu canviar la fórmula TRANSPOSE com:

= TRANSPOSE (IF (B3: D10 <70, "FAIL", B3: D10)

i obtindreu la sortida final com es mostra a continuació.

Exemple 3

De vegades cal afegir alguns caràcters als existents juntament amb transposar. Suposem que teniu una llista d'identificadors a B4: B7 com es mostra a continuació.

Voleu transposar els identificadors i afegir-hi el prefix "ID". Podeu fer-ho mitjançant la fórmula Excel:

= TRANSPOSE ("ID" i B4: B7)

Això transposarà les dades i afegirà el prefix a cadascun dels continguts de la cel·la.

També podeu afegir qualsevol valor com a sufix mitjançant la fórmula TRANSPOSE anterior. Això pot ser útil quan s'utilitza la funció Transposar per concatenar les paraules donades en diferents files en una sola cel·la. Per fer-ho, podeu seguir els passos següents:

• Escriviu la sintaxi: CONCATENATE (TRANSPOSE (B4: B7 & ”,”). No premeu Retorn.
• Seleccioneu la funció de transposició excel com es mostra a continuació.

• Premeu la tecla F9.

• Elimina {} de la fórmula

• Premeu Retorn.

Concatenarà les cel·les en una sola cel·la.

Exemple # 4

Suposem que teniu dades de la biblioteca on els diversos temes estan disposats en un prestatge i que hi ha diferents prestatges, tal com es mostra a continuació.

És possible que vulgueu recuperar la ubicació de qualsevol dels subjectes (indicats a H3: H11) mitjançant aquestes dades (B4: E6).

Podeu fer-ho mitjançant la sintaxi

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

Vegem com funciona aquesta fórmula TRANSPOSE en excel.

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

Quan es dóna a H3, serà - ($ C $ 4: $ E $ 6 = H3).

Això forma una matriu d'1 i 0 per indicar la presència o l'absència del valor. Per a Biology in H3, formarà la matriu com a: {1,0,0; 0,0,0; 0,0,0}

• TRANSPOSEU (COLUMNA ($ C $ 4: $ E $ 6) ^ 0))

Crea una matriu de 3 files en una columna i una potència de 0 assegura que els números es converteixin en 1. La sortida d'aquesta funció és {1,1,1}

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

El MMULT en excel multiplica la sortida d’A i B.

MMULT ({1,0,0; 0,0,0; 0,0,0}, {1,1,1}) dóna la sortida {1; 0; 0}

• PARTIT (1, MMULT (), 0)

Coincidirà amb la sortida de C amb 1. MATCH (1, {1; 0; 0}, 0) retorna la posició 1.

• ÍNDEX ($ B $ 4: $ B $ 6, MATCH (, 0))

Identificarà el valor de la cel·la per a la qual la funció de concordança va especificar la posició. INDEX ($ B $ 4: $ B $ 6, 1) retornarà A1.

De manera similar per a "Geografia" com a tema, serà:

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

Coses que cal recordar

1. L’opció de copiar i enganxar crea duplicats.
2. Aquesta funció enllaça les dades amb la font.
3. Si el nombre de files i columnes seleccionades no és igual a les columnes i files de les dades d'origen, es produirà un error #VALUE
4. Un cop introduïda, no es pot canviar cap cel·la que formi part d'aquesta funció.
5. Premeu CTRL + MAJÚS + ENTRAR per introduir la fórmula en excel.