現在のITパスポート試験は120分で100問の問題を解く暗記型の試験であり、表計算の問題は1問程度しか出題されません。得点率60%が合格ラインなので、表計算の問題を捨てても合否には全く影響しません。ITパスポートに合格したところでパソコンの技能の証明にはなりませんから、表計算の問題を捨ててもかまわないと思います。
しかし、国家試験で表計算を捨てることはできても、実務でExcelを捨てることはできません。ITパスポートを取得するのはシステム部門ではなく一般の職種の人であり、IT活用スキルをアピールしておきながら、IF関数すら活用できないようでは話になりません。少なくともオートSUM、IF関数、COUNTIF関数が使えるようになってから、ITパスポート試験を受けてほしいものです。
そこで、ITパスポート試験とその前身の初級シスアド午前試験の過去問のなかから、SUM、IF、COUNTIFの問題を出題します。
- 絶対暗記！かけ算や割り算の計算はノーヒントで解けるように練習せよ
- 関数の基本SUM・IF・COUNTIFが使えるようになってから受験してほしい
- INTやMOD関数を用いて数値の一部を取り出す整数問題と関数の過去問
- 丸め誤差、循環参照、並べ替え、グラフ、マクロなど表計算過去問
目次
- ０．ITパスポート関連書籍について
- １．累計をSUMで求める
- ２．按分
- ３．最大最小を除く平均
- ４．平均だからといってAVERAGEとは限らない
- ５．COUNTIFと複合参照
- ６．空白にする
- ７．IF文に演算を付加することができる
- ８．IFと複合参照
- ９．IF、AND、OR
- 10．入れ子
- 11．ABC分析
０．ITパスポート関連書籍について
ITパスポート試験は入門レベルの情報処理の国家試験で、たくさんの対策テキストや問題集が販売されています。試験を受けなくてもテキストを読むだけで情報処理の基本が身に付きます。
１．累計をSUMで求める
問題
セルA2～A11の累計を求めたい。次の方法によりB～D列に累計を求めなさい。
（１）セルA2を初期値として、足し算で順次加算する方法
（２）セルC2にSUMを用いた数式を入れ、C11まで複写する方法
（３）セルD11にSUMを用いた数式を入れ、D2まで複写する方法
出典
平成21春ITパスポート問87改題
解説
大きい数値から順に累計を求めること（降順の累計）は、売上分析、生産管理等で必須の計算手法です。10秒以内で求められるように練習すべきです。まず、SUMを使わない方法としては、初めの数を参照して、上の数と左の数を足します。
次にSUMの範囲を固定する方法です。=SUM(固定:相対)で累計を求めることができます。これも重要です。
このときセルC11は「=SUM($A$2:A11)」となりますから、セルD11にA2～A11の合計を求め、A2だけ絶対参照にして上向きにコピーすることもできます。
別解
下から累計を求めて（昇順の累計、SUM(A11:$A$11)）、合計から引くという方法もあります。11行目に「=SUM($A$2:$A$11)-SUM(A11:$A$11)+A11」と入力して上向きにコピーします。
２．按分
問題
（１）発生した間接費用を各課の売上高に応じて比例配分しなさい。
（２）次の表は営業担当者4人の職能と営業活動占有度を数値化して表したものである。個人売上目標額を「職能＊営業活動占有度」に比例して決めるとき、営業所売上目標額を配分しなさい。
出典と参考記事
（１）平成17春初級シスアド問24、平成9初級シスアド問40改題
（２）平成21春初級シスアド問23、平成18秋初級シスアド問24、平成13春初級シスアド問27改題
- 【Excel合計】表の意味を考えてSUM関数を使ってもよいか判断する練習問題 - わえなび ワード＆エクセル問題集
- 【Excel】按分の計算方法と按分比率を補正する計算式の練習問題【割合の問題、就職活動】 - わえなび ワード＆エクセル問題集
解説
（１）売り上げで配分
複数の人に配分するときに特定の比率を用いることがあります。これを按分といいます。按分は分けるものに構成比率（内訳/SUM）を掛けます。これは絶対暗記です！！間接費用に構成比率を掛けます。
間接費用と合計については絶対参照ですが、縦向きにオートフィルをするので、少なくとも「$1」「$3」「$7」の行番号の固定は必要です。
（２）指数で按分
按分の比率はポイントや指数で表すこともあります。今回の場合は２つの指数を掛けた値を按分比率とします。
全体の目標に構成比率を掛けます。
按分比率の合計と営業所売上目標額については絶対参照ですが、横向きにオートフィルをするので、少なくとも「$F」「$B」「$E」の列番号の固定は必要です。
３．最大最小を除く平均
問題
5人の審判員が付けた点数のうち、最高点と最低点それぞれを1人ずつ除いた３人の平均を計算して得点とする競技がある。この方式で得点を求めなさい。
出典と参考記事
平成14秋初級シスアド問26改題。平成13秋、平成11春、平成9でも同じ問題が出題された。
解説
平均と言えばAVERAGEですが、合計を個数で割っていることを理解していれば、AVERAGEを使わない平均も計算できるはずです。問題文の通り、合計から最大と最小を引いて３で割ればよいです。
- =(SUM(A2:E2)-MAX(A2:E2)-MIN(A2:E2))/3
別解
人数をCOUNT-2としてもよいです。
- =(SUM(A2:E2)-MAX(A2:E2)-MIN(A2:E2))/(COUNT(A2:E2)-2)
４．平均だからといってAVERAGEとは限らない
問題
（１）3つの学校で実施した小遣い金額調査の集計結果を用いて、3校生徒全体の1人当たりの平均小遣いを求めなさい。
（２）各支店における売上高と営業要員数から、営業要員ごとの平均売上高と全支店の合計値を求めなさい。
（３）1組～6組の受講生に10点満点のテストを実施し、2行目～11行目に点数を入力した。空欄を欠席者とするとき、各組の受験者数と平均点を求めなさい。また、受験者全体の数と平均点を求めなさい。
出典と参考記事
（１）平成13春初級シスアド問28、平成10秋初級シスアド問42改題
（２）平成12春初級シスアド問11改題
（３）平成26秋ITパスポート中問C問94改題
- 【Excel関数】平均AVERAGEは、数値の個数COUNTを意識して使うこと - わえなび ワード＆エクセル問題集
- 【Excel関数】加重平均とは「重み」が異なることを考慮した平均のことです - わえなび ワード＆エクセル問題集
解説
（１）人数＊平均の総和／合計人数
各学校の平均は人数で割ったものですが、分母が異なるもの同士を合計したり平均してはいけません。いったん人数＊平均で合計を求めてから、合計人数で割ります。
- =(B2*C2+B3*C3+B4*C4)/SUM(B2:B4)
（２）平均の平均ではない
営業要員ごとの平均売上高は売上高を人数で割ります。
合計を求めますが、平均の合計を求めてはいけません。
平均を求めた場合は、全体を5つの支店で割ったものであり、間違いです。
営業23人の平均を求めます。全体の売上高を23で割ります。
（３）COUNTとAVERAGE
受験者数はCOUNT関数（数値の個数）で求めます。
平均点はAVERAGE関数で求めます。
受験者数は合計してもよいですが、平均点の合計や平均を求めてはいけません。
元のデータで平均を求めます。
別解
全体の合計を求めてから、受験者数で割ります。
５．COUNTIFと複合参照
問題
ある店舗では購入者にアンケートを実施した。アンケートの内容は、店舗、接客サービス、案内パンフレット、技術仕上がり、価格、総合的な満足度の６つの項目について、5段階評価で回答を求めた。回答が得られた15人分について、その回答を3行目～17行目に入力した。21行目～25行目に評価1～5の人数を求めなさい。
また、次のように入力した場合はどうか。
出典
平成27秋ITパスポート中問A問85改題。平成26秋ITパスポート中問C問93、平成23秋ITパスポート問69、平成18秋初級シスアド問23、平成15春初級シスアド問26、平成13秋初級シスアド問23、平成10秋初級シスアド問33でも類題が出題されている。COUNTIFは今までこのパターンしか出題されていない。
解説
アンケートを集計するときには、アンケート1人分の回答を1行のデータとして入力します。
人数を集計するにはCOUNTIF関数を使います。COUNTIF(集計する範囲,検索条件)です。
複合参照にします。集計する範囲は3行目～17行目で固定し、評価はA列で固定します。
COUNTIFは通常、複合参照で使います。範囲は行番号を固定し、検索条件は列番号を固定します。
横向きに入力した場合、集計範囲が横長になります。範囲は列番号を固定し、検索条件は行番号を固定します。
集計する範囲はB～P列で固定し、評価は13行目の固定になります。
６．空白にする
問題
前年比を求めなさい。ただし、エラーの場合は空白にしなさい。
出典
平成6シスアド問34改題
解説
前年比を求めます。当期を前期で割ります。ゼロで除算するとエラーになります。
空白にするときは「=IF(条件,"",～～～)」とします。
７．IF文に演算を付加することができる
問題
定価と数量をかけて売上高を求めなさい。ただし、数量が10を超えているときは1割引きにする。
出典
平成13春初級シスアド問26改題
解説
数量が10より大きいとき、定価＊数量の0.9倍、それ以外は定価＊数量です。
別解
定価＊数量で、数量が10より大きいとき0.9倍、それ以外は1倍をかけることもできます。
８．IFと複合参照
問題
（１）支店ごとの月別の売上データを評価する。各月の各支店の評価欄に、該当支店の売上額がA～C支店の該当月の売上額の平均値を下回る場合に×、平均値以上であれば〇を表示しなさい。
（２）ある店では、親子丼を廃止して新たにサラダと玉子をメニューに加えることを検討している。サラダと玉子の売上数量を0～5000とするとき、サラダと卵の粗利益の合計が、親子丼の粗利益以上の場合は〇、親子丼の粗利益未満の場合は×としなさい。
出典
（１）平成30春ITパスポート問60改題
（２）平成24秋ITパスポート中問A問88改題
解説
（１）売上額が平均未満の場合に×、それ以外は〇とします。
売上額は相対参照のままで良いですが、平均の範囲は複合参照にします。
右にオートフィルしても変わらないように、C～E列を固定します。
- =IF(C2<AVERAGE($C2:$E2),"×","〇")
（２）粗利は単価－原価なので、（単価－原価）＊売上数量 で粗利益を求めることができます。それだけ求めると次のようになります。単価と原価は絶対参照ですが、サラダ売上数量は行固定、玉子売上数量は列固定です。
- =($B$3-$C$3)*C$7+($B$4-$C$4)*$B8
同じように親子丼の粗利益も求められますから、IF文にすると次のようになります。
- =IF( ($B$3-$C$3)*C$7+($B$4-$C$4)*$B8>=($B$2-$C$2)*$D$2,"〇","×")
９．IF、AND、OR
問題
２つの科目X、Yの成績を評価して合否を判定する。合計点が120点以上であり、かつ、2科目とも50点以上であれば合格、それ以外は不合格と表示しなさい。
出典
平成31春ITパスポート問98改題。
解説
2つ以上の条件を両方満たす場合はAND（論理積）、どちらか一方を満たせばよい場合はOR（論理和）です。合計点120点以上、X科目50点以上、Y科目50点以上の3つの条件を満たした場合を合格とするので、AND(合計>=120,X>=50,Y>=50)となります。
10．入れ子
問題
（１）セルB2～C8に学生の成績が科目ごとに入力されていて、数学50点以上を合格とする。数学が50点未満であっても英語が50点以上であれば合格として、それ以外を不合格としなさい。
（２）セルA2～A16の得点について平均との差を求めなさい。また、得点が平均点より10点以上高ければ「優」、平均点以上で平均点との差が10点未満であれば「良」、平均点より低くて差が10点以内なら「可」、それ以外は「不可」と表示しなさい。
出典
（１）平成28秋ITパスポート問82改題
（２）平成20春初級シスアド問23、平成14春初級シスアド問23改題
解説
（１）3分岐
2科目ともに50点以上で合格ならAND(数学>=50,英語>=50)、2科目のうちどちらか1つでも50点以上で合格ならOR(数学>=50,英語>=50)です。数学が50点以上なら英語の得点に関係なく合格とし、数学が50点未満なら英語の点数で合否を決めるという場合、ANDでもORでもありません。
「英語の点数で合否を決める」部分にIF関数を入れます。
- =IF(B2>=50,"合格",IF(C2>=50,"合格","不合格"))
（２）4分岐
平均との差は、得点から平均を引きます。平均は絶対参照です。
平均との差が10以上なら「優」、０以上なら「良」、－10点以上なら「可」、それ以外は「不可」とします。
- =IF(B2>=10,"優",IF(B2>=0,"良",IF(B2>=-10,"可","不可")))
IF関数の入れ子については次の式を何度も練習して暗記すべきです。
- =IF(A1>=60,"A","B")
- =IF(A1>=80,"A",IF(A1>=60,"B","C"))
- =IF(A1>=80,"A",IF(A1>=70,"B",IF(A1>=60,"C","D")))
- =IF(A1>=90,"S",IF(A1>=80,"A",IF(A1>=70,"B",IF(A1>=60,"C","D"))))
11．ABC分析
問題
売上構成比率を基準に商品をランク分けし、ランクに応じた仕入れ、販売管理の重点化を図っている。上位70％以内に入る商品をA、上位90％以内に入る商品をB、それ以外をCとして、商品のランクを求めなさい。
出典と参考記事
平成21秋ITパスポート問95改題
- 【Excel】累計と構成比率の求め方が分かれば、累計構成比も求められる - わえなび ワード＆エクセル問題集
- 【Excelグラフ】２軸の複合グラフを作る方法とパレート図の作り方 - わえなび ワード＆エクセル問題集
解説
まず、パレートの法則（2割の商品が8割の売り上げをもたらす）に基づいてABC分析を行うときは必ず降順に並べ替えます。しかし、合計を並べ替えてはいけないので、A1:D11を範囲選択して並べ替えます。
売上高の累計を合計で割ると売上構成比率累計になります。
ランクは入れ子で入力します。
解説は以上です。
試験問題の著作権はIPA独立行政法人情報処理推進機構にあります。この解説は公式のものではありません。
- 絶対暗記！かけ算や割り算の計算はノーヒントで解けるように練習せよ
- 関数の基本SUM・IF・COUNTIFが使えるようになってから受験してほしい
- INTやMOD関数を用いて数値の一部を取り出す整数問題と関数の過去問
- 丸め誤差、循環参照、並べ替え、グラフ、マクロなど表計算過去問
