わえなび ワード&エクセル問題集

当サイトにリンクを張るのは自由です。WordとExcelの練習問題の動画を「Youtube」で無料公開しています。みんなで勉強しましょう!

【Excel】SUM関数の合計する範囲の始点や終点を固定するとどうなるか?

オートSUMで合計を求めることを覚えたら式の意味を考えてみましょう。例えば、セル範囲A1~A5の合計を求めるときには「=SUM(A1:A5)」と書きます。オートフィルをすると相対参照によって合計範囲が変わります。もし、合計範囲を絶対参照にして「=SUM($A$1:$A$5)」とすると、オートフィルをしても合計範囲が固定されますから、数式は変わりません。

では、合計範囲の始点だけを固定して「=SUM($A$1:A5)」とするとどうなるでしょうか?また、終点だけを固定して「=SUM(A1:$A$5)」とするとどうなるのでしょうか?そして、どのような場合に使えばよいのでしょうか?

そこで、今回はSUM関数の合計範囲の始点または終点を固定する手法について出題します。

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.合計する範囲の始点・終点を固定する

問題

次の図で、B列のデータを使って次の値を求めなさい。

(1)2041年からの累計
(2)2050年から過去2年分、3年分とさかのぼった累計

f:id:waenavi:20190825140453j:plain

 

解説

(1)始点の固定=上からの累計

オートSUMのボタンを押します。B2を選択します。

f:id:waenavi:20190825140902j:plain

 

この状態のまま、キーボードでコロン(:)を入力すると、「:B2」と表示されますが、表示されないときは、「:B2」と直接入力します。

f:id:waenavi:20190825140906j:plain

 

初めのB2だけ絶対参照にします。

f:id:waenavi:20190825140909j:plain

 

オートフィルをします。累計になります。

f:id:waenavi:20190825140913j:plain

 

範囲のはじめが絶対参照、終わりを相対参照にします。オートフィルをしてもはじめのB2は動きません。範囲の終わりだけ動きます。これによって合計する範囲が広がって、上からの累計になります。

f:id:waenavi:20190825140918j:plain

 

(2)終点の固定=下からの累計

先ほどと同じように、オートSUMのボタンを押します。今度は10年分の合計を求めます。

f:id:waenavi:20190825141220j:plain

 

ただし、範囲の終わりだけ絶対参照にします。

f:id:waenavi:20190825141228j:plain

 

オートフィルをします。どんどん減っていきます。初めは10年分の合計ですが、引き算されていきます。下から見ると累計になっています。

f:id:waenavi:20190825141232j:plain

 

範囲のはじめが相対参照、終わりを絶対参照にします。オートフィルをしてもB11は動きません。範囲のはじめだけ動きます。これによって合計する範囲が狭くなっていきます。

f:id:waenavi:20190825141237j:plain

 

2.SUMの絶対参照、複合参照

問題

次の図で、各中学校で学年別の人数の構成比率を求めなさい。

f:id:waenavi:20190825140540j:plain

 

解説

まずは西中学校だけを考えます。構成比率を求めるには合計を求める必要がありますが、計算式の中に入れることもできます。

f:id:waenavi:20190825141747j:plain

 

オートSUMのボタンを押します。人数の合計を出します。

f:id:waenavi:20190825141752j:plain

 

イコールの後に、「C2/」と入れます。これで、1年の人数を合計で割ったことになります。また、合計の範囲はすべて絶対参照にします。オートフィルをします。

f:id:waenavi:20190825141757j:plain

 

これで完成です。

f:id:waenavi:20190825141802j:plain

 

次に東中学校に計算式をコピーすることを考えます。このままコピーをすると合計の範囲が西中学校のまま動きません。

f:id:waenavi:20190825141805j:plain

f:id:waenavi:20190825141808j:plain

 

そこで、合計の範囲を、絶対参照から複合参照に変えます。合計する範囲が2行目から4行目に変える必要がありますので、行番号の固定を外し、列番号の固定だけにします。

f:id:waenavi:20190825141812j:plain

 

これでコピーをします。今度はうまくいきました。複合参照で行固定を外したことによってSUMの合計範囲が縦に動きます。

f:id:waenavi:20190825141814j:plain

 

SUM関数の絶対参照の式を 複合参照にするとき、下にコピーするときは列固定にします。

f:id:waenavi:20190825141817j:plain

 

また、横にコピーするときは行固定にします。

f:id:waenavi:20190825141820j:plain

 

3.行単位の累計

問題

次の図で、SUM関数を使って、実績の行の下に累計を求めたい。第1週(7日間)の実績で累計を求めなさい。また、第2~4週についても、それぞれの週で累計を求めなさい。

f:id:waenavi:20190825142249j:plain

 

解説

まずは、第1週だけを考えます。オートSUMのボタンを押します。コロン(:)を押して=SUM(C2:C2)とします。

f:id:waenavi:20190825142610j:plain

 

累計は範囲の始点だけを絶対参照にします。

f:id:waenavi:20190825142613j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190825142617j:plain

 

これを第2週にコピーします。計算式を確認します。C2 ではなく C6 が正しいはずですが、絶対参照にしているため、C2のままになっています。

f:id:waenavi:20190825142622j:plain

 

行の固定を外して、Cだけの固定にします。

f:id:waenavi:20190825142625j:plain

 

 

f:id:waenavi:20190825143039j:plain

 

オートフィルをし直します。

f:id:waenavi:20190825142627j:plain

 

コピーをします。今度はうまくいきました。

f:id:waenavi:20190825142630j:plain

 

4.複数行にまたがる累計

問題

さきほどの問題で、第1週の月曜日から第4週の日曜日までの、28日間で累計を求めなさい。

f:id:waenavi:20190825142249j:plain

 

解説

第1週は同じです。複合参照です。

f:id:waenavi:20190825142807j:plain

 

次に第2週を考えます。1つだけコピーしますこれに第1週の日曜日の累計を足します。これも複合参照でI列だけ固定です。

f:id:waenavi:20190825142809j:plain

 

オートフィルをします。コピーします。これで完成です。

f:id:waenavi:20190825142812j:plain

 

別解

足し算をする代わりに、SUM関数の中に入れることもできます。かっこの中に、カンマ、I4とします。I4は複合参照です。

f:id:waenavi:20190825142816j:plain

 

5.SUMをSUMでわる(加重平均)

問題

各小学校の平均身長と生徒数が次の表のとおりであるとき、4つの小学校を合わせたときの平均身長を求めなさい。

f:id:waenavi:20190825143331j:plain

 

解説

平均から平均を求めるときは必ず加重平均です。

f:id:waenavi:20190825143645j:plain

 

いったん平均から合計を求めて、それを割り算することで平均を求めます。各学校の平均身長に人数をかけます。これで各学校の身長の合計になります。

f:id:waenavi:20190825143647j:plain

 

オートSUMボタンで4つの合計を出します。

f:id:waenavi:20190825143650j:plain

 

これで合計を合計で割ります。これで完成です。

f:id:waenavi:20190825143654j:plain

 

この割り算を1つの式にすることもできます。身長の総合計を出します。このSUMの部分をコピーします。

f:id:waenavi:20190825143657j:plain

 

スラッシュを入力した後に、貼り付けます。

f:id:waenavi:20190825143700j:plain

 

後ろの範囲をC列に直します。

f:id:waenavi:20190825143705j:plain

 

これで完成です。

f:id:waenavi:20190825143710j:plain

 

6.SUMをSUMでわる(累計構成比)

問題

SUM / SUMで、売上の平均単価、売上金額の累計構成比をそれぞれ求めなさい。

f:id:waenavi:20190825143355j:plain

 

解説

単価と販売数をかけて売上金額を出します。

f:id:waenavi:20190825144309j:plain

 

この売上金額の合計を、販売数の合計で割ります。売上金額の合計を求めます。

f:id:waenavi:20190825144312j:plain

 

このSUMの部分をコピーします。スラッシュを入力した後に、貼り付けます。

f:id:waenavi:20190825144314j:plain

 

後ろの範囲をC列に直します。

f:id:waenavi:20190825144319j:plain

 

これで完成です。

f:id:waenavi:20190825144322j:plain

 

累計構成比は、累計を合計で割ります。累計は始点を固定した合計です。合計は全部固定した絶対参照です。

f:id:waenavi:20190825144325j:plain

 

オートSUMのボタンを押します。D2で、コロン(:)を押します。

f:id:waenavi:20190825144328j:plain

 

このSUMの部分をコピーします。スラッシュを入力した後に、貼り付けます。

f:id:waenavi:20190825144331j:plain

 

後ろの範囲をD2:D11に直します。

f:id:waenavi:20190825144334j:plain

 

累計なので最初を固定します。合計は全部固定の絶対参照です。

f:id:waenavi:20190825144338j:plain

 

オートフィルをします。これで完成です。

f:id:waenavi:20190825144342j:plain

 


解説は以上です。


7.動画版はこちら(無料)

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 6-9、6-9-2 のYoutube動画を書き起こして、加筆修正したものです。

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]