☰

警告：

tag-icon • tag-icon google-spreadsheets
警告：

我有一組單元格，其中有單位數字的評級數字與旁邊的文本混合（評級為 1 - 9）。我想要做的是對於每個單元格，提取數字並產生平均值。我知道像 Search 和 RegexExtract 這樣的函數，但我不明白如何使用它們來獲取所有數字，因為它似乎只傳回第一個匹配項。

以下是單一儲存格中的文字範例：

A - 1
B - 5
C - 9
D -4
E -7
F -  2

對於上面的文本，我希望建立一個陣列 {1,5,9,4,7,2} 並將其傳遞給average函數。

我還使用 Google Sheets，但大多數標準 Excel 函數的工作原理應該是相同的。謝謝。

答案1

我想建議，兩步驟解決方案可以解決該問題：

警告：

  • 下面顯示的方法適用於 Excel 用戶，因為 VBA 不適用於 Google Sheet。
  • Excel 檔案可以轉換為XLSX適合/可使用 Google Sheet 編輯的文件格式。
  • SUMPRODUCT也可與 Google Sheet 搭配使用。

在此輸入影像描述

步驟1：

  • 要么按Alt+F11 或右右鍵S表 TAB 並從彈出式選單中點擊View 取得 VB 編輯器的程式碼。

  • C鴉片和磷將此代碼用作中號odule，然後回到工作表。

    Public Function SplitNum(pWorkRng As Range, pIsNumber As Boolean) As String

Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
xStr = VBA.Mid(pWorkRng.Value, i, 1)
If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
    SplitNum = SplitNum + xStr
End If
 Next
End Function

  • 在儲存格中輸入此公式AI5：=SplitNum(AH1,TRUE)

第2步：

  • 要得到AVERAGE 在儲存格中輸入此公式AJ5。

    =SUMPRODUCT(--MID(AI5,ROW(INDIRECT("1:" & LEN(AI5))),1))/Len(AI5)

Google 試算表使用者可以使用以下公式：

=AVERAGE(SPLIT(AH1,CONCATENATE(SPLIT(AH1,".0123456789"))))

注意 -根據需要調整公式中的儲存格引用。

答案2

如果您想要的是AVERAGE這些值中的一個，請在表格中嘗試（使用 A1 中的資料：

=average(split(trim(REGEXREPLACE(A1,"\D+"," "))," "))

我們只是刪除非數字，而不是嘗試提取每個數字；然後將它們分開並平均。有時，刪除我們不想要的東西是一種方便的技術，而不是試圖提取我們真正想要的東西。

在此輸入影像描述

為了Excel用戶，如果您有Excel 2016+且具有該TEXTJOIN功能，則可以使用此大批公式：

=AVERAGE(ABS(FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(" ",TRUE,A1:A6)," ","</s><s>") & "</s></t>","//s[number()=number()]")))

由於這是一個數組公式，因此您需要按住ctrl+ 的shift同時點擊 來「確認」它enter。如果運算正確，Excel 會{...}在公式周圍放置大括號，如公式欄所示

相關內容