計算が苦手な人は、Excelの関数を使うのも苦手です。Excelの関数は、足し算・引き算・掛け算・割り算ができる人を対象として作られているので、簡単な計算式が立てられない人が使えないのは当たり前のことです。
Excelで応用問題がスラスラと解けるようになるためには、まず、計算の基本パターンを暗記しなければなりません。掛け算九九を覚えなければ掛け算の文章題ができないのと同じです。Excelの基本パターンは、表の形を見たらノーヒントで解けるくらい練習して、暗記するべきです。
そこで、今回は、ITパスポート試験とその前身の初級シスアド午前試験の過去問のなかから、関数を使わない計算問題を出題します。すべて国家試験に出題された超重要問題ですから、何百回でも何千回でも練習して暗記しましょう。
- 絶対暗記！かけ算や割り算の計算はノーヒントで解けるように練習せよ
- 関数の基本SUM・IF・COUNTIFが使えるようになってから受験してほしい
- INTやMOD関数を用いて数値の一部を取り出す整数問題と関数の過去問
- 丸め誤差、循環参照、並べ替え、グラフ、マクロなど表計算過去問
目次
- ０．ITパスポート関連書籍について
- １．累計
- ２．差引残高
- ３．移動合計
- ４．数列
- ５．複合参照
- ６．ウェイトが％で示されている表
- ７．フラグによる加算
- ８．税抜・税込
- ９．100としたときの指標
- 10．換算
- 11．伸び率で予測する
- 12．売上構成比と粗利率の計算
- 13．売上構成比率累計
- 14．按分
- 15．累乗と複利計算
- 16．減価償却
- 17．変動費率と損益分岐点
０．ITパスポート関連書籍について
ITパスポート試験は入門レベルの情報処理の国家試験で、たくさんの対策テキストや問題集が販売されています。試験を受けなくてもテキストを読むだけで情報処理の基本が身に付きます。
１．累計
問題
月に６回の説明会を実施している。月間累計と年間累計を求めなさい。
出典と参考記事
平成19春初級シスアド問25改題。平成10年秋、平成8年でも同じ問題が出題されている。
- 【Excel数式】セルの「参照」が分からなければ相対参照は理解できない - わえなび ワード＆エクセル問題集
- 【Excel数式】計算が苦手な人のための「累計」「残高」「繰越」 - わえなび ワード＆エクセル問題集
解説
累計は足し算しかないので簡単です。累計の計算は最初の数を参照するところから始まります。
次に、上の数と左の数を足します。累計の最後の数は月間合計です。C列とD列は同じです。
これを２月にコピーします。月間はこれで良いですが、年間累計は間違いです。
１月の年間累計（月間合計）に第１回の数を足します。
これを３月にコピーします。３月の最後の数は３か月の合計となります。
２．差引残高
問題
出張で5万円程度の費用がかかることが予想されたため、出発前に仮払いとして5万円を受け取った。旅費の精算をしたい。表を完成させなさい。
出典と参考記事
平成11春初級シスアド問40、平成7年シスアド問30改題
解説
まず、原則として、収入と支出と残高は列を分けて入力するのが基本です。
差引残高も累計の一種です。残高は引き算です。
一番下の数値が最終の残高になります。
精算をするときは収入と支出の合計を求め、収入から支出を引きます。最終の残高と一致すれば精算完了です。
別解
差引残高の本来の求め方は「残高＋収入－支出」です。
３．移動合計
問題
今年の1月～12月について、12か月移動合計と累計を求めて、次の折れ線グラフを作成しなさい。
出典と参考記事
平成25春ITパスポート中問C問93改題
解説
通常、12か月の移動合計は12か月の売上をSUMで合計すればよいですが、昨年の合計を使って計算する場合は、昨年の売り上げを引きながら今年の売り上げを足します。つまり、差引残高と同じ計算になります。
- （累計）－（昨年）＋（今年）
まず、1月の移動合計は、昨年の合計から昨年の1月を引いてその代わりに今年の1月を足します。
2月以降はその残高から昨年を引き今年を足せばよいです。
売り上げの累計は最初の数を参照して、加算していくだけです。12か月移動合計も売上累計も最終的には今年の年間合計と一致します。
グラフは、A1:A13とC1:E13を選択して、折れ線グラフを挿入します。このように、データ・移動合計・累計の3つを折れ線で表したグラフのことをZグラフまたはZチャートと言います。
別解
今年の売り上げの合計があります。最終的にこの値になるのでこれを参照したとします。
11月の移動合計は今年の12月の売り上げを引き、昨年の12月の売り上げを足します。
上向きにオートフィルをします。
11月の売上累計は今年の12月の売り上げを引くだけです。逆算もできるように練習しましょう。
４．数列
問題
（１）1日目に1円、2日目に2円、3日目に4円というように前日の2倍のお金を貯金した時の貯金額をA列に、貯金の累計をB列に求めなさい。
（２）次の漸化式で表される数列{an}がある。第10項a10まで求めなさい。
a1=1、a2=2、an+2=an+1+an (n>=1)
出典と参考記事
（１）平成11春初級シスアド問37、平成6年シスアド問33改題
（２）平成20秋初級シスアド問25改題。平成16秋、平成14春、平成10年春、平成8年春でも同じ問題が出題されている。
- 【Excel】オートフィルは累積的に同じ計算を繰り返すときにも使う - わえなび ワード＆エクセル問題集
- 【Excel】計算式で数列を求めることによって規則的にデータを並べる練習 - わえなび ワード＆エクセル問題集
解説
（１）等比数列の総和
等差数列、等比数列、階差数列といった基本的な数列とその総和（累計）は、Excelで求められるようにしておくべきです。2倍にする式を入力します。
累計を求めます。翌日の貯金額－1になります。
（２）フィボナッチ数列
1、2、1+2=3、2+3=5、3+5=8、5+8=13、・・・のように2つ前の数と1つ前の数を足してできる数列のことをフィボナッチ数列（Fibonacci sequence）といいます。「フィボナッチ数列」という言葉も知っておくべきです。数列は前項または前々項を用いて計算しますが、必ず相対参照で求めます。
第10項は89ですが、これは34と55を足した数です。
５．複合参照
問題
（１）次の表でセルB2に数式を入力して、掛け算九九の表を完成させなさい。
（２）次の表の各行について、A列の値を基準としてB列の値との差をD列に、C列の値との差をE列に求めなさい。
出典と参考記事
（１）平成8秋初級シスアド問29改題
（２）平成12秋初級シスアド問38、平成7春初級シスアド問32改題
解説
（１）掛け算九九
複合参照は基本です。何回も練習しましょう。1行目とA列に数値が入力してあるのですから、「$1」「$A」と固定します。
（２）特定の列を基準とする
A列に数値が並んでいてそれを基準とする場合、「$A」と固定します。
６．ウェイトが％で示されている表
問題
次の表は、天気（晴れ、曇り、雨）の確率と商品ごとの転記別の売上予測額である。確率を踏まえた当日の売り上げ予想額（期待値）を求めなさい。
出典
平成29春ITパスポート問91改題
解説
表の中に比重（ウェイト）を表す数値があって、合計が１（100%）になる場合はそれを掛けます。確率は比重を表すパーセンテージでもあるので、それぞれ比重を掛けて足したらよいです。これを期待値といいます。
なお、確率は絶対参照で良いですが、複合参照の場合は行番号の固定です。オートフィルが縦方向のみの場合は、行番号を固定するかどうかを検討します。列番号は固定してもしなくてもよいです。
７．フラグによる加算
問題
2行目の数値は各政党の議席数である。「１」になっている政党の議席を足した場合の合計をF列に求めなさい。
出典と参考記事
平成10春初級シスアド問30改題
解説
単純に足し算をするのではなく、１または０を掛けることによって選択的に加算することができます。この１または０をフラグといいます。Q党とR党を「１」にするとQ党とR党だけの合計になり、その他の政党は加算されません。「表に０または１がたくさんあったらフラグ！」と考えても差し支えありません。
ウェイトの考え方で言えば、100%と0%を比重として掛けるのと同じです。2行目に数値が並んでいて、それを固定して掛け算をしているのですから「$2」とします。
８．税抜・税込
問題
セルD2とE2に設定した２種類の税率で、商品AとBの税込価格を計算しなさい。
出典と参考記事
平成30秋ITパスポート問80、平成22春ITパスポート問55改題
解説
比率で加算するときは（１＋率）、減算するときは（１－率）をかけ、逆算する場合は割り算をします。
税抜から税込へは（１＋税率）をかけ、税込から税抜へは（１＋税率）を割ります。絶対暗記です。
B列の税抜き価格と、2行目の税率を掛けるのですから「$B」「$2」の複合参照です。
ちなみに、ITパスポート試験は、誤って$B4+(1+$D$2)と入力したらどうなるか？という問題だったのですが、税率を$D$2つまり5%で完全に固定しているのですから、すべて５％加算されるだけです。
９．100としたときの指標
問題
1999年度売上を100としたときの各年度の売上指数を求めなさい。
出典と参考記事
平成20春初級シスアド問24、平成18春初級シスアド問26、平成11春初級シスアド問37改題
解説
1999年を基準とすれば2007年は約2倍になっていますから、そのことを指数として表そうとしています。変化後を変化前で割る鉄則に従えば、2007年を1999年で割ることは明らかです。
いっぱんに特定の基準を設定して比率を求める場合、その基準となる値で割ります。その基準を固定するので絶対参照で割ります。これは、1999年度売上を「1」とした時の指数です。
100とする場合は100倍すればよいです。
10．換算
問題
満点の点数が異なる3科目の試験を行い、4人が受験した。各試験の得点を100点満点に換算した数値を求めなさい。
出典と参考記事
平成18春初級シスアド問26改題
- 単位の換算と換算倍率、比例計算に関する応用問題【Excel割合の問題、就職活動】 - わえなび ワード＆エクセル問題集
- Excelの数式で簡単にできる得点調整の方法と傾斜配点の計算方法（正しい得点操作の方法） - わえなび ワード＆エクセル問題集
解説
換算率は換算後を換算前で割ります。例えば、250点を100点にする場合の換算率は100/250です。それを元の得点（素点）に掛けます。このとき、満点については行固定の複合参照になります。
11．伸び率で予測する
問題
6月の数値を基準として、過去2年間の伸び率（対6月比増加率）の平均を使って、今年の7月以降の数値を予測しようとしている。セルE4の数式を入力して、表を完成させなさい。
出典と参考記事
平成10春初級シスアド問32改題
解説
ある時点を基準として、「同じように伸びるだろう」と考えて予測するには、伸び率（正確に言えば増加率）を掛けます。平均増加率を求めます。増加率は7月/6月です。本当は2年間の増加率はそれぞれ分母が違いますので、増加率を足したり平均を求めたりしてはいけませんが、今回は問題の指示通り足して2で割って平均とします。
これに元の数と平均増加率を掛けることによって、将来の予測をすることができます。
6月を基準としているので、D列を固定します。
12．売上構成比と粗利率の計算
問題
次の表はあるラーメン屋の売り上げである。セルD6、E2～H2に数式を入力して表を完成させなさい。ただし、売上比率は売上構成比のことである。
出典と参考記事
平成27秋ITパスポート問89・90改題。平成24秋ITパスポート問86でも同じ問題が出題されたが、メニューは牛丼、親子丼、カツ丼、カレーだった。
- 構成比率は全体の何％？の前に、大きい数で割る感覚を鍛えなさい【Excel割合の問題、就職活動】 - わえなび ワード＆エクセル問題集
-
分母を失った％はパーセントではなく「パーセントポイント」です【Excel割合の問題、就職活動】 - わえなび ワード＆エクセル問題集
解説
粗利と売上構成比率の計算はノーヒントでできなければなりません！！！特に飲食店を営んでいる人で原価率や粗利率を知らない人はいません。しょう油ラーメンの原価350円はラーメンを作る時の材料費で、お客さんには分からない金額です。単価はメニューに書いてある値段です。
売上高は単価＊数量です。
500円のしょう油ラーメンを作るのに350円の費用がかかるのですから、粗利は150円です。2通りの求め方があります。単価から原価を引いて数量を掛ける方法と、売上高から粗利を引く方法です。両方できるようにしておきましょう。
売上数量、売上高、粗利の合計を求めておきます。
売上比率は各メニューの構成比率と考えられます。売上比率を求めてよく売れている商品を求めようとしています。醤油ラーメンの売上を合計で割ります。合計は絶対参照です。
粗利率は利益の比率です。利益の比率が高いほうがいいです。売り上げの中の粗利の割合なので割り算です。
売上比率と粗利率は6行目までオートフィルをします。
セルD6で求めた合計を右にオートフィルをする場合は売上比率までです。売上比率は合計をもととする比率なので合計してもかまいません（分母が同じ）が、粗利率はそれぞれの売上高が分母なので分母が異なります。分母が異なるパーセンテージは合計してはいけません。
また、単価や原価のように単位量当たりの数量は合計を求めてはいけません。
ちなみに、単価と原価があれば、原価率（原価/単価）と粗利率（１から原価率を引く）を求めることができますが、単価や原価の合計を求めてはいけないので、合計の原価率や粗利率も求めてはいけません。各商品の売上数量が異なるので当然のことです。
売上金額を求めてから、売上合計、売上原価合計または売上粗利合計を使って求めます。
13．売上構成比率累計
問題
各商品の売上高、売上原価、平均在庫高が次の表のとおりであるとき、表を完成させなさい。
出典と参考記事
平成21秋ITパスポート中問B問93改題
解説
原則として単価＊数量＝売上高、原価＊数量＝売上原価です。売り上げから原価を引けば利益になります。
これらの合計を求めます。
利益率は、売り上げの中の利益の割合なので、D列/B列です。この時の合計は合計売上原価/合計売上高であって、SUMではありません。
累計は売上高の累計です。売上高の大きい商品から順に累計を求めるのが一般的です。
構成比率は、各商品の売り上げを合計で割ります。
その累計を求めます。
別解
売上構成比率（G列）の累計と、売上累計（F列）の構成比率は同じです。売上累計の最後が合計なのでこれを利用して求めることもできます。
14．按分
問題
来年の売上目標を今年の10%増とした。各月の目標金額を次の2つの方法で求めなさい。
（１）各月の売上を10%増にして合計する。
（２）年間合計を10%増にして、各月で按分する
出典と参考記事
平成25春ITパスポート中問C問96改題
解説
通常は10%増にするのであれば、（１）のように各月の売り上げを1.1倍して合計すればよいです。
ITパスポート試験で出題されたのは（２）の按分する方法です。まず、年間合計を10%増にします。
按分は配分する合計に構成比率を掛けます。目標合計に構成比率（1月／今年の合計）を掛けます。合計は絶対参照ですが、縦向きにオートフィルをすることから、少なくとも「$14」の固定は必要です。
15．累乗と複利計算
問題
元金1000円のとき複利を計算しようとしている。
（１）元金を含めていくらになるか求めなさい。
（２）利息だけを求めなさい。
出典と参考記事
平成19秋初級シスアド問24、平成15春初級シスアド問23改題
- 【Excel】逆数と反数、平方根、累乗は初心者の段階で習得すべき＿数式の基本 - わえなび ワード＆エクセル問題集
- 【Excel財務関数序論】現在価値PVと将来価値FVと複利計算の基本的な考え方 - わえなび ワード＆エクセル問題集
解説
利率が0.02のとき、毎年1.02倍（=1+0.02）を繰り返して増やします。1.02倍を繰り返すので累乗になります。これが増加率になります。元金に増加率を掛けます。
元金は絶対参照、年数のA列と利率の2行目の複合参照です。
利息だけにするには、増加率から1を引けばよいです。
16．減価償却
問題
40万円で購入した備品の未償却残高と毎年の償却額を求めなさい。ただし、計算方法には定率法を用い、償却率は31.9%とし、小数点以下を考慮しなくてもよい。
出典と参考記事
平成11春初級シスアド問38改題
解説
40万円を1年目で一気に費用計上すると赤字になってしまって大変なので、10年くらいかけて徐々に減らし、残高を０円にしていこうという計算です。これを減価償却といいます。償却率31.9%とは、簡単に言えば前年の残高の31.9%を減らすという意味です。最初の残高は40万円です。40万円のうち31.9%をひきます。＄の固定は不要です。
償却額は前年の残高の31.9%です。これも＄の固定は不要です。
別解
未償却は償却の残りですから、未償却残高は68.1%です。したがって前年の残高の68.1%をかけるだけでもよいです。難しく見えますが単なる等比数列です。
17．変動費率と損益分岐点
問題
固定費1850千円、変動費率65%、売上高0～10000千円のときの総費用と利益を求めなさい。また、損益分岐点を求めなさい。
出典と参考記事
平成27春ITパスポート中問B問93・問94改題
解説
費用には固定費と変動費がありますが、固定費は一定で、変動費は売り上げに比例して増えます。変動費率65%ということは売上の65%が変動費であるということです、変動費率と固定費は絶対参照ですが、下向きにオートフィルをするので少なくとも「＄１」「＄２」の固定は絶対に必要です。
利益は売り上げから費用を引いた額です。
売り上げの65%が変動費なので残りの35%で固定費を支払わなければなりません。支払えたら黒字、支払えなければ赤字です。残りの35%と固定費が一致したらプラスマイナスゼロになります。これが損益分岐点です。売り上げに35％を掛けたら固定費になりますから、逆算して、固定費を35%で割ればよいです。
解説は以上です。
試験問題の著作権はIPA独立行政法人情報処理推進機構にあります。この解説は公式のものではありません。
- 絶対暗記！かけ算や割り算の計算はノーヒントで解けるように練習せよ
- 関数の基本SUM・IF・COUNTIFが使えるようになってから受験してほしい
- INTやMOD関数を用いて数値の一部を取り出す整数問題と関数の過去問
- 丸め誤差、循環参照、並べ替え、グラフ、マクロなど表計算過去問
