Excel में डेटा कैसे साफ करें: एक शुरुआती मार्गदर्शिका

Excel में बेसिक डेटा क्लीनिंग

अतिरिक्त स्पेस हटाएँ

ट्रेलिंग व्हाइट स्पेस परेशान कर सकते हैं या समस्याएँ पैदा कर सकते हैं क्योंकि वे डेटा विश्लेषण में असंगतियाँ और फ़ॉर्मेटिंग समस्याएँ ला सकते हैं।

Excel में अतिरिक्त स्पेस हटाने के दो प्रमुख तरीके हैं। आइए दोनों की समीक्षा करें।

Find and Replace का उपयोग करके अतिरिक्त स्पेस हटाएँ

अतिरिक्त स्पेस हटाने के चरण इस प्रकार हैं।

• उन सेल्स की रेंज चुनें जहाँ आप अतिरिक्त स्पेस हटाना चाहते हैं।

• Ctrl + H शॉर्टकट का उपयोग करके Find and Replace डायलॉग बॉक्स खोलें।

• Find what फ़ील्ड में, स्पेसबार दो बार दबाएँ ताकि दो स्पेस भरें।

• Replace with फ़ील्ड में, स्पेसबार एक बार दबाएँ ताकि एक स्पेस भरें।

• Replace All पर क्लिक करें।

• जब तक डबल स्पेस न मिलें, तब तक इस प्रक्रिया को दोहराएँ।

Find and replace डायलॉग बॉक्स। स्रोत: लेखक द्वारा छवि

TRIM का उपयोग करके अतिरिक्त स्पेस हटाएँ

• क्लीन किए गए डेटा के लिए एक नया कॉलम बनाएँ।

• नए कॉलम के पहले सेल में सूत्र दर्ज करें: =TRIM(cell_with_extra_spaces)

• बाकी पंक्तियों पर सूत्र लागू करने के लिए सेल के निचले दाएँ कोने पर डबल-क्लिक करें।

• क्लीन किया गया डेटा कॉपी करें और फ़ॉर्मूलों को हटाने के लिए उसे वैल्यूज़ के रूप में पेस्ट करें।

सभी खाली सेल्स को चुनें और संभालें

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

• उस रेंज को हाइलाइट करें जहाँ आप खाली सेल्स को ढूँढकर संभालना चाहते हैं।

• Ctrl + G दबाकर Go To डायलॉग बॉक्स खोलें।

• “Special…” बटन पर क्लिक करें। इससे Go To Special डायलॉग बॉक्स खुलेगा।

• Blanks विकल्प चुनें और OK पर क्लिक करें। अब चयनित रेंज में सभी खाली सेल्स चुने जाएँगे।

• अब आप कोई मान या सूत्र दर्ज कर सकते हैं। Ctrl + Enter दबाएँ।

खाली सेल्स चुनें और उनका उपचार करें। स्रोत: लेखक द्वारा छवि

स्पेल चेकिंग

गलत वर्तनी वाले शब्द डेटा को गैर-पेशेवर और समझने में कठिन बना सकते हैं, इसलिए उन्हें ठीक करना महत्वपूर्ण है।

• पूरे वर्कशीट या किसी विशिष्ट रेंज की जाँच करें।

• Ribbon पर Review टैब पर जाएँ।

• Proofing समूह में Spelling बटन पर क्लिक करें।

• Spelling डायलॉग बॉक्स खुलेगा, जिसमें पहली पाई गई गलत वर्तनी और सुधार के सुझाव दिखेंगे।

• आवश्यकतानुसार गलत वर्तनी वाले शब्दों की समीक्षा करें और उन्हें सुधारें।

स्पेल चेकिंग। स्रोत: लेखक द्वारा छवि

Excel में त्रुटि प्रबंधन और मान्यकरण

आइए त्रुटि प्रबंधन पर नज़र डालें, जिसमें त्रुटियों को हाइलाइट करना या डुप्लीकेट हटाना जैसी सामान्य समस्याओं का समाधान शामिल है।

त्रुटियाँ हाइलाइट करें

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

• उन सेल्स की रेंज चुनें जिनमें आप त्रुटियाँ जाँचना चाहते हैं।

• Ribbon पर Home टैब पर जाएँ।

• Styles समूह में, Conditional Formatting पर क्लिक करें।

• ड्रॉपडाउन मेनू से New Rule चुनें।

• चुनें: Use a formula to determine which cells to format.

• Format values where this formula is true फ़ील्ड में सूत्र दर्ज करें: =ISERROR(cell)

• अपनी इच्छित फ़ॉर्मेटिंग विकल्प चुनने के लिए Format… बटन पर क्लिक करें।

• फ़ॉर्मेटिंग विकल्प चुनने के बाद OK पर क्लिक करें।

• नियम लागू करने के लिए New Formatting Rule डायलॉग बॉक्स में फिर से OK पर क्लिक करें।

कंडीशनल फ़ॉर्मेटिंग का उपयोग करना। स्रोत: लेखक द्वारा छवि

डुप्लीकेट हटाएँ

डुप्लीकेट प्रविष्टियाँ विश्लेषण और अंतर्दृष्टियों को विकृत कर सकती हैं, इसलिए उन्हें हटाने से डेटा की शुद्धता सुनिश्चित होती है।

• उस वर्कशीट या रेंज का चयन करें जहाँ से आप डुप्लीकेट हटाना चाहते हैं।

• Ribbon पर Data टैब पर जाएँ।

• Data Tools समूह में Remove Duplicates पर क्लिक करें।

• Remove Duplicates डायलॉग बॉक्स में, वह कॉलम निर्दिष्ट करें जिसमें डुप्लीकेट जाँचना है।

• OK पर क्लिक करें। Excel हटाई गई डुप्लीकेट वैल्यूज़ की संख्या दिखाता हुआ संदेश प्रदर्शित करेगा।

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

डुप्लीकेट पंक्तियाँ आपके डेटा को अव्यवस्थित कर सकती हैं और आपके विश्लेषण की विश्वसनीयता को प्रभावित कर सकती हैं। इसे प्रबंधित करने का तरीका यहाँ है।

• उस रेंज का चयन करें जहाँ से आप डुप्लीकेट पंक्तियाँ हटाना चाहते हैं।

• Ribbon पर Data टैब में जाएँ और Sort & Filter समूह में Advanced पर क्लिक करें।

• Advanced Filter डायलॉग बॉक्स में, Copy to another location चुनें।

• List रेंज को अपनी चयनित रेंज पर सेट करें।

• Copy to फ़ील्ड में वह सेल सेट करें जहाँ आप अद्वितीय पंक्तियों को कॉपी करना चाहते हैं।

• Unique Records Only बॉक्स पर टिक करें और फिर OK पर क्लिक करें।

Excel में टेक्स्ट ऑपरेशंस

आइए टेक्स्ट ऑपरेशंस जैसे स्ट्रिंग्स को जोड़ना (concatenate) देखें।

Concatenate

कई टेक्स्ट स्ट्रिंग्स को एक सेल में मिलाना अलग-अलग स्रोतों की जानकारी को एक सुसंगत फ़ॉर्मेट में जोड़कर अधिक अर्थपूर्ण और व्यवस्थित डेटा बनाने में मदद करता है।

• उस सेल का चयन करें जहाँ आप संयोजित परिणाम देखना चाहते हैं।
• =CONCATENATE() टाइप करें।
• कॉमा या & ऑपरेटर से अलग करते हुए वे सेल चुनें जिन्हें आप जोड़ना चाहते हैं।
• परिणाम देखने के लिए Enter दबाएँ।

सेल्स को जोड़ना। स्रोत: लेखक द्वारा छवि

टेक्स्ट का केस बदलना

एकसमान टेक्स्ट केसिंग पठनीयता में सुधार करती है और आपके डेटा में स्थिरता बनाए रखती है। आइए देखें कि इसे कैसे समायोजित करें।

• परिवर्तित टेक्स्ट के लिए एक नया कॉलम बनाएँ।

• किसी सेल में सूत्र दर्ज करें: =UPPER() या =LOWER() या =PROPER()

• Enter दबाकर सूत्र लागू करें।

• आवश्यक होने पर अन्य सेल्स पर सूत्र लागू करने के लिए फिल हैंडल खींचें।

टेक्स्ट का केस बदलना। स्रोत: लेखक द्वारा छवि

टेक्स्ट से नॉन-प्रिंटिंग कैरेक्टर्स हटाना

टैब, लाइन ब्रेक, और विशेष कैरेक्टर्स जैसे नॉन-प्रिंटिंग कैरेक्टर्स डेटा प्रोसेसिंग को बाधित करके समस्याएँ पैदा कर सकते हैं, जिससे डेटा का सही ढंग से विश्लेषण या विज़ुअलाइज़ करना मुश्किल हो जाता है।

• उन नॉन-प्रिंटिंग कैरेक्टर्स की पहचान करें जिन्हें आपको हटाना है।

• उस सेल का चयन करें जिसमें नॉन-प्रिंटिंग कैरेक्टर्स सहित टेक्स्ट है।

• किसी नए सेल में यह फ़ंक्शन उपयोग करें: =CLEAN(text).

• अतिरिक्त स्पेस और नॉन-प्रिंटिंग कैरेक्टर्स हटाने के लिए उपयोग करें: =TRIM(CLEAN(text)).

• मूल टेक्स्ट के स्थान पर क्लीन किया हुआ टेक्स्ट कॉपी-पेस्ट करें।

Excel में CLEAN फ़ंक्शन का उपयोग। स्रोत: लेखक द्वारा छवि

Excel में डेटा ट्रांसफ़ॉर्मेशन

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

टेक्स्ट से कॉलम में डेटा पार्सिंग

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

• उस डेटा का चयन करें जिसे आप विभाजित करना चाहते हैं।

• Data टैब पर जाएँ और Text to Columns पर क्लिक करें।

• Delimited या Fixed Width चुनें और Next पर क्लिक करें।

• Delimited के लिए, अपने डेटा द्वारा उपयोग किए जाने वाले डिलिमिटर चुनें और Next पर क्लिक करें।

• Fixed Width के लिए, Data Preview विंडो में कॉलम ब्रेक सेट करें और Next पर क्लिक करें।

• प्रत्येक कॉलम के लिए डेटा फ़ॉर्मेट चुनें।

• Finish पर क्लिक करें।

डेटा पार्सिंग के लिए Text to Columns का उपयोग। स्रोत: लेखक द्वारा छवि

Flash Fill

Flash Fill आपके डेटा में पहचाने गए पैटर्न के आधार पर अपने आप मान भर देता है। Excel में Flash Fill ऐसे उपयोग करें:

• मौजूदा डेटा के बगल वाले सेल में किसी पैटर्न का पालन करते हुए डेटा दर्ज करें।

• Excel को पैटर्न पहचानने में मदद के लिए अगले सेल में एक और उदाहरण दें।

• उदाहरण वाले सेल का चयन करें।

• Ribbon पर Data टैब पर जाएँ।

• Data Tools समूह में Flash Fill पर क्लिक करें।

• Excel पहचाने गए पैटर्न के आधार पर शेष सेल्स को अपने आप भर देगा।

कॉलम्स को मर्ज और स्प्लिट करना

कॉलम्स का मर्ज और स्प्लिट करना डेटा को आपकी विश्लेषण आवश्यकताओं के अनुसार व्यवस्थित करने में मदद करता है। सेल्स को मर्ज करने के लिए ये चरण अपनाएँ:

• Home टैब पर जाएँ, और Alignment समूह में Merge & Center ड्रॉप-डाउन मेनू पर क्लिक करें।
• अपना पसंदीदा मर्ज विकल्प चुनें।

कॉलम्स को स्प्लिट करने के लिए ये चरण अपनाएँ:

• Data टैब पर जाएँ और Data Tools समूह में Text to Columns पर क्लिक करें।
• अपने डेटा की ज़रूरत के अनुसार delimited या fixed width चुनें।
• स्प्लिट डेटा के गंतव्य का चयन करें और Finish पर क्लिक करें।

कॉलम्स और रोज़ को ट्रांसफ़ॉर्म और पुनर्व्यवस्थित करना

डेटा को पुनर्व्यवस्थित करना उसे अधिक तार्किक और सुलभ फ़ॉर्मेट में प्रस्तुत करने में मदद करता है। रो को रो और कॉलम को कॉलम में ट्रांसफ़ॉर्म करने के लिए:

• उस डेटा को हाइलाइट करें जिसे आप ट्रांसफ़ॉर्म करना चाहते हैं (आवश्यक हो तो हैडर सहित)।

• चयन पर राइट-क्लिक करें और Copy चुनें या Ctrl+C दबाएँ।

• वह सेल चुनें जहाँ से ट्रांसफ़ॉर्म किया गया डेटा शुरू होगा।

• गंतव्य सेल पर राइट-क्लिक करें, Paste Special चुनें और फिर Transpose चुनें।

• आप Ctrl+Alt+V का उपयोग करके Paste Special डायलॉग बॉक्स भी खोल सकते हैं, फिर Transpose विकल्प पर टिक करें और OK पर क्लिक करें।

कॉलम्स/रोज़ को पुनर्व्यवस्थित करने के लिए:

• जिस पूरे कॉलम को आप स्थानांतरित करना चाहते हैं, उसे चुनने के लिए उसके हैडर पर क्लिक करें।

• राइट-क्लिक करें और Cut चुनें या Ctrl+X दबाएँ।

• जिस कॉलम में आप कट किया हुआ कॉलम ले जाना चाहते हैं, उसे चुनें, कॉलम हैडर पर राइट-क्लिक करें, और Insert Cut Cells. चुनें।

• इसी तरह, रो के लिए, जिस रो को आप दूसरी रो में स्थानांतरित करना चाहते हैं, उसे चुनें और कट करें तथा वहाँ पेस्ट करें।

Excel में संख्यात्मक और दिनांक संबंधी सुधार

संख्यात्मक और दिनांक संबंधी सुधारों में संख्यात्मक डेटा और तिथियों को ठीक करना और मानकीकृत करना शामिल है ताकि वे सटीक हों और एकसमान रूप से फ़ॉर्मेटेड हों।

नंबर और संकेत सुधारना

गलत नंबर फ़ॉर्मेट से गलत व्याख्या, गणना त्रुटियाँ, और डेटा सॉर्टिंग व तुलना की समस्याएँ हो सकती हैं।

• उन सेल्स का चयन करें जिनमें वे नंबर हैं जिन्हें आपको ठीक करना है।
• Home टैब पर जाएँ, Number समूह में Number ड्रॉप-डाउन मेनू पर क्लिक करें, और उपयुक्त नंबर फ़ॉर्मेट चुनें (जैसे, General, Number, Currency)।

तिथियाँ और समय ठीक करना

सही ढंग से फ़ॉर्मेट की गई तिथियाँ समय-आधारित विश्लेषण और रिपोर्टिंग के लिए अत्यंत महत्वपूर्ण हैं।

• उन सेल्स को हाइलाइट करें जिनमें तिथियाँ हैं।

• Home टैब पर जाएँ।

• Number Format ड्रॉप-डाउन मेनू पर क्लिक करें और Short Date या Long Date चुनें।

Excel में उन्नत डेटा प्रबंधन

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

जॉइन या मिलान करके टेबल डेटा का सामंजस्य

विभिन्न टेबलों से डेटा को जोड़ना या मिलाना व्यापक और सुसंगत विश्लेषण सुनिश्चित करता है।

डेटा मिलाने के लिए VLOOKUP का उपयोग:

• सुनिश्चित करें कि दोनों टेबल एक ही वर्कशीट पर सुलभ हों।

• वह सेल चुनें जहाँ आप मिलान किया हुआ डेटा दिखाना चाहते हैं।

• यह फ़ंक्शन उपयोग करें: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

• आवश्यकतानुसार अन्य सेल्स में फ़ॉर्मूला कॉपी करने के लिए फिल हैंडल खींचें।

अधिक लचीलापन के लिए INDEX और MATCH का उपयोग

• वह सेल चुनें जहाँ आप मिलान किया हुआ डेटा दिखाना चाहते हैं।

• इन फ़ंक्शनों का संयोजन उपयोग करें: =INDEX(array, MATCH(lookup_value, lookup_array, 0))

• आवश्यकतानुसार अन्य सेल्स में फ़ॉर्मूला कॉपी करने के लिए फिल हैंडल खींचें।

अंतिम विचार

Excel में कई डेटा क्लीनिंग फ़ंक्शंस हैं जो आपको निर्धारित मानकों के अनुरूप डेटा को साफ़ और मान्य करने की अनुमति देते हैं। ये त्रुटियों को कम करने और आपके डेटासेट्स की गुणवत्ता में सुधार करने में मदद कर सकते हैं। Excel की क्षमताओं में निपुणता पाने के लिए, हमारे Introduction to Excel कोर्स में नामांकन करने पर विचार करें।

इसके आगे, चाहे आप डुप्लीकेट फ़ील्ड्स हटाने के लिए डेटा स्क्रब कर रहे हों या डेटा प्रविष्टियों के फ़ॉर्मेट्स को मानकीकृत कर रहे हों, Excel में इस प्रक्रिया को आसान बनाने के लिए सभी टूल मौजूद हैं। यदि आप अपने डेटा प्रिपरेशन कौशल को बेहतर बनाना चाहते हैं, तो हमारा Data Preparation in Excel कोर्स इस विषय पर व्यापक कवरेज प्रदान करता है।

अपनी विश्लेषणात्मक कौशल को और बढ़ाने के लिए, हमारा Data Analysis in Excel कोर्स विशेष रूप से उपयोगी लग सकता है। यह कोर्स डेटा क्लीनिंग के बाद के विश्लेषण की बारीकियों में जाता है। साथ ही, यदि आप जानना चाहते हैं कि स्वच्छ डेटा वित्तीय पूर्वानुमान का कैसे समर्थन करता है, तो हमारा Financial Modeling in Excel कोर्स भी आपकी रुचि का हो सकता है।

यदि आप अपने डेटा क्लीनिंग कौशल का विस्तार करना चाहते हैं, तो Power Query पर विचार करें, जो Excel और Power BI में अंतर्निहित एक लचीला और प्रभावी टूल है, जो आपको डेटा को निर्बाध रूप से आयात और ट्रांसफ़ॉर्म करने की अनुमति देता है। Data Cleaning in Python या Cleaning Data in R को भी एक्सप्लोर करने पर विचार करें। ये कोर्स लोकप्रिय प्रोग्रामिंग भाषाओं का उपयोग करके डेटा क्लीनिंग के लिए मज़बूत तकनीकें और सर्वोत्तम प्रथाएँ प्रदान करते हैं।