Functia TRANSPOSE
După copierea unei zone continue de celule, Excel are opțiunea ”Paste Special Transpose”, care comută rândurile copiate în coloane.
Această opțiune nu este dinamică: dacă se modifică valorile din zona copiată (sursă), valorile din zona destinație rămân neschimbate.
Pentru ca valorile să se modifice dinamic, se poate folosi funcția TRANSPOSE.
Funcția matrice TRANSPOSE ”comută/modifică” orientarea unei zone de celule - din vertical în orizontal sau invers. Funcția TRANSPOSE se introduce ca formulă matrice într-o zonă care are același număr de rânduri și coloane ca intervalul sursă.
De exemplu, dacă zona sursă are R rânduri și C coloane, zona destinație (selectată) va avea C rânduri și R coloane.
Spre deosebire de opțiunea ”Paste Special Transpose”, unde se poate selecta doar o celulă destinație, funcția cere selectarea întregii zone în care vor fi transpuse valorile, altfel va prelua numai valorile care ”încap” în zona selectată.
Sintaxă
=TRANSPOSE (array)
unde array este zona de celule care va fi transpusă.
Când zona selectată în array este transpusă, primul rând devine prima coloană, al doilea rând devine a doua coloană etc.
Dacă se modifică valoarea celulelor din zona sursă, automat va fi modificată și valoarea celulelor corespunzătoare din zona destinație.
Mod de lucru
Dacă zona sursă are R rânduri și C coloane, se selectează zona destinație - C rânduri și R coloane
Se tastează =TRANSPOSE(
Se selectează zona sursă, apoi se închide paranteza )
Se apasă tastele Ctrl+Shift+Enter. Excel va adăuga automat acolade înainte și după formulă.
Acoladele sunt modul în care Excel recunoaște o formulă matrice. Nu pot fi introduse manual, ele sunt adăugate automat, apăsând CTRL + SHIFT + ENTER.
Pentru a șterge formula matrice, se selectează cel puțin zona matricei și se apasă Delete.
Dacă se selectează doar o parte a zonei, Excel afișează mesajul:
Tabel transpus fără zerouri
Funcția TRANSPOSE din Excel convertește celulele goale în zerouri. Pentru a păstra celulele goale, se folosește funcția IF. Dacă o celulă este necompletată, funcția IF returnează un șir gol (două ghilimele duble fără nimic între ele), care este preluată de funcția TRANSPOSE.
Un altfel de Transpose
Opțiunea „Paste Special Transpose” este o modalitate excelentă de a transpune date, dar celulele sursă nu modifică dinamic celulele țintă, așa cum se întâmplă la Paste Link. Mai ales în care tabelul este mare, este destul de dificil să se facă Copy-Paste Link pentru fiecare celulă în parte.
Pentru a face posibilă modificarea dinamică în tabelul țintă, este nevoie de câteva trucuri.
Mai întâi se copie tabelul (aici, zona B2:C6) și se lipește într-o altă zonă din foaia de lucru (cu opțiunea Paste Link).
În zona destinație (B8:C12), fiecare celulă va conține câte o legătură (link) la valoarea din zona sursă (semnul = și adresa celulei din care preia valoarea).
Deocamdată tabelul țintă este identic cu cel sursă (nu este transpus).
Înainte de a transpune tabelul nou obținut, se selectează tabelul țintă, se deschide caseta Find and Replace (Ctrl+H) și se înlocuiește semnul = cu câteva caractere care nu se regăsesc în tabel, de exemplu ZZZ.
Acum tabelul se poate copia și transpune cu opțiunea „Paste Special Transpose”.
Excel va prelua valorile din zona B8:C12 și le va transpune în zona aleasă - aici am selectat doar celula E8, iar Excel a completat automat valorile în celulele de la dreapta celulei E8 și de sub ea (două rânduri și 5 coloane).
La final, se selectează tabelul transpus, se deschide iar caseta Find and Replace și se face operațiunea inversă: înlocuirea ZZZ cu semnul =.
Astfel, în fiecare celulă se revine la formula inițială, cea dinaintea transpunerii. Celulele transpuse sunt legate la celulele sursă: modificarea unei valori într-o celulă sursă duce la modificarea automată a valorii din celula țintă.
Fiecare celulă din tabelul transpus conține câte o legătură la tabelul sursă și poate fi mutat sau preluat (cu Cut - Paste) în altă locație.
Mutarea unui tabel se face prin selectarea celulelor, plasarea cursorului de mouse deasupra bordurii (verzi a) selecției, iar când peste cursor apare semnul plus cu săgeți la capete, se poate face mutatea celulelor cu click&drag. În timpul mutării, aceasta nu se poate anula interactiv. Dar după mutare, ea se poate anula cu Ctrl+Z (Undo).
Doar tabelele transpuse cu opțiunea „Paste Special Transpose” păstrează formatarea tabelelor sursă.