[[20150916132548]]　『SUM関数で範囲をずれないようにするには？』（あやころ）　ページの最後に飛ぶ

『SUM関数で範囲をずれないようにするには？』（あやころ）

現在、売上管理表のデータを作成しています。
A列には品目、B列には売上金額を入力していて、
B46のセルに=SUM(B2:B45)と入力して、
B2:B45の売上合計がB46のセルに出るようなデータを作ってます。

このデータを会社のサーバーフォルダで数人で共有しており、
B2:B45の範囲には下方向へ行やセルを挿入したり、行を削除したりして、売上データを随時更新しています。
そのため、売上金額の範囲の行数は常に変動します。

ここでB2にセルを挿入し下方向にシフトした場合、売上合計の数式が=SUM(B3:B46)となってしまいます。
B2にセルを挿入し下方向にシフトした時に売上合計がB2:B46の合計になるようにするにはどうしたらいいでしょうか？

また、B46にセルを挿入し下方向にシフトした場合は、売上合計の数式が=SUM(B2:B45)となってしまいます。
B46にセルを挿入し下方向にシフトした時に売上合計がB2:B46の合計になるようにするにはどうしたらいいでしょうか？

教えてください。

よろしくお願いします。

＜使用 Excel：Excel2007、使用 OS：Windows7 ＞

 =SUM(INDIRECT("B2:B"&ROW()-1))
 でどうでしょうか？
（se_9） 2015/09/16(水) 13:50

 テーブル機能を使われたらどうでしょうか？
（稲葉） 2015/09/16(水) 17:09

(se-9)さん、ありがとうございました。
希望していたことができるようになりました。
（あやころ） 2015/09/18(金) 07:29

(稲葉)さん、回答ありがとうございます。
テーブル機能を使うとその後の集計や編集が使いにくいと言われた為、今回は活用しませんでした。ありがとうございました。
（あやころ） 2015/09/18(金) 07:33

 ＞テーブル機能を使うとその後の集計や編集が使いにくいと言われた為、
 逆にしやすくなると思うけど・・・仕方ないね。
（稲葉） 2015/09/18(金) 08:19

追加で質問させてください。

上記のデータとは別に現場別の労務管理表のデータを作成しています。

Ａ列に連番、Ｂ列に現場名、Ｃ列に階数（１Ｆ、２Ｆなど）、Ｄ列に業者名、Ｅ列以降にに必要労務数を月ごとに入力しています。（E列に４月、F列に５月、G列に６月・・・）

このデータも会社のサーバーフォルダで数人で共有しており、
E列以降の列では下方向へ行やセルを挿入したり、行を削除したりして、データを随時更新しています。
そのため、E列以降の列の最終行数は常に変動します。

現在
E2：E45→○○工場の現場の４月の必要労務数
E46　　→○○工場の現場の4月の必要労務数の合計
E47:E80→△△ビルの現場の４月の必要労務数
E81　　→△△ビルの現場の4月の必要労務数の合計

が入力されています。

(SE-9)さんの方法で
E46に=SUM(INDIRECT("E2:E"&ROW()-1))
E81に=SUM(INDIRECT("E47:E"&ROW()-1))を入力すると、

E2にセルを挿入し下方向にシフトしても、E82の数式は=SUM(INDIRECT("E47:E"&ROW()-1))となってしまうため、△△ビルの現場の労務数の合計が合わないのです。
E2にセルを挿入し下方向にシフトしても、△△ビルの現場の労務数の合計が出るようにするにはどうしたらいいでしょうか？
（あやころ） 2015/09/21(月) 00:09

 合計行には何か「この行は合計行」とわかる目印は無いですか？

 Ｅ１セルが　数値では無い　として、
 例えばその目印が　合計　であり、それが仮に　Ｄ列　にあるとすれば、

 =SUM($E$1:INDEX(E:E,ROW()-1))-SUMIF($D$1:INDEX(D:D,ROW()-1),"合計",$E$1:INDEX(E:E,ROW()-1))*2

 　合計　の前後に都度変わってしまう文字列が含まれる場合はワイルドカードを使って、
 "合計"　→　"*合計"　や　"合計*"　や　"*合計*"　へ変更。

 というようなことで対処できると思いますが、どうでしょう？

（とっても初心者） 2015/09/21(月) 09:47

 Ｄ列に業者名が入力されているとのことですが、行挿入前で例えば

 E2セルからE45セルはA
 E47セルからE80セルはB

 みたいにある範囲内には同じ業者名が入っていますか？
（se_9） 2015/09/24(木) 09:37

 例。

 B9 =SUMIF(A$1:A9,"<>"&A9,B$1)-SUMIF(A$1:INDEX(A:A,ROW()-1),A9,B$1)
 各合計行へコピー。

 ※合計行のA列は同じ文字列で。
（GobGob） 2015/09/24(木) 12:11

（とっても初心者）さん、ありがとうございます。
教えていただいた方法で対処できました。
（あやころ） 2015/09/25(金) 01:47

（se_9）さん、

 範囲内には同じ業者ではなく、いろいろな業者が入ってます。

たとえば

 E2セルからE45セルはAとBとC
 E47セルからE80セルはBとCとD

のような感じです。

（あやころ） 2015/09/25(金) 01:50

（GobGob）さん、ありがとうございます。
教えていただいた方法でも対応できました。

後学のために知りたいのですが、

SUMIF(A$1:A9,"<>"&A9,B$1)の部分で合計範囲を
B$1:B9とせずにB$1だけで合計範囲を指定できるのはなぜでしょうか？

教えてください。

よろしくお願いします。
（あやころ） 2015/09/25(金) 02:01

 SUMIF関数の場合は合計範囲がどのような範囲でも合計範囲の一番左上のセルを基点として検索範囲と同じ広さに補正される。
 (検索範囲より大きい場合は狭まり、検索範囲より小さい範囲は広がる)

 なので基点になるセルだけを指定するだけで検索範囲に対応する範囲が合計範囲となる。

 ただし、2007で追加された複数条件に対応したSUMIFS関数の場合はそれぞれの検索範囲と合計範囲は全て同じ広さでないとエラーになる。
（ねむねむ） 2015/09/25(金) 09:54

（ねむねむ）さん、ありがとうございます！

SUMIF関数にそのような機能があるとは知りませんでした。
大変勉強になりました。
（あやころ） 2015/09/26(土) 06:47

また追加で質問させてください。
過去ログを見て、参考にして上記の売上データとは別で下記のようなデータを作成してます。

 　　Ａ　　Ｂ　　　Ｃ
 １　店舗　商品名　売上金額
 ２　Ａ店　○商品　5000
 ３　Ａ店　×商品　2000
 ４　Ｂ店　○商品　1000
 ５　Ｂ店　×商品　7000
 ６　Ｃ店　○商品　8000
 ７　Ｃ店　×商品　6000
 ８　合計　○商品　？？??@
 ９　合計　×商品　？？??A

 ？？??@には =SUMPRODUCT((B2:B7="○商品")*SUBTOTAL(9,INDIRECT("C"&ROW(2:7))))
 ？？??Aには =SUMPRODUCT((B2:B7="×商品")*SUBTOTAL(9,INDIRECT("C"&ROW(2:7))))
 を入れて、オートフィルタでＡ店・Ｂ店のみを表示した時には
 ２店舗だけの商品の売上合計を出すようにしました。

これも2行～８行目に行を追加したときでも

 ？？?@には =SUMPRODUCT((B2:B7="○商品")*SUBTOTAL(9,INDIRECT("C"&ROW(2:7))))
 ？？?Aには =SUMPRODUCT((B2:B7="×商品")*SUBTOTAL(9,INDIRECT("C"&ROW(2:7))))
となってしまいます。

行を追加・削除したら、自動で合計範囲も拡大・縮小するにはどうしたらいいでしょうか？

 良い方法をご教示お願いします
（あやころ） 2015/09/26(土) 07:11

コメント返信：

[ 一覧(最新更新順) ]

[[20150916132548]] 『SUM関数で範囲をずれないようにするには？』（あやころ） ページの最後に飛ぶ

[[20150916132548]]　『SUM関数で範囲をずれないようにするには？』（あやころ）　ページの最後に飛ぶ