Nigute ushobora gukora icyitegererezo muri excel: imyambarire 4 ikora

Mugihe ukorana nimbonerahamwe ya Excel, ni nkenerwa kenshi kugirango uhitemo guhitamo ibipimo runaka cyangwa mubihe byinshi. Porogaramu irashobora gukora ibi muburyo butandukanye ukoresheje urukurikirane rwibikoresho. Reka tumenye uburyo bwo gukora icyitegererezo muri Excele ukoresheje amahitamo atandukanye.

Icyitegererezo

Ibyitegererezo byamakuru bigizwe nuburyo bwo gutoranya kuva kuri ibyo bisubizo byuzuye bihaza ibisabwa, bikurikirwa nibisohoka muri bo ku rupapuro rwurutonde rwihariye cyangwa murwego rwinkomoko.

Uburyo 1: Koresha autof

Inzira yoroshye yo guhitamo ni ugukoresha Autofilter yagutse. Reba uko wabikora kurugero runaka.

1. Hitamo agace kurupapuro, mumakuru ushaka gukora icyitegererezo. Muri tab ya Murugo, kanda kuri "Sort na Akayunguruzo". Ishyirwa muburyo bwo guhindura. Kurutonde rufungura nyuma y'uru rutonde, kanda kuri buto ya "Akayunguruzo".

   

   Hariho amahirwe yo gukora kandi ukundi. Kugirango ukore ibi, nyuma yo guhitamo akarere kurupapuro, twimukira kuri tab "data". Kanda kuri buto ya "Akayunguruzo", ishyirwa kuri kaseti mumaso noyunguruzo.



2. Nyuma yiki gikorwa kumutwe wameza, picturoms isa nkaho itangira gushungura muburyo bwimpande nto yimpande eshatu muruhande rwiburyo bwa selile. Kanda kuri iki gishushanyo mumutwe wicyo nkingi, ukurikije ibyo twifuza gukora icyitegererezo. Muri menu ikoresha menu, genda unyuze kuri "Inyandiko yubuyunguruzo". Ibikurikira, hitamo umwanya "Akayunguruzo ka Shushanya ...".



3. Umukoresha wogushuka idirishya arakorwa. Muri yo, urashobora gushiraho imipaka ihitamo. Mu rutonde rwamanutse ku nkingi ya selile isanzwe, ibyo dukoresha kurugero, urashobora guhitamo kimwe mu bintu bitanu:
   • bingana;
   • bingana;
   • byinshi;
   • byinshi cyangwa bingana;
   • ntoya.

   Reka dushyireho imiterere nkurugero kugirango duhitemo indangagaciro gusa amafaranga yinjira arenga amafaranga 10,000. Dushiraho uhinduka kumwanya "byinshi". Mu murima ukwiye uhuye n'agaciro "10,000". Gukora ibikorwa, kanda kuri buto "OK".



4. Nkuko tubibona, nyuma yo kugisimba, gusa imirongo yagumye, aho amafaranga yinjira arenga amafaranga 10,000.



5. Ariko murwego rumwe turashobora kongeramo imiterere ya kabiri. Kugirango ukore ibi, garuka ku idirishya ryuzuye. Nkuko dushobora kubibona, mugice cyacyo cyo hepfo hari indi mico hamwe ninzego zijyanye no kwinjiza. Reka noneho dushyire hejuru yo gutoranya amafaranga 15,000. Kugirango ukore ibi, shiraho switch kumwanya wa "gake", kandi mu murima iburyo bukwiye agaciro "15000".

   Mubyongeyeho, haracyariho guhindura ibintu. Afite ingingo ebyiri "na" na "cyangwa". Mburabuzi, yashyizwe mumwanya wambere. Ibi bivuze ko imirongo yonyine izaguma muri sample ihaza ibibujijwe. Niba yashyizwe kuri "cyangwa" umwanya, noneho indangagaciro zikwiye kuri kimwe mubihe bibiri bizagumaho. Kuri twe, ugomba gushyiraho switch kuri "na", ni ukuvuga, va kuri iyi mirimo isanzwe. Nyuma yuko indangagaciro zose zinjiye, kanda kuri buto ya OK.



6. Noneho imirongo gusa yagumye kumeza, aho umubare winjiza utari munsi ya 10,000, ariko ntarenga amafaranga 15,000.



7. Mu buryo nk'ubwo, urashobora gushiraho akayunguruzo mu zindi nkingi. Birashoboka gukomeza gushungura no kubihe byashize byashyizwe mu nkingi. Reka rero turebe uko guhitamo bifatwa ukoresheje filt format. Kanda kuri foltration igishushanyo mbonera. Uhoraho ukanze kuri "Akayunguruzo Kumunsi" Urutonde na filteri.



8. Imigenzo ya Autofilt Gukora idirishya ritangira. Kora guhitamo ibisubizo mumeza kuva 4 kugeza 6 Gicurasi 2016 birimo. Muguhitamo guhinduranya, nkuko mubibona, ndetse nibindi byinshi kuruta imiterere yumubare. Hitamo umwanya "nyuma cyangwa uhwanye". Mu murima iburyo, shyira agaciro "04.05.2016". Muburyo bwo hasi, twashizeho switch kuri "kugeza cyangwa ingana". Muburyo bwiza, andika agaciro "06.05.2016". Imiterere ihuza guhinduranya ikiruhuko mumwanya usanzwe - "na". Kugirango usabe ibijyanye nigikorwa, kanda buto "OK".



9. Nkuko mubibona, urutonde rwacu rwanze kurushaho. Noneho imirongo gusa irasigaye muri yo, aho amafaranga yinjiza atandukanye kuva 10,000 kugeza 15,000 amafaranga kuva kuri 04.05 kugeza 06.05.2016.



10. Turashobora gusubiramo gushungura muri kimwe mu nkingi. Reka tubikore kugirango duhangire indangagaciro. Kanda ahanditse Autofilter mu nkingi ijyanye. Murutonde rutonyanga, kanda kuri "Gusiba" ikintu.



11. Nkuko mubibona, nyuma yibi bikorwa, icyitegererezo cyamafaranga yinjiza azahagarikwa, ariko azafata amahitamo gusa (kuva 04/05/2016 kugeza 06.05.2016).



12. Iyi mbonerahamwe ifite indi nkingi - "izina". Irimo amakuru muburyo bwinyandiko. Reka turebe uburyo bwo gukora icyitegererezo ukoresheje gushungura ukoresheje indangagaciro.

    Kanda kuri filteri igishushanyo mwizina ryinkingi. Guhora ujya kumazina yurutonde "Umwanya uyunguruzi" na "Shushanya Akayunguruzo ...".



13. Umukoresha Autofilter Idirishya rifungura. Reka dukore icyitegererezo namazina y "ibirayi" n "" inyama ". Mubice byambere, imiterere ihinduka igishishwa "bingana". Mu murima iburyo bwayo buhuza ijambo "ibirayi". Guhindura igice cyo hasi nabyo bizashyira umwanya "uhwanye". Mu murima uhaba, ndakora inyandiko - "inyama". Noneho ubu turimo gukora ibyo batageze mbere: Shiraho guhuza uhindukirira umwanya "cyangwa". Noneho umurongo urimo ikintu runaka cyerekanwe kizerekanwa kuri ecran. Kanda kuri buto ya "OK".



14. Nkuko mubibona, habaho imbogamizi kumunsi shyashya (kuva 04.05.2016 kugeza 06.05.2016) kandi mwizina (ibirayi ninyama). Nta mbibi zibuza amafaranga yinjira.



15. Urashobora gukuraho rwose akayunguruzo nuburyo bumwe bwakoreshejwe mugushiraho. Kandi ntacyo bitwaye uburyo bwakoreshejwe. Kugirango usubiremo akayunguruzo, mugihe muri tab "data", kanda kuri buto ya "Akayunguruzo", iherereye mu itsinda ".

    

    Ihitamo rya kabiri ririmo inzibacyuho kuri tab "urugo". Dukora gukanda kuri lebon kuri "Sort na Akayunguruzo" mu gice cyo guhindura. Murutonde rukora, kanda kuri buto ya "Akayunguruzo".

Mugihe ukoresheje kimwe mubikorwa bibiri byavuzwe haruguru, gushukwa bizasibwa, kandi ibisubizo byicyitegererezo birasukurwa. Ni ukuvuga, ameza azerekanwa na leta yose ifite amakuru.

Isomo: Imodoka yo kuyungurura ibikorwa muri Excel

Uburyo 2: Gushyira mu bikorwa formulaila

Kora guhitamo birashobora kandi gukoresha formula igoye ya array. Bitandukanye na verisiyo ibanza, ubu buryo butanga umusaruro wibisubizo mumeza itandukanye.

1. Kurupapuro rumwe, turema ameza yubusa hamwe namazina amwe yinkingi mumutwe nkisoko.



2. Tanga selile zose zubusa zinkingi ya mbere yimeza nshya. Shyira indanga mumurongo wa formula. Hano hazinjizwa na formula itanga icyitegererezo kubipimo byagenwe. Tuzahitamo umurongo, ingano yinjiza irenze amafaranga 15,000. Murugero rwihariye, amahuriro yatangijwe azareba atya:

   = Indangagaciro (A2: A29; nto (niba (15000

   Mubisanzwe, muri buri kibazo runaka, aderesi yingirabuzimafatizo kandi iringaniye izaba iyawe. Kuri uru rugero, urashobora kugereranya formulaire hamwe na coordinateur kurugero hanyuma umenyera kubyo ukeneye.



3. Kubera ko iyi ari formulaire ya array, kugirango ubishyire mubikorwa, ugomba gukanda buto yintonde, ariko Ctrl + Shift + Injira Urufunguzo. Turabikora.



4. Kugira inkingi ya kabiri hamwe namatariki hanyuma ugashyiraho indanga mumurongo wa formula, tumenyekanisha imvugo ikurikira:

   = Indangagaciro (B2: B29; nto (niba (15000

   Kanda Ctrl + Shift + andika urufunguzo.



5. Mu buryo nk'ubwo, mu nkingi zifite amafaranga, andika formulaire y'ibi bikurikira:

   = Indangagaciro (C2: C29; nto (niba (15000

   Na none, andika Ctrl + Shift + andika urufunguzo.

   Mubibazo bitatu byose, gusa agaciro ka mbere gusa guhuza birahinduka, kandi ibisigaye bya formula birasa rwose.



6. Nkuko tubibona, ameza yuzuyemo amakuru, ariko isura yayo ntabwo ari nziza rwose, byongeye, indangagaciro zamatariki zuzuyemo nabi. Birakenewe gukosora izi nama. Ubudakwiye bwitariki bifitanye isano nukuntu imiterere yinkingi yinkingi ihuye, kandi dukeneye gushyiraho imiterere yitariki. Turagaragaza inkingi yose, harimo selile hamwe namakosa, hanyuma ukande kuri interineti yerekana buto yimbeba iburyo. Ku rutonde rugaragara, unyuze mu "bwoko bw'akagari ...".



7. Mu idirishya rigizwe, fungura "umubare". Muri "imiterere ya numero" yo guhagarika agaciro "itariki". Kuruhande rwiburyo bwidirishya, urashobora guhitamo itariki yifuzwa. Nyuma yigenamiterere ryerekanwe, kanda kuri buto "OK".



8. Noneho itariki igaragara neza. Ariko, nkuko tubibona, hepfo yimeza yuzuyemo selile zirimo agaciro katari "# Umubare!". Mubyukuri, iyi niyo selile, amakuru avuye ku cyitegererezo batagize. Byaba byiza cyane niba byerekanwe na gato ubusa. Kuri izo ntego, dukoresha imiterere. Turagaragaza selile zose zimeza, usibye kumutwe. Mugihe uri muri tab ya Murugo, kanda ahanditse "Imitereregisi", uri muri "Style" ibikoresho bya Accery. Kurutonde rugaragara, hitamo "Gukora Amategeko ..." ikintu.



9. Mu idirishya rifungura, hitamo ubwoko bwa cound "imiterere gusa ingirangingo zirimo". Mu rwego rwa mbere, munsi yanditse "gakomangingo gusa ingirabuzimafatizo zidasanzwe," hitamo umwanya "ikosa". Ibikurikira, kanda kuri "imiterere ..." buto.



10. Mu idirishya ryiruka, jya kuri tab "font" hanyuma uhitemo umweru mumurima ukwiye. Nyuma yibi bikorwa, kanda kuri buto "OK".



11. Kuri buto hamwe nizina rimwe, kanda nyuma yo kugaruka mugukora idirishya.

Noneho dufite icyitegererezo cyarangiye kumupaka wagenwe mumashusho atandukanye.

Isomo: Imiterere imeze muri Excel

Uburyo 3: Gutegura mubihe byinshi ukoresheje formula

Nkuko ukoresheje akayunguruzo, ukoresheje formula urashobora guhitamo mubihe byinshi. Kurugero, fata imbonerahamwe imwe, kimwe nimbonerahamwe yubusa, aho ibisubizo bizaba ibisohoka, bimaze gukorwa kumibare. Tuzashyiraho imipaka yambere yumupaka wo hasi wo gutoranya hinjiza amafaranga 15,000, hamwe nubuzima bwa kabiri bwumupaka wo hejuru wimibare 20.000.

1. Injira mu nkingi zitandukanye, imiterere yimbibi yo kwipimisha.



2. Nko muburyo bwambere, bundi buryo butanga inkingi zubusa kumeza nshya hanyuma wandike formulane eshatu zijyanye nayo. Mu nkingi ya mbere tumenyekanisha imvugo ikurikira:

   = Indangagaciro (A2: A29; ntoya (niba ($ d $ 2 = C2: C29); C29); C29) - $ C $ 1) - Umurongo ($ C $ 1))

   Mu nkingi ikurikira, ihuza neza formulate imwe, gusa muguhindura ihuza ako kanya nyuma yizina ryumukoresha, urutonde rwinkingi zijyanye nuko dukeneye, kubijyanye na ikigereranyo hamwe nuburyo bwambere.

   Igihe cyose nyuma yo kwinjira, ntukibagirwe kubona Ctrl + shift + andika urufunguzo.



3. Ibyiza byubu buryo mbere yuko iyambere ni uko niba dushaka guhindura imipaka yicyitegererezo, ntabwo bikenewe guhindura formula ikomeye ubwayo, ubwayo ari ibibazo rwose. Birahagije mu nkingi y'ibihe ku rupapuro kugirango uhindure imipaka ku zikenerwa n'abakoresha. Ibisubizo byo guhitamo bizahita bihinduka.

Uburyo 4: Gutoranya

Mu buhungiro, hamwe nubufasha bwa formula idasanzwe, birashoboka kandi gushyira mu bikorwa guhitamo. Irasabwa mubihe bimwe na bimwe mugihe ukeneye kwerekana ishusho isanzwe idafite isesengura ryuzuye ryamakuru yose ya array.

1. Ibumoso bw'ameza Simbukira inkingi imwe. Mu kagari k'inkingi ikurikira, iherereye ahateganye na selile ya mbere hamwe namakuru yimeza, andika formula:

   = Ifata ()

   Iyi mikorere yerekana umubare usanzwe. Kugirango ubikore, kanda kuri buto yinjira.



2. Kugirango ukore inkingi yose yimibare idasanzwe, shyira indanga kuruhande rwiburyo bwiburyo bwa selile, ikubiyemo formula. Ikimenyetso cyuzuye kiragaragara. Ndarambuye hamwe na buto yimbeba yibumoso ugereranije nimbonerahamwe hamwe namakuru kugeza igihe kirangiye.



3. Ubu dufite udusengura urwego rwuzuyemo imibare idasanzwe. Ariko, ikubiyemo formula ya cradi. Tugomba gukorana nindangagaciro. Kugirango ukore ibi, kopi kumurongo wubusa iburyo. Hitamo urwego rwa selile zifite imibare idasanzwe. Biri muri tab "urugo", kanda kuri "kopi" kuri lente.



4. Turagaragaza inkingi irimo ubusa hanyuma ukande kanda iburyo, guhamagara ibikubiyemo. Muri "Shyiramo ibipimo" ibikoresho, hitamo "agaciro", cyerekanwe nka pictogrades hamwe nimibare.



5. Nyuma yibyo, mugihe uri muri tab "murugo", kanda kumashusho asanzwe amenyerewe ya "Sort hanyuma akayunguruzo". Murutonde rutonyanga, hagarika guhitamo kuri "Custom Socing".



6. Gutondekanya Igenamiterere Idirishya rikora. Witondere gushiraho amatiku ahateganye na parameter "amakuru yanjye arimo imitwe" niba cap irahari, ariko nta makimba. Muri "ubwoko bwa", bugaragaza izina ryiyi nkingi aho indangagaciro zandukuwe zirimo imibare idasanzwe. Muri "Stre", Kureka igenamiterere risanzwe. Mu "gahunda", urashobora guhitamo ibipimo nk '"kuzamuka" na "kumanuka". Ku cyitegererezo kidasanzwe, agaciro ntigifite. Nyuma yigenamiterere ryakozwe, kanda kuri buto "OK".



7. Nyuma yibyo, indangagaciro zose zameza zubatswe muburyo buzaza cyangwa kugabanuka kwimibare idasanzwe. Urashobora gufata umubare wimirongo iyo ariwo wose uva kumeza (5, 10, 12, 15, 15, nibindi) kandi birashobora gufatwa nkibisubizo byicyitegererezo kidasanzwe.

Isomo: Gutondekanya no Gushungura amakuru kuri Excel

Nkuko mubibona, icyitegererezo mumeza ya Excel birashobora gukorwa, byombi ukoresheje autofilter no gukoresha formulaire idasanzwe. Ku rubanza rwa mbere, ibisubizo bizerekanwa mumeza yinkomoko, no mu cya kabiri - ahantu hatandukanye. Birashoboka kubyara guhitamo, byombi na byinshi. Mubyongeyeho, urashobora gukora icyitegererezo kidasanzwe ukoresheje imikorere ifatika.