จากบทความที่แล้ว ชำแหละ VLOOKUP จากข้างหลังทะลุถึงหัวใจ ภาค 1 (VLOOKUP คลุกเคล้า COLUMNS หรือ MATCH) ผมทิ้งท้ายว่า VLOOKUP ยังมีข้อจำกัดอีกข้อหนึ่ง

และด้วยข้อจำกัดนี้เอง ทำให้หลายคนหันไปใช้ฟังก์ชันอื่นแทน

ข้อจำกัดที่ว่าคือ

เซลล์อ้างอิงต้องอยู่คอลัมน์ซ้ายสุดเสมอ !!

ในความเป็นจริงแล้ว คอลัมน์อ้างอิง (ที่อยู่ในตารางอ้างอิง) อาจไม่ได้อยู่ด้านซ้ายสุดเสมอไป

หรืออาจมีใครปรารถนาดี (ประสงค์ร้าย) ย้ายคอลัมน์อ้างอิงไปอยู่ที่อื่น (ซะงั้น!)

ลองนึกภาพว่า เรานั่งทำงานในออฟฟิศจนถึงสี่ทุ่ม ใช้ VLOOKUP ดึงข้อมูลที่ต้องการเสร็จเรียบร้อย เซฟไฟล์ไว้ในแชร์ไดรฟ์  ปิดคอมพ์กลับบ้าน

พอเช้ามา เปิดไฟล์ที่เซฟเมื่อคืน แล้วเจอแบบนี้

#N/A ตรึม!

มาจากไหน(วะ)เนี่ย!

เมื่อคืนไม่ได้เป็นแบบนี้ซะหน่อย!

เห็นแล้วน้ำตาเล็ดหัวเข่า ต้องไปคุยกับแผนกไอทีจับมือคนร้ายดมให้ได้…

แต่…ต่อให้จับคนร้ายได้ ก็ไม่ทันแล้ว ต้องมาแก้อยู่ดี T_T

เคสนี้เกิดจากอะไรน่ะหรือครับ?

มา.. ผมจะเล่าให้ฟัง

ตอนแรกเราใช้คอลัมน์ชื่อ Product เป็นตัวอ้างอิง เพื่อดึงข้อมูลจากคอลัมน์อื่นๆ เช่น ดึงข้อมูล ProductGroup จากคอลัมน์ B

เดิมที Product อยู่คอลัมน์ A

แต่ตอนเที่ยงคืน มีผู้ร้าย ย้ายคอลัมน์ Product จากคอลัมน์ A ไปคอลัมน์ D โดยไม่บอกเรา (ใครฟะ!)

ด้วยความที่ VLOOKUP มีข้อจำกัดว่าคอลัมน์อ้างอิง (Product) ต้องอยู่ซ้ายสุดเสมอ แต่คอลัมน์ Product ถูกย้ายจากคอลัมน์ A ไปอยู่คอลัมน์ D แล้ว สูตรของเราเลยพังพินาศสิ้น!

แล้วจะแก้ไขยังไงล่ะ?

แน่นอนครับ ปัญหามีไว้ทาย

เอ๊ย.. มีไว้แก้ ^^

วิธีแก้ที่ได้ผลชะงัดคือ เปลี่ยนฟังก์ชันที่ใช้จาก VLOOKUP เป็น INDEX

ซึ่งเจ้า INDEX นี้ไม่มีข้อจำกัดว่า คอลัมน์อ้างอิง(ในตารางอ้างอิง)ต้องอยู่ซ้ายสุด

จะอยู่คอลัมน์ไหนก็ได้ ขอแค่มีคอลัมน์นั้นอยู่ในตารางอ้างอิงก็พอ

(การทำตารางอ้างอิงที่ดีไม่ควรมีเซลล์ว่าง ไม่งั้นอาจเกิดปัญหาที่ไม่คาดฝันขึ้นได้ ถ้าจำเป็นต้องมีค่าว่าง มีก็ให้ใส่ค่าอื่นลงไป เช่น None, Not Assigned, 0 และทุกข้อมูลในคอลัมน์อ้างอิงไม่ควรซ้ำกันเลย (เป็น Unique))

จากเคสเดิม เราสามารถนำ INDEX ลบข้อด้อยของ VLOOKUP ได้ดังนี้ครับ

สูตรแถวที่ 2-5 เขียนด้วยฟังก์ชัน VLOOKUP เมื่อมีคนร้ายย้ายคอลัมน์อ้างอิงของเรา (ในตารางอ้างอิง) ผลลัพธ์จึงพังพินาศอย่างที่เห็น

แม้ว่าค่าในเซลล์ F2, F3 ไม่ขึ้น #N/A ผลลัพธ์ที่ได้ก็ไม่ถูกต้อง T_T

แต่ถ้าใช้ INDEX (ตั้งแต่แถวที่ 6 เป็นต้นไป) จะเห็นได้ว่า ไม่มีค่าไหนมีความผิดพลาดเลย

หรือเขียนสูตร (ในเซลล์ C6) ว่า

=INDEX( Product!$A$3:$C$13 ,
MATCH(Data!$A6,Product!$D$3:$D$13,0) ,
MATCH(Data!C$1,Product!$A$2:$C$2,0))

สูตรนี้ ต้องการดึงค่าออกมาว่า ProductGroup ของ Product K คืออะไร (คำตอบคือ Group III)

เวลาดึงค่า มันทำงานแบบนี้ครับ

หรือก็คือ ดึงค่าในแถวที่มีข้อมูล Product K (แถวที่ 13) จากคอลัมน์ ProductGroup (คอลัมน์ A) นั่นเอง!

ขออธิบายฟังก์ชัน INDEX นิดนึงนะครับ

โครงสร้างของสูตรคือ

=INDEX( array , row_num , [column_num] )

จริงๆแล้ว INDEX มี 2 โครงสร้าง สำหรับบทความนี้ ขออธิบายเฉพาะโครงสร้างแบบนี้นะครับ

(อีกโครงสร้างหนึ่งคือ INDEX(reference, row_num, [column_num], [area_num]) ซึ่งในทางปฏิบัติใช้น้อยกว่าโครงสร้างแบบแรก)

array คือ ช่วงข้อมูลในตารางอ้างอิง หรือจากตัวอย่างนี้ก็คือ Product!$A$3:$C$13 

row_num คือ ลำดับที่ในแนวนอนของข้อมูลที่เราต้องการ

เช่น เคสนี้เราต้องการ ลำดับที่ในแนวนอนของ Product K หรือก็คือ ข้อมูลในแถวที่ 13

ปัญหาคือ เราจะรู้ได้อย่างไรว่า ข้อมูลที่เราต้องการอยู่ลำดับที่เท่าไร?

นั่งนับทุกครั้งไม่ไหวแน่ๆ T_T

เอ็กเซลเค้าคิดถึงปัญหานี้ไว้แล้วครับ จึงสร้างฟังก์ชัน MATCH ขึ้นมาเพื่อตอบโจทย์นี้

จากเคสนี้ เราจึงเขียน row_num ด้วย

MATCH( Data!$A6, Product!$D$3:$D$13, 0 )

ขออธิบายโครงสร้างของฟังก์ชัน MATCH นิดนึงนะครับ เผื่อบางคนอาจไม่คุ้นเคย

โครงสร้างของ MATCH คือ

=MATCH ( lookup_value , lookup_array , [match_type] )

lookup_value คือ ค่าที่เราต้องการ หรือจากตัวอย่างนี้ก็คือ $A6 (มีค่าเท่ากับ Product K)

lookup_array คือ ช่วงข้อมูล (คอลัมน์) ในตารางอ้างอิงที่เก็บค่า lookup_value เอาไว้

จากตัวอย่างนี้ Product K (lookup_value) อยู่ในคอลัมน์ชื่อ Product (คอลัมน์ D ในชีต Product)

lookup_array จึงแทนค่าได้เป็น Product!$D$3:$D$13

[match_type] มี 3 แบบ คือ

• 0 : ถ้าใช้ 0 แสดงว่าเราต้องการให้ค่า lookup_value เหมือนค่าใน lookup_array แบบเป๊ะๆ
• 1 : การใช้ 1 เหมาะกับกรณีที่ lookup_value เป็นตัวเลข และ ข้อมูลใน lookup_array จัดเรียงแบบ น้อยไปมาก
• -1 : การใช้  -1 เหมาะกับกรณีที่ lookup_value เป็นตัวเลข และ ข้อมูลใน lookup_array จัดเรียงแบบ มากไปน้อย

การที่มีวงเล็บก้ามปูครอบ match_type หรือ [match_type] แสดงว่า เราจะใส่ค่านี้ หรือไม่ใส่ก็ได้

Note: ถ้าไม่ใส่ เอ็กเซลจะใส่ให้เอง และเอ็กเซลจะใส่ค่า match_type เป็น 1

(ถ้าต้องการให้ค่าใน lookup_value เหมือนค่าใน lookup_array แบบเป๊ะๆ เราต้องใส่ 0 นะครับ)

ตัวอย่างนี้ ผมต้องการให้  lookup_value เหมือนค่าใน lookup_array แบบเป๊ะๆ จึงใส่ match_type เป็น 0

ผลลัพธ์ที่ได้จาก MATCH( Data!$A6, Product!$D$3:$D$13, 0 ) คือ 11

บางคนอาจงงว่า ทำไม ลำดับของ Product K จึงได้ 11 ทำไมไม่ได้ 13 ?

เหตุผลที่ได้ 11 เพราะข้อมูลชุดนี้เริ่มจากแถวที่ 3 (ไม่นับหัวตาราง) แสดงว่า แถวที่ 1 และ 2 ไม่อยู่ในช่วงข้อมูลที่เราต้องการ

เวลาคิด จึงต้องเอาแถวของข้อมูลนั้น ลบด้วย 2 ผลลัพธ์จึงกลายเป็น 13 – 2

= 11

แปลว่า Product K อยู่ในลำดับที่ 11 ของชุดข้อมูล (แต่อยู่ในแถวที่ 13)

กลับมาที่โครงสร้างของ INDEX กันต่อนะครับ

[column_num] คือ ลำดับที่ของคอลัมน์(ในตารางอ้างอิง )ที่เราต้องการ

(การที่ column_num มีวงเล็บก้ามปูครอบ [column_num] มีความหมายทางเทคนิคว่า จะใส่หรือไม่ใส่ก็ได้)

ตัวอย่างนี้ เราต้องการดึงค่าจากคอลัมน์ ProductGroup

ดูด้วยตา เรารู้ว่า คอลัมน์ ProductGroup อยู่คอลัมน์ A หรืออยู่คอลัมน์ลำดับที่ 1

ปัญหาคือ จะทำให้เอ็กเซลรู้ได้ยังไงว่า ต้องการข้อมูลในคอลัมน์ลำดับที่ 1

ไม่ยากครับ..

ทริกก็คือ ตั้งชื่อหัวคอลัมน์ในตารางผลลัพธ์ ให้เหมือนตารางอ้างอิง

จากเคสเดิม เราต้องการดึงข้อมูล ProductGroup จากตารางอ้างอิง มาไว้ที่เซลล์ C6 ที่อยู่ในตารางผลลัพธ์

เราก็ตั้งชื่อหัวคอลัมน์ในตารางผลลัพธ์ (เซลล์ C1) ให้เป็น ProductGroup

ตั้งชื่อเหมือนกันแล้วยังไงต่อน่ะหรือครับ?

ใช้ฟังก์ชัน MATCH เพื่อหาว่า ProductGroup อยู่ในคอลัมน์ลำดับทีเท่าไร

แทนที่จะพิมพ์ 1 เข้าไปตรงๆ เราก็ใช้

MATCH( Data!C$1 , Product!$A$2:$C$2 , 0 ) 

ค่าที่ได้คือ 1 เหมือนกัน

(การใช้ดอลลาร์ไซน์ ($) ช่วยเขียนเป็น Data!C$1 ทำให้เราสามารถก็อปปี้สูตรนี้เพื่อดึงค่าจากคอลัมน์อื่นๆได้ด้วย)

อยากให้ลองใช้ INDEX กันดูนะครับ แล้วจะรู้ว่าความสบายมันมีจริง ^__^

แล้ว INDEX มีข้อเสียไหม?

เท่าที่พอนึกออก มี 3 ข้อครับ

1. เขียนสูตรยาวกว่า VLOOKUP เวลาแกะสูตร อาจรู้สึกว่าแกะยากกว่าสูตรที่เขียนด้วย VLOOKUP
2. คนส่วนใหญ่ไม่ค่อยถนัดใช้ INDEX เวลาส่งต่อไฟล์ให้คนอื่น คนที่รับไฟล์อาจรู้สึกไม่คุ้นเคย อยากให้ใช้ VLOOKUP มากกว่า
3. ไม่สามารถดึงข้อมูลมากกว่าหนึ่งเงื่อนไขได้
   • เช่น อยากทราบว่าประเทศที่อยู่ในทวีปเอเชีย (เงื่อนไขที่ 1) ประเทศใดมีค่าครองชีพสูงกว่าประเทศไทย (เงื่อนไขที่ 2) และเวลาในประเทศนั้นห่างจากประเทศไทยไม่เกินสองชั่วโมง (เงื่อนไขที่ 3) แต่ VLOOKUP ก็มีข้อจำกัดนี้เช่นกัน
   • สามารถลบข้อจำกัดโดยการใช้สูตรอาร์เรย์ (Array Formula) ถ้ามีโอกาสจะนำมาเล่าสู่กันฟังครับ ^__^

เช่นเคยเหมือนทุกครั้ง ไฟล์ตัวอย่างของบทความนี้ อยู่ในลิงค์ด้านล่างครับ

VLOOKUP_INDEX_150131

เรื่องราวของ VLOOKUP ยังไม่จบแค่นี้นะครับ ยังมี VLOOKUP แบบ Approximate Match ที่สามารถใช้แทน IF ซ้อนกันหลายๆชั้นได้

VLOOKUP แบบ Approximate Match คืออะไร?

เดี๋ยวคุยกันต่อในภาค 3 ครับ ^__^

.

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

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