Apvērst datus programmā Excel - Apgriezta datu secība kolonnā/rindā

Dažreiz var būt nepieciešams apgriezt datus programmā Excel, t.i., apgriezt datu secību otrādi vertikālā datu kopā un no kreisās uz labo horizontālajā datu kopā.

Tagad, ja jūs domājat, ka programmā Excel ir jābūt iebūvētai funkcijai, es baidos, ka jūs būsiet vīlušies.

Lai gan ir vairāki veidi, kā jūs varat apgriezt datus programmā Excel, nav iebūvēta līdzekļa. Bet to var viegli izdarīt, izmantojot vienkāršu šķirošanas triku, formulas vai VBA.

Šajā apmācībā es parādīšu, kā pārvērst datus Excel rindās, kolonnās un tabulās.

Tātad, sāksim!

Apgrieziet datus, izmantojot SORT un Helper kolonnu

Viens no vienkāršākajiem veidiem, kā mainīt datu secību programmā Excel, būtu izmantot palīgu kolonnu un pēc tam izmantot šo palīga kolonnu, lai kārtotu datus.

Apgriezt datus vertikāli (apgrieztā secībā otrādi)

Pieņemsim, ka slejā ir datu nosaukumu kopa, kā parādīts zemāk, un vēlaties apvērst šos datus:

Tālāk ir norādītas darbības, lai apgrieztu datus vertikāli:

1. Blakus esošajā slejā kā slejas virsrakstu ievadiet “Helper”
2. Palīga kolonnā ievadiet skaitļu sēriju (1,2,3 utt.). Lai to izdarītu ātri, varat izmantot šeit parādītās metodes
3. Atlasiet visu datu kopu, ieskaitot palīgu kolonnu
4. Noklikšķiniet uz cilnes Dati
5. Noklikšķiniet uz ikonas Kārtot
6. Dialoglodziņā Kārtot nolaižamajā izvēlnē Kārtot pēc atlasiet Palīgs
7. Nolaižamajā pasūtījuma izvēlnē atlasiet “Lielākais līdz mazākais”
8. Noklikšķiniet uz Labi

Iepriekš minētās darbības sakārtotu datus, pamatojoties uz palīga kolonnas vērtībām, kā rezultātā tiktu mainīta arī datu nosaukumu secība.

Kad esat pabeidzis, lūdzu, izdzēsiet palīga kolonnu.

Šajā piemērā es jums parādīju, kā apvērst datus, ja jums ir tikai viena kolonna, taču varat izmantot to pašu paņēmienu, ja jums ir visa tabula. Vienkārši pārliecinieties, ka esat atlasījis visu tabulu un pēc tam izmantojiet palīgu kolonnu, lai kārtotu datus dilstošā secībā.

Apgrieziet datus horizontāli

Varat arī izmantot to pašu metodiku, lai Excel programmā horizontāli apgrieztu datus.

Programmai Excel ir iespēja kārtot datus horizontāli, izmantojot dialoglodziņu Kārtot (funkcija "Kārtot no kreisās uz labo").

Pieņemsim, ka jums ir tabula, kā parādīts zemāk, un vēlaties šos datus apgriezt horizontāli.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Zemāk esošajā rindā kā rindas virsrakstu ievadiet “Palīgs”
2. Palīgrindā ievadiet skaitļu sēriju (1,2,3 utt.).
3. Atlasiet visu datu kopu, ieskaitot palīga rindu
4. Noklikšķiniet uz cilnes Dati
5. Noklikšķiniet uz ikonas Kārtot
6. Dialoglodziņā Kārtot noklikšķiniet uz pogas Opcijas.
7. Atvērtajā dialoglodziņā noklikšķiniet uz "Kārtot no kreisās uz labo"
8. Noklikšķiniet uz Labi
9. Nolaižamajā izvēlnē Kārtot pēc atlasiet 3. rindu (vai jebkuru rindu, kurā ir jūsu palīga kolonna)
10. Nolaižamajā pasūtījuma izvēlnē atlasiet “Lielākais līdz mazākais”
11. Noklikšķiniet uz Labi

Iepriekš minētās darbības apvērsīs visu tabulu horizontāli.

Kad esat pabeidzis, varat noņemt palīga rindu.

Apvērst datus, izmantojot formulas

Microsoft 365 ir ieguvis dažas jaunas formulas, kas patiešām atvieglo kolonnas vai tabulas secības maiņu programmā Excel.

Šajā sadaļā es parādīšu, kā to izdarīt, izmantojot SORTBY formulu (ja izmantojat Microsoft 365) vai INDEX formulu (ja neizmantojat Microsoft 365)

Funkcijas SORTBY izmantošana (pieejama Microsoft 365)

Pieņemsim, ka jums ir tabula, kā parādīts zemāk, un vēlaties apgriezt šīs tabulas datus:

Lai to izdarītu, vispirms nokopējiet galvenes un novietojiet tās vietā, kur vēlaties pārvērst tabulu

Tagad izmantojiet šādu formulu zem šūnas kreisās galvenes šūnā:

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

Iepriekš minētā formula kārto datus un, lai tos kārtotu, izmantojot ROW funkcijas rezultātu.

Funkcija ROW šajā gadījumā atgriezīs skaitļu masīvu, kas attēlo rindu numurus starp norādīto diapazonu (kas šajā piemērā būtu skaitļu sērija, piemēram, 2, 3, 4 utt.).

Un tā kā šīs formulas trešais arguments ir -1, tas liktu formulai sakārtot datus dilstošā secībā.

Ieraksts, kuram ir visaugstākais rindas numurs, būtu augšpusē, un tas, kuram ir zemākais kārtulas numurs, tiktu novietots apakšā, būtībā mainot datu secību.

Kad esat pabeidzis, varat pārveidot formulu par vērtībām, lai iegūtu statisku tabulu.

Funkcijas INDEX izmantošana

Ja jums nav piekļuves funkcijai SORTBY, neuztraucieties - varat izmantot pārsteidzošo INDEX funkciju.

Pieņemsim, ka jums ir nosaukumu datu kopa, kā parādīts zemāk, un vēlaties šos datus apvērst.

Zemāk ir formula, kā to izdarīt:

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

Kā šī formula darbojas?

Iepriekšminētā formula izmanto funkciju INDEX, kas atgrieztu šūnas vērtību, pamatojoties uz otrajā argumentā norādīto skaitli.

Īstā maģija notiek otrajā argumentā, kur esmu izmantojis ROWS funkciju.

Tā kā es esmu bloķējis atsauces otro daļu funkcijā ROWS, pirmajā šūnā tas atgriezīs rindu skaitu starp A2 un A12, kas būtu 11.

Bet, kad tas iet uz leju rindās, pirmā atsauce mainītos uz A3, pēc tam uz A4 un tā tālāk, bet otrā atsauce paliktu tāda, kāda tā ir, jo esmu to bloķējusi un padarījusi absolūtu.

Ejot pa rindām, funkcijas ROWS rezultāts samazināsies par 1, no 11 līdz 10 līdz 9 utt.

Un tā kā funkcija INDEX atgriež mums vērtību, pamatojoties uz otrā argumenta skaitli, tas galu galā dod mums datus apgrieztā secībā.

To pašu formulu var izmantot pat tad, ja datu kopā ir vairākas kolonnas. tomēr jums būs jānorāda otrs arguments, kas norādītu slejas numuru, no kura dati ir jāiegūst.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties mainīt visas tabulas secību:

Zemāk ir formula, kas to darīs jūsu vietā:

= INDEKSS ($ A $ 2: $ B $ 12, ROWS (A2: $ A $ 12), SLEJAS ($ A $ 2: A2))

Šī ir līdzīga formula, kurā esmu pievienojis arī trešo argumentu, kas norāda kolonnas numuru, no kura jāiegūst vērtība.

Lai šo formulu padarītu dinamisku, esmu izmantojis funkciju SLEJAS, kas nemitīgi mainīs kolonnas vērtību no 1 uz 2 līdz 3, kopējot to pa labi.

Kad esat pabeidzis, formulas var pārvērst vērtībās, lai pārliecinātos, ka jums ir statisks rezultāts.

Piezīme. Ja izmantojat formulu, lai mainītu datu kopas secību programmā Excel, tā nesaglabātu sākotnējo formatējumu. Ja arī sakārtotajiem datiem ir nepieciešams sākotnējais formatējums, varat to lietot manuāli vai kopēt un ielīmēt formatējumu no sākotnējās datu kopas jaunajā sakārtotajā datu kopā

Apgrieziet datus, izmantojot VBA

Ja datu apgriešana programmā Excel ir jādara diezgan bieži, varat izmēģināt arī VBA metodi.

Izmantojot VBA makro kodu, varat to vienu reizi kopēt un ielīmēt darbgrāmatā VBA redaktorā un pēc tam atkal un atkal izmantot tajā pašā darbgrāmatā.

Varat arī saglabāt kodu personiskajā makro darbgrāmatā vai kā Excel pievienojumprogrammu un izmantot to jebkurā savas sistēmas darbgrāmatā.

Zemāk ir VBA kods, kas darblapā vertikāli apvērstu atlasītos datus.

Sub FlipVerically () 'kods: Sumit Bansal no TrumpExcel.com Dim TopRow kā variants Dim LastRow kā variants Dim StartNum kā vesels skaitlis Dim EndNum kā vesels skaitlis 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

Lai izmantotu šo kodu, vispirms jāizvēlas datu kopa, kuru vēlaties mainīt (izņemot galvenes), un pēc tam palaidiet šo kodu.

Kā šis kods darbojas?

Iepriekš minētais kods vispirms saskaita kopējo datu kopas rindu skaitu un piešķir to mainīgajam EndNum.

Pēc tam tiek izmantota cilne “Do Do”, kur notiek datu kārtošana.

Šie dati tiek sakārtoti, ņemot pirmo un pēdējo rindu un mainot tos. Pēc tam tas pāriet uz otro rindu otrajā pēdējā rindā un pēc tam tos apmaina. Tad tas pāriet uz trešo rindu trešajā pēdējā rindā un tā tālāk.

Cilpa beidzas, kad ir pabeigta visa šķirošana.

Tas arī izmanto rekvizītu Application.ScreenUpdating un, palaižot kodu, iestata to uz FALSE, un pēc tam pagrieziet to atpakaļ uz TRUE, kad kods ir beidzis darboties.

Tas nodrošina, ka ekrānā neredzat izmaiņas reāllaikā, kā arī paātrina procesu.

Kā lietot kodeksu?

Lai kopētu un ielīmētu šo kodu VB redaktorā, veiciet tālāk norādītās darbības.

1. Atveriet Excel failu, kuram vēlaties pievienot VBA kodu
2. Turiet taustiņu ALT un nospiediet taustiņu F-11 (varat arī doties uz cilni Izstrādātājs un noklikšķināt uz ikonas Visual Basic)
3. Atvērtajā Visual Basic redaktorā VBA redaktora kreisajā daļā būs Project Explorer. Ja jūs to neredzat, noklikšķiniet uz cilnes “Skatīt” un pēc tam uz “Project Explorer”
4. Ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta, kurā vēlaties pievienot kodu
5. Dodieties uz opciju Ievietot un pēc tam noklikšķiniet uz moduļa. Tādējādi darbgrāmatai tiks pievienots jauns modulis
6. Veiciet dubultklikšķi uz moduļa ikonas Project Explorer. Tiks atvērts šī moduļa koda logs
7. Kopējiet un ielīmējiet iepriekš minēto VBA kodu koda logā

Lai palaistu VBA makro kodu, vispirms atlasiet datu kopu, kuru vēlaties apvērst (izņemot galvenes).

Kad dati ir atlasīti, dodieties uz VB redaktoru un rīkjoslā noklikšķiniet uz zaļās atskaņošanas pogas vai atlasiet jebkuru koda rindiņu un pēc tam nospiediet taustiņu F5

Tātad šīs ir dažas no metodēm, kuras varat izmantot, lai apgrieztu datus programmā Excel (t.i., mainītu datu kopas secību).

Visas metodes, kuras esmu apskatījis šajā apmācībā (formulas, SORT funkcija un VBA), var izmantot, lai apvērstu datus vertikāli un horizontāli (lai mainītu horizontālos datus, jums attiecīgi jāpielāgo formula un VBA kods).

Es ceru, ka šī apmācība jums šķita noderīga.