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

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】絶対参照は表の形とコピーの有無で判断できるように練習せよ

Excelの数式の最初の難関は、絶対参照です。絶対参照とは数式をコピーしても参照するセルを変えない(固定する)ことです。絶対参照を理解するには「相対参照が原則で、絶対参照が例外である」ということを理解しなければなりません(後述)。

 

相対参照のメリットはオートフィル(コピー)によって多くの計算結果を得ることができるということです。縦または横に並んでいる数値を使って一気に計算できますから、とっても便利です。相対参照は便利な機能なのです!! しかし、例外として、数式の一部を固定したい場合、固定するということを指示しなければなりません。

そこで、今回は、表の形とコピーの状況を見て、絶対参照の必要性を判断する練習問題を出題します。

 

 

目次

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

1.絶対参照の「固定」とは何か

問題

次の6つの計算をしなさい。また、「0.15」の部分を「0.24」に変更して再度計算しなさい。

「23x0.15」「810x0.15」「1200x0.15」「999x0.15」「13650x0.15」「5.5x0.15」

解説

相対参照の場合、12個の数を入力して掛け算をします。

f:id:waenavi:20190317170018j:plain

 

この計算は、A列にある数値をすべて0.15倍するだけなので、0.15を6個入力するのが無駄です。0.15は1か所だけに固定して、0.15倍できるのが理想です。

f:id:waenavi:20190317170027j:plain

 

しかし、このまま0.15を消すと、答えが0になります。

f:id:waenavi:20190317170531j:plain

 

相対参照の影響で、計算式に使われている参照セルが1個ずつ下にずれていくからです。

f:id:waenavi:20190317170535j:plain

 

この緑の枠を動かさないようにすればよいということになります。

f:id:waenavi:20190317170541j:plain

 

そこで、最初の計算式を修正します。B1のところをクリックしてカーソルを置きます。

f:id:waenavi:20190317170551j:plain

 

この状態でF4キーを1回押します。ドルマーク($)がつきます。これは固定を表します。

f:id:waenavi:20190317170602j:plain

 

確定して、オートフィルします。これで正しい答えが出ます。

f:id:waenavi:20190317170605j:plain

 

計算式を見ると、ドルマーク($)で固定したものはB1のままです。

f:id:waenavi:20190317170611j:plain

 

このように、相対参照の影響を受けないようにドルマーク($)を付けて固定をすることを絶対参照といいます。コピーをしても参照するセルは動きません。

f:id:waenavi:20190317170615j:plain

 

計算式で共通の値があるときは、入力が1回で済むように考え、かならず絶対参照を使います。

f:id:waenavi:20190317170618j:plain

f:id:waenavi:20190317170622j:plain

 

0.15を0.24に変えます。連動してすべて0.24倍になります。

f:id:waenavi:20190317170626j:plain

 

2.1か所しかなければ固定

問題

3つの店舗A,B,Cの売上目標がそれぞれ50,000円で、売上が46,000円、51,500円、60,000円であった。目標達成率をパーセンテージで表しなさい。 

解説

まずは、与えられたデータを入力します。これを用いて計算します。達成率は、目標から見てそれよりも多いか少ないかを知るために計算します。目標より多ければ目標達成、少なければ残念ということです。

f:id:waenavi:20190317173913j:plain

 

目標で割ると計算できます。

f:id:waenavi:20190317173922j:plain

 

この場合、実績を目標で割る計算式を入力します。

f:id:waenavi:20190317173927j:plain

 

確定する前に絶対参照が必要かを確認します。絶対参照が必要かどうかは、オートフィルをした時のことを前提に考えます。オートフィルをしたときに、青と緑の枠も同時に下に動いていいかどうかを考えます。

f:id:waenavi:20190317173932j:plain

 

実績は各店舗によって異なりますので相対参照でかまいませんが、目標は1か所しかありませんので固定する必要があります。

f:id:waenavi:20190317173937j:plain

f:id:waenavi:20190317173942j:plain

 

F4キーで固定します。

f:id:waenavi:20190317173946j:plain

 

オートフィルします。

f:id:waenavi:20190317173950j:plain

 

パーセントスタイルにします。これで完成です。92%はノルマを達成できなかったことを表し、100%を超えているものは目標をクリアしたことを表します。

f:id:waenavi:20190317173954j:plain

 

計算式を確認すると緑の枠は動きません。

f:id:waenavi:20190317174007j:plain

f:id:waenavi:20190317173958j:plain

 

このパーセンテージから100%にあたる1を引くとプラスマイナスがわかりやすいという考え方もあります。

f:id:waenavi:20190317174011j:plain

f:id:waenavi:20190317174016j:plain

 

しかし、がんばって目標を達成したにもかかわらず、ゼロと評価されてしまう欠点があります。

f:id:waenavi:20190317174022j:plain

 

別解

今度は、横向きに入力しても練習してみましょう。

f:id:waenavi:20190317174127j:plain

 

計算式を入力するときにオートフィルをした時の影響を考えます。B2は右にシフトしなければいけませんが、A2は固定しなければいけません。

f:id:waenavi:20190317174131j:plain

 

A2にはドルマーク($)が必要です。

f:id:waenavi:20190317174135j:plain

 

オートフィルをします。パーセントスタイルにします。これで完成です。

f:id:waenavi:20190317174140j:plain

 

3.絶対参照を考える手順

問題

次の表で、1ドル=110円として、ドルを円に換算しなさい。また、1ドル=105円として再度計算しなさい。

f:id:waenavi:20190317174930j:plain

 

解説

1ドルを110倍すると110になり、円になります。逆に110円を110で割ると1になり、ドルになります。このことからドルを円にするときは110倍、逆に円をドルにするときは110で割ります。

f:id:waenavi:20190318084826j:plain

 

まず、換算レートである110を入力しておきます。

f:id:waenavi:20190318084830j:plain

 

計算式を入力します。110倍です。ここで絶対参照を考えます。

f:id:waenavi:20190318084833j:plain

 

オートフィルやコピーを前提として計算式を入力するときは必ず絶対参照を考えます。このときオートフィルの方向を考え、計算式に使っているセルも同じ方向に動くかどうかを考えます。

f:id:waenavi:20190318084836j:plain

 

今回の場合、オートフィルは右方向です。

f:id:waenavi:20190318084839j:plain

 

掛け算に使用するセルは2つですが、110は右に動いてはいけないものです。絶対参照です。

f:id:waenavi:20190318084843j:plain

 

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

f:id:waenavi:20190318084846j:plain

 

オートフィルをしたときは最後のセルの計算式を確認して、正しい式になっていることを確認します。

f:id:waenavi:20190318084850j:plain

 

105円に変更します。

f:id:waenavi:20190318084854j:plain

 

4.構成比率=個別÷全体

問題

7月の売上が22500円、8月が23000円、9月が24000円だった。3か月の合計を計算し、7月の売上がそのうちの約何%を占めているか求めなさい。 

解説

7月から9月までの売り上げを入れます。入力は縦方向でも横方向でも構いません。

f:id:waenavi:20190318092803j:plain

 

3つを足して合計を出します(SUMでもよい)。69500円です。

f:id:waenavi:20190318092807j:plain

 

そのうち7月の売上が何%を占めているかを求めます。全体の何%に当たるかを表す比率を構成比率ということがあります。合計を分母にすることで計算できます。7月の売上を全体で割ります。

f:id:waenavi:20190318092810j:plain

 

7月だけの比率を出すだけであれば絶対参照は不要ですが、8月9月も比率を出すのであれば、絶対参照を考えます。

f:id:waenavi:20190318092814j:plain

 

オートフィルの方向は下です。

f:id:waenavi:20190318092817j:plain

 

これにあわせて売り上げは下に下がりますが、合計の位置は固定です。

f:id:waenavi:20190318092821j:plain

 

これで完成です。

f:id:waenavi:20190318092824j:plain

 

5.何を固定するかは表の作り方による

問題

定価を割引した時の販売価格を計算できる表をできるだけ多く作りなさい。

f:id:waenavi:20190318094212j:plain 

 

割引率を固定する場合

回答例として2つの表を解説します。

f:id:waenavi:20190318095022j:plain

 

指定されたパーセンテージだけ増やすことを割増、減らすことを割引といいます。
100%に比率を足せば割増、引けば割引になります。

f:id:waenavi:20190318095026j:plain

 

1つ目は割引率のセルを1箇所にしたパターンです。計算式を入力します。

f:id:waenavi:20190318095029j:plain

 

割引率の場所は1箇所で固定なので絶対参照です。

f:id:waenavi:20190318095033j:plain

 

これで完成です。

f:id:waenavi:20190318095036j:plain

 

割引率を変えることによっていろいろな割引が計算できます。

f:id:waenavi:20190318095040j:plain

 

定価を固定する場合

次に元の価格のセルを1箇所にしたパターンです。計算式を入力します。今度は元の価格が1箇所なのでこれが絶対参照です。

f:id:waenavi:20190318095044j:plain

 

これで完成です。

f:id:waenavi:20190318095047j:plain

 

元の価格を変えることによっていろいろな割引率を計算できます。

f:id:waenavi:20190318095051j:plain

 

6.計算結果を予測する練習

問題

次の表で、5つの計算式をそれぞれ下向きにオートフィルをした時の計算結果がどうなるか述べなさい。

f:id:waenavi:20190318103239j:plain

 

解説

A1+A1は両方とも相対参照です。これをオートフィルするとA1はA2,A3と動きます。

f:id:waenavi:20190318105013j:plain

 

したがって、A列の値の2倍になります。

f:id:waenavi:20190318105016j:plain

 

一方だけドルマーク($)がついている場合、A1のまま動きません。

f:id:waenavi:20190318105020j:plain

 

つまり、1+1、1+2、1+3となります。したがって、A列の値に1加えた数になります。

f:id:waenavi:20190318105023j:plain

 

両方ともドルマーク($)がついている場合は、オートフィルをしてもA1のまま変わりません。

f:id:waenavi:20190318105026j:plain

 

したがって、すべて2になります。

f:id:waenavi:20190318105030j:plain

 

A1とA2をたすと3です。これが1個ずつ下にずれますから、2+3、3+4、4+5となります。

f:id:waenavi:20190318105033j:plain

 

したがって、3から順に奇数が並びます。

f:id:waenavi:20190318105037j:plain

 

A1にドルマーク($)がついているので、1のまま固定となります。

f:id:waenavi:20190318105040j:plain

 

したがって、C列と同じになります。

f:id:waenavi:20190318105044j:plain

 

7.表の形を見て絶対参照の判断をする練習

(1)練習問題

問題

次の図で、左の表(1~10)を11倍した結果を、太枠で囲まれた部分に求めようとしている。左上は1*11、右下は10*11である。左上の計算式について、絶対参照が必要か、理由を付けて述べなさい。

f:id:waenavi:20190318100244j:plain

 

解説

入力した計算式を、別のセルにオートフィルまたはコピーする可能性がある場合、絶対参照を考える必要があります。

f:id:waenavi:20190318100816j:plain

 

オートフィルまたはコピーする方向と連動して、計算の対象も動いてよいかどうかが判断基準です。

f:id:waenavi:20190318100820j:plain

 

動いてはいけない場合は絶対参照です。

f:id:waenavi:20190318100824j:plain

 

1番と11番をかけます。縦と横にオートフィルをする予定です。

f:id:waenavi:20190318100827j:plain

 

1~10番は縦と横に動くものですが、11番は1か所しかないため、固定すべきです。

f:id:waenavi:20190318100831j:plain

f:id:waenavi:20190318100835j:plain

 

したがって、11は絶対参照になります。

f:id:waenavi:20190318100838j:plain

 

オートフィルをします。これですべて11倍になります。

f:id:waenavi:20190318100841j:plain

 

(2)特訓問題

問題

さきほどの問題と同じ要領で、太枠の左上にある計算式について絶対参照が必要か、理由を付けて述べなさい。

設問1

f:id:waenavi:20190318101358j:plain

設問2

f:id:waenavi:20190318101401j:plain

設問3

f:id:waenavi:20190318101404j:plain

設問4

f:id:waenavi:20190318101408j:plain

設問5

f:id:waenavi:20190318101411j:plain

設問6

f:id:waenavi:20190318101414j:plain

 

設問1

1と6をかけます。1~5と、太枠は同じ大きさです。これは相対参照です。

f:id:waenavi:20190318102001j:plain

 

6は1か所しかないので絶対参照です。下向きにオートフィルをしても6より下に数値はありません。

f:id:waenavi:20190318102004j:plain

 

設問2

1,6,7,12をかけます。1~5、7~11と、太枠は同じ大きさです。これは相対参照です。

f:id:waenavi:20190318102209j:plain

 

6と12は下に何もありません。したがって6と12は絶対参照です。

f:id:waenavi:20190318102212j:plain

 

設問3

1と2と12をかけます。2~11、12~21と、太枠は同じ大きさです。これは相対参照です。

f:id:waenavi:20190318102332j:plain

 

1はその下が空いているので絶対参照です。

f:id:waenavi:20190318102335j:plain

 

設問4

1と2と7をかけます。2~6と太枠が同じ大きさです。

f:id:waenavi:20190318102443j:plain

 

1と7は絶対参照です。

f:id:waenavi:20190318102446j:plain

 

設問5

1と16と17をかけます。右向きにコピーをします。

f:id:waenavi:20190318102630j:plain

 

このとき右に、16と17に相当するものがないので、この2つは絶対参照です。

f:id:waenavi:20190318102634j:plain

 

設問6

1と11をかけます。

f:id:waenavi:20190318102725j:plain

 

横向きにオートフィルをするとともに縦向きにコピーをしますが、11は右にも下にも、それに続くものがないので絶対参照です。

f:id:waenavi:20190318102729j:plain

 


解説は以上です。


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

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 5-6、5-7、「Excel新演習3」Program 3-1 の3本のYoutube動画を書き起こしたものです。

 

 


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