คนทำงานส่วนใหญ่ น่าจะเคยใช้ฟังก์ชันใน 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 แทนนะครับ ^__^

สำหรับเพื่อนพี่น้องที่ต้องการไฟล์ตัวอย่าง สามารถดาวน์โหลดจากลิงค์นี้ได้เลยครับ

VLOOKUP_150128

.

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^