Table of Contents
OFFSET function in Excel इस Post में हम जानेंगे कि Offset Function को Other Excel functions जैसे COUNTA(), MATCH() आदि Formula के साथ combine करके कैसे, एक dynamic और Powerful Formula बनाया जा सकता है। लेकिन इससे पहले यह समझना जरुरी है कि Offset Function क्या है और Excel Sheet में इसका Use कैसे करते है। इसके लिए आप नीचे दिए गए Link पर Click करके Post को जरूर पढ़े –
Offset Function with Counta in Excel
OFFSET function in Excel OFFSET function in Excel को Counta function के साथ Use करने के लिए हमने यहाँ Data तैयार किया है। जैसा कि आप नीचे Image में देख सकते है-
इस Data में तीन columns दिए गए है- “Days”, “Products name” और “Total sales”.यहाँ हमे लास्ट पाँच दिन के टोटल सेल्स निकालनी है। Total Sales निकालने लिए हम Formula इस प्रकार लगाएंगे –
=SUM(OFFSET(A1,COUNTA(C2:C8)-4,2,5,1))
Result – 1,45,000
Formula को समझे –
Formula =SUM(OFFSET(A1, COUNTA(C2:C8)-4,2,5,1)) में सबसे पहले हम offset Formula को समझेंगे –
Offset function के सबसे पहले Arguments reference में हमने Cell A1 को Select किया है जहाँ से offset Formula Start होगा।
अब दूसरे Arguments row में हमने counta Formula का इस्तेमाल किया है। इस Formula में हमने Column C में दिए गए Sales Range को Select किया है। सबसे पहले counta Formula Column C के सभी non-blank Cell को count करेगा और उसके बाद Four को Minus करेगा।
=OFFSET(A1, COUNTA(C2:C8)-4,2,5,1)
=OFFSET(A1, 7 -4 ,2,5,1)
=SUM(OFFSET(A1, 3,2,5,1))
Note – यहाँ हमने counta Formula में Four को इसलिए minus किया है क्योंकि हमे wed से टोटल सेल्स का सम निकालना है जो कि Offset function के अनुसार तीसरे row में है।
offset के तीसरे Argument cols में हमने 2 दिया है क्योंकि data में Total Sales का Index Number 2 है। इसके अलावा height में 5 और width 1 Define किया गया है। Height में 5 इसलिए दिया गया है क्योंकि हमे Data से 5 rows का sum निकालना है और width 1 Define किया क्योंकि हमे सिर्फ एक Column का sum निकालना है।
Excel Offset Function for Dynamic ange in Excel
अब हम जानेंगे कि Excel Offset Function का इस्तेमाल dynamic range बनाने के लिए किस प्रकार किया जाता है। Formula में dynamic range एक ऐसा Range होता है जो data में Changes के साथ automatically adjust हो जाता है। अगर हम टेबल में कोई नया Data add करते है या remove करते है तो यह परिस्थिति के अनुसार अपने आप को adjust कर लेता है। जिससे हमे बार – बार Formula में Changes करने की जरुरत नहीं पड़ती।
तो चलिए इसे Example के द्वारा समझते है- यहाँ हमने एक Data लिया है जिसमे Month के अनुसार sales data दिया गया है। इस data में हमे लास्ट टू मन्थ्स के sales का सम निकालना है।
इसके लिए हमे Formula को इस तरह dynamic बनाना है कि अगर हम Main Data में कोई new row Add करते है तो Formula मे कोई Changes करने की जरुरत न पड़े। इसके लिए Formula कुछ इस प्रकार लगाएंगे –
=SUM(OFFSET(A1,COUNTA(A:A)-2,COUNTA(2:2)-3, 2,3))
Result – 2654.00
Formula को समझे –
यहाँ Formula में आप देख सकते है कि Offset Funcion को dynamic बनाने के लिए हमने rows और cols की जगह Counta Function इस्तेमाल किया है। सबसे पहले हमने reference के लिए A1 को Select किया है।
दूसरे argument rows के लिए “Counta(A:A)-2” का इस्तेमाल किया गया है जिसका मतलब है कि यह column A के सभी non -blank Cells को Count करके 2 को minus करेगा। यह Result Six return करता है , जो कि offset function के लिए row Number का काम करेगा।
इसी प्रकार cols के लिए “Counta(2:2)-3” का Use किया गया है। यह Formula 2nd row के non -blank Cells को Count करके 3 को minus करेगा। जिससे Result हमे 1 मिलेगा। “1” offset के लिए Column Number का काम करेगा।
Offset के लास्ट मे दो arguments height और width में- height के लिए 2 और width 3 डिफाइन किया गया है।
Note- यहाँ हमे Two rows और Three Column (column B ,C,D) की values का sum निकालना है इसलिए height के लिए 2 और width के लिए 3 डिफाइन किया गया है।
=SUM(OFFSET(A1,COUNTA(A:A)-2,COUNTA(2:2)-3, 2, 3))
=SUM(OFFSET(A1, 8-6 , 4-3 , 2, 3))
=SUM(OFFSET(A1, 6 , 1, 2, 3))
=SUM(B7:D8)
2654.00
Offset with Match Function in Excel
Excel Offset Function के साथ Match Function का इस्तेमाल बहुत ही useful माना जाता है। Offset function में rows और cols argument की जगह Match Function का इस्तेमाल करके उसे dynamic बनाया जाता है। आईये Example के द्वारा इसे में समझते है –
हमने यहाँ एक Excel Data लिया है जिसमे कुछ person के Name, Months के Name और Total Sales दी गयी है। हमे offset और match का एक साथ इस्तेमाल करके “sumedh” का total Sales निकालना है।
इसके लिए हमने Cell F1 में Formula इस प्रकार लगाया है-
=OFFSET(A1, MATCH(E2, $A$2:$A$8, 0), MATCH(F1,$A$1:$C$1,0)-1)
Result – 2000
Formula को समझे
Offset Function के पहले Argument में हमने A1 Cell को Select reference के लिए Select किया है।
दूसरे argument rows के लिए हमने Match function का Use किया है। Match Function E2 में दी गयी Value को main Data में Search करके इसकी position को find करता है, जो कि Five है।
इसी प्रकार तीसरे argument cols के लिए भी हमने Match Function का इस्तेमाल किया है। जिसमे Cell F1 में दी गयी Value की position को find किया गया है जो कि three है। इसके बाद match function से One को Minus किया गया है क्यों कि Offset function के अनुसार टोटल सेल्स कॉलम 2nd Position पर है।
=OFFSET(A1, MATCH(E2, $A$2:$A$8, 0), MATCH(F1,$A$1:$C$1,0)-1)
=OFFSET(A1, 5, 3-1)
=OFFSET(A1, 5, 2)
2000
उम्मीद है कि यह Post आपके लिए Helpful होगी। अगर कोई सवाल या सुझाव हो तो Comment करे।
Read Also Mail Merge in MS Word in Hindi
Pingback: Vlookup in Excel in Hindi | Advance Excel Formulas - Last-Date