Excel 前綴或後綴

Question 1

選項1：

我想建議 UDF（使用者定義函數），它不僅計算逗號分隔數字的出現次數，還計算文字。

怎麼運作的：

按Alt+F11然後取得VB編輯器複製&貼上這段程式碼為模組。

Option Explicit

Function CountOccurrence(SearchRange As Range, Phrase As String) As Long

Dim RE As Object, MC As Object
Dim sPat As String
Dim V As Variant
Dim I As Long, J As Long

V = SearchRange

Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.MultiLine = True
.ignorecase = True
.Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
End With

For I = 1 To UBound(V, 1)
If RE.test(V(I, 1)) Then J = J + 1
Next I

CountOccurrence = J

End Function

在 Range 中輸入 Criteria H18:H26，然後在 Cell 中輸入此公式I18並填寫。

=CountOccurrence($G$18:$G$24,H18)

選項2：

在儲存格中輸入此公式I18並填寫。

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

根據需要調整儲存格引用。

Answer

選項1：

我想建議 UDF（使用者定義函數），它不僅計算逗號分隔數字的出現次數，還計算文字。

怎麼運作的：

按Alt+F11然後取得VB編輯器複製&貼上這段程式碼為模組。

Option Explicit

Function CountOccurrence(SearchRange As Range, Phrase As String) As Long

Dim RE As Object, MC As Object
Dim sPat As String
Dim V As Variant
Dim I As Long, J As Long

V = SearchRange

Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.MultiLine = True
.ignorecase = True
.Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
End With

For I = 1 To UBound(V, 1)
If RE.test(V(I, 1)) Then J = J + 1
Next I

CountOccurrence = J

End Function

在 Range 中輸入 Criteria H18:H26，然後在 Cell 中輸入此公式I18並填寫。

=CountOccurrence($G$18:$G$24,H18)

選項2：

在儲存格中輸入此公式I18並填寫。

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

根據需要調整儲存格引用。

Question 2

首先，您需要將數字拆分為單獨的儲存格：
- 選擇您的數據
- 在資料標籤上選擇“拆分為列”
- 選擇“分隔”，下一步
- 選擇資料以「逗號」分隔，完成
現在你可以使用 COUNTIF，例如
=COUNTIF($A$2:$C$5,E2)

Answer

首先，您需要將數字拆分為單獨的儲存格：
- 選擇您的數據
- 在資料標籤上選擇“拆分為列”
- 選擇“分隔”，下一步
- 選擇資料以「逗號」分隔，完成
現在你可以使用 COUNTIF，例如
=COUNTIF($A$2:$C$5,E2)

Question 3

看起來您已經有了一個解決方案，但我將提供一個處理動態資料的非 VBA 解決方案。它使用一些輔助列，您可以將它們預先填充到任意大的範圍。當沒有關聯資料時，儲存格將為空白。一些輔助列可以被消除；包含它們是為了最大限度地減少重複，但所有輔助列都可以隱藏。

您的資料位於 A 列。 B3中的公式為：

=IF(ISBLANK(A3),"",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)

C 列只是 B 列的累積成分計數0。 C3中的公式為：

=IF(ISBLANK(A3),"",SUM(B3:B$3))

將 B 列和 C 列填入您可能有資料的盡可能多的行中。如果需要，您可以隨時擴充這些列。

E欄只是為了方便。它為解析值提供索引。您可以對其進行硬編碼1，然後為每個連續行添加 1。沒有充分的理由，我基於行號（-2下面公式中的是調整值以從開始1）。超出值總數的儲存格將顯示為空白。我在E3的公式：

=IF(ROW()-2>MAX($C$2:$C$10),"",ROW()-2)

F列只是為了避免公式重複。它提取將從中解析當前組件的相關 A 列條目。 F3中的公式為：

=IFERROR(OFFSET($A$2,MATCH(E3-1,$C$2:$C$10,1),0),"")

它透過比較 E 列中的元件編號與 C 列中的累積元件計數來找到適當的條目。

G 列是已解析的元件值，全部位於易於使用的單一連續列中。 G3中的公式為：

=IFERROR(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",LEN(F3))),(E3-INDEX($C$2:$C$10,MATCH(E3-1,$C$2:$C$10,1))-1)*LEN(F3)+1,LEN(F3))),"")

這透過從目前元素編號中減去最後一個「完成」輸入記錄的累積元素計數來確定從列 F 條目中解析哪個元素。

列 E 到 G 應傳播到足夠的行，以覆蓋預期數量的組件值（至少是資料行數的幾倍）。請注意，上面涉及 $C$2:$C$10 範圍的所有公式都應進行調整，以包含整個資料範圍。

現在您已將所有已解析的元素放在一個漂亮的列中，可以使用多種方法來聚合它們並取得計數。我使用了資料透視表，它同時也為您提供唯一值的清單。

選擇資料透視表 G 列的完整預填範圍。將該欄位用於行視窗和值視窗（選擇計數作為聚合）。此範圍將包括未使用行中的空白，因此請使用內建過濾器取消選擇空白。

當資料變更時，只需刷新資料透視表並驗證是否在篩選器中選擇了任何新元素值。

Answer