Ilanda idatha ku-Microsoft Excel

Lapho usebenza namatafula e-Excel, kaningi kufanele uwabakhethe ngokuya ngenkomba ethile noma ngokuya ngemibandela ethile. Uhlelo lungakwenza lokhu ngezindlela ezahlukahlukene kusetshenziswa amathuluzi amaningi. Ake sibheke ukuthi singasampula kanjani ku-Excel sisebenzisa izinketho ezahlukahlukene.

Ukuhlungwa

Ukukhethwa kwedatha kuqukethe inqubo yokukhetha kusuka ekuhlelweni okuvamile kwaleyo miphumela egcwalisa imibandela enikeziwe, ngokuphuma kwawo okulandelayo kweshidi njengohlu oluhlukile noma kuluhlu lwangempela.

Indlela yoku-1: sebenzisa i-autofilter ethuthukisiwe

Indlela elula yokwenza ukukhetha ukusebenzisa i-autofilter ethuthukisiwe. Cabanga ukuthi ungakwenza kanjani lokhu ngesibonelo esithile.

1. Khetha indawo eshidini, phakathi kwedatha ofuna ukuyikhetha. Kuthebhu "Ikhaya" chofoza inkinobho Hlunga nesihlungi. Itholakala kwibhlokhi yezilungiselelo. "Ukuhlela". Kuluhlu oluvula ngemuva kwalokhu, chofoza inkinobho "Hlunga".

   

   Kukhona ithuba lokwenza ngokuhlukile. Ukuze wenze lokhu, ngemuva kokukhetha indawo eshidini, iya kuthebhu "Idatha". Chofoza inkinobho "Hlunga"ebhalwe ethepheni eqenjini Hlunga nesihlungi.



2. Ngemuva kwalesi senzo, kuvela izithombe ezinhlokweni zetafula ukuze ziqale ukuhlunga ngesimo sonxantathu abancane abhekiswe phansi onqenqemeni lwesokudla lwamaseli. Sichofoza lesi sithonjana ekuhambeni kwekholomu esifuna ukukhetha kulo. Kumenyu evulayo, iya entweni "Izihlungi Zombhalo". Okulandelayo, khetha isikhundla "Isihlungi sangokwezifiso ...".



3. Iwindows lokuhlunga komsebenzisi liyasebenza. Kuyo, ungabeka umkhawulo lapho ukukhethwa kuzokwenziwa. Kuluhlu olwehlayo lwekholomu equkethe amaseli efomethi yenombolo esiyisebenzisa njengesibonelo, ungakhetha eyodwa yezinhlobo ezinhlanu zemibandela:
   • ilingana ne;
   • alilingani;
   • ngaphezulu;
   • ngaphezulu noma okulinganayo;
   • kancane.

   Masibekele isibonelo njengombandela wendlela yokuthi ukhethe kuphela amanani lapho inani lemali lemali lidlula ama-ruble ayi-10,000. Setha ukushintshela esikhundleni Okuningi. Faka inani enkambini efanele "10000". Ukuze wenze isenzo, chofoza inkinobho "Kulungile".



4. Njengoba ubona, ngemuva kokuhlunga kwakukhona imigqa kuphela lapho inani lemali lemali lidlula ama-ruble ayi-10,000.



5. Kepha kukholamu efanayo, singangeza isimo sesibili. Ukuze wenze lokhu, siphinda sibuyele ewindini lokuhlunga lomsebenzisi. Njengoba ukwazi ukubona, engxenyeni yayo engezansi kunenye futhi indlela yesimo nenkambu yokufaka ehambisanayo. Manje ake sibeke umkhawulo ophezulu wokukhethwa kuma-ruble ayi-15,000. Ukuze wenze lokhu, beka switch Ngaphansi, futhi Ensimini ngakwesokudla sifaka inani "15000".

   Ngaphezu kwalokho, kukhona nokushintsha kwesimo. Unezikhundla ezimbili "Futhi" futhi "NOMA". Ngokuzenzakalelayo, isethwe kwisikhundla sokuqala. Lokhu kusho ukuthi kuphela imigqa eyanelisa yomibili imikhawulo ezohlala isampula. Uma izobekwa ezikhundleni "NOMA"khona-ke kuzoba namagugu afanele noma yisiphi salezi zimo ezimbili. Kithi, udinga ukusetha ukushintshela ku "Futhi", okungukuthi, shiya lokhu kulungiselelwa njengezenzakalelayo. Ngemuva kokuthi wonke amanani afakiwe, chofoza inkinobho "Kulungile".



6. Manje etafuleni kunemigqa kuphela lapho inani lemali engenayo lingaphansi kwama-ruble ayi-10,000, kepha alidluli ama-ruble ayi-15,000.



7. Ngokufanayo, ungalungisa izihlungi kwamanye amakholomu. Ngasikhathi sinye, kungenzeka ukonga ukuhlunga ngokuya ngemibandela yangaphambilini esethwe kukholamu. Ngakho-ke, ake sibheke ukuthi ukuhlunga kwenziwa kanjani kumaseli ngefomethi yosuku. Chofoza isithonjana sokuhlunga kukholamu ehambisanayo. Vele ngokuchofoza ezintweni zohlu "Hlunga ngosuku" futhi Ukuhlunga ngokwezifiso.



8. Iwindi lomsebenzisi elisebenza ngokuzenzakalela liqala futhi. Senza ukukhetha kwemiphumela etafuleni kusuka ngoMeyi 4 kuya ku-Meyi 6, 2016 kubandakanywa. Ekushintsheni kwesimo sokukhetha, njengoba sibona, kunezinketho eziningi kakhulu kunokwifomethi yenombolo. Khetha isikhundla "Ngemuva noma ukufana". Ensimini ngakwesokudla, setha inani "04.05.2016". Ebhlokeni elingaphansi, setha ukushintshela kwisikhundla "Kuya noma kulingana no". Faka inani enkambini efanele "06.05.2016". Sishiya ukushintshana kokuhambisana kwesimo kusimo esizenzakalelayo - "Futhi". Ukuze usebenzise ukusefa kokusebenza, chofoza inkinobho "Kulungile".



9. Njengoba ukwazi ukubona, uhlu lwethu seluncishisiwe futhi. Manje kusale imigqa kuphela kuyo, lapho inani lemali lehluka ukusuka kuma-ruble ayi-10,000 kuye ku-15,000 wesikhathi kusukela ngoMeyi 4 kuya kuMeyi 6, 2016 okubandakanya.



10. Singakwazi ukusetha kabusha ukusefa kwelinye lamakholamu. Lokhu sizokwenza ngamanani entela. Chofoza isithonjana se-autofilter kukholamu ehambisanayo. Kuluhlu olwehliswayo, chofoza entweni Susa Isihlungi.



11. Njengoba ubona, ngemuva kwalezi zenzo, ukukhethwa ngenani lemali kuzokhutshazwa, futhi kuphela ukukhethwa ngezinsuku okuzohlala (kusuka ngo-05/04/2016 kuya ku-05/06/2016).



12. Kukhona enye ikholomu kuleli thebula - "Igama". Iqukethe idatha ngefomethi yombhalo. Ake sibheke ukuthi bakha kanjani ukukhetha kusetshenziswa ukuhlunga ngalezi zinani.

    Chofoza isithonjana sokuhlunga egameni lekholomu. Sihamba ngamagama ohlu "Izihlungi Zombhalo" futhi "Isihlungi sangokwezifiso ...".



13. Iwindi lomsebenzisi elisebenza ngokuzenzakalela livula futhi. Ake senze ukukhetha kwezinto "Izambane" futhi Inyama. Ebhlokini lokuqala, setha ukushintshela kwesimo "Okufanayo". Ensimini kwesokudla sayo sifaka igama "Izambane". I-switch block ephansi nayo ifakwa esikhundleni "Okufanayo". Ensimini ephambene nayo, yenza irekhodi - Inyama. Futhi-ke senza lokho esingazange sikwenze ngaphambili: setha ukushintshwa kwezimo zokuguquguquka "NOMA". Manje umugqa oqukethe noma yiziphi izimo ezibekiwe uzokhonjiswa esikrinini. Chofoza inkinobho "Kulungile".



14. Njengoba ubona, kusampula entsha kunemikhawulo ngosuku (kusuka ngomhla ka-05/04/2016 kuya ku-05/06/2006) nangegama (amazambane nenyama). Akukho mkhawulo enanini lemali engenayo.



15. Ungasusa ngokuphelele isihlungi ngezindlela ezifanayo owazisebenzisa ukuyifaka. Ngaphezu kwalokho, akunandaba ukuthi isetshenziswe yiphi indlela. Ukusetha ukusefa kabusha, kuthebhu "Idatha" chofoza inkinobho "Hlunga"elibekwe eqenjini Hlunga nesihlungi.

    

    Inketho yesibili ifaka ukuya kuthebhu "Ikhaya". Lapho, sichofoza inkinobho kubambo Hlunga nesihlungi ebhlokini "Ukuhlela". Kuluhlu olusetshenzisiwe, chofoza inkinobho "Hlunga".

Usebenzisa noma iyiphi yalezi zindlela ezimbili ezingenhla, ukuhlunga kuzosuswa, bese imiphumela yokuqokwa izosulwa. Okusho ukuthi, ithebula lizokhombisa lonke uhlu ledatha enalo.

Isifundo: Umsebenzi we-Autofilter ku-Excel

Indlela 2: ukusebenzisa ifomula elifanayo

Ungenza nokukhetha ngokusebenzisa ifomula yama-array eyinkimbinkimbi. Ngokungafani nenguqulo yangaphambilini, le ndlela ihlinzeka ngokukhishwa kwemiphumela etafuleni elihlukile.

1. Eshidi linye, yakha itafula elingenalutho elinamagama ekholamu afanayo kunhlokweni njengomthombo.



2. Khetha wonke amaseli angenalutho kwikholamu yokuqala yetafula elisha. Sibeka isikhombisi kumugqa wamafomula. Lapha nje kuzofakwa ifomula ekhiqiza okukhethiwe ngokuya ngemigomo ebekiwe. Sikhetha imigqa lapho inani lemali lemali lidlula ama-ruble ayi-15,000. Esibonelweni sethu esithile, ifomula yokufaka izobukeka kanjena:

   = INDEX (A2: A29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1)

   Ngokwemvelo, esimweni ngasinye, ikheli lamaseli nobubanzi kuzohluka. Kulesi sibonelo, ungaqhathanisa ifomula nezixhumanisi zomfanekiso futhi uzivumelanise nezidingo zakho.



3. Njengoba le yifomula yamalungu afanayo, ukuze uyisebenzise isebenza, kufanele ungacindezeli inkinobho Ngena, nesinqamuleli sekhibhodi I-Ctrl + Shift + Faka. Siyakwenza.



4. Ukukhetha ikholamu yesibili ngezinsuku nokubeka isikhombisi kubha yefomula, sethula inkulumo ethi:

   = INDEX (B2: B29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))

   Cindezela isinqamuleli sekhibhodi I-Ctrl + Shift + Faka.



5. Ngokufanayo, kwikholamu engenayo sifaka ifomula ngale ndlela elandelayo:

   = INDEX (C2: C29; LOW (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1)

   Futhi, ukuthayipha isinqamuleli sekhibhodi I-Ctrl + Shift + Faka.

   Kuzona zontathu lezi zigameko, okuwukuphela kokuvumelanisa kokushintshwa kwenani okuguqukayo, futhi yonke ifomula ifana ngokuphelele.



6. Njengoba ukwazi ukubona, itafula ligcwele idatha, kepha ukubukeka kwayo akubukeki ngokuphelele, ngaphezu kwalokho, amanani wusuku agcwaliswe ngokungalungile. Udinga ukulungisa la maphutha. Usuku alulungile ngoba ifomethi yeseli yekholomu ehambisanayo ivamile, futhi sidinga ukusetha ifomethi yosuku. Khetha ikholomu yonke, kufaka phakathi amaseli anamaphutha, bese uqhafaza ekukhetheni ngenkinobho yegundane elifanele. Kuluhlu olubonakala, iya ku "Ifomethi yeseli ...".



7. Ewindini lokufometha elivula, vula ithebhu "Inombolo". Ebhlokini "Amafomethi ezinombolo" qokomisa inani Usuku. Engxenyeni engakwesokudla yewindi, ungakhetha uhlobo olufunayo lokubonisa kosuku. Ngemuva kokuthi izilungiselelo zisethelwe, chofoza inkinobho "Kulungile".



8. Manje usuku luboniswa kahle. Kepha, njengoba sibona, yonke ingxenye engezansi yetafula igcwele amaseli aqukethe inani eliyiphutha "# NUMBER!". Eqinisweni, lawa ngamaseli lapho kwakungekho datha eyanele evela kusampula. Kungakhangeka ngokwengeziwe uma bekhonjiswa bengenalutho nhlobo. Ngalezi zinhloso sizosebenzisa ukufomatha okunemibandela. Khetha wonke amaseli asetafuleni ngaphandle kwenhloko. Ukuba kuthebhu "Ikhaya" chofoza inkinobho Ukufomatha okunemibandelaetholakala kwibhulokhi yamathuluzi Izitayela. Kuluhlu olubonakala, khetha "Dala umthetho ...".



9. Ewindini elivulayo, khetha uhlobo lomthetho "Fometha amaseli aqukethe kuphela". Ebhokisini lokuqala elibhalwe phansi "Fomula amaseli kuphela lesi simo esilandelayo eyiqiniso" khetha isikhundla "Amaphutha". Okulandelayo, chofoza inkinobho "Ifomathi ...".



10. Ewindows ukufometha okuqala, iya kuthebhu Ifonti Ensimini ehambisanayo, khetha okumhlophe. Ngemuva kwalezi zenzo, chofoza inkinobho "Kulungile".



11. Chofoza inkinobho enegama elifanayo ngemuva kokubuyela kuwindi lokudala izimo.

Manje sinesampula eyenziwe ngomumo yokuvinjelwa okucacisiwe etafuleni elihlelwe kahle kahle.

Isifundo: Ukufomatha okunemibandela ku-Excel

Indlela 3: amasampula ngokuya ngemibandela eminingana esebenzisa ifomula

Njengalapho usebenzisa isihlungi, usebenzisa ifomula, ungakhetha ngokuya ngemibandela eminingana. Isibonelo sizothatha lonke itafula lomthombo, futhi netafula elingenalutho lapho imiphumela izokhonjiswa khona, kufomethwe ukubalwa kwenombolo kanye nemibandela. Sibeka umkhawulo wokuqala kumkhawulo ophansi wokukhethwa kwemali engenayo yama-ruble ayi-15,000, nesimo sesibili emkhawulweni ophezulu wama-ruble angama-20,000.

1. Sifaka imibandela yemikhawulo yokukhethwa kukholamu ehlukile.



2. Njengendlela yangaphambilini, sikhetha amakholomu angenalutho wethebula elitsha ngamunye bese ufaka amafomula amathathu ahambelana nawo. Kwikholamu lokuqala, engeza le nkulumo elandelayo:

   = INDEX (A2: A29; LOW (IF (($ D $ 2 = C2: C29); LINE (C2: C29); ""); LINE (C2: C29) -LINE ($ C $ 1)) - ISILAYE ($ C $ 1))

   Kumakholamu alandelayo, sifaka amafomula afanayo, siguqula izixhumanisi ngokushesha ngemuva kwegama opharetha INDEX kukholamu ehambisanayo esiyidingayo, ngokufanekisa nendlela yangaphambilini.

   Njalo ngemuva kokungena, ungakhohlwa ukuthayipha inhlanganisela yokhiye I-Ctrl + Shift + Faka.



3. Ukusebenziseka kwale ndlela ngaphezulu kwalena eyedlule ukuthi uma sifuna ukuguqula imingcele yesampula,-ke asikho isidingo sokushintsha ifomula yamalungu afanayo uqobo, okuyinkinga enayo uqobo. Kwanele kukholamu yezimo eshidini ukushintsha izinombolo zomngcele kulokho okudingwa ngumsebenzisi. Imiphumela yokukhetha izoshintsha ngokushesha.

Indlela 4: amasampula angahleliwe

Ku-Excel usebenzisa ifomula ekhethekile QAPHELA Ukukhetha okungahleliwe nakho kungasebenza. Kuyadingeka ukuthi kukhiqizwe kwezinye izimo lapho kusetshenzwa ngenani elikhulu ledatha, lapho kunesidingo sokuveza isithombe esijwayelekile ngaphandle kokuhlaziywa okuphelele kwemininingwane yonke ekuhlu.

1. Ngakwesobunxele kwetafula yeqa ikholomu eyodwa. Esitokisini sekholomu elandelayo, ebekwe maqondana neseli lokuqala elinemininingwane yetafula, singena kwifomula:

   = IMANDLA ()

   Lo msebenzi ukhombisa inombolo engahleliwe. Ukuze uyisebenzise, ​​chofoza inkinobho ENTER.



2. Ukuze wenze ikholomu ephelele yezinombolo ezingahleliwe, beka ikhesa ekhoneni elingezansi kweseli esele liqukethe ifomula. Umaka wokugcwalisa uyavela. Sihudula phansi ngenkinobho yegundane yesobunxele icindezelwe ngokufana etafuleni ledatha kuze kube sekupheleni.



3. Manje sinohlu lwamaseli agcwele izinombolo ezingahleliwe. Kepha, iqukethe ifomula QAPHELA. Sidinga ukusebenza ngamanani amsulwa. Ukuze wenze lokhu, kopisha kwikholomu engenalutho ngakwesokudla. Khetha uhla lweseli olunezinombolo ezingahleliwe. Itholakala kuthebhu "Ikhaya"chofoza isithonjana Kopisha ekhasethini.



4. Khetha ikholomu engenalutho bese uqhafaza kwesokudla, uheha imenyu yokuqukethwe. Eqenjini lamathuluzi Faka Izinketho khetha into "Amanani"kuboniswe njenge-pictograph enezinombolo.



5. Ngemuva kwalokho, ukuba kuthebhu "Ikhaya", chofoza isithonjana esivele sisazi Hlunga nesihlungi. Kuluhlu olwehliswayo, yeka ukukhetha lapho Uhlobo Lwezifiso.



6. Iwindi lezilungiselelo zokuhlela lisebenza lasebenza. Qiniseka ukuthi ubheka ibhokisi eliseduze kwepharamitha "Imininingwane yami iqukethe izihloko"uma kukhona isigqoko kepha akukho okukhonjwa. Ensimini Hlunga khombisa igama lekholomu eliqukethe amanani okopiwe wezinombolo ezingahleliwe. Ensimini "Hlunga" shiya izilungiselelo ezizenzakalelayo. Ensimini "I-oda" ungakhetha ipharamitha njenge "Iyenyuka"kanjalo futhi Yehla ". Ukuthola amasampula okungahleliwe, lokhu akunandaba. Ngemuva kokuthi izilungiselelo zenziwe, chofoza inkinobho "Kulungile".



7. Ngemuva kwalokho, wonke amanani etafula ahlelwe ngokukhuphuka noma ukwehla kwe-oda lezinombolo ezingahleliwe. Ungathatha noma iyiphi inombolo yemigqa yokuqala kusuka etafuleni (5, 10, 12, 15, njll.) Futhi zingabhekwa njengomphumela wesampula engahleliwe.

Isifundo: Hlunga futhi uhlunge idatha ku-Excel

Njengoba ukwazi ukubona, ukukhethwa kuspredishithi se-Excel kungenziwa ngokusebenzisa i-autofilter noma ukusebenzisa amafomula akhethekile. Esimweni sokuqala, umphumela uzokhonjiswa kuthebula lokuqala, kuthi owesibili - endaweni ehlukile. Kungenzeka wenze ukukhetha, ngesimo esisodwa nakwabambalwa. Ungase futhi ukhethe ngokungahleliwe usebenzisa umsebenzi QAPHELA.