Obrnite podatke v Excelu (obrnite vrstni red podatkov v stolpcih/vrsticah)

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

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

Čeprav obstaja več načinov za obračanje podatkov v Excelu, ni vgrajene funkcije.Toda to lahko enostavno storite s preprostimi triki za razvrščanje, formulami ali VBA.

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

Pa začnimo!

Obrnite podatke z uporabo SORT in pomožnih stolpcev

Eden najpreprostejših načinov za obrnjeno vrstni red podatkov v Excelu je, da uporabite stolpec pomoči in nato uporabite ta pomožni stolpec za razvrščanje podatkov.

Obrni podatke navpično (obrnjen vrstni red)

Recimo, da imate stolpec, kot je spodajIme podatkovnega niza, in želite obrniti te podatke:

Tu so koraki za navpično obračanje podatkov:

1. V sosednji stolpec vnesite "Helper" kot naslov stolpca
2. V stolpec pomoči vnesite niz številk (1, 2, 3 itd.).To lahko storite hitro z uporabo metode, prikazane tukaj
3. Izberite celoten nabor podatkov, vključno s pomožnimi stolpci
4. Kliknite zavihek Podatki
5. Kliknite ikono za razvrščanje
6. V pogovornem oknu Razvrsti izberite Pomočnik v spustnem meniju Razvrsti po
7. V spustnem meniju naročila izberite "Veliko do majhno"
8. Kliknite V redu

Zgornji koraki bodo podatke razvrstili na podlagi vrednosti pomožnih stolpcev, zaradi česar bodo tudi imena v podatkih v obratnem vrstnem redu.

Ko končate, lahko spustite stolpec pomoči.

V tem primeru vam pokažem, kako obrniti podatke, ko je samo en stolpec, vendar lahko uporabite isto tehniko, če imate celotno tabelo.Prepričajte se, da ste izbrali celotno tabelo, nato pa uporabite stolpec pomoči, da razvrstite podatke v padajočem vrstnem redu.

Obrnite podatke vodoravno

Podatke lahko tudi vodoravno obrnete v Excelu na enak način.

Excel ima možnost vodoravnega razvrščanja podatkov s pogovornim oknom Razvrsti (funkcija Razvrsti od leve proti desni).

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

Tu so koraki za to:

1. V spodnji vrstici vnesite "Helper" kot naslov vrstice
2. V pomožni vrstici vnesite niz številk (1, 2, 3 itd.).
3. Izberite celoten nabor podatkov, vključno s pomožnimi vrsticami
4. Kliknite zavihek Podatki
5. Kliknite ikono za razvrščanje
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. Na spustnem seznamu Razvrsti po izberite vrstico 3 (ali kateri koli drug stolpec za pomočnike)
10. V spustnem meniju naročila izberite "Veliko do majhno"
11. Kliknite V redu

Zgornji koraki bodo celotno mizo obrnili vodoravno.

Ko končate, lahko izbrišete pomožno vrstico.

Uporabite Excel, da formule obrnete na glavoobrnite podatke

Microsoft 365 ima nekaj novih formul, s katerimi je zelo enostavno obrniti vrstni red stolpca ali tabele 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)

Uporabite funkcijo SORTBY (na voljo v Microsoft 365)

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

Če želite to narediti, najprej kopirajte glave in jih postavite na mesto, kjer želite obrniti mizo

Zdaj uporabite naslednjo formulo pod skrajno levo celico glave:

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

Zgornja formula razvrsti podatke in razvrsti na podlagi rezultata funkcije ROW.

V tem primeru bo funkcija ROW vrnila niz številk, ki predstavljajo številke vrstic med navedenim obsegom (v tem primeru niz številk, kot so 2, 3, 4 itd.).

Ker je tretji parameter te formule -1, prisili formulo, da razvrsti podatke v padajočem vrstnem redu.

Zapis z najvišjo številko vrstice bo na vrhu, zapis z najnižjo številko pravila pa na dnu, kar v bistvu obrne vrstni red podatkov.

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

Uporabite funkcijo indeksa

Če ne morete uporabiti funkcije SORTBY, ne skrbite – lahko uporabite neverjetno funkcijo INDEX.

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

Tukaj je formula za to:

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

 Kako deluje ta formula?

Zgornja formula uporablja funkcijo INDEX, ki bo vrnila vrednost iz celice na podlagi števila, določenega v drugem parametru.

Prava čarovnija se zgodi, ko uporabim drugi parameter funkcije ROWS.

Ker sem drugi del sklicevanja zaklenil v funkciji ROWS, bo v prvi celici vrnil število vrstic med A2 in A12, kar je 11.

Toda ko gre po vrstici navzdol, se bo prva referenca spremenila v A3, nato A4 in tako naprej, medtem ko bo druga referenca ostala takšna, kot je, ker sem jo zaklenil in naredil absolutno.

Ko gremo navzdol, se bo rezultat funkcije ROWS zmanjšal za 1, z 11 na 10 na 9 itd.

Ker funkcija INDEX vrne vrednost na podlagi števila v drugem argumentu, nam to na koncu poda podatke v obratnem vrstnem redu.

Enako formulo lahko uporabite tudi, če je v naboru podatkov več stolpcev.Vendar morate podati drugi parameter, ki bo določil številko stolpca, iz katerega je treba pridobiti podatke.

Recimo, da imate nabor podatkov, ki izgleda takole, in želite obrniti vrstni red celotne tabele:

Tukaj je formula, ki bo to naredila za vas:

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

Tukaj je podobna formula, kjer sem dodal tudi tretji parameter, ki določa številko stolpca, iz katerega je treba vzeti vrednost.

Da bi to formulo naredil dinamično, sem uporabil funkcijo COLUMNS, ki nenehno spreminja vrednost stolpca iz 1 na 2 na 3, ko jo kopirate na desno.

Ko končate, lahko formulo pretvorite v vrednost, da zagotovite statične rezultate.

Opomba: Ko uporabite formulo za obratno zaporedje nabora podatkov v Excelu, ta ne ohrani izvirnega oblikovanja.Če potrebujete tudi izvirno oblikovanje razvrščenih podatkov, ga lahko uporabite ročno ali kopirate in prilepite oblikovanje iz izvirnega nabora podatkov v nov razvrščeni nabor podatkov

Obrnite podatke z uporabo VBA

Če je obračanje podatkov v Excelu nekaj, kar morate pogosto početi, 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 osebni delovni zvezek makra ali kot dodatek za Excel in jo lahko uporabite v katerem koli delovnem zvezku v vašem sistemu.

Spodaj je koda VBA za navpično obračanje izbranih podatkov na delovnem listu.

Sub FlipVerically ()

Dim TopRow kot različica
Dim LastRow kot različica
Zatemni StartNum kot celo število
Dim EndNum kot celo število

Application.ScreenUpdating = Napačno

Začetna številka = 1
Končna številka = izbira.vrstic.štev

Naredi Medtem ko StartNum < EndNum
Zgornja vrstica = Izbira. Vrstice (Začetna številka)
Zadnja vrstica = izbor. vrstice (končno število)
Selection.Rows(EndNum) = Zgornja vrstica
Selection.Rows(StartNum) = Zadnja vrstica
Začetna številka = Začetna številka + 1
Končna številka = Končna številka - 1
Zanka

Application.ScreenUpdating = Res
End Sub

Če želite uporabiti to kodo, morate najprej izbrati nabor podatkov, ki ga želite obrniti (razen glav), nato zagnati to kodo.

Kako deluje ta koda?

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

Nato za razvrščanje podatkov uporabi zanko Do While.

Te podatke razvrsti tako, da vzame prvo in zadnjo vrstico in ju zamenja.Nato se premakne v drugo vrstico predzadnje vrstice in jih nato zamenja.Nato se premakne v tretjo vrstico od tretje do zadnje in tako naprej.

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

Uporablja tudi lastnost Application.ScreenUpdating in jo nastavi na FALSE, ko se koda izvaja, nato pa jo vrne v TRUE, ko se koda konča.

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

Kako uporabljati kodo?

Kopirajte in prilepite to kodo v urejevalnik VB, kot sledi:

1. Odprite datoteko Excel, kamor želite dodati kodo VBA
2. Držite ALT in pritisnite F-11 (lahko greste tudi na zavihek razvijalec in kliknete ikono Visual Basic)
3. V urejevalniku Visual Basic, ki se odpre, bo na levi strani urejevalnika VBA Project Explorer.Če ga ne vidite, kliknite zavihek Pogled in nato Project Explorer
4. Z desno tipko miške kliknite kateri koli predmet delovnega zvezka, kamor želite dodati kodo
5. Pojdite na možnost Vstavi in ​​kliknite na Modul.To bo delovnemu zvezku dodalo nov modul
6. Dvokliknite ikono modula v Project Explorerju.To bo odprlo okno s kodo za ta modul
7. Kopirajte in prilepite zgornjo kodo VBA v okno kode

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

Z izbranimi podatki pojdite v urejevalnik VB in kliknite zeleni gumb za predvajanje v orodni vrstici ali izberite katero koli vrstico v kodi in pritisnite F5

Torej, to je nekaj načinov, kako lahko podatke obrnete v Excelu (tj. obrnete vrstni red nabora podatkov).

Vse metode, ki sem jih obravnaval v tej vadnici (formule, funkcije SORT in VBA), je mogoče uporabiti za navpično in vodoravno obračanje podatkov (za horizontalno obračanje podatkov morate ustrezno prilagoditi formule in kodo VBA).

Upam, da se vam je ta vadnica zdela koristna.