How to use OFFSET function - with other excel functions | Hindi

Hello friends....इस पोस्ट में हम जानेंगे कि Offset Function को अन्य एक्सेल functions जैसे COUNTA(), MATCH() आदि functions के साथ combine करके कैसे, एक dynamic और पावरफुल फार्मूला बनाया जा सकता है। लेकिन इससे पहले यह समझना जरुरी है कि Offset Function क्या है और एक्सेल शीट में इसका प्रयोग कैसे करते है। इसके लिए आप नीचे दिए गए लिंक पर क्लिक करके मेरी पोस्ट को जरूर पढ़े -

Basics of excel Offset function with example

इस पोस्ट में आगे है -

Offset function with counta in excel hindi

Excel function for dynamic range in excel in hindi

Offset with match function in excel hindi

Offset function with counta in excel hindi

excel Offset function को Counta function के साथ इस्तेमाल करने के लिए हमने यहाँ डाटा तैयार किया है। जैसा कि आप नीचे इमेज में देख सकते है-

इस डाटा में तीन columns दिए गए है- "Days", "Products name"और  "Tatal sales".यहाँ हमे last 5 days की total sales निकालनी है। Total sales निकालने लिए हम फार्मूला इस प्रकार लगाएंगे -

=SUM(OFFSET(A1,COUNTA(C2:C8)-4,2,5,1))  

Result - 1,45,000

फार्मूला को समझे -

फार्मूला =SUM(OFFSET(A1, COUNTA(C2:C8)-4,2,5,1)) में सबसे पहले हम offset फंक्शन को समझेंगे -

Offset function के सबसे पहले आर्गुमेंट reference में हमने सेल A1 को सेलेक्ट किया है जहा से offset फंक्शन शुरू होगा। 

अब दूसरे आर्गुमेंट row में हमने counta फंक्शन का प्रयोग किया है। इस फंक्शन में हमने कॉलम C में दिए गए sales रेंज को सेलेक्ट किया है। सबसे पहले counta फंक्शन कॉलम C के सभी non-blank सेल को count करेगा और उसके बाद 4 को 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 फंक्शन में 4 को इसलिए minus किया है क्युकि हमे wed से total sales का sum निकालना है जो कि Offset function के अनुसार 3rd row में है। 

offset के तीसरे आर्गुमेंट cols में हमने 2 दिया है क्युकि data में Total Sales का इंडेक्स नंबर 2 है। इसके अलावा height में 5 और width 1 डिफाइन किया गया है। Height में 5 इसलिए दिया गया है क्युकि हमे डाटा से 5 rows का sum निकालना है और width 1  डिफाइन किया क्युकि हमे सिर्फ एक कॉलम का sum निकालना है। 

Excel Offset function for dynamic range in excel in hindi

अब हम जानेंगे कि Excel Offset Function का प्रयोग dynamic range बनाने के लिए किस प्रकार किया जाता है। फार्मूला में dynamic range एक ऐसा रेंज होता है जो data में बदलाव के साथ automatically adjust हो जाता है। अगर हम table में कोई नया डाटा add करते है या remove करते है तो यह परिस्थिति के अनुसार अपने आप को adjust कर लेता है। जिससे हमे बार - बार फार्मूला में बदलाव करने की जरुरत नहीं पड़ती।

 

तो चलिए इसे उदाहरण के द्वारा समझते है- यहाँ हमने एक डाटा लिया है जिसमे Month के अनुसार sales data दिया गया है। इस डाटा में हमे last 2 months के sales का sum निकालना है। 

इसके लिए हमे फार्मूला को इस तरह dynamic बनाना है कि अगर हम main डाटा में कोई new row जोड़ते है तो फार्मूला कोई बदलाव करने की जरुरत न पड़े। इसके लिए फार्मूला कुछ इस प्रकार लगाएंगे -

=SUM(OFFSET(A1,COUNTA(A:A)-2,COUNTA(2:2)-3, 2,3))

 

Result - 2654.00

फार्मूला को समझे -

यहाँ फार्मूला में आप देख सकते है कि Offset Funcion को dynamic बनाने के लिए हमने rows और cols की जगह Counta Function प्रयोग किया है। सबसे पहले हमने reference  के लिए A1 को सेलेक्ट किया है। 

दूसरे argument rows के लिए "Counta(A:A)-2" का प्रयोग किया गया है जिसका मतलब है कि यह column A के सभी non -blank सेल्स को काउंट करके 2 को minus करेगा। यह रिजल्ट 6 return करता है , जो कि offset function के लिए row नंबर का काम करेगा।

इसी प्रकार cols के लिए "Counta(2:2)-3" का प्रयोग किया गया है। यह फंक्शन 2nd row के non -blank सेल्स को काउंट करके 3 को minus करेगा। जिससे रिजल्ट हमे 1 मिलेगा। "1" offset के लिए कॉलम नंबर का काम करेगा। 

Offset के last दो arguments height और width में- height के लिए 2 और width 3 define किया गया है.

Note- यहाँ हमे दो rows और तीन कॉलम (column B ,C,D) की values का sum निकालना है इसलिए height के लिए 2 और width के लिए 3 define किया गया है। 

=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 hindi

एक्सेल Offset Function के साथ Match Function का प्रयोग बहुत ही useful माना जाता है। Offset function में rows और cols argument की जगह Match Function का इस्तेमाल करके उसे dynamic बनाया जाता है. आईये उदाहरण के द्वारा इसे डिटेल में समझते है -

हमने यहाँ एक एक्सेल डाटा लिया है जिसमे कुछ person के नाम , Months के नाम और Total Sales दी गयी है। हमे offset और match का एक साथ प्रयोग करके "sumedh" का total Sales निकालना है। 

इसके लिए हमने सेल F1 में फार्मूला इस प्रकार लगाया है-

=OFFSET(A1, MATCH(E2, $A$2:$A$8, 0), MATCH(F1,$A$1:$C$1,0)-1)

Result - 2000

  

फार्मूला को समझे 

Offset Function के पहले argument में हमने A1 सेल को सेलेक्ट reference के लिए सेलेक्ट किया है। 

दूसरे argument rows के लिए हमने Match function का प्रयोग किया है. Match Function E2 में दी गयी वैल्यू को main डाटा में सर्च करके इसकी position को find करता है, जो कि 5 है। 

इसी प्रकार तीसरे argument cols के लिए भी हमने Match Function का प्रयोग किया है। जिसमे सेल F1 में दी गयी वैल्यू की position को find किया गया है जो कि 3 है। इसके बाद match function से 1 को माइनस किया गया है क्यों कि Offset function के अनुसार Total Sales कॉलम 2nd पोजीशन पर है। 

=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

उम्मीद है कि यह पोस्ट आपके लिए हेल्पफुल होगी। अगर कोई सवाल या सुझाव हो तो कमेंट करे। Thank you. 

Other posts

Excel features (in Hindi)

Ms-excel definition | Home tab in ms-excel | Data validation

| How to create dropdown in excel | Chart in excel in hindi | Pivot table in excel in hindi

Learn Excel Functions

basic Excel functions 

Advance excel function and formulas

Tags-  Offset function in excel hindi, offset with counta function in excel hindi,offset with match function in excel hindi, offset function for dynamic range in excel in hindi