เทคนิคลับสุดยอด VLOOKUP ข้อมูลเร็วขึ้น 100 เท่า! (ขึ้นไป)

Edit : ผมมีเขียนบทความใหม่ไว้ ซึ่งเนื้อหาครอบคลุมและมีเครื่องมือที่อัปเดทกว่านี้ หากสนใจไปอ่านได้ที่นี่ ครับ ^^

เพื่อนๆ เคยทำงานที่ต้อง Lookup ข้อมูลเยอะๆ ใน Excel รึเปล่าครับ? หลายๆคนอาจใช้เทคนิคปรับโหมดการคำนวณจาก Automatic เป็น Manual (ที่ต้องกด F9 เพื่อคำนวณ) เพื่อให้มันคำนวณทีเดียวเมื่อเราเขียนสูตรทุกอย่างพร้อมแล้ว ที่ต้องทำอย่างนั้นเพราะว่ามันนานมากกกกก

สิ่งที่ทำให้มันช้ามักจะเกิดขึ้นกับสูตรประเภทที่ต้อง Lookup ข้อมูล โดยเฉพาะอย่างยิ่งถ้ามีข้อมูลที่ต้อง Lookup เป็นหมื่นหรือเป็นแสนแถว บางทีรอหลายชั่วโมงก็ยังไม่เสร็จ และถ้าสังเกตให้ดี การ Lookup ที่ช้าจะเป็นการ Lookup ประเภท Exact Match เท่านั้น (ต้องเจอผลลัพธ์เป๊ะๆ) ซึ่งจะไม่เกิดอาการช้ากับการ Lookup แบบ Approximate Match ซึ่งจะเร็วกว่ามาก

วันนี้ผมจะมาแนะนำเทคนิคที่จะทำให้ VLOOKUP แบบหาเป๊ะๆ แต่ไม่ต้องรอนานอย่างที่เคย ทำยังไงมาดูกันครับ

ทบทวนสูตร VLOOKUP กันซักนิด

=VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])

=VLOOKUP(ค้นหาคำนี้,จากคอลัมน์แรกในตารางนี้,เมื่อเจอแล้วให้เอาค่าในคอลัมน์ที่ xx ของตารางกลับมา (นับซ้ายไปขวา), [ใช้โหมด Lookup แบบ Approximate Match หรือ Exact Match])

VLOOKUP มี 2 โหมดด้วยกัน เรามาดูกันว่าทั้งสองแบบต่างกันยังไง?

• Approximate Match (ตั้งค่า Range Lookup เป็น TRUE หรือ 1) แปลว่า แม้ไม่เจอค่าที่ต้องการหาเป๊ะๆ ก็ยังส่งค่าบางอย่างกลับมาได้ โดยมีหลักการดังนี้
  • ทำงานเร็วมาก เพราะทำงานด้วยการค้นหาแบบ Binary Search (ลองอ่านข้างล่างสุดดูวิธีการทำงานได้ แต่ไม่เข้าใจไม่เป็นไรครับ)
  • มีข้อจำกัดคือ ข้อมูล Reference (คอลัมน์แรกของ table_array) ต้องเรียงน้อยไปมากเท่านั้น (ไม่งั้นผลลัพธ์จะมั่ว)
  • ถ้าหากข้อมูลเรียงจากน้อยไปมากแล้ว : มันจะวิ่งหาในคอลัมน์แรกใน table_array ลงจากบนลงล่าง จนเจอข้อมูลที่มากกว่า lookup_value แล้วเด้งกลับขึ้นข้างบน 1 ช่อง
  • จากนั้นจะเอาค่าในคอลัมน์ที่ col_index_num กลับมา
• Exact Match (ตั้งค่า Range Lookup เป็น FALSE หรือ 0) แปลว่า ต้องเจอค่า/คำที่ต้องการเป๊ะๆ จึงจะส่งค่ากลับมา ถ้าไม่เจอจะ Error เลย
  • ทำงานช้า(มาก) เพราะทำงานด้วยการค้นหาแบบ Linear Search (ไม่เข้าใจไม่เป็นไรครับ)
  • ข้อดีคือ ข้อมูล Reference (คอลัมน์แรกของ table_array) ไม่จำเป็นต้องเรียงจากน้อยไปมาก
  • วิ่งหาในคอลัมน์แรกใน table_array ลงจากบนลงล่าง จนเจอข้อมูลเป๊ะๆ ถ้าไม่เจอขึ้น Error

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

เคล็ดลับอยู่ตรงนี้แหละครับ!! เราจะไม่ใช้สูตร Exact Match เพราะมันช้า เราจะใช้ VLOOKUP แบบ Approximate Match มาช่วยแทนเพราะเร็วกว่า

แต่จะกำจัดผลลัพธ์ที่ไม่ต้องการออกไปได้ยังไง มาดูกัน

Concept ของเทคนิคลับ ทำยังไงให้เร็ว!?

ก่อนอื่น เราต้องเรียงคอลัมน์แรกในตารางอ้างอิงจากน้อยไปมากก่อน จึงจะใช้สูตร VLOOKUP แบบ TRUE ได้

จากนั้นเราก็สามารถ แต่ใส่เงื่อนไขเพื่อเช็คว่า “ถ้าเอาคอลัมน์แรกกลับมา มันจะเท่ากับ Lookup_value ที่ใช้คนหารึเปล่า?”

• ถ้าเท่ากันแสดงว่าใช้ได้ ให้เอาค่าจากคอลัมน์ที่ต้องการ ( คอลัมน์ที่ 3 )กลับมา
• ถ้าไม่เท่ากัน แสดงว่าใช้ไม่ได้ ก็ให้ขึ้น Error ไป

เงื่อนไขที่เช็ค =VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value
(ซึ่งจะให้ผลลัพธ์เป็น TRUE/FALSE)

• กรณีใช้ได้ (จริง) : =VLOOKUP(lookup_value, table_array, 3, TRUE)
• กรณีใช้ไม่ได้ (เท็จ) : ให้ขึ้นข้อความ Error เช่น = NA() หรือจะขึ้น =”Error” หรือ =”ไม่เจอจ้า” ก็ได้ครับ…

สรุปวิธีเขียนสูตร VLOOKUP ให้เร็วขึ้น 100 เท่า!! (แต่ต้องเรียงคอลัมน์แรกของ table_array ก่อน)

=IF(VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value,
VLOOKUP(lookup_value, table_array, col_index_num, TRUE),NA() )

เพียงแค่นี้ สูตร VLOOKUP คุณก็จะเร็วขึ้นสุดๆ จนเทียบกับของเดิมไม่ได้เลย Enjoy VLOOOKUP นะครับ!!