17 ปม VLOOKUP

☝️17 ปม =VLOOKUP( B4, $E:$Z, 2, 0)
สูตรนี้หาค่าได้ถูกต้อง แต่ยังไม่ดีตรงไหนเอ่ย

  1. พอเจอสูตร VLookup ที่สร้างไว้ ให้มองหาเลข 0 หรือคำว่า FALSE หน้าวงเล็บปิดก่อนเพื่อน ถ้าเจอแสดงว่าสูตรนี้ทำหน้าที่หาค่าแบบ Exact Match
  2. Exact Match เป็นการหาค่าแบบที่ปลอดภัย เพราะถ้าหาค่าที่ตรงกับ B3 เจอก็จะหาคำตอบมาให้ แต่ถ้าหาไม่เจอ ก็จะแสดง error Not Available หรือ N/A ออกมาเตือนว่า ไม่มีค่านั้น หาค่าไม่พบ (แต่ถ้าไม่มีเลข 0 หรือ FALSE จะหาค่าอื่นที่ไม่ตรงกับ B3 มาให้ก็เป็นได้)
  3. สูตรที่ทำงานแบบ Exact Match นั้น Column ซ้ายสุดที่ใช้เก็บค่าที่ใช้ค้นหา ไม่จำเป็นต้องเรียงลำดับ ให้จำว่าเลข 0 หรือ FALSE นั้น แปลว่า ไม่ นั่นคือไม่ต้องเรียงลำดับ
  4. การตรวจสอบค่าแบบ Exact Match จะเสียเวลานาน หากค่าที่ใช้หานั้นอยู่รายการท้ายๆของตาราง และถ้าอ้างอิงกับทั้ง Column E ไว้แบบนั้น Excel ต้องเทียบค่าไปเรื่อยๆทุก Row ที่มีในตาราง ซึ่งอาจต้องทำงานกว่า 1 ล้านครั้งทีเดียว ดังนั้นควรกำหนดขอบเขตให้สูตรนี้ค้นหาค่าในพื้นที่ที่จำเป็นเท่านั้น
  5. การกำหนดพื้นที่แบบทั้ง Column $E:$Z ถ้าผมจำไม่ผิด Excel ไม่ได้ค้นหาค่าไปจนหมดทุก Row ที่เป็นเซลล์ว่างหรอกครับ แต่ตรวจสอบเฉพาะพื้นที่ซึ่งถูกใช้งานเท่านั้น (Used Cells) เช่น เฉพาะพื้นที่ที่ถูกกำหนดรูปแบบเอาไว้ หรือกรอกค่าไว้ (ตรวจสอบได้โดยกดปุ่ม Ctrl+End จะพาไปเซลล์ขวาล่างสุดของตารางที่ถูกใช้งาน Used Cell)
  6. เมื่อใดไปกรอกค่าหรือเซลล์ใดเซลล์หนึ่งในพื้นที่ Column $E:$Z มีการเปลี่ยนแปลง จะกระตุ้นให้ทุกสูตรที่อ้างอิงพื้นที่นั้น คำนวณใหม่ตามทันที ซึ่งอาจเป็นการคำนวณที่ไม่จำเป็น แม้ไม่ได้กระทบกับคำตอบในเลขที่ Column 2 ที่กำหนดไว้ก็ตาม
  7. การกำหนดพื้นที่แบบทั้ง Column $E:$Z จะส่งผลทำให้ไม่สามารถ Cut พื้นที่ตารางข้อมูลเฉพาะส่วนที่มีรายการบันทึกไว้ไปที่อื่น หากต้องการย้ายตารางต้องย้ายทั้ง Column ไปด้วยกัน
  8. แทนที่จะกำหนดพื้นที่ทั้ง Column $E:$Z ควรกำหนดพื้นที่เฉพาะพื้นที่จาก Row ที่จำเป็นในการค้นหาค่าเท่านั้น โดยอาจกำหนดขอบเขตเผื่อไว้เอง เช่น E2:Z1000 หรือใช้คำสั่ง Table ช่วย หรือใช้สูตร Offset กำหนดขอบเขตเท่าที่จะนำไปใช้งานต่อ
  9. ในการสร้างสูตร VLookup โดยทั่วไปมักกำหนดพื้นที่ตารางขนาดเดียวกันไว้เสมอ จากนั้นจึงใส่เลขที่ Column ของค่าเรื่องที่ต้องการลงไป เช่น เลข 2 ในตัวอย่างนี้
  10. การใช้พื้นที่ขนาดเดียวกัน แม้จะหาค่าจากพื้นที่แค่ไม่กี่ Column ก็ตาม ช่วยให้สร้างสูตรได้ง่ายและตรวจสอบได้ทันทีว่าหาค่าจากตารางเดียวกัน แต่ถ้ากำหนดขนาดให้ขยายตามเลขที่ Column ที่ต้องการค้นหา แม้ช่วยให้สูตรทำงานได้เร็วขึ้น แต่จะสร้างสูตรยากขึ้นมาก ต้องเทียบข้อดีข้อเสียนี้ให้ดีว่าจะคุ้มไหม
  11. เลขที่ Column 2 ของคำตอบที่ต้องการ สามารถใช้สูตร Match หรือ Columns ช่วยนับจำนวน Column ให้ก็ได้จะได้ไม่ต้องนับเอง แต่ต้องสร้างสูตรที่เผื่อว่ายังทำงานได้ถูกต้องเมื่อมีการโยกย้ายหรือสลับ Column ไปที่อื่น
  12. ควรเลือกใช้สูตร VLookup เมื่อมั่นใจว่าโครงสร้างตารางฐานข้อมูลเป็นมาตรฐาน ไม่มีการเปลี่ยนแปลงโครงสร้างอีกแล้วจะมั่นใจขึ้น หากไม่มั่นใจว่าผู้ใช้อาจซนหรือทำการแก้ไขที่ทำให้สูตรหาค่าเพี้ยน ควรเปลี่ยนไปใช้สูตร Match Index หรือ XLookup ( หากใช้ 365 กันทั้งบริษัท)
  13. ค่าที่ใช้ค้นหา หรือ B4 หลังวงเล็บด้านหน้านั้น ควรใส่ $ ให้เหมาะสมว่าอีกหน่อยจะลอกสูตรนี้ไปวางที่ใดและอยากจะใช้ค่าเดิมในการค้นหาต่อไปอีกหรือไม่
  14. ตอนที่พิมพ์สูตรควรพิมพ์ด้วยตัวอักษรตัวเล็ก vlookup แล้วสังเกตว่าถ้าสะกดสูตรถูกต้อง จะเปลี่ยนไปเป็น VLOOKUP ตัวใหญ่ทั้งหมดให้เอง
  15. การกำหนดตำแหน่งพื้นที่ตาราง ไม่ควรใช้กรอกตำแหน่งอ้างอิงลงไปเองหรือต้องใช้เมาส์หรือแป้นพิมพ์ไปเสียเวลาเลือกพื้นที่ แต่ควรตั้งชื่อ Range Name ไว้ก่อนแล้วกดปุ่ม F3 เพื่อนำชื่อตารางลงไปใส่ในสูตร
  16. ถ้ายังไม่เก่ง ยังไม่คล่องกับการใช้สูตร ไม่ควรใช้กับตารางพื้นที่ขนาดใหญ่ที่ตรวจสอบยากว่าหาค่าถูกหรือไม่ ควรฝึกใช้สูตรกับตารางพื้นที่ขนาดเล็กที่มองออกได้ทันทีว่าถูกหรือผิด
  17. ไม่ควรคิดห่วงแค่เวลาที่ Excel ใช้ในการคำนวณหาค่ามาให้ แต่ควรมุ่งลดเวลาและขั้นตอนในการสร้างสูตรนี้ให้เหลือน้อยที่สุด และเมื่อส่งไปให้ผู้อื่นใช้ต่อ ต้องตรวจสอบและทำความเข้าใจที่ไปที่มาได้ง่าย

อื่นๆ มี comment อะไรอยากเพิ่มอีกไหมครับ

เชิญชมคลิปเรื่อง Smart VLookup ได้จาก

https://www.excelexperttraining.com/book/index.php/excel-articles-and-videos/excel-expert-application/vlookup-index-smart-calculation

Related Articles

© Copyright 1999

สงวนลิขสิทธิ์ตามกฎหมาย

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

อนญาตให้นำไปใช้เพื่อสาธารณประโยชน์โดยขอให้ระบุที่มาและชื่อผู้เขียนกำกับไว้ด้วยเสมอ

ลิงก์เว็บ Excel Expert Training

เว็บสำหรับ เรียนออนไลน์

เว็บสำหรับ เรียนแบบกลุ่ม-ส่วนตัว

ติดตามข่าวสารได้จาก facebook

ถามปัญหาได้ที่ กลุ่มคนรัก Excel

และไลน์กลุ่ม Excel Expert Group

ที่อยู่และการติดต่อ

สมเกียรติ ฟุ้งเกียรติ 7/1 รามคำแหง ซอย 35 หัวหมาก บางกะปิ กทม 10240 โทร 097-140-5555, 02-718-9331

Excel@ExcelExpertTraining.com

sfk234x234