shinshimashimaの日記： EXCEL クロス集計の小計の交点とSUBTOTAL 5
日記 by
shinshimashima
EXCELで集計する場合で途中で小計挟む場合、SUBTOTAL(9,ほげほげ)使えば楽なんだけど
縦横で両方向に集計する場合、交点が困る。
例えば、年間の交通事故を横を月、縦に都道府県で集計するとして、
途中に四半期ごとと地方ごとの小計を挟むとする。
この場合、SUBTOTAL使えば総計ところで小計行飛ばしてくれるから助かるんだけど
交点、つまり「近畿地方の1月～3月」みたいなところがオートフィルだと集計対象がSUBTOTALのみになって0になって、
そこだけ手動で直す必要があって、結構数あるんで手間だ。
テーブル使う手も人が見るための表だとセル結合必須なので使い辛い。
なんかいい手ないかなぁ
SUMじゃだめ (スコア:1)
書き方が悪かったかな
縦方向、横方向の途中に小計が挟まるので、そこはSUMだとやってられんのです。
片方向だけ取り出すと
京都府、大阪府、奈良県・・・近畿計、香川県、徳島県・・・四国計・・・全国計
みないな感じです。場合によっては大阪府の中に大阪市、堺市・・・があったりする。
というか単純な縦横集計だったらオートSUMボタン一発なのです。
（テーブルかフィルター有効時だとオートSUMがにSUBTOTALになるけど、総計部分が0になる）
Re: (スコア:0)
そのつもりで書いていますよ。ある列もしくは行に存在する項目の全てについて、いずれかの小計に1回だけ含まれているのなら、その列もしくは行の合計は項目の合計の2倍になります。
ただ、都道府県以外に市町村まで入っていると、列の合計が項目の合計の2倍（項目の合計+小計の合計）とはならないので、面倒になりますね。
そうであれば、行方向をSUMで行けばいいかもしれないです。年の合計を四半期の小計の合計にするとか。
列方向の集計にはSUBTOTALを使わない (スコア:0)
稚拙ではありますが、列方向の集計に関しては小計をSUMにして、合計はSUMを2で割れば良いかと。交点はどちらでも良いですが、列で式を合せると警告を回避できます。
Re:列方向の集計にはSUBTOTALを使わない (スコア:1)
私も自分だけの集計なら「SUMを2で割る」にすると思いますが、
他人が見る可能性があるなら、数式を見ても意図は理解して貰えない可能性が高いでしょうから、
合計欄はSUMIFで見出し列が小計になってるものを除外して足すかな。
例えば、1行目が見出しでB～Pに各月と小計が入ってるなら、
合計のQ列は SUMIF($B$1:$P$1,"<>小計",$B2:$P2) って感じで。
SUM(B2:P2)/2+N("小計も合算して2倍になってるので、2で割ると小計を除外できる")
みたいにきっちりコメントを残してもいいけど、これでコメントになるというのも
初見だと意味不明なバッドノウハウ
Re: (スコア:0)
確かにバッドノウハウですし、その方法が適切だと思います。でも、うちの職場だと、この方が計算の内容が分かるので理解が早いです。SUMIFでさえ、みっちり説明しないといけないので、個別に範囲指定して済ませてしまうことが多いですかね。