Sebenzisa i-SUM ne-OFFSET ukuthola amathayali wezintambo eziguqukayo zedatha
Uma ishidi lakho lokusebenzela i- Excel lifaka izibalo ngokususelwa kuluhlu lwamaseli olushintshayo, besebenzisa imisebenzi ye-SUM ne-OFFSET ndawonye ku-SUM OFFSET ifomula yenza umsebenzi wokugcina izibalo usesikhathini.
Dala i-Dynamic Range Nge-SUM kanye ne-OFFSET Imisebenzi
Uma usebenzisa izibalo isikhathi esiqhubeka sishintsha - njengokuphelele kokudayiswa kwenyanga - umsebenzi we-OFFSET ikuvumela ukuba usethe ububanzi obushukumisayo obulokhu bushintsha njengoba izibalo zokuthengisa zansuku zonke zengeziwe.
Ngokwalo, umsebenzi we- SUM ungahlala ubeka amangqamuzana amasha wedatha efakwe ebangeni ekufingqiwe.
Okuhlukile okuvela lapho idatha efakwa esitokisini lapho umsebenzi okwamanje ukhona khona.
Esikisweni somfanekiso esihambisana nalesi sihloko, izibalo ezintsha zokuthengisa zansuku zonke zengezwe phansi ohlwini, okuphoqa isamba ukuqhubeka sishintsha iseli elilodwa njalo uma idatha entsha ingeziwe.
Uma umsebenzi we-SUM wasetshenziselwa ukuqoqa idatha, kuzodingeka ukuthi uguqule ububanzi bamaseli asetshenziselwa ukuphikisana komsebenzi njalo lapho idatha entsha ingeziwe.
Ngokusebenzisa imisebenzi ye-SUM ne-OFFSET ndawonye, noma kunjalo, ububanzi obuhlanganisiwe buba namandla. Ngamanye amazwi, lishintsha ukwamukela amaseli amasha wedatha. Ukwengezwa kwamaseli amasha wedatha akubanga izinkinga ngoba ububanzi buqhubeka nokulungiswa njengoba iseli ngalinye elisha lengeziwe.
I-Syntax ne-Arguments
Bheka umfanekiso ohambisana nalesi sihloko ukulandela nalesi sifundo.
Kule fomula, umsebenzi we-SUM usetshenziselwa ukuqedela uhla lwedatha olunikezwa njengengxabano yalo. Iphuzu lokuqala laleli hlu liyi-static futhi libhekwa njengencazelo yeselula kwinombolo yokuqala okufanele ihlanganiswe ifomula.
Umsebenzi we-OFFSET unamathele ngaphakathi komsebenzi we-SUM futhi usetshenziselwa ukudala iphuzu lokuqeda amandla kuluhlu lwedatha olufakwe ifomula. Lokhu kufezwa ngokubeka iphuzu lokuphela kobubanzi esitokisini esisodwa ngenhla yefomula.
I- syntax yefomula:
= I-SUM (Isigaba Sokuqala: OFFSET (Ukubhekisela, Imigqa, Ama-Cols))Isigaba Sokuqala - (esidingekayo) iphuzu lokuqala lamakhansela azokwenziwa umsebenzi we-SUM. Esikisweni somfanekiso, lokhu kuyiselula B2.
Izikhombo - (ezidingekayo) ireferensi yeseli esetshenziselwa ukubala iphuzu lokuphela kwebanga likhona imigqa eminingi namakholomu away. Esikhathini somfanekiso wesithombe, impikiswano yokubhekisela kuyinkomba yeseli yefomula ngokwayo ngoba sihlale sifuna ububanzi buqede iseli elilodwa ngaphezu kwefomula.
Imigqa - (edingekayo) inombolo yemigqa engenhla noma ngaphansi kwempikiswano yokubhekisela esetshenziselwa ukubala ukukhokha. Leli xabiso lingabe lihle, libi, noma lisethelwe ku-zero.
Uma indawo ye-offset ingaphezulu kwempikiswano yokubhekisela , leli xabiso alibi. Uma kungezansi, ingxabano yamaRows inhle. Uma lokho okubekwe eceleni kutholakala emgqeni ofanayo, le mpikiswano iyinhlawulo. Kulesi sibonelo, ukukhokha kuqala umugqa owodwa ngaphezu kwempikiswano yokubhekisela , ngakho-ke inani lale mpikiswano alibi (-1).
Amakholomu - (adingekayo) inamba yamakholomu ngakwesobunxele noma kwesokudla sempikiswano yokubhekisela esetshenziselwa ukubala ukukhokha. Leli xabiso lingabe lihle, libi, noma lisethelwe ku-zero
Uma indawo ye-offset ingakwesokunxele se-argument yokubhekisela , leli xabiso alibi. Uma ngakwesokudla, ukuphikisana kwamaCols kulungile. Kulesi sibonelo, idatha ehlanganisiwe ikhona kukholomu efanayo njengefomula ngakho inani lale mpikiswano liyizinga.
Ukusebenzisa i-SUM i-OFFSET i-Formula ku-Total Sales Data
Lesi sibonelo sisebenzisa ifomula ye-SUM OFFSET ukuze ubuyisele inani lamanani wokudayiswa nsuku zonke okubalulwe kukholomu yephepha lokusebenzela.
Ekuqaleni, ifomula yangena esitokisini B6 futhi yafinyelela idatha yokuthengisa izinsuku ezine.
Isinyathelo esilandelayo ukuhambisa ifomu le-SUM OFFSET phansi emgqeni ukuze wenze indawo yokuthengisa yosuku lwesihlanu.
Lokhu kufezwa ngokufaka umugqa omusha 6, okwenza ifomula iphinde ilandele 7.
Ngenxa yokuhamba, i-Excel ivuselela ngokuzenzakalelayo impikiswano yokubhekisela kumakhalekhukhwini B7 futhi ineza iseli B6 ebangeni elifingqwe ifomula.
Ukufaka i-SUM OFFSET ifomu
- Chofoza kuseli B6, indawo lapho imiphumela yefomula izoboniswa ekuqaleni.
- Chofoza kuthebhu yefomula yemenyu ye- ribbon .
- Khetha i- Math & Trig kusuka ku-riboni ukuvula uhlu lokudonsa phansi lomsebenzi.
- Chofoza ku- SUM ohlwini ukuze ukhulise ibhokisi lengxoxo lomsebenzi.
- Ebhokisini lengxoxo, chofoza kulayini leNombolo1 .
- Chofoza kuseli B2 ukufaka leli referensi yeseli ebhokisini lengxoxo. Le ndawo yisiphelo sokugcina se-formula;
- Ebhokisini lebhokisi, chofoza kumugqa weNombolo2 .
- Faka umsebenzi olandelayo we-OFFSET: OFFSET (B6, -1,0) ukwakha iphasiwedi eqinile yefomula.
- Chofoza OK ukuqedela umsebenzi bese uvala ibhokisi lengxoxo.
Ingqikithi engu- $ 5679.15 livela esitokisini B7.
Uma uchofoza ku-cell B3, umsebenzi ophelele = SUM (B2: OFFSET (B6, -1,0)) ibonakala kubha yamatomu ngaphezulu kwephepha lokusebenzela.
Ukungeza i-Sales Day Data Data
Ukwengeza idatha yokuthengisa yosuku olulandelayo:
- Chofoza ngakwesokudla ehlokweni lomugqa olayini 6 ukuze uvule imenyu yesimo.
- Ku imenyu, chofoza ku- Faka ukuze ufake umugqa omusha ekhasini lokusebenzela.
- Ngenxa yalokho, i-SUM OFFSET ifomula ifinyelela esitokisini B7 nomugqa 6 manje ayinalutho.
- Chofoza kuseli A6 .
- Faka inombolo engu- 5 ukukhombisa ukuthi inani lokuthengisa lelanga lesihlanu liyangena.
- Chofoza kuseli B6.
- Thayipha inombolo $ 1458.25 bese ucindezela ukhiye we- Enter kwikhibhodi.
I-Cell B7 ivuselela inani elisha lika- $ 7137.40.
Uma uchofoza ku-cell B7, ifomula ebuyekeziwe = SUM (B2: OFFSET (B7, -1,0)) ivela kubha yefomula.
Qaphela : Umsebenzi we-OFFSET unemibono emibili yokuzikhethela: Ubukhulu nobubanzi, okwenziwe kulesi sibonelo.
Lezi zingxabano zingasetshenziselwa ukutshela umsebenzi we-OFFSET ukuma komkhiqizo ngokwemigqa eminingi kakhulu namakholomu amaningi kakhulu.
Ngokushiya lezi zimpikiswano, umsebenzi, ngokuzenzakalelayo, usebenzise ukuphakama nobubanzi be-argument yokubhekisela, okuyilo, kuleso sibonelo ububanzi obuningi nobubanzi bekholomu eyodwa.