Obrni podatke v Excelu - Obratni vrstni red podatkov v stolpcu/vrstici

Včasih boste morda morali obrniti podatke v Excelu, to je, da obrnete vrstni red podatkov na glavo v navpičnem naboru podatkov in od leve proti desni v vodoravnem naboru podatkov.

Če mislite, da mora biti za to v Excelu vgrajena funkcija, se bojim, da boste razočarani.

Čeprav obstaja več načinov, kako lahko podatke obrnete v Excelu, ni vgrajene funkcije. To pa lahko preprosto storite s preprostim trikom za razvrščanje, formulami ali VBA.

V tej vadnici vam bom pokazal, kako obrnete podatke v vrsticah, stolpcih in tabelah v Excelu.

Pa začnimo!

Obrnite podatke s pomočjo razvrščanja in stolpca za pomoč

Eden najlažjih načinov za spremembo vrstnega reda podatkov v Excelu bi bil uporaba stolpca pomočnika in nato ta stolpec pomočnika za razvrščanje podatkov.

Obrnite podatke navpično (obratni vrstni red na glavo)

Recimo, da imate v stolpcu nabor podatkov z imeni, kot je prikazano spodaj, in želite te podatke obrniti:

Spodaj so navedeni koraki za navpično obračanje podatkov:

1. V sosednji stolpec kot naslov stolpca vnesite "Pomočnik"
2. V pomožni stolpec vnesite niz številk (1,2,3 itd.). Če želite to narediti hitro, lahko uporabite tukaj prikazane metode
3. Izberite celoten nabor podatkov, vključno s pomožnim stolpcem
4. Kliknite zavihek Podatki
5. Kliknite ikono Razvrsti
6. V pogovornem oknu Razvrsti izberite »Pomočnik« v spustnem meniju »Razvrsti po«
7. V spustnem meniju Naročilo izberite »Od največjega do najmanjšega«
8. Kliknite V redu

Zgornji koraki bi razvrstili podatke glede na vrednosti pomožnih stolpcev, kar bi privedlo tudi do obračanja vrstnega reda imen v podatkih.

Ko končate, lahko izbrišete stolpec za pomoč.

V tem primeru sem vam pokazal, kako obrniti podatke, če imate samo en stolpec, lahko pa uporabite isto tehniko, če imate celo tabelo. Prepričajte se, da ste izbrali celotno tabelo in nato s pomožnim stolpcem razvrstili podatke po padajočem vrstnem redu.

Obrnite podatke vodoravno

Enako metodo lahko uporabite tudi za horizontalno obračanje podatkov v Excelu.

Excel ima možnost horizontalnega razvrščanja podatkov z uporabo pogovornega okna Razvrsti (funkcija »Razvrsti od leve proti desni«).

Recimo, da imate tabelo, kot je prikazano spodaj, in želite te podatke obrniti vodoravno.

Spodaj so navedeni koraki za to:

1. V spodnjo vrstico vnesite »Pomočnik« kot naslov vrstice
2. V pomožno vrstico vnesite niz številk (1,2,3 itd.).
3. Izberite celoten nabor podatkov, vključno s pomožno vrstico
4. Kliknite zavihek Podatki
5. Kliknite ikono Razvrsti
6. V pogovornem oknu Razvrsti kliknite gumb Možnosti.
7. V pogovornem oknu, ki se odpre, kliknite »Razvrsti od leve proti desni«
8. Kliknite V redu
9. V spustnem meniju Razvrsti po izberite 3. vrstico (ali katero koli vrstico s stolpcem pomočnikom)
10. V spustnem meniju Naročilo izberite »Od največjega do najmanjšega«
11. Kliknite V redu

Zgornji koraki bi vodoravno obrnili celotno mizo.

Ko končate, lahko odstranite pomožno vrstico.

Obrnite podatke s formulami

Microsoft 365 ima nekaj novih formul, ki olajšajo spreminjanje vrstnega reda stolpcev ali tabel v Excelu.

V tem razdelku vam bom pokazal, kako to storite s formulo SORTBY (če uporabljate Microsoft 365) ali formulo INDEX (če ne uporabljate Microsoft 365)

Uporaba funkcije SORTBY (na voljo v Microsoft 365)

Recimo, da imate tabelo, kot je prikazano spodaj, in želite podatke v tej tabeli obrniti:

Če želite to narediti, najprej kopirajte glave in jih postavite tja, kjer želite obrnjeno tabelo

Zdaj uporabite naslednjo formulo pod celico v najbolj levi glavi:

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

Zgornja formula razvršča podatke in z uporabo rezultata funkcije ROW kot podlago za njihovo razvrščanje.

Funkcija ROW bi v tem primeru vrnila niz številk, ki predstavljajo številke vrstic med določenim obsegom (kar bi bilo v tem primeru niz števil, kot so 2, 3, 4 itd.).

In ker je tretji argument te formule -1, bi formulo prisilil, da podatke razvrsti po padajočem vrstnem redu.

Zapis z najvišjo številko vrstice bi bil na vrhu, tisti z najmanjšo številko pravila pa na dnu, kar bi v bistvu obrnilo vrstni red podatkov.

Ko končate, lahko formulo pretvorite v vrednosti, da dobite statično tabelo.

Uporaba funkcije INDEX

Če nimate dostopa do funkcije SORTBY, ne skrbite - uporabite lahko neverjetno funkcijo INDEX.

Recimo, da imate nabor podatkov imen, kot je prikazano spodaj, in želite te podatke obrniti.

Spodaj je formula za to:

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

Kako deluje ta formula?

Zgornja formula uporablja funkcijo INDEX, ki bi vrnila vrednost iz celice na podlagi števila, navedenega v drugem argumentu.

Prava čarovnija se zgodi v drugem argumentu, kjer sem uporabil funkcijo ROWS.

Ker sem drugi del sklica zaklenil v funkciji ROWS, bi v prvi celici vrnilo število vrstic med A2 in A12, ki bi bilo 11.

Ko pa gre navzdol po vrsticah, bi se prva referenca spremenila v A3, nato pa v A4 in tako naprej, medtem ko bi druga referenca ostala nespremenjena, ker sem jo zaklenil in naredil absolutno.

Ko se spuščamo po vrsticah, bi se rezultat funkcije ROWS zmanjšal za 1, z 11 na 10 na 9 itd.

In ker nam funkcija INDEX vrne vrednost, ki temelji na številki v drugem argumentu, bi nam to na koncu dalo podatke v obratnem vrstnem redu.

Lahko uporabite isto formulo, tudi če imate v naboru podatkov več stolpcev. vendar boste morali določiti drugi argument, ki bi določil številko stolpca, iz katerega je treba pridobiti podatke.

Recimo, da imate nabor podatkov, kot je prikazano spodaj, in želite obrniti vrstni red celotne tabele:

Spodaj je formula, ki vam bo to omogočila:

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

To je podobna formula, kjer sem dodal tudi tretji argument, ki določa številko stolpca, iz katerega je treba pridobiti vrednost.

Da bi bila ta formula dinamična, sem uporabil funkcijo COLUMNS, ki bi spreminjala vrednost stolpca z 1 na 2 na 3, ko jo kopirate v desno.

Ko končate, lahko formule pretvorite v vrednosti, da zagotovite statičen rezultat.

Opomba: Če s formulo obrnete vrstni red podatkovnega niza v Excelu, ne bo ohranil prvotnega oblikovanja. Če potrebujete izvirno oblikovanje tudi za razvrščene podatke, jo lahko uporabite ročno ali pa kopirate in prilepite oblikovanje iz prvotnega nabora podatkov v nov razvrščeni nabor podatkov

Obrnite podatke z uporabo VBA

Če morate pogosto obračati podatke v Excelu, lahko poskusite tudi z metodo VBA.

S kodo makra VBA jo lahko enkrat kopirate in prilepite v delovni zvezek v urejevalniku VBA, nato pa jo znova in znova uporabite v istem delovnem zvezku.

Kodo lahko shranite tudi v delovni zvezek osebnega makra ali kot dodatek za Excel in jo lahko uporabite v katerem koli delovnem zvezku v sistemu.

Spodaj je koda VBA, ki bi izbrane podatke obrnila navpično na delovnem listu.

Sub FlipVerically () 'Code by Sumit Bansal iz TrumpExcel.com Dim TopRow kot varianta Dim LastRow Kot varianta Dim StartNum Kot celo število Dim EndNum kot celoštevilska 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 Loop Application.ScreenUpdating = True End Sub

Če želite uporabiti to kodo, morate najprej izbrati niz podatkov, ki ga želite razveljaviti (brez glav), in nato zagnati to kodo.

Kako deluje ta koda?

Zgornja koda najprej šteje skupno število vrstic v naboru podatkov in to dodeli spremenljivki EndNum.

Nato uporabi zanko Do While, kjer pride do razvrščanja podatkov.

Te podatke razvrsti tako, da vzame prvo in zadnjo vrstico ter jih zamenja. Nato se premakne v drugo vrstico v drugi zadnji vrstici in jih nato zamenja. Nato se premakne v tretjo vrstico v tretji zadnji vrstici in tako naprej.

Zanka se konča, ko je opravljeno vse razvrščanje.

Uporablja tudi lastnost Application.ScreenUpdating in jo med izvajanjem kode nastavi na FALSE, nato pa jo vrne v TRUE, ko se koda dokonča.

To zagotavlja, da na zaslonu ne vidite sprememb, ki se dogajajo v realnem času, prav tako pa pospeši postopek.

Kako uporabljati kodo?

Sledite spodnjim korakom, da kopirate in prilepite to kodo v urejevalnik VB:

1. Odprite datoteko Excel, kamor želite dodati kodo VBA
2. Držite tipko ALT in pritisnite tipko F-11 (lahko tudi odprete zavihek Razvijalec in kliknete ikono Visual Basic)
3. V urejevalniku Visual Basic, ki se odpre, bi bil v levem delu urejevalnika VBA Project Explorer. Če ga ne vidite, kliknite zavihek »Pogled« in nato »Raziskovalec projektov«
4. Z desno miškino tipko kliknite kateri koli predmet delovnega zvezka, v katerega želite dodati kodo
5. Pojdite na možnost Vstavi in ​​kliknite Modul. S tem boste v delovni zvezek dodali nov modul
6. Dvokliknite ikono modula v Raziskovalcu projektov. S tem se odpre okno kode za ta modul
7. Kopirajte in prilepite zgornjo kodo VBA v okno za kodo

Če želite zagnati kodo makra VBA, najprej izberite nabor podatkov, ki ga želite obrniti (razen glav).

Ko so podatki izbrani, pojdite v urejevalnik VB in kliknite zeleni gumb za predvajanje v orodni vrstici ali izberite poljubno vrstico v kodi in pritisnite tipko F5

To je torej nekaj načinov, s katerimi lahko obrnete podatke v Excelu (tj. Obrnete vrstni red nabora podatkov).

Vse metode, ki sem jih obravnaval v tej vadnici (formule, funkcija SORT in VBA), se lahko uporabijo za obračanje podatkov navpično in vodoravno (formulo in kodo VBA boste morali prilagoditi za horizontalno obračanje podatkov).

Upam, da vam je bila ta vadnica koristna.