こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

EXCEL行挿入・行削除しても正しく集計する方法

例えばSUM関数で、1~10行目までが明細行で、11行目に合計行がある場合、明細の途中で1行挿入しても、自動的に集計範囲が変わってくれますが、最終明細行の下に行挿入した場合、集計範囲が変わってくれません。
 (1)SUM(C1:C10) → 明細途中に1行挿入 → SUM(C1:C11)
 (2)SUM(C1:C10) → 明細最後に1行挿入 → SUM(C1:C10)

(2)の場合でも、全明細行集計するようにする方法(関数)があれば教えてください。(Office365使用)
よろしくお願いします。

投稿日時 - 2019-05-07 19:33:58

QNo.9614487

困ってます

質問者が選んだベストアンサー

>(2)の場合でも、全明細行集計するようにする方法(関数)があれば教えてください。(Office365使用)
SUM関数の範囲をOFFSET関数で指定すれば良いと思います。
=SUM(OFFSET(C1,0,0,ROW()-1))
OFFSET関数の第4引数は高さを指定しますのでセルの挿入/削除を行ってもSUM関数が設定された直上のセルまで集計できます。
Excelの全てのバージョンで応用可能と思います。

投稿日時 - 2019-05-07 22:18:05

お礼

ご回答ありがとうございます。
このような場面でOFFSET関数が使えるんですね。
本当にありがとうございました。

投稿日時 - 2019-05-08 13:13:09

ANo.4

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(5)

ANo.5

=SUM(C1:OFFSET(C11,-1,0))
でいいと思います。


>11行目に合計行がある
んだから、
>最終明細行(10行)の下に行挿入
の表現は正しいとも思います。 (私は)

投稿日時 - 2019-05-08 10:11:41

お礼

そう言って頂けると助かります。
ありがとうございました。

投稿日時 - 2019-05-08 13:19:22

ANo.3

こんなやり方もある、ということで。
「テーブル化」の活用です。他にもメリットがあります。
ーー
説明例として、A列に、A1:A6に
データ
1
2
3
4
5
とあるとします。A1の「データ}は項目見出しです。
ーー
「テーブル化」(一般の意味ではなく、エクセルの術語としての意味)します。
・[ホーム]、[テーブルとして書式設定] の順に選択します。
・テーブルのスタイルを選択します。
・[テーブルとして書式設定] ダイアログ ボックスで、セル範囲を設定します。
A7に 合計を求めるため =SUM(A2:A6)をいれます。
A7を選択しておいて右クリック。
・挿入
・テーブルの行(上)
式は =SUM(A2:A7)に変化(自動で範囲拡張)します。
A7に数値を(例7)と入れると、A8は、22になります。
ーーー
頻繁に、色んな行で、行挿入があるときは、テーブル化しておくと良いようです。もちろん、途中行での行挿入は(上行に追加されることを念頭に行うと)問題(数式の範囲が拡がる)ありません。

投稿日時 - 2019-05-07 21:10:42

ANo.2

》 最終明細行の下に行挿入した場合
それ、「挿入」ではありません。「追加」ですよ。

投稿日時 - 2019-05-07 20:21:59

お礼

ご指摘ありがとうございます。
厳密にはそうかもしれませんね。
大変失礼しました。

投稿日時 - 2019-05-08 13:17:43

ANo.1

1から10行目まで合計するなら11行目を空白にして
11行目まで合計しましょう。
追加が出たら10行目と11行目の間に入れればきちんと合計できます。

投稿日時 - 2019-05-07 19:44:14

お礼

なるほど、そういう方法もありますね。
ありがとうございました。

投稿日時 - 2019-05-08 13:15:36

-広告-
-広告-

あなたにオススメの質問

-広告-
-広告-