Transpose Aiki a cikin Excel

Daga lokaci zuwa lokaci, mai amfani da Excel yana iya samun aikin juya kewayon bayanan da ke da tsarin kwance zuwa na tsaye. Ana kiran wannan tsari transposition. Wannan kalma sabuwa ce ga yawancin mutane, domin a cikin aikin PC na yau da kullun ba sai ka yi amfani da wannan aikin ba. Duk da haka, waɗanda dole ne suyi aiki tare da bayanai masu yawa suna buƙatar sanin yadda ake yin shi. A yau za mu yi magana dalla-dalla game da yadda ake yin shi, da wane aiki, da kuma duba wasu hanyoyin dalla-dalla.

Ayyukan TRANSPOSE - mai sarrafa jeri na sel a cikin Excel

Daya daga cikin mafi ban sha'awa da kuma aiki hanyoyin jujjuya tebur a cikin Excel shine aikin TRANSP. Tare da taimakonsa, zaku iya juya kewayon bayanan da ke kwance zuwa tsaye, ko aiwatar da aikin baya. Bari mu ga yadda za a yi aiki da shi.

Aiki syntax

Ma'anar wannan aikin yana da sauƙi mai ban mamaki: TRANSPOSE (tsari). Wato muna bukatar mu yi amfani da hujja guda ɗaya kawai, wanda shine saitin bayanai wanda ke buƙatar canza shi zuwa hangen nesa ko a tsaye, gwargwadon abin da yake a asali.

Canza jeri a tsaye na sel (ginshiƙai)

A ce muna da ginshiƙi mai kewayon B2:B6. Za su iya ƙunsar ƙima da ƙima da ƙima waɗanda ke mayar da sakamako ga waɗannan sel. Ba shi da mahimmanci a gare mu, canzawa yana yiwuwa a lokuta biyu. Bayan amfani da wannan aikin, tsayin jere zai kasance iri ɗaya da ainihin tsawon ginshiƙin kewayo.

Jerin matakai don amfani da wannan dabarar shine kamar haka:

1. Zaɓi layi. A cikin yanayinmu, yana da tsawon sel biyar.
2. Bayan haka, matsar da siginan kwamfuta zuwa sandar dabara, kuma shigar da dabarar a can =TSARKI(B2:B6).
3. Latsa haɗin maɓalli Ctrl + Shift + Shigar.

A zahiri, a cikin yanayin ku, kuna buƙatar ƙayyade kewayon da ya dace da teburin ku.

Matsar da jeri na sel a kwance ( layuka)

A ka'ida, tsarin aikin kusan iri ɗaya ne da sakin layi na baya. A ce muna da kirtani tare da daidaitawar farawa da ƙarshen B10:F10. Yana kuma iya ƙunsar duka dabi'u kai tsaye da ƙididdiga. Bari mu yi ginshiƙi daga ciki, wanda zai sami ma'auni iri ɗaya da na asali na asali. Jerin ayyuka kamar haka:

1. Zaɓi wannan shafi tare da linzamin kwamfuta. Hakanan zaka iya amfani da maɓallan da ke kan madannai Ctrl da kibiya ta ƙasa, bayan danna saman tantanin halitta na wannan shafi.
2. Bayan haka muna rubuta dabara =TSARKI(B10:F10) a cikin dabara bar.
3. Muna rubuta shi azaman dabarar tsararru ta amfani da haɗin maɓalli Ctrl + Shift + Shigar.

Canjawa tare da Manna Musamman

Wani zaɓi mai yuwuwar juyawa shine amfani da aikin Manna na Musamman. Wannan ba ma'aikaci ba ne da za a yi amfani da shi a cikin ƙididdiga, amma kuma yana ɗaya daga cikin shahararrun hanyoyin juya ginshiƙai zuwa layuka da akasin haka.

Wannan zaɓi yana kan Shafin Gida. Don samun dama gare shi, kuna buƙatar nemo rukunin “Clipboard”, kuma a can nemo maɓallin “Manna”. Bayan haka, buɗe menu wanda ke ƙarƙashin wannan zaɓi kuma zaɓi abu "Transpose". Kafin haka, kuna buƙatar zaɓar kewayon da kuke son zaɓar. A sakamakon haka, za mu sami irin wannan kewayon, kawai akasin aka kwatanta.

Hanyoyi 3 don Canja wurin Tebu a cikin Excel

Amma a zahiri, akwai ƙarin hanyoyin da za a juya ginshiƙai zuwa layuka da akasin haka. Bari mu bayyana hanyoyi 3 da za mu iya jujjuya tebur a cikin Excel. Mun tattauna biyu daga cikinsu a sama, amma za mu ba da wasu ƙarin misalai don ku sami kyakkyawan ra'ayi na uXNUMXbuXNUMXbhow don aiwatar da wannan hanya.

Hanyar 1: Manna Musamman

Wannan hanya ita ce mafi sauƙi. Ya isa ya danna maɓallai biyu, kuma mai amfani yana karɓar nau'in tebur da aka canza. Bari mu ba da ƙaramin misali don ƙarin haske. A ce muna da tebur mai ɗauke da bayanai game da samfuran nawa ne a halin yanzu suke cikin haja, da kuma nawa farashinsu gabaɗaya. Teburin da kansa yayi kama.

Mun ga cewa muna da kan kai da ginshiƙi tare da lambobin samfur. A cikin misalinmu, taken yana ƙunshe da bayani game da wane samfuri, farashinsa, nawa yake a hannun jari, da menene jimillar kuɗin duk samfuran da ke da alaƙa da wannan abun da ke cikin haja. Muna samun farashi bisa ga dabara inda aka ninka farashin da yawa. Don sanya misalin ya zama abin gani, bari mu mai da taken kore.

Ayyukanmu shine tabbatar da cewa bayanin da ke cikin tebur yana tsaye a kwance. Wato, don ginshiƙan su zama layuka. Jerin ayyuka a yanayinmu zai kasance kamar haka:

1. Zaɓi kewayon bayanan da muke buƙatar juyawa. Bayan haka, muna kwafi wannan bayanan.
2. Sanya siginan kwamfuta a ko'ina a kan takardar. Sannan danna maɓallin linzamin kwamfuta na dama kuma buɗe menu na mahallin.
3. Sa'an nan kuma danna maɓallin "Paste Special" button.

Bayan kammala wadannan matakai, kana bukatar ka danna kan "Transpose" button. Maimakon haka, duba akwatin kusa da wannan abu. Ba mu canza wasu saitunan ba, sannan danna maɓallin "Ok".

Bayan aiwatar da waɗannan matakan, an bar mu da tebur iri ɗaya, layuka da ginshiƙansa kawai an tsara su daban. Hakanan lura cewa sel masu ɗauke da bayanin iri ɗaya ana haskaka su da kore. Tambaya: Menene ya faru da dabarun da ke cikin kewayon asali? Matsayinsu ya canza, amma su da kansu sun zauna. Adireshin sel sun canza sauƙaƙa zuwa waɗanda aka yi bayan jujjuyawar.

Kusan ayyuka iri ɗaya ne ake buƙatar yin su don canza ƙima, ba ƙididdiga ba. A wannan yanayin, kuna buƙatar amfani da menu na Manna Special, amma kafin wannan, zaɓi kewayon bayanan da ke ɗauke da ƙimar. Mun ga cewa ana iya kiran taga ta musamman ta hanyoyi biyu: ta hanyar menu na musamman akan kintinkiri ko menu na mahallin.

Hanyar 2. Aikin TRANSP a cikin Excel

A haƙiƙa, ba a ƙara yin amfani da wannan hanyar da ƙarfi kamar yadda aka yi a farkon bayyanar wannan maƙunsar shirin. Wannan saboda wannan hanya ta fi rikitarwa fiye da amfani da Manna Special. Duk da haka, yana samun amfani da shi wajen sarrafa tebur ta atomatik.

Har ila yau, wannan aikin yana cikin Excel, don haka yana da muhimmanci a san game da shi, ko da yake an daina amfani da shi kusan. Tun da farko mun yi la'akari da hanya, yadda za a yi aiki da shi. Yanzu za mu ƙara wannan ilimin tare da ƙarin misali.

1. Da farko, muna buƙatar zaɓar kewayon bayanan da za a yi amfani da su don jujjuya teburin. Kuna buƙatar kawai zaɓi yankin akasin haka. Misali, a cikin wannan misali muna da ginshiƙai 4 da layuka 6. Sabili da haka, wajibi ne don zaɓar yanki tare da halaye masu adawa: ginshiƙan 6 da layuka 4. Hoton ya nuna shi sosai.
2. Bayan haka, nan da nan za mu fara cika wannan tantanin halitta. Yana da mahimmanci kada a cire zaɓin da gangan. Saboda haka, dole ne ka saka dabarar kai tsaye a cikin mashaya dabara.
3. Na gaba, danna haɗin maɓallin Ctrl + Shift + Shigar. Ka tuna cewa wannan tsari ne na tsararru, tun da muna aiki tare da babban saitin bayanai a lokaci ɗaya, wanda za a canza shi zuwa wani babban saitin sel.

Bayan mun shigar da bayanan, sai mu danna maballin Shigar, bayan haka muna samun sakamako mai zuwa.

Mun ga cewa dabara ba a canjawa wuri zuwa sabon tebur. Haka kuma an yi asarar tsarin. Poeto

Duk wannan dole ne a yi da hannu. Bugu da kari, ku tuna cewa wannan tebur yana da alaƙa da na asali. Sabili da haka, da zaran an canza wasu bayanai a cikin kewayon asali, waɗannan gyare-gyare ana yin su ta atomatik zuwa teburin da aka juye.

Sabili da haka, wannan hanya ta dace sosai a cikin lokuta inda kake buƙatar tabbatar da cewa an haɗa teburin da aka jujjuya zuwa na asali. Idan kun yi amfani da abin sakawa na musamman, wannan yuwuwar ba za ta kasance ba.

teburin taƙaitaccen bayani

Wannan wata sabuwar hanya ce ta asali, wacce ke ba da damar ba kawai don jujjuya teburin ba, har ma don aiwatar da babban adadin ayyuka. Gaskiya, tsarin jujjuyawar zai ɗan bambanta idan aka kwatanta da hanyoyin da suka gabata. Jerin ayyuka kamar haka:

1. Mu yi tebur pivot. Don yin wannan, kuna buƙatar zaɓar teburin da muke buƙatar juyawa. Bayan haka, je zuwa abin "Saka" kuma nemi "Table Pivot" a can. Akwatin maganganu kamar wanda ke cikin wannan hoton hoton zai bayyana.
2. Anan za ku iya sake tsara kewayon da za a yi shi, da kuma yin wasu saitunan da yawa. Yanzu muna da sha'awar wuri na tebur pivot - a kan sabon takarda.
3. Bayan haka, za a ƙirƙiri shimfidu na teburin pivot ta atomatik. Wajibi ne a sanya alama a cikinsa waɗannan abubuwan da za mu yi amfani da su, sannan dole ne a motsa su zuwa wurin da ya dace. A cikin yanayinmu, muna buƙatar matsar da abin "samfurin" zuwa "Shafin Sunaye", da "Farashin kowane Piece" zuwa "Dabi'u".
4. Bayan haka, a ƙarshe za a ƙirƙiri teburin pivot. Ƙarin kari shine lissafin atomatik na ƙimar ƙarshe.
5. Kuna iya canza wasu saitunan kuma. Misali, cire alamar "Farashin kowane yanki" kuma duba abin "Jimlar farashi". A sakamakon haka, za mu sami tebur mai ɗauke da bayanai game da nawa farashin samfuran. Wannan hanyar jujjuyawar ta fi sauran aiki sosai. Bari mu bayyana wasu fa'idodin allunan pivot:

1. Kayan aiki da kai. Tare da taimakon allunan pivot, zaku iya taƙaita bayanai ta atomatik, da kuma canza matsayi na ginshiƙai da ginshiƙai ba da gangan ba. Don yin wannan, ba kwa buƙatar yin kowane ƙarin matakai.
2. Yin hulɗa. Mai amfani zai iya canza tsarin bayanai sau da yawa kamar yadda yake buƙatar yin ayyukansa. Misali, zaku iya canza tsari na ginshiƙai, da kuma bayanan rukuni ta hanyar sabani. Ana iya yin wannan sau da yawa kamar yadda mai amfani ke buƙata. Kuma a zahiri yana ɗaukar ƙasa da minti ɗaya.
3. Sauƙi don tsara bayanai. Yana da sauƙi sosai don shirya tebur pivot kamar yadda mutum yake so. Don yin wannan, kawai yi ƴan danna linzamin kwamfuta.
4. Samun dabi'u. Adadin ƙididdiga masu yawa waɗanda aka yi amfani da su don ƙirƙirar rahotanni suna cikin samun damar mutum kai tsaye kuma suna da sauƙin haɗawa cikin tebur pivot. Waɗannan su ne bayanai kamar summing, samun ma'anar lissafi, ƙayyade adadin sel, ninkawa, gano mafi girma da ƙananan dabi'u a cikin samfurin da aka ƙayyade.
5. Ikon ƙirƙirar ginshiƙi na taƙaitawa. Idan PivotTables an sake ƙididdige su, ana sabunta ginshiƙi masu alaƙa ta atomatik. Yana yiwuwa a ƙirƙira adadin sigogi kamar yadda kuke buƙata. Ana iya canza su duka don takamaiman aiki kuma ba za a haɗa su ba.
6. Ikon tace bayanai.
7. Yana yiwuwa a gina tebur pivot bisa tushen bayanan tushe fiye da ɗaya. Saboda haka, aikin su zai zama mafi girma.

Gaskiya, lokacin amfani da allunan pivot, dole ne a la'akari da hane-hane masu zuwa:

1. Ba duk bayanai ba ne za a iya amfani da su don samar da allunan pivot. Kafin a yi amfani da su don wannan dalili, dole ne a daidaita sel. A cikin kalmomi masu sauƙi, yi daidai. Abubuwan bukatu na tilas: kasancewar layin kan kai, cikar dukkan layukan, daidaiton tsarin bayanai.
2. Ana sabunta bayanan Semi-atomatik. Don samun sabon bayani a cikin tebur mai pivot, dole ne ku danna maɓalli na musamman.
3. Teburan pivot suna ɗaukar sarari da yawa. Wannan na iya haifar da ɗan rushewar kwamfutar. Hakanan, fayil ɗin zai yi wahala a aika ta imel saboda wannan.

Hakanan, bayan ƙirƙirar tebur pivot, mai amfani ba shi da ikon ƙara sabbin bayanai.