“ไม่ทราบว่ามีวิธีเปรียบเทียบข้อมูลใน Excel จากข้อมูล 2 รายการหรือไม่ ว่ามี item ไหนต่างกันบ้างครับ?”
คำถามนี้มีคนถามบ่อยพอสมควร เลยเขียนเป็นบทความให้อ่านกัน คนอื่นๆ จะได้รับประโยชน์ไปด้วยเนอะ
เอาล่ะ! สมมติผมมีข้อมูลอยู่ 2 List ซึ่งอยู่คนละ Sheet ดังนี้
SheetA 
SheetB
จะเห็นว่ามีทั้งรายการที่มีใน A แต่ไม่มีใน B รวมถึง มีใน B แต่ไม่มีใน A ด้วย
ผมจะเสนอวิธี Compare List นี้ 2 วิธีด้วยกัน คือ
1. วิธีใช้ VLOOKUP
2. วิธีใช้ Power Query
ซึ่งความพิเศษของบทความนี้ คือ ผมจะแสดง Step การแก้ปัญหาเหมือนกันเป๊ะๆ เลย แต่ใช้แค่เครื่องมือต่างกันเท่านั้นเอง
เพื่อนๆ จะได้เข้าใจว่า Power Query สามารถทำงานในลักษณะเดียวกับที่เราใช้ Excel ปกติได้ยังไง? ซึ่งเพื่อนๆ ก็จะรู้จักทั้งการ Apppend/ Remove Duplicates / Merge/ เขียนสูตร/Filter ใน Power Query ด้วยครับ
สารบัญ
วิธีที่ 1 : ใช้ VLOOKUP เปรียบเทียบข้อมูล
Step 1 : เอาข้อมูลมารวมกันใน Sheet เดียว
ก่อนที่เราจะใช้สูตร VLOOKUP ค้นหาข้อมูล ผมขอนำ List จากทั้งสอง Sheet มารวมกัันก่อนใน Sheet ใหม่ ( ตั้งชื่อว่า Sheet All ) ด้วยการ Copy Paste ปกติ
จะเห็นว่ามันมีข้อมูลซ้ำๆ กันอยู่ ดังนั้นเราจะกด Remove Duplicates ก่อนจะทำงานต่อด้วยครับ โดยไปที่ [Data] => Data Tools => Remove Duplicates
Step 2 : หาข้อมูลว่าเจอใน List ที่สนใจหรือไม่
จากนั้นเราจะใช้ VLOOKUP หาว่ามีข้อมูลที่เราสนใจใน Sheet A หรือไม่?
ดังนั้นเราจะเขียนสูตรประมาณนี้ก่อนครับ
หาใน Sheet A =VLOOKUP(A2,SheetA!A:A,1,FALSE)
เราจะสามารถตีความผลลัพธ์ได้ว่า
- ถ้าขึ้น #N/A แปลว่า ไม่เจอข้อมูลใน Sheet A
- ถ้าไม่ใช่ #N/A แปลว่า เจอ ข้อมูลใน Sheet A
ในทำนองเดียวกัน ในอีก Sheet เราก็ทำเหมือนกัน ได้ว่า
หาใน Sheet B =VLOOKUP(A2,SheetB!A:A,1,FALSE)
Step 3 : ทำ Column สรุปผล เพื่อเลือกเฉพาะสิ่งที่เราสนใจ
ถ้าเราสนใจอยากจะ List ทุก item ที่ไม่เจอครบทั้ง 2 List ก็ใช้วิธีการสร้างคอลัมน์ใหม่ แล้วใส่เงื่อนไขแบบ OR ลงไปก็ได้ครับ
หลักการคือ เราจะตรวจสอบว่ามีตัวใดตัวหนึ่ง Error หรือไม่ แปลว่า อย่างน้อยหา PartNum ที่กำลังสนใจไม่พบใน Sheet ใด Sheet หนึ่ง
ดังนี้ =OR(ISNA(B2),ISNA(C2))
จากนั้นเราก็ Filter เฉพาะตัวที่เป็น TRUE ได้เลยครับ (แปลว่า อย่างน้อยหาไม่พบใน Sheet ใด Sheet หนึ่ง)
วิธีที่ 2 : ใช้ Power Query เปรียบเทียบข้อมูล
คราวนี้วิธีที่ 2 จะใช้ Power Query แทน ซึ่งมีข้อดีตรงที่สามารถกด Refresh เมื่อข้อมูลเปลี่ยนไปได้เลย (ไม่ต้องนั่ง copy paste /remove duplicates ใหม่อีกที) แต่หลักการคิดจะเหมือนกับวิธี VLOOKUP เลยครับ
Step 1 : เอาข้อมูลมารวมกันใน Sheet เดียว
ก่อนอื่นให้เอาทั้ง 2 List เข้าเป็น Query ก่อน (ทั้ง 2 อันเลย)
โดยตั้งชื่อ Query แต่ละอันเป็น TableA กับ TableB ด้วย (ทำทีละอัน) และให้ทำเป็น Load to…Only create Connection อย่างเดียว เราจะได้ไม่ต้องมีตารางออกมาเยอะแยะ เดี๋ยวงง
Tips : หลักการ คือ เราจะให้ผลลัพธ์สุดท้ายเท่านั้น ที่ออกมาเป็น Table จริงๆ นอกนั้นให้ create Connection อย่างเดียวนะครับ
พอได้ครบทั้ง 2 Query แล้ว ให้สร้าง Query ใหม่ แบบ Append ก่อน จะได้เห็นภาพครบทุก Item (การ Append คือ เอาข้อมูลมาต่อตูดกัน จำนวนแถวจะเพิ่มขึ้น เหมือนกัยการที่เรา Copy ข้อมูลทั้ง 2 Sheet มาต่อตูดกันแบบวิธีแรกนั่นแหละครับ)
เลือก Append Table A และ B เข้าด้วยกัน
ตอนแรกข้อมูลอาจจะออกมาซ้ำกัน (เพราะมันเอาสองตารางมาต่อตูดกันจริงๆ) ดังนั้นเราจะ Remove Duplicates ออกก่อน
Step 2 : หาข้อมูลว่าเจอใน List ที่สนใจหรือไม่
พอเราได้ List ที่สมบูรณ์และไม่ซ้ำกันแล้ว จากนั้นเราจะเอาไป Merge กับ Table A (เหมือน VLOOKUP หาข้อมูลใน Table A นั่นแหละ )
โดยที่ Step ในการ Merge เค้าจะให้เลือกว่า column ไหนใน 2 ตาราง ที่มีความสัมพันธ์กัน เราก็เลือกไปเลยว่าเป็น PartNum ทั้งคู่นั่นแหละ
พอกด Ok ตอนแรกมันจะออกมาเป็น Table ก่อน
ให้กด Expand เพื่อแตกเอาผลลัพธ์ออกมา (ตรงนี้จริงๆ เลือกได้ว่าจะเอาคอลัมน์ไหนกลับมาบ้าง แต่ในที่นี้เรามีคอลัมน์เดียว 555)
จะได้ผลลัพธ์ว่ามีการ Map เจอกับ Table A ตัวไหนบ้าง เหมือนกับตอนที่เรา VLOOKUP เลยมะ?
ทำอย่างนี้อีกทีกับ Table B เพื่อดูว่า Map เจอตัวไหนบ้าง
Step 3 : ทำ Column สรุปผล เพื่อเลือกเฉพาะสิ่งที่เราสนใจ
ถ้าเราอยากได้ตัวที่ตัวใดตัวหนึ่งเป็น Null ดังนั้นเราจะสร้างคอลัมน์ใหม่ โดย Add Column => Custom Column โดยเขียนสูตรแบบ or ดังนี้ (ตรงชื่อคอลัมน์ สามารถ double click จาก List ด้านขวาได้นะ ไม่ต้องพิมพ์เอง)
พอเรา Filter เลือกเฉพาะที่เป็น TRUE ก็จะได้ตัวที่เราสนใจครับ
จากนั้นถ้าจะโหลดออกมาเป็น Table ก็ Close & Load ได้เลยจบ เช่นเดียวกับตอนที่ใช้ VLOOKUP
แต่มีข้อดีกว่าคือ ถ้าเกิดมีข้อมูลเพิ่มขึ้นอีก เรากด Refresh ทีเดียวจบเลย!!
