ฟังก์ชันการค้นหาทำให้การดึงข้อมูลมีประสิทธิภาพ Excel เสนอตัวเลือกการค้นหาที่หลากหลาย และการเลือกกลยุทธ์ที่เหมาะสมอาจเป็นเรื่องยุ่งยาก การเลือกแนวทางที่เหมาะสมที่สุดจะทำให้ได้ผลลัพธ์ที่น่าเชื่อถือที่สุด ในบทช่วยสอนนี้ เราสร้างเฟรมเวิร์กฟังก์ชันการค้นหา Excel สำหรับการเลือกกลยุทธ์ที่เหมาะสม เฟรมเวิร์กนี้ช่วยให้ผู้ใช้ที่มีประสบการณ์ตัดสินใจได้ว่าแนวทางใดเหมาะสมที่สุดโดยพิจารณาจากรูปร่าง ขนาด และความตั้งใจของข้อมูล
คุณสามารถปฏิบัติตามผังงานด้านบนเพื่อเลือกกลยุทธ์การค้นหาที่เหมาะสม
XLOOKUP:ดีที่สุดสำหรับการค้นหาแบบตัวต่อตัว (Modern Excel)
ขั้นแรก ตัดสินใจเลือกสิ่งที่คุณกำลังมองหา คุณกำลังเรียกข้อมูลบันทึกที่ตรงกันเพียงรายการเดียว หรือคุณต้องการบันทึกทั้งหมดที่ตรงกับเกณฑ์ของคุณหรือไม่
ใช้ XLOOKUP เมื่อคุณต้องการผลลัพธ์เดียว สูตรที่อ่านได้ และตัวเลือกการจัดการ "ไม่พบ"
พิจารณาชุดข้อมูลการขายตัวอย่างซึ่งมีคำสั่งซื้อ รายละเอียดผลิตภัณฑ์ และข้อมูลลูกค้าอยู่ในตารางแยกกัน เราสำรวจกลยุทธ์การค้นหาเพื่อดึงข้อมูลการขายจากตารางต่างๆ
ค้นหาราคาต่อหน่วยเป็นคำสั่งซื้อ:
=XLOOKUP(D2, Products!$A$2:$A$7, Products!$D$2:$D$7, "Not found")
สูตรที่คล้ายกันในรูปแบบตาราง:
=XLOOKUP(Orders[@SKU], Products[SKU], Products[UnitPrice], "SKU not found")
การค้นหาชื่อลูกค้าในคำสั่งซื้อ:
=XLOOKUP(Orders[@CustomerID], Customers[CustomerID], Customers[CustomerName], "Customer not found")
สูตรเหล่านี้จะค้นหาค่าในตารางต่างๆ และส่งกลับระเบียนที่ตรงกันเพียงรายการเดียว XLOOKUP สามารถเป็นตัวเลือกเริ่มต้นของคุณสำหรับการค้นหาแบบหนึ่งต่อหนึ่งใน Excel สมัยใหม่ มันมี:
- ไวยากรณ์ที่ชัดเจนและอ่านง่ายพร้อมการจัดการข้อผิดพลาดในตัวผ่านทาง if_not_found ข้อโต้แย้ง
- การสนับสนุนดั้งเดิมสำหรับการค้นหาด้านซ้ายและแบบสองทิศทาง
- โหมดการจับคู่แบบตรงทั้งหมดและโดยประมาณโดยไม่มีพารามิเตอร์ประเภทการจับคู่แบบอาร์เคน
ใช้ XLOOKUP เมื่อคุณมี Microsoft 365 หรือ Excel 2021+ และต้องการเรียกข้อมูลค่าเดียวที่ตรงไปตรงมา
INDEX/MATCH:ดีที่สุดสำหรับความเข้ากันได้และยังคงทรงพลัง
เลือก INDEX/MATCH หากไฟล์ของคุณต้องทำงานใน Excel เวอร์ชันเก่า หรือเมื่อมาตรฐานทีมของคุณคือ INDEX/MATCH
ราคาต่อหน่วยผ่าน INDEX/MATCH:
=INDEX(Products[UnitPrice], MATCH(Orders[@SKU], Products[SKU], 0))
สูตรนี้ยังคงเป็นตัวเลือกที่ดีสำหรับเวอร์ชันเก่า แต่จะอ่านผิดได้ง่ายกว่าและดูแลรักษาตามขนาดได้ยากกว่า XLOOKUP สำหรับหลายๆ ทีม
ตัวกรอง:ดีที่สุดสำหรับผลลัพธ์แบบหนึ่งต่อหลายรายการ (รายการที่หก)
เลือกตัวกรองเมื่อคุณต้องการรายการเรกคอร์ดแบบไดนามิกที่อัปเดตโดยอัตโนมัติ โดยแยกชุดย่อยของข้อมูลตามเกณฑ์และอัปเดตผลลัพธ์เหล่านั้นโดยอัตโนมัติ FILTER สร้างขึ้นโดยมีจุดประสงค์เพื่อการดึงข้อมูลแบบหนึ่งต่อหลาย:
- ส่งคืนแถวที่ตรงกันทั้งหมดเป็นอาร์เรย์สปิลแบบไดนามิก
- จัดการหลายเกณฑ์ด้วยตรรกะบูลีนและ/หรือ
- ขยายและย่อโดยอัตโนมัติเมื่อข้อมูลต้นฉบับเปลี่ยนแปลง
- ผสานรวมอย่างลงตัวกับฟังก์ชันอาร์เรย์ไดนามิกอื่นๆ
แสดงคำสั่งซื้อทั้งหมดสำหรับรหัสลูกค้า:
=FILTER(Orders, Orders[CustomerID]=H2, "No orders found")
คำสั่งซื้อภูมิภาคตะวันตกทั้งหมดสำหรับ SKU P-101:
=FILTER(Orders, (Orders[Region]=H9)*(Orders[SKU]=H7), "No matches")
สูตรเหล่านี้แตกต่างจาก XLOOKUP เนื่องจาก FILTER จะไม่ส่งกลับค่าเดียว แต่จะส่งกลับคำตอบที่หกในรูปแบบตาราง นั่นเป็นงานที่แตกต่างจาก XLOOKUP/INDEX-MATCH
Power Query:ดีที่สุดเมื่อการค้นหาเป็นการ "เข้าร่วม" จริงๆ (การสร้างแบบจำลองข้อมูล)
Power Query กลายเป็นตัวเลือกที่ดีกว่าเมื่อคุณต้องการดำเนินการแปลงผลลัพธ์ที่ถูกกรอง ใช้มันเมื่อ
- ข้อมูลมีขนาดใหญ่
- ข้อมูลมาจากแหล่งภายนอกและต้องรีเฟรช
- คุณต้องการไปป์ไลน์การเปลี่ยนแปลงที่ทำซ้ำได้
- คุณต้องการสร้างตารางที่สะอาดและผสานกันสำหรับ PivotTable/แดชบอร์ด
มาสร้างตารางที่มีคอลัมน์ผลิตภัณฑ์และลูกค้า (ราคาต่อหน่วย ผลิตภัณฑ์ ชื่อลูกค้า เซ็กเมนต์)
ขั้นตอน (รวม/เข้าร่วม):
- แปลงช่วงเป็นตาราง (กด CTRL+T ). ข้อมูลของเราอยู่ในรูปแบบตารางแล้ว
- ไปที่ ข้อมูล แท็บ>> เลือก รับข้อมูล >> เลือก จากตาราง/ช่วง (เริ่มต้นด้วย คำสั่งซื้อ ).
- ในตัวแก้ไข Power Query:หน้าแรก >> เลือก รวมข้อความค้นหา
- รวม คำสั่งซื้อ กับ ผลิตภัณฑ์ ตาราง
- เลือก คำสั่งซื้อ[SKU] และ ผลิตภัณฑ์[SKU]
- เข้าร่วมชนิด: เลือก ด้านนอกซ้าย (เก็บคำสั่งซื้อทั้งหมด)
- คลิก ตกลง
- คลิก ขยาย ไอคอน>> เลือก ผลิตภัณฑ์ หมวดหมู่ และราคาต่อหน่วย
- คลิก ตกลง
- รวม คำสั่งซื้อ กับลูกค้า ตาราง
- จับคู่ คำสั่งซื้อ[รหัสลูกค้า] ถึง ลูกค้า[รหัสลูกค้า]
- คลิก ขยาย ไอคอน>> เลือก ชื่อลูกค้า , กลุ่ม
- คลิก ตกลง
- คลิก ปิดและโหลด เพื่อโหลดข้อมูลใน Excel
นี่คือตารางที่เพิ่มข้อมูลคำสั่งซื้อใหม่ ซึ่งประกอบด้วยรายละเอียดสินค้าและรายละเอียดลูกค้า
คุณรวมครั้งเดียว รีเฟรชได้ทุกเมื่อ และหลีกเลี่ยงการค้นหาซ้ำในแถวหลายพันแถวและหลายคอลัมน์
การเลือกระหว่าง FILTER และ Power Query สำหรับแบบหนึ่งต่อกลุ่ม
ทั้ง FILTER และ Power Query นำเสนอผลลัพธ์อัตโนมัติขั้นสูง บางครั้งผู้ใช้ต้องเผชิญกับภาวะที่กลืนไม่เข้าคายไม่ออกเมื่อเลือกแบบหนึ่งต่อกลุ่ม
- เลือกตัวกรองเมื่อต้องใช้แผ่นงานเชิงโต้ตอบ เปลี่ยนเซลล์เกณฑ์ และรายการจะอัปเดตทันที มันมีประโยชน์สำหรับแดชบอร์ดแบบโต้ตอบ
- เลือก Power Query เมื่อจำเป็นต้องใช้ชุดข้อมูลที่รีเฟรชได้เพื่อป้อนการรายงาน (PivotTable, แผนภูมิ, ตารางแบบจำลอง) โดยเฉพาะอย่างยิ่งกับข้อมูลที่ใหญ่กว่า
เคล็ดลับและคำแนะนำ
- หากเอาต์พุตเป็นเซลล์เดียว ให้เลือก XLOOKUP (หรือ INDEX/MATCH เพื่อความเข้ากันได้)
- หากผลลัพธ์เป็นรายการ/ตารางที่ควรขยายโดยอัตโนมัติ ให้เลือกตัวกรอง
- หากงานเป็นการรวมชุดข้อมูลโดยพื้นฐานและต้องรีเฟรช/ปรับขนาดได้ ให้ใช้ Power Query
- หากคุณทำการค้นหาแบบเดียวกันในหลายคอลัมน์ (ราคา หมวดหมู่ ชื่อ กลุ่ม...) เราขอแนะนำอย่างยิ่งให้รวม Power Query แทนการใช้สูตรซ้ำ
บทสรุป
ผังงานบทช่วยสอนนี้ช่วยให้คุณเลือกกลยุทธ์การค้นหาที่เหมาะสม ตัดสินใจก่อนว่าต้องใช้การค้นหาข้อมูลประเภทใด ไม่มีฟังก์ชันการค้นหาที่ "ดีที่สุด" แบบสากล เนื่องจากบริบทจะกำหนดแนวทางที่เหมาะสมที่สุด เชี่ยวชาญกรอบงาน ไม่ใช่แค่ฟังก์ชั่น และกลยุทธ์การค้นหาของคุณจะปรับขนาดตามความต้องการของคุณ พัฒนาความรู้สึกเชิงกลยุทธ์เพื่อเลือกวิธีการค้นหาที่เหมาะสม และโซลูชันของคุณจะสร้างได้เร็วกว่า บำรุงรักษาง่ายกว่า และมีประสิทธิภาพมากขึ้นตามความต้องการที่พัฒนาขึ้น
รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!