Okuqukethwe
Yiqiniso, umqondo wobubanzi ku-Excel ungenye yezinto ezibalulekile. Yini? Sonke siyazi ukuthi ishidi lakhiwe ngamaseli. Manje, uma ezimbalwa zazo ziqukethe ulwazi oluthile, khona-ke lobu ububanzi. Ngamagama alula, lawa ngamaseli amabili noma ngaphezulu kudokhumenti.
Ububanzi busetshenziswa ngokusebenzayo kumafomula, futhi bungasetshenziswa njengomthombo wedatha wamagrafu, amashadi, nezinye izindlela ezibonakalayo zokubonisa ulwazi. Ake sibhekisise ukuthi singasebenza kanjani ngebanga.
Ungawakhetha kanjani amaseli, imigqa namakholomu
Iseli isici esiqukethe noma esingaqukatha ulwazi oluthile. Umugqa amaseli emugqeni. Ikholomu, ngokulandelanayo, kukholamu. Konke kulula.
Ngaphambi kokufaka idatha noma wenze idatha ethile ngobubanzi, udinga ukufunda ukuthi ungakhetha kanjani amaseli, amakholomu, nemigqa.
Ukuze ukhethe iseli, udinga ukuchofoza kuyo. Iseli ngalinye linekheli. Isibonelo, leyo etholakala ezimpambanweni zomgwaqo zekholomu C nomugqa 3 ibizwa ngokuthi C3.
Ngokufanelekile, ukukhetha ikholomu, kufanele uchofoze uhlamvu olubonisa igama lekholomu. Esimweni sethu, lokhu kuyikholomu C.
Njengoba ungase uqagele, ukukhetha umugqa, udinga ukwenza okufanayo, kuphela ngegama lomugqa.
Ububanzi beseli: isibonelo
Manje ake sibheke eminye imisebenzi engenziwa ngokuqondile ebangeni. Ngakho-ke, ukuze ukhethe ububanzi B2:C4, udinga ukuthola ikhona elingakwesokudla leseli B2, kithi elisebenza njengeseli engaphezulu kwesokunxele, bese uhudula ikhesa ku-C4.
Kubalulekile! Akusona isikwele ekhoneni elingezansi kwesokudla, kodwa kalula, njengokungathi, donsa leli seli. Isikwele siwumaka wokuqedela ngokuzenzakalela, sihluke kancane.
Ububanzi abuhlali bunamaseli aseduze kwamanye. Ukuze uyikhethe, udinga ukucindezela ukhiye Ctrl futhi, ngaphandle kokuwukhulula, chofoza kuseli ngalinye okufanele lifakwe kulobu bubanzi.
Indlela yokugcwalisa ububanzi
Ukuze ugcwalise ububanzi ngamavelu athile, kufanele wenze lezi zinyathelo ezilandelayo:
- Faka inani olifunayo kuseli B2. Kungaba izinombolo noma umbhalo. Kungenzeka futhi ukufaka ifomula. Esimweni sethu, lena inombolo 2.
5 - Okulandelayo, chofoza umaka wokugcwalisa ngokuzenzakalelayo (ibhokisi elifanayo nje esicele ukuthi ungalichofozi ngaphambilini) bese ulihudulela phansi uze ufike ekugcineni kobubanzi.
Umphumela uzoba okulandelayo. Lapha sigcwalise wonke amaseli adingekayo ngezinombolo 2.
Ukuqedela ngokuzenzakalela kungenye yezici ezicelwe kakhulu ku-Excel. Ikuvumela ukuthi ubhalele kumaseli obubanzi hhayi nje inani elilodwa, kodwa futhi nesethi yonke yedatha ehambisana nephethini ethile. Isibonelo, uchungechunge lwezinombolo luyi-2, 4, 6, 8, 10 njalonjalo.
Ukuze senze lokhu, sidinga ukufaka amanani amabili okuqala okulandelana kumaseli ancikene ngokuqondile bese sihambisa umaka wokugcwalisa okuzenzakalelayo enombolweni edingekayo yamaseli.
Ngokufanayo, ungagcwalisa ububanzi ngezinsuku ozifunayo, nazo ezilandela iphethini ethile. Ukwenza lokhu, asifake idethi kaJuni 13, 2013 kanye nedethi kaJuni 16, 2013 ngefomethi yase-US.
Ngemva kwalokho, siqhuba ukuhudula nokuwisa esikujwayele kakade.
Ukushintsha kobubanzi
Ukuze uhambise ububanzi, vele ulandele izinyathelo ezimbalwa ezilula. Okokuqala udinga ukukhetha ububanzi obudingekayo bese ubamba omunye wemingcele yawo. Esimweni sethu, okulungile.
Khona-ke udinga nje ukuyihambisa endaweni efanele futhi ukhulule igundane.
Ikopisha futhi inamathisele ububanzi
Lona futhi omunye wemisebenzi evamile eyenziwa ngabasebenzisi be-Excel ngobubanzi.
Ukuze wenze lokhu, udinga ukukhetha ububanzi, chofoza kwesokudla kuso bese uchofoza "Kopisha". Ungasebenzisa futhi isinqamuleli sekhibhodi u-Ctrl + C.
Ungathola futhi inkinobho enikelwe kuthebhu ethi Ikhaya eqenjini lebhodi lokunamathisela.
Isinyathelo esilandelayo ukunamathisela ulwazi oludingayo kwenye indawo. Ukuze wenze lokhu, udinga ukuthola iseli elizosebenza njengekhoneni eliphezulu kwesokunxele lobubanzi, bese ubiza imenyu yokuqukethwe ngendlela efanayo, kodwa ngesikhathi esifanayo thola into ethi "Faka". Ungasebenzisa futhi inhlanganisela ejwayelekile ye-Ctrl + V, esebenza kunoma yiluphi uhlelo.
Ungawufaka kanjani umugqa noma ikholomu ethile
Ukufaka irowu noma ikholomu kwenziwa ngendlela efanayo. Okokuqala udinga ukuwakhetha.
Kuphela ngemva kwalokho udinga ukuchofoza kwesokudla bese uchofoza inkinobho ethi "Faka", etholakala ngezansi nje.
Ngale ndlela, sikwazile ukufaka umugqa.
Amabanga Aqanjiwe
Njengoba igama liphakamisa, igama libhekisela ebangeni elinikezwe igama. Lokhu kulula kakhulu, ngoba kwandisa okuqukethwe kwayo kolwazi, okuwusizo ikakhulukazi, isibonelo, uma abantu abambalwa besebenza kudokhumenti efanayo ngesikhathi esisodwa.
Ungabela igama ebangeni ngokusebenzisa Isiphathi Segama, esingatholakala ngaphansi kokuthi Amafomula - Amagama Achaziwe - Isiphathi Segama.
Kodwa ngokuvamile, kunezindlela eziningana. Ake sibheke ezinye izibonelo.
Isibonelo se-1
Ake sithi sibhekene nomsebenzi wokunquma umthamo wokuthengiswa kwempahla. Ngale njongo, sinebanga le-B2:B10. Ukuze unikeze igama, kufanele usebenzise izinkomba eziphelele.
Ngokuvamile, izenzo zethu zimi kanje:
- Khetha ububanzi obufunayo.
- Yiya kuthebhu ethi “Amafomula” bese uthola umyalo othi “Yabela Igama” lapho.
- Okulandelayo, kuzovela ibhokisi lengxoxo lapho kufanele ucacise igama lobubanzi. Esimweni sethu, lokhu "Ukuthengisa".
- Kukhona futhi inkambu ethi "Region", ekuvumela ukuthi ukhethe ishidi lapho lobu bubanzi butholakala khona.
- Hlola ukuthi ububanzi obulungile bucacisiwe. Ifomula kufanele ibe: ='1isizini'!$B$2:$B$10
- Chofoza OK.
19
Manje usungakwazi ukufaka igama layo esikhundleni sekheli lebanga. Ngakho, usebenzisa ifomula =USUM(Ukuthengisa) ungakwazi ukubala isamba yokuthengisa yonke imikhiqizo.
Ngokufanayo, ungakwazi ukubala ivolumu yokuthengisa emaphakathi usebenzisa ifomula =ISILINGANISO(Ukuthengisa).
Kungani sisebenzise ukukhuluma ngokuphelele? Ngoba ivumela i-Excel ukuthi ifake i-hardcode ububanzi obungeke bushintshe lapho ikopishwa.
Kwezinye izimo kungcono ukusebenzisa isixhumanisi esihlobene.
Isibonelo se-2
Manje ake sinqume inani lokuthengiswa kwesizini ngayinye kwezine. Ungakwazi ukujwayelana nolwazi lokuthengisa eshidini le-4_season.
Kulesi sithombe-skrini, ububanzi bumi kanje.
B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10
Ngokufanelekile, sidinga ukubeka amafomula kumaseli B11, C11, D11 kanye ne-E11.
Yiqiniso, ukwenza lo msebenzi ube ngokoqobo, ungakha ububanzi obuningi, kodwa lokhu kuphazamisa kancane. Kungcono ukusebenzisa eyodwa. Ukuze wenze ukuphila kube lula kakhulu, udinga ukusebenzisa ikheli elihlobene. Kulokhu, kwanele ukumane ube nebanga elilodwa, kithi elizobizwa ngokuthi “Seasonal_Sales”
Ukuze wenze lokhu, udinga ukuvula imenenja yegama, faka igama ebhokisini lengxoxo. Indlela efanayo. Ngaphambi kokuchofoza "KULUNGILE", udinga ukuqinisekisa ukuthi ifomula ifakwe kulayini "Ububanzi". ='4amasizini'!B$2:B$10
Kulokhu, ikheli lihlanganisiwe. Njengoba ubona, alukho uphawu lwedola phambi kwegama lekholomu. Lokhu kukuvumela ukuthi wenze isamba samanani asemigqa efanayo kodwa amakholomu ahlukene.
Ngaphezu kwalokho, inqubo iyafana.
Manje sidinga ukufaka ifomula kuseli B11 =SUM(Isizini_Yokuthengisa). Ngaphezu kwalokho, sisebenzisa umaka wokuqedela ngokuzenzakalela, siwudlulisela kumaseli angomakhelwane, futhi lokhu kuwumphumela.
Okunconyiwe: Uma ucindezela inkinobho ethi F2 kuyilapho kukhethwa iseli eliqukethe ifomula enegama lobubanzi, amaseli alungile azogqanyiswa ngomngcele oluhlaza okwesibhakabhaka.
Isibonelo se-3
Ububanzi obuqanjiwe bungasetshenziswa futhi kufomula eyinkimbinkimbi. Ake sithi sinefomula enkulu lapho kusetshenziswa ububanzi obuqanjiwe izikhathi eziningi.
=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)
Uma udinga ukwenza izinguquko kuhlu lwedatha esetshenzisiwe, kuzodingeka wenze lokhu izikhathi ezintathu. Kodwa uma unikeza igama ebangeni ngaphambi kokwenza izinguquko ngokuqondile, khona-ke kwanele ukulishintsha kumphathi wegama, futhi igama lizohlala linjalo. Lokhu kulula kakhulu.
Ngaphezu kwalokho, uma uqala ukuthayipha igama lebanga, i-Excel izophakamisa ngokuzenzakalelayo kanye namanye amafomula.
Ububanzi obuzenzakalelayo
Ngokuvamile, lapho usebenza ngolwazi kuspredishithi, akwenzeki ukwazi kusengaphambili ukuthi ingakanani idatha ezoqoqwa. Ngakho-ke, asazi njalo ukuthi yiluphi ububanzi okufanele silunikeze igama elithile. Ngakho-ke, ungenza ububanzi bushintshe ngokuzenzakalelayo kuye ngokuthi ingakanani idatha efakiwe.
Ake sithi ungumtshali-zimali futhi udinga ukwazi ukuthi yimalini oyitholile isiyonke ngesikhathi sokutshala imali entweni ethile. Futhi ake sithi unombiko onjalo.
Ukuze wenze lokhu, kukhona umsebenzi "Amagama Anamandla". Ukuze uyabele, udinga ukwenza okulandelayo:
- Vula iwindi elithi Nikeza Igama.
- Gcwalisa izinkambu njengoba kukhonjisiwe kusithombe-skrini.
26
Kubalulekile ukuqaphela ukuthi esikhundleni sobubanzi, kusetshenziswa ifomula enomsebenzi UKULAHLWA kanye nomsebenzi HLOLA.
Manje udinga ukufaka umsebenzi we-SUM negama lobubanzi njengengxabano. Ngemva kokuzama lokhu ngokwenza, ungabona ukuthi isamba sishintsha kanjani kuye ngenani lezinto ezifakiwe.
Njengoba ubona, kunezindlela eziningi ezithakazelisayo zokuxhumana nobubanzi. Sithemba ukuthi uwuthandile lo mhlahlandlela kusukela kokuyisisekelo kuya kochwepheshe futhi uwuthole uwusizo.