搜尋字串字數，如何下公式呢?

gaishutsusuru

下載 (16.17 KB)

2021-8-3 07:59

大家早，

想請問如圖的F2~F4公式，該如何下才能顯示值呢?

F2想搜尋有包含1~10的字串，而我用COUNTIF($A$2:$A$4,E2&"*")，卻無法找出值，不知道問題出在哪?


附上檔案： 活頁簿1.zip (7.12 KB)

下載次數: 6

2021-8-3 07:58

想麻煩各位協助提供想法，謝謝您們。

ML089

=COUNTIF($A$2:$A$4,"*"&SUBSTITUTE(E2,"~","~~")&"*")

准提部林

1~10
91~100
會有錯誤~~

陣列:
=COUNT(FIND(", "&E2&",",", "&A$2:A$19&","))

嚴謹些:
=COUNT(FIND(","&E2&",",","&SUBSTITUTE(A$2:A$19," ",)&","))

ML089

2樓公式修正
=SUM(COUNTIF($A$2:$A$19,{"","* "}&SUBSTITUTE(E2,"~","?")&{"";",*"}))
分隔符號為 "," + " "(空格)

資料分隔格是要統一，不然COUNTIF函數統計容易出錯，可以參考3樓 嚴謹公式

ML089

增加邊界狀況判斷，減少資料格式不一樣錯誤
=IF(E2="","",SUM(COUNTIF(A:A,{"","* ","*,"}&SUBSTITUTE(E2,"~","?")&{"";" *";",*"})))

增加3種邊界判斷  ""，" "，","

gaishutsusuru

感謝ML089和准提部林的熱心幫忙，公式可以使用!