Excel में डेटा वैलिडेशन क्या है? Excel में डेटा वैलिडेशन नियम जोड़ने, संपादित करने और हटाने की व्यापक मार्गदर्शिका

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

यदि आप शुरू करने से पहले Excel की बुनियादी बातें—मुख्य सूत्र, ऑपरेटर, गणितीय फ़ंक्शन, आदि—दोहराना चाहें, तो हमारा Excel Formulas चीट शीट देखें।

Excel में डेटा वैलिडेशन क्या है?

Excel में डेटा वैलिडेशन एक विशेष फीचर है जो नियंत्रित करता है कि किसी सेल में कौन-से मान दर्ज किए जा सकते हैं। यह कई तकनीकों की पेशकश करता है जिनका उपयोग मौजूदा मानों की जांच करने या नए मानों पर लागू होने वाले नियम बनाने में किया जा सकता है।

Excel में Data Validation फीचर खोजने के लिए, ये चरण अपनाएं:

1. Data टैब खोलें।
2. Data Tools समूह पर जाएं।
3. Data Validation बटन पर क्लिक करें।

Data Validation फीचर खोलना

इसके बाद निम्न पॉप-अप विंडो स्क्रीन पर दिखाई देगी।

Data Validation पॉप-अप विंडो

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

डेटा वैलिडेशन क्यों महत्वपूर्ण है?

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

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

डेटा गुणवत्ता

डेटा वैलिडेशन गलत या असंगत मानों को डेटाबेस में स्टोर होने से रोकता है। इससे आगे चलकर त्रुटियां कम होती हैं, समग्र डेटा गुणवत्ता बढ़ती है, और डेटा विश्लेषण की सार्थकता में सुधार होता है।

उच्च-गुणवत्ता वाले डेटा के महत्व को GIGO अवधारणा बखूबी दर्शाती है, जो कंप्यूटर विज्ञान में लोकप्रिय है और जिसका अर्थ है garbage-in और garbage-out। मूल रूप से, त्रुटिपूर्ण डेटा का विश्लेषण गलत और भ्रामक परिणामों की ओर ले जाता है।

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

• विश्वविद्यालय रिकॉर्ड: किसी विश्वविद्यालय में छात्रों की संख्या दशमलव में नहीं हो सकती; केवल पूर्णांक मान्य हैं।
• चिकित्सा रिकॉर्ड: किसी व्यक्ति का अधिकतम शरीर तापमान 50°C से अधिक नहीं हो सकता।
• आयु प्रविष्टियाँ: उम्र का फ़ील्ड केवल वास्तविक मान स्वीकार करे, आमतौर पर 0 से 120 के बीच।
• ईमेल पते: ईमेल फ़ील्ड को एक विशिष्ट प्रारूप का पालन करना चाहिए, जिसमें '@' चिन्ह शामिल हो। 
• इन्वेंटरी काउंट: इन्वेंटरी सिस्टम में उत्पाद की मात्रा नकारात्मक नहीं होनी चाहिए।

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

डेटा अखंडता और अनुपालन

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

स्वास्थ्य क्षेत्र में, रोगी जानकारी की सुरक्षा के लिए HIPAA अनुपालन अनिवार्य है। इसी प्रकार, EU में कार्यरत व्यवसायों को GDPR का पालन करना होता है, जिससे व्यक्तियों के डेटा की गोपनीयता और सुरक्षा सुनिश्चित होती है। 

डेटा सफाई और प्रीप्रोसेसिंग

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

निर्णय-निर्माण

मान्यीकृत डेटा का विश्लेषण विश्वसनीय और सूझबूझ भरे निष्कर्षों की ओर ले जाता है, जो बदले में अधिक सूचित, डेटा-संचालित निर्णय लेने में मदद करता है। दीर्घकाल में, डेटा वैलिडेशन कंपनी का समय और धन बचाता है और एक स्वस्थ, अधिक किफायती और लाभदायक व्यवसाय का परिणाम देता है।

Excel में विभिन्न डेटा वैलिडेशन तकनीकें

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

डेटा प्रकार और सीमा/लंबाई को सीमित करना

Excel में, आप निम्नलिखित Excel डेटा प्रकार चुन सकते हैं: Whole number, Decimal, Date, Time, और Text।

अब एक तालिका बनाते हैं ताकि अलग-अलग डेटा प्रकारों पर कुछ तार्किक शर्तों की प्रयोज्यता पर विचार किया जा सके। तार्किक शर्तें हैं: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, और less than or equal to।

• Between और Not Between: ये शर्तें आम तौर पर संख्यात्मक मानों, तिथियों और समय के साथ प्रयोग होती हैं। text पर लागू होने पर, between और not between का तात्पर्य सेल में वर्णों की संख्या यानी लंबाई से होता है।

• Equal to और Not Equal to: ये सभी डेटा प्रकारों पर लागू हो सकती हैं। text के लिए, यह सटीक मिलान की जांच करता है।

• Greater Than, Less Than, Greater Than or Equal To, और Less Than or Equal To: ये शर्तें whole number, date, और time डेटा प्रकारों के लिए उपयुक्त हैं, लेकिन text पर सीधे लागू नहीं होतीं जब तक कि आप टेक्स्ट की लंबाई या कुछ विशिष्ट मामलों में वर्णानुक्रम पर विचार न करें।

ड्रॉप-डाउन सूची बनाना

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

1. वे आइटम दर्ज करें जिन्हें आप अपनी ड्रॉप-डाउन सूची में दिखाना चाहते हैं। आप पूरी सूची चुनकर, राइट-क्लिक करके, Sort चुनकर और कोई विधि चुनकर सूची को सॉर्ट भी कर सकते हैं।

ड्रॉप-डाउन सूची के लिए आइटम दर्ज करना

2. अपनी सूची को Excel टेबल में बदलें। इसकी रेंज में कोई भी सेल चुनें और Ctrl+T दबाएं। यह कदम महत्वपूर्ण है क्योंकि यदि आप सूची में आइटम जोड़ते या हटाते हैं, तो उन पर निर्भर किसी भी ड्रॉप-डाउन सूची को स्वचालित रूप से अपडेट कर दिया जाएगा।

ड्रॉप-डाउन की सूची को टेबल में बदलना

3. वर्कशीट में वह Excel सेल या सेल्स चुनें जहां आप ड्रॉप-डाउन सूची जोड़ना चाहते हैं।
4. पॉप-अप Data Validation विंडो खोलें। Data टैब खोलें। Data Tools समूह पर जाएं और Data Validation बटन पर क्लिक करें।
5. Settings टैब पर, Allow फ़ील्ड में List चुनें। In-cell dropdown बॉक्स को चयनित रहने दें। यदि वे Excel सेल या सेल्स खाली रह सकते हैं तो Ignore blank बॉक्स चयनित रहने दें; अन्यथा, उसका चयन हटाएं:

डेटा वैलिडेशन विधि के रूप में ड्रॉप-डाउन सूची सेट करना

6. Source बॉक्स में कर्सर रखें, अपनी सूची की रेंज चुनें और OK दबाएं। चयन विकल्प के रूप में हेडर पंक्ति से बचने के लिए उसे शामिल न करें।

ड्रॉप-डाउन सूची के लिए स्रोत चुनना

7. वे Excel सेल या सेल्स चुनें जिनके लिए आपने यह डेटा वैलिडेशन बनाया था। अब आपको उस सेल के दाईं ओर एक ड्रॉप-डाउन त्रिकोण दिखाई देगा। त्रिकोण पर क्लिक करने के बाद, आप उस सेल के लिए आवश्यक मान चुन सकते हैं।

किसी सेल में ड्रॉप-डाउन सूची खोलना

कस्टम फ़ॉर्मूले लागू करना

• range: यहाँ हम उन Excel सेल्स की रेंज का उल्लेख कर रहे हैं जिनके लिए हम कस्टम फ़ॉर्मूला-आधारित डेटा वैलिडेशन लागू करना चाहते हैं।
• uppermost_cell: यहाँ हम अपनी रेंज के पहले, या सबसे ऊपर वाले, सेल का उल्लेख कर रहे हैं।

केवल संख्याएँ या केवल टेक्स्ट

निम्न दो फ़ॉर्मूलों का उद्देश्य यह सत्यापित करना है कि किसी विशिष्ट सेल की सामग्री या तो संख्या है या टेक्स्ट। यह तब उपयोगी है जब आपको क्रमशः संख्यात्मक और गैर-संख्यात्मक मानों में, या टेक्स्ट और गैर-टेक्स्ट मानों में अंतर करना हो।

=ISNUMBER(uppermost_cell)संचालित द्वारा

=ISTEXT(uppermost_cell)संचालित द्वारा

केवल अपरकेस या लोअरकेस टेक्स्ट

=AND(EXACT(uppermost_cell, UPPER(uppermost_cell)), ISTEXT(uppermost_cell))संचालित द्वारा

=AND(EXACT(uppermost_cell, LOWER(uppermost_cell)), ISTEXT(uppermost_cell))संचालित द्वारा

डुप्लिकेट नहीं

=COUNTIF(range, uppermost_cell)<=1संचालित द्वारा

केवल किसी विशिष्ट सबस्ट्रिंग से शुरू होने वाले मान

निम्न फ़ॉर्मूला जाँचता है कि किसी स्ट्रिंग के पहले n अक्षर किसी दिए गए सबस्ट्रिंग से बिल्कुल मेल खाते हैं या नहीं। यह TRUE या FALSE लौटाएगा। 

=EXACT(LEFT(uppermost_cell, n), substring)संचालित द्वारा

केवल कार्यदिवस या केवल सप्ताहांत

=WEEKDAY(uppermost_cell, 2)<=5संचालित द्वारा

=WEEKDAY(uppermost_cell, 2)>5संचालित द्वारा

केवल पिछली तिथियाँ

=uppermost_cell<TODAY()संचालित द्वारा

लीडिंग या ट्रेलिंग स्पेस नहीं

=uppermost_cell=TRIM(uppermost_cell)संचालित द्वारा

कोई स्पेस नहीं

=uppermost_cell=SUBSTITUTE(uppermost_cell, " ", "")संचालित द्वारा

कुल से अधिक होने से रोकना

=SUM(range)<=total_cellसंचालित द्वारा

Excel में फ़ॉर्मूलों के साथ अपनी विशेषज्ञता को जल्दी बढ़ाने के लिए, एक व्यापक और अभ्यास-उन्मुख Data Analysis in Excel कोर्स करने पर विचार करें।

Excel में डेटा वैलिडेशन कैसे उपयोग करें

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

डेटा वैलिडेशन नियमों के लिए Excel वर्कबुक को कैसे कॉन्फ़िगर करें

1. वे Excel सेल या सेल्स चुनें जहां आप डेटा वैलिडेशन चाहते हैं।
2. पॉप-अप Data Validation विंडो खोलें। Data टैब खोलें। Data Tools समूह पर जाएं और Data Validation बटन पर क्लिक करें।
3. Settings टैब पर, Allow फ़ील्ड में आवश्यक विधि चुनें, जो डिफ़ॉल्ट रूप से Any value पर सेट होती है (अर्थात कोई डेटा वैलिडेशन नहीं)।

आवश्यक विधि का चयन करना

4. यदि वे Excel सेल या सेल्स खाली रह सकते हैं तो Ignore blank बॉक्स चयनित रहने दें; अन्यथा, उसका चयन हटाएं।

खाली सेल्स को संभालना

5. यदि आपने List विधि चुनी थी, तो अपने डेटा प्रकार से संबंधित कोई पूर्वनिर्धारित नियम चुनें। यदि आपने Custom विधि चुनी थी, तो कोई कस्टम फ़ॉर्मूला दर्ज करें।

डेटा की सीमा/लंबाई सीमित करने के लिए तार्किक शर्त चुनना

डेटा की सीमा/लंबाई सीमित करने के लिए निचली और ऊपरी सीमाएँ दर्ज करना

कस्टम फ़ॉर्मूला लागू करना

6. Input Message टैब पर, आप Title और Input message सेट कर सकते हैं। डिफ़ॉल्ट रूप से, कोई इनपुट संदेश नहीं होता। 

उपयोगकर्ता को सही प्रकार के मान के लिए इनपुट संदेश सेट करना

इनपुट संदेश कैसा दिखता है, यह जांचना

7. Error Alert टैब पर, आप त्रुटि संदेश की Style (Stop, Warning, या Information), Title, और Error message सेट कर सकते हैं। डिफ़ॉल्ट रूप से, त्रुटि संदेश Stop शैली का होता है।

त्रुटि संदेश सेट करना

OK पर क्लिक करने के बाद, आप लागू डेटा वैलिडेशन वाले किसी भी खाली Excel सेल का चयन करके और कोई अमान्य मान दर्ज करके इसे परख सकते हैं। तुलना के लिए, यहाँ प्रत्येक शैली में एक नमूना त्रुटि संदेश दिखाया गया है। 

अमान्य मान दर्ज करने पर Stop शैली का त्रुटि संदेश प्राप्त होना

अमान्य मान दर्ज करने पर Warning शैली का त्रुटि संदेश प्राप्त होना

अमान्य मान दर्ज करने पर Information शैली का त्रुटि संदेश प्राप्त होना

अमान्य मान दर्ज करने पर डिफ़ॉल्ट त्रुटि संदेश

ध्यान दें: Warning या Information शैली के त्रुटि संदेश लोगों को अमान्य डेटा दर्ज करने से नहीं रोकते, लेकिन Stop शैली के त्रुटि संदेश रोकते हैं।

पहले से भरे हुए सेल्स पर Excel में डेटा वैलिडेशन का उपयोग कैसे करें

यदि उन Excel सेल्स में से कुछ पहले से मानों से भरे हों जिन पर आप डेटा वैलिडेशन करना चाहते हैं तो क्या करें? Data Validation फीचर प्रविष्टि-पूर्व और प्रविष्टि-पश्चात—दोनों प्रकार का डेटा वैलिडेशन अनुमति देता है। 

1. वे Excel सेल्स चुनें जहाँ आप डेटा वैलिडेशन करना चाहते हैं। इनमें से कुछ सेल्स में पहले से मान होंगे।

डेटा वैलिडेशन के लिए सेल्स चुनना, जिनमें पहले से मान हैं, उन्हें भी शामिल करते हुए

2. Data Validation पॉप-अप विंडो खोलें। Data टैब खोलें। Data Tools समूह में Data Validation बटन खोलें। 
3. Settings टैब पर, Allow फ़ील्ड में आवश्यक विधि चुनें, जो डिफ़ॉल्ट रूप से Any value पर सेट होती है।

चयनित सेल्स के लिए डेटा वैलिडेशन सेट करना

3. उन Excel सेल्स में से किसी एक को चुनें जिन पर आपने यह डेटा वैलिडेशन लागू किया है, Data टैब खोलें, Data Tools समूह के Data Validation बटन के दाईं ओर ड्रॉप-डाउन तीर पर क्लिक करें, और Circle Invalid Data चुनें।

Excel में अमान्य डेटा को घेरना (सर्कल करना)

सर्कल किए हुए अमान्य डेटा का उदाहरण

4. अब आप प्रत्येक अमान्य मान को संशोधित कर सकते हैं ताकि वह लगाए गए मानदंडों को पूरा करे। ऐसा करने के बाद, उस मान के चारों ओर का सर्कल स्वतः गायब हो जाएगा। यह याद रखना महत्वपूर्ण है कि यदि आप केवल अमान्य मान को हटा देते हैं, तो सर्कल बना रहेगा।
5. वैलिडेशन सर्कल्स हटाने के लिए, उन Excel सेल्स में से किसी एक को चुनें जिन पर आपने यह डेटा वैलिडेशन लागू किया है, Data टैब खोलें, Data Tools समूह में Data Validation बटन खोलें, और फिर Clear Validation Circles चुनें। 

अमान्य डेटा से वैलिडेशन सर्कल्स हटाना

Excel में डेटा वैलिडेशन कैसे संपादित करें

1. ऐसा कोई भी Excel सेल चुनें जिस पर पहले से डेटा वैलिडेशन लागू हो।
2. पॉप-अप Data Validation विंडो खोलें। Data टैब खोलें। Data Tools समूह पर जाएं और Data Validation बटन पर क्लिक करें।
3. Data Validation विंडो के किसी भी टैब पर आवश्यक संशोधन करें। आवश्यकता होने पर, "Excel में डेटा वैलिडेशन कैसे उपयोग करें" अनुभाग के निर्देशों को दोहराएं।
4. Settings टैब पर जाएं और Apply these changes to all other cells with the same settings को चुनें।
5. OK दबाएं।

उसी सेटिंग वाले अन्य सभी सेल्स पर डेटा वैलिडेशन संशोधन लागू करना

Excel में डेटा वैलिडेशन कैसे हटाएं

1. ऐसा कोई भी Excel सेल चुनें जिस पर पहले से डेटा वैलिडेशन लागू हो।
2. पॉप-अप Data Validation विंडो खोलें। Data टैब खोलें। Data Tools समूह पर जाएं और Data Validation बटन पर क्लिक करें।
3. Settings टैब पर जाएं और Apply these changes to all other cells with the same settings को चुनें।
4. Clear All बटन पर क्लिक करें।
5. OK दबाएं।

डेटा वैलिडेशन हटाना

निष्कर्ष

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

यदि आप Excel की पूरी शक्ति को अनलॉक करना चाहते हैं और व्यावहारिक अभ्यासों के माध्यम से डेटा को तैयार करना, विज़ुअलाइज़ करना और विश्लेषण करना सीखना चाहते हैं, साथ ही कुछ उन्नत फीचर्स की खोज करना चाहते हैं, तो व्यापक और शुरुआती-अनुकूल स्किल ट्रैक Excel Fundamentals आज़माएँ। साथ ही, Data Preparation in Excel या Financial Modeling in Excel जैसे विशेष कोर्स पर भी विचार करें, जो आपकी विशेषज्ञता को गहराई देने के लिए केंद्रित प्रशिक्षण प्रदान करते हैं। ये संसाधन आपको बुनियादी डेटा वैलिडेशन से लेकर उन्नत Excel फीचर्स में महारत हासिल करने तक मदद करेंगे।