Umsebenzi we-Transpose ku-Excel

Ngokuhamba kwesikhathi, umsebenzisi we-Excel angase abe nomsebenzi wokuguqula uhla lwedatha enokwakheka okuvundlile lube oluqondile. Le nqubo ibizwa ngokuthi ukuguqulwa. Leli gama lisha kubantu abaningi, ngoba emsebenzini ojwayelekile we-PC akudingeki ukuthi usebenzise lo msebenzi. Kodwa-ke, labo okufanele basebenze ngenani elikhulu ledatha badinga ukwazi ukuthi kwenziwa kanjani. Namuhla sizokhuluma ngokuningiliziwe mayelana nendlela yokwenza, ngamuphi umsebenzi, futhi sibheke ezinye izindlela ngokuningiliziwe.

Umsebenzi we-TRANSPOSE – transpose range ranges ku-Excel

Enye yezindlela ezithakazelisa kakhulu nezisebenzayo zokuguqula ithebula ku-Excel wumsebenzi I-TRANSP. Ngosizo lwayo, ungakwazi ukushintsha ibanga ledatha elivundlile libe eliqondile, noma wenze umsebenzi wokuhlehla. Ake sibone ukuthi singasebenza kanjani nayo.

I-syntax yomsebenzi

I-syntax yalo msebenzi ilula ngendlela emangalisayo: TRANSPOSE(uhlu). Okusho ukuthi, sidinga ukusebenzisa i-agumenti eyodwa kuphela, okuyisethi yedatha edinga ukuguqulelwa ekubukeni okuvundlile noma okuqondile, kuye ngokuthi bekuyini ekuqaleni.

Ukudlulisa ububanzi obume mpo bamaseli (amakholomu)

Ake sithi sinekholomu enobubanzi B2:B6. Angaqukatha kokubili amanani enziwe kakade namafomula abuyisela imiphumela kulawa maseli. Akubalulekile kangako kithi, ukuguqulwa kungenzeka kuzo zombili izimo. Ngemva kokusebenzisa lo msebenzi, ubude bomugqa buzofana nobude bekholomu yobubanzi bangempela.

Ukulandelana kwezinyathelo zokusebenzisa le fomula imi kanje:

1. Khetha umugqa. Esimeni sethu, inobude bamaseli amahlanu.
2. Ngemva kwalokho, hambisa ikhesa kubha yefomula, bese ufaka ifomula lapho =TRANSP(B2:B6).
3. Cindezela inhlanganisela yokhiye Ctrl + Shift + Enter.

Ngokwemvelo, esimweni sakho, udinga ukucacisa ububanzi obujwayelekile etafuleni lakho.

Ukudlulisa ububanzi bamaseli avundlile (imigqa)

Empeleni, indlela yokwenza icishe ifane nesigaba sangaphambilini. Ake sithi sineyunithi yezinhlamvu enezixhumanisi zokuqala nesiphetho B10:F10. Ingaqukatha kokubili amanani aqondile namafomula. Masenze ikholomu ngayo, ezoba nobukhulu obufanayo nomugqa wokuqala. Ukulandelana kwezenzo kanje:

1. Khetha le kholomu ngegundane. Ungasebenzisa futhi okhiye kukhibhodi u-Ctrl kanye nomcibisholo oya phansi, ngemva kokuchofoza iseli eliphezulu kakhulu lale kholomu.
2. Ngemva kwalokho sibhala ifomula =TRANSP(B10:F10) kubha yefomula.
3. Sibhala njengefomula yamalungu sisebenzisa inhlanganisela yokhiye Ctrl + Shift + Enter.

Ukudlulisa nge-Namathisela Okukhethekile

Enye inketho yokuguqula engaba khona ukusebenzisa umsebenzi othi Namathisela Okukhethekile. Lokhu akuseyena u-opharetha okufanele asetshenziswe kumafomula, kodwa futhi kungenye yezindlela ezidumile zokuphendula amakholomu abe imigqa futhi okuphambene nalokho.

Le nketho ikuthebhu ethi Ikhaya. Ukuze uyifinyelele, udinga ukuthola iqembu elithi "Ibhodi lokunamathisela", bese uthola inkinobho ethi "Namathisela". Ngemuva kwalokho, vula imenyu etholakala ngaphansi kwalolu khetho bese ukhetha into ethi "Transpose". Ngaphambi kwalokho, udinga ukukhetha ibanga ofuna ukulikhetha. Ngenxa yalokho, sizothola ububanzi obufanayo, okuphambene kuphela okuboniswayo.

Izindlela ezi-3 Zokuguqula Ithebula ku-Excel

Kodwa empeleni, kunezindlela eziningi zokuguqula amakholomu abe imigqa futhi ngokuphambene nalokho. Ake sichaze izindlela ezi-3 esingaguqula ngazo itafula ku-Excel. Sixoxile ngezimbili zazo ngenhla, kodwa sizokunikeza ezinye izibonelo ezimbalwa ukuze uthole umbono ongcono wendlela yokuqhuba le nqubo.

Indlela 1: Namathisela Okukhethekile

Le ndlela ilula kakhulu. Kwanele ukucindezela izinkinobho ezimbalwa, futhi umsebenzisi uthola inguqulo yetafula. Ake sinikeze isibonelo esincane ukuze kucace kakhudlwana. Ake sithi sinethebula eliqukethe ulwazi mayelana nokuthi mingaki imikhiqizo esesitokweni njengamanje, kanye nokuthi ibiza malini isiyonke. Itafula ngokwalo libukeka kanje.

Siyabona ukuthi sinesihloko kanye nekholomu enezinombolo zomkhiqizo. Esibonelweni sethu, unhlokweni uqukethe ulwazi mayelana nokuthi yimuphi umkhiqizo, ukuthi ubiza malini, ungakanani esitokweni, futhi yiziphi izindleko eziphelele zayo yonke imikhiqizo ehlobene nale nto esesitokweni. Sithola izindleko ngokuya ngefomula lapho izindleko ziphindaphindwa khona ngobuningi. Ukwenza isibonelo sibonakale kakhudlwana, masenze unhlokweni ube luhlaza.

Umsebenzi wethu uwukuqinisekisa ukuthi ulwazi oluqukethwe etafuleni lubekwe ngokuvundlile. Okusho ukuthi, ukuze amakholomu abe imigqa. Ukulandelana kwezenzo esimweni sethu kuzoba kanje:

1. Khetha ububanzi bedatha esiyidingayo ukuze sizungezise. Ngemva kwalokho, sikopisha le datha.
2. Beka ikhesa noma yikuphi eshidini. Bese uchofoza inkinobho yegundane kwesokudla bese uvula imenyu yokuqukethwe.
3. Bese uchofoza inkinobho ethi "Namathisela Okukhethekile".

Ngemva kokuqeda lezi zinyathelo, udinga uchofoze inkinobho "Transpose". Kunalokho, qoka ibhokisi eliseduze kwale nto. Asizishintshi ezinye izilungiselelo, bese uchofoza inkinobho ethi "KULUNGILE".

Ngemva kokwenza lezi zinyathelo, sisele netafula elifanayo, imigqa namakholomu kuphela ahlelwe ngendlela ehlukile. Futhi qaphela ukuthi amaseli aqukethe ulwazi olufanayo agqanyiswe ngokuluhlaza. Umbuzo: kwenzekeni kumafomula abekububanzi bokuqala? Indawo yabo ishintshile, kodwa bona ngokwabo basamile. Amakheli amaseli avele ashintsha abe yilawo akhiwe ngemva kokuguqulwa.

Cishe izenzo ezifanayo zidinga ukwenziwa ukuze uguqule amanani, hhayi amafomula. Kulokhu, udinga futhi ukusebenzisa imenyu ethi Namathisela Okukhethekile, kodwa ngaphambi kwalokho, khetha ububanzi bedatha equkethe amanani. Siyabona ukuthi iwindi elithi Namathisela Okukhethekile lingabizwa ngezindlela ezimbili: ngemenyu ekhethekile kuribhoni noma imenyu yokuqukethwe.

Indlela 2. Umsebenzi we-TRANSP ku-Excel

Eqinisweni, le ndlela ayisasetshenziswa njengoba yayinjalo ekuqaleni kokuvela kwalolu hlelo lwesipredishithi. Lokhu kungenxa yokuthi le ndlela iyinkimbinkimbi kakhulu kunokusebenzisa Namathisela Okukhethekile. Kodwa-ke, ithola ukusetshenziswa kwayo ekushintsheni kwetafula okuzenzakalelayo.

Futhi, lo msebenzi uku-Excel, ngakho-ke kubalulekile ukwazi ngawo, nakuba ungasasetshenziswa cishe. Ngaphambilini sicabangele inqubo, indlela yokusebenza nayo. Manje sizokwengeza lolu lwazi ngesibonelo esengeziwe.

1. Okokuqala, sidinga ukukhetha ububanzi bedatha obuzosetshenziselwa ukuguqula ithebula. Udinga nje ukukhetha indawo ngokuphambene nalokho. Isibonelo, kulesi sibonelo sinekholomu emi-4 nemigqa engu-6. Ngakho-ke, kuyadingeka ukukhetha indawo enezici eziphambene: amakholomu angu-6 nemigqa emi-4. Isithombe sibonisa kahle kakhulu.
2. Ngemva kwalokho, siqala ngokushesha ukugcwalisa leli seli. Kubalulekile ukuthi ungasusi ngephutha ukukhethwa. Ngakho-ke, kufanele ucacise ifomula ngokuqondile kubha yefomula.
3. Okulandelayo, cindezela inhlanganisela yokhiye Ctrl + Shift + Enter. Khumbula ukuthi lena ifomula yamalungu afanayo, njengoba sisebenza nesethi enkulu yedatha ngesikhathi esisodwa, ezodluliselwa kwelinye iqoqo elikhulu lamaseli.

Ngemva kokufaka idatha, sicindezela ukhiye we-Enter, ngemva kwalokho sithola umphumela olandelayo.

Siyabona ukuthi ifomula ayizange idluliselwe etafuleni elisha. Ukufometha nakho kulahlekile. Poeto

Konke lokhu kuzodingeka kwenziwe ngesandla. Ngaphezu kwalokho, khumbula ukuthi leli thebula lihlobene nelokuqala. Ngakho-ke, ngokushesha nje lapho olunye ulwazi lushintshwa ebangeni lokuqala, lokhu kulungiswa kwenziwa ngokuzenzakalelayo kuthebula eliguquliwe.

Ngakho-ke, le ndlela ifaneleka kahle ezimweni lapho udinga ukuqinisekisa ukuthi ithebula eliguquliwe lixhunywe nelokuqala. Uma usebenzisa okokufaka okukhethekile, lokhu kungenzeka ngeke kusaba khona.

isifinyezo ithebula

Lena indlela entsha ngokuyisisekelo, okwenza kube nokwenzeka hhayi kuphela ukudlulisa itafula, kodwa futhi ukwenza inani elikhulu lezenzo. Yiqiniso, indlela yokuguqula izohluka kancane uma iqhathaniswa nezindlela zangaphambilini. Ukulandelana kwezenzo kanje:

1. Masenze itafula le-pivot. Ukuze wenze lokhu, udinga ukukhetha ithebula okudingeka silidlulise. Ngemva kwalokho, iya entweni ethi “Faka” bese ubheka “Ithebula Le-Pivot” lapho. Ibhokisi lengxoxo elifana naleli elikulesi sithombe-skrini lizovela.
2. Lapha ungakwazi ukunikeza kabusha ububanzi okuzokwenziwa kusuka kubo, futhi wenze nenani lezinye izilungiselelo. Manje sinentshisekelo ngokuyinhloko endaweni yetafula le-pivot - eshidini elisha.
3. Ngemva kwalokho, ukwakheka kwethebula le-pivot kuzokwenziwa ngokuzenzakalelayo. Kudingeka ukumaka kuyo lezo zinto esizozisebenzisa, bese kufanele zihanjiswe endaweni efanele. Esimweni sethu, sidinga ukuhambisa into "Yomkhiqizo" kokuthi "Amagama Ekholomu", kanye nokuthi "Intengo Ngocezu ngalunye" iye kokuthi "Amanani".
4. Ngemva kwalokho, ithebula le-pivot lizogcina lidaliwe. Ibhonasi eyengeziwe iwukubalwa okuzenzakalelayo kwenani lokugcina.
5. Ungashintsha nezinye izilungiselelo. Isibonelo, susa ukumaka into ethi “Intengo ngocezu ngalunye” bese uhlola into ethi “Isamba sezindleko”. Ngenxa yalokho, sizoba nethebula eliqukethe ulwazi mayelana nokuthi imikhiqizo ibiza malini. Le ndlela yokuguqula isebenza kakhulu kunezinye. Ake sichaze ezinye zezinzuzo zamathebula e-pivot:

1. Okuzenzakalelayo. Ngosizo lwamathebula e-pivot, ungakwazi ukufingqa idatha ngokuzenzakalela, futhi uguqule indawo yamakholomu namakholomu ngokuzenzakalelayo. Ukuze wenze lokhu, awudingi ukwenza noma yiziphi izinyathelo ezengeziwe.
2. Ukusebenzisana. Umsebenzisi angashintsha ukwakheka kolwazi izikhathi eziningi njengoba edinga ukwenza imisebenzi yakhe. Isibonelo, ungashintsha ukuhleleka kwamakholomu, kanye nedatha yeqembu ngendlela engafanele. Lokhu kungenziwa izikhathi eziningi njengoba kudingeka umsebenzisi. Futhi ngokoqobo kuthatha ngaphansi komzuzu.
3. Kulula ukufometha idatha. Kulula kakhulu ukuhlela itafula le-pivot ngendlela umuntu afuna ngayo. Ukuze wenze lokhu, vele wenze ukuchofoza kwegundane okumbalwa.
4. Ukuthola amanani. Inani elikhulu kakhulu lamafomula asetshenziselwa ukudala imibiko atholakala ekufinyelelekeni okuqondile komuntu futhi kulula ukuwahlanganisa kuthebula le-pivot. Lawa idatha efana nokuhlanganisa, ukuthola incazelo ye-arithmetic, enquma inani lamaseli, aphindaphindeka, athole amanani amakhulu kakhulu namancane kusampula eshiwo.
5. Ikhono lokudala amashadi esifinyezo. Uma ama-PivotTables abalwa kabusha, amashadi awo ahlobene abuyekezwa ngokuzenzakalelayo. Kungenzeka ukudala amashadi amaningi njengoba uwadinga. Zonke zingashintshwa ukuze kwenziwe umsebenzi othile futhi ngeke zixhunywe.
6. Ikhono lokuhlunga idatha.
7. Kungenzeka ukwakha ithebula le-pivot elisuselwe kusethi engaphezu kweyodwa yolwazi lomthombo. Ngakho-ke, ukusebenza kwabo kuzoba kukhulu kakhulu.

Yiqiniso, lapho usebenzisa amathebula e-pivot, imikhawulo elandelayo kufanele inakwe:

1. Akulona lonke ulwazi olungasetshenziswa ukwenza amathebula e-pivot. Ngaphambi kokuthi zisetshenziselwe le njongo, amaseli kufanele ajwayeleke. Ngamagama alula, thola kahle. Izidingo eziyisibopho: ukuba khona komugqa wesihloko, ukugcwala kwayo yonke imigqa, ukulingana kwamafomethi wedatha.
2. Idatha ibuyekezwa kancane kancane. Ukuze uthole ulwazi olusha kuthebula le-pivot, kufanele uchofoze inkinobho ekhethekile.
3. Amathebula e-Pivot athatha isikhala esiningi. Lokhu kungase kubangele ukuphazamiseka okuthile kwekhompyutha. Futhi, ifayela kuzoba nzima ukuthumela nge-E-mail ngenxa yalokhu.

Futhi, ngemva kokudala ithebula le-pivot, umsebenzisi akanalo ikhono lokwengeza ulwazi olusha.