Month: February 2018

  • VLOOKUP Approximate Match

    VLOOKUP Approximate Match

    อย่างที่เคยบอกไปแล้วว่า VLOOKUP มี 2 แบบ คือ Exact Match และ VLOOKUP Approximate Match

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

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

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

    VLOOKUP Approximate Match 1

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

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

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

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

    VLOOKUP Approximate Match 2

    การใช้สูตร 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

    การทำงานที่แท้จริงของ 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)

    และถ้าข้อมูลเรียงมั่วจะเกิดอะไรขึ้น?

    ผมได้ทำคลิปเดาการทำงานของ VLOOKUP Approximate Match แบบเป๊ะๆ เอาไว้ ใครสนใจลองไล่ดูได้ครับ