VLOOKUP คืออะไร?

VLOOKUP (Vertical Lookup) เป็นฟังก์ชั่นใน Excel ที่จัดอยู่ในประเภทการค้นหาและอ้างอิงข้อมูล เช่น เอาไว้หาว่ารหัสสินค้าที่กำลังค้นหาคือสินค้าชื่ออะไร จากตารางอ้างอิงที่เรามีอยู่ โดยถือว่าเป็นฟังก์ชั่นที่ผู้หัดใช้ Excel แรกๆ อาจรู้สึกว่ามันใช้ยาก ซึ่งก็จริง แต่ถ้าหากว่าเราใช้ฟังก์ชั่นนี้เป็นแล้ว เราจะใช้งาน Excel ทำอะไรได้อีกมากเลยทีเดียว ดังนั้นเรามาหัดใช้กันให้คล่องเถอะครับ

วิธีการใช้งาน (ภาษาเป็นทางการ)

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

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

วิธีการใช้งาน ภาษาชาวบ้าน

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

VLOOKUP มี 2 โหมดด้วยกัน

  1. Approximate Match (ตั้งค่า Range Lookup เป็น TRUE หรือ 1) แปลว่า แม้ไม่เจอค่าที่ต้องการหาเป๊ะๆ ก็ยังส่งค่าบางอย่างกลับมาได้ (แล้วมันส่งค่าอะไรกลับมาเดี๋ยวจะอธิบายอีกที)
  2. Exact Match (ตั้งค่า Range Lookup เป็น FALSE หรือ 0) แปลว่า ต้องเจอค่า/คำที่ต้องการเป๊ะๆ จึงจะส่งค่ากลับมา

ก่อนอื่นผมของเริ่มจากตัวง่ายกว่าก่อน นั่นก็คือ VLOOKUP โหมด Exact Match ครับ

VLOOKUP โหมด Exact Match

จุดประสงค์หลัก : เหมาะกับการ Map ข้อมูล (ดึงค่าจาก Reference ที่กำหนด)
มันจะทำงานดังนี้

Excel VLOOOKUP exact match

  1. หาคำที่ต้องการจากคอลัมน์ซ้ายสุดของตารางที่กำหนด (Table Array) และทำการหาจากบนลงล่าง (Vertical Lookup)
  2. ผลมี 2 กรณี
    1. ถ้าเจอคำที่ต้องการ(ต้องเหมือนเป๊ะๆ) มันจะหยุดที่บรรทัดนั้น
      • ถ้ามีค่าที่ตรงเงื่อนไขหลายค่าในคอลัมน์ที่ค้นหา มันจะยึดที่เจออันแรกสุด
    2. ไม่เจอคำที่ต้องการเป๊ะๆ จะแสดงค่า Error กลับมาเป็น #N/A
  3. กรณีเจอคำที่ต้องการเป๊ะๆ จะวิ่งไปทางขวาตาม Col Index ที่กำหนด เริ่มนับที่คอลัมน์ซ้ายสุดเป็น 1, ถัดไปทางขวา =2, 3, 4… เพื่อเอาค่าใน Col Index ที่กำหนดกลับมาแสดงค่า
    • ตัวอย่างในรูป ช่อง G4 เกิดจากการใช้ VLOOKUP ค้นหาทะเบียน นม6666 ว่าอยู่บรรทัดไหน ถ้าเจอให้เอาคอลัมน์ที่ 2 ของบรรทัดนั้นกลับมา (นับเฉพาะขอบเขตตารางที่เลือกไว้ ไม่เกี่ยวว่าคอลัมน์ที่ 2 คือ B) ซึ่งจะได้ค่า BMW

เอาล่ะ หลังจากเข้าใจการทำงานของ VLOOKUP โหมด Exact Match แล้ว เราจะมาเรียนรู้การทำงานของ VLOOKUP โหมด Approximate Match กันครับ

VLOOKUP โหมด Approximate Match

จุดประสงค์หลัก :

เหมาะกับการดึงค่าข้อมูลจาก Reference ที่มีการกำหนดการอ้างอิงข้อมูลเป็นช่วง ที่แต่ละช่วงไม่จำเป็นต้องเท่ากัน

Tips: VLOOKUP โหมดนี้จะทำงานได้รวดเร็วกว่าโหมด Exact  Match มากเพราะทำงานด้วยระบบการค้นหา Binary Search ซึ่งแบ่งข้อมูลทีละครึ่งแล้วค้นหา จึงจำเป็นต้องเรียงข้อมูลจากน้อยไปมากเสียก่อน (ไม่เข้าใจไม่เป็นไรครับ)
มันจะทำงานดังนี้

  • ต้องเรียงค่าที่เราจะค้นหาในตารางอ้างอิงจากน้อยไปมากเสียก่อน นั่นคือ ข้อมูลในคอลัมน์ซ้ายสุดของตารางอ้างอิงจะต้องเรียงจากน้อยไปมากเท่านั้น มิฉะนั้นการทำงานของ VLOOKUP จะ Error
  • หากทำการเรียงข้อมูลแล้ว มันจะทำงานแบบนี้ครับ (ถ้ายังไม่เรียง จะต้องเข้าใจการทำงานของ Binary Search จริงๆ ซึ่งซับซ้อน แต่ผมมีอธิบายอยู่ข้างล่างของบทความแล้วครับ) :
    หาคำที่ต้องการจากคอลัมน์ซ้ายสุดของตารางที่กำหนด (Table Array) โดยทำการหาจากบนลงล่าง (Vertical Lookup) จนเจอตัวที่มากกว่าค่าที่กำหนดแล้วจะเด้งกลับ 1 บรรทัด
  • ดังนั้นการค้นหาจะมี 3 กรณี
    1. ถ้าเจอค่าที่ต้องการเป๊ะๆ:

    o   ถ้ามีค่าที่ตรงเป๊ะๆ ค่าเดียว มันจะยึดค่าบรรทัดนั้น
    o   ถ้ามีค่าที่ตรงเป๊ะๆ หลายค่า มันจะยึดที่เจออันล่างสุด (เนื่องจากมันจะหาลงมาเรื่อยๆ จนเจอค่าที่มากกว่าแล้วเด้งขึ้น 1 บรรทัดอย่างที่บอกไปตอนต้น)

    1. ถ้าไม่เจอค่าที่ต้องการเป๊ะๆ :

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

    ทั้งกรณีที่ 1 และ 2 หลังจากเจอแล้ว มันจะมองในแถวเดียวกัน แล้วนำค่ากลับมาจาก Col Index ที่กำหนด เริ่มนับที่คอลัมน์ซ้ายสุดเป็น 1, ถัดไปทางขวา =2, 3, 4…

Tips: Binary Search  ทำงานยังไง?

  1. หาว่ามีข้อมูลกี่แถว เช่น มี N แถว
  2. หาจุดแบ่งครึ่ง คิดจาก (N+1)/2 แล้วปัดเศษทิ้ง
  3. เทียบ Lookup Value กับค่าที่อยู่จุดแบ่งครึ่ง ว่าค่า Lookup Value เท่ากับ มากกว่า หรือ น้อยกว่า
  4. ถ้าค่า lookup value เท่ากับ ก็เอาตัวแบ่งครึ่งนั้นเลย / ถ้า lookup value น้อยกว่า ก็จะทำการค้นหาจากครึ่งบนต่อ / ถ้าค่า lookup value มากกว่า ก็จะทำการค้นหาจากครึ่งล่างต่อ
  5. ไล่ Step 1-4 ไปเรื่อยๆ จนเหลือตัวเดียว ตัวนั้นคือผลลัพธ์ที่ได้

ตัวอย่าง binary-search

อธิบาย P-004

  • รอบ 1.  มี 8 แถว  (P-002 ถึง P-019) จุดแบ่งครึ่ง คือ P-017 => หาครึ่งบนต่อ เพราะ P-004 น้อยกว่า P-017
  • รอบ 2. มี 4 แถว  (P-002 ถึง P-017) จุดแบ่งครึ่ง คือ P-005=> หาครึ่งบนต่อ เพราะ P-004 น้อยกว่า P-005
  • รอบ 3. จบที่ P-002 เพราะเหลือตัวเดียว

อธิบาย P-018

  • รอบ 1. มี 8 แถว  (P-002 ถึง P-019) จุดแบ่งครึ่ง คือ P-017 => หาครึ่งล่างต่อ เพราะ P-018 มากกว่า P-017
  • รอบ 2. มี 5 แถว  (P-017 ถึง P-019) จุดแบ่งครึ่ง คือ P-016 => หาครึ่งล่างต่อ เพราะ P-018 มากกว่า P-016
  • รอบ 3. มี 3 แถว  (P-016 ถึง P-019)จุดแบ่งครึ่ง คือ P-020 => หาครึ่งบนต่อ เพราะ P-018 น้อยกว่า P-020
  • รอบ 4. จบที่ P-016 เพราะเหลือตัวเดียว