एक्सेल फ़ार्मुलों से एक और शब्द बहुत परिचित है,

एक्सेल फॉर्मूला क्या है? 

माइक्रोसॉफ्ट एक्सेल में, सूत्र एक व्यंजक होता है जो कक्षों की एक श्रेणी में मानों पर कार्य करता है। ये सूत्र त्रुटि होने पर भी परिणाम लौटाते हैं। एक्सेल सूत्र आपको जोड़, घटाव, गुणा और भाग जैसी गणनाएँ करने में सक्षम बनाते हैं। इसके अलावा, आप एक्सेल में कक्षों की एक श्रेणी के लिए औसत निकाल सकते हैं और प्रतिशत की गणना कर सकते हैं, दिनांक और समय मानों में हेरफेर कर सकते हैं, और भी बहुत कुछ कर सकते हैं।

एक्सेल में सूत्र

एक्सेल फ़ार्मुलों से एक और शब्द बहुत परिचित है, और वह है " फ़ंक्शन "। "फ़ॉर्मूला" और "फ़ंक्शन" ये दो शब्द कभी-कभी एक-दूसरे के स्थान पर इस्तेमाल किए जा सकते हैं। ये आपस में बहुत जुड़े हुए हैं, फिर भी अलग हैं। एक फ़ार्मूला बराबर के चिह्न से शुरू होता है। वहीं, फ़ंक्शन का इस्तेमाल उन जटिल गणनाओं को करने के लिए किया जाता है जिन्हें मैन्युअल रूप से नहीं किया जा सकता। एक्सेल में फ़ंक्शन के नाम उनके इच्छित उपयोग को दर्शाते हैं।

नीचे दिया गया उदाहरण दर्शाता है कि हमने '*' ऑपरेटर के साथ गुणन सूत्र का मैन्युअल रूप से उपयोग कैसे किया है।

नमूना सूत्र: "=A2*B2" 

चित्र: माइक्रोसॉफ्ट एक्सेल फॉर्मूला

नीचे दिया गया उदाहरण दिखाता है कि हमने गुणन करने के लिए 'PRODUCT' फ़ंक्शन का उपयोग कैसे किया है। जैसा कि आप देख सकते हैं, हमने यहाँ गणितीय ऑपरेटर का उपयोग नहीं किया है। 

नमूना सूत्र: "=PRODUCT(A2,B2)" 

एक्सेल फ़ार्मुलों और फ़ंक्शंस की मदद से आप अपने काम कुशलतापूर्वक कर सकते हैं और समय भी बचा सकते हैं। आइए आगे बढ़ते हैं और एक्सेल में उपलब्ध विभिन्न प्रकार के फ़ंक्शंस के बारे में जानें और ज़रूरत पड़ने पर संबंधित फ़ार्मुलों का इस्तेमाल करें।

यदि आप एक्सेल से परे एनालिटिक्स में एक मजबूत आधार बनाने का लक्ष्य रखते हैं, तो डेटा-संचालित निर्णय लेने में अपनी विशेषज्ञता को गहरा करने के लिए डेटा एनालिटिक्स प्रमाणन पर विचार करें ।

शीर्ष एक्सेल सूत्र और फ़ंक्शन

डेटासेट पर आप किस तरह का ऑपरेशन करना चाहते हैं, इसके आधार पर एक्सेल में ढेरों फ़ॉर्मूले और फ़ंक्शन उपलब्ध हैं। हम गणितीय ऑपरेशन, कैरेक्टर-टेक्स्ट फ़ंक्शन, डेटा और समय, sumif-countif, और कुछ लुकअप फ़ंक्शन से संबंधित फ़ॉर्मूले और फ़ंक्शन पर गौर करेंगे। 

1. योग

SUM() फ़ंक्शन, जैसा कि नाम से ही स्पष्ट है, चयनित सेल मानों की श्रेणी का योग देता है। यह गणितीय संक्रिया करता है, जो योग है। इसका एक उदाहरण नीचे दिया गया है:

=योग(C2:C4)

चित्र: एक्सेल में योग फ़ंक्शन

जैसा कि आप ऊपर देख सकते हैं, प्रत्येक इकाई की कुल बिक्री राशि ज्ञात करने के लिए, हमें बस "=SUM(C2:C4)" फ़ंक्शन टाइप करना था। इससे 300, 385 और 480 अपने आप जुड़ जाते हैं। परिणाम C5 में संग्रहीत होता है। 

2. औसत

AVERAGE() फ़ंक्शन चयनित सेल मानों की श्रेणी का औसत निकालने पर केंद्रित है। जैसा कि नीचे दिए गए उदाहरण से देखा जा सकता है, कुल बिक्री का औसत निकालने के लिए, आपको बस यह टाइप करना होगा:

=औसत(C2, C3, C4)

चित्र: एक्सेल में औसत फ़ंक्शन

यह स्वचालित रूप से औसत की गणना करता है, और आप परिणाम को अपने इच्छित स्थान पर संग्रहीत कर सकते हैं।

3. गिनती

COUNT() फ़ंक्शन किसी श्रेणी में उन कक्षों की कुल संख्या गिनता है जिनमें कोई संख्या होती है। इसमें रिक्त कक्ष और संख्यात्मक के अलावा किसी अन्य प्रारूप में डेटा रखने वाले कक्ष शामिल नहीं होते हैं। 

= गिनती(C1:C4)

 

चित्र: माइक्रोसॉफ्ट एक्सेल फ़ंक्शन - गणना

जैसा कि ऊपर देखा गया है, यहाँ हम C1 से C4 तक, आदर्श रूप से चार कक्षों की गिनती कर रहे हैं। लेकिन चूँकि COUNT फ़ंक्शन केवल संख्यात्मक मानों वाले कक्षों को ही ध्यान में रखता है, इसलिए उत्तर 3 है क्योंकि यहाँ "कुल बिक्री" वाला कक्ष छोड़ दिया गया है। 

यदि आपको संख्यात्मक मानों, टेक्स्ट और किसी अन्य डेटा फ़ॉर्मेट वाले सभी कक्षों की गणना करनी है, तो आपको 'COUNTA()' फ़ंक्शन का उपयोग करना होगा। हालाँकि, COUNTA() किसी भी रिक्त कक्ष की गणना नहीं करता है।

कोशिकाओं की श्रेणी में मौजूद रिक्त कोशिकाओं की संख्या गिनने के लिए COUNTBLANK() का उपयोग किया जाता है।  

4. उप-योग

आगे बढ़ते हुए, आइए अब समझते हैं कि सबटोटल फ़ंक्शन कैसे काम करता है। SUBTOTAL() फ़ंक्शन डेटाबेस में सबटोटल लौटाता है। आप अपनी ज़रूरत के अनुसार औसत, गणना, योग, न्यूनतम, अधिकतम, न्यूनतम और अन्य चुन सकते हैं। आइए ऐसे ही दो उदाहरणों पर नज़र डालें।

चित्र: एक्सेल में उप-योग फ़ंक्शन

ऊपर दिए गए उदाहरण में, हमने A2 से A4 तक की कोशिकाओं पर उप-योग की गणना की है। जैसा कि आप देख सकते हैं, प्रयुक्त फ़ंक्शन है

=उप-योग(1, A2: A4)

उप-योग सूची में "1" औसत को दर्शाता है। इसलिए, उपरोक्त फ़ंक्शन A2: A4 का औसत देगा और इसका उत्तर 11 है, जो C5 में संग्रहीत है। इसी प्रकार,

“=उप-योग(4, A2: A4)”

यह A2 से A4 तक अधिकतम मान वाले सेल का चयन करता है, जो 12 है। फ़ंक्शन में "4" को शामिल करने से अधिकतम परिणाम मिलता है। 

चित्र: एक्सेल में गणना फ़ंक्शन

5. मापांक

MOD() फ़ंक्शन किसी विशिष्ट संख्या को भाजक से भाग देने पर शेषफल लौटाने पर काम करता है। आइए अब बेहतर समझ के लिए नीचे दिए गए उदाहरणों पर एक नज़र डालें।

• पहले उदाहरण में, हमने 10 को 3 से भाग दिया है। शेषफल की गणना फ़ंक्शन का उपयोग करके की जाती है, परिणाम B2 में संग्रहीत होता है। हम सीधे “=MOD(10,3)” भी टाइप कर सकते हैं क्योंकि इससे वही उत्तर मिलेगा।

      =एमओडी(ए2,3)

चित्र: एक्सेल में मापांक फ़ंक्शन

• इसी प्रकार, यहाँ भी हमने 12 को 4 से भाग दिया है। शेषफल 0 है, जो B3 में संग्रहीत है। 

चित्र: एक्सेल में मापांक फ़ंक्शन

6. शक्ति

फ़ंक्शन "Power()" किसी संख्या को एक निश्चित घात तक बढ़ाने पर उसका परिणाम देता है। आइए नीचे दिए गए उदाहरणों पर एक नज़र डालें:

चित्र: एक्सेल में पावर फ़ंक्शन

जैसा कि आप ऊपर देख सकते हैं, A2 में संग्रहीत 10 की घात 3 तक ज्ञात करने के लिए, हमें टाइप करना होगा:

=पावर(A2,3)

एक्सेल में पावर फ़ंक्शन इस प्रकार काम करता है।

7. छत

इसके बाद, हमारे पास सीलिंग फ़ंक्शन है। CEILING() फ़ंक्शन किसी संख्या को उसके निकटतम महत्त्व के गुणज तक पूर्णांकित करता है। 

      

चित्र: एक्सेल में सीलिंग फ़ंक्शन

=सीलिंग(A2,1)

35.316 के लिए 5 का निकटतम उच्चतम गुणज 40 है।

8. फर्श

सीलिंग फ़ंक्शन के विपरीत, फ़्लोर फ़ंक्शन किसी संख्या को महत्व के निकटतम गुणज तक पूर्णांकित करता है।

चित्र: एक्सेल में फ़्लोर फ़ंक्शन

=फ़्लोर(A2,1)

35.316 के लिए 5 का निकटतम न्यूनतम गुणज 35 है।

9. संयोजित करें

यह फ़ंक्शन कई टेक्स्ट स्ट्रिंग्स को एक टेक्स्ट स्ट्रिंग में मर्ज या संयोजित करता है। नीचे इस फ़ंक्शन को निष्पादित करने के विभिन्न तरीके दिए गए हैं।

• इस उदाहरण में, हमने निम्न वाक्यविन्यास का प्रयोग किया है:

  =CONCATENATE(A25, " ", B25)

चित्र: एक्सेल में कॉन्कैटेनेट फ़ंक्शन

• इस उदाहरण में, हमने निम्न वाक्यविन्यास का प्रयोग किया है: 

        "=CONCATENATE(A27&" "&B27)"

चित्र: एक्सेल में कॉन्कैटेनेट फ़ंक्शन

एक्सेल में संयोजन ऑपरेशन को क्रियान्वित करने के ये दो तरीके थे।

10. लेन

फ़ंक्शन LEN() एक स्ट्रिंग में कुल वर्णों की संख्या लौटाता है। इसलिए, यह रिक्त स्थान और विशेष वर्णों सहित कुल वर्णों की गणना करेगा। नीचे Len फ़ंक्शन का एक उदाहरण दिया गया है।

    

चित्र: एक्सेल में Len फ़ंक्शन

आइये अब इस लेख की सूची में अगले एक्सेल फ़ंक्शन पर चलते हैं।

11. प्रतिस्थापित करें

जैसा कि नाम से पता चलता है, REPLACE() फ़ंक्शन टेक्स्ट स्ट्रिंग के भाग को एक अलग टेक्स्ट स्ट्रिंग से बदलने पर काम करता है। 

इसका सिंटैक्स है "=REPLACE(old_text, start_num, num_chars, new_text)"। यहाँ, start_num उस इंडेक्स स्थिति को दर्शाता है जहाँ से आप वर्णों को बदलना शुरू करना चाहते हैं। इसके बाद, num_chars उन वर्णों की संख्या दर्शाता है जिन्हें आप बदलना चाहते हैं। 

आइये देखें कि हम इस फ़ंक्शन का उपयोग किस प्रकार कर सकते हैं।

• यहाँ, हम A101 को B101 से प्रतिस्थापित कर रहे हैं

     =रिप्लेस(A15,1,1,"B")

   

चित्र: एक्सेल में रिप्लेस फ़ंक्शन

• अब हम A102 को A2102 से प्रतिस्थापित कर रहे हैं:

        “=REPLACE(A16,1,1, "A2")” 

चित्र: एक्सेल में रिप्लेस फ़ंक्शन

• अंत में, हम Adam के स्थान पर Saam लिख रहे हैं:

        “=REPLACE(A17,1,2, "Sa")” 

 

चित्र: एक्सेल में रिप्लेस फ़ंक्शन

आइये अब हम अपने अगले कार्य की ओर बढ़ते हैं। 

12. स्थानापन्न

SUBSTITUTE() फ़ंक्शन टेक्स्ट स्ट्रिंग में मौजूदा टेक्स्ट को नए टेक्स्ट से बदल देता है। 

वाक्यविन्यास है “=SUBSTITUTE(text, old_text, new_text, [instance_num])”।

यहाँ, [instance_num] एक से अधिक बार वर्तमान पाठ की सूचकांक स्थिति को संदर्भित करता है। 

नीचे इस फ़ंक्शन के कुछ उदाहरण दिए गए हैं:

• यहाँ, हम “मुझे पसंद है” के स्थान पर “उसे पसंद है” लिख रहे हैं: 

        “=SUBSTITUTE(A20, "मुझे पसंद है","उसे पसंद है")” 

  

चित्र: एक्सेल में स्थानापन्न फ़ंक्शन

• इसके बाद, हम सेल A21 में मूल टेक्स्ट में मौजूद दूसरे 2010 को “=SUBSTITUTE(A21,2010, 2016,2)” टाइप करके 2016 से प्रतिस्थापित कर रहे हैं।

चित्र: एक्सेल में स्थानापन्न फ़ंक्शन

• अब, हम मूल पाठ में दोनों 2010 को “=SUBSTITUTE(A22,2010,2016)” टाइप करके 2016 से बदल रहे हैं।

चित्र: एक्सेल में स्थानापन्न फ़ंक्शन

यह सब प्रतिस्थापन फ़ंक्शन के बारे में था, आइए अब हम अपने अगले फ़ंक्शन पर चलते हैं। 

13. बाएँ, दाएँ, मध्य

LEFT() फ़ंक्शन टेक्स्ट स्ट्रिंग की शुरुआत से वर्णों की संख्या देता है। वहीं, MID() फ़ंक्शन टेक्स्ट स्ट्रिंग के बीच से वर्णों को लौटाता है, बशर्ते कि शुरुआती स्थिति और लंबाई दी गई हो। अंत में, right() फ़ंक्शन टेक्स्ट स्ट्रिंग के अंत से वर्णों की संख्या लौटाता है।  

आइये इन कार्यों को कुछ उदाहरणों से समझें।

• नीचे दिए गए उदाहरण में, हम सेल A5 में वाक्य पर सबसे बाईं ओर का शब्द प्राप्त करने के लिए left फ़ंक्शन का उपयोग करते हैं।

चित्र: एक्सेल में बायाँ फ़ंक्शन

नीचे mid फ़ंक्शन का उपयोग करके एक उदाहरण दिखाया गया है।

चित्र: एक्सेल में Mid फ़ंक्शन

• यहां, हमारे पास सही फ़ंक्शन का एक उदाहरण है।

चित्र: एक्सेल में दायाँ फ़ंक्शन

14. ऊपरी, निचला, उचित

UPPER() फ़ंक्शन किसी भी टेक्स्ट स्ट्रिंग को अपरकेस में बदलता है। इसके विपरीत, LOWER() फ़ंक्शन किसी भी टेक्स्ट स्ट्रिंग को लोअरकेस में बदलता है। PROPER() फ़ंक्शन किसी भी टेक्स्ट स्ट्रिंग को प्रॉपर केस में बदलता है, यानी, प्रत्येक शब्द का पहला अक्षर अपरकेस में होगा और बाकी सभी लोअरकेस में।

आइये इसे निम्नलिखित उदाहरणों से बेहतर ढंग से समझें:

• यहां, हमने A6 में लिखे पाठ को A7 में पूर्णतः बड़े अक्षरों में परिवर्तित कर दिया है।

चित्र: एक्सेल में ऊपरी फ़ंक्शन

• अब, हमने A6 में पाठ को पूर्ण लोअरकेस में परिवर्तित कर दिया है, जैसा कि A7 में देखा गया है।

चित्र: एक्सेल में लोअर फ़ंक्शन

• अंततः, हमने A6 में अनुचित पाठ को A7 में स्वच्छ और उचित प्रारूप में परिवर्तित कर दिया है।

चित्र: एक्सेल में उचित फ़ंक्शन

अब, आइए एक्सेल में कुछ दिनांक और समय फ़ंक्शनों का अन्वेषण करें।

15. अब()

एक्सेल में NOW() फ़ंक्शन वर्तमान सिस्टम दिनांक और समय देता है।

चित्र: अब एक्सेल में फ़ंक्शन

NOW() फ़ंक्शन का परिणाम आपके सिस्टम दिनांक और समय के आधार पर बदल जाएगा।

16. आज()

एक्सेल में TODAY() फ़ंक्शन वर्तमान सिस्टम दिनांक प्रदान करता है।

चित्र: एक्सेल में टुडे फ़ंक्शन

DAY() फ़ंक्शन का उपयोग महीने का दिन बताने के लिए किया जाता है। यह 1 से 31 के बीच की कोई संख्या होगी। 1 महीने का पहला दिन है, और 31 महीने का आखिरी दिन है।

चित्र: एक्सेल में दिन फ़ंक्शन

MONTH() फ़ंक्शन महीना लौटाता है, जो 1 से 12 तक की संख्या है, जहाँ 1 जनवरी है और 12 दिसंबर है।

 चित्र: एक्सेल में महीना फ़ंक्शन

YEAR() फ़ंक्शन, जैसा कि नाम से पता चलता है, दिनांक मान से वर्ष लौटाता है।

चित्र: एक्सेल में वर्ष फ़ंक्शन

17. समय()

TIME() फ़ंक्शन संख्याओं के रूप में दिए गए घंटे, मिनट, सेकंड को समय प्रारूप के साथ स्वरूपित एक्सेल सीरियल नंबर में परिवर्तित करता है।

चित्र: एक्सेल में समय फ़ंक्शन

18. घंटा, मिनट, सेकंड

HOUR() फ़ंक्शन 0 से 23 तक की संख्या के रूप में समय मान से घंटे उत्पन्न करता है। यहां, 0 का अर्थ है 12 AM और 23 का अर्थ है 11 PM।

चित्र: एक्सेल में घंटा फ़ंक्शन

फ़ंक्शन MINUTE(), समय मान से मिनट को 0 से 59 तक की संख्या के रूप में लौटाता है।

चित्र: एक्सेल में मिनट फ़ंक्शन

SECOND() फ़ंक्शन किसी समय मान से सेकंड को 0 से 59 तक की संख्या के रूप में लौटाता है।

चित्र: एक्सेल में दूसरा फ़ंक्शन

19. दिनांकित

DATEDIF() फ़ंक्शन दो तिथियों के बीच वर्षों, महीनों या दिनों के संदर्भ में अंतर प्रदान करता है।

नीचे DATEDIF फ़ंक्शन का एक उदाहरण दिया गया है, जहां हम दो दी गई तिथियों, जन्म तिथि और आज की तिथि के आधार पर किसी व्यक्ति की वर्तमान आयु की गणना करते हैं।

चित्र: एक्सेल में Datedif फ़ंक्शन

अब, आइए एक्सेल में कुछ महत्वपूर्ण उन्नत कार्यों पर नजर डालें, जिनका उपयोग डेटा का विश्लेषण करने और रिपोर्ट बनाने के लिए लोकप्रिय रूप से किया जाता है।

20. वीलुकअप

इस लेख में अगला विषय VLOOKUP() फ़ंक्शन है । यह एक वर्टिकल लुकअप है जो किसी तालिका के सबसे बाएँ कॉलम में किसी विशिष्ट मान को खोजने के लिए ज़िम्मेदार है। फिर यह आपके द्वारा निर्दिष्ट कॉलम से उसी पंक्ति में एक मान लौटाता है। 

नीचे VLOOKUP फ़ंक्शन के लिए तर्क दिए गए हैं:

बहस	विवरण
लुकअप_वैल्यू	यह वह मान है जिसे आपको तालिका के पहले कॉलम में देखना है।
मेज़	यह उस तालिका को इंगित करता है जिससे मान प्राप्त किया जाता है।
col_index	तालिका में कॉलम से मान प्राप्त किया जाना है।
रेंज देखना	[वैकल्पिक] TRUE = अनुमानित मिलान (डिफ़ॉल्ट). FALSE = सटीक मिलान.

हम यह जानने के लिए नीचे दी गई तालिका का उपयोग करेंगे कि VLOOKUP फ़ंक्शन कैसे काम करता है।

यदि आप यह जानना चाहते हैं कि स्टुअर्ट किस विभाग से संबंधित है, तो आप नीचे दिखाए अनुसार VLOOKUP फ़ंक्शन का उपयोग कर सकते हैं:

चित्र: एक्सेल में Vlookup फ़ंक्शन

यहां, A11 सेल में लुकअप मान है, A2: E7 तालिका सारणी है, 3 विभागों के बारे में जानकारी के साथ कॉलम इंडेक्स संख्या है, और 0 रेंज लुकअप है। 

यदि आप एंटर दबाते हैं, तो यह "मार्केटिंग" लौटाएगा, जो दर्शाता है कि स्टुअर्ट मार्केटिंग विभाग से है।

21. एचलुकअप

VLOOKUP की तरह, हमारे पास HLOOKUP() या हॉरिजॉन्टल लुकअप नामक एक और फ़ंक्शन है। HLOOKUP फ़ंक्शन किसी तालिका या लाभों की सरणी की शीर्ष पंक्ति में मान खोजता है। यह आपके द्वारा निर्दिष्ट पंक्ति के उसी कॉलम में मान देता है।

नीचे HLOOKUP फ़ंक्शन के लिए तर्क दिए गए हैं:

बहस	विवरण
लुकअप_वैल्यू	यह देखने के लिए मान इंगित करता है
मेज़	यह वह तालिका है जिससे आपको डेटा प्राप्त करना है
पंक्ति_सूचकांक	यह वह पंक्ति संख्या है जिससे डेटा प्राप्त करना है
रेंज देखना	[वैकल्पिक] यह एक बूलियन है जो सटीक या अनुमानित मिलान दर्शाता है। डिफ़ॉल्ट मान TRUE है, जिसका अर्थ है अनुमानित मिलान।

नीचे दी गई तालिका को देखते हुए, आइए देखें कि आप HLOOKUP का उपयोग करके जेनसन शहर कैसे ढूंढ सकते हैं।

                                                     चित्र: एक्सेल में Hlookup फ़ंक्शन

यहां, H23 लुकअप मान है, अर्थात, जेनसन, G1:M5 तालिका सारणी है, 4 पंक्ति सूचकांक संख्या है, 0 अनुमानित मिलान के लिए है।

एक बार जब आप एंटर दबाते हैं, तो यह “न्यूयॉर्क” लौटाएगा।

22. IF फॉर्मूला

IF () फ़ंक्शन किसी दी गई शर्त की जाँच करता है और यदि वह सत्य है, तो एक विशिष्ट मान लौटाता है। यदि शर्त असत्य है, तो यह एक अन्य मान लौटाता है।

नीचे दिए गए उदाहरण में, हम यह जांचना चाहते हैं कि सेल A2 में मान 5 से अधिक है या नहीं। यदि यह 5 से अधिक है, तो फ़ंक्शन "हाँ 4 अधिक है" लौटाएगा, अन्यथा यह "नहीं" लौटाएगा।

                     चित्र: एक्सेल में यदि फ़ंक्शन

इस स्थिति में, यह 'नहीं' लौटाएगा क्योंकि 4, 5 से बड़ा नहीं है।

'IFERROR' एक और लोकप्रिय फ़ंक्शन है। यह फ़ंक्शन तब मान लौटाता है जब किसी व्यंजक का मूल्यांकन त्रुटिपूर्ण होता है, अन्यथा यह व्यंजक का मान लौटाता है।

मान लीजिए आप 10 को 0 से भाग देना चाहते हैं। यह एक अमान्य व्यंजक है, क्योंकि आप किसी संख्या को शून्य से भाग नहीं दे सकते। इससे त्रुटि उत्पन्न होगी। 

उपरोक्त फ़ंक्शन “विभाजित नहीं किया जा सकता” लौटाएगा।

23. इंडेक्स-मैच

INDEX-MATCH फ़ंक्शन का उपयोग बाईं ओर स्थित कॉलम में मान लौटाने के लिए किया जाता है। VLOOKUP के साथ, आपको दाईं ओर स्थित कॉलम से मूल्यांकन लौटाना पड़ता है। VLOOKUP के बजाय index-match का उपयोग करने का एक अन्य कारण यह है कि VLOOKUP को Excel से अधिक प्रोसेसिंग पावर की आवश्यकता होती है। ऐसा इसलिए है क्योंकि इसे आपके द्वारा चयनित संपूर्ण तालिका सरणी का मूल्यांकन करना होता है। INDEX-MATCH के साथ, Excel को केवल लुकअप कॉलम और रिटर्न कॉलम पर विचार करना होता है।

नीचे दी गई तालिका का उपयोग करके, आइए देखें कि आप उस शहर का पता कैसे लगा सकते हैं जहां जेन्सन रहता है।

चित्र: एक्सेल में इंडेक्स-मैच फ़ंक्शन

अब, आइए ज़म्पा विभाग का पता लगाएं।

24. काउंटिफ

COUNTIF() फ़ंक्शन का उपयोग किसी श्रेणी के भीतर दी गई शर्त को पूरा करने वाले कुल कक्षों की संख्या गिनने के लिए किया जाता है। 

नीचे प्रत्येक देश और क्षेत्र में कोरोना वायरस के मामलों और मौतों के बारे में जानकारी के साथ कोरोना वायरस नमूना डेटासेट दिया गया है।

आइये जानें कि तालिका में अफगानिस्तान कितनी बार उपस्थित है।

                                                          

चित्र: एक्सेल में Countif फ़ंक्शन

COUNTIFS फ़ंक्शन दी गई शर्तों के सेट द्वारा निर्दिष्ट कोशिकाओं की संख्या की गणना करता है।

यदि आप उन दिनों की संख्या गिनना चाहते हैं जिनमें भारत में मामले 100 से अधिक रहे हैं। यहां बताया गया है कि आप COUNTIFS फ़ंक्शन का उपयोग कैसे कर सकते हैं।

25. सुमिफ

SUMIF() फ़ंक्शन किसी दी गई शर्त या मानदंड द्वारा निर्दिष्ट कोशिकाओं को जोड़ता है।

नीचे कोरोनावायरस डेटासेट है जिसके उपयोग से हम 3 जून 2020 तक भारत में कुल मामलों की संख्या ज्ञात करेंगे। (हमारे डेटासेट में 31 दिसंबर 2020 से 3 जून                                     

 चित्र: एक्सेल में Sumif फ़ंक्शन

SUMIFS() फ़ंक्शन शर्तों या मानदंडों के दिए गए सेट द्वारा निर्दिष्ट कोशिकाओं को जोड़ता है।

आइए उन दिनों में फ्रांस में कुल मामलों का पता लगाएं जब मौतें 100 से कम रही हों। 

26. लक्ष्य की खोज

लक्ष्य खोज (Goal Seek) उन्नत एक्सेल फ़ंक्शंस में अंतर्निहित एक फ़ंक्शन है जो आपको मान्यताओं को बदलकर वांछित परिणाम प्राप्त करने की अनुमति देता है। वांछित परिणाम प्राप्त करने के लिए यह प्रक्रिया परीक्षण और त्रुटि विधि पर निर्भर करती है। 

इसे बेहतर ढंग से समझने के लिए आइए एक उदाहरण देखें।

उदाहरण

इस उदाहरण में, हमारा उद्देश्य यह पता लगाना है कि यदि व्यक्ति भुगतान करना चाहता है तो ब्याज दर क्या होगी 

ऋण राशि का निपटान करने के लिए प्रति माह 5000 डॉलर।

पीएमटी फ़ंक्शन का उपयोग तब किया जाता है जब आप ऋण राशि का निपटान करने के लिए आवश्यक मासिक भुगतान की गणना करना चाहते हैं।

आइये इस समस्या को चरणों में समझते हुए देखें कि हम 400,000 डॉलर के ऋण के लिए 5,000 डॉलर प्रति माह के भुगतान पर ब्याज दर की गणना कैसे कर सकते हैं।

• PMT फ़ॉर्मूला अब भुगतान सेल के बगल वाले सेल में दर्ज किया जाना चाहिए। फ़िलहाल, ब्याज दर सेल में कोई मान नहीं है। एक्सेल हमें $3,333.33 का भुगतान देता है क्योंकि यह ब्याज दर 0% मानता है। इसे अनदेखा करें।

• डेटा > क्या - यदि विश्लेषण > लक्ष्य खोज पर जाएं

• मासिक भुगतान -5,000 पर सेट करें। राशि में कटौती ऋणात्मक मान दर्शाती है।
  
  ब्याज दर को बदलते सेल के रूप में सेट करें।

• ओके पर क्लिक करें। आप देखेंगे कि लक्ष्य प्राप्ति फ़ंक्शन स्वचालित रूप से ऋण राशि चुकाने के लिए आवश्यक ब्याज दर बता देता है। 

होम > संख्या पर जाएं और मान को प्रतिशत में बदलें ।

आपका परिणाम नीचे जैसा दिखेगा:

27. सॉल्वर के साथ क्या-अगर विश्लेषण

क्या-अगर विश्लेषण उन्नत एक्सेल में सूत्रों के लिए विभिन्न परिदृश्यों को आज़माने के लिए मूल्यों को बदलने की विधि है।

विभिन्न परिणामों का पता लगाने के लिए इन उन्नत एक्सेल सूत्रों में से एक या एकाधिक में मूल्यों के कई अलग-अलग सेटों का उपयोग किया जा सकता है।

सॉल्वर क्या-अगर विश्लेषण के लिए आदर्श है। यह माइक्रोसॉफ्ट एक्सेल में एक ऐड-इन प्रोग्राम है और कई स्तरों पर उपयोगी है। इस सुविधा का उपयोग ऑब्जेक्टिव सेल नामक सेल में किसी सूत्र के लिए इष्टतम मान निर्धारित करने के लिए किया जा सकता है। हालाँकि, वर्कशीट पर अन्य सूत्र सेल मानों पर कुछ प्रतिबंध या सीमाएँ लागू होती हैं ।

सॉल्वर निर्णय चरों के साथ काम करता है, जो कोशिकाओं का एक समूह होता है जिसका उपयोग उद्देश्य और प्रतिबंध कोशिकाओं में सूत्रों की गणना में किया जाता है। सॉल्वर प्रतिबंध कोशिकाओं की सीमाओं पर काम करने के लिए निर्णय चर कोशिकाओं के मान को समायोजित करता है। यह प्रक्रिया उद्देश्य कोशिका के लिए वांछित परिणाम निर्धारित करने में सहायता करती है।

a. सॉल्वर ऐड-इन सक्रिय करना

• फ़ाइल टैब पर, विकल्प पर क्लिक करें.
• ऐड-इन्स पर जाएं, सॉल्वर ऐड-इन चुनें, और गो बटन पर क्लिक करें।

• सॉल्वर ऐड-इन की जांच करें और ओके पर क्लिक करें।

•  डेटा टैब में, विश्लेषण समूह में, आप देख सकते हैं कि सॉल्वर विकल्प जोड़ा गया है।

ख. एक्सेल में सॉल्वर का उपयोग कैसे करें

इस उदाहरण में, हम एक सरल अनुकूलन समस्या का समाधान खोजने का प्रयास करेंगे।

- समस्या: मान लीजिए आप व्यवसाय के मालिक हैं और आप चाहते हैं कि आपकी आय 8000 डॉलर हो।

- लक्ष्य: लक्ष्य प्राप्त करने के लिए बेची जाने वाली इकाइयों और प्रति इकाई मूल्य की गणना करें।

उदाहरण के लिए, हमने निम्नलिखित मॉडल बनाया है:

• डेटा टैब पर, विश्लेषण समूह में, सॉल्वर बटन पर क्लिक करें।
• निर्धारित उद्देश्य में, आय सेल का चयन करें और उसका मान $8000 पर सेट करें।
• वेरिएबल सेल को बदलने के लिए, C5, C6, और C10 सेल का चयन करें।

• हल करें पर क्लिक करें. 

आपका डेटा मॉडल परिस्थितियों के अनुसार बदल जाएगा।

28. यदि-अन्यथा

IF फ़ंक्शन का उपयोग शर्त का परीक्षण करने के लिए किया जाता है और यदि शर्त वास्तव में सत्य है तो एक मान लौटाता है और यदि यह असत्य है तो एक पूर्वनिर्धारित भिन्न मान लौटाता है।

यदि-अन्यथा =IF(परीक्षण, सत्य परिणाम, असत्य परिणाम)

29. यदि-त्रुटि

जब कोई सूत्र त्रुटि उत्पन्न करता है, तो Excel IFERROR फ़ंक्शन एक वैकल्पिक परिणाम लौटाता है, तथा जब कोई त्रुटि नहीं पाई जाती है, तो एक अपेक्षित परिणाम लौटाता है। 

यदि-त्रुटि =IFERROR (मान, value_if_error)

उदाहरण के लिए, जब कोई सूत्र किसी संख्या को 0 से विभाजित करने का प्रयास करता है, तो Excel शून्य से भाग त्रुटि लौटाता है।

IFERROR फ़ंक्शन का उपयोग करके, यदि सूत्र में त्रुटि आती है तो आप एक संदेश जोड़ सकते हैं।

30. सूचकांक और मिलान

यह एक उन्नत एक्सेल फ़ंक्शन है। MATCH फ़ंक्शन किसी निर्दिष्ट श्रेणी में किसी मान की स्थिति लौटाने के लिए डिज़ाइन किया गया है, जबकि INDEX फ़ंक्शन एक-आयामी श्रेणी में मौजूद एक विशिष्ट मान लौटाता है।

MATCH फ़ंक्शन उस आईडी का पद लौटाता है जिसे आप खोज रहे हैं। INDEX फ़ंक्शन उस पद के अनुरूप वेतन का मान लौटाएगा।

31. ऑफसेट फ़ंक्शन

OFFSET फ़ंक्शन कक्षों की एक श्रेणी का संदर्भ लौटाता है जो किसी कक्ष या कक्षों की श्रेणी से पंक्तियों और स्तंभों की एक निर्दिष्ट संख्या होती है।

ऑफसेट फ़ंक्शन =OFFSET(संदर्भ, पंक्तियाँ, कॉलम, [ऊँचाई], [चौड़ाई])

उदाहरण:

निम्नलिखित आंकड़ों पर विचार करें: 

 A1 से शुरू होने वाले C4 को संदर्भित करने के लिए, संदर्भ A1 है, पंक्तियाँ 3 हैं और कॉलम 2 हैं:

32. OFFSET के साथ SUM फ़ंक्शन

इस उदाहरण में, हमारे पास दो वर्षों का मासिक बिक्री डेटा है। हमारा लक्ष्य किसी विशिष्ट महीने की बिक्री का योग ज्ञात करना है।

OFFSET फ़ंक्शन 1x2 की श्रेणी लौटाता है, जो सेल A2 के नीचे 8 पंक्तियाँ और सेल A2 के दाईं ओर 1 कॉलम है। इसके बाद SUM फ़ंक्शन इस श्रेणी का योगफल निकालता है।

33. एक्सेल में तुलना ऑपरेटर

तुलना ऑपरेटर	अर्थ	सूत्र उदाहरण
=	के बराबर	=A2=B2
<>	असमान	=A2<>B2
>	से अधिक	=A2>B2
<	से कम	=A2<B2
>=	से बड़ा या बराबर	=A2>=B2
<=	से कम या बराबर	=A2<=B2

एक्सेल ज़्यादातर एनालिटिक्स करियर के लिए बस शुरुआती बिंदु है। सांख्यिकीय मॉडलिंग, डैशबोर्ड, व्यावसायिक अंतर्दृष्टि और डेटा रणनीति में विस्तार के लिए, एक डेटा एनालिटिक्स प्रमाणन कार्यक्रम संरचित, उद्योग-प्रासंगिक प्रशिक्षण प्रदान कर सकता है।