วิธีเปรียบเทียบข้อมูล 2 ตาราง ว่ามีรายการไหนตรงกัน ไม่ตรงกัน?

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

และเพื่อให้มันเจ๋งยิ่งขึ้น ผมจะแสดงวิธีที่ 2 วิธีเลยละกัน นั่นคือ 1. ใช้สูตร 2. ใช้ Power Query

ก่อนอื่น สมมติว่าผมมีข้อมูลดังนี้

วิธีการใช้สูตรในการเปรียบเทียบข้อมูล

Q1: คนไหนมีชื่ออยู่ทั้ง 2 ตาราง?

หากเราอยากรู้ว่าคนไหนบ้างที่มีชื่ออยู่ใน 2 ตาราง เราจะทำยังไง?

แนวคิดในการเปรียบเทียบข้อมูลก็คือ เราจะหาชื่อในตาราง Math ทีละตัว โดยเอาแต่ละตัววิ่งหาในตาราง Excel ว่าเจอรึเปล่า? ถ้ามันเจอ ก็แปลงว่าตัวนั้นอยู่ทั้ง 2 ตาราง จริงมะ?

ดังนั้นเราจะเขียนสูตรใน D4 เพื่อเอาคะแนนในอีกตารางได้ว่า

=VLOOKUP(B4,G:H,2,FALSE)

ปล. สูตรข้างบนเป็นฉบับคนขี้เกียจ ซึ่งเลือกทั้งคอลัมน์เลย เพื่อนๆ อาจเลือกเฉพาะที่มีข้อมูลก็ได้นะครับ แต่ต้องกดปุ่ม F4 เพื่อ lock table_array ให้อยู่กับที่ด้วยนะ

หาเราอยากให้นับเป็น 0 กับ 1 ว่าเจอหรือไม่? ก็ใช้พวก ISERROR มาดักก็ได้ ถ้าไม่เจอมันจะ Error ก็คือ 0 ถ้าไม่ Error ก็คือ 1 ดังนั้นผมใส่สูตรใน E4 ว่า

=--NOT(ISERROR(D4))

โดยที่ — คือการแปลงค่า TRUE/FALSE ให้เป็น 1 กับ 0 นั่นเอง (จะใช้วิธี *1 ก็ได้)

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

=--NOT(ISERROR(MATCH(G4,B:B,0)))

คราวนี้ผมลองใช้ฟังก์ชัน MATCH บ้าง และรวบสูตรทีเดียวเลย เพราะ MATCH จะทำได้แค่หาเจอรึเปล่า? เอาคะแนนกลับมาไม่ได้ ยกเว้นจะใช้ INDEX ช่วย

สรุปแล้วตัวที่เจอในทั้ง 2 ตาราง ก็คือ B,E,I นั่นเอง (จะทำฝั่งไหนก็คำตอบเหมือนกัน)

ถ้าอยากได้แต่รายการที่เจอทั้งคู่ ก็ใช้วิธี Filter เอาเลข 1 ออกมาจะง่ายที่สุด

ถ้าจะเขียนสูตรก็จะยากกว่ามาก เช่น ใน B17 เขียนว่า

=SMALL(IF(E:E=1,ROW(E:E)),A17) แล้วกด Ctrl+Shift+Enter

เพื่อใช้ว่าแถวที่เจอ Excel =1 อยู่แถวไหนบ้าง เป็นลำดับ 1,2,3…

จากนั้นค่อยใช้ INDEX ถึงค่าที่ต้องการกลับมา เช่น C17 เขียนว่า

=INDEX(B:B,B17)

ซึ่งบางคนยังไม่มีพื้นฐานการเขียนสูตรที่ดีพอ หากให้อธิบายโดยละเอียดคงต้องไว้บทความอื่นๆ

ดังนั้นในบทความนี้ผมจะยังไม่ใช้วิธีเขียนสูตรในการแสดงเฉพาะผลลัพธ์ที่อยากได้นะครับ เพราะมันยากเกินไป ต้องอธิบายหลายเรื่องเลย เช่น Array Formula กว่าจะเข้าใจได้ (แต่เดี๋ยวเราจะแสดงวิธี Power Query ซึ่งเข้าใจง่ายกับคนทั่วไปมากกว่า)

Q2: คนไหนบ้างมีชื่อในตาราง Math แต่ไม่อยู่ใน Excel

อันนี้ก็ง่ายมากเพราะว่าเราก็แค่ไปที่ตาราง Math แล้ว Filter เลือกตัวที่เจอExcel เป็น 0 แค่นั้นเอง

Q3: คนไหนบ้างมีชื่อในตาราง Excel แต่ไม่อยู่ใน Math

อันนี้ก็ง่ายมากเพราะว่าเราก็แค่ไปที่ตาราง Excel แล้ว Filter เลือกตัวที่เจอMath เป็น 0 แค่นั้นเอง

Q4: คนไหนบ้างที่มีชื่อในตารางใดตารางหนึ่ง แต่ไม่ได้อยู่ทั้งสองตาราง

อันนี้เราก็สามารถ Copy ข้อมูลที่ Filter แล้วใน Q3 กับ Q4 มาต่อกันเท่านั้นเอง (ไม่ต้อง REmove Duplicates ด้วย เพราะชื่อก็จะไม่ซ้ำกันอยู่แล้ว จริงมะ?)

ปัญหาของการที่ต้อง Manual Copy

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

แต่ถ้าเป็นงานที่ต้องทำแบบนี้ประจำๆ แบบงาน Routine การที่ต้องมานั่ง Filter Copy Paste ใหม่ทุกครั้ง ก็จะเสียเวลาและมีโอกาสผิดพลาดเกิดขึ้นมาได้

ดังนั้นเดี๋ยวผมจะแสดงวิธีการใช้ Power Query จัดการเรื่องนี้ ซึ่งเราจะไม่ต้องมานั่ง Copy Paste ข้อมูลอีกต่อไป และก็ไม่เข้าใจยากเท่ากับการเขียนสูตรด้วย (แต่จะมีขั้นตอนในการกดปุ่มเยอะ ถ้ายังไม่คุ้นอาจจะรู้สึกว่ามันเยอะ แต่ตอนทำจริงมันเร็วนะ)

วิธีการใช้ Power Query ในการเปรียบเทียบข้อมูล

ก่อนอื่น ผมขอแปลงข้อมูลของเราเป็น Table ก่อน เพื่อความสะดวกในหลายๆ อย่าง (ถ้าจะไม่แปลงก็ต้องตั้งชื่อให้กับ Range ก่อนเอาเข้า Power Query คล้ายๆ แบบนี้)

โดยผมตั้งชื่อเป็น tblMath กับ tblExcel (ตั้งชื่อที่ละตารางนะ)

จากนั้นก็เอาแต่ละตารางเข้า PowerQuery

จากนั้นกด Close & Load to…

เลือกแบบ Connection Only เพื่อให้อ้างอิงข้อมูลตารางนั้นๆได้ โดยที่ยังไม่เอาผลลัพธ์ออกมาจริงๆ

ทำให้ครบทั้งสองตาราง จะมี List ของ Query ที่ทำไว้อยู่ด้านขวา

คราวนี้เราจะเริ่มทำผลลัพธ์แล้วล่ะ

Q1: คนไหนมีชื่ออยู่ทั้ง 2 ตาราง?

คลิ๊กขวาที่ Query tblMath แล้วเลือก Merge

จากนั้นเลือกว่าจะ Merge กับ tblExcel โดยให้เลือกคอลัมน์ที่เป็นตัวเชื่อมด้วย นั่นก็คือ ชื่อ

โดยให้เลือกวิธี Join Kind (วิธีการ Join) ซึ่งถ้าเป็น Version ใหม่ๆ ที่อัปเดทแล้วจะมีวิธีการ Join เยอะแยะเลย และตัวที่เราจะใช้คือ Inner Join ซึ่งแปลว่าต้องเจอในทั้ง 2 ตาราง (ถ้าใครเป็น version เก่า ไม่มี Dropdown ให้เลือก น่าจะมีให้ติ๊ก only matching row ก็ติ๊กไปครับ)

ผลลัพธ์จะออกมาเป็นคำว่า Table ก่อน ซึ่งเราสามารถกด Expand เอาค่าข้างในออกมาก็ได้ แต่ไม่จำเป็น เพราะจริงๆ เราได้รายการที่เจอทั้ง 2 ตารางแล้ว

เราสามารถลบคอลัมน์อื่น ให้เหลือแค่ชื่ออย่างเดียวก็จบเลย

แต่ถ้าอยากได้คะแนน Excel ออกมาด้วย ก็กดปุ่ม Expand มุมขวาบน แล้วเลือกว่าจะเอาคอลัมน์อะไรจากอีกตารางบ้าง?

พอ ok ก็จะได้ผลลัพธ์เลย

จากนั้นเราตั้งชื่อ Query ด้านขวานิดนึงเพื่อความเรียบร้อย

แล้วกด Close & Load to… ออกมาเป็น Table ในตำแหน่งที่ต้องการได้

พอกด ok ก็จะได้ผลัพธ์เลย

อันนี้จริงๆ ดูเหมือนมีหลาย Step แต่พอกดในการทำงานจริงมันจะเร็วมากนะครับ ต้องลองๆ

Q2: คนไหนบ้างมีชื่อในตาราง Math แต่ไม่อยู่ใน Excel

อันนี้เราทำเหมือน Q1 เลย แต่ว่าเปลี่ยนการ JoinKind ตอน Merge เป็น Left Anti Join ซึ่งแปลว่ามีในตารางแรก แต่ไม่มีในตารางที่สอง

ถ้าใครไม่มี Left Anti ให้เลือก สามารถแก้สูตรจาก Query ใน Q1 จาก JoinKind.Inner ให้เป็น JoinKind.LeftAnti ได้เลย จะได้ผลเหมือนกัน (ตัวพิมพ์เล็กพิมพ์ใหญ่ให้เป๊ะนะ)

ปล. ใน Power Query Editor ของใครไม่มีช่อง Formula bar ขึ้นมาให้ไปติ๊กใน View -> Formula Bar

ซึ่งข้อนี้ผมตั้งชื่อ Query ว่า Q2-OnlyinMath

Q3: คนไหนบ้างมีชื่อในตาราง Excel แต่ไม่อยู่ใน Math

อันนี้ทำได้ 2 แบบ คือ เราทำเหมือน Q2 เลย แต่ให้คลิ๊กขวา Merge ที่ตาราง tblExcel เพื่อให้ tblExcel เป็นตารางหลักแทน

หรือจะทำแบบ Q2 แล้วให้ tblMath เป็นตารางหลักเหมือนเดิม แล้วเปลี่ยน JoinKind เป็น Right Anti ซึ่งแปลว่ามีในตารางที่สองแต่ไม่มีในตารางแรก

ซึ่งเดี๋ยวผมจะแสดงวิธีหลังให้ดู จะได้เห็นตัวอย่างเยอะๆ หลายๆ แบบ

ตอนแรกมันจะขึ้น null เพราะไม่มีในตาราง Math ไง

ให้เราลบคอลัมน์ชื่อกับคะแนนทิ้งไป เพราะไม่ใช้

จากนั้นกด expand table ออกมาให้หมด แต่คราวนี้เราไม่เอาชื่อตารางเป็น prefixแล้ว

จะได้ผลลัพธ์แบบนี้

ซึ่งข้อนี้ผมตั้งชื่อ Query ว่า Q3-OnlyinExcel

Q4: คนไหนบ้างที่มีชื่อในตารางใดตารางหนึ่ง แต่ไม่ได้อยู่ทั้งสองตาราง

ข้อนี้จริงๆ แล้วเราสามารถเอา Q2 กับ Q3 มาต่อตูดกันได้เลย (เรียกว่า Append)

โดยที่คลิ๊กขวาที่ Q2-OnlyinMath แล้วเลือก Append

แล้วก็เลือกว่า Append กับ Q3-OnlyinExcel

จากนั้นเราก็ได้ผลลัพมาเลย!

แต่ถ้าเราอยากบอกด้วยว่าแต่ละบรรทัดมาจากตารางไหน ผมแนะนำให้ไปเพิ่มชื่อตารางเป็นอีกคอลัมน์นึงใน Q2-OnlyinMath และ Q3-OnlyinExcel ซะก่อน ดังนี้

กลับเข้าไป Edit ใน Q2-OnlyinMath ดังนี้ แล้วกด Close & Load

ทำแบบเดียวกันกับ Q3-OnlyinExcel แล้วกด Close & Load

จากนั้นกลับมา Append อีกทีนึง ก็จะมีคอลัมน์ SourceTable โผล่มาละ

และนี่ก็คือภาพรวมผลลัพธ์ทั้งหมด

ข้อมูลเปลี่ยนก็กด Refresh ได้เลย

ซึ่งความดีงามของ Power Query ก็คือ ถ้าข้อมูลต้นทางเปลี่ยนไป เราจะ Refresh ได้เลย

เช่น ผมไปเพิ่ม N 88 ในตารางแรก

แค่ผมไปที่ Data -> Refresh All เท่านั้นจบเลย
Tips : ถ้าคลิ๊กขวา Refresh ที่ตารางผลลัพธ์ มันจะแก้แค่ตารางนั้นอันเดียว ถ้าจะแก้ทุกอันพร้อมกันให้กด Refresh All แบบนี้แหละ

ผลลัพธ์ทุกตารางจะเปลี่ยนไปทันที

เรียงผลลัพธ์ให้สวยงาม

สังเกตว่าตาราง Q4 ยังไม่ได้กด Sort ตามชื่อ (Append มันจะยังไม่ sort) ถ้าจะ sort ก็เข้าไปแก้ใน Q4 แล้วกด Sort ก็จบเลย

พอกด close & load ผลของ Q4 ก็จะมีการเรียงตามชื่อเลย จบ

แถม Q5 : List รายชื่อทั้งหมดเลย แล้วแสดงคะแนนของแต่ละวิชา

หากต้องการ List รายชื่อทั้งหมดจากทั้ง 2 ตาราง เราสามารถทำได้ 2 แบบ คือ

1. ใช้การ Merge แบบ Full Outer Join
2. Append ข้อมูลก่อนใช้ Left Join 2 ที

Q5.1 : ใช้การ Merge แบบ Full Outer Join

โดยคลิ๊กขวาที่ tblMath แล้วเลือก Merge แบบ Full Outer Join

จะได้ผลลัพธ์ดังนี้

จากนั้นกด Expand ออกมาแบบเอาทุกอย่างเลย

จะได้ผลลัพธ์แบบนี้ ซึ่งจะได้ทุกแถวของทั้ง 2 ตารางมาเลย

จากนั้นเราจะสร้างคอลัมน์ใหม่ โดยเลือกอะไรก็ได้คอลัมน์เดียวก่อน แล้วไปที่แถบ Add Column-> Conditional Column โดยเอาชื่อมารวมกันดังนี้ (ถ้าช่องนั้นว่างให้เอาอีกช่อง ไม่งั้นก็เอาช่องเดิม)

ปล. ใครที่ไม่มี Conditional Column ให้ Add Custom Column แล้วใส่สูตร if เอาเองว่า

if [ชื่อ] = null then [tblExcel.ชื่อ] else [ชื่อ]

จะได้ผลัพธ์แบบนี้เหมือนกัน

จากคลิ๊กคอลัมน์ ชื่อCombine กด Ctrl ค้างไว้แล้วคลิ๊ก คะแนน กับ tblExcel.คะแนน แล้วกดคลิ๊กขวา Remove Other Column

จะได้ผลัพธ์เหลือเฉพาะตัวที่ต้องการเลย

จากนั้น จะ sort หรือเปลี่ยนชื่อคอลัมน์อะไรก็ตามใจนะ เช่น

Q5.1 : Append ข้อมูลก่อนใช้ Left Join 2 ที

อีกวิธีนึงคือเราเอา tblMath กับ Excel Append กันก่อน

จะได้แบบนี้ ซึ่งให้เราลบคอลัมน์คะแนนออกไปก่อน

จากนั้นกด Home -> Remove Rows -> Remove Duplicates เพื่อกำจัดตัวซ้ำ

จากนั้น Merge กับตารางคะแนนแต่ละอันแบบ Left Outer (ยึดฝั่งซ้าย หรือฝั่งบนเป็นหลัก)

อันนี้ Merge กับ tblExcel ก่อน (อะไรก่อนก็ได้)

ต่อไป Merge กับ tblMath

จะได้แบบนี้

จากนั้นกด Expand คะแนนแต่ละอันออกมา

ทำครบทั้ง 2 ตารางก็จะได้แบบนี้เลย

กด Sort ชื่อซะหน่อยก็จบเลย

Close & Load to… Table ออกมาก็ได้ผลเหมือนกันแหละ

จบแล้ววว

และนี่ก็คือ 2 วิธี ในการเทียบข้อมูล 2 ตาราง ว่ามีรายการไหนตรงกัน ไม่ตรงกัน มันยาวมากๆ เลย (มีใครอ่านจบบ้าง comment บอกหน่อยนะ 555)

ใครลองแล้วได้ผลลัพธ์เป็นยังไง ทำได้ไม่ได้ยังไง? Comment บอกได้เลยนะครับ