ดึงข้อมูลที่มีค่าตาม range ที่กำหนด

[prasitkeng]

สืบเนื่องจาก Excel Live by Santipong Nasui No. 20: Array (https://www.youtube.com/watch?v=09Nt7CZRidw)

ถ้าผมต้องการดึง row ที่มีชื่อตามที่ต้องการ แต่มากกว่า 1 ชื่อ (คือไม่ใช่แค่ Nancy อย่างเดียว แต่เป็น Nancy, Magaret, ...) จะสามารถทำได้อย่างไรบ้างครับ

ตอนนี้ที่ผมทำได้คือทำได้แค่ 2 ชื่อ ตามสูตรข้างล่าง โดยผมให้ช่องที่ใส่ชื่อที่ต้องการหาอยู่ที่ B1 และ B2 และเลื่อน row ลงไป 1 แถว (header อยู่ที่แถวที่ 3 และ ให้ข้อมูลที่ต้องการเริ่มแสดงที่ row ที่ 4 แทนครับ)

{=INDEX(Invoice!G$2:G$31,SMALL(IF(ISNUMBER(SEARCH($B$1,Invoice!$C$2:$C$31))+
ISNUMBER(SEARCH($B$2,Invoice!$C$2:$C$31))-ISNUMBER(SEARCH($B$1,Invoice!$C$2:$C$31))*ISNUMBER(SEARCH($B$2,Invoice!$C$2:$C$31)),ROW(Invoice!$C$2:$C$31)-ROW(Invoice!$C$2)+1),ROWS($A$1:$A10)))}

โดยผมใช้ or (ใช้ algebraic expression ของ boolean มาช่วย เนื่องจาก or return แค่ค่าเดียว ไม่ return เป็น array: A or B = A + B - A*B) มาช่วยใน argument logical_test ของ if (ส่วนที่ขีดเส้นใต้) ซึ่งยาวมากๆและถ้าต้องการหาตั้งแต่ 3 ชื่อขึ้นไปคงเขียนสูตรยาวมากๆกว่านี้แน่ๆ อยากทราบว่าพอจะมีวิธีอื่นหรือฟังก์ชันอื่นๆมาช่วยได้ไหมครับ

[snasui]

แนบไฟล์ตัวอย่างพร้อมแสดงตัวอย่างคำตอบที่ต้องการมาด้วยจะได้สะดวกในการตอบครับ

กรณีเรามีการค้นหาข้อมูลหลายค่าพร้อมกันในคราวเดียวสามารถใช้ Mmult เข้ามาช่วยได้ ยกตัวอยา่งข้อมูลอยู่ที่ A2:A6 และเราต้องการค้นหา 2 ค่าพร้อมกันคือ ff และ bb

ที่เซลล์ C2 คีย์

=IFERROR(INDEX($A$2:$A$6,SMALL(IF(MMULT({1,1},TRANSPOSE(--ISNUMBER(SEARCH({"ff","bb"},$A$2:$A$6)))),TRANSPOSE(ROW($A$2:$A$6)-ROW($A$2)+1)),ROWS(C$2:C2))),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง

ค่าคงที่ Array {1,1} แทน การค้นหาแบบ 2 ค่า หากเป็น 3 ค่าให้คีย์เป็น {1,1,1} เป็นต้น

[prasitkeng]

แนบไฟล์ตัวอย่างพร้อมแสดงตัวอย่างคำตอบที่ต้องการมาด้วยจะได้สะดวกในการตอบครับ

แนบไฟล์นะครับ

[prasitkeng]

อันนี้เป็นรูปหลังจากที่ผมแกะสูตรนะครับ เอามาแบ่งปัน
https://www.mx7.com/view2/AbyggRwPlOnlmIRL

[prasitkeng]

หลังจากผมแกะสูตรของอาจารย์แล้ว ผมมีคำถามเพิ่มน่ะครับ

ผมมีความรู้สึกว่า เราอาจจะไม่จำเป็นต้องใช้ฟังก์ชัน TRANSPOSE ก็ได้ เลยลองมา modify สูตรตรงช่วง

SEARCH({"ff","bb"},$A$2:$A$6)

ให้เป็น

SEARCH({"ff";"bb"},$A$2:$A$6)

แทน

ซึ่งผมคาดว่ามันควรจะ return มาเป็น matrix ขนาด 2x5 มิติ (2 แถว 5 หลัก) เลย
แต่ผมไปลองเล่นดู (ตามรูปด้านล่าง) ปรากฏว่ามันไม่ได้ return ตามที่ต้องการน่ะครับ มันเป็นเพราะอะไรกันแน่



[ถ้ากรณีที่แก้ปัญหาข้างบนได้แล้ว]
ผมคิดว่าเราสามารถแก้สูตรตรง

MMULT({1,1},TRANSPOSE(--ISNUMBER(SEARCH({"ff","bb"},$A$2:$A$6))))

ให้กลายเป็น

MMULT(--ISNUMBER(SEARCH({"ff";"bb"},$A$2:$A$6)),{1,1})

แทน

และลดฟังก์ชัน TRANSPOSE ที่ return ของฟังก์ชัน IF

TRANSPOSE(ROW($A$2:$A$6)-ROW($A$2)+1)

ไปได้น่ะครับ

[snasui]

อ่านที่ผมอธิบายที่นี่ครับ http://www.snasui.com/viewtopic.php?t=8972#p56064 ต้องเข้าใจแต่ละส่วนประกอบของ Mmult ด้วยว่ากำหนดไว้อย่างไร การสลับลำดับส่วนประกอบย่อมเกิดปัญหาได้หากฟังก์ชั่นนั้นไม่อนุญาตให้สลับส่วนประกอบกันได้ครับ

[prasitkeng]

ผมเข้าใจนะครับว่า เมทริกซ์ไม่มีสมบัติสลับที่การคูณ

แต่คำถามหลักของผมคือ ทำไมสูตร

SEARCH({"ff";"bb"},$A$2:$A$6)

มันถึงไม่คืนค่ามาเป็น matrix ขนาด 2x5 ครับ

[snasui]

ข้อมูลเป็น Array แนวเดียวกันคือแนวตั้งทั้งคู่ครับ

หากเปลี่ยน Array ตัวหน้าเป็นแนวนอนจะได้ลักษณะการค้นหาแบบ 2*5 ครับ

[prasitkeng]

อาจารย์ครับ แล้วพอจะมีวิธีสร้าง array {1,1,...} ให้มัน dynamic ไหมครับ จะได้ไม่ต้องมาพิมพ์เอง

[puriwutpokin]

ลองปรับเป็น array {1,1,...} เป็น TRANSPOSE(COUNTIF($A$1:$A$13,$A$1:$A$13))
สีแดงคือช่วง Dynamic

[eyepop99]

อันนี้เป็นรูปหลังจากที่ผมแกะสูตรนะครับ เอามาแบ่งปัน
https://www.mx7.com/view2/AbyggRwPlOnlmIRL

ละเอียดมากขอบคุณครับ

[snasui]

อาจารย์ครับ แล้วพอจะมีวิธีสร้าง array {1,1,...} ให้มัน dynamic ไหมครับ จะได้ไม่ต้องมาพิมพ์เอง

สามารถใช้ Column(Range)^0 ได้ โดย Range คือช่วงข้อมูลในแนวบรรรทัดครับ

[prasitkeng]

ขอบคุณทุกๆท่านมากๆครับ