21 Izici Eziwusizo Ze-Excel Zabakhangisi Be-inthanethi

Ukumaketha nge-inthanethi kuyinkambu enenzuzo emangalisayo yomsebenzi wabantu, ikakhulukazi ezikhathini zakamuva, lapho noma yiliphi ibhizinisi lihamba ku-inthanethi noma nini lapho kunokwenzeka. Futhi naphezu kweqiniso lokuthi izinqubo eziningi zebhizinisi zilawulwa izinhlelo ezikhethekile, umuntu akahlali enesabelomali esanele sokuzithola, kanye nesikhathi sokuziqonda kahle.

Futhi isixazululo sale nkinga silula kakhulu - i-Excel endala enhle, lapho ungagcina khona imininingwane egciniwe yokuhola, izinhlu zokuposa, ukuhlaziya ukusebenza kokumaketha, ukuhlela isabelomali, ukwenza ucwaningo nokwenza eminye imisebenzi edingekayo kulo msebenzi onzima. Namuhla sizojwayelana nemisebenzi engama-21 ye-Excel ezofanela wonke umkhangisi we-inthanethi. Ngaphambi kokuba siqale, ake siqonde imiqondo ethile ebalulekile:

1. I-syntax. Lezi yizingxenye eziyinhloko zomsebenzi nokuthi ubhalwa kanjani nokuthi lezi zingxenye zakhiwe ngokulandelana kwazo. Ngokuvamile, i-syntax yanoma yimuphi umsebenzi ihlukaniswe izingxenye ezimbili: igama layo ngokwalo kanye nezimpikiswano - lezo eziguquguqukayo umsebenzi owamukelayo ukuze uthole umphumela noma wenze isenzo esithile. Ngaphambi kokubhala ifomula, udinga ukubeka uphawu olulinganayo, okuthi ku-Excel lusho uhlamvu lokokufaka kwalo.
2. Izimpikiswano zingabhalwa kokubili izinombolo kanye nefomethi yombhalo. Ngaphezu kwalokho, ungasebenzisa abanye opharetha njengezingxabano, okukuvumela ukuthi ubhale ama-algorithms agcwele ku-Excel. I-agumenti ethathe inani ibizwa ngokuthi ipharamitha yomsebenzi. Kodwa ngokuvamile la magama amabili asetshenziswa njengomqondofana. Kodwa eqinisweni, kunomehluko phakathi kwabo. Ibhulokhi yokungqubuzana iqala ngobakaki ovulekile, ohlukaniswe isemikholoni, futhi ibhulokhi yempikiswano igcina kubakaki abavaliwe.

Isibonelo somsebenzi olungile − =INANI(A1:A5). Hhayi-ke, siqale?

Umsebenzi we-VLOOKUP

Ngalesi sici, umsebenzisi angathola ulwazi olufana nemibandela ethile futhi alusebenzise kwenye ifomula noma alubhale kuseli ehlukile. VPR isifinyezo esimele “Ukubuka Okuqondile”. Lena ifomula eyinkimbinkimbi enezimpikiswano ezine:

1. Inani elifunekayo. Leli inani okuzokwenziwa ngalo ukusesha ulwazi esizolidinga. Isebenza njengekheli leseli noma inani ngokwalo noma libuyiselwe ngenye indlela.
2. Ithebula. Leli ibanga lapho udinga ukucinga khona ulwazi. Inani elidingekayo kufanele libe kukholomu yokuqala yethebula. Inani lokubuyisela lingaba ngokuphelele kunoma yiliphi iseli elifakwe kulobu bubanzi.
3. Inombolo yekholomu. Lena inombolo ye-ordinal (ukunaka - hhayi ikheli, kodwa inombolo ye-ordinal) yekholomu equkethe inani.
4. Ukubuka isikhawu. Leli inani le-boolean (okungukuthi, lapha udinga ukufaka ifomula noma inani elikhiqizayo TRUE or AMANGA), okubonisa indlela ulwazi okufanele lwakhiwe ngayo. Uma udlula le mpikiswano inani TRUE, khona-ke okuqukethwe kwamaseli kufanele ku-odwe ngendlela eyodwa kwezimbili: ngokulandelana kwezinhlamvu noma ngokunyuka. Kulokhu, ifomula izothola inani elicishe lifane nalelo eliseshwayo. Uma ucacise njengengxabano AMANGA, kuzoseshwa kuphela inani eliqondile. Kulesi simo, ukuhlungwa kwedatha yekholomu akubalulekile kangako.

Impikiswano yokugcina ayibalulekile kangako ukuyisebenzisa. Ake sinikeze izibonelo ezithile zokuthi lo msebenzi ungasetshenziswa kanjani. Ake sithi sinethebula elichaza inombolo yokuchofoza emibuzweni eyahlukene. Sidinga ukuthola ukuthi zingaki ezenziwe ngesicelo "sokuthenga ithebhulethi".

Efomini yethu, besibheke ngokukhethekile igama elithi “ithebhulethi”, esilisetha njengenani esilifunayo. Impikiswano ethi “ithebula” lapha iqoqo lamaseli aqala ngeseli A1 futhi agcine ngeseli B6. Inombolo yekholomu esimweni sethu ngu-2. Ngemva kokufaka yonke imingcele edingekayo kufomula, sithole umugqa olandelayo: =I-VLOOKUP(C3;A1:B6;2).

Ngemuva kokuthi siyibhale esitokisini, sithole umphumela ohambisana nenani lezicelo zokuthenga ithebhulethi. Ungayibona kusithombe-skrini esingenhla. Esimweni sethu, sisebenzisa umsebenzi VPR ngezinkomba ezehlukene zempikiswano yesine.

Lapha sifaka inombolo engu-900000, futhi ifomula ithole ngokuzenzakalelayo inani eliseduze nalokhu futhi yakhipha umbuzo othi "uthenge imoto". Njengoba singabona, impikiswano "yokubheka isikhashana" iqukethe inani TRUE. Uma sisesha nge-agumenti efanayo ethi FALSE, kuzomele sibhale inombolo efana ncamashi nenani lokusesha, njengakulesi sithombe-skrini.

Njengoba sibona, umsebenzi VPR inamathuba abanzi kakhulu, kodwa, kunjalo, kunzima ukuyiqonda. Kodwa onkulunkulu abazange bawashise amabhodwe.

Umsebenzi we-IF

Lo msebenzi uyadingeka ukuze wengeze izici ezithile zokuhlela kusipredishithi. Ivumela umsebenzisi ukuthi ahlole ukuthi okuhlukile kuyahlangabezana yini nemibandela ethile. Uma kunjalo, umsebenzi wenza isenzo esisodwa, uma kungenjalo, esinye. I-syntax yalo msebenzi ihlanganisa ama-agumenti alandelayo:

1. Inkulumo ye-boolean eqondile. Lona umbandela okufanele ubhekwe. Isibonelo, noma ngabe isimo sezulu ngaphandle singaphansi kweziro noma cha.
2. Idatha ezocutshungulwa uma umbandela uyiqiniso. Ifomethi ayikwazi ukuba izinombolo kuphela. Ungakwazi futhi ukubhala iyunithi yezinhlamvu yombhalo ezobuyiselwa kwenye ifomula noma ebhalelwe iseli. Futhi, uma inani liyiqiniso, ungasebenzisa ifomula ezokwenza izibalo ezengeziwe. Ungasebenzisa futhi imisebenzi IF, ezibhalwe njengezimpikiswano komunye umsebenzi IF. Kulesi simo, singasetha i-algorithm egcwele ngokugcwele: uma umbandela uhlangabezana nesimo, bese senza isenzo 1, uma singakwenzi, bese sihlola ukuthobela umbandela 2. Ngokulandelayo, kukhona nokuhlanganisa. Uma kunamaketanga amaningi anjalo, umsebenzisi angadideka. Ngakho-ke, kusanconywa ukusebenzisa ama-macros ukubhala ama-algorithms ayinkimbinkimbi.
3. Inani uma kungamanga. Lokhu kuyafana kuphela uma isisho singafani nemibandela enikezwe ku-agumenti yokuqala. Kulokhu, ungasebenzisa ama-agumenti afanayo ncamashi nasesimweni esidlule.

Ukuze sifanekise, ake sithathe isibonelo esincane.

Ifomula eboniswe kulesi sithombe-skrini ihlola ukuthi imali engenayo yansuku zonke ingaphezu kuka-30000 yini. Uma kunjalo, iseli libonisa ulwazi lokuthi icebo liqediwe. Uma leli nani lingaphansi noma lilingana, kuzobe sekuvela isaziso sokuthi uhlelo aluqedwanga. Qaphela ukuthi sihlala sifaka iyunithi yezinhlamvu ezicashuniwe. Umthetho ofanayo uyasebenza kuwo wonke amanye amafomula. Manje ake sinikeze isibonelo esibonisa indlela yokusebenzisa imisebenzi eminingi efakwe isidleke IF.

Siyabona ukuthi kunemiphumela emithathu engaba khona yokusebenzisa le fomula. Inombolo enkulu yemiphumela ifomula enemisebenzi efakwe esidlekeni ekhawulelwe kuyo IF - 64. Ungahlola futhi ukuthi iseli ayinalutho. Ukwenza lolu hlobo lwesheke, kunefomula ekhethekile ebizwa ngokuthi I-EPUSTO. Ikuvumela ukuthi umiselele umsebenzi omude IF, ehlola ukuthi iseli ayinalutho yini, ngefomula eyodwa elula. Kulokhu, ifomula izoba:

Umsebenzi ISBLANK ukubuyisela kuthatha iseli njenge-agumenti, futhi njalo ibuyisela inani le-boolean. Umsebenzi IF iwumongo wezinye izici eziningi esizozibheka ngokulandelayo, ngoba zidlala indima enkulu ekukhangiseni. Eqinisweni, ziningi zazo, kodwa sizobheka ezintathu namuhla: I-SUMMESLI, COUNTIF, IFERROR.

Imisebenzi ye-SUMIF ne-SUMIFS

Umsebenzi I-SUMMESLI yenza kube nokwenzeka ukuhlanganisa kuphela leyo datha ehlangabezana nemibandela ethile futhi esebangeni. Lo msebenzi unezimpikiswano ezintathu:

1. Ibanga. Leli isethi yamaseli adinga ukubhekwa ukuze kubonakale ukuthi akhona yini amaseli phakathi kwalo afana nesimo sokunquma esishiwo.
2. Umbandela. Lena i-agumenti ecacisa ngqo amapharamitha lapho amaseli azofingqwa khona. Noma yiluphi uhlobo lwedatha lungasebenza njengombandela: iseli, umbhalo, inombolo, kanye nomsebenzi (isibonelo, onengqondo). Kubalulekile ukucabangela ukuthi izindlela eziqukethe umbhalo nezimpawu zezibalo kufanele zibhalwe ngamamaki okucaphuna.
3. Ibanga lesifinyezo. Le mpikiswano ayidingi ukucaciswa uma ububanzi bokulinganisa bulingana nobubanzi bokuhlola umbandela.

Ake sithathe isibonelo esincane ukuze sisibonise. Lapha, sisebenzisa umsebenzi, sengeze zonke izicelo ezinezinguquko ezingaphezu kwezinkulungwane eziyikhulu.

Kukhona nenguqulo yesibili yalo msebenzi, ebhalwe ngokuthi I-SUMMESLIMN. Ngosizo lwayo, izindlela ezimbalwa zingacatshangelwa ngesikhathi esisodwa. I-syntax yayo iyavumelana nezimo futhi incike enanini lama-agumenti azosetshenziswa. Ifomula ejwayelekile ibukeka kanje: =I-SUMIFS(isifinyezo_ububanzi, isimo_ububanzi1, isimo1, [condition_range2, condition2], …). Izimpikiswano ezintathu zokuqala kufanele zicaciswe, bese yonke into incike ekutheni zingaki imibandela umuntu afuna ukuyimisa.

COUNTIF kanye COUNTIFS imisebenzi

Lo msebenzi unquma ukuthi mangaki amaseli kububanzi afana nesimo esithile. I-syntax yomsebenzi ihlanganisa ama-agumenti alandelayo:

1. Ibanga. Lena idathasethi ezoqinisekiswa futhi ibalwe.
2. Umbandela. Lesi yisimo lapho idatha okufanele ihlangabezane nayo.

Esibonelweni esisinikezayo manje, lo msebenzi unqume ukuthi bangaki okhiye abanoshintsho olungaphezu kwezinkulungwane eziyikhulu. Kuvele ukuthi bathathu kuphela okhiye abanjalo.

Inombolo enkulu engaba khona yemibandela kulo msebenzi umbandela owodwa. Kodwa ngokufanayo nenketho yangaphambilini, ungasebenzisa umsebenzi COUNTIFSukusetha imibandela eyengeziwe. I-syntax yalo msebenzi ithi: COUNTIFS(condition_range1, condition1, [condition_range2, condition2], …).

Inombolo enkulu yemibandela nobubanzi okufanele buhlolwe futhi ibalwe ngu-127.

IPHUTHA umsebenzi

Ngalo msebenzi, iseli lizobuyisela inani elishiwo umsebenzisi uma kwenzeka iphutha ngenxa yokubala komsebenzi othile. I-syntax yalo msebenzi imi kanje: =IFERROR(inani;inani_uma_iphutha). Njengoba ubona, lo msebenzi udinga izimpikiswano ezimbili:

1. Incazelo. Lapha udinga ukubhala phansi ifomula, ngokusho ukuthi yimaphi amaphutha azocutshungulwa, uma ekhona.
2. Inani uma iphutha. Leli inani elizoboniswa kuseli uma ukusebenza kwefomula kwehluleka.

Nesibonelo esingasifanekisa. Ake sithi sinetafula elinjalo.

Siyabona ukuthi ikhawunta ayisebenzi lapha, ngakho-ke azikho izivakashi, futhi kwathengwa okungu-32. Ngokwemvelo, isimo esinjalo asinakwenzeka empilweni yangempela, ngakho-ke sidinga ukucubungula leli phutha. Senza lokho kanye. Sithole amaphuzu emdlalweni IFERROR ukungqubuzana ngendlela yefomula yokuhlukanisa inani lokuthenga ngenani labavakashi. Futhi uma kwenzeka iphutha (futhi kulokhu kuhlukaniswa ngo-zero), ifomula ibhala "hlola kabusha". Lo msebenzi uyazi ukuthi ukuhlukanisa ngoziro akwenzeki, ngakho ubuyisela inani elifanele.

LEFT umsebenzi

Ngalo msebenzi, umsebenzisi angathola inombolo ayifunayo yezinhlamvu zeyunithi yezinhlamvu yombhalo, ezitholakala ngakwesokunxele. Umsebenzi uqukethe izimpikiswano ezimbili. Ngokuvamile, ifomula imi kanje: =KUSELE(umbhalo,[inombolo_yezinhlamvu_zezinhlamvu]).

Ama-agumenti kulo msebenzi ahlanganisa iyunithi yezinhlamvu yombhalo noma iseli equkethe izinhlamvu ezizobuyiswa, kanye nenani lezinhlamvu okufanele zibalwe kusukela ohlangothini lwesobunxele. Ekukhangiseni, lesi sici sizokuvumela ukuthi uqonde ukuthi izihloko zamakhasi ewebhu zizobukeka kanjani.

Kulokhu, sikhethe izinhlamvu ezingu-60 kwesokunxele seyunithi yezinhlamvu equkethwe kuseli A5. Besifuna ukuhlola ukuthi isihloko esifingqiwe sizobukeka kanjani.

Umsebenzi we-PTR

Lo msebenzi empeleni ufana nowangaphambilini, kuphela ukuvumela ukuthi ukhethe indawo yokuqala lapho ungaqala khona ukubala izinhlamvu. I-syntax yayo ihlanganisa izimpikiswano ezintathu:

1. Iyunithi yezinhlamvu yombhalo. Ngokwethiyori nje, ungabhala umugqa lapha ngokuqondile, kodwa kusebenza kahle kakhulu ukunikeza izixhumanisi zamaseli.
2. Indawo yokuqala. Lona uhlamvu lapho isibalo senani lezinhlamvu ezichazwe kumpikiswano yesithathu siqala khona.
3. Inombolo yezinhlamvu. I-agumenti efana naleyo esemsebenzini odlule.

Ngalo msebenzi, isibonelo, ungakwazi ukususa inombolo ethile yezinhlamvu ekuqaleni nasekupheleni kochungechunge lombhalo.

Esimweni sethu, sabasusa kusukela ekuqaleni.

Umsebenzi OPHEZULU

Uma udinga ukwenza isiqiniseko sokuthi wonke amagama ochungechungeni lombhalo olutholakala kuseli ethile abhalwe ngosonhlamvukazi, ungasebenzisa umsebenzi. OKULAWULAYO. Kudingeka i-agumenti eyodwa kuphela, iyunithi yezinhlamvu yombhalo yenziwe ibe nkulu. Ingashaywa ngokuqondile kubakaki, noma ibe yiseli. Esimeni sakamuva, kufanele unikeze isixhumanisi kuso.

Umsebenzi OPHANSI

Lo msebenzi uphambene ncamashi nowangaphambili. Ngosizo lwayo, ungenza zonke izinhlamvu ezintanjeni zibe zincane. Kudingeka futhi ukungqubuzana okukodwa nje njengeyunithi yezinhlamvu yombhalo, evezwa ngokuqondile njengombhalo noma igcinwe kuseli elithile. Nasi isibonelo sokuthi siwusebenzise kanjani lo msebenzi ukuze siguqule igama lekholomu ethi “Idethi Yokuzalwa” libe lapho zonke izinhlamvu zincane khona.

SESHA umsebenzi

Ngalo msebenzi, umsebenzisi angakwazi ukunquma ubukhona bento ethile enanini elimisiwe futhi aqonde kahle ukuthi ikuphi. Iqukethe izimpikiswano ezimbalwa:

1. Inani elifunekayo. Lolu uchungechunge lombhalo, inombolo, okufanele iseshwe ebangeni ledatha.
2. Amalungu afanayo abukwa. Isethi yedatha eseshwayo ukuze kutholwe inani eliqukethwe ku-agumenti yangaphambilini.
3. Uhlobo lwamamephu. Lokhu kuphikisana kungokuzithandela. Ngayo, ungathola idatha ngokunembe kakhudlwana. Kunezinhlobo ezintathu zokuqhathanisa: 1 - inani elingaphansi noma elilinganayo (sikhuluma ngedatha yezinombolo, futhi uhlu ngokwalo kufanele luhlungwe ngendlela ekhuphukayo), 2 - ukufanisa ncamashi, -1 - inani elikhulu noma elilinganayo.

Ukuze kucace, isibonelo esincane. Lapha sizame ukuqonda ukuthi yisiphi isicelo esinenani lezinguquko ezingaphansi noma ezilingana no-900.

Ifomula ibuyisele inani elingu-3, ​​okungeyona inombolo yomugqa ephelele, kodwa ehlobene. Okusho ukuthi, hhayi ngekheli, kodwa ngenombolo ehlobene nesiqalo sebanga ledatha elikhethiwe, elingaqala noma kuphi.

Umsebenzi we-DLSTR

Lo msebenzi wenza kube nokwenzeka ukubala ubude beyunithi yezinhlamvu yombhalo. Kudingeka impikiswano eyodwa - ikheli leseli noma iyunithi yezinhlamvu yombhalo. Isibonelo, ekukhangiseni, kuhle ukuyisebenzisela ukuhlola inani lezinhlamvu encazelweni.

CONCATENATE umsebenzi

Ngalo opharetha, ungakwazi ukuhlanganisa amanani ombhalo amaningi abe yiyunithi yezinhlamvu eyodwa enkulu. Ama-agumenti amaseli noma iyunithi yezinhlamvu zombhalo eziqondile kuzimpawu zokucaphuna ezihlukaniswa ngokhefana. Futhi nasi isibonelo esincane sokusebenzisa lo msebenzi.

Umsebenzi we-PROP

Lo opharetha ikuvumela ukuthi wenze zonke izinhlamvu zokuqala zamagama ziqale ngosonhlamvukazi. Kuthatha iyunithi yezinhlamvu yombhalo noma umsebenzi obuyisela eyodwa njengokuphela kwe-agumenti yayo. Lolu hlelo lokusebenza lufaneleka kahle ekubhaleni uhlu oluhlanganisa amagama amaningi afanele noma ezinye izimo lapho lungaba usizo khona.

UMSEBENZI WOMSEBENZI

Lo opharetha wenza kube nokwenzeka ukususa zonke izinhlamvu ezingabonakali entanjeni yombhalo. Kuthatha impikiswano eyodwa kuphela. Kulesi sibonelo, umbhalo uqukethe uhlamvu olungaphrinteki olususwe umsebenzi.

Lesi sici kufanele sisetshenziswe ezimeni lapho umsebenzisi ekopishe khona umbhalo osuka kolunye uhlelo futhi izinhlamvu ezingaphrinteki zidluliselwe ngokuzenzakalelayo kusipredishithi se-Excel.

Umsebenzi we-TRIM

Ngalo opharetha, umsebenzisi angasusa zonke izikhala ezingadingekile phakathi kwamagama. Kufaka ikheli leseli, okuwukuphela kwengxabano. Nasi isibonelo sokusebenzisa lo msebenzi ukushiya isikhala esisodwa phakathi kwamagama.

THOLA umsebenzi

Ngalo msebenzi, umsebenzisi angathola umbhalo ngaphakathi komunye umbhalo. Lo msebenzi ubucayi kakhulu. Ngakho-ke, izinhlamvu ezinkulu nezincane kufanele zihlonishwe. Lo msebenzi uthatha ama-agumenti amathathu:

1. Umbhalo oyifunayo. Lolu wuchungechunge oluseshwayo.
2. Umbhalo obhekwayo uwububanzi okuseshwayo okwenziwa ngabo.
3. Indawo yokuqala impikiswano ozikhethela yona ecacisa uhlamvu lokuqala okuseshwa kulo.

Umsebenzi we-INDEX

Ngalo msebenzi, umsebenzisi angathola inani alifunayo. Inezimpikiswano ezintathu ezidingekayo:

1. Uhlu. Ububanzi bedatha ehlaziywayo.
2. Inombolo yomugqa. Inombolo ye-ordinal yomugqa kulobu bubanzi. Qaphela! Akulona ikheli, kodwa inombolo yomugqa.
3. Inombolo yekholomu. Okufanayo nokuphikisana kwangaphambilini, kukholomu kuphela. Le mpikiswano ingashiywa ingenalutho.

EXACT umsebenzi

Lo opharetha angasetshenziswa ukuthola ukuthi iyunithi yezinhlamvu ezimbili zombhalo ziyefana yini. Uma zifana, ibuyisela inani TRUE. Uma behlukile - AMANGA.

NOMA umsebenzi

Lo msebenzi ikuvumela ukuthi usethe ukukhetha kwesimo 1 noma umbandela 2. Uma okungenani eyodwa yazo iyiqiniso, inani lokubuyisela lithi – TRUE. Ungacacisa kufikela kumanani we-boolean angama-255.

Umsebenzi kanye

Umsebenzi ubuyisela inani TRUEuma zonke izimpikiswano zayo zibuyisela inani elifanayo.

Lena impikiswano enengqondo ebaluleke kakhulu ekuvumela ukuthi usethe izimo ezimbalwa ngesikhathi esisodwa, okufanele zibhekwe kanyekanye.

Umsebenzi we-OFFSET

Lo msebenzi ukuvumela ukuthi uthole ireferensi yobubanzi obusulwa inombolo ethile yemigqa namakholomu asuka ezixhumanisini zangempela. Izimpikiswano: ireferensi yeseli yokuqala yobubanzi, mingaki imigqa ezosuswa, mangaki amakholomu okufanele asuswe, buyini ubude bobubanzi obusha nokuthi buyini ububanzi bobubanzi obusha.

Isiphetho

Ngosizo lwemisebenzi ye-Excel, umdayisi angakwazi ukuhlaziya ngokuguquguqukayo ukusebenza kwesayithi, ukuguqulwa, nezinye izinkomba. Njengoba ubona, azikho izinhlelo ezikhethekile ezidingekayo, i-office suite endala yanele ukusebenzisa cishe noma yimuphi umqondo.