एक्सेल में फॉर्मूला-आधारित सशर्त स्वरूपण में महारत हासिल करें

फॉर्मूला-आधारित सशर्त स्वरूपण नियम बनाना

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

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

• सशर्त स्वरूपण लागू करने के लिए सेल रेंज का चयन करें।
• होम टैब पर जाएं >> सशर्त स्वरूपण से >> नया नियम चुनें।
• नया स्वरूपण नियम संवाद बॉक्स में;
  • सूत्र का उपयोग करके निर्धारित करें कि किन सेलों को प्रारूपित करना है विकल्प चुनें।
  • इस सूत्र के सत्य होने पर मान प्रारूपित करें बॉक्स में;
    • कस्टम सूत्र डालें।
  • प्रारूप पर क्लिक करके अपनी पसंद का प्रारूप चुनें।
  • ओके पर क्लिक करें।

आइए फॉर्मूला-आधारित नियमों के साथ उन्नत सशर्त स्वरूपण लागू करने के लिए एक बिक्री डेटासेट पर विचार करें।

1. शर्त के आधार पर पंक्तियों को हाइलाइट करें

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

सूत्र:

=$G2>=5000

यह सूत्र जांचता है कि सेल C2 (बिक्री) में मान 5000 से अधिक है या नहीं। यदि तुलना पंक्तियों में सुसंगत है तो कॉलम के लिए पूर्ण संदर्भ ($) का उपयोग करें।

• प्रारूप पर क्लिक करें और अपना पसंदीदा स्वरूपण विकल्प सेट करें, हमने हरा रंग चुना।
• नियम लागू करने के लिए ओके पर क्लिक करें।

2. डुप्लिकेट बिक्री प्रविष्टियों को हाइलाइट करें

आप अपने डेटासेट से डुप्लिकेट बिक्री प्रविष्टियों को हाइलाइट करके उन्हें हटा सकते हैं। निम्न सूत्र डालें।

सूत्र:

=COUNTIFS($A$2:$A$17, $A2, $B$2:$B$17, $B2, $C$2:$C$17, $C2, $D$2:$D$17, $D2, $E$2:$E$17, $E2, $F$2:$F$17, $F2, $G$2:$G$17, $G2) > 1

यह सूत्र कॉलम A से G तक प्रत्येक कॉलम की जांच करता है ताकि यह निर्धारित किया जा सके कि निर्दिष्ट रेंज के भीतर प्रत्येक पंक्ति में मानों का संयोजन डुप्लिकेट है या नहीं, फिर यह कॉलम A से G में प्रत्येक पंक्ति के मानों के अद्वितीय संयोजन को गिनता है। यदि गिनती 1 से अधिक है, तो पंक्ति को डुप्लिकेट माना जाता है। फिर पंक्ति को डुप्लिकेट के रूप में हाइलाइट किया जाता है।

• प्रारूप पर क्लिक करें, और अपना स्वरूपण चुनें, हमने पीला भरण चुना।
• नियम लागू करने के लिए ओके पर क्लिक करें।

3. हर दूसरी पंक्ति को रंगें (ज़ेब्रा स्ट्रिपिंग)

डेटा तालिका को प्रारूपित करने और इसे पढ़ने में आसान बनाने के लिए आप हर दूसरी पंक्ति में रंग जोड़ सकते हैं। यह प्रारूप जोड़ने के लिए, निम्न सूत्र का उपयोग करें।

सूत्र:

=MOD(ROW(),2)=0

ROW() फ़ंक्शन वर्तमान पंक्ति संख्या लौटाता है। फिर MOD(ROW(),2)=0 जांचता है कि पंक्ति संख्या सम है या नहीं।

• प्रारूप पर क्लिक करें, और अपना स्वरूपण चुनें, हमने हल्का ग्रे बैकग्राउंड भरण चुना।
• नियम लागू करने के लिए ओके पर क्लिक करें।

4. पिछली तिथियों को हाइलाइट करें

यदि आप आज की तिथि से पहले की तिथियों को हाइलाइट करना चाहते हैं ताकि अतिदेय आइटम की पहचान की जा सके, तो आप TODAY फ़ंक्शन के सूत्र का उपयोग करके ऐसा कर सकते हैं।

सूत्र:

=AND(ISNUMBER(A2), A2 < TODAY())

यह सूत्र जांचता है कि A2 में तिथि आज की तिथि से पहले है या नहीं ताकि आपके डेटासेट में किसी भी पिछली तिथि को हाइलाइट किया जा सके।

• प्रारूप पर क्लिक करें, और अपना स्वरूपण चुनें, हमने लाल भरण चुना।
• नियम लागू करने के लिए ओके पर क्लिक करें।

5. शीर्ष 10% बिक्री मूल्यों को हाइलाइट करें

आप अपने बिक्री डेटासेट के शीर्ष 10% बिक्री को हाइलाइट कर सकते हैं। शीर्ष बिक्री को हाइलाइट करने के लिए, आप PERCENTILE फ़ंक्शन का उपयोग कर सकते हैं।

सूत्र:

=$G2>=PERCENTILE($G$2:$G$17,0.9)

PERCENTILE($G$2:$G$17,0.9) रेंज का 90वां पर्सेंटाइल गणना करता है, इसलिए इस से अधिक या बराबर मान शीर्ष 10% में हैं। यदि कॉलम G में कोई मान इस पर्सेंटाइल से अधिक या बराबर है, तो संपूर्ण पंक्ति पर स्वरूपण लागू होगा।

• प्रारूप पर क्लिक करें, और अपना स्वरूपण चुनें, हमने हरे भरण रंग के साथ बोल्ड टेक्स्ट चुना।
• नियम लागू करने के लिए ओके पर क्लिक करें।

6. कई शर्तों को संयोजित करना

आप तार्किक फ़ंक्शन का उपयोग करके कई शर्तों को संयोजित करके जटिल सशर्त स्वरूपण नियम बना सकते हैं। AND/OR फ़ंक्शन का उपयोग करके कई शर्तों के आधार पर सेल हाइलाइट करें।

सूत्र:

=AND($G2>=3000, $C2="पूर्व")

यह सूत्र पंक्तियों को तभी हाइलाइट करता है जब बिक्री 3000 से अधिक या बराबर हो और क्षेत्र पूर्व हो।

• प्रारूप पर क्लिक करें, और अपना स्वरूपण चुनें, हमने पीच भरण रंग के साथ इटैलिक फ़ॉन्ट चुना।
• नियम लागू करने के लिए ओके पर क्लिक करें।

सशर्त स्वरूपण नियम प्रबंधित करना

अपने सशर्त स्वरूपण को प्रबंधित करने के लिए:

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

निष्कर्ष

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