Excelの除算の和の方法について

質問

Excelの関数を用いて計算ファイルを作成しています。

例えば、
=SUMPRODUCT(A1:G1/A2:G2)
とすれば、

=A1/A2+B1/B2+C1/C2+D1/D2+E1/E2+F1/F2+G1/G2
の意味となりますが、

「A1とB1」「A2とB2」を「セルを結合して・・・」数値を入れた場合、
結果は「#DIV/0!」となります。
結合した際の分母にくる「B2=空白=0」と認識しているためと推察しています。
これを改善して数値を出す方法はありますでしょうか。
計算式以外に文章等を入れ込んでいるため、セルを結合してセルの大きさを調整する必要があります。
これをしないと見た目が汚い書類になってしまいます。

ご指導の程、よろしくお願いいたします。

回答No.3

bunjii · ベストアンサー

>「A1とB1」「A2とB2」を「セルを結合して・・・」数値を入れた場合、
>結果は「#DIV/0!」となります。
> 結合した際の分母にくる「B2=空白=0」と認識しているためと推察しています。
そうなります。
複数のセルを結合すると値を入力できるセルは左上の1つのセルのみになり、その他のセルは未入力のセルとして計算する場合は数値の0と判断されます。

>これを改善して数値を出す方法はありますでしょうか。
Excelのバージョンが2007以降であればSUM関数の引数をIFERROR関数でエラーチェックし、エラーの部分を0に置き替えれば目的の値が返ります。
=SUM(IFERROR(A1:G1/A2:G2,0))
但し、数式の確定時にCtrl+Shift+Enterの打鍵で配列数式にしてください。
確定後の数式バーは下記のような表示になります。
{=SUM(IFERROR(A1:G1/A2:G2,0))}

お礼

ご返答いただきありがとうございます。

新規ファイルを作成し、適当に結合セルと数値を打ち込み、
ご指導いただいた方法で除算の和が行えるかトライとしたところ、できました。

そこで、目的のファイルに適用したところ、
「配列数列は結合セルでは使用できません。」と表示が出て、できません。
計算の適用範囲などを確認したりしたりしましたが、やはり駄目でした。

新規ファイルでシンプルなものだと成功し、目的のファイルだと失敗する状態です。
目的のファイルは、計算には関係ないと思いますが、
条件付き書式で一定数でセルの色が変わるようにしたり、
別のシートから参照するようにしています。

回答No.5

bunjii

>そこで、目的のファイルに適用したところ、
>「配列数列は結合セルでは使用できません。」と表示が出て、できません。
Excelのバージョンは？
当方ではExcel 2013で検証しましたが結合セルが含まれていても配列数式を使用できました。

お礼

ありがとうございます。
私も2013を使用しています。

作成中のファイル以外（新規ファイル）ですと問題なく計算されます。
新規ファイルでは、複数の結合セルを混ぜたもので試してます。

問題なく計算されたファイルに記入した関数をコピーし、
目的のファイルに貼り付けた後、計算範囲をカーソルで移動拡張（範囲の再設定）しました。
そうすると、記載しました「配列数列は・・・」が表示されます。

「/（除算）」のかわりに「*（乗算）」でしてみても同様に「配列・・・」と表示されます。

結合して一まとめにしているセルの数を変えるなど、色々とパターンを変えて原因の特定をしてみます。

回答No.4

masnoske

Excelの場合、1つのシートで入力も集計も報告書も片付いてしまいます。
これが長所でもあり短所でもあります。
使いやすいブックにするには、ちょっとしたコツがあります。
データ入力専用シートと見た目を整える出力シートを分けます。
そうすれば、データ入力であっちこっちバラバラのセルに入力することがなくなり、集計なども簡単になります。
見た目は出力シートで好きなように整えればOKです。

お礼

ご返答いただきありがとうございます。

回答No.2

kkkkkm

単純にB1/B2を無くせばいいのでしたら
=SUMPRODUCT(C1:G1/C2:G2)+A1/A2
など

お礼

ご返答いただきありがとうございます。

実際のファイルでは、結合セルが複数個複雑に入っている状態です。
ですので、記載した例のような単純なものでないため、
結合セルを一端除外して、他の和のみで行うことは困難（より複雑）になってしまいます。

回答No.1

aokii

セルを結合してしまうと、セルの引用は1つのみになりますので、セルを元のように分離しておく以外に、簡単に数値を出す方法はありません。計算式以外に文章等を入れ込む場合は、そのセルに書式設定で、以下のように、計算式以外に文章等を入れ込むことはできます。
0.00 "計算式以外の文章等"

お礼

ご返答いただき、ありがとうございます。