Preokrenite podatke u Excelu - Obrnuti redoslijed podataka u stupcu/redu

Ponekad ćete možda morati okrenuti podatke u Excelu, tj. Obrnuti redoslijed podataka naopako u okomitom skupu podataka i slijeva nadesno u vodoravnom skupu podataka.

Bojim se da biste bili razočarani ako mislite da mora postojati ugrađena značajka za to u Excelu.

Iako postoji više načina na koje možete okrenuti podatke u Excelu, nema ugrađene značajke. No to možete jednostavno učiniti pomoću jednostavnog trika za sortiranje, formula ili VBA.

U ovom vodiču pokazat ću vam kako okrenuti podatke u retke, stupce i tablice u Excelu.

Pa krenimo!

Preokrenite podatke pomoću razvrstavanja i stupca pomoćnika

Jedan od najjednostavnijih načina da promijenite redoslijed podataka u Excelu bio bi upotreba pomoćnog stupca, a zatim upotrijebite taj pomoćni stupac za sortiranje podataka.

Okreni podatke okomito (obrnutim redoslijedom naopako)

Pretpostavimo da imate skup podataka imena u stupcu kao što je prikazano u nastavku i želite okrenuti ove podatke:

U nastavku su navedeni koraci za vertikalno okretanje podataka:

1. U susjedni stupac unesite "Pomoćnik" kao naslov stupca
2. U pomoćni stupac unesite niz brojeva (1,2,3 i tako dalje). Da biste to učinili brzo, možete koristiti ovdje prikazane metode
3. Odaberite cijeli skup podataka uključujući pomoćni stupac
4. Kliknite karticu Podaci
5. Pritisnite ikonu Sortiraj
6. U dijaloškom okviru Sortiranje odaberite "Pomoć" na padajućem izborniku "Poredaj po"
7. Na padajućem izborniku Narudžba odaberite "Od najvećeg do najmanjeg"
8. Pritisnite U redu

Gore navedeni koraci razvrstali bi podatke na temelju vrijednosti stupaca pomoćnika, što bi također dovelo do obrnutog redoslijeda imena u podacima.

Kad završite, slobodno izbrišite pomoćni stupac.

U ovom primjeru sam vam pokazao kako preokrenuti podatke ako imate samo jedan stupac, ali možete koristiti i istu tehniku ​​ako imate cijelu tablicu. Samo provjerite jeste li odabrali cijelu tablicu, a zatim upotrijebite pomoćni stupac za sortiranje podataka u opadajućem redoslijedu.

Preokrenite podatke vodoravno

Također možete slijediti istu metodologiju za horizontalno okretanje podataka u Excelu.

Excel ima mogućnost vodoravnog razvrstavanja podataka pomoću dijaloškog okvira Sortiranje (značajka ‘Sortiraj slijeva nadesno’).

Pretpostavimo da imate tablicu kao što je prikazano u nastavku i želite ove podatke okrenuti vodoravno.

U nastavku su navedeni koraci za to:

1. U donji redak unesite "Pomoćnik" kao naslov za redak
2. U pomoćni redak unesite niz brojeva (1,2,3 i tako dalje).
3. Odaberite cijeli skup podataka uključujući pomoćni redak
4. Kliknite karticu Podaci
5. Pritisnite ikonu Sortiraj
6. U dijaloškom okviru Sortiranje kliknite gumb Opcije.
7. U dijaloškom okviru koji se otvori kliknite "Poredaj slijeva nadesno"
8. Pritisnite U redu
9. Na padajućem izborniku Poredaj po, odaberite 3. redak (ili bilo koji red koji sadrži vaš pomoćni stupac)
10. Na padajućem izborniku Narudžba odaberite "Od najvećeg do najmanjeg"
11. Pritisnite U redu

Gore navedeni koraci preokrenuli bi cijelu tablicu vodoravno.

Nakon što završite, možete ukloniti pomoćni red.

Preokrenite podatke pomoću formula

Microsoft 365 ima neke nove formule koje doista olakšavaju promjenu redoslijeda stupca ili tablice u Excelu.

U ovom odjeljku pokazat ću vam kako to učiniti pomoću formule SORTBY (ako koristite Microsoft 365) ili formule INDEX (ako ne koristite Microsoft 365)

Korištenje funkcije SORTBY (dostupno u Microsoft 365)

Pretpostavimo da imate tablicu kao što je prikazano u nastavku i želite okrenuti podatke u ovoj tablici:

Da biste to učinili, prvo kopirajte zaglavlja i postavite ih na mjesto gdje želite preokrenutu tablicu

Sada upotrijebite sljedeću formulu ispod ćelije u krajnjem lijevom zaglavlju:

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

Gornja formula sortira podatke i koristi rezultat funkcije ROW kao osnovu za njihovo sortiranje.

Funkcija ROW bi u ovom slučaju vratila niz brojeva koji predstavlja brojeve redaka između navedenog raspona (koji bi u ovom primjeru bio niz brojeva kao što su 2, 3, 4 itd.).

A budući da je treći argument ove formule -1, forsirala bi formulu da podatke sortira prema padajućem redoslijedu.

Zapis koji ima najveći broj redaka dolazio bi pri vrhu, a onaj koji ima najmanji broj pravila išao bi pri dnu, bitno mijenjajući redoslijed podataka.

Kada to učinite, možete pretvoriti formulu u vrijednosti kako biste dobili statičku tablicu.

Korištenje funkcije INDEX

U slučaju da nemate pristup funkciji SORTBY, ne brinite - možete upotrijebiti nevjerojatnu funkciju INDEKS.

Pretpostavimo da imate skup imena kao što je prikazano u nastavku i želite okrenuti te podatke.

Ispod je formula za to:

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

Kako funkcionira ova formula?

Gornja formula koristi funkciju INDEX koja bi vratila vrijednost iz ćelije na temelju broja navedenog u drugom argumentu.

Prava čarolija događa se u drugom argumentu gdje sam koristio funkciju ROWS.

Budući da sam drugi dio reference zaključao u funkciji ROWS, u prvoj ćeliji bi se vratio broj redaka između A2 i A12, koji bi bio 11.

No, kad se spusti niz redove, prva referenca promijenila bi se u A3, a zatim u A4, i tako dalje, dok bi druga referenca ostala takva kakva jest jer sam je zaključao i učinio apsolutnom.

Kako idemo niz redove, rezultat funkcije ROWS smanjio bi se za 1, s 11 na 10 na 9, i tako dalje.

A budući da nam funkcija INDEX vraća vrijednost na temelju broja u drugom argumentu, to bi nam u konačnici dalo podatke obrnutim redoslijedom.

Možete koristiti istu formulu čak i ako u skupu podataka imate više stupaca. međutim, morat ćete navesti drugi argument koji bi naveo broj stupca iz kojeg je potrebno dohvatiti podatke.

Pretpostavimo da imate skup podataka kao što je prikazano u nastavku i želite obrnuti redoslijed cijele tablice:

Ispod je formula koja će to učiniti umjesto vas:

= INDEKS ($ A $ 2: $ B $ 12, ROWS (A2: $ A $ 12), COLUMNS ($ A $ 2: A2))

Ovo je slična formula u kojoj sam dodao i treći argument koji navodi broj stupca iz kojeg treba dohvatiti vrijednost.

Da bih ovu formulu učinio dinamičnom, upotrijebio sam funkciju COLUMNS koja bi nastavila mijenjati vrijednost stupca s 1 na 2 na 3 dok je kopirate s desne strane.

Kada završite, možete pretvoriti formule u vrijednosti kako biste bili sigurni da imate statički rezultat.

Napomena: Kada koristite formulu za obrnuti redoslijed skupa podataka u Excelu, on neće zadržati izvorno oblikovanje. Ako vam je potrebno i izvorno oblikovanje sortiranih podataka, možete ga primijeniti ručno ili kopirati i zalijepiti oblikovanje iz izvornog skupa podataka u novi sortirani skup podataka

Preokrenite podatke pomoću VBA

Ako prelistavanje podataka u Excelu morate često raditi, možete isprobati i metodu VBA.

S kodom VBA makronaredbe možete ga kopirati i zalijepiti jednom u radnoj knjizi u uređivaču VBA, a zatim ga koristiti uvijek iznova u istoj radnoj knjizi.

Kôd možete spremiti i u Osobnu makro radnu knjigu ili kao dodatak za Excel i moći ga koristiti u bilo kojoj radnoj knjizi na vašem sustavu.

Ispod je VBA kôd koji bi odabrane podatke okomito okrenuo na radnom listu.

Sub FlipVerically () 'Code by Sumit Bansal iz TrumpExcel.com Dim TopRow kao varijanta Dim LastRow Kao varijanta Dim Dim StartNum Kao cijeli broj Dim EndNum Kao cjelobrojna aplikacija.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 aplikacija petlje.ScreenUpdating = True End Sub

Da biste koristili ovaj kôd, najprije morate odabrati skup podataka koji želite poništiti (isključujući zaglavlja), a zatim pokrenuti ovaj kôd.

Kako funkcionira ovaj kod?

Gornji kôd prvo broji ukupan broj redaka u skupu podataka i dodjeljuje to varijabli EndNum.

Zatim koristi petlju Do While gdje se vrši sortiranje podataka.

Način razvrstavanja ovih podataka je uzimanje prvog i posljednjeg retka i zamjena ovih podataka. Zatim se premješta u 2. red u drugom posljednjem redu, a zatim ih mijenja. Zatim se prelazi u treći red u trećem posljednjem redu i tako dalje.

Petlja se završava kada se izvrši sve sortiranje.

Također koristi svojstvo Application.ScreenUpdating i postavlja ga na FALSE tijekom izvođenja koda, a zatim ga vraća na TRUE kada se kôd dovrši.

To osigurava da ne vidite promjene koje se događaju u stvarnom vremenu na vašem zaslonu, a također ubrzava proces.

Kako koristiti Kodeks?

Slijedite korake u nastavku za kopiranje i lijepljenje ovog koda u VB Editor:

1. Otvorite Excel datoteku u koju želite dodati VBA kôd
2. Držite tipku ALT i pritisnite tipku F-11 (također možete otići na karticu Developer i kliknuti na ikonu Visual Basic)
3. U uređivaču Visual Basic koji se otvara, u lijevom dijelu uređivača VBA bio bi Project Explorer. Ako ga ne vidite, kliknite karticu "Pogled", a zatim kliknite "Projekt Explorer"
4. Desnom tipkom miša kliknite bilo koji objekt radne knjige u koji želite dodati kôd
5. Idite na opciju Umetanje, a zatim kliknite na Modul. Ovo će dodati novi modul u radnu knjigu
6. Dvaput kliknite na ikonu modula u Project Exploreru. Ovo će otvoriti prozor koda za taj modul
7. Kopirajte i zalijepite gornji VBA kôd u prozor koda

Da biste pokrenuli VBA makro kôd, najprije odaberite skup podataka koji želite okrenuti (isključujući zaglavlja).

S odabranim podacima idite na VB Editor i kliknite zeleni gumb za reprodukciju na alatnoj traci ili odaberite bilo koji redak u kodu, a zatim pritisnite tipku F5

Dakle, ovo su neke od metoda koje možete koristiti za preokretanje podataka u Excelu (tj. Obrnuti redoslijed skupa podataka).

Sve metode koje sam opisao u ovom vodiču (formule, značajka SORT i VBA) mogu se koristiti za okretanje podataka okomito i vodoravno (formulu i VBA kôd morate prilagoditi za horizontalno prevrtanje podataka).

Nadam se da vam je ovaj vodič bio koristan.