एक्सेल सशर्त स्वरूपण सूत्र

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

स्वरूपण विकल्प तभी लागू होते हैं जब चयनित कक्ष इन निर्धारित स्थितियों को पूरा करते हैं।

फ़ॉर्मेटिंग विकल्प जिन्हें लागू किया जा सकता है उनमें फ़ॉन्ट और पृष्ठभूमि रंग परिवर्तन, फ़ॉन्ट शैलियों, सेल सीमाएं, और डेटा में संख्या स्वरूपण जोड़ना शामिल है।

एक्सेल 2007 के बाद से, एक्सेल में आमतौर पर उपयोग की जाने वाली स्थितियों के लिए कई अंतर्निहित विकल्प होते हैं जैसे कि किसी निश्चित मान से अधिक या उससे कम संख्या या उन मानों को ढूंढना जो औसत मान के ऊपर या नीचे हैं ।

इन प्री-सेट विकल्पों के अतिरिक्त, उपयोगकर्ता द्वारा निर्दिष्ट स्थितियों के परीक्षण के लिए Excel सूत्रों का उपयोग करके कस्टम सशर्त स्वरूपण नियम बनाना भी संभव है।

एकाधिक नियमों को लागू करना

विभिन्न स्थितियों के परीक्षण के लिए एक ही डेटा पर एक से अधिक नियम लागू किए जा सकते हैं। उदाहरण के लिए, बजट डेटा में ऐसी स्थितियां हो सकती हैं जो कुल बजट के 50%, 75%, और 100% - जैसे कुछ स्तरों पर प्रारूपण परिवर्तन लागू करते हैं।

ऐसी परिस्थितियों में, एक्सेल पहले निर्धारित करता है कि विभिन्न नियम संघर्ष करते हैं, और यदि ऐसा है, तो यह निर्धारित करने के लिए कि कार्यक्रम डेटा पर कौन सा सशर्त स्वरूपण नियम लागू होता है, यह निर्धारित करने के लिए कार्यक्रम की प्राथमिकता निर्धारित करता है।

उदाहरण: सशर्त स्वरूपण के साथ 25% से 50% से अधिक डेटा प्राप्त करना

निम्नलिखित उदाहरण में, दो कस्टम सशर्त स्वरूपण नियम बी 2 से बी 5 की सीमा पर लागू किए जाएंगे।

• पहला नियम यह देखने के लिए जांच करता है कि कक्ष A2 से A5 में डेटा 25% से अधिक B2 से B5 के संबंधित मान से अधिक है या नहीं।
• दूसरा नियम यह देखने के लिए जांच करता है कि ए 2: ए 5 में समान डेटा बी 2: बी 5 में 50% से अधिक के अनुरूप मान से अधिक है।

जैसा ऊपर उपरोक्त छवि में देखा जा सकता है, यदि उपरोक्त स्थितियों में से कोई भी सत्य है, तो बी 1: बी 4 में सेल या सेल का पृष्ठभूमि रंग बदल जाएगा।

• डेटा के लिए जहां अंतर 25% से अधिक है, सेल पृष्ठभूमि रंग नीले रंग में बदल जाएगा।
• यदि अंतर 50% से अधिक है तो सेल पृष्ठभूमि रंग लाल हो जाएगा।

इस कार्य को पूरा करने के लिए उपयोग किए जाने वाले नियम,

= (ए 2-बी 2) / ए 2> 25% = (ए 2-बी 2) / ए 2> 50%

सशर्त स्वरूपण का उपयोग करके नए स्वरूपण नियम संवाद बॉक्स का उपयोग किया जाएगा।

ट्यूटोरियल डेटा दर्ज करना

1. उपर्युक्त छवि में दिखाई देने वाले सेल ए 1 से सी 5 में डेटा दर्ज करें

नोट: ट्यूटोरियल का चरण 3 कोशिकाओं C2: C4 में सूत्र जोड़ देगा जो सशर्त स्वरूपण नियमों की सटीकता की जांच करने के लिए कक्ष A2: A5 और B2: B5 में मानों के बीच सटीक प्रतिशत अंतर दिखाते हैं।

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

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

25% से अधिक वृद्धि खोजने के लिए सशर्त स्वरूपण सेट करना

1. कार्यपत्रक में बी 2 से बी 5 को हाइलाइट करें।
2. रिबन के होम टैब पर क्लिक करें।
3. ड्रॉप डाउन मेनू खोलने के लिए रिबन में सशर्त स्वरूपण आइकन पर क्लिक करें।
4. उपरोक्त छवि में दिखाई देने वाले नए स्वरूपण नियम संवाद बॉक्स को खोलने के लिए नया नियम चुनें।
5. संवाद बॉक्स के ऊपरी भाग में, अंतिम विकल्प पर क्लिक करें: यह निर्धारित करने के लिए कि कौन से सेल्स प्रारूपित करने के लिए सूत्र का उपयोग करें।
6. संवाद बॉक्स के निचले हिस्से में, प्रारूप मानों पर क्लिक करें जहां यह सूत्र सत्य है: रेखा।
7. सूत्र को टाइप करें: = (ए 2-बी 2) / ए 2> प्रदान की गई जगह में 25%
8. स्वरूप कक्ष संवाद बॉक्स खोलने के लिए प्रारूप बटन पर क्लिक करें।
9. इस संवाद बॉक्स में, भरें टैब पर क्लिक करें और नीला भरने का रंग चुनें।
10. संवाद बॉक्स को बंद करने के लिए दो बार ठीक क्लिक करें और वर्कशीट पर वापस आएं।
11. इस बिंदु पर, कोशिकाओं बी 3 और बी 5 का पृष्ठभूमि रंग नीला होना चाहिए।

50% से अधिक वृद्धि खोजने के लिए सशर्त स्वरूपण सेट करना

1. कोशिकाओं बी 2 से बी 5 के साथ अभी भी चयनित है, उपरोक्त चरण 1 से 6 को दोहराएं।
2. सूत्र को टाइप करें: = (ए 2-बी 2) / ए 2> प्रदान की गई जगह में 50%।
3. स्वरूप कक्ष संवाद बॉक्स खोलने के लिए प्रारूप बटन पर क्लिक करें।
4. भरें टैब पर क्लिक करें और एक लाल भरने का रंग चुनें।
5. संवाद बॉक्स को बंद करने के लिए दो बार ठीक क्लिक करें और वर्कशीट पर वापस आएं।
6. सेल बी 3 का पृष्ठभूमि रंग अभी भी नीला होना चाहिए जो दर्शाता है कि कोशिकाओं ए 3 और बी 3 में संख्याओं के बीच प्रतिशत अंतर 25% से अधिक है लेकिन 50% से कम या बराबर है।
7. सेल बी 5 का पृष्ठभूमि रंग लाल रंग में बदलना चाहिए जो दर्शाता है कि कोशिकाओं ए 5 और बी 5 में संख्याओं के बीच प्रतिशत अंतर 50% से अधिक है।

सशर्त स्वरूपण नियमों की जांच

% अंतर की गणना

यह जांचने के लिए कि सशर्त स्वरूपण नियम सही हैं, हम कक्ष C2: C5 में सूत्रों को दर्ज कर सकते हैं जो श्रेणी ए 2: ए 5 और बी 2: बी 5 में संख्याओं के बीच सटीक प्रतिशत अंतर की गणना करेंगे।

1. इसे सक्रिय सेल बनाने के लिए सेल सी 2 पर क्लिक करें।
2. सूत्र = (ए 2-बी 2) / ए 2 में टाइप करें और कीबोर्ड पर एंटर कुंजी दबाएं।
3. उत्तर 10% सेल सी 2 में दिखाई देना चाहिए, यह दर्शाता है कि सेल बी 2 में संख्या सेल बी 2 की संख्या से 10% अधिक है।
4. प्रतिशत को प्रतिशत के रूप में प्रदर्शित करने के लिए सेल सी 2 पर स्वरूपण को बदलना आवश्यक हो सकता है।
5. सेल सी 2 से कोशिकाओं सी 3 से सी 5 तक सूत्र की प्रतिलिपि बनाने के लिए भरने वाले हैंडल का उपयोग करें।
6. कोशिकाओं सी 3 से सी 5 के लिए जवाब होना चाहिए: 30%, 25%, और 60%।
7. इन कोशिकाओं में दिए गए उत्तरों से पता चलता है कि सशर्त स्वरूपण नियम सही हैं क्योंकि कक्ष ए 3 और बी 3 के बीच अंतर 25% से अधिक है और कक्ष ए 5 और बी 5 के बीच का अंतर 50% से अधिक है।
8. सेल बी 4 ने रंग नहीं बदला क्योंकि कोशिकाओं ए 4 और बी 4 के बीच का अंतर 25% के बराबर है, और हमारे सशर्त स्वरूपण नियम ने निर्दिष्ट किया है कि पृष्ठभूमि रंग के लिए नीले रंग में बदलने के लिए 25% से अधिक प्रतिशत आवश्यक था।

सशर्त स्वरूपण नियमों के लिए प्राथमिकता का आदेश

विवादित सशर्त स्वरूपण नियम लागू करना

जब डेटा के समान सीमा पर एकाधिक नियम लागू होते हैं, तो एक्सेल पहले निर्धारित करता है कि नियम संघर्ष करते हैं या नहीं।

विवादित नियम वे हैं जहां प्रत्येक नियम के लिए चुने गए स्वरूपण विकल्प दोनों को एक ही डेटा पर लागू नहीं किया जा सकता है।

इस ट्यूटोरियल में इस्तेमाल किए गए उदाहरण में, दोनों नियम नियमों का विरोध करते हैं क्योंकि दोनों नियम समान स्वरूपण विकल्प का उपयोग करते हैं - पृष्ठभूमि सेल रंग बदलने के लिए।

ऐसी स्थिति में जहां दूसरा नियम सत्य है (मूल्य में अंतर दो कोशिकाओं के बीच 50% से अधिक है) तो पहला नियम (25% से अधिक मूल्य में अंतर) भी सत्य है।

एक्सेल के प्राथमिकता का आदेश

चूंकि एक सेल में एक ही समय में एक लाल और नीली पृष्ठभूमि नहीं हो सकती है, इसलिए एक्सेल को यह पता होना चाहिए कि कौन सा सशर्त स्वरूपण नियम लागू होना चाहिए।

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

जैसा कि ऊपर दी गई छवि में दिखाया गया है, इस ट्यूटोरियल में उपयोग किया जाने वाला दूसरा नियम (= (ए 2-बी 2) / ए 2> 50%) सूची में अधिक है और इसलिए, पहले नियम पर प्राथमिकता है।

नतीजतन, सेल बी 5 का पृष्ठभूमि रंग लाल हो गया है।

डिफ़ॉल्ट रूप से, सूची के शीर्ष पर नए नियम जोड़े जाते हैं और इसलिए, उच्च प्राथमिकता होती है।

प्राथमिकता के क्रम को बदलने के लिए उपरोक्त छवि में पहचाने गए संवाद बॉक्स में ऊपर और नीचे तीर बटन का उपयोग करें।

गैर-विवादित नियमों को लागू करना

यदि दो या दो से अधिक सशर्त स्वरूपण नियम संघर्ष नहीं करते हैं तो दोनों नियम लागू होते हैं जब प्रत्येक नियम परीक्षण की स्थिति सच हो जाती है।

यदि हमारे उदाहरण में पहला सशर्त स्वरूपण नियम (= (ए 2-बी 2) / ए 2> 25%) नीली पृष्ठभूमि रंग की बजाय नीली सीमा के साथ कोशिकाओं बी 2: बी 5 की श्रेणी स्वरूपित करता है, तो दो सशर्त स्वरूपण नियम तब तक संघर्ष नहीं करेंगे दोनों प्रारूपों को दूसरे के साथ हस्तक्षेप किए बिना लागू किया जा सकता है।

नतीजतन, सेल बी 5 में नीली सीमा और लाल पृष्ठभूमि रंग दोनों होंगे, क्योंकि कोशिकाओं ए 5 और बी 5 में संख्याओं के बीच अंतर 25 और 50 प्रतिशत से अधिक है।

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

सशर्त स्वरूपण नियमों और मैन्युअल रूप से लागू स्वरूपण विकल्पों के बीच संघर्ष के मामले में, सशर्त स्वरूपण नियम हमेशा प्राथमिकता लेता है और मैन्युअल रूप से जोड़े गए स्वरूपण विकल्पों के बजाय लागू किया जाएगा।

यदि पीले रंग की पृष्ठभूमि रंग शुरू में कोशिकाओं बी 2 से बी 5 पर लागू किया गया था, तो सशर्त स्वरूपण नियम जोड़े जाने के बाद, केवल कोशिका बी 2 और बी 4 पीले रहेंगे।

चूंकि सशर्त स्वरूपण नियम कोशिकाओं बी 3 और बी 5 पर लागू होते हैं, इसलिए उनके पृष्ठभूमि रंग क्रमशः पीले से नीले और लाल रंग में बदल जाएंगे।