Ntchito ya Transpose mu Excel

Nthawi ndi nthawi, wogwiritsa ntchito Excel atha kukhala ndi ntchito yosinthira deta yomwe ili ndi mawonekedwe opingasa kukhala ofukula. Njira imeneyi imatchedwa transposition. Mawuwa ndi atsopano kwa anthu ambiri, chifukwa mu ntchito yamba ya PC simuyenera kugwiritsa ntchito ntchitoyi. Komabe, omwe amayenera kugwira ntchito ndi data yochuluka ayenera kudziwa momwe angachitire. Lero tikambirana mwatsatanetsatane momwe tingachitire, ndi ntchito yotani, komanso kuyang'ana njira zina mwatsatanetsatane.

Ntchito ya TRANSPOSE - ma transpose cell mu Excel

Imodzi mwa njira zosangalatsa komanso zogwira ntchito zosinthira tebulo mu Excel ndi ntchito Mtengo wa TRANSP. Ndi chithandizo chake, mutha kusintha magawo opingasa a data kukhala ofukula, kapena kuyambiranso. Tiyeni tiwone momwe tingagwirire nayo ntchito.

Ntchito syntax

Kalembedwe ka ntchitoyi ndi yosavuta kwambiri: TRANSPOSE (osiyanasiyana). Ndiko kuti, tiyenera kugwiritsa ntchito mkangano umodzi wokha, womwe ndi seti ya data yomwe imayenera kusinthidwa kukhala yopingasa kapena yowongoka, malingana ndi zomwe zinali poyamba.

Kusintha magawo oyimirira a ma cell (mizere)

Tiyerekeze kuti tili ndi mzere wokhala ndi B2:B6. Atha kukhala ndi zonse zomwe zidapangidwa kale komanso ma fomula omwe amabwezera zotsatira kumaselo awa. Sikofunikira kwambiri kwa ife, kusinthika kumatheka muzochitika zonsezi. Mukamaliza kugwiritsa ntchito izi, kutalika kwa mizere kudzakhala kofanana ndi kutalika kwa mzere woyamba.

Kutsatira njira zogwiritsira ntchito fomulayi ndi motere:

1. Sankhani mzere. Kwa ife, ili ndi kutalika kwa maselo asanu.
2. Pambuyo pake, sunthani cholozera ku kapamwamba kapamwamba, ndikulowetsani ndondomeko pamenepo =TRANSP(B2:B6).
3. Dinani kuphatikiza kiyi Ctrl + Shift + Lowani.

Mwachilengedwe, kwa inu, muyenera kufotokoza mtundu womwe uli patebulo lanu.

Kutembenuza ma cell opingasa (mizere)

Kwenikweni, limagwirira ntchito ndi pafupifupi ofanana ndime yapita. Tiyerekeze kuti tili ndi chingwe chokhala ndi zoyambira ndi zomaliza B10:F10. Itha kukhalanso ndi zonse zomwe zimafunikira komanso ma formula. Tiyeni tipange mzati kuchokera pamenepo, womwe udzakhala ndi miyeso yofanana ndi mzere woyambirira. Kutsatira kwa zochita ndi motere:

1. Sankhani ndime iyi ndi mbewa. Mukhozanso kugwiritsa ntchito makiyi pa kiyibodi Ctrl ndi muvi pansi, mutatha kuwonekera pa selo pamwamba kwambiri pa ndime iyi.
2. Pambuyo pake timalemba chilinganizo =TRANSP(B10:F10) mu bar formula.
3. Timalemba ngati chilinganizo chotsatira pogwiritsa ntchito kiyi Ctrl + Shift + Enter.

Transposing ndi Paste Special

Njira ina yosinthira ndiyo kugwiritsa ntchito Paste Special ntchito. Uyu salinso wogwiritsira ntchito kuti agwiritsidwe ntchito m'ma formula, komanso ndi imodzi mwa njira zodziwika bwino zosinthira mizere kukhala mizere ndi mosemphanitsa.

Njira iyi ili pa tsamba loyambira. Kuti mupeze izo, muyenera kupeza gulu la "Clipboard", ndipo pezani batani "Matani". Pambuyo pake, tsegulani menyu yomwe ili pansi pa njirayi ndikusankha chinthucho "Transpose". Izi zisanachitike, muyenera kusankha mtundu womwe mukufuna kusankha. Zotsatira zake, tidzakhala ndi mtundu womwewo, mosiyana ndi mawonekedwe.

Njira za 3 Zosinthira Tebulo mu Excel

Koma kwenikweni, pali njira zambiri zosinthira mizere kukhala mizere ndi mosemphanitsa. Tiyeni tifotokoze njira zitatu zomwe titha kusinthira tebulo mu Excel. Takambirana ziwiri pamwambapa, koma tipereka zitsanzo zingapo kuti mukhale ndi lingaliro labwino la mau3buXNUMX momwe mungachitire izi.

Njira 1: Matani Mwapadera

Njira imeneyi ndi yosavuta. Ndikokwanira kukanikiza mabatani angapo, ndipo wogwiritsa ntchito amalandira mawonekedwe a tebulo. Tiyeni tipereke chitsanzo chaching'ono kuti timveke bwino. Tiyerekeze kuti tili ndi tebulo lomwe lili ndi zambiri za kuchuluka kwa zinthu zomwe zili mgululi, komanso kuchuluka kwa ndalama zonse. Gome lokha likuwoneka chonchi.

Tikuwona kuti tili ndi mutu ndi mzere wokhala ndi manambala azinthu. M'chitsanzo chathu, mutu uli ndi chidziwitso cha mankhwala, kuchuluka kwa ndalama, kuchuluka kwa ndalama zomwe zilipo, ndi mtengo wanji wazinthu zonse zokhudzana ndi chinthu ichi chomwe chilipo. Timapeza mtengo molingana ndi ndondomeko yomwe mtengo umachulukitsidwa ndi kuchuluka kwake. Kuti chitsanzocho chiwonekere, tiyeni tipange mutu wobiriwira.

Ntchito yathu ndikuwonetsetsa kuti zomwe zili patebulozo zapezeka mopingasa. Ndiko kuti, kuti mizati ikhale mizere. Zotsatira za zochita mwathu zidzakhala motere:

1. Sankhani kuchuluka kwa data yomwe tikufuna kuti tisinthe. Pambuyo pake, timakopera deta iyi.
2. Ikani cholozera paliponse pa pepala. Kenako dinani batani lakumanja la mbewa ndikutsegula menyu yankhaniyo.
3. Kenako dinani batani la "Paste Special".

Mukamaliza masitepe awa, muyenera alemba pa "Transpose" batani. M'malo mwake, chongani bokosi pafupi ndi chinthuchi. Sitisintha zoikamo zina, ndiyeno dinani "Chabwino" batani.

Pambuyo pochita masitepewa, timasiyidwa ndi tebulo lomwelo, mizere yake yokha ndi mizati imakonzedwa mosiyana. Dziwaninso kuti ma cell omwe ali ndi chidziwitso chomwecho amawonetsedwa mobiriwira. Funso: chinachitika ndi chiyani ku ma formula omwe anali m'gulu loyambirira? Malo awo asintha, koma iwo okha akhalabe. Maadiresi a maselo anangosintha kukhala omwe anapangidwa pambuyo pa kusintha.

Pafupifupi zochita zomwezo ziyenera kuchitidwa kuti asinthe zinthu, osati ma formula. Pankhaniyi, muyeneranso kugwiritsa ntchito Matanizani Special menyu, koma zisanachitike, sankhani kuchuluka kwa data yomwe ili ndi zikhalidwe. Tikuwona kuti Paste Special zenera zitha kuyitanidwa m'njira ziwiri: kudzera pamenyu yapadera pa riboni kapena menyu yankhani.

Njira 2. Ntchito ya TRANSP mu Excel

M'malo mwake, njirayi sikugwiritsidwanso ntchito mwachangu monga momwe zinalili pachiyambi pomwe pulogalamu ya spreadsheet idawonekera. Izi ndichifukwa choti njirayi ndi yovuta kwambiri kuposa kugwiritsa ntchito Paste Special. Komabe, imapeza kugwiritsidwa ntchito kwake pakusintha kwa tebulo.

Komanso, ntchitoyi ili mu Excel, kotero ndikofunikira kudziwa za izo, ngakhale sizigwiritsidwanso ntchito pafupifupi. M'mbuyomu tidakambirana njira, momwe tingagwiritsire ntchito. Tsopano tidzawonjezera chidziwitso ichi ndi chitsanzo chowonjezera.

1. Choyamba, tiyenera kusankha mtundu wa data womwe udzagwiritsidwe ntchito posinthira tebulo. Mukungoyenera kusankha malo m'malo mwake. Mwachitsanzo, mu chitsanzo ichi tili ndi mizati 4 ndi mizere 6. Chifukwa chake, ndikofunikira kusankha malo omwe ali ndi mawonekedwe osiyana: mizati 6 ndi mizere 4. Chithunzicho chikuwonetsa bwino kwambiri.
2. Pambuyo pake, nthawi yomweyo timayamba kudzaza selo ili. Ndikofunika kuti musachotse mwangozi zosankhazo. Chifukwa chake, muyenera kufotokozera fomuyo mwachindunji mu bar ya formula.
3. Kenako, dinani makiyi kuphatikiza Ctrl + Shift + Lowani. Kumbukirani kuti iyi ndi njira yotsatizana, popeza tikugwira ntchito ndi deta yayikulu nthawi imodzi, yomwe idzasamutsidwira kumagulu ena akuluakulu.

Tikalowetsa deta, timakanikiza Enter key, pambuyo pake timapeza zotsatira zotsatirazi.

Tikuwona kuti chilinganizocho sichinasamutsidwe ku tebulo latsopano. Mapangidwe adatayikanso. Poeto

Zonsezi ziyenera kuchitidwa pamanja. Kuphatikiza apo, kumbukirani kuti tebulo ili likugwirizana ndi loyambirira. Chifukwa chake, chidziwitso china chikangosinthidwa mumtundu woyambirira, zosinthazi zimangopangidwa patebulo losinthidwa.

Choncho, njirayi ndi yoyenera pazochitika zomwe muyenera kuonetsetsa kuti tebulo losinthidwa likugwirizana ndi loyambirira. Ngati mugwiritsa ntchito choyikapo chapadera, kuthekera uku sikudzakhalanso.

mwachidule tebulo

Iyi ndi njira yatsopano, yomwe imapangitsa kuti zitheke kusuntha tebulo, komanso kuchita zinthu zambiri. Zowona, makina osinthira adzakhala osiyana pang'ono poyerekeza ndi njira zam'mbuyomu. Kutsatira kwa zochita ndi motere:

1. Tiyeni tipange pivot table. Kuti muchite izi, muyenera kusankha tebulo lomwe tikufunika kusinthira. Pambuyo pake, pitani ku chinthu cha "Insert" ndikuyang'ana "Pivot Table" pamenepo. Bokosi la zokambirana ngati lomwe lili pachithunzichi lidzawonekera.
2. Apa mutha kugawanso mtundu womwe udzapangidwe, komanso kupanga zosintha zina zingapo. Tsopano tili ndi chidwi kwambiri ndi malo a tebulo la pivot - pa pepala latsopano.
3. Pambuyo pake, masanjidwe a tebulo la pivot adzapangidwa okha. Ndikofunikira kuyika chizindikiro m'menemo zinthu zomwe tidzagwiritse ntchito, ndiyeno ziyenera kusamutsidwa kupita kumalo oyenera. Kwa ife, tiyenera kusuntha chinthu cha "Product" ku "Column Names", ndi "Price Per Piece" ku "Values".
4. Pambuyo pake, tebulo la pivot lidzapangidwa pomaliza. Bhonasi yowonjezera ndiyo kuwerengera komaliza kwa mtengo womaliza.
5. Mutha kusinthanso makonda ena. Mwachitsanzo, sankhani chinthucho "Mtengo pachidutswa chilichonse" ndikuwona chinthucho "Ndalama zonse". Zotsatira zake, tidzakhala ndi tebulo lomwe lili ndi chidziwitso cha kuchuluka kwa zinthu zomwe timagula. Njira yosinthira iyi ndiyothandiza kwambiri kuposa ina. Tiyeni tifotokoze zina mwazabwino za ma pivot tables:

1. Zochita zokha. Mothandizidwa ndi ma pivot tables, mutha kufotokoza mwachidule deta yokha, komanso kusintha malo a mizati ndi mizati mosasamala. Kuti muchite izi, simuyenera kuchita zina zowonjezera.
2. Kuyanjana. Wogwiritsa ntchitoyo amatha kusintha kapangidwe ka chidziwitso nthawi zambiri momwe angafunikire kuti agwire ntchito zake. Mwachitsanzo, mutha kusintha dongosolo la mizati, komanso deta yamagulu mosasamala. Izi zitha kuchitika nthawi zambiri momwe wogwiritsa ntchito amafunikira. Ndipo kwenikweni zimatenga zosakwana miniti.
3. Zosavuta kupanga deta. Ndikosavuta kukonza tebulo la pivot momwe munthu akufunira. Kuti muchite izi, ingodinani pang'ono mbewa.
4. Kupeza zofunika. Chiwerengero chochulukira cha mafomu omwe amagwiritsidwa ntchito popanga malipoti amapezeka momwe munthu angafikire mwachindunji ndipo ndi osavuta kuphatikiza mu tebulo la pivot. Izi ndi data monga kumasulira, kupeza tanthauzo la masamu, kudziwa kuchuluka kwa maselo, kuchulukitsa, kupeza zikhalidwe zazikulu komanso zazing'ono pazomwe zatchulidwa.
5. Kutha kupanga ma chart achidule. Ngati ma PivotTables awerengedwanso, ma chart awo ogwirizana nawo amasinthidwa zokha. Ndizotheka kupanga ma chart ambiri momwe mungafunire. Onsewa akhoza kusinthidwa kuti agwire ntchito inayake ndipo sangagwirizane.
6. Kutha kusefa deta.
7. Ndizotheka kupanga tebulo la pivot kutengera zambiri zamtundu umodzi. Chifukwa chake, magwiridwe antchito awo adzakhala okulirapo.

Zowona, mukamagwiritsa ntchito ma pivot tables, zoletsa zotsatirazi ziyenera kuganiziridwa:

1. Sizidziwitso zonse zomwe zingagwiritsidwe ntchito kupanga ma pivot table. Asanayambe kugwiritsidwa ntchito pachifukwa ichi, maselo ayenera kukhala abwinobwino. M’mawu osavuta, konzekerani. Zofunikira zovomerezeka: kukhalapo kwa mzere wamutu, kudzaza kwa mizere yonse, kufanana kwa mawonekedwe a data.
2. Zambiri zimasinthidwa semi-automatic. Kuti mudziwe zatsopano pa tebulo la pivot, muyenera dinani batani lapadera.
3. Ma pivot tables amatenga malo ambiri. Izi zitha kuyambitsa kusokoneza kwina kwa kompyuta. Komanso, fayiloyo idzakhala yovuta kutumiza ndi E-mail chifukwa cha izi.

Komanso, mutatha kupanga tebulo la pivot, wogwiritsa ntchito alibe mphamvu zowonjezera zatsopano.