☰

Microsoft Excel の数式で英数字文字列の数値の合計を取得するにはどうすればよいでしょうか?

tag-icon • tag-icon microsoft-excel
Microsoft Excel の数式で英数字文字列の数値の合計を取得するにはどうすればよいでしょうか?

セルに次のようなデータがあります:

良い反応 -6、RNR -2、声なし -1

セル内の数字のみの合計を取得する必要があります。 1 つの数式でこれを行うにはどうすればよいですか?

答え1

私はエクセルを使います正規表現検索/置換アドイン、Excel に正規表現機能を追加します。
(私はそのアドインとは何ら関係がなく、ただの熱心なユーザーです)

ここでは、次のような数式を使用して、すべての数値を見つけて合計することができます。
=SUM(RegExFind(A1,"\d+"))

または、負の値も考慮します。
=SUM(RegExFind(A1,"[-\d]+"))

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

答え2

提供された情報に基づき、抽出する値は3つだけで、それぞれの長さは1桁であると仮定すると、以下の文字列はA2:

=LEFT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),1)+RIGHT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),1)+RIGHT(A2,1)

もちろん、ここで私が立てた仮定はいくつかありますが、

それでも問題が解決しない場合は、データの構造などを詳しく教えてください。修正してみます。

答え3

データが本当に表示されているとおりで、数字の前に が付き<space>、その後に<comma>または文字列の末尾が続く場合は、次のように使用できます。

数式を定義して名前をseq_99付ける

seq_99 refers to:  =IF(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))=1,1,(ROW(INDEX($1:$65535,1,1):INDEX($1:$65535,255,1))-1)*99)

これを使えば配列入力式：

=SUM(IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",REPT(" ",99)),seq_99,99))),--TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",REPT(" ",99)),seq_99,99))))

に配列入力数式を入力するには、ctrl+ をshift押しながらを押しますenter。これを正しく実行すると、Excel によって{...}数式が中括弧で囲まれ、数式バーに表示されます。

は負の数を表していると想定しました-。そうでない場合は、数式を変更してそれを除外するのは簡単です。

答え4

小さな VBA 関数を提案したいと思います。区切り文字は以下のように調整できることに注意してください。

Function SumNumbers(rngS As Range, Optional strDelim As String = "-") As Double

    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)

    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum

End Function

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

ノート：

  • この関数を通常の方法で使用します。=SumNumbers(セルアドレス)。
  • 区切り文字がスペースの場合は、strDelim 文字列 = " "引数リスト内。
  • 区切り文字がカンマの場合は、strDelim を文字列として = ","引数リスト内。

これがお役に立てば幸いです。

関連情報