☰

警告：

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} 并将其传递给平均函数。

我也在使用 Google Sheets，但大多数标准 Excel 函数应该都可以使用。谢谢。

答案1

我建议通过两个步骤来解决这个问题：

警告：

  • 下面显示的方法适用于 Excel 用户，因为 VBA 不适用于 Google Sheet。
  • Excel 文件可以转换为XLSX适合/可使用 Google Sheet 编辑的文件格式。
  • SUMPRODUCT也适用于 Google Sheet。

在此处输入图片描述

步骤1：

  • 按下Alt+F11 或R点击年代按 T​​AB 并从弹出菜单中点击五查看代码以获取 VB 编辑器。

  • Copy 和磷将此代码粘贴为米模块，然后返回到工作表。

    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 Sheet 用户可以使用以下公式：

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

注意： -根据需要调整公式中的单元格引用。

答案2

如果您想要的是AVERAGE这些值，请在 Sheets 中尝试（使用 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 将{...}在公式栏中看到括号

相关内容