VLOOKUP โหมด Approximate Match

VLOOKUP โหมด Approximate Match 1
ฟังก์ชันปัดตัวเลข ปัดเศษทศนิยม
9 เทคนิค Excel มือใหม่ก็เก่งได้อย่างรวดเร็ว

อย่างที่เคยบอกไปแล้วว่า VLOOKUP มี 2 แบบ ซึ่งแบบพื้นฐานคือแบบ Exact Match ซึ่งจะ Map ข้อมูลได้ จะต้องเจอคำค้นหาอยู่ในคอลัมน์แรกของตารางอ้างอิงแบบเป๊ะๆ เท่านั้น

แต่ในหลายๆ สถานการณ์ VLOOKUP แบบ Exact Match นั้นไม่สามารถตอบโจทย์ได้ เช่น กรณีที่มีเงื่อนไขในการใช้ตารางอ้างอิงเป็นช่วง เช่น การจัดเกรด การให้ commission เป็นต้น

ตัวอย่างเช่น การจัดเกรดจากคะแนนแบบนี้

VLOOKUP โหมด Approximate Match 2

จะเห็นว่า ถ้าจะทำตารางเพื่อรองรับการหาแบบ Exact Match จะต้องสร้างตารางให้มี 100 ค่าเลย เพื่อรองรับแต่ละคะแนน (จริงๆ ต้องทำ 101 ค่าด้วย เพราะต้องรองรับตั้งแต่ 0-100 คะแนน) ยิ่งที่ค่าตัวเลขคะแนนเป็นทศนิืิยมได้นี่ ไม่มีทางสร้างตารางแบบ Exact Match รองรับไว้ล่วงหน้าได้เลย

แต่ถ้าเราเปลี่ยนวิธีมาใช้ VLOOKUP แบบ Approximate Match จะแก้ปัญหานี้ได้ง่ายๆ เลย

วิธีเตรียมข้อมูลเพื่อรองรับการทำ VLOOKUP แบบ Approximate Match

วิธีเตรียมที่ง่ายที่สุด คือ ให้สร้างคอลัมน์ใหม่ไว้ทางซ้ายของข้อมูล แล้วใส่ค่าที่น้อยที่สุดในช่วงนั้นเข้าไป (ต้องเรียงจากน้อยไปมากด้วยนะ)

VLOOKUP โหมด Approximate Match 3

การใช้สูตร VLOOKUP แบบ Approximate Match

จากนั้นก็ใช้ VLOOKUP ได้ตามปกติเลย จากสูตร

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

  • table_array ให้คอลัมน์แรกสุดเรียงจากน้อยไปมาก ดังนั้นควรลากแค่ตัวข้อมูล ไม่ต้องลากหัวตาราง
  • ให้เปลี่ยน Range Lookup เป็น TRUE

เช่น ใน C10 เขียนว่า =VLOOKUP(B10,A2:C6,3,TRUE) เพื่อ Lookup คะแนน 63 ซึ่งจะได้ผลเป็น C นั่นเอง

VLOOKUP โหมด Approximate Match 4

การทำงานที่แท้จริงของ VLOOKUP โหมด Approximate Match

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

และหลังจาก หาคำที่ต้องการจากคอลัมน์ซ้ายสุดของตารางที่กำหนด (Table Array) โดยทำการหาจากบนลงล่าง (Vertical Lookup) จนเจอตัวที่มากกว่าค่าที่กำหนดแล้วจะเด้งกลับขึ้นข้างบน 1 บรรทัด

vlookup-approx-2

ผลลัพธ์จากการใช้ฟังก์ชัน

  1. ถ้าเจอค่าที่ต้องการเป๊ะๆ : ถ้ามีค่าเดียว มันจะยึดค่าบรรทัดนั้น ถ้ามี หลายค่า มันจะยึดที่เจออันล่างสุด(เจอค่าที่มากกว่าแล้วเด้งขึ้น 1 บรรทัด)
  2. ถ้าไม่เจอค่าที่ต้องการเป๊ะๆ :ขึ้นอยู่กับค่าคำค้นหาเทียบกับค่าในตารางอ้างอิง
    • อยู่ระหว่างค่าในตาราง : จะหาตัวที่มากกว่าคำค้นหาแล้วเด้งกลับ 1 ช่อง
    • มากกว่าทุกค่าในตาราง : เจอตัวล่างสุด เพราะเด้งมา 1 ช่องจากนอกตาราง
    • น้อยกว่าทุกค่าในตาราง : จะแสดงเป็น #N/A (เป็นกรณีเดียวที่จะ Error)

........

ใครที่ชื่นชอบบทความนี้และเห็นว่ามีประโยชน์ ช่วยบอกต่อเพื่อนๆ ของคุณด้วยนะครับ
ยิ่งเกิดประโยชน์กับคนเยอะๆ ผู้เขียนก็ยิ่งดีใจครับ คนแชร์ก็ได้ร่วมสร้างประโยชน์ด้วยนะ ^^

อย่าลืมกดติดตามเทพเอ็กเซลได้ที่ Facebook.com/ThepExcel
และสามารถติดตามคลิปวีดีโอเจ๋งๆ ได้ที่ YouTube Channel ของ Thep Excel ครับ


ฟังก์ชันปัดตัวเลข ปัดเศษทศนิยม
9 เทคนิค Excel มือใหม่ก็เก่งได้อย่างรวดเร็ว

Posted on: February 13, 2018
Tags: , ,

Leave a Reply

Your email address will not be published. Required fields are marked *