参照
- 本章で用いる実習用ファイル
数式のコピー
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 1年 | 人数 | 2年 | 人数 | |
| 2 | A組 | 33 | A組 | 29 | |
| 3 | B組 | 35 | B組 | 31 | |
| 4 | C組 | 32 | C組 | 30 | |
| 5 | D組 | 30 | D組 | 31 | |
| 6 | E組 | 34 | E組 | 32 | |
| 7 | 1年計 | 2年計 | |||
| 9 |
1年計←B2:B6の合計
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 1年 | 人数 | 2年 | 人数 | |
| 2 | A組 | 33 | A組 | 29 | |
| 3 | B組 | 35 | B組 | 31 | |
| 4 | C組 | 32 | C組 | 30 | |
| 5 | D組 | 30 | D組 | 31 | |
| 6 | E組 | 34 | E組 | 32 | |
| 7 | 1年計 | =SUM(B2:B6) | 2年計 | ||
| 9 |
B7(1年計)の数式をD7(2年計)にコピー
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 1年 | 人数 | 2年 | 人数 | |
| 2 | A組 | 33 | A組 | 29 | |
| 3 | B組 | 35 | B組 | 31 | |
| 4 | C組 | 32 | C組 | 30 | |
| 5 | D組 | 30 | D組 | 31 | |
| 6 | E組 | 34 | E組 | 32 | |
| 7 | 1年計 | 164 | 2年計 | =SUM(D2:D6) | |
| 9 |
入力セルと参照セルとの相対的な位置関係がコピーされる
相対参照
時間*時給で日々の支給額が決まる。この表の支給額(E列)を全て求めたい。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 日付 | 曜日 | 時間 | 時給 | 支給額 | |
| 2 | 10月11日 | 金 | 4.5 | ¥1,000 | ||
| 3 | 10月12日 | 土 | 6 | ¥1,000 | ||
| 4 | 10月13日 | 日 | 3 | ¥1,000 | ||
| 5 | 10月14日 | 月 | 5.5 | ¥1,000 | ||
| 6 | 10月15日 | 火 | 3.5 | ¥1,000 | ||
| 7 | 10月16日 | 水 | 6 | ¥1,000 | ||
| 8 | 10月17日 | 木 | 1 | ¥1,000 | ||
| 9 |
- セルE2に式を立てる。
A B C D E F 1 日付 曜日 時間 時給 支給額 2 10月11日 金 4.5 ¥1,000 =C2*D2 3 10月12日 土 6 ¥1,000 4 10月13日 日 3 ¥1,000 5 10月14日 月 5.5 ¥1,000 6 10月15日 火 3.5 ¥1,000 7 10月16日 水 6 ¥1,000 8 10月17日 木 1 ¥1,000 9 - オートフィルで残りのセルを入力する。
A B C D E F 1 日付 曜日 時間 時給 支給額 2 10月11日 金 4.5 ¥1,000 =C2*D2 3 10月12日 土 6 ¥1,000 =C3*D3 4 10月13日 日 3 ¥1,000 =C4*D4 5 10月14日 月 5.5 ¥1,000 =C5*D5 6 10月15日 火 3.5 ¥1,000 =C6*D6 7 10月16日 水 6 ¥1,000 =C7*D7 8 10月17日 木 1 ¥1,000 =C8*D8 9
セル参照で式を立てて、オートフィルを用いて数式を入力すると、数式を入力した場所に応じて、参照元のセル番地が変化する。こうした動き方をするセル参照をセルの相対参照と呼ぶ。
Excelは初期状態では相対参照となる。
絶対参照
先の例は時給が日々変わるケースなら妥当だが、そうでない場合、無駄である(メンテナンス性が落ちる)。時給が固定なら、一カ所にまとめるのが正しい。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 日付 | 曜日 | 時間 | 支給額 | 時給 | ||
| 2 | 10月11日 | 金 | 4.5 | ¥1,000 | |||
| 3 | 10月12日 | 土 | 6 | ||||
| 4 | 10月13日 | 日 | 3 | ||||
| 5 | 10月14日 | 月 | 5.5 | ||||
| 6 | 10月15日 | 火 | 3.5 | ||||
| 7 | 10月16日 | 水 | 6 | ||||
| 8 | 10月17日 | 木 | 1 | ||||
| 9 |
- セルD2に式を立てる。
A B C D E F G 1 日付 曜日 時間 支給額 時給 2 10月11日 金 4.5 =C2*F2 ¥1,000 3 10月12日 土 6 4 10月13日 日 3 5 10月14日 月 5.5 6 10月15日 火 3.5 7 10月16日 水 6 8 10月17日 木 1 9 - オートフィルで残りのセルを入力する。
上手くいかない。なぜ?Why?A B C D E F G 1 日付 曜日 時間 支給額 時給 2 10月11日 金 4.5 ¥4,500 ¥1,000 3 10月12日 土 6 ¥0 4 10月13日 日 3 ¥0 5 10月14日 月 5.5 ¥0 6 10月15日 火 3.5 ¥0 7 10月16日 水 6 ¥0 8 10月17日 木 1 ¥0 9 A B C D E F G 1 日付 曜日 時間 支給額 時給 2 10月11日 金 4.5 =C2*F2 ¥1,000 3 10月12日 土 6 =C3*F3 4 10月13日 日 3 =C4*F4 5 10月14日 月 5.5 =C5*F5 6 10月15日 火 3.5 =C6*F6 7 10月16日 水 6 =C7*F7 8 10月17日 木 1 =C8*F8 9
「支給額」を求めるに支給額(D列)がD2→D3→…→D8と移動するに即して、時間(C列)もC2→C3→…→C8と移動するのは正しい(相対参照)。しかし時給(F2)までF2→F3→…→F8と参照元が移動してしまう。これでは正しい計算ができない。
正しく支払額を求めるためには時間は参照元が移動して欲しいが、時給は常にF2を参照する必要がある。
このように参照元(支払額)が移動しても、参照先が移動しないようなセル参照を絶対参照と呼ぶ。
- 列番号の前に$を付ける(ex. $F2)と参照元をオートフィルで移動しても、列(F列)が固定される(行は動く)。
- 行番号の前に$を付ける(ex. F$2)と参照元をオートフィルで移動しても、行(2行)が固定される(列は動く)。
- 列番号と行番号の前に$を付ける(ex. $F$2)と参照元をオートフィルで移動しても、列(F列)も行(2行)も固定される。
- 改めてセルD2に式を立てる。セルF2への参照を絶対参照とする。
A B C D E F G 1 日付 曜日 時間 支給額 時給 2 10月11日 金 4.5 =C2*$F$2 ¥1,000 3 10月12日 土 6 4 10月13日 日 3 5 10月14日 月 5.5 6 10月15日 火 3.5 7 10月16日 水 6 8 10月17日 木 1 9 - オートフィルで残りのセルを入力する。
正しく計算できた。A B C D E F G 1 日付 曜日 時間 支給額 時給 2 10月11日 金 4.5 =C2*$F$2 ¥1,000 3 10月12日 土 6 =C3*$F$2 4 10月13日 日 3 =C4*$F$2 5 10月14日 月 5.5 =C5*$F$2 6 10月15日 火 3.5 =C6*$F$2 7 10月16日 水 6 =C7*$F$2 8 10月17日 木 1 =C8*$F$2 9
セルD2に数式を入力する際、セルF2への参照は$F2・F$2・$F$2のどれが正しいのだろうか?
明らかに$F2は正しくない。セルF2の2行目を固定したいのに、$F2では2→3→…→8と動いてしまって、絶対参照にした意味が無い。
F$2・$F$2はどちらも2行目が固定され、行方向に参照元が移動しても、常にセルF2を参照できる。従ってこの二つならどちらでも構わないが、意味としては$F$2がより正しい。このシートでは時給はセルF2に入力されているのであって、どこのセルから参照しようとそこは動かない。そうである以上、列・行どのように参照元が動こうとも、つねに時給が入力されているセルであるセルF2を参照させる$F$2と入力するべきである。
課題
下表の
を全て埋めて表を完成させよ。ただしセルに数式を入力するのは
のセルのみで、残りのセルはオートフィルを用いて入力すること。
課題:人口比(
形式)
男女の合計人数と男女ごとの人口比率を求め、表を完成させよ(セルC4は100%になる)。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 性別 | 人口 | 人口比(%) | |
| 2 | 男 | 691,923 | ||
| 3 | 女 | 769,667 | 52.66% | |
| 4 | 合計 | 100.00% | ||
| 5 |
課題:納品書(形式)
本体価格に3年保証を付けた金額を税込みで「合計金額」欄に入力せよ。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 商品 | 本体 | 3年保証 | 合計金額 | |
| 2 | Camellia S9 | ¥65,000 | ¥18,000 | ||
| 3 | Magnolia A5 | ¥24,000 | ¥6,700 | ¥33,770 | |
| 4 | juice P9 | ¥18,000 | ¥3,600 | ¥23,760 | |
| 5 | Ange E9 | ¥15,000 | ¥1,200 | ¥17,820 | |
| 6 | |||||
| 7 | 税率 | 10% | |||
| 8 |
課題:販売台数(形式)
あるスマホショップの月間販売台数を示した表である。
品目ごとの金額と品目ごとの金額の割合を計算せよ。表示形式は適宜設定すること。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 価格 | 数量 | 合計(¥) | 割合(%) | ||
| 2 | Camellia S9 | ¥65,000 | 342 | |||
| 3 | Magnolia A5 | ¥24,000 | 321 | ¥7,704,000 | 10.86% | |
| 4 | juice P9 | ¥18,000 | 540 | ¥9,720,000 | 13.71% | |
| 5 | Ange E9 | ¥15,000 | 854 | ¥12,810,000 | 18.06% | |
| 6 | MM 14 | ¥28,700 | 643 | ¥18,454,100 | 26.02% | |
| 7 | 合計 | 100.00% | ||||
| 8 | ||||||
課題:家計費(形式)
1世帯あたり1年間の支出(全世帯)を示した表である。
各年ごとの品目別の構成比と2000年時に比して2020年の消費金額の増減率を求めよ。
※増減率…元の状態に比べて、新しい状態がどのぐらい増えたか(ex. 100→110なら増減(実数)は+10。元の状態(100)に対する比率は10/100で10%となる)。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 品目 | 2000年(円) | 2000年構成比(%) | 2019年(円) | 2020年構成比(%) | 増減比 | |
| 2 | 食料費 | 973,680 | 965,536 | ||||
| 3 | 住居費 | 246,334 | 6.47% | 205,235 | 5.83% | -16.68% | |
| 4 | 光熱・水道費 | 259,546 | 6.82% | 263,408 | 7.48% | 1.49% | |
| 5 | 家具・家事用品費 | 139,148 | 3.65% | 140,598 | 3.99% | 1.04% | |
| 6 | 被服および履き物費 | 206,742 | 5.43% | 135,677 | 3.85% | -34.37% | |
| 7 | 保健医療費 | 137,732 | 3.62% | 168,124 | 4.78% | 22.07% | |
| 8 | 交通・通信費 | 438,748 | 11.52% | 525,768 | 14.93% | 19.83% | |
| 9 | 教育費 | 167,089 | 4.39% | 137,937 | 3.92% | -17.45% | |
| 10 | 教養娯楽費 | 403,055 | 10.58% | 368,150 | 10.46% | -8.66% | |
| 11 | その他消費支出 | 835,862 | 21.95% | 610,115 | 17.33% | -27.01% | |
| 12 |
課題:アルバイト収入(形式)
ある学生の月ごとのアルバイト収入を月ごとにまとめた表がある。これを元に累積の収入額を月単位で計算せよ。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 月 | 収入 | 収入合計 | |
| 2 | 1月 | 35,400 | ||
| 3 | 2月 | 43,800 | 79,200 | |
| 4 | 3月 | 29,340 | 108,540 | |
| 5 | 4月 | 45,320 | 153,860 | |
| 6 | 5月 | 65,340 | 219,200 | |
| 7 | 6月 | 34,245 | 253,445 | |
| 8 | 7月 | 45,329 | 298,774 | |
| 9 | 8月 | 43,456 | 342,230 | |
| 10 | 9月 | 85,430 | 427,660 | |
| 11 | 10月 | 23,400 | 451,060 | |
| 12 | 11月 | 43,450 | 494,510 | |
| 13 | 12月 | 102,345 | 596,855 | |
| 14 |
例えば
- 2月の収入合計は1月の収入(B2)から2月の収入(B3)までを合計(SUM)したものである。
- 3月の収入合計は1月の収入(B2)から3月の収入(B4)までを合計(SUM)したものである。
- 4月の収入合計は1月の収入(B2)から4月の収入(B5)までを合計(SUM)したものである。
課題:九九表(形式)
九九表を完成させよ。
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ||
| 2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | ||
| 3 | 2 | 2 | 4 | 6 | 8 | 10 | 12 | 14 | 16 | 18 | |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 | 18 | 21 | 24 | 27 | |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 | 24 | 28 | 32 | 36 | |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 | 30 | 35 | 40 | 45 | |
| 7 | 6 | 6 | 12 | 18 | 24 | 30 | 36 | 42 | 48 | 54 | |
| 8 | 7 | 7 | 14 | 21 | 28 | 35 | 42 | 49 | 56 | 63 | |
| 9 | 8 | 8 | 16 | 24 | 32 | 40 | 48 | 56 | 64 | 72 | |
| 10 | 9 | 9 | 18 | 27 | 36 | 45 | 54 | 63 | 72 | 81 | |
| 11 |
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 | ||
| 2 | 1 | =A2*B1 | ||||
| 3 | 2 | =A3*B2 | ||||
| 4 | 3 | =A4*B3 | ||||
| 5 | 4 | =A5*B4 | ||||
セルB2をB5までコピーしたとき、「=A5*B1」の計算をして欲しいのに!
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | 1 | 2 | 3 | 4 | ||
| 2 | 1 | =A2*B1 | =B2*C1 | =C2*D1 | =D2*E1 | |
| 3 | 2 | |||||
| 4 | 3 | |||||
| 5 | 4 | |||||
セルB2をE2までコピーしたとき、「=A2*E1」の計算をして欲しいのに!
課題:ホテル宿泊料金(形式)
AHOホテルの宿泊料金は部屋の種類と宿泊日によって金額が決まる。すべての宿泊料金を求めよ。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 平日 | 休日 | 休日前日 | ||
| 2 | シングル | 7,000 | 8,400 | ||
| 3 | ツイン | 9,000 | 10,350 | 10,800 | |
| 4 | ダブル | 10,000 | 11,500 | 12,000 | |
| 5 | デラックスツイン | 25,000 | 28,750 | 30,000 | |
| 6 | |||||
| 7 | 休日 | 休日前日 | |||
| 8 | 割増率 | 15% | 20% | ||
| 9 |
課題:ツアー料金(形式)
キャメリアツアーズの河口湖ツアーでは基本料金とオプション料金を組み合わせてツアー料金が決まる。すべての料金を求めよ。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | OP料金 | シングル | ツイン | スイート | ||
| 2 | 10,000 | 22,000 | 38,000 | |||
| 3 | 食事なし | 0 | 272,000 | 288,000 | ||
| 4 | 朝食付き | 12,000 | 272,0000 | 284,000 | 300,000 | |
| 5 | 全食事付き | 45,000 | 305,000 | 317,000 | 333,000 | |
| 6 | 観光付き | 35,000 | 295,000 | 307,000 | 323,000 | |
| 7 | 全食事・観光付き | 70,000 | 330,000 | 342,000 | 358,000 | |
| 8 | ||||||
| 9 | 基本料金 | 250,000 | ||||
| 10 | ||||||
課題:点数換算(形式)
満点が異なる試験結果を100点満点換算せよ。換算後の点数は小数点以下第1位まで表示せよ。
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | 素点 | 100点満点換算 | ||||||
| 2 | 氏名 | 国語 | 数学 | 英語 | 国語 | 数学 | 英語 | |
| 3 | 高橋 理子 | 180 | 121 | 69 | 80.7 | 76.7 | ||
| 4 | 新垣 希空 | 164 | 75 | 46 | 77.0 | 50.0 | 51.1 | |
| 5 | 亀井 安美 | 153 | 135 | 71 | 76.5 | 90.0 | 78.9 | |
| 6 | 道重 ゆめの | 132 | 145 | 55 | 66.0 | 96.7 | 61.1 | |
| 7 | 田中 樹々 | 159 | 127 | 38 | 79.5 | 84.7 | 42.2 | |
| 8 | 満点 | 200 | 150 | 90 | 100 | |||
| 9 | ||||||||