สูตร Search แล้วต้องการหลายคำตอบ

[Pongpat_s]

สวัสดีครับ ผมต้องการ search หาคำที่ต้องการในตาราง database
ถ้าเจอคำใดๆ ที่ประกอบไปด้วยคำที่ค้นหา ก็จะให้ดึงค่ามันออกมา
ผมได้ทำการนับจำนวน คำที่เจอเบื้องต้นแล้วครับ
แต่ผมไม่รู้สูตรที่จะดึงค่า
1) คำใดๆ ที่เจอในประโยค
2) ข้อมูลของ Row, ซ้ายสุดของตาราง ของคำที่เจอ
3) ข้อมูลของ Column, บนสุดของตาราง ของคำที่เจอ
คำตอบที่ต้องการ อยู่ในตารางสีเขียวครับ

ขอบคุณสำหรับคำแนะนำครับ

[snasui]

ตัวอย่างสูตรครับ

J4 คีย์

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($B$4:$B$8)*10000+COLUMN($B$4:$D$4))/(LEFT($B$4:$D$8,4)=$H4),COLUMNS($J4:J$4)),"r0c0000"),0)&", "&INDEX($A:$A,AGGREGATE(15,6,ROW($B$4:$B$8)/(LEFT($B$4:$D$8,4)=$H4),COLUMNS($J4:J$4)))&", "&INDEX($3:$3,AGGREGATE(15,6,COLUMN($B$4:$D$4)/(LEFT($B$4:$D$8,4)=$H4),COLUMNS($J4:J$4))),"")

Enter > Copy ไปทางขวาและลงด้านล่าง

[Bo_ry]

อีกแบบ
J4
=IF(COLUMNS($J3:J3)>$I4,"",INDEX($B$4:$D$8&", "&$A$4:$A$8&", "&$B$3:$D$3,MOD(AGGREGATE(15,6,COLUMN($B$4:$D$8)*1000+ROW($B$4:$D$8)/ISNUMBER(SEARCH($H4,$B$4:$D$8)),COLUMNS($J4:J4)),1000)-ROW($A$3),AGGREGATE(15,6,COLUMN($B$4:$D$8)/ISNUMBER(SEARCH($H4,$B$4:$D$8)),COLUMNS($J4:J4))-COLUMN($A$3)))

[Pongpat_s]

ตัวอย่างสูตรครับ

J4 คีย์

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($B$4:$B$8)*10000+COLUMN($B$4:$D$4))/(LEFT($B$4:$D$8,4)=$H4),COLUMNS($J4:J$4)),"r0c0000"),0)&", "&INDEX($A:$A,AGGREGATE(15,6,ROW($B$4:$B$8)/(LEFT($B$4:$D$8,4)=$H4),COLUMNS($J4:J$4)))&", "&INDEX($3:$3,AGGREGATE(15,6,COLUMN($B$4:$D$4)/(LEFT($B$4:$D$8,4)=$H4),COLUMNS($J4:J$4))),"")

Enter > Copy ไปทางขวาและลงด้านล่าง

ขอบคุณครับ ได้คำตอบเลยครับ ผมขออนุญาตถามเพิ่มเติมครับ
ส่วนตรงสูตรนี้ครับ ROW($B$4:$B$8)*10000+COLUMN($B$4:$D$4))
ถ้าเฉพาะ ROW($B$4:$B$8) = {4;5;6;7;8} คำถามคือทำไมต้องเอาไปคูณ 10000 แล้วก็บวก {2,3,4} ?
ช่วยอธิบายส่วนนี้ให้ผมหน่อยได้ไหมครับ เผื่อผมจะได้เอาไปศึกษาต่อเพิ่มเติมครับ

[Pongpat_s]

อีกแบบ
J4
=IF(COLUMNS($J3:J3)>$I4,"",INDEX($B$4:$D$8&", "&$A$4:$A$8&", "&$B$3:$D$3,MOD(AGGREGATE(15,6,COLUMN($B$4:$D$8)*1000+ROW($B$4:$D$8)/ISNUMBER(SEARCH($H4,$B$4:$D$8)),COLUMNS($J4:J4)),1000)-ROW($A$3),AGGREGATE(15,6,COLUMN($B$4:$D$8)/ISNUMBER(SEARCH($H4,$B$4:$D$8)),COLUMNS($J4:J4))-COLUMN($A$3)))

ขอบคุณครับ ขออนุญาตสอบถามเพิ่มเติมครับ
1. สูตร Index ใน argument แรก Index($B$4:$D$8&", "&$A$4:$A$8&", "&$B$3:$D$3
รบกวนช่วยอธิบายการทำงานใน Array นี้หน่อยได้ไหมครับ

ทีนี้เวลาข้อมูลเยอะๆ หลายตาราง ผมก็ต้องตั้ง Name manager 3 ชื่อ ต่อหนึ่งตารางใช่ไหมครับ ? ก็ประกอบไปด้วย
1.1 B4:D8 คือช่วงข้อมูลที่ต้องการ Lookup
1.2 A4:A8 คือช่วงของ name list ในแนว row
1.3 B3:D3 คือช่วง name list ในแนว column

2. จากสูตรที่ดึงข้อมูลของ row number
MOD(AGGREGATE(15,6,COLUMN($B$4:$D$8)*1000+ROW($B$4:$D$8)/ISNUMBER(SEARCH($H4,$B$4:$D$8)),COLUMNS($J4:J4)),1000)-ROW($A$3)
ช่วยอธิบายสูตรนี้ได้ไหมครับ

ขอบคุณล่วงหน้าครับ

[snasui]

ตัวอย่างสูตรครับ

J4 คีย์

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($B$4:$B$8)*10000+COLUMN($B$4:$D$4))/(LEFT($B$4:$D$8,4)=$H4),COLUMNS($J4:J$4)),"r0c0000"),0)&", "&INDEX($A:$A,AGGREGATE(15,6,ROW($B$4:$B$8)/(LEFT($B$4:$D$8,4)=$H4),COLUMNS($J4:J$4)))&", "&INDEX($3:$3,AGGREGATE(15,6,COLUMN($B$4:$D$4)/(LEFT($B$4:$D$8,4)=$H4),COLUMNS($J4:J$4))),"")

Enter > Copy ไปทางขวาและลงด้านล่าง

ขอบคุณครับ ได้คำตอบเลยครับ ผมขออนุญาตถามเพิ่มเติมครับ
ส่วนตรงสูตรนี้ครับ ROW($B$4:$B$8)*10000+COLUMN($B$4:$D$4))
ถ้าเฉพาะ ROW($B$4:$B$8) = {4;5;6;7;8} คำถามคือทำไมต้องเอาไปคูณ 10000 แล้วก็บวก {2,3,4} ?
ช่วยอธิบายส่วนนี้ให้ผมหน่อยได้ไหมครับ เผื่อผมจะได้เอาไปศึกษาต่อเพิ่มเติมครับ

การนำไปคูณ 10000 แล้วบวกกับค่าตำแหน่งคอลัมน์เพื่อให้ค่าที่แตกต่างกันในแต่ละเซลล์ที่ตรงกับเงื่อนไข จากนั้นนำค่านี้ไปแปลงด้วยฟังก์ชั่น Indirect ให้กลับมาเป็นตำแหน่งเซลล์อีกทีครับ

[Bo_ry]

=$B$4:$D$8&", "&$A$4:$A$8&", "&$B$3:$D$3
ได้กรอบแดง
=COLUMN($B$4:$D$8)*1000+ROW($B$4:$D$8)-COLUMN($A$3)*1000-ROW(A3)
ได้กรอบเขียว
=COLUMN($B$4:$D$8)*1000+ROW($B$4:$D$8)-COLUMN($A$3)*1000-ROW(A3)/ISNUMBER(SEARCH($H4,$B$4:$D$8))
กรองมาเฉพาะที่มี A001
ได้กรอบฟ้า
จากกรอบฟ้า ดึงตัวเลขจากน้อยไปมาก
แล้วแยกหลัก 1000 ก็ได้ เลข Column
แล้วแยกหลัก หน่วย ก็ได้ เลข Row
เอาไปใช้กับ Index ดึงค่าจากกรอบแดงมาได้