หลายๆ คนคงรู้จักการใช้พวก VLOOKUP หรือ MATCH เพื่อหาข้อมูลที่ต้องการแบบ Exact Match กันอยู่แล้ว ซึ่งมันจะได้ค่าของ lookup_value ตัวแรกที่เจอ (นับจากข้างบนลงล่าง) แต่ก็มีหลายสถานการณ์ที่เราอยาก Lookup หาค่าตัวสุดท้ายมากกว่า
โดยเฉพาะเวลาที่ฐานข้อมูลเรียงตามวันที่/เวลาจากเก่าไปใหม่ เราย่อมอยากได้ข้อมูลที่อัปเดทล่าสุดมากกว่าอยากได้บรรทัดแรกจริงมั้ย? เช่นข้อมูลเป็นแบบนี้
ในบทความนี้ผมจะสอนวิธีการ Lookup หาค่าตัวสุดท้าย มาดูกันว่าทำยังไง เอาตั้งแต่แบบเข้าใจง่ายไปยาก (นิดๆ)เลย
สารบัญ
กรณีที่ 1 : กรณียอมให้เรียง Data ใหม่ได้ทั้งหมด
ถ้าแบบนี้ก็ง่ายสุด ให้เรากด sort ใหม่ ให้เอาวันเวลาใหม่สุดมาอยู่ข้างบน
แบบี้เราจะ VLOOKUP แบบ Exact Match ได้เลย
=VLOOKUP(G2,C:D,2,FALSE)
กรณีที่ 2 : กรณียอมให้เรียง Product ใหม่ได้ แต่ใน Product เดียวกันวันเวลาต้องเรียงจากเก่าไปใหม่
สมมติเรียงแบบนี้ได้
แบบนี้เราจะใช้ VLOOKUP Approximate Match ได้เลย เพราะแบบ Approximate Match จะได้ตัวสุดท้ายอยู่แล้ว
=VLOOKUP(G2,C:D,2,TRUE)
แต่สูตรข้างบนนี้จะใช้ได้ ต้องมั่นใจว่ามี lookup_value ที่หาใน database นะ เพราะไม่งั้นมันอาจจะผิดได้
เช่น แม้ว่า lookup_value เป็นคำว่า “cนี้ไม่มีซะหน่อย” ซึ่งควรจะได้ #N/A ก็ดันเจออยู่ดี
(แบบ Approximate Match มันจะเอาตัวสุดท้ายที่น้อยกว่าหรือเท่ากับค่า lookup_value มาให้เรา)
ดังนั้นถ้าจะให้ชัวร์ ต้องลองดึงค่าคอลัมน์แรกกลับมาแล้วใช้ IF เช็คอีกที (คล้ายเทคนิค VLOOKUP เร็ว 100 เท่า) เช่น
=IF(VLOOKUP(G2,C:D,1,TRUE)=G2,VLOOKUP(G2,C:D,2,TRUE),NA())
กรณีที่ 3 : กรณีห้ามเรียงอะไรทั้งสิ้น ต้องเรียงเหมือนเดิม คือตามวันเวลาอย่างเดียวจากเก่าไปใหม่
ถ้าเป็น Excel Version เก่า (กว่า Excel 365)
วิธีง่ายสุด คือ เราก็ต้องใช้ความรู้สูตรแบบ Array มาช่วย ดังนี้ ซึ่งจะค่อนข้างยืดหยุ่นมากๆ เพราะเขียนเงื่อนไขยังไงก็ได้
=LOOKUP(lookup_value,lookup_vector,[result_vector])=LOOKUP(2,1/(เงื่อนไข),$D$2:$D$11) =LOOKUP(2,1/($C$2:$C$11=G2),$D$2:$D$11)
LOOKUP นั้นจะทำงานแบบ Approximate Match เสมอ ซึ่งเรา Lookup ค่าเลข 2
จากตาราง lookup_vector ที่เราสร้างให้ได้เลข 1 กับ Error จากการเขียนเงื่อนไขแบบนี้
=1/($C$2:$C$11=G2) ซึ่ง 1/TRUE ได้ 1, 1/FALSE ได้ Error
พอเรา lookup ด้วย 2 ก็จะได้บรรทัดที่มี 1 ตัวสุดท้ายอยู่ แล้วเราค่อยสั่งเอาผลลัพธ์ในคอลัมน์ D กลับมา (LOOKUP ดีตรงที่ว่า แยก Lookup_vector กับ Result_vector ออกจากกันได้)
ถ้ามี Excel 365
ใช้ XLOOKUP ตัว LOOKUP สุดโกง
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
match_mode
- 0 – Exact match. If none found, return #N/A. This is the default.
- -1 – Exact match. If none found, return the next smaller item.
- 1 – Exact match. If none found, return the next larger item.
- 2 – A wildcard match where *, ?, and ~ have special meaning.
search_mode
- 1 – Perform a search starting at the first item. This is the default.
- -1 – Perform a reverse search starting at the last item.
- 2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.
- -2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
ดังนั้นเราจะใช้แบบนี้
=XLOOKUP(G2,$C$2:$C$11,$D$2:$D$11,"-",0,-1) จบเลย
ใช้ FILTER
นอกจากนี้ใน Excel 365 เรายังใช้ FILTER มาช่วยก็ได้นะ โดย FILTER OrderID เฉพาะที่ product เป็น a ออกมาหลายๆ รายการเลย (เรียกว่า Spill Array)
=FILTER(D2:D11,C2:C11=G2)
ซึ่งวิธีนี้ดีกว่าวิธีอื่นตรงที่เรากำหนดได้เลยว่าจะเอา item ลำดับที่เท่าไหร่กลับมาก็ได้ ด้วยสูตรนี้
=INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),เลขลำดับ)ถ้าอยากได้อันสุดท้ายอันเดียว ก็ใช้ INDEX มาช่วยก็ได้ แล้วใช้ COUNTA นับว่า Filter มาเหลือกี่ตัว (ใช้ COUNTIFS ก็ได้แล้วแต่ชอบ) แล้วเอาใช้ในเลขลำดับ
=INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),COUNTA(FILTER($D$2:$D$11,$C$2:$C$11=G2)))
หรือ
INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),COUNTIFS($C$2:$C$11,G2))
error #CALC คือมัน Filter แล้วไม่มีผลลัพธ์ออกมา ถ้าอยากให้ไม่ขึ้น Error ก็ใช้ IFERROR ดักไปก็ได้ เช่น
=IFERROR(INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),COUNTIFS($C$2:$C$11,G2)),"-")
ใช้ Power Query
เราเอา transaction table เข้าไป แล้ว เรียงใหม่ใน power queryจะได้แบบนี้ (กดเรียงวันที่ก่อน และกดเรียงเวลา)
จากนั้นให้ add index column ก่อนจะ remove duplicates ไม่งั้นมันจะมี bug ไปใช้ลำดับก่อนจะ sort ในการเก็บตัวบนสุด
จากนั้นก็ remove duplicates ที่คอลัมน์ product ซะก็จะได้ตัวล่าสุด จบเลย
พอได้ตารางนี้แล้ว เราจะเอาไป Merge กับตารางอื่นยังไงก็ได้แล้ว ไม่ขอทำให้ดูละกันนะครับ
สรุปแล้วคุณชอบแบบไหนครับ?
ทำให้ดูไปหลายแบบมากๆ ว่าแต่คุณชอบแบบไหนกันมั่งครับ comment บอกได้นะ หรือถ้าใครมีวิธีดีๆ แบบอื่นๆ ก็ comment มาได้เลยเช่นกันครับ
