Odwróć dane w Excelu - Odwrotna kolejność danych w kolumnie/wierszu

Czasami możesz potrzebować odwrócić dane w programie Excel, tj. Odwrócić kolejność danych do góry nogami w pionowym zestawie danych i od lewej do prawej w poziomym zestawie danych.

Teraz, jeśli myślisz, że musi być wbudowana funkcja, aby to zrobić w Excelu, obawiam się, że będziesz rozczarowany.

Chociaż istnieje wiele sposobów odwracania danych w programie Excel, nie ma wbudowanej funkcji. Ale możesz to łatwo zrobić, używając prostej sztuczki sortowania, formuł lub VBA.

W tym samouczku pokażę, jak odwracać dane w wierszach, kolumnach i tabelach w programie Excel.

Więc zacznijmy!

Odwróć dane za pomocą sortowania i kolumny pomocniczej

Jednym z najłatwiejszych sposobów odwrócenia kolejności danych w programie Excel byłoby użycie kolumny pomocniczej, a następnie użycie tej kolumny pomocniczej do sortowania danych.

Odwróć dane w pionie (odwrotna kolejność do góry nogami)

Załóżmy, że masz zestaw danych z nazwami w kolumnie, jak pokazano poniżej i chcesz odwrócić te dane:

Poniżej znajdują się kroki, aby odwrócić dane w pionie:

1. W sąsiedniej kolumnie wpisz „Pomocnik” jako nagłówek kolumny
2. W kolumnie pomocnika wprowadź serię liczb (1,2,3 itd.). Możesz użyć pokazanych tutaj metod, aby zrobić to szybko
3. Wybierz cały zestaw danych, w tym kolumnę pomocnika
4. Kliknij kartę Dane
5. Kliknij ikonę Sortuj
6. W oknie dialogowym Sortuj wybierz „Pomocnik” z listy rozwijanej „Sortuj według”
7. W menu Zamówienie wybierz „Od największego do najmniejszego”
8. Kliknij OK

Powyższe kroki posortowałyby dane na podstawie wartości kolumn pomocniczych, co również doprowadziłoby do odwrócenia kolejności nazw w danych.

Po zakończeniu możesz usunąć kolumnę pomocnika.

W tym przykładzie pokazałem, jak odwrócić dane, gdy masz tylko jedną kolumnę, ale możesz również użyć tej samej techniki, jeśli masz całą tabelę. Upewnij się tylko, że wybrałeś całą tabelę, a następnie użyj kolumny pomocniczej, aby posortować dane w kolejności malejącej.

Odwróć dane w poziomie

Możesz również zastosować tę samą metodologię, aby odwrócić dane w poziomie w programie Excel.

Excel ma opcję sortowania danych w poziomie za pomocą okna dialogowego Sortuj (funkcja „Sortuj od lewej do prawej”).

Załóżmy, że masz tabelę pokazaną poniżej i chcesz odwrócić te dane w poziomie.

Poniżej znajdują się kroki, aby to zrobić:

1. W wierszu poniżej wpisz „Pomocnik” jako nagłówek wiersza
2. W wierszu pomocnika wprowadź serię liczb (1,2,3 itd.).
3. Wybierz cały zestaw danych, w tym wiersz pomocniczy
4. Kliknij kartę Dane
5. Kliknij ikonę Sortuj
6. W oknie dialogowym Sortuj kliknij przycisk Opcje.
7. W wyświetlonym oknie dialogowym kliknij „Sortuj od lewej do prawej”
8. Kliknij OK
9. Z listy rozwijanej Sortuj według wybierz Wiersz 3 (lub dowolny wiersz z kolumną pomocniczą)
10. W menu Zamówienie wybierz „Od największego do najmniejszego”
11. Kliknij OK

Powyższe kroki obróciłyby cały stół w poziomie.

Po zakończeniu możesz usunąć wiersz pomocników.

Odwróć dane za pomocą formuł

Microsoft 365 ma kilka nowych formuł, które naprawdę ułatwiają odwrócenie kolejności kolumn lub tabeli w Excelu.

W tej sekcji pokażę, jak to zrobić za pomocą formuły SORTUJ (jeśli korzystasz z platformy Microsoft 365) lub formuły INDEKS (jeśli nie korzystasz z platformy Microsoft 365)

Korzystanie z funkcji SORTUJ (dostępna w Microsoft 365)

Załóżmy, że masz tabelę pokazaną poniżej i chcesz odwrócić dane w tej tabeli:

Aby to zrobić, najpierw skopiuj nagłówki i umieść je tam, gdzie chcesz odwrócić tabelę

Teraz użyj następującej formuły poniżej komórki w lewym nagłówku:

=SORTUJ($A$2:$B$12;WIERSZ(A2:A12);-1)

Powyższa formuła sortuje dane i korzystając z wyniku funkcji WIERSZ jako podstawy do ich sortowania.

W tym przypadku funkcja WIERSZ zwróci tablicę liczb reprezentującą numery wierszy w określonym zakresie (który w tym przykładzie byłby serią liczb, na przykład 2, 3, 4 itd.).

A ponieważ trzecim argumentem tej formuły jest -1, zmusiłoby to formułę do sortowania danych w kolejności malejącej.

Rekord o najwyższym numerze wiersza znalazłby się na górze, a rekord o najniższym numerze reguły znalazłby się na dole, zasadniczo odwracając kolejność danych.

Po zakończeniu możesz przekonwertować formułę na wartości, aby uzyskać tabelę statyczną.

Korzystanie z funkcji INDEKS

Jeśli nie masz dostępu do funkcji SORTBY, nie martw się - możesz skorzystać z niesamowitej funkcji INDEX.

Załóżmy, że masz zestaw danych z nazwami, jak pokazano poniżej i chcesz odwrócić te dane.

Poniżej znajduje się formuła, aby to zrobić:

=INDEKS($A$2:$A$12;WIERSZE(A2:$A$12))

Jak działa ta formuła?

Powyższa formuła używa funkcji INDEKS, która zwróci wartość z komórki na podstawie liczby określonej w drugim argumencie.

Prawdziwa magia dzieje się w drugim argumencie, w którym użyłem funkcji WIERSZY.

Ponieważ zablokowałem drugą część odwołania w funkcji WIERSZY, w pierwszej komórce zwróci liczbę wierszy między A2 a A12, czyli 11.

Ale kiedy przesuwa się w dół wierszy, pierwsze odniesienie zmieni się na A3, potem na A4 i tak dalej, podczas gdy drugie odwołanie pozostanie bez zmian, ponieważ je zablokowałem i uczyniłem bezwzględnym.

Gdy idziemy w dół wierszy, wynik funkcji WIERSZE zmniejszy się o 1, z 11 do 10 do 9 i tak dalej.

A ponieważ funkcja INDEX zwraca nam wartość opartą na liczbie w drugim argumencie, ostatecznie da nam to dane w odwrotnej kolejności.

Możesz użyć tej samej formuły, nawet jeśli w zestawie danych jest wiele kolumn. jednak będziesz musiał podać drugi argument, który określi numer kolumny, z której dane mają być pobrane.

Załóżmy, że masz zestaw danych, jak pokazano poniżej i chcesz odwrócić kolejność całej tabeli:

Poniżej znajduje się formuła, która zrobi to za Ciebie:

=INDEKS($A$2:$B$12;WIERSZE(A2:$A$12);KOLUMNY($A$2:A2))

Jest to podobna formuła, w której dodałem również trzeci argument określający numer kolumny, z której należy pobrać wartość.

Aby uczynić tę formułę dynamiczną, użyłem funkcji COLUMNS, która zmieniałaby wartość kolumny z 1 na 2 na 3 podczas kopiowania jej w prawo.

Po zakończeniu możesz przekonwertować formuły na wartości, aby upewnić się, że masz statyczny wynik.

Uwaga: W przypadku użycia formuły do ​​odwrócenia kolejności zestawu danych w programie Excel oryginalne formatowanie nie zostanie zachowane. Jeśli potrzebujesz oryginalnego formatowania również dla posortowanych danych, możesz zastosować je ręcznie lub skopiować i wkleić formatowanie z oryginalnego zestawu danych do nowego posortowanego zestawu danych

Odwróć dane za pomocą VBA

Jeśli odwracanie danych w Excelu jest czymś, co musisz robić dość często, możesz również wypróbować metodę VBA.

Za pomocą kodu makra VBA można go skopiować i wkleić raz w skoroszycie w edytorze VBA, a następnie używać go wielokrotnie w tym samym skoroszycie.

Możesz również zapisać kod w skoroszycie makr osobistych lub jako dodatek programu Excel i używać go w dowolnym skoroszycie w systemie.

Poniżej znajduje się kod VBA, który odwróciłby wybrane dane w pionie w arkuszu.

Sub FlipVerically() 'Kod autorstwa Sumit Bansal z 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) = Wybór TopRow.Rows(StartNum) = LastRow StartNum = StartNum + 1EndNum = EndNum - 1 pętla Application.ScreenUpdating = True End Sub

Aby użyć tego kodu, musisz najpierw wybrać zestaw danych, który chcesz odwrócić (z wyłączeniem nagłówków), a następnie uruchomić ten kod.

Jak działa ten kod?

Powyższy kod najpierw zlicza całkowitą liczbę wierszy w zestawie danych i przypisuje ją do zmiennej EndNum.

Następnie używa pętli Do While, w której odbywa się sortowanie danych.

Sposób sortowania tych danych polega na pobraniu pierwszego i ostatniego wiersza i zamienieniu ich. Następnie przechodzi do drugiego rzędu w przedostatnim rzędzie, a następnie je zamienia. Następnie przechodzi do trzeciego rzędu w trzecim ostatnim rzędzie i tak dalej.

Pętla kończy się po zakończeniu sortowania.

Używa również właściwości Application.ScreenUpdating i ustawia ją na FALSE podczas uruchamiania kodu, a następnie przywraca ją do wartości TRUE po zakończeniu działania kodu.

Gwarantuje to, że nie zobaczysz zmian zachodzących w czasie rzeczywistym na ekranie, co również przyspiesza proces.

Jak korzystać z Kodeksu?

Wykonaj poniższe czynności, aby skopiować i wkleić ten kod w edytorze VB:

1. Otwórz plik Excel, do którego chcesz dodać kod VBA
2. Przytrzymaj klawisz ALT i naciśnij klawisz F-11 (możesz również przejść do zakładki Deweloper i kliknąć ikonę Visual Basic)
3. W edytorze Visual Basic, który się otworzy, po lewej stronie edytora VBA będzie znajdować się Eksplorator projektów. Jeśli go nie widzisz, kliknij kartę „Widok”, a następnie kliknij „Eksplorator projektu”
4. Kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu, do którego chcesz dodać kod
5. Przejdź do opcji Wstaw, a następnie kliknij Moduł. Spowoduje to dodanie nowego modułu do skoroszytu
6. Kliknij dwukrotnie ikonę modułu w Eksploratorze projektów. Spowoduje to otwarcie okna kodu dla tego modułu
7. Skopiuj i wklej powyższy kod VBA do okna kodu

Aby uruchomić kod makra VBA, najpierw wybierz zestaw danych, który chcesz odwrócić (z wyłączeniem nagłówków).

Po wybraniu danych przejdź do edytora VB i kliknij zielony przycisk odtwarzania na pasku narzędzi lub wybierz dowolną linię w kodzie, a następnie naciśnij klawisz F5

Oto niektóre z metod, których możesz użyć do odwrócenia danych w programie Excel (tj. Odwrócenia kolejności zestawu danych).

Wszystkie metody, które omówiłem w tym samouczku (formuły, funkcja SORT i VBA), mogą być użyte do odwrócenia danych w pionie i poziomie (będziesz musiał odpowiednio dostosować formułę i kod VBA do odwracania danych w poziomie).

Mam nadzieję, że ten samouczek okazał się przydatny.