ฟังก์ชัน Lookup ทำให้การดึงข้อมูลมีประสิทธิภาพ Excel มีตัวเลือก Lookup หลายตัว และการเลือกกลยุทธ์ที่เหมาะสมอาจ棘手 การเลือกวิธีที่เหมาะสมที่สุดให้ผลลัพธ์ที่น่าเชื่อถือที่สุด ในบทช่วยสอนนี้ เราสร้างกรอบงาน Excel lookup function สำหรับเลือกกลยุทธ์ที่ถูกต้อง กรอบงานนี้ช่วยให้ผู้ใช้ระดับสูงตัดสินใจว่าวิธีไหนเหมาะสมที่สุดตามรูปร่างข้อมูล ขนาด และเจตนา
คุณสามารถปฏิบัติตามแผนภาพข้างต้นเพื่อเลือกกลยุทธ์ lookup ที่ถูกต้อง
XLOOKUP: เหมาะสำหรับ One-to-One Lookup (Excel สมัยใหม่)
ก่อนอื่น ตัดสินใจว่าคุณกำลังค้นหาอะไร คุณกำลังดึงเรคคอร์ดที่ตรงกันเพียงตัวเดียว หรือคุณต้องการเรคคอร์ดทั้งหมดที่ตรงกับเกณฑ์ของคุณ?
ใช้ XLOOKUP เมื่อคุณต้องการผลลัพธ์เดียว สูตรที่อ่านง่าย และการจัดการ "not found" แบบ optional
พิจารณาชุดข้อมูลการขายตัวอย่างที่คำสั่งซื้อ รายละเอียดสินค้า และข้อมูลลูกค้าอยู่ในตารางแยกกัน เราสำรวจกลยุทธ์ lookup สำหรับดึงข้อมูลการขายจากตารางต่างๆ
Lookup UnitPrice เข้า Orders:
=XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7, "Not found")
สูตรคล้ายกันในรูปแบบ Table:
=XLOOKUP(Orders[@SKU], Products[SKU], Products[UnitPrice], "SKU not found")
ค้นหา CustomerName ใน Orders:
=XLOOKUP(Orders[@CustomerID], Customers[CustomerID], Customers[CustomerName], "Customer not found")
สูตรเหล่านี้ค้นหาค่าในตารางต่างๆ และคืนเรคคอร์ดที่ตรงกันเพียงตัวเดียว XLOOKUP สามารถเป็นตัวเลือกเริ่มต้นของคุณสำหรับ one-to-one lookups ใน Excel สมัยใหม่ มันเสนอ:
- Syntax สะอาด อ่านง่าย พร้อม error handling ผ่านอาร์กิวเมนต์ if_not_found
- รองรับ leftward และ bidirectional lookups โดยเนทีฟ
- Exact และ approximate match modes โดยไม่ต้องใช้พารามิเตอร์ match-type ที่ลึกลับ
ใช้ XLOOKUP เมื่อคุณมี Microsoft 365 หรือ Excel 2021+ และต้องการการดึงค่าเดี่ยวที่ตรงไปตรงมา
INDEX/MATCH: เหมาะสำหรับความเข้ากันได้และยังทรงพลัง
เลือก INDEX/MATCH ถ้าไฟล์ของคุณต้องทำงานใน Excel รุ่นเก่า หรือเมื่อมาตรฐานของทีมคือ INDEX/MATCH
UnitPrice ผ่าน INDEX/MATCH:
=INDEX(Products[UnitPrice], MATCH(Orders[@SKU], Products[SKU], 0))
สูตรนี้ยังคงเป็นตัวเลือกที่มั่นคงสำหรับรุ่นเก่า แต่อ่านยากกว่าและบำรุงรักษายากกว่า XLOOKUP สำหรับทีมหลายทีม
FILTER: เหมาะสำหรับ One-to-Many Results (Spilled Lists)
เลือก FILTER เมื่อคุณต้องการรายการเรคคอร์ดที่ไดนามิกซึ่งอัปเดตอัตโนมัติ มันดึงชุดย่อยของข้อมูลตามเกณฑ์และเก็บผลลัพธ์ไว้อัปเดตเสมอ FILTER สร้างขึ้นมาเพื่อ one-to-many retrieval:
- คืนแถวที่ตรงกันทั้งหมดเป็น dynamic spill array
- จัดการหลายเกณฑ์ด้วย Boolean AND/OR logic
- ขยายและหดตัวอัตโนมัติเมื่อข้อมูลต้นทางเปลี่ยน
- ผสานรวมกับฟังก์ชัน dynamic array อื่นๆ ได้อย่างราบรื่น
แสดงคำสั่งซื้อทั้งหมดสำหรับ CustomerID:
=FILTER(Orders, Orders[CustomerID]=H2, "No orders found")
คำสั่งซื้อ West Region ทั้งหมดสำหรับ SKU P-101:
=FILTER(Orders, (Orders[Region]=H9)*(Orders[SKU]=H7), "No matches")
สูตรเหล่านี้ต่างจาก XLOOKUP เพราะ FILTER ไม่คืนค่าเดียว—มันคืนคำตอบรูปตารางที่ spill ออกมา นั่นคืองานที่ต่างจาก XLOOKUP/INDEX-MATCH
Power Query: เหมาะเมื่อ Lookup จริงๆ แล้วคือ "Join" (Data Modeling)
Power Query กลายเป็นตัวเลือกที่ดีกว่าเมื่อคุณต้องทำการแปลงข้อมูลบนผลลัพธ์ที่กรองแล้ว ใช้เมื่อ:
- ข้อมูลมีขนาดใหญ่
- ข้อมูลมาจากแหล่งภายนอกและต้อง refresh
- คุณต้องการ transformation pipeline ที่ทำซ้ำได้
- คุณต้องการสร้างตารางรวมที่สะอาดสำหรับ PivotTables/แดชบอร์ด
มาสร้างตารางที่รวมคอลัมน์ Product และ Customer (UnitPrice, Product, CustomerName, Segment)
ขั้นตอน (Merge/Join):
- แปลงช่วงเป็นตาราง (กด CTRL+T) ข้อมูลของเราอยู่ในรูปแบบตารางแล้ว
- ไปที่แท็บ Data >> เลือก Get Data >> เลือก From Table/Range (เริ่มจาก Orders)
- ใน Power Query Editor: Home >> เลือก Merge Queries
- Merge Orders กับ Products table
- เลือก Orders[SKU] และ Products[SKU]
- Join kind: เลือก Left Outer (เก็บ Orders ทั้งหมด)
- คลิก OK
-
- คลิกไอคอน Expand >> เลือก Product, Category, และ UnitPrice
- คลิก OK
- Merge Orders กับตาราง Customers
- จับคู่ Orders[CustomerID] กับ Customers[CustomerID]
-
- คลิกไอคอน Expand >> เลือก CustomerName, Segment
- คลิก OK
- คลิก Close & Load เพื่อโหลดข้อมูลใน Excel
นี่คือตาราง Orders ที่เพิ่มรายละเอียด product และ customer ใหม่
คุณ merge หนึ่งครั้ง refresh ได้ทุกเมื่อ และหลีกเลี่ยงการ lookup ซ้ำๆ ข้ามหลายพันแถวและหลายคอลัมน์
การเลือกระหว่าง FILTER และ Power Query สำหรับ One-to-Many
FILTER และ Power Query ทั้งคู่ให้ผลลัพธ์ขั้นสูง อัตโนมัติ บางครั้งผู้ใช้ลังเลเมื่อเลือกหนึ่งสำหรับ one-to-many
- เลือก FILTER เมื่อต้องการประสบการณ์ชีทแบบโต้ตอบ เปลี่ยนเซลล์เกณฑ์ และรายการอัปเดตทันที เหมาะสำหรับแดชบอร์ดโต้ตอบ
- เลือก Power Query เมื่อต้องการชุดข้อมูลที่ refresh ได้เพื่อรายงาน (PivotTables, charts, model tables) โดยเฉพาะกับข้อมูลใหญ่
เคล็ดลับและเทคนิค
- ถ้าผลลัพธ์เป็นเซลล์เดียว ให้เลือก XLOOKUP (หรือ INDEX/MATCH สำหรับความเข้ากันได้)
- ถ้าผลลัพธ์เป็นรายการ/ตารางที่ควรโตอัตโนมัติ ให้เลือก FILTER
- ถ้างานพื้นฐานคือการ join ชุดข้อมูลและต้อง refresh/scale ได้ ให้ใช้ Power Query
- ถ้าคุณทำ lookup เดียวกันข้ามหลายคอลัมน์ (ราคา หมวดหมู่ ชื่อ segment…) เราแนะนำ Power Query merge แทนการซ้ำสูตร
สรุป
แผนภาพกระบวนการในบทช่วยสอนนี้ช่วยให้คุณเลือกกลยุทธ์ lookup ที่ถูกต้อง ตัดสินใจก่อนว่าข้อมูลของคุณต้องการ lookup ประเภทไหน ไม่มี lookup function "ที่ดีที่สุด" สากล—บริบทกำหนดวิธีที่เหมาะสมที่สุด เก่งกรอบงาน ไม่ใช่แค่ฟังก์ชัน และกลยุทธ์ lookup ของคุณจะปรับขนาดได้ตามความต้องการ พัฒนาสense กลยุทธ์ในการเลือกวิธี lookup ที่ถูกต้อง และโซลูชันของคุณจะเร็วกว่าในการสร้าง ง่ายกว่าในการบำรุงรักษา และทนทานกว่าเมื่อความต้องการพัฒนาไป
