એક્સેલ SUM અને OFFSET ફોર્મ્યુલા

ડેટાની ગતિશીલ શ્રેણી માટે સરેરાશ શોધવા માટે SUM અને OFFSET નો ઉપયોગ કરો

જો તમારી એક્સેલ કાર્યપત્રક કોષોની બદલાતા શ્રેણીને આધારે ગણતરીઓનો સમાવેશ કરે છે, તો SUM OFFSET ફોર્મુલામાં SUM અને OFFSET વિધેયોનો ઉપયોગ કરીને ગણતરીને અપ ટુ ડેટ રાખવાની ક્રિયા સરળ બનાવે છે.

SUM અને OFFSET કાર્યો સાથે ગતિશીલ રેંજ બનાવો

જો તમે સતત બદલાતી ગાળા માટે ગણતરીઓનો ઉપયોગ કરો છો - જેમ કે મહિના માટે કુલ વેચાણ - OFFSET કાર્ય તમને ગતિશીલ શ્રેણીને સેટ કરવાની પરવાનગી આપે છે જે દરેક દિવસના વેચાણની સંખ્યા ઉમેરાય છે તેમ બદલાતી રહે છે.

પોતાના દ્વારા, SUM કાર્ય સામાન્ય રીતે સમાવિષ્ટ કરવામાં આવેલી રેંજમાં દાખલ કરવામાં આવેલી માહિતીના નવા કોષોને સમાવી શકે છે.

એક અપવાદ ત્યારે થાય છે જ્યારે ડેટા સેલમાં શામેલ થાય છે જ્યાં કાર્ય હાલમાં સ્થિત થયેલ છે.

આ લેખમાં જે ઇમેજ ઈમેજ છે તે સાથે, દરેક દિવસની નવી વેચાણની વિગતો યાદીના તળિયે ઉમેરવામાં આવે છે, જે દર વખતે નવા ડેટાને ઉમેરવામાં આવે તે વખતે દરેક સેલને સતત એક સેલમાં ખસેડવાની ફરજ પાડે છે.

જો SUM ફંક્શનનો ઉપયોગ તેના ડેટાને કુલ કરવા માટે કરવામાં આવે છે, તો વિધેયના દલીલ તરીકે વપરાતા કોશિકાઓના રેન્જને સંશોધિત કરવા માટે દર વખતે નવો ડેટા ઉમેરવામાં આવવો જરૂરી બનશે.

એકસાથે SUM અને OFFSET વિધેયોનો ઉપયોગ કરીને, તેમ છતાં, પૂર્ણ થયેલ શ્રેણી જે ગતિશીલ બને છે. બીજા શબ્દોમાં કહીએ તો, તે ડેટાના નવા કોષોને સમાવવા બદલ બદલે છે. ડેટાના નવા કોશિકાઓના ઉમેરાથી સમસ્યા ઊભી થતી નથી કારણ કે શ્રેણી દરેક નવા કોષ ઉમેરવામાં આવે તે પ્રમાણે સંતુલિત રહે છે.

સિન્ટેક્સ અને દલીલો

આ ટ્યુટોરીયલ સાથે અનુસરવા માટે આ લેખ સાથેની છબીનો સંદર્ભ લો.

આ સૂત્રમાં, SUM કાર્યનો ઉપયોગ તેના દલીલ તરીકે પૂરા પાડવામાં આવેલા ડેટાની શ્રેણી માટે થાય છે. આ શ્રેણી માટેનો પ્રારંભ બિંદુ સ્થિર છે અને સૂત્ર દ્વારા પૂર્ણ કરવા માટે પ્રથમ નંબરના સેલ સંદર્ભ તરીકે ઓળખવામાં આવે છે.

OFFSET ફંક્શન SUM કાર્યની અંદર નેસ્ટ કરવામાં આવે છે અને સૂત્ર દ્વારા કુલ ડેટાના ડાયનેમિક એન્ડપોઇંટને બનાવવા માટે વપરાય છે. આ સૂત્રના સ્થાનની ઉપરના એક કોષ સુધીના અંત્યબિંદુને ગોઠવીને પરિપૂર્ણ થાય છે.

સૂત્રનું વાક્યરચના :

= SUM (રેંજ પ્રારંભ: OFFSET (સંદર્ભ, પંક્તિઓ, સ્તંભો))

રેંજ પ્રારંભ - (આવશ્યક) કોષોની શ્રેણી માટે પ્રારંભિક બિંદુ કે જે SUM કાર્ય દ્વારા પૂર્ણ કરવામાં આવશે. ઉદાહરણ ઈમેજમાં, આ સેલ B2 છે.

સંદર્ભ - (આવશ્યક) શ્રેણીના અંતબિંદુની ગણતરી કરવા માટે વપરાયેલા કોષ સંદર્ભમાં ઘણી પંક્તિઓ અને કૉલમ્સ દૂર છે. ઉદાહરણ ઈમેજમાં, સંદર્ભ દલીલ સૂત્ર માટેનો સેલ સંદર્ભ છે કારણ કે હંમેશા શ્રેણીને સૂત્રની ઉપર એક કોષ સમાપ્ત કરવા માગે છે.

પંક્તિઓ - (આવશ્યક) ઑફસેટની ગણતરીમાં ઉપયોગમાં લેવાયેલ સંદર્ભ દલીલ ઉપર અથવા નીચે પંક્તિઓની સંખ્યા. આ મૂલ્ય હકારાત્મક, નકારાત્મક અથવા શૂન્ય પર સેટ કરી શકાય છે.

જો ઓફસેટનું સ્થાન સંદર્ભ દલીલ ઉપર છે, તો આ મૂલ્ય નકારાત્મક છે. જો તે નીચે છે, પંક્તિઓ દલીલ હકારાત્મક છે. જો ઓફસેટ એ જ પંક્તિમાં સ્થિત છે, તો આ દલીલ શૂન્ય છે. આ ઉદાહરણમાં, ઑફસેટ સંદર્ભ દલીલ ઉપર એક પંક્તિની શરૂઆત કરે છે, તેથી આ દલીલ માટેનું મૂલ્ય નકારાત્મક એક (-1) છે.

કોલ્સ - (આવશ્યક) ઑફસેટની ગણતરીમાં વપરાતા સંદર્ભ દલીલની ડાબે અથવા જમણા કૉલમ્સની સંખ્યા. આ મૂલ્ય હકારાત્મક, નકારાત્મક અથવા શૂન્ય પર સેટ કરી શકાય છે

જો ઓફસેટનું સ્થાન સંદર્ભ દલીલની ડાબી બાજુએ છે, તો આ મૂલ્ય નકારાત્મક છે. જમણે, કોલ્સ દલીલ હકારાત્મક છે. આ ઉદાહરણમાં, કુલ કરાયેલા ડેટા સૂત્ર તરીકે સમાન સ્તંભમાં છે તેથી આ દલીલ માટેનું મૂલ્ય શૂન્ય છે.

કુલ વેચાણ ડેટા માટે SUM OFFSET ફોર્મ્યુલાનો ઉપયોગ કરવો

આ ઉદાહરણ કાર્યપત્રકના કૉલમ B માં સૂચિબદ્ધ દૈનિક વેચાણના આંકડાઓ માટે કુલ પરત કરવા SUM OFFSET સૂત્રનો ઉપયોગ કરે છે.

પ્રારંભમાં, સૂત્ર કોષ બી 6 માં દાખલ થયો હતો અને ચાર દિવસ માટે વેચાણ ડેટાને કુલ કરાયો હતો.

આગળનું પગલું એ SUM OFFSET સૂત્રને પાંચમા દિવસની કુલ વેચાણની જગ્યા બનાવવા માટે પંક્તિને નીચે ખસેડવાનું છે.

આ નવી પંક્તિ 6 દાખલ કરીને પરિપૂર્ણ થાય છે, જે સૂત્ર નીચે પંક્તિ 7 પર ખસે છે.

આ પગલાને પરિણામે, એક્સેલ સૂત્રો દ્વારા સૂચિત શ્રેણીમાં આપમેળે સંદર્ભ દલીલને સેલ B7 પર અપડેટ કરે છે અને સેલ બી 6 ને ઉમેરે છે.

SUM OFFSET ફોર્મ્યુલા દાખલ

1. સેલ બી 6 પર ક્લિક કરો, જે સ્થાન છે જ્યાં ફોર્મુલાનાં પરિણામો શરૂઆતમાં દર્શાવવામાં આવશે.
2. રિબન મેનૂના ફોર્મ્યુલા ટેબ પર ક્લિક કરો.
3. વિધેય ડ્રોપ-ડાઉન સૂચિ ખોલવા માટે રિબનમાંથી મઠ અને ટ્રિગ પસંદ કરો
4. ફંક્શનનાં સંવાદ બૉક્સને લાવવા માટે સૂચિ પર ક્લિક કરો.
5. સંવાદ બૉક્સમાં, Number1 લીટી પર ક્લિક કરો.
6. સંવાદ બૉક્સમાં આ સેલ સંદર્ભ દાખલ કરવા માટે સેલ B2 પર ક્લિક કરો. આ સ્થાન સૂત્ર માટે સ્ટેટિક એન્ડપોઇંટ છે;
7. સંવાદ બૉક્સમાં, Number2 લીટી પર ક્લિક કરો.
8. સૂત્ર માટે ડાયનેમિક એન્ડપોઇંટ રચવા માટે OFFSET કાર્ય: OFFSET (બી 6, -1.0) દાખલ કરો.
9. કાર્ય પૂર્ણ કરવા માટે ઑકે ક્લિક કરો અને સંવાદ બૉક્સ બંધ કરો.

કુલ $ 5679.15 સેલ B7 માં દેખાય છે.

જ્યારે તમે સેલ B3 પર ક્લિક કરો છો, ત્યારે પૂર્ણ કાર્ય = SUM (B2: OFFSET (બી 6, -1.0) કાર્યપત્રક ઉપર સૂત્ર બારમાં દેખાય છે.

આગામી દિવસના સેલ્સ ડેટાને ઉમેરી રહ્યા છે

બીજા દિવસે વેચાણ ડેટા ઉમેરવા માટે:

1. સંદર્ભ મેનૂ ખોલવા માટે પંક્તિ 6 માટે પંક્તિ હેડર પર રાઇટ-ક્લિક કરો.
2. મેનૂમાં, કાર્યપત્રમાં નવી પંક્તિ શામેલ કરવા માટે સામેલ કરો પર ક્લિક કરો .
3. પરિણામે, SUM OFFSET સૂત્ર સેલ B7 અને પંક્તિ 6 પર ફરે છે હવે ખાલી છે.
4. સેલ A6 પર ક્લિક કરો.
5. પાંચમી દિવસ માટે વેચાણ કુલ દાખલ કરવામાં આવી રહી છે તે દર્શાવવા માટે નંબર 5 દાખલ કરો.
6. સેલ બી 6 પર ક્લિક કરો.
7. $ 1458.25 નંબર લખો અને કિબોર્ડ પર Enter કી દબાવો.

સેલ B7 $ 7137.40 ની કુલ કુલ અપડેટ કરે છે.

જ્યારે તમે સેલ B7 પર ક્લિક કરો છો, ત્યારે અપડેટ સૂત્ર = SUM (B2: OFFSET (B7, -1.0)) ફોર્મુલા બારમાં દેખાય છે.

નોંધ : OFFSET કાર્યમાં બે વૈકલ્પિક દલીલો છે: ઊંચાઈ અને પહોળાઈ, જે આ ઉદાહરણમાં અવગણવામાં આવી હતી.

આ દલીલોનો ઉપયોગ OFFSET કાર્યને આઉટપુટના આકારને જણાવવા માટે કરી શકાય છે, જેમાં તે એટલી બધી હરોળો છે અને ઘણા બધા સ્તંભો વિશાળ છે.

આ દલીલોને બાદ કરીને, કાર્ય, મૂળભૂત રીતે, સંદર્ભ દલીલની ઊંચાઈ અને પહોળાઈનો ઉપયોગ કરે છે, જે આ ઉદાહરણમાં એક પંક્તિ ઉચ્ચ અને એક કૉલમ પહોળી છે.