Cool Tech

XLOOKUP ฟังชั่นใหม่ของ Excel ที่เกิดมาแทน VLOOKUP

XLOOKUP คือ ฟังชั่นใหม่ของ Excel เปิดตัวเมื่อเดือนสิงหาคม 2019 เพื่อแทนที่ฟังชั่น VLOOKUP และ HLOOKUP เขียนง่ายกว่าเดิม แถมมีลูกเล่นมากขึ้น เช่น การดึงข้อมูลจากหลายๆ คอลัมน์พร้อมกัน และ search_mode เพื่อให้ฟังชั่นทำงานเร็วขึ้น บทความนี้แอดมารีวิววิธีการใช้งานฟังชั่นนี้ด้วย Excel Office 365

*สามารถดาวน์โหลดไฟล์ตัวอย่างได้ที่นี่

VLOOKUP

สมัยก่อนถ้าเรารู้ชื่อทีมฟุตบอล (Team = “Manchester United”) และต้องการ lookup คะแนนล่าสุดของทีมที่เราต้องการ (Points = ?)
เราต้องเขียนฟังชั่น VLOOKUP ตามตัวอย่างด้านล่าง

    =VLOOKUP("Manchester United", B2:J21, 9, FALSE)

เลข 9 ในสูตรคือตำแหน่งของคอลัมน์ที่เราต้องการดึงค่าออกมา (column index) ส่วน FALSE คือการดึงค่าแบบ exact match แปลว่าในตาราง B2:J21 คอลัมน์ B ต้องมีคำว่า “Manchester United” อยู่จริงๆ VLOOKUP ถึงจะสามารถดึงค่ากลับมาได้ สูตรด้านล่างอ่านเป็นภาษาไทยว่า “แมนยูตอนนี้มีแต้มอยู่กี่คะแนน?”

ข้อจำกัดของ VLOOKUP คือการดึงค่าได้ทีละคอลัมน์ และเราต้องรู้ตำแหน่งของคอลัมน์ที่ต้องการดึงด้วย ถ้า Excel หาคำว่า “Manchester United”
ไม่เจอ VLOOKUP จะส่งค่า error “#N/A” กลับมา

ทำไมมันยุ่งยากอย่างนี้! คราวนี้มาลองดูวิธีการเขียน XLOOKUP กันบ้าง

XLOOKUP

XLOOKUP มี required arguments ที่ต้องระบุค่าทั้งหมด 3 ตัว คือ lookup_value, lookup_array และ return_array สังเกตเวลาเราพิมพ์ชื่อ
ฟังชั่น ในเซลล์ Excel จะแสดงชื่อ arguments ของฟังชั่นนั้นขึ้นมา

Note – arguments ที่อยู่ใน [ ] เช่น [if_not_found] คือ optional ไม่ต้องใส่ค่าก็กด Enter รันฟังชั่นได้ปกติ

สูตร XLOOKUP ด้านล่างจะได้ผลลัพธ์เท่ากับ VLOOKUP ที่เราเขียนใน section ที่แล้ว โดย “Manchester United” คือ lookup value, B2:B21 คือ
คอลัมน์ที่เราต้องการไป search และ J2:J21 คือคอลัมน์ที่เราต้องการดึงค่ามาแสดงเป็นผลลัพธ์ เราสามารถใช้เม้าส์เลือกคอลัมน์ได้เลย
(i.e. ไม่จำเป็นต้องระบุ column index เหมือนตอนใช้ VLOOKUP แล้ว)

=XLOOKUP("Manchester United", B2:B21, J2:J21)

ถ้า XLOOKUP หาคำว่า “Manchester United” ในคอลัมน์ B2:B21 ไม่เจอ แทนที่จะแสดงค่า #N/A เราสามารถเลือกคำที่ต้องการแสดงได้เองเช่น “Not Found” ใส่เป็น argument ที่สี่ของสูตร XLOOKUP แบบนี้

=XLOOKUP("Manchester United", B2:B21, J2:J21, "Not Found")

และทีเด็ดของฟังชั่น XLOOKUP คือความสามารถในการดึงค่าจากหลายๆคอลัมน์ได้พร้อมกัน นี่คือเหตุผลที่ argument ที่สามของฟังชั่นนี้ชื่อว่า “return_array” ตัวอย่างด้านล่างเราดึงค่าจากคอลัมน์ D:F ของ “Manchester United” ออกมา 3 คอลัมน์พร้อมกันเลย! =XLOOKUP("Manchester United", B2:B21, D2:F21, "Not Found")

ถ้าเราต้องการ lookup value แบบ exact match เหมือนฟังชั่น VLOOKUP เราสามารถใส่เลขศูนย์ใน argument ที่ห้า (อันนี้ใช้ logic เดียวกับของ VLOOKUP)

=XLOOKUP("Manchester United", B2:B21, D2:F21, "Not Found", 0)

ส่วน argument สุดท้าย [search_mode] คือการเลือกว่าจะให้ XLOOKUP หา lookup_value จากด้านบนลงล่าง (default = 1) หรือจากล่างขึ้นมาบน
หรือแบบ binary search เพื่อให้ฟังชั่นทำงานเร็วขึ้น

Summary

อ่านบทความนี้จบก็เลิกใช้ VLOOKUP ได้เลย XLOOKUP ดีกว่าเยอะ!

XLOOKUP มี required arguments สามตัว: lookup_value, lookup_array, return_array
XLOOKUP สามารถดึงค่าจากหลายๆคอลัมน์ได้พร้อมกัน
XLOOKUP สามารถระบุค่าที่ต้องการ ถ้าหา lookup_value ใน lookup_array ไม่เจอ
XLOOKUP เกิดมาเพื่อแทนที่ VLOOKUP และ HLOOKUP เลย ลองดูตัวอย่างการเขียน lookup_value แบบ row-wise ได้ในไฟล์ตัวอย่างของบทความนี้

เพื่อนๆ อยากใช้งาน XLOOKUP บ้างมั้ย ถ้าใครอยากใช้ ต้องอดใจรอนิดนึงนะ เพราะว่า XLOOKUP ใช้ได้ใน Excel Office 365 เท่านั้น อดใจรอไปก่อนนะเพื่อนๆ แต่ฟังก์ชั่นถัดไป เพื่อนๆ สามารถนำไปใช้งานได้เลย เราลองไปทำความรู้จักกับฟังก์ชั่นต่อไปกันเลยจ้า

Excel Flash Fill การเติมข้อความให้อัตโนมัติ ตามรูปแบบที่เราต้องการ

สำหรับคนที่ต้องการใช้ Flash Fill Excel ในการเติมข้อความต่างๆ ที่เราได้กำหนดรูปแบบเอาไว้แล้ว โดยเครื่องมือตัวนี้ มีมาตั้งแต่ Excel 2013 /2016 โดยเป็น Tools ที่ใช้ง่ายต่อการใช้งานมาก ประหยัดเวลามาก ในการทำข้อมูลเพิ่มเติมขึ้นมาอีกคอลัมน์ หรือแถว ใน Microsoft Excel โดยถ้าเพื่อนๆจะข้อมูลของตัวเลข จำนวนมากๆและต้องการมาเรียงข้อมูลให้อยู่ใน format แบบใหม่ที่เราต้องการ เพื่อนๆคงไม่นั่งมาทำเองทีละช่องละจริงไหม โดยผมแนะนำให้ใช้ Flash Fill ในการเติมข้อมูลให้ดีกว่า

หลักการทำงานของ Flash Fill

หากคุณต้องการผลลัพธ์แบบไหน ก็พิมพ์บอก Excel ไปซะ จากนั้นกด Ctrl+E แล้ว Excel จะพยายามหารูปแบบ (Pattern) จากสิ่งที่คุณพิมพ์ลงไปเอง ! (ถ้ายังหา Pattern ไม่ได้ ก็พิมพ์สอนเพิ่มไปอีก แล้วมันจะฉลาดขึ้น) เราลองไปดูตัวอย่างว่าสามารถใช้ Flash Fill ช่วยทำอะไรได้บ้าง

ตัวอย่าง : การแยก ชื่อจริง – นามสกุล ออกจากกัน

โดยผมต้องการข้อมูลที่แยกออกจากกัน ทั้ง First name / Last name

ทำการพิมพ์ ข้อความลงไปก่อน ให้ถูกต้อง > คลิกเมาส์ไว้ที่ Cell ที่เราพิมพ์ข้อความ ที่เราตั้งเป็นตัวอย่าง > คลิก Tab : Data >
คลิก Flash Fill (CTRL + E)

จากนั้นเราจะได้ข้อมูลดังภาพ โดยข้อมูลต่างๆเราไม่ต้องทำการกรอกเลย Excel จะทำการกรอกให้ตามปกติ

ตัวอย่าง : การเปลี่ยนตัวเลขฟอร์แมทของเบอร์โทรศัพท์

ทำการพิมพ์ข้อมูลที่ถูกต้องตามตัวอย่างหรือข้อมูลที่เราต้องการ > เลือก Cell ข้อมูลที่เราแสดงเป็นตัวอย่าง > แถบ Data > คลิก Flash Fill (CTRL+E)

เราก็จะได้ข้อมูลดังภาพ

ตัวอย่าง : ต้องการแบ่ง – ในการทำ ID ของสิ่งของหรือพนักงาน

ทำการพิมพ์ข้อความที่ถูกต้องลงไป > คลิก Cell ที่เราพิมพ์เป็นตัวอย่าง > Tab : Data > คลิก Flash Fill (CTRL+E)

เราก็จะได้ดังภาพ

ในการใช้ฟังก์ชั่น Flash Fill เป็นการกรอกข้อมูลตามรูปแบบที่เราต้องการ โดยคอลัมน์ หรือแถวอื่นๆ เราจะได้ไม่ต้องมากรอกข้อมูลลงเองนั่นเอง โดยประหยัดเวลามากมายในการทำงาน โดยถ้าเราเจอข้อมูลมากๆใน Excel หวังว่าเราคงไม่มานั่งทำเองทีละเซลล์หรอก จริงมะ

Create Date : 30 เมษายน 2564

Last Update : 31 พฤษภาคม 2564 10:32:58 น.

0 comments

Counter : 366 Pageviews.