स्प्रेडशीट्स में कंडीशनल फ़ंक्शन्स

जब भी किसी को लगभग किसी भी प्रोग्रामिंग भाषा की शिक्षा दी जाती है, तो सबसे पहले जिन चीज़ों का सामना होता है, वे कंडीशनल्स होते हैं। यानी क्लासिक IF-ELSEIF-ELSE स्टेटमेंट्स, जो कार्यक्रमों को तार्किक शर्तों के आधार पर अलग-अलग संचालन करने देते हैं। हालांकि, Excel के संदर्भ में यह वह पहली चीज़ नहीं होती जो दिमाग में आती है। फिर भी, ये वहाँ मौजूद हैं और Excel में थोड़ी जटिलता वाले रिपोर्ट्स पर काम करते समय काफी महत्वपूर्ण होते हैं। विशेष रूप से, इस ट्यूटोरियल में हम Excel में IF-ELSE स्टेटमेंट्स की संरचना के साथ-साथ कुछ उन्नत फ़ंक्शन्स जैसे COUNTIF() को कवर करेंगे, जो कुछ स्थितियों में बहुत काम आते हैं।

मूलभूत IF स्टेटमेंट्स

शुरू करने के लिए, आइए Excel में एक बेसिक IF स्टेटमेंट कैसे लिखते हैं, उसका एक अवलोकन देखते हैं। इसका सिंटैक्स इस प्रकार है:

IF(condition, value_if_true, value_if_false)

जहाँ:

• condition: कोई मान या ऐसा लॉजिकल ऑपरेशन जो TRUE या FALSE का मान देता है
• value_if_true: वह मान जो शर्त TRUE होने पर लौटाया जाएगा
• value_if_false: वह मान जो शर्त FALSE होने पर लौटाया जाएगा

अब एक साधारण खिलौना-तालिका के साथ बेसिक कंडीशनल का उदाहरण देखते हैं:

सरल है, है ना? जो लोग R से परिचित हैं, उन्होंने शायद नोट किया होगा कि यह सिंटैक्स मूल रूप से ifelse() फ़ंक्शन जैसा ही है। हालांकि, कुछ चीज़ें थोड़ी अलग हैं। उदाहरण के लिए, लॉजिकल ऑपरेटर्स। इस साधारण उदाहरण में मैंने ">" लॉजिकल ऑपरेटर का उपयोग किया है, जो greater than यानी से बड़ा दर्शाने का मानक तरीका है। लेकिन कुछ अन्य इतने मानक नहीं हैं। इसमें not equal to ऑपरेटर शामिल है, जिसे "<>" के रूप में लिखा जाता है।

Excel में उपयोग किए जाने वाले सभी लॉजिकल ऑपरेटर्स का पूरा विवरण नीचे देखा जा सकता है:

OR(), AND() और NOT()

तुलनात्मक ऑपरेटर्स ही Excel के कंडीशनल स्टेटमेंट्स के ऐसे घटक नहीं हैं जो Python, R, या Matlab में आपके उपयोग से अलग हों। वास्तव में, OR, AND और NOT जैसे बूलियन ऑपरेशन्स को परिभाषित करने का तरीका अलग है। उदाहरण के लिए, Excel में आप OR अभिव्यक्ति इस तरह लिखेंगे:

IF(OR(value_1 = k, value_2 = y), value_if_true, value_if_false)

Excel के अंदर सभी बूलियन ऑपरेटर्स को क्रिया में देखने के लिए, नीचे दिए गए उदाहरण को देखें:

नेस्टेड IF स्टेटमेंट्स

जैसे आप Python, R, या Matlab में IF-ELSEIF-ELSE स्टेटमेंट्स को नेस्ट कर सकते हैं, वैसे ही आप Excel में भी कर सकते हैं। ऐसा करने के लिए, जब कोई IF स्टेटमेंट TRUE या FALSE पर मूल्यांकन करे, तो उसके भीतर एक और IF स्टेटमेंट जोड़ना होता है। इससे आप अधिक शर्तों का परीक्षण कर सकते हैं और उनके आधार पर अधिक परिणाम लौटा सकते हैं। Excel अधिकतम 64 IF स्टेटमेंट्स को नेस्ट कर सकता है। हालांकि, मैं बहुत अधिक IF स्टेटमेंट्स नेस्ट करने के खिलाफ सलाह दूंगा, क्योंकि आपको लॉजिक के साथ सावधान रहना होता है, और बाद में उसे डिबग करना या बदलना काफी कठिन हो सकता है।

इसके साथ ही, यहाँ Excel में नेस्टेड IFs का एक सरल उपयोग-मामला है:

COUNTIF() और COUNTIFS()

पारंपरिक IF स्टेटमेंट्स के अलावा, Excel में अन्य फ़ंक्शन्स भी हैं जो दी गई शर्तों के आधार पर संचालन कर सकते हैं। इस खंड में, मैं कंडीशनल काउंटिंग फ़ंक्शन्स पर ध्यान केंद्रित करूँगा, विशेष रूप से COUNTIF() और COUNTIFS() पर।

COUNTIF() उपयोगकर्ता को उन कोशिकाओं की गणना करने देता है जो एकल मानदंड को पूरा करती हैं। इसका सिंटैक्स इस प्रकार है:

COUNTIF(cell_range_to_count, criteria)

जहाँ:

• cell_range_to_count: वह विशिष्ट सेल-रेंज जिसे आप गिनना चाहते हैं
• criteria: वह मानदंड जो नियंत्रित करता है कि किन कोशिकाओं को गिना जाना चाहिए। ये काफी विविध हो सकते हैं, उदाहरण के लिए:
  • COUNTIF(A1:A10, 10) - यह गिनेगा कि A1 से A10 की रेंज में कितनी कोशिकाएँ 10 के बराबर हैं
  • COUNTIF(A1:A10, B5) - यह गिनेगा कि A1 से A10 की रेंज में कितनी कोशिकाएँ B5 सेल के मान के बराबर हैं
  • COUNTIF(A1:A10, "Gandalf") - यह गिनेगा कि A1 से A10 की रेंज में कितनी कोशिकाएँ "Gandalf" के बराबर हैं
  • COUNTIF(A1:A10, ">20") - यह गिनेगा कि A1 से A10 की रेंज में कितनी कोशिकाएँ 20 से अधिक हैं
  • COUNTIF(A1:A10, "<>Gandalf") - यह गिनेगा कि A1 से A10 की रेंज में कितनी कोशिकाएँ "Gandalf" के बराबर नहीं हैं

COUNTIFS, जैसा कि आप समझ गए होंगे, COUNTIF से इस रूप में अलग है कि यह उपयोगकर्ता को एकाधिक मानदंडों को पूरा करने वाली कोशिकाओं की गणना करने देता है। इसका सिंटैक्स इस प्रकार है:

COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2,...cell_range_to_count_n, criteria_n)

ध्यान देने योग्य बात यह है कि COUNTIFS में आप जितनी भी अतिरिक्त रेंज निर्दिष्ट करते हैं, उनमें पंक्तियों और स्तंभों की संख्या मूल रेंज (cell_range_to_count_1) के समान होनी चाहिए, अन्यथा त्रुटि मिलेगी।

ये दोनों फ़ंक्शन्स व्यावसायिक रिपोर्ट्स में उपयोग के लिए शानदार हैं। मेरे लिए सबसे अच्छे उदाहरणों में से एक यह है कि ये हमारे सेल्स प्रतिनिधियों द्वारा हर महीने की गई अपॉइंटमेंट्स की संख्या और वे हमारी सेल्स फ़नल में कहाँ स्थित हैं, गिनना कितना आसान बना देते हैं।

नीचे दिए गए वीडियो में, मैं दिखाऊँगा कि ये दोनों फ़ंक्शन्स व्यवहार में कैसे काम करते हैं। पहले, आइए काल्पनिक सेल्सपर्सन्स के एक समूह की कुल अपॉइंटमेंट्स को ही गिन लें:

कितना आसान था, है ना? आपने हमारे काल्पनिक सेल्सपर्सन्स में से प्रत्येक की कुल अपॉइंटमेंट्स गिन लीं। सिद्धांत रूप से, आप यह केवल बेसिक IF स्टेटमेंट्स का उपयोग करके भी कर सकते थे, लेकिन बिल्कुल वही परिणाम पाने के लिए काफी अधिक मेहनत लगती।

अब मान लीजिए आप यह भी गिनना चाहते हैं कि इन सेल्सपर्सन्स में से प्रत्येक की हर महीने कितनी अपॉइंटमेंट्स थीं। इस स्थिति में, COUNTIFS() समाधान है; इसे आप नीचे दिए गए वीडियो में क्रिया में देख सकते हैं:

टिप्पणी: आपने देखा होगा कि मैंने पिछले वीडियो में जिस रेंज को गिनना था, उसे $ चिह्नों में बाँध दिया था। ऐसा इसलिए किया जाता है ताकि जब मैं फ़ॉर्मूला नीचे खींचूँ तो वह रेंज लॉक रहे और बदले नहीं।

Voilà, हमने बिना किसी दिक्कत के अपने डमी सेल्सपर्सन्स की अपॉइंटमेंट्स को महीने के हिसाब से जोड़ लिया। यह उल्लेखनीय है कि आप इसे और अधिक "कुशल" तरीके से भी कर सकते हैं, यदि आप $ चिह्नों के साथ खेलें ताकि फ़ॉर्मूलों को घसीटते समय कुछ कोशिकाएँ और रेंजेस न हिलें (जैसे, मैं सिर्फ F2 में फ़ॉर्मूला लिखकर उसे बाकी में खींच सकता था)। हालांकि, अधिक स्पष्टता देने के लिए, मैंने जनवरी और फरवरी के लिए COUNTIFS फ़ॉर्मूले अलग-अलग लिखे। फिर भी, यदि आप उत्सुक हैं, तो मैं आपको प्रोत्साहित करता हूँ कि आप F2 में COUNTIFS() फ़ॉर्मूला इस तरह से फिर से लिखें कि आप उसे बाकी कोशिकाओं में खींच सकें और वही परिणाम मिलें। यह उपयोगी अभ्यास हो सकता है क्योंकि बड़े Excel व्यावसायिक रिपोर्ट्स में फ़ॉर्मूलों को खींचते समय रेंजेस और कोशिकाओं को लॉक करना सुविधाजनक रहता है। इस ट्यूटोरियल में उपयोग की गई डेटा तालिका यहाँ है।

SUMIF() और SUMIFS()

हमने देखा कि आप COUNTIF() और COUNTIFS() के साथ एकल या बहु-मानदंडों को पूरा करने वाली कोशिकाओं की गणना कैसे कर सकते हैं, लेकिन यदि आपको सिर्फ गिनने के बजाय कोशिकाओं को जोड़ना हो तो? यहाँ काम आते हैं SUMIF() और SUMIFS()। ये फ़ंक्शन्स विशेष रूप से तब उपयोगी होते हैं जब आपको, उदाहरण के लिए, किसी दिए गए सेल्सपर्सन द्वारा कुल या किसी विशेष महीने में उत्पन्न राजस्व को जोड़ना हो। दोनों फ़ंक्शन्स का सिंटैक्स इस प्रकार है:

SUMIF(criteria_range, criteria, (optional) sum_range)

SUMIFS(sum_range, criteria_range_1, criteria_1, criterion_range_2, criteria_2...criteria_range_n, criteria_n)

जहाँ:

• criteria_range: वह सेल-रेंज जिसमें मानदंड की जाँच की जाएगी।
• criteria: वह कंडीशनल मानदंड जिससे निर्धारित होगा कि किन कोशिकाओं को जोड़ा जाएगा।
• sum_range: वे कोशिकाएँ जिन्हें जोड़ा जाएगा। यदि COUNTIF() में यह प्रदान नहीं किया जाता, तो criteria_range की कोशिकाओं को जोड़ा जाता है।

अब, बिना देर किए, आइए एक उदाहरण देखें जो SUMIF() और SUMIFS() दोनों का उपयोग करके हमारे नकली सेल्स प्रतिनिधियों द्वारा उत्पन्न कुल राजस्व और प्रति माह राजस्व की गणना करता है:

यह ध्यान देने योग्य है कि SUMIFS() के मामले में sum_range एक आवश्यक आर्ग्युमेंट है और यह पहला आर्ग्युमेंट होता है, लेकिन SUMIF() में यह वैकल्पिक है और आखिरी आर्ग्युमेंट होता है। जब आप दोनों फ़ॉर्मूलों का उपयोग करते हैं तो यह भ्रम पैदा कर सकता है, जैसा कि आप देख सकते हैं जब मैं फरवरी के लिए SUMIFS फ़ंक्शन टाइप कर रहा था। इसलिए, मैं दोनों फ़ॉर्मूलों के साथ काम करते समय सावधानी बरतने की सलाह देता हूँ। हमेशा जाँच लें कि आप सही सेल-रेंजेस को जोड़ रहे हैं।

IFERROR()

जो लोग कोई प्रोग्रामिंग/स्क्रिप्टिंग भाषा जानते हैं, वे जानते हैं कि एरर हैंडलिंग के लिए try/catch ब्लॉक्स जैसी चीज़ें होती हैं। Excel में हमारे पास यही काम करने के लिए IFERROR() फ़ंक्शन है। IFERROR फ़ंक्शन उपयोगकर्ता को #N/A, #VALUE! या #REF! जैसे एरर्स को "पकड़ने" और अधिक सार्थक आउटपुट देने की अनुमति देता है। IRERROR फ़ंक्शन का सिंटैक्स इस प्रकार है:

IFERROR(value, value_if_error)

जहाँ:

• value: वह मान या फ़ॉर्मूला जिसकी एरर के लिए जाँच की जानी है
• value_if_error: वह मान जो फ़ंक्शन को एरर मिलने पर आउटपुट किया जाएगा।

आइए निम्नलिखित वीडियो में इसे क्रिया में देखें:

जब आप व्यावसायिक सेल्स रिपोर्ट्स पर काम कर रहे होते हैं, तो कुछ गणनाओं को IFERROR फ़ंक्शन्स में लपेटना आमतौर पर एक अच्छा अभ्यास है। कई बार ऐसी स्थितियाँ आएँगी जब नए सेल्सपर्सन्स की अपॉइंटमेंट्स, क्वालिफ़ाइड ऑपर्च्युनिटीज़ या क्लोज़्ड डील्स बहुत कम होंगी, जिससे किसी दिए गए सेल्सपर्सन के लिए अपॉइंटमेंट्स से क्वालिफ़ाइड ऑपर्च्युनिटीज़ में बदलने का प्रतिशत निकालते समय 0 से भाग देने जैसी स्थिति हो सकती है। इससे बुरे दिखने वाले एरर्स निकल सकते हैं, जो आपके रिपोर्ट को गंदा और निर्णयकर्ताओं के लिए पढ़ना मुश्किल बना देंगे। लेकिन यदि आप इन गणनाओं को IFERROR() स्टेटमेंट्स से कवर करते हैं, तो आप उन एरर्स को डैश जैसे आँखों को सरल लगने वाले आउटपुट से बदल सकते हैं, जिससे आपकी रिपोर्ट अधिक प्रस्तुत करने योग्य बनेगी। इसके अलावा, IFERROR() स्टेटमेंट्स सिर्फ अच्छे एरर संदेश देने से आगे भी जा सकते हैं। आप इन्हें नेस्ट करके चेन किए हुए VLOOKUPs जैसी चीज़ें भी कर सकते हैं।

निष्कर्ष

बधाई हो! अब आप Excel में कंडीशनल्स का लाभ उठाने के कई तरीकों से अवगत हैं। इस ट्यूटोरियल में, मैंने उन फ़ंक्शन्स को कवर करने का प्रयास किया है जिनका उपयोग मैं अक्सर Excel व्यावसायिक रिपोर्ट्स बनाते समय करता हूँ। हालांकि, यदि आप अभी भी इस विषय पर और सीखना चाहते हैं, तो SWITCH(), AVERAGEIF() या AVERAGEIFS() जैसे अन्य फ़ंक्शन्स भी हैं जिनके बारे में मैंने यहाँ बात नहीं की है, और जो आपके लिए रुचिकर हो सकते हैं।

साथ ही, COUNTIFS() जैसे फ़ंक्शन्स में वाइल्डकार्ड्स का उपयोग करना भी संभव है, जो आंशिक स्ट्रिंग मैचों को गिनने का पूरी तरह वैध तरीका है। उदाहरण के लिए, मान लीजिए आपके पास किसी रेस्तरां की मेनू आइटम्स की सूची वाला Excel फ़ाइल है, और आप गिनना चाहते हैं कि उनमें से कितने में "fish" शब्द आता है। इस स्थिति में, आप COUNTIF(cell_range,"*fish") लिख सकते हैं और यह फ़ंक्शन swordfish या dolphinfish जैसे आइटम्स को भी गिनेगा। मैं आपको वाइल्डकार्ड्स के बारे में और सीखने के लिए प्रोत्साहित करता हूँ क्योंकि वे बहुत उपयोगी हो सकते हैं और आप नहीं जानते कि वे आपके अगले Excel प्रोजेक्ट में कब काम आ जाएँ।

अंत में, आप DataCamp पर Data Analysis with Spreadsheets कोर्स भी देखना चाहेंगे क्योंकि इसमें कंडीशनल फ़ंक्शन पर एक समर्पित सेक्शन है, जो आगे के संदर्भ के लिए उपयोगी हो सकता है।

हमारा if…elif…else in Python ट्यूटोरियल भी देखें।