Page 2 of 2
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Wed May 22, 2019 9:21 am
by Totem
มีคำถามเพิ่มเติมต่อเนื่องจากสูตรที่ใช้ได้แล้ว ถ้ากรณีที่ รายการที่ 2 ในคอลัมน์ B มีเพิ่มรายการขึ้นมาอีก 1 รายการหรือมากกว่านั้นอาจเป็น...N รายการใดๆ เช่น กขคฆงจฉช มีเพิ่มขึ้นมาอีกรายการ ทำให้ในคอลัมน์ E หรือ F รายการที่ได้ จะมีผลลัพธ์เพิ่มขึ้น จะปรับสูตรอย่างไรครับ
SEARCH(B$2&"*",$A$2:$A$12)
ครบตัวอักษรให้แสดง เพิ่มเติมล่าสุด.xlsx
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Wed May 22, 2019 11:37 am
by puriwutpokin
ใช้คอลัมน์ช่วยนะครับ
ที่ C2=IF(B2="","",SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(B2,$A$2:$A$12)),MATCH($A$2:$A$12,$A$2:$A$12,0)),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),1))) กด Ctrl+Shift+Enter คัดลอกถึง C12
D2=IF(ROWS(D$2:D2)>C$13,"",SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(C$2:C$12))))<=$C$2:$C$12,ROW($C$2:$C$12)-ROW($C$2)+1),ROWS(E$2:E2)))กด Ctrl+Shift+Enter คัดลอกถึง D12
C13=SUM(C2:C12)
E2=IFERROR(INDEX($A$2:$A$12,SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH(INDIRECT("B"&D2+1),$A$2:$A$12)),MATCH($A$2:$A$12,$A$2:$A$12,0)),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),COUNTIF(D$2:D2,D2))),"")กด Ctrl+Shift+Enter คัดลอกถึง E12
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Wed May 22, 2019 12:15 pm
by Totem
puriwutpokin wrote: Wed May 22, 2019 11:37 am
ใช้คอลัมน์ช่วยนะครับ
ที่ C2=IF(B2="","",SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH(B2,$A$2:$A$12)),MATCH($A$2:$A$12,$A$2:$A$12,0)),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),1))) กด Ctrl+Shift+Enter คัดลอกถึง C12
D2=IF(ROWS(D$2:D2)>C$13,"",SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(C$2:C$12))))<=$C$2:$C$12,ROW($C$2:$C$12)-ROW($C$2)+1),ROWS(E$2:E2)))กด Ctrl+Shift+Enter คัดลอกถึง D12
C13=SUM(C2:C12)
E2=IFERROR(INDEX($A$2:$A$12,SMALL(IF(FREQUENCY(IF(ISNUMBER(SEARCH(INDIRECT("B"&D2+1),$A$2:$A$12)),MATCH($A$2:$A$12,$A$2:$A$12,0)),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),COUNTIF(D$2:D2,D2))),"")กด Ctrl+Shift+Enter คัดลอกถึง E12
ขอบคุณครับได้ตามที่ต้องการครับ
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Wed May 22, 2019 8:48 pm
by Bo_ry
ถ้าไม่ต้องเรียง
F2
=IFERROR(INDEX($A$2:$A$99,MATCH(0,INDEX(COUNTIF(F$1:F1,$A$2:$A$99)/(ISNUMBER(SEARCH($B$2,$A$2:$A$99))+ISNUMBER(SEARCH($B$3,$A$2:$A$99))),),)),"")
ถ้าต้องเรียง
G2
=IFERROR(INDEX(A:A,MOD(AGGREGATE(15,6,TEXT(COUNTIF($A$2:$A$15,"<"&$A$2:$A$15)/(ISNUMBER(SEARCH($B$2,$A$2:$A$15))+ISNUMBER(SEARCH($B$3,$A$2:$A$15)))-SUMPRODUCT(COUNTIF($A$2:$A$15,G$1:G1)),"0;;0")*1000+ROW(A$2:$A$15),1),1000)),"")
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Tue May 28, 2019 2:46 pm
by Totem
อาจารย์และเพื่อนสมาชิก puriwutpokin ช่วยอธิบายสูตรนี้ให้หน่อยครับไม่เข้าใจครับ
D2=IF(ROWS(D$2:D2)>C$13,"",SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(C$2:C$12))))<=$C$2:$C$12,ROW($C$2:$C$12)-ROW($C$2)+1),ROWS(E$2:E2)))
ตรงนี้ครับ SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(C$2:C$12))))<=$C$2:$C$12,ROW($C$2:$C$12)-ROW($C$2)+1)
ขอบคุณครับ
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Tue May 28, 2019 4:34 pm
by puriwutpokin
ตรงนี้จะหาตำแหน่งของข้อมูลที่จะมาแสดงครับ SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(C$2:C$12))))<=$C$2:$C$12,ROW($C$2:$C$12)-ROW($C$2)+1)
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Wed May 29, 2019 8:59 am
by Totem
puriwutpokin wrote: Tue May 28, 2019 4:34 pm
ตรงนี้จะหาตำแหน่งของข้อมูลที่จะมาแสดงครับ SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(C$2:C$12))))<=$C$2:$C$12,ROW($C$2:$C$12)-ROW($C$2)+1)
ครับ ตรงที่ไม่เข้าใจ TRANSPOSE(ROW(INDIRECT("1:"&MAX(C$2:C$12)))) ทำไมต้อง TRANSPOSE (อาร์เรย์หรือช่วงของเซลล์บนแผ่นงานที่คุณต้องการสลับ ) จากแถวสลับเป็นคอลัมน์ ยังไม่กระจ่างครับ
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Wed May 29, 2019 11:05 am
by Totem
มีคำถามเพิ่มเติมครับ กรณีถ้าไม่ต้องการให้ รายการที่1 ในคอลัมน์ A เฉพาะ กขคฆงจฉชAB1 ไปแสดงรายการในคอลัมน์ E
แต่รายการอื่นๆ ยังแสดงรายการเหมือนเดิม จะปรับสูตรอย่างไรครับ
ครบตัวอักษรให้แสดง เพิ่มเติมล่าสุด ยกเว้นรายการ.xlsx
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Wed May 29, 2019 12:25 pm
by puriwutpokin
Totem wrote: Wed May 29, 2019 11:05 am
มีคำถามเพิ่มเติมครับ กรณีถ้าไม่ต้องการให้ รายการที่1 ในคอลัมน์ A เฉพาะ กขคฆงจฉชAB1 ไปแสดงรายการในคอลัมน์ E
แต่รายการอื่นๆ ยังแสดงรายการเหมือนเดิม จะปรับสูตรอย่างไรครับ
ครบตัวอักษรให้แสดง เพิ่มเติมล่าสุด ยกเว้นรายการ.xlsx
รายการที่ไม่ต้องการให้แสดง เพราะเงื่อนไขอะไรครับ เช่น ให้โชว์ 3 รายการหรืออะไร ลองให้เงื่อนไขมาดูครับ
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Wed May 29, 2019 12:52 pm
by Totem
คือรายการที่คอลัมน์ A ช่อง A10 = กขคฆงจฉชAB1 ต้องการว่า ถ้าพบตัวอักษรที่ต่อท้าย กขคฆงจฉช คือ AB1 เป็นการเฉพาะ (กำหนดเป็นตัวอย่างแต่อาจจะมีตัวอักษรมากกว่านี้หรือน้อยกว่านี้ก็ได้) ไม่ให้ไปแสดงที่คอลัมน์ E ช่อง E5 ส่วนรายการที่ช่อง E2 , E3 , E4 ยังคงใช้สูตรเงื่อนไขเดิมครับ
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Wed May 29, 2019 5:06 pm
by puriwutpokin
ปรับที่ E2=IFERROR(INDEX($A$2:$A$12,SMALL(IF(FREQUENCY(IF(NOT(ISNUMBER(SEARCH("*AB1",$A$2:$A$12))),IF(ISNUMBER(SEARCH(INDIRECT("B"&D2+1),$A$2:$A$12)),MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),COUNTIF(D$2:D2,D2))),"") กด Ctrl+Shift+Enter คัดลอกลงครับ
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Thu May 30, 2019 11:36 am
by Totem
puriwutpokin wrote: Wed May 29, 2019 5:06 pm
ปรับที่ E2=IFERROR(INDEX($A$2:$A$12,SMALL(IF(FREQUENCY(IF(NOT(ISNUMBER(SEARCH("*AB1",$A$2:$A$12))),IF(ISNUMBER(SEARCH(INDIRECT("B"&D2+1),$A$2:$A$12)),MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),COUNTIF(D$2:D2,D2))),"") กด Ctrl+Shift+Enter คัดลอกลงครับ
ขอบคุณครับได้ตามที่ต้องการครับ
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Thu May 30, 2019 12:17 pm
by Totem
ขอเพิ่มเติม หากต้องการกำหนดเป็นการเฉพาะมากกว่า 1 รายการ คือรายการที่คอลัมน์ A เช่น ช่อง A3 = กขคฆงจฉชABD1 และ ช่อง A4 = กขคฆงจฉชABG1 ต้องการว่า ถ้าพบตัวอักษรที่ต่อท้าย กขคฆงจฉช คือ ABD1 และ ABG1 เป็นการเฉพาะเพิ่มเติม ไม่ให้ไปแสดงที่คอลัมน์ E ช่อง E4 และ E5 ส่วนรายการที่ช่อง E1 , E2 , E6 ยังคงใช้สูตรเงื่อนไขเดิมครับ
ครบตัวอักษรให้แสดง เพิ่มเติมล่าสุด ยกเว้นรายการมากกว่า1.xlsx
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Thu May 30, 2019 9:12 pm
by puriwutpokin
ที่ C15:C17 คีย์ ABD1,ABG1,AB1
ที่ E2=IFERROR(INDEX($A$2:$A$12,SMALL(IF(FREQUENCY(IF(NOT(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($C$15:$C$17),$A$2:$A$12)),ROW($C$15:$C$17)^0)),IF(ISNUMBER(SEARCH(INDIRECT("B"&D2+1),$A$2:$A$12)),MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),COUNTIF(D$2:D2,D2))),"") กด Ctrl+Shift+Enter คัดลอกลงครับ
Re: หาจำนวนตัวอักษรตรงกันและครบแล้วให้แสดงออกมา
Posted: Fri May 31, 2019 10:08 am
by Totem
puriwutpokin wrote: Thu May 30, 2019 9:12 pm
ที่ C15:C17 คีย์ ABD1,ABG1,AB1
ที่ E2=IFERROR(INDEX($A$2:$A$12,SMALL(IF(FREQUENCY(IF(NOT(MMULT(--ISNUMBER(SEARCH(TRANSPOSE($C$15:$C$17),$A$2:$A$12)),ROW($C$15:$C$17)^0)),IF(ISNUMBER(SEARCH(INDIRECT("B"&D2+1),$A$2:$A$12)),MATCH($A$2:$A$12,$A$2:$A$12,0))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),ROW(INDIRECT("1:"&ROWS($A$2:$A$12)))),COUNTIF(D$2:D2,D2))),"") กด Ctrl+Shift+Enter คัดลอกลงครับ
ขอบคุณครับได้ตามที่ต้องการครับ