เวลาใช้สูตร VLookup หรือ XLookup แล้วรู้สึกไม่สบายใจ 2 ชั้น แบบนี้หรือเปล่า
1. ไม่แน่ใจว่าสูตรจะหาค่าเจอไหม ถ้าไม่เจอแล้วเกิด error เตือนขึ้นมาจะต้องมาสร้างสูตรเพื่อแก้ error กันอีก
2. ถ้าหาค่าเจอแล้วก็ตาม ยังแอบคิดห่วงอยู่ในใจอีกว่า สิ่งที่หาเจอนี้ครบทุกรายการแล้วหรือยัง เพราะถ้ามีรายการที่บันทึกซ้ำกันเอาไว้ สูตรทั้งสองนี้จะกอดคอให้คำตอบเดียวเป็นรายการแรกที่หาเจอมาให้อีกนั่นแหละ หมดสิทธิ์เห็นรายการอื่นที่ซ้ำ
พอเปลี่ยนมาใช้ Excel 365 หรือ 2021 แนะนำให้ใช้สูตร Filter แทนดีกว่าครับ สูตร Filter จะช่วยหาทุกรายการที่มีรหัสตรงกับรหัสสินค้าที่ต้องการมาให้
เหนือชั้นขึ้นอีกนิด ให้ใช้สูตร CountIF ช่วยหาก่อนว่ารหัสนั้นมีกี่รายการ จะได้ไม่ต้องเสียเวลามาแก้ error ตามทีหลัง
* ถ้ามีเพียงรายการเดียว CountIF ในเซลล์ F8 = 1 จึงใช้ VLooup หรือ XLookup ตามสูตรในภาพนี้
=IF( $F$8=1, VLOOKUP( $F$5, MyData, 2, 0 ), 0)
=IF( $F$8=1, XLOOKUP( $F$5, ID, C5:D9 ), 0)
** ถ้ามีรายการเดียวหรือหลายรายการ CountIF ในเซลล์ F8 >= 1 จึงใช้ Filter ตามสูตรในภาพนี้
=IF( $F$8>=1, FILTER( C5:D9, ID=F5 ), 0 )
Download ตัวอย่างจาก
https://drive.google.com/file/d/1k16UiuVrJKeI4VfDxSh3Kbhuzci9FFFJ/view?usp=sharing
+++++++++++++++++++++++++++++++++
Copilot ตอบ:
ใช่เลย! FILTER สามารถเป็นทางเลือกที่ดีกว่า XLOOKUP ในหลายกรณี โดยเฉพาะเมื่อทำงานกับ Dynamic Array เนื่องจากไม่ต้องกำหนดช่วงข้อมูลแบบคงที่
📌 ข้อดีของ FILTER เทียบกับ XLOOKUP ใน Dynamic Array:
- ปรับช่วงข้อมูลอัตโนมัติ: FILTER ดึงเฉพาะค่าที่ตรงตามเงื่อนไขโดยไม่ต้องกำหนดช่วงแบบแน่นอน
- รองรับการคืนค่าหลายรายการ: XLOOKUP ใช้คืนค่าเดี่ยว ส่วน FILTER สามารถคืนค่าเป็นช่วงข้อมูล
- ความยืดหยุ่นสูง: ช่วยคัดกรองข้อมูลตามหลายเงื่อนไขได้ง่ายกว่าการซ้อนหลาย XLOOKUP
- ทำงานร่วมกับ Dynamic Array ได้ดี: FILTER ปรับตัวกับข้อมูลที่เปลี่ยนแปลง ขณะที่ XLOOKUP ต้องระบุช่วงข้อมูลที่แน่นอน
📌 แต่ XLOOKUP ก็มีจุดเด่นของมัน:
- หากต้องการ ค้นหาค่าที่เจาะจง และ ไม่มีหลายค่าที่ต้องคืนกลับ XLOOKUP อาจทำงานได้ตรงไปตรงมากว่า
- สามารถใช้ค้นหาค่าที่ใกล้เคียง (Approximate Match) ได้ ซึ่ง FILTER ไม่รองรับโดยตรง
+++++++++++++++++++++++++++++++++
นี่คือข้อดีและข้อเสียของ FILTER และ XLOOKUP เพื่อช่วยให้คุณเลือกใช้ให้เหมาะสมกับงานของคุณ
🏆 FILTER
✅ ข้อดี:
- คืนค่าหลายรายการพร้อมกัน → ใช้คัดกรองข้อมูลตามเงื่อนไขโดยไม่ต้องเขียนสูตรซ้อนกัน
- รองรับ Dynamic Array → ข้อมูลที่เปลี่ยนแปลงจะปรับตามอัตโนมัติ
- สามารถกำหนดเงื่อนไขหลายรายการได้ง่าย → ใช้ AND หรือ OR ได้สะดวก
❌ ข้อเสีย:
- ไม่รองรับ Approximate Match → ไม่สามารถค้นหาค่าที่ใกล้เคียงที่สุดได้
- ไม่สามารถค้นหาจากหลายตารางพร้อมกันโดยตรง → ต้องใช้สูตรช่วย เช่น VSTACK หรือ INDIRECT
🎯 XLOOKUP
✅ ข้อดี:
- สามารถค้นหาค่าที่ใกล้เคียงที่สุดได้ (Approximate Match) → ใช้ในงานคำนวณเช่น ช่วงคะแนน
- รองรับค่าเริ่มต้นเมื่อไม่พบข้อมูล → ป้องกัน Error โดยสามารถกำหนดค่า Default ได้
- ค้นหาจากหลายตารางได้ → ใช้หลาย Lookup Array ผ่าน IF หรือ CHOOSE ได้
❌ ข้อเสีย:
- คืนค่าได้เพียงหนึ่งค่าต่อเซลล์ → ไม่สามารถคืนค่าเป็น Dynamic Array ได้โดยตรง
- ไม่ยืดหยุ่นเมื่อข้อมูลเปลี่ยนแปลง → ต้องใช้ OFFSET หรือ INDIRECT ช่วย
🧐 สรุป:
- ใช้ FILTER ถ้าต้องการ คืนค่าหลายรายการ และรองรับ Dynamic Array
- ใช้ XLOOKUP ถ้าต้องการ ค้นหาค่าที่แน่นอนหรือใกล้เคียง ในช่วงข้อมูล
+++++++++++++++++++++++++++++++++
เมื่อใช้ XLOOKUP กับ Dynamic Array คุณต้องระวังเรื่องช่วงข้อมูลที่อาจขยายหรือเปลี่ยนแปลงได้
วิธีที่แนะนำคือใช้ XLOOKUP ร่วมกับ Dynamic Array Functions เช่น SEQUENCE หรือ FILTER
✨ ตัวอย่าง 1: ค้นหาค่าใน Dynamic Array
สมมติว่าคุณมีข้อมูลรายชื่อสินค้าอยู่ในช่วง A2:A10 และราคาสินค้าอยู่ใน B2:B10
แต่ช่วงข้อมูลนี้สามารถเปลี่ยนแปลงขนาดได้ (Dynamic Array)
=XLOOKUP("สินค้า1", A2:A10, B2:B10)
☑ คำสั่งนี้จะค้นหาราคา "สินค้า1" จากช่วงข้อมูลที่กำหนด
🏆 วิธีใช้ XLOOKUP ร่วมกับ FILTER สำหรับ Dynamic Array
หากต้องการค้นหาราคาเฉพาะสินค้าที่ตรงตามเงื่อนไข เช่น ราคามากกว่า 500
ใช้ FILTER เพื่อกำหนดช่วงข้อมูลให้เหมาะกับ Dynamic Array
=XLOOKUP("สินค้า1", FILTER(A2:A10, B2:B10>500), FILTER(B2:B10, B2:B10>500))
☑ สูตรนี้จะค้นหาสินค้าเฉพาะที่ราคามากกว่า 500 และใช้กับ Dynamic Array
🔄 XLOOKUP ร่วมกับ SEQUENCE สำหรับ Dynamic Array
ถ้าคุณต้องการ เลือกข้อมูลแถวที่เปลี่ยนแปลงตลอดเวลา เช่น ดึงข้อมูลจากคอลัมน์ที่เปลี่ยนแปลง
ลองใช้ SEQUENCE เพื่อกำหนดช่วง Dynamic Array
=XLOOKUP(1000, SEQUENCE(10,1,500,100), SEQUENCE(10,1,1000,100))
☑ สูตรนี้จะค้นหาค่าที่ใกล้เคียงกับ 1000 ในชุดตัวเลขที่สร้างขึ้นแบบ Dynamic
🚀 สรุป
- XLOOKUP สามารถใช้ร่วมกับ FILTER เพื่อค้นหาข้อมูลที่ตรงเงื่อนไข
- XLOOKUP ร่วมกับ SEQUENCE เหมาะกับการดึงค่าตามช่วงที่เปลี่ยนไป
- เลือกช่วงข้อมูลที่ Dynamic Array รองรับ เพื่อให้สูตรทำงานได้ถูกต้อง
+++++++++++++++++++++++++++++++++
ผมถามกลับไปตามนี้
สูตรนี้ =XLOOKUP("สินค้า1", A2:A10, B2:B10) ถ้าช่วงข้อมูลเป็น dynamic array การที่อ้างอิง A2:A10 กับ B2:B10 ไว้ พอตารางที่เป็น dynamic array ขยายขนาดทั้งจำนวน column และจำนวน row
สูตร =XLOOKUP("สินค้า1", A2:A10, B2:B10) จะขยายตำแหน่งอ้่างอิงในวงเล็บตามไหม
Copilot ตอบ
สูตร =XLOOKUP("สินค้า1", A2:A10, B2:B10) จะ ไม่ขยายอัตโนมัติ ตามขนาดของ Dynamic Array หากตารางมีการขยายทั้งแถวและคอลัมน์
เนื่องจาก XLOOKUP ใช้ช่วงแบบ Fixed References (เช่น A2:A10, B2:B10) ซึ่งไม่ได้ปรับขนาดตาม Spilled Range
🔄 วิธีแก้ไขให้ XLOOKUP รองรับ Dynamic Array
✅ ใช้ชื่อช่วงแบบ Dynamic Range:
ลองใช้ INDEX หรือ INDIRECT เพื่อช่วยกำหนดช่วงข้อมูลให้เปลี่ยนแปลงตาม Dynamic Array
=XLOOKUP("สินค้า1", A2:INDEX(A:A,ROWS(A2#)), B2:INDEX(B:B,ROWS(B2#)))
📌 สูตรนี้ใช้ A2# และ B2# เพื่อให้ XLOOKUP อ้างอิงช่วงข้อมูลที่ขยายอัตโนมัติ
✅ ใช้ FILTER เพื่อช่วยกำหนดช่วง:
=XLOOKUP("สินค้า1", FILTER(A2:A100, A2:A100<>""), FILTER(B2:B100, A2:A100<>""))
📌 สูตรนี้ช่วยคัดกรองค่าที่มีอยู่จริง และปรับช่วงให้รองรับ Dynamic Array
🚀 สรุป
- XLOOKUP ไม่สามารถปรับขนาดช่วงข้อมูลเอง → ต้องใช้ INDEX หรือ FILTER ช่วย
- ช่วงที่เป็น Dynamic Array ใช้ # (Spilled Range) ได้ → เช่น A2# เพื่อให้ XLOOKUP ดึงข้อมูลที่ขยายอัตโนมัติ
คุณลองใช้กับข้อมูลที่มีอยู่หรือยังครับ? 😊
