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

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

【Excel複合参照】行固定や列固定のドルマークの意味と練習問題


セルの参照方法には相対参照のほか、絶対参照と複合参照があります。Excelで計算するときには必ずこれらの参照方法をマスターしなければなりません。しかし、行固定または列固定のドルマーク($)をどこに付けたらいいのか迷ってしまいます。

計算の対象となっている数値が縦に並んでいる場合は列固定でその列番号にドルマークをつけます。横に並んでいる場合は行固定でその行番号にドルマークをつけます。このように理解すればよいですが、数式をコピーする方向によってドルマークの有無が変わってきます。

そこで、今回は、複合参照を使った応用問題を出題しますので、1問ずつしっかりと理由を説明できるようにしましょう。

f:id:waenavi:20190318191120j:plain

 

 

目次

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

1.相対参照と絶対参照

相対参照と絶対参照についてはそれぞれ、こちらの記事をご覧ください。

 

2.複合参照(行固定)

問題

9教科のうち、5教科を2倍、4教科を3.5倍しなさい。

f:id:waenavi:20190318150814j:plain

 

解説

掛け算を入力します。右にオートフィルをするときは、倍率と評定は連動するので相対参照です。

f:id:waenavi:20190318151829j:plain

 

正しい答えが出ます。

f:id:waenavi:20190318151832j:plain

 

今度は下にオートフィルします。この場合は倍率の方を固定する必要があります。

f:id:waenavi:20190318151835j:plain

 

絶対参照にすればうまくいきます。

f:id:waenavi:20190318151840j:plain

 

絶対参照はドルマーク($)が2つつきます。これは行番号も列番号も固定することを表しています。

f:id:waenavi:20190318151843j:plain

 

計算式は縦と横にオートフィルをするのに対して、倍率は横方向にだけ入力してあります。

f:id:waenavi:20190318151847j:plain

 

このとき行番号だけ固定します。

f:id:waenavi:20190318151851j:plain

 

F4キーを何回か押すことで切り替えができます。「B$2」とします。

f:id:waenavi:20190318151855j:plain

 

オートフィルをします。今度はうまくいきました。

f:id:waenavi:20190318151902j:plain

 

絶対参照のうち行列の一方だけ固定することを「複合参照」といいます。

f:id:waenavi:20190318151907j:plain

 

計算式は縦と横にオートフィルをするのに対して、参照するセルが横に並んでいる場合、行の固定が必要です。

f:id:waenavi:20190318151910j:plain

 

固定が必要な行を見るとその行番号は「2」になっています。このとき、「2」だけドルマーク($)をつけます(B$2)。

f:id:waenavi:20190318151913j:plain

 

3.複合参照(列固定)

問題

Yes/Noで答える10個の質問のアンケートをしたところ、次のような回答者数になった。
Yes/Noの合計とそれぞれの比率を求めなさい。

f:id:waenavi:20190318150833j:plain

 

解説

まずは足し算をします。これは相対参照です。

f:id:waenavi:20190318152803j:plain

 

つぎに構成比率を出します。比率は内訳/合計です。

f:id:waenavi:20190318152807j:plain

 

このとき下向きにオートフィルをするだけであればこれで問題ありませんが、右にオートフィルをするときには、合計を固定する必要があります。

f:id:waenavi:20190318152810j:plain

f:id:waenavi:20190318152815j:plain

 

計算式は縦と横にオートフィルをするのに対して、合計は縦方向だけです。このとき列番号だけ固定します。f:id:waenavi:20190318152818j:plain

 

固定が必要な列を見ると、その列番号はDです。このときDだけドルマーク($)を付けます。

f:id:waenavi:20190318152823j:plain

 

オートフィルをします。

f:id:waenavi:20190318152826j:plain

 

これで完成です。

f:id:waenavi:20190318152831j:plain

 

4.かけざん九九の表

問題

かけざん九九の表を完成させなさい。

f:id:waenavi:20190318150845j:plain

 

解説

最初に計算式を入れてから考えます。

f:id:waenavi:20190318154618j:plain

 

計算式は縦と横にオートフィルをするのに対して、A列は縦だけ、1行目は横だけです。右にオートフィルをするときには左の列を固定する必要があります。下向きにオートフィルをするときには上の行を固定する必要があります。このように縦に並んだセルと、横に並んだセルを使って計算する場合、それぞれ固定を考えます。

f:id:waenavi:20190318154623j:plain

 

縦に並んだセルはA列にあります。したがってAの前にドルマーク($)を付けます。

f:id:waenavi:20190318154628j:plain

 

横に並んだセルは1行目にあります。したがって1の前にもドルマーク($)を付けます。それぞれF4キーでドルマーク($)をつけます。

f:id:waenavi:20190318154632j:plain

 

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

f:id:waenavi:20190318154635j:plain

 

5.行固定・列固定で$を付ける場所

問題

税抜価格に消費税を加算して税込価格を求めなさい。なお、税率は3%、5%、8%、10%、15%とする。

f:id:waenavi:20190318154922j:plain

 

解説

まずは計算式を入力します。税抜価格の割増です。

f:id:waenavi:20190318155152j:plain

 

計算式は縦と横にオートフィルをしますが、税抜価格はB列、税率は2行目で固定です。

f:id:waenavi:20190318155156j:plain

 

ドルマーク($)を付ける場所は、計算するセル(参照するセル)が縦に並んでいるか横に並んでいるかによって見方が変わります。に並んでいる場合はそのを見て列番号に$をつけます。また、に並んでいる場合はその左側を見て行番号に$を付けます。

f:id:waenavi:20190318155159j:plain

 

税抜価格のBと、税率の2を固定します。

f:id:waenavi:20190318155204j:plain

 

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

f:id:waenavi:20190318155208j:plain

 

6.表の形から列または行固定を判断する練習問題

(1)例題

問題

次の図で、左の2つの表(1~5と6~20)を用いて、太枠の部分に掛け算の結果を求めようとしている。左上は1*6、右下は5*20である。左上の計算式について、列固定または行固定が必要か、理由を付けて述べなさい。

f:id:waenavi:20190318160430j:plain

 

解説

列固定または行固定が必要かを考えるにはまず、計算式をオートフィルする方向を考えます。縦と横の両方であれば複合参照の可能性があります。そして、その方向に、参照セルも連動してもよいかどうかを考えます。この図の場合、計算式の方向は右と下です。縦と横の両方にオートフィルをします。

f:id:waenavi:20190318161054j:plain

 

1~5は縦に数字が並んでいます。列固定が必要です。

f:id:waenavi:20190318161059j:plain

 

6~15のほうは縦横に動いて問題ありません。つまり、相対参照でかまいません。

f:id:waenavi:20190318161103j:plain

 

1と6をかけます。このとき、1~5が縦並びであることから、その列番号を見ます。

f:id:waenavi:20190318161107j:plain

 

Aにドルマーク($)を付けます。

f:id:waenavi:20190318161110j:plain

 

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

f:id:waenavi:20190318161113j:plain

 

右下が5x20になっていることを確認します。

f:id:waenavi:20190318161117j:plain

 

(2)特訓問題

問題

さきほどの問題と同じ要領で、太枠の左上にある計算式について、列固定または行固定が必要か述べなさい。

設問1

f:id:waenavi:20190318162006j:plain

設問2

f:id:waenavi:20190318162010j:plain

設問3

f:id:waenavi:20190318162013j:plain

設問4

f:id:waenavi:20190318162017j:plain

設問5

f:id:waenavi:20190318162020j:plain

設問6

f:id:waenavi:20190318162023j:plain

設問7

f:id:waenavi:20190318162027j:plain

設問8

f:id:waenavi:20190318162030j:plain

設問9

f:id:waenavi:20190318162033j:plain

設問10

f:id:waenavi:20190318162037j:plain

 

設問1

1と16をかけます。

f:id:waenavi:20190318163459j:plain

 

1~15は5x3の表であるのに対して、16~20は縦だけです。16のほうは列固定が必要です。

f:id:waenavi:20190318163502j:plain

 

必ず、右下の計算式が合っていることを確認します。

f:id:waenavi:20190318163507j:plain

 

設問2

1と6をかけます。

f:id:waenavi:20190318163606j:plain

 

1~5は横に並んでいます。1のほうは行固定が必要です。

f:id:waenavi:20190318163615j:plain

 

設問3

1と16をかけます。

f:id:waenavi:20190318163644j:plain

 

16~20は横方向だけです。16は行固定が必要です。

f:id:waenavi:20190318163649j:plain

 

設問4

1と6をかけます。1~5は縦だけ、6~10は横だけです。1は列固定、6は行固定が必要です。

f:id:waenavi:20190318163809j:plain

f:id:waenavi:20190318163812j:plain

 

設問5

1と6と11をかけます。設問4と同じように、1は列固定、6は行固定が必要です。

f:id:waenavi:20190318163851j:plain

 

さらに11は縦にも横にも並んでいない、1か所しかないセルです。これは絶対参照です。

f:id:waenavi:20190318163855j:plain

 

設問6

1と6と11と16と21をかけます。

f:id:waenavi:20190318164002j:plain

 

1~5と、6~10は縦に並んでいます。11~15、16~20は横に並んでいます。1と6は列固定、11と16は行固定です。

f:id:waenavi:20190318164006j:plain

 

さらに21は絶対参照です。

f:id:waenavi:20190318164011j:plain

 

設問7

1と16と17と18をかけます。1~15で考えれば、相対参照です。

f:id:waenavi:20190318164151j:plain

 

計算式は右に3つオートフィルをするのに対して、16と17の右側に数字がありません。16と17は絶対参照です。

f:id:waenavi:20190318164154j:plain

 

18~20は横並びなので、18は行固定です。

f:id:waenavi:20190318164159j:plain

f:id:waenavi:20190318164202j:plain

 

設問8

1と6と16をかけます。

f:id:waenavi:20190318164307j:plain

 

1は縦並びで列固定、6は相対参照、16は絶対参照です。

f:id:waenavi:20190318164312j:plain

 

設問9

1と6と16をかけます。計算式のオートフィルは縦方向だけのように見えますが、右側も同じ計算式を利用することを考えれば、縦と横方向にコピーをするものと考えられます。

f:id:waenavi:20190318164409j:plain

 

1は縦並びで列固定です。

f:id:waenavi:20190318164413j:plain

 

6~10は縦並びですが、右にコピーをして11~15も利用することを考えれば相対参照です。

f:id:waenavi:20190318164417j:plain

 

16については、右にコピーしたときに17になると考えられるので、行固定が良いです。

f:id:waenavi:20190318164421j:plain

 

設問10

1と6と16をかけます。先ほどの問題と同じように、少し飛んでいますが縦横にコピーをするものと考えます。

f:id:waenavi:20190318164546j:plain

 

1~5は横並びで行固定、16と17は縦並びで列固定です。

f:id:waenavi:20190318164550j:plain

 

6~10は、下にコピーして11~15を利用することを考えれば相対参照です。

f:id:waenavi:20190318164555j:plain

 

コピーをして完成です。

f:id:waenavi:20190318164559j:plain

 

7.応用問題

(1)規則的に数を並べる

問題

次の表を、複合参照を使って作りなさい。なお、完成イメージのデータ以外に、入力するデータがあっても構わない。

f:id:waenavi:20190318172128j:plain

 

解説

規則的に並んでいる表の場合、複合参照を使えば、すぐにできる場合があります。この表の場合、4桁の数字ですが最初の2桁と最後の1ケタは連番です(参考:【Excel】計算式で数列を求めることによって規則的にデータを並べる練習)。

f:id:waenavi:20190318172736j:plain

 

縦に19~21、横に1~5を入力します。1901は19*100+1です。

f:id:waenavi:20190318172740j:plain

 

複合参照にします(A列と1行目固定)。

f:id:waenavi:20190318172743j:plain

 

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

f:id:waenavi:20190318172746j:plain

 

4桁の部分だけ使いたいときには、コピーをして、値で貼り付ければどこでも使うことができます。

f:id:waenavi:20190318172750j:plain

f:id:waenavi:20190318172753j:plain

 

(2)列固定*列固定*行固定

問題

A列に定価、B列に割引率、2行目に個数を入力した。定価*個数を、割引をした金額を求めなさい。

f:id:waenavi:20190318172905j:plain

 

解説

割引率の分だけ減らす計算をします。定価x個数x(1-割引率)です。

f:id:waenavi:20190318173404j:plain

 

複合参照にします。

f:id:waenavi:20190318173408j:plain

 

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

f:id:waenavi:20190318173413j:plain

 

(3)文字列連結と複合参照

問題

複合参照を使って次の表を作りなさい。なお、完成イメージのデータ以外に、入力するデータがあっても構わない。

f:id:waenavi:20190318172924j:plain

 

解説

ハイフンの前は縦向きにアルファベットが並んでいます。

f:id:waenavi:20190318173935j:plain

 

ハイフンの後は横向きに並んでいます。これを連結します。

f:id:waenavi:20190318173938j:plain

 

まず、縦向きにAからFまで入力します。これをコピーします。

f:id:waenavi:20190318173942j:plain

 

貼り付けるときに、行列を入れ替えて貼り付けます。横向きになります。

f:id:waenavi:20190318173945j:plain

 

AとAをハイフンで連結します(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。

f:id:waenavi:20190318173949j:plain

 

複合参照にします。

f:id:waenavi:20190318173953j:plain

 

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

f:id:waenavi:20190318173956j:plain

 

別解

ここでハイフンを別のセルに入力したとします。この場合、最初の計算式は3つのセルを連結することになります。

f:id:waenavi:20190318174000j:plain

 

複合参照ですが、ハイフンは絶対参照です。

f:id:waenavi:20190318174003j:plain

 

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

f:id:waenavi:20190318174007j:plain

 

これで、ハイフンを別の文字に変えることもできます。

f:id:waenavi:20190318174010j:plain

 

(4)ローマ字表

問題

複合参照を使って次の表を作りなさい。なお、完成イメージのデータ以外に、入力するデータがあっても構わない。

f:id:waenavi:20190318172933j:plain

 

解説

かっこ書きの部分はイレギュラーなので後で考えます。

f:id:waenavi:20190318175835j:plain

 

A列と1行目にアルファベットを入力します。これを連結します。

f:id:waenavi:20190318175839j:plain

 

複合参照にします。

f:id:waenavi:20190318175843j:plain

 

オートフィルをします。

f:id:waenavi:20190318175847j:plain

 

A列と1行目を別の場所にコピーします。かっこ書きの部分だけ入力します。

f:id:waenavi:20190318175851j:plain

 

この左と右の表を連結します。元の計算式を修正して、右の表をさらに連結しますが、これは相対参照になります。

f:id:waenavi:20190318175854j:plain

 

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

f:id:waenavi:20190318175857j:plain

 

(5)2つの変数

問題

いま、ある競技で47都道府県の代表チームがすでに決定している。全国大会を行う前に、東日本17チームと西日本30チームに分けて、それぞれ東日本予選、西日本予選を行うことになった。予選通過できる確率を、東西でできるだけ等しくしたい。東西それぞれ何チームを予選通過とすればよいか。

f:id:waenavi:20190318172942j:plain

 

解説

例えば、東日本から2チーム選ばれるのであれば、選ばれる確率は17分の2です。西日本から4チーム選ばれるのであれば、30分の4です。東日本と西日本で不公平にならないように、この確率の差を少なくしようとしています。そこで、17分の1~17分の17、30分の1~30分の30を求めて、その差を計算します。

f:id:waenavi:20190318181229j:plain 

 

縦と横に数字を入力します。

f:id:waenavi:20190318181233j:plain

 

17分の1から30分の1を引きます。

f:id:waenavi:20190318181236j:plain

 

複合参照にします。

f:id:waenavi:20190318181239j:plain

 

オートフィルをします。これで最も0に近い組み合わせが正解ですが、とても見にくいです。

f:id:waenavi:20190318181243j:plain

 

そこで初めの計算式を修正して1000倍します。

f:id:waenavi:20190318181247j:plain

 

整数の表示にします。2が一番0に近いです。東日本から4チーム、西日本から7チーム選べば、誤差が1000分の2で最も公平になります。

f:id:waenavi:20190318181250j:plain

 

このとき予選落ちが13チームと23チームになります。

f:id:waenavi:20190318181254j:plain

 

予選通過と予選落ちを逆にしても、誤差は同じです。よって、最適解は(4,7)、(13,23)となります。

f:id:waenavi:20190318181258j:plain

 


解説は以上です。


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

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

 

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ