Announcement

Collapse
No announcement yet.

สูตรที่ใช้งานได้สะดวกกว่า VLookup หรือ Match Index

Collapse
X
Collapse

  • สูตรที่ใช้งานได้สะดวกกว่า VLookup หรือ Match Index


    สูตร VLookup มีความสำคัญมากที่สุดในการใช้ Excel เลยก็ว่าได้ เพราะหลังจากที่สร้างตารางฐานข้อมูลเก็บรายการไว้แล้ว ก็จำเป็นต้องอาศัยสูตร VLookup ดึงข้อมูลที่ต้องการมาใช้ สูตรนี้จึงเป็นเหมือนหน้าด่าน เป็นสูตรแรกๆที่ต้องฝึกใช้ให้เป็น โดยสังเกตว่าถ้าตารางฐานข้อมูลยังมีโครงสร้างไม่แน่นอนก็ควรเปลี่ยนจาก VLookup มาใช้สูตร Match ร่วมกับ Index แทนเพราะมีความยืดหยุ่นมากกว่า

    ถ้าปราศจากสูตรเหล่านี้ผู้ใช้ Excel จะต้องใช้วิธี copy หรือลิงก์ข้อมูลที่ต้องการมาใช้แทนเซลล์ต่อเซลล์ กลายเป็นภาระอย่างมากกับการใช้สายตาหาตำแหน่งเซลล์ซึ่งอาจหาค่าผิดพลาดได้ง่าย

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

    ดังนั้นแทนที่จะสร้างสูตรแค่นี้ =VLookup(ค่าที่ใช้หา, พื้นที่ตารางฐานข้อมูล, เลขที่ column ของคำตอบ, 0 หรือ False) จึงไม่ปลอดภัย ต้องซ้อนเข้าไปในสูตร IfError หรือ IF IsError ไว้ด้วยเสมอ

    ในกรณีที่คำตอบที่ต้องการเป็นเลข ต้องใช้สูตรแบบนี้
    =IFError( สูตรVLookup, 0) หรือ
    =IF(IsError( สูตรVLookup หรือ Match ), 0, สูตรVLookup )

    ในกรณีที่คำตอบที่ต้องการเป็นตัวอักษร ต้องใช้สูตรแบบนี้
    =IFError( สูตรVLookup, “หาค่าที่ต้องการไม่พบ”) หรือ
    =IF(IsError( สูตรVLookup หรือ Match ), “หาค่าที่ต้องการไม่พบ”, สูตรVLookup )

    สาเหตุที่ใช้สูตร Match ตรวจสอบ error เพราะ Match จะทำงานเร็วกว่า VLookup แต่ถ้าตารางฐานข้อมูลไม่ใหญ่นักจะใช้ VLookup หรือ Match ก็ไม่ได้ช้าเร็วแตกต่างกัน

    บางคนชอบทำให้แสดงเป็นช่องว่างแทนเมื่อ error มักจะใส่ “” แทนเลข 0 หรือแทนข้อความ “หาค่าที่ต้องการไม่พบ” ซึ่งผมไม่แนะนำการทำให้เป็นช่องว่างด้วยวิธีใช้ “” แทนเป็นอย่างยิ่ง เพราะช่องว่างที่เห็นนั้นไม่ได้ว่างจริง เมื่อลิงก์ค่าไปคำนวณต่อจะบวกลบคูณหารต่อไม่ได้

    นอกจากนี้สูตร VLookup หรือ Match จะหารายการแรกที่ตรงกับค่าที่ใช้หาเท่านั้น จึงเป็นข้อต้องห้ามเลยทีเดียวว่า ก่อนที่จะนำสูตรนี้มาใช้ต้องมั่นใจก่อนว่าในตารางฐานข้อมูลนั้นมีรายการที่ตรงกับค่าที่ใช้หาเพียงรายการเดียว ห้ามมีรายการซ้ำเด็ดขาด

    ดังนั้นก่อนที่จะใช้สูตร VLookup ได้อย่างมั่นใจ จำเป็นต้องใช้สูตร CountIF นับจำนวนรายการก่อนว่ามีเพียงรายการเดียวใช่หรือไม่

    หลังจากที่มั่นใจว่ามีเพียงรายการเดียวที่ตรงกับค่าที่จะนำมาใช้หาแล้ว แทนที่จะเสียเวลาใช้สูตร VLookup แบบ Exact Match สร้างซ้อนเข้าไปในสูตร IfError หรือ IsError ที่ซ้อนใน If อีกชั้น ยังมีสูตรที่หลายคนคิดไม่ถึงกันอยู่อีกที่สามารถนำมาใช้แทน VLookup หรือแม้แต่ Match Index ได้เลย โดยมีเงื่อนไขก่อนจะนำมาใช้งาน ดังนี้
    1. เมื่อคำตอบที่ต้องการ เป็นตัวเลข
    2. มีเพียงรายการเดียว
    3. แฟ้มที่ใช้ไม่เสียเวลาคำนวณนานนัก เพราะสูตรนี้จะใช้เวลาคำนวณช้ากว่า
    4. ข้อมูลที่เก็บไว้ไม่จำเป็นต้องอยู่ในพื้นที่ตารางติดกัน จะอยู่ต่างชีทหรือต่างแฟ้มกันก็ยังได้ ขอเพียงพื้นที่ตารางที่เก็บค่าที่ใช้หากับพื้นที่ตารางตัวเลขคำตอบที่ต้องการมีขนาดเท่ากัน
    5. หาค่าแบบ Exact Match ซึ่งหากหาค่าไม่พบ ต้องการคำตอบเท่ากับ 0

    สูตรที่ว่านี้คือ SumIF หรือ SumIFS ไงครับ
      Posting comments is disabled.

    Article Tags

    Collapse

    Latest Articles

    Collapse

    Working...
    X