หากต้องการแยกหลายรายการที่ตรงกันไปยังเซลล์ที่แยกจากกัน ในคอลัมน์ที่แยกจากกัน คุณสามารถใช้สูตรอาร์เรย์ตาม INDEX และ SMALL ในตัวอย่างที่แสดง สูตรใน F5 คือ:
{= IFERROR ( INDEX (names, SMALL ( IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1), COLUMNS ($E:E5))),'')}
นี่คือสูตรอาร์เรย์และต้องป้อนด้วย Control + Shift + Enter
วิธีย้ายแผนภูมิใน excel
หลังจากที่คุณป้อนสูตรในเซลล์แรกแล้ว ให้ลากลงและข้ามเพื่อเติมลงในเซลล์อื่นๆ
คำอธิบาย
หมายเหตุ: สูตรนี้ใช้ two ช่วงที่มีชื่อ : 'ชื่อ' หมายถึง C5:C11 และ 'กลุ่ม' หมายถึง B5:B11 ชื่อเหล่านี้ถูกกำหนดไว้ในภาพหน้าจอด้านบนเช่นกัน
สาระสำคัญของสูตรนี้คือ: เรากำลังใช้ฟังก์ชัน SMALL เพื่อสร้างหมายเลขแถวที่สอดคล้องกับ 'การจับคู่ที่ n' เมื่อเราได้หมายเลขแถวแล้ว เราก็ส่งต่อไปยังฟังก์ชันฟังก์ชัน INDEX ซึ่งจะคืนค่าที่แถวนั้น
เคล็ดลับคือ SMALL ทำงานกับอาร์เรย์ที่สร้างโดย IF แบบไดนามิกในบิตนี้:
IF (groups=$E5, ROW (names)- MIN ( ROW (names))+1)
ตัวอย่างนี้ทดสอบ 'กลุ่ม' ช่วงที่มีชื่อสำหรับค่าใน E5 หากพบ จะส่งคืนหมายเลขแถวจากอาร์เรย์ของหมายเลขแถวสัมพัทธ์ที่สร้างด้วย:
ROW (names)- MIN ( ROW (names))+1
ผลลัพธ์สุดท้ายคืออาร์เรย์ที่มีตัวเลขที่มีการจับคู่ และ FALSE โดยที่ไม่:
วิธีใช้ if else ใน excel
{1FALSEFALSEFALSEFALSE6FALSE}
อาร์เรย์นี้เข้าสู่ SMALL ค่า k สำหรับ SMALL (n) มาจาก an ขยายช่วง :
COLUMNS ($E:E5)
เมื่อคัดลอกข้ามตารางผลลัพธ์ ช่วงจะขยาย ทำให้ k (nth) เพิ่มขึ้น ฟังก์ชัน SMALL ส่งคืนหมายเลขแถวที่ตรงกันแต่ละแถว ซึ่งระบุให้กับฟังก์ชัน INDEX เป็น row_num โดยมี 'ชื่อ' ช่วงที่มีชื่อเป็นอาร์เรย์
การจัดการข้อผิดพลาด
เมื่อ COLUMNS ส่งคืนค่าสำหรับ k ที่ไม่มีอยู่ SMALL จะแสดงข้อผิดพลาด #NUM สิ่งนี้จะเกิดขึ้นหลังจากการแข่งขันทั้งหมดเกิดขึ้น เพื่อระงับข้อผิดพลาด เรารวมสูตรในฟังก์ชัน IFERROR เพื่อตรวจจับข้อผิดพลาดและส่งคืน an สตริงว่าง ('').
ผู้เขียน Dave Bruns