Lapho usebenza namatafula e-Excel, kuvame ukudingeka ukuba ukhethe ngokulandela umgomo othile noma ngezimo eziningana. Lolu hlelo lungenza lokhu ngezindlela ezehlukene besebenzisa amathuluzi amaningi. Ake sibone ukuthi singasampula kanjani ku-Excel besebenzisa izinketho ezihlukahlukene.
Isampuli
Isampuli yedatha ifaka inqubo yokukhethwa kusukela emphakathini jikelele walezo zinto ezanelisa izimo ezicacisiwe, ngokukhishwa kwazo okulandelayo kushidi kuhlu oluhlukile noma ebangeni lokuqala.
Indlela 1: sebenzisa i-autofilter ethuthukile
Indlela elula yokwenza ukhetho ukusebenzisa i-autofilter ephakeme. Cabanga ukuthi ungakwenza kanjani lokhu ngesibonelo esithile.
- Khetha indawo eshadini, phakathi kwedatha ofuna ukuyisampula. Kuthebhu "Ikhaya" chofoza inkinobho "Hlunga futhi uhlunge". Ifakwa ezicini zokuvimba. Ukuhlela. Kulolu hlu oluvula emva kwalokhu, chofoza inkinobho. "Hlunga".
Kungenzeka ukwenza ngokuhlukile. Ukuze wenze lokhu, ngemuva kokukhetha indawo kushidi, hamba kuthebhu "Idatha". Chofoza inkinobho "Hlunga"okuthunyelwe kwi-tape eqenjini "Hlunga futhi uhlunge".
- Ngemuva kwalesi senzo, izithonjana ezivela etafuleni ezizoqala ukuhlunga ngesimo sezintambo ezincane ziphendukele phansi ngakwesokunene kwamaseli. Chofoza kulesi sithonjana esihlokweni sekholomu esifuna ukukhetha. Emenyu yokuqala, chofoza entweni "Izihlungi zombhalo". Okulandelayo, khetha isikhundla "Isihlungi sangokwezifiso ...".
- Iwindi lokuhlunga ngokwezifiso livuliwe. Kungenzeka ukusetha umkhawulo lapho ukhetho luzokwenziwa khona. Kuhlu lokudonsa phansi lwekholomu equkethe amangqamuzana omumo wefoni, esisebenzisa njengesibonelo, ungakhetha eyodwa yezinhlobo ezinhlanu zezimo:
- ulingana;
- akulingani;
- okuningi;
- okukhulu noma elinganayo;
- kancane
Masibeke isimo njengesibonelo ukuze sikwazi ukhethe kuphela amanani lapho inani lemali engenayo lidlula ama-ruble angu-10 000. Setha inkinobho ukuma "Okunye". Faka inani kumkhawulo olungile "10000". Ukuze wenze isenzo, chofoza inkinobho. "Kulungile".
- Njengoba ubona, emva kokuhlunga, kunezinamba kuphela lapho inani lemali engenayo lidlula ama-ruble angu-10 000.
- Kodwa kukholomu efanayo singakwazi ukwengeza isimo sesibili. Ukwenza lokhu, buyela ewindini lokuhlunga lesiko. Njengoba ubona, engxenyeni yayo engezansi kunomunye umshini wokushintsha kanye nensimu yokufaka ehambisanayo. Ake sibeke umkhawulo wokukhetha ongaphezulu wama-ruble angu-15 000. Ukuze wenze lokhu, setha inkinobho endaweni "Okuncane", futhi ensimini kuya kwesokudla faka inani "15000".
Ngaphezu kwalokho, kunezimo zokushintsha. Unezikhundla ezimbili "Futhi" futhi "NOMA". Ngokuzenzakalelayo isethwe esimweni sokuqala. Lokhu kusho ukuthi imigqa kuphela eyanelisa izibopho zombili izohlala ekukhethweni. Uma efakwa esikhundleni "NOMA", khona-ke kuyoba khona amanani afanelekile kulezi zimo ezimbili. Esimweni sethu, udinga ukusetha ushintsho "Futhi", okungukuthi, shiya lokhu kulungiselelwa okuzenzakalelayo. Ngemuva kokuthi wonke amanani afakiwe, chofoza inkinobho. "Kulungile".
- Manje itafula linemigqa kuphela lapho inani lemali engenayo lingaphansi kwama-ruble angu-10 000, kodwa alidluli ama-ruble angu-15 000.
- Ngokufanayo, ungahlela izihlungi kwezinye izinsika. Ngesikhathi esifanayo, kungenzeka futhi ukulondoloza ukuhlunga ngemibandela yangaphambilini ebekiwe kumakholomu. Ngakho-ke, ake sibone ukuthi ukhetho lwenziwa kanjani usebenzisa isihlungi samaseli kufomethi yosuku. Chofoza kwisithonjana sokuhlunga kukholomu ehambelanayo. Chofoza ngokweqile ezintweni ohlwini. "Hlunga ngosuku" futhi "Isihlungi Ngokwezifiso".
- Iwindi le-autofilter yangokwezifiso liqala futhi. Yenza ukhetho lwemiphumela etafuleni kusukela ngo-4 kuya ku-6 Meyi 2016 kubandakanya. Esikhathini sokukhetha inkinobho yokukhetha, njengoba ubona, kunezinketho ezingaphezulu kakhulu kunefomethi yenombolo. Khetha isikhundla "Emva noma ngokulinganayo". Ensimini ngakwesokudla, setha inani "04.05.2016". Esigodini esingaphansi, setha inkinobho endaweni "Kuzo noma kulingana no". Faka inani ngakwesokudla "06.05.2016". Isimo sokuhambisana nesimo sishiywe endaweni ezenzakalelayo - "Futhi". Ukuze ufake ukuhlunga esenzweni, chofoza inkinobho "Kulungile".
- Njengoba ungabona, uhlu lwethu luye lwadlula nakakhulu. Manje kuphela imigqa esele kuyo, lapho inani lemali engenayo lihluka kusuka ku-10 000 kuya ku-15 000 rubleki kusukela ngo-04.05 kuya ku-06.05.2016 kubandakanya.
- Singasetha kabusha ukuhlunga kwelinye lamakholomu. Yenzani lokhu ngamanani wenzuzo. Chofoza kusithonjana se-autofilter kukholomu ehambelanayo. Kuhlu lokudonsa phansi, chofoza entweni. "Susa i-Filter".
- Njengoba ungabona, emva kwalezi zenzo, isampula ngemali yenzuzo izokhutshazwa, futhi kuphela ukukhethwa ngamalanga kuzohlala (kusukela ngo-04.05.2016 kuya ku-06.05.2016).
- Leli thebula linenye ikholomu - "Igama". Iqukethe idatha ekufomeni yombhalo. Ake sibone ukuthi ungakha kanjani isampula ngokusebenzisa ukuhlunga ngalezi zimiso.
Chofoza kwisithonjana sokuhlunga egameni lekholomu. Ukulandelana ngokwehlukana ngokusebenzisa uhlu "Izihlungi zombhalo" futhi "Isihlungi sangokwezifiso ...".
- Iwindi le-autofilter lomsebenzisi livula futhi. Masenze isampula ngegama. "Amazambane" futhi "Inyama". Esikhathini sokuqala ibhulogi, inkinobho yesimo isethelwe "Kulingana". Ensimini kuya kwesokudla yena angene igama "Amazambane". Ukushintshwa kwebhokisi elingaphansi kufakwe esikhundleni "Kulingana". Ensimini esibhekene naye senza ukungena - "Inyama". Futhi-ke senza lokho esingakaze senze ngaphambili: sibeka inkinobho yokuhambelana endaweni "NOMA". Manje umugqa oqukethe noma yiziphi izimo ezicacisiwe uzoboniswa esibukweni. Chofoza inkinobho "Kulungile".
- Njengoba ubona, esampula entsha kukhona ukulinganiselwa ngosuku (kusuka 04/05/2016 kuya 05/06/2016) futhi ngegama (amazambane nenyama). Akukho mkhawulo kunani lemali engenayo.
- Ungasusa ngokuphelele isihlungi usebenzisa izindlela ezifanayo ezisetshenziselwa ukufaka. Futhi kungakhathaliseki ukuthi iyiphi indlela esetshenziswa ngayo. Ukusetha kabusha ukuhlunga, ekubeni kuthebhu "Idatha" chofoza inkinobho "Hlunga"okubanjwe eqenjini "Hlunga futhi uhlunge".
Ukhetho lwesibili luhilela ukushintshela kuthebhu "Ikhaya". Lapho senza ngokuchofoza ku-ribbon inkinobho. "Hlunga futhi uhlunge" ku-block Ukuhlela. Kuluhlu olusebenziwe chofoza inkinobho. "Hlunga".
Uma usebenzisa enye yezindlela ezimbili ezingenhla, ukuhlunga kuzosuswa, futhi imiphumela yesampuli izosuswa. Okusho ukuthi, itafula lizobonisa yonke imininingwane yedatha enayo.
Isifundo: Umsebenzi wokuhlunga ngokuzenzakalela ku-Excel
Indlela 2: Sebenzisa ifomula ye-Array
Ungenza futhi ukhetho ngokusebenzisa ifomula yamathuluzi amaningi. Ngokungafani nenguqulo yangaphambilini, le ndlela inikeza ukukhishwa komphumela kuthebula elihlukile.
- Esikhatsini esifanayo, yakha itafula elingenalutho namagama ekholomu afanayo ekhanda njengekhodi yomthombo.
- Khetha wonke amaseli angenalutho ekholomu yokuqala yetafula elisha. Setha isikhombisi kubha yefomula. Nje lapha ifomula izongeniswa, isampula ngokusho kwezinqubo ezicacisiwe. Sizokhetha imigqa, inani lemali engenayo okungaphezu kwama-ruble angu-15 000. Esikhathini sethu esithile, ifomula oyifakayo izobukeka kanje:
= INDEX (A2: A29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Ngokwemvelo, kwimeko ngayinye ikheli lamaseli namabanga lizohluka. Kulesi sibonelo, ungaqhathanisa ifomula nezixhumanisi emfanekisweni bese uzivumelanisa nezidingo zakho.
- Njengoba lokhu kuyindlela yokwakha, ukuze uyisebenzise ngokusebenza, akudingeki ucindezele inkinobho Ngenakanye nesinqamuleli sekhibhodi Ctrl + Shift + Faka. Siyakwenza.
- Ukukhetha ikholomu yesibili ngezinsuku bese ubeka isikhombisi kubha yamasethingi, faka inkulumo elandelayo:
= INDEX (B2: B29; LOWEST (IF (15000 <= C2: C29; STR2 (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Hitha isinqamuleli sekhibhodi Ctrl + Shift + Faka.
- Ngokufanayo, kukholomu ngemali engenayo ifomula elilandelayo:
= INDEX (C2: C29; LOWEST (IF (15000 <= C2: C29; STRING (C2: C29); ""); STRING () - STRING ($ C $ 1)) - STRING ($ C $ 1))Futhi, siyiphayipha inqamuleli Ctrl + Shift + Faka.
Kuwo wonke amacala amathathu, inani lokuqala lokuhlelwa kwezixhumanisi lishintsha, futhi zonke ezinye amafomula zifana ngokuphelele.
- Njengoba ubona, ithebula ligcwele idatha, kodwa ukubukeka kwayo akukona okukhangayo, ngaphandle kwalokho, amanani wosuku agcwaliswa kuyo ngokungalungile. Kubalulekile ukulungisa lezi ziphutha. Usuku olungalungile lungenxa yokuthi ifomethi yamaseli ekholomu ehambelana nayo evamile, futhi sidinga ukusetha ifomethi yosuku. Khetha lonke ikholomu, kufaka phakathi amaseli anamaphutha, bese uchofoze ekukhetheni inkinobho yegundane efanele. Kulolu hlu oluvela entweni "Ifomethi yeselula ...".
- Ewindini lokufometha elivula, vula ithebhu "Inombolo". Ku-block "Amafomu Amanani" khetha inani "Usuku". Ngakwesokudla ngakwesokudla kwewindi, ungakhetha uhlobo oluthandayo lokubonisa usuku. Emva kokusethwa kwezilungiselelo, chofoza inkinobho. "Kulungile".
- Manje usuku luboniswa ngendlela efanele. Kodwa, njengoba ubona, yonke into etafuleni igcwele amangqamuzana aqukethe inani eliyiphutha. "#NUM!". Eqinisweni, lawa angamaseli angenayo idatha eyanele kusuka kusampula. Kungaba ngcono nakakhulu uma beboniswa konke okungenalutho. Ngale njongo, sisebenzisa ukufometha okunemibandela. Khetha wonke amaseli etafuleni ngaphandle kwesihloko. Ukuba ngaphakathi kuthebhu "Ikhaya" chofoza inkinobho "Ukufometha okunemibandela"okusemabhodleleni amathuluzi "Izitayela". Ohlu oluvela, khetha into "Dala umthetho ...".
- Ewindini evula, khetha uhlobo lokubusa "Fometha amaseli kuphela aqukethe". Ensimini yokuqala ngaphansi kokubhaliswa "Fometha amaseli kuphela lapho isimo esilandelayo sifinyelelwa" khetha isikhundla "Amaphutha". Okulandelayo, chofoza inkinobho "Fometha ...".
- Ewindini lokufometha elivula, iya kuthebhu "Ifonti" bese ukhetha umbala omhlophe ensimini ehambelanayo. Ngemva kwalezi zenzo, chofoza inkinobho. "Kulungile".
- Chofoza inkinobho ngegama elifanayo ngqo ngemva kokubuyela ewindini lokubeka isimo.
Manje sine isampuli esenziwe ngokulungele umkhawulo ocacisiwe kuthebula elihlukile elihlelwe kahle.
Isifundo: Ukufometha okunemibandela ku-Excel
Indlela 3: isampula ngezimo eziningana usebenzisa ifomula
Njengalapho usebenzisa isihlungi, usebenzisa ifomula, ungasampula ngezimo eziningana. Isibonelo, ake sithathe wonke amathebula ewumthombo owodwa, kanye netafula elingenalutho lapho imiphumela izoboniswa khona, ngokufometha kwesimo senombolo nesimo esivele senziwe. Beka umkhawulo wokuqala kumkhawulo ophansi wokukhethwa kwemali ye-ruble engu-15 000, futhi isimo sesibili ngumkhawulo ongaphezulu wama-ruble angu-20,000.
- Sifaka kukholomu ehlukile imibandela yomngcele yesampula.
- Njengendlela yangaphambilini, khetha ngokuhlukile ukhethe amakholomu angenalutho etafuleni elisha bese ufaka amafomula amathathu afanayo kuwo. Ekholomu yokuqala faka inkulumo elandelayo:
= INDEX (A2: A29; LOWEST (IF (($ D $ 2 = C2: C29); STRING (C2: C29); ""); STRING (C2: C29) - STRING ($ C $ 1)) - STRING ($ C $ 1))Kulamakholomu alandelayo sifaka amafomula afanayo, kuphela ngokushintsha izixhumanisi ngokushesha ngemuva kwegama lomqhubi. INDEX kuya kumakholomu ahambelanayo esiwadingayo, ngokufanisa nendlela yangaphambilini.
Njalo emva kokungena ungakhohlwa ukuthayipha ukhiye wezinqamuleli Ctrl + Shift + Faka.
- Inzuzo yale ndlela ngaphezu kweyaphambilini yukuthi uma sifuna ukushintsha imingcele yesampula, ngeke sidinge ukushintsha ifomula yamagama ngokwalo, yona ngokwayo iyinkinga enkulu. Kwanele ukushintsha izinombolo zomngcele kukholomu yemibandela kushidi kulabo okudingwa ngumsebenzisi. Imiphumela yokukhetha izoshintsha ngokushesha ngokuzenzakalelayo.
Indlela 4: isampuli esinqunyiwe
E-Excel ngefomula ekhethekile I-SLCIS Ukukhetha okungahleliwe nakho kungasetshenziswa. Kudingeka kwenziwe kwezinye izimo uma usebenza ngemininingwane enkulu, uma udinga ukuveza isithombe jikelele ngaphandle kokuhlaziywa okuphelele kwawo wonke idatha ohlwini.
- Ngakwesokunxele kwethebula, weqa ikholomu eyodwa. Esikhathini sekholomu elilandelayo, elibhekene neseli lokuqala nedatha etafuleni, faka ifomula:
= RAND ()Lo msebenzi ubonisa inombolo ehleliwe. Ukuze uyisebenze, chofoza inkinobho ENTER.
- Ukuze wenze ikholomu yonke yezibalo ezingahleliwe, setha isikhombisi ekhoneni elingaphansi lesokudla leseli, esivele liqukethe ifomula. Uphawu lokugcwalisa luvela. Yidonsa phansi inkinobho yegundane lesokunxele igxilwe ngokufana netafula nedatha ekupheleni kwayo.
- Manje sinezinhlobo zamaseli agcwele izinombolo ezingahleliwe. Kodwa, iqukethe ifomula I-SLCIS. Kudingeka sisebenze ngamanani ahlanzekile. Ukuze wenze lokhu, kopisha kukholomu engenalutho ngakwesokudla. Khetha ububanzi bamaseli anezinombolo ezingahleliwe. Kutholakala kuthebhu "Ikhaya", chofoza kusithonjana "Kopisha" kwi-tape.
- Khetha ikholomu engenalutho bese uchofoza inkinobho yesokudla yegundane, ucela imenyu yesimo. Eqoqweni lamathuluzi "Izinketho zokufaka" khetha into "Amanani"iboniswe njenge-pictogram enezinombolo.
- Ngemuva kwalokho, ekubeni kuthebhu "Ikhaya", chofoza isithonjana esivele sisajwayele "Hlunga futhi uhlunge". Kuhlu lokudonsa, hlala ukukhethwa kwento "Hlunga ngezifiso".
- Iwindi yokuhlela yezilungiselelo ivuliwe. Qinisekisa ukuthi ubuka ibhokisi eceleni kwepharamitha. "Idatha yami inezihloko"uma kukhona i-cap, kodwa ayikho uphawu lokuhlola. Ensimini "Hlunga nge" cacisa igama lekholomu equkethe amanani akopishiwe wezinombolo ezingahleliwe. Ensimini "Hlunga" shiya izilungiselelo ezizenzakalelayo. Ensimini "Hlela" ungakhetha ukhetho njenge "Ukukhuphuka"kanjalo futhi "Yehla". Ukuze uthole isampula engahleliwe, lokhu akunandaba. Ngemuva kokuthi izilungiselelo zenziwe, chofoza inkinobho. "Kulungile".
- Emva kwalokho, zonke izindinganiso zetafula zihlelwe ngokunyuka noma ukwehla kwenombolo yezibalo ezingahleliwe. Ungathatha noma iyiphi inamba yemigqa yokuqala kusukela etafuleni (5, 10, 12, 15, njll) futhi ingabhekwa njengomphumela wesampula okungahleliwe.
Isifundo: Hlunga bese uhlunga idatha ku-Excel
Njengoba ubona, isampula ku-spreadsheet ye-Excel singenziwa, njengokusiza kokuhlunga okuzenzakalelayo, nangokusebenzisa amafomula akhethekile. Esikhathini sokuqala, umphumela uzoboniswa etafuleni lokuqala, futhi okwesibili - endaweni ehlukile. Kukhona ithuba lokukhetha, kokubili esimweni esisodwa, futhi eziningana. Ngaphezu kwalokho, ungenza isampula okungahleliwe ngokusebenzisa lo msebenzi I-SLCIS.
