เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Excel ทั่วไปExcel Array Formula

สารพัดวิธี Lookup หาค่าตัวสุดท้าย

lookup last value lookup หาค่าสุดท้าย

หลายๆ คนคงรู้จักการใช้พวก VLOOKUP หรือ MATCH เพื่อหาข้อมูลที่ต้องการแบบ Exact Match กันอยู่แล้ว ซึ่งมันจะได้ค่าของ lookup_value ตัวแรกที่เจอ (นับจากข้างบนลงล่าง) แต่ก็มีหลายสถานการณ์ที่เราอยาก Lookup หาค่าตัวสุดท้ายมากกว่า

โดยเฉพาะเวลาที่ฐานข้อมูลเรียงตามวันที่/เวลาจากเก่าไปใหม่ เราย่อมอยากได้ข้อมูลที่อัปเดทล่าสุดมากกว่าอยากได้บรรทัดแรกจริงมั้ย? เช่นข้อมูลเป็นแบบนี้

สารพัดวิธี Lookup หาค่าตัวสุดท้าย 1

ในบทความนี้ผมจะสอนวิธีการ Lookup หาค่าตัวสุดท้าย มาดูกันว่าทำยังไง เอาตั้งแต่แบบเข้าใจง่ายไปยาก (นิดๆ)เลย

กรณีที่ 1 : กรณียอมให้เรียง Data ใหม่ได้ทั้งหมด

ถ้าแบบนี้ก็ง่ายสุด ให้เรากด sort ใหม่ ให้เอาวันเวลาใหม่สุดมาอยู่ข้างบน

สารพัดวิธี Lookup หาค่าตัวสุดท้าย 2

แบบี้เราจะ VLOOKUP แบบ Exact Match ได้เลย

=VLOOKUP(G2,C:D,2,FALSE)
สารพัดวิธี Lookup หาค่าตัวสุดท้าย 3

กรณีที่ 2 : กรณียอมให้เรียง Product ใหม่ได้ แต่ใน Product เดียวกันวันเวลาต้องเรียงจากเก่าไปใหม่

สมมติเรียงแบบนี้ได้

สารพัดวิธี Lookup หาค่าตัวสุดท้าย 4

แบบนี้เราจะใช้ VLOOKUP Approximate Match ได้เลย เพราะแบบ Approximate Match จะได้ตัวสุดท้ายอยู่แล้ว

=VLOOKUP(G2,C:D,2,TRUE)
สารพัดวิธี Lookup หาค่าตัวสุดท้าย 5

แต่สูตรข้างบนนี้จะใช้ได้ ต้องมั่นใจว่ามี 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())
สารพัดวิธี Lookup หาค่าตัวสุดท้าย 6

กรณีที่ 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 หาค่าตัวสุดท้าย 7

พอเรา lookup ด้วย 2 ก็จะได้บรรทัดที่มี 1 ตัวสุดท้ายอยู่ แล้วเราค่อยสั่งเอาผลลัพธ์ในคอลัมน์ D กลับมา (LOOKUP ดีตรงที่ว่า แยก Lookup_vector กับ Result_vector ออกจากกันได้)

สารพัดวิธี Lookup หาค่าตัวสุดท้าย 8

ถ้ามี 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) จบเลย
สารพัดวิธี Lookup หาค่าตัวสุดท้าย 9

ใช้ FILTER

นอกจากนี้ใน Excel 365 เรายังใช้ FILTER มาช่วยก็ได้นะ โดย FILTER OrderID เฉพาะที่ product เป็น a ออกมาหลายๆ รายการเลย (เรียกว่า Spill Array)

=FILTER(D2:D11,C2:C11=G2)
สารพัดวิธี Lookup หาค่าตัวสุดท้าย 10

ซึ่งวิธีนี้ดีกว่าวิธีอื่นตรงที่เรากำหนดได้เลยว่าจะเอา 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)))
สารพัดวิธี Lookup หาค่าตัวสุดท้าย 11

หรือ

INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),COUNTIFS($C$2:$C$11,G2))
สารพัดวิธี Lookup หาค่าตัวสุดท้าย 12

error #CALC คือมัน Filter แล้วไม่มีผลลัพธ์ออกมา ถ้าอยากให้ไม่ขึ้น Error ก็ใช้ IFERROR ดักไปก็ได้ เช่น

=IFERROR(INDEX(FILTER($D$2:$D$11,$C$2:$C$11=G2),COUNTIFS($C$2:$C$11,G2)),"-")
สารพัดวิธี Lookup หาค่าตัวสุดท้าย 13

ใช้ Power Query

เราเอา transaction table เข้าไป แล้ว เรียงใหม่ใน power queryจะได้แบบนี้ (กดเรียงวันที่ก่อน และกดเรียงเวลา)

สารพัดวิธี Lookup หาค่าตัวสุดท้าย 14

จากนั้นให้ add index column ก่อนจะ remove duplicates ไม่งั้นมันจะมี bug ไปใช้ลำดับก่อนจะ sort ในการเก็บตัวบนสุด

สารพัดวิธี Lookup หาค่าตัวสุดท้าย 15

จากนั้นก็ remove duplicates ที่คอลัมน์ product ซะก็จะได้ตัวล่าสุด จบเลย

สารพัดวิธี Lookup หาค่าตัวสุดท้าย 16

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

สรุปแล้วคุณชอบแบบไหนครับ?

ทำให้ดูไปหลายแบบมากๆ ว่าแต่คุณชอบแบบไหนกันมั่งครับ comment บอกได้นะ หรือถ้าใครมีวิธีดีๆ แบบอื่นๆ ก็ comment มาได้เลยเช่นกันครับ

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 2.8K
  •  
  •  
  •  
  •  
  • 2.8K
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply