情報ラボ ＞ 表計算のしくみ
セルに「計算のしかた（式）」を書いておくと、もとの数を変えたときに結果が自動で計算し直されます。ブラウザの中で動く小さな表計算で、式・関数・セルの参照・並べ替えと抽出を確かめましょう。
見本の人物・商品・金額はすべて架空です。見本を選び直すと、それまでの入力は消えます。
範囲は、1つ目のセルを押してから「範囲を選ぶ」を押し、もう1つの角のセルを押して選びます（パソコンでは Shift を押しながらクリックでも選べます）。式を入力している途中に「=」「+」「(」「,」「:」などの後でセルを押すと、そのセル番地が式に入ります。
| 関数 | 書き方 | はたらき |
|---|---|---|
| SUM | SUM(範囲, …) | 数値の合計 |
| AVERAGE | AVERAGE(範囲, …) | 数値の平均（空のセル・文字のセルは数に入れない。0 は入れる） |
| MAX / MIN | MAX(範囲, …) | いちばん大きい数 / 小さい数 |
| COUNT | COUNT(範囲, …) | 数値が入っているセルの個数 |
| COUNTIF | COUNTIF(範囲, 条件) | 条件に合うセルの個数。条件の例 ">=80" "1組" "<>"（空でない）"*部"（* は任意の文字の並び、? は任意の1文字） |
| SUMIF | SUMIF(範囲, 条件, [合計範囲]) | 条件に合う行の合計。合計範囲を省くと「範囲」を合計 |
| IF | IF(条件, 真のとき, [偽のとき]) | 条件が TRUE なら2つ目、FALSE なら3つ目の値 |
| ROUND | ROUND(数値, 桁数) | 四捨五入。桁数 1 なら小数第1位まで、0 なら整数、−1 なら十の位まで |
| RANK.EQ（RANK） | RANK.EQ(数値, 範囲, [順序]) | 範囲の中での順位。順序を省くか 0 なら大きい順、それ以外は小さい順。同じ値は同じ順位 |
| VLOOKUP | VLOOKUP(検索値, 範囲, 列番号, [検索の型]) | 範囲の左はしの列で検索値を探し、同じ行の「列番号」列目の値を返す。FALSE は完全一致、TRUE または省略は近似一致 |
演算子：+ - * / ^（べき乗）% &（文字列をつなぐ）= <> < > <= >=（比較）、かっこ ( )、範囲 :。文字列は "合格" のように " で囲みます。全角で入力した記号や数字は半角として読みます。
色のついたセル番地を押すたびに、A1 → $A$1 → A$1 → $A1 → A1 の順に切りかわります。
金色の枠が元のセルです。表のセルを押すと、そのセルの式と、参照しているセル（同じ色）が表示されます。
上のキーほど優先されます。2つ目のキーは、1つ目のキーが同じ行どうしの並べ方です。
条件に合う行だけを残します。
抽出した行を、グループごとにまとめて計算します。
表計算ソフトウェア（スプレッドシート）は、縦横のマス目に数値や文字を入れて、計算・集計・並べ替え・グラフの作成などができるソフトウェアです。Microsoft Excel や Google スプレッドシートなどがあり、成績の集計、売上や家計の管理、アンケートの集計など、身近なデータの処理に広く使われています。
表計算のいちばんの特徴は、マス目に「計算の結果」ではなく「計算のしかた（式）」を書いておけることです。もとの数を書きかえると、その数を使っている式の結果が自動で計算し直されます。電卓で計算した結果を書き写す方法では、数が1つ変わるたびに計算をやり直さなければなりませんが、表計算なら入力し直すのはもとの数だけですみます。
マス目の1つひとつをセルといいます。横の並びを行（1, 2, 3 …）、縦の並びを列（A, B, C …）といい、セルの位置は列と行を組み合わせたセル番地で表します。B3 は「B列の3行目」のセルです。
いくつかのセルをまとめて指定するときは、長方形の左上と右下のセル番地を「:」でつないだ範囲を使います。B2:D5 は、B2 を左上、D5 を右下とする 3列 × 4行 ＝ 12 個のセルです。
セルに「=」から始まる内容を入れると式になります。=B2+C2 のようにセル番地を書くと、そのセルに入っている値を使って計算します。このように、式の中でほかのセルを指定することを参照といいます。式で使う主な演算子と、計算される順番（優先順位）は次のとおりです。上にあるものほど先に計算されます。
| 順番 | 演算子 | 意味 |
|---|---|---|
| 1 | : | 範囲（B2:B9 など） |
| 2 | - | 負の符号（−1 など） |
| 3 | % | パーセント（50% は 0.5） |
| 4 | ^ | べき乗（2^3 は 8） |
| 5 | * / | かけ算・割り算 |
| 6 | + - | 足し算・引き算 |
| 7 | & | 文字列をつなぐ |
| 8 | = <> < > <= >= | 比較（等しい、等しくない、より小さい、より大きい、以下、以上） |
同じ順番の演算子は、左から順に計算されます。かっこ ( ) の中は先に計算されるので、=2+3*4 は 14、=(2+3)*4 は 20 です。比較の結果は TRUE（真）か FALSE（偽）の論理値になり、IF 関数の条件などに使います。
表計算では、1つのセルに作った式をほかのセルへコピーして使い回します。このとき、式の中のセル番地は、コピーした方向へ同じだけずれます。C2 の =B2/B7 を1つ下の C3 へコピーすると =B3/B8 になります。このように、コピーするとずれる参照を相対参照といいます。式は「B2 というセル」ではなく、「この式のあるセルから見て、左に1つのセル」という位置の関係を覚えている、と考えると分かりやすくなります。だから、1行目に作った「その行の合計」の式を下へコピーするだけで、どの行でも正しくその行の合計が求められるのです。
コピーしてもずれては困るときは、列や行の前に $ をつけて固定します。これを絶対参照といいます。列か行の片方だけを固定する書き方は複合参照といいます。A1 のセルにある式を、2行下・2列右の C3 へコピーしたとき、参照は次のように変わります。
| 元の参照 | 種類 | C3 へコピーすると |
|---|---|---|
$A$1 | 列も行も固定（絶対参照） | $A$1（変わらない） |
A$1 | 行だけ固定（複合参照） | C$1 |
$A1 | 列だけ固定（複合参照） | $A3 |
A1 | どちらも固定しない（相対参照） | C3 |
Excel では、式を編集しているときに F4 キーを押すと、参照の種類を切りかえられます。
割合を =B2/B7 で求めて下へコピーすると、売上の B2 だけでなく、合計の B7 まで B8、B9 …とずれてしまいます。B8 は空のセルなので 0 として扱われ、0 で割ることになって #DIV/0! になります。合計のセルは動いてほしくないので =B2/$B$7 と書きます。下へコピーするときに変わるのは行だけなので、行だけを固定した =B2/B$7 でも正しく計算できます。
もっと気をつけたいのは、エラーが出ないまちがいです。税率を1つのセルに入れて =A2*(1+E1) と書き、$ をつけずに下へコピーすると、参照先の E2、E3 …は空のセルなので、エラーにならずに「税抜価格のまま」という誤った値が出ます。式をコピーしたら、いくつかのセルを選んで、参照しているセルが正しいかを確かめる習慣をつけましょう。
関数は、よく使う計算をひとまとめにしたしくみで、=関数名(引数) の形で書きます。かっこの中に渡す値を引数といい、複数あるときは「,」で区切ります。
| 関数 | 例 | はたらきと注意 |
|---|---|---|
| SUM | =SUM(B2:B9) | 数値の合計。 |
| AVERAGE | =AVERAGE(B2:B9) | 数値の平均。範囲の中の空のセルや文字が入ったセルは無視されますが、0 が入ったセルは数に入ります。 |
| MAX・MIN | =MAX(B2:B9) | 最大値・最小値。 |
| COUNT | =COUNT(B2:B9) | 数値が入っているセルの個数。範囲の中の空のセル・文字・論理値・エラー値は数えません。 |
| COUNTIF | =COUNTIF(B2:B9,">=80") | 条件に合うセルの個数。比較の条件は " で囲みます。* は任意の文字の並び、? は任意の1文字を表し、大文字と小文字は区別しません。 |
| SUMIF | =SUMIF(B2:B7,"飲み物",E2:E7) | 条件に合う行の値の合計。3つ目の範囲を省くと、1つ目の範囲そのものを合計します。 |
| IF | =IF(F2>=80,"A","B") | 条件が TRUE なら2つ目、FALSE なら3つ目の値になります。 |
| ROUND | =ROUND(A2,1) | 四捨五入。桁数が正なら小数点以下、0 なら整数、負なら整数部分で丸めます（=ROUND(626.3,-3) は 1000）。 |
| RANK.EQ | =RANK.EQ(E2,$E$2:$E$9) | 範囲の中での順位。3つ目の順序を省くか 0 なら大きい順、0 以外なら小さい順です。 |
| VLOOKUP | =VLOOKUP(A2,$F$2:$H$6,3,FALSE) | 範囲の左はしの列から検索値を探し、同じ行の、左から数えて「列番号」列目の値を返します。 |
RANK.EQ は、同じ値に同じ順位をつけます。その分、次の順位は飛ばされます。2位が2人いれば、その次は4位になり、3位の人はいません。範囲の中の数値以外の値は無視されます。並べ替えをしなくても順位が求められ、点数を直すと順位も自動で計算し直されます。
4つ目の引数「検索の型」に FALSE を指定すると、検索値と完全に一致する値を探し、見つからなければ #N/A になります。TRUE を指定するか、4つ目を省くと近似一致になります。近似一致では、範囲の左はしの列が小さい順に並べてあることが前提で、検索値をこえる値が出てくる手前の行、つまり検索値以下でいちばん近い値の行が使われます。「3000円以上なら送料300円」のような料金表を引くときに便利です。
4つ目を省くと近似一致になるので、商品コードのように完全に一致するものを探すときは、FALSE を書き忘れないことが大切です。また、列番号が範囲の列数より大きいと #REF! になります。検索値の 101（数値）と、表の中の「101」（文字列）は別のものとして扱われる点にも注意しましょう。
IF の中に IF を入れる（入れ子にする）と、3つ以上に分けられます。=IF(F2>=80,"A",IF(F2>=65,"B","C")) は、80以上なら A、65以上なら B、それ以外は C です。条件は外側から順に調べられ、TRUE になった時点で結果が決まります。そのため =IF(F2>=65,"B",IF(F2>=80,"A","C")) のように、ゆるい条件を先に書くと、80点以上の人も先に B に決まってしまい、A が1人も出ません。Excel では IF を64個まで入れ子にできますが、入れ子が深い式は読みにくく、まちがいの原因になるのですすめられていません。
式がうまく計算できないとき、セルにはエラー値が表示されます。エラー値は、どこがおかしいかを知らせる手がかりです。
| エラー値 | 主な原因 | 直し方の例 |
|---|---|---|
#DIV/0! | 0 や空のセルで割った | 割る数を確かめる。=IF(B7=0,"",B2/B7) のように、0 のときは割り算をしないようにする |
#NAME? | 関数名のつづりのまちがい、文字列を " で囲んでいない、範囲の「:」が抜けている | 関数名と " を確かめる |
#VALUE! | 計算に使うセルに文字や空白が入っている | 数値を入れ直す。「80点」のように単位をつけない |
#REF! | 参照しているセルが正しくない（参照していたセルを削除した、VLOOKUP の列番号が大きすぎるなど） | 参照先を指定し直す |
#N/A | VLOOKUP などで、探した値が見つからない | 検索値と表を確かめる。検索の型を確かめる |
エラーは、そのセルを参照しているセルにも伝わります。見本「商品コードで探す」では、1行の #N/A が合計のセルにまで伝わることを確かめられます。エラーを見つけたら、表示されているセルではなく、最初にエラーが起きたセルを探して直します。
式が、直接または間接に自分自身のセルを参照している状態を循環参照といいます。B9 に =B10+1、B10 に =B9*2 と入れると、B9 を求めるには B10 が、B10 を求めるには B9 が必要になり、計算の順番が決まりません。Excel では、循環参照が見つかると警告のメッセージが表示され、セルには 0 や最後に計算された値が表示されることがあります。このページの表計算では「循環参照」と表示します。
表計算ソフトは、どのセルがどのセルを参照しているかという関係を記録しています。Microsoft の開発者向けの説明によると、Excel はこの関係（依存関係のツリー）から、式を計算する順番の一覧（計算チェーン）を作ります。新しいデータや式が入力されると、そのセルに直接・間接に依存するセルすべてに「計算し直しが必要」という印をつけ、計算チェーンの順に計算し直します。A1 を変えたとき、A1 を使う B1、B1 を使う C1 の順に計算すれば、どのセルも新しい値をもとに計算できるからです。「表計算ソフトを動かす」のタブでは、値を変えたときに計算し直したセルを、その順番どおりに光らせて表示します。
1行に1件のデータ、1列に1つの項目を入れた表は、データベースのように扱えます。表計算ソフトには、そのための機能があります。
これらの操作は、リレーショナルデータベースを操作する言語 SQL の SELECT 文と対応しています。SQL について詳しくは「データベースとSQL」で体験できます。
| 表計算での操作 | SQL の書き方の例 |
|---|---|
| 並べ替え（数学の高い順、同点は番号順） | ORDER BY 数学 DESC, 番号 ASC |
| フィルタ（1組で、数学が80以上） | WHERE 組 = 1 AND 数学 >= 80 |
| 必要な列だけを見る | SELECT 氏名, 数学 |
| 組ごとの平均 | SELECT 組, AVG(数学) … GROUP BY 組 |
=COUNTIF(C2:C19,1) | SELECT COUNT(*) FROM テスト結果 WHERE 組 = 1 |
=SUMIF(C2:C19,1,F2:F19) | SELECT SUM(数学) FROM テスト結果 WHERE 組 = 1 |
集計した結果は、グラフにすると比べやすくなります。ただし、グラフの種類は目的に合わせて選びます。量の大小を比べるなら棒グラフ、時間による変化なら折れ線グラフ、全体に対する割合なら円グラフや帯グラフが向いています。縦軸を 0 から始めないと小さな差が大きく見えるなど、同じデータでも見せ方で印象が変わることにも注意が必要です。くわしくは「データを分析しよう」で確かめられます。
このページの表計算は、学習のために作った小さなもので、Excel や Google スプレッドシートと同じ書き方で式を書けますが、細かい動きがちがうことがあります。日付・時刻、セルの書式、範囲をまとめて返す式には対応していません。文字列の大小は文字の番号（文字コード）の順で比べます。セルの中の数値は、セルの幅に入るように有効数字を減らして表示します（=0.1+0.2 は 0.3 と表示）。式の書き方そのものがまちがっているときは #ERROR! と表示します。
情報Ⅰ 授業の進め方ガイドでは、この教材を次の授業で使います。ねらい・導入の問い・50分の流れ・確認問題をまとめています。