มาถึงภาคสุดท้ายของไตรภาค VLOOKUP กันแล้วนะครับ
จากสองภาคแรก ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 1 (VLOOKUP คลุกเคล้า COLUMNS หรือ MATCH) และ ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 2 (VLOOKUP vs INDEX) เราคุยกันเฉพาะ VLOOKUP ที่ลงท้ายด้วย FALSE หรือ 0 เท่านั้น
VLOOKUP แบบนี้มีชื่อทางการว่า VLOOKUP แบบ Exact Match
ยังมี VLOOKUP อีกแบบนึง ที่ลงท้ายด้วย TRUE หรือ 1 (หรือไม่ใส่องค์ประกอบลำดับที่ 4 ในสูตร)
VLOOKUP แบบนี้มีชื่อทางการว่า VLOOKUP แบบ Approximate Match
VLOOKUP แบบ Exact Match ต่างกับ Approximate Match ยังไงน่ะหรือครับ?
ถ้าเป็นแบบ Exact Match ค่าที่ค้นหาต้องเหมือนค่าในตารางอ้างอิงแบบเป๊ะๆ ถ้าไม่เป๊ะ คำตอบที่ได้คือ #N/A
ถ้าเป็นแบบ Approximate Match ค่าที่ค้นหาเหมือนค่าในตารางอ้างอิงแบบ “ครือๆ” ก็พอ ถ้าไม่เหมือนเป๊ะ มันจะดึงค่าใกล้เคียงมาให้เอง
เหมือนแบบ “ครือๆ” คืออะไรน่ะหรือครับ?
ขออธิบายด้วยตัวอย่างนี้ครับ น่าจะเห็นภาพได้ง่ายที่สุด
สมมติว่า เราเป็นอาจารย์ หลังจากตะลุยตรวจข้อสอบนักศึกษาจนหมดแล้ว ก็ถึงเวลาให้เกรด
เกณฑ์การให้เกรดเป็นแบบนี้
ปัญหาคือ มีนักศึกษาตั้ง 500 คน ถ้ามานั่งใส่เกรดทีละคน มีหวังทำถึงตีสองแน่นอน T_T
ถ้าใช้ IF ช่วย ก็ต้องใช้ IF ซ้อนกัน 8 ชั้น !
8 ชั้น !!
ซ้อนกันเยอะขนาดนี้ แค่คิดก็เศร้าแล้ว
ถึงเขียนได้ โอกาสเขียนผิดก็สูงมาก
ยิ่งถ้าเกณฑ์การให้คะแนนเปลี่ยน ต้องมานั่งเปลี่ยนสูตรใน IF ทุกชั้น
แบบนี้ไม่สนุกเลย T_T
แต่เดี๋ยวก่อน !
เรื่องนี้จะไม่เป็นปัญหาเลย ถ้าใช้ VLOOKUP แบบ Approximate Match
ใช้ยังไงน่ะหรือครับ?
จากเคสนี้ เราเก็บค่าคะแนนสอบไว้ที่เซลล์ E4 แล้วต้องการให้เซลล์ F4 แสดงผลว่า คะแนนนี้ (77) ได้เกรดเท่าไร
แค่เขียนสูตรในเซลล์ F4 ว่า
=VLOOKUP( E4, $A$4:$C$12, 3 , 1 )
ได้คำตอบ B+
จบ!
ต่อให้มีนักศึกษา 5,000 คน แค่ก็อปปี้สูตรลงมาด้านล่าง แว๊บเดียวเสร็จ ^__^
VLOOKUP แบบนี้ใช้งานยังไงน่ะหรือครับ?
ลองเทียบโครงสร้างของ VLOOKUP กันแบบตัวๆนะครับ
โครงสร้างปกติของ VLOOKUP คือ
=VLOOKUP ( Lookup_value , Table_array , Col_index_num , Range_lookup )
Lookup_value คือ ต้องการดึงค่าของอะไร ในที่นี้คือ E4 (คะแนน 77)
Table_array คือ ตารางอ้างอิง ในที่นี้คือเกณฑ์ที่เราใช้สำหรับการให้เกรด หรือเขียนไว้ในช่วงเซลล์ $A$4:$C$12 (ควรเขียนแล้วล็อคด้วยดอลลาร์ไซน์ ($) เพื่อป้องกันการเลื่อนเวลาลากสูตร)
Col_index_num ค่าที่ต้องการดึง เป็นคอลัมน์ลำดับที่เท่าไรของตารางอ้างอิง ในที่นี้คือ 3 เพราะเราต้องการดึงเกรด เกรดอยู่ในคอลัมน์ C ซึ่งถือเป็นคอลัมน์ลำดับที่ 3 ของตารางอ้างอิง
Range_lookup คือ ระบุว่าให้ค้นหาแบบใด ระหว่าง Exact Match หรือ Approximate Match
- ถ้าเป็น Exact Match ให้ใส่ FALSE หรือ 0
- ถ้าเป็น Approximate Match ให้ใส่ TRUE หรือ 1 หรือปล่อยว่างไปเลยก็ได้ (ถ้าเราไม่ใส่ เอ็กเซลจะทึกทักว่าเราต้องการค้นหาแบบ Approximate Match)
เคสนี้ คะแนนอาจไม่จำเป็นต้องตรงกับคะแนนที่เขียนในคอลัมน์ซ้ายสุดแบบเป๊ะๆ
เช่น 77 อยู่ในช่วงของ 75 – 79
ไม่ใช่ตัวเลขที่ตรงกับ 75 แบบเป๊ะๆ แต่ก็สามารถดึงคำตอบที่ถูกต้อง (B+) ออกมาได้
เพราะเราใช้ VLOOKUP แบบ “ครือๆ” หรือแบบ Approximate Match นั่นเอง
แล้วถ้าข้อมูลตรงกับค่าในคอลัมน์ด้านซ้ายแบบเป๊ะๆล่ะ?
ถ้าตรงแบบเป๊ะๆ ก็จะดึงค่าได้ตามนั้นครับ
เช่น คะแนน 60 ได้เกรด C
จริงๆแล้ว การทำงานของ VLOOKUP แบบ Approximate Match นี้ เอ็กเซลต้องการแค่คอลัมน์อ้างอิง และคอลัมน์ที่ต้องการดึงค่า เพียง 2 คอลัมน์เท่านั้น
เช่นเคสนี้ เอ็กเซลต้องการแค่คอลัมน์ A (ที่เก็บคะแนนขอบบนของแต่ละช่วง) และ คอลัมน์ C เท่านั้น
คอลัมน์ B (ที่เก็บคะแนนขอบบนของแต่ละช่วง) จะมีหรือไม่มีก็ได้
หรือจะสร้างตารางอ้างอิงให้มีแค่ 2 คอลัมน์ แบบนี้ก็ได้
ได้ผลลัพธ์เหมือนกัน (อย่าลืมเปลี่ยน Col_index_num จาก 3 ให้เป็น 2 ด้วยนะครับ เพราะเกรดถูกเลื่อนมาอยู่ในคอลัมน์ B ซึ่งถือเป็นคอลัมน์ลำดับที่ 2 ของตารางอ้างอิง)
แต่การเขียนตารางอ้างอิงแบบนี้ (ระบุเฉพาะขอบล่าง) อาจยากต่อการสื่อสาร และมีโอกาสงง การระบุทั้งขอบบนและขอบล่าง ให้ความเข้าใจที่ดีกว่า
ถ้าศึกษาในเชิงลึก จะพบว่า VLOOKUP แบบ Approximate Match ทำงานเร็วกว่าแบบ Exact Match หลายเท่า !!
ยิ่งถ้าใช้กับข้อมูลเยอะๆ จะพบว่าเร็วกว่ามากๆๆๆ
VLOOKUP แบบ Approximate Match นี่สุดยอดเลยใช่ไหมครับ ^__^
งั้นเราใช้ VLOOKUP แบบ Approximate Match แทน Exact Match เลยได้ไหม?
คำตอบคือ ได้แบบมีเงื่อนไข ครับ
เงื่อนไขที่ว่า คือ
ต้องเรียงลำดับข้อมูลในตารางอ้างอิง (คอลัมน์ซ้ายสุด) จากน้อยไปมาก เท่านั้น !
ห้ามเรียงจากมากไปน้อย หรือ ห้ามเรียงลำดับแบบตามใจฉัน ไม่งั้นค่าที่ดึงมามีโอกาสผิดสูงมาก
ถ้าข้อมูลเป็นตัวเลข (เช่นเคสนี้) การเรียงข้อมูลในตารางอ้างอิงจากน้อยไปมาก คงไม่ใช่ปัญหา
แต่ถ้าข้อมูลเป็นตัวหนังสือ (เช่น ชือลูกค้า ชื่อสินค้า) การเรียงข้อมูลจากน้อยไปมาก อาจไม่สะดวกในทางปฏิบัติ ยิ่งถ้าค่าไม่ตรงกันแบบเป๊ะๆ เอ็กเซลจะดึงค่าใกล้เคียงมาแทน ซึ่งอาจไม่ใช่ค่าที่เราต้องการ
(เทคนิคการเขียนสูตรเพื่อป้องกันความผิดพลาดกรณีที่ข้อมูลเป็นตัวหนังสือ คุณเทพเอ็กเซล (inwexcel, ศิระ เอกบุตร) เคยเผยเคล็ดลับไว้ในบทความนี้ ขอบคุณคุณเทพเอ็กเซลสำหรับเทคนิคดีๆครับ)
เพราะฉะนั้น การศึกษา VLOOKUP ให้เข้าใจแบบถ่องแท้ทั้งแบบ Exact Match และ Approximate Match จึงเป็นสิ่งจำเป็น
ยิ่งถ้าใช้ INDEX+MATCH จนคล่องแล้วล่ะก็
วรยุทธ์เอ็กเซลของคุณ คงอยู่ไม่ตำกว่าขั้นที่ 5 ของ 10 ขั้นวิทยายุทธ Excel แน่นอน
ใครที่ผ่านขั้นนี้มาแล้ว อย่าลืมศึกษาฟังก์ชัน OFFSET และ INDIRECT เพื่อลบข้อจำกัดเคสที่ซับซ้อนนะครับ
แล้วคุณจะรู้ว่า ความ “มันส์” มันเป็นยังไง ^^
เช่นเคยเหมือนทุกครั้ง ไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดจากลิงค์ด้านล่างได้เลยครับ
VLOOKUP_ApproximateMatch_170315
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่
อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^_^
อธิบายเข้าใจง่ายดีคะ ขอบคุณคะ
ดีใจที่ชอบนะครับ ^__^
ความเห็นเพิ่มเติม อาจจะ ไม่ตรงกับคำสั่ง vlookup นั้น แต่มีผล
บ่อยครั้งเราจะได้ตารางมาแบบนี้
1 – 49 F
50-54 D
55 -59 D+
ตารางก็ ok นะ แต่ถ้าคะแนนมาเป็น ทศนิยม หละ
เช่น มาเป็น 49.28 คือ ใช้คำสั่ง อาจจะถูกตามคำสั่ง vlookup ทำงานได้
แต่อาจจะไม่ถูกตาม ผู้ใช้ อันนี้ต้องเป็นคำถามไปยังเจ้าของงาน
เพราะ ถ้าบอกว่า49.28 ควรได้D แสดงว่าเราใช้ ค่า 1,50,55 ในการตรวจสอบคงจะไม่ถูกตามความต้องการจริง
สำหรับเคสนี้ สร้างตารางอ้างอิงเป็น
1 F
49 D
54 D+
สามารถตอบโจทย์ได้ครับ คนที่ได้คะแนน 49.28 จะได้เกรด D เพราะคะแนนสูงกว่า 49 ซึ่งเป็นขอบล่างของเกรด D ครับ
อย่างที่เขียนในบทความ ตัวเลขขอบบน จะใส่หรือไม่ใส่ก็ได้ ไม่มีผลต่อการคำนวณครับ ใส่เพื่อให้เข้าใจง่ายเฉยๆครับ
ใช้งานได้ จริง
อยากสอบถามคะ ถ้าเรามีฐานลูกค้าเยอะมากๆ แต่พอเราจะเลือกชื่อบริษัทมา1ชื่อ แล้วให้ ที่อยู่บริษัท กับเลขประจำตัวผู้เสียภาษีมาด้วย แต่คนละช่องกัน ต้องทำยังไงคะ
ถ้าจะใส่ช่องที่ต้องการค้นหาเป็นดังนี้จะใส่สูตรอย่างไรคับ PT-01/OR ปกติต้องใส่เพียงตัวเลขเช่น123456