สิ่งที่ควรรู้
- สร้างฟังก์ชัน INDEX ก่อน แล้วเริ่มฟังก์ชัน MATCH ต่อยอดโดยใส่อาร์กิวเมนต์ Lookup_value
- เพิ่มอาร์กิวเมนต์ Lookup_array ตามด้วย Match_type แล้วระบุช่วงคอลัมน์
- แปลงฟังก์ชันต่อยอดเป็น Array Formula โดยกด Ctrl+Shift+Enter สุดท้ายเพิ่มเงื่อนไขค้นหาในแผ่นงาน
บทความนี้อธิบายวิธาสร้างสูตร Lookup ที่ใช้หลายเกณฑ์ใน Excel เพื่อค้นหาข้อมูลในฐานข้อมูลหรือตารางข้อมูล โดยใช้ Array Formula ซึ่งเกี่ยวข้องกับการต่อยอดฟังก์ชัน MATCH ภายในฟังก์ชัน INDEX ใช้ได้กับ Excel สำหรับ Microsoft 365, Excel 2019, 2016, 2013, 2010 และ Excel สำหรับ Mac
ทำตามบทเรียนได้เลย
เพื่อทำตามขั้นตอนในบทเรียนนี้ ให้ใส่ข้อมูลตัวอย่างลงในเซลล์ต่อไปนี้ ตามภาพด้านล่าง แถว 3 และ 4 ว่างไว้สำหรับ Array Formula ที่จะสร้างในบทเรียนนี้ (หมายเหตุ: บทเรียนนี้ไม่รวมการจัดรูปแบบที่เห็นในภาพ)
- ใส่ข้อมูลช่วงบนสุดในเซลล์ D1 ถึง F2
- ใส่ข้อมูลช่วงที่สองในเซลล์ D5 ถึง F11
สร้างฟังก์ชัน INDEX ใน Excel
ฟังก์ชัน INDEX เป็นหนึ่งในฟังก์ชันน้อยๆ ใน Excel ที่มีหลายรูปแบบ มี Array Form และ Reference Form Array Form จะส่งคืนข้อมูลจากฐานข้อมูลหรือตารางข้อมูล ส่วน Reference Form จะให้การอ้างอิงเซลล์หรือตำแหน่งของข้อมูลในตาราง
ในบทเรียนนี้ใช้ Array Form เพื่อหาชื่อผู้จำหน่ายของ "Titanium Widgets" แทนที่จะเป็นการอ้างอิงเซลล์ไปยังผู้จำหน่ายในฐานข้อมูล
ทำตามขั้นตอนเหล่านี้เพื่อสร้างฟังก์ชัน INDEX:
- เลือกเซลล์ F3 ให้เป็น Active cell เซลล์นี้คือที่ที่จะใส่ฟังก์ชันต่อยอด
- ไปที่ Formulas
- เลือก Lookup & Reference เพื่อเปิดรายการแบบดรอปดาวน์ของฟังก์ชัน
- เลือก INDEX เพื่อเปิดกล่องโต้ตอบ Select Arguments
- เลือก array, row_num, column_num
- เลือก OK เพื่อเปิดกล่องโต้ตอบ Function Arguments (ใน Excel for Mac จะเปิด Formula Builder)
- วางเคอร์เซอร์ในกล่องข้อความ Array
- เลือกไฮไลท์เซลล์ D6 ถึง F11 ในแผ่นงานเพื่อใส่ช่วงเข้าไปในกล่องโต้ตอบ ทิ้งกล่องโต้ตอบ Function Arguments ไว้เปิดอยู่ สูตรยังไม่เสร็จ จะเสร็จในขั้นตอนต่อไป
เริ่มฟังก์ชัน MATCH แบบต่อยอด (Nested)
เมื่อต่อยอดฟังก์ชันหนึ่งอยู่ภายในอีกฟังก์ชันหนึ่ง ไม่สามารถเปิด Formula Builder ของฟังก์ชันที่สอง (Nested) เพื่อใส่อาร์กิวเมนต์ได้ ฟังก์ชันที่ต่อยอดต้องใส่เป็นอาร์กิวเมนต์ของฟังก์ชันแรกแทน
เมื่อใส่ฟังก์ชันด้วยมือ อาร์กิวเมนต์ของฟังก์ชันจะคั่นจากกันด้วยเครื่องหมายจุลภาค (Comma)
ขั้นตอนแรกในการใส่ฟังก์ชัน MATCH แบบต่อยอดคือการใส่อาร์กิวเมนต์ Lookup_value Lookup_value คือตำแหน่งหรือการอ้างอิงเซลล์สำหรับเงื่อนไขค้นหาที่จะจับคู่ในฐานข้อมูล
Lookup_value รับได้เพียงเกณฑ์ค้นหา (Search Criteria) เดียว เพื่อค้นหาหลายเกณฑ์ ให้ขยาย Lookup_value โดยการต่อติด (Concatenate) การอ้างอิงเซลล์ 2 ตัวขึ้นไปด้วยสัญลักษณ์ Ampersand (&)
- ในกล่องโต้ตอบ Function Arguments วางเคอร์เซอร์ในกล่องข้อความ Row_num
- พิมพ์ MATCH(
- เลือกเซลล์ D3 เพื่อใส่การอ้างอิงเซลล์นั้นเข้าไปในกล่องโต้ตอบ
- พิมพ์ & (Ampersand) หลังการอ้างอิงเซลล์ D3 เพื่อเพิ่มการอ้างอิงเซลล์ที่สอง
- เลือกเซลล์ E3 เพื่อใส่การอ้างอิงเซลล์ที่สอง
- พิมพ์ , (Comma) หลังการอ้างอิงเซลล์ E3 เพื่อเสร็จสิ้นการใส่อาร์กิวเมนต์ Lookup_value ของฟังก์ชัน MATCH ในขั้นตอนสุดท้ายของบทเรียน จะใส่ Lookup_values ลงในเซลล์ D3 และ E3 ของแผ่นงาน
เสร็จสิ้นฟังก์ชัน MATCH แบบต่อยอด
ขั้นตอนนี้ครอบคลุมการเพิ่มอาร์กิวเมนต์ Lookup_array สำหรับฟังก์ชัน MATCH แบบต่อยอด Lookup_array คือช่วงเซลล์ที่ฟังก์ชัน MATCH ค้นหาเพื่อหา Lookup_value ที่เพิ่มไปในขั้นตอนก่อนหน้า
เนื่องจากระบุฟิลด์ค้นหา 2 ฟิลด์ในอาร์กิวเมนต์ Lookup_value จึงต้องทำเช่นเดียวกันกับ Lookup_array ฟังก์ชัน MATCH ค้นหาได้เพียงอาเรย์เดียวต่อเกณฑ์หนึ่ง เพื่อใส่อาเรย์หลายตัว ให้ใช้ Ampersand เพื่อต่อติดอาเรย์เข้าด้วยกัน
- วางเคอร์เซอร์ท้ายข้อมูลในกล่องข้อความ Row_num เคอร์เซอร์จะอยู่หลัง Comma ท้ายรายการปัจจุบัน
- เลือกไฮไลท์เซลล์ D6 ถึง D11 ในแผ่นงานเพื่อใส่ช่วง นี่คืออาเรย์แรกที่ฟังก์ชันค้นหา
- พิมพ์ & (Ampersand) หลังการอ้างอิงเซลล์ D6:D11 สัญลักษณ์นี้ทำให้ฟังก์ชันค้นหา 2 อาเรย์
- เลือกไฮไลท์เซลล์ E6 ถึง E11 ในแผ่นงานเพื่อใส่ช่วง นี่คืออาเรย์ที่สองที่ฟังก์ชันค้นหา
- พิมพ์ , (Comma) หลังการอ้างอิงเซลล์ E3 เพื่อเสร็จสิ้นการใส่อาร์กิวเมนต์ Lookup_array ของฟังก์ชัน MATCH ทิ้งกล่องโต้ตอบไว้เปิดสำหรับขั้นตอนต่อไป
เพิ่มอาร์กิวเมนต์ Match Type
อาร์กิวเมนต์ที่สามและสุดท้ายของ MATCH คือ Match_type อาร์กิวเมนต์นี้บอก Excel ว่าจะจับคู่ Lookup_value กับค่าใน Lookup_array อย่างไร ตัวเลือกมี 1, 0, หรือ -1
อาร์กิวเมนต์นี้เป็นตัวเลือก ถ้าละเว้น ฟังก์ชันจะใช้ค่าเริ่มต้นเป็น 1
- ถ้า Match_type = 1 หรือละเว้น: MATCH หาค่าสูงสุดที่น้อยกว่าหรือเท่ากับ Lookup_value ข้อมูล Lookup_array ต้องเรียงลำดับจากน้อยไปมาก (Ascending)
- ถ้า Match_type = 0: MATCH หาค่าแรกที่เท่ากับ Lookup_value ข้อมูล Lookup_array เรียงลำดับได้ทุกแบบ
- ถ้า Match_type = -1: MATCH หาค่าต่ำสุดที่มากกว่าหรือเท่ากับ Lookup_value ข้อมูล Lookup_array ต้องเรียงลำดับจากมากไปน้อย (Descending)
ทำขั้นตอนเหล่านี้หลัง Comma ที่ใส่ในขั้นตอนก่อนหน้าบนบรรทัด Row_num ในฟังก์ชัน INDEX:
- พิมพ์ 0 (ศูนย์) หลัง Comma ในกล่องข้อความ Row_num ตัวเลขนี้ทำให้ฟังก์ชันต่อยอดส่งคืนการจับคู่ที่ตรงกันเป๊ะกับเงื่อนไขที่ใส่ในเซลล์ D3 และ E3
- พิมพ์ ) (วงเล็บปิด) เพื่อปิดฟังก์ชัน MATCH ทิ้งกล่องโต้ตอบไว้เปิดสำหรับขั้นตอนต่อไป
เสร็จสิ้นฟังก์ชัน INDEX
ฟังก์ชัน MATCH เสร็จแล้ว ตอนนี้ย้ายไปที่กล่องข้อความ Column_num ของกล่องโต้ตอบและใส่อาร์กิวเมนต์สุดท้ายของฟังก์ชัน INDEX อาร์กิวเมนต์นี้บอก Excel ว่าหมายเลขคอลัมน์ในช่วง D6 ถึง F11 คืออะไร ช่วงนี้คือที่ที่ฟังก์ชันจะหาข้อมูลที่ส่งคืน ในกรณีนี้คือผู้จำหน่ายของ Titanium Widgets
- วางเคอร์เซอร์ในกล่องข้อความ Column_num
- พิมพ์ 3 (เลขสาม) ตัวเลขนี้บอกสูตรให้หาข้อมูลในคอลัมน์ที่ 3 ของช่วง D6 ถึง F11 ทิ้งกล่องโต้ตอบไว้เปิดสำหรับขั้นตอนต่อไป
สร้าง Array Formula
ก่อนปิดกล่องโต้ตอบ ให้แปลงฟังก์ชันต่อยอดเป็น Array Formula Array นี้ทำให้ฟังก์ชันค้นหาหลายเงื่อนไขในตารางข้อมูลได้ ในบทเรียนนี้จับคู่ 2 เงื่อนไข: Widgets จากคอลัมน์ 1 และ Titanium จากคอลัมน์ 2
เพื่อสร้าง Array Formula ใน Excel ให้กด CTRL, SHIFT, และ ENTER พร้อมกัน เมื่อกดแล้ว ฟังก์ชันจะถูกล้อมรอบด้วยวงเล็บปีกกา { } บ่งบอกว่าตอนนี้เป็น Array แล้ว
- เลือก OK เพื่อปิดกล่องโต้ตอบ (ใน Excel for Mac เลือก Done)
- เลือกเซลล์ F3 เพื่อดูสูตร แล้ววางเคอร์เซอร์ท้ายสูตรใน Formula Bar
- เพื่อแปลงสูตรเป็น Array ให้กด CTRL+SHIFT+ENTER
- ข้อผิดพลาด #N/A จะปรากฏในเซลล์ F3 นี่คือเซลล์ที่ใส่ฟังก์ชัน
- ข้อผิดพลาด #N/A ปรากฏเพราะเซลล์ D3 และ E3 ว่างเปล่า D3 และ E3 คือเซลล์ที่ฟังก์ชันมองหา Lookup_value หลังจากใส่ข้อมูลในเซลล์ทั้งสองนี้ ข้อผิดพลาดจะถูกแทนที่ด้วยข้อมูลจากฐานข้อมูล
เพิ่มเกณฑ์ค้นหา (Search Criteria)
ขั้นตอนสุดท้ายคือเพิ่มเงื่อนไขค้นหาลงในแผ่นงาน ขั้นตอนนี้จับคู่เงื่อนไข Widgets จากคอลัมน์ 1 และ Titanium จากคอลัมน์ 2
ถ้าสูตรพบการจับคู่ทั้งสองเงื่อนไขในคอลัมน์ที่เหมาะสมในฐานข้อมูล จะส่งคืนค่าจากคอลัมน์ที่สาม
- เลือกเซลล์ D3
- พิมพ์ Widgets
- เลือกเซลล์ E3
- พิมพ์ Titanium แล้วกด Enter
- ชื่อผู้จำหน่าย Widgets Inc. จะปรากฏในเซลล์ F3 นี่คือผู้จำหน่ายรายเดียวที่ขาย Titanium Widgets
- เลือกเซลล์ F3 สูตรจะปรากฏในแถบสูตรเหนือแผ่นงาน
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}
ในตัวอย่างนี้มีผู้จำหน่าย Titanium Widgets เพียงรายเดียว หากมีมากกว่าหนึ่งราย ผู้จำหน่ายที่แสดงขึ้นก่อนในฐานข้อมูลจะถูกส่งคืนโดยฟังก์ชัน
