🤓
ลอง download ตัวอย่างนี้ไปทดลองกันครับ ลองเปลี่ยนรหัสให้ซ้ำกัน เรียงกัน ไม่เรียงกัน หรือกรอกรายการเพิ่มต่อท้ายว่าสูตร VLookup ยังทำงานได้เสมอ
https://drive.google.com/file/d/1M6IDW6wefPG5-2xGx01lkSdpYu3yYi1L/view?usp=sharing
☝️ ปกติสูตร VLookup แบบ Approximate Match ทำงานเร็วกว่าแบบ Exact Match อยู่แล้ว ถ้ามีจำนวนรายการนับหมื่นจะเร็วกว่า 28 เท่า แต่พอมีจำนวนรายการนับล้าน จะเร็วกว่าถึง 3,600 เท่าทีเดียว
ในวงเล็บของสูตร VLookup(รหัสที่ใช้หา, พื้นที่ตารางข้อมูล, เลขที่ column ของคำตอบ, 👉 True/False แค่เปลี่ยนตรงส่วนนี้)
จากภาพนี้ F3 เป็นเซลล์พระเอก ทำหน้าที่หาว่ารหัสใน Column B นั้นเรียงจากน้อยไปมากหรือไม่ โดยใช้สูตรเทียบพื้นที่รหัสแบบเหลื่อมกันว่ามีค่าน้อยกว่ากันไหม ถ้าเรียงก็จะคืนค่า TRUE แต่ถ้าไม่เรียงก็จะคืนค่า FALSE ส่งผลทำให้ VLookup เปลี่ยนวิธีการค้นหาตามให้ทันที
F3 =AND( OFFSET(B2,1,0,COUNTA(B:B)-2,1) < OFFSET(B2,2,0,COUNTA(B:B)-2,1) )
OFFSET(B2,1,0,COUNTA(B:B)-2,1) ทำหน้าที่หาพื้นที่ B3:B9 ส่วน OFFSET(B2,2,0,COUNTA(B:B)-2,1) หาพื้นที่ B4:B10 โดยสาเหตุที่ใช้ Offset เพื่อทำให้ขยายพื้นที่ตามจำนวนรายการให้เองทันที (Dynamic Range)
ถ้าจำนวนรายการเท่าเดิม แทนที่จะใช้สูตร Offset ที่อาจไม่เคยใช้กัน จะใช้สูตรแบบนี้แทนก็ได้ ง่ายกว่าเยอะ
F3 =AND( B3:B9 < B4:B10 )
จากนั้นก็ลิงก์ F3 ไปใช้ใน VLookup
I3 =VLOOKUP($H$3,MyData,2,$F$3)
J3 =VLOOKUP($H$3,MyData,3,$F$3)
ซึ่งถ้าสูตรคืนค่าเป็น Error ผมปรับให้สูตรเปลี่ยนเป็นคำเตือนว่า Not Valid หรือเลข 0 แทนโดยใช้ CountIF ช่วยตรวจสอบว่ามีรหัสเพียงค่าเดียวหรือไม่ ถ้าหาค่าไม่พบหรือมีค่าซ้ำให้เปลี่ยนเป็นคำเตือนแทน
I3 : =IF(COUNTIF(Id,$H$3)<>1, "Not Valid", VLOOKUP($H$3,MyData,2,$F$3))
J3 : =IF( COUNTIF(Id,$H$3)<>1, 0, VLOOKUP($H$3,MyData,3,$F$3))
+++++++++++++++++++++++++++++++++++
ที่ว่าเร็วขึ้นเป็นพันเท่าเนี่ย ผมทราบมาจากเว็บนี้ครับ
http://dailydoseofexcel.com/archives/2015/04/23/how-much-faster-is-the-double-vlookup-trick/
มีการแนะนำวิธีทำให้ VLookup ทำงานเร็วขึ้นในเว็บนี้และอีกตั้งหลายเว็บ เช่น
https://exceljet.net/formulas/vlookup-faster-vlookup
https://www.thepexcel.com/super-fast-vlookup/
ซึ่งต้นตอบอกว่านำหลักการมาจากหนังสือ Making Excel go Faster โดย Charles Williams
แนะนำให้ใช้ VLookup แบบ Approximate ซ้อนกันแบบนี้ในเซลล์ I17
=IF(VLOOKUP($H$3,MyData,1)=$H$3, VLOOKUP($H$3,MyData,2), NA())
โดย VLOOKUP($H$3,MyData,1)=$H$3 ทำหน้าที่หาว่าหาค่าเจอใน column แรกไหม ถ้าเจอก็ให้หาค่าจาก column อื่นต่อ แต่ถ้าหาไม่เจอก็จะคืนค่าเป็น NA
วิธีนี้ต่างจากวิธีของผม ซึ่งยังหาคำตอบให้ได้ด้วยแต่จะหาค่าแบบ Exact match แทนให้ทันที เพราะใช้สูตรในเซลล์ F3 ตรวจสอบพบว่าใน column แรกไม่ได้เรียงลำดับไว้
