エクセル／列追加時、合計行の計算式

図のような、日別の売上表を作成しております。
月合計の【個数】や【売上】を求める計算式をご教示頂けないでしょうか。

実際の作業としては、図にあるテンプレートが始めにあります。
売上のあった日（図の例は3月18日）に、3月15日のBCD列をコピーし、月合計との間に挿入しており、個数や単価を入力しております。

その際、月合計のH列12行目の【個数】に入れる数式ですが、『=B12』のままなので、
列を追加する毎に『＝B12+E12』と追加しており、その後は追加される毎に『＝B12+E12+
H12』と毎回入力する原始的なやり方をしております。
最終日には、電卓にて再計算をし一致してるかどうか確認をしておりますが、たまに計算式の追加を忘れたりするようで、合計がズレてることが発見されます。

もちろん、ひと月分の横長の表を最初からテンプレートにすれば良いという事もあるかと思いますが、
売上が無い列を削除したりするやり方以外で計算式でなんとかならないか知りたいです。

最後は確認の為に再計算を前提としますが、途中の計算式の追加の煩わしさをなんとかできないものでしょうか。
色々とネットにて調べてみたのですが、解決に至らず…。お力を貸して頂ければ幸いです。

質問者からの補足コメント

• 早々のご回答ありがとうございます。
  
  ご教示頂いた、計算式だと、範囲内の全てが足し算され、求めたい【個数】だけではなく、【個数】【単価】【売上】の合計値となりませんか？
  また、【売上】についての計算式は、範囲外となっており合計がエラーです。
  
  テンプレートの状態の際に、E列3行目に入れておく計算式は、＝SUM（B3）としておき、3/18の列を増やしても、自動計算はしてくれないようです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2023/03/15 11:45

• zongai様
  早々のご回答ありがとうございます。
  満足いく結果となり感謝しております。
  応用をしたく、色々と試してみました。
  ------------
  $B$11←ここは固定？
  $11:$11←結果を出したい行
  "個数"←ここの中の名前を変更（単価・売上）の合計もOK。
  ------------
  上記を元に、画像の表の場合、A～E社、合計に下記計算式を入れてみましたが、結果は0となり計算されませんでした。
  
  J列4行目セル内の計算式
  ----------------------------------
  =SUMIF($E$3:INDEX($4:$4,1,COLUMN()-1),"A社",INDIRECT(ADDRESS(ROW(),2)&":"&ADDRESS(ROW(),COLUMN()-1)))
  ----------------------------------
  
  なぜでしょう。

  
  No.2の回答に寄せられた補足コメントです。 補足日時：2023/03/15 14:54

No.7 回答者： goomania 回答日時： 2023/03/20 07:40

No.6です。

前回の回答に以下のとおり脱字がありました。大変失礼しました。お詫びして訂正いたします。

（誤）INDIRECT関数の「参照形式」の引数を0(通常は1)とし、1C1形式の表示を利用することで解決できます。

（正）INDIRECT関数の「参照形式」の引数を0(通常は1)とし、R1C1形式の表示を利用することで解決できます。

No.6 回答者： goomania 回答日時： 2023/03/19 22:53

zongaiさんの回答で解決していると思いますが、横から失礼します。

zongaiさんの回答はSUMIF関数、INDIRECT関数、ADDRESS関数を用いた数式ですが、SUMPRODUCT関数とOFFSET関数を用いて同じ効果をもつ数式を作成できます。

添付画像をご覧ください。J4セルに

=SUMPRODUCT((OFFSET($E$3,0,0,1,COLUMNS($E:$J)-1)=J$3)*(OFFSET($E4,0,0,1,COLUMNS($E:$J)-1)))･･･①

という数式を記述し、右方向、下方向へコピーしています。
これで、添付画像でご覧のように集計結果が表示されます。
その上で、合計欄の左側に5列挿入して、仮に3/18の実績を貼り付けしたとします。すると合計欄のA社の合計はO4セルになっているばずです。
このときO4セルの数式をみると

=SUMPRODUCT((OFFSET($E$3,0,0,1,COLUMNS($E:$O)-1)=O$3)*(OFFSET($E4,0,0,1,COLUMNS($E:$O)-1)))

という風に自動的に見出し範囲、合計範囲が拡張されています。つまり列を挿入しても問題なく集計できることが判ります。
※「コピーしたセルの挿入」で実行すると上記の自動拡張は行われないので注意。

zongaiさんの数式ではCOLUMN()-1として、「データ開始列から自分の列数の一つ左の列まで」を特定しようとしています。一方①ではCOLUMNS($E:$J)-１として「データ開始列からJ列の一つ左の列まで」として、数式の中で「J列」を固定してしまっています。
しかし、J列の左側に列を挿入するのであれば、数式中に「$J」を記述してもEXCELの機能で自動的に挿入分拡張されるというわけです。
なお、①の数式の前半のOFFSET関数では添付画像の青枠部分を、後半のOFFSET関数では赤枠部分を指定しています。後半のOFFSET関数では行を相対参照にして下方向へコピーしたときに赤矢印方向に範囲が移動するようにしています。

また、zongaiさんの数式と全く同じ意味の数式をADDRESS関数なしで作成できます。J4セルに、

=SUMIF(INDIRECT("R3C5:R3C"&COLUMN()-1,0),J$3,INDIRECT("R"&ROW()&"C5:R"&ROW()&"C"&COLUMN()-1,0))･･･②

という数式を記述することで、ADDRESS関数を省略できます。ADDRESS関数はADDRESS(行数,列数)で「$E$3」などのセル番地を返す関数ですが、行数、列数が判明している場合、INDIRECT関数の「参照形式」の引数を0(通常は1)とし、1C1形式の表示を利用することで解決できます。
つまりRの次に行数、Cの次に列数を(「E3」セルなら「R3C5」と)記述する形式を使うという方法です。

この方式はVBAでよく登場する「･･･Cells(行数,列数).･･･」といった表記方法と似ているため、VBAを扱う方は余り違和感を感じないものの筈ですが、そうでない方には馴染みのないものかも知れません。
このためzongaiさんは、より直感的で判り易いと判断してADDRESS関数を用いたのだと思います。

ただ、数式の長さでは①②がzongaiさんの回答のオリジナルの数式より若干短くなります。

No.5 回答者： zongai 回答日時： 2023/03/15 16:03

補足の添付画像に合わせて書き換えるならこうなります。

=SUMIF($E$3:INDEX($3:$3,1,COLUMN()-1),"a",INDIRECT(ADDRESS(ROW(),5)&":"&ADDRESS(ROW(),COLUMN()-1)))

わかりにくいのでこっちの式に変更提案。

=SUMIF(INDIRECT("E3:"&ADDRESS(3,COLUMN()-1)),J$3,INDIRECT("E"&ROW()&":"&ADDRESS(ROW(),COLUMN()-1)))

後半のコレは、足すセルを抽出する範囲。
INDIRECT("E"&ROW()&":"&ADDRESS(ROW(),COLUMN()-1))
「E列の現在の行」セル　～　「現在の列の1個前　の　現在の行」セル
※「現在の」は「数式の書かれている」というという意味で。

前半のコレは、キーワードを検索する範囲。社名の範囲の3行目。
INDIRECT("E3:"&ADDRESS(3,COLUMN()-1))
後半部分の行[ROW()]を「3」に固定したもの。

真ん中のコレは、検索キーワードあるセル
J$3

J4書いたセルを、フィルハンドルをドラッグするなど、J4:N8の範囲にコピーしてください。

ご自身で式を分析される方のようなので、使われてる関数を探れば内容も理解できるかな、と思います。

この回答へのお礼

zongai様

何度もご回答頂き感謝申し上げます。
列を追加しても、計算式はバッチリでした。
関数についての、ご説明もありがとうございます。
まだまだ勉強は必要となりますが、なんとなく理解できました。
大変ありがとうございました。

これより、もう少しだけ、計算式が正しいか検証してみたいと思います。
取り急ぎお礼まで。

お礼日時：2023/03/15 17:54

No.4 回答者： zongai 回答日時： 2023/03/15 12:44

添付画像のように、

合計だけを別に抜き出しておけば、
このようなシンプルな式で運用できますが…

日々コピペ、のスタイルでの運用は変えられないのかな？

この回答へのお礼

zongai様

このようなご提案も頂きありがとうございます。
これもありですね！
考えが古いのか、時系列で見て行き、同じ目線上に答えがないとしっくりこなくて、別での抜き出しは考えてませんでした。
折角、教えてもらったので、横の集計とご提案のあった別に抜き出すパターンの表を早速作成してみたいと思います。
ありがとうございました。

お礼日時：2023/03/15 17:42

No.3 回答者： fujillin 回答日時： 2023/03/15 12:26

こんにちは

最初のテンプレートの状態で、E3セルに
　=SUMIF(OFFSET($C$2:E$2,,-1),$E$2,OFFSET($C3:E3,,-1))
の式を入れて、E3:E5にフィルコピーしておけばよさそうですね。

＞3月15日のBCD列をコピーし、月合計との間に挿入しており～
質問者様がこの操作を行うのならまだ良いですが、人に任せるといろいろな方法で操作してしまいそうです。
例えば、挿入するのではなく「E:G列を右側にコピーしてから、元のE:G列に新しいデータを入力する」とか
他にもいろいろと考えられますので、他人が操作する場合は、操作方法によっては計算結果が正しくなくなることも考えられます。
直接E:G列に新しいデータを上書きして、その右にご提示のように『＝B12+E12』の式を追加しておくとかだと元に戻ってしまいますよね？

ですので、もしも操作が一定していない環境であるなら、そのあたりに十分な工夫がないと、せっかく計算できるようにしても意味がなくなる可能性がありそうな気がします。

この回答へのお礼

fujillin様

早々のご回答感謝致します。
求めてた通りの結果となりました。
そして、ようやく、計算式の解読ができ列が増えた時も計算ができました！
大変勉強になります。簡単な感じでいいですね。
OFFSET関数については、まだまだ勉強しなければなりませんが（汗）

補足にてzongai様にあげた画像の表だと、下記の計算式ですよね？

A社
=SUMIF(OFFSET($F3:J$3,,-1),J$3,OFFSET($F4:J4,,-1))
B社
=SUMIF(OFFSET($F3:K$3,,-1),K$3,OFFSET($F4:K4,,-1))

今後はこのような表があったら、使っていきたいと思います。

そして、懸念されております、「他人が操作」ということですが、
自分しか使用しないので大丈夫です。

すごく勉強になりました。
また、何かあれば教えて下さいませ。

お礼日時：2023/03/15 17:31

No.2 回答者： zongai 回答日時： 2023/03/15 11:50

H12にこの式を入れて、下にコピーでどうでしょうか。

=SUMIF($B$11:INDEX($11:$11,1,COLUMN()-1),"個数",INDIRECT(ADDRESS(ROW(),2)&":"&ADDRESS(ROW(),COLUMN()-1)))

No.1 回答者： よろしくです。。。。。 回答日時： 2023/03/15 11:17

売上表から月合計の個数や売上を求めるための計算式は以下のようになります。

【個数】の場合：=SUM(B12:H12)

【売上】の場合：=SUM(I12:P12)

これらの計算式を、月合計の行に入力することで、自動的にその月の合計値が計算されます。

また、列を追加する場合にも、列の挿入後に計算式を自動的に適用する方法があります。具体的には、以下の手順を行ってください。

月合計の行に、列を追加したい場所に挿入する。
追加した列に、直前の列の計算式をコピーする。たとえば、列Fを追加した場合は、列Eの計算式をコピーする。
追加した列の計算式を、追加した列のセルに貼り付ける。
追加した列の計算式内のセル番号を、追加した列のセルの位置に合わせて修正する。
これらの手順を行うことで、新しい列の計算式が自動的に適用されるようになります。