Prevrátenie údajov v programe Excel - Opačné poradie údajov v stĺpci/riadku

Niekedy môže byť potrebné prevrátiť údaje v programe Excel, tj. Obrátiť poradie údajov hore nohami vo vertikálnej množine údajov a zľava doprava v horizontálnej množine údajov.

Ak si teraz myslíte, že na to musí byť v programe Excel vstavaná funkcia, obávam sa, že budete sklamaní.

Aj keď existuje niekoľko spôsobov, ako môžete prevrátiť údaje v programe Excel, neexistuje žiadna vstavaná funkcia. Môžete to však ľahko urobiť pomocou jednoduchého triediaceho triku, vzorcov alebo VBA.

V tomto tutoriále vám ukážem, ako prevrátiť údaje v riadkoch, stĺpcoch a tabuľkách v programe Excel.

Začnime teda!

Prevráťte údaje pomocou stĺpca SORT a pomocníka

Jeden z najľahších spôsobov, ako obrátiť poradie údajov v programe Excel, by bolo použiť pomocný stĺpec a potom tento pomocný stĺpec použiť na zoradenie údajov.

Prevrátiť údaje vertikálne (obrátené poradie hore nohami)

Predpokladajme, že máte v stĺpci skupinu údajov s menami, ako je uvedené nižšie, a chcete tieto údaje prevrátiť:

Nasledujú kroky na vertikálne prevrátenie údajov:

1. Do susedného stĺpca zadajte ako nadpis stĺpca „Pomocník“
2. Do stĺpca pomocníka zadajte sériu čísel (1,2,3 atď.). Na rýchle vykonanie tohto postupu môžete použiť tu uvedené metódy
3. Vyberte celý súbor údajov vrátane pomocného stĺpca
4. Kliknite na kartu Údaje
5. Kliknite na ikonu Zoradiť
6. V dialógovom okne Zoradiť vyberte v rozbaľovacej ponuke „Zoradiť podľa“ položku „Pomocník“
7. V rozbaľovacom zozname Objednávka vyberte možnosť „Od najväčšieho po najmenší“.
8. Kliknite na tlačidlo OK

Vyššie uvedené kroky by zoradili údaje na základe hodnôt stĺpca pomocníka, čo by tiež viedlo k obráteniu poradia mien v údajoch.

Keď budete hotoví, môžete pomocný stĺpec odstrániť.

V tomto prípade som vám ukázal, ako prevrátiť údaje, keď máte iba jeden stĺpec, ale rovnakú techniku ​​môžete použiť aj vtedy, ak máte celú tabuľku. Uistite sa, že vyberiete celú tabuľku a potom pomocou pomocného stĺpca zoradíte údaje zostupne.

Prevrátiť údaje horizontálne

Rovnakú metodológiu môžete použiť aj na horizontálne prevrátenie údajov v programe Excel.

Excel má možnosť zoradiť údaje horizontálne pomocou dialógového okna Zoradiť (funkcia „Zoradiť zľava doprava“).

Predpokladajme, že máte tabuľku, ako je uvedené nižšie, a chcete tieto údaje prevrátiť horizontálne.

Nasledujú kroky, ako to urobiť:

1. V nasledujúcom riadku zadajte ako nadpis riadka „Pomocník“
2. Do pomocného riadka zadajte sériu čísel (1,2,3 atď.).
3. Vyberte celý súbor údajov vrátane pomocného riadka
4. Kliknite na kartu Údaje
5. Kliknite na ikonu Zoradiť
6. V dialógovom okne Zoradiť kliknite na tlačidlo Možnosti.
7. V dialógovom okne, ktoré sa otvorí, kliknite na položku „Zoradiť zľava doprava“
8. Kliknite na tlačidlo OK
9. V rozbaľovacom zozname Zoradiť podľa vyberte riadok 3 (alebo ktorýkoľvek riadok obsahuje váš pomocný stĺpec)
10. V rozbaľovacom zozname Objednávka vyberte možnosť „Od najväčšieho po najmenší“.
11. Kliknite na tlačidlo OK

Vyššie uvedené kroky by prevrátili celý stôl vodorovne.

Po dokončení môžete pomocný rad odstrániť.

Prevrátenie údajov pomocou vzorcov

Microsoft 365 má niekoľko nových vzorcov, vďaka ktorým je skutočne ľahké obrátiť poradie stĺpcov alebo tabuliek v programe Excel.

V tejto časti vám ukážem, ako to urobiť pomocou vzorca SORTBY (ak používate Microsoft 365) alebo vzorca INDEX (ak nepoužívate Microsoft 365)

Použitie funkcie SORTBY (k dispozícii v Microsoft 365)

Predpokladajme, že máte tabuľku uvedenú nižšie a chcete prevrátiť údaje v tejto tabuľke:

Ak to chcete urobiť, najskôr skopírujte hlavičky a umiestnite ich na miesto, kde chcete prevrátený stôl

Teraz použite nasledujúci vzorec pod bunkou v hlavičke úplne vľavo:

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

Vyššie uvedený vzorec triedi údaje a pomocou výsledku funkcie ROW ako základu na ich zoradenie.

Funkcia ROW by v tomto prípade vrátila pole čísel, ktoré predstavuje čísla riadkov medzi zadaným rozsahom (čo by v tomto prípade bola séria čísel, ako sú 2, 3, 4 atď.).

A keďže tretí argument tohto vzorca je -1, prinútil by vzorec zoradiť údaje zostupne.

Záznam, ktorý má najvyššie číslo riadka, bude v hornej časti a záznam s najnižším číslom pravidla v spodnej časti, čím sa v zásade obráti poradie údajov.

Po dokončení môžete vzorec previesť na hodnoty a získať tak statickú tabuľku.

Použitie funkcie INDEX

V prípade, že nemáte prístup k funkcii SORTBY, nebojte sa - môžete použiť úžasnú funkciu INDEX.

Predpokladajme, že máte množinu údajov s menami, ako je uvedené nižšie, a chcete tieto údaje prevrátiť.

Nasleduje vzorec, ako to urobiť:

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

Ako tento vzorec funguje?

Vyššie uvedený vzorec používa funkciu INDEX, ktorá by vrátila hodnotu z bunky na základe čísla uvedeného v druhom argumente.

Skutočná mágia sa stane v druhom argumente, kde som použil funkciu ROWS.

Pretože som uzamkol druhú časť odkazu vo funkcii ROWS, v prvej bunke by vrátil počet riadkov medzi A2 a A12, čo by bolo 11.

Ale keď to pôjde po riadkoch, prvá referencia sa zmení na A3 a potom A4 a tak ďalej, zatiaľ čo druhá referencia zostane taká, aká je, pretože som ju uzamkol a urobil ju absolútnou.

Keď ideme po riadkoch, výsledok funkcie ROWS by sa znížil o 1, z 11 na 10 až 9 atď.

A keďže funkcia INDEX nám vracia hodnotu na základe čísla v druhom argumente, v konečnom dôsledku by nám to poskytlo údaje v opačnom poradí.

Rovnaký vzorec môžete použiť aj vtedy, ak máte v súbore údajov viac stĺpcov. budete však musieť zadať druhý argument, ktorý by špecifikoval číslo stĺpca, z ktorého je potrebné načítať údaje.

Predpokladajme, že máte množinu údajov, ako je uvedené nižšie, a chcete obrátiť poradie celej tabuľky:

Nasleduje vzorec, ktorý to za vás urobí:

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

Je to podobný vzorec, kde som tiež pridal tretí argument, ktorý určuje číslo stĺpca, z ktorého by sa mala hodnota načítať.

Aby bol tento vzorec dynamický, použil som funkciu COLUMNS, ktorá by pri kopírovaní doprava stále menila hodnotu stĺpca z 1 na 2 až 3.

Po dokončení môžete vzorce previesť na hodnoty, aby ste sa uistili, že máte statický výsledok.

Poznámka: Keď použijete vzorec na obrátenie poradia množiny údajov v programe Excel, nezachová pôvodné formátovanie. Ak potrebujete pôvodné formátovanie aj pre zoradené údaje, môžete ho použiť buď manuálne, alebo skopírovať a prilepiť formátovanie z pôvodnej množiny údajov do novej triedenej množiny údajov.

Prevrátenie údajov pomocou VBA

Ak je prevracanie údajov v programe Excel niečo, čo musíte robiť dosť často, môžete tiež vyskúšať metódu VBA.

S kódom makra VBA ho môžete skopírovať a prilepiť jedenkrát do zošita v editore VBA a potom ho znova použiť znova a znova v tom istom zošite.

Kód si môžete uložiť aj do osobného zošita makier alebo ako doplnok Excelu a môžete ho použiť v ľubovoľnom zošite vo vašom systéme.

Nasleduje kód VBA, ktorý by vertikálne prevrátil vybrané údaje v pracovnom hárku.

Sub FlipVerically () 'Code od Sumit Bansal od TrumpExcel.com Dim TopRow ako variant Dim LastRow ako Variant Dim StartNum ako celé číslo Dim EndNum ako celé číslo Application.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

Ak chcete použiť tento kód, musíte najskôr vybrať množinu údajov, ktorú chcete obrátiť (okrem hlavičiek), a potom tento kód spustiť.

Ako tento kód funguje?

Vyššie uvedený kód najskôr spočíta celkový počet riadkov v množine údajov a priradí ich premennej EndNum.

Potom použije slučku Do While, kde dochádza k triedeniu údajov.

Zoraďuje tieto údaje tak, že zoberie prvý a posledný riadok a zamení ich. Potom sa presunie do 2. radu v druhom poslednom rade a potom ich vymení. Potom sa presunie do tretieho radu v treťom poslednom rade a tak ďalej.

Smyčka končí, keď je všetky triedenie dokončené.

Tiež používa vlastnosť Application.ScreenUpdating a pri spustení kódu ho nastaví na hodnotu FALSE a potom, keď sa beh kódu dokončí, vráti ho späť na hodnotu TRUE.

Zaistí to, že na obrazovke neuvidíte zmeny, ktoré sa dejú v reálnom čase, tiež to proces urýchli.

Ako používať kódex?

Pri kopírovaní a prilepení tohto kódu do editora VB postupujte podľa nasledujúcich pokynov:

1. Otvorte súbor Excel, do ktorého chcete pridať kód VBA
2. Podržte kláves ALT a stlačte kláves F-11 (môžete tiež prejsť na kartu Vývojár a kliknúť na ikonu Visual Basic)
3. V editore jazyka Visual Basic, ktorý sa otvorí, by bol v ľavej časti editora VBA Project Explorer. Ak to nevidíte, kliknite na kartu „Zobraziť“ a potom kliknite na „Prieskumník projektu“
4. Kliknite pravým tlačidlom myši na ľubovoľný objekt zošita, do ktorého chcete pridať kód
5. Prejdite na možnosť Vložiť a potom kliknite na Modul. Tým sa do zošita pridá nový modul
6. Dvakrát kliknite na ikonu modulu v programe Project Explorer. Otvorí sa okno s kódom pre tento modul
7. Skopírujte a prilepte vyššie uvedený kód VBA do okna kódu

Ak chcete spustiť kód makra VBA, najskôr vyberte množinu údajov, ktorú chcete prevrátiť (okrem hlavičiek).

S vybranými údajmi prejdite do editora VB a kliknite na zelené tlačidlo prehrávania na paneli s nástrojmi alebo vyberte ľubovoľný riadok v kóde a potom stlačte kláves F5

Toto sú niektoré z metód, ktoré môžete použiť na prevrátenie údajov v programe Excel (t. J. Obrátenie poradia množiny údajov).

Všetky metódy, ktorým som sa venoval v tomto návode (vzorce, funkcia SORT a VBA), je možné použiť na prevrátenie údajov vertikálne aj horizontálne (na horizontálne preklopenie údajov budete musieť zodpovedajúcim spôsobom upraviť vzorec a kód VBA).

Dúfam, že ste našli tento návod užitočný.