Excel

INDEX และ MATCH ที่มีหลายเกณฑ์

Index Match With Multiple Criteria

สูตร Excel: INDEX และ MATCH พร้อมหลายเกณฑ์สูตรทั่วไป |_+_| สรุป

ในการค้นหาค่าด้วย 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 และ 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


^