สูตร Excel สำหรับการล้างข้อมูลทั่วไป
เป็นเวลาหลายปีแล้วที่ฉันใช้สิ่งพิมพ์เป็นแหล่งข้อมูลเพื่ออธิบายวิธีดำเนินการต่างๆ และเก็บบันทึกไว้ให้ตัวเองดูในภายหลัง! ลูกค้าส่งไฟล์ข้อมูลลูกค้าที่เป็นภัยพิบัติมาให้เรา แทบทุกช่องมีรูปแบบไม่ถูกต้อง และด้วยเหตุนี้ เราจึงไม่สามารถนำเข้าข้อมูลได้ แม้ว่าจะมี Add-on ดีๆ สำหรับ 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 ในตัวที่เชื่อมต่อกัน แต่ต้องแน่ใจว่าใช้ 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),"")
จำกัด จำนวนตัวละครและเพิ่ม ...
คุณเคยต้องการล้างคำอธิบายเมตาของคุณหรือไม่? หากคุณต้องการดึงเนื้อหาลงใน Excel แล้วตัดเนื้อหาเพื่อใช้ในช่องคำอธิบาย Meta (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)
แน่นอนว่าสิ่งเหล่านี้ไม่ได้หมายถึงให้ครอบคลุม... เพียงแค่สูตรสั้นๆ ที่จะช่วยให้คุณเริ่มต้นได้อย่างรวดเร็ว! คุณใช้สูตรอะไรอีกบ้าง? เพิ่มพวกเขาในความคิดเห็น และฉันจะให้เครดิตคุณในขณะที่ฉันอัปเดตบทความนี้