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

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】丸め誤差、循環参照、並べ替え、グラフ、マクロなど表計算過去問(ITパスポート表計算Part4)

これまで国家試験のITパスポート試験とその前身の初級シスアド午前試験の過去問をまとめてきました。ITパスポート試験や基本情報技術者試験を受ける人はもちろんのこと、Excelを使って仕事をしているすべての社会人が当然に知っていなければならない基本知識です。

 

Excelをはじめとする表計算ソフトと言えば計算して分析するのがメインですから、これらの基本的な計算はしっかりと理解しておかなければなりません。しかし、計算以外にもデータベース機能やグラフ作成機能もあります。

そこで、今回は、計算式や関数以外の過去問に挑戦してみましょう。

目次

0.ITパスポート関連書籍について

ITパスポート試験は入門レベルの情報処理の国家試験で、たくさんの対策テキストや問題集が販売されています。試験を受けなくてもテキストを読むだけで情報処理の基本が身に付きます。

1.表示形式と丸め誤差

問題

次の表で構成比を計算しなさい。

f:id:waenavi:20190815190832j:plain

 

出典と参考記事

平成8年初級シスアド問28改題

解説

構成比率は売り上げを合計で割ります。

f:id:waenavi:20190815190910j:plain

 

ところで、38+11+17+24+11=101です。これは小数点以下を四捨五入したことによって起こる丸め誤差です。Excelで小数点以下四捨五入の表示形式を設定しても、値としては小数部分も持っているため、誤差が発生します。

f:id:waenavi:20190815191607j:plain

 

誤差が発生しているものを足しても、合計は100%のままです。

f:id:waenavi:20190815191739j:plain

 

Excelを使っても使わなくても四捨五入による誤差は発生するので、むやみに計算式を変えたり、むりやり誤差を調整しようとしてはいけません。また、四捨五入のルールを勝手に変えてはいけません。この場合は、数式をそのままにして「四捨五入によって合計値が100にならないことがあります」などの但し書きを書くのが一般的です。

f:id:waenavi:20190815191924j:plain

 

また、小数部分を表示することによって、100%になる場合は表示します。

f:id:waenavi:20190815191952j:plain

 

2.絶対値

問題

「56、36、20、16、・・・」は1つ前の数と2つ前の数を比較して、大きい数から小さい数を引く計算を繰り返してできる数列である。これをExcelで計算して、はじめて0が表示されるのは第何項か確認しなさい。また、これを続けるとどのような数列になるか述べなさい。

出典と参考記事

平成9年初級シスアド問43改題

解説

大きいほうから小さいほうを引くのは絶対値を使います。数列{an}の漸化式で表すと次のようになります。

  • a1=56、a2=36、an+2=絶対値(an+1-an) (n>=1)

Excelでは、絶対値の関数ABSを使います。

f:id:waenavi:20190815190228j:plain

 

オートフィルをすると第10項で0になることが分かります。

f:id:waenavi:20190815190312j:plain

 

さらに続けると4,4,0の繰り返しになります。このようにExcelでは繰り返しの計算をすることができます。

f:id:waenavi:20190815190456j:plain

 

3.循環参照

問題

粗利を1000円とするための原価と販売価格を求めようとしている。販売価格は原価+粗利なので「=B2+C2」とし、原価は販売価格-粗利なので「=A2-B2」と入力した。正しく計算できない理由を述べなさい。

f:id:waenavi:20190815192047j:plain

 

出典と参考記事

平成11年秋初級シスアド問39改題

解説

自分自身を参照しているために、自分自身の数式を計算できない状況を循環参照といいます。販売価格を求めるには原価が必要ですが、原価は販売価格によって決定するので、結局のところ、販売価格が販売価格を参照していることになります。複数のセルが互いの値を参照している場合、循環参照となり計算結果が出ません。

f:id:waenavi:20190815192230j:plain

 

4.並べ替え

問題

営業部では表計算ソフトで受注情報を管理している。図は、ある部員が表の並べ替え作業を行った際の、整列前と整列後の表である。どのように並べ替えをしたか。ただし、整列はすべて昇順とする。

f:id:waenavi:20190815192405j:plain

 

出典と参考記事

平成13年春初級シスアド問25改題

解説

4列のうち昇順になっている列を探します。担当者の列はアルファベットの昇順になっています。

f:id:waenavi:20190815192824j:plain

 

次に、担当者Aの4行のなかで昇順になっている列を探します。顧客の50音順になっています。

f:id:waenavi:20190815192828j:plain

 

さらに、担当者A、顧客あのなかで昇順になっているのは日付です。

f:id:waenavi:20190815192830j:plain

 

したがって、最優先キー:担当者、第2優先キー:顧客、第3優先キー:日付です。

f:id:waenavi:20190815192834j:plain

 

5.グラフの数値軸

問題

表計算ソフトのグラフ表示機能を使って、表に示す売上実績を基に12か月の売上実績推移を表す折れ線グラフを作成したところ、縦軸の目盛りが自動的に0~1,500,000円の範囲で表示された。ところが売上の変動があまり強調されていないので、グラフの枠の大きさは変えずに変動をもっと強調して表示したい。グラフ編集機能を使って編集する場合、最も適切なものはどれか。

  1. 縦軸の単位を千円にして0~1,500(千円)にする。
  2. 縦軸の表示範囲を1,000,000~1,300,000にする。
  3. 表示するグラフを棒グラフに変換する。
  4. 横軸と平行に平均売上実績の補助線を入れる。

f:id:waenavi:20190815193043j:plain

 

出典と参考記事

平成19年秋初級シスアド問44

解説

折れ線グラフをかいて縦軸の目盛りを0~1500000にします。あまり変動が無いように見えます。

f:id:waenavi:20190815193614j:plain

 

縦軸の単位を変えるには表示単位を「千」にします。ゼロが3つ消えるだけです。

f:id:waenavi:20190815193701j:plain

 

縦軸の表示範囲を変えるには最小値と最大値を設定します。変動を強調することができます。

f:id:waenavi:20190815193818j:plain

 

グラフの種類を変更すると縦棒グラフにすることができます。図形が変わるだけです。

f:id:waenavi:20190815194112j:plain

 

最後に、絶対参照で平均を求めます。

f:id:waenavi:20190815194230j:plain

 

グラフの範囲を広げます。変動が無いことが強調されてしまいます

f:id:waenavi:20190815194324j:plain

 

6.マクロ(並べ替え)

問題

商品管理台帳のデータを在庫金額の大きいものから順に整列しようとした。このとき、操作を誤ったため、見出し行が一番下になり、在庫金額の大きいものから順にならなかった。

f:id:waenavi:20190815194459j:plain

 

この誤った整列処理は、マクロの自動記録で次のように記録された。マクロを修正しなさい。

  • 選択セル(A1~E100)
  • 選択セルの整列(整列キー:C列、順序:昇順)

出典

平成17年秋初級シスアド問25改題

解説

在庫金額の大きいものから順ということは、整列キー:E列、順序:降順です。

選択セル(A1~E100)にしたことによって見出し行が一番下になってしまったのであれば、先頭行を見出し行とみなされていない可能性があります。選択範囲をA2~E100にすればよいです。

  • 選択セル(A2~E100)
  • 選択セルの整列(整列キー:E列、順序:降順)

別解

Excelのマクロ(VBA)には先頭行を見出し行とみなすかどうかのプロパティ(.Header)があります。「.Header = xlYes」とすれば、先頭行が見出し行とみなされるので、選択範囲を変える必要はありません。

 

7.マクロ(ループ)

問題

ワークシート中のある列において、数値が100未満であるセルの文字色を赤に変更したい。この処理を行うために、マクロ「red」を作成した。このマクロでは「繰り返し条件」を満たしている間、「繰返し範囲の開始」と「繰返し範囲終了」で囲まれた処理を繰り返す。ワークシート中のデータ件数が明確に決まっていないとき、このマクロの「繰り返し条件」はどのように指定したらよいか。ここで、このワークシートのデータは、先頭行からの連続した行に何らかの文字色で数値が格納されているものとし、データが格納されていないセルの値は、空値とする。

(マクロredの記述)

  • マクロred()
  • 繰返し範囲の開始「繰り返し条件」間は繰り返す
  • セルの値が100未満なら、セルの文字色を赤にする
  • 対象セルを1行下に移動させる
  • 繰返し範囲の終了

出典と参考記事

平成14年春初級シスアド問24

解説

空白になるまで繰り返すというのはマクロでよく登場する処理です。そして、途中に空白があったら処理が途中で止まってしまうという問題もよくあることです。この問題は先頭行から連続してデータが入力されているということなので、「対象セルの値が空値でない」間繰り返せばよいです。

対象セルをSelectionとして、マクロにすると次のようになります。

Sub red()
With Selection
Do While .Value <> ""
If .Value < 100 Then .Font.ColorIndex = 3
.Offset(1, 0).Select
Loop
End With
End Sub

 


解説は以上です。

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


 

 


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