ทำความสะอาดข้อมูล Excel: 19 เทคนิคมืออาชีพสำหรับการวิเคราะห์ที่แม่นยำ

ด้วยการป้อนและจัดระเบียบข้อมูล Microsoft Excel ยังสามารถวิเคราะห์ข้อมูลระดับเริ่มต้น-กลางได้ง่ายในปัจจุบัน สำหรับการใช้งานประจำวัน มีเครื่องมือที่ใช้งานได้จริงมากมาย การทำความสะอาดข้อมูล (Data cleaning) เป็นขั้นตอนแรกสำคัญของทุกวิธีการวิเคราะห์ ประกอบด้วยการลบ เตรียม หรือแปลงค่าที่ไม่ต้องการหรือผิดปกติให้ใช้งานได้ ในบทช่วยสอนนี้ เราจะอภิปรายเทคนิคทำความสะอาดข้อมูลต่างๆ และวิธีทำใน Excel

ดาวน์โหลดไฟล์งานที่ใช้สาธิตได้จากลิงก์ด้านล่าง

19 เทคนิคทำความสะอาดข้อมูลใน Excel ที่จะเป็นประโยชน์

เราจะอภิปรายเทคนิคทำความสะอาดข้อมูลใน Excel ทั้งหมด 19 ข้อ ทุกข้อสำคัญในกรณีต่างๆ ติดตามดูการทำงานของแต่ละข้อ หรือหาข้อกำหนดที่ต้องการจากสารบัญด้านบน

1. ลบแถวซ้ำ (Remove Duplicate Rows)

ข้อมูลอาจมีแถวซ้ำ ไม่ว่าสาเหตุใด กส่วนใหญ่ต้องกำจัดแถวซ้ำ ในอดีต การลบข้อมูลซ้ำเป็นงานทำด้วยมือ—แม้จะทำได้ด้วยเทคนิคขั้นสูง แต่คำสั่งRemove Duplicatesทำให้ง่ายขึ้น คำสั่งนี้เพิ่มมาตั้งแต่ Excel 2007

ลองคัดลอกคอลัมน์ข้างๆ แล้วลบซ้ำเพื่อเปรียบเทียบภายหลัง

ทำตามขั้นตอนเพื่อลบซ้ำ:

ขั้นตอน:

• เลือกคอลัมน์ที่ต้องการลบซ้ำ
• ไปแท็บDataบนริบบอน
• เลือกRemove Duplicatesจากกลุ่มData Tools

• เลือกตัวเลือกContinue with the current selectionในกล่องป๊อปอัป แล้วคลิกRemove Duplicates

• คลิกOK

จะลบค่าซ้ำทั้งหมดจากช่วงที่เลือก

2. ไฮไลท์ค่าซ้ำ (Highlight Duplicate Values)

บางครั้งแทนการลบค่าซ้ำทั้งหมด การไฮไลท์จะช่วยได้ أكثر นี่คือสถานการณ์และวิธีต่างๆในการไฮไลท์ค่าซ้ำ

ใช้ Conditional Formatting

ส่วนแรก ใช้ฟีเจอร์Conditional Formattingไฮไลท์ค่าซ้ำ บนชุดข้อมูลต่อไปนี้

ทำตามขั้นตอน:

ขั้นตอน:

• เลือกเซลล์ที่ต้องการตรวจสอบซ้ำ กรณีนี้คือ D5:D18
• ไปHome > เลือกConditional Formatting > ไปHighlight Cells Rules > เลือกDuplicate Values

• จะมีหน้าต่างDuplicate Valuesปรากฏ
• เลือกDuplicate
• เลือกตัวเลือกสีในvalues with กรณีนี้เลือกLight Red Fill with Dark Red Text

• จะเห็นค่าซ้ำในคอลัมน์Dถูกไฮไลท์

ไฮไลท์ซ้ำรวมครั้งแรก (Including First Occurrence)

สำหรับชุดข้อมูลเดียวกัน เราจะไฮไลท์ค่าซ้ำรวมครั้งแรกด้วย เหมือนข้างต้น แต่ใช้เส้นทางต่างกันพร้อมฟังก์ชันCOUNTIF ทำตามขั้นตอน:

ขั้นตอน:

• ไปหน้าต่างNew Formatting Ruleผ่านConditional Formatting

• เลือกUse a formula to determine which cells to format
• ใส่สูตรในกล่องสูตร:

=COUNTIF($D$5:$E$16,$D5)>1

• จะเห็นคอลัมน์Dถูกไฮไลท์สำหรับค่าซ้ำ รวมครั้งแรก

ไฮไลท์ซ้ำไม่รวมครั้งแรก (Excluding First Occurrence)

เทคนิคอีกอย่างที่ประโยชน์สำหรับระบุค่าซ้ำ ไม่ไฮไลท์ครั้งแรก เหมาะหากต้องการดูเฉพาะค่าที่ซ้ำซ้อน ไม่ใช่ทุกค่าซ้ำ ใช้ฟังก์ชันCOUNTIFเช่นกัน

ทำตามขั้นตอน:

ขั้นตอน:

• ไปNew Formatting Ruleผ่านConditional Formattingเหมือนเดิม

• เลือกUse a formula to determine which cells to formatเหมือนเดิม
• ใส่สูตร:

=COUNTIF($D$5:$D5,$D5)>1

• กดOK จะไฮไลท์ค่าซ้ำทั้งหมด ไม่รวมครั้งแรก

3. แยกข้อความเป็นหลายเซลล์ (Split Text into Multiple Cells)

เมื่อนำเข้าข้อมูลจากแหล่งอื่น อาจเกิดค่าหลายค่ารวมอยู่ในคอลัมน์เดียว ภาพต่อไปแสดงตัวอย่าง

มีฟีเจอร์Text to Columnที่ช่วยแยกข้อความเหล่านี้

ขั้นตอน:

• เลือกช่วงที่ต้องการแยกข้อความ
• ไปแท็บDataบนริบบอน
• จากกลุ่มData ToolsเลือกText to Columns

• เลือกDelimitedในกล่องถัดไป

• กดNext กล่องใหม่ปรากฏ
• เลือกSpaceภายใต้Delimiteroptions เนื่องจากข้อความคั่นด้วยช่องว่าง
• กดNext

• กดFinish

• ข้อความจะถูกแยก

• ปรับแต่งเล็กน้อยเพื่อความสวยงาม

นี่คือวิธีแยกข้อความเป็นเซลล์ต่างๆ พร้อมเทคนิคทำความสะอาดข้อมูลอื่นๆ

4. แปลงข้อมูลด้วยสูตร (Transforming Data with Formulas)

บางครั้งต้องการให้รูปแบบตัวอักษรของข้อความเหมือนกัน Excel ไม่มีวิธีโดยตรงเปลี่ยนเคส แต่ทำได้ง่ายด้วยสูตร 3 สูตรสำหรับเปลี่ยนข้อความ:

UPPER: แปลงเป็นตัวพิมพ์ใหญ่ทั้งหมด

LOWER: แปลงเป็นตัวพิมพ์เล็กทั้งหมด

PROPER: แปลงเป็นProper Case(ตัวแรกของแต่ละคำเป็นพิมพ์ใหญ่ เช่นชื่อเฉพาะ)

ใช้ชุดข้อมูลนี้สำหรับทุกตัวอย่าง

เปลี่ยนเป็นตัวพิมพ์ใหญ่ (Upper Case)

ใช้ฟังก์ชันUPPER ทำตามขั้นตอน:

ขั้นตอน:

• เลือกเซลล์C5
• ใส่สูตร:

=UPPER(B5)

• กดEnter

• เลือกเซลล์อีกครั้ง ลากแฮนด์ล์เติม (fill handle) เพื่อเติมสูตรไปเซลล์อื่นๆ

เปลี่ยนเป็นตัวพิมพ์เล็ก (Lower Case)

สาธิตการแปลงทั้งหมดเป็นตัวพิมพ์เล็ก

ขั้นตอน:

• เลือกเซลล์D5
• ใส่สูตร:

=LOWER(B5)

• กดEnter

• ลากแฮนด์ล์เติมเพื่อคัดลอกสูตร

เปลี่ยนเป็น Proper Case

Proper Case หมายถึงตัวพิมพ์ใหญ่ที่ตำแหน่งที่ถูกต้อง ตัวพิมพ์เล็กที่เหลือ ใช้วิธีนี้:

ขั้นตอน:

• เลือกเซลล์E5
• ใส่สูตร:

=PROPER(B5)

• กดEnter

• ลากแฮนด์ล์เติมเพื่อเติมสูตรไปที่เหลือ

5. ลบช่องว่างเกิน (Remove Extra Spaces)

สำคัญมากที่ต้องมั่นใจข้อความไม่มีช่องว่างเกิน ไม่สามารถมองเห็นช่องว่างท้ายสตริงได้ ช่องว่างเกินก่อปัญหามาก ตอนเปรียบเทียบสตริง เช่น "July" ไม่เท่ากับ "July " (ช่องว่างท้าย) ตัวแรก 4 ตัวอักษร ตัวที่สอง 5 ตัวอักษร

มี 2 เทคนิคลบช่องว่างเกินใน Excel

ใช้ทั้ง 2 วิธีกับชุดข้อมูลนี้

ใช้ฟังก์ชัน TRIM

ใช้TRIMลบช่องว่างนำหน้าและท้าย ทุกช่องว่างหลายช่องจะกลายเป็นช่องเดียว

ขั้นตอน:

• เพิ่มคอลัมน์Updated Dataแสดงผล
• คลิกเซลล์D5
• พิมพ์ =TRIM เลือกเซลล์C5เป็นอาร์กิวเมนต์แรก ปิดวงเล็บ
• สูตรเป็น:

=TRIM(C5)

• กดEnter

• ลากแฮนด์ล์เติมไปเซลล์สุดท้าย

เห็นว่าช่องว่างไม่จำเป็นหายไป เหลือช่องว่างเดียวระหว่างคำ

ใช้คุณสมบัติ Find & Replace:

ส่วนนี้สาธิตใช้คำสั่ง Find & Replace ลบช่องว่างทั้งหมด

ขั้นตอน:

• เลือกข้อมูลที่ต้องการลบช่องว่างเกิน
• ไปแท็บHome
• จากคำสั่งEditingไปFind & Select
• เลือกReplaceจากรายการดรอปดาวน์

• เลือกReplace จะได้กล่องโต้ตอบ
• พิมพ์ช่องว่างในฟิลด์Find what
• ทิ้งReplace withว่าง
• กดReplace All

• หลังกดReplace All จะมีป๊อปอัปแสดงจำนวนการแทนที่
• กดOKบนป๊อปอัป
• กดCloseกล่องโต้ตอบ
• ได้ผลลัพธ์

6. ลบอักขระแปลก (Remove Strange Characters)

หลายครั้งหลังนำเข้าข้อมูลเข้าเวิร์กชีท Excel จะพบอักขระแปลก (บางครั้งพิมพ์ไม่ได้) ใช้ฟังก์ชัน CLEANลบอักขระที่พิมพ์ไม่ได้ทั้งหมดจากข้อความ หากข้อมูลอยู่ในเซลล์A2 ใช้:

=CLEAN(A2)

ฟังก์ชันCLEANอาจพลาดอักขระ Unicode ที่พิมพ์ไม่ได้บางตัว โปรแกรมลบอักขระ 32 ตัวแรกในรหัส ASCII 7-bit ดูระบบช่วยเหลือ Excel เพื่อลบอักขระ Unicode ที่พิมพ์ไม่ได้

7. แปลงค่า (Convert Values)

เทคนิคสำคัญอีกอย่างคือแปลงค่าในชุดข้อมูลจากหน่วยหนึ่งไปอีกหน่วย เช่น นำเข้าไฟล์ที่มีค่าเป็น fluid ounces (fl oz) ต้องแสดงเป็นมิลลิลิตร ฟังก์ชันCONVERTของ Excel ทำได้หลายประเภท

ฟังก์ชันนี้มีประโยชน์มาก จัดการหน่วยวัดทั่วไปในหมวด: น้ำหนัก/มวล ระยะทาง เวลา แรงดัน แรง พลังงาน พลัง ช चुंबคภาพ อุณหภูมิ ปริมาตร ของเหลว พื้นที่ บิตและไบต์ และความเร็ว

สาธิตด้วยชุดข้อมูลนี้ ใช้แปลงอุณหภูมิเป็นฟาเรนไฮต์

ขั้นตอน:

• เลือกเซลล์เอาต์พุตD5
• พิมพ์สูตร:

=CONVERT(C5,"C","F")

• กดEnter

• ลากแฮนด์ล์เติมไปจบคอลัมน์เพื่อแปลงค่าทั้งหมด

นี่คือวิธีแปลงค่าเป็นหน่วยอื่น สำหรับการแปลงหน่วยอื่นๆ ดูบทความนี้

8. ไฮไลท์ข้อผิดพลาด (Highlight Errors)

จากสูตร/สตริง/ค่าที่ไม่ถูกต้อง สเปรดชีตจะแสดงข้อผิดพลาด หากค้นหาทีละตัวจะใช้เวลามาก นี่คือเทคนิคง่ายหาและไฮไลท์ข้อผิดพลาด

สมมติมีชุดข้อมูลมีข้อผิดพลาดบางเซลล์ ไฮไลท์ด้วย Conditional Formatting และฟีเจอร์Go to Special

ดูสเปรดชีตต่อไปนี้

ขั้นตอน:

• เลือกทั้งชุดข้อมูล คลิกNew RuleจากตัวเลือกConditional Formatting

• ในประเภทกฎ เลือกFormat Only cells that contain แล้วเลือกErrorsจากรายการดรอปดาวน์

• คลิกOK

นี่คือผลลัพธ์สุดท้าย เห็นว่า Excel ไฮไลท์ค่าที่ผิดพลาด

นี่คือวิธีไฮไลท์ข้อผิดพลาดเป็นหนึ่งในเทคนิคทำความสะอาดข้อมูลใน Excel

9. รวมคอลัมน์ (Join Columns)

การรวมคอลัมน์เป็นเทคนิคที่อาจต้องใช้ขณะทำความสะอาดหรือจัดเรียงข้อมูล สาธิตด้วยชุดข้อมูลนี้

ขั้นตอน:

• เลือกเซลล์E5
• ใส่สูตร:

=B5&" "&D5&" "&C5

• กดEnter

• ลากแฮนด์ล์เติมไปจบคอลัมน์เพื่อคัดลอกสูตร

นี่คือวิธีรวม 2 คอลัมน์ขึ้นไปใน Excel

10. จัดเรียงคอลัมน์ใหม่ (Rearrange Columns)

บางครั้งต้องจัดเรียงคอลัมน์ในเวิร์กชีต หากคิดสักครู่ แก้ได้โดย: สร้างคอลัมน์ว่าง ลากคอลัมน์อื่นเข้าไป คอลัมน์ที่ลากจะเหลือช่องว่าง ต้องลบคอลัมน์นั้นหลังเสร็จ

นี่คือวิธีง่ายกว่าบนชุดข้อมูลนี้

ขั้นตอน:

• เลือกคอลัมน์ที่ต้องการย้ายโดยคลิกหัวคอลัมน์
• Cut คอลัมน์ โดยเมนูคลิกขวาหรือกดCtrl+X จะเห็นเส้นประขอบการเลือก

• คลิกขวาที่หัวคอลัมน์ก่อนหน้าที่ต้องการวางคอลัมน์ก่อนหน้า
• เลือกInsert Cut Cellsจากเมนูบริบท

• คอลัมน์ก่อนหน้าจะถูกแทรกที่นี่

นี่คือวิธาจัดเรียงคอลัมน์ใหม่เป็นส่วนหนึ่งของเทคนิคทำความสะอาดข้อมูลใน Excel

11. สลับลำดับแถวแบบสุ่ม (Randomize Rows)

ส่วนนี้อภิปรายการสุ่มลำดับแถว 雖然ไม่ค่อยมีประโยชน์ในกรณีส่วนใหญ่ แต่ยังเป็นทริกเครื่องมือถ้าอยากจัดเรียงชุดข้อมูลใหม่ หรือเปลี่ยนลำดับรายการผ่าน Excel

จะทำกับชุดข้อมูลนี้

ขั้นตอน:

• สร้างคอลัมน์ใหม่หลังคอลัมน์B คลิกตัวอักษรหัวคอลัมน์C ทั้งคอลัมน์Cจะถูกเลือก
• คลิกขวาเลือกคำสั่ง Insert จะได้คอลัมน์ใหม่

• ตั้งชื่อคอลัมน์ใหม่ (เช่น Random Number) เลือกเซลล์แรก (C5) ใส่สูตร:

=RAND()

• กดENTER จะได้ตัวเลขสุ่มในเซลล์C5 ค่าน้อยกว่า 1

• เลือกเซลล์อีกครั้ง ลากแฮนด์ล์เติมไปจบคอลัมน์เพื่อคัดลอกสูตร

• ขณะเลือกเซลล์C5ไปแท็บHome
• ไปกลุ่มEditingคลิกSort & Filter
• เลือกSort Smallest to Largestจากเมนูดรอปดาวน์

• ทันทีที่คลิก แถวจะสุ่มตัวเอง สังเกตว่าไม่ใช่แค่คอลัมน์C แต่ทุกคอลัมน์ก็สุ่มเปลี่ยนตามแถว

นี่คือขั้นตอนเมื่อมีชุดข้อมูลหลายคอลัมน์ และต้องการสุ่มแถว ให้คอลัมน์ทั้งหมดตามแถวสุ่มค่าไปด้วย

12. ดึงชื่อไฟล์จาก URL (Extract File Name from URL)

บางครั้งมีรายการ URL ต้องการดึงเฉพาะชื่อไฟล์ ทำได้ดังนี้ สมมติเซลล์B5มี URL: https://example.com/assets/images/horse.jpg

ต้องใช้สูตรที่มีฟังก์ชันหลายตัว: RIGHT, LEN, FIND, SUBSTITUTE อย่ากังวล มีคำอธิบายด้านล่าง

ขั้นตอน:

• เลือกเซลล์ที่ต้องการวางชื่อไฟล์ กรณีนี้คือC5
• ใส่สูตร:

=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))))

• กดEnter

🔎 การแยกวิเคราะห์สูตร

👉 SUBSTITUTE(B5,"/","") ลบ"/"ทั้งหมดจาก URL คืนค่า http:www.example.comassetsimageshorse.jpg

👉 LEN(SUBSTITUTE(B5,"/","")) คืนความยาวสตริงก่อนหน้า

👉 LEN(B5) คืนความยาวสตริงในเซลล์B5ซึ่งเป็น 46 ตรงนี้

👉 LEN(B5)-LEN(SUBSTITUTE(B5,"/",""))) บ่งชี้ความต่างของค่าความยาวทั้งสอง ซึ่งบ่งชี้จำนวน "/" ที่ถูกลบ

👉 SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/",""))) แทนที่ "/" ทั้งหมดในเซลล์B5ด้วย "*" ด้วยหมายเลขอินสแตนซ์จากผลฟังก์ชันก่อนหน้า

👉 FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))) หาตำแหน่ง "*" ในสตริงจนถึงตอนนี้

👉 LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/","")))) คือความต่างระหว่างความยาวสตริงต้นฉบับกับตำแหน่งก่อนหน้า

👉 สุดท้าย RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5,"/","*",LEN(B5)-LEN(SUBSTITUTE(B5,"/",""))))) ดึงจำนวนตัวอักษรนั้นจากด้านขวาของเซลล์

13. จับคู่ข้อความในรายการ (Match Text in List)

ดูรูปต่อไปนี้ เราจะหาว่าใครลาออกจากด้านซ้าย มีรายการหมายเลขผู้ลาออกทางขวา

ข้างต้นเป็นตัวอย่างง่าย ข้อมูลอยู่ในช่วงB5:D133 เป้าหมายระบุแถวในโซนข้อมูลที่ปรากฏในรายการResigned Membersคอลัมน์G สามารถลบแถวที่ไม่จำเป็นได้ภายหลัง ใช้ผสมผสานฟังก์ชันIFและCOUNTIF

ขั้นตอน:

• เลือกเซลล์E5
• ใส่สูตร:

=IF(COUNTIF($G$5:$G$25,C5),"Resigned","")

• กดEnter

• ดับเบิลคลิกแฮนด์ล์เติมมุมขวาล่างของขอบเซลล์ หรือลากก็ได้ แต่ข้อมูลเยอะ จึงเลือกวิธีก่อนหน้า
• ทำเสร็จ สเปรดชีตจะเป็นแบบนี้

นี่คือวิธีจับคู่ข้อความเป็นส่วนหนึ่งของเทคนิควิเคราะห์ข้อมูลใน Excel

14. แปลงข้อมูลแนวตั้งเป็นแนวนอน (Change Vertical Data to Horizontal)

การ Transpose หรือเปลี่ยนแนวตั้งเป็นแนวนอน และในทางกลับกัน เป็นเทคนิคทั่วไปในการวิเคราะห์ข้อมูล เราจะพูดถึงการเปลี่ยนข้อมูลแนวตั้งเป็นแนวนอนในซีรีส์เทคนิคทำความสะอาดข้อมูลใน Excel

ใช้ชุดข้อมูลนี้

ใช้ตัวเลือก Paste Special

วิธีง่ายที่สุดเปลี่ยนคอลัมน์แนวตั้งเป็นแถวแนวนอนคือใช้ตัวเลือกPaste Specialของ Excel ยังคงรูปแบบเดิมพอดี ไม่ต้องจัดรูปแบบใหม่ ทำตามขั้นตอน:

• เลือกช่วงที่ต้องการเปลี่ยนเป็นแถวแนวนอน ที่นี่เลือกช่วงB4:C10
• กดCtrl+Cคัดลอกช่วง
• เลือกเซลล์ที่ต้องการวางแนวนอน กรณีนี้เลือกเซลล์B12

จะเปลี่ยนคอลัมน์แนวตั้งเป็นแถวแนวนอนในสเปรดชีต

ใช้ฟังก์ชัน TRANSPOSE:

ใช้ฟังก์ชัน Excel แปลงคอลัมน์แนวตั้งเป็นแถวแนวนอน ใช้TRANSPOSE ข้อดีคือได้การอัปเดตแบบไดนามิกในแถวแนวนอน หากเปลี่ยนแปลงข้อมูลหลัก แต่แถวแนวนอนจะไม่มีรูปแบบเหมือนคอลัมน์แนวตั้ง ต้องจัดรูปแบบหลังใช้วิธีนี้

ขั้นตอน:

• เลือกเซลล์ที่ต้องการวางสูตร
• ใส่สูตร:

=TRANSPOSE(B4:C10)

• กดEnter

นี่คือวิธีเปลี่ยนข้อมูลแนวตั้งเป็นแนวนอนด้วยฟังก์ชันTRANSPOSE

15. เติมเซลล์ว่าง (Fill Blank Cells)

หนึ่งในเทคนิคทำความสะอาดข้อมูลสำคัญที่สุดคือเติมค่าว่าง ไม่ว่าจะเป็นค่าเฉลี่ยในตัวเลข หรือค่าจากแถวก่อนหน้า/ถัดไป

บางครั้งเราทิ้งเซลล์ว่างไว้เพื่อบ่งชี้ว่าจะเป็นตัวแรกที่เติม ดูชุดข้อมูลเพื่อเข้าใจ แต่บางครั้งก่อปัญหา เช่น ตอนกรองหรือจัดเรียงข้อมูล ดังนั้นเติมให้หมดดีกว่า มีวิธีง่ายๆ

นี่คือชุดข้อมูลของเรา

เติมได้ด้วย 2 วิธีง่าย

ใช้คำสั่ง Fill

ถ้าข้อมูลน้อย สามารถป้อนค่าที่หายไปจากด้านบนด้วยมือ หรือใช้คำสั่งนี้ ทำตามขั้นตอน:

• เลือกเซลล์B6
• ไปHome
• จากกลุ่มEditingคลิกFillแล้วDown

• ชุดข้อมูลจะเป็นแบบนี้

• ทำซ้ำกับเซลล์ว่างถัดไป เพื่อเติมเซลล์ว่างโดยดึงค่าจากด้านบน

แทนที่จะเลือกทีละตัว ทำได้ครั้งเดียว: เลือกเซลล์ที่เติมแล้ว ดับเบิลคลิกไอคอนบวก (+)ของเซลล์

ทันที เซลล์ว่างทั้งหมดจะเติมค่าเดียวกัน

ดูผลลัพธ์สุดท้าย

ใช้แป้นพิมพ์ลัด:

ทำได้เหมือนกันด้วยแป้นลัด Ctrl+D เลือกเซลล์ว่าง กดแป้นลัด จะเติมค่าจากเซลล์ก่อนหน้าในคอลัมน์เดียวกัน ทำตามขั้นตอน:

• เลือกเซลล์B6
• กดCtrl+D

• เลือกเซลล์B9กดCtrl+Dได้ผลดังภาพ

• เติมค่าว่างทั้งหมดโดยเลือกและกดแป้นลัด

16. ตรวจสอบการสะกด (Check Spelling)

ใช้ Word หรือโปรเซสเซอร์คำอื่น มักใช้ตรวจสะกดบ่อย การสะกดผิดในเอกสารข้อความน่าสมปรกाश แต่ใน Excel การสะกดผิดก็ก่อปัญหาร้ายได้ เช่น ถ้าจัดตารางข้อมูลตามเดือน ชื่อเดือนสะกดผิดจะทำให้ปีมี 13 เดือน

นี่คือชุดข้อมูลสาธิต

จะแก้ไขช่วงC5:C7 ทำตามขั้นตอน:

ขั้นตอน:

• เลือกช่วงC5:C7
• ไปแท็บReviewเลือกSpellingจากProofing หรือข้ามขั้นตอนนี้กดF7

• เลือกคำแนะนำที่ถูกต้อง คลิกChangeเพื่อเปลี่ยนค่าเซลล์ ข้อผิดพลาดมาในลำดับที่เลือก

• จะแก้ข้อผิดพลาดแรกในชุดข้อมูล

• ทำซ้ำกับข้อผิดพลาดทั้งหมด จะได้การสะกดที่ถูกต้อง

17. แทนที่หรือลบข้อความ (Replace or Remove Text)

การลบและแทนที่ข้อมูล โดยเฉพาะค่าผิดปกติ (outliers) เป็นงานสำคัญในกระบวนการทำความสะอาดข้อมูล ดังนั้นในส่วนนี้จะสาธิตการแทนที่และลบข้อความใน Excel ทำได้ 3 วิธี

ทุกวิธีใช้ชุดข้อมูลนี้

ใช้ฟีเจอร์ Find & Replace:

Excel มีฟีเจอร์โดยตรงช่วยหา แทนที่ และลบค่าบางค่าจากอีกค่า ทำตามขั้นตอน:

• เลือกช่วงที่ต้องการแก้ไข กรณีนี้C5:C13
• ไปHome
• จากกลุ่มEditingเลือกFind & Replace
• เลือกReplaceจากดรอปดาวน์

• ไปแท็บReplaceในกล่องป๊อปอัป
• ใส่ค่าที่ต้องการแทนที่ในFind what และค่าที่จะแทนที่ในReplace with

• กดReplace Allเพื่อแทนที่ทั้งหมดในชุดข้อมูล

นี่คือผลลัพธ์หลังลบขีดกลาง (hyphens) ทั้งหมด

ใช้ Flash Fill:

ฟีเจอร์อื่นสำหรับแทนที่/ลบค่าในการทำความสะอาดข้อมูล คือใช้Flash Fill สำหรับชุดข้อมูลเดียวกัน ทำตามขั้นตอน:

• พิมพ์ผลลัพธ์สุดท้ายหลังลบ/แทนที่ค่าที่ต้องการในเซลล์แรก เราแทนที่ขีดกลางตัวที่สองด้วย colon

• เริ่มพิมพ์ตัวที่สองด้วยมือ ทันทีที่เริ่มพิมพ์ Excel จะจดจำรูปแบบและเสนอผลลัพธ์ต่อไป

หมายเหตุ

หากคำแนะนำไม่ขึ้นในตัวที่สอง ลองทำด้วยมือ บางครั้งเสนอที่ตัวที่ 3-4 หากรูปแบบไม่ชัดเจนสำหรับ Excel

• กดEnter

ดังนั้น คุณสามารถแทนที่/ลบค่าได้ด้วยฟีเจอร์นifty นี้ขณะทำความสะอาดข้อมูล

ใช้ฟังก์ชัน SUBSTITUTE:

อีกวิธีแทนที่/ลบค่าใน Excel คือใช้ฟังก์ชัน SUBSTITUTE ฟังก์ชันนี้รับ 3 ค่า: สตริง, ค่าที่จะแทนที่, ค่าที่จะแทนที่ด้วย ทำตามขั้นตอน:

• เลือกเซลล์C5
• ใส่สูตร:

=SUBSTITUTE(B5,"-",":",2)

• กดEnter

• เลือกเซลล์อีกครั้ง ลากแฮนด์ล์เติมไปจบคอลัมน์

18. เพิ่มข้อความในเซลล์ (Add Text to Cells)

การเพิ่มข้อความไม่ใช่แค่เทคนิคทำความสะอาดข้อมูล แต่วัน반ในการใช้งาน Excel ทั่วไป เพิ่มข้อความในเซลล์ได้หลายวิธี สาธิตด้วยชุดข้อมูลนี้

ใช้วิธีใดวิธีหนึ่งด้านล่างเพื่อเพิ่มข้อความในเซลล์

ใช้สัญลักษณ์ Ampersand (&):

สัญลักษณ์&ในสูตรทำงานเป็นตัวเชื่อมสตริง เชื่อมสตริงก่อนหน้าและถัดไป ทำตามขั้นตอน:

• เลือกเซลล์C5
• ใส่สูตร:

=B5&".mp3"

• กดEnter

• ลากแฮนด์ล์เติมไปจบคอลัมน์

ใช้ฟีเจอร์ Flash Fill:

ทำตามขั้นตอนเพื่อเพิ่มข้อความด้วย Flash Fill ง่ายๆ

• พิมพ์ค่าที่ต้องการในเซลล์C5ด้วยมือ

• เริ่มเติมตัวที่สองในชุดข้อมูล ทันทีที่เริ่ม Excel จะเสนอรูปแบบ

• ทันทีที่คำแนะนำปรากฏ กดEnter

นี่คืออีกวิธีเพิ่มข้อความใน Excel

ใช้ฟังก์ชัน CONCATENATE:

ฟังก์ชัน CONCATENATEใน Excel ตามชื่อ รวมสตริง 2 ตัวขึ้นไป รับสตริงเหล่านั้นเป็นอาร์กิวเมนต์ ทำงานเหมือนวิธี Ampersand ทำตามขั้นตอน:

• เลือกเซลล์C5
• ใส่สูตร:

=CONCATENATE(B5,".mp3")

• กดEnter

• เลือกเซลล์อีกครั้ง ลากแฮนด์ล์เติมไปจบคอลัมน์

19. แก้เครื่องหมายลบนำหน้าท้าย (Fix Trailing Minus Sign)

บางครั้งนำข้อมูลจากแหล่งอื่น ค่าลบมีเครื่องหมายลบท้าย ดูรูปด้านล่าง

Excel ไม่นับค่าที่มีเครื่องหมายลบท้ายเป็นตัวเลข แต่ถือว่าเป็นข้อความ อาจก่อปัญหา ไม่ใช่แค่ทำความสะอาดข้อมูล แต่การใช้งานทั่วไปอย่างใช้สูตร ดังนั้นต้องแก้ไขให้เป็นรูปแบบตัวเลขลบของ Excel

ขั้นตอน:

• เลือกช่วงที่ต้องการแปลง เราคัดลอกไว้เปรียบเทียบกับก่อนหน้า

• ไปแท็บDataบนริบบอน
• เลือกText to Columnsจากกลุ่มData Tools

• กดFinishทันทีในวิซาร์ด

จะแปลงค่าลบที่มีเครื่องหมายลบท้ายทั้งหมดเป็นรูปแบบเครื่องหมายลบของ Excel อัตโนมัติ

ขั้นตอนนี้ทำงานได้เพราะมีการตั้งค่าเริ่มต้นในกล่องโต้ตอบAdvanced Text Import Settings

สรุป

ข้างต้นคือเทคนิคทำความสะอาดข้อมูลใน Excel ที่มีประโยชน์ที่สุดสำหรับการวิเคราะห์ข้อมูล หวังว่าคุณจะใช้เทคนิคเหล่านี้ได้สะดวก หากมีคำถามหรือข้อเสนอแนะ ให้ความคิดเห็นด้านล่าง

สำหรับคู่มือเพิ่มเติม เยี่ยมชมExceldemy.com