วิธีสร้างสูตรการค้นหา Excel ด้วยเกณฑ์หลายเกณฑ์

ข้อควรรู้

• ขั้นแรก ให้สร้างฟังก์ชัน INDEX จากนั้นเริ่มฟังก์ชัน MATCH ที่ซ้อนกันโดยป้อนอาร์กิวเมนต์ Lookup_value
• ถัดไป เพิ่มอาร์กิวเมนต์ Lookup_array ตามด้วย Match_type อาร์กิวเมนต์ จากนั้นระบุช่วงของคอลัมน์
• จากนั้น เปลี่ยนฟังก์ชันที่ซ้อนกันเป็นสูตรอาร์เรย์โดยกด Ctrl +เปลี่ยน +ป้อน . สุดท้าย เพิ่มคำค้นหาลงในเวิร์กชีต

บทความนี้อธิบายวิธีสร้างสูตรการค้นหาที่ใช้เกณฑ์หลายเกณฑ์ใน Excel เพื่อค้นหาข้อมูลในฐานข้อมูลหรือตารางข้อมูลโดยใช้สูตรอาร์เรย์ สูตรอาร์เรย์เกี่ยวข้องกับการซ้อนฟังก์ชัน MATCH ภายในฟังก์ชัน INDEX ข้อมูลครอบคลุม Excel สำหรับ Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 และ Excel for Mac

ทำตามพร้อมกับบทช่วยสอน

หากต้องการทำตามขั้นตอนในบทช่วยสอนนี้ ให้ป้อนข้อมูลตัวอย่างลงในเซลล์ต่อไปนี้ ดังที่แสดงในภาพด้านล่าง แถวที่ 3 และ 4 เว้นว่างไว้เพื่อรองรับสูตรอาร์เรย์ที่สร้างขึ้นระหว่างบทช่วยสอนนี้ (โปรดทราบว่าบทช่วยสอนนี้ไม่มีการจัดรูปแบบที่เห็นในภาพ)

• ป้อนข้อมูลช่วงบนสุดลงในเซลล์ D1 ถึง F2
• ป้อนช่วงที่สองลงในเซลล์ D5 ถึง F11

สร้างฟังก์ชัน INDEX ใน Excel

ฟังก์ชัน INDEX เป็นหนึ่งในฟังก์ชันไม่กี่อย่างใน Excel ที่มีหลายรูปแบบ ฟังก์ชันนี้มีแบบฟอร์มอาร์เรย์และแบบฟอร์มอ้างอิง Array Form ส่งคืนข้อมูลจากฐานข้อมูลหรือตารางข้อมูล แบบฟอร์มอ้างอิงให้การอ้างอิงเซลล์หรือตำแหน่งของข้อมูลในตาราง

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

ทำตามขั้นตอนเหล่านี้เพื่อสร้างฟังก์ชัน INDEX:

1. เลือกเซลล์ F3 เพื่อให้เป็นเซลล์ที่ใช้งานอยู่ เซลล์นี้เป็นตำแหน่งที่จะป้อนฟังก์ชันที่ซ้อนกัน

2. ไปที่สูตร .

   

3. เลือก การค้นหาและอ้างอิง เพื่อเปิดรายการฟังก์ชั่นแบบเลื่อนลง

4. เลือก ดัชนี เพื่อเปิด เลือกอาร์กิวเมนต์ กล่องโต้ตอบ

5. เลือก array,row_num,column_num .

6. เลือก ตกลง เพื่อเปิด อาร์กิวเมนต์ของฟังก์ชัน กล่องโต้ตอบ ใน Excel for Mac ตัวสร้างสูตรจะเปิดขึ้น

7. วางเคอร์เซอร์ใน อาร์เรย์ กล่องข้อความ

8. ไฮไลต์เซลล์ D6 ผ่าน F11 ในเวิร์กชีตเพื่อป้อนช่วงลงในกล่องโต้ตอบ

   เปิดกล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชันทิ้งไว้ สูตรไม่หมด คุณจะต้องกรอกสูตรตามคำแนะนำด้านล่าง

   

เริ่มฟังก์ชัน Nested MATCH

เมื่อซ้อนฟังก์ชันหนึ่งในฟังก์ชันอื่น จะไม่สามารถเปิดฟังก์ชันที่สองหรือตัวสร้างสูตรของฟังก์ชันที่ซ้อนกันเพื่อป้อนอาร์กิวเมนต์ที่จำเป็น ต้องป้อนฟังก์ชันที่ซ้อนกันเป็นหนึ่งในอาร์กิวเมนต์ของฟังก์ชันแรก

เมื่อป้อนฟังก์ชันด้วยตนเอง อาร์กิวเมนต์ของฟังก์ชันจะถูกคั่นด้วยเครื่องหมายจุลภาค

ขั้นตอนแรกในการเข้าสู่ฟังก์ชัน MATCH ที่ซ้อนกันคือการป้อนอาร์กิวเมนต์ Lookup_value Lookup_value คือตำแหน่งหรือการอ้างอิงเซลล์สำหรับคำค้นหาที่จะจับคู่ในฐานข้อมูล

Lookup_value ยอมรับเกณฑ์หรือคำค้นหาเดียวเท่านั้น หากต้องการค้นหาหลายเกณฑ์ ให้ขยาย Lookup_value โดยเชื่อมหรือรวมการอ้างอิงเซลล์ตั้งแต่สองรายการขึ้นไปโดยใช้สัญลักษณ์และ (&)

1. ใน อาร์กิวเมนต์ของฟังก์ชัน กล่องโต้ตอบ วางเคอร์เซอร์ใน Row_num กล่องข้อความ

2. ป้อน MATCH( .

3. เลือกเซลล์ D3 เพื่อใส่การอ้างอิงเซลล์นั้นลงในกล่องโต้ตอบ

4. ป้อน & (เครื่องหมายและ) หลังการอ้างอิงเซลล์ D3 เพื่อเพิ่มการอ้างอิงเซลล์ที่สอง

5. เลือกเซลล์ E3 เพื่อป้อนการอ้างอิงเซลล์ที่สอง

6. ป้อน , (เครื่องหมายจุลภาค) หลังเซลล์อ้างอิง E3 เพื่อให้รายการของอาร์กิวเมนต์ Lookup_value ของฟังก์ชัน MATCH สมบูรณ์

   

   ในขั้นตอนสุดท้ายของบทช่วยสอน Lookup_values ​​จะถูกป้อนลงในเซลล์ D3 และ E3 ของเวิร์กชีต

ทำฟังก์ชัน Nested MATCH ให้สมบูรณ์

ขั้นตอนนี้ครอบคลุมการเพิ่มอาร์กิวเมนต์ Lookup_array สำหรับฟังก์ชัน MATCH ที่ซ้อนกัน Lookup_array คือช่วงของเซลล์ที่ฟังก์ชัน MATCH ค้นหาเพื่อค้นหาอาร์กิวเมนต์ Lookup_value ที่เพิ่มในขั้นตอนก่อนหน้าของบทช่วยสอน

เนื่องจากมีการระบุช่องค้นหาสองช่องในอาร์กิวเมนต์ Lookup_array จึงต้องทำเช่นเดียวกันสำหรับ Lookup_array ฟังก์ชัน MATCH จะค้นหาเพียงอาร์เรย์เดียวสำหรับแต่ละคำที่ระบุ ในการป้อนหลายอาร์เรย์ ให้ใช้เครื่องหมายและเพื่อเชื่อมอาร์เรย์เข้าด้วยกัน

1. วางเคอร์เซอร์ที่ส่วนท้ายของข้อมูลใน Row_num กล่องข้อความ. เคอร์เซอร์ปรากฏขึ้นหลังเครื่องหมายจุลภาคที่ส่วนท้ายของรายการปัจจุบัน

2. ไฮไลต์เซลล์ D6 ผ่าน D11 ในแผ่นงานเพื่อเข้าสู่ช่วง ช่วงนี้เป็นอาร์เรย์แรกที่ฟังก์ชันค้นหา

3. ป้อน & (เครื่องหมายและ) หลังการอ้างอิงเซลล์ D6:D11 . สัญลักษณ์นี้ทำให้ฟังก์ชันค้นหาสองอาร์เรย์

4. ไฮไลต์เซลล์ E6 ผ่าน E11 ในแผ่นงานเพื่อเข้าสู่ช่วง ช่วงนี้เป็นอาร์เรย์ที่สองที่ฟังก์ชันค้นหา

5. ป้อน , (เครื่องหมายจุลภาค) หลังการอ้างอิงเซลล์ E3 เพื่อป้อนอาร์กิวเมนต์ Lookup_array ของฟังก์ชัน MATCH ให้สมบูรณ์

   

6. เปิดกล่องโต้ตอบทิ้งไว้เพื่อไปยังขั้นตอนถัดไปในบทแนะนำ

เพิ่มอาร์กิวเมนต์ประเภท MATCH

อาร์กิวเมนต์ที่สามและสุดท้ายของ ฟังก์ชัน MATCH คือ Match_type ข้อโต้แย้ง. อาร์กิวเมนต์นี้จะบอก Excel ว่าจะจับคู่ Lookup_value กับค่าใน Lookup_array อย่างไร ตัวเลือกที่ใช้ได้คือ 1, 0 หรือ -1

อาร์กิวเมนต์นี้เป็นทางเลือก หากไม่ระบุ ฟังก์ชันจะใช้ค่าเริ่มต้นที่ 1

• หาก Match_type =1 หรือละไว้ MATCH จะค้นหาค่าที่ใหญ่ที่สุดที่น้อยกว่าหรือเท่ากับ Lookup_value ข้อมูล Lookup_array ต้องเรียงลำดับจากน้อยไปหามาก
• หาก Match_type =0 MATCH จะค้นหาค่าแรกที่เท่ากับ Lookup_value ข้อมูล Lookup_array สามารถจัดเรียงในลำดับใดก็ได้
• หาก Match_type =-1 MATCH จะค้นหาค่าที่น้อยที่สุดที่มากกว่าหรือเท่ากับ Lookup_value ต้องจัดเรียงข้อมูล Lookup_array จากมากไปหาน้อย

ป้อนขั้นตอนเหล่านี้หลังจากป้อนเครื่องหมายจุลภาคในขั้นตอนก่อนหน้าในบรรทัด Row_num ในฟังก์ชัน INDEX:

1. ป้อน 0 (ศูนย์) หลังเครื่องหมายจุลภาคใน Row_num กล่องข้อความ. ตัวเลขนี้ทำให้ฟังก์ชันที่ซ้อนกันส่งกลับค่าที่ตรงกันทุกประการกับคำที่ป้อนในเซลล์ D3 และ E3

2. ใส่ ) (วงเล็บปิด) เพื่อให้ฟังก์ชัน MATCH สมบูรณ์

   

3. เปิดกล่องโต้ตอบทิ้งไว้เพื่อไปยังขั้นตอนถัดไปในบทแนะนำ

สิ้นสุดฟังก์ชัน INDEX

ฟังก์ชัน MATCH เสร็จสิ้น ได้เวลาย้ายไปยังกล่องข้อความ Column_num ของกล่องโต้ตอบ และป้อนอาร์กิวเมนต์สุดท้ายสำหรับฟังก์ชัน INDEX อาร์กิวเมนต์นี้บอก Excel ว่าหมายเลขคอลัมน์อยู่ในช่วง D6 ถึง F11 ช่วงนี้เป็นช่วงที่ค้นหาข้อมูลที่ส่งคืนโดยฟังก์ชัน ในกรณีนี้คือซัพพลายเออร์สำหรับวิดเจ็ตไทเทเนียม

1. วางเคอร์เซอร์ใน Column_num กล่องข้อความ

2. ป้อน 3 (หมายเลขสาม). ตัวเลขนี้บอกให้สูตรค้นหาข้อมูลในคอลัมน์ที่สามของช่วง D6 ถึง F11

   

3. เปิดกล่องโต้ตอบทิ้งไว้เพื่อไปยังขั้นตอนถัดไปในบทแนะนำ

สร้างสูตรอาร์เรย์

ก่อนปิดกล่องโต้ตอบ ให้เปลี่ยนฟังก์ชันที่ซ้อนกันเป็นสูตรอาร์เรย์ อาร์เรย์นี้อนุญาตให้ฟังก์ชันค้นหาคำหลายคำในตารางข้อมูล ในบทช่วยสอนนี้ มีการจับคู่คำศัพท์สองคำ:วิดเจ็ตจากคอลัมน์ 1 และ Titanium จากคอลัมน์ 2

ในการสร้างสูตรอาร์เรย์ใน Excel ให้กด CTRL , SHIFT และ ENTER คีย์พร้อมกัน เมื่อกดแล้ว ฟังก์ชันจะถูกล้อมรอบด้วยวงเล็บปีกกา แสดงว่าตอนนี้ฟังก์ชันนี้เป็นอาร์เรย์

1. เลือก ตกลง เพื่อปิดกล่องโต้ตอบ ใน Excel for Mac ให้เลือก เสร็จสิ้น .

2. เลือกเซลล์ F3 เพื่อดูสูตร จากนั้นวางเคอร์เซอร์ที่ส่วนท้ายของสูตรในแถบสูตร

3. ในการแปลงสูตรเป็นอาร์เรย์ ให้กด CTRL +SHIFT +ENTER .

4. #N/A ข้อผิดพลาดปรากฏในเซลล์ F3 นี่คือเซลล์ที่ป้อนฟังก์ชัน

5. ข้อผิดพลาด #N/A ปรากฏในเซลล์ F3 เนื่องจากเซลล์ D3 และ E3 ว่างเปล่า D3 และ E3 คือเซลล์ที่ฟังก์ชันค้นหา Lookup_value หลังจากเพิ่มข้อมูลลงในสองเซลล์แล้ว ข้อผิดพลาดจะถูกแทนที่ด้วยข้อมูลจากฐานข้อมูล

   

เพิ่มเกณฑ์การค้นหา

ขั้นตอนสุดท้ายคือการเพิ่มคำค้นหาลงในเวิร์กชีต ขั้นตอนนี้ตรงกับเงื่อนไข Widgets จากคอลัมน์ 1 และ ไทเทเนียมจากเสา 2

ถ้าสูตรพบคำที่ตรงกันสำหรับทั้งสองคำในคอลัมน์ที่เหมาะสมในฐานข้อมูล สูตรจะคืนค่าจากคอลัมน์ที่สาม

1. เลือกเซลล์ D3 .

2. ป้อน วิดเจ็ต .

3. เลือกเซลล์ E3 .

4. พิมพ์ ไทเทเนี่ยม และกด Enter .

5. ชื่อซัพพลายเออร์ Widgets Inc. ปรากฏในเซลล์ F3 นี่คือซัพพลายเออร์รายเดียวในรายการที่ขาย Titanium Widget

6. เลือกเซลล์ F3 . ฟังก์ชันจะปรากฏในแถบสูตรเหนือเวิร์กชีต

   {=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}
   
   

   ในตัวอย่างนี้ มีซัพพลายเออร์เพียงรายเดียวสำหรับวิดเจ็ตไทเทเนียม หากมีซัพพลายเออร์มากกว่าหนึ่งราย ฟังก์ชันจะส่งคืนซัพพลายเออร์ที่มีรายชื่อเป็นอันดับแรกในฐานข้อมูล