कॉमन डेटा क्लीनअप के लिए एक्सेल फॉर्मूला
वर्षों से, मैंने प्रकाशन का उपयोग एक संसाधन के रूप में यह बताने के लिए किया है कि चीजों को कैसे करना है और बाद में देखने के लिए अपने लिए एक रिकॉर्ड रखना है! एक ग्राहक ने हमें एक ग्राहक डेटा फ़ाइल सौंपी जो एक आपदा थी। वस्तुतः प्रत्येक फ़ील्ड ग़लत स्वरूपित थी, और परिणामस्वरूप, हम डेटा आयात नहीं कर सके। जबकि विज़ुअल बेसिक का उपयोग करके एक्सेल को साफ करने के लिए कुछ बेहतरीन ऐड-ऑन हैं, हम मैक के लिए ऑफिस चलाते हैं, जो मैक्रोज़ का समर्थन नहीं करेगा। इसके बजाय, हम सहायता के लिए सीधे फ़ार्मुलों की तलाश करते हैं। मैंने सोचा कि मैं उनमें से कुछ को यहां साझा करूं ताकि आप उनका उपयोग कर सकें।
गैर-संख्यात्मक वर्ण निकालें
सिस्टम को अक्सर देश कोड और बिना किसी विराम चिह्न के एक विशिष्ट 11-अंकीय फॉर्मूले में फ़ोन नंबर डालने की आवश्यकता होती है। हालाँकि, लोग अक्सर इस डेटा को डैश और पीरियड्स के साथ दर्ज करते हैं। इसके लिए यहां एक बेहतरीन फॉर्मूला है सभी गैर-संख्यात्मक वर्णों को हटाना एक्सेल में। सूत्र सेल A2 में डेटा की समीक्षा करता है:
=IF(A2="","",SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))
आप परिणामी कॉलम की प्रतिलिपि बना सकते हैं और उपयोग कर सकते हैं संपादित करें> चिपकाएँ मान ठीक से स्वरूपित परिणाम के साथ डेटा पर लिखने के लिए।
एक के साथ एकाधिक फ़ील्ड का मूल्यांकन करें
हम अक्सर किसी आयात से अधूरे रिकॉर्ड को शुद्ध कर देते हैं। उपयोगकर्ताओं को यह एहसास नहीं है कि आपको हमेशा जटिल पदानुक्रमित सूत्र लिखने की ज़रूरत नहीं है और आप इसके बजाय एक OR कथन लिख सकते हैं। मैं नीचे दिए गए उदाहरण में गुम डेटा के लिए A2, B2, C2, D2, या E2 की जाँच करना चाहता हूँ। यदि कोई डेटा गुम है, तो मैं 0 लौटाऊंगा; अन्यथा, 1. यह मुझे डेटा को क्रमबद्ध करने और अधूरे रिकॉर्ड को हटाने की अनुमति देगा।
=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)
ट्रिम और कॉनसैटनेट फील्ड्स
यदि आपके डेटा में प्रथम और अंतिम नाम फ़ील्ड हैं, लेकिन आपके आयात में पूरा नाम फ़ील्ड है, तो आप अंतर्निहित एक्सेल फ़ंक्शन कॉन्टेनेट का उपयोग करके फ़ील्ड को बड़े करीने से एक साथ जोड़ सकते हैं, लेकिन पहले या बाद में किसी भी खाली स्थान को हटाने के लिए TRIM का उपयोग करना सुनिश्चित करें मूलपाठ। यदि किसी फ़ील्ड में डेटा नहीं है तो हम संपूर्ण फ़ील्ड को TRIM से लपेटते हैं:
=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))
मान्य ईमेल पते की जाँच करें
एक बहुत ही सरल सूत्र जो @ और दोनों को खोजता है। एक ईमेल पते में (नहीं आरएफसी मानक
):=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))
पहले और आखिरी नाम निकालें
कभी-कभी समस्या विपरीत होती है। आपके डेटा में एक पूरा नाम फ़ील्ड है, लेकिन आपको पहले और अंतिम नामों को पार्स करना होगा। ये सूत्र प्रथम और अंतिम नाम के बीच की जगह की तलाश करते हैं और जहां आवश्यक हो वहां पाठ पकड़ लेते हैं। यदि A2 में कोई अंतिम नाम या रिक्त प्रविष्टि नहीं है तो यह उसे भी संभालता है।
=IFERROR(IF(SEARCH(" ",A2,1),LEFT(A2, SEARCH(" ",A2,1)),A2),IF(LEN(A2)>0,A2,""))
और अंतिम नाम:
=IFERROR(IF(SEARCH(" ",A2,1),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),A2),"")
वर्णों की संख्या को सीमित करें और जोड़ें ...
क्या आप कभी अपना मेटा विवरण साफ़ करना चाहते हैं? यदि आप सामग्री को एक्सेल में खींचना चाहते हैं और फिर मेटा विवरण फ़ील्ड (150 से 160 वर्ण) में उपयोग के लिए सामग्री को ट्रिम करना चाहते हैं, तो आप इस सूत्र का उपयोग करके ऐसा कर सकते हैं। यह स्पष्ट रूप से एक स्थान पर विवरण को तोड़ता है और फिर ... जोड़ता है:
=IF(LEN(A1)>155,LEFT(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ",""))))) & IF(LEN(A1)>FIND("*",SUBSTITUTE(A1," ","*",LEN(LEFT(A1,154))-LEN(SUBSTITUTE(LEFT(A1,154)," ","")))),"…",""),A1)
निःसंदेह, ये व्यापक होने के लिए नहीं हैं... बस कुछ त्वरित सूत्र हैं जो आपको एक त्वरित शुरुआत करने में मदद करेंगे! आप स्वयं को किन अन्य सूत्रों का उपयोग करते हुए पाते हैं? उन्हें टिप्पणियों में जोड़ें, और इस लेख को अपडेट करते समय मैं आपको श्रेय दूंगा।