एक्सेल सूत्र: डुप्लिकेट पंक्तियों को हाइलाइट करें -

सामान्य सूत्र

=COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1)

सारांश

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

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

उदाहरण के लिए, यदि आपके पास B4: D11 कक्षों में मान हैं, और संपूर्ण डुप्लिकेट पंक्तियों को हाइलाइट करना चाहते हैं, तो आप बदसूरत सूत्र का उपयोग कर सकते हैं:

=COUNTIFS($B$4:$B$11,$B4,$C$4:$C$11,$C4,$D$4:$D$11,$D4)>1

एक क्लीनर वाक्यविन्यास के लिए नामित पर्वतमाला

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

=COUNTIFS(col_b,$B4,col_c,$C4,col_d,$D4)>1

स्पष्टीकरण

सूत्र में, COUNTIFS अपने "माता-पिता" कॉलम में दिखाई देने वाली सेल में प्रत्येक मान की संख्या को गिनता है। परिभाषा के अनुसार, प्रत्येक मूल्य को कम से कम एक बार दिखाई देना चाहिए, इसलिए जब गिनती> 1, मूल्य एक डुप्लिकेट होना चाहिए। संदर्भों को सावधानीपूर्वक बंद किया जाता है, इसलिए सूत्र केवल तभी वापस आएगा जब एक पंक्ति में सभी 3 कोशिकाएं अपने संबंधित स्तंभों में एक से अधिक बार दिखाई दें।

हेल्पर कॉलम ऑप्शन "चीटिंग" को एक साथ एक सेल में एक पंक्ति में सभी मानों को मिलाकर एक सेल बनाता है। उसके बाद COUNTIF केवल यह गणना करता है कि कॉलम D में यह समाप्‍त मान कितनी बार आता है।

सहायक स्तंभ + संघनन

यदि आपको अपने डेटा में सहायक कॉलम जोड़ने में कोई आपत्ति नहीं है, तो आप सशर्त स्वरूपण फ़ॉर्मूला को थोड़ा सरल कर सकते हैं। एक सहायक कॉलम में, सभी कॉलमों से मानों को मिलाएं। उदाहरण के लिए, स्तंभ E में एक सूत्र जोड़ें जो ऐसा दिखता है:

=B4&C4&D4

फिर सशर्त स्वरूपण नियम में निम्नलिखित सूत्र का उपयोग करें:

=COUNTIF($E$4:$E$11,$E4)>1

यह बहुत सरल नियम है, और यदि आप चाहें तो आप सहायक कॉलम को छिपा सकते हैं।

यदि आपके पास वास्तव में बड़ी संख्या में कॉलम हैं, तो आप TEXTJOIN फ़ंक्शन (एक्सेल 2016 365) का उपयोग करके एक सीमा का उपयोग कर सहमति बना सकते हैं:

=TEXTJOIN(",",TRUE,A1:Z1)

फिर आप ऊपर के रूप में COUNTIF का उपयोग कर सकते हैं।

SUMPRODUCT

यदि आप 2007 से पहले एक्सेल के संस्करण का उपयोग कर रहे हैं, तो आप इस तरह SUMPRODUCT का उपयोग कर सकते हैं:

=SUMPRODUCT((col_b=$B4)*(col_c=$C4)*(col_d=$D4))>1