Blog

Vlookup เป็นฟังก์ชันที่ใช้งานกันแพร่หลายมากอย่างหนึ่ง เป็นฟังก์ชันดึงข้อมูลจากตารางข้อมูลมาแสดงผล กลายเป็นฟังก์ชันพื้นฐานที่ผู้ร่วมงานส่วนใหญ่จะใช้งานเป็นกันทุกคน เพราะเป็นตัวช่วยให้ไม่ต้องไป export จาก excel เพื่อไปดึงข้อมูลหรือรวมตารางใน Access

ถึงแม้ ฟังก์ชันนี้ใช้งานง่าย และทดแทนการเชื่อมข้อมูลใน Access ได้เกือบหมดก็จริง แต่ส่วนตัว ยังชอบใช้ Access มากกว่าเพราะบางอย่างที่ทำใน Access ให้ผลตรงใจกว่า แต่บางคนก็อาจจะมองว่าเป็นเพราะความเคยชินกับ Access ทำให้เกิดอคติไปเอง เรื่องนี้ก็ไม่รู้เหมือนกัน แต่ถ้าต้องใช้ excel ก็ไม่มีปัญหาอะไรเหมือนกัน เพราะในเมื่อผู้ร่วมงานส่วนใหญ่นิยมใช้ Excel มากกว่า Access ก็เลยต้องหัดใช้ Excel ตามเขาไป

UPDATE

ปัจจุบัน Microsoft ได้ออกฟังก์ชันใหม่ คือ XLookup มาแทนที่เรียบร้อยแล้ว แต่ใช้ได้กับ Excel ในเวอร์ชัน 2019 ขึ้นไป หรือ Microsoft 365 เท่านั้น ถ้าเป็น Excel เวอร์ชันเก่ากว่านั้น ยังคงต้องใช้ Vlookup เหมือนเดิมไปก่อน ดังนั้น หากใครคิดว่าจะต้องเขียนสูตรการใช้งานใน Excel ที่ต้องใช้งานร่วมกับคนอื่น แล้วไม่มั่นใจว่า Excel ที่คนอื่นใช้เป็นเวอร์ชันไหน การใช้ฟังก์ชันนี้ก็ยังเป็นตัวเลือกที่ดี แต่ถ้าไม่ต้องใช้งานร่วมกับใคร แนะนำว่าให้ใช้ XLookup จะสะดวกกว่ามาก

ท่านสามารถอ่านคำอธิบาย และ วิดีโอ จากเว็บไซต์ของไมโครซอฟต์ได้ที่นี่

Vlookup

ก่อนอื่น เรามาดูว่าฟังก์ชันนี้ใช้งานอย่างไร มีข้อดี ข้อจำกัดอย่างไรบ้าง

ฟังก์ชันนี้อยู่ในกลุ่ม reference สำหรับค้นหาค่าในตาราง ตัวอย่างเช่น  (ดูภาพประกอบก็ได้) เรามีข้อมูลตารางทั้งหมด 7 คอลัมน์ ตั้งแต่ คอลัมน์ D ถึง J ดังนี้

• คอลัมน์ D เป็นข้อมูล ลำดับที่
• คอลัมน์ E เป็นข้อมูล Code
• คอลัมน์ F เป็นข้อมูล การใช้ประโยชน์บริเวณตำแหน่งที่ตั้ง
• คอลัมน์ G เป็นข้อมูล ตำแหน่งที่ตั้ง
• คอลัมน์ H เป็นข้อมูล ผิวจราจร
• คอลัมน์ I เป็นข้อมูล ความกว้างผิวจราจร
• คอลัมน์ J เป็นข้อมูล เขตทาง

สมมติว่า เราต้องทำงานชิ้นหนึ่ง ซึ่งให้ข้อมูลเป็น Code มา เราอยากรู้ว่า Code นี้ คือ ตำแหน่งที่ตั้งใด แทนที่จะเปิดตาราง แล้วใช้ตามองหาไปตามลำดับ (ซึ่งบางคนอาจถนัดแบบนี้?) แล้วมาบันทึกสิ่งที่พบ ทีละช่อง ถ้ามีข้อมูลน้อย ๆ แค่สองสามข้อมูล การเปิดตารางดูแล้วบันทึกเองก็ไม่เป็นไร แต่ถ้ามีข้อมูลเป็นร้อยเป็นพันหรือมากกว่า คงไม่ใช่เรื่องน่าทำมากนัก เป็นการ Work Hard แบบไร้สมอง แถมยังมีความเสี่ยงจากการบันทึกข้อมูลผิดอีก ขอแนะนำให้ใช้วิธี lookup แทน จะช่วยให้ทำงานสะดวกกว่า

เรามา work smart โดยการใช้ Lookup กันดีกว่า จะเป็น V (Vertical – แนวตั้ง) หรือ H (Horizon – แนวนอน) ก็คล้ายกัน หรือ ใครจะใช้ Index Match ก็ได้เช่นกัน (ซึ่งจะว่าไป ช่วยเรื่องข้อจำกัดของ lookup หลายเรื่อง แต่ปัจจุบันมี XLookup อุดช่องว่างไปเยอะแล้วเหมือนกัน)

รูปแบบการเขียนสูตร

การเขียนสูตร สำหรับฟังก์ชันนี้ มีดังนี้

= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

นั่นคือ การใช้งานฟังก์ชันนี้ จะมีองค์ประกอบที่เราต้องใส่ จำนวน 4 องค์ประกอบ ด้วยกัน

1. lookup_value – ค่าที่ต้องการค้นหา
2. table_array – ช่วงในตารางที่ครอบคลุมตั้งแต่ “ค่าที่ต้องการค้นหา” ไปจนถึง ค่าที่แสดงผลลัพธ์
3. col_index_num – ลำดับของคอลัมน์ที่ต้องการให้แสดงผลลัพธ์
4. range_lookup – เราต้องการแสดงเฉพาะค่าที่ตรงกันกับค่าที่ต้องการต้นหาเท่านั้นหรือไม่?

จากตัวอย่าง เราจะได้ออกมาแบบนี้

ข้อมูล Code อยู่ในคอลัมน์ E (ช่วง E3 – E9)

ตำแหน่งที่ตั้ง อยู่ในคอลัมน์ G (ช่วง G3 – G9)

เราจะหาว่าข้อมูล Code ที่ได้รับมา คือ ตำแหน่งที่ตั้งใด โดยจะใส่ค่าค้นหาใน A3 และให้แสดงผลใน B3

การทำงาน

กระบวนการทำงานก็คือ เอาค่าของ Code ใน A3 ไปเทียบกับค่าของ Code ในช่วง E3 – E9 และให้แสดงค่าของคอลัมน์ G ที่ตรงกับค่า Code ที่ได้ในช่วง E3 – E9 มาแสดงในช่อง B3

ดังนั้น ใน B3 เราจะใช้ฟังก์ชัน Vlookup เขียนสูตรดังนี้

=VLOOKUP(A3,E3:J9,3,FALSE)

อธิบายได้ดังนี้ (ทำรูปภาพประกอบ หวังใจว่าจะเข้าใจได้ง่ายขึ้น)

จากตัวอย่างข้างต้น เราเขียน step การสร้างสูตรดังนี้

(ดูวิดีโอฉบับสั้น ไม่เกิน 5 นาที ก่อนก็ได้)

1 – คลิกเลือกที่ Cell B3 ซึ่งเป็นช่องแสดงผล

2 – พิมพ์ = Lookup(

3– คลิกที่ A3 หรือ พิมพ์ A3 ลงไปตรง ๆ ตามด้วย จุลภาค (,) – ตรงนี้คือ ค่าที่ต้องการค้นหา หรือ lookup_value เราต้องการ ค่าที่จะกรอกใน A3

Tip: ถ้าจะสร้างรหัสเพื่อใช้เป็น Identity อย่าใช้ตัวเลขอย่างเดียว เพราะบางครั้งในกรณีที่ข้อมูลมีปริมาณมากแล้วไม่ได้ตรวจสอบให้ดีข้อมูลที่เป็นตัวเลขอาจจะโดนจัดเก็บในรูปแบบ “ข้อความ”

Tip: เรื่องนี้เป็นเรื่องที่ต้องระวังเวลาทำตารางข้อมูลไม่ว่าจะเป็นเรื่องใด ข้อมูลที่แสดง identity หรือข้อมูลเฉพาะระเบียน (ซึ่งมักจะใช้เป็นรหัสหรือไม่ใช่รหัสก็ตาม) ไม่ควรมีข้อมูลนี้ซ้ำกัน ตรงนี้ต่างจาก Access ที่จะแสดงข้อมูลซ้ำทั้งหมดให้เลย ถ้าใช้ vlookup จะต้องใส่ Array หรืออาจจะใช้ฟังก์ชันอื่นมาช่วยอีก ถ้ามีข้อมูลซ้ำกันจะเป็นอย่างไร จะแสดงให้เห็นช่วงท้ายบทความนี้

4– คลิกลาก Cell E3 ถึง J9 หรือพิมพ์ E3:J9 – ช่วงตารางข้อมูล (table_array) E3:J9 ตรงนี้คือ เรานับจากคอลัมน์ E ใช้ตั้งแต่ E3 (ไม่เอาหัวตาราง) ซึ่งเป็นข้อมูล Code ไปจนถึง คอลัมน์ J ซึ่งข้อมูลสุดท้ายในคอลัมน์คือ อยู่ใน J9

Tip: ค่าการค้นหาต้องอยู่ในคอลัมน์แรกของช่วงที่เลือกมา เช่นตัวอย่าง ตารางข้อมูลเริ่มที่คอลัมน์ D ก็จริง แต่เวลาเลือกขอบเขตหรือช่วงข้อมูล จะเริ่มจากคอลัมน์ E3 เพราะข้อมูล Code อยู่ในคอลัมน์ E ซึ่งเป็นจุดเริ่มแรกของสำหรับค่าที่จะใช้ค้นหา ตรงนี้เป็นข้อจำกัดของฟังก์ชันนี้ว่า ค่าที่ค้นหา จะต้องแสดงในคอลัมน์แรกของตารางข้อมูล ในกรณีที่ไม่อยากย้ายตำแหน่งคอลัมน์ มีทางเลือกให้ใช้ index – match หรือ XLookup

Tip: แต่สำหรับค่าสุดท้าย แนะนำว่าเอาแค่ถึงคอลัมน์สุดท้ายที่ต้องการใช้ก็พอ ในตัวอย่างนี้ความจริง เอาแค่ G9 ก็ได้ แต่ทำให้ดูในกรณีว่า เผื่อสำหรับการดึงข้อมูลจากคอลัมน์อื่นมาแสดงด้วย ถ้าข้อมูลเล็กน้อยจะไม่ค่อยมีปัญหา แต่ถ้าข้อมูลที่ท่านใช้มีปริมาณมาก เช่นมีข้อมูลนับแสนระเบียน การใส่อะไรที่ไม่จำเป็นไปอาจจะหมายถึงการประมวลผลที่เกินความจำเป็นซึ่งทำให้เกิดความหน่วงจากการประมวลผล

5– พิมพ์เลข 3 ตรงนี้คือ ลำดับของคอลัมน์ที่เราต้องการให้แสดงผล ซึ่งค่าที่เราต้องการให้แสดงผลลัพธ์อยู่ในคอลัมน์ G การนับ ให้นับจากช่วงที่เราเลือก ซึ่งในตัวอย่างนี้จะเป็นคอลัมน์ E จนถึง J เราจะนับคอลัมน์ E เป็นลำดับที่ 1 คอลัมน์ F เป็นลำดับที่ 2 คอลัมน์ G เป็นลำดับที่ 3 …

6- พิมพ์ False หรือ 0 แล้วใส่ วงเล็บปิด เคาะ enter ดูผลลัพธ์ได้เลย

เราใส่ 0 หรือ False เพื่อระบุว่า แสดงเฉพาะค่าที่ตรงกันกับคำค้นหาเท่านั้น เช่น ถ้าต้องการค้นหา Code S02 ก็ให้แสดงค่าของ S02 เท่านั้น หากไม่พบค่า S02 (อาจจจะเป็นเพราะพิมพ์ผิด หรือ อื่น ๆ) จะแสดงค่าเป็น #N/A อ่านรายละเอียดเกี่ยวกับค่าผิดพลาดที่เกิดขึ้นได้ที่เรื่อง#N/A

ถ้าระบุค่าเป็น TRUE (หรือ 1) จะแสดงผลที่ตรงกันโดยประมาณ ถ้าไม่ระบุค่าใดลงไป จะแสดงผลเสมือนระบุค่าเป็น TRUE หรือค่าที่ตรงกันโดยประมาณเสมอ

Warning: ค่า range_lookup นี่ส่วนใหญ่จะเลือกให้เป็น FALSE เพราะต้องการให้แสดงผลลัพธ์ตรงกับค่าที่ต้องการเท่านั้น แต่ถ้าข้อมูลซ้ำกัน จะแสดงผลแค่ข้อมูลแรกที่พบเท่านั้น ในตัวอย่าง S03 มีสองค่า (ลำดับที่ 5 กับ ลำดับที่ 7) แต่ vlookup จะแสดงค่าแรกที่เจอเท่านั้น อันนี้ตารางข้อมูลมีข้อมูลไม่เท่าไหร่เลยสังเกตได้ง่าย แต่ถ้าต้องทำกับข้อมูลหลายพันแถว หรือหลายหมื่นแถว ถ้าไม่ระวังให้ดีมีข้อมูลตัวที่จะใช้หาค่าซ้ำกัน ก็จะได้ผลลัพธ์ที่ไม่ครบถ้วน

อีกตัวอย่างหนึ่ง

แล้วบางคนอาจจะสงสัย (หรือไม่สงสัย? ว่าแต่มีใครอ่านหรือเปล่า?) ว่า ทำไมถึงมี range_lookup แบบ TRUE มีไว้ทำไม แถมยังเป็นค่าตั้งต้นเสียด้วย (คือถ้าไม่ใส่กำกับไว้ จะถือเสมือนว่าใส่เป็น 1 หรือ True) เพราะดูเหมือนไม่มีการใช้งาน แต่ที่ผ่านมาก็ใช้งานข้อกำหนดแบบประมาณกันเหมือนกัน ตัวอย่างเช่น การให้เกรดนักศึกษาในการสอบ

• ถ้าสอบได้ 90 คะแนนขึ้นไปได้เกรด A
• ถ้าสอบได้ 80 คะแนนขึ้นไปได้เกรด B
• ถ้าสอบได้ 70 คะแนนขึ้นไปได้เกรด C
• ถ้าสอบได้ 60 คะแนนขึ้นไปได้เกรด D
• และถ้าสอบได้ต่ำกว่า 60 คะแนนขึ้นไปได้เกรด F

แบบนี้ถ้าคะแนนที่ได้ สมมติเช่น 45 ก็จะได้ F ขึ้นมา

แต่วิธีนี้ ต้องมั่นใจว่าได้มีการเรียงลำดับตารางสำหรับอ้างอิงไว้ดีแล้ว!

คำเตือน

ค่า range_lookup ที่เป็น false ไม่น่ามีปัญหา คือ มันแสดงผลเฉพาะที่ค่าตรงกันเท่านั้น ถ้าไม่พบมันจะแสดงค่า #N/A แต่ถ้าใช้ true หรือต้องการหาค่าที่เป็นช่วงโดยประมาณอันนี้ต้องระวังเวลาทำตารางข้อมูล ค่าที่ใช้จะต้องเรียงลำดับกันเท่านั้น เพราะพื้นฐานการทำงานของ vlookup จะค้นหาจากบนลงล่าง เวลามันเจอค่าแรกเป็นอะไร มันก็เลือกตรงนั้นเลย เช่นเดียวกับค่าซ้ำมันจะส่งเฉพาะค่าแรกที่เจอให้เท่านั้น

ตัวอย่างเรื่องการตัดเกรด สมมติว่า เราไม่ได้จัดการเรียงข้อมูลให้ดีจากน้อยไปหามาก เกิดเอา 70 = C ไปไว้ล่างสุด จะเกิดอะไรขึ้น?

อย่างที่เห็น คะแนนที่อยู่ระหว่าง 70 – 79 ซึ่งควรจะได้เกรด C กลายเป็นได้เกรด D ไปแทน

สาเหตุที่เป็นเช่นนี้ก็ต้องทำความเข้าใจหลักการทำงานของ Lookup (ทั้ง Vertical และ Horizon) การหาค่าของข้อมูลในแนวตั้ง จากข้างบนลงล่าง ถ้าข้อมูลซ้ำ จะแสดงค่าเฉพาะค่าแรกเท่านั้น ก็เป็นเหตุผลเดียวกัน

ปัญหาที่พบบ่อย

ตั้งแต่ทำงานมา ปัญหาที่เจอบ่อย (ไม่นับสร้างสูตรไม่เป็น หรือเขียนสูตรผิด) คือไม่ได้ตรวจสอบข้อมูลที่นำมาใช้ ทำให้ Table Array ที่ใช้ค้นหามีค่าซ้ำ ซึ่งจะแสดงผลเฉพาะค่าแรกที่เจอเท่านั้น ดังนั้นต้องตรวจทานเรื่องข้อมูลที่ใช้เป็น Table Array ให้ดีว่าไม่มีค่าซ้ำกัน

การตรวจทานทำได้ง่ายมาก เมื่อใช้ Condition Formatting เพื่อกำหนดลักษณะของค่าที่ซ้ำกัน โดยคลิกเลือกคอลัมน์ที่ต้องการหาค่าซ้ำ แล้วไปที่ Home > Condition Formatting > Highlight Cells Rules > Duplicate Values วิธีนี้จะทำให้เกิดไฮไลต์ในค่าที่ซ้ำกัน สามารถใช้ตัวกรอง (Filter) เลือก Filter by Color เพื่อกรองตัวที่ซ้ำกันขึ้นมาดูได้ว่าจะแก้หรือจะลบ

ไม่แนะนำให้ใช้ Remove Duplicates เพราะมันจะลบข้อมูลให้เหลือ 1 ข้อมูลทันที โดยที่ไม่ได้ตรวจสอบก่อน ยกเว้นจะเป็นการเลือกทุกคอลัมน์ ซึ่งหมายความว่าข้อมูลจะซ้ำกันทุกรายการ แบบนั้นคือซ้ำจริง จะใช้ Remove Duplicates ก็ไม่มีปัญหา แต่ถ้าเลือกค่าซ้ำจากบางคอลัมน์  อาจจะมีข้อมูลในคอลัมน์อื่นที่ไม่ตรงกัน แบบนั้นต้องตรวจสอบก่อนลบเพราะอาจเกิดจากความผิดพลาดระหว่างบันทึกข้อมูล