Flip Data în Excel - Inversați ordinea datelor în coloană / rând

Uneori, este posibil să aveți nevoie să răsturnați datele în Excel, adică să inversați ordinea datelor cu susul în jos într-un set de date vertical și de la stânga la dreapta într-un set de date orizontal.

Acum, dacă vă gândiți că trebuie să existe o funcție încorporată pentru a face acest lucru în Excel, mă tem că nu ați fi dezamăgiți.

Deși există mai multe moduri în care puteți întoarce datele în Excel, nu există nicio caracteristică încorporată. Dar puteți face acest lucru cu ușurință folosind un simplu truc de sortare, formule sau VBA.

În acest tutorial, vă voi arăta cum să răsturnați datele în rânduri, coloane și tabele în Excel.

Asadar, haideti sa începem!

Flip Data folosind SORT și coloana Helper

Una dintre cele mai simple modalități de a inversa ordinea datelor din Excel ar fi utilizarea unei coloane de ajutor și apoi utilizarea acelei coloane de ajutor pentru a sorta datele.

Întoarceți datele pe verticală (comanda inversă invers)

Să presupunem că aveți un set de date de nume într-o coloană așa cum se arată mai jos și doriți să răsturnați aceste date:

Mai jos sunt pașii pentru a răsturna datele pe verticală:

1. În coloana alăturată, introduceți „Asistent” ca titlu pentru coloană
2. În coloana de ajutor, introduceți o serie de numere (1,2,3 și așa mai departe). Puteți utiliza metodele prezentate aici pentru a face acest lucru rapid
3. Selectați întregul set de date, inclusiv coloana de asistență
4. Faceți clic pe fila Date
5. Faceți clic pe pictograma Sortare
6. În caseta de dialog Sortare, selectați „Asistent” în meniul derulant „Sortare după”
7. În meniul derulant Comandă, selectați „De la cel mai mare la cel mai mic”
8. Faceți clic pe OK

Pașii de mai sus ar ordona datele pe baza valorilor coloanei de ajutor, ceea ce ar duce și la inversarea ordinii numelor din date.

După ce ați terminat, nu ezitați să ștergeți coloana de ajutor.

În acest exemplu, v-am arătat cum să întoarceți datele când aveți doar o coloană, dar puteți utiliza aceeași tehnică dacă aveți un tabel întreg. Asigurați-vă că selectați întregul tabel și apoi utilizați coloana de ajutor pentru a sorta datele în ordine descrescătoare.

Întoarceți datele pe orizontală

De asemenea, puteți urma aceeași metodologie pentru a răsturna datele orizontal în Excel.

Excel are opțiunea de a sorta datele pe orizontală utilizând caseta de dialog Sortare (caracteristica „Sortare de la stânga la dreapta”).

Să presupunem că aveți un tabel așa cum se arată mai jos și doriți să întoarceți aceste date pe orizontală.

Mai jos sunt pașii pentru a face acest lucru:

1. În rândul de mai jos, introduceți „Asistent” ca titlu pentru rând
2. În rândul de ajutor, introduceți o serie de numere (1,2,3 și așa mai departe).
3. Selectați întregul set de date, inclusiv rândul asistent
4. Faceți clic pe fila Date
5. Faceți clic pe pictograma Sortare
6. În caseta de dialog Sortare, faceți clic pe butonul Opțiuni.
7. În caseta de dialog care se deschide, faceți clic pe „Sortare de la stânga la dreapta”
8. Faceți clic pe OK
9. În meniul derulant Sortare după, selectați Rândul 3 (sau orice rând are coloana dvs. de ajutor)
10. În meniul derulant Comandă, selectați „De la cel mai mare la cel mai mic”
11. Faceți clic pe OK

Pașii de mai sus ar roti întregul tabel pe orizontală.

După ce ați terminat, puteți elimina rândul de ajutor.

Întoarceți datele folosind formule

Microsoft 365 are câteva formule noi care facilitează inversarea ordinii unei coloane sau a unui tabel în Excel.

În această secțiune, vă voi arăta cum să faceți acest lucru folosind formula SORTBY (dacă utilizați Microsoft 365) sau formula INDEX (dacă nu utilizați Microsoft 365)

Utilizarea funcției SORTBY (disponibilă în Microsoft 365)

Să presupunem că aveți un tabel așa cum se arată mai jos și doriți să întoarceți datele din acest tabel:

Pentru a face acest lucru, mai întâi, copiați anteturile și așezați-le acolo unde doriți tabelul răsturnat

Acum, utilizați următoarea formulă sub celula din antetul cel mai din stânga:

= SORTBY ($ A $ 2: $ B $ 12, ROW (A2: A12), - 1)

Formula de mai sus sortează datele și utilizând rezultatul funcției ROW ca bază pentru a le sorta.

Funcția ROW în acest caz ar returna o matrice de numere care reprezintă numerele rândurilor între intervalul specificat (care în acest exemplu ar fi o serie de numere precum 2, 3, 4 și așa mai departe).

Și întrucât al treilea argument al acestei formule este -1, ar forța formula să sorteze datele în ordine descrescătoare.

Înregistrarea care are cel mai mare număr de rând ar veni în partea de sus și cea care are cel mai mic număr de regulă ar merge în partea de jos, inversând în esență ordinea datelor.

După ce ați terminat, puteți converti formula în valori pentru a obține un tabel static.

Utilizarea funcției INDEX

În cazul în care nu aveți acces la funcția SORTBY, nu vă faceți griji - puteți utiliza funcția uimitoare INDEX.

Să presupunem că aveți un set de date cu nume așa cum se arată mai jos și doriți să întoarceți aceste date.

Mai jos este formula pentru a face acest lucru:

= INDEX ($ A $ 2: $ A $ 12, ROWS (A2: $ A $ 12))

Cum funcționează această formulă?

Formula de mai sus folosește funcția INDEX care ar returna valoarea din celulă pe baza numărului specificat în al doilea argument.

Adevărata magie se întâmplă în al doilea argument în care am folosit funcția ROWS.

Deoarece am blocat a doua parte a referinței în funcția ROWS, în prima celulă, acesta ar returna numărul de rânduri între A2 și A12, care ar fi 11.

Dar când coboară pe rânduri, prima referință s-ar schimba în A3, apoi A4 și așa mai departe, în timp ce a doua referință ar rămâne așa deoarece am blocat-o și am făcut-o absolută.

Pe măsură ce coborâm rândurile, rezultatul funcției ROWS ar scădea cu 1, de la 11 la 10 la 9 și așa mai departe.

Și întrucât funcția INDEX ne returnează valoarea pe baza numărului din al doilea argument, aceasta ne-ar da în cele din urmă datele în ordine inversă.

Puteți utiliza aceeași formulă chiar dacă aveți mai multe coloane în setul de date. cu toate acestea, va trebui să specificați un al doilea argument care să specifice numărul coloanei din care trebuie preluate datele.

Să presupunem că aveți un set de date așa cum se arată mai jos și doriți să inversați ordinea întregului tabel:

Mai jos este formula care va face asta pentru dvs.:

= INDEX ($ A $ 2: $ B $ 12, RUNURI (A2: $ A $ 12), COLOANE ($ A $ 2: A2))

Aceasta este o formulă similară în care am adăugat și un al treilea argument care specifică numărul coloanei din care ar trebui preluată valoarea.

Pentru a face această formulă dinamică, am folosit funcția COLUMNE care ar continua să schimbe valoarea coloanei de la 1 la 2 la 3 pe măsură ce o copiați în dreapta.

După ce ați terminat, puteți converti formulele în valori pentru a vă asigura că aveți un rezultat static.

Notă: Când utilizați o formulă pentru a inversa ordinea unui set de date în Excel, acesta nu va păstra formatarea originală. Dacă aveți nevoie și de formatarea originală pe datele sortate, o puteți aplica manual sau copiați și lipiți formatarea din setul de date original în noul set de date sortate

Flip Data folosind VBA

Dacă răsturnarea datelor în Excel este ceva ce trebuie să faceți destul de des, puteți încerca și metoda VBA.

Cu un cod macro VBA, îl puteți copia și lipi o dată în registrul de lucru în editorul VBA, apoi îl puteți refolosi în repetate rânduri în același registru de lucru.

Puteți, de asemenea, să salvați codul în registrul de lucru personal Macro sau ca un supliment Excel și să îl puteți utiliza în orice registru de lucru din sistemul dvs.

Mai jos este codul VBA care ar întoarce datele selectate pe verticală în foaia de lucru.

Sub FlipVerically () 'Code by Sumit Bansal from TrumpExcel.com Dim TopRow As Variant Dim LastRow As Variant Dim StartNum As Integer Dim EndNum As Integer Application.ScreenUpdating = False StartNum = 1 EndNum = Selection.Rows.Count Do While StartNum <EndNum TopRow = Selection.Rows (StartNum) LastRow = Selection.Rows (EndNum) Selection.Rows (EndNum) = TopRow Selection.Rows (StartNum) = LastRow StartNum = StartNum + 1 EndNum = EndNum - 1 Loop Application.ScreenUpdating = True End Sub

Pentru a utiliza acest cod, trebuie mai întâi să selectați setul de date pe care doriți să îl inversați (cu excepția antetelor) și apoi să rulați acest cod.

Cum funcționează acest cod?

Codul de mai sus contorizează mai întâi numărul total de rânduri din setul de date și îl atribuie variabilei EndNum.

Apoi folosește bucla Do While în care are loc sortarea datelor.

Modul în care sortează aceste date este luând primul și ultimul rând și schimbându-le. Se mută apoi pe al doilea rând din ultimul rând al doilea și apoi le schimbă. Apoi trece la al treilea rând din ultimul rând al treilea și așa mai departe.

Bucla se termină când se face toate sortările.

De asemenea, folosește proprietatea Application.ScreenUpdating și o setează la FALS în timp ce rulează codul, apoi îl întoarce la TRUE atunci când codul s-a finalizat.

Acest lucru vă asigură că nu vedeți schimbările în timp real pe ecran, de asemenea, accelerează procesul.

Cum se folosește codul?

Urmați pașii de mai jos pentru a copia și lipi acest cod în Editorul VB:

1. Deschideți fișierul Excel unde doriți să adăugați codul VBA
2. Țineți apăsată tasta ALT și apăsați tasta F-11 (puteți merge și la fila Dezvoltator și faceți clic pe pictograma Visual Basic)
3. În Visual Basic Editor care se deschide, ar exista un Project Explorer în partea stângă a editorului VBA. Dacă nu o vedeți, faceți clic pe fila „Vizualizare”, apoi faceți clic pe „Project Explorer”
4. Faceți clic dreapta pe oricare dintre obiectele pentru registrul de lucru în care doriți să adăugați codul
5. Mergeți la opțiunea Insert și apoi faceți clic pe Module. Aceasta va adăuga un nou modul în registrul de lucru
6. Faceți dublu clic pe pictograma modulului din Project Explorer. Aceasta va deschide fereastra de cod pentru modulul respectiv
7. Copiați și lipiți codul VBA de mai sus în fereastra codului

Pentru a rula codul macro VBA, selectați mai întâi setul de date pe care doriți să îl întoarceți (cu excepția antetelor).

Cu datele selectate, accesați Editorul VB și faceți clic pe butonul verde de redare din bara de instrumente sau selectați orice linie din cod și apoi apăsați tasta F5

Deci, acestea sunt câteva dintre metodele pe care le puteți utiliza pentru a răsturna datele în Excel (adică inversați ordinea setului de date).

Toate metodele pe care le-am acoperit în acest tutorial (formule, caracteristica SORT și VBA) pot fi folosite pentru a răsfoi datele pe verticală și orizontală (va trebui să ajustați formula și codul VBA în consecință pentru răsucirea orizontală a datelor).

Sper că ți s-a părut util acest tutorial.