Käännä tiedot Excelissä - Käänteinen järjestys sarakkeessa/rivillä

Joskus saatat joutua kääntämään tiedot Excelissä, ts. Kääntämään tietojen järjestyksen ylösalaisin pystysuorassa tietojoukossa ja vasemmalta oikealle horisontaalisessa tietojoukossa.

Jos ajattelet, että Excelissä on oltava sisäänrakennettu ominaisuus tämän suorittamiseksi, pelkään, että olet pettynyt.

Vaikka Excelissä on useita tapoja kääntää tietoja, sisäänrakennettua ominaisuutta ei ole. Mutta voit tehdä tämän helposti käyttämällä yksinkertaista lajittelutemppua, kaavoja tai VBA: ta.

Tässä opetusohjelmassa näytän, kuinka voit kääntää Excelin rivien, sarakkeiden ja taulukoiden tiedot.

Aloitetaan siis!

Käännä tiedot SORT- ja Helper -sarakkeen avulla

Yksi helpoimmista tavoista kääntää tietojen järjestys Excelissä on käyttää auttajasaraketta ja lajitella sitten kyseinen sarake.

Käännä tiedot pystysuoraan (käänteinen järjestys ylösalaisin)

Oletetaan, että sinulla on nimien tietojoukko alla olevassa sarakkeessa ja haluat kääntää nämä tiedot:

Alla on vaiheet tietojen kääntämiseksi pystysuunnassa:

1. Kirjoita viereiseen sarakkeeseen "Helper" sarakkeen otsikkoon
2. Kirjoita auttaja -sarakkeeseen numerosarja (1,2,3 ja niin edelleen). Voit tehdä tämän nopeasti käyttämällä tässä esitettyjä menetelmiä
3. Valitse koko tietojoukko, mukaan lukien auttaja -sarake
4. Napsauta Data -välilehteä
5. Napsauta Lajittele -kuvaketta
6. Valitse Lajittele -valintaikkunan avattavasta Lajitteluperuste -valikosta Helper
7. Valitse avattavasta Tilaa-valikosta "Suurimmasta pienimpään"
8. Napsauta OK

Yllä olevat vaiheet lajittelisivat tiedot auttajasarakkeen arvojen perusteella, mikä johtaisi myös tietojen nimien järjestyksen muuttamiseen.

Kun olet valmis, voit poistaa auttajasarakkeen.

Tässä esimerkissä olen näyttänyt sinulle, kuinka voit kääntää tiedot, kun sinulla on vain yksi sarake, mutta voit myös käyttää samaa tekniikkaa, jos sinulla on koko taulukko. Varmista vain, että valitset koko taulukon ja lajittelet tiedot sitten alenevassa järjestyksessä apuohjelman sarakkeen avulla.

Käännä tiedot vaakasuoraan

Voit myös käyttää samaa menetelmää kääntääksesi tiedot vaakasuunnassa Excelissä.

Excelissä on mahdollisuus lajitella tiedot vaakasuunnassa Lajittelu -valintaikkunan avulla (Lajittele vasemmalta oikealle -ominaisuus).

Oletetaan, että sinulla on alla olevan taulukon mukainen taulukko ja haluat kääntää nämä tiedot vaakasuunnassa.

Alla on vaiheet tämän tekemiseen:

1. Kirjoita alla olevaan riviin otsikko "Helper"
2. Kirjoita apuriville numerosarja (1,2,3 jne.).
3. Valitse koko tietojoukko apurivi mukaan lukien
4. Napsauta Data -välilehteä
5. Napsauta Lajittele -kuvaketta
6. Napsauta Lajittele -valintaikkunan Asetukset -painiketta.
7. Napsauta avautuvassa valintaikkunassa Lajittele vasemmalta oikealle
8. Napsauta OK
9. Valitse avattavasta Lajitteluperuste-valikosta rivi 3 (tai millä tahansa rivillä on auttajan sarake)
10. Valitse avattavasta Tilaa-valikosta "Suurimmasta pienimpään"
11. Napsauta OK

Yllä olevat vaiheet kääntäisivät koko taulukon vaakasuoraan.

Kun olet valmis, voit poistaa apurivin.

Käännä tiedot kaavojen avulla

Microsoft 365: ssä on uusia kaavoja, joiden avulla sarakkeen tai taulukon järjestyksen kääntäminen Excelissä on todella helppoa.

Tässä osiossa näytän sinulle, kuinka voit tehdä tämän käyttämällä SORTBY -kaavaa (jos käytät Microsoft 365: tä) tai INDEX -kaavaa (jos et käytä Microsoft 365: tä)

SORTBY -toiminnon käyttäminen (saatavana Microsoft 365: ssä)

Oletetaan, että sinulla on alla olevan taulukon mukainen taulukko ja haluat kääntää tämän taulukon tiedot:

Kopioi ensin otsikot ja aseta ne paikkaan, johon haluat kääntää taulukon

Käytä nyt seuraavaa kaavaa vasemmanpuoleisimman otsikon solun alla:

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

Yllä oleva kaava lajittelee tiedot ja käyttää lajittelun perustana ROW -funktion tulosta.

ROW -funktio palauttaisi tässä tapauksessa numeroryhmän, joka edustaa määritetyn alueen välistä rivinumeroa (joka tässä esimerkissä olisi numerosarja, kuten 2, 3, 4 ja niin edelleen).

Ja koska tämän kaavan kolmas argumentti on -1, se pakottaisi kaavan lajittelemaan tiedot laskevaan järjestykseen.

Tietue, jolla on suurin rivinumero, olisi yläreunassa ja se, jolla on pienin sääntömäärä, alareunassa, mikä muuttaa olennaisesti tietojen järjestyksen.

Kun olet valmis, voit muuntaa kaavan arvoiksi saadaksesi staattisen taulukon.

INDEX -toiminnon käyttäminen

Jos sinulla ei ole SORTBY -toimintoa, älä huoli - voit käyttää hämmästyttävää INDEX -toimintoa.

Oletetaan, että sinulla on alla kuvattu nimitiedosto ja haluat kääntää nämä tiedot.

Alla on kaava tämän tekemiseen:

= INDEKSI ($ A $ 2: $ A $ 12, RIVIT (A2: $ A $ 12))

Kuinka tämä kaava toimii?

Yllä oleva kaava käyttää INDEX -funktiota, joka palauttaisi solun arvon toisessa argumentissa määritetyn numeron perusteella.

Todellinen taikuus tapahtuu toisessa argumentissa, jossa olen käyttänyt RIVIT -funktiota.

Koska olen lukinnut ROWS -funktion viittauksen toisen osan ensimmäisessä solussa, se palauttaisi rivien määrän A2 ja A12 välillä, joka olisi 11.

Mutta kun se menee rivejä alaspäin, ensimmäinen viittaus muuttuu A3: ksi ja sitten A4: ksi ja niin edelleen, kun taas toinen viittaus pysyy sellaisenaan, koska olen lukinnut sen ja tehnyt siitä absoluuttisen.

Kun menemme rivejä alaspäin, RIVIT -funktion tulos pienenee 1: llä, 11: stä 10: een 9 ja niin edelleen.

Ja koska INDEX -funktio palauttaa meille arvon, joka perustuu toisen argumentin numeroon, tämä antaisi meille lopulta tiedot päinvastaisessa järjestyksessä.

Voit käyttää samaa kaavaa, vaikka tietojoukossa olisi useita sarakkeita. sinun on kuitenkin määritettävä toinen argumentti, joka määrittää sarakkeen numeron, josta tiedot on haettava.

Oletetaan, että sinulla on alla esitetyn kaltainen tietojoukko ja haluat kääntää koko taulukon järjestyksen:

Alla on kaava, joka tekee sen puolestasi:

= INDEKSI ($ A $ 2: $ B $ 12, RIVIT (A2: $ A $ 12), COLUMNS ($ A $ 2: A2))

Tämä on samanlainen kaava, johon olen lisännyt myös kolmannen argumentin, joka määrittää sarakkeen numeron, josta arvo tulee hakea.

Jotta tämä kaava olisi dynaaminen, olen käyttänyt COLUMNS -funktiota, joka jatkuvasti muuttaa sarakkeen arvoa 1 - 2 - 3, kun kopioit sen oikealle.

Kun olet valmis, voit muuntaa kaavat arvoiksi varmistaaksesi, että saat staattisen tuloksen.

Huomautus: Kun käytät kaavaa kääntääksesi tietojoukon järjestyksen Excelissä, se ei säilytä alkuperäistä muotoilua. Jos tarvitset myös alkuperäisen muotoilun lajiteltuihin tietoihin, voit joko soveltaa sitä manuaalisesti tai kopioida ja liittää muotoilun alkuperäisestä tietojoukosta uuteen lajiteltuun tietojoukkoon

Käännä tiedot VBA: n avulla

Jos tietojen kääntäminen Excelissä on jotain, mitä sinun on tehtävä melko usein, voit kokeilla myös VBA -menetelmää.

VBA -makrokoodin avulla voit kopioida ja liittää sen kerran työkirjaan VBA -editorissa ja käyttää sitä uudelleen ja uudelleen samassa työkirjassa.

Voit myös tallentaa koodin henkilökohtaiseen makro-työkirjaan tai Excel-apuohjelmaksi ja käyttää sitä missä tahansa järjestelmän työkirjassa.

Alla on VBA -koodi, joka kääntää valitut tiedot laskentataulukon pystysuunnassa.

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

Jotta voit käyttää tätä koodia, sinun on ensin valittava tietojoukko, jonka haluat peruuttaa (otsikoita lukuun ottamatta), ja sitten suoritettava tämä koodi.

Kuinka tämä koodi toimii?

Yllä oleva koodi laskee ensin tietojoukon rivien kokonaismäärän ja määrittää sen muuttujalle EndNum.

Sitten se käyttää Do while -silmukkaa, jossa tietojen lajittelu tapahtuu.

Tietojen lajittelu tapahtuu ottamalla ensimmäinen ja viimeinen rivi ja vaihtamalla ne. Sitten se siirtyy toisen rivin toiselle viimeiselle riville ja vaihtaa sitten nämä. Sitten se siirtyy kolmannen viimeisen rivin kolmannelle riville ja niin edelleen.

Silmukka päättyy, kun kaikki lajittelu on tehty.

Se käyttää myös Application.ScreenUpdating -ominaisuutta ja asettaa sen arvoon FALSE koodin suorittamisen aikana ja kääntää sen sitten takaisin arvoon TRUE, kun koodi on suoritettu loppuun.

Tämä varmistaa, että et näe muutoksia tapahtuvan reaaliajassa näytölläsi, ja se myös nopeuttaa prosessia.

Kuinka käyttää koodia?

Kopioi ja liitä tämä koodi VB Editoriin seuraavasti:

1. Avaa Excel -tiedosto, johon haluat lisätä VBA -koodin
2. Pidä ALT-näppäintä painettuna ja paina F-11-näppäintä (voit myös siirtyä Kehittäjä-välilehteen ja napsauttaa Visual Basic -kuvaketta)
3. Avautuvassa Visual Basic Editorissa VBA -editorin vasemmassa osassa olisi Project Explorer. Jos et näe sitä, napsauta Näytä -välilehteä ja valitse sitten Project Explorer
4. Napsauta hiiren kakkospainikkeella mitä tahansa työkirjan objektia, johon haluat lisätä koodin
5. Siirry Lisää -vaihtoehtoon ja napsauta sitten Moduuli. Tämä lisää uuden moduulin työkirjaan
6. Kaksoisnapsauta moduulin kuvaketta Project Explorerissa. Tämä avaa kyseisen moduulin koodi -ikkunan
7. Kopioi ja liitä yllä oleva VBA -koodi koodi -ikkunaan

Jos haluat suorittaa VBA -makrokoodin, valitse ensin tietojoukko, jonka haluat kääntää (otsikoita lukuun ottamatta).

Kun tiedot on valittu, siirry VB -editoriin ja napsauta työkalupalkin vihreää toistopainiketta tai valitse mikä tahansa rivi koodissa ja paina sitten F5 -näppäintä

Joten nämä ovat joitain menetelmiä, joilla voit kääntää tietoja Excelissä (eli kääntää tietojoukon järjestyksen).

Kaikkia tässä opetusohjelmassa käsiteltyjä menetelmiä (kaavat, SORT -ominaisuus ja VBA) voidaan käyttää tietojen kääntämiseen pysty- ja vaakasuunnassa (sinun on säädettävä kaava ja VBA -koodi vastaavasti vaakasuuntaisten tietojen kääntämiseksi).

Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä.