Funzione TRANSPOSE di Excel

Cosa fa la funzione di trasposizione in Excel?

La funzione di trasposizione in Excel viene utilizzata per ruotare i valori o passare i valori da righe a colonne e colonne a righe, poiché questa funzione accetta un array di celle come argomento, quindi per eseguirlo dobbiamo premere CSE o Control + Maiusc + Invio, selezionare prima la dimensione esatta dell'intervallo che deve essere trasposto e quindi utilizzare la formula di trasposizione.

Sintassi

Di seguito è riportata la formula TRANSPOSE in excel.

argomenti

array: obbligatorio. Un intervallo di celle che desideri trasporre.

È una funzione di matrice .

Come utilizzare la funzione TRANSPOSE in Excel? (con esempi)

Puoi scaricare questo modello Excel di funzione TRANSPOSE qui - Modello Excel di funzione TRANSPOSE

Esempio 1

I dati originali che vuoi trasporre sono in A3: B8.

  • Passaggio 1: seleziona l'intervallo in cui desideri il valore trasposto, qui, D6: I7.

  • Passaggio 2: Ora, digita la formula TRANSPOSE nella regione selezionata e premi CTRL + MAIUSC + INVIO (o COMANDO + MAIUSC + INVIO in Mac).

= TRASPOSIZIONE (A3: B7)

Otterrai l'uscita trasposta in D6: I7 come mostrato di seguito.

In questo, D3: I4 è l'uscita trasposta copia-incolla e D6: I7 è l'uscita trasposta.

Si noti che la funzione TRANSPOSE di Excel è

{= TRANSPOSE (A3: B7)}

Le parentesi graffe {} mostrate sopra indicano che la funzione è stata immessa come formula di matrice.

Ora, se devi cambiare qualsiasi valore nei dati originali, dì B8 = SUM (B3: B7) l'uscita trasposta, dove hai usato il TRANSPOSE, verrà automaticamente aggiornata, ma non nel caso in cui hai usato copy- opzione incolla.

Può anche essere utilizzato insieme ad altre funzioni per ottenere l'output desiderato. Vediamo alcuni esempi di questo.

Esempio n. 2

Supponi di avere un elenco di studenti e dei loro voti come mostrato di seguito. Alcuni degli studenti non hanno dato esami. Quindi, la colonna dei voti viene lasciata vuota per quegli studenti.

Vuoi trasporre questi dati e, quando la cella è vuota, vuoi inserire "Assente" in quella cella.

Puoi farlo usando Transpose insieme alla funzione IF in Excel. Puoi utilizzare la seguente formula TRANSPOSE in Excel per farlo:

= TRASPORTO (SE (B3: D10 = "", "ASSENTE", B3: D10))

'IF (B3: D10 = ”“, “ASSENTE”' significa che se una qualsiasi cella nell'intervallo B3: D10 è vuota, inserirà invece “Assente” e trasporrà l'intervallo dato.

Supponiamo che in questo elenco, se uno studente ha ottenuto meno di 70 punti o non si è presentato all'esame è considerato FALLITO, è possibile modificare la formula TRANSPOSE come:

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

e otterrai l'output finale come mostrato di seguito.

Esempio n. 3

A volte è necessario aggiungere alcuni caratteri a quelli esistenti insieme alla trasposizione. Supponi di avere un elenco di ID in B4: B7 come mostrato di seguito.

Si desidera trasporre gli ID e aggiungere il prefisso "ID" ad esso. Puoi farlo usando la Formula in Excel:

= TRANSPOSE ("ID" e B4: B7)

Questo trasporrà i dati e aggiungerà il prefisso a ciascuno dei contenuti della cella.

Puoi anche aggiungere qualsiasi valore come suffisso utilizzando la formula TRANSPOSE sopra. Ciò può essere utile quando si utilizza la funzione Transpose per concatenare le parole fornite in righe diverse in una singola cella. Per fare ciò, puoi seguire i seguenti passaggi:

  • Digita la sintassi: CONCATENATE (TRANSPOSE (B4: B7 & ","). Non premere Invio.
  • Seleziona la funzione di trasposizione di Excel come mostrato di seguito.

  • Premere il tasto F9.

  • Rimuovi {} dalla formula

  • Premere Invio.

Concatenerà le celle in una singola cella.

Esempio n. 4

Supponiamo di avere una libreria di dati in cui i diversi argomenti sono disposti su uno scaffale e ci sono diversi scaffali come mostrato di seguito.

Potresti voler recuperare la posizione di uno qualsiasi dei soggetti (forniti in H3: H11) utilizzando questi dati (B4: E6).

Puoi farlo usando la sintassi

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

Vediamo come funziona questa TRANSPOSE Formula in Excel.

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

Quando è dato in H3, sarà - ($ C $ 4: $ E $ 6 = H3).

Questo forma una matrice di 1 e 0 per indicare la presenza o l'assenza del valore. Per la biologia in H3, formerà la matrice come: {1,0,0; 0,0,0; 0,0,0}

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

Crea un array di 3 righe in una colonna e una potenza di 0 assicura che i numeri vengano convertiti in 1. L'output di questa funzione è {1,1,1}

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

L'MMULT in Excel moltiplica l'output di A e B.

MMULT ({1,0,0; 0,0,0; 0,0,0}, {1,1,1}) restituisce l'output {1; 0; 0}

  • PARTITA (1, MMULT (), 0)

Corrisponderà all'output di C con 1. MATCH (1, {1; 0; 0}, 0) restituisce la posizione 1.

  • INDICE ($ B $ 4: $ B $ 6, CORRISPONDENZA (, 0))

Identificherà il valore della cella per la quale la funzione di corrispondenza ha specificato la posizione. INDICE ($ B $ 4: $ B $ 6, 1) restituirà A1.

Allo stesso modo per "Geografia" come soggetto, sarà:

  1. - ($ C $ 4: $ E $ 6 = D6) restituisce {0,0,0; 0,0,0; 0,1,0}
  2. TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0)) ripete {1,1,1}
  3. MMULT ({0,0,0; 0,0,0; 0,1,0}, {1,1,1}) restituisce {0; 0; 1}
  4. CONFRONTA (1, {0; 0; 1}, 0) restituisce 3
  5. INDICE ($ B $ 4: $ B $ 6, 3) restituisce A3.

Cose da ricordare

  1. L'opzione copia-incolla crea duplicati.
  2. Questa funzione collega i dati alla sorgente.
  3. Se il numero di righe e colonne selezionate non è uguale alle colonne e alle righe dei dati di origine, verrà visualizzato un errore #VALORE
  4. Una volta che è stato inserito, qualsiasi singola cella che fa parte di questa funzione non può essere modificata.
  5. Premi CTRL + MAIUSC + INVIO per inserire la Formula in Excel.