Adatok átfordítása Excelben - Az adatok fordított sorrendje oszlopban/sorban

Előfordulhat, hogy az Excelben meg kell fordítania az adatokat, azaz meg kell fordítania az adatok sorrendjét egy függőleges adatkészletben, balról jobbra egy vízszintes adatkészletben.

Ha most azt gondolja, hogy ennek az Excelben egy beépített funkciónak kell lennie, attól tartok, hogy csalódott lesz.

Bár többféle módon is megfordíthatja az adatokat az Excelben, nincs beépített szolgáltatás. De ezt könnyedén megteheti egyszerű rendezési trükk, képletek vagy VBA segítségével.

Ebben az oktatóanyagban megmutatom, hogyan lehet az adatokat sorokban, oszlopokban és táblázatokban megfordítani az Excelben.

Kezdjük hát el!

Forgassa az adatokat a SORT és a Helper oszlop használatával

Az Excel legegyszerűbb módja annak, hogy megfordítsuk az adatok sorrendjét, ha segédoszlopot használunk, majd ezt a segédoszlopot használjuk az adatok rendezéséhez.

Fordítsa el az adatokat függőlegesen (fordított sorrend fejjel lefelé)

Tegyük fel, hogy az alábbi oszlopban van adatnevek halmaza, és meg szeretné fordítani ezeket:

Az alábbiakban bemutatjuk az adatok függőleges megfordításának lépéseit:

1. A szomszédos oszlopban írja be a „Helper” oszlop fejlécét
2. A segítő oszlopba írja be a számsorozatot (1,2,3 és így tovább). Az itt bemutatott módszerekkel gyorsan megteheti
3. Válassza ki a teljes adatkészletet, beleértve a segítő oszlopot is
4. Kattintson az Adatok fülre
5. Kattintson a Rendezés ikonra
6. A Rendezés párbeszédpanelen válassza a „Segítő” lehetőséget a „Rendezés” legördülő menüben
7. A Rendelés legördülő menüben válassza a „Legnagyobbtól a legkisebbig” lehetőséget
8. Kattintson az OK gombra

A fenti lépések a segítő oszlop értékei alapján rendeznék az adatokat, ami az adatok nevek sorrendjének megfordításához is vezetne.

Ha elkészült, nyugodtan törölje a segítő oszlopot.

Ebben a példában megmutattam, hogyan lehet megfordítani az adatokat, ha csak egy oszlopa van, de ugyanazt a technikát is használhatja, ha teljes táblája van. Csak győződjön meg arról, hogy kiválasztotta a teljes táblázatot, majd a segítő oszlop segítségével rendezi az adatokat csökkenő sorrendben.

Forgassa az adatokat vízszintesen

Ugyanezt a módszert követheti az adatok vízszintes megfordításához az Excelben.

Az Excelnek lehetősége van az adatok vízszintes rendezésére a Rendezés párbeszédpanel segítségével (a „Rendezés balról jobbra” funkció).

Tegyük fel, hogy van egy táblázata, amint az alább látható, és vízszintesen szeretné fordítani ezeket az adatokat.

Ennek lépései az alábbiakban találhatók:

1. Az alábbi sorba írja be a „Helper” címet a sor fejlécéül
2. A segítő sorba írja be a számsorozatot (1,2,3 stb.).
3. Válassza ki a teljes adatkészletet, beleértve a segítő sort is
4. Kattintson az Adatok fülre
5. Kattintson a Rendezés ikonra
6. A Rendezés párbeszédpanelen kattintson a Beállítások gombra.
7. A megnyíló párbeszédpanelen kattintson a „Rendezés balról jobbra” lehetőségre
8. Kattintson az OK gombra
9. A Rendezés szerint legördülő menüben válassza a 3. sort (vagy bármelyik sort, amelyben a segédoszlop található)
10. A Rendelés legördülő menüben válassza a „Legnagyobbtól a legkisebbig” lehetőséget
11. Kattintson az OK gombra

A fenti lépések az egész táblázatot vízszintesen megfordítják.

Ha elkészült, eltávolíthatja a segítő sort.

Adatok fordítása képletek segítségével

A Microsoft 365 néhány új képlettel rendelkezik, amelyek nagyon egyszerűvé teszik az oszlopok vagy táblázatok sorrendjének megfordítását az Excelben.

Ebben a részben megmutatom, hogyan kell ezt megtenni a SORTBY képlet (ha Microsoft 365 -öt használ) vagy az INDEX képlet (ha nem Microsoft 365 -öt használ) segítségével

A SORTBY funkció használata (elérhető a Microsoft 365 -ben)

Tegyük fel, hogy van egy táblázata, amint az alább látható, és meg szeretné fordítani a táblázatban szereplő adatokat:

Ehhez először másolja le a fejléceket, és helyezze azokat a helyre, ahová az elfordított táblázatot szeretné

Most használja a következő képletet a bal oldali fejléc cella alatt:

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

A fenti képlet rendezi az adatokat, és a ROW függvény eredményét használja alapul a rendezéshez.

A ROW függvény ebben az esetben egy számtömböt ad vissza, amely a megadott tartomány közötti sorszámokat képviseli (ebben a példában számok, például 2, 3, 4 és így tovább).

És mivel ennek a képletnek a harmadik argumentuma -1, arra kényszerítené a képletet, hogy csökkenő sorrendben rendezze az adatokat.

A rekord, amely a legmagasabb sorszámmal rendelkezik, felülre kerül, és a legalacsonyabb szabályszámú alulra kerül, lényegében megfordítva az adatok sorrendjét.

Ha elkészült, konvertálhatja a képletet értékekre, hogy statikus táblát kapjon.

Az INDEX funkció használata

Ha nem rendelkezik hozzáféréssel a SORTBY funkcióhoz, ne aggódjon - használhatja a csodálatos INDEX funkciót.

Tegyük fel, hogy rendelkezik az alább látható nevek adathalmazával, és meg szeretné fordítani ezeket az adatokat.

Az alábbi képlet ennek elvégzésére szolgál:

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

Hogyan működik ez a képlet?

A fenti képlet az INDEX függvényt használja, amely visszaadja a cella értékét a második argumentumban megadott szám alapján.

Az igazi varázslat a második érvben történik, ahol a ROWS funkciót használtam.

Mivel a ROWS függvényben lezártam a hivatkozás második részét, az első cellában az A2 és A12 közötti sorok számát adja vissza, ami 11 lenne.

De amikor lemegy a sorokban, az első hivatkozás A3 -ra, majd A4 -re és így tovább változik, míg a második hivatkozás úgy marad, ahogy van, mert lezártam és abszolútvá tettem.

Ahogy haladunk lefelé a sorokban, a ROWS függvény eredménye 1 -gyel csökken, 11 -ről 10 -re 9 -re stb.

És mivel az INDEX függvény a második argumentumban szereplő szám alapján adja vissza az értéket, ez végül fordított sorrendben adja meg az adatokat.

Ugyanazt a képletet akkor is használhatja, ha több oszlop van az adatkészletben. azonban meg kell adnia egy második argumentumot, amely megadja az oszlop számát, amelyből le kell tölteni az adatokat.

Tegyük fel, hogy rendelkezik egy adathalmazzal az alábbiak szerint, és meg szeretné fordítani a teljes táblázat sorrendjét:

Az alábbi képlet ezt teszi az Ön számára:

= INDEX ($ A $ 2: $ B $ 12, SOROK (A2: $ A $ 12), OSZLOPOK ($ A $ 2: A2))

Ez egy hasonló képlet, ahol hozzáadtam egy harmadik érvet is, amely meghatározza az oszlop számát, amelyből az értéket le kell kérni.

A képlet dinamikussá tételéhez a COLUMNS függvényt használtam, amely az oszlop értékét 1 -ről 2 -re 3 -ra változtatja, amikor jobbra másolja.

Ha elkészült, a képleteket értékekké alakíthatja, hogy megbizonyosodjon arról, hogy statikus eredményt kap.

Megjegyzés: Ha képlet segítségével megfordítja az adathalmazok sorrendjét az Excelben, az nem fogja megtartani az eredeti formázást. Ha szüksége van az eredeti formázásra a rendezett adatokon is, akkor manuálisan alkalmazhatja, vagy lemásolhatja és beillesztheti a formázást az eredeti adatkészletből az új rendezett adatkészletbe

Adatok átfordítása a VBA használatával

Ha az adatok Excelben történő fordítását gyakran kell elvégeznie, akkor próbálja ki a VBA módszert is.

A VBA makrókóddal egyszer átmásolhatja és beillesztheti a munkafüzetbe a VBA szerkesztőben, majd újra és újra felhasználhatja ugyanazon a munkafüzeten.

A kódot elmentheti a Személyes makró munkafüzetbe vagy Excel-bővítménybe is, és használhatja a rendszer bármely munkafüzetében.

Az alábbiakban látható a VBA kód, amely függőlegesen megfordítja a kiválasztott adatokat a munkalapon.

Sub FlipVerically () 'kódja: Sumit Bansal a TrumpExcel.com -tól Dim TopRow mint variáns Dim LastRow mint változat Dim = 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

A kód használatához először ki kell választania a visszafordítani kívánt adathalmazt (a fejlécek kivételével), majd futtatni ezt a kódot.

Hogyan működik ez a kód?

A fenti kód először megszámolja az adathalmaz összes sorát, és hozzárendeli az EndNum változóhoz.

Ezután a Do Do ciklust használja, ahol az adatok rendezése történik.

Ezeket az adatokat úgy rendezi, hogy az első és az utolsó sort veszi fel, és ezeket felcseréli. Ezután a második utolsó sor 2. sorába lép, majd felcseréli ezeket. Ezután a harmadik utolsó sor harmadik sorába lép, és így tovább.

A ciklus befejeződik, amikor az összes válogatás befejeződött.

Az Application.ScreenUpdating tulajdonságot is használja, és a kód futtatása közben FALSE értékre állítja, majd a kód futtatása után kapcsolja vissza az IGAZ értékre.

Ez biztosítja, hogy nem látja a változásokat valós időben a képernyőn, és felgyorsítja a folyamatot.

Hogyan kell használni a kódot?

Kövesse az alábbi lépéseket a kód másolásához és beillesztéséhez a VB szerkesztőbe:

1. Nyissa meg azt az Excel -fájlt, ahová hozzá szeretné adni a VBA -kódot
2. Tartsa lenyomva az ALT billentyűt, és nyomja meg az F-11 billentyűt (a Fejlesztő fülre is léphet, és kattintson a Visual Basic ikonra)
3. A megnyíló Visual Basic szerkesztőben a VBA szerkesztő bal oldalán egy Project Explorer található. Ha nem látja, kattintson a "Nézet" fülre, majd a "Project Explorer" gombra
4. Kattintson a jobb gombbal a munkafüzet bármelyik objektumára, amelyhez hozzá szeretné adni a kódot
5. Lépjen a Beszúrás opcióra, majd kattintson a Modul elemre. Ez új modult ad hozzá a munkafüzethez
6. Kattintson duplán a modul ikonra a Project Explorerben. Ez megnyitja az adott modul kódablakát
7. Másolja ki és illessze be a fenti VBA kódot a kódablakba

A VBA makrókód futtatásához először válassza ki az átfordítani kívánt adatkészletet (a fejlécek kivételével).

Ha az adatok ki vannak választva, lépjen a VB szerkesztőbe, és kattintson a zöld lejátszás gombra az eszköztáron, vagy jelöljön ki egy sort a kódban, majd nyomja meg az F5 billentyűt

Tehát ezek azok a módszerek, amelyekkel az Excelben megfordíthatja az adatokat (azaz megfordíthatja az adatkészlet sorrendjét).

Az ebben az oktatóanyagban ismertetett összes módszer (képletek, SORT funkció és VBA) használható az adatok függőleges és vízszintes felforgatására (a vízszintes adatforgatáshoz a képletet és a VBA -kódot ennek megfelelően kell beállítani).

Remélem, hasznosnak találta ezt az oktatóanyagot.