初級システムアドミニストレータ/表計算ソフト
初級システムアドミニストレータ試験（初級シスアド）は2009年（平成21年）度春期の試験を最後に廃止されました。ただし、初級シスアドの後継であるITパスポート試験や基本情報技術者試験にも表計算ソフトに関する問題が出題されています。（基本情報技術者試験には2009年春期より追加されました。）
- お知らせ
特に基本情報技術者試験では表計算ソフトの配点がとても大きいので、合格を目指される方はしっかり学習してください。
表計算ソフト
編集かつて「コンピュータに計算させる」といえば、専門家に任せるのが当然という時代がありました。そんなコンピュータを身近なものにしてくれたソフトウェア、表計算ソフトのお話です。
前提知識
編集Excelなどの表計算ソフトで、簡単な関数(=SUM()関数など)を使ったことがあること
用語
編集- 引数(ひきすう)
- 関数が計算をする際に、材料とする値。SUM(A1:D1)の場合、“A1:D1”の部分のこと
- 関数値
- 関数が計算した後の、計算結果。「戻り値(もどりち)」と呼ぶ場合もある
- 行
- よこの並びのこと。表計算ソフトの場合は、1行目、2行目…と、数字で表す
- 列
- たての並びのこと。表計算ソフトの場合は、A列目、B列目…と、アルファベットで表す
| A | B | … | |
|---|---|---|---|
| 1 | |||
| 2 | |||
| 3 | |||
| ： |
- セル
- 表計算の、一つのマス目のこと
- セル範囲
- 複数のセルをひとまとまりとして指定したもの。
※かつてはセル範囲は“A1〜D1”と表記されていましたが、ITパスポート試験では2015年（平成27年）7月7日から、基本情報技術者試験では同年の秋期試験から“A1:D1”という表記に変更されました。
MS-Excelとの相違点
編集いかにマイクロソフト社のExcelが普及していようと、国が認定する試験において、特定企業の表計算ソフトをひいきするわけにいきません。そのため試験センターでは、情報処理技術者試験のために、独自の表計算ソフトの仕様を考え出しました。ですがそれはExcelによく似たものです。以下に、その表計算ソフトをExcelと比べてた際の違いを挙げておきます。これを参考にしながら、過去問題の表計算シートをドンドン試してみて下さい。
- 絶対参照の際につける$記号の使い方は、Excelと全く同じです。
- 関数名は日本語ですが、Excelの関数名を意識したネーミングです。
- 範囲指定は、「（B3～D7）」のように書きます(Excelでは(B3:D7))。
- 関数名は、全角の日本語です(Excelでは半角のアルファベット)。
- 計算記号(演算子)は全角です(Excelでは半角)が、使い方はExcelと同じです。
- 比較演算子“≧”は、Excelでは“>=”です。
- 数式の冒頭に、イコール記号を入れなくとも計算してくれます(Excelでは必須)。
- 引数をくくるための丸括弧は、全角(Excelでは半角)です。
- 文字列をくくるための記号は、’合格’のようにシングルクォーテーション(Excelではダブルクォーテーション)です。
- 論理演算の結果は、漢字で’真’か’偽’です(Excelでは“TRUE”か“FALSE”)。
関数
編集次に、試験センターが策定した表計算ソフトの、関数の一覧表を挙げます。
なお、この表に出てくる関数の「引数(ひきすう)」という言葉は、関数が計算をする際の“材料”となる値のことです。関数は、引数として与えられた値に基づいて、処理を行います。
| 関数名（引数1，引数2，…） |
そして、関数が計算で求めた値(数値や文字列、’真’ か’偽’の論理値、セル番号の場合はその番号が指し示すセルの値)によって、その関数が入力されたセルに表示される計算結果が変わります。
- 【コラム】
- Q:本番の試験で配られる試験問題に「表計算ソフトの機能・用語」が印刷されていますが、それでも関数を覚える必要がありますか？
- A:はい。解くスピードが速くなり、解答時間を稼ぐことができます。実務にも直結しますので、マスターしておくべきです。
| 試験センター策定の関数名 | 対応するExcelの関数名 | 使われ方 |
|---|---|---|
| 合計（A1:A6） | =SUM(A1:A6) | 指定された範囲すべての数値の合計を求め、関数値として返す。 |
| 平均（B1:B6） | =AVERAGE(B1:B6) | 指定された範囲すべての数値の平均を求め、関数値として返す。 |
| 平方根（C1） | =SQRT(C1) | 引数の値(正の数でなければならない)の正の平方根を、関数値として返す。 |
| 標準偏差（D1:D6） | =STDEV(D1:D6) | 指定された範囲すべての数値の標準偏差を求め、関数値として返す。 |
| 最大（E1:E6） | =MAX(E1:E6) | 指定された範囲すべての数値のうち、最大の値を関数値として返す。 |
| 最小（F1:F6） | =MIN(F1:F6) | 指定された範囲すべての数値のうち、最小の値を関数値として返す。 |
| IF（G1＞G2，’合格’，’不合格’） | =IF(G1>G2,"合格","不合格") | 左端の論理式が真(成立する)ならば、真ん中が実行される。偽(成立しない)ならば、右端が実行される。 |
| 個数（H1:H6） | =COUNTA(H1:H6) | 指定された範囲のうち、空白でないセルの個数を、関数値として返す。 |
| 条件付個数（I1:I10，’≧70’） | =COUNTIF(I1:I10,">=70") | 左に指定した範囲のうち、右の条件を満たすセルの個数を、関数値として返す。 |
| 整数部（J1） | =INT(J1) | 引数の数値より小さい値で、最大の整数を、関数値として返す。たとえばJ1が3.9の場合は3が、-3.9の場合は-4となる。 |
| 剰余（K1，K2） | =MOD(K1,K2) | 「左の値÷右の値」の余りを、関数値として返す。 |
| 論理積（論理式1，論理式2，…） | =AND(論理式1,論理式2,…) | 引数の論理式の全てが“真”の場合のみ、関数値として“真”を返す。 |
| 論理和（論理式1，論理式2，…） | =OR(論理式1,論理式2,…) | 引数の論理式が1つ以上“真”の場合、関数値として“真”を返す。 |
| 否定（論理式） | =NOT(論理式) | 引数の論理式と反対の論理値を、関数値として返す。たとえば、否定（5＜1）だと、関数値は“真”を返す。 |
ところが、試験センターが策定したこれら関数だけでは、合格するには不十分です。というのは、あらかじめ公表されているこれら関数の他にも、以下の関数も過去の試験には出題されており、これらも当たり前のように出題されているからです。
| 試験会場で出題された関数名 | 対応するExcelの関数名 | 使われ方 |
|---|---|---|
| 垂直照合（照合値，照合範囲，列位置） | =VLOOKUP(L8,M5:P10,4) | “照合範囲”の最左端列を上から順に調べ、“照合値”と同じ値を含むセルが初めて現れる行を探す。次に“照合範囲”の最左端列を1列目として、右に“列位置”列目のセル値を、関数値として返す。 |
| 条件付合計（範囲，検索条件，合計させる範囲） | =SUMIF(Q1:Q20,R5,S1:S20) | “範囲”の中から“検索条件”と同じ値を持つセルを探し、“合計させる範囲”のうち対応する行のセルの合計値を、関数値として返す。 |
| 日付値（’yyyy/mm/dd’） | =DATEVALUE("2007/6/1") | 1901年1月1日からyyyy年mm月dd日までの日数を関数値として返す。(注：Windows上のExcelの場合、1900年1月1日が基準日) |
| 日付（y，m，d） | =DATE(2007,6,1) | 基準日からy年m月d日までの日数を算出する。(注：Windows上のExcelの場合、1900年1月1日が基準日) |
相対参照(そうたいさんしょう)と絶対参照(ぜったいさんしょう)
編集相対や絶対、という言葉を見て、難しく考えなくてもOKです。「自分のセルから見て」か「特定のセルから見て」かの違い、ととらえて下さい。
相対参照(そうたいさんしょう)
編集| A | |
|---|---|
| 1 | 20 |
| 2 | A1*2 |
| 3 | |
| 4 |
セルA2に数式“A1*2”を入力しました(もちろん、瞬時に40と表示されます)。
ところで(ちょっと難しい話ですが、ここがポイント)表計算ソフトは、セルA2の数式が、どのように扱われているのでしょうか？
実は「『セルA1』*『2』」では、ありません。
「『自分のセルの、1つ上』*『2』」として扱われます。
そのため、セルA2の数式をセルA4まで複写(連続コピー)すると、「『自分のセルの、1つ上』*『2』」という情報が複写されるため、結果、このような数式が入ります。
| A | |
|---|---|
| 1 | 20 |
| 2 | A1*2 |
| 3 | A2*2 |
| 4 | A3*2 |
もちろん瞬時に計算されて、それぞれのセルには1行ごとに2倍ずつの値が表示されます。
| A | |
|---|---|
| 1 | 20 |
| 2 | 40 |
| 3 | 80 |
| 4 | 160 |
このように表計算ソフトは「自分のセルから見て、どこにあるセルか」を基準として、場所を指定します。この考えを「相対参照」と呼び、何も指定しない場合の表計算ソフトでは、この相対参照で座標を扱っています。
絶対参照
編集次に、このようなデータを入力してみました。
| A | B | |
|---|---|---|
| 1 | 消費税率 | 1.05 |
| 2 | ||
| 3 | 単価 | 税込単価 |
| 4 | 100 | |
| 5 | 120 | |
| 6 | 80 |
皆様には、単価と消費税率を掛けた、このような一覧表を作ってもらいたいのです。
| A | B | |
|---|---|---|
| 1 | 消費税率 | 1.05 |
| 2 | ||
| 3 | 単価 | 税込単価 |
| 4 | 100 | 105 |
| 5 | 120 | 106 |
| 6 | 80 | 84 |
そこでまず、セルB4に、単価と消費税率を掛ける数式を入力しました。
| A | B | |
|---|---|---|
| 1 | 消費税率 | 1.05 |
| 2 | ||
| 3 | 単価 | 税込単価 |
| 4 | 100 | A4*B1 |
| 5 | 120 | |
| 6 | 80 |
もちろん、セルB4は瞬時に105と表示されます。
ところで、このセルB4の数式を下方向に複写すると、どうなるでしょうか?
実は、相対参照の考え方が働き、このような数式となってしまいます。
| A | B | |
|---|---|---|
| 1 | 消費税率 | 1.05 |
| 2 | ||
| 3 | 単価 | 税込単価 |
| 4 | 100 | A4*B1 |
| 5 | 120 | A5*B2 |
| 6 | 80 | A6*B3 |
「『自分のセルの、1つ左』*『自分のセルの、3つ上』」という情報がセルB5とセルB6へと複写されたため、消費税率の値が入ったセルからはズレてしまいました。
その結果、セルB5には0が表示され、セルB6はエラー表示となります(文字列と数値を掛け算しようとしたため)。
| A | B | |
|---|---|---|
| 1 | 消費税率 | 1.05 |
| 2 | ||
| 3 | 単価 | 税込単価 |
| 4 | 100 | 105 |
| 5 | 120 | 0 |
| 6 | 80 | エラー |
さあ困りました。どうしましょう。複写した後でも、消費税率のセル(セルB1)と掛け算してもらわないと困ります。そこで出てくるのが「絶対参照」というテクニック、そして$記号です。
この$記号、複写する時に、座標の値を変えたくない時に使います。列でも行でも、その直前(＝座標の値の、すぐ左)に$記号をつけるのがミソです。今回の場合、行番号を固定したいので、セルB4の数式のうち、行番号の直前(左側)に$記号をつけます。
| A | B | |
|---|---|---|
| 1 | 消費税率 | 1.05 |
| 2 | ||
| 3 | 単価 | 税込単価 |
| 4 | 100 | A4*B$1 |
| 5 | 120 | |
| 6 | 80 |
このように$印をつけた上で、下方向に複写すると、
| A | B | |
|---|---|---|
| 1 | 消費税率 | 1.05 |
| 2 | ||
| 3 | 単価 | 税込単価 |
| 4 | 100 | A4*B$1 |
| 5 | 120 | A5*B$1 |
| 6 | 80 | A6*B$1 |
これでめでたく、欲しかった計算結果が得られました。
| A | B | |
|---|---|---|
| 1 | 消費税率 | 1.05 |
| 2 | ||
| 3 | 単価 | 税込単価 |
| 4 | 100 | 105 |
| 5 | 120 | 106 |
| 6 | 80 | 84 |
複写する時に、(列でも行でも)ズレてもらっては困る方のすぐ左側に、$記号をつけて釘をさすイメージでとらえて下さい。なお、この$記号は、列と行の両方を同時に固定したい場合(つまり「座標の値を、どう複写してもズレないように」したい場合)にも使えますが、その時には$B$1のように書きます。
IF関数
編集IF（条件式，その条件式が真の時の処理，その条件式が偽の時の処理）
これは「もし『条件式』に書かれたことがが正しければ、『その条件式が真の時』の処理をせよ。でなければ『その条件式が偽の時』の処理をせよ。」という意味だと、とらえて下さい。
たとえば、あるセルに、IF（B3≧5，‘5より大きい’，‘5未満’）と入力しておき、セルB3の値を10にすると「5より大きい」と表示されます。
じつは、IF文の中に、さらにIF文を書くこともできます。たとえば、
IF（B8＝’優’，’あなたは80点以上でした。’，IF（B8＝’可’，’あなたは60点～79点でした。’，’不可でしたか？’）)
このIF文を、流れ図で書くと、このようになります。
（図 ＩＦ文の「入れ子」）
このように、ある構造の中に、同じ構造のものが入っていることを「入れ子(いれこ)」構造と呼ぶこともあります。
論理積(ろんりせき)関数と、論理和(ろんりわ)関数
編集論理積（論理式1，論理式2，…）
論理和（論理式1，論理式2，…）
否定（論理式）
論理積は“AND”のことで、「かつ」とも訳されます。また論理和は“OR”のことで、「または」とも訳されます。論理積関数は「引数の、全てが‘真’」の場合、全体として‘真’になります。論理和関数は「引数の、一つでも‘真’」の場合、全体として‘真’になります。
否定関数は「引数が‘真’」の場合、全体として‘偽’になります。また逆に「引数が‘偽’」なら、全体として‘真’になります。
- 【コラム】
- IF関数の、条件式の書き方
IF関数の『条件式』は、結果として“真”か“偽”になるならば、どのような書き方をしても正しく動作します。たとえば、IF（5＜3，’正しい’，’間違い’） と書くと、つねに「間違い」と表示されます。
ここから先は、頭の体操のつもりで読んでください。
IF（’真’，E11，E12） と書くと、つねにセルE11と同じ内容が表示されます。
IF（論理積（3＜5，否定（’偽’），’真’），E11，E12） と書くと、つねにセルE11と同じ内容が表示されます(IF関数の中の条件式は「3が5より小さく、かつ、間違いではなく、かつ、正しい。だから全体として見ても、正しい」という解釈になります)。
垂直照合(すいちょくしょうごう)関数
編集垂直照合（照合値，照合範囲，列位置）
垂直照合関数は、「(会員番号順などを検索キーに使って)たてに並んでいる中から特定の行を選び出し、その行の右をたどって値を取り出す」ことができる関数です。検索に便利なので、よく出題されます。
使用例その１
編集垂直照合（A1，B1～E5，3）
意味
編集まず、セルA1の値と同じ値を、B列を上から順に探し、最初に見つかったセルの行を右にたどって3列目(ここではD列)の値を関数値として返します。
使用例その２
編集| A | B | C | D | |
|---|---|---|---|---|
| 1 | 会員番号 | 名前 | 出身地 | 好物 |
| 2 | ABC1 | 安藤 | 東京 | もんじゃ焼 |
| 3 | ABC2 | 伊藤 | 大阪 | たこ焼 |
| 4 | ABC3 | 宇部 | 名古屋 | きしめん |
| 5 | ABC4 | 江頭 | 札幌 | ラーメン |
| 6 | ABC5 | 小田 | 博多 | 明太子 |
| 7 | 番号入力→ | ABC2 | 名前 |
ここで、セルD7に、IF関数と組み合わせた垂直照合関数として、
垂直照合（B7，A2～D6，IF（C7＝’会員番号’，1，IF（C7＝’名前’，2，IF（C7＝’出身地’，3，4）））） を入力します。
その上で、セルB7に会員番号を、セルC7に検索したい項目名(この場合「名前」)を入力すると、セルD7に検索結果(この場合「伊藤」)を表示できます。
- 【コラム】MS-Excelでの垂直照合 : 上記の例と同じことを、Excelで行いたい場合の書き方は、以下の通りです。
=VLOOKUP(A1,B1:E5,3) (“垂直照合（A1，B1～E5，3）”と同じ意味) =VLOOKUP(B7,A2:D6,IF(C7="会員番号",1,IF(C7="名前",2,IF(C7="出身地",3,4))))
(“垂直照合（B7，A2～D6，IF（C7＝’会員番号’，1，IF（C7＝’名前’，2，IF（C7＝’出身地’，3，4））））”と同じ意味）
絶対参照に使う記号($記号)
編集試験出題の最も多いパターンは、「別のセルに複写してもちゃんと計算できるよう、正しく$記号をつけられるか？」です。
このような出題は、日頃から表計算ソフトを使っている方にとって有利です。ですが、普段からExcelを使い慣れている方が「何も考えずにExcelの、F4キーに任せる」といった、行・列とも絶対参照でガチガチに固める癖がついている方は、今のうちに練習しておきましょう。
$記号をつける・つけないの判断が慣れないうちは、ここに書いた解き方を覚えて下さい。試験会場で焦った時も、この解き方に立ち返って考えると、自然と落ち着いて解くことができるものです。
例題 表を、このような計算式で埋めたい場合の素早く答えを見つけるためのテクニックは？
| … | L | M | N | O | P | Q | … | |
|---|---|---|---|---|---|---|---|---|
| ： | ||||||||
| 12 | A5+B6 | B5+B6 | C5+B6 | D5+B6 | E5+B6 | F5+B6 | ||
| 13 | A5+B7 | B5+B7 | C5+B7 | D5+B7 | E5+B7 | F5+B7 | ||
| 14 | A5+B8 | B5+B8 | C5+B8 | D5+B8 | E5+B8 | F5+B8 | ||
| 15 | A5+B9 | B5+B9 | C5+B9 | D5+B9 | E5+B9 | F5+B9 | ||
| 16 | A5+B10 | B5+B10 | C5+B10 | D5+B10 | E5+B10 | F5+B10 | ||
| ： |
解き方のテクニック
編集- 1.まずは、$記号のことを忘れます。
たとえば、A1、A$1、$A1、$A$1は、どれも同じくA1だと考えます。
- 2.複写する範囲の、「左上・左下・右上」のセルだけを見ます。
- 「セルD2の数式を、セルF7まで複写する」設問の場合は、セルD2・セルD7・セルF2に着目。
- 「セルC1の数式を、セルC5まで複写する」設問の場合は、セルC1とセルC5だけに着目。
- 3.これら3つのセルが“とりあえず正しく計算してくれる計算式”を考え出します。
ここではその結果、“左上・左下・右上”に入るべき計算式が、
| … | L | M | N | O | P | Q | … | |
|---|---|---|---|---|---|---|---|---|
| ： | ||||||||
| 12 | A5+B6 | … | … | … | … | F5+B6 | ||
| 13 | ： | |||||||
| 14 | ： | |||||||
| 15 | ： | |||||||
| 16 | A5+B10 | |||||||
| ： |
であったとします。
- 4.左上セルの値のうち、下方向に複写しても変わらない値を洗い出し、$記号をつけます。
| … | L | M | N | O | P | Q | … | |
|---|---|---|---|---|---|---|---|---|
| ： | ||||||||
| 12 | A5+B6 | … | … | … | … | F5+B6 | ||
| 13 | A5+B7 | |||||||
| 14 | A5+B8 | |||||||
| 15 | A5+B9 | |||||||
| 16 | A5+B10 | |||||||
| ： |
左上セルの計算式中には“A5”と書かれていますが、下方向であっても値が変化していません。ということは、“A5”のうち、行を表す数字部分のみを($記号で)固定すべきだとわかります。$記号は、固定させたい値のすぐ左につけますので、まずは“A$5”であると求まります。勘の良い方はここで「“$A$5”と書いても、同じでは？」とお考えかも知れません。ですが、まだ言い切れません。次に、右方向に複写することを考えるまで、答えを出すのを急いではいけません。
- 5.左上セルの値のうち、右方向に複写しても変わらない値を洗い出して、$記号をつけます。
| … | L | M | N | O | P | Q | … | |
|---|---|---|---|---|---|---|---|---|
| ： | ||||||||
| 12 | A5+B6 | B5+B6 | C5+B6 | D5+B6 | E5+B6 | F5+B6 | ||
| 13 | ： | |||||||
| 14 | ： | |||||||
| 15 | ： | |||||||
| 16 | A5+B10 | |||||||
| ： |
左上セルの計算式中には“B6”と書かれていますが、右方向であっても値が変化していません。ということは、“B6”のうち、列を表すアルファベット部分のみを($記号で)固定すべきだとわかります。$記号は、固定させたい値のすぐ左につけますので、“$B6”であると求まります。
- 6.先程の、4と5を合わせて考えます。
左上セルの計算式“A5+B6”に$記号をつけるならば、“A$5+$B6”である、と求まります。
表計算ソフトの分野は、特に午後試験の得点源です。解くためのポイントを解説しましたが、今の段階では、1問を解くのに1時間かかってしまうかもしれません。最初はそれでも良いのです。今は時間を気にするより、「確実に解ける」ことに力を注いで下さい。