ต้องรู้
- ขั้นแรก สร้างฟังก์ชัน INDEX จากนั้นเริ่มฟังก์ชัน MATCH ที่ซ้อนกันโดยป้อนอาร์กิวเมนต์ Lookup_value
- ถัดไป เพิ่มอาร์กิวเมนต์ Lookup_array ตามด้วยอาร์กิวเมนต์ Match_type จากนั้นระบุช่วงของคอลัมน์
- จากนั้น เปลี่ยนฟังก์ชันที่ซ้อนกันเป็นสูตรอาร์เรย์โดยกด Ctrl+ Shift+Enter. สุดท้าย เพิ่มคำค้นหาลงในเวิร์กชีต
บทความนี้อธิบายวิธีสร้างสูตรการค้นหาที่ใช้หลายเกณฑ์ใน Excel เพื่อค้นหาข้อมูลในฐานข้อมูลหรือตารางข้อมูลโดยใช้สูตรอาร์เรย์สูตรอาร์เรย์เกี่ยวข้องกับการซ้อนฟังก์ชัน MATCH ภายในฟังก์ชัน INDEX ข้อมูลครอบคลุม Excel สำหรับ Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 และ Excel สำหรับ Mac
ติดตามการสอน
หากต้องการทำตามขั้นตอนในบทช่วยสอนนี้ ให้ป้อนข้อมูลตัวอย่างลงในเซลล์ต่อไปนี้ ดังที่แสดงในภาพด้านล่าง แถวที่ 3 และ 4 เว้นว่างไว้เพื่อรองรับสูตรอาร์เรย์ที่สร้างขึ้นระหว่างบทช่วยสอนนี้ (โปรดทราบว่าบทช่วยสอนนี้ไม่มีการจัดรูปแบบที่เห็นในภาพ)
- ป้อนช่วงบนสุดของข้อมูลลงในเซลล์ D1 ถึง F2
- ป้อนช่วงที่สองลงในเซลล์ D5 ถึง F11
สร้างฟังก์ชัน INDEX ใน Excel
ฟังก์ชัน INDEX เป็นหนึ่งในไม่กี่ฟังก์ชันใน Excel ที่มีหลายรูปแบบ ฟังก์ชันนี้มีแบบฟอร์มอาร์เรย์และแบบฟอร์มอ้างอิง Array Form ส่งคืนข้อมูลจากฐานข้อมูลหรือตารางข้อมูลแบบฟอร์มอ้างอิงให้การอ้างอิงเซลล์หรือตำแหน่งของข้อมูลในตาราง
ในบทช่วยสอนนี้ แบบฟอร์มอาร์เรย์จะใช้เพื่อค้นหาชื่อซัพพลายเออร์สำหรับวิดเจ็ตไทเทเนียม แทนที่จะใช้การอ้างอิงเซลล์ไปยังซัพพลายเออร์รายนี้ในฐานข้อมูล
ทำตามขั้นตอนเหล่านี้เพื่อสร้างฟังก์ชัน INDEX:
-
เลือกเซลล์ F3 เพื่อทำให้เป็นเซลล์ที่ทำงานอยู่ เซลล์นี้เป็นตำแหน่งที่จะป้อนฟังก์ชันที่ซ้อนกัน
-
ไปที่ สูตร.
Image - เลือก ค้นหาและอ้างอิง เพื่อเปิดรายการฟังก์ชั่นแบบเลื่อนลง
- เลือก INDEX เพื่อเปิดกล่องโต้ตอบ เลือกอาร์กิวเมนต์ กล่องโต้ตอบ
- เลือก array, row_num, column_num.
- เลือก OK เพื่อเปิดกล่องโต้ตอบ อาร์กิวเมนต์ของฟังก์ชัน ใน Excel สำหรับ Mac ตัวสร้างสูตรจะเปิดขึ้น
- วางเคอร์เซอร์ในกล่องข้อความ Array
-
ไฮไลต์เซลล์ D6 ถึง F11 ในเวิร์กชีตเพื่อป้อนช่วงลงในกล่องโต้ตอบ
เปิดกล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชันทิ้งไว้ สูตรไม่หมด คุณจะต้องกรอกสูตรตามคำแนะนำด้านล่าง
Image
เริ่มฟังก์ชัน Nested MATCH
เมื่อซ้อนฟังก์ชันหนึ่งในฟังก์ชันอื่น จะไม่สามารถเปิดตัวสร้างสูตรของฟังก์ชันที่สองหรือซ้อนกันเพื่อป้อนอาร์กิวเมนต์ที่จำเป็น ต้องป้อนฟังก์ชันที่ซ้อนกันเป็นหนึ่งในอาร์กิวเมนต์ของฟังก์ชันแรก
เมื่อป้อนฟังก์ชันด้วยตนเอง อาร์กิวเมนต์ของฟังก์ชันจะถูกคั่นด้วยเครื่องหมายจุลภาค
ขั้นตอนแรกในการเข้าสู่ฟังก์ชัน MATCH ที่ซ้อนกันคือการป้อนอาร์กิวเมนต์ Lookup_value Lookup_value คือตำแหน่งหรือการอ้างอิงเซลล์สำหรับคำค้นหาที่จะจับคู่ในฐานข้อมูล
Lookup_value ยอมรับเกณฑ์หรือคำค้นหาเพียงคำเดียวเท่านั้น หากต้องการค้นหาหลายเกณฑ์ ให้ขยาย Lookup_value โดยการต่อหรือรวมการอ้างอิงเซลล์ตั้งแต่สองรายการขึ้นไปโดยใช้สัญลักษณ์และ (&)
- ในกล่องโต้ตอบ อาร์กิวเมนต์ของฟังก์ชัน วางเคอร์เซอร์ใน Row_num กล่องข้อความ
- ป้อน MATCH(.
- เลือกเซลล์ D3 เพื่อป้อนการอ้างอิงเซลล์นั้นลงในกล่องโต้ตอบ
- Enter & (เครื่องหมาย) หลังจากการอ้างอิงเซลล์ D3 เพื่อเพิ่มการอ้างอิงเซลล์ที่สอง
- เลือกเซลล์ E3 เพื่อเข้าสู่การอ้างอิงเซลล์ที่สอง
-
Enter , (เครื่องหมายจุลภาค) หลังการอ้างอิงเซลล์ E3 เพื่อทำให้รายการของอาร์กิวเมนต์ Lookup_value ของฟังก์ชัน MATCH สมบูรณ์
Image ในขั้นตอนสุดท้ายของบทช่วยสอนนี้ Lookup_values จะถูกป้อนลงในเซลล์ D3 และ E3 ของเวิร์กชีต
ทำฟังก์ชัน Nested MATCH ให้สมบูรณ์
ขั้นตอนนี้ครอบคลุมการเพิ่มอาร์กิวเมนต์ Lookup_array สำหรับฟังก์ชัน MATCH ที่ซ้อนกัน Lookup_array คือช่วงของเซลล์ที่ฟังก์ชัน MATCH ค้นหาเพื่อค้นหาอาร์กิวเมนต์ Lookup_value ที่เพิ่มในขั้นตอนก่อนหน้าของบทช่วยสอน
เนื่องจากมีการระบุช่องค้นหาสองช่องในอาร์กิวเมนต์ Lookup_array จึงต้องทำแบบเดียวกันสำหรับ Lookup_array ฟังก์ชัน MATCH จะค้นหาเพียงอาร์เรย์เดียวสำหรับแต่ละคำที่ระบุ ในการป้อนหลายอาร์เรย์ ให้ใช้เครื่องหมายและเพื่อเชื่อมอาร์เรย์เข้าด้วยกัน
- วางเคอร์เซอร์ที่ท้ายข้อมูลในกล่องข้อความ Row_num เคอร์เซอร์ปรากฏขึ้นหลังเครื่องหมายจุลภาคที่ส่วนท้ายของรายการปัจจุบัน
- ไฮไลต์เซลล์ D6 ถึง D11 ในเวิร์กชีตเพื่อเข้าสู่ช่วง ช่วงนี้เป็นอาร์เรย์แรกที่ฟังก์ชันค้นหา
-
Enter & (เครื่องหมายและ) หลังจากการอ้างอิงเซลล์ D6:D11 สัญลักษณ์นี้ทำให้ฟังก์ชันค้นหาสองอาร์เรย์
- ไฮไลต์เซลล์ E6 ถึง E11 ในเวิร์กชีตเพื่อเข้าสู่ช่วง ช่วงนี้เป็นอาร์เรย์ที่สองที่ฟังก์ชันค้นหา
-
Enter , (เครื่องหมายจุลภาค) หลังการอ้างอิงเซลล์ E3 เพื่อป้อนอาร์กิวเมนต์ Lookup_array ของฟังก์ชัน MATCH
Image - เปิดกล่องโต้ตอบทิ้งไว้สำหรับขั้นตอนต่อไปในบทช่วยสอน
เพิ่มอาร์กิวเมนต์ประเภท 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:
- Enter 0 (ศูนย์) หลังเครื่องหมายจุลภาคในกล่องข้อความ Row_num ตัวเลขนี้ทำให้ฟังก์ชันที่ซ้อนกันส่งกลับค่าที่ตรงกันทุกประการกับเงื่อนไขที่ป้อนในเซลล์ D3 และ E3
-
Enter ) (วงเล็บปิด) เพื่อให้ฟังก์ชัน MATCH สมบูรณ์
Image - เปิดกล่องโต้ตอบทิ้งไว้สำหรับขั้นตอนต่อไปในบทช่วยสอน
จบฟังก์ชัน INDEX
ฟังก์ชัน MATCH เสร็จแล้ว ได้เวลาย้ายไปยังกล่องข้อความ Column_num ของกล่องโต้ตอบ และป้อนอาร์กิวเมนต์สุดท้ายสำหรับฟังก์ชัน INDEX อาร์กิวเมนต์นี้บอก Excel ว่าหมายเลขคอลัมน์อยู่ในช่วง D6 ถึง F11 ช่วงนี้เป็นช่วงที่ค้นหาข้อมูลที่ส่งคืนโดยฟังก์ชันในกรณีนี้ ซัพพลายเออร์สำหรับวิดเจ็ตไทเทเนียม
- วางเคอร์เซอร์ใน Column_num กล่องข้อความ
-
Enter 3 (หมายเลขสาม). ตัวเลขนี้บอกให้สูตรค้นหาข้อมูลในคอลัมน์ที่สามของช่วง D6 ถึง F11
Image - เปิดกล่องโต้ตอบทิ้งไว้สำหรับขั้นตอนต่อไปในบทช่วยสอน
สร้างสูตรอาร์เรย์
ก่อนปิดกล่องโต้ตอบ ให้เปลี่ยนฟังก์ชันที่ซ้อนกันเป็นสูตรอาร์เรย์ อาร์เรย์นี้อนุญาตให้ฟังก์ชันค้นหาคำหลายคำในตารางข้อมูล ในบทช่วยสอนนี้ มีการจับคู่คำศัพท์สองคำ: วิดเจ็ตจากคอลัมน์ 1 และ Titanium จากคอลัมน์ 2
ในการสร้างสูตรอาร์เรย์ใน Excel ให้กด CTRL, SHIFT และ ENTERคีย์พร้อมกัน เมื่อกดแล้ว ฟังก์ชันจะถูกล้อมรอบด้วยวงเล็บปีกกา แสดงว่าฟังก์ชันนี้เป็นอาร์เรย์
- เลือก OK เพื่อปิดกล่องโต้ตอบ ใน Excel สำหรับ Mac ให้เลือก Done.
- เลือกเซลล์ F3 เพื่อดูสูตร จากนั้นวางเคอร์เซอร์ที่ท้ายสูตรในแถบสูตร
- ในการแปลงสูตรเป็นอาร์เรย์ ให้กด CTRL+ SHIFT+ ENTER
- A ข้อผิดพลาด N/A ปรากฏในเซลล์ F3 นี่คือเซลล์ที่ป้อนฟังก์ชัน
-
ข้อผิดพลาด N/A ปรากฏในเซลล์ F3 เนื่องจากเซลล์ D3 และ E3 ว่างเปล่า D3 และ E3 เป็นเซลล์ที่ฟังก์ชันค้นหา Lookup_value หลังจากเพิ่มข้อมูลลงในสองเซลล์แล้ว ข้อผิดพลาดจะถูกแทนที่ด้วยข้อมูลจากฐานข้อมูล
Image
เพิ่มเกณฑ์การค้นหา
ขั้นตอนสุดท้ายคือการเพิ่มคำค้นหาลงในเวิร์กชีต ขั้นตอนนี้ตรงกับเงื่อนไข Widgets จากคอลัมน์ 1 และ Titanium จากคอลัมน์ 2
หากสูตรพบคำที่ตรงกันสำหรับทั้งสองคำในคอลัมน์ที่เหมาะสมในฐานข้อมูล ระบบจะส่งกลับค่าจากคอลัมน์ที่สาม
- เลือกเซลล์ D3.
- ป้อน วิดเจ็ต.
- เลือกเซลล์ E3.
- ประเภท ไทเทเนี่ยม แล้วกด Enter.
- ชื่อซัพพลายเออร์ Widgets Inc. ปรากฏในเซลล์ F3 นี่คือซัพพลายเออร์เพียงรายเดียวที่จำหน่าย Titanium Widgets
-
เลือกเซลล์ F3 ฟังก์ชันจะปรากฏในแถบสูตรเหนือเวิร์กชีต
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
ในตัวอย่างนี้ มีซัพพลายเออร์เพียงรายเดียวสำหรับวิดเจ็ตไทเทเนียม หากมีซัพพลายเออร์มากกว่าหนึ่งราย ฟังก์ชันจะส่งคืนซัพพลายเออร์ที่มีรายชื่อเป็นอันดับแรกในฐานข้อมูล
Image
