Excel2010 sumifs関数で、結合セルの「0」と認識される個所を参照する方法を教えて下さい

Excel2010で、Sumifs関数を使って、2つの条件に一致するセルの合計値を出そうとしています。

「条件範囲1」（Ａ列）の範囲に、図のような結合セルが含まれており、
「条件範囲2」（B列）の「実績」に該当する数値を合計したい場合、
どのような数式を組めばよいでしょうか。

=SUMIFS($C$2:$C$7,$A$2:$A$7,B13,$B$2:$B$7,C13)

上記の数式の場合、「条件2」を「実績」にすると、
「条件範囲1」は結合セルの下側のセル「0」を参照してしまい、うまく計算できません。
条件範囲1にOFFSET関数を使用し、1行下を参照させようとしましたが、解決できませんでした。

わかる方、教えて下さい！

No.3 ベストアンサー 回答者： tom04 回答日時： 2017/01/31 10:47

こんにちは！

No.2さんがおっしゃっているように集計などを行う場合、極力セルの結合は避けた方が良いと思います。
どうしてもセル結合があってもやりたいときは
No.1さんの回答のように「条件範囲」と「条件」の行をずらすことで可能になります。
ただ結合セルがもっと多い場合は間違いの元ですので、
↓の画像のように作業用の列を設けてみてはどうでしょうか？

画像では作業列D2セルに
=IF(A2="",D1,A2)
という数式を入れフィルハンドルで下へコピー！
H2セルに
=SUMIFS(C:C,D:D,F2,B:B,G2)
として下へコピーしています。m(_ _)m

この回答へのお礼

回答ありがとうございます。
ただ単にD列に同じデータを手入力するのではなく、IF関数でA列が空白データだった場合の入力を自動化しているのですね。勉強になります。
D列を非表示にすれば、見た目も綺麗になりますね。ありがとうございます！

お礼日時：2017/02/02 01:38

No.5 回答者： yuji3690 回答日時： 2017/02/03 00:06

データは同じくA2~C7にあるとして、E2~に条件１をF2~に条件２を入力し、G2~に合計を計算するとします。

G2に
=SUMPRODUCT((A$2:A$7=E2)*1,(INDIRECT("B"&MATCH(F2,B:B,0)&":B"&COUNTA(B$1:B$7)+MATCH(F2,B:B,0)-2)=F2)*1,INDIRECT("C"&MATCH(F2,B:B,0)&":C"&COUNTA(B$1:B$7)+MATCH(F2,B:B,0)-2))
と入力し、必要なだけコピーしてください。
データの範囲は自分で変更してください。
内容としてはほぼ同じなのですが、１つの結合セルが3行以上にわたる場合、条件範囲１を上にずらすと範囲外になり選択できなくなるので、条件範囲2と合計対象範囲を下にずらすことにしました。

G2の説明として
A2~7でE2に一致し、
B列の条件2がB列で一致する一番上の行（予定なら2、実績なら3　以降①とします）～B列に入力されているデータ（1行目含む）の数（この場合7）+①-2（つまり予定の場合7+2-2=7、実績の場合7+3-2=8　以降②とします）行でF2に一致する
という条件に当てはまる行の、
C列の①～②に入っているデータを合計したものを表示します。

長いですね…
これで条件が増えたりしても、各データが同じ行数ずつ入っている限り大丈夫です。
予定・実績・X・予定・実績・X・予定…といった感じで。

範囲をずらす式作るのに、なかなか思った式で作れなくて苦労しました（汗）

この回答へのお礼

お礼が遅くなりすみません。
やはり、範囲をずらすのが良いやり方のようですね。
ありがとうございます！

お礼日時：2017/03/05 09:11

No.4 回答者： d-q-t-p 回答日時： 2017/01/31 17:16

表に計算を合わせるんじゃなくて 計算に表を合わせるべきかと思います。

自分で計算できないなら特に。

=SUMIFS(C2:C7,A1:A6,B13,B2:B7,C13)

この回答へのお礼

回答ありがとうございます。1行ずらすやり方ですね。

＞表に計算を合わせるんじゃなくて 計算に表を合わせるべきかと思います。

頭が固くてなかなかそのような考え方にたどり着けませんでした。
ありがとうございます。

お礼日時：2017/02/02 01:38

No.2 回答者： chonami 回答日時： 2017/01/31 09:33

結合やめちゃったらいいんじゃないですかね？

集計に使う表で結合するとか意味がわからないです。

たまにあるんですけど、「上司の命（趣味）で仕方なく…」みたいな事情があるなら、
一旦結合解除→空白セルをうめる→別のセルで結合セルを作る→そのセルをコピー→実際に結合したいセルに書式のみ貼付

で、見た目は結合でもどのセルにもデータが入った状態になります。

この回答へのお礼

回答ありがとうございます。
下記の方法は知らなかったです！こんな方法もあるのですね。

一旦結合解除→空白セルをうめる→別のセルで結合セルを作る→そのセルをコピー→実際に結合したいセルに書式のみ貼付

結合しないのが一番なのですが、見栄え上、結合したかったのです。
ありがとうございます。

お礼日時：2017/02/02 01:29

No.1 回答者： yuji3690 回答日時： 2017/01/31 02:21

SUMIFS($C$2:$C$7,$A$1:$A$6,B13,$B$2:$B$7,C13)

とすれば実績のみの合計を表示することはできます。
ただし、この場合条件2がどちらか分かった上で式を選ばないといけません。
両方の場合に同じ式で計算できるようにするのならば、A列の範囲を指定するところで、条件2が条件範囲2の何番目か(この例では1か2だけですが)を判定し、それに応じてA列の指定範囲を変更させる必要があります。
中身がややこしくなるので問題なければ先に書いた方法をお勧めします。
必要なら後で書いた方法の式も書きますが。

この回答へのお礼

1行目のやり方でうまくいきました！1行ずらすだけで解決できるなんて思っていませんでした。ありがとうございます。

今後、条件が変わることもあるので、判定の方法も教えていただけますか？
よろしくお願いします！

お礼日時：2017/02/02 01:38