สูตรการล้างข้อมูลทั่วไปใน 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 ที่ต่อกันในตัว แต่อย่าลืมใช้ 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 Description (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 เพื่อลดสแปม เรียนรู้วิธีการประมวลผลข้อมูลความคิดเห็นของคุณ.