Apversti duomenis „Excel“ - Atvirkštinė duomenų stulpelio/eilutės tvarka

Kartais gali tekti apversti „Excel“ duomenis, t. Y. Pakeisti vertikalių duomenų rinkinio duomenų eiliškumą aukštyn kojomis, o horizontaliame duomenų rinkinyje - iš kairės į dešinę.

Dabar, jei manote, kad tam turi būti įdiegta „Excel“ funkcija, bijau, kad nusivilsite.

Nors yra keli būdai, kaip galite apversti duomenis „Excel“, nėra integruotos funkcijos. Bet tai galite lengvai padaryti naudodami paprastą rūšiavimo triuką, formules ar VBA.

Šioje pamokoje parodysiu, kaip apversti duomenis „Excel“ eilutėse, stulpeliuose ir lentelėse.

Taigi pradėkime!

Apverskite duomenis naudodami SORT ir pagalbinį stulpelį

Vienas iš paprasčiausių būdų pakeisti „Excel“ duomenų tvarką būtų naudoti pagalbinį stulpelį ir tada naudoti tą pagalbinį stulpelį duomenims rūšiuoti.

Apverskite duomenis vertikaliai (atvirkštine tvarka aukštyn kojom)

Tarkime, kad stulpelyje yra duomenų pavadinimų rinkinys, kaip parodyta žemiau, ir norite apversti šiuos duomenis:

Žemiau pateikiami veiksmai, kaip apversti duomenis vertikaliai:

1. Gretimame stulpelyje įveskite „Pagalbininkas“ kaip stulpelio antraštę
2. Pagalbos stulpelyje įveskite skaičių seriją (1,2,3 ir pan.). Norėdami tai padaryti greitai, galite naudoti čia pateiktus metodus
3. Pasirinkite visą duomenų rinkinį, įskaitant pagalbinį stulpelį
4. Spustelėkite skirtuką Duomenys
5. Spustelėkite piktogramą Rūšiuoti
6. Dialogo lange Rūšiuoti išskleidžiamajame meniu Rūšiuoti pagal pasirinkite „Pagalbininkas“
7. Išskleidžiamajame meniu Užsakymas pasirinkite „Didžiausias-mažiausias“
8. Spustelėkite Gerai

Pirmiau minėti veiksmai surūšiuotų duomenis pagal pagalbinio stulpelio reikšmes, o tai taip pat leistų pakeisti duomenų pavadinimų tvarką.

Baigę nedvejodami ištrinkite pagalbinį stulpelį.

Šiame pavyzdyje aš jums parodžiau, kaip apversti duomenis, kai turite tik vieną stulpelį, tačiau taip pat galite naudoti tą pačią techniką, jei turite visą lentelę. Tiesiog įsitikinkite, kad pasirinkote visą lentelę ir naudokite stulpelį pagalbininkas, kad surūšiuotumėte duomenis mažėjančia tvarka.

Apverskite duomenis horizontaliai

Taip pat galite vadovautis ta pačia metodika, norėdami apversti duomenis horizontaliai „Excel“.

„Excel“ turi galimybę rūšiuoti duomenis horizontaliai naudojant dialogo langą Rūšiuoti (funkcija „Rūšiuoti iš kairės į dešinę“).

Tarkime, kad turite lentelę, kaip parodyta žemiau, ir norite apversti šiuos duomenis horizontaliai.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Žemiau esančioje eilutėje įveskite „Pagalbininkas“ kaip eilutės antraštę
2. Pagalbinėje eilutėje įveskite skaičių seriją (1,2,3 ir pan.).
3. Pasirinkite visą duomenų rinkinį, įskaitant pagalbinę eilutę
4. Spustelėkite skirtuką Duomenys
5. Spustelėkite piktogramą Rūšiuoti
6. Dialogo lange Rūšiuoti spustelėkite mygtuką Parinktys.
7. Atsidariusiame dialogo lange spustelėkite „Rūšiuoti iš kairės į dešinę“
8. Spustelėkite Gerai
9. Išskleidžiamajame meniu Rūšiuoti pagal pasirinkite 3 eilutę (arba bet kurią eilutę, kurioje yra pagalbinis stulpelis)
10. Išskleidžiamajame meniu Užsakymas pasirinkite „Didžiausias-mažiausias“
11. Spustelėkite Gerai

Pirmiau minėti veiksmai apvers visą stalą horizontaliai.

Baigę galite pašalinti pagalbinę eilę.

Apversti duomenis naudojant formules

„Microsoft 365“ turi keletą naujų formulių, leidžiančių tikrai lengvai pakeisti stulpelio ar lentelės tvarką „Excel“.

Šiame skyriuje parodysiu, kaip tai padaryti naudojant SORTBY formulę (jei naudojate „Microsoft 365“) arba INDEX formulę (jei nenaudojate „Microsoft 365“)

Funkcijos SORTBY naudojimas (galima „Microsoft 365“)

Tarkime, kad turite lentelę, kaip parodyta žemiau, ir norite apversti šios lentelės duomenis:

Norėdami tai padaryti, pirmiausia nukopijuokite antraštes ir padėkite jas ten, kur norite apversti lentelę

Dabar naudokite šią formulę po langeliu kairiojoje antraštėje:

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

Aukščiau pateikta formulė surūšiuoja duomenis ir, naudodama ROW funkcijos rezultatą, juos surūšiuoja.

Funkcija ROW šiuo atveju grąžins skaičių masyvą, kuris vaizduoja eilučių skaičius tarp nurodyto diapazono (kuris šiame pavyzdyje būtų skaičių seka, pvz., 2, 3, 4 ir pan.).

Ir kadangi trečias šios formulės argumentas yra -1, tai priverstų formulę rūšiuoti duomenis mažėjančia tvarka.

Įrašas, kuriame yra didžiausias eilutės numeris, būtų viršuje, o tas, kurio taisyklės numeris mažiausias, būtų apačioje, iš esmės pakeisdamas duomenų eiliškumą.

Baigę formulę galite konvertuoti į reikšmes, kad gautumėte statinę lentelę.

Funkcijos INDEX naudojimas

Jei neturite prieigos prie SORTBY funkcijos, nesijaudinkite - galite naudoti nuostabią INDEX funkciją.

Tarkime, kad turite vardų duomenų rinkinį, kaip parodyta žemiau, ir norite apversti šiuos duomenis.

Žemiau yra formulė, kaip tai padaryti:

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

Kaip veikia ši formulė?

Aukščiau pateiktoje formulėje naudojama funkcija INDEX, kuri grąžintų reikšmę iš langelio pagal skaičių, nurodytą antrajame argumente.

Tikroji magija įvyksta antrajame argumente, kuriame naudojau ROWS funkciją.

Kadangi aš užrakinau antrąją nuorodos dalį funkcijoje ROWS, pirmame langelyje jis grąžintų eilučių skaičių tarp A2 ir A12, kuris būtų 11.

Bet kai jis eina žemyn eilutėmis, pirmoji nuoroda pasikeis į A3, tada į A4 ir pan., O antroji nuoroda išliks tokia, kokia buvo, nes aš ją užrakinau ir padariau absoliučia.

Kai einame žemyn eilėmis, funkcijos ROWS rezultatas sumažėtų 1, nuo 11 iki 10 iki 9 ir pan.

Kadangi funkcija INDEX grąžina mums vertę, pagrįstą antrojo argumento skaičiumi, tai galiausiai duosime duomenis atvirkštine tvarka.

Tą pačią formulę galite naudoti, net jei duomenų rinkinyje yra keli stulpeliai. tačiau turėsite nurodyti antrą argumentą, kuris nurodytų stulpelio numerį, iš kurio reikia gauti duomenis.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite pakeisti visos lentelės tvarką:

Žemiau yra formulė, kuri tai padarys už jus:

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

Tai panaši formulė, kurioje taip pat pridėjau trečiąjį argumentą, nurodantį stulpelio numerį, iš kurio turėtų būti gauta vertė.

Kad ši formulė būtų dinamiška, aš panaudojau funkciją STULPELIAI, kuri nuolat keis stulpelio reikšmę nuo 1 iki 2, kai nukopijuosite ją į dešinę.

Kai tai padarysite, formules galite konvertuoti į reikšmes, kad įsitikintumėte, jog turite statinį rezultatą.

Pastaba: Kai naudojate formulę, kad pakeistumėte „Excel“ duomenų rinkinio tvarką, ji neišsaugotų pradinio formatavimo. Jei jums taip pat reikia pradinio formato surūšiuotiems duomenims, galite juos pritaikyti rankiniu būdu arba nukopijuoti ir įklijuoti formatavimą iš pradinio duomenų rinkinio į naują surūšiuotą duomenų rinkinį

Apverskite duomenis naudodami VBA

Jei duomenų vartymas „Excel“ yra tai, ką turite padaryti gana dažnai, taip pat galite išbandyti VBA metodą.

Naudodami VBA makrokomandą, galite vieną kartą nukopijuoti ir įklijuoti ją į darbaknygę VBA redaktoriuje ir vėl ir vėl naudoti toje pačioje darbaknygėje.

Taip pat galite išsaugoti kodą asmeninėje makrokomandos darbaknygėje arba kaip „Excel“ priedą ir naudoti jį bet kurioje savo sistemos darbaknygėje.

Žemiau yra VBA kodas, kuris pasirinktinius duomenis apverstų darbalapyje.

Sub FlipVerically () 'kodas Sumit Bansal iš 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.Cows Do Do 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 = Tikroji pabaiga

Norėdami naudoti šį kodą, pirmiausia turite pasirinkti duomenų rinkinį, kurį norite pakeisti (išskyrus antraštes), ir tada paleisti šį kodą.

Kaip veikia šis kodas?

Pirmiau pateiktas kodas pirmiausia suskaičiuoja bendrą duomenų rinkinio eilučių skaičių ir priskiria jį kintamajam „EndNum“.

Tada ji naudoja ciklą „Do while“, kur rūšiuojami duomenys.

Šiuos duomenis rūšiuoja pirmoji ir paskutinė eilutės ir jas pakeisdamos. Tada jis pereina į antrą paskutinės eilutės eilutę ir tada jas keičia. Tada jis pereina į trečiąją eilutę trečioje paskutinėje eilutėje ir pan.

Ciklas baigiasi, kai baigiamas visas rūšiavimas.

Jis taip pat naudoja ypatybę „Application.ScreenUpdating“ ir nustato ją į „FALSE“ vykdydamas kodą, o tada, kai kodas bus paleistas, grąžinkite jį į „TRUE“.

Tai užtikrina, kad nematote pokyčių realiuoju laiku ekrane, taip pat pagreitina procesą.

Kaip naudotis kodeksu?

Norėdami nukopijuoti ir įklijuoti šį kodą į VB redaktorių, atlikite šiuos veiksmus:

1. Atidarykite „Excel“ failą, kuriame norite pridėti VBA kodą
2. Laikykite nuspaudę ALT klavišą ir paspauskite klavišą F-11 (taip pat galite pereiti į skirtuką „Kūrėjas“ ir spustelėti „Visual Basic“ piktogramą)
3. Atsidariusiame „Visual Basic“ redaktoriuje kairėje VBA redaktoriaus dalyje būtų „Project Explorer“. Jei nematote, spustelėkite skirtuką „Peržiūrėti“, tada spustelėkite „Project Explorer“
4. Dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės objektą, į kurį norite įtraukti kodą
5. Eikite į parinktį Įterpti ir spustelėkite Modulis. Tai pridės naują modulį prie darbaknygės
6. Dukart spustelėkite modulio piktogramą „Project Explorer“. Tai atvers to modulio kodo langą
7. Nukopijuokite ir įklijuokite aukščiau pateiktą VBA kodą į kodo langą

Norėdami paleisti VBA makrokomandą, pirmiausia pasirinkite duomenų rinkinį, kurį norite apversti (išskyrus antraštes).

Pasirinkę duomenis, eikite į VB redaktorių ir įrankių juostoje spustelėkite žalią atkūrimo mygtuką arba pasirinkite bet kurią kodo eilutę ir paspauskite klavišą F5

Taigi tai yra keletas metodų, kuriuos galite naudoti norėdami apversti duomenis „Excel“ (t. Y. Pakeisti duomenų rinkinio eiliškumą).

Visi metodai, kuriuos aptariau šioje pamokoje (formulės, rūšiavimo funkcija ir VBA), gali būti naudojami duomenims apversti vertikaliai ir horizontaliai (turėsite atitinkamai pakoreguoti formulę ir VBA kodą, kad būtų galima apversti horizontalius duomenis).

Tikiuosi, kad ši pamoka jums buvo naudinga.