麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 請問如何讀取儲存格裡的詞的次數? [打印本頁]

作者: gaishutsusuru 時間: 2022-1-18 19:21 標題: 請問如何讀取儲存格裡的詞的次數?

大家好，

想請教大家一個問題：
[attach]34605[/attach]

相關說明：
(1) A4:B6，這個是資料
(2) E4,E5 是想讀取的詞
(3) F4,F5 是讀取的詞的次數
(4) G4,G5 是該讀取的詞出現在儲存格A4~A6對應的哪一個值(Q1,Q2,Q3)

希望能把(3)(4)，用公式寫出來。(我有試了用countif，但好像不能成功運作><)

最後，附上附檔給您參考：[attach]34606[/attach]

如果看不懂問題的描述可以問我，再麻煩大家協助幫忙，謝謝大家。

作者: samwang 時間: 2022-1-19 08:02

本帖最後由 samwang 於 2022-1-19 08:04 編輯

回復 1# gaishutsusuru
只會VBA，僅供參考，謝謝
Sub test()
Dim Arr, Brr, a, xD, T$, i&, i1&
Brr = Range([e4], [e65536].End(3).Offset(, 2))
Arr = Range([a4], [b65536].End(3))
For i1 = 1 To UBound(Brr)
T = Brr(i1, 1)
For i = 1 To UBound(Arr)
      a = Split(Arr(i, 2), " ")
      For j = 0 To UBound(a)
         If InStr(a(j), ",") Then a(j) = Replace(a(j), ",", "")
         If InStr(a(j), ".") Then a(j) = Replace(a(j), ".", "")
         If a(j) = T Then
            Brr(i1, 2) = Brr(i1, 2) + 1
            Brr(i1, 3) = IIf(Brr(i1, 3) = "", Arr(i, 1), Brr(i1, 3) & "," & Arr(i, 1))
         End If
      Next
Next
Next
Range("e4").Resize(UBound(Brr), 3) = Brr
End Sub

作者: gaishutsusuru 時間: 2022-1-19 08:14

回復 2# samwang

您好，謝謝您的幫忙，

但我實際測了以後，出現如下圖：
(按一次巨集)
[attach]34609[/attach]
(按二次巨集)
[attach]34610[/attach]

另一部份，
(1) 實際的資料，會超過A4:B6的三筆資料(而例子中只有列舉3筆)
(2) 實際想讀取的詞，也會超過E4,E5 的二個詞(而例子中只有列舉2個)

(如果可以，還是希望能有公式的解法><)

謝謝您的抽空幫忙。

作者: 劉大胃 時間: 2022-1-19 10:51

Hello gaishutsusuru,

這個看起來, 很難用公式的方式達成, 就如pen來說, 有在同一個句子裡, 出現過2次, 但全都要count出來, 依我的理解, 這個只能用巨集的方式進行了!!
Thanks ~

作者: gaishutsusuru 時間: 2022-1-19 12:49

回復 4# 劉大胃

Hello 劉大胃，

謝謝您的解說。

如果問題改成：在同一個儲存格裡，雖然出現過2次以上，但仍只要計入1次就好。
(因此範例中，儲存格F4的pen的值就不要顯示4，而是顯示3就好了。)

請問這樣可以用公式達成嗎？謝謝您。

作者: hcm19522 時間: 2022-1-19 14:26

https://blog.xuite.net/hcm19522/twblog/590221805

作者: gaishutsusuru 時間: 2022-1-19 15:06

回復 6# hcm19522

hcm大大您好，

謝謝您的幫忙，公式可以用，太感謝您了。

(但實測後有個小小的問題，因為實際上資料可能會大於3筆，所以G3向右拉的話，要拉很多個儲存格才能在G3正確顯示。請問這個問題有方法可以解決嗎?)

作者: 劉大胃 時間: 2022-1-19 17:40

Hello gaishutsusuru,

我的方法比較土法練鋼, 但~也是運用橫軸來做各別的判斷, 然後再Summary起來.
Thanks ~

F4=COUNTIFS($B$4

B$6,"* " & E4 & "*")
G4=IF(LEN(H4 & I4 & J4)>0,LEFT(H4 & I4 & J4,LEN(H4 & I4 & J4)-1),"")
H4=IF(COUNTIFS(INDIRECT("$B" & H$3 & "

B" & H$3),"* " & $E4 & "*")>0,INDIRECT("$A" & H$3) & ",","")
I4=IF(COUNTIFS(INDIRECT("$B" & I$3 & "

B" & I$3),"* " & $E4 & "*")>0,INDIRECT("$A" & I$3) & ",","")
J4==IF(COUNTIFS(INDIRECT("$B" & J$3 & "

B" & J$3),"* " & $E4 & "*")>0,INDIRECT("$A" & J$3) & ",","")

作者: hcm19522 時間: 2022-1-19 18:18

回復 7# gaishutsusuru
我是2007版較新版本有指令無須右拉

作者: gaishutsusuru 時間: 2022-1-19 18:40

回復 9# hcm19522

hcm大大您好，

請問您說的指令是什麼呢？有參考的資料嗎？
謝謝您

作者: gaishutsusuru 時間: 2022-1-22 11:49

回復 8# 劉大胃

劉大胃大大您好，

我試著一下您的公式，並且想了一下呈現方式。

想請教如果不要在一個儲存格(G4)內放「Q1,Q2,Q3」，而是分別放在個別的儲存格G4(顯示：Q1) ,H4(顯示：Q2),I4(顯示：Q3)呢?
這樣公式可以怎麼改寫呢? 謝謝您。

(另外，您提供的公式，有顯示圖示，不知道是為什麼><)

作者: 准提部林 時間: 2022-1-22 14:55

三萬多行, 還是用vba吧???
最好再多傳些完整的例子,
為避免 THE 與 THEN 這類混淆, 需要用空格符來 SPLIT,
但會被其它符號干擾, 如:逗號,句號,問號, 驚歎號,破折號, 小蚯蚓....等等

作者: gaishutsusuru 時間: 2022-1-22 15:39

回復 12# 准提部林

准提部林大大您好，

謝謝您的提醒，我修改了問題，請問題目這樣修改的話，是否可以用公式寫出來了呢：
[attach]34613[/attach]
更動相關說明：
(1) 從F欄開始向右顯示對應出現的A欄的值，存放在不同的儲存格，如圖所示。
(2) D欄用來判斷B欄的值，不分大小寫。

附上附檔：[attach]34614[/attach]

再麻煩大大抽空協助，謝謝您。

作者: gaishutsusuru 時間: 2022-1-23 10:34

回復 13# gaishutsusuru

早安各位大大，關於問題的補充說明：

不好意思，如果用公式相對困難，而用VBA可以的話，也歡迎大大們提供協助，謝謝。
(但資料的筆數、關鍵字的判斷，希望能延伸到整個B欄 (B:B) 、D欄 (D:D)，以方便擴充)

再麻煩大家的幫忙了，謝謝。

作者: 准提部林 時間: 2022-1-23 13:37

回復 14# gaishutsusuru

基本上, 這用公式不太符合實益,
VBA範例檔:
[attach]34615[/attach]

有關"正則"的知識, 自行去找參考資料, 太複雜...不想多花時間去解釋~~

作者: gaishutsusuru 時間: 2022-1-23 14:15

本帖最後由 gaishutsusuru 於 2022-1-23 14:17 編輯

回復 15# 准提部林

謝謝准提部林大大的幫忙，太厲害了。謝謝您。細節我再來了解看看。

不好意思，我突然想到另一個問題，想再請教您，有沒有辦法加上超連結的功能呢?

[attach]34616[/attach]
例如：點擊儲存格F2的Q1的超連結，就會跳到Q1(儲存格A2)；其他依此類推。

希望能再抽空協助，謝謝您。

作者: hcm19522 時間: 2022-1-23 14:21

回復 10# gaishutsusuru

這個指令 =TEXTJOIN("、",1,IF((~)*(~)*(~),~,""))

作者: 准提部林 時間: 2022-1-23 14:49

回復 16# gaishutsusuru

用 hyperlink 超連結:
[attach]34617[/attach]

作者: gaishutsusuru 時間: 2022-1-23 18:19

檔案可以使用
謝謝准提部林、hcm19522 的幫忙。

作者: gaishutsusuru 時間: 2022-2-18 16:37

本帖最後由 gaishutsusuru 於 2022-2-18 16:40 編輯

回復 18# 准提部林

准提部林大大您好，

想再次進一步請教您，

我在您給的檔案「正則-統計英文單字-01.rar」中，想新增一些功能：

(1) 工作表：工作表1
新增：篩選條件1 (把想要處理的關鍵字，在D欄打勾)
(↑所以原來您提供的巨集可能會跑掉了><)

(2) 工作表：篩選1
說明：在欄A~C，列出符合篩選條件1的關鍵字、內容、題號。
例1：列出在工作表1D欄打勾的關鍵字：「then, and ,BUY」

(3) 工作表：篩選2
說明：在欄E~G，列出符合「篩選條件1、篩選條件2、篩選條件3」的關鍵字、內容、題號
(註：「篩選條件2、篩選條件3」是篩選出題號內有符合的對應條件)
(註：若篩選條件2是空白的，則只列出符合「篩選條件1、篩選條件3」的)
(註：若篩選條件3是空白的，則只列出符合「篩選條件1、篩選條件2」的)

例1：(我把結果手動放在工作表：篩選2的A1~G5，供參，要刪除)
篩選條件1：then, and ,BUY
篩選條件2：a1,a2,a3
篩選條件3：1月,5月,6月
=> 列出 ①關鍵字符合「then  , and , BUY」且 ②該題號出現在「a1 或 a2 或 a3」裡且 ③該題號出現在「1月或 5月或 6月」裡

例2：(我把結果手動放在工作表：篩選2的A9~G11，供參，要刪除)
篩選條件1：then, and ,BUY
篩選條件2：a1
篩選條件3：
=> 列出 ①關鍵字符合「then  , and , BUY」且 ②該題號出現在「a1」裡

例3：(我把結果手動放在工作表：篩選2的A14~G16，供參，要刪除)
篩選條件1：then, and ,BUY
篩選條件2：
篩選條件3：5月
=> 列出 ①關鍵字符合「then  , and , BUY」且 ②該題號出現在「5月」裡

因此，想再次拜託准提部林大大幫忙，如何利用巨集處理出「工作表：篩選1」、「工作表：篩選2」的結果呢?

最後附上附檔：[attach]34672[/attach]

再麻煩您能抽空協助，謝謝您的幫忙。

作者: 准提部林 時間: 2022-2-20 13:01

回復 20# gaishutsusuru

試試看:
[attach]34674[/attach]

與原帖雖有相關, 但需求完全不同 ,有問題最好另行開帖, 沒有時間這樣跟帖~~

作者: gaishutsusuru 時間: 2022-2-21 13:29

回復 21# 准提部林

准提部林大大您好，

真的謝謝您的幫忙。
不好意思，以後發文會再多多注意的，謝謝您。

作者: Andy2483 時間: 2022-11-10 08:11

本帖最後由 Andy2483 於 2022-11-10 08:17 編輯

回復 21# 准提部林

'謝謝前輩
'這帖學到很多知識
'1.=HYPERLINK(), =HYPERLINK(""#xx"",""yy"")==HYPERLINK(""#工作表1!xx"",""yy"")
'2.更認識字串變數裡保留雙引號" 這字元
'3."[^A-Za-z\'-]" 正則文字規則---保留"英文字" + "單引號" + "-"
'4.精確的儲存格位置帶入陣列值

執行前:
[attach]35478[/attach]

執行結果:
[attach]35479[/attach]
Sub TEST_A1()
Dim Arr, Brr, xD, B, i&, j%, Fx$, CT$, T$, T1$, R&, C%, Cx%
'↑宣告變數
Set xD = CreateObject("Scripting.Dictionary")
'↑令 xD是字典
Call 清除 '清除E列右方內容
'↑執行 Sub 清除() 副程式

Arr = Range([h1], [h65536].End(xlUp)).Resize(, 200)
'↑令Arr是陣列!倒入[H1]到H欄最後一個有內容儲存格再往右擴展200欄(到了GY欄)
For i = 2 To UBound(Arr)
'↑設順迴圈!從2 到Arr陣列縱向最後列號
T = LCase(Trim(Arr(i, 1)))
'↑先將Arr陣列的第一欄迴圈列去除前後空格後,
'再把剩下的字串裡的英文字母轉小寫
If T <> "" Then xD(T) = i
'↑如果T字串不是空的!就將T字串當key(鍵)納入字典, item是迴圈數也是(工作表的列號)  '@@
'T也是後面程序要用的關鍵字
Next i
Fx = "=HYPERLINK(""#xx"",""yy"")"  '超連結公式共用字串--xx替換為位址--yy替換為要顯示文字
'↑令Fx字串是 "=HYPERLINK("#xx","yy")"
'↑藉此帖學習如何讓字串變數裡保留雙引號" 這字元!
'研究結果:先從該行程式碼頭尾各去除掉一個 " ,以下是細節
'1.剩下的字元 =HYPERLINK(""#xx"",""yy""),連續的""會保留一個",成為變數裡的"字元!
' 成為 =HYPERLINK("#xx","yy")
'2.要注意如果分配到最後剩下一個"字元 ,是不允許的!
'  例如 MsgBox "1""是錯的!但是VBA會幫補1個",變成 MsgBox "1"""  (去除頭尾",剩1"",最後顯示1")
'3.非雙引號字元之間分配間隔剩下一個"字元,也是不允許的!
'  例如MsgBox "1"2"是錯的! (去除頭尾",剩1"2,只剩中間一個",會出現編譯錯誤訊息),
'  MsgBox "1"2""也是錯的!  改為  MsgBox "12"""  訊息窗 12",改為  MsgBox "1""2"  訊息窗 1"2
'4.MsgBox """1""2""3""4"  ,訊息窗 "1"2"3"4

CT = "[^A-Za-z\'-]"  '文字規則---保留"英文字" + "單引號" + "-"
'↑令CT 是字串 "[^A-Za-z\'-]"  ,
'如何讓字串變數裡保留單引號' 這字元?? 字串的頭尾有雙引號包夾住就可以
Brr = Range([b1], [a65536].End(xlUp))
'↑令Arr是陣列!倒入[B1]與A欄有內容儲存格間擴展為最小方正區域儲存格的值
For i = 2 To UBound(Brr)
'↑設外順迴圈!從 2到Brr陣列縱向最後列號
T = Trim(正則轉換(LCase(Brr(i, 2)), " ", CT))
'↑將文字轉小寫,並以正則將不要的文字替換空格後傳回
'令T是Brr陣列裡第二欄迴圈列的被處理過的值! 如何處理??
'先將原值字串轉換為小寫做為正則轉換()自訂義函數的被正則字串,取代文字是空白字元,
'規則字符是 CT = "[^A-Za-z\'-]"
'正則之後再去掉字串頭尾的空白字元
For Each B In Split(T, " ") '以空格分拆單字
'↑設內順迴圈!令 B 是一維陣列裡的一員!
'哪來的一維陣列? 正則之後的字串用空白字元分割就是一維陣列
      R = xD(B & "")
      '↑令B變成字串後當key(鍵)!查字典裡的item是什麼? 丟給R 長整數裝著!
      '如果不是初始值 0 就是長整數(工作表的列號) '在前面@@標註位置
      If R = 0 Then GoTo b01
      '↑如果 R=0, 表示不是關鍵字或空格. 略過! 就跳到 b01的位置繼續執行
      T1 = B & "|" & i
      '↑令T1字串是 B字串 & "|" & 外迴圈數的組合字串(以下稱B|i組合字串)!
   '關鍵字+i列號...用于排除同一列字串出現相同關鍵字一次以上
      xD(T1) = xD(T1) + 1
      '↑令 B|i組合字串當key ,item累加 1
      If xD(T1) > 1 Then GoTo b01
      '↑同一字串出現1次以上, 不再處理, 略過! 就跳到 b01的位置繼續執行
      Arr(R, 2) = Arr(R, 2) + 1
   '↑令Arr陣列的第二欄(關鍵字所在的列)位置累加 1
      C = Arr(R, 2)
   '↑令C是次數累計
      Arr(R, C + 2) = Replace(Replace(Fx, "xx", "A" & i), "yy", Brr(i, 1))
      '↑由左而右填入"題號"...替代成超連公式
   'Arr陣列裡關鍵字所在的列號(次數累計+2欄)的位置填入 Fx被處理過的字串
      'Fx怎麼被處理??  Fx這變數在前方早就被定義為固定變數 "=HYPERLINK("#xx","yy")"
      '在此處只是每個符合判斷式的迴圈,拿它出來變為想要的超連結公式的文字串
      '因為此文字串的前方 = 在 Arr陣列裡就只是字元! 最後面程序貼到儲存格裡會變成超連結公式
      If C > Cx Then
   '↑Cx如果次數累計大於 Cx(這不知道是什麼的短整數??) (Cx的初始值是 0)
         Cx = C
      '↑Cx是要裝入 C次數累計加 1的數字!
      '多這個 Cx變數另一個目的是為了要的在後面程序中精確的儲存格位置貼入值
         Arr(1, C + 2) = "題號-" & Cx
         '↑標題列加序號
      End If
b01: Next
i01: Next i
Arr(1, 2) = "次數"
If Cx = 0 Then Exit Sub
'↑如果沒有資料!就結束執行
With [h1].Resize(UBound(Arr), Cx + 2)
'↑接下來是關於[H1]儲存格向下擴展Arr陣列縱向最大列號,向右擴展標題列_題號序號再加 2 欄
.Value = Arr
'↑將Arr陣列值從 [h1]開始帶入
.EntireColumn.AutoFit
'↑這些欄位自動調整欄寬
End With
End Sub

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)