Funkcija transponiranja v Excelu

Od časa do časa ima lahko uporabnik Excela nalogo spremeniti obseg podatkov, ki imajo vodoravno strukturo, v navpično. Ta proces se imenuje transpozicija. Ta beseda je za večino ljudi nova, saj vam pri običajnem delu z računalnikom ni treba uporabljati te operacije. Toda tisti, ki morajo delati z velikimi količinami podatkov, morajo to vedeti. Danes bomo podrobneje govorili o tem, kako to izvesti, s kakšno funkcijo in podrobno preučiti nekatere druge metode.

Funkcija TRANSPOSE – prestavite obsege celic v Excelu

Ena najbolj zanimivih in funkcionalnih metod prenosa tabele v Excelu je funkcija TRANSP. Z njegovo pomočjo lahko vodoravni obseg podatkov spremenite v navpičnega ali izvedete obratno operacijo. Poglejmo, kako delati z njim.

Sintaksa funkcije

Sintaksa za to funkcijo je neverjetno preprosta: TRANSPONIRAJ(niz). To pomeni, da moramo uporabiti samo en argument, ki je nabor podatkov, ki ga je treba pretvoriti v vodoravni ali navpični pogled, odvisno od tega, kakšen je bil prvotno.

Prenos navpičnih obsegov celic (stolpcev)

Recimo, da imamo stolpec z obsegom B2:B6. Vsebujejo lahko že pripravljene vrednosti in formule, ki vrnejo rezultate v te celice. Za nas to ni tako pomembno, prenos je možen v obeh primerih. Po uporabi te funkcije bo dolžina vrstice enaka prvotni dolžini stolpca obsega.

Zaporedje korakov za uporabo te formule je naslednje:

1. Izberite vrstico. V našem primeru ima dolžino pet celic.
2. Po tem premaknite kazalec v vrstico s formulami in tam vnesite formulo =TRANSP(B2:B6).
3. Pritisnite kombinacijo tipk Ctrl + Shift + Enter.

Seveda morate v vašem primeru določiti obseg, ki je značilen za vašo tabelo.

Prenos vodoravnih obsegov celic (vrstic)

Načeloma je mehanizem delovanja skoraj enak prejšnjemu odstavku. Recimo, da imamo niz z začetnimi in končnimi koordinatami B10:F10. Vsebuje lahko tudi neposredne vrednosti in formule. Iz nje naredimo stolpec, ki bo enakih dimenzij kot prvotna vrstica. Zaporedje dejanj je naslednje:

1. Z miško izberite ta stolpec. Uporabite lahko tudi tipke na tipkovnici Ctrl in puščico navzdol, potem ko kliknete najvišjo celico tega stolpca.
2. Po tem napišemo formulo =TRANSP(B10:F10) v vrstici formule.
3. Zapišemo jo kot matrično formulo s kombinacijo tipk Ctrl + Shift + Enter.

Transponiranje s posebnim lepljenjem

Druga možna možnost prenosa je uporaba funkcije Posebno lepljenje. To ni več operator, ki bi ga uporabljali v formulah, ampak je tudi eden od priljubljenih načinov za spreminjanje stolpcev v vrstice in obratno.

Ta možnost je na zavihku Domov. Za dostop do njega morate poiskati skupino »Odložišče« in tam poiskati gumb »Prilepi«. Po tem odprite meni, ki se nahaja pod to možnostjo, in izberite postavko »Transpose«. Pred tem morate izbrati obseg, ki ga želite izbrati. Kot rezultat bomo dobili enak razpon, le nasprotno se zrcali.

3 načini za prenos tabele v Excel

Toda v resnici obstaja veliko več načinov za spreminjanje stolpcev v vrstice in obratno. Opišimo 3 metode, s katerimi lahko prestavimo tabelo v Excel. Zgoraj smo razpravljali o dveh od njih, vendar bomo podali še nekaj primerov, da boste bolje razumeli uXNUMXbuXNUMXbkako izvesti ta postopek.

1. način: Posebno lepljenje

Ta metoda je najpreprostejša. Dovolj je, da pritisnete nekaj gumbov in uporabnik prejme transponirano različico tabele. Za večjo jasnost navedimo majhen primer. Recimo, da imamo tabelo, ki vsebuje informacije o tem, koliko izdelkov je trenutno na zalogi in koliko skupaj stanejo. Sama tabela izgleda takole.

Vidimo, da imamo glavo in stolpec s številkami izdelkov. V našem primeru glava vsebuje informacije o tem, kateri izdelek, koliko stane, koliko je na zalogi in kakšna je skupna cena vseh izdelkov, povezanih s tem artiklom, ki so na zalogi. Strošek dobimo po formuli, kjer strošek pomnožimo s količino. Da bo primer bolj nazoren, naredimo glavo zeleno.

Naša naloga je zagotoviti, da so informacije v tabeli postavljene vodoravno. To pomeni, da stolpci postanejo vrstice. Zaporedje dejanj v našem primeru bo naslednje:

1. Izberite obseg podatkov, ki jih moramo rotirati. Nato te podatke kopiramo.
2. Kazalec postavite kamor koli na list. Nato kliknite desni gumb miške in odprite kontekstni meni.
3. Nato kliknite gumb »Posebno prilepi«.

Ko končate te korake, morate klikniti gumb »Transpose«. Namesto tega potrdite polje poleg tega elementa. Drugih nastavitev ne spreminjamo in nato kliknemo na gumb »V redu«.

Po izvedbi teh korakov nam ostane ista tabela, le njene vrstice in stolpci so razporejeni drugače. Upoštevajte tudi, da so celice, ki vsebujejo iste informacije, označene z zeleno. Vprašanje: kaj se je zgodilo s formulami, ki so bile v prvotnem obsegu? Njihova lokacija se je spremenila, sami pa so ostali. Naslovi celic so se preprosto spremenili v tiste, ki so nastali po transpoziciji.

Skoraj enaka dejanja je treba izvesti za prenos vrednosti, ne formul. V tem primeru morate uporabiti tudi meni Posebno lepljenje, vendar pred tem izberite obseg podatkov, ki vsebujejo vrednosti. Vidimo, da lahko okno Posebno lepljenje pokličemo na dva načina: prek posebnega menija na traku ali kontekstnega menija.

Metoda 2. Funkcija TRANSP v Excelu

Pravzaprav se ta metoda ne uporablja več tako aktivno, kot je bila na samem začetku pojava tega programa za preglednice. To je zato, ker je ta metoda veliko bolj zapletena kot uporaba posebnega lepljenja. Vendar pa najde svojo uporabo pri avtomatiziranem prenosu tabel.

Tudi ta funkcija je v Excelu, zato jo je nujno poznati, čeprav se skoraj ne uporablja več. Prej smo obravnavali postopek, kako delati z njim. Zdaj bomo to znanje dopolnili z dodatnim primerom.

1. Najprej moramo izbrati obseg podatkov, ki bo uporabljen za prenos tabele. Samo nasprotno morate izbrati območje. Na primer, v tem primeru imamo 4 stolpce in 6 vrstic. Zato je treba izbrati območje z nasprotnimi lastnostmi: 6 stolpcev in 4 vrstice. Slika to zelo dobro prikazuje.
2. Po tem takoj začnemo polniti to celico. Pomembno je, da izbire ne odstranite pomotoma. Zato morate formulo določiti neposredno v vrstici s formulami.
3. Nato pritisnite kombinacijo tipk Ctrl + Shift + Enter. Ne pozabite, da je to matrična formula, saj delamo z velikim naborom podatkov hkrati, ki bodo preneseni v drug velik nabor celic.

Ko vnesemo podatke, pritisnemo tipko Enter, nakar dobimo naslednji rezultat.

Vidimo, da formula ni bila prenesena v novo tabelo. Izgubljeno je bilo tudi oblikovanje. Poeto

Vse to bo treba narediti ročno. Poleg tega ne pozabite, da je ta tabela povezana z izvirno. Zato se te prilagoditve samodejno izvedejo v transponirano tabelo, takoj ko se nekatere informacije spremenijo v izvirnem obsegu.

Zato je ta metoda zelo primerna v primerih, ko morate zagotoviti, da je prenesena tabela povezana z izvirno. Če uporabljate poseben vložek, te možnosti ne bo več.

povzetek tabele

To je popolnoma nova metoda, ki omogoča ne le prenos tabele, temveč tudi izvajanje velikega števila dejanj. Res je, mehanizem prenosa bo v primerjavi s prejšnjimi metodami nekoliko drugačen. Zaporedje dejanj je naslednje:

1. Naredimo vrtilno tabelo. Če želite to narediti, morate izbrati tabelo, ki jo moramo prenesti. Po tem pojdite na element »Vstavi« in tam poiščite »Vrtilna tabela«. Prikaže se pogovorno okno, kot je na tem posnetku zaslona.
2. Tukaj lahko znova dodelite obseg, iz katerega bo izdelan, kot tudi opravite številne druge nastavitve. Sedaj nas zanima predvsem mesto vrtilne tabele – na novem listu.
3. Po tem bo samodejno ustvarjena postavitev vrtilne tabele. V njem je treba označiti tiste predmete, ki jih bomo uporabili, nato pa jih je treba premakniti na pravo mesto. V našem primeru moramo element »Izdelek« premakniti v »Imena stolpcev« in »Cena na kos« v »Vrednosti«.
4. Po tem bo vrtilna tabela končno ustvarjena. Dodaten bonus je avtomatski izračun končne vrednosti.
5. Spremenite lahko tudi druge nastavitve. Na primer, počistite polje »Cena na kos« in označite element »Skupni stroški«. Kot rezultat bomo imeli tabelo s podatki o tem, koliko stanejo izdelki. Ta metoda prenosa je veliko bolj funkcionalna od drugih. Opišimo nekaj prednosti vrtilnih tabel:

1. Avtomatizacija. S pomočjo vrtilnih tabel lahko samodejno povzemate podatke, pa tudi poljubno spreminjate položaj stolpcev in stolpcev. Če želite to narediti, vam ni treba izvesti nobenih dodatnih korakov.
2. Interaktivnost. Uporabnik lahko spremeni strukturo informacij tolikokrat, kolikor je potrebno za opravljanje svojih nalog. Na primer, lahko spremenite vrstni red stolpcev, pa tudi združite podatke na poljuben način. To lahko storite tolikokrat, kot uporabnik potrebuje. In traja dobesedno manj kot minuto.
3. Enostavno formatiranje podatkov. Vrtilno tabelo je zelo enostavno urediti tako, kot si človek želi. Če želite to narediti, naredite le nekaj klikov z miško.
4. Pridobivanje vrednosti. Ogromno število formul, ki se uporabljajo za ustvarjanje poročil, je neposredno dostopnih osebi in jih je enostavno vključiti v vrtilno tabelo. To so podatki, kot so seštevanje, pridobivanje aritmetične sredine, določanje števila celic, množenje, iskanje največjih in najmanjših vrednosti v določenem vzorcu.
5. Sposobnost ustvarjanja zbirnih grafikonov. Če se vrtilne tabele ponovno izračunajo, se njihovi povezani grafikoni samodejno posodobijo. Možno je ustvariti toliko grafikonov, kot jih potrebujete. Vse jih je mogoče spremeniti za določeno nalogo in ne bodo medsebojno povezane.
6. Možnost filtriranja podatkov.
7. Vrtilno tabelo je mogoče sestaviti na podlagi več kot enega niza izvornih informacij. Zato bo njihova funkcionalnost še večja.

Res je, pri uporabi vrtilnih tabel je treba upoštevati naslednje omejitve:

1. Vseh informacij ni mogoče uporabiti za ustvarjanje vrtilnih tabel. Preden jih lahko uporabimo v ta namen, je treba celice normalizirati. Z enostavnimi besedami, razumejte. Obvezne zahteve: prisotnost naslovne vrstice, polnost vseh vrstic, enakost formatov podatkov.
2. Podatki se posodabljajo polavtomatsko. Če želite dobiti nove informacije v vrtilni tabeli, morate klikniti poseben gumb.
3. Vrtilne tabele zavzamejo veliko prostora. To lahko povzroči nekatere motnje v delovanju računalnika. Prav tako bo datoteko zaradi tega težko poslati po e-pošti.

Prav tako po izdelavi vrtilne tabele uporabnik nima možnosti dodajanja novih informacij.