सशर्त स्वरूपण के साथ डुप्लिकेट - एक्सेल टिप्स

क्रेग क्रॉसमैन के कंप्यूटर अमेरिका रेडियो शो पर कल रात, जो बोस्टन से एक सवाल था:

मेरे पास चालान संख्याओं का एक कॉलम है। मैं डुप्लिकेट को चिह्नित करने के लिए एक्सेल का उपयोग कैसे कर सकता हूं?

मैंने सशर्त स्वरूपों और COUNTIF सूत्र का उपयोग करने का सुझाव दिया। उस काम को कैसे करना है, इस बारे में विवरण यहां दिए गए हैं।

हम पूरी श्रेणी के लिए सशर्त स्वरूपण सेट करना चाहते हैं, लेकिन सीमा में पहले सेल के लिए एक सशर्त प्रारूप सेट करना आसान है और फिर उस सशर्त प्रारूप की प्रतिलिपि बनाएँ। हमारे मामले में, सेल A1 में इनवॉइस नंबर है, इसलिए मैं सेल A2 का चयन करूंगा और मेनू से, प्रारूप> सशर्त स्वरूपण का चयन करूंगा। सशर्त स्वरूपण संवाद प्रारंभिक ड्रॉपडाउन "सेल वैल्यू इज़" कहकर शुरू होता है। यदि आप इसके बगल में तीर को छूते हैं, तो आप "फॉर्मूला इज़" चुन सकते हैं।

"फॉर्मूला इज" का चयन करने के बाद, संवाद बॉक्स उपस्थिति बदलता है। "बीच x और y" के लिए बक्से के बजाय, अब एक एकल सूत्र बॉक्स है। यह सूत्र बॉक्स अविश्वसनीय रूप से शक्तिशाली है। आप किसी भी सूत्र में टाइप कर सकते हैं जिसे आप सपना देख सकते हैं, जब तक कि वह सूत्र TRUE या FALSE का मूल्यांकन करेगा।

हमारे मामले में, हमें COUNTIF सूत्र का उपयोग करने की आवश्यकता है। बॉक्स में टाइप करने का सूत्र है

=COUNTIF(A:A,A2)>1

अंग्रेजी में, यह कहता है, "कॉलम की पूरी श्रृंखला के माध्यम से देखें। गणना करें कि उस सीमा में कितनी कोशिकाएं वैसी ही हैं जैसे कि A2 में हैं। (यह वास्तव में महत्वपूर्ण है कि सूत्र में" A2 "इंगित हो। वर्तमान सेल - वह सेल जिसे आप सशर्त स्वरूपण में सेट कर रहे हैं। इसलिए - यदि आपका डेटा कॉलम E में है और आप E5 में पहले सशर्त स्वरूपण सेट कर रहे हैं, तो सूत्र होगा =COUNTIF(E:E,E5)>0)। फिर, हम यह देखने के लिए तुलना करते हैं कि क्या गिनती है। is> 1. आदर्श रूप से, कोई डुप्लिकेट नहीं है, गिनती हमेशा 1 होगी - क्योंकि सेल A2 सीमा में है - हमें स्तंभ A में ठीक एक सेल मिलना चाहिए जिसमें A2 जैसा ही मान हो।

स्वरूप… बटन पर क्लिक करें

अब यह एक अप्रिय प्रारूप का चयन करने का समय है। इस स्वरूप कक्ष संवाद के शीर्ष पर तीन टैब हैं। फ़ॉन्ट टैब आमतौर पर पहले होता है, इसलिए आप एक बोल्ड, लाल फ़ॉन्ट का चयन कर सकते हैं, लेकिन मुझे कुछ अधिक अप्रिय लगता है। मैं आमतौर पर पैटर्न टैब पर क्लिक करता हूं और चमकदार लाल या चमकीले पीले रंग का चयन करता हूं। रंग चुनें, फिर स्वरूप कक्ष संवाद बंद करने के लिए ठीक क्लिक करें।

आपको "प्रारूप का उपयोग करने के लिए पूर्वावलोकन" बॉक्स में चयनित प्रारूप दिखाई देगा। सशर्त स्वरूपण संवाद बंद करने के लिए ठीक क्लिक करें …

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

आपको अपनी सीमा में A2 से नीचे के सशर्त स्वरूपण को दूसरे कक्षों में कॉपी करने की आवश्यकता है। A2 में sill के साथ, Edit> Copy करें। संपूर्ण कॉलम चुनने के लिए Ctrl + Spacebar को हिट करें। संपादित करें> विशेष पेस्ट करें। चिपकाएँ विशेष संवाद में, स्वरूप पर क्लिक करें। ओके पर क्लिक करें।

यह कॉलम में सभी कोशिकाओं के सशर्त स्वरूपण की प्रतिलिपि बनाएगा। अब - अंत में - आप लाल स्वरूपण के साथ कुछ कोशिकाओं को देखते हैं, यह दर्शाता है कि आपके पास एक डुप्लिकेट है।

सेल ए 3 में जाना और कॉपी के बाद सशर्त प्रारूप को देखना जानकारीपूर्ण है। A3 का चयन करें, सशर्त स्वरूपण को लाने के लिए od को हिट करें कॉलम A: A में कितनी बार A3 प्रकट होता है यह गिनने के लिए फॉर्मूला Is बॉक्स में सूत्र बदल गया।

टिप्पणियाँ

जो के सवाल में, उनके पास रेंज में केवल 1700 चालान थे। मैंने सशर्त स्वरूपण के साथ 65536 सेल स्थापित किए हैं और प्रत्येक सेल वर्तमान सेल की तुलना 65536 अन्य कोशिकाओं से कर रहा है। एक्सेल 2005 में - अधिक पंक्तियों के साथ - समस्या और भी बदतर होगी। तकनीकी रूप से, पहले चरण में सूत्र हो सकता है:=COUNTIF($A$2:$A$1751,A2)>1

इसके अलावा, पूरे कॉलम में सशर्त प्रारूप को कॉपी करते समय, आप पेस्ट स्पेशल फॉर्मेट्स करने से पहले डेटा के साथ सिर्फ पंक्तियों को चुन सकते थे।

अधिक

प्रश्न के बाद मैंने जो दूसरा मुद्दा बताया, वह यह है कि आप वास्तव में एक सशर्त प्रारूप के आधार पर एक कॉलम को सॉर्ट नहीं कर सकते। यदि आपको इस डेटा को क्रमबद्ध करने की आवश्यकता है ताकि डुप्लिकेट एक क्षेत्र में हों, तो इन चरणों का पालन करें। सबसे पहले, B1 को "डुप्लिकेट?" नामक शीर्षक जोड़ें। बी 2 में इस सूत्र टाइप करें: =COUNTIF(A:A,A2)>1।

बी 2 में सेल पॉइंटर के साथ, रेंज के नीचे सभी तरह से फॉर्मूला कॉपी करने के लिए ऑटोफिल हैंडल (सेल के निचले दाएं कोने में थोड़ा वर्ग) पर क्लिक करें।

अब आप स्तंभ B अवरोही और A आरोही के आधार पर श्रेणी के शीर्ष पर समस्या चालान कर सकते हैं।

यह समाधान मानता है कि आप डुप्लिकेट इनवॉइस के बीओटीएच को उजागर करना चाहते हैं ताकि आप मैन्युअल रूप से पता लगा सकें कि किसको हटाना है या सही करना है। यदि आप डुप्लिकेट की पहली घटना को चिह्नित नहीं करना चाहते हैं, तो आप फॉर्मूला को समायोजित कर सकते हैं =COUNTIF($A$2:$A2,A2)>1:। दिखाए गए अनुसार डॉलर के संकेतों को दर्ज करना महत्वपूर्ण है। यह केवल वर्तमान सेल से सभी कोशिकाओं को देखेगा, डुप्लिकेट प्रविष्टियों की तलाश करेगा।

इस सवाल के लिए बोस्टन से जो धन्यवाद!