Kako pretvoriti redove u kolone u Excelu

Ovaj vodič objašnjava šta treba učiniti da pretvorite redove u stupce u programu Excel. Kada koristite Excel, često morate da menjate redove i kolone u tabeli. Na primjer, dešava se da je osoba napravila ogromnu tablicu, a onda je shvatila da je mnogo lakše pročitati ako je okrenete. 

Ova detaljna uputstva prikazuje nekoliko načina transponovanja Excel tablica, kao i česte greške koje mogu da se suoče sa korisnikom. Sve se mogu koristiti u bilo kojoj verziji Excela, kao vrlo staroj, tako i samoj novoj.

Korištenje funkcije Paste Special

Recimo da imate dva skupa podataka. Drugo je ono sa čime želite da završite. Prva je tabela koju treba transponovati. U prvoj verziji imena zemalja su predstavljena u različitim stupcima, a vrlo je nezgodno čitati, a još više, međusobno upoređivati ​​karakteristike država. Stoga je mnogo bolje organizirati tabelu tako da se nazivi zemalja pojavljuju u različitim stupcima.Kako pretvoriti redove u kolone u Excelu

Da biste zamijenili redove i stupce, uradite sljedeće:

  1. Odaberite originalnu tabelu. Ako želite da vidite celu tabelu odjednom (ako je veoma velika), potrebno je da pritisnete kombinaciju tastera Ctrl + Home, a nakon toga – Ctrl + Shift + End.
  2. Ćelije se zatim kopiraju. To se može učiniti kroz kontekstni meni ili pritiskom na kombinaciju tipki Ctrl+C. Preporučuje se da se odmah naviknete na posljednju opciju, jer ako naučite vruće tipke, mnoge zadatke možete obaviti doslovno u jednoj sekundi.
  3. Odaberite prvu ćeliju u ciljnom rasponu. Pri étom on dolžan nahoditʹsâ za krajami tablice. Također je potrebno odabrati jačejku na ovaj način, da tablicu nakon transponiranja ne mijenjaju s drugim podacima. Na primjer, ako početna tablica ima 4 stupca i 10 redaka, tada će nakon izvođenja svih ovih operacija proširiti 10 ćelija prema dolje i 4 ćelije u stranu. Stoga, unutar ovog raspona (računajući od ciljne ćelije) ne bi trebalo biti ništa.
  4. Na ciljnoj ćeliji morate kliknuti desnom tipkom miša i odabrati "Specijalno zalijepi", a zatim potvrditi okvir pored natpisa "Transponirati".Kako pretvoriti redove u kolone u Excelu

Bitan: ako postoje formule u izvornoj tabeli, važno je osigurati da se apsolutne reference koriste u svakoj ćeliji. Ovo se mora učiniti tako da se sve veze automatski ažuriraju.

Ogromna prednost opcija «Posebna ustavka» je mogućnost transponovanja tabele samo za nekoliko sekundi. I pri tome se potpuno zadržava formatiranje, što takođe omogućava ekonomičnost kuču vremena.

Bez obzira na ove âvnye pluse, postoji i niz ozbiljnih nedostataka, koji mešaju ovaj metod, nazivaju se univerzalnim:

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

Kako zamijeniti redove i kolone i povezati ih sa glavnom tablicom?

Dakle, šta se može učiniti da opcija “Specijalno zalijepi” može povezati tabelu sa glavnim podacima i onim koji je rezultat transpozicije? Uostalom, svi bi željeli da se podaci automatski ažuriraju.

  1. Skopirajte tablicu, za koju je potrebno transponiranje.
  2. Odaberite ćeliju bez podataka u praznom području tabele.
  3. Pokrenite meni Paste Special, slično kao u prethodnom primjeru. Nakon toga, potrebno je da kliknete na dugme „Ubaci vezu“, koje se nalazi u donjem levom uglu.Kako pretvoriti redove u kolone u Excelu
  4. Rezultat će biti sljedeći.Kako pretvoriti redove u kolone u Excelu
  5. Izaberite novu tabelu i pustite okno «Nađi i zameni» pritiskom kombinacije tastera Ctrl + H.
  6. Zamijenite sve znakove vvodne formule (=) na hhh (ili volite drugu kombinaciju znakova, koja nema u originalnoj tablici).Kako pretvoriti redove u kolone u Excelu
  7. Kao rezultat toga, ispostavit će se nešto strašno, ali ovo je obavezan međuuvjet za postizanje željenog rezultata. Tada će sve izgledati predivno.Kako pretvoriti redove u kolone u Excelu
  8. Kopirajte rezultirajuću tablicu i zatim koristite Paste Special da je transponirate.

Nakon toga, trebate samo ponovo otvoriti dijalog "Pronađi i zamijeniti" i koristiti ga da promijenite "xxx" u ćelijama u "=" tako da sve ćelije budu povezane s originalnim informacijama.Kako pretvoriti redove u kolone u ExceluNaravno, ovo je složenije i duže, ali ova metoda vam omogućava da zaobiđete nedostatak veze s originalnom tablicom. Ali ovaj pristup ima i nedostatak. Izražava se u potrebi da se samostalno nastavi formatiranje.

Primjena formula

Postoje dvije funkcije koje vam omogućavaju da fleksibilno mijenjate redove i stupce: TRANSP i DVSSYL. Ovdje također postoji mogućnost očuvanja veze s prvobitnom tablicom, a nekoliko mehanika radi.

funkcija TRANSP

Zapravo, ova formula direktno transponuje tabelu. 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 redove u kolone u Excelu

  1. Izbrojite broj kolona i redova u tabeli i pronađite prazan prostor na listu koji ima iste dimenzije.
  2. Pokrenite režim uređivanja pritiskom na taster F2.
  3. Napišite funkciju TRANSP sa rasponom podataka u zagradama. Važno je uvijek koristiti apsolutne reference kada koristite ovu funkciju.Kako pretvoriti redove u kolone u Excelu
  4. Pritisnite kombinaciju tastera Ctrl+Shift+Enter. Važno je da pritisnete tačno kombinaciju tastera, inače formula neće raditi.

To je to, sada rezultat izgleda ovako!Kako pretvoriti redove u kolone u Excelu

Prednost ove formule je ista kao i prethodne metode (koristeći funkciju „pronađi i zamijeni“) je da kada se osnovne informacije promijene, transponovana tabela će se automatski ažurirati.

Nema nedostataka u nee to isto imasâ:

  1. Formatiranje, kao u slučaju metode Find and Replace, neće biti sačuvano.
  2. Originalna tabela mora sadržavati neke podatke, inače će u nekim ćelijama biti nule.
  3. Izlišna zavisnost od izvora danih. Da postoji, ovaj metod ima protivpoloženi nedostatak – nemogućnost promene transponovane tabele. Ako pokušate ovo napraviti, program skaže, što je nemoguće urediti dio mase.

Također, ova funkcija se ne može uvijek fleksibilno primijeniti u različitim situacijama, tako da morate znati o njoj, ali koristiti efikasnije tehnike.

Koristeći INDIREKTNU formulu

Mehanika ove metode je vrlo slična korištenju formule TRANSP, ali pri tom njegovo korištenje rješava problem nemogućnosti uređivanja transponirane tablice bez povezivanja s osnovnim informacijama.

No odnoj formuly INDIREKTNO nije dovoljno: još uvijek morate koristiti funkciju ADDRESS. U ovom primjeru neće biti velike tabele, kako vas ne bismo preopteretili s puno nepotrebnih informacija.

Dakle, recimo da imate takvu tabelu koja se sastoji od 4 kolone i 5 redova.Kako pretvoriti redove u kolone u Excelu

Potrebno je preduzeti sljedeće radnje:

  1. Unesite ovu formulu: =INDIREKTNO(ADRESA(KOLONA(A1),RED(A1))) u gornju lijevu ćeliju nove tabele (u našem primjeru to je A7) i pritisnite tipku enter. Ako informacije ne počinju u prvom redu ili prvom stupcu, morat ćete koristiti složeniju formulu: =ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1)-СТОЛБЕЦ($A$1)+СТРОКА($A$1);СТРОКА(A1)-СТРОКА($A$1)+СТОЛБЕЦ($A$1))). U ovoj formuli, A1 označava gornju ćeliju tabele, na osnovu koje će se formirati transponovana ćelija.
  2. Proširite formulu na cijelo područje gdje će se postaviti nova verzija tabele. Da biste to učinili, povucite marker u donjem desnom kutu prve ćelije na suprotni kraj buduće tablice.Kako pretvoriti redove u kolone u Excelu
  3. Sve! Ispostavilo se da je tabela uspješno transponirana i još uvijek je možete uređivati. Naravno, njen izgled ostavlja mnogo da se poželi, ali popraviti ga nije teško.Kako pretvoriti redove u kolone u Excelu Da biste vratili ispravno formatiranje, potrebno je kopirati tablicu koju smo transponirali (odnosno originalnu), a zatim odabrati novostvorenu tablicu. Zatim kliknite desnim tasterom miša na odabrani raspon, a zatim kliknite na "Formatiranje" u opcijama lijepljenja.Kako pretvoriti redove u kolone u Excelu

Dakle, koristeći funkciju INDIREKTNO omogućava vam da uredite apsolutno bilo koju vrijednost u konačnoj tablici, a podaci će uvijek biti ažurirani čim se izvrše bilo kakve promjene u izvornoj tablici.

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

Ovaj metod je najbolji, budući da je uprkos tome, što se ne održava formatiranje u novostvorenoj tablici, moguće je jednostavno pokrenuti.

Kako funkcionira kombinacija INDIREKTNE i ADRESE formule?

Nakon toga, kako ste se razišli u tom, kao što koristite ovu formulu za transponiranje tablica, možete, možda, više duboko izučiti princip rada ove metode.

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

=INDIREKTNO(“B1”)Kako pretvoriti redove u kolone u Excelu

Kazalosʹ by, začem éto delatʹ? Vedʹ možete prosto napisati ssylku na jačejku na drugoj jačejke. Nema prednosti ove funkcije u tom, što u ssylku možete pretvoriti ljubu stroku, pa čak i tu, koja se potpuno kreira pomoću druge formule. Sobstveno, éto my i delaem u formuli.

Beyond Function ADDRESS takođe se koristi u formuli KOLONA и LINE. Prvi vraća adresu ćelije na osnovu već poznatih brojeva redova i kolona. Ovdje je važno pratiti ispravan redoslijed. Prvo se navodi red, a zatim samo kolona. Na primjer, funkcija ADRESA(4;2) će vratiti adresu $B$2.

Sleduûŝaâ ispolʹzovanaâ vyše funkciâ – éto KOLONA. Ovdje je potrebno da formula dobije broj stupca iz određene reference. Na primjer, ako koristite parametar B2 u zagradama ove funkcije, ona će vratiti broj 2, jer je druga kolona stupac B.

Očigledno, funkcija ROW radi na isti način, samo vraća broj reda. 

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

=INDIREKTNO(ADRESA(KOLONA(A1),RED(A1)))

Ovdje to prvo možete vidjeti u funkciji ADDRESS kolona je navedena, a tek onda red. I tu se krije tajna radne sposobnosti ove formule. Sjećamo se da ova funkcija radi na način ogledala, a prvi argument u njoj je broj reda. I ispada da kada upišemo adresu kolone tamo, ona se pretvara u broj reda i obrnuto.

To estʹ, ako podytožitʹ:

  1. Dobijamo brojeve stupaca i redova pomoću odgovarajućih funkcija.
  2. Korištenje funkcije ADDRESS redovi postaju kolone i obrnuto.
  3. funkcija INDIREKTNO pomaže u prikazu preslikanih podataka u ćeliji.

Tako jednostavno sve ispada!

Korišćenje makroa za transponovanje

Makro je mali program. Može se koristiti za automatizaciju procesa. Važno je uzeti u obzir da makro ima neka ograničenja. Metoda maksimalnog transponovanja omogućava vam da radite sa 65536 elemenata. Ako se ovo ograničenje prekorači, to će rezultirati gubitkom podataka.

Vo vsem ostalim, éto efikasna metoda automatizacije, koja uspeva mnogo oblegčitʹ žiznʹ.

Na primjer, možete upisati trenutni kod, koji će mijenjati mjesta i kolone.

Sub TransposeColumnsRows()

    Dim SourceRange As Range

    Dim DestRange As Range

    Postavite SourceRange = Application.InputBox(Prompt:=»Molimo odaberite opseg za transponovanje», Title:=»Transponiraj redove u kolone», Tip:=8)

    Postavite DestRange = Application.InputBox(Prompt:=»Izaberite gornju lijevu ćeliju odredišnog raspona», Naslov:=»Transponiraj redove u kolone», Tip:=8)

    SourceRange.Copy

    DestRange.Select

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

    Application.CutCopyMode = False

End Sub

No ako je poznat u programirani posebno net, ništa strašnog. Može se koristiti opisanim višim sposobama. A potom učitʹsâ novomu po meri osvoeniâ starog.

Ostavite odgovor