Transponeerige funktsioon Excelis

Aeg-ajalt võib Exceli kasutajal olla ülesanne muuta horisontaalse struktuuriga andmevahemik vertikaalseks. Seda protsessi nimetatakse ülevõtmiseks. See sõna on enamiku inimeste jaoks uus, sest tavalises arvutitöös ei pea te seda toimingut kasutama. Kuid need, kes peavad töötama suurte andmemahtudega, peavad teadma, kuidas seda teha. Täna räägime üksikasjalikumalt, kuidas seda teostada, millise funktsiooniga, ja vaatame üksikasjalikult ka mõnda muud meetodit.

Funktsioon TRANSPOSE – lahtrivahemike transponeerimine Excelis

Üks huvitavamaid ja funktsionaalsemaid tabelite ülekandmise meetodeid Excelis on funktsioon ÜLEKANDMINE. Selle abil saate muuta horisontaalse andmevahemiku vertikaalseks või teha vastupidist toimingut. Vaatame, kuidas sellega töötada.

Funktsiooni süntaks

Selle funktsiooni süntaks on uskumatult lihtne: TRANSPOSE(massiiv). See tähendab, et me peame kasutama ainult ühte argumenti, mis on andmekogum, mis tuleb teisendada horisontaalseks või vertikaalseks vaateks, olenevalt sellest, mis see algselt oli.

Lahtrite (veerude) vertikaalsete vahemike transponeerimine

Oletame, et meil on veerg vahemikus B2:B6. Need võivad sisaldada nii valmisväärtusi kui ka valemeid, mis tagastavad nendele lahtritele tulemused. Meie jaoks pole see nii oluline, ülevõtmine on mõlemal juhul võimalik. Pärast selle funktsiooni rakendamist on rea pikkus sama, mis algse vahemiku veeru pikkus.

Selle valemi kasutamise toimingute jada on järgmine:

1. Valige rida. Meie puhul on selle pikkus viis lahtrit.
2. Seejärel liigutage kursor valemiribale ja sisestage sinna valem =TRANSP(B2:B6).
3. Vajutage klahvikombinatsiooni Ctrl + Shift + Enter.

Loomulikult peate teie puhul määrama teie tabeli jaoks tüüpilise vahemiku.

Horisontaalsete lahtrite vahemike (ridade) transponeerimine

Põhimõtteliselt on toimemehhanism peaaegu sama, mis eelmises lõigus. Oletame, et meil on string algus- ja lõpukoordinaatidega B10:F10. See võib sisaldada ka nii otseseid väärtusi kui ka valemeid. Teeme sellest veeru, millel on samad mõõtmed kui algsel real. Toimingute jada on järgmine:

1. Valige see veerg hiirega. Pärast selle veeru ülemisel lahtril klõpsamist saate kasutada ka klaviatuuri klahve Ctrl ja allanoolt.
2. Pärast seda kirjutame valemi =TRANSP(B10:F10) valemiribal.
3. Kirjutame selle massiivivalemina, kasutades klahvikombinatsiooni Ctrl + Shift + Enter.

Transponeerimine Paste Specialiga

Teine võimalik ülevõtmise võimalus on kasutada funktsiooni Paste Special. See ei ole enam valemites kasutatav operaator, vaid see on ka üks populaarsemaid meetodeid veergude ridadeks muutmiseks ja vastupidi.

See valik on vahekaardil Avaleht. Sellele juurdepääsuks peate leidma grupi "Lõikelaud" ja sealt leidma nupu "Kleebi". Pärast seda avage selle valiku all asuv menüü ja valige üksus "Transponeeri". Enne seda peate valima vahemiku, mida soovite valida. Selle tulemusena saame sama vahemiku, peegeldub ainult vastupidine.

3 võimalust tabeli ülekandmiseks Excelis

Kuid tegelikult on veergude ridadeks muutmiseks ja vastupidi palju rohkem võimalusi. Kirjeldame 3 meetodit, mille abil saame tabelit Excelis üle kanda. Arutasime ülalpool neist kahte, kuid toome veel mõned näited, et saaksite parema ettekujutuse uXNUMXbuXNUMXb kuidas seda protseduuri läbi viia.

1. meetod: kleebi spetsiaalselt

See meetod on kõige lihtsam. Piisab paari nupu vajutamisest ja kasutaja saab tabeli transponeeritud versiooni. Suurema selguse huvides toome väikese näite. Oletame, et meil on tabel, mis sisaldab teavet selle kohta, kui palju tooteid hetkel laos on ja kui palju need kokku maksavad. Tabel ise näeb välja selline.

Näeme, et meil on tootenumbritega päis ja veerg. Meie näites sisaldab päis teavet selle kohta, milline toode, kui palju see maksab, kui palju see on laos ja mis on kõigi selle kaubaga seotud laos olevate toodete kogumaksumus. Kulu saame valemi järgi, kus kulu korrutatakse kogusega. Näite visuaalsemaks muutmiseks muudame päise roheliseks.

Meie ülesanne on tagada, et tabelis sisalduv teave paikneks horisontaalselt. See tähendab, et veerud muutuvad ridadeks. Meie puhul on toimingute jada järgmine:

1. Valige andmevahemik, mida peame pöörama. Pärast seda kopeerime need andmed.
2. Asetage kursor lehel ükskõik kuhu. Seejärel klõpsake hiire paremat nuppu ja avage kontekstimenüü.
3. Seejärel klõpsake nuppu "Kleebi spetsiaalselt".

Pärast nende toimingute sooritamist peate klõpsama nuppu "Transponeeri". Pigem märkige ruut selle üksuse kõrval. Me ei muuda muid sätteid ja seejärel klõpsake nuppu "OK".

Pärast nende toimingute sooritamist jääb meile sama tabel, ainult selle read ja veerud on paigutatud erinevalt. Pange tähele, et sama teavet sisaldavad lahtrid on roheliselt esile tõstetud. Küsimus: mis juhtus valemitega, mis olid algses vahemikus? Nende asukoht on muutunud, aga nemad ise on jäänud. Lahtrite aadressid muutusid lihtsalt nendeks, mis moodustati pärast transponeerimist.

Väärtuste, mitte valemite ülekandmiseks tuleb teha peaaegu samu toiminguid. Sel juhul tuleb kasutada ka menüüd Paste Special, kuid enne seda tuleb valida väärtusi sisaldav andmevahemik. Näeme, et akent Paste Special saab kutsuda kahel viisil: lindi erimenüü või kontekstimenüü kaudu.

2. meetod. TRANSP funktsioon Excelis

Tegelikult ei kasutata seda meetodit enam nii aktiivselt kui selle arvutustabeliprogrammi ilmumise alguses. Seda seetõttu, et see meetod on palju keerulisem kui Paste Special kasutamine. Siiski leiab seda kasutust tabelite ülevõtmise automatiseerimisel.

Lisaks on see funktsioon Excelis, seega on selle kohta kindlasti vaja teada, kuigi seda enam peaaegu ei kasutata. Varem kaalusime protseduuri, kuidas sellega töötada. Nüüd täiendame neid teadmisi täiendava näitega.

1. Esiteks peame valima andmevahemiku, mida tabeli ülekandmiseks kasutatakse. Peate lihtsalt valima ala, vastupidi. Näiteks selles näites on meil 4 veergu ja 6 rida. Seetõttu on vaja valida vastandlike omadustega ala: 6 veergu ja 4 rida. Pilt näitab seda väga hästi.
2. Pärast seda hakkame kohe seda lahtrit täitma. Oluline on mitte kogemata valikut eemaldada. Seetõttu peate määrama valemi otse valemiribal.
3. Järgmisena vajutage klahvikombinatsiooni Ctrl + Shift + Enter. Pidage meeles, et see on massiivivalem, kuna töötame korraga suure andmehulgaga, mis kantakse üle teise suurde lahtrikomplekti.

Pärast andmete sisestamist vajutame sisestusklahvi, mille järel saame järgmise tulemuse.

Näeme, et valemit ei viidud uude tabelisse. Ka vormindamine läks kaduma. Poeto

Kõik see tuleb teha käsitsi. Lisaks pidage meeles, et see tabel on seotud algse tabeliga. Seega, niipea kui mõnda teavet algses vahemikus muudetakse, tehakse need kohandused automaatselt ülekantud tabelisse.

Seetõttu sobib see meetod hästi juhtudel, kui peate tagama, et ülekantud tabel on lingitud algse tabeliga. Kui kasutate spetsiaalset vahetükki, siis seda võimalust enam ei ole.

kokkuvõtlik tabel

See on põhimõtteliselt uus meetod, mis võimaldab mitte ainult tabelit üle võtta, vaid ka teha tohutul hulgal toiminguid. Tõsi, ülevõtmise mehhanism on eelmiste meetoditega võrreldes mõnevõrra erinev. Toimingute jada on järgmine:

1. Teeme pöördetabeli. Selleks peate valima tabeli, mille peame üle võtma. Pärast seda minge üksusele "Sisesta" ja otsige sealt üles "Pivot Table". Ilmub dialoogiboks, nagu sellel ekraanipildil.
2. Siin saate määrata vahemiku, millest see tehakse, ja teha mitmeid muid sätteid. Meid huvitab nüüd eelkõige pöördetabeli koht – uuel lehel.
3. Pärast seda luuakse automaatselt pivot-tabeli paigutus. Sellesse on vaja märkida need esemed, mida kasutame, ja seejärel tuleb need õigesse kohta teisaldada. Meie puhul peame teisaldama üksuse „Toode” jaotisesse „Veerude nimed” ja „Tüki hind” jaotisesse „Väärtused”.
4. Pärast seda luuakse lõpuks liigendtabel. Lisaboonuseks on lõppväärtuse automaatne arvutamine.
5. Saate muuta ka muid seadeid. Näiteks tühjendage märkeruut "Tüki hind" ja märkige üksus "Kogu maksumus". Selle tulemusena saame tabeli, mis sisaldab teavet toodete maksumuse kohta. See ülevõtmise meetod on palju funktsionaalsem kui teised. Kirjeldame mõningaid pivot-tabelite eeliseid.

1. Automatiseerimine. Pivot-tabelite abil saate andmeid automaatselt kokku võtta, samuti veergude ja veergude asukohta suvaliselt muuta. Selleks ei pea te lisatoiminguid tegema.
2. Interaktiivsus. Kasutaja saab teabe struktuuri muuta nii mitu korda, kui ta oma ülesannete täitmiseks vajab. Näiteks saate muuta veergude järjekorda ja andmeid rühmitada suvalisel viisil. Seda saab teha nii mitu korda, kui kasutaja vajab. Ja see võtab sõna otseses mõttes vähem kui minuti.
3. Lihtne vormindada andmeid. Pivot tabelit on väga lihtne korraldada nii, nagu inimene soovib. Selleks tehke paar hiireklõpsu.
4. Väärtuste saamine. Valdav hulk valemeid, mida aruannete koostamiseks kasutatakse, asuvad inimese otseses juurdepääsetavuses ja neid on lihtne liigendtabelisse integreerida. Need on sellised andmed nagu liitmine, aritmeetilise keskmise saamine, lahtrite arvu määramine, korrutamine, määratud valimi suurima ja väikseima väärtuse leidmine.
5. Võimalus koostada kokkuvõtvaid diagramme. Kui PivotTable-liigendtabelid arvutatakse ümber, värskendatakse nendega seotud diagramme automaatselt. Diagramme on võimalik koostada nii palju kui vaja. Neid kõiki saab konkreetse ülesande jaoks muuta ja need ei ole omavahel seotud.
6. Võimalus andmeid filtreerida.
7. Pivot-tabelit on võimalik koostada rohkem kui ühe lähteteabe komplekti põhjal. Seetõttu muutub nende funktsionaalsus veelgi suuremaks.

Tõsi, pivot-tabelite kasutamisel tuleb arvestada järgmiste piirangutega:

1. Pivot-tabelite loomiseks ei saa kogu teavet kasutada. Enne nende kasutamist selleks otstarbeks tuleb rakud normaliseerida. Lihtsamalt öeldes, tehke seda õigesti. Kohustuslikud nõuded: päiserea olemasolu, kõikide ridade täius, andmevormingute võrdsus.
2. Andmeid uuendatakse poolautomaatselt. Pivot-tabelisse uue teabe saamiseks peate klõpsama spetsiaalset nuppu.
3. Pivot-tabelid võtavad palju ruumi. See võib põhjustada arvuti töös mõningaid häireid. Samuti on selle tõttu raske faili e-postiga saata.

Samuti ei ole kasutajal pärast pivot-tabeli loomist võimalust uut teavet lisada.