VLOOKUP ข้อมูลเยอะแล้วช้า แก้ยังไงให้เร็ว?

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

แนวทาง 1 : ใช้ Excel 365 สิ

ใน Excel 365 version 1809 เป็นต้นไป ทาง Microsoft ได้มีการปรับปรุงการประสิทธิภาพการทำงานของสูตรพวก VLOOKUP ให้ทำงานเร็วขึ้น โดยหลักการคือให้โปรแกรมมีการสร้าง Index ในคอลัมน์ที่ทำการค้นหา (แม้ไม่ได้เรียง) ก่อนทำการค้นหา ซึ่งผลลัพธ์ของ VLOOKUP ใน Excel 365 จะเร็วขึ้นประมาณ 4 เท่าเมื่อเทียบกับ Excel 2010 หรือ 2016

แต่ก็นะ… กรณีที่เราอุตส่าห์มี Excel 365 แล้ว และคนที่เราต้องใช้งานด้วยก็มี Excel 365 แบบเรา ผมแนะนำให้ใช้ฟังก์ชันสุดโกงอย่าง XLOOKUP ไปเลยก็ได้นะครับ เร็วเท่ากันกับ VLOOKUP (แบบ 365 ที่แก้ให้เร็วแล้ว) แต่ดีตรงว่า XLOOKUP ใช้งานง่ายกว่าอีก!

แนวทาง 2 : ถ้า VLOOKUP แบบเร็ว 100 เท่า

เมื่อปี 2014 ผมได้เคยเขียนบทความเกี่ยวกับการทำ VLOOKUP แบบเร็ว 100 เท่า ซึ่งใช้ VLOOKUP แบบ Approximate Match 2 ตัว มาช่วยกันทำงานร่วมกับฟังก์ชัน IF เพื่อเลียนแบบ VLOOKUP Exact Match ได้ และแน่นอนว่าก็ใช้กับ Excel version เก่าๆ ได้ด้วย

วิธีการที่จะใช้เทคนิคนี้ได้คือ

1. ให้เรียงคอลัมน์ซ้ายสุดของ Table_Array จากน้อยไปมากก่อนเสมอ
2. เขียนสูตรในฝั่งที่เราต้องการแสดงผลลัพธ์ดังนี้ (และใน table_array อย่าเลือกรวมหัวตาราง)

=IF(VLOOKUP(lookup_value, table_array, 1, TRUE)=lookup_value,
VLOOKUP(lookup_value, table_array, col_index_num, TRUE),NA() )

ที่ต้องใช้ IF มาช่วยเพราะว่า VLOOKUP แบบ Approximate แม้จะทำงานเร็วมาก แต่มีโอกาสได้ข้อมูลผิดตัวกลับมา (กรณี Lookup_Value ไม่ได้ตรงกับค่าใน คอลัมน์ซ้ายสุดของ Table_Array เป๊ะๆ) เราจึงให้ลองเอาคอลัมน์ที่ 1 กลับมาดูว่าตรงกับ lookup_value หรือไม่ ถ้าตรงจึงจะเชื่อสิ่งที่เอากลับมาได้

ดังนั้นสูตรนี้จึงแปลว่า ถ้าเอาคอลัมน์แรกกลับมา มันจะเท่ากับ Lookup_value ที่ใช้คนหารึเปล่า?” ถ้าเท่ากันแสดงว่าใช้ได้ ให้เอาค่าจากคอลัมน์ที่ต้องการกลับมาได้ (เช่น คอลัมน์ลำดับที่ 3 ที่ต้องการจริงๆ)

ในรูปที่เป็นการสรุปในคอลัมน์ F ก็เลยบอกว่า IF คอลัมน์ E จริง ให้เชื่อ ค่าในคอลัมน์ C นอกนั้นให้เป็น NA() ไปซะ

แนวทาง 3 : ใช้ INDEX+MATCH ดีกว่า กรณีที่ Lookup ค่ากลับมาหลายคอลัมน์

หลายคนอาจเคยได้ยินเกี่ยวกับฟังก์ชัน INDEX และ MATCH มาบ้าง และอาจสงสัยว่ามันดีกว่า VLOOKUP มั้ย?

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

ดังนั้นถ้าจะเอาค่ากลับมาแค่คอลัมน์เดียว ใช้ VLOOKUP จะสะดวกกว่า และก็ไม่ได้ช้ากว่าแต่อย่างใด

เพราะว่าถ้าเรามาพิจารณาแล้ว ขั้นตอนการทำงานนั้นแบ่งเป็น 2 แบบคือ

• ค้น = ค้นหาว่าข้อมูล lookup_value อยู่ไหน => ช้าที่ขั้นตอนนี้แหละ!!
• คว้า = หยิบข้อมูลในคอลัมน์ที่ต้องการกลับมา (คว้า) => เร็ว

แต่ถ้าเราต้องการเอาค่ากลับมาหลายๆ คอลัมน์ การใช้ VLOOKUP หลายๆ รอบเพื่อให้ได้ข้อมูลหลายๆ คอลัมน์นั้นจะช้าโดยใช่เหตุ

เช่น เราต้องการเอาค่ากลับมา 3 คอลัมน์ จากตารางอ้างอิงที่ต้องการ ปกติแล้วเราก็จะใช้ VLOOKUP 3 ที (ต่อ 1 บรรทัด) โดยเปลี่ยน Col_index_num เป็นตัวเลขลำดับ 3 เลขที่ต้องการ

ไอ้การเขียน VLOOKUP หลายๆ Cell ต่อ 1 บรรทัด มันต้องทำการค้นหาข้อมูลใหม่หลายรอบเลยว่า Lookup_value อยู่แถวที่เท่าไหร่ ทั้งๆ ที่จริงๆ มันควรจะค้นทีเดียวก็ได้แล้วจำลำดับแถวเอาไว้ (แต่สูตร Excel มันไม่ได้จำนะ ว่ามันเคยหามาแล้ว)

ดังนั้นเราก็เลยใช้เทคนิคในการสร้างคอลัมน์พิเศษแล้วใช้ MATCH เพื่อให้มันหาว่าสิ่งที่ต้องการอยู่ลำดับที่เท่าไหร่ (ผลของ MATCH จะออกมาเป็นตัวเลขลำดับตำแหน่งของ Lookup_value) จากนั้นค่อยใช้ INDEX คว้าหยิบข้อมูลในลำดับที่ต้องการได้เลย ซึ่งขั้นตอนการคว้านั้นเร็วมากกๆๆๆๆ ครับ และใช้กับ Excel version เก่าๆ ได้เช่นกัน

แนวทางการเขียนสูตรจะทำประมาณนี้ นั่นคือสร้างคอลัมน์ Match เพื่อคำนวณลำดับเลขแถวที่ต้องการ แล้วด้านขวาใช้ INDEX หยิบค่าอย่างเดียวรัวๆ เลย

ดังนั้นสรุปว่าวิธีนี้จะเร็วกว่ามาก กรณีที่เอาค่ากลับมาหลายคอลัมน์ เช่น ถ้าเอาค่ากลับมา 10 คอลัมน์ ก็น่าจะเร็วขึ้นประมาณ 10 เท่าจากวิธีปกติได้ครับ

Tips : ถ้าจะเร็วแบบ Peak เลย คือเราเอา VLOOKUP 100 เท่า มาแทนการใช้ MATCH หาตำแหน่งได้นะ มันจะเร็วแบบสุดๆๆๆๆๆๆ เลย

แนวทาง 4 : ใช้ Merge Query ใน Power Query แทน VLOOKUP

นอกจากการใช้สูตรพวก VLOOKUP ในการเอาข้อมูลจากอีกที่มาแปะในที่ที่ต้องการได้ (เรียกว่า Mapping ข้อมูล) อีกวิธีนึงที่ทำได้ก็คือใช้เครื่องมือ Power Query นั่นเอง ซึ่งทำง่ายมากแค่ต้องเอา Table ทั้งสองฝั่ง เข้าสู่ Power Query ก่อน ซึ่งผมเอาเข้าแบบ Connection Only ไว้

จากนั้นก็เลือกไปที่ตารางหลัก แล้วสั่ง Merge Query

ผลลัพธ์แต่ละช่องจะออกมาเป็น Table ก่อน ยังใช้งานไม่ได้ ต้องกด Expand ค่าออกมา และเลือกว่าอยากได้คอลัมน์ไหนบ้าง เอา Prefix มั้ย? (ซึ่งผมไม่เอา)

แค่นี้ก็ได้ค่าที่ต้องการทันที หลายคอลัมน์พร้อมกันเลยด้วย!

จากนั้นถ้าจะเอาผลลัพธ์ออกไปใช้ ก็กด Close & Load to… แล้วค่อยเลือกว่าจะเอาออกไปเป็นอะไร เช่น เป็น Table หรือ จะเอาเข้า Pivot Table เลย หรือจะเข้า Data Model ก็ได้

นี่ไง ได้ละ ซึ่งเร็วมากเลยนะ

แนวทาง 5 : ใช้ Data Model

จริงๆ แล้วก่อนที่เราจะทำการ VLOOKUP หรืออะไรก็แล้วแต่ข้างบน เราต้องถามตัวเองก่อนเลยนะ ว่า “เราจะเอาข้อมูลจากตารางอ้างอิงมาแปะในตารางหลักทำไม?”

ถ้าแปะเพื่อแค่ให้มี Field ในการ Pivot ได้

ถ้าแปะเพื่อแค่ให้มี Field ในการ Pivot ได้ แค่นี้เราไม่จำเป็นต้องแปะเลย เราแค่ใช้ Data Model มาช่วย ทุกอย่างจะง่ายขึ้นมาก และไม่ต้องเสียเวลา Lookup เลยแม้แต่วินาทีเดียว แต่เปลี่ยนมาเป็นใช้การสร้าง Data Model แทน ซึ่งเสียเวลาทำครั้งเดียว ต่อไปจะต้องการค่าอะไรก็ทำได้เลย

การจะใช้ Data Model ได้นั้น ต้องมี Excel 2013 ขึ้นไป (ให้ Activate Com Add-in ที่ชื่อว่า Power Pivot) หรือจะใช้ Power BI ก็ได้

หลักการคือ ให้เอาตารางทั้งหมดที่มีความสัมพันธ์กันเนี่ย Load เข้าสู่ Data Model ซะ ซึ่งจะโหลดผ่าน Ribbon Power Pivot หรือจะโหลดผ่านการ Close & Load to… ของ Power Query ก็ได้

เช่น ผม add จากข้อมูลที่เป็น Table ใน Sheet Excel ดังนี้

มันก็จะไปโผล่ในหน้าจอ Power Pivot ดังนี้

กด icon Excel mี่มุมซ้ายบนเพื่อกลับมาใน Excel แล้ว add ตารางอื่นๆ ที่เกี่ยวข้องทั้งหมดเข้าไป

สมมติผม add เข้าไป 3 ตารางพอใจแล้ว ก็ให้กด Diagram view ใน Power Pivot

จากนั้นทำการลากเชื่อมความสัมพันธ์ระหว่างแต่ละตาราง (Relationship) ว่าเชื่อมกันด้วย Field ไหน

ลากเชื่อม Relationship ให้ครบ

จากนั้นกด icon Pivot Table ใน Ribbon ของ Power Pivot ได้เลย มันจะสร้าง Pivot Table โหมด Data Model ขึ้นมาให้

ซึ่งจะเห็นว่าเราสามารถลาก Field คนละตารางมา Pivot รวมกันได้เลย

ถ้าต้องการ Lookup ค่ามาทำบางอย่างต่อ

ถ้าหากเราไม่ได้แค่จะเอา Field มา Pivot รวมกันเฉยๆ แต่ต้องการ Lookup ค่ามาทำบางอย่างต่อในตาราง เราก็สามารถทำได้เช่นกัน โดยหลังจากที่ทำ Data Model เสร็จแล้ว ให้ไปที่ Data View ของเครื่องมือ Power Pivot แล้วไปในตารางที่ต้องการ

จากนั้นสร้างคอลัมน์ใหม่ในตารางหลักที่เราต้องการ (ฝั่งที่ความสัมพันธ์เป็น *) แล้วเรียกใช้ฟังก์ชัน RELATED ได้เลย ซึ่งมันจะให้เลือกว่าต้องการ Field ไหนจากตารางอื่น ซึ่งในที่นี้ผมต้องการชื่อ จากตาราง SalesRef

ดังนั้นสูตรจะเขียนแค่นี้เลย

=RELATED(SalesRef[ชื่อ])

และเราก็ได้ค่าตามที่ต้องการ ซึ่งเร็วมากๆ เช่นกัน

สรุป

ใครสนใจเรื่อง Power Query ก็สามารถไปอ่านบทความที่เป็นเนื้อหาตัวอย่างของหนังสือ Excel Power Up ของผมได้ ฟรี!

ใครสนใจเรื่องของ Data Model ผมแนะนำให้ไปศึกษาบทความซีรีส์ Power BI ที่ผมเขียนไว้ก็ได้ครับ (ฟรี!) มีหลายตอนมากๆ ซึ่งตามความเห็นของผม การศึกษา Data Model และ DAX จาก Power BI จะเรียนรู้ง่ายกว่าทำใน Excel เพราะเครื่องมือมันพร้อมมากกว่า

ขายของนิดนึง

อย่างไรก็ตาม หากอยากจะใช้ Power BI ได้ดี ควรจะใช้ Pivot Table ใน Excel ให้เป็นก่อน และควรมีความเข้าใจให้ดีว่าจริงๆ แล้วตัวเลขใน Pivot มันคำนวณมาจากไหนด้วย ซึ่งผมมี หรือถ้าจะปรับพื้นเรื่องอื่นๆ เช่น Excel ทั้งหมด, Power Query, Power BI ลองไป