एक्सेल डेटा क्लीनिंग मास्टर करें: सटीक विश्लेषण के लिए 19 सिद्ध तकनीकें

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

आप नीचे दिए गए लिंक से प्रदर्शन के लिए उपयोग की गई वर्कबुक डाउनलोड कर सकते हैं।

एक्सेल में 19 डेटा क्लीनिंग तकनीकें जो काम आएंगी

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

1. डुप्लिकेट पंक्तियाँ हटाएं

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

आइए तुलना के लिए बाद में कॉलम को बगल में दोहराएं और फिर डुप्लिकेट हटाएं।

डुप्लिकेट हटाने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, उस कॉलम का चयन करें जिससे आप डुप्लिकेट हटाना चाहते हैं।
• अब अपने रिबन पर डेटा टैब पर जाएं।
• फिर वहां डेटा टूल्स समूह से डुप्लिकेट हटाएं चुनें।

• उसके बाद, पॉप-अप बॉक्स में वर्तमान चयन के साथ जारी रखें विकल्प चुनें और डुप्लिकेट हटाएं पर क्लिक करें।

• अगला, ओके पर क्लिक करें।

यह चयन से सभी डुप्लिकेट को हटा देगा।

2. डुप्लिकेट मान हाइलाइट करें

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

सशर्त स्वरूपण का उपयोग करना

पहले भाग में, हम डुप्लिकेट मानों को हाइलाइट करने के लिए सशर्त स्वरूपण सुविधा का उपयोग करने जा रहे हैं। हम इसे निम्नलिखित डेटासेट पर करेंगे।

यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।

चरण:

• सबसे पहले, उन सेल्स का चयन करें जिन्हें हम डुप्लिकेट करना चाहते हैं। इस मामले में, यह D5:D18 है।
• दूसरा, होम > सशर्त स्वरूपण > सेल नियम हाइलाइट करें > डुप्लिकेट मान चुनें।

• अंततः, एक डुप्लिकेट मान विंडो दिखाई देगी।
• तीसरा, डुप्लिकेट चुनें।
• चौथा, मान के साथ में कोई भी रंग विकल्प चुनें। इस मामले में, हमने गहरे लाल टेक्स्ट के साथ हल्का लाल भराव चुना है।

• परिणामस्वरूप, हम देखेंगे कि D कॉलम में डुप्लिकेट मान हाइलाइट हो गए हैं।

पहली घटना सहित डुप्लिकेट हाइलाइट करना

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

चरण:

• सबसे पहले, सशर्त स्वरूपण पर जाकर नया स्वरूपण नियम विंडो पर जाएं।

• दूसरा, कौन से सेल्स को प्रारूपित करना है यह निर्धारित करने के लिए सूत्र का उपयोग करें चुनें।
• तीसरा, सूत्र बॉक्स में निम्न सूत्र लिखें।

=COUNTIF($D$5:$E$16,$D5)>1

• परिणामस्वरूप, हम देखेंगे कि D कॉलम डुप्लिकेट के लिए हाइलाइट हो गया है, जिसमें पहली घटनाएं भी शामिल हैं।

पहली घटना को छोड़कर डुप्लिकेट हाइलाइट करना

अब हम डुप्लिकेट मानों की पहचान करने के लिए सबसे उपयोगी तकनीकों में से एक पर चर्चा करने जा रहे हैं। यह पहली घटनाओं को छोड़कर उन्हें हाइलाइट नहीं करता है। यह मददगार है यदि हम सभी डुप्लिकेट मानों के बजाय केवल आवर्ती मानों का निरीक्षण करना चाहते हैं। पिछले वाले की तरह, हम यहां COUNTIF फ़ंक्शन का उपयोग करेंगे।

यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।

चरण:

• इसे करने के लिए, पहले, सशर्त स्वरूपण विकल्प के माध्यम से नया स्वरूपण नियम पर जाएं जैसा कि हमने पहले चर्चा की थी।

• दूसरा, पहले की तरह ही कौन से सेल्स को प्रारूपित करना है यह निर्धारित करने के लिए सूत्र का उपयोग करें चुनें।
• तीसरा, सूत्र बॉक्स में निम्न सूत्र लिखें।

=COUNTIF($D$5:$D5,$D5)>1

• ओके पर क्लिक करने के बाद, यह पहली घटनाओं को छोड़कर सभी डुप्लिकेट को हाइलाइट करेगा।

3. टेक्स्ट को एकाधिक सेल्स में विभाजित करें

जब आप किसी अन्य स्रोत से डेटा आयात करते हैं, तो ऐसा हो सकता है कि कई मान एक ही कॉलम में आयात हो गए हों। निम्न छवि इस प्रकार की आयात घटना का एक उदाहरण दिखाती है।

एक उपयोगी टेक्स्ट टू कॉलम सुविधा है जिसका उपयोग हम इन टेक्स्ट को विभाजित करने में मदद के लिए कर सकते हैं।

चरण:

• सबसे पहले, उस रेंज का चयन करें जिससे आप टेक्स्ट विभाजित करना चाहते हैं।
• फिर अपने रिबन पर डेटा टैब पर जाएं।
• उसके बाद, डेटा टूल्स समूह से, टेक्स्ट टू कॉलम्स चुनें।

• अगला, अगले बॉक्स में सीमांकित चुनें।

• अगला पर क्लिक करने के बाद, एक और बॉक्स दिखाई देगा।
• यहां सीमांकक विकल्पों के तहत स्पेस चुनें क्योंकि टेक्स्ट स्पेस से विभाजित है।
• फिर अगला पर क्लिक करें।

• उसके बाद, समाप्त पर क्लिक करें।

• अंत में, टेक्स्ट विभाजित हो जाएगा।

• अब इसे और अधिक प्रस्तुत करने योग्य बनाने के लिए कुछ संशोधन करें।

यह है कि हम एक्सेल में अन्य डेटा क्लीनिंग तकनीकों के साथ टेक्स्ट को अलग-अलग सेल्स में कैसे विभाजित कर सकते हैं।

4. सूत्रों के साथ डेटा परिवर्तित करना

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

UPPER: यह सूत्र टेक्स्ट को ALL UPPERCASE में परिवर्तित करता है।

LOWER: यह सूत्र टेक्स्ट को all lowercase में परिवर्तित करता है।

PROPER: यह टेक्स्ट को उचित केस में परिवर्तित करता है (प्रत्येक शब्द का पहला अक्षर बड़ा होगा, जैसे उचित नाम में)।

हम सभी प्रस्तुतियों के लिए निम्नलिखित डेटासेट का उपयोग करेंगे।

अपर केस में बदलना

जैसा कि पहले उल्लेख किया गया है, हम इस उद्देश्य के लिए UPPER फ़ंक्शन का उपयोग करेंगे। यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।

चरण:

• सबसे पहले, सेल C5 का चयन करें।
• फिर इसमें निम्न सूत्र लिखें।

=UPPER(B5)

• उसके बाद, एंटर दबाएं।

• अब सेल को फिर से चुनें और फिल हैंडल आइकन को क्लिक करके खींचें ताकि बाकी सेल्स को सूत्र से भर दिया जाए।

लोअर केस में बदलना

अगला, हम प्रदर्शित करेंगे कि सभी टेक्स्ट को लोअरकेस में कैसे परिवर्तित किया जाए।

हम इसके लिए निम्न चरणों का पालन करेंगे।

चरण:

• सबसे पहले, सेल D5 का चयन करें।
• फिर इसमें निम्न सूत्र लिखें।

=LOWER(B5)

• उसके बाद, एंटर दबाएं।

• अब सेल को फिर से चुनें और फिल हैंडल आइकन को क्लिक करके खींचें ताकि बाकी सेल्स को सूत्र से भर दिया जाए।

उचित केस में बदलना

उचित केस उचित स्थानों पर बड़े अक्षरों और अन्य स्थानों पर लोअरकेस को इंगित करता है। इस डेटासेट के लिए, हम इसके लिए निम्न विधि का उपयोग कर रहे हैं।

चरण:

• सबसे पहले, सेल E5 का चयन करें।
• फिर इसमें निम्न सूत्र लिखें।

=PROPER(B5)

• उसके बाद, एंटर दबाएं।

• अंत में, सेल को फिर से चुनें और फिल हैंडल आइकन को क्लिक करके खींचें ताकि बाकी सेल्स भर जाएं।

5. अतिरिक्त स्पेस हटाएं

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

हम एक्सेल में अतिरिक्त स्पेस हटाने के लिए इन दो डेटा क्लीनिंग तकनीकों का पालन कर सकते हैं।

हम उन दोनों का उपयोग निम्नलिखित डेटासेट में करेंगे।

TRIM फ़ंक्शन का उपयोग करना

हम टेक्स्ट से सभी अग्रणी और अनुगामी स्पेस को हटाने के लिए TRIM फ़ंक्शन का उपयोग करते हैं। TRIM फ़ंक्शन एकाधिक स्पेस को एकल स्पेस से भी बदल देता है।

चरण:

• सबसे पहले, परिणाम दिखाने के लिए अपडेटेड डेटा नामक एक कॉलम जोड़ें।
• फिर सेल D5 पर क्लिक करें।
• =TRIM टाइप करें और पहले आर्गुमेंट में सेल C5 चुनें। पैरेंथेसिस बंद करें।
• तो, सूत्र बन जाता है:

=TRIM(C5)

• अब, एंटर दबाएं।

• अब फिल हैंडल आइकन को अंतिम सेल तक खींचें।

हम देख सकते हैं कि अनावश्यक स्पेस हटा दिए गए हैं; प्रत्येक शब्द के बाद केवल एकल स्पेस मौजूद हैं।

फाइंड एंड रिप्लेस सुविधा का उपयोग करना:

इस खंड में, हम चर्चा करेंगे कि सभी स्पेस को हटाने के लिए फाइंड एंड रिप्लेस कमांड का उपयोग कैसे करें।

चरण:

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

• जब हम रिप्लेस चुनते हैं, तो हमें एक डायलॉग बॉक्स मिलेगा।
• ढूंढें क्या फ़ील्ड में एक खाली स्पेस टाइप करें।
• बदलें साथ बॉक्स को खाली रखें।
• फिर, सभी बदलें पर क्लिक करें।

• सभी बदलें पर क्लिक करने के बाद, हमें प्रतिस्थापन की संख्या दिखाने वाला एक पॉप-अप मिलेगा।
• अब पॉप-अप पर ओके क्लिक करें।
• फिर डायलॉग बॉक्स के बंद करें पर क्लिक करें।
• अंत में, हमें परिणाम मिलता है।

6. अजीब वर्ण हटाएं

अक्सर, एक्सेल वर्कशीट में डेटा आयात करने के बाद, आप पाएंगे कि कुछ अजीब (कभी-कभी अनप्रिंटेबल) वर्ण हैं। आप टेक्स्ट से सभी नॉन-प्रिंटिंग वर्णों को हटाने के लिए CLEAN फ़ंक्शन का उपयोग कर सकते हैं। यदि डेटा सेल A2 में है, तो आप अपना काम करने के लिए इसका उपयोग कर सकते हैं:

=CLEAN(A2)

CLEAN फ़ंक्शन कभी-कभी कुछ नॉन-प्रिंटिंग यूनिकोड वर्णों को मिस कर सकता है। यह 7-बिट ASCII कोड में पहले 32 नॉन-प्रिंटिंग वर्णों को हटाने के लिए प्रोग्राम किया गया है। नॉन-प्रिंटिंग यूनिकोड वर्णों को कैसे हटाएं, इस बारे में जानकारी के लिए एक्सेल हेल्प सिस्टम ब्राउज़ करें।

7. मान परिवर्तित करें

डेटा क्लीनिंग तकनीकों में एक और महत्वपूर्ण है एक डेटासेट में एक मान को दूसरे में परिवर्तित करना। कभी-कभी आपको एक मीट्रिक सिस्टम से दूसरे मीट्रिक सिस्टम में मान परिवर्तित करने की आवश्यकता हो सकती है। उदाहरण के लिए, आप एक फ़ाइल आयात कर सकते हैं जिसमें तरल औंस (fl oz) में मान हैं, और उन्हें मिलीलीटर में व्यक्त करने की आवश्यकता है। एक्सेल का CONVERT फ़ंक्शन इस प्रकार और कई अन्य प्रकार के रूपांतरण कर सकता है।

यह फ़ंक्शन अत्यधिक सहायक है और निम्नलिखित श्रेणियों में सबसे आम माप इकाइयों को संभाल सकता है: वजन और द्रव्यमान, दूरी, समय, दबाव, बल, ऊर्जा, शक्ति, चुंबकत्व, तापमान, आयतन, तरल, क्षेत्र, बिट्स और बाइट्स, और गति।

प्रदर्शन के लिए, आइए एक डेटासेट देखें और इसे लागू करें। यह डेटासेट है।

तापमान को फारेनहाइट में बदलने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, आउटपुट सेल D5 चुनें
• फिर निम्न सूत्र टाइप करें

=CONVERT(C5,"C","F")

• उसके बाद, एंटर दबाएं।

• अब फिल हैंडल आइकन को क्लिक करके कॉलम के अंत तक खींचें ताकि सभी मान परिवर्तित हो जाएं।

यह है कि हम मानों को अन्य इकाइयों में कैसे परिवर्तित कर सकते हैं। अन्य इकाई रूपांतरण के लिए, यह लेख देखें।

8. त्रुटियों को हाइलाइट करें

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

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

निम्नलिखित स्प्रेडशीट पर एक नज़र डालें।

यहां त्रुटियों को हाइलाइट करने के चरण दिए गए हैं।

चरण:

• सबसे ऊपर, पूरे डेटासेट का चयन करें और सशर्त स्वरूपण विकल्पों से नया नियम पर क्लिक करें।

• फिर, नियम प्रकार में केवल उन सेल्स को प्रारूपित करें जिनमें हैं चुनें और फिर नीचे ड्रॉप-डाउन सूची से त्रुटियां चुनें।

• फिर ओके पर क्लिक करें।

यह डेटासेट का अंतिम परिणाम है। जैसा कि आप देख सकते हैं, एक्सेल ने त्रुटि मानों को हाइलाइट किया।

यह है कि आप एक्सेल में डेटा क्लीनिंग तकनीकों में से एक के रूप में त्रुटियों को कैसे हाइलाइट कर सकते हैं।

9. कॉलम जोड़ें

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

यह देखने के लिए इन चरणों का पालन करें कि हम एक्सेल में अलग-अलग सेल्स से इन टेक्स्ट को कैसे जोड़ सकते हैं।

चरण:

• सबसे पहले, सेल E5 का चयन करें।
• फिर निम्न सूत्र लिखें।

=B5&" "&D5&" "&C5

• उसके बाद, एंटर दबाएं।

• अब फिल हैंडल आइकन को क्लिक करके कॉलम के अंत तक खींचें ताकि बाकी सेल्स के लिए सूत्र दोहराया जाए।

यह है कि हम एक्सेल में दो या अधिक कॉलम कैसे जोड़ सकते हैं।

10. कॉलम पुनर्व्यवस्थित करें

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

यहां एक आसान तरीका है जिसका उपयोग आप कॉलम को आसानी से पुनर्व्यवस्थित करने के लिए कर सकते हैं, हम निम्नलिखित डेटासेट पर कर रहे हैं।

चरण:

• सबसे पहले, कॉलम हेडर पर क्लिक करके उस कॉलम का चयन करें जिसे आप स्थानांतरित करना चाहते हैं।
• फिर या तो संदर्भ मेनू का उपयोग करके या Ctrl+X दबाकर कॉलम काटें। आपको इस बिंदु पर चयन की सीमा पर बिंदीदार रेखा दिखाई देगी।

• उसके बाद, उस कॉलम हेडर पर राइट-क्लिक करें जिसके सामने आप पिछले कॉलम को रखना चाहते हैं।
• अब संदर्भ मेनू से कट सेल्स डालें चुनें।

• यह पिछले कॉलम को यहां डाल देगा।

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

11. पंक्तियों को रैंडमाइज़ करें

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

हम निम्नलिखित डेटासेट के लिए ऐसा कार्य करने जा रहे हैं।

एक्सेल में पंक्तियों को रैंडमाइज़ करने और क्रम बदलने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, हमें कॉलम B के बाद एक नया कॉलम बनाना होगा। इसके लिए, कॉलम C के हेडर अक्षर पर क्लिक करें, और पूरा C कॉलम चुना जाएगा।
• फिर, राइट-क्लिक करें और इन्सर्ट कमांड चुनें, और एक नया कॉलम बन जाएगा।

• अब, नव निर्मित कॉलम को एक नाम दें (यानी रैंडम नंबर)। पहला सेल चुनें (यानी C5) कॉलम और सेल में निम्न सूत्र टाइप करें।

=RAND()

• उसके बाद, एंटर दबाएं, और सेल C5 में एक रैंडम नंबर प्रदर्शित होगा। संख्या 1 से कम है।

• अगला, सेल को फिर से चुनें और फिल हैंडल आइकन को क्लिक करके कॉलम के अंत तक खींचें ताकि बाकी सेल्स के लिए सूत्र दोहराया जाए।

• जब सेल C5 चयनित हो तो होम टैब पर जाएं।
• फिर संपादन समूह पर जाएं और सॉर्ट एंड फ़िल्टर पर क्लिक करें।
• अब ड्रॉप-डाउन मेनू से सबसे छोटे से सबसे बड़े तक सॉर्ट करें चुनें।

• जैसे ही आप कमांड पर क्लिक करते हैं, आपकी पंक्तियाँ खुद को रैंडमाइज़ कर लेंगी। यह ध्यान देने योग्य है कि न केवल कॉलम C, बल्कि पंक्तियों के साथ सभी कॉलम भी बेतरतीब ढंग से बदल गए हैं।

तो ये वे चरण हैं जिनका आप पालन कर सकते हैं जब आपको एक डेटासेट कई कॉलम के साथ मिला हो और आप अपनी पंक्तियों को इस तरह से रैंडमाइज़ करना चाहते हों कि एक पंक्ति के साथ सभी कॉलम अपने मानों को रैंडमाइज़ करें।

12. URL से फ़ाइल नाम निकालें

कभी-कभी आपके पास URL की एक सूची हो सकती है और आपको केवल फ़ाइल नाम निकालने की आवश्यकता होती है। निम्न तरीके से, आप URL से फ़ाइल नाम निकाल सकते हैं। मान लें सेल B5 में यह URL है: https://example.com/assets/images/horse.jpg.

हमें यहां कार्यों की एक पूरी श्रृंखला से मिलकर एक सूत्र की आवश्यकता होगी। चिंता न करें, इसे समझने के लिए बाद में एक व्याख्या जोड़ी गई है। इस सूत्र में RIGHT, LEN, FIND और SUBSTITUTE फ़ंक्शन हैं।

अब लिंक से फ़ाइल नाम (horse.jpg) निकालने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, उस सेल का चयन करें जहां आप फ़ाइल नाम रखना चाहते हैं। इस मामले में, यह सेल C5 है।
• फिर इसमें निम्न सूत्र लिखें।

=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))))

• उसके बाद, एंटर दबाएं।

🔎 सूत्र का विवरण

👉 SUBSTITUTE(B5,"/","") URL से सभी "/" को हटा देता है। यह http:www.example.comassetsimageshorse.jpg लौटाता है।

👉 LEN(SUBSTITUTE(B5,"/","")) पिछली स्ट्रिंग की लंबाई लौटाता है।

👉 LEN(B5) सेल B4 में स्ट्रिंग की लंबाई लौटाता है जो यहां 46 है।

👉 LEN(B5)-LEN(SUBSTITUTE(B5,"/",""))) दो लंबाई मानों के बीच का अंतर दर्शाता है। यह बदले में हटाए गए "/" की संख्या को दर्शाता है।

👉 SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/",""))) सेल B5 में पिछले फ़ंक्शन के परिणाम से उदाहरण संख्या के साथ स्टार चिह्न (*) के साथ सभी स्लैश (/) को प्रतिस्थापित करता है।

👉 FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))) अब तक स्ट्रिंग में "*" की स्थिति ढूंढता है।

👉 LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))) मूल स्ट्रिंग लंबाई और पिछली स्थिति के बीच का अंतर है।

👉 अंत में, RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/",""))))) सेल के दाईं ओर से उतने वर्ण निकालता है।

13. सूची में टेक्स्ट मिलान करें

आइए निम्न आकृति पर एक नज़र डालें। हम अपने उदाहरण के बाईं ओर से उन व्यक्तियों का पता लगाने जा रहे हैं जिन्होंने इस्तीफा दे दिया है। दाईं ओर इस्तीफा देने वाले नंबरों की एक सूची है।

ऊपर वाला एक सरल उदाहरण दिखाता है। डेटा रेंज B5:D133 में है। लक्ष्य डेटा ज़ोन में उन पंक्तियों की पहचान करना है जो कॉलम G में इस्तीफा देने वाले सदस्यों की सूची में दिखाई दे रहे हैं। आप इन अनावश्यक पंक्तियों को बाद में हटा सकते हैं। हम इसके लिए IF और COUNTIF फ़ंक्शन के संयोजन का उपयोग करने जा रहे हैं।

हमारे डेटासेट में इस्तीफा स्थिति प्राप्त करने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, सेल E5 का चयन करें।
• फिर निम्न सूत्र लिखें।

=IF(COUNTIF($G$5:$G$25,C5),"Resigned","")

• उसके बाद, एंटर दबाएं।

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

यह है कि हम एक्सेल में डेटा विश्लेषण तकनीकों के हिस्से के रूप में टेक्स्ट का मिलान कैसे कर सकते हैं।

14. लंबवत डेटा को क्षैतिज में बदलें

किसी डेटासेट में लंबवत को क्षैतिज और इसके विपरीत में बदलना डेटा विश्लेषण में एक और आम बात है। तो हम अब एक्सेल में डेटा क्लीनिंग तकनीकों की श्रृंखला में लंबवत डेटा को क्षैतिज में बदलने पर चर्चा करेंगे।

उसके लिए, हम निम्नलिखित डेटासेट का उपयोग करेंगे।

पेस्ट स्पेशल विकल्प का उपयोग करना

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

• सबसे पहले, उस रेंज का चयन करें जिसे आप क्षैतिज पंक्तियों में बदलना चाहते हैं। यहां, हमने रेंज B4:C10 चुनी है।
• दूसरे, Ctrl+C दबाकर रेंज कॉपी करें।
• तीसरे, उस सेल का चयन करें जहां आप रेंज को क्षैतिज रूप से पेस्ट करना चाहते हैं। हमारे मामले में, हमने सेल B12 चुना है।

यह लंबवत कॉलम को एक्सेल स्प्रेडशीट में क्षैतिज पंक्तियों में बदल देगा।

TRANSPOSE फ़ंक्शन का उपयोग करना:

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

अधिक जानने के लिए नीचे दिए गए चरणों का पालन करें।

चरण:

• सबसे पहले, उस सेल का चयन करें जहां आप सूत्र रखना चाहते हैं।
• फिर इसमें निम्न सूत्र लिखें।

=TRANSPOSE(B4:C10)

• अब एंटर दबाएं।

यह है कि हम TRANSPOSE फ़ंक्शन का उपयोग करके लंबवत डेटा को क्षैतिज में कैसे बदल सकते हैं।

15. खाली सेल्स भरें

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

कभी-कभी हम कुछ खाली सेल्स रखते हैं ताकि यह समझा जा सके कि वे पहले भरे जाएंगे, इसे समझने के लिए हमारे डेटासेट पर एक नज़र डालें। लेकिन कभी-कभी यह समस्याएं पैदा करता है जैसे जब हम डेटा फ़िल्टर या सॉर्ट कर रहे होते हैं। इसलिए उन्हें भरना बेहतर है और इसे करने के आसान तरीके हैं।

यह हमारा डेटासेट है।

हम इन दो सरल विधियों का उपयोग करके उन्हें भर सकते हैं।

फिल कमांड का उपयोग करना

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

• सेल B6 चुनें।
• फिर होम पर जाएं
• संपादन समूह से, फिल पर क्लिक करें और फिर डाउन।

• डेटासेट अब इस तरह दिखेगा।

• अब सभी खाली सेल्स को भरने के लिए प्रक्रिया को दोहराएं और सभी सेल्स भरें।

यह डेटासेट में खाली सेल्स भरने का एक तरीका है।

कीबोर्ड शॉर्टकट का उपयोग करना:

हम कीबोर्ड शॉर्टकट का उपयोग करके उसी परिणाम को प्राप्त कर सकते हैं। शॉर्टकट Ctrl+D है। आपको बस खाली सेल का चयन करना है और शॉर्टकट दबाना है। यह सेल को उसी कॉलम में पिछले सेल के मान से भर देगा। अधिक विस्तृत गाइड के लिए इन चरणों का पालन करें।

• सबसे पहले, सेल B6 चुनें।
• फिर अपने कीबोर्ड पर Ctrl+D दबाएं।

• अब सेल B9 का चयन करके Ctrl+D दबाने से निम्न परिणाम प्राप्त होगा।

• अब सभी खाली मानों को चुनकर और शॉर्टकट दबाकर भरें।

16. वर्तनी जांचें

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

यहां हमने प्रदर्शन के लिए एक डेटासेट बनाया है।

हम रेंज C5:C7 को सही करने जा रहे हैं। यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।

चरण:

• सबसे पहले, रेंज C5:C7 चुनें।
• फिर समीक्षा टैब पर जाएं और प्रूफिंग से वर्तनी चुनें। आप इस चरण को छोड़ सकते हैं और अपने कीबोर्ड पर F7 दबा सकते हैं।

• अब सही सुझाव चुनें और किसी विशेष सेल के मान को बदलने के लिए बदलें पर क्लिक करें। त्रुटियाँ यहां चयन क्रम में आती हैं।

• यह डेटासेट में पहली त्रुटि को बदल देगा।

• अब सभी त्रुटियों के लिए इस प्रक्रिया को दोहराएं और इस तरह आपके पास सही वर्तनी होगी।

17. टेक्स्ट बदलें या हटाएं

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

सभी विधियों के लिए, हम निम्नलिखित डेटासेट का उपयोग कर रहे हैं।

फाइंड एंड रिप्लेस सुविधा का उपयोग करना:

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

• सबसे पहले, उस रेंज का चयन करें जिसे आप संशोधित करना चाहते हैं। इस मामले में, यह C5:C13 है।
• फिर होम पर जाएं
• उसके बाद, संपादन समूह अनुभाग से फाइंड एंड रिप्लेस चुनें।
• अगला, ड्रॉप-डाउन से रिप्लेस चुनें।

• अब, पॉप-अप बॉक्स में रिप्लेस टैब पर जाएं।
• फिर ढूंढें क्या फ़ील्ड में वह मान डालें जिसे आप बदलना चाहते हैं और बदलें साथ में वह मान जिसे आप उससे बदलना चाहते हैं

• अंत में, डेटासेट में उन सभी को बदलने के लिए सभी बदलें पर क्लिक करें।

यह डेटासेट से सभी हाइफ़न को हटाने के बाद अंतिम उत्पाद होगा।

फ्लैश फिल का उपयोग करना:

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

• सबसे पहले, पहले सेल में वांछित मान को हटाने या बदलने के बाद अंतिम उत्पाद लिखें। हम स्ट्रिंग में दूसरे हाइफ़न को कोलन से बदल रहे हैं।

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

नोट

यदि सुझाव दूसरी एंट्री में नहीं होता है, तो इसे मैन्युअली आज़माएं। यह कभी-कभी तीसरी या चौथी एंट्री पर आउटपुट का सुझाव देता है यदि पैटर्न एक्सेल के लिए इतना स्पष्ट नहीं है।

• अब एंटर दबाएं।

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

SUBSTITUTE फ़ंक्शन का उपयोग करना:

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

• सबसे पहले, सेल C5 का चयन करें।
• फिर इसमें निम्न सूत्र लिखें।

=SUBSTITUTE(B5,"-",":",2)

• उसके बाद, एंटर दबाएं।

• अंत में, सेल को फिर से चुनें और फिल हैंडल आइकन को क्लिक करके कॉलम के अंत तक खींचें ताकि बाकी सेल्स के लिए सूत्र दोहराया जाए।

18. सेल्स में टेक्स्ट जोड़ें

टेक्स्ट जोड़ना न केवल डेटा क्लीनिंग तकनीकों में से एक के रूप में आम है बल्कि सामान्य एक्सेल ऑपरेशन में भी है। हम विभिन्न तरीकों से सेल्स में टेक्स्ट जोड़ सकते हैं। उन प्रदर्शनों के लिए, हम निम्नलिखित डेटासेट का उपयोग करेंगे।

अब आप सेल्स में टेक्स्ट जोड़ने के लिए निम्नलिखित विधियों में से एक का उपयोग कर सकते हैं।

एम्परसैंड (&) सिंबल का उपयोग करना:

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

• सबसे पहले, सेल C5 का चयन करें।
• दूसरे, नीचे सूत्र डालें।

=B5&".mp3"

• अगला, एंटर दबाएं।

• अंत में, फिल हैंडल आइकन को क्लिक करके कॉलम के अंत तक खींचें ताकि बाकी सेल्स के लिए सूत्र दोहराया जाए।

फ्लैश फिल सुविधा लागू करना:

एक्सेल में फ्लैश फिल सुविधा का उपयोग करके आसानी से टेक्स्ट जोड़ने के लिए इन चरणों का पालन करें।

• सबसे पहले, सेल C5 में पहला इच्छित मान मैन्युअली लिखें।

• फिर डेटासेट में दूसरा भरना शुरू करें। एक बार जब आप भरना शुरू करते हैं तो एक्सेल पैटर्न का सुझाव देगा।

• जैसे ही सुझाव दिखाई दे, एंटर दबाएं।

यह एक्सेल में टेक्स्ट जोड़ने का एक और तरीका है।

CONCATENATE फ़ंक्शन का उपयोग करना:

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

• सबसे पहले, सेल C5 का चयन करें।
• फिर इसमें निम्न सूत्र लिखें।

=CONCATENATE(B5,".mp3")

• उसके बाद, एंटर दबाएं।

• अंत में, सेल को फिर से चुनें और फिल हैंडल आइकन को क्लिक करके कॉलम के अंत तक खींचें ताकि बाकी सेल्स के लिए सूत्र दोहराया जाए।

19. अनुगामी माइनस साइन ठीक करें

कभी-कभी अन्य स्रोतों से डेटा लेते समय, नकारात्मक मानों में एक अनुगामी माइनस साइन होता है। निम्न आकृति पर एक नज़र डालें।

एक्सेल अनुगामी नकारात्मक संकेतों वाले मानों को संख्याओं के रूप में नहीं मानता है। इसके बजाय, यह उन्हें टेक्स्ट के रूप में मानता है। यह न केवल डेटा क्लीनिंग तकनीकों में बल्कि सामान्य ऑपरेशन जैसे एक्सेल में सूत्रों का उपयोग करने में भी समस्याएं पैदा कर सकता है। इसलिए हमें इस समस्या का समाधान करना होगा और उन्हें नकारात्मक संख्याओं में परिवर्तित करना होगा।

यह करने के लिए इन सरल चरणों का पालन करें।

चरण:

• सबसे पहले, उस रेंज का चयन करें जिसे आप परिवर्तित करना चाहते हैं। हमने इसकी तुलना पिछले वाले से करने के लिए इसे कॉपी किया है।

• फिर अपने रिबन पर डेटा टैब पर जाएं।
• फिर डेटा टूल्स समूह अनुभाग से टेक्स्ट टू कॉलम्स चुनें।

• अंत में, विज़ार्ड में सीधे समाप्त पर क्लिक करें।

यह स्वचालित रूप से अनुगामी माइनस संकेतों वाले सभी नकारात्मक मानों को एक्सेल के नकारात्मक साइन प्रारूप में बदल देगा।

यह प्रक्रिया काम करती है क्योंकि उन्नत टेक्स्ट आयात सेटिंग्स डायलॉग बॉक्स में एक डिफ़ॉल्ट सेटिंग है।

निष्कर्ष

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

इस तरह के और गाइड के लिए, Exceldemy.com पर जाएं।