หาวิธีทำความสะอาดข้อมูลอัตโนมัติใน Excel? ที่นี่คือที่ถูกต้อง บางครั้งข้อมูลที่ได้ไม่สะอาดพร้อมวิเคราะห์ ในกรณีเหล่านั้น ต้องทำความสะอาดและเตรียมข้อมูลด้วยขั้นตอนต่างๆ ที่นี่คุณจะพบ10วิธีอธิบายขั้นตอนสำหรับทำความสะอาดข้อมูลอัตโนมัติใน Excel
10 วิธีทำความสะอาดข้อมูลอัตโนมัติใน Excel
นี่คือชุดข้อมูลไม่เป็นระเบียบ มีชื่อ (Name), วันเกิด (Date of Birth), อาชีพ (Profession), และเงินเดือน (Salary)ของบางคน เราจะแสดงวิธีทำความสะอาดข้อมูลอัตโนมัติใน Excel ด้วยชุดข้อมูลนี้
1. ใช้ฟีเจอร์ Power Query ทำความสะอาดข้อมูล
วิธีแรก ใช้Power Query Featureสำหรับทำความสะอาดข้อมูลอัตโนมัติ ทำตามขั้นตอน:
ขั้นตอน:
- เลือกช่วงเซลล์B4:D10
- ไปData tab >> คลิกFrom Table/Range
- กล่องCreate Tableจะเปิด ชุดข้อมูลถูกเลือกแล้ว
- กดOK
- จะได้Power Query Editor
- คลิกUse First Row as Headersตั้งส่วนหัว
- เพื่อเปลี่ยนเคสข้อความ เลือกคอลัมน์Name
- ไปTransform tab >> คลิกText Column >> Format >> เลือกCapitalize Each Word
- คลิกปุ่มด้านล่างเพื่อลบแถวที่มีเซลล์ว่าง
- คลิกRemove Empty
- คลิกClose & Load >> เลือกClose & Load To
- กล่องImport Dataจะเปิด
- เลือกตัวเลือกNew worksheet
- กดOK
- ดังนั้น คุณสามารถทำความสะอาดข้อมูลอัตโนมัติด้วยPower Query Editor
2. ใช้ฟีเจอร์ Text to Columns ใน Excel
ในชุดข้อมูล ชื่อและนามสกุลอยู่ในคอลัมน์เดียวชื่อName สามารถแยกเป็น 2 คอลัมน์ด้วยText to Columnsสำหรับทำความสะอาดข้อมูลอัตโนมัติ
ขั้นตอน:
- เลือกช่วงเซลล์B5:B10
- ไปData tab >> Data Tools >> Text to Columns
- คลิกNext
- เลือกSemicolon, Comma, Space และ @ในOthersเป็นDelimiters
- คลิกNext
- ระบุเซลล์C5เป็นDestination
- คลิกFinish
- สุดท้าย ข้อมูลถูกแบ่งเป็น 2 คอลัมน์First NameและLast Name
3. ใช้ฟีเจอร์ Flash Fill ทำความสะอาดข้อมูลอัตโนมัติ
ตอนนี้คุณจะพบวิธีใช้Flash Fill Featureสำหรับทำความสะอาดข้อมูลอัตโนมัติ ในชุดข้อมูลมีสัญลักษณ์ไม่ต้องการหลายตัว ลบได้โดยทำตามขั้นตอน:
ขั้นตอน:
- ใส่Jackในคอลัมน์First Name
- ไปData tab >> Data Tools >> Flash Fill
- จะเห็นสัญลักษณ์ไม่ต้องการอื่นๆ ถูกลบ
4. ใช้ฟังก์ชัน SUBSTITUTE ทำความสะอาดข้อมูล
ต่อไป จะแสดงวิธีลบสัญลักษณ์ไม่ต้องการสำหรับทำความสะอาดข้อมูลอัตโนมัติใน Excel ด้วยฟังก์ชัน SUBSTITUTE
ทำตามขั้นตอน:
ขั้นตอน:
- เลือกเซลล์D5
- ใส่สูตร:
=SUBSTITUTE(C5,"@", )
ที่นี่ ในSUBSTITUTE ใส่เซลล์C5เป็นtext, "@"เป็นold_text และ ว่าง (" ")เป็นnew_text
- กดENTERลากFill HandleลงAutoFillสูตรไปเซลล์อื่น
- สุดท้าย ฟังก์ชัน SUBSTITUTEลบสัญลักษณ์ไม่ต้องการจากคอลัมน์D
5. ใช้ฟีเจอร์ Find & Replace ใน Excel
วิธีที่ 5 ทำความสะอาดข้อมูลด้วยFind and Replaceใน Excel ที่นี่เห็นค่า 2 ค่ามี"#"และ 2 ค่ามี"@" และ ";" จะแทนที่ค่าเหล่านี้ด้วยค่าว่างด้วยFind and Replace
ขั้นตอน:
- เลือกช่วงเซลล์B5:D10
- ไปHome tab >> Editing >> Find & Select >> Replace
- กล่องFind and Replaceจะเปิด
- ใส่"#"ในFind what และว่างในReplace with
- คลิกReplace
- จะเห็น"#"ถูกลบ
- เช่นกัน ลบ"@"และ";"ได้ด้วยFind & Replace Feature
6. ใช้ Number Format ทำความสะอาดข้อมูลอัตโนมัติ
เรียนรู้การเปลี่ยนNumber Formatสำหรับทำความสะอาดข้อมูลอัตโนมัติใน Excel ที่นี่บางค่าDate of Birthไม่อยู่ในรูปแบบDate ทำตามขั้นตอน:
ขั้นตอน:
- เลือกช่วงเซลล์C5:C10
- ไปHome tab >> Number Format >> คลิกปุ่มdrop-down
- เลือกShort Date
- ดังนั้น คุณสามารถเปลี่ยนNumber Formatของชุดข้อมูลเพื่อทำความสะอาดข้อมูลอัตโนมัติใน Excel
7. ใช้ตัวเลือก Remove Duplicates ทำความสะอาดข้อมูล
ลบค่าซ้ำด้วยRemove Duplicatesสำหรับทำความสะอาดข้อมูลอัตโนมัติใน Excel
ขั้นตอน:
- เลือกช่วงเซลล์B4:D11
- ไปData tab >> Data Tools >> Remove Duplicates
- กล่องโต้ตอบRemove Duplicatesจะเปิด
- กดOK
- กล่องอีกอันแสดงข้อมูลค่าซ้ำจะปรากฏ
- กดOK
- สุดท้าย Remove Duplicatesจะลบค่าซ้ำจากชุดข้อมูล
8. ใช้ฟีเจอร์ Go To Special ทำความสะอาดข้อมูลอัตโนมัติ
ใช้Go to Special featureตรวจจับเซลล์ว่างสำหรับทำความสะอาดข้อมูลอัตโนมัติใน Excel
ขั้นตอน:
- เลือกช่วงเซลล์B4:D11
- ไปHome tab >> Editing >> Find & Select >> Go To Special
- กล่องGo To Specialจะปรากฏ
- เลือกBlanks
- คลิกOK
- จะเห็นเซลล์ว่างถูกเลือก
- เปลี่ยนรูปแบบเซลล์ที่เลือกตามต้องการ
- ที่นี่ไปHome tab >> Fill Color >> เลือกRedเป็นFill Color
- ดังนั้น คุณสามารถตรวจจับเซลล์ว่างเพื่อทำความสะอาดข้อมูลใน Excel อัตโนมัติ
9. จับคู่ข้อความในรายการ ทำความสะอาดข้อมูล
คุณอาจมีข้อมูลและต้องการตรวจสอบกับรายการอื่นที่มีอยู่ ดูภาพหน้าจอตัวอย่าง เราจะหาผู้ที่ลาออกจากด้านซ้าย มีรายการหมายเลขผู้ลาออกทางขวา
ภาพหน้าจอแสดงตัวอย่างง่าย ข้อมูลอยู่ในช่วงB5:D22 เป้าหมายระบุแถวในโซนข้อมูลที่ปรากฏในรายการResigned Membersคอลัมน์F ลบแถวที่ไม่จำเป็นได้ภายหลัง
ขั้นตอน:
- เลือกเซลล์D5
- ใส่สูตร:
=IF(COUNTIF($F$5:$F$11,C5),"Resigned","")
ที่นี่ ฟังก์ชัน COUNTIFของสูตรจะคืน1หาก (ส่วนหนา) ตรงกับ ค่าในรายการ(ช่วงเซลล์F5:F11) กับ ค่าข้อมูล(เซลล์C5) หากส่วนนี้คืน1หรือมากกว่า ฟังก์ชัน IFจะคืน"Resigned" มิฉะนั้นคืนค่าว่าง
- กดENTERลากFill HandleลงAutoFillสูตรไปเซลล์อื่น
สูตรทั้งหมดจะแสดงคำว่า"Resigned"หาก"Member Num"ในคอลัมน์Cพบในรายการ"Resigned Members" หากหมายเลขสมาชิกไม่พบ จะคืนสตริงว่าง
- ตอนนี้ สูตรทั้งหมดจะแสดง"Resigned"หาก"Member Num"ในคอลัมน์Cพบใน"Resigned Members" หากไม่พบ คืนสตริงว่าง
คุณสามารถจัดเรียงรายการตามคอลัมน์D แถวสำหรับResigned Membersทั้งหมดจะรวมกันและลบได้เร็ว
- จัดเรียงตามคอลัมน์D เลือกเซลล์ D5 ถึง D22
- เลือก Home⇒Editing⇒Sort & Filter ⇒ Sort Z to A
- กล่องSort Warningจะเปิด
- เลือกExpand the selection
- คลิกSort
- ไฟล์ตัวอย่าง Excel จะเป็นแบบนี้
เทคนิคนี้ปรับใช้ได้กับงานจับคู่รายการประเภทอื่นๆ
10. ใช้ Spell Check ทำความสะอาดใน Excel
ขั้นตอนสุดท้าย แสดงวิธีSpell Checkสำหรับทำความสะอาดข้อมูลอัตโนมัติใน Excel ทำตามขั้นตอน:
ขั้นตอน:
- เลือกช่วงเซลล์C5:C10
- ไปReview tab >> คลิกabc Spelling
- กล่องSpellingจะเปิด
- เลือกManager
- คลิกChange
- เลือกReceptionist
- คลิกChange
- เลือกClerk
- คลิกChange
- กล่องเตือนMicrosoft Excelจะปรากฏ
- คลิกOK
- ดังนั้น คุณสามารถใช้Spell Checkเพื่อทำความสะอาดข้อมูลใน Excel
ส่วนฝึกปฏิบัติ
ในบทความคุณจะพบสมุดงาน Excel เพื่อฝึกฝนด้วยตนเอง
สรุป
ในบทความนี้ แสดงวิธีทำความสะอาดข้อมูลอัตโนมัติใน Excel 10 วิธี หวังว่าจะน่าสนใจและมีประโยชน์ หากมีส่วนไหนยาก กรุณาคอมเมนต์ แจ้งให้รู้หากมีทางเลือกอื่นที่พลาด และเยี่ยมชมExcelDemyสำหรับบทความอื่นๆ ขอบคุณ!
