Transponovana funkcija u Excelu

S vremena na vrijeme, korisnik Excela može imati zadatak da niz podataka koji imaju horizontalnu strukturu pretvori u vertikalnu. Ovaj proces se naziva transpozicija. Ova riječ je za većinu ljudi nova, jer u normalnom radu na računaru ne morate koristiti ovu operaciju. Međutim, oni koji moraju raditi s velikim količinama podataka moraju znati kako to učiniti. Danas ćemo detaljnije govoriti o tome kako to izvesti, s kojom funkcijom, a također ćemo detaljnije pogledati neke druge metode.

TRANSPOSE funkcija – transponovanje raspona ćelija u Excelu

Jedna od najzanimljivijih i najfunkcionalnijih metoda transpozicije tablice u Excelu je funkcija TRANSP. Uz njegovu pomoć možete horizontalni raspon podataka pretvoriti u vertikalni ili izvršiti obrnutu operaciju. Hajde da vidimo kako da radimo sa tim.

Sintaksa funkcije

Sintaksa za ovu funkciju je neverovatno jednostavna: TRANSPOSE(niz). Odnosno, trebamo koristiti samo jedan argument, a to je skup podataka koji treba pretvoriti u horizontalni ili vertikalni prikaz, ovisno o tome kakav je izvorno bio.

Transponiranje vertikalnih raspona ćelija (kolona)

Pretpostavimo da imamo kolonu sa opsegom B2:B6. Mogu sadržavati i gotove vrijednosti i formule koje vraćaju rezultate u ove ćelije. Za nas to nije toliko bitno, transpozicija je moguća u oba slučaja. Nakon primjene ove funkcije, dužina reda će biti ista kao i originalna dužina stupca raspona.

Redoslijed koraka za korištenje ove formule je sljedeći:

1. Odaberite liniju. U našem slučaju ima dužinu od pet ćelija.
2. Nakon toga, pomaknite kursor na traku formule i tamo unesite formulu =TRANSP(B2:B6).
3. Pritisnite kombinaciju tipki Ctrl + Shift + Enter.

Naravno, u vašem slučaju morate odrediti raspon koji je tipičan za vašu tablicu.

Transponiranje horizontalnih raspona ćelija (redova)

U principu, mehanizam djelovanja je skoro isti kao u prethodnom paragrafu. Pretpostavimo da imamo niz sa početnim i krajnjim koordinatama B10:F10. Također može sadržavati i direktne vrijednosti i formule. Napravimo od njega stupac koji će imati iste dimenzije kao i originalni red. Redoslijed radnji je sljedeći:

1. Odaberite ovu kolonu mišem. Takođe možete koristiti tastere na tastaturi Ctrl i strelicu nadole, nakon što kliknete na najgornju ćeliju ove kolone.
2. Nakon toga pišemo formulu =TRANSP(B10:F10) u traci formule.
3. Pišemo ga kao formulu niza koristeći kombinaciju tipki Ctrl + Shift + Enter.

Transponiranje sa Paste Special

Druga moguća opcija transpozicije je korištenje funkcije Paste Special. Ovo više nije operator koji se koristi u formulama, ali je također jedna od popularnih metoda za pretvaranje stupaca u redove i obrnuto.

Ova opcija se nalazi na kartici Početna. Da biste joj pristupili, morate pronaći grupu "Clipboard" i tamo pronaći dugme "Zalijepi". Nakon toga otvorite meni koji se nalazi ispod ove opcije i odaberite stavku “Transponiraj”. Prije toga morate odabrati raspon koji želite odabrati. Kao rezultat, dobit ćemo isti raspon, samo što je suprotno preslikano.

3 načina transponovanja tabele u Excel

Ali u stvari, postoji mnogo više načina za pretvaranje stupaca u redove i obrnuto. Hajde da opišemo 3 metode pomoću kojih možemo transponovati tabelu u Excel. Gore smo raspravljali o dva od njih, ali ćemo dati još nekoliko primjera kako biste dobili bolju predstavu o tome kako izvršiti ovu proceduru.

Metoda 1: Posebno zalijepite

Ova metoda je najjednostavnija. Dovoljno je pritisnuti nekoliko tastera i korisnik dobija transponovanu verziju tabele. Dajemo mali primjer radi veće jasnoće. Pretpostavimo da imamo tabelu koja sadrži informacije o tome koliko proizvoda je trenutno na zalihama, kao i koliko koštaju ukupno. Sama tabela izgleda ovako.

Vidimo da imamo zaglavlje i kolonu sa brojevima proizvoda. U našem primjeru, zaglavlje sadrži informacije o tome koji proizvod, koliko košta, koliko je na zalihama i kolika je ukupna cijena svih proizvoda vezanih za ovaj artikl koji su na zalihama. Dobijamo trošak prema formuli gdje se trošak množi sa količinom. Da bi primjer bio vizualniji, učinimo zaglavlje zelenim.

Naš zadatak je osigurati da se informacije sadržane u tabeli nalaze vodoravno. Odnosno, tako da kolone postanu redovi. Redoslijed radnji u našem slučaju bit će sljedeći:

1. Odaberite raspon podataka koje trebamo rotirati. Nakon toga kopiramo ove podatke.
2. Postavite kursor bilo gdje na listu. Zatim kliknite na desnu tipku miša i otvorite kontekstni meni.
3. Zatim kliknite na dugme "Specijalno zalijepi".

Nakon što završite ove korake, potrebno je da kliknete na dugme „Transponiraj“. Umjesto toga, označite polje pored ove stavke. Ostale postavke ne mijenjamo, a zatim kliknemo na dugme “OK”.

Nakon izvođenja ovih koraka ostaje nam ista tabela, samo su njeni redovi i kolone drugačije raspoređeni. Također imajte na umu da su ćelije koje sadrže iste informacije označene zelenom bojom. Pitanje: šta se desilo sa formulama koje su bile u originalnom opsegu? Njihova lokacija se promijenila, ali oni sami su ostali. Adrese ćelija su se jednostavno promijenile u one koje su nastale nakon transpozicije.

Gotovo iste radnje potrebno je izvršiti za transponiranje vrijednosti, a ne formula. U ovom slučaju, također morate koristiti izbornik Paste Special, ali prije toga odaberite raspon podataka koji sadrži vrijednosti. Vidimo da se prozor Specijalno lijepljenje može pozvati na dva načina: kroz poseban meni na traci ili kroz kontekstni meni.

Metoda 2. TRANSP funkcija u Excelu

Zapravo, ova metoda se više ne koristi tako aktivno kao što je bila na samom početku pojave ovog programa za proračunske tablice. To je zato što je ova metoda mnogo složenija od korištenja Paste Special. Međutim, pronalazi svoju upotrebu u automatizaciji transpozicije tablice.

Također, ova funkcija je u Excel-u, pa je neophodno znati za nju, iako se više gotovo i ne koristi. Ranije smo razmatrali proceduru, kako raditi s njom. Sada ćemo ovo znanje dopuniti dodatnim primjerom.

1. Prvo, moramo odabrati raspon podataka koji će se koristiti za transponiranje tablice. Vi samo trebate odabrati područje naprotiv. Na primjer, u ovom primjeru imamo 4 stupca i 6 redova. Stoga je potrebno odabrati područje sa suprotnim karakteristikama: 6 stupaca i 4 reda. Slika to jako dobro pokazuje.
2. Nakon toga, odmah počinjemo popunjavati ovu ćeliju. Važno je da slučajno ne uklonite odabir. Stoga morate navesti formulu direktno u traci formule.
3. Zatim pritisnite kombinaciju tipki Ctrl + Shift + Enter. Zapamtite da je ovo formula niza, budući da radimo s velikim skupom podataka odjednom, koji će se prenijeti u drugi veliki skup ćelija.

Nakon što unesemo podatke, pritisnemo tipku Enter, nakon čega dobivamo sljedeći rezultat.

Vidimo da formula nije prebačena u novu tabelu. Formatiranje je također izgubljeno. Poeto

Sve ovo će morati da se uradi ručno. Osim toga, zapamtite da je ova tabela povezana s originalnom. Stoga, čim se neke informacije promijene u originalnom rasponu, ova prilagođavanja se automatski vrše na transponiranoj tabeli.

Stoga je ova metoda dobro prikladna u slučajevima kada trebate osigurati da je transponirana tablica povezana s originalnom. Ako koristite poseban umetak, ova mogućnost više neće biti.

sumarna tabela

Ovo je fundamentalno nova metoda, koja omogućava ne samo transponiranje tablice, već i izvođenje velikog broja radnji. Istina, mehanizam transpozicije bit će nešto drugačiji u odnosu na prethodne metode. Redoslijed radnji je sljedeći:

1. Napravimo stožernu tabelu. Da biste to učinili, morate odabrati tablicu koju trebamo transponirati. Nakon toga idite na stavku "Umetanje" i tamo potražite "Pivot Table". Pojavit će se dijaloški okvir poput ovog na snimku ekrana.
2. Ovdje možete preraspodijeliti raspon iz kojeg će biti napravljen, kao i napraviti niz drugih postavki. Sada nas prvenstveno zanima mjesto stožerne tablice – na novom listu.
3. Nakon toga, automatski će se kreirati izgled pivot tablice. U njemu je potrebno označiti one predmete koje ćemo koristiti, a zatim ih premjestiti na pravo mjesto. U našem slučaju, trebamo premjestiti stavku “Proizvod” u “Nazivi kolona”, a “Cijena po komadu” u “Vrijednosti”.
4. Nakon toga, stožerna tabela će biti konačno kreirana. Dodatni bonus je automatski obračun konačne vrijednosti.
5. Možete promijeniti i druge postavke. Na primjer, poništite opciju “Cijena po komadu” i označite stavku “Ukupni troškovi”. Kao rezultat toga, imat ćemo tabelu koja sadrži informacije o tome koliko koštaju proizvodi. Ova metoda transpozicije je mnogo funkcionalnija od ostalih. Hajde da opišemo neke od prednosti pivot tabela:

1. Automatizacija. Uz pomoć pivot tabela možete automatski sumirati podatke, kao i proizvoljno mijenjati položaj kolona i kolona. Da biste to učinili, ne morate izvršiti nikakve dodatne korake.
2. Interaktivnost. Korisnik može promijeniti strukturu informacija onoliko puta koliko mu je potrebno za obavljanje svojih zadataka. Na primjer, možete promijeniti redoslijed kolona, ​​kao i grupne podatke na proizvoljan način. Ovo se može učiniti onoliko puta koliko je potrebno korisniku. I bukvalno traje manje od minute.
3. Lako formatiranje podataka. Vrlo je lako urediti stožerni sto na način na koji osoba želi. Da biste to učinili, samo napravite nekoliko klikova mišem.
4. Dobivanje vrijednosti. Ogroman broj formula koje se koriste za kreiranje izveštaja nalaze se u direktnoj dostupnosti osobe i lako se integrišu u zaokretnu tabelu. To su podaci kao što su zbrajanje, dobivanje aritmetičke sredine, određivanje broja ćelija, množenje, pronalaženje najveće i najmanje vrijednosti u navedenom uzorku.
5. Mogućnost kreiranja sažetih grafikona. Ako se zaokretne tabele ponovo izračunavaju, njihovi povezani grafikoni se automatski ažuriraju. Moguće je kreirati onoliko grafikona koliko vam je potrebno. Svi se oni mogu mijenjati za određeni zadatak i neće biti međusobno povezani.
6. Mogućnost filtriranja podataka.
7. Moguće je izgraditi pivot tablicu na osnovu više od jednog skupa izvornih informacija. Stoga će njihova funkcionalnost postati još veća.

Istina, kada koristite zaokretne tablice, moraju se uzeti u obzir sljedeća ograničenja:

1. Ne mogu se sve informacije koristiti za generiranje pivot tablica. Prije nego što se mogu koristiti u tu svrhu, ćelije se moraju normalizirati. Jednostavnim riječima, shvatite kako treba. Obavezni zahtjevi: postojanje reda zaglavlja, popunjenost svih redova, jednakost formata podataka.
2. Podaci se ažuriraju poluautomatski. Da biste dobili nove informacije u pivot tabeli, morate kliknuti na posebno dugme.
3. Okretni stolovi zauzimaju puno prostora. To može dovesti do nekih poremećaja u radu računara. Takođe, fajl će biti teško poslati e-poštom zbog toga.

Takođe, nakon kreiranja pivot tabele, korisnik nema mogućnost dodavanja novih informacija.