Otočte údaje hore nohami v Exceli (obrátte poradie údajov v stĺpcoch/riadkoch)

Niekedy možno budete musieť prevrátiť údaje v Exceli hore nohami, to znamená obrátiť poradie údajov vo vertikálnom súbore údajov a otočiť poradie údajov zľava doprava v horizontálnom súbore údajov.

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

Aj keď existuje niekoľko spôsobov, ako prevrátiť údaje v Exceli, nie je tam žiadna vstavaná funkcia.Ale môžete to ľahko urobiť pomocou jednoduchých triediacich trikov, vzorcov alebo VBA.

V tomto návode vám ukážem, ako prevrátiť údaje v riadkoch, stĺpcoch a tabuľkách hore nohami v Exceli.

Tak poďme na to!

Preklápajte údaje pomocou SORT a pomocných stĺpcov

Jedným z najjednoduchších spôsobov, ako obrátiť poradie údajov v Exceli, je použiť pomocný stĺpec a potom použiť tento pomocný stĺpec na zoradenie údajov.

Vertikálne preklopenie údajov (v opačnom poradí)

Predpokladajme, že máte stĺpec ako nižšieNázov súboru údajova chcete prevrátiť tieto údaje:

Tu sú kroky na vertikálne prevrátenie údajov:

1. V susednom stĺpci zadajte ako nadpis stĺpca „Pomocník“.
2. Do stĺpca pomocníka zadajte sériu čísel (1, 2, 3 atď.).Môžete to urobiť rýchlo pomocou tu uvedenej metódy
3. Vyberte celú množinu údajov vrátane pomocných stĺpcov
4. Kliknite na kartu Údaje
5. Kliknite na ikonu zoradenia
6. V dialógovom okne Zoradiť vyberte položku Pomocník z rozbaľovacej ponuky Zoradiť podľa
7. V rozbaľovacej ponuke objednávky vyberte možnosť „Veľké po malé“
8. Kliknite na tlačidlo OK

Vyššie uvedené kroky zoradia údaje na základe hodnôt pomocného stĺpca, čo tiež spôsobí, že názvy v údajoch budú v opačnom poradí.

Po dokončení môžete stĺpec pomocníka vypustiť.

V tomto príklade vám ukážem, ako prevrátiť údaje, keď existuje iba jeden stĺpec, ale môžete použiť rovnakú techniku, ak máte celú tabuľku.Len sa uistite, že ste vybrali celú tabuľku a potom použite pomocný stĺpec na zoradenie údajov v zostupnom poradí.

Preklopte údaje vodorovne

Rovnakým spôsobom môžete údaje v Exceli prevrátiť aj horizontálne.

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

Predpokladajme, že máte tabuľku zobrazenú nižšie a chcete tieto údaje vodorovne prevrátiť.

Tu sú kroky, ako to urobiť:

1. V riadku nižšie zadajte „Pomocník“ ako názov riadku
2. V pomocnom riadku zadajte sériu čísel (1, 2, 3 atď.).
3. Vyberte celú množinu údajov vrátane pomocných riadkov
4. Kliknite na kartu Údaje
5. Kliknite na ikonu zoradenia
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 čokoľvek, čo má váš pomocný stĺpec)
10. V rozbaľovacej ponuke objednávky vyberte možnosť „Veľké po malé“
11. Kliknite na tlačidlo OK

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

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

Použite Excel na otočenie vzorcov hore nohamipreklopiť dáta

Microsoft 365 má niekoľko nových vzorcov, vďaka ktorým je veľmi jednoduché obrátiť poradie stĺpca alebo tabuľky v Exceli.

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 (dostupná v Microsoft 365)

Predpokladajme, že máte tabuľku, ako je táto, 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 tabuľku prevrátiť

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

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

Vyššie uvedený vzorec zoradí údaje a zoradí na základe výsledku funkcie ROW.

V tomto prípade funkcia ROW vráti pole čísel reprezentujúcich čísla riadkov v zadanom rozsahu (v tomto príklade sériu čísel, ako napríklad 2, 3, 4 atď.).

Keďže tretí parameter tohto vzorca je -1, núti vzorec zoradiť údaje v zostupnom poradí.

Záznam s najvyšším číslom riadku bude navrchu a záznam s najnižším číslom pravidla bude naspodku, čím sa v zásade zmení poradie údajov.

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

Použite funkciu indexu

Ak nemôžete použiť funkciu SORTBY, nebojte sa - môžete použiť úžasnú funkciu INDEX.

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

Tu je 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á vráti hodnotu z bunky na základe čísla zadaného v druhom parametri.

Skutočná mágia sa stane, keď použijem druhý parameter funkcie ROWS.

Keďže som uzamkol druhú časť odkazu vo funkcii ROWS, v prvej bunke vráti počet riadkov medzi A2 a A12, čo je 11.

Ale ako to ide dole v riadku, prvá referencia sa zmení na A3, potom A4 atď., zatiaľ čo druhá referencia zostane taká, aká je, pretože som ju uzamkol a urobil ju absolútnou.

Keď ideme nadol, výsledok funkcie ROWS sa zníži o 1, z 11 na 10 na 9 atď.

Keďže funkcia INDEX vracia hodnotu založenú na čísle v druhom argumente, výsledkom je, že údaje sú v opačnom poradí.

Rovnaký vzorec môžete použiť aj v prípade, že množina údajov obsahuje viacero stĺpcov.Musíte však zadať druhý parameter, ktorý určí číslo stĺpca, z ktorého je potrebné načítať údaje.

Predpokladajme, že máte množinu údajov, ktorá vyzerá takto, a chcete obrátiť poradie celej tabuľky:

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

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

Tu je podobný vzorec, do ktorého som pridal aj tretí parameter, ktorý určuje číslo stĺpca, z ktorého sa má hodnota prevziať.

Aby bol tento vzorec dynamický, použil som funkciu COLUMNS, ktorá neustále mení hodnotu stĺpca z 1 na 2 až 3, keď ho kopírujete doprava.

Po dokončení môžete vzorec previesť na hodnotu, aby ste zabezpečili statické výsledky.

Poznámka: Keď použijete vzorec na obrátenie poradia množiny údajov v Exceli, nezachová sa pôvodné formátovanie.Ak potrebujete aj pôvodné formátovanie zoradených údajov, môžete ho použiť manuálne alebo skopírovať a prilepiť formátovanie z pôvodného súboru údajov do nového zoradeného súboru údajov

Preklopenie údajov pomocou VBA

Ak je preklápanie údajov v Exceli niečo, čo musíte robiť často, môžete vyskúšať aj metódu VBA.

Pomocou kódu makra VBA ho môžete skopírovať a prilepiť raz do zošita v editore VBA a potom ho znova a znova použiť v tom istom zošite.

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

Nižšie je uvedený kód VBA na vertikálne prevrátenie vybratých údajov v pracovnom hárku.

Sub FlipVerically()

Dim TopRow ako variant
Dim LastRow ako variant
Dim StartNum as Integer
Dim EndNum ako Integer

Application.ScreenUpdating = False

Počiatočné číslo = 1
EndNum = Výber. Počet riadkov

Urobiť Kým StartNum < EndNum
TopRow = Výber. Riadky (počiatočné číslo)
Posledný riadok = výber. Riadky (koncové číslo)
Selection.Rows(EndNum) = TopRow
Selection.Rows(StartNum) = PoslednýRow
PočiatočnéNum = PočiatočnéNum + 1
EndNum = EndNum - 1
Slučka

Application.ScreenUpdating = Pravda
End Sub

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

Ako tento kód funguje?

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

Potom použije slučku Do While na triedenie údajov.

Tieto údaje triedi tak, že vezme prvý a posledný riadok a vymení ich.Potom sa presunie do druhého radu predposledného radu a potom ich vymení.Potom sa presunie do tretieho radu od tretieho po posledný riadok atď.

Cyklus sa skončí, keď je všetko triedenie dokončené.

Používa tiež vlastnosť Application.ScreenUpdating a nastaví ju na FALSE, keď je kód spustený, a potom ju vráti späť na TRUE, keď kód skončí.

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

Ako použiť kód?

Skopírujte a prilepte tento kód do editora VB takto:

1. Otvorte súbor programu Excel, do ktorého chcete pridať kód VBA
2. Podržte ALT a stlačte F-11 (môžete tiež prejsť na kartu vývojára a kliknúť na ikonu Visual Basic)
3. V editore Visual Basic, ktorý sa otvorí, bude na ľavej strane editora VBA Project Explorer.Ak ho nevidíte, kliknite na kartu Zobraziť a potom kliknite na položku Project Explorer
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 kliknite na Modul.Tým sa do zošita pridá nový modul
6. Dvakrát kliknite na ikonu modulu v Project Explorer.Tým sa otvorí okno s kódom pre daný 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čky).

S vybratý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 stlačte kláves F5

Toto sú niektoré zo spôsobov, ako môžete prevrátiť údaje v Exceli (tj obrátiť poradie množiny údajov).

Všetky metódy, ktoré som prebral v tomto návode (vzorce, funkcie SORT a VBA), sa dajú použiť na vertikálne a horizontálne prevrátenie údajov (pre horizontálne preklápanie údajov musíte zodpovedajúcim spôsobom upraviť vzorce a kód VBA).

Dúfam, že vám bol tento návod užitočný.