Snasui Excel Forum

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

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

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

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 ไปทางขวาและลงด้านล่าง

อีกแบบ
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)))

snasui wrote: Mon Aug 03, 2020 8:47 am ตัวอย่างสูตรครับ

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} ?
ช่วยอธิบายส่วนนี้ให้ผมหน่อยได้ไหมครับ เผื่อผมจะได้เอาไปศึกษาต่อเพิ่มเติมครับ

Bo_ry wrote: Mon Aug 03, 2020 12:31 pm อีกแบบ
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)
ช่วยอธิบายสูตรนี้ได้ไหมครับ

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

Pongpat_s wrote: Mon Aug 03, 2020 4:10 pm

snasui wrote: Mon Aug 03, 2020 8:47 am ตัวอย่างสูตรครับ

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 ให้กลับมาเป็นตำแหน่งเซลล์อีกทีครับ

=$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 ดึงค่าจากกรอบแดงมาได้