การค้นหาความแตกต่างใน Excel – อธิบาย 5 เทคนิคง่ายๆ

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

ข่าวดี? Excelมาพร้อมกับเครื่องมือและเทคนิคมากมายที่จะช่วยคุณค้นหาและแก้ไขปัญหาเหล่านี้ ในบทความนี้ คุณจะได้เรียนรู้เกี่ยวกับวิธีที่มีประสิทธิภาพมากที่สุด ซึ่งรวมถึงการกรอง การจัดรูปแบบตามเงื่อนไข และการใช้สูตรขั้นสูง

การผสมผสานเทคนิคเหล่านี้เข้ากับเวิร์กโฟลว์ประจำวันของคุณ คุณจะมีประสิทธิภาพมากขึ้นในการจัดการและวิเคราะห์ข้อมูลของคุณ ในที่สุดนำไปสู่การตัดสินใจที่ดีขึ้นและผลลัพธ์ที่ดีขึ้นสำหรับโครงการของคุณ

สารบัญ

• ทำความเข้าใจเกี่ยวกับความแตกต่างใน Excel
• 5 วิธีในการค้นหาความแตกต่างใน Excel
• #1 – การค้นหาความแตกต่างผ่านการตรวจสอบด้วยตนเองใน Excel
• #2 – การใช้ตัวกรองใน Excel เพื่อตรวจหาและแก้ไขข้อมูลที่ไม่สอดคล้องกัน
• 3# – การเน้นความแตกต่างด้วยการจัดรูปแบบตามเงื่อนไข
• #4 – ค้นหาความแตกต่างด้วยฟังก์ชัน Excel ขั้นสูง
  • ฟังก์ชัน IF และ IS
  • VLOOKUP, HLOOKUP และ XLOOKUP
  • จับคู่
• #5 – การใช้ Add-in ของ Excel เพื่อตรวจหาความคลาดเคลื่อน
• คุณจะสร้างกฎการตรวจสอบข้อมูลเพื่อป้องกันความคลาดเคลื่อนได้อย่างไร
• คำพูดสุดท้ายของเรา

ทำความเข้าใจเกี่ยวกับความแตกต่างใน Excel

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

ลองดูข้อมูลต่อไปนี้ คุณจะสังเกตเห็นว่าเพศของWalter WhiteและWayne Cenaสะกดผิด – Mael แทนที่จะเป็น Male อันที่จริง เป็นเรื่องง่ายที่จะสังเกตและแก้ไขข้อผิดพลาดในสถานการณ์นี้ แต่นั่นไม่ใช่กรณีของสเปรดชีตที่มีแถว & คอลัมน์หลายพันแถว

เมื่อต้องการค้นหาและจัดการความคลาดเคลื่อนเหล่านี้ คุณสามารถใช้ คุณลักษณะของ Excel ต่อไปนี้ :

• ตัวกรอง:ใช้ตัวกรองกับคอลัมน์เฉพาะเพื่อดูค่าที่ไม่ซ้ำกันและระบุข้อมูลที่ไม่สอดคล้องกันได้อย่างรวดเร็ว
• การจัดรูปแบบตามเงื่อนไข:ใช้กฎการจัดรูปแบบเพื่อเน้นเซลล์ที่ตรงตามเกณฑ์เฉพาะหรือข้อมูลที่ไม่ตรงกันระหว่างคอลัมน์
• ฟังก์ชัน Excel ขั้นสูง:ใช้ฟังก์ชันต่างๆ เช่น MATCH และVLOOKUPเพื่อเปรียบเทียบชุดข้อมูลและระบุความแตกต่างระหว่างชุดข้อมูล

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

5 วิธีในการค้นหาความแตกต่างใน Excel

มี 5 วิธีหลักที่เราต้องการจัดการกับความแตกต่างใน Excel:

1. ทบทวนด้วยตนเอง
2. การกรอง
3. การจัดรูปแบบตามเงื่อนไข
4. ฟังก์ชันขั้นสูงของ Excel
5. โปรแกรมเสริมของ Excel

มาดูแต่ละข้อกันเพื่อที่คุณจะได้หาวิธีที่ดีที่สุดที่เหมาะกับคุณ

#1 – การค้นหาความแตกต่างผ่านการตรวจสอบด้วยตนเองใน Excel

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

ต่อไปนี้เป็นขั้นตอนบางส่วนที่คุณสามารถปฏิบัติตามเพื่อทำการตรวจสอบด้วยตนเองได้:

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

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

#2 – การใช้ตัวกรองใน Excel เพื่อตรวจหาและแก้ไขข้อมูลที่ไม่สอดคล้องกัน

วิธีที่ง่ายที่สุดวิธีหนึ่งในการตรวจจับข้อมูลที่ไม่สอดคล้อง กันคือการใช้ตัวกรอง ของ Excel

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

1. เลือกSort & Filterในแท็บHome

2. เลือกตัวกรอง – ตรวจสอบให้แน่ใจว่าเคอร์เซอร์ของคุณอยู่ในช่วงของข้อมูล

3. คลิกลูกศรรายการแบบเลื่อนลงในคอลัมน์ที่มีข้อมูลไม่สอดคล้องกัน

4. ตอนนี้ ยกเลิกการเลือกข้อมูลการจับคู่ที่ถูกต้องทั้งหมด ในกรณี ของเรา เป็นผู้ชาย จากนั้นคลิกตกลงเพื่อบันทึกการเปลี่ยนแปลง

5. แผ่นงาน Excel ของคุณจะแสดงเฉพาะแถวที่มีค่าไม่ถูกต้อง หากต้องการแก้ไข ให้คลิกค้นหาและเลือกในแท็บหน้าแรก

6. คลิกแทนที่เมื่อรายการแบบหล่นลงปรากฏขึ้น

7. พิมพ์ค่าที่ไม่ถูกต้องในFind whatและพิมพ์ค่าที่ถูกต้องในReplace with จากนั้นคลิกแทนที่ทั้งหมด

8. กล่องโต้ตอบจะปรากฏขึ้นเพื่อยืนยันการเปลี่ยนแปลง คลิกตกลงเพื่อดำเนินการต่อ

3# – การเน้นความแตกต่างด้วยการจัดรูปแบบตามเงื่อนไข

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

คุณสามารถเข้าถึงกฎต่างๆ เช่น เซลล์ไฮไลท์, บน/ล่าง, แถบข้อมูล, สเกลสี & ชุดเหล็ก นอกจากนี้ คุณสามารถสร้างกฎใหม่และจัดการหรือล้างกฎก่อนหน้านี้ได้

เราจะใช้ตารางตัวอย่างเพื่อแสดงพลังของการจัดรูปแบบตามเงื่อนไข:

1. เลือกตารางหรือแถวและคอลัมน์ที่ต้องการ เรากำลังเลือกคอลัมน์ A (บริษัท) และคอลัมน์ B (ประเทศ) จากนั้นเลือกการจัดรูปแบบตามเงื่อนไขบนแท็บหน้าแรก

2. เลือกประเภทกฎแล้วเลือกกฎ เรากำลังเลือกกฎเซลล์ไฮไลท์และค่าที่ซ้ำกัน...

3. ตอนนี้เราต้องเลือกว่าจะเน้น ค่า ซ้ำหรือไม่ซ้ำและเติมสีและแบบอักษรเพื่อจัดรูปแบบเซลล์ เมื่อเสร็จแล้ว ให้คลิกตกลง

4. ตอนนี้ค่าที่ซ้ำกันทั้งหมดจะถูกเน้นด้วยการเติมสีแดงอ่อนด้วยข้อความสีแดงเข้ม

#4 – ค้นหาความแตกต่างด้วยฟังก์ชัน Excel ขั้นสูง

ฟังก์ชัน IF และ IS

ในการเปรียบเทียบเซลล์และระบุความแตกต่าง คุณสามารถใช้ฟังก์ชัน IF และ IS

ตัวอย่างเช่น เราได้ใช้สูตร=IF(A2=B2,"MATCH","NOT MATCHED")เพื่อเปรียบเทียบเซลล์ A2 และ B2 และเราได้ลากสูตรเพื่อใช้กับเซลล์อื่น ดังนั้นเซลล์ที่เหมือนกันจะส่งคืนMATCHมิฉะนั้น จะ ไม่MATCHED

คุณสามารถรวม IF กับฟังก์ชัน IS เช่น ISNUMBER หรือ ISTEXT เพื่อตรวจสอบประเภทข้อมูลเฉพาะ ตัวอย่างเช่น สูตร=IF(ISNUMBER(B2), "Number", "NaN")จะส่งกลับNumberในคอลัมน์ c ถ้า B2 เป็นตัวเลขและไม่ใช่NaN

VLOOKUP, HLOOKUP และ XLOOKUP

ฟังก์ชัน VLOOKUP (แนวตั้ง) และ HLOOKUP (แนวนอน) มีประโยชน์เมื่อเปรียบเทียบค่าในคอลัมน์หรือแถวที่แตกต่างกันของสองตาราง ในการดำเนินการ ให้ใช้สูตรต่อไปนี้: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) หรือ=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

แทนที่lookup_valueด้วยค่าที่คุณต้องการค้นหาในตารางอื่น, table_arrayด้วยช่วงของเซลล์ในตารางที่สอง, col_index_numหรือrow_index_numด้วยหมายเลขดัชนีของคอลัมน์หรือแถวที่คุณต้องการส่งกลับค่า และ[range_lookup]ด้วยFALSEสำหรับการจับคู่แบบตรงทั้งหมด หรือTRUEสำหรับการจับคู่โดยประมาณ

ในบทช่วยสอนต่อไปนี้ เรากำลังใช้ฟังก์ชัน VLOOKUP บนคอลัมน์ E เพื่อเปรียบเทียบชื่อพนักงานในคอลัมน์ A และคอลัมน์ D เพื่อดึงเงินเดือนออกจากคอลัมน์ B หากพบว่าตรงกัน

XLOOKUP เป็นเวอร์ชันรีเฟรชของ VLOOKUP และ HLOOKUP ที่มีอยู่ใน Excel 2021 และ Excel 365 สูตรสำหรับ XLOOKUP คือ=XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found]):

แทนที่lookup_valueด้วยค่าที่คุณต้องการค้นหาในอีกตารางหนึ่งlookup_arrayด้วยช่วงของเซลล์ที่จะค้นหาในตารางที่สองreturn_arrayด้วยอาร์เรย์ที่คุณต้องการส่งคืนค่า และ[if_nout_found]ด้วยค่าข้อความหากไม่ตรงกัน จะพบค่า

นี่คือตัวอย่างรวดเร็วของ XLOOKUP ที่มีตารางเดียวกัน:

จับคู่

นอกจากนี้ยังสามารถใช้ฟังก์ชัน MATCH เพื่อเปรียบเทียบสองรายการเพื่อหาค่าความคลาดเคลื่อนได้อีกด้วย

ฟังก์ชัน MATCH =MATCH(lookup_value, lookup_array, [match_type])ค้นหารายการที่ระบุในช่วงหนึ่งและส่งกลับตำแหน่งสัมพัทธ์ของรายการภายในช่วงนั้น

ที่นี่ เรากำลังหาตำแหน่งของแตงโมในอาร์เรย์A2:A8โดยใช้ MATCH เรากำลังใช้match_typeเป็น0 เพื่อค้นหาค่า แรกที่เท่ากับlookup_value ทุกประการ

#5 – การใช้ Add-in ของ Excel เพื่อตรวจหาความคลาดเคลื่อน

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

ขั้นแรก ให้ลองใช้Add-in Inquire ของสเปรดชีต วิธีนี้สามารถช่วยคุณเปรียบเทียบสมุดงานสองเล่มและเน้นความแตกต่างทีละเซลล์ หากต้องการเปิดใช้ Add-in Inquire ของสเปรดชีต:

1. คลิกแท็บไฟล์

2. เลือกตัวเลือกแล้วคลิกประเภทAdd-Ins

3. ใน ช่อง ManageเลือกCOM Add-inแล้วคลิกGo

4. ทำเครื่องหมาย ในช่อง สอบถามสเปรดชีตแล้วคลิกตกลง

5. เมื่อเปิดใช้งาน ไปที่ แท็บ สอบถามเพื่อใช้คำสั่งเปรียบเทียบไฟล์

เครื่องมือที่มีประโยชน์อีกอย่างหนึ่งสำหรับการตรวจจับความคลาดเคลื่อนคือAnalysis ToolPak Add-in นี้มีฟังก์ชันทางสถิติขั้นสูงและเครื่องมือวิเคราะห์ข้อมูล ซึ่งจะเป็นประโยชน์ในการวิเคราะห์ความคลาดเคลื่อน

หากต้องการเปิดใช้งาน Analysis ToolPak ให้ทำตามขั้นตอนเดียวกับการเปิดใช้งาน Add-in ของ Spreadsheet Inquire แต่เลือกAdd-in ของ Excelใน ช่อง Manageและเลือกช่องAnalysis ToolPak

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

คุณจะสร้างกฎการตรวจสอบข้อมูลเพื่อป้องกันความคลาดเคลื่อนได้อย่างไร

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

1. เลือกเซลล์ที่คุณต้องการจำกัด ซึ่งอาจเป็นเซลล์เดียว ช่วงของเซลล์ หรือทั้งคอลัมน์ก็ได้

2. ไปที่ แท็บ Dataในแถบเครื่องมือ แล้วคลิก ปุ่ม Data Validation (ทำเครื่องหมายด้วยช่องแนวนอนสองช่อง เครื่องหมายถูกสีเขียว และวงกลมกากบาทสีแดง)

3. ในหน้าต่าง Data Validation ตรวจสอบให้แน่ใจว่าคุณอยู่ในแท็บSettings ที่นี่ คุณจะมีโอกาสกำหนดเกณฑ์การตรวจสอบตามความต้องการของคุณ

มีเกณฑ์การตรวจสอบหลายประเภทใน Excel ซึ่งบางประเภทรวมถึง:

• จำนวนเต็ม – อนุญาตเฉพาะจำนวนเต็มในช่วงที่กำหนดเท่านั้น
• ทศนิยม – อนุญาตเฉพาะตัวเลขทศนิยมภายในช่วงที่กำหนด
• รายการ – จำกัดอินพุตให้เป็นรายการค่าที่ยอมรับได้ที่กำหนดไว้ล่วงหน้า
• วันที่ – ต้องการวันที่ภายในช่วงวันที่ที่ระบุ
• เวลา – ยอมรับเฉพาะเวลาภายในช่วงเวลาหนึ่งเท่านั้น
• ความยาวข้อความ – บังคับใช้ข้อจำกัดเกี่ยวกับความยาวของข้อความที่ป้อน
• กำหนดเอง – ช่วยให้คุณสร้างกฎการตรวจสอบที่กำหนดเองโดยใช้สูตร Excel

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

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

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

คำพูดสุดท้ายของเรา

ในบทความนี้ คุณได้เรียนรู้วิธีการต่างๆ ในการค้นหาและแก้ไขความคลาดเคลื่อนในข้อมูล Excel ของคุณ เรามาสรุปกลยุทธ์หลักเหล่านี้กัน:

• การใช้ตัวกรองแบบเลื่อนลงในคอลัมน์เพื่อระบุค่าที่ไม่ซ้ำและข้อมูลที่ไม่สอดคล้องกัน
• ใช้การจัดรูปแบบตามเงื่อนไขในเซลล์ที่เลือกเพื่อเน้นความแตกต่างโดยใช้เทมเพลตกฎมากมาย
• การใช้ฟังก์ชันขั้นสูงของ Excel เช่นMATCH , VLOOKUP , XLOOKUPฯลฯ เพื่อตรวจสอบข้อมูลในรายการหรือตารางสองรายการ
• การใช้Add-in ของ Excelเพื่อตรวจหาความคลาดเคลื่อน
• การตั้ง ค่ากฎ การตรวจสอบข้อมูลเพื่อป้องกันข้อผิดพลาดในข้อมูล

ด้วยเทคนิคเหล่านี้ในชุดทักษะ Excel ข้อมูลของคุณจะแม่นยำและน่าเชื่อถือมากขึ้น

อย่าลืมฝึกฝนวิธีการเหล่านี้เป็นประจำเพื่อให้ตรวจจับความคลาดเคลื่อนในสเปรดชีตได้อย่างมีประสิทธิภาพมากขึ้น

ฝึกฝนทักษะของคุณอย่างต่อเนื่องโดยสมัครเข้าร่วม ของเรา และสำรวจฟีเจอร์และฟังก์ชันใหม่ๆ ต่อไปเพื่อให้เชี่ยวชาญยิ่งขึ้นใน Excel