【Excel】計算結果がゼロ以外の場合にのみ反映

質問

以下は表の構成上のことです（そのためなぜこのような計算方法をするのかということは聞かないでください）。

(1)パターン1
A1セル～A4セルに「ゼロ以外の数値」が入りA1～A4の合計がA5セルにでるが、A5が正か負の数値の場合にはA6セル～A9セルには必ず「ゼロの数値」が入る（A10セルにはA6～A9の結果が入るが当然結果はゼロ）。

(2)パターン2
パターン1とは逆に、A1セル～A4セルには「ゼロの数値」が入りA1～A4の合計がA5セルにでるが、A5が正か負の数値の場合にはA6セル～A9セルには必ず「ゼロ以外の数値」が入る（A10セルにはA6～A9の結果が入るが当然結果は正か負）。

このような入力パターン（条件）ですが、A11セルにはA10の合計結果が正か負の場合にのみ、その結果が反映させたいのです。その方法がありましたら教えてください。

回答No.11

mt2015

> エラーメッセージは「数式は隣接したセルを使用していません」です。

あぁ～、ハイハイ。
Excelのおせっかい機能（スマートタグ）ですね。
『A1からA9まで足している様ですが、A5が入っていませんよ。良いんですか？』とExcelがおせっかいにも警告してくれているだけでエラーではありません。

これを出さなくする方法は以下の通りです。
Excelのバージョンによって多少違いますが大体同じです（今回はExcel2010での操作を記します）。

メニューのファイル→オプション→数式　と展開し、エラーチェックルール内の「領域内のセルを除いた数式」のチェックを外します。

回答No.10

mt2015

> エラー表示がでてしまうのですが…これを回避する方法に「無視する」がありますが、この数式を含んだフォームをコピーする必要がありますのですべてエラーの含んだフォームとなってしまいます。

エラーは何でしょうか？
もし「＃Value!」と出ているのでしたら、A1:A4、A6:A9の何処かに文字列、おそらくスペースが入っている可能性があります。
スペースをなくすか、SUM関数を使った方の式なら＃Value!になりません。

補足

エラーメッセージは「数式は隣接したセルを使用していません」です。
たとえばA列に作ったフォームをコピーしてI列に利用しているためと思ってB列にコピーして計算してみましたがやはり同じエラーメッセージがでてしまいます。
「数式は隣接したセルを使用していません」という意味についてはよくわかりませんが、表計算といっても（列方向でも行方向でも）隣接したセル同士で計算をしなければならないというわけではありませんよね？隣接していないセル同士の計算もしなければならないので関数という機能も又あるのだと思いますが…

いずれにしろエラー（を示す緑の三角）を消せないでしょうか？

回答No.9

mt2015

> これまでの3つの条件どおりですが、今度はA1からA4の合計をA5、A6からA9の合計をA10に指定せずA11にその結果を出す場合を考えてみましたが、以下のとおりであっているでしょうか？

カッコが1つ足りませんが、以下の様に変更すればOKです。

=IF(((A1+A2+A3+A4=0)+A6+A7+A8+A9=0))>0,A1+A2+A3+A4+A6+A7+A8+A9,0)
↓
=IF(((A1+A2+A3+A4=0)+(A6+A7+A8+A9=0))>0,A1+A2+A3+A4+A6+A7+A8+A9,0)

ただ、合計するセルが多いのでSUM関数を使った方が見やすくなると思います。
=IF(((SUM(A1:A4)=0)+(SUM(A6:A9)=0))>0,SUM(A1:A4,A6:A9),0)

補足

ありがとうございます。
エラー表示がでてしまうのですが…これを回避する方法に「無視する」がありますが、この数式を含んだフォームをコピーする必要がありますのですべてエラーの含んだフォームとなってしまいます。
おおもとのフォームにてエラー表示をさせないように設定することはできますでしょうか？

回答No.8

mt2015

> この式を例とし「A11にはA5、A10の0では無い方の値を表示する」ということになるのかについて、具体的に数式の構造の説明をしていただけると理解が深まります。よろしくお願いいたします。

実際には「A11にはA5、A10の0では無い方の値を表示する」ではなく、「A5、A10のどちらか、あるいは両方が0の時、A5+A10の値を表示し、それ以外は0を表示する」としています。

式を説明します。
=IF(((A5=0)+(A10=0))>0,A5+A10,0)

式中の(A5=0)と(A10=0)は、論理式と言われているもので、例えばA5=0が成り立つ場合：1、不成立の場合：0　と言う値が返ります。
ですから、(A5=0)+(A10=0)は、A5、A10のどちらも0以外の時：0、どちらかが0の時：1、両方0の時：2と言うことになります。
IF関数を使い、((A5=0)+(A10=0))>0　が成立する（つまりA5、A10の片方、または両方が0）時、A5+A10の結果を表示し、それ以外は0を表示しています。

論理式を使うと式がすっきりして見やすいため当方は良く使いますが、初心者には却って解りにくかったかもしれません。

補足

難しいですねー。理論式は二進法のようなものでしょうか。
でもひとつ学びました。

応用といいますか、別の式を考えてみました。
これまでの3つの条件どおりですが、今度はA1からA4の合計をA5、A6からA9の合計をA10に指定せずA11にその結果を出す場合を考えてみましたが、以下のとおりであっているでしょうか？

=IF(((A1+A2+A3+A4=0)+A6+A7+A8+A9=0))>0,A1+A2+A3+A4+A6+A7+A8+A9,0)

つまりA1+A2+A3+A4=0はA5=0の部分を省略したものですがこのような別の方法でもこの理論式は成立しますか？
試してみたところ目的どおりになりましたが一応お墨付きをいただいきたいです。

回答No.7

mt2015

訂正
=IF(((A5=0)+(A10=0))>0,A5+A10,””）
　↓
=IF(((A5=0)+(A10=0))>0,A5+A10,0)

補足

試してみましたが成功しました。ありがとうございます。

関数は勉強中ですので蛇足質問ではありますが少々教えてください。

この数式の場合を単純に読めばA5=0、A10=0はA5もA10も0であるという条件ですが、それらの合計は0になります。

「0,A5+A10,0」もカンマで仕切られている0とA5と、0とA10の合計だと思いますが、>でこの結果は前者よりも小さい場合という条件になり今の理解力ではまったくこの数式がなぜこのような結果になるのか読めません。
またなぜ()が2つも括られているのかもわかりません。

この式を例とし「A11にはA5、A10の0では無い方の値を表示する」ということになるのかについて、具体的に数式の構造の説明をしていただけると理解が深まります。よろしくお願いいたします。

回答No.6

mt2015

ANo.3、ANo.5です。

補足拝見しました。
まだ説明に矛盾があるようなのですがこういう事で良いですか？

条件1：A5、A10のどちらか片方は0である。
条件2：A11にはA5、A10の0では無い方の値を表示する。
条件3：上記条件1が成立しないとき、A11には0を表示する。

=IF(((A5=0)+(A10=0))>0,A5+A10,"")

回答No.5

mt2015

ANo.3です。

> ご回答を試してみましたがひとつめの数値入力（また結果）のパターン2のみ成功しました。

質問では「A11セルにはA10の合計結果が正か負の場合にのみ、その結果が反映させたいのです」
と、ありますので補足で示されたパターン1の場合はA10＝0ですから表示なしが正しいのでは？

こちらから質問します。
セルA11の値はセルA5、A10の値だけで判断できますよね。
セルA5、A10の数値が以下の様になる事があるのか、あるならその時 セルA11には何を表示したいのか回答してください。

Q1.セルA5：10、セルA10：0
Q2.セルA5： 0、セルA10：20
Q3.セルA5： 0、セルA10：0
Q4.セルA5：-5、セルA10：-7
Q5.セルA5：-5、セルA10：10
Q6.セルA5：-5、セルA10：3
Q7.セルA5：-5、セルA10：0
Q8.セルA5： 0、セルA10：-7

補足

説明が難しいのでわかりづらくてすみません。

パターン1

A1➡0
A2➡0
A3➡0
A4➡1000
A5（A1～A4の合計）➡1000

A6➡0
A7➡0
A8➡0
A9➡0
A10（A6～A9の合計）➡0

A11➡1000（A5の結果）

パターン2

A1➡0
A2➡0
A3➡0
A4➡0
A5（A1～A4の合計）➡0

A6➡0
A7➡0
A8➡0
A9➡1000
A10（A6～A9の合計）➡1000

A11➡1000（A10の結果）

つまりA11にはパターン1または2の結果を反映させたいのです。

しかしパターンいずれかがゼロの結果の場合には一方のパターンには正か負の結果となるという条件（表の構成）ですから、A11に反映される結果は矛盾なく、ゼロ以外のパターンの結果が表示されることになります。

またご回答の関数ですとパターン2の結果（A10)が0のときには空白となりますが空白ではなく「計算結果」ですので「0」が表示されることが目的です。

パターン1を通常払い、2を立替と考えてください。
どちらか一方の支払の場合にはもう一方は入力しません。しかしそれぞれの明細項目の結果をA10にだし、A11に同値を反映させるというのが目的です。

回答No.4

panacon

条件付書式設定で次の値を含むで0指定ではなく、次の値に等しいで0指定にして、その時のフォントの色を白色、それ以外を黒色にしてみてください。

補足

できました。
ですが目的は、どちらか一方のパターンの結果がゼロかそうではないかという場合の表示が目的なのではないのです。
条件付き書式設定は参考になりました。ありがとうございます。

回答No.3

mt2015

A10に入るのは「正」や「負」といった文字列ではなく数値ですよね？
で、あればA11に↓これで
=IF(A10=0,"",A10)

補足

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

＞A10に入るのは「正」や「負」といった文字列ではなく数値ですよね？

はい。0以外の数値です。

ご回答を試してみましたがひとつめの数値入力（また結果）のパターン2のみ成功しました。

たとえばパターン1では以下のようになりますが

A1➡0
A2➡0
A3➡0
A4➡1000
A5（A1～A4の合計）➡1000

A6➡0
A7➡0
A8➡0
A9➡0
A10（A6～A9の合計）➡0

A11➡1000（A5の結果）

というように、A11にもA5の結果を反映させたいのです。
つまりA11には2つの入力パターンのひとつの結果が反映されるというのが目的です。

また質問に間違いがありました。

パターン2の

＞A5が正か負の数値の場合にはA6セル～A9セルには必ず「ゼロ以外の数値」が入る

「A5が正か負の数値の場合にはA6セル～A9セルには必ず「ゼロ以外の数値」が入る」に訂正します。

回答No.2

panacon

先の回答で白黒の設定が反対でした。
＞＞つまり、A11が0の時は、書式を黒色にし、それ以外の書式を白色にすれば事足りると思います。
＞＞A11が0の時は、白色。それ以外は黒色でした。

色で見えなくすることで数字は残っていますから、さらにA11の結果で何か計算することも可能となります。

補足

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

条件付き書式というのは面白い方法だと思いましたが当方、一度も使ったことがない機能ですので試してみました。
「新しい書式ルール」の「指定の値を含むセルだけを書式設定」で試してみました。「セルの値」では「特定の文字列」＞「次の値を含む」を「0」に設定し、「書式」の文字を白色で指定してみたところ、確かにA11の入力文字は消えましたが当然のことながら10などゼロが入った数値でも消えてしまいまい、ご回答者様の考えられる正しい設定ではないと思います。

具体的な方法をご教授くだされば幸いです。