基本情報技術者試験の午後試験には、選択問題として表計算が出題されます。問題冊子の最後に表計算ソフトの仕様が掲載されていますが、試験時間中に読むヒマは無いので事前に把握しておく必要があります。
基本情報技術者試験で出題される関数はすべてExcel関数で再現可能であり、仕様もほぼ同じです。選択問題で表計算を選択するのであれば、出題される関数くらいはExcelで使えてほしいものです。
そこで、今回は、基本情報に出題されるすべての関数をExcelで再現してみようという企画です。簡単な問題なので受験生の皆さんはもちろん全問正解だと思いますが、こんな簡単な問題が出題されるわけがありませんので、実際には対策テキストまたは問題集を買ってしっかりと勉強しましょう。
目次
０．基本情報技術者試験について
基本情報技術者試験の問題に使用される関数の説明についての著作権はIPA情報処理推進機構にあります（IPA公式サイト：https://www.jitec.ipa.go.jp/）。また、基本情報技術者試験（表計算）の詳細は、市販のテキストをご覧ください。
１．数学・算術演算
（１）整数部 INT
問題
- A列に数値を入力した。B列に、この数値を超えない15の倍数を求めなさい。
- D列に3桁の数値を入力した。E列に、100の位を求めなさい。
IPAによる説明
整数部（算術式）
算術式の値以下で最大の整数を返す。
解説
15の倍数というのは整数を15倍すれば求められるので、A列の値を超えない15の倍数は、いったん15で割って切り捨てた値（整数値）を15倍すればよいです。Excelでは「=INT(A3/15)*15」、試験問題としては「=整数部(A3/15)*15」となります。
次に、関数「整数部」は大きい数値の上の桁を取り出すときにも使います。100の位を求めるには100で割って切り捨てればよいです。Excelでは「=INT(D3/100)」、試験問題としては「=整数部(D3/100)」となります。
（２）剰余 MOD
問題
- A列に3桁の数値を入力した。B列に、下2桁を求めなさい。
- D列に数値を入力した。E列に、4で割ったときの剰余を出力しなさい。ただし、4の倍数のときは4にしなさい。
IPAによる説明
剰余（算術式1,算術式2）
算術式1の値を被除数、算術式2の値を序数として除算を行ったときの剰余を返す。関数「剰余」と「整数部」は、剰余（x,y）=x-y*整数部（x/y）という関係を満たす。
解説
関数「剰余」は割り算の余りのことですが、大きい数値の下の桁を取り出すときにも使います。下2桁は100で割ったときの余りです。Excelでは「=MOD(A3,100)」、試験問題としては「=剰余(A3/100)」となります。
プラスの場合もマイナスの場合も4で割ったときの余りは必ず０～３です（Excelのきまり）。Nで割ったときの余りは０～N-1です。4で割ったときの余りを求めると4の倍数は０になります。
ところが、4の倍数から1を引いてから余りを求めると余りは３なので、それに1を足せば４になります。よって、Excelでは「=MOD(D3-1,4)+1」、試験問題としては「=剰余(D3-1,4)+1」となります。
（３）平方根 SQRT
問題
１～30のなかで平方根の中で小数部分を求めなさい。また、最も大きいものはどれか。
IPAによる説明
平方根（算術式）
算術式の値の非負の平方根を返す。算術式の値は、非負の数値でなければならない。
解説
数学で平方根と言えばプラスとマイナスの2つがありますが、基本情報技術者試験ではプラスのほう（非負）を指すというルールになっています。小数部分は元の数から整数部を引けばよいので、Excelでは「=SQRT(A3)-INT(SQRT(A3))」、試験問題としては「=平方根(A3)-整数部(平方根(A3))」となります。一番多いのはルート24です。
別解その１
上記の剰余の説明で「剰余（x,y）=x-y*整数部（x/y）という関係を満たす」と書いてあります。y=1とすると、「剰余（x,1）=x-整数部（x）」となります。つまり、剰余（x,1）は、xの小数部分となります。したがって「=MOD(SQRT(A3),1)」としてもよいです。試験問題では「=剰余(平方根(A3),1)」となります。
別解その２
Excelでは演算子を用いて0.5乗（^0.5）をするのが一般的です。「=MOD(A3^0.5,1)」となります。
（４）切上げROUNDUP、四捨五入ROUND、切捨てROUNDDOWN
問題
- A列の金額の1000円未満を四捨五入した金額をB列に求めなさい。
- 1000円札が十分にあるものとする。1000円札だけでA列の金額を支払いたい。C列に、支払う金額を求めなさい。
- 1000円札と硬貨が十分にあるものとする。1000円札と硬貨でA列の金額を支払いたい。D列に、1000円札の枚数を求めなさい。
IPAによる説明
切上げ（算術式,桁位置）
四捨五入（算術式,桁位置）
切捨て（算術式,桁位置）
算術式の値を指定した桁位置で、関数「切上げ」は切り上げた値を、関数「四捨五入」は四捨五入した値を、関数「切捨て」は切り捨てた値を返す。ここで、桁位置は小数第1位の桁を０とし、右方向を正として数えたときの位置とする。
解説
1000円未満つまり小数点から数えて下3桁を四捨五入するので、桁位置は－３です。Excelでは「=ROUND(A4,-3)」、試験問題としては「=四捨五入(A4,-3)」となります。
1000円札で支払うためには、1000円未満を切り上げる必要があります。Excelでは「=ROUNDUP(A4,-3)」、試験問題としては「=切上げ(A4,-3)」となります。
1000円未満は硬貨で支払えばよいので1000円未満を切り捨てて1000で割ります。Excelでは「=ROUNDDOWN(A4,-3)/1000」、試験問題としては「=切捨て(A4,-3)/1000」となります。
別解
「=ROUNDDOWN(A4/1000,0)」でもよいです。これは「=INT(A4/1000)」と同じです。
（５）結合 CONCATENATE
問題
A列とB列の値をハイフンで連結しなさい。
IPAによる説明
結合（式1,式2,・・・）
式1、式2、・・・のそれぞれの値を文字列として扱い、それらを引数の順につないでできる一つの文字列を返す。
解説
ExcelではCONCATENATE関数を用いて「=CONCATENATE(A3,"-",B3)」、試験問題としては「=結合(A3,'-',B3)」となります。
別解
なお、Excelでは演算子&を用いて結合するのが一般的です。「=A3&"-"&B3」です。
（６）乱数 RAND
問題
0以上100未満の小数を10個表示しなさい。
IPAによる説明
乱数（）
0以上100未満の一様乱数（実数値）を返す。
解説
RAND関数は０以上1未満の小数なので、100倍すれば０以上100未満の小数になります。Excelでは「=RAND()*100」、試験問題としては「=乱数()*100」となります。
２．統計・集計関数
（１）合計SUM、最大MAX、最小MIN
問題
A列～E列の５つの数値のうち、最大と最小を除く３つの平均を求めなさい。
IPAによる説明
合計（セル範囲）
セル範囲に含まれる数値の合計を返す。
最大（セル範囲）
セル範囲に含まれる数値の最大値を返す。
最小（セル範囲）
セル範囲に含まれる数値の最小値を返す。
解説
合計から最大と最小を引き、3で割れば平均となります。Excelでは「=(SUM(A3:E3)-MAX(A3:E3)-MIN(A3:E3))/3」、試験問題としては「=(合計(A3:E3)-最大(A3:E3)-最小(A3:E3))/3」となります。
（２）平均AVERAGE
問題
3か月移動平均を求めなさい。
IPAによる説明
平均（セル範囲）
セル範囲に含まれる数値の平均を返す。
解説
3か月移動平均は当月を含む過去3か月の平均であり、3月の移動平均は1月～3月の平均です。Excelでは「=AVERAGE(B3:B5)」、試験問題としては「=平均(B3:B5)」となります。
（３）個数COUNTA
問題
A列～C列の一部にデータを入力した。A列～C列にデータが入力されていれば1、空欄が1つでもあれば０と表示しなさい。
IPAによる説明
個数（セル範囲）
セル範囲に含まれるセルのうち、空白セルでないセルの個数を返す。
解説
空白でないセルを数える関数はCOUNTAですが、カウントすると０～３になります。A列～C列のうち１つでも空欄があれば2以下なので、3で割ったら０になります。
Excelでは「=INT(COUNTA(A4:C4)/3)」、試験問題としては「=整数部(個数(A4:C4)/3)」となります。
別解
Excelの場合は「=(COUNTA(A4:C4)=3)*1」でもよいです。
（４）順位 RANK
問題
A列に走り幅跳びの記録、D列に100ｍ走の記録を入力した。それぞれ順位を求めなさい。
IPAによる説明
順位（算術式,セル範囲,順序の指定）
セル範囲の中での算術式の値の順位を、順序の指定が０の場合は昇順で、１の場合は降順で数えて、その順位を返す。ここで、セル範囲の中に同じ値がある場合、それらを同順とし、次の順位は同順の個数だけ加算した順位とする。
解説
RANK関数は第3引数だけ注意しなければなりません。Excelの仕様とは逆になります。
幅跳びは数値が大きいほうが上位（降順）なので、Excelでは０ですが、試験では１を指定します。Excelでは「=RANK(A4,$A$4:$A$13,0)」、試験問題としては「=順位(A4,$A$4:$A$13,1)」となります。
100ｍ走のタイムは数値の小さいほうが先着（昇順）なので、Excelでは１ですが、試験では０を指定します。Excelでは「=RANK(D4,$D$4:$D$13,1)」、試験問題としては「=順位(D4,$D$4:$D$13,0)」となります。
（５）標本標準偏差STDEV.S、母標準偏差STDEV.P
問題
セル範囲A4:A13を標本として標準偏差を求めなさい。また、母集団として標準偏差を求めなさい。
IPAによる説明
標本標準偏差（セル範囲）
セル範囲に含まれる数値を標本として計算した標準偏差を返す。
母標準偏差（セル範囲）
セル範囲に含まれる数値を母集団として計算した標準偏差を返す。
解説
標準偏差は平均からの離れ具合（散らばり方の大きさ）をみる指標です。標準偏差が大きいほど分散しているということです。標本は母集団の一部分であり、標本標準偏差は、実際の母集団の標準偏差よりも小さくなりがちになるので補正しています。したがって、セル範囲が同じ場合は標本標準偏差のほうが少し大きくなります。
標本標準偏差は「=STDEV.S(A4:A13)」、母標準偏差は「=STDEV.P(A4:A13)」となります。
３．論理
（１）IF
問題
A列に数値を入力した。
- B列に累計を求めなさい。ただし、累計が1000を超えたら「over」としなさい。
- B列に累計を求めなさい。ただし、累計が900を超えたら1000との差を「残り～」と表示し、1000を超えたら「over」としなさい。
IPAによる説明
IF（論理式,式1,式2）
論理式の値がtrueのとき式1の値を、falseのとき式2を返す。
解説
累計をSUMで求める場合は始点だけ固定して「SUM($A$4:A4))」とします。したがって、Excelでは「=IF(SUM($A$4:A4)>1000,"over",SUM($A$4:A4))」、試験問題としては「=IF(合計($A$4:A4)>1000,'over',合計($A$4:A4))」となります。
900超と1000超では1000超のほうが範囲が狭い（条件がきつい）ので、1000超の判定を先に記述します。
- =IF(累計>1000,"over",IF(累計>900,"残り"&1000-累計,累計))
したがって、Excelでは「=IF(SUM($A$4:A4)>1000,"over",IF(SUM($A$4:A4)>900,"残り"&1000-SUM($A$4:A4),SUM($A$4:A4)))」、試験問題としては「=IF(合計($A$4:A4)>1000,"over",IF(合計($A$4:A4)>900,結合("残り",1000-合計($A$4:A4)),合計($A$4:A4)))」となります。
（２）論理積AND、論理和OR、否定NOT
問題
A列に西暦を4桁で入力した。4の倍数で100の倍数でない年、または400の倍数の年をうるう年とする。B列に、うるう年であればTRUE、そうでなければFALSEと表示しなさい。
IPAによる説明
論理積（論理式1,論理式2,・・・）
論理式1、論理式2、・・・の値が全てtrueのとき、trueを返す。それ以外のときfalseを返す。
論理和（論理式1,論理式2,・・・）
論理式1、論理式2、・・・の値のうち、少なくとも一つがtrueのとき、trueを返す。それ以外のときfalseを返す。
否定（論理式）
論理式の値がtrueのときfalseを、falseのときtrueを返す。
解説
「4の倍数で100の倍数でない」は条件を2つ満たす必要があるので論理積（AND）です。Excelでは「AND(MOD(A4,4)=0,MOD(A4,100)<>0)」、試験問題としては「論理積(剰余(A4,4)=0,剰余(A4,100)<>0)」となります。
否定を使うと、Excelでは「AND(MOD(A4,4),NOT(MOD(A4,100)))」とすることもできます。
「または400の倍数」は2つの条件のうち1つを満たせばよいので論理和（OR）です。Excelでは「=OR(～,MOD(A4,400)=0)」、試験問題としては「=論理和(～,剰余(A4,400)=0)」となります。
したがって、Excelでは「=OR(AND(MOD(A4,4)=0,MOD(A4,100)),MOD(A5,400)=0)」、試験問題としては「=論理和(論理積(剰余(A4,4)=0,剰余(A4,100)<>0),剰余(A4,400)=0)」となります。
ちなみに、先頭に否定を付けると、ANDとOR、不等号が全て逆になるのでExcelでは「=NOT(AND(OR(MOD(A4,4),MOD(A4,100)=0),MOD(A4,400)))」、試験問題としては「=否定(論理積(論理和(剰余(A4,4)<>0,剰余(A4,100)=0),剰余(A4,400)<>0)))」となります。
４．統計＋論理
（１）条件付個数 COUNTIF
問題
A列に日付を入力した。2011/1/31が何個あるか数えなさい。また、2011/2/1以降の日付が何個あるか数えなさい。
IPAによる説明
条件付個数（セル範囲,検索条件の記述）
セル範囲に含まれるセルのうち、検索条件の記述で指定された条件を満たすセルの個数を返す。検索条件の記述は比較演算子と式の組で記述し、セル範囲に含まれる各セルと式の値を、指定した演算子によって評価する。
解説
条件付きで個数を求めるにはCOUNTIFを使います。したがって、Excelでは「=COUNTIF(A4:A15,"2011/1/31")」、試験問題としては「=条件付個数(A4:A15,=日付(2011,1,31))」となります。
検索条件の記述はセルを参照することもできますから、Excelでは「=COUNTIF(A4:A15,C4)」、試験問題としては「=条件付個数(A4:A15,=C4)」とすることもできます。
「2/1以降」は不等号を使います。Excelでは「=COUNTIF(A4:A15,">=2011/2/1")」、試験問題としては「=条件付個数(A4:A15,>=日付(2011,2,1)」となります。
検索条件の記述はセルを参照することもできますから、Excelでは「=COUNTIF(A4:A15,">="&C5)」、試験問題としては「=条件付個数(A4:A15,>=C5)」とすることもできます。
（２）条件付合計 SUMIF
問題
A列に日付、B列に金額を入力した。2011/1/31の合計金額を求めなさい。また、2011/2/1以降の合計金額を求めなさい。
IPAによる説明
条件付き合計（検索のセル範囲,検索条件の記述,合計のセル範囲）
行数及び列数が共に同じ検査のセル範囲と合計のセル範囲に対して、検索と合計を行う。検索のセル範囲に含まれるセルのうち、検索条件の記述で指定される条件を満たすセルをすべて探す。検索条件の記述を満たした各セルについての左上端からの位置と、合計のセル範囲中で同じ位置にある各セルの値を合計して返す。
検索条件の記述は比較演算子と式の組で記述し、検索のセル範囲に含まれる各セルと式の値を、指定した比較演算子によって評価する。
解説
条件付合計（SUMIF）は、条件付個数（COUNTIF）と同じように検索する範囲と検索条件を記述しますが、さらに、合計する範囲を指定します。1/31を探して合計を求める場合、Excelでは「=SUMIF(A4:A15,D4,B4:B15)」、試験問題としては「=条件付合計(A4:A15,=D4,B4:B15)」となります。
2/1以降は不等号を用いて、Excelでは「=SUMIF(A4:A15,">="&D5,B4:B15)」、試験問題としては「=条件付合計(A4:A15,>=D5,B4:B15)」となります。
５．検索
（１）垂直照合 VLOOKUP
問題
- 1/30～2/2の午前・昼間・夕方の時間帯別の売上金額を入力した。2/1午前と夕方の売上を求めなさい。
- 1/24～2/15の売上金額を入力した。1月最後の売上を求めなさい。
IPAによる説明
垂直照合（式,セル範囲,列の位置,検索の指定）
セル範囲の左端列を上から下に走査し、検索の指定によって指定される条件を満たすセルが現れる最初の行を探す。その行に対して、セル範囲の左端列から列を1、2、・・・と数え、セル範囲に含まれる列の位置で指定した列にあるセルの値を返す。
- 検索の指定が0の場合の条件：式の値と一致する値を検索する。
- 検索の指定が1の場合の条件：式の値以下の最大値を検索する。このとき、左端列は上から順に昇順に整列されている必要がある。
解説
条件付合計では検索する列と合計する列を別々に指定しましたが、垂直照合の場合は、検索する列（A列）を左端列として結果を返す列（C列＝3列目）を含めて範囲指定します。2/1を上から探して最初に見つかった行（午前）の金額を返すので、Excelでは「=VLOOKUP(A17,A4:C15,3,0)」、試験問題としては「=垂直照合(A17,A4:C15,3,0)」となります。通常は第4引数を0にします。
1月最後は1/31ですが、その日付がありません。1/31以下の最大値を探すときは第4引数を１にします。Excelでは「=VLOOKUP("2011/1/31"*1,E4:F15,2,1)」、試験問題としては「=垂直照合(日付(2011,1,31),E4:F15,2,1)」となります。
（２）水平照合 HLOOKUP
問題
A列に出発空港コード、B列に出発空港名、4行目に到着空港コード、5行目に到着空港名を入力した。出発・到着のコードからそれぞれ空港名を求めなさい。
IPAによる説明
水平照合（式,セル範囲,行の位置,検索の指定）
セル範囲の上端行を左から右に走査し、検索の指定によって指定される条件を満たすセルが現れる最初の列を探す。その列に対して、セル範囲の上端行から行を1、2、・・・と数え、セル範囲に含まれる行の位置で指定した行にあるセルの値を返す。
- 検索の指定が0の場合の条件：式の値と一致する値を検索する。
- 検索の指定が1の場合の条件：式の値以下の最大値を検索する。このとき、上端行は左から順に昇順に整列されている必要がある。
解説
コードと名称が縦に並んでいるときは垂直照合（VLOOKUP）、横に並んでいるときは水平照合（HLOOKUP）です。
Excelでは「=VLOOKUP(B13,A6:B10,2,0)」「=HLOOKUP(B14,C4:G5,2,0)」、試験問題としては「=垂直照合(B13,A6:B10,2,0)」「=水平照合(B14,C4:G5,2,0)」となります。通常は第4引数を0にします。
（３）照合検索 LOOKUP
問題
A～Eの場所を探して、それに対応する都市名を求めなさい。
IPAによる説明
照合検索（式,検索のセル範囲,抽出のセル範囲）
1行又は1列を対象とする同じ大きさの検索のセル範囲と抽出のセル範囲に対して、検索のセル範囲を左端又は上端から走査し、式の値と一致する最初のセルを探す。見つかったセルの検索のセル範囲の中での位置と、抽出のセルの範囲の中での位置が同じセルの値を返す。
解説
Excelでは「=LOOKUP(C4,A4:A8,A10:A14)」、試験問題としては「=照合検索(C4,A4:A8,A10:A14)」となります。
（４）照合一致 MATCH
問題
- A4:A13のうち上から何番目のデータかを求めなさい。
- D4:D13のうち10の位が一致するものが上から何番目のデータかを求めなさい。
IPAによる説明
照合一致（式,セル範囲,検索の指定）
1行又は1列を対象とするセル範囲に対して、セル範囲の左端又は上端から走査し、検索の指定によって指定される条件を満たす最初のセルを探す。見つかったセルの位置を、セルの範囲の左端又は上端から1、2、・・・と数えた値とし、その値を返す。
- 検索の指定が0の場合の条件：式の値と一致する値を検索する。
- 検索の指定が1の場合の条件：式の値以下の最大値を検索する。このとき、セル範囲は上端又は左端から順に昇順に整列されている必要がある。
- 検索の指定が-1の場合の条件：式の値以上の最小値を検索する。このとき、セル範囲は上端又は左端から順に降順に整列されている必要がある。
解説
セルA16のデータをA4:A13から探すので、Excelでは「=MATCH(A16,A4:A13,0)」、試験問題としては「=照合一致(A16,A4:A13,0)」です。上から何番目かが分かります。通常は第3引数を０にします。
セルD16のデータをD4:D13から探すので、Excelでは「=MATCH(D16,D4:D13,1)」、試験問題としては「=照合一致(D16,D4:D13,1)」です。完全一致ではなく、検索値以下の最大値を求めるには第3引数を１にします。
（５）表引き INDEX
問題
4桁の数字のうち上2桁と下2桁で判定して該当するアルファベットを求めなさい。例えば、上2桁が10～19で、下2桁が70～79だったら「H」とする。
IPAによる説明
表引き（セル範囲,行の位置,列の位置）
セル範囲の左上端から行と列をそれぞれ1、2、・・・と数え、セル範囲に含まれる行の位置と列の位置で指定した場所にあるセルの値を返す。
解説
上2桁は「整数部(A11/100)」です。上2桁をA5:A9から探すので、Excelでは「MATCH(INT(A11/100),A5:A9,1)」、試験問題としては「照合一致(整数部(A11/100),A5:A9,1)」です。上から何番目かが分かります。
下2桁は「剰余(A11,100)」です。下2桁をB4:F4から探すので、Excelでは「MATCH(MOD(A11,100),B4:F4,1)」、試験問題としては「照合一致(剰余(A11,100),B4:F4,1)」です。左から何番目かが分かります。
二次元の表から行番号、列番号を使って表引きをすればよいので「=表引き(B5:F9,～,～)」となります。したがって、Excelでは「=INDEX(B5:F9, MATCH(INT(A11/100),A5:A9,1), MATCH(MOD(A11,100),B4:F4,1))」、試験問題としては「=表引き(B5:F9,照合一致(整数部(A11/100),A5:A9,1),照合一致(剰余(A11,100),B4:F4,1))」となります。
６．マクロの相対セル参照
問題
4桁の数字のうち10の位と1の位で判定して該当するアルファベットを「相対」で求めなさい。例えば、34だったら30+4なので「N」とする。
IPAによる説明
相対（セル変数,行の位置,列の位置）
セル変数で指定したセルを基準のセルとする。そのセルの行番号と列番号の位置を０とし、下または右方向を正として数え、行の位置と列の位置で指定した数と一致する場所にあるセルを表す変数である。
解説
10の位は、Excelでは「INT(A11/10)」、試験問題としては「整数部(A11/10)」です。これで上から何番目かが分かります。1の位は、Excelでは「MOD(A11,10)」、試験問題としては「剰余(A11,10)」です。これで左から何番目かが分かります。
基本情報技術者試験の相対（OFFSET）は、表引きとほぼ同じ考え方ですが、範囲を指定するのではなく、始点のセルを指定するというのが異なります。
例えば、始点をセルA4とした場合、INDEXとほぼ同じ式になります。Excelでは「=OFFSET(A4, INT(A11/10), MOD(A11,10))」、試験問題としては「=相対(A4, 整数部(A11/10), 剰余(A11,10))」となります。
始点をセルB5とすると行番号と列番号を0から始めるので、それぞれ１を引くことになります。Excelでは「=OFFSET(B5, INT(A11/10)-1, MOD(A11,10)-1)」、試験問題としては「=相対(B5, 整数部(A11/10)-1, 剰余(A11,10)-1)」となります。
７．さいごに
この記事はIPAによる関数の説明をもとにサンプル問題を作成したものであり、合格を保証するものではありません。
解説は以上です。
