Transponiranje funkcije u Excelu

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

TRANSPOSE funkcija – transponiranje raspona ćelija u Excelu

Jedna od najzanimljivijih i najfunkcionalnijih metoda transpozicije tablice u Excelu je funkcija TRANSP. Uz njegovu pomoć možete vodoravni raspon podataka pretvoriti u okomiti ili izvršiti obrnutu operaciju. Pogledajmo kako s njim raditi.

Sintaksa funkcije

Sintaksa ove funkcije je nevjerojatno jednostavna: TRANSPONIRAJ(niz). Odnosno, trebamo koristiti samo jedan argument, a to je skup podataka koji se treba pretvoriti u vodoravni ili okomiti prikaz, ovisno o tome što je izvorno bio.

Transponiranje okomitih raspona ćelija (stupaca)

Pretpostavimo da imamo stupac s rasponom B2:B6. Mogu sadržavati i gotove vrijednosti i formule koje vraćaju rezultate u te ćelije. Nama to nije toliko važno, transpozicija je moguća u oba slučaja. Nakon primjene ove funkcije, duljina retka bit će ista kao izvorna duljina stupca raspona.

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

1. Odaberite liniju. U našem slučaju ima duljinu 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 navesti raspon koji je tipičan za vašu tablicu.

Transponiranje vodoravnih raspona ćelija (redova)

U principu, mehanizam djelovanja je gotovo isti kao u prethodnom odlomku. Pretpostavimo da imamo niz s početnim i krajnjim koordinatama B10:F10. Također može sadržavati i izravne vrijednosti i formule. Napravimo od njega stupac koji će imati iste dimenzije kao izvorni red. Slijed radnji je sljedeći:

1. Odaberite ovaj stupac mišem. Također možete koristiti tipke na tipkovnici Ctrl i strelicu prema dolje, nakon što kliknete na najvišu ćeliju ovog stupca.
2. Nakon toga napišemo formulu =TRANSP(B10:F10) u traci formule.
3. Zapisujemo ga kao formulu niza pomoću kombinacije tipki Ctrl + Shift + Enter.

Transponiranje pomoću 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 retke i obrnuto.

Ova se opcija nalazi na kartici Početna. Da biste mu pristupili, morate pronaći grupu "Međuspremnik", a tamo pronaći gumb "Zalijepi". Nakon toga otvorite izbornik koji se nalazi ispod ove opcije i odaberite stavku “Transpose”. Prije toga morate odabrati raspon koji želite odabrati. Kao rezultat toga, dobit ćemo isti raspon, samo se suprotno zrcali.

3 načina transponiranja tablice u Excelu

Ali zapravo postoji mnogo više načina za pretvaranje stupaca u retke i obrnuto. Opišimo 3 metode pomoću kojih možemo transponirati tablicu u Excelu. Gore smo raspravljali o dva od njih, ali dat ćemo još nekoliko primjera kako biste stekli bolju ideju o uXNUMXbuXNUMXbkako izvršiti ovaj postupak.

Metoda 1: Posebno lijepljenje

Ova metoda je najjednostavnija. Dovoljno je pritisnuti nekoliko tipki i korisnik dobiva transponiranu verziju tablice. Navedimo mali primjer radi veće jasnoće. Pretpostavimo da imamo tablicu koja sadrži podatke o tome koliko je proizvoda trenutno na zalihi, kao i koliko ukupno koštaju. Sama tablica izgleda ovako.

Vidimo da imamo zaglavlje i stupac s brojevima proizvoda. U našem primjeru, zaglavlje sadrži informacije o tome koji je proizvod, koliko košta, koliko ga ima na zalihi i kolika je ukupna cijena svih proizvoda povezanih s ovom stavkom koji su na zalihi. Trošak dobivamo prema formuli gdje se trošak množi s količinom. Kako bismo primjer učinili vizualnijim, neka zaglavlje postane zeleno.

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

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

Nakon dovršetka ovih koraka, trebate kliknuti na gumb "Transpose". Radije označite okvir pored ove stavke. Ostale postavke ne mijenjamo, a zatim kliknite na gumb "OK".

Nakon izvođenja ovih koraka, ostaje nam ista tablica, samo su njeni redovi i stupci drugačije raspoređeni. Također imajte na umu da su ćelije koje sadrže iste informacije označene zelenom bojom. Pitanje: što se dogodilo s formulama koje su bile u izvornom rasponu? Njihov položaj se promijenio, ali su oni sami ostali. Adrese ćelija jednostavno su se promijenile u one koje su nastale nakon transpozicije.

Za transponiranje vrijednosti potrebno je izvršiti gotovo iste radnje, a ne formule. U tom slučaju također trebate koristiti izbornik Posebno lijepljenje, ali prije toga odaberite raspon podataka koji sadrže vrijednosti. Vidimo da se prozor Paste Special može pozvati na dva načina: kroz poseban izbornik na vrpci ili kontekstni izbornik.

Metoda 2. TRANSP funkcija u Excelu

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

Također, ova funkcija postoji u Excelu, pa je nužno znati o njoj, iako se više gotovo i ne koristi. Ranije smo razmotrili postupak, kako raditi s njim. 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 redaka. Stoga je potrebno odabrati područje suprotnih karakteristika: 6 stupaca i 4 reda. Slika to jako dobro pokazuje.
2. Nakon toga odmah počinjemo ispunjavati ovu ćeliju. Važno je ne slučajno ukloniti odabir. Stoga morate navesti formulu izravno u traci formule.
3. Zatim pritisnite kombinaciju tipki Ctrl + Shift + Enter. Imajte na umu da je ovo formula polja, budući da radimo s velikim skupom podataka odjednom, koji će biti prebačeni u drugi veliki skup ćelija.

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

Vidimo da formula nije prenesena u novu tablicu. Formatiranje je također izgubljeno. Poeto

Sve će to morati biti učinjeno ručno. Osim toga, zapamtite da je ova tablica povezana s izvornom. Stoga, čim se neke informacije promijene u izvornom rasponu, te se prilagodbe automatski vrše na transponiranoj tablici.

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

sažetak tablice

Ovo je temeljno nova metoda koja omogućuje ne samo transponiranje tablice, već i izvođenje velikog broja radnji. Istina, mehanizam transpozicije bit će nešto drugačiji u usporedbi s prethodnim metodama. Slijed radnji je sljedeći:

1. Napravimo stožernu tablicu. Da biste to učinili, morate odabrati tablicu koju trebamo transponirati. Nakon toga idite na stavku "Umetni" i tamo potražite "Zaokretnu tablicu". Pojavit će se dijaloški okvir poput ovog na ovoj snimci zaslona.
2. Ovdje možete ponovno dodijeliti raspon iz kojeg će biti izrađen, kao i napraviti niz drugih postavki. Sada nas prvenstveno zanima mjesto stožerne tablice – na novom listu.
3. Nakon toga će se automatski kreirati izgled zaokretne tablice. U njemu je potrebno označiti one predmete koje ćemo koristiti, a zatim ih premjestiti na pravo mjesto. U našem slučaju, moramo premjestiti stavku "Proizvod" u "Imena stupaca", a "Cijenu po komadu" u "Vrijednosti".
4. Nakon toga će pivot tablica biti konačno kreirana. Dodatni bonus je automatski izračun konačne vrijednosti.
5. Možete promijeniti i druge postavke. Na primjer, poništite stavku “Cijena po komadu” i označite stavku “Ukupni trošak”. Kao rezultat, imat ćemo tablicu s podacima o tome koliko koštaju proizvodi. Ova metoda prijenosa puno je funkcionalnija od ostalih. Opišimo neke od prednosti zaokretnih tablica:

1. Automatizacija. Uz pomoć zaokretnih tablica možete automatski sažeti podatke, kao i proizvoljno mijenjati položaj stupaca i stupaca. Da biste to učinili, ne morate poduzimati nikakve dodatne korake.
2. Interaktivnost. Korisnik može mijenjati strukturu informacija onoliko puta koliko mu je potrebno za obavljanje zadataka. Na primjer, možete promijeniti redoslijed stupaca, kao i grupirati podatke na proizvoljan način. To se može učiniti onoliko puta koliko korisnik treba. I doslovno traje manje od minute.
3. Lako formatiranje podataka. Vrlo je jednostavno postaviti stožernu tablicu 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 izradu izvješća nalaze se u izravnoj dostupnosti osobe i lako ih je integrirati u stožernu tablicu. 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. Sposobnost izrade sažetih grafikona. Ako se zaokretne tablice ponovno izračunaju, njihovi povezani grafikoni automatski se ažuriraju. Moguće je izraditi 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 izraditi zaokretnu tablicu na temelju više od jednog skupa izvornih informacija. Stoga će njihova funkcionalnost postati još veća.

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

1. Ne mogu se sve informacije koristiti za generiranje zaokretnih tablica. Prije nego što se mogu koristiti u tu svrhu, stanice se moraju normalizirati. Jednostavnim riječima, shvatite kako treba. Obavezni zahtjevi: prisutnost retka zaglavlja, punoća svih redaka, jednakost formata podataka.
2. Podaci se ažuriraju poluautomatski. Da biste dobili nove informacije u zaokretnoj tablici, morate kliknuti na poseban gumb.
3. Pivot tablice zauzimaju puno prostora. To može dovesti do nekih smetnji u radu računala. Također, datoteku će zbog toga biti teško poslati e-poštom.

Također, nakon izrade pivot tablice, korisnik nema mogućnost dodavanja novih informacija.