Funcția de transpunere în Excel

Din când în când, un utilizator Excel poate avea sarcina de a transforma o serie de date care au o structură orizontală într-una verticală. Acest proces se numește transpunere. Acest cuvânt este nou pentru majoritatea oamenilor, deoarece în funcționarea normală la computer nu trebuie să utilizați această operațiune. Cu toate acestea, cei care trebuie să lucreze cu cantități mari de date trebuie să știe cum să o facă. Astăzi vom vorbi mai detaliat despre cum să-l îndeplinim, cu ce funcție și vom analiza în detaliu alte metode.

Funcția TRANSPOSE – transpune intervalele de celule în Excel

Una dintre cele mai interesante și funcționale metode de transpunere a tabelelor din Excel este funcția TRANSP. Cu ajutorul acestuia, puteți transforma un interval de date orizontal într-unul vertical sau puteți efectua operația inversă. Să vedem cum să lucrăm cu el.

Sintaxa funcției

Sintaxa pentru această funcție este incredibil de simplă: TRANSPOSE(matrice). Adică, trebuie să folosim un singur argument, care este un set de date care trebuie convertit într-o vedere orizontală sau verticală, în funcție de ceea ce a fost inițial.

Transpunerea intervalelor verticale de celule (coloane)

Să presupunem că avem o coloană cu intervalul B2:B6. Ele pot conține atât valori gata făcute, cât și formule care returnează rezultate acestor celule. Nu este atât de important pentru noi, transpunerea este posibilă în ambele cazuri. După aplicarea acestei funcții, lungimea rândului va fi aceeași cu lungimea coloanei intervalului original.

Secvența de pași pentru utilizarea acestei formule este următoarea:

1. Selectați o linie. În cazul nostru, are o lungime de cinci celule.
2. După aceea, mutați cursorul pe bara de formule și introduceți formula acolo =TRANSP(B2:B6).
3. Apăsați combinația de taste Ctrl + Shift + Enter.

Desigur, în cazul dvs., trebuie să specificați intervalul care este tipic pentru masa dvs.

Transpunerea intervalelor de celule orizontale (rânduri)

În principiu, mecanismul de acțiune este aproape același cu cel din paragraful anterior. Să presupunem că avem un șir cu coordonatele de început și de sfârșit B10:F10. De asemenea, poate conține atât valori direct, cât și formule. Să facem o coloană din ea, care va avea aceleași dimensiuni ca și rândul original. Secvența acțiunilor este următoarea:

1. Selectați această coloană cu mouse-ul. De asemenea, puteți utiliza tastele de pe tastatură Ctrl și săgeata în jos, după ce faceți clic pe celula de sus a acestei coloane.
2. După aceea scriem formula =TRANSP(B10:F10) în bara de formule.
3. O scriem ca o formulă matrice folosind combinația de taste Ctrl + Shift + Enter.

Transpunerea cu Paste Special

O altă opțiune posibilă de transpunere este utilizarea funcției Paste Special. Acesta nu mai este un operator care să fie folosit în formule, dar este și una dintre metodele populare de transformare a coloanelor în rânduri și invers.

Această opțiune se află în fila Acasă. Pentru a-l accesa, trebuie să găsiți grupul „Clipboard” și acolo găsiți butonul „Lipire”. După aceea, deschideți meniul care se află sub această opțiune și selectați elementul „Transpunere”. Înainte de aceasta, trebuie să selectați intervalul pe care doriți să îl selectați. Ca urmare, vom obține același interval, doar opusul este oglindit.

3 moduri de a transpune un tabel în Excel

Dar, de fapt, există mult mai multe moduri de a transforma coloanele în rânduri și invers. Să descriem 3 metode prin care putem transpune un tabel în Excel. Am discutat două dintre ele mai sus, dar vom mai oferi câteva exemple pentru a vă face o idee mai bună despre uXNUMXbuXNUMXbcum să efectuați această procedură.

Metoda 1: Paste Special

Această metodă este cea mai simplă. Este suficient să apăsați câteva butoane, iar utilizatorul primește o versiune transpusă a tabelului. Să dăm un mic exemplu pentru o mai mare claritate. Să presupunem că avem un tabel care conține informații despre câte produse sunt în stoc în prezent, precum și cât costă în total. Masa în sine arată așa.

Vedem că avem un antet și o coloană cu numere de produs. În exemplul nostru, antetul conține informații despre ce produs, cât costă, cât este în stoc și care este costul total al tuturor produselor legate de acest articol care sunt în stoc. Obținem costul conform formulei în care costul este înmulțit cu cantitatea. Pentru a face exemplul mai vizual, să facem antetul verde.

Sarcina noastră este să ne asigurăm că informațiile conținute în tabel sunt situate orizontal. Adică, astfel încât coloanele să devină rânduri. Secvența acțiunilor în cazul nostru va fi următoarea:

1. Selectați intervalul de date pe care trebuie să îl rotim. După aceea, copiem aceste date.
2. Plasați cursorul oriunde pe foaie. Apoi faceți clic pe butonul din dreapta al mouse-ului și deschideți meniul contextual.
3. Apoi faceți clic pe butonul „Lipire specială”.

După parcurgerea acestor pași, trebuie să faceți clic pe butonul „Transpunere”. Mai degrabă, bifați caseta de lângă acest articol. Nu modificăm alte setări, apoi facem clic pe butonul „OK”.

După efectuarea acestor pași, rămânem cu același tabel, doar rândurile și coloanele acestuia sunt aranjate diferit. De asemenea, rețineți că celulele care conțin aceleași informații sunt evidențiate cu verde. Întrebare: ce s-a întâmplat cu formulele care erau în intervalul inițial? Locația lor s-a schimbat, dar ei înșiși au rămas. Adresele celulelor s-au schimbat pur și simplu în cele care s-au format după transpunere.

Aproape aceleași acțiuni trebuie efectuate pentru a transpune valori, nu formule. În acest caz, trebuie să utilizați și meniul Paste Special, dar înainte de aceasta, selectați intervalul de date care conține valorile. Vedem că fereastra Paste Special poate fi apelată în două moduri: printr-un meniu special pe panglică sau prin meniul contextual.

Metoda 2. Funcția TRANSP în Excel

De fapt, această metodă nu mai este folosită la fel de activ ca la începutul apariției acestui program de calcul. Acest lucru se datorează faptului că această metodă este mult mai complicată decât utilizarea Paste Special. Cu toate acestea, își găsește utilizarea în automatizarea transpunerii tabelelor.

De asemenea, această funcție este în Excel, așa că este imperativ să știți despre ea, deși nu mai este folosită aproape. Mai devreme am luat în considerare procedura, cum să lucrăm cu ea. Acum vom completa aceste cunoștințe cu un exemplu suplimentar.

1. Mai întâi, trebuie să selectăm intervalul de date care va fi folosit pentru a transpune tabelul. Trebuie doar să selectați zona, dimpotrivă. De exemplu, în acest exemplu avem 4 coloane și 6 rânduri. Prin urmare, este necesar să selectați o zonă cu caracteristici opuse: 6 coloane și 4 rânduri. Poza o arată foarte bine.
2. După aceea, începem imediat să completăm această celulă. Este important să nu eliminați accidental selecția. Prin urmare, trebuie să specificați formula direct în bara de formule.
3. Apoi, apăsați combinația de taste Ctrl + Shift + Enter. Rețineți că aceasta este o formulă matrice, deoarece lucrăm cu un set mare de date simultan, care vor fi transferate într-un alt set mare de celule.

După ce introducem datele, apăsăm tasta Enter, după care obținem următorul rezultat.

Vedem că formula nu a fost transferată în noul tabel. Formatarea a fost, de asemenea, pierdută. Poeto

Toate acestea vor trebui făcute manual. În plus, rețineți că acest tabel este legat de cel original. Prin urmare, de îndată ce unele informații sunt modificate în intervalul inițial, aceste ajustări sunt efectuate automat în tabelul transpus.

Prin urmare, această metodă este potrivită în cazurile în care trebuie să vă asigurați că tabelul transpus este legat de cel original. Dacă utilizați o inserție specială, această posibilitate nu va mai fi.

tabel rezumativ

Aceasta este o metodă fundamental nouă, care face posibilă nu numai transpunerea tabelului, ci și realizarea unui număr mare de acțiuni. Adevărat, mecanismul de transpunere va fi oarecum diferit față de metodele anterioare. Secvența acțiunilor este următoarea:

1. Să facem un tabel pivot. Pentru a face acest lucru, trebuie să selectați tabelul pe care trebuie să îl transpunem. După aceea, mergeți la elementul „Inserare” și căutați acolo „Tabel pivotant”. Va apărea o casetă de dialog ca cea din această captură de ecran.
2. Aici puteți reatribui intervalul din care va fi făcut, precum și puteți face o serie de alte setări. Acum suntem interesați în primul rând de locul tabelului pivot – pe o foaie nouă.
3. După aceea, aspectul tabelului pivot va fi creat automat. Este necesar să se marcheze în el acele articole pe care le vom folosi, iar apoi acestea trebuie mutate în locul corect. În cazul nostru, trebuie să mutăm elementul „Produs” în „Nume coloane” și „Preț pe bucată” în „Valori”.
4. După aceea, tabelul pivot va fi creat în sfârșit. Un bonus suplimentar este calculul automat al valorii finale.
5. Puteți modifica și alte setări. De exemplu, debifați articolul „Preț pe bucată” și bifați articolul „Cost total”. Ca urmare, vom avea un tabel care conține informații despre cât costă produsele. Această metodă de transpunere este mult mai funcțională decât celelalte. Să descriem câteva dintre beneficiile tabelelor pivot:

1. Automatizare. Cu ajutorul tabelelor pivot, puteți rezuma datele în mod automat, precum și puteți modifica poziția coloanelor și coloanelor în mod arbitrar. Pentru a face acest lucru, nu trebuie să efectuați niciun pas suplimentar.
2. Interactivitate. Utilizatorul poate schimba structura informațiilor de câte ori are nevoie pentru a-și îndeplini sarcinile. De exemplu, puteți modifica ordinea coloanelor, precum și gruparea datelor într-un mod arbitrar. Acest lucru se poate face de câte ori are nevoie utilizatorul. Și literalmente durează mai puțin de un minut.
3. Ușor de formatat datele. Este foarte ușor să aranjați un tabel pivot așa cum dorește o persoană. Pentru a face acest lucru, faceți doar câteva clicuri de mouse.
4. Obținerea de valori. Numărul copleșitor de formule care sunt folosite pentru a crea rapoarte se află în accesibilitatea directă a unei persoane și sunt ușor de integrat într-un tabel pivot. Acestea sunt date precum însumarea, obținerea mediei aritmetice, determinarea numărului de celule, înmulțirea, găsirea celor mai mari și mai mici valori din eșantionul specificat.
5. Abilitatea de a crea diagrame rezumative. Dacă tabelele pivot sunt recalculate, diagramele asociate acestora sunt actualizate automat. Este posibil să creați câte diagrame aveți nevoie. Toate pot fi modificate pentru o anumită sarcină și nu vor fi interconectate.
6. Abilitatea de a filtra datele.
7. Este posibil să construiți un tabel pivot bazat pe mai mult de un set de informații sursă. Prin urmare, funcționalitatea lor va deveni și mai mare.

Adevărat, atunci când utilizați tabele pivot, trebuie luate în considerare următoarele restricții:

1. Nu toate informațiile pot fi folosite pentru a genera tabele pivot. Înainte de a putea fi utilizate în acest scop, celulele trebuie normalizate. Cu cuvinte simple, faceți bine. Cerințe obligatorii: prezența unei linii de antet, plenitudinea tuturor liniilor, egalitatea formatelor de date.
2. Datele sunt actualizate semi-automat. Pentru a obține informații noi în tabelul pivot, trebuie să faceți clic pe un buton special.
3. Tabelele pivot ocupă mult spațiu. Acest lucru poate duce la o anumită întrerupere a computerului. De asemenea, fișierul va fi dificil de trimis prin e-mail din această cauză.

De asemenea, după crearea unui tabel pivot, utilizatorul nu are capacitatea de a adăuga informații noi.