2006年度経済情報処理

第3回　絶対参照と計算式

• Excel の計算式は、コピーすると参照しているセルが自動的に変わって便利
• しかし、時にはコピーしてもいつも同じセルを参照していてほしいことがある
• そんなときに使う方法が「絶対参照」

↑経済情報処理（2006）ホームページに戻る

3.1 計算式とセル参照

• Excelの計算式は、=から始まる文字列として表現される
  • =1+1
  • =A1+3
  • =B1*C2
• あるセルに入力してある計算式で、別のセルの値を使うことを「参照(reference）」とよぶ。上の例で言えば、=A1+3 の A1　や =B1*C2 の B1 や C2 が参照である
• 他のセルの値を計算に使うことで、Excel の計算式は関連するセルの値が書き換えられた時に自動的に再計算するようにできている

例題1：　消費税を含む支払額の計算

外税の商品に対して、消費税額と支払総額を計算する。たとえば、八百屋で買い物をした際に合計額から簡単に消費税を出せる表を作ることを考えよう。金額は100円から2500円まで50円刻みで計算できるようにする

Step1. 100円の時を考える

この場合、

• セルA2 → 100
• セルB2 → =A2*0.05
• セルC2 → =A2+B2

という計算式を使うことになる。これらの計算式は

• セル A2の値は、100という数値
• セル B2 の値は セル A2 の値に消費税率0.05(=5%)を乗じたもの
• セル C2 の値は セル A2 の値とセル B2 の値を加えたもの

であることを意味している。Excel の計算式では、計算式の中にセル番地を書くと、そのセルの値に置き換えて計算する。上の例で言えば

• セル B2 の値は セル A2 の値（100）に消費税率0.05(=5%)を乗じたもの。つまり 5
• セル C2 の値は セル A2 の値（100）とセル B2 （5）の値を加えたもの。つまり 105

ということである。参照しているセルの値が変われば、計算結果も当然変わる。たとえば、セルA2 を 200に変更すると、セル B2 の値は 10、 セル C2 の値は 210　となる。

Step 2. オートフィルを使って、金額を50円刻みで金額を入力する

50円間隔で数値を入れるだけなので、100円の下に150円を入力してからオートフィルを使えば簡単に入力できる

Step 3. 消費税額と支払い総額の計算式をコピーする

練習問題1. 複利計算を行う

利息の計算期間が1年のとき、年利5%で10000円を10年預金する。10年後の元利合計を求めなさい。

ヒント

• 年利5%のとき、1年目の利子は 10000円×5% = 500円であるから、1年後の元利合計は 10000 + 500 = 10500円となる
• 2年目の利子は、元金が 10500円となっているので 10500円×5%= 525円である。2年後の元利合計は 10500 + 525 = 11025円となる
• 3年後の利子は、元金が 11025円となっているので 11025円×5%=551.25円である（*1）。3年後の元利合計は 11025+551.25=11576.25円となる
• 

（*1）実際の預貯金における1円未満の利子に関する扱いは、契約によって異なる

練習問題2. 買い物をするためにショッピングリボ払いを利用した。支払い条件は以下の通り

1. 毎月の利用締め日は月末日とする
2. 支払い日は利用締め日の翌月末とする
3. 利用締め日の利用残高に対して、支払日までの間に手数料がかかる。手数料は年間 13.2%で、月間は 13.2%÷12=1.1%である
4. 商品購入日から利用締め日までは手数料がかからない
5. 毎月の支払い額は手数料を含んで 5000円。支払い対象残高+手数料が5000未満だった場合は支払い対象残高+手数料

このショッピングリボ払いを使って、7月に 60000円の買い物をした場合に、すべて支払い終わった時点での支払い総額を計算しなさい。

ヒント

支払額を最大 5000円にするためには、払わなければならない額と 5000円の小さい方を支払額にするという計算式を用いればよい。具体的には min という関数が利用できる。

3.2 より進んだ参照（絶対参照）を行ってみる

• 例題1では消費税率が 5% であることを前提として、計算式の中に 5%という定数を埋め込んでいた
• しかし、消費税はかつて 3% だったものが 5% に引き上げられた、さらに近い将来引き上げがあると予想されている
• そこで、消費税率を計算式の中に埋め込むのではなく、別にデータとして与えて計算式の中から参照してみる

例題2: 消費税率を変えられるようにして、消費税額と支払総額を計算してみる

外税の商品に対して、消費税額と支払総額を計算する。たとえば、八百屋で買い物をした際に合計額から簡単に消費税を出せる表を作ることを考えよう。金額は100円から2500円まで50円刻みで計算できるようにする。ただし、消費税率を簡単に変えられるようにすること

Step 1. 消費税率をセルにデータとして入れる

今回は消費税率を定数として計算式に入れないので、セル B1に消費税率を入力して、計算式から参照する

Step 2. 100円の時の計算式を入力する

例題1と同様にセルに計算式を入れていくが、今度は 0.05という定数の代わりに セル B1 を参照するので消費税額の計算式が違うことに注意。具体的には

• セルA4 → 100
• セルB4 → =A4*B1
• セルC4 → =A4+B4

という計算式を入れてみよう。

Step 3. オートフィルを使って、金額を50円刻みで金額を入力する

50円間隔で数値を入れるだけなので、100円の下に150円を入力してからオートフィルを使えば簡単に入力できる。これは例題１と全く同じ

Step 4. 消費税額と支払い総額の計算式をコピーする

これも例題1と同じ……と言いたいが、うまくいっていないように見える

商品価格の時の消費税額は 150×0.05であるから 0のわけはない。おかしい。とりあえずセル B5 にどんな計算式がコピーされたか確認してみる必要があるだろう

期待する計算式は、商品価格（ A5 ）×消費税率（ B1 ）だが、実際にセル B5 に入っている計算式は =A5*B2である。セル B2 はデータが入っていない空白セルなので、計算式から参照されるときには 0 扱いになる。0を掛けているので当然計算結果は 0となる。

計算結果が 0 になる理由は分かったが、 Excel ではこのような計算式をコピーで入力できないのだろうか。だとしたら大変面倒なので Excel には「コピーしても調整されずに、計算式の中で常に同じセルを表す番地表現」が存在する。このような参照形式を「絶対参照」と呼び、 $B$1　のようにセル番地の表現に $マークをつける。これに対して何もついてないセル番地の表現は「相対参照」と呼ぶ。

では、絶対参照を使ってやり直そう。この場合は Step 2 で計算式を入れるところを修正すれば、Step 3以降は同じ手順で大丈夫

Step2改. 絶対参照を使った計算式を入力する

セル B1 は計算式をコピーしても変わっては困るので絶対参照として入力する

• セルA4 → 100
• セルB4 → =A4*$B$1
• セルC4 → =A4+B4

このあと、Step 3., Step 4を行うと、以下のようになる

計算式を見ると、以下のようになっており、確かに絶対参照にした部分は計算式をコピーしても変わっていない

Tips: Excel での内部表現から相対参照と絶対参照を理解する

練習問題3. 元本と金利を変えられるようにして複利計算を行う

利息の計算期間が1年のとき、

• セル B1 に　元本額
• セル B2 に　年利率

を入れる。10年後の元利合計を求めなさい。

練習問題4. 買い物をするためにショッピングリボ払いを利用した

支払い条件は以下の通り

1. 毎月の利用締め日は月末日とする
2. 支払い日は利用締め日の翌月末とする
3. 利用締め日の利用残高に対して、支払日までの間に手数料がかかる。年間手数料率はセル B1　に入力してあり、月間手数料率は　年間手数料率÷12で計算する
4. 商品購入日から利用締め日までは手数料がかからない
5. 毎月の支払い額は手数料を含んだ額を セル B2 に入力してある。支払い対象残高+手数料が毎月の支払額未満だった場合は支払い対象残高+手数料となる

このショッピングリボ払いを使って、7月にセル B3 に入力してあるだけの買い物をした場合に、すべて支払い終わった時点での支払い総額を計算しなさい。

3.3 いろいろな絶対参照を使いこなす

• 絶対参照は、行と列の両方を一意に指定するものだけではない
• 行または列だけを一意に指定する絶対参照も場合によっては便利
• コピーなどを考えると、一意に指定する部分は少ない方が一般的に使い勝手がいい

絶対参照には、

1. $A$1 のように列番号も行番号も一意に指定する表現
2. $A1 のように、列番号は一意に指定するが行番号は計算式が入っているセルからの相対的な位置で指定する表現
3. A$1 のように、列番号は計算式が入っているセルからの相対的な位置で指定するが、行番号は一意に指定する表現

の3種類がある。この3つには以下のような違いがある。

• $A$1 は、行も列も一意に指定しているため、どのようにコピーしても参照しているセルは変わらない。ワークシートのどこからでも同じセルを参照したいときに使うとよい。たとえば セル B1 に =$A$1+3 という計算式を入れて、これをセル C1, B2, C2 にコピーした場合、
  • セル C1 → =$A$1+3
  • セル B2 → =$A$1+3
  • セル C2 → =$A$1+3
  • 
  のようにコピーされる
• $A1はワークシートのどこからでも同じ列を指定しつつ、行に対しては計算式が入っているセルからの相対的な位置で指定するようになっている。そのため、$A1は横方向に計算式をコピーした際は同じセルを参照するが、縦方向に計算式をコピーしたときは行番号が変わる。たとえばセル B1 に =$A1+3 という計算式を入れて、これをセル C1, B2, C2 にコピーした場合、
  • セル C1 → =$A1+3
  • セル B2 → =$A2+3
  • セル C2 → =$A2+3
  • 
  のようにコピーされる
• A$1　はワークシートのどこからでも同じ行を指定しつつ、列に対しては計算式が入っているセルからの相対的な位置で指定するようになっている。そのため、A$1は縦方向に計算式をコピーした際は同じセルを参照するが、横方向に計算式をコピーしたときは列番号が変わる。たとえばセル B1 に =A$1+3 という計算式を入れて、これをセル C1, B2, C2 にコピーした場合、
  • セル C1 → =B$1+3
  • セル B2 → =A$1+3
  • セル C2 → =B$1+3
  • 
  のようにコピーされる

例題3: 消費税が商品の種類によって異なる場合の消費税計算表をつくる

消費税率が高率に引き上げられる際、消費税の逆進性を抑えるため生活必需品（たとえば食料品）には低い税率を適用する特例をもうけることが一般的である。このようなケースでは、同じ金額の商品を購入しても商品の種類によって適用される税率が異なる。そこで

• 食料品　→ 5%
• それ以外 → 15%

の消費税を仮定して、消費税込みの支払額を計算する表を作成してみる。

Step1. 表の大枠を作成する

縦方向に購入金額（100円〜2500円、50円刻み）、横方向に消費税率（5%, 15%）を取って下図のように入力する

Step2. 支払総額の計算式を入力する

セル B7 → = $A7+$A7*B$3

セル B7 は、食料品を 100円買ったときの消費税込みの支払額を計算しているので、購入金額＋購入金額×消費税率となる（*2）。このあとでコピーすることを前提としているため、金額の欄（A列）を参照している部分と、消費税率（3行）を参照している部分が絶対参照になっている。

（*2）もちろん、= $A7*(1+B$3 )と書いた方が座りがいいと思う人はそう書いてもかまわない

Step3. セル B7 の計算式をコピーする

最初からコピーするつもりで計算式を作ってあるので、今度は単純にコピーすれば大丈夫。

練習問題5: 例題3では、計算式の中で行あるいは列だけを絶対参照にしたが、これを行・列の両方を絶対指定した場合にはどのような不具合があるか試しなさい。

練習問題6: 元本を与えたときに、金利によって元利合計がどう変わるかを計算する

利息の計算期間が1年のとき、

• セル B1 に　元本額
• セル B3〜K3に利子率（1%〜10%、　1%刻み）

を入れる。元本を変えれば再計算されるように注意した上で、各利子率に対応する 10年後の元利合計を求めなさい。

入力例：

練習問題7: 以下の表はきつね食品の営業資料である。このデータを使って以下の計算をしなさい

1. きつね食品、たぬき物産、いなり産業各社の年間総売上高を計算しなさい
2. きつね食品、たぬき物産、いなり産業各社の年間総売上高を分母に、各月の売上高を分子にして、年間売上高に占める各月の割合を計算しなさい
3. きつね食品、たぬき物産、いなり産業3社の年間総売上高合計を分母にして、この 3社の売上がしめる割合をそれぞれ計算しなさい

練習問題8: 以下の表は我が国の一般会計歳入の内訳を示している。各歳入項目が歳入全体にしめる割合を計算しなさい

練習問題9: 19×19の計算表をつくる

インドでは日本で言う九九の代わりに、19×19までの積をすべて覚えるという。このために 19×19の表をつくりなさい

練習問題10: 効用関数の値を計算する

Aさんの効用関数が U(x, y) = x y で与えられているとする。x, y　がそれぞれ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 の値を取るときの効用関数を表として計算しなさい

練習問題11. 買い物をするためにショッピングリボ払いを利用した

支払い条件は以下の通り

1. 毎月の利用締め日は月末日とする
2. 支払い日は利用締め日の翌月末とする
3. 利用締め日の利用残高に対して、支払日までの間に手数料がかかる。年間手数料率はセル B1　に入力してあり、月間手数料率は　年間手数料率÷12で計算する
4. 商品購入日から利用締め日までは手数料がかからない
5. 毎月の支払い額は手数料を含んだ額として 3000円、5000円、10000円、15000円、18000円、20000円、25000円、30000円、40000円、50000円から選択できる
6. 支払い対象残高+手数料が毎月の支払額未満だった場合は支払い対象残高+手数料となる

このショッピングリボ払いを使って、7月にセル B2 に入力してあるだけの買い物をした場合に、すべて支払い終わった時点での支払い総額を毎月の支払い額ごとに計算しなさい。

↑経済情報処理（2006）ホームページに戻る

©2006, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.