วิศวกรรีพอร์ต

มาถึงภาคสุดท้ายของไตรภาค 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 เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

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