คําที่สะกดผิด ช่องว่างต่อท้ายที่ล้าหลัง คํานําหน้าที่ไม่ต้องการ กรณีที่ไม่เหมาะสม และอักขระที่ไม่พิมพ์ออกมาจะสร้างความประทับใจแรกที่ไม่ดี และนั่นไม่ใช่รายการที่สมบูรณ์ของวิธีที่ข้อมูลของคุณอาจสกปรก พับปลอกหุ้มของคุณ ถึงเวลาแล้วที่คุณต้องทําความสะอาดเวิร์กชีตของคุณด้วย Microsoft Excel ในฤดูใบไม้ผลิ
พื้นฐานของการทําความสะอาดข้อมูลของคุณ
คุณไม่มีสิทธิ์ควบคุมรูปแบบและชนิดของข้อมูลที่คุณนําเข้าจากแหล่งข้อมูลภายนอกเสมอไป เช่น ฐานข้อมูล ไฟล์ข้อความ หรือเว็บเพจ ก่อนที่คุณจะสามารถวิเคราะห์ข้อมูล คุณมักจะต้องล้างข้อมูล โชคดีที่ Excel มีฟีเจอร์มากมายที่ช่วยให้คุณได้รับข้อมูลในรูปแบบที่แม่นยําที่คุณต้องการ บางครั้งงานนั้นตรงไปตรงมาและมีคุณสมบัติเฉพาะที่ทําหน้าที่ให้คุณ ตัวอย่างเช่น คุณสามารถใช้ตัวตรวจสอบการสะกดเพื่อล้างคําที่สะกดผิดในคอลัมน์ที่มีข้อคิดเห็นหรือคําอธิบายได้อย่างง่ายดาย หรือถ้าคุณต้องการเอาแถวที่ซ้ํากันออก คุณสามารถทําได้อย่างรวดเร็วโดยใช้กล่องโต้ตอบ เอารายการที่ซ้ํากันออก
ในบางครั้ง คุณอาจต้องจัดการคอลัมน์อย่างน้อยหนึ่งคอลัมน์โดยใช้สูตรเพื่อแปลงค่าที่นําเข้าให้เป็นค่าใหม่ ตัวอย่างเช่น ถ้าคุณต้องการเอาช่องว่างต่อท้ายออก คุณสามารถสร้างคอลัมน์ใหม่เพื่อล้างข้อมูลโดยใช้สูตร เติมคอลัมน์ใหม่ แปลงสูตรของคอลัมน์ใหม่ให้เป็นค่า แล้วเอาคอลัมน์เดิมออก
ขั้นตอนพื้นฐานสําหรับการทําความสะอาดข้อมูลมีดังนี้:
นําเข้าข้อมูลจากแหล่งข้อมูลภายนอก
สร้างสําเนาสํารองของข้อมูลต้นฉบับในเวิร์กบุ๊กที่แยกต่างหาก
ตรวจสอบให้แน่ใจว่าข้อมูลอยู่ในรูปแบบตารางของแถวและคอลัมน์ด้วย: ข้อมูลที่คล้ายกันในแต่ละคอลัมน์ คอลัมน์และแถวทั้งหมดสามารถมองเห็นได้ และไม่มีแถวว่างอยู่ภายในช่วง เพื่อให้ได้ผลลัพธ์ที่ดีที่สุด ให้ใช้ตาราง Excel
ทํางานที่ไม่จําเป็นต้องมีการจัดการคอลัมน์ก่อน เช่น การตรวจสอบการสะกด หรือการใช้กล่องโต้ตอบ ค้นหาและแทนที่
ถัดไป ให้ทํางานที่จําเป็นต้องมีการจัดการคอลัมน์ ขั้นตอนทั่วไปสําหรับการจัดการคอลัมน์คือ:
- แทรกคอลัมน์ใหม่ (B) ถัดจากคอลัมน์ต้นฉบับ (A) ที่ต้องการการทําความสะอาด
- เพิ่มสูตรที่จะแปลงข้อมูลที่ด้านบนของคอลัมน์ใหม่ (B)
- เติมสูตรในคอลัมน์ใหม่ (B) ในตาราง Excel คอลัมน์จากการคํานวณจะถูกสร้างขึ้นโดยอัตโนมัติโดยเติมค่าไว้
- เลือกคอลัมน์ใหม่ (B) คัดลอก แล้ววางเป็นค่าลงในคอลัมน์ใหม่ (B)
- เอาคอลัมน์ต้นฉบับ (A) ออก ซึ่งจะแปลงคอลัมน์ใหม่จาก B เป็น A
เมื่อต้องการทําความสะอาดแหล่งข้อมูลเดียวกันเป็นระยะๆ ให้พิจารณาการบันทึกแมโครหรือโค้ดการเขียนเพื่อทําให้กระบวนการทั้งหมดเป็นอัตโนมัติ นอกจากนี้ยังมี Add-in ภายนอกจํานวนหนึ่งที่เขียนโดยผู้จําหน่ายรายอื่น ซึ่งแสดงอยู่ในส่วน ผู้ให้บริการของบริษัทอื่น ซึ่งคุณสามารถพิจารณาใช้ได้ในกรณีที่คุณไม่มีเวลาหรือทรัพยากรเพื่อทําให้กระบวนการเป็นแบบอัตโนมัติด้วยตัวคุณเอง
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| ใส่ข้อมูลในเซลล์เวิร์กชีตโดยอัตโนมัติ | แสดงวิธีใช้คําสั่ง เติม |
|
สร้างและจัดรูปแบบตาราง ปรับขนาดตารางด้วยการเพิ่มหรือนำแถวและคอลัมน์ออก ใช้คอลัมน์จากการคำนวณในตาราง Excel |
แสดงวิธีการสร้างตาราง Excel และเพิ่มหรือลบคอลัมน์หรือคอลัมน์จากการคํานวณ |
| การสร้างแมโคร | แสดงหลายวิธีในการทํางานที่ทําซ้ําๆ โดยอัตโนมัติโดยใช้แมโคร |
การตรวจการสะกด
คุณสามารถใช้ตัวตรวจสอบการสะกดเพื่อค้นหาคําที่สะกดผิดได้ไม่เพียงแต่จะค้นหาค่าที่ไม่ได้ใช้อย่างสม่ําเสมอ เช่น ชื่อผลิตภัณฑ์หรือบริษัทด้วยการเพิ่มค่าเหล่านั้นลงในพจนานุกรมแบบกําหนดเอง
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| ตรวจสอบการสะกดและไวยากรณ์ | แสดงวิธีการแก้ไขคําที่สะกดผิดบนเวิร์กชีต |
| ใช้พจนานุกรมแบบกําหนดเองเพื่อเพิ่มคําลงในตัวตรวจสอบการสะกด | อธิบายวิธีการใช้พจนานุกรมแบบกําหนดเอง |
กําลังเอาแถวที่ซ้ํากันออก
แถวที่ซ้ํากันเป็นปัญหาทั่วไปเมื่อคุณนําเข้าข้อมูล เป็นความคิดที่ดีที่จะกรองค่าที่ไม่ซ้ํากันก่อนเพื่อยืนยันว่าผลลัพธ์เป็นสิ่งที่คุณต้องการก่อนที่คุณจะเอาค่าที่ซ้ํากันออก
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| กรองค่าที่ไม่ซ้ำกันหรือเอาค่าที่ซ้ำกันออก | แสดงสองกระบวนงานที่ใกล้เคียงกัน: วิธีกรองแถวที่ไม่ซ้ํากันและวิธีการเอาแถวที่ซ้ํากันออก |
การค้นหาและการแทนที่ข้อความ
คุณอาจต้องการเอาสตริงนําหน้าทั่วไป ออก เช่น ป้ายชื่อที่ตามด้วยเครื่องหมายจุดคู่และช่องว่าง หรือคําต่อท้าย เช่น วลีวงเล็บ ที่ส่วนท้ายของสตริงที่ล้าสมัยหรือไม่จําเป็น คุณสามารถทําได้โดยการค้นหาอินสแตนซ์ของข้อความนั้น แล้วแทนที่โดยไม่มีข้อความหรือข้อความอื่นๆ
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
|
ตรวจสอบว่าเซลล์มีข้อความหรือไม่ (ไม่ตรงตามตัวพิมพ์ใหญ่-เล็ก) ตรวจสอบว่าเซลล์มีข้อความหรือไม่ (ตรงตามตัวพิมพ์ใหญ่-เล็ก) |
แสดงวิธีใช้คําสั่ง ค้นหา และฟังก์ชันต่างๆ เพื่อค้นหาข้อความ |
| เอาอักขระออกจากข้อความ | แสดงวิธีใช้คําสั่ง แทนที่ และฟังก์ชันต่างๆ เพื่อเอาข้อความออก |
| ค้นหา หรือแทนที่ข้อความและตัวเลขบนเวิร์กชีต | แสดงวิธีใช้กล่องโต้ตอบ ค้นหาและแทนที่ |
|
FIND, FINDB SEARCH, SEARCHB REPLACE, REPLACEB SUBSTITUTE LEFT, LEFTB RIGHT, RIGHTB LEN, LENB MID, MIDB |
นี่คือฟังก์ชันที่คุณสามารถใช้เพื่อทํางานการจัดการสตริงต่างๆ เช่น การค้นหาและแทนที่สตริงย่อยภายในสตริง การแยกส่วนของสตริง หรือการกําหนดความยาวของสตริง |
การเปลี่ยนตัวพิมพ์ของข้อความ
บางครั้งข้อความจะมาในถุงผสม โดยเฉพาะอย่างยิ่งเมื่อกรณีของข้อความมีความกังวล เมื่อใช้ฟังก์ชัน Case อย่างน้อยหนึ่งฟังก์ชัน คุณสามารถแปลงข้อความเป็นตัวอักษรพิมพ์เล็ก เช่น ที่อยู่อีเมล ตัวอักษรพิมพ์ใหญ่ เช่น รหัสผลิตภัณฑ์ หรือตัวพิมพ์ที่เหมาะสม เช่น ชื่อหรือหนังสือ
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| เปลี่ยนตัวพิมพ์ข้อความ | แสดงวิธีใช้ฟังก์ชัน Case ทั้งสามฟังก์ชัน |
| LOWER | แปลงอักษรตัวพิมพ์ใหญ่ทั้งหมดในสตริงข้อความให้เป็นตัวอักษรพิมพ์เล็ก |
| PROPER | ทําให้ตัวอักษรตัวแรกในสตริงข้อความและตัวอักษรอื่นๆ ในข้อความที่ตามหลังอักขระใดๆ ที่ไม่ใช่ตัวอักษรเป็นตัวพิมพ์ใหญ่ แปลงตัวอักษรอื่นๆ ทั้งหมดให้เป็นตัวอักษรพิมพ์เล็ก |
| UPPER | แปลงข้อความเป็นตัวอักษรพิมพ์ใหญ่ |
การเอาช่องว่างและอักขระที่ไม่พิมพ์ออกจากข้อความ
ในบางครั้ง ค่าข้อความจะมีอักขระนําหน้า ต่อท้าย หรืออักขระช่องว่างฝังตัวหลายตัว (ชุดอักขระ Unicode ค่า 32 และ 160) หรืออักขระที่ไม่พิมพ์ (ชุดอักขระ Unicode ค่า 0 ถึง 31, 127, 129, 141, 143, 144 และ 157) บางครั้งอักขระเหล่านี้อาจทําให้เกิดผลลัพธ์ที่ไม่คาดคิดเมื่อคุณเรียงลําดับ กรอง หรือค้นหา ตัวอย่างเช่น ในแหล่งข้อมูลภายนอก ผู้ใช้อาจเกิดข้อผิดพลาดในการพิมพ์โดยการเพิ่มอักขระช่องว่างเพิ่มเติมโดยไม่ได้ตั้งใจ หรือข้อมูลที่นําเข้าจากแหล่งข้อมูลภายนอกอาจมีอักขระที่ไม่พิมพ์ซึ่งฝังตัวอยู่ในข้อความ เนื่องจากอักขระเหล่านี้ไม่สังเกตเห็นได้ง่าย ผลลัพธ์ที่ไม่คาดคิดอาจเข้าใจได้ยาก เมื่อต้องการเอาอักขระที่ไม่ต้องการเหล่านี้ออก คุณสามารถใช้ฟังก์ชัน TRIM, CLEAN และ SUBSTITUTE ร่วมกันได้
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| CODE | ส่งกลับโค้ดตัวเลขสําหรับอักขระแรกในสตริงข้อความ |
| CLEAN | เอาอักขระที่ไม่สามารถพิมพ์ได้ 32 ตัวแรกในโค้ด ASCII แบบ 7 บิต (ค่า 0 ถึง 31) ออกจากข้อความ |
| TRIM | เอาอักขระช่องว่าง ASCII แบบ 7 บิต (ค่า 32) ออกจากข้อความ |
| SUBSTITUTE | คุณสามารถใช้ฟังก์ชัน SUBSTITUTE เพื่อแทนที่อักขระ Unicode ที่มีค่าสูงกว่า (ค่า 127, 129, 141, 143, 144, 157 และ 160) ด้วยอักขระ ASCII แบบ 7 บิตที่ฟังก์ชัน TRIM และ CLEAN ได้รับการออกแบบมา |
การแก้ไขตัวเลขและเครื่องหมายตัวเลข
มีปัญหาหลักๆ สองประการเกี่ยวกับตัวเลขที่อาจทําให้คุณต้องล้างข้อมูล: ตัวเลขถูกนําเข้าเป็นข้อความโดยไม่ได้ตั้งใจ และต้องมีการเปลี่ยนเครื่องหมายลบเป็นมาตรฐานสําหรับองค์กรของคุณ
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| แปลงตัวเลขที่เก็บเป็นข้อความให้เป็นตัวเลข | แสดงวิธีการแปลงตัวเลขที่ถูกจัดรูปแบบและจัดเก็บในเซลล์เป็นข้อความ ซึ่งอาจทําให้เกิดปัญหาในการคํานวณหรือสร้างความสับสนในการจัดเรียงให้เป็นรูปแบบตัวเลข |
| DOLLAR | แปลงตัวเลขเป็นรูปแบบข้อความและใช้สัญลักษณ์สกุลเงิน |
| TEXT | แปลงค่าเป็นข้อความในรูปแบบตัวเลขที่ระบุ |
| ถาวร | ปัดเศษตัวเลขเป็นจํานวนทศนิยมที่ระบุ จัดรูปแบบตัวเลขในรูปแบบทศนิยมโดยใช้เครื่องหมายมหัพภาคและจุลภาค แล้วส่งกลับผลลัพธ์เป็นข้อความ |
| ค่า | ฟังก์ชันนี้จะแปลงสตริงข้อความที่แทนจำนวนให้เป็นตัวเลข |
การแก้ไขวันที่และเวลา
เนื่องจากมีรูปแบบวันที่ที่แตกต่างกันมากมาย และเนื่องจากรูปแบบเหล่านี้อาจสับสนกับโค้ดส่วนลําดับเลขหรือสตริงอื่นๆ ที่มีเครื่องหมายทับหรือยัติภังค์ วันที่และเวลามักจะต้องถูกแปลงและจัดรูปแบบใหม่
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| เปลี่ยนการตีความระบบวันที่ รูปแบบ หรือตัวเลขปีแบบสองหลัก | อธิบายการทํางานของระบบวันที่ใน Office Excel |
| แปลงเวลา | แสดงวิธีการแปลงระหว่างหน่วยเวลาต่างๆ |
| การแปลงวันที่ซึ่งเก็บเป็นข้อความให้เป็นวันที่ | แสดงวิธีแปลงวันที่ที่ถูกจัดรูปแบบและจัดเก็บในเซลล์เป็นข้อความ ซึ่งอาจทําให้เกิดปัญหาในการคํานวณหรือสร้างความสับสนในการจัดเรียงตามรูปแบบวันที่ |
| วัน | ส่งกลับเลขลําดับต่อเนื่องที่แสดงถึงวันที่ที่เฉพาะเจาะจง ถ้ารูปแบบเซลล์เป็น ทั่วไป ก่อนที่จะใส่ฟังก์ชัน ผลลัพธ์จะถูกจัดรูปแบบเป็นวันที่ |
| DATEVALUE | แปลงวันที่ที่แสดงด้วยข้อความเป็นเลขลําดับ |
| TIME | ส่งกลับตัวเลขทศนิยมของเวลาที่ระบุ ถ้ารูปแบบเซลล์เป็น ทั่วไป ก่อนที่จะใส่ฟังก์ชัน ผลลัพธ์จะถูกจัดรูปแบบเป็นวันที่ |
| TIMEVALUE | ส่งกลับตัวเลขทศนิยมของเวลาที่แสดงด้วยสตริงข้อความ ตัวเลขทศนิยมคือค่าตั้งแต่ 0 (ศูนย์) ถึง 0.99999999 ซึ่งแทนเวลาตั้งแต่ 0:00:00 (12:00:00 AM) ถึง 23:59:59 (11:59:59 P.M.) |
การผสานและการแยกคอลัมน์
งานทั่วไปหลังจากนําเข้าข้อมูลจากแหล่งข้อมูลภายนอกคือการผสานคอลัมน์อย่างน้อยสองคอลัมน์ให้เป็นหนึ่งคอลัมน์ หรือแยกคอลัมน์หนึ่งคอลัมน์ออกเป็นคอลัมน์อย่างน้อยสองคอลัมน์ ตัวอย่างเช่น คุณอาจต้องการแยกคอลัมน์ที่มีชื่อเต็มเป็นชื่อและนามสกุล หรือคุณอาจต้องการแยกคอลัมน์ที่มีเขตข้อมูลที่อยู่เป็นคอลัมน์ถนน เมือง ภูมิภาค และรหัสไปรษณีย์ที่แยกจากกัน การย้อนกลับอาจเป็นจริง คุณอาจต้องการผสานคอลัมน์ชื่อและนามสกุลลงในคอลัมน์ชื่อเต็ม หรือรวมคอลัมน์ที่อยู่ที่แยกกันเป็นคอลัมน์เดียว ค่าทั่วไปเพิ่มเติมที่อาจต้องการการผสานเป็นคอลัมน์เดียวหรือแยกออกเป็นหลายคอลัมน์ ได้แก่ รหัสผลิตภัณฑ์ เส้นทางไฟล์ และที่อยู่อินเทอร์เน็ตโพรโทคอล (IP)
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
|
รวมชื่อและนามสกุล การรวมข้อความและตัวเลข รวมข้อความด้วยวันที่หรือเวลา รวมคอลัมน์หลายคอลัมน์โดยใช้ฟังก์ชัน |
แสดงตัวอย่างทั่วไปของการรวมค่าจากคอลัมน์อย่างน้อยสองคอลัมน์ |
| แยกข้อความเป็นหลายคอลัมน์ด้วยการแปลงข้อความเป็นตัวช่วยสร้างคอลัมน์ | แสดงวิธีใช้ตัวช่วยสร้างนี้เพื่อแยกคอลัมน์โดยยึดตามตัวคั่นทั่วไปต่างๆ |
| แยกข้อความลงในคอลัมน์ต่างๆ ด้วยฟังก์ชัน | แสดงวิธีใช้ฟังก์ชัน LEFT, MID, RIGHT, SEARCH และ LEN เพื่อแยกคอลัมน์ชื่อออกเป็นคอลัมน์อย่างน้อยสองคอลัมน์ |
| รวมหรือแยกเนื้อหาของเซลล์ | แสดงวิธีใช้ฟังก์ชัน CONCATENATE ตัวดําเนินการ & (เครื่องหมายและ) และตัวช่วยสร้างการแปลงข้อความเป็นคอลัมน์ |
| ผสานเซลล์หรือแยกเซลล์ที่ถูกผสาน | แสดงวิธีใช้คําสั่งผสานเซลล์ผสานข้าม และผสานและจัดกึ่งกลาง |
| CONCATENATE | เชื่อมสตริงข้อความอย่างน้อยสองสตริงให้เป็นสตริงข้อความเดียว |
การแปลงและการจัดเรียงคอลัมน์และแถวใหม่
ฟีเจอร์การวิเคราะห์และการจัดรูปแบบส่วนใหญ่ใน Office Excel จะถือว่าข้อมูลมีอยู่ในตารางสองมิติเดียวแบบแฟลต ในบางครั้ง คุณอาจต้องการทําให้แถวกลายเป็นคอลัมน์ และคอลัมน์จะกลายเป็นแถว ในบางครั้ง ข้อมูลไม่ได้มีโครงสร้างเป็นรูปแบบตาราง และคุณต้องการวิธีแปลงข้อมูลจากตารางเป็นรูปแบบตาราง
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| TRANSPOSE | ส่งกลับช่วงของเซลล์แนวตั้งเป็นช่วงแนวนอน หรือกลับกัน |
การเชื่อมต่อข้อมูลตารางด้วยการเชื่อมหรือการจับคู่
ในบางครั้ง ผู้ดูแลระบบฐานข้อมูลจะใช้ Office Excel เพื่อค้นหาและแก้ไขข้อผิดพลาดที่ตรงกันเมื่อตารางสองตารางหรือมากกว่าถูกรวมเข้าด้วยกัน ซึ่งอาจเกี่ยวข้องกับการเชื่อมต่อสองตารางจากเวิร์กชีตที่ต่างกัน ใหม่ ตัวอย่างเช่น เมื่อต้องการดูระเบียนทั้งหมดในทั้งสองตาราง หรือเพื่อเปรียบเทียบตารางและค้นหาแถวที่ไม่ตรงกัน
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| การค้นหาค่าในรายการของข้อมูล | แสดงวิธีทั่วไปในการค้นหาข้อมูลโดยใช้ฟังก์ชันการค้นหา |
| LOOKUP | ส่งกลับค่าจากช่วงหนึ่งแถวหรือหนึ่งคอลัมน์หรือจากอาร์เรย์ ฟังก์ชัน LOOKUP มีรูปแบบไวยากรณ์สองรูปแบบ คือ ฟอร์มเวกเตอร์และรูปแบบอาร์เรย์ |
| HLOOKUP | ค้นหาค่าในแถวบนสุดของตารางหรืออาร์เรย์ของค่า แล้วส่งกลับค่าในคอลัมน์เดียวกันจากแถวที่คุณระบุในตารางหรืออาร์เรย์ |
| VLOOKUP | ค้นหาค่าในคอลัมน์แรกของอาร์เรย์ตารางและส่งกลับค่าในแถวเดียวกันจากคอลัมน์อื่นในอาร์เรย์ตาราง |
| ดัชนี | ส่งกลับค่าหรือการอ้างอิงไปยังค่าจากภายในตารางหรือช่วง ฟังก์ชัน INDEX มีสองรูปแบบ คือ รูปแบบอาร์เรย์และรูปแบบการอ้างอิง |
| MATCH | ส่งกลับตําแหน่งสัมพันธ์ของรายการในอาร์เรย์ที่ตรงกับค่าที่ระบุในลําดับที่ระบุ ใช้ MATCH แทนฟังก์ชัน LOOKUP อย่างใดอย่างหนึ่งเมื่อคุณต้องการตําแหน่งของรายการในช่วงแทนที่จะเป็นรายการ |
| OFFSET | ส่งกลับการอ้างอิงไปยังช่วงที่เป็นจํานวนแถวและคอลัมน์ที่ระบุจากเซลล์หรือช่วงของเซลล์ การอ้างอิงที่ส่งกลับอาจเป็นเซลล์เดียวหรือช่วงของเซลล์ก็ได้ คุณสามารถระบุจํานวนแถวและจํานวนคอลัมน์ที่จะส่งกลับได้ |
ผู้ให้บริการของบริษัทภายนอก
ต่อไปนี้คือรายชื่อบางส่วนของผู้ให้บริการของบริษัทอื่นที่มีผลิตภัณฑ์ที่ใช้เพื่อล้างข้อมูลในหลากหลายวิธี
หมายเหตุ
Microsoft ไม่สนับสนุนผลิตภัณฑ์ของบริษัทอื่น