☰

セル内の複雑な合計値

tag-icon • tag-icon microsoft-excel worksheet-function
セル内の複雑な合計値

これをテキストとして返す数式の結果があるとします+10+5+1+40。それらの数値を合計して合計を算出したいとします。

結果は任意の数の値になる可能性があるので、 または のようになり+2+5ます+10+5+7+1+8。

参考までに、元のデータ セットはこれS10_S5_S1_S40またはこれのようなものですE_E_S2_S5。追加したいのは S の横の数字だけなので、それ以外のものはすべて削除しました。

最後に、できれば VBA を避けたいので、スーパーユーザーに投稿しています。

アップデート

この式により： =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D25,"E",""),"_",""),"S","=",1),"S","+")

=10+5+1+40これをD25 = ...まで下げましたがS10_S5_S1_S40、これは実際には前に があるのと同じだと+思います。

答え1

例のように1桁または2桁の数字しかないと仮定すると、この「配列数式」はD25のすべての「S」の数字を合計します。

=SUM(IF(MID(D25,ROW(INDIRECT("1:"&LEN(D25))),1)="S",(0&SUBSTITUTE(MID(D25,ROW(INDIRECT("1:"&LEN(D25)))+1,2),"_",""))+0))

確認済みCTRL+SHIFT+ENTER

D25にS10_S5_S1_S40それが含まれている場合、結果は56になります。

答え2

それを実行するワークシート数式はありません。古い Excel 4 マクロ関数 EVALUATE() を使用できますが、名前付き範囲でのみ使用できます。

たとえば、列Dにデータがあり、列Eに代替数式がある場合、F25を選択して名前マネージャーで新しい名前付き数式を作成します。「EvaluateMe」という名前を付けて、参照できるようにします。

=EVALUATE(Sheet1!E25)

次に、

=EvaluateMe

セル F25 に入力すると、結果が表示されます。この名前付き範囲は相対参照を使用しており、定義時にアクティブ セルが F25 にあった場合は、常に左隣のセルを評価しようとします。

答え3

これはバリーの「素晴らしい」答えから派生したものです。次の「配列数式」

=SUM(IF(MID(D25,ROW(INDIRECT("1:"&LEN(D25))),1)="S",MID(D25,ROW(INDIRECT("1:"&LEN(D25)))+1,IFERROR(FIND("_",D25,ROW(INDIRECT("1:"&LEN(D25)))+1),LEN(D25)+1)-(ROW(INDIRECT("1:"&LEN(D25)))+1))+0))

Ctrl( + Shift+で入力) は、長さに関係なく、Enterセル内のすべての「S」の数字を追加します。したがって、に「S10_S5_S1_S240」が含まれている場合、上記の数式では結果 256 が返されます。これは負の数も処理します。「S10_S5_S–1_S240」は 254 になります。D25D25

まだ徹底的にテストしていません。おそらく月曜日の朝に私自身も何か問題を見つけるでしょう。:)

関連情報