XLOOKUP ใช้ ยังไงให้เจ๋ง? เพิ่มประสิทธิภาพด้วย Binary Search

แบ่งปันบทความนี้ให้คนที่คุณหวังดี :

ผมพบว่ามีเพื่อน ๆ หลายคนเป็นชาว VLOOKUP เป็นจำนวนมาก (ซึ่งไม่ได้ผิดนะ!) แต่ผมมาขาย XLOOKUP นั่นแหละ เลยพยายามยกข้อดีมา เพื่อให้เพื่อนผู้อ่านทุกคนเปิดใจและในบทความนี้จะมาแนะนำ XLOOKUP ใช้ ยังไงให้เจ๋งกับ XLOOKUP Binary Search นั่นเองครับ

เพราะหลังจากที่ได้พูดถึงในบทความก่อนหน้าที่อวยนักอวยหนา โน้มน้าวให้เพื่อนผู้อ่านทุกคนเปลี่ยนมาใช้ VLOOKUP และได้ทิ้งทวนก่อนจากกันว่า Binary Search คือหนึ่งในคุณสมบัติเด่นที่ทำให้ XLOOKUP ทรงพลังมากกว่าหลายเท่า แล้วคำถามคือ XLOOKUP ใช้ยังไงให้เจ๋งขนาดนั้นล่ะ?

รับรองว่าบทความนี้จะทำให้เพื่อน ๆ เข้าใจการใช้งาน XLOOKUP ได้ดียิ่งขึ้นและใช้ได้อย่างมีประสิทธิภาพ ช่วยในการทำงานให้สะดวกขึ้นอย่างแน่นอนครับ

ก่อนอื่นหากเพื่อน ๆ ผู้อ่านอยากอ่านบทความก่อนหน้าว่าผมอวย XLOOKUP ขนาดไหน สามารถจิ้มที่รูปเพื่ออ่านหรือกดได้ที่ลิงก์ด้านล่างนี้เลยครับ

XLOOKUP คืออะไร? ดีกว่า VLOOKUP แค่ไหน อธิบายแบบเข้าใจง่าย
XLOOKUP คืออะไร? ดีกว่า VLOOKUP แค่ไหน อธิบายแบบเข้าใจง่าย (wawasabii.com)

ถ้าเพื่อน ๆ พร้อมแล้ว ลุยกันเลยครับ GO!

XLOOKUP Syntax

XLOOKUP ใช้ยังไงกันแน่ ขอบรีฟสั้น ๆ ตามคู่มือวิธีการใช้ของ Microsoft ดังต่อไปนี้ครับ

Argumentคำอธิบาย
lookup_value
ต้องระบุ*
ค่าที่จะค้นหา

*ถ้าไม่ใส่ค่าอะไรไว้ XLOOKUP จะส่งกลับเซลล์ว่างที่พบใน lookup_array   
lookup_array
จำเป็น
Array หรือช่วงที่จะค้นหา
return_array
จำเป็น
Array หรือช่วงที่จะส่งกลับ
[if_not_found]
ไม่จำเป็น
ไม่พบค่าที่ตรงกันที่ถูกต้อง ให้ส่งกลับข้อความ [if_not_found] ที่คุณใส่ถ้าไม่พบค่าที่ตรงกันที่ถูกต้อง และ [if_not_found] หายไป #N/A จะถูกส่งกลับ
[match_mode]
ไม่จำเป็น
ระบุชนิดการจับคู่: 0 – ตรงกันทุกประการ ถ้าไม่พบ ให้ส่งคืน #N/A นี่คือค่าเริ่มต้น-1 – ตรงกันทุกประการ ถ้าไม่พบ ให้ส่งกลับรายการขนาดเล็กถัดไป1 – ตรงกันทุกประการ ถ้าไม่พบ ให้ส่งกลับรายการที่มีขนาดใหญ่ขึ้นถัดไป2 – การตรงกันกับอักขระตัวแทนที่มี *,? และ ~ มีความหมายพิเศษ
[search_mode]
ไม่จำเป็น
ระบุโหมดการค้นหาที่จะใช้:1 – ดําเนินการค้นหาโดยเริ่มต้นที่รายการแรก นี่คือค่าเริ่มต้น-1 – ดําเนินการค้นหาย้อนกลับโดยเริ่มต้นที่รายการสุดท้าย2 – ทําการค้นหาแบบไบนารีโดยอาศัย lookup_array เรียงลําดับจากน้อยไปหามาก ถ้าไม่ได้เรียงลําดับ ผลลัพธ์ที่ไม่ถูกต้องจะถูกส่งกลับ-2 – ทําการค้นหาแบบไบนารีโดยอาศัย lookup_array เรียงลําดับจากมากไปหาน้อย ถ้าไม่ได้เรียงลําดับ ผลลัพธ์ที่ไม่ถูกต้องจะถูกส่งกลับ
ฟังก์ชัน XLOOKUP – ฝ่ายสนับสนุนของ Microsoft

ถ้าอ่านแล้วงง ขออธิบายแปลไทยเป็นไทยแบบนี้ครับ

  1. lookup_value – ค่าที่เราต้องการนำมาค้นหา
  2. lookup_array – ช่วงที่เราจะค้นหา กล่าวคือที่เราจะเอามา match ให้ตรงกับ lookup value นั่นเอง
  3. return_array – ช่วงของคำตอบที่ต้องการเพื่อนำมาแสดงผลลัพธ์

ใส่แค่ 3 arguments ก็สามารถใช้งานได้แล้ว

=XLOOKUP(ค่าที่เราต้องการนำมาค้นหา, ช่วงที่เราจะค้นหา, ช่วงของคำตอบที่ต้องส่งกลับ

จากตัวอย่างในรูป จะเขียนได้สั้นกว่านั่นหมายความว่าเราสามารถใช้งานได้คล่องแคล่วกว่าเดิมจาก VLOOKUP เพราะสามารถเลือกคอลัมน์ได้ทันที ไม่ต้องมาหา index ของคอลัมน์ที่จะ return ค่า แถมไม่ต้องมาพิมพ์ FALSE หรือ 0 เพื่อระบุเป็น Exact match อีก

  • สูตรเข้าใจง่ายมากกว่า เพราะปรับปรุงให้ดีมากกว่าเดิม
  • หมดปัญหาการ lookup คอลัมน์ที่อยู่ทางด้านซ้าย
  • ไม่ต้องมานั่งดูว่าเป็น index ที่เท่าไหร่เพราะสามารถคลุมเลือก return array ได้ทันที
  • ไม่ต้องใช้ IFERROR มาครอบสำหรับกรณี not found เพราะมี if_not_found อยู่แล้ว
  • Match mode เริ่มต้นเป็น Exact match ที่เป็น match ยอดนิยมในการใช้งานจริง

ยังไงลองใช้งานดู อาจจะไม่ค่อยชินถ้ายังติดกับ VLOOKUP แต่พอได้ใช้งานไปเรื่อย ๆ จะคล่องแล้วจะรู้สึกว่าสะดวกขึ้นครับ

ทีนี้กลับมาที่หัวข้อหลักของเราว่า จะยกระดับการใช้งานให้เจ๋งยิ่งขึ้นยังไง? ต้องขอเท้าความไปที่ Binary Search ก่อนเลยครับ

Binary Search คืออะไร?

ขอยกตัวอย่างการใช้งานสั้น ๆ ลองนึกถึงการเปิดหาพจนานุกรมที่เราค้นหาคำสักคำในนั้น เราก็คงไม่ได้ไล่เปิดตั้งแต่ ก.ไก่ หรือตั้งแต่ตัว A แต่เราจะเปิดตัวอักษรที่ใกล้เคียง ใครเซียนก็จะเปิดได้เจอหมวดตัวอักษรนั้นเลยซึ่งไวกว่าเปิดตั้งแต่ตัวอักษรแรกแน่นอนครับ

เช่น ถ้าเราต้องการค้นหาคำว่า Story เราเปิดพจนานุกรมไปเลยกลางเล่มลงไปเพราะเรารู้ว่าตัว S เป็นตัวอักษรลำดับที่ 19 จากทั้งหมด 26 ตัว ถ้าเกิดว่าเปิดไปเจอตัว Q เราจะเปิดเลยไปเพราะรู้ว่ายังไม่ถึง แต่ถ้าเปิดเจอตัว T หรือ U ขึ้นมา เราจะถอยย้อนกลับไปเพราะว่าเลย S ไปแล้วนั่นเองครับ

Books on white wooden shelf photo – Free Furniture Image on Unsplash
Books on white wooden shelf photo – Free Furniture Image on Unsplash

การค้นหาแบบทวิภาค (Binary Search)

การค้นหาแบบทวิภาค (Binary Search) คืออัลกอริทึมการค้นหาข้อมูลที่มีประสิทธิภาพ ใช้สำหรับค้นหาข้อมูลในชุดข้อมูลที่เรียงลำดับไว้แล้ว โดยใช้วิธีแบ่งครึ่งข้อมูลออกเป็นสองส่วนซ้ำ ๆ จนกว่าจะพบข้อมูลที่ต้องการ โดยวิธีการเป็นแบบนี้ครับเราจะมีค่า target คือค่าที่ต้องการค้นหา และค่า midpoint หรือกึ่งกลางจะเป็นจุดตัดที่เราเอาไว้แบ่งสำหรับช่วงข้อมูล

วิธีการค้นหาแบบ Binary Search

  1. เรียงลำดับข้อมูลจากน้อยไปมาก
  2. หาค่ากึ่งกลาง (midpoint) ของชุดข้อมูล
  3. เปรียบเทียบค่าที่ต้องการค้นหา (target) กับค่ากึ่งกลาง
    • ถ้า target เท่ากับค่ากึ่งกลาง แสดงว่าพบข้อมูลที่ต้องการ
    • ถ้า target น้อยกว่าค่ากึ่งกลาง ให้แบ่งครึ่งข้อมูล ด้านซ้าย ออกเป็นสองส่วน
    • ถ้า target มากกว่าค่ากึ่งกลาง ให้แบ่งครึ่งข้อมูล ด้านขวา ออกเป็นสองส่วน
  4. ทำซ้ำขั้นตอน 2-3 กับชุดข้อมูลที่แบ่งใหม่ จนกว่าจะพบข้อมูลที่ต้องการหรือจนกว่าชุดข้อมูลจะว่างเปล่า

จะมีทฤษฎีนิดนึง แต่เพื่อให้เห็นภาพมากขึ้น มีตัวอย่างมาให้ชมครับ สำหรับกรณีแรกเปรียบเทียบให้ดูว่าการค้นหาแบบ Binary (คล้ายกับ XLOOKUP ที่ใช้ Binary search) กับ Sequential คือการค้นหาแบบปกติไล่หาไปเรื่อย ๆ (คล้ายกับ VLOOKUP หรือ XLOOKUP ที่ค้นหาแบบปกติ) ในตัวอย่างคือค่าที่เป็น target คือ 37

Binary Vs Linear Search Animated Gifs (mathwarehouse.com) – Side-by-side look

เมื่อแบ่งครึ่งข้อมูลที่มี 17 ตัว (index 0-16) จะได้ไปตกที่ index 8 มีค่า midpoint เท่ากับ 23 และเราพบว่าค่า 37 (target) มีค่ามากกว่า midpoint จึงต้องแบ่งข้อมูลด้านขวาออกเป็นสองส่วน ทำวนแบบนี้ไปเรื่อย ๆ อีก 3 ครั้ง จะเจอเลข target ที่เราต้องการ ขณะที่การค้นหาแบบ Sequential กว่าจะเจอคือหลับไปแล้ว

Best case scenario

ยิ่งไปกว่านั้นกรณี Best case ที่ข้อมูลตัวเลข target = midpoint จะเจอได้ทันที

Worst case scenario

และต่อให้เป็นกรณี worst case ยังใช้จำนวน Steps ที่ไม่ได้มากขั้นตอน ไม่ได้รอจนหลับ

จุดแข็งและข้อสังเกตของ Binary Search

จุดแข็งของ Binary Search

  • มีประสิทธิภาพสูง ใช้เวลาในการค้นหาน้อยลงเมื่อเทียบกับวิธีการค้นหาแบบอื่น
  • สามารถนำไปใช้กับชุดข้อมูลที่มีขนาดใหญ่ได้

ข้อสังเกตของ Binary Search

  • ชุดข้อมูลต้องเรียงลำดับไว้ก่อน
  • ไม่สามารถใช้กับชุดข้อมูลที่มีข้อมูลซ้ำกันได้

แน่นอนล่ะว่าการเปิดพจนานุกรมเราคงไม่ได้เจอตัว S มาตั้งแต่หน้าแรก ต้องเรียงข้อมูลลำดับให้เรียบร้อยก่อนแต่ด้วยความเจ๋งของการค้นหาแบบนี้ทำให้ข้อมูลที่มีขนาดใหญ่สามารถรับมือได้ไม่ยากครับ

Binary Search ใน XLOOKUP ใช้ ยังไงในการทำ Excel

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

2020 Ford Mustang GT Fastback Insta: @demiantejeda

คล้ายเดิมกับ 3 arguments แรกที่ต้องการจะใส่ ต่อมาคือ if_not_found ถ้าเราไม่ใช้เนื่องจากว่าเป็น optional สามารถที่จะใส่ comma (,) เพื่อข้ามไปได้เลย และสำหรับ Search mode จะมีเป็น 1, -1, 2, -2 หากต้องการใช้ Binary Search จะใช้ 2 หรือ -2

ความแตกต่างคือลำดับการเรียงของ lookup_array ใช้ผิดจะไม่สามารถดึงค่าได้นะครับ

  • ถ้าเรียงจากน้อยไปหามาก (Ascending order) จะใช้ 2
  • ถ้าเรียงจากมากไปหาน้อย (Descending order) จะใช้ -2

จากรูปด้านบนพอได้ใช้งานจริง จะพบว่าตัว lookup_value นั้นไม่ต้องเรียงลำดับก็ได้แต่ lookup_array ต้องเรียงลำดับก่อนใช้งาน ซึ่งอันนี้ข้อมูลจำนวน 1,000 แถวอาจจะไม่ได้รู้สึกถึงความแตกต่างเมื่อเทียบกับ XLOOKUP ธรรมดาหรือ VLOOKUP มากนัก

ขณะที่ภาพนี้ที่แม้ว่าจะสูตรถูก เรียงลำดับข้อมูลแล้ว แต่ Search mode (-2) ของเราใส่ผิดจะทำให้ไม่สามารถคืนค่าได้ เพราะว่า lookup_array นั้นเรียงจากน้อยไปหามาก แต่ Search mode ของเราไปค้นหาแบบมากไปหาน้อยทำให้ไม่สามารถได้ผลลัพธ์ตามที่ต้องการ

XLOOKUP Binary Search เร็ว แรง ทะลุ Excel?

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

การทดสอบของ Professor Excel ใช้เป็นข้อมูลแสนแถว และเปรียบเทียบจับเวลาการคำนวณสูตรของ VLOOKUP, XLOOKUP, INDEX/MATCH, XLOOKUP with Binary Search

อันดับแรกเลย XLOOKUP แบบธรรมดาจะใช้เวลารันนานกว่า VLOOKUP ครับ แต่สะดวกกว่าในเรื่องของการใช้งานจริง ๆ หมดปัญหาเรื่อง index หรือการพิมพ์สูตรที่งงงวย

เมื่อเปรียบเทียบกับตัวอื่น ๆ ก็จะพบว่า XLOOKUP without Binary Search ใช้ระยะเวลาในการคำนวณที่นานกว่าจริง ๆ แล้วถ้าใช้ Binary Search ล่ะ?

That’s impressive: the binary XLOOKUP can save 69% of calculation time compared to a normal XLOOKUP.
เร็วกว่าของแทร่

พอเปลี่ยนมาใช้ XLOOKUP แบบ Binary Search ก็พบว่าเร็วขึ้นอย่างเห็นได้ชัด รถสปอร์ตพี่มาแล้วน้อง!

เมื่อเปรียบเทียบกับ VLOOKUP เดิม ๆ จะพบว่าเร็วขึ้นอย่างเห็นได้ชัดเช่นเดียวกัน ขณะที่ VLOOKUP แบบ approximate match จะช้ากว่าเล็กน้อยเท่านั้น แต่เราสน exact match ไม่ใช่เรอะ ถือว่าไวอยู่ดี ยิ่งข้อมูลมีจำนวนแถวมากจะเห็นความแตกต่างมากขึ้นครับ

หรือหากเพื่อน ๆ ผู้อ่านอยากดูเป็นคลิปที่ทดสอบ 8 แสน transactions ที่ lookup value 5 แสนแถวสามารถดูได้ที่คลิปด้านล่างนี้เลย เห็นความต่างของแทร่

Speed Test – VLOOKUP, XLOOKUP, INDEX MATCH, Binary Search in Excel

สรุปแล้วควรใช้อะไร?

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

red-and-white ford

ความเห็นของผมการเลือกใช้สูตร การเลือกใช้โปรแกรมก็เปรียบเหมือนการเลือกเครื่องมือในการประกอบอาชีพครับ เราเลือกเครื่องมือให้ถูกกับการใช้งานก็จะช่วยแบ่งเบาภาระเราได้มาก เหมือนกับเชฟ เหมือนกับช่างกล้อง และหลาย ๆ อาชีพ ที่ต้องใช้อุปกรณ์เครื่องมือของตัวเองให้ถูกต้อง เลือกผิดชีวิตอาจจะไม่ได้เปลี่ยนแต่อาจจะไปได้ช้า ไม่ก็เพิ่มภาระงานแทน (ชีวิตคือการเรียนรู้ ถ้าผิดพลาดเริ่มกันใหม่ได้นะคับ ;-;)

ถ้าจากข้อมูลที่มีไม่มากใช้ในที่ทำงานทั่ว ๆ ไป ผมสนับสนุนและแนะนำให้ใช้ XLOOKUP เพราะไวกว่า สะดวกกว่า เข้าใจง่ายกว่า แต่ถ้าข้อมูลจำนวนมาก ๆ เกินไปคงไม่ได้ทำใน Excel อาจจะใช้เครื่องมืออื่นแทนเพราะ Spreadsheet น่าจะค้าง555

แต่บางกรณี Excel อาจจะเป็นรุ่นเก่าที่ไม่รองรับ หรือเป็น Google Sheets บางคนชินกับ VLOOKUP บางคนต้องใช้ INDEX/MATCH ผมว่าไม่ได้ผิดอะไรขึ้นอยู่กับเคส ขึ้นอยู่กับเครื่องมือที่มี หลัก ๆ แล้วเอาทำงานไม่ปวดหัวไม่ต้องมาแก้ตัวเลขไปมาข้อมูลไม่ตรง น่าจะแฮปปี้แล้วครับ

สิ่งที่สำคัญที่สุดคือ “การรีดประสิทธิภาพออกมาให้ได้มากที่สุด ในสภาวะที่ศักยภาพของเครื่องมือและคนที่มีอยู่จำกัด” นี่แหละครับที่ผมมองว่าสำคัญ

ขอให้มีความสุขและสนุกกับการทำงานนะครับ

Source:



แบ่งปันบทความนี้ให้คนที่คุณหวังดี :

Similar Posts