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