【情報I】表計算ソフトの使い方を図解で総まとめ—関数・参照・グラフ・集計の手順｜武蔵野個別指導塾

合計は合うのに式をコピーするとずれる理由を、セル参照の図と例題で確かめます。条件付き集計、完全一致の検索、並べ替えとフィルタ、目的に合うグラフまで、表を根拠に説明する手順を学べます。

よくあるのは、塾の自習室の白板の前で、表計算の式を下へコピーした結果を見比べ、「最初の行だけ正しいのに、なぜ次の行から答えが変になるのか」と手が止まる場面です。白板に書いた式は同じ形に見えても、ソフトが読んでいるセルの位置は移っています。計算そのものの間違いなのか、参照先の間違いなのかを分けると、直す場所が見えてきます。

表計算は、足し算を速くするだけの道具ではありません。条件に合うデータを数え、別の表から対応する値を探し、並べ替えやグラフで特徴を確かめる道具でもあります。ただし、操作に成功したことと、問いに合う結果が出たことは別です。表の1行が何を表し、どの範囲を計算し、結果を何と比較するかまで説明できるようにしましょう。ここでは小さな学習記録を使って、式を作るところから結果を確かめるところまでつなげます。

この単元で身につけること

• セルの番地と範囲を読み取ります。列と行を区別し、合計の対象に見出しや集計行を混ぜずに指定できるようにします。
• 相対参照と絶対参照を使い分けます。式をコピーしたとき、動かしたい参照と固定したい参照を、計算の意味から選びます。
• SUM、AVERAGE、COUNTIF、IFの役割を説明します。合計、平均、条件に合う個数、条件による値の選択を区別します。
• VLOOKUPで対応表を引きます。検索する列、取り出す列、完全一致の指定を確認し、意図しない値の取り出しを防ぎます。
• 並べ替え、フィルタ、グラフを目的に合わせます。行の対応を保ち、表示された範囲と集計範囲の違いにも注意します。

中学校までに学んだ平均やグラフの読み取りと、情報Iで学ぶ条件分岐やデータの整理がつながる単元です。関数名だけを覚えるより、「どのデータを受け取り、何を返すか」という入出力の関係で整理すると、新しい式にも対応しやすくなります。

基本の考え方

セルは値の置き場所であり、式の参照先です

表計算ソフトで扱う格子状の表をワークシートといい、その1つのますをセルといいます。縦に並ぶまとまりが列、横に並ぶまとまりが行です。列をA、B、C、行を1、2、3と表す場合、B列の2行目がB2です。B2:B5という範囲は、B2、B3、B4、B5の4セルを含みます。端のセルも含むため、行番号の差だけを個数にしないようにします。

セルに入れるものには、数値、文字列、数式などがあります。数式は通常、等号から始めます。数値の30は計算に使えますが、文字列として入力された「30分」は、そのまま数値の30として扱われるとは限りません。見出しに「学習時間（分）」と書き、データのセルには30だけを入れると、単位を保ちながら計算しやすくなります。識別のための番号は、計算する量とは役割が異なります。先頭の0を残したい番号などは、文字列として統一して扱います。

以下では、1行を1つの記録とし、次の表を使います。1行目が見出し、2〜5行目がデータです。C列の達成率は、各行の学習時間を共通の目標時間で割った割合で、あとから式を入れます。目標時間はE1に見出し、E2に60という数値を入れておきます。

この表には氏名などを入れる必要はありません。練習に必要な列だけを用意し、元データ、計算結果、共通の設定値を区別しておくと、どこを変更した結果なのか追いやすくなります。目標が変わったときに直す場所も、E2の1か所にまとまります。

コピーしたときに動く参照と動かない参照

相対参照では、数式のセルを移した分だけ、参照先も移ります。C2にB2を参照する式を入れてC3へコピーすると、参照先はB3になります。これは単に文字を同じように複製する操作ではありません。「同じ行の左隣を見る」という位置関係を保つ操作です。

絶対参照では、列名と行番号の前に固定を表す記号を付け、コピーしても参照先を変えません。達成率を求めるなら、学習時間の行は変え、目標時間のセルは固定します。C2に入れる式と、C3へコピーしたあとの式は次の関係です。ここからの表計算の式は入力例であり、Pythonのプログラムとは区別して読んでください。

C2に入力する式：=B2/$E$2
C3へコピーした式：=B3/$E$2

図1のように、左側の学習時間は行に合わせて移動し、右側の目標時間は同じセルを見続けます。まず日本語で「何を変え、何を固定するか」を決め、そのあとで式にすると、記号の付け忘れに気づきやすくなります。

固定は列と行に別々に指定できます。列だけ固定、行だけ固定する形を混合参照といいます。横にも縦にもコピーする表で使いますが、最初は両方を固定する絶対参照を確実に扱いましょう。次の3つは、いずれもB2を出発点にした参照で、固定する部分が異なります。

$B2：列だけ固定
B$2：行だけ固定
$B$2：列と行を固定

なお、固定とはコピー時の参照の動きについての説明です。セルの編集を禁止する機能ではありません。E2の値を60から別の数値に変えれば、そこを参照する結果も変わります。また、行や列を挿入したときの参照の調整は、式を隣へコピーする話とは分けて考えます。

関数は入力に対して決まった処理を行います

SUMは数値の合計、AVERAGEは数値の平均を返す関数です。関数に渡す範囲や条件などを引数といいます。学習時間の合計と平均を求める入力例は次のとおりです。範囲の先頭と末尾が正しいかを、表と照合します。

=SUM(B2:B5)
=AVERAGE(B2:B5)

結果は合計180、平均45です。平均の分母は、この例では数値が入っている4セルです。参照範囲にある空白セルは、AVERAGEでは通常、平均の個数に含めません。一方、数値の0は含めます。未記録を空白にすることと、「学習時間が0分だった」と記録することは違うため、集計前に意味を決めておく必要があります。

COUNTIFは条件に合うセルの個数を返します。45分以上の記録を数えるなら、範囲と条件を別々に渡します。比較記号を含む条件は、次のように引用符で囲みます。

=COUNTIF(B2:B5,">=45")

IFは条件が成り立つ場合と成り立たない場合で、返す値を選びます。COUNTIFが複数のセルから個数を求めるのに対し、IFは与えた条件を判定して結果を返します。両者は条件を使いますが、求める答えの形が異なります。この区別は、次の例題で実際の出力を比べると整理できます。

例題で解き方を確かめる

例題1　合計・平均・達成率を求めます

前の学習記録について、学習時間の合計と平均を求めます。また、目標時間60分に対する各記録の達成率をC列に表示します。ここでは達成率を百分率で表示し、小数部分のない値として読み取ります。

1. 合計と平均の対象をB2:B5に決めます。1行目は見出しであり、C列は別の意味の量なので、学習時間の4セルだけを選ぶためです。
2. SUMとAVERAGEを使って計算します。30＋45＋60＋45＝180、180÷4＝45と手計算でも確かめると、範囲の取り違えを見つけられます。
3. C2に、B2を絶対参照のE2で割る式を入れ、C5までコピーします。分子は各記録の時間、分母は共通の目標時間という役割を保つためです。
4. C2:C5の表示形式を百分率にします。計算値の0.5を50％と表示する操作であり、元の割合にさらに100を掛ける操作ではありません。

たとえば0.5を先に100倍して50にしたうえで百分率表示にすると、5000％になってしまいます。「セル内部の値」と「画面に見せる形式」を分けて確かめましょう。表示する桁を減らしても、通常は計算に使う内部の値まで丸められるわけではありません。

同じ計算を短いPythonのプログラムで表すと、次のようになります。リストの4つの値が、表のB2:B5に対応します。

times = [30, 45, 60, 45]
target = 60
print(sum(times))
print(sum(times) / len(times))
print([int(t / target * 100) for t in times])

出力は次のとおりです。この例は百分率がすべて整数になるため整数化していますが、端数がある値の丸め方を一般に表すコードではありません。

180
45.0
[50, 75, 100, 75]

答え：合計180分、平均45分、C2〜C5の達成率は順に50％、75％、100％、75％です。

例題2　条件に合う件数と行ごとの判定を求めます

45分以上の記録は何件あるでしょうか。また、各行について45分以上なら「達成」、それ以外なら「再計画」とD列に表示します。境界である45分を含める点に注目しましょう。

1. 条件を「45以上」と書き出します。「45より大きい」だと45を除いてしまうので、日本語の条件を先に確かめるためです。
2. COUNTIFでB2:B5のうち条件に合うセルを数えます。45、60、45の3つが該当し、合計時間ではなく件数が答えになります。
3. D2にIFの式を入れ、D5までコピーします。B2の参照を相対参照にすれば、各行の学習時間を順番に判定できるためです。

件数：=COUNTIF(B2:B5,">=45")
D2の式：=IF(B2>=45,"達成","再計画")

IFの2番目の引数は条件が成り立つ場合の値、3番目は成り立たない場合の値です。順番を逆にすると、計算エラーが出なくても意味は逆になります。式が受け付けられたことだけで正しいと判断せず、30と45を1つずつ代入して判定を確かめます。

Pythonで件数と判定を並べると、両者の違いを出力からも確認できます。比較の結果を数える処理と、文字列を選ぶ処理を分けて読んでみましょう。

times = [30, 45, 60, 45]
print(sum(1 for t in times if t >= 45))
print(["達成" if t >= 45 else "再計画" for t in times])

3
['再計画', '達成', '達成', '達成']

答え：45分以上は3件で、D2〜D5は順に「再計画」「達成」「達成」「達成」です。

例題3　対応表から学習分野を取り出します

別の対応表をG1:H4に作ります。G1は「コード」、H1は「分野」という見出しで、G2:H4には次のデータを入れます。F2に入力したコード20に対応する分野を取り出しましょう。

1. 検索する値をF2、検索する範囲をG2:H4と決めます。VLOOKUPは指定範囲の最も左の列を縦方向に探すので、コード列を範囲の左端に置くためです。
2. 取り出す列番号を2とします。これはワークシート全体の列番号ではなく、指定範囲内で左から数えた番号だからです。
3. 最後の引数にFALSEを指定します。コードは等しいものを探す目的なので、近似一致ではなく完全一致を選ぶためです。
4. コピーしても対応表がずれないように、範囲を絶対参照にします。検索値のセルは各行で変わっても、照合する表は共通にするためです。

=VLOOKUP(F2,$G$2:$H$4,2,FALSE)

図2のように、まず左列の20を見つけ、その同じ行の2列目を取り出します。20という数値を計算に使うのではなく、対応する行を見つけるための手がかりにしています。

完全一致で存在しないコードを指定すると、一致するデータがないことを示すエラーになります。そこで適当な分野を返すように直すのではなく、コードの入力や対応表を調べます。また、コードが重複すると、通常は最初に見つかった行が返るので、1つのコードに複数の分野を対応させない設計が必要です。数値の20と文字列の20を混在させることも、照合の失敗につながります。

最後の引数を省くと、一般的な表計算ソフトでは近似一致として扱われます。近似一致は、検索列の並び方などに条件があり、段階別の区分を求める場合に使う考え方です。コードの照合ではFALSEを明記する習慣を付けましょう。検索する列が範囲の左端でない場合や、その左側の値を取り出したい場合には、範囲の組み直しや別の検索方法が必要です。

Pythonでは、対応関係を辞書で表すと照合の考え方を短く示せます。次のコードはVLOOKUPの全機能の再現ではなく、コードから分野を取り出す部分を表しています。

fields = {10: "計算", 20: "読解", 30: "復習"}
code = 20
print(fields[code])

読解

答え：コード20に対応する値は「読解」で、指定範囲内の列番号は2です。

例題4　並べ替え・抽出・グラフをつなげます

最初の学習記録を、時間の長い順に並べます。同じ時間なら記録番号の小さい順とし、その後、45分以上の記録だけを表示します。最後に、元の4件の学習時間の違いをグラフに表します。

1. 見出しを含む記録の表全体を並べ替えの対象にします。時間だけ動かすと、記録番号と時間の対応が壊れるためです。共通の目標時間や別の対応表は含めません。
2. 第1のキーを学習時間の降順、第2のキーを記録番号の昇順にします。45分の2件にも順序を指定し、結果を一意に説明できるようにするためです。
3. 時間が45以上というフィルタを設定します。並べ方を変える操作と、条件に合う行だけを表示する操作を分けるためです。
4. フィルタを解除して元の4件を確認し、記録番号と時間を使って棒グラフを作ります。カテゴリごとの量の比較が目的なので、棒の長さで時間を示すためです。

フィルタは条件に合わない行を削除する操作ではありません。解除すると30分の記録も再び表示できます。グラフに非表示の行を含めるかは設定に左右されるため、4件を比べるなら4件が元データとして選ばれているかを確認します。

答え：並べ替え後の記録番号は103、102、104、101で、抽出後は103、102、104の3件です。4件の時間比較には棒グラフを使います。

つまずきやすいところと直し方

誤り1　固定する分母まで相対参照にします

誤った書き方は、C2に次の式を入れ、そのまま下へコピーすることです。E2が目標時間でも、E3以降は目標時間の置き場所ではありません。

誤り：=B2/E2
下へコピーした結果：=B3/E3
正しい書き方：=B2/$E$2

どこが違うかというと、学習時間と目標時間に同じ移動の規則を与えている点です。正しい書き方では、共通の分母だけを固定します。コピー後のC3を選び、参照先がB3とE2になっているかを見ると、最初の1行が正しいだけでは見つからなかった問題を確認できます。

逆に分子まで絶対参照にすると、どの行もB2の30分を使ってしまいます。エラーが出ずに全行が50％になる場合でも、意味は正しくありません。先頭行と次の行で、式と参照先を1組ずつ確かめるのが有効です。

誤り2　数値の列だけを並べ替えます

誤った書き方は、「B2:B5だけを選んで降順にする」です。どこが違うかというと、1つの記録を構成するA列の番号とB列の時間を切り離している点です。たとえば101の時間は30分なのに、番号を動かさず時間だけ動かすと60分が隣に来てしまいます。

正しい書き方は、「記録の表全体を対象にして、学習時間をキーに降順で並べ替える」です。表計算ソフトが範囲の拡張を確認する場合も、選ばれた範囲を見てから進めます。1行を1つの記録として整えることは、入力のためだけでなく、こうした処理で対応を守るためにも必要です。

図3の棒は、元の記録番号と時間を保ったグラフです。縦軸の0、30、60を等間隔に置き、棒の高さは30、45、60、45に比例させています。並べ替えの前後で表と図の対応を照合すると、値だけが移動した誤りも見つけやすくなります。

誤り3　表示中の行だけが自動で集計されると思います

誤った書き方は、「45分以上をフィルタ表示したので、SUMで求めた合計も表示中の150分になった」です。どこが違うかというと、表示の状態と数式が参照する範囲を同じものだと考えている点です。元の4件がB2:B5にあるなら、通常のSUMはフィルタで隠れた30分も含め、180を返します。

正しい書き方は、「表示中の3件の合計は150分、元の範囲のSUMは180分」と区別することです。表示行だけを集計する機能としてSUBTOTALなどがありますが、その場合も集計方法と対象を確認します。手で隠した行とフィルタで除いた行をどう扱うかまで、使う機能の規則に沿って確かめる必要があります。

この違いはグラフにも関係します。見たいのが全体なのか、条件を満たす集団なのかを決め、図の題名や説明に対象を示しましょう。グラフがきれいにできても、対象がずれていれば問いへの答えにはなりません。

練習問題

練習1　空白と0を区別します

B2、B3、B4、B5に、それぞれ20、0、空白、40が入っています。SUMとAVERAGEでB2:B5を指定した結果を求めてください。また、空白を0で埋めた場合の平均も求めます。空白は未記録、0は実際に0分だった記録という違いを意識します。

練習2　コピー後の式を読みます

C2に次の式が入っています。これをC4へコピーしたときの式を書いてください。B4は48、E2は60とし、結果を百分率で答えます。途中のC3を経由して考えても、直接2行下へ移すと考えても構いません。

=B2/$E$2

練習3　条件と検索を確かめます

B2:B5が40、45、50、45のとき、COUNTIFで45以上を数えると何件でしょうか。また、例題3の対応表でF2を30にしたとき、完全一致のVLOOKUPが返す分野を答えてください。件数と文字列をそれぞれ求める問題です。

練習4　並び順とグラフを選びます

記録番号201、202、203の学習時間が順に20、50、30分です。記録を時間の降順に並べ、30分以上をフィルタ表示したときの番号を順に答えてください。さらに、別の表にある月曜日から金曜日までの日々の時間の推移を示すなら、どのグラフが適切か、理由とともに答えます。

答えと解説

練習1の答えは、合計60、平均20、空白を0にしたあとの平均15です。最初は数値の20、0、40の3件が対象なので60÷3です。空白を0に変えると対象は4件になり、60÷4になります。合計が変わらなくても、平均の意味と値は変わります。

練習2の答えは次の式で、結果は80％です。コピー先は2行下なのでB2がB4になり、絶対参照のE2は変わりません。48÷60＝0.8を百分率表示にします。

=B4/$E$2

練習3の答えは3件と「復習」です。45、50、45が条件に合い、コード30は対応表の4行目にあります。ワークシート上の行番号4と、取り出す列番号2を混同しないようにします。

練習4の答えは202、203で、日々の推移には折れ線グラフが適切です。降順は202、203、201で、20分の201がフィルタで非表示になります。日付には順序があり、折れ線で結ぶと増減を追いやすくなります。ただし、記録のない日を0分と決めつけて線をつなぐことは避けます。

次の単元へのつながりと家庭での確かめ方

表計算で整理した表は、データの分析や可視化の出発点になります。カテゴリの量を比べるなら棒グラフ、時間による変化を見るなら折れ線グラフ、2つの量の関係を見るなら散布図というように、問いに合わせて選びます。円グラフは、同じ全体を重なりなく分けた内訳の割合を示す場合に使います。平均値と人数のように単位も意味も異なる値を、1つの円の内訳にしてはいけません。

家庭では、次の2つをそれぞれ5分で確かめられます。1つ目は、紙に3行の表と目標値を1つ書き、式を下へコピーしたときに変わる参照先を指で追う方法です。端末がなくても、参照の仕組みは練習できます。2つ目は、身近な記録を4件だけ用意し、合計、45以上の件数、時間順の並びを先に手で求めてから表計算と照合する方法です。数を小さく保つと、操作の結果を自分で判断できます。

個別指導では、式のどの部分で止まったかに応じて、セルの番地の読み方、割合の割り算、以上とより大きいの区別まで戻って確かめます。お子さまの理解を家庭で見る際も、操作の速さより「その範囲を選んだ理由」を一言で説明できるかが手がかりになります。

まとめ

表に結果が表示されたら、いったん操作を止めて、その数値がどの記録から生まれたのかをたどってみましょう。合計180分を説明するには、4件を含めたこと、同じ単位で足したこと、表示を絞っても参照範囲は残っていることを確かめる必要があります。こうした説明ができれば、見慣れない表でも問いとデータを結び付けられます。

正しい答えの表示だけでは、表計算を理解した証拠にはなりません。参照先を指し、対象を説明し、結果を確かめるところに理解が表れます。