Překlopit data v aplikaci Excel - Obrácené pořadí dat ve sloupci/řádku

Někdy může být potřeba převrátit data v aplikaci Excel, tj. Obrátit pořadí dat vzhůru nohama ve svislé datové sadě a zleva doprava v horizontální datové sadě.

Nyní, pokud si myslíte, že k tomu musí být v aplikaci Excel vestavěná funkce, obávám se, že budete zklamáni.

I když existuje několik způsobů, jak můžete převrátit data v aplikaci Excel, neexistuje žádná vestavěná funkce. Ale můžete to snadno provést pomocí jednoduchého třídícího triku, vzorců nebo VBA.

V tomto tutoriálu vám ukážu, jak převrátit data v řádcích, sloupcích a tabulkách v aplikaci Excel.

Začněme tedy!

Flip data using SORT a Helper Column

Jedním z nejjednodušších způsobů, jak změnit pořadí dat v aplikaci Excel, by bylo použít pomocný sloupec a poté tento pomocný sloupec použít k třídění dat.

Překlopit data svisle (obrácené pořadí vzhůru nohama)

Předpokládejme, že máte datovou sadu jmen ve sloupci, jak je uvedeno níže, a chcete tato data převrátit:

Níže jsou uvedeny kroky k převrácení dat svisle:

1. Do vedlejšího sloupce zadejte jako nadpis sloupce „Pomocník“
2. Do pomocného sloupce zadejte řadu čísel (1,2,3 atd.). Chcete -li to provést rychle, můžete použít zde uvedené metody
3. Vyberte celou sadu dat včetně pomocného sloupce
4. Klikněte na kartu Data
5. Klikněte na ikonu Seřadit
6. V dialogovém okně Seřadit vyberte v rozevíracím seznamu „Seřadit podle“ „Pomocník“
7. V rozevíracím seznamu Objednávka vyberte možnost „Největší od nejmenšího“
8. Klikněte na OK

Výše uvedené kroky by seřadily data na základě hodnot pomocného sloupce, což by také vedlo k obrácení pořadí jmen v datech.

Až budete hotovi, můžete pomocný sloupec smazat.

V tomto příkladu jsem vám ukázal, jak převrátit data, když máte jen jeden sloupec, ale můžete použít stejnou techniku, pokud máte celou tabulku. Jen se ujistěte, že vyberete celou tabulku a poté pomocí pomocného sloupce seřadíte data sestupně.

Překlopte data vodorovně

Stejnou metodiku můžete použít také k horizontálnímu převrácení dat v Excelu.

Excel má možnost třídit data horizontálně pomocí dialogového okna Seřadit (funkce „Seřadit zleva doprava“).

Předpokládejme, že máte tabulku, jak je uvedeno níže, a chcete tato data převrátit vodorovně.

Níže jsou uvedeny kroky, jak to provést:

1. Do řádku níže zadejte „Helper“ jako nadpis řádku
2. Do pomocného řádku zadejte řadu čísel (1,2,3 atd.).
3. Vyberte celou sadu dat včetně pomocného řádku
4. Klikněte na kartu Data
5. Klikněte na ikonu Seřadit
6. V dialogovém okně Seřadit klikněte na tlačítko Možnosti.
7. V dialogovém okně, které se otevře, klikněte na „Seřadit zleva doprava“
8. Klikněte na OK
9. V rozevíracím seznamu Seřadit podle vyberte řádek 3 (nebo jakýkoli řádek má váš pomocný sloupec)
10. V rozevíracím seznamu Objednávka vyberte možnost „Největší od nejmenšího“
11. Klikněte na OK

Výše uvedené kroky by převrátily celý stůl vodorovně.

Až budete hotovi, můžete pomocnou řadu odebrat.

Převrácení dat pomocí vzorců

Microsoft 365 má několik nových vzorců, které opravdu usnadňují obrácení pořadí sloupců nebo tabulek v aplikaci Excel.

V této části vám ukážu, jak to provést pomocí vzorce SORTBY (pokud používáte Microsoft 365) nebo vzorce INDEX (pokud nepoužíváte Microsoft 365)

Použití funkce SORTBY (k dispozici v Microsoft 365)

Předpokládejme, že máte tabulku, jak je uvedeno níže, a chcete převrátit data v této tabulce:

Chcete -li to provést, nejprve zkopírujte záhlaví a umístěte je tam, kde chcete převrácený stůl

Nyní použijte následující vzorec pod buňkou v záhlaví nejvíce vlevo:

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

Výše uvedený vzorec třídí data a pomocí výsledku funkce ROW jako základu pro jejich řazení.

Funkce ROW by v tomto případě vrátila pole čísel, která představují čísla řádků mezi zadaným rozsahem (což by v tomto případě byla řada čísel, jako jsou 2, 3, 4 atd.).

A protože třetí argument tohoto vzorce je -1, přinutil by vzorec třídit data sestupně.

Záznam, který má nejvyšší číslo řádku, by se nacházel nahoře a ten, který má nejnižší číslo pravidla, by šel dole, což by v podstatě obrátilo pořadí dat.

Až budete hotovi, můžete vzorec převést na hodnoty a získat tak statickou tabulku.

Použití funkce INDEX

V případě, že nemáte přístup k funkci SORTBY, nebojte se - můžete použít úžasnou funkci INDEX.

Předpokládejme, že máte datovou sadu jmen, jak je uvedeno níže, a chcete tato data převrátit.

Níže je uveden vzorec, jak to provést:

= INDEX ($ A $ 2: $ A $ 12, ŘADY (A2: $ A $ 12))

Jak tento vzorec funguje?

Výše uvedený vzorec používá funkci INDEX, která by vrátila hodnotu z buňky na základě čísla uvedeného v druhém argumentu.

Skutečné kouzlo se děje ve druhém argumentu, kde jsem použil funkci ROWS.

Protože jsem zamkl druhou část odkazu ve funkci ROWS, v první buňce by to vrátilo počet řádků mezi A2 a A12, což by bylo 11.

Ale když to jde po řádcích, první reference by se změnila na A3 a pak na A4 atd.

Jak budeme klesat po řádcích, výsledek funkce ROWS by se snížil o 1, z 11 na 10 až 9 atd.

A protože funkce INDEX nám vrací hodnotu na základě čísla ve druhém argumentu, nakonec by nám to dalo data v opačném pořadí.

Stejný vzorec můžete použít, i když máte v sadě dat více sloupců. budete však muset zadat druhý argument, který by určoval číslo sloupce, ze kterého je třeba data načíst.

Předpokládejme, že máte sadu dat, jak je uvedeno níže, a chcete obrátit pořadí celé tabulky:

Níže je vzorec, který to udělá za vás:

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

Jedná se o podobný vzorec, kde jsem také přidal třetí argument, který určuje číslo sloupce, ze kterého by měla být hodnota načtena.

Aby byl tento vzorec dynamický, použil jsem funkci COLUMNS, která by při kopírování doprava stále měnila hodnotu sloupce z 1 na 2 až 3.

Jakmile budete hotovi, můžete převést vzorce na hodnoty, abyste se ujistili, že máte statický výsledek.

Poznámka: Když použijete vzorec pro obrácení pořadí datové sady v aplikaci Excel, nezachová původní formátování. Pokud potřebujete původní formátování také u seřazených dat, můžete je použít buď ručně, nebo zkopírovat a vložit formátování z původní datové sady do nové seřazené datové sady

Flip data using VBA

Pokud je převrácení dat v Excelu něco, co musíte dělat poměrně často, můžete také vyzkoušet metodu VBA.

Pomocí kódu makra VBA jej můžete zkopírovat a vložit jednou do sešitu v editoru VBA a poté znovu použít znovu a znovu ve stejném sešitu.

Kód můžete také uložit do osobního sešitu maker nebo jako doplněk aplikace Excel a můžete jej použít v jakémkoli sešitu ve vašem systému.

Níže je kód VBA, který by svisle převrátil vybraná data v listu.

Sub FlipVerically () 'Code by Sumit Bansal from TrumpExcel.com Dim TopRow As Variant Dim LastRow As Variant Dim StartNum As Integer Dim EndNum As Integer 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

Chcete -li tento kód použít, musíte nejprve provést výběr datové sady, kterou chcete obrátit (kromě záhlaví), a poté tento kód spustit.

Jak tento kód funguje?

Výše uvedený kód nejprve spočítá celkový počet řádků v datové sadě a přiřadí je proměnné EndNum.

Poté používá smyčku Do While, kde dochází k třídění dat.

Třídí tato data tak, že vezme první a poslední řádek a prohodí je. Poté se přesune do 2. řady ve druhé poslední řadě a poté je prohodí. Poté se přesune do třetí řady ve třetí poslední řadě a tak dále.

Smyčka končí, když je provedeno veškeré třídění.

Používá také vlastnost Application.ScreenUpdating a nastaví ji na FALSE při spuštění kódu a poté ji po spuštění spustí zpět na hodnotu TRUE.

Tím zajistíte, že na obrazovce neuvidíte změny, ke kterým dochází v reálném čase, a také to proces urychlí.

Jak kodex používat?

Chcete -li zkopírovat a vložit tento kód do editoru VB, postupujte podle následujících pokynů:

1. Otevřete soubor aplikace Excel, kam chcete přidat kód VBA
2. Podržte klávesu ALT a stiskněte klávesu F-11 (můžete také přejít na kartu Vývojář a kliknout na ikonu Visual Basic)
3. V editoru jazyka, který se otevře, by v levé části editoru VBA byl Project Explorer. Pokud to nevidíte, klikněte na kartu „Zobrazit“ a poté klikněte na „Průzkumník projektů“
4. Klepněte pravým tlačítkem na libovolný objekt sešitu, do kterého chcete přidat kód
5. Přejděte na možnost Vložit a poté klikněte na Modul. Tím se do sešitu přidá nový modul
6. Poklepejte na ikonu modulu v aplikaci Project Explorer. Tím se otevře okno kódu pro daný modul
7. Zkopírujte a vložte výše uvedený kód VBA do okna kódu

Chcete -li spustit kód makra VBA, nejprve vyberte datovou sadu, kterou chcete převrátit (kromě záhlaví).

S vybranými daty přejděte do editoru VB a klikněte na zelené tlačítko přehrávání na panelu nástrojů nebo vyberte libovolný řádek v kódu a poté stiskněte klávesu F5

Toto jsou tedy některé z metod, které můžete použít k převrácení dat v aplikaci Excel (tj. Obrácení pořadí datové sady).

Všechny metody, které jsem pokryl v tomto tutoriálu (vzorce, funkce SORT a VBA), lze použít k převrácení dat svisle a vodorovně (pro horizontální překlopení dat budete muset odpovídajícím způsobem upravit vzorec a kód VBA).

Doufám, že jste našli tento návod užitečný.