คําที่สะกดผิด ช่องว่างต่อท้ายที่ดื้อรั้น คํานําหน้าที่ไม่ต้องการ ตัวพิมพ์ที่ไม่เหมาะสม และอักขระที่ไม่สามารถพิมพ์ออกมาสร้างความประทับใจแรกพบที่ไม่ดี และนั่นยังไม่ใช่รายการวิธีที่ข้อมูลของคุณสกปรกทั้งหมด ม้วนแขนเสื้อของคุณ ถึงเวลาทําความสะอาดเวิร์กชีตของคุณในฤดูใบไม้ผลิด้วย 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 | แปลงตัวเลขไปเป็นรูปแบบข้อความ และใช้สัญลักษณ์สกุลเงิน |
| ข้อความ | แปลงค่าเป็นข้อความในรูปแบบตัวเลขที่ระบุ |
| แก้ไขแล้ว | ปัดค่าตัวเลขเป็นเลขทศนิยมตามที่ระบุ จัดรูปแบบตัวเลขเป็นรูปแบบทศนิยมโดยใช้เครื่องหมายมหัพภาคและจุลภาค และส่งกลับค่าผลลัพธ์เป็นข้อความ |
| ค่า | ฟังก์ชันนี้จะแปลงสตริงข้อความที่แทนจำนวนให้เป็นตัวเลข |
แก้ไขวันที่และเวลา
เนื่องจากมีรูปแบบวันที่หลายรูปแบบ และเนื่องจากรูปแบบเหล่านี้อาจสับสนกับโค้ดส่วนลําดับเลขหรือสตริงอื่นๆ ที่มีเครื่องหมายทับหรือยัติภังค์ วันที่และเวลามักจะต้องถูกแปลงและจัดรูปแบบใหม่
| ข้อมูลเพิ่มเติม | คำอธิบาย |
|---|---|
| เปลี่ยนระบบวันที่ รูปแบบ หรือการตีความปีแบบสองหลัก | อธิบายวิธีการทํางานของระบบวันที่ใน Office Excel |
| แปลงเวลา | แสดงวิธีการแปลงระหว่างหน่วยเวลาที่แตกต่างกัน |
| การแปลงวันที่ซึ่งเก็บเป็นข้อความให้เป็นวันที่ | แสดงวิธีการแปลงวันที่ที่จัดรูปแบบและเก็บในเซลล์เป็นข้อความ ซึ่งอาจทําให้เกิดปัญหาในการคํานวณหรือสร้างความสับสนในลําดับการจัดเรียงให้เป็นรูปแบบวันที่ |
| วันที่ | ส่งกลับเลขลําดับต่อเนื่องที่แสดงถึงวันที่ใดวันหนึ่ง ถ้ารูปแบบของเซลล์เป็นรูปแบบทั่วไปก่อนที่จะใส่ฟังก์ชัน ผลลัพธ์ที่ได้จะถูกจัดรูปแบบให้เป็นวันที่ |
| DATEVALUE | แปลงวันที่ที่แสดงด้วยข้อความเป็นเลขลําดับ |
| TIME | ส่งกลับตัวเลขทศนิยมของเวลาที่ระบุ ถ้ารูปแบบของเซลล์เป็นรูปแบบทั่วไปก่อนที่จะใส่ฟังก์ชัน ผลลัพธ์ที่ได้จะถูกจัดรูปแบบให้เป็นวันที่ |
| TIMEVALUE | ส่งกลับค่าตัวเลขทศนิยมของเวลาซึ่งแทนโดยสตริงข้อความ ตัวเลขทศนิยมคือค่าในช่วงตั้งแต่ 0 (ศูนย์) ถึง 0.999999999 ซึ่งจะแทนเวลาตั้งแต่ 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 ไม่ได้ให้การสนับสนุนสําหรับผลิตภัณฑ์ของบริษัทอื่น