セルの参照と数式のコピーペースト

セルのアドレス参照と数式コピーを利用して、同じような計算を素早く行う

ポイント

ここで利用する表データ：elementary-data.xls

課題

図はMicrosoft Excelでの例です
  1. セル値のアドレス参照を使って、売り上げデータから「店平均」、「品別合計」、「店別合計」を計算する。 また、数式で使うセルの値を変更すると、自動的に再計算されることを確かめる。 。 左図が直接計、右図は総和関数SUMと平均関数AVERAGEを使った様子を示す。

  2. 計算式で用いるセルのアドレス参照には、次の2つの指定方法がある。

    相対セル参照
    相対セル参照（であることを確認するに）は、ただそのままセルアドレスで表されるセル指定表示によるセル参照である（ことを見ればよい）。 たとえば B5 と指定する。 ただし、その見かけのセルアドレスとは裏腹に、非常に大きな意味を持っており、以下で説明するように、表計算においては深い意味を持っている。

    相対セル参照では、現在数式を入力しているセル位置（たとえば、(x0,y0) ）からみて、目的のセル（たとえば、(x1,y1)）がどこに位置するかを（コンピュータ内部では）相対位置（dx,dy), dx=x1-x0, dy=y1-y0）によって（でも、表示されているのは目的のセル位置(x1,y1)でしかないのだけど）指定する。

    絶対的セル参照
    数式が入力されているセル位置（上の例では、(x0,y0)）に関係なく、数式内で参照するセル位置を絶対的座標で（上の例では、(x1,y1)）指定する。 絶対セル参照は、たとえば、 $E$5 のように、記号 $ を使ってセル位置を絶対的位置として指定する。

    たとえば、上の左図では、セル B10 に入力された数式 =B5+B6+B7+B8 では、相対参照セルを使っていることがわかる。 そこで、これを次のように読むのである：

    式が入力されているセル位置相対参照セル意味
    B10 B5 セル位置から上に5目にあるセルの式値
    B6 セル位置から上に4目にあるセルの式値
    B7 セル位置から上に3目にあるセルの式値
    B8 セル位置から上に2目にあるセルの式値

    相対参照では、現在着目しているセル位置（そこに入力されている数式は数式バーに表示）と目的セルのセル位置との相対関係（オフセット）によって、目的のセル位置が指定されている。 ただし、その目的のセル位置の「表記」にはsheet上のセル位置を使っているだけであることを理解しておく。

  3. 計算式を"コピー"して、隣に"ペースト"することによって、隣のセルに同じような計算式の入力をたやすく行うことができる。 数式のコピー・ペーストでは、数式が入力されているセル内の数式内で指定された相対アドレス参照セルは、ペースト先でも同じ相対的な位置関係を維持してコピーされる。

    計算式のコピー・ペースト方法： 表計算では、隣り合うセルにある数式のコピー・ペーストは頻繁に行うため、数式のコピー・ペーストについては次の特別な方法が用意されている。

    隣り合うセルの数式のコピー・ペーストは次の手順で行う。
    1. コピーしたい数式セル範囲を選択する（マウスカーソルは白十字）
    2. マウスカーソルを、選択したセルの右下にゆっくり移動して、黒十字に変化したことを確認
    3. マウス（左）ボタンを押して、数式をコピー・ペーストしたい隣のセルへとマウスをドラッグする
    4. コピー元のセルに隣り合うペースト先のセルが（水平または垂直に）一列に並んでいるときは、そのまま一気に数式をペーストしたいセルまでドラッグする

  4. C10 に入力されている計算式 =SUM(C5:C8) をコピーし、隣のセル D10 にペーストすると、セル D10 には数式 =SUM(D5:D8) が入力されることになる。 数式内容がそのままペーストされるわけではなく、数式が相対セル参照で書かれている場合には、ペースト先のセルでの計算に適合するようにセル番号が調整されてペーストされる。 数式が絶対セル参照を含んでいる場合、そのセル指定はペースト先でも調整されず、表内の特定の位置を指したままである。

  5. 各支店の売り上げ利益をセル F5 〜 F8 に計算する。 ただし、 [売り上げ利益]=[売上] * [利益率] で求めるものとし、利益率は全商品で共通であるとする。
    右図のように、F5 に計算式 = E5 * E12 として計算するのは妥当だろうか？ この計算式をセル F6〜F8 にコピーし、その結果を検討せよ。 （ヒント：売り上げ利益の計算式では、利益率が格納されているセル E12を絶対参照 $E$12 で指定する。）

研究

参考：複数セル領域の選択

たとえば、右図のB3:C7, E3:E7, B9:E11の3領域のように、複数のセル領域を同時に選択するには、コントロールキー([Ctrl])を押しながらマウスでドラッグしながら領域指定を続けて行う。