午後問題の歩き方 | 表計算も簡単ではなくプログラミング問題(1)基礎知識


2020-03-04 更新

プログラミング言語の経験がない人の多くは、午後問題の問 7 ~問 11 で、問 11 の表計算を選んでいます。その選択は、何ら間違いではありません。

ただし、表計算が簡単であるとは、決して思わないでください。

きちんと基礎知識を学習した上で、数多くの過去問題を練習しておかないと、合格点を取れないからです。

今回は、表計算の基礎知識を説明します。次回の記事で、表計算の問題の解き方のパターンを説明します。

 

基礎知識 1:Excel との違いを知る

現在、最も普及している表計算ソフトは、Excel( Microsoft Excel )でしょう。しかし、国家試験である基本情報技術者試験に、特定のメーカーの製品を使った問題を出題するわけにはいきません。

そこで、架空の表計算ソフトがあるとしています。この表計算ソフトには、とくに名前は付けられていません。これ以降では、「 FE の表計算」と呼びます。FE は、基本情報技術者試験の英語表記の略称です。

 

Excel と FE の表計算の機能は、基本的に同じです。

セルの中に計算式を入れてワークシートを作り、そのワークシートにデータを入力して目的の結果を得るという機能です。計算式の中で、あらかじめ用意されている関数を使うこともできます。関数の引数で、セルの範囲を指定することもあります。

これらの表現において、Excel と FE の表計算には、以下に示した違いがあります。

表現 Excel FE の表計算
計算式 = を付ける
(例)= A2 + B3
= を付けない
(例)A2 + B3
関数の名前 英語
(例)SUM( A1:A10 )
日本語
(例)合計( A1 ~ A10 )
セルの範囲 :で示す
(例)A1:A10
~ で示す
(例)A1 ~ A10

 

基礎知識 2:相対参照と絶対参照の使い方を知る

A2 や B3 など、セルの場所を示す表現をセル番地と呼びます。セル番地には、相対参照絶対参照があります。

これらは、セルに入力した計算式をコピーして他のセルに貼り付けたときに、セル番地を変化させるかどうかを指定するものです。

A2 や B3 のように、何も指定しないと相対参照になり、$A2 や B$3 のように $ を付けると絶対参照になります。$ という文字の有無で相対参照と絶対参照を示すのは、Excel と FE の表計算どちらも同じです。

 

相対参照の場合は、コピー先に合わせてセル番地が自動的に変化します。

たとえば、以下のワークシートでは、D2 に入力された B2 * C2 という計算式をコピーして、D3 と D4 に貼り付けています。

コピー先に合わせて、D3 の計算式は B3 * C3 に変化し、D4 の計算式は B4 * C4 に変化しています。

[売上金額を求めるワークシート]

なぜ、このような仕組みになっているかというと、その方が便利だからです。

これを相対参照と呼ぶのは、D2 に入力された B2 * C2 の B2 と C2 は、絶対的に B2 と C2 というセルを指しているのではなく、D2 から見て B2 と C2 の相対位置にあるセル、つまり B2 は同じ行で 2 列前にあるセル、C2 は同じ行で 1 列前にあるセルを指しているからです。

 

$ を付けて絶対参照を使わなければならない場合もあります。

たとえば、以下のワークシートでは、関東地方における東京都の人口の構成比を求めるために、C2 に B2 / B$9 という計算式を入力して、それをコピーし、C3 ~ C8 に貼り付けています。

[人口の構成比を求めるワークシート]

B$9 の 9 の前に $ があるのは、$ がないとコピー先に合わせて B9 が B10 ~ B15 に変化してしまうからです。

関東地方の人口の合計は、どの県の構成比を求める時も、絶対的に B9 にあるので、絶対参照を使うことになります。

 

絶対参照を示す $ は、$A2 のように列名だけに付けることも、A$2 のように行番号だけに付けることも、$A$2 のように、列名と行番号の両方に付けることもできます。

列名だけに付けた場合は、貼り付け先で列名が変化せず行番号が変化します。
行番号だけに付けた場合は、貼り付け先で行番号が変化せず列名が変化します。
列名と行番号の両方に付けた場合は、貼り付け先で列名も行番号も変化しません。

 

先ほどの人口の構成比では、コピーした計算式を縦方向に貼り付けていました。この場合は、$ がないと行番号だけが変化するので、行番号だけに $ を付けたのです。

$B$9 のように、列名と行番号の両方に $ を付けても、同じ計算結果が得られますが、試験問題では無駄なことはしません。

列名の B の前に $ がなくても、B は変化しないので $ を付ける必要はないのです。

 

ここまでの説明を読んで、「そんなこと覚えるなんて面倒だ!」と思われたかもしれませんが、がんばって覚えてください。なぜなら、試験問題の選択肢には、同じ計算式で $ があるものとないものが示される場合があるからです。

 

以下は、平成 27 年度 秋期 問 13「 PC 販売店での購入金額の計算」の設問 1 空欄 b の選択肢です。

問題の内容は省略しますが、答えを選択肢ウの「セット値引き表!I3 」と選択肢エの「セット値引き表!I$3 」に絞り込めたとしましょう。両者の違いは、3 の前に $ があるかないかです。どちらが適切かを判断できなければ、答えを選べません。

b に関する解答群

ア セット値引き表!A3  イ セット値引き表!A$3
ウ セット値引き表I13  エ セット値引き表!I$3

 

基礎知識 3:関数の種類と機能を知る

FE の表計算には、数多くの関数が用意されています。用意されているだけでなく、問題の中でもバリバリ使われています。

試験問題には、関数の仕様書が添付されていますが、それを試験当日に調べているようでは、時間が足りなくなってしまいます。

あらかじめ、関数の仕様書に目を通しておきましょう。丸暗記する必要はありませんが、どんな関数があるのかは知っておくべきです。

 

以下に、関数の名前を示します。全部で 27 個あります。もしも、仕様書に示されていない関数が使われる場合は、問題文の中で説明されます。

 

[算術計算を行う関数]

  • 合計(セル範囲)
  • 平均(セル範囲)
  • 最大(セル範囲)
  • 最小(セル範囲)
  • 剰余(値 1 ,値 2 )
  • 平方根(値)
  • 整数部(値)
  • 切上げ(値,桁位置)
  • 切捨て(値,桁位置)
  • 四捨五入(値,桁位置)
  • 乱数( )
  • 標本標準偏差(セル範囲)
  • 母標準偏差(セル範囲)

 

[IF 関数と論理演算関数]

IF( 条件,値 1,値 2)

論理積( 条件1,条件 2,・・・)

論理和( 条件1,条件 2,・・・)

否定(条件)

 

[照合と検索を行う関数]

垂直照合(値,セル範囲,列の位置,検索の指定)

水平照合(値,セル範囲,行の位置,検索の指定)

照合検索(値,検索のセル範囲,抽出のセル範囲)

照合一致(値,セル範囲,検索の指定)

条件付合計(検索のセル範囲,条件,合計のセル範囲)

 

[その他の関数]

個数(セル範囲)

条件付個数(セル範囲,検索条件)

順位(値,セル範囲,順序指定)

結合(文字列または式 1 ,文字列または式 2 ,・・・)

表引き(セル範囲,行の位置,列の位置)

 

仕様書を読んだだけでは、関数の機能がよくわからないことがあるかもしれません。その場合には、自分でシンプルな具体例を作ってみましょう。

たとえば、以下は、仕様書に示された垂直照合の説明です。機能も例も文書で説明されているので、ちょっとわかりにくいでしょう。

 

[仕様書に示された説明]

垂直照合(式,セル範囲,列の位置,検索の指定) セル範囲の左端列を上から下に走査し,検索の指定によって指定される条件を満たすセルが現れる最初の行を探す。その行に対して,セル範囲の左端列から列を 1, 2,・・・ と数え,セル範囲に含まれる列の位置で指定した列にあるセルの値を返す。

  • 検索の指定が 0 の場合の条件 : 式の値と一致する値を検索する。
  • 検索の指定が 1 の場合の条件 : 式の値以下の最大値を検索する。このとき, 左端列は上から順に昇順に整列されている必要がある。

[例]垂直照合 (15,A2:E10,5,0) は,セル範囲の左端列をセル A2,A3, ・・・,A10 と探す。このとき,セル A6 で 15 を最初に見つけたとすると,左端列 A から数えて 5 列目の列 E 中で,セル A6 と同じ行にあるセル E6 の値を返す。

 

以下は、仕様書の説明をもとに作成した具体例です。

照合と検索を行う関数の機能は、「表形式でデータが格納されているのだから、こういう関数があると便利だよね!」というものです。

表形式のデータは、データの範囲(赤い点線で囲んだ部分)の左端に縦に検索キー(ここでは、学生番号)があるものです。そこで左端で目的のデータを検索し、一致した位置から右方向に進んで、目的のデータ(ここでは、身長や体重)を得ます。それを行うのが垂直照合です。

垂直照合(2, A2 ~ C4, 3, 0) は、「 A2 ~ C4 の範囲の左端で」「 2 を検索し」「一致した位置から右に 3 個進め(先頭を 1 番目と数えます)」という意味です。

これによって、学生番号 2 の体重 55 が得られます。このような具体例を作ると、わかりやすいでしょう。

 

[シンプルな具体例]

 

PR

 

基礎知識 4:マクロの役割を知る

表計算の問題の前半部は、セルに計算式を入力してワークシートを作るという内容です。そして、後半部は、そのワークシートを使うマクロを作るという内容です。

マクロは、プログラミング言語なので、「マクロが使えるなら、ワークシートなんて作らないで、全部プログラムでやっちゃえばいいじゃないか?」と思ったことはありませんか。

マクロの基礎知識として、そもそもなぜマクロを作るのかを理解しておきましょう。

表計算の使い方は、ワークシートを作ることが基本です。そのワークシートを手作業で使ってもよいのですが、データの入力と結果の確認を自動化するためにマクロを作るのです。

例を示しましょう。

以下は、学生のテストに結果に、何点かを加点(下駄を履かせるってヤツです)して、合格者が何人になるかを確認するためのワークシートです。テストを実施した先生が、合格者を増やすために使うものだと考えてください。

 

[学生のテスト結果に加点するワークシート]

 

A54 に加点を入力すると、E54 に加点後の合格者数が表示されます。先生は、現状の合格者が 30 人と少ないので、加点して 40 人以上にしたいとします。

まず、A54 に 1 を入力しました。合格者が増えて、E54 には 32 と表示されました。
次に、A54 に 2 を入力しました。さらに合格者が増えて、E54 には 35 と表示されました。

この後は、A54 に 3 を入力して E54 を確認し、40 未満だったら A54 に 4 を入力して E54 を確認し、・・・・、という手順を手作業で繰り返すことになります。

このような面倒な作業を自動化するのが、マクロの役割です。

 

以下は、合格者を 40 人以上にするには、最低何点を加点すればよいかを求めるマクロです。

繰り返し処理の前に、初期値として A54(加点)に 1 を入力しています。E54 < 40(加点後の合格者数が40未満である限り)という条件で、A54 の値を 1 ずつ増やしています。

このマクロの実行が終了したとき、A54 には、合格者を 40 人以上にするための最低の加点が得られます。

 

[データの入力と結果の確認を自動化するマクロの例]

○マクロ : AddPoint
・ A54 <- 1
■ E54 < 40
| ・ A54 <- A54 + 1
■

 

 

実務で Excel を使うときには、あまり深く考えずにセルに計算式を入力して、結果が良ければそれで OK としているでしょう。

しかし、FE の表計算の問題では、結果を見ることができません。

「こういう計算式を入れて、このように$を付けて、それをコピーして貼り付けたのだから、結果はこうなるはずだ」と考えなければなりません。

これは、プログラムを作るときにアルゴリズムを考える行為に似ています。表計算は、帳票の作成という用途に限定された プログラミングツール なのです。

だから、プログラミング言語の選択肢に表計算があるのです。

 

他のプログラミング言語と比べて、何も引け目を感じることなどありませんので、堂々と表計算を選択してください。

 

それでは、またお会いしましょう!

 

label あわせて読みたい

午後問題の歩き方 | 表計算問題の解き方のパターン (2) 過去問演習

 

label 関連タグ
実は、午前試験を『免除』できます 独習ゼミで午前免除試験を受けた 85% の方が、
午前分野を免除しています。
2021 年度 春期試験 向け
8 月発売予定!
2021年度 春期試験向けは
8 月販売予定!

詳しく見てみるplay_circle_filled
label これまでの『午後問題の歩き方』の連載一覧 label 著者

『プログラムはなぜ動くのか』(日経BP)が大ベストセラー
IT技術を楽しく・分かりやすく教える“自称ソフトウェア芸人”

大手電気メーカーでPCの製造、ソフトハウスでプログラマを経験。独立後、現在はアプリケーションの開発と販売に従事。その傍ら、書籍・雑誌の執筆、またセミナー講師として活躍。軽快な口調で、知識0ベースのITエンジニアや一般書店フェアなどの一般的なPCユーザの講習ではダントツの評価。
お客様の満足を何よりも大切にし、わかりやすい、のせるのが上手い自称ソフトウェア芸人。

主な著作物

  • 「プログラムはなぜ動くのか」(日経BP)
  • 「コンピュータはなぜ動くのか」(日経BP)
  • 「出るとこだけ! 基本情報技術者」 (翔泳社)
  • 「ベテランが丁寧に教えてくれる ハードウェアの知識と実務」(翔泳社)
  • 「ifとelseの思考術」(ソフトバンククリエイティブ) など多数