Kako pretvoriti retke u stupce u Excelu

Ovaj vodič objašnjava što učiniti za pretvaranje redaka u stupce u Excelu. Kada koristite Excel, često morate zamijeniti retke i stupce u tablici. Na primjer, dogodi se da je osoba napravila ogromnu tablicu, a onda je shvatila da ju je puno lakše čitati ako je okrenete. 

Ova detaljna uputa govori o nekoliko načina prijenosa Excel-tablica, kao io čestim pojavljivanjima pogrešaka, s kojima se može otvoriti korisnički račun. Svi oni mogu koristiti bilo koju verziju Excela, kao vrlo staru, tako i samu novu.

Korištenje značajke Paste Special

Recimo da imate dva skupa podataka. Drugo je ono s čime želite završiti. Prva je tablica koju treba transponirati. U prvoj verziji imena zemalja prikazana su u različitim stupcima i vrlo je nezgodno čitati, a još više uspoređivati ​​karakteristike država jedna s drugom. Stoga je puno bolje organizirati tablicu tako da se nazivi zemalja pojavljuju u različitim stupcima.Kako pretvoriti retke u stupce u Excelu

Da biste zamijenili retke i stupce, učinite sljedeće:

  1. Odaberite izvornu tablicu. Ako želite vidjeti cijelu tablicu odjednom (ako je jako velika), trebate pritisnuti kombinaciju tipki Ctrl + Home, a nakon toga – Ctrl + Shift + End.
  2. Ćelije se zatim kopiraju. To se može učiniti putem kontekstnog izbornika ili pritiskom na kombinaciju tipki Ctrl+C. Preporuča se da se odmah naviknete na posljednju opciju, jer ako naučite vruće tipke, možete izvršiti mnoge zadatke doslovno u jednoj sekundi.
  3. Odaberite prvu ćeliju u ciljnom rasponu. Pri tome se mora nalaziti za granice tablice. Također je potrebno podesiti drugu jačejku na ovaj način, da se tablica nakon prijenosa ne promijeni s podacima. Na primjer, ako početna tablica ima 4 stupca i 10 redaka, tada će se nakon izvođenja svih ovih operacija raširiti 10 ćelija prema dolje i 4 ćelije u stranu. Stoga unutar ovog raspona (brojeći od ciljne ćelije) ne bi trebalo biti ničega.
  4. Na ciljnoj ćeliji morate kliknuti desnom tipkom miša i odabrati "Posebno zalijepi", a zatim potvrdite okvir pored natpisa "Transpose".Kako pretvoriti retke u stupce u Excelu

Važno: ako postoje formule u izvornoj tablici, važno je osigurati da se u svakoj ćeliji koriste apsolutne reference. To se mora učiniti kako bi se sve veze automatski ažurirale.

Ogromna prednost opcije «Specijalna ponuda» se sastoji u mogućnosti prijenosa tablice za samo nekoliko sekundi. Pri tome se potpuno čuva formatiranje, što također omogućuje štednju vremena.

Bez obzira na ove jasne pluseve, postoji i niz ozbiljnih nedostataka koji pokušavaju ovu metodu nazvati univerzalnim:

  1. Loše je koristiti ga za transponiranje punopravnih tablica koje nisu svedene na banalni raspon vrijednosti. U tom će slučaju funkcija “Transpose” biti onemogućena. Da biste zaobišli ovaj problem, morate pretvoriti tablicu u raspon.
  2. Ova je metoda vrlo prikladna za jednokratno prenošenje jer ne povezuje novu tablicu s izvornim podacima. Jednostavnim riječima, kada promijenite jednu od tablica, informacije se neće automatski ažurirati u drugoj. Stoga će se transpozicija morati ponoviti. 

Kako zamijeniti retke i stupce i povezati ih s glavnom tablicom?

Dakle, što učiniti da opcija “Paste Special” može povezati tablicu s glavnim podacima i onim koji je rezultat transpozicije? Uostalom, svi bi htjeli da se podaci automatski ažuriraju.

  1. Skopirajte tablicu za koju je potrebno prijenos.
  2. Odaberite ćeliju bez podataka u praznom području tablice.
  3. Pokrenite Paste Special izbornik, slično kao u prethodnom primjeru. Nakon toga potrebno je kliknuti na gumb “Insert link” koji se nalazi dolje lijevo.Kako pretvoriti retke u stupce u Excelu
  4. Rezultat će biti sljedeći.Kako pretvoriti retke u stupce u Excelu
  5. Odaberite novu tablicu i pokrenite prozor «Pronađi i promijeni» pomoću kombinacije tipki Ctrl + H.
  6. Zamijeni sve znakove uvodne formule (=) na hhh (ili bilo koju drugu kombinaciju znakova kojih nema u originalnoj tablici).Kako pretvoriti retke u stupce u Excelu
  7. Kao rezultat toga, ispast će nešto strašno, ali ovo je obavezan srednji uvjet za postizanje željenog rezultata. Tada će sve izgledati lijepo.Kako pretvoriti retke u stupce u Excelu
  8. Kopirajte dobivenu tablicu i zatim upotrijebite Paste Special kako biste je transponirali.

Nakon toga samo trebate ponovno otvoriti dijaloški okvir "Pronađi i zamijeni" i koristiti ga za promjenu "xxx" u ćelijama u "=" tako da sve ćelije budu povezane s izvornim informacijama.Kako pretvoriti retke u stupce u ExceluNaravno, ovo je kompliciranije i duže, ali ova metoda vam omogućuje da zaobiđete nedostatak veze na izvornu tablicu. Ali ovaj pristup ima i nedostatak. Izražava se u potrebi samostalnog nastavka oblikovanja.

Primjena formula

Postoje dvije funkcije koje vam omogućuju fleksibilnu promjenu redaka i stupaca: TRANSP i DVSSYL. Ovdje također postoji mogućnost očuvanja veze s početnom tablicom, ali mehanički rad nekoliko inaća.

funkcija TRANSP

Zapravo, ova formula izravno transponira proračunsku tablicu. Sintaksa je sljedeća:

=TRANSP(niz)

Sada ćemo to pokušati primijeniti na tablicu koja sadrži podatke o stanovništvu pojedinih država.Kako pretvoriti retke u stupce u Excelu

  1. Izbrojite broj stupaca i redaka u tablici i pronađite prazan prostor na listu koji ima iste dimenzije.
  2. Pokrenite način uređivanja pritiskom na tipku F2.
  3. Napiši funkciju TRANSP s rasponom podataka u zagradama. Važno je uvijek koristiti apsolutne reference kada koristite ovu funkciju.Kako pretvoriti retke u stupce u Excelu
  4. Pritisnite kombinaciju tipki Ctrl+Shift+Enter. Važno je pritisnuti točno kombinaciju tipki, inače će formula odbiti raditi.

To je to, sada rezultat izgleda ovako!Kako pretvoriti retke u stupce u Excelu

Prednost ove formule ista je kao i prethodne metode (upotrebom funkcije "pronađi i zamijeni") je ta da će se transponirana tablica automatski ažurirati kada se temeljni podaci promijene.

Nema nedostataka u kojima se također ne nalaze:

  1. Oblikovanje, kao u slučaju metode Pronađi i zamijeni, neće biti spremljeno.
  2. Izvorna tablica mora sadržavati neke podatke, inače će u nekim ćelijama biti nule.
  3. Izlišna ovisnost o izvoru podataka. Ipak, ova metoda ima protivpoložan nedostatak – nemoguće je promijeniti transponiranu tablicu. Ako pokušate to napraviti, program iskaže da je nemoguće urediti dio masiva.

Također, ova se funkcija ne može uvijek fleksibilno primijeniti u različitim situacijama, pa je morate znati, ali koristiti učinkovitije tehnike.

Korištenje INDIREKTNE formule

Mehanika ove metode vrlo je slična korištenju formule TRANSP, ali pri ovom njegovom korištenju rješava se problem nemogućeg uređivanja transponirane tablice bez potrebe povezivanja s osnovnim informacijama.

No jedne formule INDIREKTNO nije dovoljno: još uvijek morate koristiti funkciju ADRESA. U ovom primjeru neće biti velike tablice, kako vas ne bismo opteretili gomilom nepotrebnih podataka.

Dakle, recimo da imate takvu tablicu koja se sastoji od 4 stupca i 5 redaka.Kako pretvoriti retke u stupce u Excelu

Potrebno je poduzeti sljedeće radnje:

  1. Unesite ovu formulu: =INDIREKT(ADRESA(STUPAC(A1),RED(A1))) u gornju lijevu ćeliju nove tablice (u našem primjeru to je A7) i pritisnite tipku enter. Ako podaci ne počinju u prvom retku ili prvom stupcu, tada ćete morati upotrijebiti složeniju formulu: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). U ovoj formuli A1 označava gornju ćeliju tablice, na temelju koje će se formirati transponirana ćelija.
  2. Proširite formulu na cijelo područje gdje će se postaviti nova verzija tablice. Da biste to učinili, povucite oznaku u donjem desnom kutu prve ćelije na suprotni kraj buduće tablice.Kako pretvoriti retke u stupce u Excelu
  3. Svi! Pokazalo se da je tablica uspješno transponirana i još uvijek je možete uređivati. Naravno, njezin izgled ostavlja mnogo želja, ali popraviti ga nije teško.Kako pretvoriti retke u stupce u Excelu Da biste vratili ispravno oblikovanje, trebate kopirati tablicu koju smo transponirali (odnosno izvornu), zatim odabrati novostvorenu tablicu. Zatim desnom tipkom miša kliknite odabrani raspon, a zatim kliknite na "Formatiranje" u opcijama lijepljenja.Kako pretvoriti retke u stupce u Excelu

Dakle, korištenje funkcije INDIREKTNO omogućuje uređivanje apsolutno bilo koje vrijednosti u konačnoj tablici, a podaci će uvijek biti ažurirani čim se naprave bilo kakve promjene u izvornoj tablici.

Naravno, ova metoda je teža, ali ako je razradite u praksi, onda sve nije tako strašno.

Ovaj metod je jedan od najboljih, budući da unatoč tome što se ne čuva formatiranje u novoj tablici, možete ga lako uspostaviti.

Kako funkcionira kombinacija formula INDIRECT i ADDRESS?

Nakon toga, kako ste razradili u tome, kako koristiti jedinstvenost ove formule za transponiranje tablice, moguće je da želite dublje izučiti princip rada ove metode.

funkcija INDIREKTNO u našoj formuli koristi se za stvaranje neizravne reference ćelije. Na primjer, ako trebate navesti istu vrijednost u ćeliji A8 kao u B1, tada možete napisati formulu

=INDIRECT(“B1”)Kako pretvoriti retke u stupce u Excelu

Kazalo je, zašto to raditi? Možete jednostavno napisati link na jačejku u drugoj jačejki. No prednost je ova funkcija u tom, da se u linku može pretvoriti apsolutno bilo koja linija, čak i ona, koja se stvara pomoću druge formule. Vlasnički, to mi i radimo u formuli.

Izvan funkcije ADRESA također se koristi u formuli KOLONA и ONLINE. Prvi vraća adresu ćelije na temelju već poznatih brojeva redaka i stupaca. Ovdje je važno slijediti točan redoslijed. Prvo se navede red, a zatim samo stupac. Na primjer, funkcija ADRESA(4;2) će vratiti adresu $B$2.

Sljedeća iskorištena funkcija – to je KOLONA. Ovdje je potrebno kako bi formula dobila broj stupca iz određene reference. Na primjer, ako koristite parametar B2 u zagradama ove funkcije, ona će vratiti broj 2, jer je drugi stupac stupac B.

Očito, funkcija ROW radi na isti način, samo vraća broj retka. 

A sada ćemo koristiti ne apstraktne primjere, već vrlo specifičnu formulu koja je korištena gore:

=INDIREKT(ADRESA(STUPAC(A1),RED(A1)))

Ovdje možete vidjeti to prvo u funkciji ADRESA navodi se stupac, a tek onda redak. I upravo se tu krije tajna djelotvornosti ove formule. Sjećamo se da ova funkcija radi zrcalno, a prvi argument u njoj je broj retka. I ispada da kada tamo napišemo adresu stupca, ona se pretvara u broj retka i obrnuto.

To jest, ako podytožitʹ:

  1. Brojeve stupaca i redaka dobivamo pomoću odgovarajućih funkcija.
  2. Korištenje funkcije ADRESA redovi postaju stupci i obrnuto.
  3. funkcija INDIREKTNO pomaže prikazati zrcaljene podatke u ćeliji.

Tako jednostavno sve ispada!

Korištenje makronaredbe za transponiranje

Makro je mali program. Može se koristiti za automatizaciju procesa. Važno je uzeti u obzir da makro ima neka ograničenja. Maksimalna metoda transponiranja omogućuje vam rad s 65536 elemenata. Ako se ovo ograničenje prekorači, to će rezultirati gubitkom podataka.

Sve ostalo, to je učinkovita metoda automatizacije, koja može značajno olakšati život.

Na primjer, možete napisati takav kod koji će mijenjati mjesta nizova i kolona.

Sub TransposeColumnsRows()

    Dim SourceRange As Range

    Dim DestRange kao raspon

    Postavi SourceRange = Application.InputBox(Prompt:=»Odaberite raspon za transponiranje», Title:=»Transponiraj retke u stupce», Type:=8)

    Postavi DestRange = Application.InputBox(Prompt:=»Odaberite gornju lijevu ćeliju odredišnog raspona», Title:=»Transponiraj retke u stupce», Type:=8)

    SourceRange.Copy

    DestRange.Select

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

    Application.CutCopyMode = False

End Sub

No, ništa strašno nije poznato u programiranju. Može se koristiti opisanim višim načinima. A potom naučiti novo po mjerenju starog.

Ostavi odgovor