Sebenzisa i-SUM kunye ne-OFFSET ukuze ufumane iitotali zeendawo ezichanekileyo zolwazi
Ukuba iphepha lakho lokusebenzela lase-Excel liquka izibalo esekelwe kwinqanaba elitshintshi leeseli, ukusebenzisa i-SUM kunye ne-OFFSET imisebenzi kunye ne-SUM OFFSET ifomula yenza lula umsebenzi wokugcina izibalo ngokusesikweni.
Yakha iDynamic Range kunye ne-SUM kunye ne-OFFSET Imisebenzi
Ukuba usebenzisa izibalo zexesha elihlala liguquka - njengento yokuthengisa yonke inyanga - umsebenzi we-OFFSET ikuvumela ukuba usethe uluhlu olushukumisayo oluqhubeka lugqithiso njengoko iimpawu zentengiso nganye ngosuku zongezwa.
Ngokwalo, umsebenzi we- SUM unokukwazi ukufumana iiseli ezintsha zedata zifakwe kwibala elichazwe.
Ukungafani omnye kwenzeka xa idatha ifakwe kwiseli apho umsebenzi ukhoyo khona ngoku.
Kwimizekelo yomfanekiso ohamba kunye neli nqaku, amanani athengiswayo kwintsuku nganye ayongezwa phantsi kweloluhlu, okubangela ukuba inani liqhubeke ngokutshintshisa iseli enye ngalunye ixesha lwedatha entsha.
Ukuba umsebenzi we-SUM wasetyenziselwa ukuzithemba yonke idatha, kuya kufuneka ukuba utshintshe uluhlu lweetyeli ezisetyenziselwa ingxabano yomsebenzi rhoqo ngexesha lwedatha entsha.
Ngokusebenzisa imisebenzi ye-SUM kunye ne-OFFSET kunye, nangona kunjalo, uluhlu oluhlanganisiweyo luba lukhulu. Ngamanye amagama, litshintsha ukulungiselela iiseli ezintsha zeedatha. Ukongezwa kweeseli ezintsha zeedatha akubangeli iingxaki kuba uhla luqhubeka lulungelelaniswa njengoko iseli ngalinye elitsha longezelelweyo.
I-Syntax kunye neengxoxo
Jonga umfanekiso ohamba nale nqaku ukulandela kunye nale khokelo.
Kule ndlela, umsebenzi we-SUM usetyenziselwa ukugqiba uluhlu lweenkcukacha ezinikezelwa njengengxabano yalo. Isiqalo sokuqala salolu hlobo luyi-static kwaye luchongwa njengeteksi yeseli kwinombolo yokuqala eya kuzaliswa yifom.
Umsebenzi we-OFFSET unamathele ngaphakathi komsebenzi we-SUM kwaye usetyenziselwa ukudala ipopu yokugqibela kwinqanaba leenkcukacha ezizaliswe ngolu hlobo. Oku kufezwe ngokubeka umgca wokuphela kwoluhlu kwisinye iseli ngaphezu kwendawo yefomula.
I- syntax yefomula:
= I-SUM Qala: I-OFFSET (Qwalasela, iifolo, iCols))I-Start Start - (efunekayo) kwindawo yokuqala yoluhlu lweeseli eziza kubalwa ngumsebenzi we-SUM. Kwomzekelo womfanekiso, le neseli yeB2.
Inkcazelo- (efunekayo) iselferensi yeseli esetyenzisiweyo ekubaleni iphupha lokuphela kwebala lifumaneka emigqeni emibini kunye neikholomu kude. Kwimizekelo yomfanekiso, impikiswano yeReferensi yereferensi yeseli yefomula ngokwayo kuba sisoloko sifuna uluhlu luphelise enye iseli ngaphezu kwefomula.
Imigangatho - (efunekayo) inani lemiqolo engentla okanye ngaphantsi kwempikiswano yokuBhekisela ekusebenziseni ukubala. Eli xabiso lingaba lihle, libi, okanye lisethelwe kwi-zero.
Ukuba indawo ye-offset ingaphezulu kwempikiswano yokubhekisa, eli xabiso alibi. Ukuba ilapha ngezantsi, ingxabano yeeRows is positive. Ukuba i-offset ifumaneke kumqolo ofanayo, le ngxabano iyona. Kulo mzekelo, ukuhlawulela kuqalisa omnye umgca ngaphezu kwempikiswano yokuLungisa, ngoko ixabiso le ngxabano libi (-1).
Ama-Cols - (afunekayo) inani lamakholomu ngakwesobunxele okanye ngakwesokudla se- Arbitration argument esetyenzisiweyo ekubaleni i-offset. Eli xabiso lingaba lihle, libi, okanye lisethelwe kwi-zero
Ukuba indawo ye-offset ingakwesokunxele se-Arguments of Reference , eli xabiso alibi. Ukuba ngakwesokudla, ingxabano yamaCols ilungile. Kulo mzekelo, idatha ehlanganisiweyo iyafumaneka kwikholam enye njengolu hlobo ukuze ixabiso le ngxabano liyiyo.
Ukusebenzisa i-SUM I-OFFSET ye-Formula kwi-Total Sales Data
Lo mzekelo usebenzisa ifom ye-SUM OFFSET ukubuyisela inani leemveliso zansuku zonke ezidweliswe kwikholamu B yephepha lokusebenzela.
Ekuqaleni, ifom yangeniswa kwi-cell B6 kwaye yazaliswa idatha yokuthengisa ngeentsuku ezine.
Isinyathelo esilandelayo kukuhambisa ifom ye-SUM OFFSET phantsi komgca ukuze wenze indawo yokuthengisa yosuku lwesihlanu.
Oku kufezekiswa ngokufaka umqolo omtsha 6, ohambisa ifomula ukuya kumqolo wesi-7.
Ngenxa yesigxina, i-Excel ihlaziya ngokuzenzekelayo impikiswano yokuBhekisela kwiseli B7 kwaye idibanisa iseli B6 kwinqanaba elichongiweyo kwifom.
Ukufaka i-SUM OFFSET yeFomula
- Cofa kwifowuni B6, apho indawo apho iziphumo zefomyula ziza kuboniswa ekuqaleni.
- Cofa kwiTrafom ithebhu yemenyu yebhondi .
- Khetha i- Math & Trig kwi-ribbon ukuvula uluhlu lokuhlaselwa komsebenzi.
- Cofa kwi- SUM kuloluhlu ukukhupha ibhokisi yencoko yencoko yomsebenzi.
- Kwibhokisi yencoko, nqakraza kumgca weNombolo1 .
- Cofa kwifowuni B2 ukungena kule fayile yeselenki kwibhokisi yencoko. Le ndawo yipopota yokugqibela yefomula;
- Kwibhokisi yencoko, nqakraza kumgca weNombolo2 .
- Faka umsebenzi olandelayo we-OFFSET: I- OFFSET (B6, -1,0) ukwenza ifom ye- endom enamandla.
- Cofa u- OK ukugqiba umsebenzi kwaye uvale ibhokisi yencoko.
Imali engama-5679.15 ibonakala kwiseli B7.
Xa ucofa kwi-cell B3, umsebenzi opheleleyo = ISUM (B2: OFFSET (B6, -1,0)) ibonakala kwifom yefomula ngaphezu kwephepha lokusebenzela.
Ukongeza iDatha yoThengiso lokuThengisa
Ukongeza idatha yokuthengisa yosuku olulandelayo:
- Nqakraza ngakwesokudla kwintloko yomqolo kumqolo we-6 ukuvula imenyu yomongo.
- Kwimenyu, nqakraza Faka kwifowuni entsha kwiphepha lokusebenzela.
- Ngenxa yoko, i-SUM OFFSET ifomula ifom yehla kwi-cell B7 kunye nomgca 6 ngoku ayinalutho.
- Cofa kwi cell A6 .
- Faka inombolo ye- 5 ukubonisa ukuba inani lokuthengisa lemihla yesihlanu lifakwe.
- Cofa kwifowuni B6.
- Thayipha inombolo ye -1458.25 ye-$ uze ucofe iqhosha lokungena kwikhibhodi.
Ukuhlaziywa kweSeli B7 kwixabiso elitsha lama -7137.40.
Xa ucofa kwi-cell B7, ifom ehlaziyiweyo = SUM (B2: OFFSET (B7, -1,0)) ibonakala kwifom yefomula.
Qaphela : Umsebenzi we-OFFSET uneziphakamiso ezimbini zokuzikhethela: Ubukhulu nobubanzi, eziye zashiywa kulo mzekelo.
Ezi ngxabano zingasetyenziselwa ukuxelela umsebenzi we-OFFSET umlo wesiphumo ngokwemiqathango emininzi ephezulu kunye namanqaku amaninzi.
Ngokushiya ezi ngxabano, umsebenzi, ngokungagqibekanga, isebenzisa ukuphakama nobubanzi beengxabano zeNgxelo, apho, kulo mzekelo ungowomgca ophezulu kunye nekhole enye.