質問: excel2010 sumifs関数で、結合セルの「0」と認識される個所を参照する方法を教えて下さい excel2010で、sumifs関数を使って、2つの条件に一致するセルの合計値を出そうとしています。 「条件範囲1」(a列)の範囲に、図のような結合セルが含まれており、 「条件範囲2」(b列)の「実績」に該当する数値を合計したい場合、 どのような数式を組めばよいでしょうか。 =sumifs($c$2:$c$7,$a$2:$a$7,b13,$b$2:$b$7,c13) 上記の数式の場合、「条件2」を「実績」にすると、 「条件範囲1」は結合セルの下側のセル「0」を参照してしまい、うまく計算できません。 条件範囲1にoffset関数を使用し、1行下を参照させようとしましたが、解決できませんでした。 わかる方、教えて下さい!
回答: データは同じくa2~c7にあるとして、e2~に条件1をf2~に条件2を入力し、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)) と入力し、必要なだけコピーしてください。 データの範囲は自分で変更してください。 内容としてはほぼ同じなのですが、1つの結合セルが3行以上にわたる場合、条件範囲1を上にずらすと範囲外になり選択できなくなるので、条件範囲2と合計対象範囲を下にずらすことにしました。 g2の説明として a2~7でe2に一致し、 b列の条件2がb列で一致する一番上の行(予定なら2、実績なら3 以降1とします)~b列に入力されているデータ(1行目含む)の数(この場合7)+1-2(つまり予定の場合7+2-2=7、実績の場合7+3-2=8 以降2とします)行でf2に一致する という条件に当てはまる行の、 c列の1~2に入っているデータを合計したものを表示します。 長いですね... これで条件が増えたりしても、各データが同じ行数ずつ入っている限り大丈夫です。 予定・実績・x・予定・実績・x・予定...といった感じで。 範囲をずらす式作るのに、なかなか思った式で作れなくて苦労しました(汗)