Kako pretvoriti vrstice v stolpce v Excelu

Ta priročnik pojasnjuje, kaj storiti za pretvorbo vrstic v stolpce v Excelu. Ko uporabljate Excel, morate pogosto zamenjati vrstice in stolpce v tabeli. Na primer, zgodi se, da je človek naredil ogromno mizo, potem pa je ugotovil, da jo je veliko lažje brati, če jo obrnete. 

To podrobno navodilo opisuje več načinov prenosa Excelovih tabel in tudi pogosto pojavljajoče se napake, s katerimi se lahko prikaže uporabnik. Vsi lahko uporabljajo katero koli različico Excela, kot zelo staro, tako in samo novo.

Uporaba funkcije Posebno lepljenje

Recimo, da imate dva niza podatkov. Drugo je tisto, kar želite končati. Prva je tabela, ki jo je treba prenesti. V prvi različici so imena držav predstavljena v različnih stolpcih in je zelo neprijetno brati, še bolj pa primerjati značilnosti držav med seboj. Zato je veliko bolje tabelo organizirati tako, da se imena držav pojavljajo v različnih stolpcih.Kako pretvoriti vrstice v stolpce v Excelu

Če želite zamenjati vrstice in stolpce, naredite naslednje:

  1. Izberite izvirno tabelo. Če želite videti celotno tabelo naenkrat (če je zelo velika), morate pritisniti kombinacijo tipk Ctrl + Home in nato Ctrl + Shift + End.
  2. Celice se nato kopirajo. To lahko storite prek kontekstnega menija ali s pritiskom na kombinacijo tipk Ctrl+C. Priporočljivo je, da se takoj navadite na zadnjo možnost, saj če se naučite vročih tipk, lahko veliko nalog opravite dobesedno v eni sekundi.
  3. Izberite prvo celico v ciljnem obsegu. Pri tem se mora nahajati za omejitvami tablice. Tudi druge je treba podobrati ячейку na tak način, da se tabela po prenosu ne spremeni s podatki. Na primer, če ima začetna tabela 4 stolpce in 10 vrstic, se bo po izvedbi vseh teh operacij razširila 10 celic navzdol in 4 celice na stran. Zato znotraj tega obsega (šteto od ciljne celice) ne sme biti ničesar.
  4. Na ciljni celici morate z desno tipko miške klikniti in izbrati »Posebno lepljenje«, nato pa potrditi polje poleg napisa »Premestitev«.Kako pretvoriti vrstice v stolpce v Excelu

pomembno: če so v izvorni tabeli formule, je pomembno zagotoviti, da se v vsaki celici uporabljajo absolutne reference. To je treba storiti tako, da se vse povezave samodejno posodobijo.

Ogromna prednost možnosti »Posebna vstavka« se skriva v možnosti prenosa tabele le v nekaj sekundah. Pri tem se v celoti ohranja formatiranje, kar omogoča tudi varčevanje kuču časa.

Ne glede na te jasne pluse, obstaja in vrsta resnih pomanjkljivosti, ki mešajo to metodo, imenovano univerzalnim:

  1. Slabo ga je uporabljati za prenos polnopravnih tabel, ki niso zmanjšane na banalen obseg vrednosti. V tem primeru bo funkcija »Transpose« onemogočena. Če se želite izogniti tej težavi, morate tabelo pretvoriti v obseg.
  2. Ta metoda je zelo primerna za enkratni prenos, ker ne poveže nove tabele z izvirnimi podatki. Preprosto povedano, ko spremenite eno od tabel, se informacije v drugi ne bodo samodejno posodobile. Zato bo treba prenos ponoviti. 

Kako zamenjati vrstice in stolpce ter jih povezati z glavno tabelo?

Kaj je torej mogoče storiti, da bo možnost »Posebno lepljenje« povezala tabelo z glavnimi podatki in tistimi, ki izhajajo iz prenosa? Konec koncev bi vsi želeli, da se podatki samodejno posodabljajo.

  1. Skopirajte tabelo, za katero je potrebno prenos.
  2. V praznem delu tabele izberite celico brez podatkov.
  3. Zaženite meni Posebno lepljenje, podobno kot v prejšnjem primeru. Po tem morate klikniti gumb »Vstavi povezavo«, ki ga najdete spodaj levo.Kako pretvoriti vrstice v stolpce v Excelu
  4. Rezultat bo naslednji.Kako pretvoriti vrstice v stolpce v Excelu
  5. Izberite novo tabelo in odprite okno «Poišči in zamenjaj» s kombinacijo tipk Ctrl + H.
  6. Zamenjaj vse znake vnosne formule (=) na hhh (ali katero koli drugo kombinacijo znakov, ki ni v originalni tabeli).Kako pretvoriti vrstice v stolpce v Excelu
  7. Posledično se bo izkazalo nekaj groznega, vendar je to obvezen vmesni pogoj za doseganje želenega rezultata. Potem bo vse videti lepo.Kako pretvoriti vrstice v stolpce v Excelu
  8. Kopirajte nastalo tabelo in jo nato prestavite s posebno lepljenjem.

Po tem morate le znova odpreti pogovorno okno »Najdi in zamenjaj« in ga uporabiti za spremembo »xxx« v celicah v »=«, tako da so vse celice povezane z izvirnimi informacijami.Kako pretvoriti vrstice v stolpce v ExceluSeveda je to bolj zapleteno in daljše, vendar vam ta metoda omogoča, da se izognete pomanjkanju povezave do izvirne tabele. Toda ta pristop ima tudi pomanjkljivost. Izraža se v potrebi po samostojnem nadaljevanju oblikovanja.

Uporaba formul

Obstajata dve funkciji, ki omogočata prilagodljivo spreminjanje vrstic in stolpcev: TRANSP in DVSSYL. Tukaj je tudi možnost ohraniti povezavo s prvotno tablico, vendar mehanika dela nekaj inna.

funkcija TRANSP

Pravzaprav ta formula neposredno prestavi preglednico. Sintaksa je naslednja:

=TRANSP(niz)

Zdaj ga bomo poskušali uporabiti v tabeli, ki vsebuje podatke o številu prebivalcev posameznih držav.Kako pretvoriti vrstice v stolpce v Excelu

  1. Preštejte število stolpcev in vrstic v tabeli in poiščite prazen prostor na listu, ki ima enake dimenzije.
  2. Zaženite način urejanja s pritiskom na tipko F2.
  3. Napišite funkcijo TRANSP z obsegom podatkov v oklepajih. Pomembno je, da pri uporabi te funkcije vedno uporabljate absolutne reference.Kako pretvoriti vrstice v stolpce v Excelu
  4. Pritisnite kombinacijo tipk Ctrl+Shift+Enter. Pomembno je, da pritisnete točno kombinacijo tipk, sicer formula ne bo delovala.

To je to, zdaj je rezultat videti takole!Kako pretvoriti vrstice v stolpce v Excelu

Prednost te formule je enaka kot pri prejšnji metodi (z uporabo funkcije »poišči in zamenjaj«) je, da se bo transponirana tabela samodejno posodobila, ko se osnovne informacije spremenijo.

Nobene pomanjkljivosti tudi ne obstajajo:

  1. Oblikovanje, tako kot v primeru metode Najdi in zamenjaj, ne bo shranjeno.
  2. Prvotna tabela mora vsebovati nekaj podatkov, sicer bodo v nekaterih celicah ničle.
  3. Izhodna odvisnost od izvornih podatkov. Seveda ima ta metoda nasproten pomanjkanju – ni mogoče spremeniti transponirane tablice. Če poskusite to narediti, program napiše, da ni mogoče urediti dela masiva.

Tudi te funkcije ni mogoče vedno prilagodljivo uporabiti v različnih situacijah, zato morate vedeti o njej, vendar uporabiti učinkovitejše tehnike.

Uporaba INDIREKTNE formule

Mehanika te metode je zelo podobna uporabi formule TRANSP, vendar pri tej uporabi njegova uporaba rešuje težavo, da ni mogoče urediti transponirane tablice brez povezave z osnovno informacijo.

No ene formule INDIRECT ni dovolj: še vedno morate uporabiti funkcijo NASLOV. V tem primeru ne bo velike tabele, da vas ne preobremenimo z veliko nepotrebnimi informacijami.

Torej, recimo, da imate takšno tabelo, sestavljeno iz 4 stolpcev in 5 vrstic.Kako pretvoriti vrstice v stolpce v Excelu

Izvesti je treba naslednje ukrepe:

  1. Vnesite to formulo: =POSREDNO(NASLOV(STOLPEC(A1),VRSTICA(A1))) v zgornjo levo celico nove tabele (v našem primeru je to A7) in pritisnite tipko enter. Če se podatki ne začnejo v prvi vrstici ali prvem stolpcu, boste morali uporabiti bolj zapleteno formulo: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). V tej formuli A1 pomeni zgornjo celico tabele, na podlagi katere bo oblikovana transponirana celica.
  2. Razširite formulo na celotno območje, kjer bo postavljena nova različica tabele. Če želite to narediti, povlecite oznako v spodnjem desnem kotu prve celice na nasprotni konec prihodnje tabele.Kako pretvoriti vrstice v stolpce v Excelu
  3. Vse! Izkazalo se je, da je bila tabela uspešno prenesena in jo lahko še urejate. Seveda njen videz pušča veliko želenega, vendar ga ni težko popraviti.Kako pretvoriti vrstice v stolpce v Excelu Če želite obnoviti pravilno oblikovanje, morate kopirati tabelo, ki smo jo prenesli (torej izvirno), nato pa izbrati novo ustvarjeno tabelo. Nato z desno miškino tipko kliknite izbrani obseg in nato v možnostih lepljenja kliknite »Oblikovanje«.Kako pretvoriti vrstice v stolpce v Excelu

Torej uporaba funkcije INDIRECT vam omogoča urejanje absolutno katere koli vrednosti v končni tabeli, podatki pa bodo vedno posodobljeni takoj, ko pride do kakršnih koli sprememb v izvorni tabeli.

Seveda je ta metoda težja, a če jo preizkusite v praksi, potem vse ni tako strašljivo.

Ta metoda je ena izmed najboljših, ker ne glede na to, da se formatiranje v novozbrani tabeli ne ohranja, ga je mogoče enostavno obnoviti.

Kako deluje kombinacija formul INDIRECT in ADDRESS?

Po tem, kako ste se razbrali v tem, kako uporabiti skupnost te formule za transponiranje tablic, se vam morda zahoče bolj globoko izučiti princip dela te metode.

funkcija INDIRECT v naši formuli se uporablja za ustvarjanje posredne reference celice. Na primer, če morate v celici A8 podati isto vrednost kot v celici B1, lahko napišete formulo

=POSREDNO(»B1«)Kako pretvoriti vrstice v stolpce v Excelu

Kazalo bi, zakaj to delati? Lahko preprosto napišete povezavo na jačejku v drugi ячейki. No prednost te funkcije je v tem, da je v povezavi mogoče spremeniti absolutno katero koli vrstico in celo tisto, ki je ustvarjena z drugo uporabo formule. Lastno, to mi in delaem v formuli.

Izven funkcije NASLOV uporablja tudi v formuli STOLPEC и LINE. Prvi vrne naslov celice na podlagi že znanih številk vrstic in stolpcev. Tukaj je pomembno upoštevati pravilno zaporedje. Najprej se določi vrstica, nato pa šele stolpec. Na primer funkcija NASLOV(4;2) vrne naslov $B$2.

Naslednja zgoraj uporabljena funkcija – to STOLPEC. Tukaj je potrebno, da formula prejme številko stolpca iz določene reference. Na primer, če uporabite parameter B2 v oklepajih te funkcije, bo vrnila številko 2, ker je drugi stolpec stolpec B.

Očitno funkcija ROW deluje na enak način, le vrne številko vrstice. 

In zdaj ne bomo uporabili abstraktnih primerov, ampak zelo specifično formulo, ki je bila uporabljena zgoraj:

=POSREDNO(NASLOV(STOLPEC(A1),VRSTICA(A1)))

Tukaj lahko vidite to najprej v funkciji NASLOV poda se stolpec in šele nato vrstica. In tu se skriva skrivnost učinkovitosti te formule. Spomnimo se, da ta funkcija deluje zrcalno in prvi argument v njej je številka vrstice. In izkazalo se je, da ko tam zapišemo naslov stolpca, se ta spremeni v številko vrstice in obratno.

To je, če podytožit:

  1. Številke stolpcev in vrstic dobimo z ustreznimi funkcijami.
  2. Uporaba funkcije NASLOV vrstice postanejo stolpci in obratno.
  3. funkcija INDIRECT pomaga prikazati zrcaljene podatke v celici.

Tako preprosto se vse izkaže!

Uporaba makra za transponiranje

Makro je majhen program. Lahko se uporablja za avtomatizacijo postopka. Pomembno je upoštevati, da ima makro nekatere omejitve. Največja metoda Transpose vam omogoča delo s 65536 elementi. Če je ta omejitev presežena, bo prišlo do izgube podatkov.

Vse ostalo, to je učinkovita metoda avtomatizacije, ki lahko znatno olajša življenje.

Na primer, lahko napišete takšno kodo, ki bo spreminjala mesta vrstic in stolpcev.

Sub TransposeColumnsRows()

    Dim SourceRange As Range

    Zatemni DestRange kot obseg

    Set SourceRange = Application.InputBox(Prompt:=»Prosimo, izberite obseg za prenos», Title:=»Transpose Rows to Columns», Type:=8)

    Set DestRange = Application.InputBox(Prompt:=»Izberite zgornjo levo celico ciljnega obsega», Title:=»Transpose Rows to Columns», Type:=8)

    SourceRange.Copy

    DestRange.Select

    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = Napačno

End Sub

No, če ni znanih v programiranju drugega, ni nič strašnega. Lahko uporabite opisane višje načine. A nato učiti novo po merjenju starega.

Pustite Odgovori