Andmete pööramine Excelis - Andmete vastupidine järjestus veerus/reas

Mõnikord võib tekkida vajadus Excelis andmeid ümber pöörata, st muuta vertikaalses andmekogus andmete järjestus tagurpidi ja horisontaalses andmekogus vasakult paremale.

Nüüd, kui arvate, et Excelis peab selleks olema sisseehitatud funktsioon, kardan, et olete pettunud.

Kuigi Excelis saab andmeid ümber pöörata mitmel viisil, pole sisseehitatud funktsiooni. Kuid saate seda hõlpsalt teha, kasutades lihtsat sortimisnippi, valemeid või VBA -d.

Selles õpetuses näitan teile, kuidas Exceli ridade, veergude ja tabelite andmeid ümber pöörata.

Nii et alustame!

Andmete ümberpööramine sortimise ja abistaja veeru abil

Üks lihtsamaid viise Exceli andmete järjekorra ümberpööramiseks oleks kasutada abiveergu ja seejärel kasutada seda abistajaveergu andmete sortimiseks.

Andmete pööramine vertikaalselt (vastupidine järjekord tagurpidi)

Oletame, et teil on veerus nimede andmekogum, nagu allpool näidatud, ja soovite need andmed ümber pöörata:

Allpool on toodud sammud andmete vertikaalseks pööramiseks.

1. Sisestage külgnevas veerus veeru pealkirjaks „Helper”
2. Sisestage abistajaveergu numbrite seeria (1,2,3 jne). Kiireks tegemiseks võite kasutada siin näidatud meetodeid
3. Valige kogu andmekogum, sealhulgas abistajaveerg
4. Klõpsake vahekaarti Andmed
5. Klõpsake ikooni Sorteeri
6. Valige dialoogiboksis Sortimine rippmenüüst „Sortimisalus” valik „Abimees”
7. Valige rippmenüüst Tellimus „Suurim kuni väikseim”
8. Klõpsake nuppu OK

Ülaltoodud sammud sorteeriksid andmed abistajaveeru väärtuste alusel, mis tooks kaasa ka andmete nimede järjekorra muutmise.

Kui olete valmis, kustutage abistaja veerg julgelt.

Selles näites olen näidanud teile, kuidas andmeid ümber pöörata, kui teil on ainult üks veerg, kuid sama tehnikat saate kasutada ka siis, kui teil on terve tabel. Lihtsalt veenduge, et valite kogu tabeli ja kasutate seejärel abistaja veergu andmete sortimiseks kahanevas järjekorras.

Pöörake andmed horisontaalselt

Sama metoodikat saate järgida ka andmete horisontaalseks pööramiseks Excelis.

Excelil on võimalus sortida andmeid horisontaalselt dialoogiboksi Sortimine (funktsioon „Sortimine vasakult paremale”).

Oletame, et teil on tabel, nagu allpool näidatud, ja soovite need andmed horisontaalselt ümber pöörata.

Allpool on toodud sammud selle tegemiseks.

1. Sisestage allolevas reas rea pealkirjaks „Helper”
2. Sisestage abireale numbrite seeria (1,2,3 jne).
3. Valige kogu andmekogum, sealhulgas abistaja rida
4. Klõpsake vahekaarti Andmed
5. Klõpsake ikooni Sorteeri
6. Klõpsake dialoogiboksis Sortimine nuppu Valikud.
7. Avanevas dialoogiboksis klõpsake nuppu Sorteeri vasakult paremale
8. Klõpsake nuppu OK
9. Valige rippmenüüst Sortimisalus rida 3 (või mis tahes reas on teie abistaja veerg)
10. Valige rippmenüüst Tellimus „Suurim kuni väikseim”
11. Klõpsake nuppu OK

Ülaltoodud sammud pööraksid kogu tabeli horisontaalselt.

Kui olete valmis, saate abistaja rea ​​eemaldada.

Andmete ümberpööramine valemite abil

Microsoft 365 on saanud mõned uued valemid, mis muudavad Exceli veeru või tabeli järjekorra muutmise väga lihtsaks.

Selles jaotises näitan teile, kuidas seda teha valemiga SORTBY (kui kasutate Microsoft 365) või valemiga INDEX (kui te ei kasuta Microsoft 365)

Funktsiooni SORTBY kasutamine (saadaval Microsoft 365 -s)

Oletame, et teil on tabel, nagu allpool näidatud, ja soovite selle tabeli andmed ümber pöörata:

Selleks kopeerige esmalt päised ja asetage need sinna, kuhu soovite ümberpööratud tabeli

Nüüd kasutage vasakpoolses päises oleva lahtri all järgmist valemit:

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

Ülaltoodud valem sorteerib andmed ja nende sortimiseks kasutatakse funktsiooni ROW tulemust.

Funktsioon ROW tagastab sel juhul numbrimassiivi, mis tähistab määratud vahemiku vahel olevaid ridade numbreid (mis selles näites oleks numbrite seeria, näiteks 2, 3, 4 jne).

Ja kuna selle valemi kolmas argument on -1, sunniks see valemit sortima andmeid kahanevas järjekorras.

Kirje, millel on kõrgeim reanumber, oleks ülaosas ja see, millel on madalaim reeglite arv, allosas, muutes andmete järjekorra sisuliselt vastupidiseks.

Kui olete valmis, saate staatilise tabeli saamiseks valemi väärtusteks teisendada.

Funktsiooni INDEX kasutamine

Kui teil pole SORTBY -funktsioonile juurdepääsu, ärge muretsege - saate kasutada hämmastavat funktsiooni INDEX.

Oletame, et teil on allpool näidatud nimede andmestik ja soovite need andmed ümber pöörata.

Allpool on valem selle tegemiseks:

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

Kuidas see valem töötab?

Ülaltoodud valem kasutab funktsiooni INDEX, mis tagastaks lahtrist saadud väärtuse teises argumendis määratud arvu alusel.

Tõeline maagia toimub teises argumendis, kus olen kasutanud funktsiooni ROWS.

Kuna olen lukustanud funktsiooni ROWS viite teise osa esimeses lahtris, tagastab see ridade arvu A2 ja A12 vahel, mis oleks 11.

Aga kui see ridadesse läheb, muutuks esimene viide A3 -ks ja seejärel A4 -ks jne, teine ​​viide jääks aga selliseks, nagu ma olen selle lukustanud ja absoluutseks muutnud.

Ridu mööda minnes väheneks funktsiooni ROWS tulemus 1 võrra, 11 -lt 10 -le ja nii edasi.

Ja kuna funktsioon INDEX tagastab meile väärtuse, mis põhineb teise argumendi numbril, annaks see meile lõpuks andmed vastupidises järjekorras.

Sama valemit saate kasutada ka siis, kui andmekogumis on mitu veergu. siiski peate määrama teise argumendi, mis määraks veeru numbri, kust andmed tuleb hankida.

Oletame, et teil on allpool näidatud andmekogum ja soovite muuta kogu tabeli järjekorda:

Allpool on valem, mis seda teie jaoks teeb:

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

See on sarnane valem, kus olen lisanud ka kolmanda argumendi, mis määrab veeru numbri, millest väärtus tuleks hankida.

Selle valemi dünaamiliseks muutmiseks olen kasutanud funktsiooni COLUMNS, mis muudaks veeru väärtust paremalt kopeerides 1 -lt 2 -le 3 -le.

Kui olete valmis, saate valemid teisendada väärtusteks, et veenduda, et teil on staatiline tulemus.

Märkus. Kui kasutate Exceli andmekogumi järjekorra ümberpööramiseks valemit, ei säilita see algset vormingut. Kui vajate sorteeritud andmetele ka algset vormingut, saate selle käsitsi rakendada või kopeerida ja kleepida vormingu algsest andmekogumist uude sorteeritud andmekogumisse

Andmete pööramine VBA abil

Kui Excelis andmete ümberpööramine on midagi, mida peate tegema üsna sageli, võite proovida ka VBA meetodit.

VBA makrokoodi abil saate selle üks kord töövihikusse kopeerida ja kleepida VBA redaktoris ning seejärel uuesti ja uuesti kasutada samas töövihikus.

Samuti saate koodi salvestada isikliku makro töövihikusse või Exceli lisandmoodulina ja kasutada seda oma süsteemi mis tahes töövihikus.

Allpool on VBA -kood, mis pööraks töölehel valitud andmed vertikaalselt ümber.

Sub FlipVerically () 'koodi autor Sumit Bansal (TrumpExcel.com) = 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

Selle koodi kasutamiseks peate esmalt tegema valiku andmekogumist, mille soovite tagasi pöörata (välja arvatud päised), ja seejärel käivitage see kood.

Kuidas see kood töötab?

Ülaltoodud kood loendab kõigepealt andmekogumi ridade koguarvu ja määrab selle muutujale EndNum.

Seejärel kasutab see andmete tegemise tsüklit, kus andmeid sorteeritakse.

Nende andmete sorteerimiseks kasutatakse esimest ja viimast rida ning need vahetatakse. Seejärel liigub see teise rea teisele reale ja vahetab need siis ära. Seejärel liigub see kolmanda viimase rea kolmandale reale ja nii edasi.

Silmus lõpeb, kui kogu sorteerimine on tehtud.

See kasutab ka atribuuti Application.ScreenUpdating ja määrab selle koodi käitamise ajal väärtusele FALSE ning seejärel keerake see tagasi väärtusele TRUE, kui kood on käivitatud.

See tagab, et te ei näe ekraanil muudatusi reaalajas, see kiirendab ka protsessi.

Kuidas koodi kasutada?

Selle koodi kopeerimiseks ja kleepimiseks VB redaktoris järgige alltoodud samme.

1. Avage Exceli fail, kuhu soovite lisada VBA -koodi
2. Hoidke all klahvi ALT ja vajutage klahvi F-11 (võite minna ka vahekaardile Arendaja ja klõpsata Visual Basic ikoonil)
3. Avanevas Visual Basic Editoris oleks VBA redaktori vasakus osas Project Explorer. Kui te seda ei näe, klõpsake vahekaarti „Vaade” ja seejärel nuppu „Projektide uurija”
4. Paremklõpsake töövihiku mis tahes objekti, kuhu soovite koodi lisada
5. Minge suvandisse Lisa ja seejärel klõpsake Moodul. See lisab töövihikusse uue mooduli
6. Topeltklõpsake Project Exploreris mooduliikooni. See avab selle mooduli koodiakna
7. Kopeerige ja kleepige ülaltoodud VBA -kood koodiaknasse

VBA makrokoodi käitamiseks valige esmalt andmestik, mida soovite ümber pöörata (välja arvatud päised).

Kui andmed on valitud, minge VB redaktorisse ja klõpsake tööriistariba rohelist esitusnuppu või valige suvaline koodirida ja vajutage seejärel klahvi F5

Nii et need on mõned meetodid, mida saate kasutada Exceli andmete pööramiseks (st andmekogumi järjekorra ümberpööramiseks).

Kõiki selles õpetuses käsitletud meetodeid (valemid, SORT -funktsioon ja VBA) saab kasutada andmete vertikaalseks ja horisontaalseks pööramiseks (horisontaalsete andmete pööramiseks peate valemit ja VBA -koodi vastavalt kohandama).

Loodan, et leidsite selle õpetuse kasulikuks.