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

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

【Excel数式】セルの「参照」が分からなければ相対参照は理解できない


電卓とは異なり、Excelで計算をすることの最大のメリットは何でしょうか?

  • 複数のセルを参照することができること
  • 複数のセルに同時に計算結果を表示することができること
  • 何度でも再計算をしてくれること

セルの参照は電卓にはできません。セルを参照することのメリットは入力値を変えるだけで再計算ができることです。その再計算をした結果も再利用することができるので、連鎖的に計算をすることもできます。

この「参照のメリット」が分からないまま、相対参照?絶対参照?複合参照?などと悩むのは時間の無駄です。いつまでたっても本質を理解することはできません。

そこで、今回は、参照とは何か?ということについて詳しく解説したいと思います。

 

 

目次

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

1.セルを参照する

(1)直接参照

問題

セルB1に、セルA1を参照する式を入力しなさい。

解説

セル番地を計算式に利用することを「参照」referenceといいます。

f:id:waenavi:20190315204708j:plain

 

イコール(=)を入力した直後に、セルA1をクリックします。これでA1を利用したことになります。これで完成です。

f:id:waenavi:20190315134303j:plain

 

確定すると0になります。これはA1に何も入力していないからです。

f:id:waenavi:20190315134308j:plain

 

セルA1に5を入力すると、連動してB1も5になります。

f:id:waenavi:20190315134312j:plain

 

このようにA1に入力した値が、そのままB1にも表示されます。

f:id:waenavi:20190315134316j:plain

 

参照することによって、A1に入力した値が、そのままB1にも表示されます。おもに同じ値を別の場所に転記するときに使います。

f:id:waenavi:20190315204517j:plain

 

文字列もそのまま表示されます。

f:id:waenavi:20190315134324j:plain

 

(2)セル参照のあとに演算を付加する

問題

セルB2に、セルB1の値を0.2倍する式を入力しなさい。

解説

イコールを入力した直後に、セルB1をクリックします。

f:id:waenavi:20190315134740j:plain

 

さらに0.2倍します。キーボードで「*0.2」と入力します。

f:id:waenavi:20190315134744j:plain

 

確定します。

f:id:waenavi:20190315134748j:plain

 

A1に値を入力すると、連動してB1、B2にも数値が表示されます。

f:id:waenavi:20190315134752j:plain

 

A1に値を入力します。B1は、A1を参照しただけなのでそのままの値が表示されます。B2は、B1を0.2倍した値が表示されます。

f:id:waenavi:20190315134755j:plain

 

(3)複数のセルを参照する

問題

セルB3に、セルB1からセルB2をひく式を入力しなさい。

解説

B1からB2を引く式を入力します。このように2つ以上のセルを参照することもできます。

f:id:waenavi:20190315135254j:plain

f:id:waenavi:20190315135257j:plain

 

これで、A1に値を入力するとB列で2割引きの計算ができます。

f:id:waenavi:20190315135302j:plain

f:id:waenavi:20190315135306j:plain

 

B1は元の値、B2は2割の値、B3は引き算して2割引きした結果が表示されます。

f:id:waenavi:20190315135309j:plain

 

A1の値をいろいろ変えることによって、2割引きの計算が繰り返しできます。「セルの参照」を使うことによって、1回の入力で3つの計算結果を同時に表示することができます。

f:id:waenavi:20190315135314j:plain

 

このように、Excelは計算結果を同時に複数表示させる事例に適しています。

f:id:waenavi:20190315135317j:plain

 

2.同じ計算を繰り返す

問題

次の図で、月曜日から土曜日までの日付を求めなさい。

f:id:waenavi:20190315135410j:plain

 

解説

日曜日の日付が決まれば、月曜から土曜までの日付も決まります。月曜日は日曜日に1を加えます。

f:id:waenavi:20190315140112j:plain

 

同じように火曜日は月曜日に1を加えます。

f:id:waenavi:20190315140116j:plain

 

これを繰り返します。

f:id:waenavi:20190315140119j:plain

 

オートフィルをすると早くできます(参考:【Excel】オートフィルは累積的に同じ計算を繰り返すときにも使う)。

f:id:waenavi:20190315140122j:plain

f:id:waenavi:20190315140126j:plain

 

日曜日の日付を変えると、連動して月曜から土曜までの日付も変わります。

f:id:waenavi:20190315140129j:plain

 

3.複数の入力と複数の計算結果

(1)2つの入力と2つの出力

問題

2つの文字列を連結して、次のように表示しなさい。

f:id:waenavi:20190315135602j:plain

 

解説

セルの参照をする計算式を作るときには、入力と計算結果の個数を考えます。今回の場合は、入力は漢字とひらがなの2つです。計算結果も2つです。したがって、合わせて4つのセルが必要です。

f:id:waenavi:20190315140133j:plain

 

上の2つのセルに漢字とひらがなを入力することを前提として、下の2つのセルに計算結果を表示させます。

f:id:waenavi:20190315140138j:plain

 

漢字とひらがなをスラッシュで連結します。このときスラッシュはダブルクォーテーションで囲みます(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。

f:id:waenavi:20190315140142j:plain

 

今度はかっこを連結します。このときもかっこはダブルクォーテーションで囲みます。

f:id:waenavi:20190315140145j:plain

 

これで完成です。

f:id:waenavi:20190315141712j:plain

 

実際に漢字とひらがなを入力してみます。

f:id:waenavi:20190315140149j:plain

f:id:waenavi:20190315140153j:plain

 

(2)3つの入力と4つの出力

問題

3つの数を用いて次のように表示しなさい。

f:id:waenavi:20190315135505j:plain

 

解説

3つの数を足したりかけたりします。入力する数は3つで計算結果は4つです。したがって、あわせて7つのセルが必要です。上3つのセルに数値を入力することを前提として、下の4つのセルに計算結果を表示させます。

f:id:waenavi:20190315141649j:plain

 

まず足し算をします。

f:id:waenavi:20190315141652j:plain

 

つぎに掛け算をします。

f:id:waenavi:20190315141655j:plain

 

足し算の文字列連結をします。このときプラスはダブルクォーテーションで囲みます。

f:id:waenavi:20190315141658j:plain

 

同じように掛け算の文字列連結をします。

f:id:waenavi:20190315141702j:plain

 

これで完成です。

f:id:waenavi:20190315141706j:plain

 

実際にA1~A3に数値を入れて計算をしてみます。

f:id:waenavi:20190315141709j:plain

 

4.セルを参照している計算式の修正

問題

セルB3に「=B1+B2」と入力するところを間違えて「=A1+A2」と入力した。
計算式を修正しなさい。

f:id:waenavi:20190315170205j:plain

 

解説

計算式はセルをダブルクリックするか、数式バーを使って修正することができます。

f:id:waenavi:20190315142628j:plain

f:id:waenavi:20190315142631j:plain

 

パターン(1)セル選択

範囲選択して、セルをクリックすると修正できます。

f:id:waenavi:20190315143030j:plain

f:id:waenavi:20190315143035j:plain

 

これで完成です。

f:id:waenavi:20190315143039j:plain

 

パターン(2)直接修正

半角モードで直接入力して直すこともできます。

f:id:waenavi:20190315143042j:plain

 

AをBに直します。

f:id:waenavi:20190315143046j:plain

 

このときアルファベットは小文字でも構いません。

f:id:waenavi:20190315143050j:plain

 

パターン(3)枠を動かす

参照セルには色のついた枠が表示されます

f:id:waenavi:20190315143054j:plain

 

この枠が太くなっていることを確認しながら、ドラッグによって動かすことで修正することができます。

f:id:waenavi:20190315143059j:plain

 

これで完成です。

f:id:waenavi:20190315143102j:plain

 

5.数式の先頭にあるイコールの意味を説明せよ

問題

次の図のように、数式を入力するときには必ず先頭にイコールを入力する。このイコールの意味を述べなさい。

f:id:waenavi:20190315170221j:plain

 

解説

コンピュータで「イコール」は、等しいことを表す場合と、代入つまり上書きすることを表す場合があります。

f:id:waenavi:20190315173431j:plain

 

数式の先頭のイコールは等しいという意味を全く持ちません。

f:id:waenavi:20190315173434j:plain

 

いっぱんに、コンピュータの「代入」は、イコールより右側にある計算をして、その計算が終わるのを待って、最後に計算結果を左側に上書きすることをいいます。

f:id:waenavi:20190315173438j:plain

f:id:waenavi:20190315173441j:plain

 

本来ならば左向きの矢印←を書くべきですが、=を使っているだけなのです。

f:id:waenavi:20190315173445j:plain

 

数式バーで数式を入力するときには、左にセル番地が表示されます。このセルに対して上書きします。

f:id:waenavi:20190315173449j:plain

 

例えばA1が2、B1が3の場合、C1は5になります。

f:id:waenavi:20190315173452j:plain

 

A1を7にすると足し算の結果は9になるはずです。しかし、すでにC1には5という数字が入っています。

f:id:waenavi:20190315173455j:plain

 

そこで、先頭のイコールの効果によって足し算された結果である9がC1に上書きされ、5が消えるのです。

f:id:waenavi:20190315173459j:plain

 

数式の内容が変わったときは、自動的に再計算されて上書きされます。

f:id:waenavi:20190315173503j:plain

 

数式の中にイコールが何個かある場合は、先頭にあるイコールだけが代入、それ以外のイコールは「等しい」という意味です。

f:id:waenavi:20190315173507j:plain

 

*補足*マイクロソフトのマニュアルによると、Excelの数式の先頭にあるイコールは数式を表す記号であり演算子ではないとしています。しかし、固定値の場合に先頭のイコールを省略しているものと考えれば、代入をしていると解釈することも可能です。

 

6.名前はまだ無い

問題

次の図の数式がエラーとして表示される理由を簡潔に述べなさい。

f:id:waenavi:20190315172901j:plain

 

解説

イコールの後に入力できる文字列は、計算の命令として解釈できる文字列に限られ、計算できない文字列は許されません。

f:id:waenavi:20190315182137j:plain

 

先頭にイコールを入力すると、計算を待つ状態になります。計算の命令ではない文字列は、計算結果を返すことができないため、エラーとなります。

f:id:waenavi:20190315182141j:plain

 

計算の対象から除外するには前後にダブルクォートをつけます。

f:id:waenavi:20190315182144j:plain

 

7.数式のコピーと相対参照

問題

Excelで、次の6つの計算をする方法をできるだけ多く考えなさい

「4x18」「12x24」「22x34」「766x875」「645x664」「1245x76」

解説

電卓で掛け算をするだけであれば計算方法は1通りです。しかし、Excelは答えを出す方法が1通りではありません。状況やセルの配置によって計算の方法が異なります。Excelで計算式を深く理解するには、1つの問題に対して、できるだけ多くの計算方法を試して、慣れることが重要です。

f:id:waenavi:20190315182558j:plain

 

状況(1)1か所

まずは、1か所に答えを出す方法です。掛け算の計算式を入力することによって計算をすることができます。

f:id:waenavi:20190315182732j:plain

 

A1とB1に値を入力します。6つの式を計算するときは6回入力しなおすことになります。

f:id:waenavi:20190315182736j:plain

 

状況(2)縦向きの場合

次に、あらかじめ12個の数値を入力した場合を考えます。A列とB列に数値を入力して、C列に答えを出すようにします。

f:id:waenavi:20190315190653j:plain

 

1行目は掛け算の計算式を入力します。

f:id:waenavi:20190315190657j:plain

f:id:waenavi:20190315190701j:plain

 

72をコピーして、残りの5か所に貼り付けます。72をコピーをしたのに、貼り付けたときには72になりません。

f:id:waenavi:20190315190704j:plain

 

この画面で青と緑のセルが計算式に参照しているセル、黒のセルが計算式です。

f:id:waenavi:20190315190708j:plain

 

青と緑と黒の3つのセルは横に並んでいます。

f:id:waenavi:20190315190712j:plain

 

計算式を下向きにコピーをすると、参照するセルも下向きに移動します。

f:id:waenavi:20190315190716j:plain

 

これによって、横並びになっている3つのセルの位置関係が変わらないようになっています。

f:id:waenavi:20190315190719j:plain

 

このように、参照を利用した計算式をコピーしたときに、参照しているセルも連動するという性質を「相対参照」といいます。Excelは相対参照が原則となっています。

f:id:waenavi:20190315190723j:plain

 

今度はC1を横向きにコピーしてみます。1296になります。

f:id:waenavi:20190315190727j:plain

 

右にコピーをすると参照するセルも連動して右に移動し、18x72で1296となります。

f:id:waenavi:20190315190732j:plain

 

ドラッグによって移動してみます。今度は72のままです。移動の場合は計算式が変わりません。

f:id:waenavi:20190315190736j:plain

 

このように相対参照には例外があります。切り取りによる移動は計算式と参照セルが連動しません。コピーの場合のみです。また、計算式を範囲選択すると相対参照にはなりません。

f:id:waenavi:20190315190740j:plain

 

セルをダブルクリックします。計算式を選択してコピーします。いったん確定します。

f:id:waenavi:20190315190744j:plain

 

セルをダブルクリックします。貼り付けます。確定します。72になります。

f:id:waenavi:20190315190747j:plain

 

状況(3)横向きの場合

今度は12個の数値を横向きに入力した場合を考えます。最初のセルに掛け算の式を入れます。

f:id:waenavi:20190315190752j:plain

 

横向きにオートフィルをします。

f:id:waenavi:20190315190756j:plain

 

オートフィルもコピーの一種ですから、相対参照になります。

f:id:waenavi:20190315190803j:plain

 

列番号が1つずつずれていき、正しく6個の掛け算ができます。

f:id:waenavi:20190315190806j:plain

 

状況(4)2列の場合

次に計算式が2列になっているパターンです。最初のセルに掛け算の式を入れます。

f:id:waenavi:20190315190813j:plain

 

オートフィルによって3つの計算ができます。

f:id:waenavi:20190315190817j:plain

 

この3つのセルをコピーして、右の列にコピーします。

f:id:waenavi:20190315190822j:plain

 

計算式と参照セルの位置関係がコピーされ(列番号が3つ増える)、正しく計算されます。 

f:id:waenavi:20190315190826j:plain

 

8.積み重ねる計算(累積的計算)

問題

次のような数列を作り、何行目で10000を超えるか確認しなさい。

f:id:waenavi:20190315182426j:plain

 

解説

1+2=3です。

f:id:waenavi:20190315190934j:plain

 

下にコピーすると行番号が1つ増えるので、2+3=5となります。

f:id:waenavi:20190315190938j:plain

 

さらにコピーします。3+5=8です。

f:id:waenavi:20190315190941j:plain

 

これを繰り返し計算するのでオートフィルをしたほうが早いです。

f:id:waenavi:20190315190944j:plain

 

このように相対参照の性質を利用すれば、前の計算結果を利用して、順次、積み重ねていく計算ができます。

f:id:waenavi:20190315190947j:plain

 

20行目で10000を超えます。

f:id:waenavi:20190315190951j:plain

 

9.相対参照の練習問題

(1)桁別コード

問題

次の図のように西暦の年月日から8桁の数値にしなさい。

f:id:waenavi:20190315192129j:plain

 

誤答例

1995と11と30を連結すると8桁になります。

f:id:waenavi:20190315192512j:plain

 

しかし、オートフィルをすると8桁になりません。月日が1桁の場合、8桁にするためには0を補う必要があります。1つずつ計算式を直していくのは間違いです。

f:id:waenavi:20190315193727j:plain

 

このように、最初の計算が正しくてもオートフィルをして正しくないものは計算式として間違いです。

f:id:waenavi:20190315192520j:plain

 

正解

数値を連結するときには掛け算を使います。年を10000倍、月を100倍して足します。

f:id:waenavi:20190315192524j:plain

 

計算式を入力します。

f:id:waenavi:20190315192527j:plain

 

これでオートフィルをすればすべて8桁になります。

f:id:waenavi:20190315192531j:plain

 

(2)累計

問題

次のような表を完成させなさい。

f:id:waenavi:20190315192210j:plain

 

解説

累計は、数値を順番に足していく計算です。今までの計算結果に、新たな数値を加えていくものと考えます。

f:id:waenavi:20190315193737j:plain

 

上の計算結果に左の数値を足します。

f:id:waenavi:20190315193813j:plain

 

最初は左の数値をそのままイコールで参照します(直接参照)。

f:id:waenavi:20190315194615j:plain

 

次に上と左を足す計算式を入れます。

f:id:waenavi:20190315194619j:plain

 

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

f:id:waenavi:20190315194623j:plain

 

今度は上の計算結果に対して文字列の連結をする計算です。上の計算結果の最後に、プラスの記号と左の数値を連結します。 

f:id:waenavi:20190315194627j:plain

 

最初は左の数値をそのままイコールで参照します。次に、上にプラスを連結して、さらに左の数値を連結します。

f:id:waenavi:20190315194630j:plain

 

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

f:id:waenavi:20190315194634j:plain

 

ところで、A列も上の数値に1を加えているものと考えれば、2行目以降が計算式にすることもできます。

f:id:waenavi:20190315194638j:plain

 

オートフィルをします。

f:id:waenavi:20190315194642j:plain

 

これで最初のセルA1以外はすべて計算式になります。最初の数字を変えるとすべて連動します。このように、できるだけ入力が少なくなるようにします。

f:id:waenavi:20190315194647j:plain

 

(3)カレンダー

問題

次の図のように7列に数字を並べる方法をできるだけ多く考えなさい

f:id:waenavi:20190315192137j:plain

 

解説

オートフィルを用いて直接入力することも可能ですが、計算式を使うことを考えます。

f:id:waenavi:20190315200757j:plain

 

まずは、ひたすら1を足していく方法です。1を入力します。つぎに1を足す計算式を入力します。

f:id:waenavi:20190315200800j:plain

 

右にオートフィルします。

f:id:waenavi:20190315200804j:plain

 

8は7+1です。

f:id:waenavi:20190315200808j:plain

 

B列からG列は左の数値に1を足す計算式が入っていますから、そのままオートフィルができます。

f:id:waenavi:20190315200811j:plain

 

14に1をたすと15です。A列の数値はG列に1を足す計算をします。

f:id:waenavi:20190315200814j:plain

 

これも、オートフィルができます。B列からG列もオートフィルができますから、結局、全部オートフィルができます。これで完成です。

f:id:waenavi:20190315200819j:plain

 

別解その1

2行目は上の数に7を足すという考え方もできます。7を足す計算式を入力します。

f:id:waenavi:20190315200932j:plain

 

オートフィルをします。

f:id:waenavi:20190315200935j:plain

 

3行目以降も7を足すことに変わりはありませんので、すべてオートフィルができます。

f:id:waenavi:20190315200939j:plain

 

最初のセル以外はすべて計算式ですから、最初のセルを変えればすべて連動して変わります。

f:id:waenavi:20190315200943j:plain

 

別解その2

最後に、右上の7を先に入力した場合を考えます。この場合、1行目は右の数から1を引くことになります。

f:id:waenavi:20190315201139j:plain

 

これを左向きにオートフィルします。

f:id:waenavi:20190315201143j:plain

 

2行目以降は上の数に7を足す計算式を入力します。

f:id:waenavi:20190315201146j:plain

 

オートフィルします。

f:id:waenavi:20190315201150j:plain

 

これで完成です。

f:id:waenavi:20190315201155j:plain

 

右上を変えると、すべて連動します。

f:id:waenavi:20190315201200j:plain

 


解説は以上です。


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

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

 

 


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