• สูตร VLookup

    แม้การใช้คำสั่ง Data > Sort และ Data > Filter สามารถช่วยค้นหาข้อมูลที่ต้องการมาแสดงได้ง่าย ถ้าใช้คำสั่ง Sort ย่อมส่งผลทำให้ลำดับรายการของข้อมูลเดิมเปลี่ยนไป ต่างจากการสั่ง Filter ซึ่งไม่กระทบกับลำดับข้อมูลที่เก็บไว้แต่อย่างใด ดังนั้นการใช้ Filter จึงเหมาะกว่าการสั่ง Sort แต่โปรดสังเกตว่าผลจากการใช้คำสั่งดังกล่าวจะแสดงข้อมูลที่ต้องการค้นหาให้เห็นในบริเวณพื้นที่ของตารางเดิมเท่านั้น หากต้องการนำข้อมูลที่ต้องการค้นหาไปแสดงที่อื่น จำเป็นต้องอาศัยสูตร VLookup หรือใช้คำสั่ง Advanced Filter

    สูตร VLookup

    สูตร VLookup เป็นสูตรยอดนิยมในการใช้ค้นหาค่าที่ต้องการจากตารางข้อมูลที่บันทึกไว้เป็นเซลล์ติดต่อกัน ตัวอักษร V นำหน้าย่อมาจากคำว่า Vertical ซึ่งแปลว่า แนวตั้ง สูตรนี้จึงมีลักษณะการทำงานตามชื่อสูตร นั่นคือ ใช้ค้นหาค่าจากตารางที่เก็บข้อมูลแต่ละเรื่องไว้ตามแนวตั้งหรือตามแนว column โดยมีข้อแม้ว่าต้องใช้ข้อมูลแนวตั้งหรือ column ด้านซ้ายสุดเท่านั้นเป็นค่าที่ใช้ค้นหา



    ตาราง B2:D7 เป็นตารางฐานข้อมูลที่เก็บข้อมูลไว้เป็นเซลล์ติดต่อกัน โดยตั้งชื่อ Range Name ว่า MyData ให้กับเซลล์ B3:D7 ซึ่งเป็นพื้นที่รายการข้อมูลที่เก็บไว้ ไม่รวมถึงพื้นที่หัวตาราง B2:D2 ซึ่งถูกตั้งชื่อว่า Header

    เมื่อต้องการค้นหาว่ารหัส Id a003 ในเซลล์ F3 มีชื่อและยอดสินค้าเท่ากับเท่าใด โดยให้แสดงคำตอบในเซลล์ G3 และ H3 ตามลำดับ ให้เริ่มพิจารณาก่อนว่าข้อมูลรหัส Id ถูกจัดเก็บไว้เป็น column ซ้ายสุดของตาราง MyData ดังนั้นจึงสามารถนำสูตร VLookup มาใช้ค้นหาชื่อและยอดสินค้าได้ตามต้องการดังนี้
    • เซลล์ G3 ใช้สูตร =VLOOKUP( $F$3, MyData, 2 ) ได้คำตอบเป็นชื่อ C
    • เซลล์ H3 ใช้สูตร =VLOOKUP( $F$3, MyData, 3 ) ได้คำตอบเป็นยอดสินค้า 30
    เลข 2 และเลข 3 ซึ่งใส่ไว้ในวงเล็บด้านหลังสุด หมายถึง การกำหนดให้สูตร VLookup นำคำตอบจากตาราง MyData ใน column ที่ 2 และ 3 มาแสดง

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


    สาเหตุที่เป็นเช่นนี้เพราะสูตร VLookup ที่ใช้อยู่นั้นเป็นสูตรแบบ Approaching Match โดยสูตรแบบนี้จะทำงานได้ถูกต้องต่อเมื่อ column ซ้ายสุดเรียงค่าไว้จากน้อยไปมากเท่านั้น หากค่าที่ต้องการหาคือ a003x ไม่มี ก็จะใช้รหัสอื่นที่น้อยกว่าหรือเท่ากับ a003x มาใช้แทนซึ่งก็คือใช้รหัส a003 แทนนั่นเอง ทำให้ยังใช้ค้นหาได้ตามปกติแต่ได้ผลลัพธ์ไม่ถูกต้อง

    สูตรแบบ Approaching Match เหมาะกับการใช้ตัวเลขซึ่งมีค่าต่อเนื่องกันไปใช้ในการค้นหา แต่ตัวอย่างนี้ค่าที่ใช้ค้นหาเป็นรหัส ขอแนะนำให้ปรับสูตรไปใช้แบบ Exact Match แทนโดยเพิ่มเลข 0 ต่อท้ายเข้าไปด้านหลังสุดในสูตรดังนี้
    • เซลล์ G3 ใช้สูตร =VLOOKUP( $F$3, MyData, 2, 0 )
    • เซลล์ H3 ใช้สูตร =VLOOKUP( $F$3, MyData, 3, 0 )
    แม้สูตรในเซลล์ G3 และ H3 จะคืนค่าว่า #N/A ก็ตาม แต่เป็นคำตอบที่ถูกต้องเพราะในเมื่อรหัส a003x ไม่มีอยู่จริง ก็ต้องหาคำตอบไม่ได้



    สูตร VLookup แบบ Exact Match ขอให้สังเกตว่าใน column ของรหัส Id ไม่จำเป็นต้องเรียงจากน้อยไปมาก ซึ่งเลข 0 ที่เพิ่มต่อท้ายลงไปนั้นมีความหมายว่า ไม่ หรือ False (หมายถึงกำหนดให้สูตรหาค่าแบบไม่จำเป็นต้องเรียงลำดับ) หากเปลี่ยนรหัสในเซลล์ F3 เป็นรหัสอื่นที่มีอยู่จริง ก็ย่อมหาคำตอบได้ถูกต้องตามต้องการ


    ข้อสังเกตสำคัญของการใช้สูตร VLookup
    1. ถ้าข้อมูลใน column ซ้ายสุดเรียงลำดับจากน้อยไปมากอยู่แล้ว จะช่วยทำให้เกิดการไล่เทียบค่าที่ต้องการได้ง่ายขึ้น ดังนั้นสูตร VLookup แบบ Approaching Match จึงให้คำตอบได้รวดเร็วกว่าสูตร VLookup แบบ Exact Match อย่างมาก
    2. แทนที่จะใส่เลข column ซึ่งเป็นค่าคงที่ไว้ในสูตร VLookup ให้นำสูตร Match เพื่อคำนวณหาเลข column แทน เช่น จากเดิมใช้สูตร =VLOOKUP($F$3,MyData,2,0) ให้เปลี่ยนเป็นสูตร =VLOOKUP($F$3,MyData, MATCH(G2,Header,0), 0) ซึ่งจะช่วยป้องกันทำให้สูตรยังคงทำงานได้ตามเดิมแม้จะมีการแทรก column ลงไปในตารางฐานข้อมูล เพราะสูตร MATCH(G2,Header,0) จะคืนค่าเป็นเลขที่ column ที่ถูกต้องให้เสมอ
    3. สูตร VLookup และสูตร Match จะใช้ลักษณะของข้อมูลเป็นส่วนประกอบในการค้นหาค่า กล่าวคือ หากค่าที่นำไปใช้ค้นหามีลักษณะเป็น Text หรือ Number ก็ต้องนำไปค้นหาจากตารางฐานข้อมูลซึ่งบันทึกไว้แบบ Text หรือ Number ตาม เช่น หากใช้ค่า ‘123 ไปใช้ค้นหา ก็จะค้นหาค่าได้ต่อเมื่อในตารางฐานข้อมูลบันทึกค่าไว้เป็น ‘123 ด้วย แต่จะค้นหาข้อมูลของ 123 ไม่พบเพราะ ‘123 มีลักษณะเป็น Text แต่ 123 มีลักษณะเป็น Number
    4. ในกรณีที่มีค่าบันทึกไว้ซ้ำหลายรายการ สูตร VLookup และสูตร Match จะค้นหาข้อมูลของรายการแรกที่บันทึกซ้ำไว้เท่านั้น
    5. เมื่อหาค่าไม่พบ แทนที่จะปล่อยให้สูตร VLookup คืนค่าเป็น #N/A ควรเปลี่ยนค่า #N/A เป็นเลข 0 หรือคำเตือนอื่นแทน โดยนำสูตร IsError หรือ IsNA มาปรับสูตรเป็น
      =IF(IsError(สูตร VLookup), 0, สูตร VLookup)
      หรือ
      =IF(IsError(สูตร VLookup), “ไม่พบค่าที่ต้องการ”, สูตร VLookup)
    6. สูตร VLookup แบบ Approaching Match =VLOOKUP($F$3,MyData,2) นี้เป็นโครงสร้างสูตรแบบสั้นซึ่งละเลข 1 ต่อท้ายไว้ ยังมีอีกรูปแบบหนึ่งคือ =VLOOKUP($F$3,MyData,2,1) โดยเลข 1 ที่ต้อท้ายสุดในวงเล็บมีความหมายว่า ใช่ หรือ True หมายถึง ให้ใช้กับตารางที่ข้อมูลใน column ซ้ายสุดเรียงจากน้อยไปมาก
    7. ถ้าเลือกใช้สูตร VLookup แบบ Approaching Match แต่ข้อมูลใน column ซ้ายสุดไม่ได้เรียงลำดับจากน้อยไปมากครบทุกรายการ สูตรนี้จะทำงานเพี้ยนถูกบ้างผิดบ้าง
    8. นอกจากสูตร VLookup แล้วยังมีสูตร HLookup ไว้ใช้กับตารางที่เก็บข้อมูลตามแนวนอน แต่มีโอกาสใช้งานน้อยมากเนื่องจากตารางฐานข้อมูลที่ดีต้องเก็บข้อมูลไว้ตามแนวตั้งซึ่งเหมาะสำหรับใช้สูตร VLookup เท่านั้น
    9. หากในแฟ้มมีการใช้สูตร VLookup หลายเซลล์และใช้อ้างอิงกับพื้นที่ตารางขนาดใหญ่ จะทำให้แฟ้มทำงานช้าลงตามจำนวนสูตรและขนาดพื้นที่ที่เพิ่มขึ้น อีกทั้งหากไม่ได้ใช้สูตร Match ช่วยในการหาเลขที่ column จะเสี่ยงได้ข้อมูลผิดทันทีที่ผู้ใช้แก้ไขโครงสร้างตารางโดยการ insert แทรก column ดังนั้นจึงแนะนำให้ใช้สูตร VLookup กับตารางที่เก็บข้อมูลขนาดไม่ใหญ่นักและไม่ค่อยมีการเปลี่ยนแปลง เช่น ตารางเก็บรายชื่อสินค้า รายชื่อลูกค้า แล้วหันไปใช้สูตร Match กับ Index กับตารางทั่วไปแทน