คนทำงานส่วนใหญ่ น่าจะเคยใช้ฟังก์ชันใน Excel กันมาไม่น้อย
หนึ่งในฟังก์ชันยอดฮิตที่ทุกคนต้องใช้ ก็คือฟังก์ชันที่มีชื่อ 3 พยางค์ว่า
VLOOKUP
VLOOKUP เป็นฟังก์ชันที่มีประโยชน์มาก เรียกได้ว่าเป็นฟังก์ชันแรกในตระกูล Lookup & Reference (เช่น HLOOKUP, MATCH, INDEX, INDIRECT) ที่เราฝึกใช้กัน
แต่… VLOOKUP ก็เป็นฟังก์ชันที่มีข้อด้อยพอตัว ซึ่งบางคนรู้ แต่บางคนไม่รู้!
แล้วเจ้าข้อด้อยทีว่ามันคืออะไรล่ะ?
วันนี้เรามาชำแหละ VLOOKUP ให้ถึงกึ๋นทะลวงทรวงกันเลยครับ ^__^
VLOOKUP มีประโยขน์อย่างไร?
VLOOKUP ใช้ดึงข้อมูลที่เกี่ยวข้อง จากข้อมูลอ้างอิงที่เรามี (ถ้าใครมีประสบการณ์ใช้ VLOOKUP แล้ว สามารถกระโดดข้ามไปอ่านย่อหน้าถัดไปได้เลยครับ ขอทวนสำหรับผู้ที่ไม่คุ้นเคยสักเล็กน้อย)
เช่น อยากรู้ว่า Product A อยู่ในกลุ่มสินค้า (Product Group) ประเภทใด
ถ้าเราใช้ฟังก์ชันเกี่ยวกับ Lookup & References ไม่เป็น เราก็ต้องเปิดตารางอ้างอิง แล้วหยอดมือทีละตัวๆ
มีสินค้า 100 ตัวก็ต้องหยอดมือ 100 ครั้ง เป็นภาระกับชีวิตสุดๆ T_T
แต่ถ้าเราใช้ VLOOKUP เป็น ชีวิตมันก็ง่ายขึ้นเยอะ


เวลาใช้งาน ผมแนะนำให้พิมพ์สูตรลงไปในเซลล์นั้นโดยตรง หรือพิมพ์ใส่ formula bar นะครับ ไม่ค่อยแนะนำให้เรียกหน้าต่างช่วยใส่ฟังก์ชัน (Function Argument Box) ขึ้นมา แล้วใส่ค่าลงไปทีละค่า (Argument) เพราะจะทำให้เราไม่เข้าใจโครงสร้างสูตร และยากจะใส่สูตรซ้อนสูตรเป็นได้
VLOOKUP มีข้อเสียอย่างไร?
ข้อเสียใหญ่เบ้อเร่อเฮิ่มข้อแรกของเจ้า VLOOKUP คือ ต้องใส่ลำดับที่ของคอลัมน์ที่ต้องการ (col_Index_num)
ส่วนใหญ่มักใส่กันเป็นตัวเลข เช่น 2
ในทางปฏิบัติ เรามักดึงข้อมูลที่เกี่ยวข้องมากกว่า 1 คอลัมน์ พอก็อปปี้สูตรไปทางขวา (เพื่อดึงข้อมูลจากคอลัมน์อื่นที่อยู่ติดกัน) ตัวเลข 2 มันก็ยังเป็น 2 เหมือนเดิม!
มันไม่ได้เปลี่ยนเป็น 3, 4, 5 ให้เราแบบอัตโนมัติ
จึงทำให้ไม่สามารถก็อปปี้สูตรไปทางขวาได้
หรือถ้าจะก็อปปี้สูตรไปทางขวา ก็ต้องแก้ลำดับที่ในสูตร(เอง)ทุกครั้ง
ถ้าเราดึงข้อมูลมา 5 คอลัมน์ ก็ต้องแก้ตัวเลขนี้ 5 ครั้ง ซึ่งมันไม่สนุกเลย T_T
ผมเคยเจอเพื่อนบางคน พยายามลบข้อด้อยข้อนี้ ด้วยการสร้างสูตรแบบนี้ครับ
จากภาพคือ แทนค่า 2 ด้วยคำว่า Product!B$1
ซึ่งเจ้า Product!B$1 ก็คือตัวเลขที่ใส่ไว้บนหัวคอลัมน์ของตารางอ้างอิง (ตารางชื่อ Product)
หรือก็คือ ใช้ค่า B1 (Product!B$1) แทน 2 นั่นเอง (Product คือชื่อของ worksheet ที่เป็นตารางอ้างอิง)
วิธีนี้สามารถลบข้อด้อยของการลากสูตรไปทางขวาได้ แต่ข้อเสียคือ ถ้าบังเอิญมีใครมาแทรกคอลัมน์ จากเดิม ProductGroup เคยอยู่ในคอลัมน์ B (ซึ่งถือเป็นคอลัมน์ลำดับที่ 2 ของตาราง) โดนแทรกแล้วเลื่อนไปเป็นคอลัมน์ C แทน (ซึ่งเป็นคอลัมน์ลำดับที่ 3 ของตาราง) ผลลัพธ์ที่ได้จาก VLOOKUP จะผิดทั้งหมด!
ในทางปฏิบัติแล้ว ผมไม่แนะนำวิธีนี้ แต่แนะนำให้ใช้ 1 ใน 2 วิธีนี้แทน (ถ้าไม่อยากใช้ฟังก์ชันอื่นแทน VLOOKUP) คือ
1 ใช้ฟังก์ชัน COLUMNS ช่วยนับลำดับคอลัมน์
จากเดิมที่เราต้องพิมพ์เลข 2 ลองเปลี่ยนวิธีการเขียนสูตรเล็กน้อยแบบนี้ครับ
จากภาพ จะเห็นได้ว่าสูตรกลายเป็น =VLOOKUP($A4,Product!$A$3:$D$13,COLUMNS(Product!$A$2:B$2),0)
หรือก็คือ ใช้
COLUMNS(Product!$A$2:B$2)
แทนตัวเลข (2) นั่นเอง!
ซึ่งเจ้าฟังก์ชัน COLUMNS นี้มีจุดประสงค์เพื่อ นับคอลัมน์ที่อยู่ตั้งแต่เซลล์ A2 ถึง B2 ว่ามีทั้งหมดกี่คอลัมน์ (คำตอบคือ 2)
ถ้าเราเขียนสูตร โดยล็อคเฉพาะคอลัมน์ A และไม่ล็อคคอลัมน์ B เมื่อเราลากสูตรไปทางขวา คอลัมน์ B ก็จะเปลี่ยนเป็นคอลัมน์ C (แต่คอลัมน์ A ไม่มีผลกระทบเนื่องจากถูกล็อคไว้) คำตอบที่ได้ก็คือ 3 โดยที่เราไม่ต้องแก้สูตรทีละคอลัมน์เลย!
ด้วยวิธีนี้ เกิดมีใครปรารถนาดี(ประสงค์ร้าย) แทรกคอลัมน์ในตารางอ้างอิง ก็จะไม่มีผลอะไรกับชีวิตเราอีกต่อไป
เมื่อคอลัมน์เลื่อน จากสูตรที่เคยเป็น
COLUMNS(Product!$A$2:B$2)
ก็จะถูกแก้เป็น
COLUMNS(Product!$A$2:C$2)
โดยอัตโนมัติ
ชีวิตเราดีขึ้นไหมครับ ^_^
2 ใช้ฟังก์ชัน MATCH ช่วยนับลำดับคอลัมน์
ถ้าสามารถตั้งชื่อคอลัมน์ของตารางผลลัพธ์ ให้ตรงกับตารางอ้างอิงได้ ผมแนะนำให้ใช้ฟังก์ชัน MATCH แทนฟังก์ชัน COLUMNS
เมื่อเราแกะสูตรแล้วจะพบว่า การใช้ฟังก์ชัน MATCH ให้ความเข้าใจดีกว่าฟังก์ชัน COLUMNS
เนื่องจากเจ้า MATCH เน้นที่ชื่อคอลัมน์ แต่เจ้า COLUMNS เน้นที่ลำดับของคอลัมน์ (โดยที่ไม่ได้สนใจชื่อคอลัมน์)
เมื่อเรานำชื่อคอลัมน์จากตารางผลลัพธ์ไปเทียบกับตารางอ้างอิง ก็จะทวนสอบได้ง่ายกว่า
แล้วจะเอาฟังก์ชัน MATCH ไปใช้ยังไง?
ลองดูวิธีใช้ในภาพครับ
จะเห็นได้ว่า เปลี่ยนจากตัวเลข (2) เป็น
MATCH(C$1,Product!$A$2:$D$2,0)
ซึ่งการทำงานของเจ้า MATCH(C$1,Product!$A$2:$D$2,0) นั้นจะเช็คว่า คอลัมน์ชื่อ ProductGroup อยู่ในคอลัมน์ลำดับที่เท่าไรของตารางอ้างอิง
ซึ่งเมื่อเทียบกับตารางอ้างอิงแล้ว (ตารางด้านล่าง) ก็คือคอลัมน์ B หรือคอลัมน์ที่ 2 นั่นเอง
เมื่อใช้ฟังก์ชัน MATCH แล้ว ก็สามารถลากสูตรไปทางขวาได้เลย
หลังจากนี้จะมีใครมาแทรกคอลัมน์ในตารางอ้างอิงกี่สิบคอลัมน์ ก็ไม่มีผลอะไรกับเราแล้ว ชีวิตเราดีขึ้นแน่นอนครับ ^o^
..
การชำแหละ VLOOKUP ยังไม่จบนะครับ ยังมีข้อด้อยข้อใหญ่(มาก)อีกข้อหนึ่ง ซึ่งแก้ไขด้วยวิธีนี้ไม่ได้
และยังมีการใช้ VLOOKUP ที่ลงท้ายด้วย 1 (ปกติ VLOOKUP ลงท้ายด้วย 0 หรือ FALSE)
แต่บทความภาคนี้ค่อนข้างยาวแล้ว ขออธิบายต่อในบทความภาค 2 และ ภาค 3 แทนนะครับ ^__^
สำหรับเพื่อนพี่น้องที่ต้องการไฟล์ตัวอย่าง สามารถดาวน์โหลดจากลิงค์นี้ได้เลยครับ
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^
เยี่ยมครับ
ขอบคุณครับ ความเห็นของผู้อ่านก็คือกำลังใจชั้นยอดของคนเขียนครับ แล้วจะเขียนบทความใหม่เรื่อยๆนะครับ
ขอบคุณครับ
โพสต์ด้านบนผมตอบเองนะครับ พอดีรับการแจ้งเตือนทางอีเมลล์เลยกดตอบจากลิงค์ในนั้นเลย เพิ่งทราบว่าระบบจะไม่รับรู้ว่าผมเป็นคนตอบ คราวนี้เลยตอบโดยเข้าจากบล็อกโดยตรงครับ
ขอบคุฟมากนะครับ
ยินดีครับ
ไม่รู้จะแก้วิธีไหน มันขึ้น #Ref ว่าคุณสมบัติเซลล์ไม่เหมือนกัน ก็ทำตารางใหม่เลย
แต่ก็ยังทำไม่ได้อยู่ดี
แต่ลองให้หาค่า คอลัมน์ที่ 1 (ตัวมันเอง) มันก็แสดงผลได้
เกิดจากสาเหตุอะไรได้บ้าง และจะแก้ปัญหาได้อย่างไรบ้าง
ขอบคุณค่ะ
ขอข้อมูลเพิ่มเติมหน่อยครับ เช่น lookup_value คืออะไร ตารางอ้างอิงหน้าตาเป็นยังไง (มีใช้ merge cell หรือเปล่า) เขียนสูตรยังไง และ error message แบบเต็มๆคืออะไร
ปกติแล้วถ้าหาค่าตัวมันเองได้ ปัญหานี้จะไม่เกิดขึ้น เดาว่าสาเหตุเกิดจากการจัดฟอร์แมตของตารางอ้างอิงที่ไม่ถูกต้องครับ
if(iserror(index(Jan!$G:$AA,J3,15)),””,index(Jan!$G:$AA,J3,15)) แถวที่ 15 สามารถใช้สูตร Match, หรือ Columns ได้หรือไม่ครับ…
ได้ครับ
จริงๆแล้วการใช้สูตรมีความยืดหยุ่นการใช้ค่าคงที่ครับ
บทความมีประโยชน์มากเลยค่ะ วันนี้ทำงานผ่านไปได้ด้วยบทความนี้
ขอบคุณมากนะคะ
ยินดีครับ ^__^