用於常見數據清理的 Excel 公式

Excel 數據清理公式

多年以來,我一直將出版物用作參考資源,不僅描述如何做事,而且還保留記錄供以後查找! 今天,我們有一個客戶,向我們提供了一個災難性的客戶數據文件。 實際上,每個字段的格式都錯誤,並且; 結果,我們無法導入數據。 儘管有一些很棒的Excel加載項可以使用Visual Basic進行清理,但我們運行的Office for Mac不支持宏。 相反,我們尋找簡單的公式來提供幫助。 我以為我會在這里分享其中的一些,以便其他人可以使用它們。

刪除非數字字符

系統通常要求將電話號碼插入帶有國家代碼且不帶標點符號的特定11位數公式中。 但是,人們通常使用破折號和句點輸入此數據。 這是一個很棒的公式 刪除所有非數字字符 在Excel中。 該公式將檢查單元格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評估多個字段

我們經常從導入中清除不完整的記錄。 用戶並沒有意識到您不必總是編寫複雜的層次結構公式,而是可以編寫OR語句。 在下面的此示例中,我要檢查A2,B2,C2,D2或E2是否缺少數據。 如果缺少任何數據,我將返回0,否則將返回1。這將允許我對數據進行排序並刪除不完整的記錄。

=IF(OR(A2="",B2="",C2="",D2="",E2=""),0,1)

修剪和連接字段

如果您的數據具有“姓氏”和“姓氏”字段,但是導入具有“全名”字段,則可以使用內置的Excel Function Concatenate將這些字段整齊地串聯在一起,但是請確保使用TRIM刪除“文本。 如果其中一個字段沒有數據,則使用TRIM包裹整個字段:

=TRIM(CONCATENATE(TRIM(A1)," ",TRIM(B1)))

檢查有效的電子郵件地址

一個非常簡單的公式,它同時查找@和。 在電子郵件地址中:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

提取名字和姓氏

有時,問題恰恰相反。 您的數據有一個全名字段,但是您需要解析名字和姓氏。 這些公式在名字和姓氏之間尋找空格,並在必要時獲取文本。 如果A2中沒有姓氏或空白條目,IT也會處理。

=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),"")

限製字符數並添加…

您是否曾經想過清理元描述? 如果您想將內容提取到Excel中,然後修剪該內容以在“元描述”字段中使用(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)

當然,這些並不意味著全面……只是一些快速公式可以幫助您快速入門! 您還會使用其他哪些公式? 在評論中添加它們,在我更新本文時,我會給予您榮譽。

你覺得呢?

本網站使用Akismet來減少垃圾郵件。 了解您的評論如何處理.