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

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

【MOS Excel検定対策】条件付き書式の設定とスパークラインの練習問題

MOS Excel一般レベルの出題範囲には「データをまとめる、整理する」という項目があります。内容は次の4つです。

  • スパークラインを挿入する
  • データのアウトラインを作成する
  • 小計を挿入する
  • 条件付き書式を適用する

Excelを仕事で使っていても、これら4つはあまり使うことが無いものばかりで、しかも分量が多いので、出題範囲の中でも厄介な存在です。

そこで、今回は、このうち「条件付き書式」「スパークライン」の2つについて解説し、アウトラインと小計については別の記事で解説したいと思います。

※なお、MOSの出題形式とは異なりますので、合格は保証しません。

 

 

目次

0.MOSの概要について

MOS Excelの概要については既に別の記事で解説しています(【MOS試験】本音で語るExcel一般レベル受験対策と合格後のこと)。また、MOSの対策テキストについてはこちらの記事をご覧ください。

 

1.条件付き書式その1

注意点

条件付き書式は、設定する条件と書式の多さ、設定した書式の管理の難しさから特に慎重に練習したほうが良い項目です。 

(1)セルの強調表示ルール

問題

B列の売上金額が36万円を超えているセルに明るい赤の背景の設定をし、数値が変更されたら書式が自動的に更新されるようにしなさい。

f:id:waenavi:20190921204921j:plain

 

解説

B2:B13を選択します。1行目の項目名を選択してはいけません。指示が無い限り14行目以降を選択してはいけません(試験対策)。

f:id:waenavi:20190921205007j:plain

 

条件付き書式、セルの強調表示ルールの中の、指定の値より大きいを選びます。

f:id:waenavi:20190921205059j:plain

 

「360000」「明るい赤の背景」とします。

f:id:waenavi:20190921205138j:plain

 

36万円の色がついていないことを確認します。36万より大きいという条件に36万円は含まれません。

f:id:waenavi:20190921205211j:plain

 

(2)選択したセルからルールをクリア

問題

B列の売上金額に設定されている条件付き書式をクリアしなさい。

f:id:waenavi:20190921205256j:plain

 

解説

B2:B13を選択します。

f:id:waenavi:20190921205401j:plain

 

条件付き書式、ルールのクリアの中から、選択したセルからルールをクリアを選びます。

f:id:waenavi:20190921205358j:plain

 

これで条件付き書式が消えます。

f:id:waenavi:20190921205007j:plain

 

(3)その他のルール

問題

B列の売上金額が36万円以上のセルに赤色の文字にしなさい。

f:id:waenavi:20190921204921j:plain

 

解説

B2:B13を選択します。

f:id:waenavi:20190921205007j:plain

 

条件付き書式、セルの強調表示ルールの中には「以上」という項目がありません。ない場合は「その他のルール」を選びます。

f:id:waenavi:20190921205659j:plain

 

「次の値以上」「360000」とします。順序は逆ですが、これで36万以上を表します。書式のボタンを押します。

f:id:waenavi:20190921205741j:plain

 

フォントの色を赤色にします。

f:id:waenavi:20190921205812j:plain

 

今度は、36万円も赤色になります。36万円以上の条件には36万円も含まれます。

f:id:waenavi:20190921205844j:plain

 

(4)平均値以下

問題

C列の注文件数が平均値以下のセルを薄い緑の塗りつぶしにしなさい。

f:id:waenavi:20190921210520j:plain

 

解説

C2:C13を選択します。

f:id:waenavi:20190921210635j:plain

 

条件付き書式、上位/下位ルールの中には「平均値以下」という項目がありません。ない場合は「その他のルール」を選びます。

f:id:waenavi:20190921210637j:plain

 

「平均より上または下の値だけを書式設定」を選びます。

f:id:waenavi:20190921210731j:plain

 

選択範囲の平均値「以下」にします。書式のボタンを押します。

f:id:waenavi:20190921210818j:plain

 

塗りつぶしを薄い緑にします。

f:id:waenavi:20190921210855j:plain

 

(5)ルールの編集

問題

C列に設定されている書式の条件を「平均値より小さい」に変更しなさい。

f:id:waenavi:20190921210933j:plain

 

解説

1つの条件付き書式を設定した後で、もう一度同じ範囲で条件付き書式を設定すると、同じ範囲に対して条件付き書式が「2つ」になります。その前に設定した条件付き書式が消えるわけではなく、修正できるわけでもありません。一度設定した条件付き書式の条件を変更するときは、「ルールの編集」をしなければなりません。

C2:C13を選択します。

f:id:waenavi:20190921211018j:plain

 

条件付き書式、ルールの管理を選びます。

f:id:waenavi:20190921211039j:plain

 

条件付き書式が選択範囲内に1つ設定されていることが分かります。これを選択したうえで、ルールの編集をします。

f:id:waenavi:20190921211126j:plain

 

「より下」に修正します。

f:id:waenavi:20190921211157j:plain

 

(6)下位3項目

問題

D列の客単価の下位3項目を薄い青色の文字にしなさい。

f:id:waenavi:20190921210933j:plain

 

解説

D2:D13を選択します。

f:id:waenavi:20190921211251j:plain

 

条件付き書式、上位/下位ルールの中には「下位10項目」があります。この10%や10項目は例示です。例えとして「10%」「10項目」と書いてあるだけで、10%や10項目でなくてもよいです。したがって、「下位10項目」を選びます。

f:id:waenavi:20190921212840j:plain

 

ここで「下位3項目」に変更することが可能です。「薄い青色の文字」が無いので、ユーザー設定の書式(「その他」という意味です)を選びます。

f:id:waenavi:20190921213206j:plain

 

フォントの色を薄い青色にします。

f:id:waenavi:20190921213316j:plain

 

(7)シート全体からルールをクリア

問題

このシートに設定されている条件付き書式をクリアしなさい。

f:id:waenavi:20190921213429j:plain

 

解説

最後に、条件付き書式、ルールのクリアの中から、「シート全体からルールをクリア」を選びます。

f:id:waenavi:20190921213607j:plain

 

これで条件付き書式がすべて消えます。

f:id:waenavi:20190921204921j:plain

 

2.条件付き書式その2

注意点

条件付き書式の難しいポイントは、問題文を読んで「条件付き書式」であることが分かりにくいことにあります。「数値が変更されたら書式が自動的に更新されるようにしなさい」と書いてあれば、明らかに条件付き書式であることが分かりますが、「カラースケールを設定しなさい」と言われても難しいのではないかと思われます(練習して覚えるしかありませんが・・・)。

(1)データバー

問題

B列の売上金額に、最小値30万円の緑色グラデーションデータバーを設定しなさい。

f:id:waenavi:20190921204921j:plain

 

解説

B2:B13を選択します。

f:id:waenavi:20190921205007j:plain

 

条件付き書式、データバー、塗りつぶし(グラデーション)のなかから緑のデータバーを選びます。データバーにはグラデーションと単色がありますので注意します。

f:id:waenavi:20190921214030j:plain

 

これで設定が完了してしまいます。

f:id:waenavi:20190921214121j:plain

 

「最小値30万」を設定しないといけないのですが、すでに条件付き書式を設定したので、ルールの編集をします。条件付き書式、ルールの管理を選びます。

f:id:waenavi:20190921214154j:plain 

 

選択範囲内に条件付き書式が1つ設定されていることが分かります。これを選択したうえで、ルールの編集をします。

f:id:waenavi:20190921214235j:plain

 

最小値を「数値」「300000」にします。

f:id:waenavi:20190921214311j:plain

 

(2)カラースケール

問題

C列の注文件数に、中間値50%の青、白、赤のカラースケールを設定しなさい。

f:id:waenavi:20190921214349j:plain

 

解説

C2:C13を選択します。

f:id:waenavi:20190921214454j:plain

 

条件付き書式、カラースケールのなかから「青、白、赤」のカラースケールを選びます。色の順序が問題文の文言と一致していることを確認します。

f:id:waenavi:20190921214435j:plain

 

これも設定が完了してしまいます。

f:id:waenavi:20190921214523j:plain

 

条件付き書式、ルールの管理を選びます。

f:id:waenavi:20190921214154j:plain

 

ルールの編集をします。

f:id:waenavi:20190921214602j:plain

 

中間値を「パーセント」「50」にします。中間値50%とは最大値と最小値の平均(足して2で割った数)ということです。ちなみに、百分位50は中央値(真ん中の順位)のことです。

f:id:waenavi:20190921214648j:plain

 

(3)アイコンセット

問題

D列の客単価に、80%以上を緑、20%以上を黄色とする3つの記号(丸囲みなし)を設定しなさい。

f:id:waenavi:20190921214939j:plain

 

解説

D2:D13を選択します。

f:id:waenavi:20190921221831j:plain

 

条件付き書式、アイコンセットのなかから「3つの記号(丸囲みなし)」を選びます。信号と記号があるので注意します。

f:id:waenavi:20190921221834j:plain

 

条件付き書式、ルールの管理を選びます。

f:id:waenavi:20190921221901j:plain

 

ルールの編集をします。

f:id:waenavi:20190921221940j:plain

 

緑を「>=」「80」「パーセント」、黄色を「>=」「20」「パーセント」にします。

f:id:waenavi:20190921222015j:plain

 

(4)パーセントだけどパーセントじゃない

問題

セル範囲E3:E13に、100%より大きい場合を緑、80%より大きい場合を黄色とする3つの記号(丸囲みなし)を設定しなさい。

f:id:waenavi:20190921222130j:plain

 

解説

E2:E13を選択します。

f:id:waenavi:20190921222105j:plain

 

条件付き書式、アイコンセットのなかから「3つの記号(丸囲みなし)」を選びます。

f:id:waenavi:20190921221834j:plain

 

条件付き書式、ルールの管理を選びます。ルールの編集をします。

f:id:waenavi:20190921222226j:plain

 

緑を「>」「1」「数値」、黄色を「>」「0.8」「数値」にします。

f:id:waenavi:20190921222330j:plain

 

D列のように数値で入力されている場合、条件の種類は「パーセント」です。80パーセントとは、(最大値*80%+最小値*20%)で計算された値のことです。ちなみに、上位80%にしたかったら「80百分位」にします。

これに対して、E列の前月比はパーセントで表されているデータであり、この場合のパーセンテージとは、純粋にそのセルに入力されているデータ(%)でアイコンの色を決定するので「数値」になります。最大値や最小値は無関係です。

f:id:waenavi:20190921222405j:plain

 

3.スパークライン

注意点

条件付き書式と混同するのがスパークラインです。スパークラインには縦棒、折れ線、勝敗の3種類があります。条件付き書式はそのセルに対して設定するのに対して、スパークラインは時系列のある複数のセルを用いて、別の列にグラフを作成する機能です。
条件付き書式は「ホーム」タブにあり、スパークラインは「挿入」タブにあります。

(1)縦棒スパークラインの挿入

問題

F列に、推移を表す縦棒スパークラインを作成し、データの最大値を強調表示しなさい。

f:id:waenavi:20190921222505j:plain

 

解説

A2:D4を選択します。スパークラインは時系列の推移や大小関係を表しますので複数の列を選択します。また、平均値は無関係なので選択してはいけません

f:id:waenavi:20190921222544j:plain

 

挿入タブ、スパークラインの縦棒をクリックします。

f:id:waenavi:20190921222616j:plain

 

データ範囲はグラフの元となるデータのことでA2:D4です。場所の範囲はスパークラインを表示する場所のことでF2:F4です。

f:id:waenavi:20190921223257j:plain

f:id:waenavi:20190921223345j:plain

 

縦棒スパークラインが挿入されます。スパークラインを選択するとスパークラインツールが表示されます。

f:id:waenavi:20190921223427j:plain

 

頂点(山)にチェックを入れると、最大値の棒だけ色が変わります。

f:id:waenavi:20190921223458j:plain

 

(2)スパークラインの種類の変更

問題

F列の縦棒スパークラインを、勝敗スパークラインに変換しなさい。 

解説

F2:F4を選択します。

f:id:waenavi:20190921223547j:plain

 

勝敗のボタンを押すと、勝敗スパークラインに変換されます。

f:id:waenavi:20190921223619j:plain

 

(3)スパークラインの削除

問題

F列のスパークラインを削除しなさい。 

解説

F2:F4を選択します。

f:id:waenavi:20190921223648j:plain

 

クリアの中の、選択したスパークライングループのクリアを選びます。これでスパークラインが消えます。

f:id:waenavi:20190921223714j:plain

 

(4)折れ線スパークライン

問題

F列に、推移を表すマーカー付き折れ線スパークラインを作成しなさい。また、スパークラインの色を赤色にしなさい。

f:id:waenavi:20190921222505j:plain

 

解説

A2:D4を選択します。

f:id:waenavi:20190921222544j:plain

 

挿入タブ、スパークラインの折れ線をクリックします。

f:id:waenavi:20190921223839j:plain

 

データ範囲はA2:D4、場所の範囲はF2:F4です。

f:id:waenavi:20190921223913j:plain

 

折れ線スパークラインが挿入されます。

f:id:waenavi:20190921224015j:plain

 

マーカーにチェックを入れるとマーカーが付きます。

f:id:waenavi:20190921224017j:plain

 

スパークラインの色を赤色にします。折れ線の色とマーカーの色はそれぞれ設定することができます。

f:id:waenavi:20190921224128j:plain

 

(5)最大値と最小値

問題

スパークラインの縦軸の最小値を20万にしなさい。

f:id:waenavi:20190921224200j:plain

 

解説

軸の、縦軸の最小値のオプションの中から、ユーザー設定値を選びます。

f:id:waenavi:20190921224242j:plain

 

「200000」にします。

f:id:waenavi:20190921224318j:plain

f:id:waenavi:20190921224342j:plain

 

8.さいごに

MOSの模擬問題など、詳しくは、FOM出版「よくわかるマスターMOS Excel 2016 対策テキスト&問題集」第2章129~134ページ、139~149ページをご覧ください。

 


解説は以上です。


 

 


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