戻る

今回はEXCELの「絶対参照（ぜったいさんしょう）」や「相対参照（そうたいさんしょう）」と呼ばれる機能について解説をします。
これらはオートフィルを使いこなすために必要な機能です。

教科書を持っている場合は、第４章7.3「相対参照と絶対参照」P.130も合わせて参照してください。

演習するために、以下の練習問題をクリックし、ダウンロードして開いてください。

練習問題 syokyu11example.xlsx

ファイル内の設問に回答し、moodle に提出してください。

まず「絶対参照」について説明します。

オートフィルで計算をする時に便利な機能なのですが、意味をよく理解しないと使いこなせないので、しっかり把握しておきましょう。

ダウンロードした練習問題の最初のシート「絶対参照とは」を見ながら考えます。

このシートは、「基本料金」と「倍率」の数値を掛け算するものです。

EXCELでは数式（計算式）を入力する時、以下のようにセルの場所を指定して計算できます。

=B7*D7

上のように書くと、指定のセルに書き込まれた値を使って計算が行えます。この例の場合、B7セルに書いてある「基本料金」の値と、D7セルに書いてある「倍率」の値を掛け算「*」していることになります。つまり「500x1.0」が計算されます。

このようにセルの場所を指し示すことを「参照」と言います。「参照」をしておけば、元のセルに書かれた数値を修正するだけで、すぐに計算結果も修正されるというメリットがあります。

「参照」を使わずに、=500x1.0のように数値を直接入力しても計算できますが、値の修正作業が大変になってしまいます。なので、できるだけ=B7*D7のように参照を使いたいです。

では他の計算も行いたいので、この計算式を下に向かって「オートフィル¹⁾」します。するとどうなるでしょうか。

全て「0」になります。一体何が起こったのでしょうか？🤔

結果がおかしいときは「間違った！」とあわてて元に戻す前に、オートフィルした数式をダブルクリックして、数式に何が起こっているのかを確かめましょう。真ん中あたりの数式をてきとうに選んでダブルクリックしてみます。

ダブルクリックすると、参照しているセルに枠が付きます。上のように色付きの枠が見えるはずです。

枠の位置に注目すると、セルの参照位置がずれている様子が分かります。ずれた結果、空欄を掛け算しています。空欄は「0」扱いなので「0 x 4.5」のような計算になっているのだと分かりました。なるほど計算結果がゼロになるわけです。

一旦、ESCキーを押して入力をキャンセルしておきましょう。

このようにEXCELではセルの「参照」が使われていると、数式や関数などをオートフィルしたりコピーした時に参照位置が移動します。これは正常な動作です。

下に向かってオートフィルすると参照も下に移動し、右に向かってオートフィルすると右に移動します。ちょうどセルの相対的な位置関係を保ったまま平行移動するイメージです。

そのおかげで、たくさん並んでいる数値を順番に計算できるわけです。

この状態（＝普通の状態）を「相対参照」と言います。

しかし今回は「￥500」と書かれたB7セルの位置が移動するのは困ります。参照位置は、たとえオートフィルしても、B7セルから絶対に動いて欲しくありません！ かといって、数式を一つずつ修正するのも大変です。なんとかして楽に計算する方法はないのでしょうか？

そんなときに便利なのが「絶対参照」です。絶対参照を使うと、数式をオートフィルやコピーしても、セルの参照位置が動かないように固定できるのです。もしB7で固定できれば、オートフィルするだけで全て計算できるので、とても楽ですね。

絶対参照は数式を修正することで実装できます。具体的には、固定したいアルファベットや数字の左側に「$」記号を書き足すのです。「$」（ドルマーク、ドル、ダラス）記号は、キーボードのShift+4キーで入力できます。

実際に絶対参照を使ってみましょう。下の例では、数式を修正し、B7セルを絶対参照にしたことで、オートフィルしても参照位置が移動しなくなっている様子を確認できます。

ちなみに、この絶対参照による位置の固定効果は、「オートフィル」をしたときだけでなく、数式を「コピー」したときも同様に働きます。たとえ数式を別の場所にコピーしても、必ず同じ場所を指すというわけです。

このように絶対参照は、数式や関数をオートフィルしたりコピーしたりする際に、セルの参照位置を固定したい時に使用します。

具体的には数式を編集して、固定したいアルファベットや数字の左側に「$」記号を書き足しましょう。

今回の練習問題では、このようにセル参照位置の固定が必要なものを集めています。

次の「RANK関数」シートに進みましょう。「RANK.EQ」という関数を使う練習問題があります。「RANK.EQ」関数は、あるデータ範囲の中で指定した値が第何位に位置するかを自動的に計測してくれる関数です。

さまざまな商品の売上データが存在しますが、「合計」欄を見ながら、売上金額の多い順に順位を求めましょう。

数字が大きいものから１位、２位、３位……という具合に、手作業でやるのはなかなか大変ですが、RANK.EQ関数を使えば楽に求められます。

教科書を持っている場合は、第４章１１－４「RANK.EQ関数」P.156 も合わせて参照してください。

それでは実際に作業を行います。

まず、一番上にある商品「爆裂コーラ」の売上が第何位に位置するかをRANK.EQ関数を使って求めます。以下の手順で操作してみましょう。

1. 結果を表示したいセル(G4)をクリックし、「関数の挿入」ボタンをクリックして「RANK.EQ」関数を選択します。
   もしRANK.EQ関数が見つからない場合は、「関数の分類」欄を「統計」に合わせると見つかります。
   
2. RANK.EQ関数では「数値」「参照」「順序」という３つの値が必要となります。それぞれ以下のように設定します。
   それぞれの引数の意味は、後で説明します。
   1. 「数値」欄をクリックし、F4 セル（爆裂コーラの合計金額）をクリックします。
   2. 「参照」欄をクリックし、F4 から F19 セル（すべての商品の合計金額データ）をドラッグします。
      F20セルの総合計額は範囲に入れないようにしましょう。必ず１位になってしまうので。
   3. 「順序」欄をクリックし、ゼロ「0」を入力します。
      
3. 「ＯＫ」ボタンかEnterキーを押すと、F4セルに計算結果「8」が表示されます。

これで「爆裂コーラ」の売上は第８位であったことが分かります。この結果は正しいです。

RANK.EQ関数の引数は、以下のような内容になっています。

=RANK.EQ( 数値 , 参照 , 順序 )

RANK.EQ関数は「参照」の範囲にある値たちの中で「数値」で指定した値が第何位になるかを計算してくれるものであることが分かります。

ここまでは計算結果に異常はなく、とくに問題はありません。

他の商品についても同様に順位を計算するため、いつもどおり「オートフィル」機能で下方向にドラッグすると、正しくない順位が表示されます。

エラーも発生しておらず、一見すると問題ないように見えますが、１位が存在しません、また同立順位のはずの値が互いに異なっていたりします。これはおかしい！😂

原因を知るために、オートフィル後の計算式をダブルクリックします。すると関数が参照しているセル範囲が色枠で表示されます。

「参照」欄で指定した範囲、上の図では赤い枠にあたる範囲がオートフィルによって本来の位置から下方向へずれた位置に移動し、表からはみ出してしまっていることが分かります。このために正しい計算ができなかったのです。

一旦ESCキーを押して入力をキャンセルします。

選択位置がずれるのは良くありません。範囲が動かないように固定したいです。どうやら絶対参照の出番のようです。

「参照」で指定した範囲が、オートフィルをしても移動しないよう固定します。

一番上の、爆裂コーラの順位を求めた数式は正しい範囲を設定していました。この式をベースに修正を施します。

一番上のG4セルをクリックして、「関数の挿入」ボタンを再びクリックします。すると、関数入力用のパネルが再び開きます。

「参照」欄に書かれている「F4:F19」に「$」記号を書き足して固定します。「F4:F19」の文字を範囲選択してF4キーを一回押せば、自動的に「$」記号が書き足されて「$F$4:$F$19」になります。
「ＯＫ」ボタンを押して修正完了です。

その後、修正を施した数式を、下に向かって再びオートフィルし直せば完成です。
以前の数式をわざわざ消去する必要はありません。新しい数式をオートフィルして上書きしてしまいましょう！😁

上の動画ではオートフィルし直した結果を再度チェックしています。
「参照」の範囲が固定され、数式をオートフィルしても移動していない様子が分かります。
今度こそ正しくオートフィルできたようですね！

練習問題で続きのシート「練習１」～「練習４」の設問にそれぞれ回答をしましょう。

きっと絶対参照を使えば楽に回答できるはずです。 絶対参照を知らないと……ずれた範囲を修正するのがとても大変な作業になるでしょう。🤣

練習問題の「絶対参照と複合参照」シートにアクセスしてください。
ここでは主に「$」記号を片方だけに付けた「複合参照」という状態について解説します。

列番号・行番号の両方に「$」が付いている「絶対参照」は、縦方向や横方向にオートフィルをしても、セルの参照位置が全く移動しません。完全に固定されます。

しかし、列番号や行番号について片方だけ「$」を削除すると、縦方向か横方向のどちらかのみ固定が解除され、セルの参照位置が縦方向か横方向にだけ移動するようになります。 この状態を「複合参照」と呼びます。

複合参照を作りたい場合は「$」記号を記入する際にF4キーを何度も押します。押すたびに「$」記号の記入位置が変化するので、簡単に複合参照を作成できます。

練習問題で「あ」と書かれたセルを横方向や下方向へオートフィルし、セルの参照範囲がどう移動するか確認しましょう。

オートフィルを縦方向や横方向に行った際、「$」記号の位置によって以下のような動作となります。

「固定したいアルファベットや数字の左側に『$』記号を書く」というルールから、どちらが固定されているのかを考えると分かりやすいでしょう。

たとえば「$A3」ならA列で固定されて動けない、「A$3」なら3行目で固定されて動けない、といった具合です。

そしてそれらが両方設定されて「$A$3」になると、A3で固定され、行も列も全く動けないという意味になります（上で使った「絶対参照」ですね）。

なお「列」と「行」という言葉が出てきますが、縦方向が「列」で、横方向が「行」となっていますので、把握しておきましょう。（EXCELは基本、横書き文化なので「横方向が行」と考えると分かりやすいかも知れません）

「$A3（A列でのみ固定）」なら上の図のオレンジの範囲をオートフィルできる、「A$3（3行目でのみ固定）」なら青い範囲をオートフィルできる、という具合です。²⁾

複合参照は、計算式をオートフィルするときに、特定の 行・列の値のみを参照するよう移動範囲を限定させたい時に便利です。

練習問題の残りのシート「練習5」～「練習8」の設問は、この複合参照を利用すると効率が良い問題になっていますので、うまく複合参照を活用して回答してください。

各練習問題の詳細を下に示します。

この問題は、あるクラスの５科目テストの点数表です。「RANK.EQ」関数を使用して、科目ごとにクラス内順位表を求めてください。

例えば国語の場合、RANK.EQ関数は以下のような設定になります。

ここでも、先の問題と同様に「参照」の範囲が重要となります。前回の問題と同じように「絶対参照」で完全に固定するとどうなるでしょうか？ 国語においては問題ないのですが、数学や英語など、他の科目も計算するために右方向にオートフィルをすると問題が発生します。

たくさんエラーが発生してしまいました……。
何が起こっているのか調べるために、オートフィルした数式、例えば英語科目にある数式をダブルクリックして確かめます。

「参照」の範囲は、本来は英語科目の点数を元にしないといけませんが「絶対参照」のせいで国語の位置から全く移動していないことが分かります。これでは正しく順位の計算ができません。英語や数学のように他の科目の計算を正しく行うためには、「参照」の範囲が横方向に移動できるようにして、該当科目の範囲を参照するようにする必要があります。

しかし、上下方向に範囲が移動してしまうとやはり正しく計算できないので、上下方向は移動させたくありません。つまり、上の図で「３行目から２８行目まで」という縦の位置は固定したいわけです。

国語の場合の「参照」範囲はC3:C28なので、３と２８だけを固定するために、C$3:C$28のように固定する必要があります。３と２８の左に「$」を追加しましょう。これが、複合参照です。

というわけで、RANK.EQ関数の引数を以下のように修正して、再度オートフィルし直すと完成です。

誤った値は削除しなくても、正しい数式でオートフィルし直せば上書きになるので問題ありません。

この問題は、あるホテルの宿泊料金一覧表です。C6～F10 セルの範囲に料金を計算します。計算方法はシンプルで「料金」×「人数」という単純な掛け算とします。

基本となる数式は以下のようになります³⁾。

もちろんこの数式のままオートフィルをすると、正しい料金計算は出来ません。セルの参照位置がずれるためです。

この表では「料金」と「人数」を掛け算するのですが、掛け算をする範囲に注意してみましょう。「料金」は全て「５行目」に書かれており、「人数」は全て「Ｂ列」に書かれていることが分かります。

ということは、どこを固定すればよいのか分かりますね。そう、５とＢで固定すればよいのです。５とＢの左に「$」を追加しましょう。

上記の式を作り、再度オートフィルすれば完成です。

この問題は、上と左にある緑色のセルに書かれた数字を掛け算する問題です。

一つ前の練習６と同じ考え方で回答できます。計算量は多いですが、オートフィルの仕方を工夫すればそれほど時間はかかりません。下の例を参考にしてください。

• 基本となる数式は、掛け算です。緑のセルの数値同士を掛け算しましょう。（左上隅の数式は、=C2*B3 または =B3*C2）
• この問題では、上の数字は全て２行目に書かれており、左の数字は全てＢ列に書かれているので、２とＢで固定すれば良いことが分かります。数式を編集して、２とＢの左に「$」を追加しましょう。（考え方は、一つ前の問題と同じです）
• 10、11、12……30 のように連番を振りたい場合は、数値が連続する２つのセルを選択した状態でオートフィルをしましょう。
  • 上記の工夫をせずに、単純に 9 を右に向かってオートフィルすると 9、9、9……のようにコピーになります。
• 広い範囲をオートフィルしたい場合は、縦または横に一旦オートフィルした後、縦または横３０セルが選択されている状態で、あらためて横または縦にオートフィルし直すと、２回のドラッグ操作ですべての範囲をオートフィルできます⁴⁾。
• 数式は本当に正しくできていますか？ 真ん中あたりのセルを適当に選び、数式が合っているかダブルクリックしてチェックすると良いでしょう。参照しているセルを表す赤い枠と青い枠が、上端と左端の緑色の数値を指していればＯＫです。問題なければESCキーを押して入力をキャンセルしましょう。
  

絶対参照と複合参照を両方使う問題です。

荷物を運ぶときの料金を計算するものです。

基本料金 1,000円 に対し、距離と重さに応じてそれぞれ追加料金が加算されます。追加料金は表の左と上に「＋○○」と書かれている値段です。

つまり、「基本料金＋距離追加料金＋重さ追加料金」という足し算なわけですね。
たとえば神奈川県に重さ 3.5kg の荷物を送るなら、1,000 + 300 + 240 = 1,540 円 という計算です。

基本的な説明については、シート上に書かれているコメントを参考にしてください。
考え方は上の問題と同じなので、いままでのやり方を応用して回答してください（ノーヒントでやってみましょう！ どうしても分からなければ先生に質問するのもＯＫ😆）。

• 絶対参照・複合参照は、数式を「オートフィル」したりコピーしたりする際に、効率を上げるための技術です。
• その効果は「オートフィルやコピーをするときに、セルの参照位置を移動させない」ことです。
• 数式内で、移動させたくない数字やアルファベットの左側に「$」を追記すれば効果を発揮します。
  • F4キーを押せば、素早く「$」を入力できます。

練習問題内の各シートの設問に解答後、「学生番号 氏名 絶対参照」というファイル名をつけて保存して、moodleに提出してください。
(例) 1223451 山田太郎 絶対参照.xlsx

提出期限は、次回の授業日いっぱいとします。

学習支援システム moodle
https://cclms.kyoto-su.ac.jp/

以上で今回の作業は終了です。おつかれさまでした。

戻る