6 Hanyoyi don Tsara Bayanai a Excel

Wannan samfurori na ƙididdigar hanyoyi daban-daban na rarraba bayanai a Excel. Za'a iya samun bayani na musamman akan shafuka masu zuwa:

1. Saurin Tattaunawa a Yanayin Kasuwanci guda ɗaya ta amfani da Tsara & Filter ko Hotunan Kyau
2. Kayan a kan Maɗallan Alamomi
3. Tsara ta Dates ko Times
4. Tsara ta kwanaki na mako, watanni ko wasu Lissafin Lissafi
5. Tsara ta Runduna - Reordering ginshiƙai

Zaɓin Bayanan da za a ƙayyade

Kafin bayanai za a iya warewa, Excel yana bukatar sanin ainihin iyakar da za'a tsara, kuma yawanci Excel yana da kyakkyawan kyau a zaɓar yankunan da suka shafi bayanai - muddin lokacin da aka shigar,

1. Ba a bar wasu layuka ko ginshiƙai a cikin wani yanki na bayanai ba;
2. kuma an bar layukan layi da ginshiƙai tsakanin yankunan da suka shafi bayanai.

Excel za ta ƙayyade, daidai sosai, idan yankin bayanai yana da sunayen filin kuma cire wannan jere daga bayanan da za a tsara.

Duk da haka, ƙyale Excel don zaɓar layin da za a yi jeri zai iya zama mai haɗari - musamman tare da yawancin bayanai waɗanda suke da wuya a bincika.

Don tabbatar da cewa an zaɓi bayanin da ya dace, nuna hasashen kafin ka fara da irin.

Idan har yanzu ana iya rarraba irin wannan layi, to mafi kusantar shi ne a ba shi suna .

01 na 05

Kayan Bayani da Kayan Bayani

Hadawa yana buƙatar amfani da maɓallin maɓalli da kuma irin tsari.

Maɓallin maɓallin shine bayanan da ke cikin shafi ko ginshiƙai da kake so a warware ta. An gano shi ta hanyar shafi ko sunan filin. A cikin hoton da ke sama, mahimman abubuwan da suke yiwuwa su ne ID na Aikin, Sunan , Age , Shirin , da Hasken An fara

A cikin sauri, danna kan tantanin tantanin halitta a cikin shafi wanda ya ƙunshi nau'in maɓallin ya isa ya gaya Excel abin da maɓallin kewayawa yake.

Don rubutu ko lambobi, lambobin biyu don irin tsari suna hawa da sauka .

Yayin da kake amfani da maɓallin Kayan & Filter a kan shafin shafin rubutun, zabin tsari a cikin jerin jerin saukewa zai canza dangane da irin bayanai a cikin zaɓin da aka zaba.

Gyara Tsara ta amfani da Tsara & Filter

A Excel, za a iya aiwatar da sauri ta hanyar amfani da Maɓalli & Filter a kan shafin shafin shafin rubutun .

Matakan da za a yi da sauri shine:

1. Danna kan tantanin halitta a cikin shafi wanda ya ƙunshi maɓallin kama
2. Danna kan shafin shafin na kintinkiri idan ya cancanta
3. Danna maɓallin Fit & Filter don buɗe jerin abubuwan da za a sauke su
4. Danna kan ɗaya daga cikin zaɓuɓɓuka guda biyu don daidaitawa a ko dai a kan hawan sama ko saukowa
5. Bincika don tabbatar da cewa an tsara bayanai daidai

Tsara Bayanan Amfani da Rubutun Hoton Hotuna

Babu wani haɗin haɗin maɓallin gajeren keyboard don rarraba bayanai a Excel.

Abin da ke samuwa yana da maɓallin hotuna, wanda ya ba ka damar amfani da keystrokes maimakon maɓallin linzamin kwamfuta don zaɓan waɗannan zaɓuɓɓukan da aka jera a sama a kan shafin shafin shafin rubutun.

• Yin amfani da keyboard don samun dama ga zabin rubutun zaɓi yana farawa tare da maɓallin Alt .

Don Tsara cikin Dokar Tafiya Ta amfani da Ƙunin Hoton

1. Danna kan tantanin halitta a cikin maɓallin kewayawa
2. Latsa maɓallai masu zuwa a kan keyboard:
3. Alt HSS
4. Tebur na bayanan ya kamata a ware shi A zuwa Z / mafi ƙanƙanta zuwa mafi girma ta hanyar da aka zaba

Maɓallan hotuna sun fassara cikin:
"Alt" key> "Home" shafin> "Shirya" ƙungiya> "Tsara & Filter" menu> "Sanya mafi ƙanƙanci zuwa Mafi Girma" zaži.

Don Tsara cikin Dokar Tashi ta Amfani da Hoton Hotanni

Matakan da za a raba a cikin umarni masu saukarwa ta yin amfani da maɓallan hotuna daidai ne da waɗanda aka lissafa don nau'in haɗuwa sai dai maɓallin haɗari mai zafi shine:

Alt HSO

Maɓallan hotuna sun fassara cikin:
"Alt" key> "Home" shafin> "Shirya" ƙungiya> "Tsara & Filter" menu> "Yaɗa Mafi Girma zuwa Mafi ƙanƙanci" zaɓi.

02 na 05

Kayan a kan Maɗallan Kalmomin Bayanai a Excel

Bugu da ƙari, yin sauri a bisa wani shafi na bayanai, siffar al'ada na Excel ba ta ba ka dama a kan ginshiƙai masu yawa ta hanyar fassara maɓalli iri iri.

A cikin nau'in mahallin, ana gano maɓallan maɓallin ta hanyar zaɓar rubutun shafi a cikin akwatin maganganu .

Kamar yadda yake da sauri, ana nuna ma'anar waɗannan maɓallan ta hanyar gano ginshiƙan ginshiƙai ko sunayen filin , a cikin teburin da ke dauke da maɓallin kama.

Tana a kan Maɓallai Maɓalli Mai Sauƙi

A cikin misalin da ke sama, an biyo matakan da suka biyo don warware bayanai a cikin kewayon H2 zuwa L12 a kan ginshiƙai guda biyu na bayanai - na farko da suna, sa'an nan kuma bayan shekaru.

1. Bayyana kewayon Kwayoyin da za a rarraba
2. Danna kan shafin shafin shafin rubutun .
3. Danna maɓallin Fit & Filter a kan rubutun don buɗe jerin saukewa.
4. Danna Custom Custom a cikin jerin saukewa don kawo sama da akwatin maganganu
5. A ƙarƙashin ginshiƙan Shirin a cikin akwatin maganganu, zaɓi Sunan daga jerin abubuwan da aka sauke don fara samo bayanan ta Shafin Sunan
6. Za'a iya zaɓin Zaɓuɓɓukan Zaɓuɓɓuka a Ƙunƙwasa - tun da irin wannan yana dogara ne akan ainihin bayanai a cikin tebur
7. A karkashin Ƙa'idar Fitarwa, zaɓa Z zuwa A daga jerin jeri don rarraba Sunan sunan a sauƙaƙewa
8. A saman akwatin maganganu, danna kan maɓallin Ƙara Ƙara don ƙara nau'in nau'i na biyu
9. Domin maɓalli na biyu, a ƙarƙashin jagorar Shirin, zaɓi Age daga jerin sunayen da aka sauke don warware rubutun tare da sunayen dakaloli ta hanyar tarihin Age
10. A ƙarƙashin Hoto Order , zaɓi Mafi Girma zuwa mafi ƙanƙanci daga jerin abubuwan da aka sauke don rarraba Tarihin ƴancin cikin sauƙi
11. Danna Ya yi a cikin akwatin maganganu don rufe akwatin maganganu da kuma raba bayanai

A sakamakon binciken mahimman nau'i na biyu, a cikin misalin da ke sama, an ƙaddamar da rubutun biyu tare da mahimman lambobi don filin Sunan a cikin tsari mai saukowa ta yin amfani da filin Age , haifar da rikodin ga dalibi A. Wilson mai shekaru 21 da haihuwa da rikodin na biyu A. Wilson shekaru 19.

Hanya na Farko: Takaddun Shafi ko Bayanai?

Tsarin bayanan da aka zaɓa don rarraba a cikin misalin da ke sama ya haɗa da rubutun shafi a sama da jere na farko.

Excel gano wannan jere ya ƙunshi bayanan da ya bambanta da bayanai a cikin layuka na gaba don haka ya zama jeri na farko don zama rubutun shafi kuma gyara hanyoyin da aka samo a cikin akwatin maganganu don hada su.

Ɗaya daga cikin ka'idodin da Excel ke amfani dashi don sanin ko jere na farko da ya ƙunshi rubutun shafi shine tsarawa. A misalin da ke sama, rubutu a jere na farko shine launi daban-daban kuma yana da launi daban daga bayanai a cikin sauran layuka. Haka kuma an rabu da shi daga layuka da ke ƙasa ta hanyar iyaka.

Excel yana amfani da wannan bambanci wajen yin ƙaddara a kan ko jere na farko shi ne jigo, kuma yana da kyakkyawan kyau a samun shi daidai - amma ba mai kuskure ba ne. Idan ya yi kuskure, ɗakon akwatin maganganun yana dauke da akwati - Akwatin na yana da sautunan kai - wanda za'a iya amfani dasu don shafe wannan zaɓi na atomatik.

Idan jere na farko ba ya ƙunshi rubutun ba, Excel yana amfani da harafin shafi - irin su Column D ko Shafin E - kamar yadda zaɓuɓɓuka a cikin zaɓi na Yankin Kwalin Gida .

03 na 05

Tsara Bayanan ta kwanan wata ko lokaci a cikin Excel

Bugu da ƙari ga ƙaddamar da bayanan rubutu ta hanyar haruffa ko lambobi daga mafi girma zuwa mafi ƙanƙanci, tsarin zaɓuɓɓukan Excel ya haɗa da dabi'u na kwanta.

Irin wannan umurni don kwanakin sune:

• Dokar haɓaka - mafi tsufa zuwa sabuwar
• Kwancen umarni - sabuwar zuwa mafi tsufa

Quick Tsara vs. Tsara Dialog Box

Tun kwanakin da lokutan an tsara bayanai ne kawai, don maɗaukaki a kan wani shafi ɗaya - irin su Kwanan wata An ƙulla a misali a cikin hoton da ke sama - hanya mai sauri za a iya amfani da shi sosai.

Ga wasu nau'ikan da ke kunshe da ginshiƙai na kwanakin ko lokuta, ana bukatar amfani da Kwalin akwatin kwance - kamar yadda a yayin da aka fice a kan ginshiƙai na lamba ko bayanan rubutu.

Tsara ta Kwanan wata Misali

Don yin saurin sauri ta kwanan wata a cikin tsari mai girma - mafi tsufa zuwa sabuwar - domin misali a cikin hoton da ke sama, matakai zasu kasance:

1. Bayyana kewayon Kwayoyin da za a rarraba
2. Danna kan shafin shafin shafin rubutun
3. Danna maɓallin Fit & Filter a kan rubutun don buɗe jerin saukewa
4. Danna kan Zabuka mafi Girma zuwa Zaɓin Sabuwar a cikin jerin don rarraba bayanai a cikin tsari mai girma
5. Dole ne a yi amfani da rubutun tare da kwanakin da suka fi dacewa a cikin Shafin da aka sanya a saman teburin

Dates da Times Ajiye a matsayin Rubutu

Idan sakamako na rarraba ta kwanan wata ba ya fita kamar yadda ake sa ran, bayanan da ke dauke da nau'in maɓalli na iya ɗauke da kwanakin ko lokutan da aka adana a matsayin rubutu na rubutu maimakon a matsayin lambobi (kwanakin da lokutan suna da cikakkun bayanai).

A cikin hoton da ke sama, rikodin na A. Peterson ya ƙare a kasan jerin, lokacin da, bisa ranar rance - Nuwamba 5, 2014 -, an sanya rikodin a sama da rikodi na A. Wilson, wanda kuma yana da rance na ranar 5 ga Nuwamba.

Dalili na sakamakon da ba a tsammani shi ne cewa kwanan kuɗi don A. Peterson an adana shi azaman rubutu, maimakon a matsayin lamba

Bayanin Mixed da Saurin Ƙari

Lokacin yin amfani da hanyar saurin sauri idan bayanan da ke dauke da rubutu da bayanan lambobi suna hade tare, Excel ya ƙunshi lamba da kuma bayanan rubutu daban - ajiye rubutun tare da bayanan rubutu a ƙasa na jerin jeri.

Excel zai iya haɗawa da rubutun shafi a cikin irin sakamakon - fassara su a matsayin wani jigon bayanan rubutu maimakon a matsayin filin sunayen don lissafin bayanai.

Tattauna Gargaɗi - Kayan Kwance Kwance

Kamar yadda aka nuna a cikin hoton da ke sama, idan ana amfani da akwatin maganganu na musamman, ko da maɗaurori a kan wani shafi, Excel yana nuna maka saƙo cewa ya ci karo da bayanan da aka adana a matsayin rubutu kuma ya ba ka zabi zuwa:

• Tada wani abu mai kama da lambar a matsayin lamba
• Tada lambobin da lambobin da aka ajiye azaman rubutu daban

Idan ka zaɓi zaɓin farko, Excel zai yi ƙoƙari ya sanya bayanan rubutu a daidai wurin da sakamakon ya kasance.

Zaži zaɓi na biyu kuma Excel zai sanya rubutun da ke dauke da bayanan rubutu a kasan irin sakamakon - kamar dai yadda ya dace da sauri.

04 na 05

Bayar da Bayanai ta Kwanan watan Yaya ko ta Watanni a Excel

Tsara ta kwana na mako ko watanni na shekara ta amfani da wannan tsarin da aka gina wanda Excel yayi amfani da shi don ƙara kwanakin ko watanni zuwa takarda aiki ta amfani da cikawa .

Wadannan jerin suna bada izinin rarraba ta kwana ko watanni na lokaci-lokaci maimakon na haruffa.

A cikin misalin da ke sama, an tsara bayanan da aka tsara ta wata guda cewa dalibai sun fara shirin su na kan layi.

Kamar yadda za a iya nuna wasu zaɓuɓɓuka, za a iya nuna jerin abubuwan kirki ta jerin al'ada ta hawan (Lahadi zuwa Asabar / Janairu zuwa Disamba) ko umarni na sauka (Asabar zuwa Lahadi / Disamba zuwa Janairu).

A cikin hoton da ke sama, ana bin matakan da suka biyo don warware samfurin samfurin a cikin kewayon H2 zuwa L12 ta watanni na shekara:

1. Bayyana kewayon Kwayoyin da za a rarraba
2. Danna kan shafin shafin shafin rubutun .
3. Danna maɓallin Fit & Filter a kan rubutun don buɗe jerin saukewa.
4. Danna Custom Custom a cikin jerin saukewa don kawo sama da akwatin maganganu
5. A ƙarƙashin ginshiƙan Shirin a cikin maganganun maganganu, zaɓi Watan Ya fara daga jerin abubuwan da aka sauke don warware bayanai ta watanni na shekara
6. Za'a iya zaɓin Zaɓuɓɓukan Zaɓuɓɓuka a Ƙunƙwasa - tun da irin wannan yana dogara ne akan ainihin bayanai a cikin tebur
7. A karkashin Ƙa'idar Fit Order , danna kan gefen ƙasa kusa da tsohuwar zaɓi A to Z don buɗe menu da aka sauke
8. A cikin menu, zaɓi Lissafin Yanayi don buɗe akwatin maganganun Lissafin Lissafi
9. A gefen hagu na akwatin maganganu, danna sau ɗaya a cikin jerin: Janairu, Fabrairu, Maris, Afrilu ... don zaɓar shi

10. Danna Ya yi don tabbatar da zaɓin kuma koma zuwa Tsara akwatin maganganu

11. Jerin zaɓaɓɓun - Janairu, Fabrairu, Maris, Afrilu - za a nuna su a ƙarƙashin umurnin

12. Danna Ya yi don rufe akwatin maganganu da kuma raba bayanai ta watanni na shekara

Lura : Ta hanyar tsoho, lissafi na al'ada suna nuna su ne kawai a cikin tsari mai girma a cikin akwatin rubutun Lissafin Lissafi . Don ware bayanan da aka saukar ta hanyar amfani da jerin al'ada bayan da aka zaba jerin da aka so domin an nuna shi a ƙarƙashin Dokar Saya a cikin akwatin maganganu kamar:

1. Danna maɓallin ƙasa kusa da jerin da aka nuna - kamar Janairu, Fabrairu, Maris, Afrilu ... don buɗe menu da aka sauke
2. A cikin menu, zaɓi zaɓi na jerin al'ada wanda aka nuna a cikin tsari mai saukowa - kamar Disamba, Nuwamba, Oktoba, Satumba ...

3. Danna Ya yi don rufe akwatin maganganu da kuma raba bayanan da aka saukar ta amfani da jerin al'ada

05 na 05

Tsara ta Runduna don sake komawa ginshiƙai a Excel

Kamar yadda aka nuna da zaɓuɓɓuka na baya, ana yin amfani da bayanai ta hanyar amfani da shafuka ko sunayen filin kuma sakamakon shine sake dawo da layuka ko bayanan bayanai.

Ƙananan sanannun, sabili da haka, ƙananan amfani da irin wannan zaɓi a Excel shine ƙaddamar da jere, wanda yana da tasiri na sake tsara tsari na ginshiƙai hagu zuwa dama a cikin takardar aiki

Ɗaya daga cikin dalilan da aka tsara ta hanyar jeri shi ne daidaita da daidaitaccen shafi tsakanin launi daban-daban na bayanai. Tare da ginshiƙai a gefen hagu zuwa dama, yana da sauƙi don kwatanta bayanan ko don kwafi da matsar da bayanai tsakanin Tables.

Samar da Dokar Shiga

Mahimmanci, duk da haka, yana samun ginshiƙai a daidai tsari na aiki mai sauƙi saboda ƙuntatawa ga ƙimar haɓaka da saukowa don zaɓuɓɓuka.

Yawancin lokaci, wajibi ne don yin amfani da tsari na al'ada, kuma Excel ya haɗa da zaɓuɓɓuka don ƙaddamar da launi ko launin launi ko ta hanyar tsara yanayin gumaka .

Wadannan zaɓuɓɓuka, kamar yadda aka tsara a ƙasa na wannan shafin, har yanzu suna aiki sosai kuma ba sauki don amfani ba.

Wataƙila hanya mafi sauki ta gaya Excel a kan tsari na ginshiƙai shine ƙara jeri a sama ko žasa bayanan layin da ke dauke da lambobi 1, 2, 3, 4 ... wanda ya nuna tsari na ginshiƙai hagu zuwa dama.

Tsara ta hanyar layuka sa'an nan kuma ya zama abu mai sauƙi na rarraba ginshiƙan mafi ƙanƙanta zuwa mafi girma ta hanyar jere da ke ƙunsar lambobi.

Da zarar an yi haka, za a iya share sauƙin lambobi .

Tsara ta hanyar Alamu Misali

A cikin samfurin samfurin da aka yi amfani da wannan jerin a kan jerin nau'in Excel, ɗakunan ID na ID ya kasance na farko a gefen hagu, sannan sunan ya biyo baya sannan kuma yawanci Age .

A cikin wannan misali, kamar yadda aka nuna a hoton da ke sama, an sake ginshikan ginshiƙai don haka Shirin Shirin yana farko a gefen hagu sannan Month ya fara , Sunan, da sauransu.

Matakan da aka biyo su sunyi amfani da su don sauya shafi don yin abin da aka gani a cikin hoton da ke sama:

1. Saka jere marar layi a sama da layin da ke kunshe da sunayen filin
2. A cikin wannan jere, shigar da lambobi masu zuwa zuwa hagu zuwa fara
   shafi H: 5, 3, 4, 1, 2
3. Bayyana kewayon H2 zuwa L13
4. Danna kan shafin shafin shafin rubutun .
5. Danna maɓallin Fit & Filter a kan rubutun don buɗe jerin saukewa.
6. Danna Custom Custom a cikin jerin saukewa don kawo sama da akwatin maganganu
7. A saman akwatin maganganu, danna kan Zaɓuɓɓuka don buɗe akwatin maganganun Zabuka
8. A cikin ɓangaren Gabatarwa na wannan zance na zangon na biyu, danna Tambaya hagu zuwa dama don warware tsari na ginshiƙan hagu zuwa dama a cikin takardun aiki
9. Danna Ya yi don rufe wannan maganganu
10. Tare da canje-canje a Gabatarwa, maɓallin ginshiƙan a cikin jigon Magana ya canza zuwa Row
11. A karkashin Hanya jigo, zabi don raba ta Row 2 - jere wanda ke kunshe da lambobi
12. Za'a iya zaɓin Zaɓuɓɓukan Zaɓuɓɓuka a Ƙunƙwasa
13. A karkashin Ƙa'idar Fitarwa, zaɓi Mafi ƙanƙanci zuwa Mafi Girma daga jerin jeri-ƙasa don warware lambobi a jere 2 a cikin tsari mai girma
14. Danna Ya yi don rufe akwatin maganganu da kuma raba ginshiƙan hagu zuwa dama ta lambobi a jere 2
15. Tsarin ginshiƙai ya kamata a fara tare da Shirin da aka bi da watan Farawa , Sunan , da sauransu.

Amfani da Yanayin Excel na Custom Zabuka don Sake Gyara ginshikan

Kamar yadda aka ambata a sama, yayin da al'ada suna samuwa a cikin akwatin maganganu na Excel, waɗannan zaɓuɓɓuka ba su da sauƙin amfani da su idan sun dawo da ginshiƙai a cikin takarda.

Zaɓuɓɓuka don ƙirƙirar samfurin tsari na al'ada samuwa a cikin Akwatin maganganu don ƙaddamar da bayanai ta hanyar:

• cell launi
• launi launi
• icon

Kuma, sai dai idan kowanne shafi ya riga ya kasance da tsari na musamman - irin su launuka daban-daban ko launuka masu launi, ana buƙatar tsarawa zuwa sel guda ɗaya a jere guda ɗaya don kowane ɗakin da za'a sake dawowa.

Alal misali, don amfani da launin launi don sake tsara ginshiƙai a cikin hoto a sama

1. Danna kowannen filin filin kuma canza launin launi don kowane - irin su ja, kore, blue, da dai sauransu.
2. A cikin Kwandon maganganu, saita Girma a kan zaɓi zuwa Font Color
3. Under Order, da hannu saita tsari na filin sunaye launuka don daidaita tsarin da ake so
4. Bayan sakewa, sake saita launin launi don kowane filin filin