ในการค้นหาค่าด้วย INDEX และ MATCH โดยใช้เกณฑ์หลายเกณฑ์ คุณสามารถใช้สูตรอาร์เรย์ได้ ในตัวอย่างที่แสดง สูตรใน H8 คือ:
{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}
หมายเหตุ: นี่คือ an สูตรอาร์เรย์ และต้องป้อนด้วย control + shift + enter ยกเว้นใน Excel 365 .
คำอธิบายนี่เป็นสูตรขั้นสูง ดูข้อมูลเบื้องต้นได้ที่ วิธีใช้ INDEX และ MATCH .
excel ถ้ามีข้อความแล้วรวม
โดยปกติ สูตร INDEX MATCH จะได้รับการกำหนดค่าด้วยชุด MATCH เพื่อดูช่วงหนึ่งคอลัมน์และให้การจับคู่ตามเกณฑ์ที่กำหนด โดยไม่ต้องเชื่อมค่าใน a คอลัมน์ผู้ช่วย หรือในสูตรเอง ไม่มีวิธีระบุเกณฑ์มากกว่าหนึ่งเกณฑ์
สูตรนี้ทำงานรอบข้อจำกัดนี้โดยใช้ ตรรกะบูลีน เพื่อสร้าง อาร์เรย์ ของหนึ่งและศูนย์เพื่อแสดงแถวที่ตรงกับเกณฑ์ทั้ง 3 ข้อ จากนั้นใช้ MATCH เพื่อจับคู่ 1 รายการแรกที่พบ อาร์เรย์ชั่วคราวของหนึ่งและศูนย์ถูกสร้างขึ้นด้วยข้อมูลโค้ดนี้:
{= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}
ที่นี่เราเปรียบเทียบสินค้าใน H5 กับสินค้าทั้งหมด ขนาดใน H6 กับทุกขนาด และสีใน H7 กับทุกสี ผลลัพธ์เริ่มต้นคือสามอาร์เรย์ของผลลัพธ์ TRUE/FALSE ดังนี้:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
เคล็ดลับ: ใช้ F9 เพื่อดูผลลัพธ์เหล่านี้ . เพียงเลือกนิพจน์ในแถบสูตร แล้วกด F9
การดำเนินการทางคณิตศาสตร์ (การคูณ) แปลงค่า TRUE FALSE เป็น 1s และ 0s:
ทำรายการแบบเลื่อนลงใน excel
{TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}
หลังจากการคูณ เรามีอาร์เรย์เดียวดังนี้:
{1110001}*{0010010}*{1010001}
ซึ่งป้อนเข้าสู่ฟังก์ชัน MATCH เป็นอาร์เรย์การค้นหา โดยมีค่าการค้นหาเท่ากับ 1:
{0010000}
ณ จุดนี้ สูตรนี้เป็นสูตร INDEX MATCH มาตรฐาน ฟังก์ชัน MATCH จะคืนค่า 3 เป็น INDEX:
MATCH (1,{0010000})
และ INDEX ส่งกลับผลลัพธ์สุดท้ายที่ .00
การสร้างภาพอาร์เรย์
อาร์เรย์ที่อธิบายข้างต้นอาจมองเห็นได้ยาก ภาพด้านล่างแสดงแนวคิดพื้นฐาน คอลัมน์ B, C และ D สอดคล้องกับข้อมูลในตัวอย่าง คอลัมน์ F ถูกสร้างขึ้นโดยการคูณสามคอลัมน์เข้าด้วยกัน เป็นอาร์เรย์ที่ส่งไปยัง MATCH
เวอร์ชันที่ไม่ใช่อาร์เรย์
คุณสามารถเพิ่ม INDEX อื่นในสูตรนี้ได้ โดยไม่จำเป็นต้องป้อนเป็นสูตรอาร์เรย์ด้วย control + shift + enter:
= INDEX (E5:E11,3)
ฟังก์ชัน INDEX สามารถจัดการอาร์เรย์โดยกำเนิด ดังนั้น INDEX ที่สองจึงถูกเพิ่มเพื่อ 'จับ' อาร์เรย์ที่สร้างด้วยการดำเนินการตรรกะบูลีนเท่านั้น และส่งคืนอาร์เรย์เดียวกันอีกครั้งไปที่ MATCH เมื่อต้องการทำเช่นนี้ INDEX ได้รับการกำหนดค่าด้วยศูนย์แถวและหนึ่งคอลัมน์ เคล็ดลับแถวศูนย์ทำให้ INDEX ส่งคืนคอลัมน์ 1 จากอาร์เรย์ (ซึ่งมีอยู่แล้วหนึ่งคอลัมน์อยู่แล้ว)
ทำไมคุณถึงต้องการเวอร์ชันที่ไม่ใช่อาร์เรย์ บางครั้ง ผู้คนลืมป้อนสูตรอาร์เรย์ด้วย control + shift + enter และสูตรส่งกลับผลลัพธ์ที่ไม่ถูกต้อง ดังนั้น สูตรที่ไม่ใช่อาร์เรย์จึง 'กันกระสุน' ได้มากกว่า อย่างไรก็ตาม การประนีประนอมเป็นสูตรที่ซับซ้อนกว่า
หมายเหตุ: ใน Excel 365 ไม่จำเป็นต้องป้อนสูตรอาร์เรย์ด้วยวิธีพิเศษ
excel แสดงชื่อวันในสัปดาห์เอกสารแนบ INDEX และ MATCH ที่มีหลายเกณฑ์.xlsx ผู้เขียน Dave Bruns