ある条件のもとで計算元としないための関数式

質問

セルA68に
=SUM(A24:A27,A36:A39,ABS(A52))
という数式が入ってます。
これを以下の条件に編集することはできますか？

A24～A27、A36～A39に入る数値がすべて0の場合にのみ、A52の数値を計算の対象としない（つまりA52は0として扱う）。

回答No.9

miso_kasu

--------ここから--------
=SUM(L23:L27,L35:L39,(SUM(L23:L27,L35:L39)<>0)*L52)
に変えましたが、まったく違った回答がでました。
--------ここまで--------
L23、L24、L25、L26、L27、L35、L36、L37、L38、L39、L52の値を提示してください。
また、まったく違った回答の値も提示してください。
尚、あなたが正しいとする値も提示して頂ければ何処に食い違いがあるか分かると思います。

--------ここから--------
＞L52（未入力）の0を乗じてください。
ということは何だったのでしょうか？
--------ここまで--------
回答No.5の補足では次の数式を提示していますのでその中でL52は未入力であると考えました。
=SUM(L23:L27,L35:L39,(SUM(L23:L27,L35:L39)<>0))
回答No.4では次の数式を提示させて頂きました。
=SUM(A24:A27,A36:A39,(SUM(A24:A27,A36:A39)<>0)*ABS(A52))
回答No.4の数式ではA列での計算例ですが回答No.5の補足ではL列での計算に置き換わっています。
回答→補足→回答→補足→回答のように繰り返すときに計算対象をA列からL列へ移り、更に、計算対象をA列へ戻すというQ&Aに混乱があるのかも知れません。

回答No.8

miso_kasu

>(2)A54には次の設定での回答を示します。
A68ではなかったのですか？
または別の列（L）にA列の相当する行番号に提示の数値を入力してL68セルへ目的の数式をコピペするのではないですか？
A52とL52の関連について説明がありませんので提示の数式で*0の発想が理解できません。

>ですが
>L52（未入力）の0を乗じてください。
>ということですので以下の式に直しました。
それが余分な修正です。
説明に不備が有ったでしょうか？
(1)で試されたA68セルをL68へコピペすれば良いだけです。
A68=SUM(A24:A27,A36:A39,(SUM(A24:A27,A36:A39)<>0)*ABS(A52))
　　　　　↓　コピペで次の数式になります。
L68=SUM(L24:L27,L36:L39,(SUM(L24:L27,L36:L39)<>0)*ABS(L52))
ABS(L52)はL52が未入力のとき0になりますので改めて*0を追記する必要はありませんし、A列用の数式へL列の特定セル（L52）を参照する意味が分かりません。

お礼

お返事が遅れて申し訳ありません。

質問のセルA68は回答を示すセルで間違いありませんが、試用シートでのセルのことでした。ですが他の参照先はすべてあってます。改めて、実際の回答先は以下となります。

補足の
(1)については実際にはA73に
(2)については実際にはA74に
それぞれ回答を示します。

A74に回答を示すための（1）の式は
=SUM(A24:A27,A36:A39,(SUM(A24:A27,A36:A39)<>0)*ABS(A52))

（２）の式は
=SUM(A23:A27,A35:A39,(SUM(A23:A27,A35:A39)<>0)*A52*0)
でよいのかと前回の補足で質問しましたが、

＞ABS(L52)はL52が未入力のとき0になりますので改めて*0を追記する必要はありません

という回答をここでもらいましたので（2）のことだと思い、先の補足で考えた

=SUM(L23:L27,L35:L39,(SUM(L23:L27,L35:L39)<>0)*L52*0)
を
=SUM(L23:L27,L35:L39,(SUM(L23:L27,L35:L39)<>0)*L52)

に変えましたが、まったく違った回答がでました。

＞L52（未入力）の0を乗じてください。
ということは何だったのでしょうか？

回答No.7

miso_kasu

回答No.6で補足の見逃しがありましたので訂正します。

>補足したエラーというのは（1円プラスされている）L52は設定していない場合についてです。
=SUM(L23:L27,L35:L39,(SUM(L23:L27,L35:L39)<>0))
　　　　　　　↓
=SUM(L23:L27,L35:L39,(SUM(L23:L27,L35:L39)<>0)*L52)
L52が未入力（数式の未設定も含む）の場合であっても*L52は必要です。
前回の回答で説明しましたが論理式の結果がTRUEのとき1になるためL52（未入力）の0を乗じてください。
Excelでは未入力セルの値は文字列として評価すると""（文字数0の文字列）になりますが数値として評価すると0になります。
従って、余分な修正をしたためにゴミが残ったものと思います。

補足

補足説明を具体的にしていなくてすみませんでした。
試してみたところ正常に計算できましたが、一応整理すると以下となります。最後の式で大丈夫だったでしょうか？

(1)A52には次の設定での回答を示します。

A23➡19,000円
A24➡1,600円
A25➡0円
A26➡1,000円
A27➡0円

A35➡0円
A36➡0円
A37➡0円
A38➡0円
A39➡0円

A52➡4,000円

(2)A54には次の設定での回答を示します。

A23➡19,000円
A24➡1,600円
A25➡0円
A26➡1,000円
A27➡0円

A35➡0円
A36➡0円
A37➡0円
A38➡0円
A39➡0円

(1)はご回答のとおりの
=SUM(A24:A27,A36:A39,(SUM(A24:A27,A36:A39)<>0)*ABS(A52))

にて、正常に計算されました。

(2)はA52に数値入力なしですので、この部分を抜いたわけですがそしたら1がプラスされてしまったわけですが、TRUEのときに1となるということで了解しました。
ですが
＞L52（未入力）の0を乗じてください。
ということですので以下の式に直しました。
=SUM(A23:A27,A35:A39,(SUM(A23:A27,A35:A39)<>0)*A52*0)

回答No.6

miso_kasu

>という設定です。つまり*ABS(A52)のみ抜きました。
何故*ABA(A52)を抜いたのですか？
質問の内容と異なることをしているため思惑通りにならないのです。
=SUM(L23:L27,L35:L39,(SUM(L23:L27,L35:L39)<>0))
　　　　　　↓
=SUM({19000;1600;0;1000;0},{0;0;0;0;0},(21600<>0))
　　　　　　↓
=SUM(21600,0,1) → 21601

Excelの計算では 21600<>0 → TRUE → 1 という仕様になっています。

>A24～A27、A36～A39に入る数値がすべて0の場合にのみ、A52の数値を計算の対象としない（つまりA52は0として扱う）。
「A24～A27、A36～A39に入る数値の何れかが0ではない場合」にA52を加算するのですよね？
今回提示の模擬データではA52の値が提示されていませんのであなたが必要とする数式を組み立てられません。
次のような数式を期待していたのでしょうか？
=IF(SUM(A24:A27,A36:A39)=0,0,SUM(A24:A27,A36:A39,A52)
今回の補足でA52を省いているのでしたら質問で提示した数式が無意味になりますので質問の内容を再確認してください。

回答No.5

miso_kasu

>元の値は間違ってません。どれも同じ結果になってしまいます。
>原因と対処の方法をご存知でしたら教えてください。
数式のみの提示なので原因を調査できません。
A24～A27、A36～A39、A52の模擬データを提示して頂ければ原因が分かります。
SUM関数はカッコ内に列記された値を合計しますので、あなたの思惑と異なる「1が加算される」要因が何処かにあると思います。

補足

補足したエラーというのは（1円プラスされている）L52は設定していない場合についてです。

=SUM(L23:L27,L35:L39,(SUM(L23:L27,L35:L39)<>0))

という設定です。つまり*ABS(A52)のみ抜きました。

事例なのですが、

A23➡19,000円
A24➡1,600円
A25➡0円
A26➡1,000円
A27➡0円

A35～A39➡0円

となります。つまりA28が合計21,600円となっているので元の値は正常なのですが、回答をだすA74には21,600円とならずに21,601円となっているのです。

ちなみに書式設定は#,##0"円"です。違うセルで試しても1円プラスされてしまいます。他に何もいじってません。どうしてでしょう…

回答No.4

miso_kasu

幾つかの方法があります。
その1つとして下記のように修正してみると良いでしょう。
=SUM(A24:A27,A36:A39,ABS(A52))
　　　　　　↓
=SUM(A24:A27,A36:A39,(SUM(A24:A27,A36:A39)<>0)*ABS(A52))

補足

いつもご回答ありがとうございます。

この式で試してみました。正常にできました。
しかしなぜかどれも計算結果が1プラスされてしまいます。
たとえば1000円という結果なら1001円というふうにです。
元の値は間違ってません。どれも同じ結果になってしまいます。
原因と対処の方法をご存知でしたら教えてください。

回答No.3

山田 太郎（@f_a_007）

【補足】論理式の計算について

質問者にとって、論理式を使った計算は馴染みがないかもしれないのでチョイと補足しておきます。添付図を一瞥されて下さい。

回答No.2

mdmp2

=SUM(A24:A27,A36:A39)+IF(COUNTIF(A24:A27,"<>0")+COUNTIF(A36:A39,"<>0")=0,0,ABS(A52))

ただし、A24:A27,A36:A39 に数値ではなく、文字が入っている場合もABS(A52)をカウントします。

回答No.1

山田 太郎（@f_a_007）

【お断り】Excelは一度も触ったことがないド素人の挑戦！

当然に見当違いだあったり、間違いだったり・・・。で、質問者もそのつもりで回答を眺めて下さい。

=SUM(A3:C3,D3:E3,ABS(F3))
　↓
=SUM(A3:C3,D3:E3,ABS(F3)*OR(A3:C3,D3:E3))

AND()があればOR()もあるだろう。0+0=0、1+0=1、1+1=1だから、OR()でいけるだろう。なんて考えて試してみたが・・・。初めての挑戦なので自信はない。