Ungayenza kanjani isampula ku-Excel: 4 Imfashini Yokusebenza

Lapho usebenza ngamatafula we-Excel, kuvame ukudingeka ukufeza ukukhetha ngenqubo ethile noma ngezimo eziningana. Uhlelo lungenza lokhu ngezindlela ezahlukahlukene usebenzisa uchungechunge lwamathuluzi. Ake sibheke ukuthi ungayenza kanjani isampula ku-Offelle usebenzisa izinketho eziningi.

Ukuxhasa

Isampula yedatha iqukethe inqubo yokukhetha kusuka kuhlu oluphelele lwaleyo miphumela eyanelisa izimo ezibekiwe, zilandelwa ukuphuma kwazo eshile ohlwini oluhlukile noma ebangeni lomthombo.

Indlela 1: Faka isicelo se-autofilted eseluliwe

Indlela elula yokwenza ukukhetha ukusetshenziswa kwe-autofilter enwetshiwe. Cabanga ukuthi ungakwenza kanjani lokhu kusibonelo esithile.

1. Khetha indawo eshidini, phakathi kwedatha ofuna ngayo ukwenza isampula. Ethebhu yasekhaya, chofoza inkinobho ethi "Hort and Filter". Ibekwe ku-Inkundla Yezilungiselelo Zokuhlela. Uhlu oluvula ngemuva kwalolu hlu, chofoza inkinobho ethi "Filter".

   

   Kukhona ithuba lokwenza futhi ngokuhlukile. Ukuze wenze lokhu, ngemuva kokukhetha indawo eshidini, sithuthela kuthebhu "yedatha". Chofoza inkinobho ethi "Filter", ebekwe ethepheni eqenjini leqembu le-HORD nehlunga.



2. Ngemuva kwalesi senzo esihlokweni setafula, ama-piccograms abonakala eqala ukuhlunga ngesimo semiphetho yonxantathu abancane onqenqemeni lwesokudla lwamaseli. Chofoza kulesi sithonjana esihlokweni saleyo kholamu, ngakho-ke sifisa ukwenza isampula. Emenyu yemenyu yokuqalisa imenyu, dlula entweni ethi "Umbhalo Wezihlungi". Okulandelayo, khetha isikhundla "Isihlungi esenziwe ngezifiso ...".



3. Iwindi lokuhlunga lomsebenzisi lisebenza. Kuyo, ungabeka umkhawulo lapho ukukhethwa kuzokwenziwa khona. Ohlwini olwehliswayo lwekholomu yeseli lezinombolo, esiyisebenzisa ngokwesibonelo, ungakhetha enye yezinhlobo ezinhlanu zemibandela:
   • lilingana;
   • ayilingani;
   • Okuningi;
   • okuningi noma okulinganayo;
   • mncane.

   Masibeke isimo njengesibonelo ukuze ukhethe amanani kuphela lapho inani lemali engenayo lidlula khona ama-ruble ayi-10,000. Sisungula ukushintshela esimweni esithi "ngaphezulu". Ensimini efanele kufanelekile ukubaluleka "10,000". Ukwenza isenzo, chofoza inkinobho ethi "Kulungile".



4. Njengoba sibona, ngemuva kokuhlungwa, kuphela imigqa kuphela eyaqhubeka, lapho inani lemali engenayo lidlula ama-ruble ayi-10,000.



5. Kepha kwikholomu efanayo singangeza isimo sesibili. Ukuze wenze lokhu, buyela ewindini le-Filtration yangokwezifiso. Njengoba sikwazi ukubona, engxenyeni yalo engezansi kunenye isimo sokushintshana kanye nenkambu ehambisanayo yokufaka. Manje ake sifake umngcele wokukhetha ophezulu wama-ruble ayi-15,000. Ukuze wenze lokhu, setha ukushintshela esimweni esithi "Esincane", nasensimini ngakwesokudla esifanele inani elithi "15000".

   Ngaphezu kwalokho, kusekhona ukushintshwa kwezimo. Unezinhlinzeko ezimbili "futhi" no "noma". Ngokuzenzakalelayo, ifakwe endaweni yokuqala. Lokhu kusho ukuthi kuphela imigqa ezosala kusampula eyanelisa zombili imikhawulo. Uma isethwe ku "noma" isikhundla, khona-ke amanani alungele noma yiziphi izimo ezimbili ezosala. Kuthu, udinga ukusetha ukushintshela esimweni esiya ku- "no" isikhundla, okungukuthi, shiya lokhu kulungiswa okuzenzakalelayo. Ngemuva kwawo wonke amanani afakiwe, chofoza inkinobho ethi OK.



6. Manje imigqa kuphela eyaqhubeka etafuleni, lapho inani lemali engenayo lingabi ngaphansi kwama-ruble ayi-10,000, kepha alwedluli ama-ruble ayi-15,000.



7. Ngokufanayo, ungahlela izihlungi kwamanye amakholamu. Kungenzeka ukugcina ukuhlunga kanye nezimo zangaphambili ezisethwe kwikholomu. Ngakho-ke, ake sibheke ukuthi ukukhethwa kuthathwa kanjani kusetshenziswa isihlungi sefomethi yezinsuku. Chofoza isithonjana se-Filtration kwikholomu ehambisanayo. Njalo ngokuchofoza kuhlu lwe- "Filter by Date" kanye nesihlungi.



8. Iwindi le-Custom AutoFilting Run liqala futhi. Yenza ukukhethwa kwemiphumela etafuleni kusuka ku-4 kuye ku-6 Meyi 2016 kufaka phakathi. Ekushintsheni kokukhethwa, njengoba ubona, izinketho eziningi kakhulu kunefomethi yenombolo. Khetha isikhundla "ngemuva noma okulinganayo". Ensimini ngakwesokudla, setha inani "04.05.2016". Ebhlokini elingezansi, sibeka i-switch to the "kuze kube noma ngokulinganayo" isikhundla. Ensimini efanele, faka inani "06.05.2016". Ukuhambelana Kwezimo Ukushintshana Kwekhefu Esimweni esizenzakalelayo - "kanye". Ukuze ufake isicelo sokuhlunga esenzweni, cindezela inkinobho ethi "Kulungile".



9. Njengoba ubona, uhlu lwethu selunqabile ngisho nangaphezulu. Manje imigqa kuphela eshiywe kuyo, lapho inani lemali engenayo liyahlukahluka kusuka kuma-ruble ayi-10,000 kuya kwayi-15,000 isikhathi kusuka ku-04.05 kuya ku-06.05.2016 kufaka phakathi.



10. Singasetha kabusha ukuhlunga kwelinye ikhomu. Ake sikwenze kumanani wemali engenayo. Chofoza isithonjana se-AutoFilter kwikholamu ehambisanayo. Ohlwini olwehliswayo, chofoza into ethi "Delete Filter".



11. Njengoba ubona, ngemuva kwalezi zenzo, isampula ngenani lemali engenayo lizokhutshazwa, kepha lizothatha kuphela ukukhetha ngosuku (kusuka ngo-04/05/2016 kuya ku-06.05.2016 kuya ku-06.05.2016).



12. Leli thebula linenye futhi ikholomu - "Igama". Inedatha ngefomethi yombhalo. Ake sibheke ukuthi singakha kanjani isampula sisebenzisa ukuhlunga ngala mavelu.

    Chofoza isithonjana sokuhlunga egameni lekholomu. Ngokuqhubekayo iya ngamagama ohlu "izihlungi zombhalo" kanye "nesihlungi esenziwe ngezifiso ...".



13. Iwindi lomsebenzisi autofilter livula futhi. Masenze isampula ngamagama athi "amazambane" nelithi "inyama". Ebhlokeni lokuqala, izimo zishintsha zisethwe esikhundleni esithi "alinganayo". Ensimini kwesokudla kwawo kufanelekile igama elithi "amazambane". Ukushintshwa kweyunithi ephansi kuzobeka nesikhundla "esilinganayo". Ensimini okuphambene nalokho, ngenza irekhodi - "inyama". Futhi manje senza lokho ababengazange bakwenze phambilini: Setha ukushintshwa kokuhambisana esikhundleni "noma". Manje umugqa oqukethe noma yiziphi zemibandela ebekiwe ezokhonjiswa esikrinini. Chofoza inkinobho ethi "KULUNGILE".



14. Njengoba ubona, kunemikhawulo ngosuku kusampula elisha (kusuka ku-04.05.2016 kuya ku-06.05.2016) nangegama (amazambane nenyama). Akukho mkhawulo ngenani lemali engenayo.



15. Ungasusa ngokuphelele isihlungi ngezindlela ezifanayo ezazisetshenziselwa ukuyifaka. Futhi akunandaba ukuthi wasetshenziswa yiphi indlela. Ukusetha kabusha ukuhlunga, ngenkathi kuthebhu ethi "Idatha", chofoza inkinobho ethi "Filter", etholakala kwiqembu "lokuhlunga nehlunga".

    

    Inketho yesibili ifaka ukuguqulwa kwethebhu "Yasekhaya". Senza ngokuchofoza ku-Ribbon kwinkinobho ethi "Hort and Filter" kuyunithi yokuhlela. Uhlu olwenziwe aculwe, chofoza inkinobho ethi "Filter".

Lapho usebenzisa noma yiziphi zalezi zindlela ezimbili ezingenhla, ukuhlunga kuzosuswa, futhi imiphumela yesampula iyahlanzwa. Okungukuthi, itafula lizokhonjiswa lonke uhlu lwemininingwane enayo.

Isifundo: Umsebenzi wokuhlunga okuzenzakalelayo ku-Excel

Indlela 2: Ukusetshenziswa kwefomula yama-array

Yenza ukukhetha kungasebenzisa nefomula eyinkimbinkimbi ye-ARAY. Ngokungafani nenguqulo yangaphambilini, le ndlela ihlinzeka ngokukhishwa komphumela etafuleni elihlukile.

1. Eshidi elifanayo, sakha itafula elingenalutho elinamagama afanayo amakholomu anhlokweni njengoMthombo.



2. Yazisa wonke amaseli angenalutho wekholomu yokuqala yetafula elisha. Faka isikhombisi entanjeni yefomula. Lapha nje kuzofakwa yifomula ekhiqiza isampula kwimibandela ebekiwe. Sizokhetha umugqa, inani lemali engenayo edlula ama-ruble ayi-15,000. Isibonelo sethu esithile, ifomula elethuliwe lizobukeka ngalendlela elandelayo:

   = Inkomba (A2: A29; incane (uma (15000

   Ngokwemvelo, esimweni ngasinye, ikheli lamaseli namabanga kuzoba ngowakho. Kulesi sibonelo, ungaqhathanisa ifomula nezixhumanisi kulo mfanekiso futhi uzivumelanise nezidingo zakho.



3. Njengoba leli yifomula elifanayo, ukuze lisebenzise isenzo, udinga ukucindezela inkinobho ethi Faka, kepha i-Ctrl + Shift + Enter key inhlanganisela. Siyakwenza.



4. Njengoba uqokomise ikholomu yesibili ngezinsuku nokufaka isikhombisi entanjeni yefomula, sethula le nkulumo elandelayo:

   = Inkomba (B2: B29; okuncane kakhulu (uma (15000

   Chofoza inhlanganisela yokhiye we-CTRL + Shift + Enter.



5. Ngokufanayo, kukholamu enokuthola imali, faka ifomula yokuqukethwe okulandelayo:

   = Inkomba (C2: C29; incane (uma (15000

   Futhi, thayipha inhlanganisela yokhiye.

   Kuwo wonke amacala amathathu, kuphela inani lokuqala lezixhumanisi liyashintsha, futhi yonke ifomula ifana ngokuphelele.



6. Njengoba sibona, itafula ligcwele idatha, kepha ukubukeka kwalo akukhanga ngokuphelele, ngaphezu kwalokho, amanani wezinsuku agcwalisiwe kuwo. Kuyadingeka ukulungisa lokhu kushiyeka. Ukungalungile kwalolu suku kuhlobene neqiniso lokuthi ifomethi lamaseli wekholomu ehambisanayo livamile, futhi sidinga ukusetha ifomethi yosuku. Sigqamisa ikholomu yonke, kufaka phakathi amaseli anamaphutha, bese uqhafaza ekuqokomeni inkinobho yegundane efanele. Uhlu oluvelayo, dlula "ngefomethi yeseli ...".



7. Efasiteleni lokufomatha elivula, vula ithebhu "yenombolo". Ku- "Numeric Formats" Vimba Ukwabela Inani "Yosuku". Ngakwesokunene sewindi, ungakhetha uhlobo lokubonisa losuku olufunayo. Ngemuva kokuthi amasethingi abonisiwe, chofoza inkinobho ethi "Kulungile".



8. Manje usuku luboniswa kahle. Kepha, njengoba sibona, yonke indawo ephansi kwetafula igcwele amaseli aqukethe inani lephutha "inombolo!". Ngamafuphi, lawo maseli, idatha evela kwisampula ababengenayo ngokwanele. Kungakhanga ngokwengeziwe uma kukhonjiswa ngeze konke kungenalutho. Ngalezi zinhloso, sisebenzisa ukufometha okunemibandela. Sigqamisa wonke amaseli etafula, ngaphandle kwekhanda. Ngenkathi usethebhu yasekhaya, chofoza inkinobho ethi "Ukufometha Okunemibandela", okuku-Ithuluzi le- "Izitayela". Uhlu oluvelayo, khetha "ukudala umthetho ..." into.



9. Efasiteleni elivula, khetha uhlobo lomthetho "ifomethi" amaseli kuphela aqukethe ". Ensimini yokuqala, ngaphansi kwefomethi ethi "Ifomethi" kuphela amaseli esimweni esilandelayo, "khetha isikhundla" sephutha ". Okulandelayo, chofoza inkinobho ethi "format ...".



10. Efasiteleni lefomethi ligijima, iya kuthebhu "font" bese ukhetha okumhlophe enkundleni efanele. Ngemuva kwalezi zenzo, chofoza inkinobho ethi "Kulungile".



11. In the inkinobho ngegama elifanayo, chofoza ngemuva kokubuyela edamini lokukwenza.

Manje sinesampula esiqediwe emkhawulweni ocacisiwe etafuleni elihlukile elihlotshiswe kahle.

Isifundo: Ukufomatha okunemibandela ku-Excel

Indlela 3: I-Sampling ngezimo eziningana zisebenzisa ifomula

Njengalapho usebenzisa isihlungi, usebenzisa ifomula ongayikhetha ngezimo eziningana. Isibonelo, thatha itafula lomthombo elifanayo, kanye netafula elingenalutho, lapho imiphumela izokhishwa khona, ngokusenza ukufomatha okunemibandela okuningana. Sizosungula umkhawulo wokuqala womngcele ophansi wokukhethwa kwemali engenayo ngama-ruble ayi-15,000, kanye nesimo sesibili somngcele ongenhla sama-ruble angama-20,000.

1. Faka ikholomu ehlukile, izimo zemingcele zesampula.



2. Njengasendleleni edlule, yabela ngokuhlukile amakholomu angenalutho etafuleni elisha bese ufaka amafomula amathathu ahambisanayo kuwo. Kwikholamu yokuqala yethule le nkulumo elandelayo:

   = Index (A2: A29; Okuncane (Uma (($ D $ 2 = C2: C29); I-C29: I-C29); I-LINE (C2: C29) -Strkok ($ C $ 1)) - Ulayini ($ C $ 1))

   Kumakholomu alandelayo, avumelane ngqo namafomula afanayo, kuphela ngokushintsha izixhumanisi ngokushesha ngemuva kwegama lopharetha, inkomba kumakholomu ahambisanayo esiwadingayo, ngokufana nendlela edlule esikudingayo.

   Isikhathi ngasinye ngemuva kokungena, ungakhohlwa ukuthola inhlanganisela yokhiye we-CTRL + Shift + Enter.



3. Inzuzo yale ndlela ngaphambi kokuthi eyedlule yilokho uma sifuna ukushintsha imingcele yesampula, ngeke kudingeke ukushintsha ifomula eqinile uqobo, okuyinkinga yayo uqobo. Kwanele kukholamu yezimo eziseshidi ukuze zishintshe izinombolo zomngcele kulabo abadingeka umsebenzisi. Imiphumela yokukhethwa izoshintsha ngokushesha ngokuzenzakalela.

Indlela 4: Isampula engahleliwe

Ekudingisweni, ngosizo lwefomula ekhethekile, kungenzeka futhi ukufaka ukukhetha okungahleliwe. Kuyadingeka kwezinye izimo lapho usebenza ngenani elikhulu ledatha lapho udinga ukwethula isithombe esivamile ngaphandle kokuhlaziywa okuphelele kwayo yonke imininingwane yedatha.

1. Kwesokunxele kwethebula leqa ikholamu eyodwa. Esitokisini sekholomu elandelayo, etholakala maqondana neseli lokuqala nedatha yetafula, faka ifomula:

   = Adhesive ()

   Lesi sici sibonisa inombolo engahleliwe. Ukuze uyisebenze, chofoza inkinobho ethi Enter.



2. Ukuze wenze ikholomu ephelele yezinombolo ezingahleliwe, setha isikhombisi ekhoneni elingezansi kwesokudla seseli, elivele liqukethe ifomula. Umaki wokugcwalisa uyavela. Ngiyelula phansi ngenkinobho yegundane kwesobunxele ngifana netafula nedatha kuze kube sekupheleni kwalo.



3. Manje sinalo uhla lwamaseli agcwele izinombolo ezingahleliwe. Kepha, uqukethe ifomula yeCalc. Sidinga ukusebenza ngamanani ahlanzekile. Ukuze wenze lokhu, kopisha kwikholamu engenalutho ngakwesokudla. Khetha uhla lwamaseli ngezinombolo ezingahleliwe. Itholakala kuthebhu "Yasekhaya", chofoza isithonjana esithi "Kopisha" ku-Ribbon.



4. Sigqamisa ikholomu engenalutho bese uqhafaza Qhafaza kwesokudla, kubiza imenyu yokuqukethwe. Ku- "Insert parameters" yamathuluzi, khetha "inani" lesigatshana, esifanekisela njengama-pictogram ngezinombolo.



5. Ngemuva kwalokho, ngenkathi usethebhu "Yasekhaya", chofoza isithonjana esijwayelekile sesithonjana "sohlobo nesihlungi". Ohlwini olwehliswayo, yeka ukukhetha entweni ethi "Ukuhlunga Kwamasiko".



6. Iwindi lezilungiselelo lokuhlunga liyasebenza. Qiniseka ukuthi ufaka umkhaza obhekene nepharamitha "Idatha yami iqukethe ama-headlines" uma i-cap itholakala, kepha akukho mikhaza. Ku- "Hlunga ngensimu", chaza igama laleyokholamu lapho amanani akopishwe ezinombolo ezingahleliwe aqukethe. Ensimini ethi "Hlunga", shiya izilungiselelo ezizenzakalelayo. Ensimini ethi "Order", ungakhetha ipharamitha 'ekhuphuka' futhi "yehla". Ngesampula engahleliwe, leli nani alinayo. Ngemuva kokuthi izilungiselelo zenziwa, chofoza inkinobho ethi "Kulungile".



7. Emva kwalokho, wonke amanani ethebula etafuleni ayakhiwa ngokuhleleka noma ukuncipha kwezinombolo ezingahleliwe. Ungathatha noma yiliphi inani lemigqa yokuqala kusuka etafuleni (5, 10, 12, 15, njll.) Futhi zingabhekwa njengomphumela wesampula engahleliwe.

Isifundo: Ukuhlunga nokuhlunga idatha ukuze udlule

Njengoba ubona, isampula etafuleni le-Excel ingakhiqizwa, zombili usebenzisa i-autofilter kanye nokusebenzisa amafomula akhethekile. Esimweni sokuqala, umphumela uzoboniswa etafuleni lomthombo, nakwesibili - endaweni ehlukile. Kungenzeka ukukhiqiza ukukhetha, zombili izimo kanye nezinye eziningana. Ngaphezu kwalokho, ungenza isampula engahleliwe usebenzisa umsebenzi wokunamathela.