इस ट्यूटोरियल में, मैं चर्चा करूंगा कि एक्सेल में उन सेल्स को कैसे हाइलाइट करें जिनमें सूची से टेक्स्ट है। मैं सेल्स को हाइलाइट करने के लिए कंडीशनल फ़ॉर्मेटिंग का उपयोग करूंगा। इसके अलावा, मैं कंडीशनल फ़ॉर्मेटिंग के रूप में कई एक्सेल फ़ंक्शन और सूत्रों का उपयोग करके सेल्स को हाइलाइट करूंगा।
आप इस लेख को तैयार करने के लिए उपयोग किए गए प्रैक्टिस वर्कबुक को डाउनलोड कर सकते हैं।
सूची से टेक्स्ट वाले सेल्स को हाइलाइट करने के 7 तरीके
1. सूची से टेक्स्ट वाले सेल्स को हाइलाइट करने के लिए COUNTIF फ़ंक्शन का उपयोग करें
आप सूची से टेक्स्ट वाले सेल्स को हाइलाइट करने के लिए COUNTIF फ़ंक्शन का उपयोग कर सकते हैं। उदाहरण के लिए, मेरे पास फल 1 (B4:B14) की एक सूची है जिसमें कई फलों के नाम हैं। दूसरी सूची में, फल 2 (D4:D9), मेरे पास कुछ अन्य फलों के नाम हैं। अब मैं फल 1 की सूची में फल 2 के फलों के नाम हाइलाइट करूंगा।
चरण:
- सबसे पहले, डेटासेट (B5:B14) चुनें।
- होम > कंडीशनल फ़ॉर्मेटिंग (स्टाइल्स समूह) पर जाएं।
- अगला, कंडीशनल फ़ॉर्मेटिंग ड्रॉप-डाउन से नया नियम पर जाएं।
- नतीजतन, नया फ़ॉर्मेटिंग नियम विंडो दिखाई देगी। फिर, नियम प्रकार: सूत्र का उपयोग करके निर्धारित करें कि कौन से सेल्स फ़ॉर्मेट करें चुनें। नीचे दिए गए सूत्र को फ़ील्ड में टाइप करें: इस सूत्र के सत्य होने पर मान फ़ॉर्मेट करें। बाद में, फ़ॉर्मेट पर क्लिक करें।
=COUNTIF($D$5:$D$9,B5)
- भरें से हाइलाइट रंग चुनें। OK क्लिक करें।
- डायलॉग बॉक्स बंद करने के लिए फिर से OK क्लिक करें।
- अंत में, आप देखेंगे कि सूची फल 2 से टेक्स्ट वाले सभी सेल्स हाइलाइट हो गए हैं।
और पढ़ें: एक्सेल में सेल कैसे हाइलाइट करें (5 तरीके)
2. सूची से टेक्स्ट वाले सेल्स को हाइलाइट करने के लिए एक्सेल MATCH फ़ंक्शन लागू करें
अब हम सूची से टेक्स्ट वाले सेल्स को हाइलाइट करने के लिए MATCH फ़ंक्शन का उपयोग करेंगे।
चरण:
- सबसे पहले, डेटासेट (B5:C14) चुनें।
- होम > कंडीशनल फ़ॉर्मेटिंग > नया नियम पर जाएं।
- नया फ़ॉर्मेटिंग नियम विंडो दिखाई देगी। फिर, नियम प्रकार: सूत्र का उपयोग करके निर्धारित करें कि कौन से सेल्स फ़ॉर्मेट करें चुनें। नीचे दिए गए सूत्र को फ़ील्ड में टाइप करें: इस सूत्र के सत्य होने पर मान फ़ॉर्मेट करें। उसके बाद, फ़ॉर्मेट बटन पर क्लिक करें और भरें टैब से हाइलाइट रंग चुनें।
=MATCH($B5,$E$5:$E$9,0)- OK > OK क्लिक करके डायलॉग बॉक्स बंद करें।
- नतीजतन, फल 1 में सूची फल 2 से टेक्स्ट वाले सभी सेल्स हाइलाइट हो गए हैं।
और पढ़ें: एक्सेल में चयनित टेक्स्ट कैसे हाइलाइट करें (8 तरीके)
3. सेल्स हाइलाइट करने के लिए SUM और COUNTIF फ़ंक्शन का संयोजन
इस बार मैं दूसरी सूची से टेक्स्ट वाले सेल्स को हाइलाइट करने के लिए फ़ंक्शन के संयोजन का उपयोग करूंगा। उदाहरण के लिए, मैं SUM फ़ंक्शन को COUNTIF फ़ंक्शन के साथ संयोजित करूंगा सेल्स हाइलाइट करने के लिए। यहां, मेरे पास एक डेटासेट किताबें (B4:B12) है जिसमें कुछ किताबों के नाम हैं। दूसरी सूची टेक्स्ट (D5:D8) में, मेरे पास एकल शब्दों की एक सूची है। तो, अब मैं डेटासेट किताबें में टेक्स्ट के शब्दों को हाइलाइट करूंगा।
चरण:
- सबसे पहले, डेटासेट (B5:B12) चुनें।
- फिर होम टैब > कंडीशनल फ़ॉर्मेटिंग ड्रॉप-डाउन चुनें।
- अगला, कंडीशनल फ़ॉर्मेटिंग ड्रॉप-डाउन से नया नियम चुनें।
- नतीजतन, नया फ़ॉर्मेटिंग नियम विंडो दिखाई देगी। नियम प्रकार: सूत्र का उपयोग करके निर्धारित करें कि कौन से सेल्स फ़ॉर्मेट करें चुनें। नीचे दिए गए सूत्र को फ़ील्ड में टाइप करें: इस सूत्र के सत्य होने पर मान फ़ॉर्मेट करें। उसके बाद, फ़ॉर्मेट बटन पर क्लिक करें और भरें टैब से हाइलाइट रंग चुनें।
=SUM(COUNTIF(B5,"*"&$D$5:$D$8&"*"))- OK > OK क्लिक करके डायलॉग बॉक्स बंद करें।
- अंत में, किताबें में सूची टेक्स्ट से टेक्स्ट वाले सेल्स हाइलाइट हो गए हैं।
यहां, COUNTIF फ़ंक्शन दी गई शर्त को पूरा करने वाली रेंज के भीतर सेल्स की संख्या लौटाता है। और SUM फ़ंक्शन रेंज में संख्याओं को जोड़ता है।
और पढ़ें: मान के आधार पर एक्सेल में सेल्स हाइलाइट करें (9 तरीके)
4. सूची से टेक्स्ट वाले सेल्स को हाइलाइट करने के लिए COUNT और SEARCH फ़ंक्शन का उपयोग करें
विधि 3 की तरह, मैं सेल्स हाइलाइट करने के लिए फ़ंक्शन के संयोजन का उपयोग करूंगा। उदाहरण के लिए, मैं COUNT और SEARCH फ़ंक्शन के संयोजन का उपयोग करके दूसरी सूची से टेक्स्ट वाले सेल्स को हाइलाइट करूंगा। इस बार, मैं फिर से दो फलों के नामों की सूची को अपने डेटासेट के रूप में उपयोग करूंगा।
चरण:
- शुरुआत में, डेटासेट (B5:B14) चुनें।
- होम > कंडीशनल फ़ॉर्मेटिंग > नया नियम पर जाएं।
- नया फ़ॉर्मेटिंग नियम डायलॉग दिखाई देगा। फिर, नियम प्रकार: सूत्र का उपयोग करके निर्धारित करें कि कौन से सेल्स फ़ॉर्मेट करें चुनें। नीचे दिए गए सूत्र को फ़ील्ड में टाइप करें: इस सूत्र के सत्य होने पर मान फ़ॉर्मेट करें।
=COUNT(SEARCH(D$5:D$9,B5))- उसके बाद, फ़ॉर्मेट बटन पर क्लिक करें और भरें टैब से हाइलाइट रंग चुनें। डायलॉग बॉक्स बंद करने के लिए OK > OK क्लिक करें।
- नतीजतन, फल 1 में सूची फल 2 से फल नाम वाले सेल्स हाइलाइट हो गए हैं।
यहां, SEARCH फ़ंक्शन उस विशिष्ट वर्ण या टेक्स्ट स्ट्रिंग की स्थिति में वर्णों की संख्या गिनता है जो बाएं से दाएं पढ़ने पर पहली बार मिलता है। दूसरी ओर, COUNT फ़ंक्शन रेंज में उन सेल्स की संख्या गिनता है जिनमें संख्याएं होती हैं।
और पढ़ें: एक्सेल में टेक्स्ट के आधार पर सेल्स हाइलाइट करें [2 तरीके]
समान पठन:
- एक्सेल में कॉलम कैसे हाइलाइट करें (3 तरीके)
- प्रतिशत के आधार पर एक्सेल में सेल रंग भरें (6 तरीके)
- एक्सेल में हर 5 पंक्तियों को कैसे हाइलाइट करें (4 तरीके)
- IF स्टेटमेंट का उपयोग करके एक्सेल में सेल हाइलाइट करें (7 तरीके)
- दो एक्सेल शीट्स की तुलना करें और अंतर हाइलाइट करें (7 तरीके)
5. सूची से सेल्स हाइलाइट करने के लिए सरल सूत्र और ड्रॉप डाउन सूची लागू करें
इस बार, मैं सेल्स हाइलाइट करने के लिए एक सरल सूत्र और ड्रॉप-डाउन सूची का उपयोग करूंगा। इस उद्देश्य के लिए, मैं एक ड्रॉप-डाउन सूची बनाऊंगा। फिर कंडीशनल फ़ॉर्मेटिंग नियम में, मैं सूत्र डालूंगा। इस बार, मैं फिर से दो फलों के नामों की सूची को अपने डेटासेट के रूप में उपयोग करूंगा।
चरण:
- सबसे पहले, सेल D5 में कुछ फलों के नामों से ड्रॉप-सूची बनाएं।
- डेटासेट (B5:B14) चुनें।
- होम > कंडीशनल फ़ॉर्मेटिंग पर जाएं।
- अगला, कंडीशनल फ़ॉर्मेटिंग ड्रॉप-डाउन > नया नियम पर जाएं।
- नतीजतन, नया फ़ॉर्मेटिंग नियम डायलॉग दिखाई देगा। उसके बाद, नियम प्रकार: सूत्र का उपयोग करके निर्धारित करें कि कौन से सेल्स फ़ॉर्मेट करें चुनें। नीचे दिए गए सूत्र को फ़ील्ड में टाइप करें: इस सूत्र के सत्य होने पर मान फ़ॉर्मेट करें। फ़ॉर्मेट बटन पर क्लिक करें और भरें टैब से हाइलाइट रंग चुनें।
=$B5=$D$5- OK > OK क्लिक करके डायलॉग बॉक्स बंद करें।
- परिणामस्वरूप, ड्रॉप-डाउन सूची से फलों वाले सेल्स हाइलाइट हो गए हैं।
- यह विधि डायनामिक रूप से काम करती है जिसका अर्थ है कि यदि आप ड्रॉप-डाउन सूची से फल का नाम बदलते हैं तो हाइलाइट किए गए सेल्स तदनुसार बदल जाएंगे।
और पढ़ें: ऊपर से नीचे तक एक्सेल में हाइलाइट कैसे करें (5 तरीके)
6. सूची से सेल्स हाइलाइट करने के लिए एक्सेल ISNUMBER और FIND फ़ंक्शन
अब, मैं सेल्स हाइलाइट करने के लिए फ़ंक्शन के संयोजन और ड्रॉप-डाउन सूची का उपयोग करूंगा। उदाहरण के लिए, मैं ISNUMBER और FIND फ़ंक्शन के संयोजन का उपयोग करके दूसरी सूची से टेक्स्ट वाले सेल्स को हाइलाइट करूंगा। इस बार, मैं फिर से दो फलों के नामों की सूची को अपने डेटासेट के रूप में उपयोग करूंगा।
चरण:
- सबसे पहले, सेल D5 में कुछ फलों के नामों से ड्रॉप-डाउन सूची बनाएं।
- डेटासेट (B5:B14) चुनें।
- होम > कंडीशनल फ़ॉर्मेटिंग > नया नियम पर जाएं।
- नया फ़ॉर्मेटिंग नियम विंडो दिखाई देगी। नियम प्रकार: सूत्र का उपयोग करके निर्धारित करें कि कौन से सेल्स फ़ॉर्मेट करें चुनें। नीचे दिए गए सूत्र को फ़ील्ड में टाइप करें: इस सूत्र के सत्य होने पर मान फ़ॉर्मेट करें। उसके बाद, फ़ॉर्मेट बटन पर क्लिक करें और भरें टैब से हाइलाइट रंग चुनें।
=ISNUMBER(FIND($D$5,B5))- फॉर्मेट रंग चुनने के बाद OK क्लिक करें। डायलॉग बॉक्स बंद करने के लिए फिर से OK क्लिक करें।
- अंत में, किताबें में सूची टेक्स्ट से टेक्स्ट वाले सेल्स हाइलाइट हो गए हैं।
यहां, FIND फ़ंक्शन दूसरे टेक्स्ट स्ट्रिंग के भीतर एक टेक्स्ट स्ट्रिंग की प्रारंभिक स्थिति लौटाता है (केस सेंसिटिव)। बाद में, ISNUMBER फ़ंक्शन जांचता है कि कोई मान संख्या है या नहीं, और TRUE या FALSE लौटाता है।
और पढ़ें: एक्सेल में चयनित सेल्स कैसे हाइलाइट करें (5 आसान तरीके)
7. SUMPRODUCT, ISNUMBER, और SEARCH फ़ंक्शन संयोजन से सूची से टेक्स्ट वाले सेल्स हाइलाइट करें
इस विधि में, मैं SUMPRODUCT फ़ंक्शन का उपयोग ISNUMBER फ़ंक्शन और SEARCH फ़ंक्शन के साथ करूंगा। मैं किताबें (B4:B12) में किताबों के नामों को टेक्स्ट (B5:B8) के शब्दों से हाइलाइट करूंगा।
चरण:
- सबसे पहले डेटासेट (B5:B12) चुनें।
- होम > कंडीशनल फ़ॉर्मेटिंग ड्रॉप-डाउन पर जाएं।
- अगला, कंडीशनल फ़ॉर्मेटिंग ड्रॉप-डाउन से नया नियम चुनें।
- नतीजतन, नया फ़ॉर्मेटिंग नियम डायलॉग बॉक्स दिखाई देगा। नियम प्रकार: सूत्र का उपयोग करके निर्धारित करें कि कौन से सेल्स फ़ॉर्मेट करें चुनें। नीचे दिए गए सूत्र को फ़ील्ड में टाइप करें: इस सूत्र के सत्य होने पर मान फ़ॉर्मेट करें। फिर, फ़ॉर्मेट बटन पर क्लिक करें और भरें टैब से हाइलाइट रंग चुनें।
=SUMPRODUCT(--ISNUMBER(SEARCH($D$5:$D$8,B5)))>0- OK > OK क्लिक करके डायलॉग बॉक्स बंद करें।
- अंत में, सूची टेक्स्ट से टेक्स्ट वाले किताबों के नाम किताबें में हाइलाइट हो गए हैं।
🔎 सूत्र कैसे काम करता है?
➤ SEARCH($D$5:$D$8,B5)
सूत्र का यह भाग लौटाता है:
{#VALUE!;#VALUE!;1;5}
यहां, SEARCH फ़ंक्शन मान की स्थिति लौटाता है। लेकिन यदि मान नहीं मिलता तो यह त्रुटि (#VALUE!) लौटाता है।
➤ ISNUMBER(SEARCH($D$5:$D$8,B5))
ISNUMBER फ़ंक्शन सभी त्रुटि परिणामों को FALSE और अन्य परिणामों को TRUE में बदल देता है।
यह भाग उत्तर देता है:
{FALSE;FALSE;TRUE;TRUE}
➤ –ISNUMBER(SEARCH($D$5:$D$8,B5))
यह भाग लौटाता है:
{0;0;1;1}
डबल माइनस (—) चिह्न सभी FALSE और TRUE मानों को 0 और 1 में बदल देता है।
➤ SUMPRODUCT(–ISNUMBER(SEARCH($D$5:$D$8,B5)))>0
यह भाग उत्तर देता है:
{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
अंत में, SUMPRODUCT फ़ंक्शन पिछले परिणाम को शून्य (0) के खिलाफ परीक्षण करता है और इस प्रकार कंडीशनल फ़ॉर्मेटिंग नियम को सेल्स हाइलाइट करने के लिए मार्गदर्शन करता है।
संबंधित सामग्री: सेल रंग पर आधारित एक्सेल सूत्र (5 उदाहरण)
निष्कर्ष
उपरोक्त लेख में, मैंने सूची से टेक्स्ट वाले सेल्स को हाइलाइट करने की कई विधियों को विस्तार से चर्चा करने का प्रयास किया है। उम्मीद है, ये विधियां और स्पष्टीकरण आपकी समस्याओं को हल करने के लिए पर्याप्त होंगे। कृपया मुझे बताएं यदि आपके कोई प्रश्न हैं।
संबंधित लेख
- एक्सेल सेल रंग: जोड़ें, संपादित करें, उपयोग करें और हटाएं
- सूत्र का उपयोग करके एक्सेल सेल में रंग भरें (5 आसान तरीके)
- एक्सेल में पंक्ति कैसे हाइलाइट करें (5 त्वरित तरीके)
- एक्सेल VBA से मान के आधार पर सेल हाइलाइट करें (5 उदाहरण)
- VBA से एक्सेल में मान के आधार पर सेल रंग बदलें (3 आसान उदाहरण)
