Excel

แยกหลายรายการที่ตรงกันออกเป็นคอลัมน์แยกกัน

Extract Multiple Matches Into Separate Columns

สูตร Excel: แยกหลายรายการที่ตรงกันออกเป็นคอลัมน์แยกกันสรุป

หากต้องการแยกหลายรายการที่ตรงกันไปยังเซลล์ที่แยกจากกัน ในคอลัมน์ที่แยกจากกัน คุณสามารถใช้สูตรอาร์เรย์ตาม 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


^