☰

警告：

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

1 桁の評価番号とその横のテキストが混在するセルのセットがあります (評価 1 - 9)。各セルの数値を抽出して平均を算出したいのですが、Search や RegexExtract などの関数については知っていますが、最初の一致のみが返されるようなので、それらを使用してすべての数値を取得する方法がわかりません。

以下は、1 つのセル内のテキストのサンプルです。

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

上記のテキストでは、配列 {1,5,9,4,7,2} を作成し、それを平均関数に渡したいと考えています。

また、私は Google スプレッドシートを使用していますが、標準的な Excel 関数のほとんどは同じように動作するはずです。ありがとうございます。

答え1

次の 2 つのステップで問題を解決できると思います。

警告：

  • 以下に示す方法は、VBA が Google スプレッドシートでは機能しないため、Excel ユーザー向けです。
  • XLSXExcel ファイルは、Google スプレッドシートに適した編集可能なファイル形式に変換できます。
  • SUMPRODUCTGoogle スプレッドシートでも動作します。

ここに画像の説明を入力してください

ステップ1：

  • どちらかを押すあlt+F11 またはRight クリックスTABを押してポップアップメニューから五VB エディターを取得するためのコードを表示します。

  • Ｃオピーとポこのコードをまモジュールを終了し、シートに戻ります。

    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：

  • 取得するためあVERAGE セルにこの数式を入力します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 2016以降をお持ちでTEXTJOIN、この機能を使用できる場合は、配列式：

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

これは配列数式なので、ctrl+shiftを押しながら を押して「確定」する必要がありますenter。これを正しく実行すると、Excelは{...}数式バーに表示されているように数式を中括弧で囲みます。

関連情報