サンプルデータcellref_es.xlsx「セルの参照方法(演習).xlsx」を開きます。
相対参照
計算式が入力されているセルをコピーしたり移動したりすると、計算式で使われているセル参照は自動的に貼り付け先のセル番地に合わせて調整されます。これを「相対参照」といいます。
シート「相対参照・絶対参照」を選択します。
ここではまず、セルF4に1人目の受験者の点数の合計を関数を使って求めた後で、残りの受験者の「合計」を先に求めた計算式をコピーして求めます。
このとき、コピー先のセルに入力された計算式には、自動的に対応するセル番地が入力されます。
- 1. セルC4に移動します。
- 2. Shift + 右方向 キーを押してセルF4までを範囲選択します。
- 3. Alt キーを押してリボンに移動します。
- 4. 左右方向 キーを押して「ホーム」タブに移動します。
- 5. Tab キーを押して「合計」に移動してEnter キーを押します。「合計」のメニューが開きます。
- 6. 主な関数一覧が表示されるので、上下方向 キーを押して「合計」に移動してEnter キーを押します。セルF4に1人目の受験者の合計点が表示されます。
次に、コピー元、セルF4をコピーし、2人目の受験者の合計点、セルF5に計算式を貼り付けます。
- 1. セルF4に移動します。
- 2. Alt キーを押してリボンに移動します。
- 3. 左右方向 キーを押して「ホーム」タブに移動します。
- 4. Tab キーを押して「コピー」に移動してEnter キーを押します。「コピー」のメニューが開きます。
- 5. 上下方向 キーを押して「コピー」に移動してEnter キーを押します。セルF4の内容がコピーされます。
＊ アクセスキー： Alt H C
＊ ショートカットキー コピー： Ctrl + C キー
- 6. コピー先のセルに移動します。ここでは、2人目の受験者の合計を求めるセルF5に移動します。
- 7. Alt キーを押してリボンに移動します。
- 8. 左右方向 キーを押して「ホーム」タブに移動します。
- 9. Tab キーを押して「貼り付け」のボタンに移動してEnter キーを押します。「貼り付け」のメニューが開きます。
- 10. 右方向 キーを押して「数式」に移動してEnter キーを押します。セルF5に2人目の受験者の合計点が表示されます。
(注) 1人目の合計点を求める計算式は「SUM(C4:E4)」です。上記のようにこの計算式を2人目の受験者の合計点を求めるセルにコピーすると、計算式は「SUM(C5:E5)」のように自動的に対応するセル参照が調整されます。
このとき、セル参照は相対的に変化しています。これは「相対参照」の機能を利用しているからです。
残りの受験者の合計点、セルF6からF17を計算式を貼り付けて求めます。
- 1. セルF6からF17までを範囲選択します。
- 2. Alt キーを押してリボンに移動します。
- 3. 左右方向 キーを押して「ホーム」タブに移動します。
- 4. Tab キーを押して「貼り付け」のボタンに移動してEnter キーを押します。「貼り付け」のメニューが開きます。
- 5. 右方向 キーを押して「数式」に移動してEnter キーを押します。セルF6からF17に他の受験者の合計点が表示されます。
絶対参照
サンプルデータのG列には全体の中での順位を表示させますが、これにはRANK関数を使用します。
ただ、RANK関数の計算式で相対参照を利用すると、2人目以降の貼り付け先で正しい結果が表示されません。
つまり、順位を求めるRANK関数では、対象となる範囲は常に同じである必要があります。
この場合は、セル参照を固定する絶対参照を利用します。
絶対参照にする場合は、セル参照に「$」(ドル)マークをつけます。
- 1. セルG4に移動します。
- 2. F2 キーを押して編集状態にします。
- 3. 『=RANK()』と入力します。
- 4. Shift + F3 キーを押します。RANK関数の引数を入力するウィンドウが開きます。
- 5. 「数値」のエディット ボックスが選択されているので、合計点の順位をつけるセル番地を入力します。ここでは『f4』と入力します。
- 6. Tab キーを押して「参照」のエディット ボックスに移動します。
- 7. 順位をつける対象となる範囲を入力します。ここでは、合計点が入力されたセルF4からF17を入力します。
- 8. まず『f4』と入力してF4 キーを押します。セルF4が絶対参照になります。
(注1) 絶対参照になると表示が「$F$4」となります。
- 9. 続けて『:(コロン)f17』と入力してF4 キーを押します。これでセルF4からF17までが絶対参照になります。
(注2) このとき、セル参照に$マークがつき、「$F$4:$F$17」となります。
- 10. Tab キーを押して「順序」のエディット ボックスに移動します。ここでは合計点の高い順位を求めるので『0』と入力します。
- 11. Tab キーを押して「OK」のボタンに移動してEnter キーを押します。1人目の順位がセルG4に表示されます。
残りの受験者の順位を数式のコピーで求めます。
- 1. セルG4に移動し、ctrl + C キーを押してコピーします。
- 2. セルG5からG17までを範囲選択します。
- 3. Alt キーを押してリボンに移動します。
- 4. 左右方向 キーを押して「ホーム」タブに移動します。
- 5. Tab キーを押して「貼り付け」に移動してEnter キーを押します。「貼り付け」のメニューが開きます。
- 6. 右方向 キーを押して「数式」に移動してEnter キーを押します。セルG5からG17に他の受験者の順位が表示されます。
(注3) 上記のようにセルG4の数式のセル参照は固定されています。これによって、2人目以降の順位を求める際、数式をコピーしても合計点が入力されたセル参照が相対的に変化していくことはありません。
複合参照
セル参照の行、列どちらか一方を固定する操作を複合参照といいます。
シート「複合参照」を選択します。
ここでは複合参照を利用して旅行プランの料金表を作成します。
まず、プランＡ、かつ食事グレードがなしの料金を求めます。複合参照を利用して、指定するセルの行もしくは列を固定した計算式を入力します。
- 1. セルC8に移動します。
- 2. セルC8に『=$C$2+$B8+C$7』と入力します。料金「30000」が表示されます。これは基本料金と同様です。
(注1) セルC2の基本料金は行/列とも固定しますが、セルB8のオプションツアーは、列のみを固定し、セルC7の食事グレードは行のみを固定します。
次に、プランAかつ食事グレードありの料金をオートフィルのコピー機能を利用して求めます。
- 1. セルC8に移動し、Shift + 下方向 キーを押してセルC9までを範囲選択します。
- 2. Alt キーを押してリボンに移動します。
- 3. 左右方向 キーを押して「ホーム」タブに移動します。
- 4. Tab キーを押して「フィル」に移動してEnter キーを押します。「フィル」のメニューが開きます。
＊ アクセスキー： Alt H F I
- 5. 上下方向 キーを押して「下方向へコピー」に移動してEnter キーを押します。セルC9に食事グレードありの料金が表示されます。
同様に、プランS、SSの食事グレードなし、ありをそれぞれオートフィルのコピー機能を利用して求めます。
- 1. セルC8からE9までを範囲選択します。
- 2. Alt キーを押してリボンに移動します。
- 3. 左右方向 キーを押して「ホーム」タブに移動します。
- 4. Tab キーを押して「フィル」に移動してEnter キーを押します。「フィル」のメニューが開きます。
＊ アクセスキー： Alt H F I
- 5. 上下方向 キーを押して「右方向へコピー」に移動してEnter キーを押します。セルD8・D9・E8・E9にそれぞれのプランの料金が表示されます。
(注2) この旅行代金表はC2のセルに基本料金、B列に食事グレードなし、もしくはありの料金、7行目にそれぞれのプランの追加料金が表示されています。
上記のように、プランA、かつ食事グレードなしの料金を求める式を入力するときに、基本料金が表示されたセルC2に食事グレードとプランAの料金をそれぞれプラスする計算式を入力します。
求めた式を他の料金にコピーするには食事グレードの列とプランの行のみセル参照を固定する必要があります。
つまり、食事グレードの追加料金が表示されたB8のB列を固定し、プランAの追加料金が表示された7行のセル参照を固定します。
操作の結果はサンプルデータcellref_kk.xlsx「セルの参照方法(結果).xlsx」をご覧ください。