この表の列合計を出すにはどんな式を書けばいい？

質問

（画像はGoogle Docsですが対象はoffice2003です)

こんなデータがあり（実際はもっと複雑）、「4/1」かつ「A」の列の合計を出したいのですがどんな関数を組み合わせればいいでしょうか？ 画像に入れ忘れましたが値が空のセルもあります。1行目は月ごとにセル結合しているのでsumproductだけではうまく行きませんでした。皆様ならどのような式を書きますか？

回答No.2

chie65535 · ベストアンサー

単純に4/1のAクラスの合計を取るのではなくて、B11、B12に入れた値に対応した合計を出したい、って事かな？

だとすると

=SUM(INDIRECT(CHAR((B11=A1)*1+(B11=C1)*3+(B11=E1)*5+(B11=G1)*7+(B12="A")*0+(B12="B")*1+64)&"3:"&CHAR((B11=A1)*1+(B11=C1)*3+(B11=E1)*5+(B11=G1)*7+(B12="A")*0+(B12="B")*1+64)&"8"))

って感じの式になります。

B11、B12の値に応じて「A2:A8」とか「B2:B8」とか「C2:C8」とかって文字列を作成し、その文字列をINDIRECT関数で「セル参照」に変換して、そのセル参照をSUM関数で合計しているだけです。

手抜きなので、列名は「A～Z」までしか対応していません。列名が「AA」以降になる場合は、もう一工夫が必要です。

お礼

助かりました、ありがとうございました。皆様のものが参考になったのですが考え方がわかりやすかったのと早かったのでBAはこちらへ。
おかげさまで皆様の意見を合わせてやった結果、自分がわかりやすいものとして下の式ができました。

B13セルへ以下を入力
=sum(offset(indirect(address(3,match(B11,A1:J1,0),1)),0,IF(B12="A",0,1),6,1))

この式にまた評価をいただきたいところですが(^^;
早くに書き込んでいただいたchie65535さん「Indirect+Sum」の考え方が参考になりました。ありがとうございました。
いろいろなアプローチがあってとても勉強になりました。

回答No.5

keithin

簡単な方法：
今の表のＡ列に１列空列を挿入して表をＢ列からスタートしてＩ列までの配置にしておき
Ｃ１１に日付
Ｃ１２にＡorＢ
があるとすると
Ｃ１３に
=SUMPRODUCT((A1:H1+B1:I1=C11)*(B2:I2=C12)*B3:I8)

メンドクサイ方法：
今の表の配置のままＡ列から表を配置
Ｂ１１に日付
Ｂ１２にＡorＢ
があるとすると
Ｂ１３に
=SUMPRODUCT((SUBTOTAL(9,INDIRECT("R1C"&FLOOR(COLUMN(A1:H1)-1,2)+1,FALSE))=B11)*(A2:H2=B12)*A3:H8)

お礼

こういったアプローチもあるんですね、なるほど。あとできちんと解読してみます。ありがとうございました。BAにできずすみません。勉強になります。

回答No.4

KURUMITO

例えばB13セルには次の式を入力します。

=IF(COUNTIF(1:1,B11)=0,"",IF(B12="A",SUM(INDEX(A:XX,3,MATCH(B11,1:1,0)):INDEX(A:XX,8,MATCH(B11,1:1,0))),IF(B12="B",SUM(INDEX(A:XX,3,MATCH(B11,1:1,0)+1):INDEX(A:XX,8,MATCH(B11,1:1,0)+1)),"")))

お礼

こういったアプローチがあるんですね。あとできちんと解読してみます。ありがとうございました。BAにできずすみません。

回答No.3

mu2011

一例です。
月日が結合セルされていては、列が確定できませんので結合を解除します。
結合解除が駄目な場合は読み捨て下さい。
（１）１行目を選択して結合を解除→選択されている状態でCtrl＋Gキー押下→セルの選択→
　　　空白を選択→ＯＫ→数式バーに=先頭セル（仮に=A1）を入力→Ctrl＋Enter
（２）B13に=SUM(OFFSET($A$3,,SUMPRODUCT((A1:H1=B11)*(A2:H2=B12)*COLUMN(A1:H1))-1,6))
　　　数式のセル範囲やOFFSET関数の第4引数（行数）はご例示を対象にしていますので
　　　調整して下さい。

お礼

とても参考になる式でした。ありがとうございました。BAにできずすみません。

回答No.1

白石 勇夫（@sunbravery）

単に合計だけでしたら
=SUM(A3:B8,C3:C8,E3:E8,G3:G8)

いかがでしょう。

お礼

助かりました、ありがとうございました。