わえなび ワード&エクセル問題集

当サイトにリンクを張るのは自由です。WordとExcelの練習問題の動画を「Youtube」で無料公開しています。みんなで勉強しましょう!

【Excel】絶対暗記!かけ算や割り算の計算はノーヒントで解けるように練習せよ(ITパスポート表計算Part1)

計算が苦手な人は、Excelの関数を使うのも苦手です。Excelの関数は、足し算・引き算・掛け算・割り算ができる人を対象として作られているので、簡単な計算式が立てられない人が使えないのは当たり前のことです。

Excelで応用問題がスラスラと解けるようになるためには、まず、計算の基本パターンを暗記しなければなりません。掛け算九九を覚えなければ掛け算の文章題ができないのと同じです。Excelの基本パターンは、表の形を見たらノーヒントで解けるくらい練習して、暗記するべきです。

そこで、今回は、ITパスポート試験とその前身の初級シスアド午前試験の過去問のなかから、関数を使わない計算問題を出題します。すべて国家試験に出題された超重要問題ですから、何百回でも何千回でも練習して暗記しましょう。

 

 

目次

1.累計

問題

月に6回の説明会を実施している。月間累計と年間累計を求めなさい。

f:id:waenavi:20190814105220j:plain

 

出典と参考記事

平成19春初級シスアド問25改題。平成10年秋、平成8年でも同じ問題が出題されている。

解説

累計は足し算しかないので簡単です。累計の計算は最初の数を参照するところから始まります。

f:id:waenavi:20190814105603j:plain

f:id:waenavi:20190814105723j:plain

 

次に、上の数と左の数を足します。累計の最後の数は月間合計です。C列とD列は同じです。

f:id:waenavi:20190814105831j:plain

f:id:waenavi:20190814105837j:plain

 

これを2月にコピーします。月間はこれで良いですが、年間累計は間違いです。

f:id:waenavi:20190814105924j:plain

 

1月の年間累計(月間合計)に第1回の数を足します。

f:id:waenavi:20190814110022j:plain

 

これを3月にコピーします。3月の最後の数は3か月の合計となります。

f:id:waenavi:20190814110045j:plain

 

2.差引残高

問題

出張で5万円程度の費用がかかることが予想されたため、出発前に仮払いとして5万円を受け取った。旅費の精算をしたい。表を完成させなさい。

f:id:waenavi:20190814110231j:plain

 

出典と参考記事

平成11春初級シスアド問40、平成7年シスアド問30改題

解説

まず、原則として、収入と支出と残高は列を分けて入力するのが基本です。

f:id:waenavi:20190814110508j:plain

 

差引残高も累計の一種です。残高は引き算です。

f:id:waenavi:20190814110810j:plain

 

一番下の数値が最終の残高になります。

f:id:waenavi:20190814110741j:plain

 

精算をするときは収入と支出の合計を求め、収入から支出を引きます。最終の残高と一致すれば精算完了です。

f:id:waenavi:20190814110937j:plain

 

別解

差引残高の本来の求め方は「残高+収入-支出」です。

f:id:waenavi:20190814111005j:plain

 

3.移動合計

問題

今年の1月~12月について、12か月移動合計と累計を求めて、次の折れ線グラフを作成しなさい。

f:id:waenavi:20190814111244j:plain

f:id:waenavi:20190814193248j:plain

 

出典と参考記事

平成25春ITパスポート中問C問93改題

解説

通常、12か月の移動合計は12か月の売上をSUMで合計すればよいですが、昨年の合計を使って計算する場合は、昨年の売り上げを引きながら今年の売り上げを足します。つまり、差引残高と同じ計算になります。

  • (累計)-(昨年)+(今年)

まず、1月の移動合計は、昨年の合計から昨年の1月を引いてその代わりに今年の1月を足します。

f:id:waenavi:20190814111610j:plain

 

2月以降はその残高から昨年を引き今年を足せばよいです。

f:id:waenavi:20190814111648j:plain

 

売り上げの累計は最初の数を参照して、加算していくだけです。12か月移動合計も売上累計も最終的には今年の年間合計と一致します。

f:id:waenavi:20190814111751j:plain

 

グラフは、A1:A13とC1:E13を選択して、折れ線グラフを挿入します。このように、データ・移動合計・累計の3つを折れ線で表したグラフのことをZグラフまたはZチャートと言います。

f:id:waenavi:20190814193441j:plain

 

別解

今年の売り上げの合計があります。最終的にこの値になるのでこれを参照したとします。

f:id:waenavi:20190814111935j:plain

 

11月の移動合計は今年の12月の売り上げを引き、昨年の12月の売り上げを足します。

f:id:waenavi:20190814112051j:plain

 

上向きにオートフィルをします。

f:id:waenavi:20190814112112j:plain

 

11月の売上累計は今年の12月の売り上げを引くだけです。逆算もできるように練習しましょう。

f:id:waenavi:20190814112211j:plain

 

4.数列

問題

(1)1日目に1円、2日目に2円、3日目に4円というように前日の2倍のお金を貯金した時の貯金額をA列に、貯金の累計をB列に求めなさい。

f:id:waenavi:20190814112610j:plain

 

(2)次の漸化式で表される数列{an}がある。第10項a10まで求めなさい。

a1=1、a2=2、an+2=an+1+an (n>=1)

f:id:waenavi:20190814113046j:plain

 

出典と参考記事

(1)平成11春初級シスアド問37、平成6年シスアド問33改題
(2)平成20秋初級シスアド問25改題。平成16秋、平成14春、平成10年春、平成8年春でも同じ問題が出題されている。

解説

(1)等比数列の総和

等差数列、等比数列、階差数列といった基本的な数列とその総和(累計)は、Excelで求められるようにしておくべきです。2倍にする式を入力します。

f:id:waenavi:20190814112704j:plain

 

累計を求めます。翌日の貯金額-1になります。

f:id:waenavi:20190814112754j:plain

 

(2)フィボナッチ数列

1、2、1+2=3、2+3=5、3+5=8、5+8=13、・・・のように2つ前の数と1つ前の数を足してできる数列のことをフィボナッチ数列Fibonacci sequence)といいます。「フィボナッチ数列」という言葉も知っておくべきです。数列は前項または前々項を用いて計算しますが、必ず相対参照で求めます。

f:id:waenavi:20190814113332j:plain

 

第10項は89ですが、これは34と55を足した数です。

f:id:waenavi:20190814115103j:plain

 

5.複合参照

問題

(1)次の表でセルB2に数式を入力して、掛け算九九の表を完成させなさい。

f:id:waenavi:20190814115213j:plain

 

(2)次の表の各行について、A列の値を基準としてB列の値との差をD列に、C列の値との差をE列に求めなさい。

f:id:waenavi:20190814115803j:plain

 

出典と参考記事

(1)平成8秋初級シスアド問29改題
(2)平成12秋初級シスアド問38、平成7春初級シスアド問32改題

解説

(1)掛け算九九

複合参照は基本です。何回も練習しましょう。1行目とA列に数値が入力してあるのですから、「$1」「$A」と固定します。

f:id:waenavi:20190814120010j:plain

f:id:waenavi:20190814115630j:plain

 

(2)特定の列を基準とする

A列に数値が並んでいてそれを基準とする場合、「$A」と固定します。

f:id:waenavi:20190814115931j:plain

f:id:waenavi:20190814191036j:plain

 

6.ウェイトが%で示されている表

問題

次の表は、天気(晴れ、曇り、雨)の確率と商品ごとの転記別の売上予測額である。確率を踏まえた当日の売り上げ予想額(期待値)を求めなさい。

f:id:waenavi:20190814120607j:plain

 

出典

平成29春ITパスポート問91改題

解説

表の中に比重(ウェイト)を表す数値があって、合計が1(100%)になる場合はそれを掛けます。確率は比重を表すパーセンテージでもあるので、それぞれ比重を掛けて足したらよいです。これを期待値といいます。

f:id:waenavi:20190814120708j:plain

 

なお、確率は絶対参照で良いですが、複合参照の場合は行番号の固定です。オートフィルが縦方向のみの場合は、行番号を固定するかどうかを検討します。列番号は固定してもしなくてもよいです。

f:id:waenavi:20190814120816j:plain

f:id:waenavi:20190814121739j:plain

 

7.フラグによる加算

問題

2行目の数値は各政党の議席数である。「1」になっている政党の議席を足した場合の合計をF列に求めなさい。

f:id:waenavi:20190814121044j:plain

 

出典と参考記事

平成10春初級シスアド問30改題

解説

単純に足し算をするのではなく、1または0を掛けることによって選択的に加算することができます。この1または0をフラグといいます。Q党とR党を「1」にするとQ党とR党だけの合計になり、その他の政党は加算されません。「表に0または1がたくさんあったらフラグ!」と考えても差し支えありません。

f:id:waenavi:20190814121258j:plain

 

ウェイトの考え方で言えば、100%と0%を比重として掛けるのと同じです。2行目に数値が並んでいて、それを固定して掛け算をしているのですから「$2」とします。

f:id:waenavi:20190814121446j:plain

 

8.税抜・税込

問題

セルD2とE2に設定した2種類の税率で、商品AとBの税込価格を計算しなさい。

f:id:waenavi:20190814122126j:plain

 

出典と参考記事

平成30秋ITパスポート問80、平成22春ITパスポート問55改題

解説

比率で加算するときは(1+率)、減算するときは(1-率)をかけ、逆算する場合は割り算をします。

f:id:waenavi:20190814123019j:plain

 

税抜から税込へは(1+税率)をかけ、税込から税抜へは(1+税率)を割ります。絶対暗記です。

f:id:waenavi:20190814123141j:plain

f:id:waenavi:20190814123630j:plain

 

B列の税抜き価格と、2行目の税率を掛けるのですから「$B」「$2」の複合参照です。

f:id:waenavi:20190814123555j:plain

 

ちなみに、ITパスポート試験は、誤って$B4+(1+$D$2)と入力したらどうなるか?という問題だったのですが、税率を$D$2つまり5%で完全に固定しているのですから、すべて5%加算されるだけです。

f:id:waenavi:20190814123757j:plain

 

9.100としたときの指標

問題

1999年度売上を100としたときの各年度の売上指数を求めなさい。

f:id:waenavi:20190814123859j:plain

 

出典と参考記事

平成20春初級シスアド問24、平成18春初級シスアド問26、平成11春初級シスアド問37改題

解説

1999年を基準とすれば2007年は約2倍になっていますから、そのことを指数として表そうとしています。変化後を変化前で割る鉄則に従えば、2007年を1999年で割ることは明らかです。

f:id:waenavi:20190814124551j:plain

 

いっぱんに特定の基準を設定して比率を求める場合、その基準となる値で割ります。その基準を固定するので絶対参照で割ります。これは、1999年度売上を「1」とした時の指数です。

f:id:waenavi:20190814124702j:plain

 

100とする場合は100倍すればよいです。

f:id:waenavi:20190814124731j:plain

 

10.換算

問題

満点の点数が異なる3科目の試験を行い、4人が受験した。各試験の得点を100点満点に換算した数値を求めなさい。

f:id:waenavi:20190814125431j:plain

 

出典と参考記事

平成18春初級シスアド問26改題

解説

換算率は換算後を換算前で割ります。例えば、250点を100点にする場合の換算率は100/250です。それを元の得点(素点)に掛けます。このとき、満点については行固定の複合参照になります。

f:id:waenavi:20190814191524j:plain

 

11.伸び率で予測する

問題

6月の数値を基準として、過去2年間の伸び率(対6月比増加率)の平均を使って、今年の7月以降の数値を予測しようとしている。セルE4の数式を入力して、表を完成させなさい。

f:id:waenavi:20190814125632j:plain

 

出典と参考記事

平成10春初級シスアド問32改題

解説

ある時点を基準として、「同じように伸びるだろう」と考えて予測するには、伸び率(正確に言えば増加率)を掛けます。平均増加率を求めます。増加率は7月/6月です。本当は2年間の増加率はそれぞれ分母が違いますので、増加率を足したり平均を求めたりしてはいけませんが、今回は問題の指示通り足して2で割って平均とします。

f:id:waenavi:20190814125930j:plain

 

これに元の数と平均増加率を掛けることによって、将来の予測をすることができます。

f:id:waenavi:20190814130008j:plain

 

6月を基準としているので、D列を固定します。

f:id:waenavi:20190814130236j:plain

 

12.売上構成比と粗利率の計算

問題

次の表はあるラーメン屋の売り上げである。セルD6、E2~H2に数式を入力して表を完成させなさい。ただし、売上比率は売上構成比のことである。

f:id:waenavi:20190814130439j:plain

 

出典と参考記事

平成27秋ITパスポート問89・90改題。平成24秋ITパスポート問86でも同じ問題が出題されたが、メニューは牛丼、親子丼、カツ丼、カレーだった。

解説

粗利と売上構成比率の計算はノーヒントでできなければなりません!!!特に飲食店を営んでいる人で原価率や粗利率を知らない人はいません。しょう油ラーメンの原価350円はラーメンを作る時の材料費で、お客さんには分からない金額です。単価はメニューに書いてある値段です。

f:id:waenavi:20190814130801j:plain

 

売上高は単価*数量です。

f:id:waenavi:20190814131421j:plain

 

500円のしょう油ラーメンを作るのに350円の費用がかかるのですから、粗利は150円です。2通りの求め方があります。単価から原価を引いて数量を掛ける方法と、売上高から粗利を引く方法です。両方できるようにしておきましょう。

f:id:waenavi:20190814131550j:plain

f:id:waenavi:20190814131553j:plain

 

売上数量、売上高、粗利の合計を求めておきます。

f:id:waenavi:20190814131618j:plain

 

売上比率は各メニューの構成比率と考えられます。売上比率を求めてよく売れている商品を求めようとしています。醤油ラーメンの売上を合計で割ります。合計は絶対参照です。

f:id:waenavi:20190814131731j:plain

 

粗利率は利益の比率です。利益の比率が高いほうがいいです。売り上げの中の粗利の割合なので割り算です。

f:id:waenavi:20190814131833j:plain

 

売上比率と粗利率は6行目までオートフィルをします。

f:id:waenavi:20190814131835j:plain

 

セルD6で求めた合計を右にオートフィルをする場合は売上比率までです。売上比率は合計をもととする比率なので合計してもかまいません(分母が同じ)が、粗利率はそれぞれの売上高が分母なので分母が異なります。分母が異なるパーセンテージは合計してはいけません。

f:id:waenavi:20190814131943j:plain

 

また、単価や原価のように単位量当たりの数量は合計を求めてはいけません。

f:id:waenavi:20190814132116j:plain

 

ちなみに、単価と原価があれば、原価率(原価/単価)と粗利率(1から原価率を引く)を求めることができますが、単価や原価の合計を求めてはいけないので、合計の原価率や粗利率も求めてはいけません。各商品の売上数量が異なるので当然のことです。

f:id:waenavi:20190814132118j:plain

 

売上金額を求めてから、売上合計、売上原価合計または売上粗利合計を使って求めます。

f:id:waenavi:20190814132121j:plain

 

13.売上構成比率累計

問題

各商品の売上高、売上原価、平均在庫高が次の表のとおりであるとき、表を完成させなさい。

f:id:waenavi:20190814132342j:plain

 

出典と参考記事

平成21秋ITパスポート中問B問93改題

解説

原則として単価*数量=売上高、原価*数量=売上原価です。売り上げから原価を引けば利益になります。

f:id:waenavi:20190814132420j:plain

 

これらの合計を求めます。

f:id:waenavi:20190814132557j:plain

 

利益率は、売り上げの中の利益の割合なので、D列/B列です。この時の合計は合計売上原価/合計売上高であって、SUMではありません。

f:id:waenavi:20190814132657j:plain

 

累計は売上高の累計です。売上高の大きい商品から順に累計を求めるのが一般的です。

f:id:waenavi:20190814132802j:plain

 

構成比率は、各商品の売り上げを合計で割ります。

f:id:waenavi:20190814132851j:plain

 

その累計を求めます。

f:id:waenavi:20190814132923j:plain

 

別解

売上構成比率(G列)の累計と、売上累計(F列)の構成比率は同じです。売上累計の最後が合計なのでこれを利用して求めることもできます。

f:id:waenavi:20190814133027j:plain

 

14.按分

問題

来年の売上目標を今年の10%増とした。各月の目標金額を次の2つの方法で求めなさい。
(1)各月の売上を10%増にして合計する。
(2)年間合計を10%増にして、各月で按分する

f:id:waenavi:20190814133206j:plain

 

出典と参考記事

平成25春ITパスポート中問C問96改題

解説

通常は10%増にするのであれば、(1)のように各月の売り上げを1.1倍して合計すればよいです。

f:id:waenavi:20190814133330j:plain

 

ITパスポート試験で出題されたのは(2)の按分する方法です。まず、年間合計を10%増にします。

f:id:waenavi:20190814133429j:plain

 

按分は配分する合計に構成比率を掛けます。目標合計に構成比率(1月/今年の合計)を掛けます。合計は絶対参照ですが、縦向きにオートフィルをすることから、少なくとも「$14」の固定は必要です。

f:id:waenavi:20190814133624j:plain

 

15.累乗と複利計算

問題

元金1000円のとき複利を計算しようとしている。

(1)元金を含めていくらになるか求めなさい。
(2)利息だけを求めなさい。

f:id:waenavi:20190814133940j:plain

 

出典と参考記事

平成19秋初級シスアド問24、平成15春初級シスアド問23改題

解説

利率が0.02のとき、毎年1.02倍(=1+0.02)を繰り返して増やします。1.02倍を繰り返すので累乗になります。これが増加率になります。元金に増加率を掛けます。

f:id:waenavi:20190814134319j:plain

 

元金は絶対参照、年数のA列と利率の2行目の複合参照です。

f:id:waenavi:20190814134551j:plain

f:id:waenavi:20190814134631j:plain

 

利息だけにするには、増加率から1を引けばよいです。

f:id:waenavi:20190814134706j:plain

 

16.減価償却

問題

40万円で購入した備品の未償却残高と毎年の償却額を求めなさい。ただし、計算方法には定率法を用い、償却率は31.9%とし、小数点以下を考慮しなくてもよい。

f:id:waenavi:20190814134951j:plain

 

出典と参考記事

平成11春初級シスアド問38改題

解説

40万円を1年目で一気に費用計上すると赤字になってしまって大変なので、10年くらいかけて徐々に減らし、残高を0円にしていこうという計算です。これを減価償却といいます。償却率31.9%とは、簡単に言えば前年の残高の31.9%を減らすという意味です。最初の残高は40万円です。40万円のうち31.9%をひきます。$の固定は不要です。

f:id:waenavi:20190814135304j:plain

 

償却額は前年の残高の31.9%です。これも$の固定は不要です。

f:id:waenavi:20190814135339j:plain

 

別解

未償却は償却の残りですから、未償却残高は68.1%です。したがって前年の残高の68.1%をかけるだけでもよいです。難しく見えますが単なる等比数列です。

f:id:waenavi:20190814135422j:plain

 

17.変動費率と損益分岐点

問題

固定費1850千円、変動費率65%、売上高0~10000千円のときの総費用と利益を求めなさい。また、損益分岐点を求めなさい。

f:id:waenavi:20190814140013j:plain

 

出典と参考記事

平成27春ITパスポート中問B問93・問94改題

解説

費用には固定費と変動費がありますが、固定費は一定で、変動費は売り上げに比例して増えます。変動費率65%ということは売上の65%が変動費であるということです、変動費率と固定費は絶対参照ですが、下向きにオートフィルをするので少なくとも「$1」「$2」の固定は絶対に必要です。

f:id:waenavi:20190814192404j:plain

 

利益は売り上げから費用を引いた額です。

f:id:waenavi:20190814140427j:plain

 

売り上げの65%が変動費なので残りの35%で固定費を支払わなければなりません。支払えたら黒字、支払えなければ赤字です。残りの35%と固定費が一致したらプラスマイナスゼロになります。これが損益分岐点です。売り上げに35%を掛けたら固定費になりますから、逆算して、固定費を35%で割ればよいです。

f:id:waenavi:20190814140543j:plain

 


解説は以上です。

試験問題の著作権はIPA独立行政法人情報処理推進機構にあります。この解説は公式のものではありません。


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]