สูตร Excel ตรวจ สอบ ข้อมูล ตรงกัน

เปรียบเทียบข้อมูล 2 ลิสต์ หรือ เปรียบเทียบ 2 คอลัมน์ หรือ 2 ตาราง ว่ามีข้อความใดที่ตรงกันหรือรายการใดไม่ตรงกัน ซึ่งงานในลักษณะนี้ ตรวจสอบได้หลายวิธีอยู่เหมือนกัน DataRevol.com จะแสดงให้เห็นวิธีการต่าง ๆ เท่าที่พอจะนึกออก

• โจทย์
• Clean ข้อมูลก่อนทำงาน
  • Find and Replace
  • Substitute
• เปรียบเทียบข้อมูล 2 ลิสต์ โดย lookup
  • เปรียบเทียบข้อมูล 2 ลิสต์ โดยใช้ Format
  • เปรียบเทียบข้อมูล 2 ลิสต์ โดยใช้ CountIF

โจทย์

จะมีรายการวัสดุอยู่ 2 รายการ เราอยากจะรู้ว่า ทั้งสองรายการนี้ มีอะไรที่เหมือนกันบ้าง เช่น List A มีรายการที่ชื่อ “ไม้แบบ” ใน List B ก็มีรายการที่ชื่อ “ไม้แบบ” อยากรู้ว่า อะไรที่ตรงกัน อะไรที่อันหนึ่งมี แต่อีกอันหนึ่งไม่มี อย่างที่เห็น เนื่องจากจำนวนรายการในลิสต์ไม่เท่ากัน อย่าง “ไม้แบบ” จากทั้งสองลิสต์ก็อยู่คนละแถวกัน เพราะจำนวนวัสดุในรายการไม่เท่ากัน จะเอามาเทียบง่าย ๆ โดยใช้สูตร = ให้ออกมาเป็น true หรือ fault ก็ไม่ได้

วิธีการแก้ปัญหา มีหลายแบบ เลือกกันตามถนัด โดยส่วนตัวแล้วถ้าเจออะไรแบบนี้ะทำใน Access เพราะรู้สึกว่ามันง่ายดี (เพราะมันออกแบบมาสำหรับจัดการเรื่องตารางและข้อมูลอยู่แล้ว) แต่การทำใน Excel ก็ง่ายไม่แพ้กัน อย่างที่จะแสดงให้เห็นด้านล่างนี้

Clean ข้อมูลก่อนทำงาน

ก่อนอื่น เพื่อป้องกันความผิดพลาดในการบันทึก เช่นเคาะเว้นวรรค หรือไม่เคาะวรรค ซึ่งจะทำให้ Excel คิดว่าเป็นคนละข้อมูลกัน ให้จัดการเรื่องนี้ก่อน โดยจะใช้ Find and Replace หรือจะใช้ฟังก์ชัน Substitute ก็ตามสะดวก

Find and Replace

วิธี Find and Replace ก็คือ คลิกเลือก Column ที่ต้องการทั้งหมด กด Ctrl+H เพื่อเรียกหน้าต่างใช้งานมา ช่อง find what เคาะ space หนึ่งที เพื่อให้รู้ว่าจะหา space และในช่อง Replace with ให้ปล่อยไว้เฉย ๆ อย่าไปยุ่งอะไรกับมัน จากนั้น กด replace all เพื่อกำจัด space ทั้งหลาย

Substitute

วิธีใช้ Substitute สมมติว่าข้อความที่ต้องการกำจัดวรรคออก อยู่ใน A1 ก็ใช้สูตร

=SUBSTITUTE(A1,” “,””)

หลักการแบบเดียวกับ Find and Replace คือแทนที่ space ว่าง ๆ ด้วยไม่ให้มี space

(ถ้าจำเป็นต้องเก็บรูปแบบการพิมพ์ในรูปแบบเดิม ก็ควร copy มาอีกคอลัมน์หนึ่ง หรือใช้ Substitute)

วิธีนี้ จะช่วยได้ในกรณีที่มีปัญหาเรื่อง เคาะวรรค ไม่เคาะวรรค หรือ มี – คั่นกลางอย่างไม่ควรจะมี ตั้งสมมติฐานว่า ชื่อรายการวัสดุต่าง ๆ สะกดเหมือนกัน ไม่ได้มีปัญหาเรื่องการพิมพ์ชื่อวัสดุ อย่างเช่น “ตะปู 2 นิ้ว” “ตะปู2นิ้ว” อันนี้จะแก้ปัญหาได้ แต่ถ้าพิมพ์มาเป็น “ตะปูสองนิ้ว” อะไรแบบนี้จะทำให้เกิด error เหมือนเดิม เพราะ Excel คงยังไม่ฉลาดพอจะรู้ว่า “2” ก็คือ “สอง”

เมื่อเตรียมข้อมูลเรียบร้อยแล้วก็เข้าสู่ขั้นตอนการเปรียบเทียบ

เปรียบเทียบข้อมูล 2 ลิสต์ โดย lookup

แนวคิดแรกก็คือ สร้างลิสต์ไฟล์ร่วมกัน แล้วเอาเฉพาะที่ไม่ซ้ำ เป็นลิสต์รายการที่มี “ครบ” ทุกรายการจากทั้ง 2 ลิสต์ แล้วค่อยมาเลือกดูว่า ในลิสต์นั้นมีอยู่ในลิสต์รวมหรือเปล่า ขาดอะไรไปบ้าง

1 สร้างลิสต์ที่เป็น unique สำหรับเอาไว้อ้างอิง โดยรวมรายการทั้งจาก List A และ List B ให้มาอยู่ในคอลัมน์เดียวกัน (จะเอาวิธี Copy แล้ว Paste เลยก็ได้ ง่ายดี) เมื่อได้เรียบร้อยแล้ว เราใช้ Remove Duplicates เพื่อกำจัดอันที่ซ้ำออก

2 ใช้ lookup จะเป็น VLookup หรือ XLookup ก็ตามแต่ถนัด เพื่อค้นหาว่า รายการใน unique list ที่เราทำขึ้น มีใน List A และ List B หรือไม่ การเอา unique เป็นตัวหลัก ก็คือ จะได้ลิสต์ที่ครอบคลุมจากทั้ง 2 ลิสต์ (เพราะเราสร้างจากการเอา 2 ลิสต์มารวมกัน) ถ้าหาตัวไหนไม่เจอใน List A หรือ B ก็จะขึ้น #N/A  เป็นการบอกว่ารายการนั้น ไม่มีอยู่ในลิสต์นั้น เช่น เหล็กเส้นกลมผิวเรียบ(SR.24)dia12มม. ใน List B ขึ้น #N/A ก็แสดงว่า รายการนี้ ไม่มีใน List B แต่มีใน List A แบบนี้เป็นต้น

3 เมื่อได้ก็สบายแล้ว เราสามารถเทียบได้เลยว่า ลิสต์ไหนไม่สมบูรณ์บ้าง คือ สูตรง่าย ๆ เลย = B2=C2 เพื่อหาว่า ลิสต์ไหนที่ขึ้น #N/A มาบ้าง (ใช้ Filter เลือกเฉพาะ #N/A ขึ้นมา) แสดงว่าอันนั้นไม่มีในอีกลิสต์หนึ่ง

จบไป 1 วิธี

TIPS: เราสามารถทำงานให้คล่องตัวขึ้น โดย แทนที่จะใช้วิธี copy and paste เพื่อรวม List A และ B เราสามารถใช้ Power Query ซึ่งมีความยืดหยุ่นกว่า (ปรับปรุงข้อมูลก็ไม่กระทบต่อวิธีการทำงาน) โดยเราจะ import เป็น create Connection แล้ว Append เข้าด้วยกัน แล้ว Remove Duplicates จากใน Power Query ได้เลย จากนั้นเอาไป Merge กับ List A และ List B คล้ายกับการ lookup เลย

สำหรับ Power Query จะเขียนแยกเรื่องการใช้งานต่างหากในโอกาสหน้า

เปรียบเทียบข้อมูล 2 ลิสต์ โดยใช้ Format

ให้ cell ช่องที่เหมือน หรือไม่เหมือนกันระหว่าง List A กับ List B แสดงผลแตกต่างกัน โดยใช้เมนู Conditional Formatting ก่อนอื่นให้เลือกลิสต์ทั้งหมดก่อน แล้วไปที่ Home > Conditional Formatting > Highlight Cell Rules > Duplicate Value…

ตรงนี้ให้เลือกว่าจะให้ไฮไลต์ส่วนที่เหมือนกัน (Duplicate) หรือไม่เหมือนกัน (Unique) แบบไหนก็ได้ แต่จากตัวอย่างจะเลือกให้ไฮไลต์สิ่งที่เหมือนกัน

ก็จะได้ผลลัพธ์มาว่ามีข้อความในลิสต์ไหนที่ไม่มีซ้ำกันในอีกลิสต์หนึ่ง สามารถใช้ Filter เพื่อเลือกข้อมูลได้

เปรียบเทียบข้อมูล 2 ลิสต์ โดยใช้ CountIF

ฟังก์ชัน CountIf ก็คือการนับแบบมีเงื่อนไข ซึ่งเราจะให้เงื่อนไขว่า ให้นับคำนี้ถ้าปรากฏอยู่ในอีกคอลัมน์หนึ่ง

อย่างเช่น ถ้าจะดูว่า คำใน ListA มีซ้ำใน ListB หรือเปล่า เราก็ใช้ว่า CountIF(B:B,A2) คือดูว่าค่าในช่อง A2 มีค่าซ้ำในคอลัมน์ B (ทั้งคอลัมน์) มั้ย

แต่จากตัวอย่างในคอลัมน์ C จะเป็นการเช็คว่า คำที่อยู่ใน A2 มีซ้ำใน คอลัมน์  B หรือไม่ ซึ่งในที่นี้จะใช้สูตร COUNTIF(B:B,[@ListA]) เพราะว่าจัดฟอร์แมตเป็น Table เอาไว้ (ซึ่งมันสะดวกมาก ๆ สำหรับการทำงานกับสูตรหรือฟังก์ชันต่าง ๆ) สูตรนี้มีค่า เหมือนกับ CountIF(B:B,A2)

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