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

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

MOS Excel2016で初心者が理解しにくい、難易度の高い問題例と学習上の注意点

MOS Excelの一般レベル(スペシャリストレベル)は一般の民間団体が主催するExcel検定の階級でいえば、2~3級のレベルに相当します。初心者にとっては、3級レベルの初歩的なものと、難易度が比較的高いものが混ざっているので、特に、出題範囲の前半部分はとっつきにくい内容となっています。

表の入力と計算をして表を完成させる作業と、大量のデータを分析する作業は分けて勉強したほうが良いです。

そこで、今回はMOS Excel2016の一般レベルの出題範囲の中で、2級レベルに相当する機能について、練習問題を出題します。

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

 

 

目次

0.MOSの概要について

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

 

1.小計を挿入する

注意点

MOSの出題範囲の中に「小計を挿入する」という項目があります(出題範囲2)。これを初心者が習得するのはかなりハードルが高いと思われます。リストの作成方法、並べ替え、集計行の挿入と置き換え、追加、削除、アウトラインレベルの操作方法など、それぞれの操作に分けて練習したほうが良いです。

※小計は、MOS Excel2019では上級レベルへ移行される予定です。

(1)並べ替え

問題

次の表についてD列の降順に並べ替えなさい。また、元の順序に戻しなさい。

f:id:waenavi:20190922085548j:plain

※準備データの作成方法(値の貼り付けで数式を消しておく)

  • D2:=INT(E2*(RAND()/2+0.5))
  • E2:=RANDBETWEEN(20000,60000)

参考記事

解説
Excelはセルに入力したデータを「リスト」として認識するには、リスト形式で入力しなければなりません(参考:【神Excel】8個の基本パターンで完全習得「リスト形式」の教科書)。リスト形式でない表は、データを羅列しただけの帳票です。

リスト形式の表は1行目に項目名があって、下に空白行、右に空白列が必要です。途中に空白行・空白列があってはいけません。

f:id:waenavi:20190922132054j:plain

 

セルD1を選択します。

f:id:waenavi:20190922132310j:plain

 

並べ替えとフィルタの中の降順のボタンを押すと降順になります。

f:id:waenavi:20190922132415j:plain

f:id:waenavi:20190922132736j:plain

 

しかし、よく考えてみると、セルD1を選択して並べ替えの指示をしてもセルが1個しかないのだから並べ替えができるわけがありません。

f:id:waenavi:20190922132807j:plain

 

セルD1を選択しただけなのに降順になるのは、Excelが表の構造を認識して「セル範囲A1:E17がリストである」と判断しているからです。リスト形式で正しく表を作れば、正しく並べ替えができます。

f:id:waenavi:20190922132836j:plain

 

セルA1を選択します。昇順で並べ替えると元の順序に戻ります。

f:id:waenavi:20190922132940j:plain

f:id:waenavi:20190922132943j:plain

 

このようにリストには並べ替えを元に戻すための連番を入力しておくのが基本です。

f:id:waenavi:20190922133024j:plain

 

(2)小計の挿入と集計行の置き換え

問題

グループ(昇順)ごとにD列を合計する集計行を挿入しなさい。また、集計するフィールドをE列に変更しなさい。

f:id:waenavi:20190922085548j:plain

 

参考記事

解説

Excelには分類やカテゴリーごとに集計する「小計」という機能があります。リストの途中に小計を入れる機能です。ということは、リストをグループごとに並べ替えておく必要があります。

f:id:waenavi:20190922134303j:plain

 

セルC1を選択して、昇順で並べ替えます。

f:id:waenavi:20190922133246j:plain

 

問題文には「集計行」と書いてありますが、「グループごと」なので小計のことです。データタブの小計のボタンを押します。

f:id:waenavi:20190922133342j:plain

 

グループの基準は「グループ」です。並べ替えをした時の列のことです。集計方法は合計です。集計フィールドはD列(中間集計)です。

f:id:waenavi:20190922133431j:plain

 

これで集計行が追加されます。

f:id:waenavi:20190922133501j:plain

 

もう一度、小計のボタンを押します。

f:id:waenavi:20190922133342j:plain

 

D列のチェックを外して、E列にします。ここで、「現在の小計をすべて置き換える」にチェックが入っていることを確認します。

f:id:waenavi:20190922133547j:plain

 

これによって、集計方法を変更することができます。

f:id:waenavi:20190922133613j:plain

 

(3)アウトラインレベルの操作

問題

各メンバーの詳細データをすべて非表示にし、グループBの詳細データだけ再表示しなさい。また、すべての詳細データを再表示しなさい。

f:id:waenavi:20190922133613j:plain

 

解説

集計行を追加するとアウトラインが設定されます。アウトラインレベルのボタンがあります。「1」のボタンは一番最後の総計だけを表示するボタンです。「2」のボタンは集計行を表示するもので、詳細データの非表示となります。

f:id:waenavi:20190922134339j:plain

 

「2」のボタンを押します。

f:id:waenavi:20190922134432j:plain

 

Kの集計行にあるプラスのボタン(+)を押すと、そのグループだけ詳細データを表示することができます。

f:id:waenavi:20190922134502j:plain

 

「3」のボタンですべての詳細データの再表示となります。

f:id:waenavi:20190922134530j:plain

 

(4)集計行の追加と削除

問題

さらに、D列・E列の平均を追加して、グループごとに改ページを挿入しなさい。また、集計行をすべて削除しなさい。

f:id:waenavi:20190922133613j:plain

 

解説

もう一度、小計のボタンを押します。

f:id:waenavi:20190922133342j:plain

 

集計の方法を平均にします。また、D列・E列にチェックを入れます。ここで、「現在の小計をすべて置き換える」のチェックを外します。さらに、「グループごとに改ページを挿入する」にチェックを入れます。

f:id:waenavi:20190922133807j:plain

 

これで、集計行の追加となります。グループごとに改ページが入ります。

f:id:waenavi:20190922133841j:plain

 

(5)集計行の削除

問題

集計行をすべて削除しなさい。

解説

念のため、最後の集計行(総計)も含めてすべて選択しておきます。

f:id:waenavi:20190922134608j:plain

 

小計のボタンを押します。

f:id:waenavi:20190922133342j:plain

 

すべて削除のボタンを押します。

f:id:waenavi:20190922134635j:plain

 

すべて削除とは、集計行の削除のことです。アウトラインもなくなります。

f:id:waenavi:20190922133246j:plain

 

2.テーブルの集計行の追加

問題

集計行を追加して、D列・E列の合計を表示しなさい。

f:id:waenavi:20190922134836j:plain

 

注意点

まず、集計行追加の問題はその対象がセル範囲なのか、テーブルなのかを考えなければなりません。セル範囲の場合は「小計」ですが、テーブルの場合は最後に集計行を表示する「オプション」です。

解説

この表はテーブルの設定になっています。集計行にチェックを入れると一番下に集計行が表示されます。

f:id:waenavi:20190922134940j:plain

 

D列・E列に合計を表示します。

f:id:waenavi:20190922135009j:plain

 

3.アウトラインの自動作成

問題

アウトラインを自動作成して、メンバーの詳細データを非表示にしなさい。また、作成されたアウトラインを削除しなさい。

f:id:waenavi:20190922135116j:plain

 

注意点

アウトラインは小計だけでなく自動作成することもできます。自動作成できるのは、Excelが表の構造を認識して「セル範囲A1:E17が小計のあるリストである」と判断しているからです。自動作成するためには、集計行に数式が入力されている場合に限られます。 

※アウトラインは、MOS Excel2019では上級レベルへ移行される予定です。

解説

データタブ、グループ化の中のアウトラインの自動作成を選びます。自動作成なので、表の範囲選択は不要です。

f:id:waenavi:20190922135213j:plain

f:id:waenavi:20190922135305j:plain

 

アウトラインレベル2で詳細データの非表示となります。

f:id:waenavi:20190922135324j:plain

 

データタブ、グループ解除の中の「アウトラインのクリア」を選びます。

f:id:waenavi:20190922135415j:plain

 

これでアウトラインの削除になります。

f:id:waenavi:20190922135116j:plain

 

4.テキストファイルのインポート

注意点

Excel 2016の出題範囲には「区切り文字で区切られたデータをテキストファイルからインポートする」というのがあります。しかも、出題範囲の最初のほうに書いてあるので何のことかさっぱり分からないのではないかと思われます。Excelが多少使えるようになってから学習すべき項目です。

まず、テキストファイルとは何かについて理解しなければなりません(参考:【ExcelとCSV】本気で理解したい初心者のためのCSV勉強会資料)。

(1)CSV

問題

1枚目のシートのセルB2に、CSVファイル「waenavi」をインポートしなさい。

f:id:waenavi:20190922135827j:plain

 

参考記事

解説

集計や計算するソフトはExcel以外にもたくさんあります。表計算の元となるデータをExcelファイルで配布してしまうと他のソフトでは使えません。そこで、テキストファイル(文字列だけのファイル)で配布することがあります。

そのうちの一つがCSVファイルです。CSVはカンマを入れながら入力されたファイルであり、Excelではこのカンマが列(セル)を表します。この配布されたデータをExcelで使えるようにする作業が「インポート」です。

f:id:waenavi:20190922141207j:plain

 

セルB2を選択します。

f:id:waenavi:20190922140229j:plain

 

データタブの外部データの取り込み、テキストファイルを選びます。

f:id:waenavi:20190922140326j:plain

 

CSVファイルを選択します。

f:id:waenavi:20190922140442j:plain

 

テキストファイルウィザード第1画面が表示されます。「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」が選択されていることを確認して、次に進みます。

f:id:waenavi:20190922140739j:plain

 

第2画面ではカンマにチェックを入れます。

f:id:waenavi:20190922140759j:plain

 

第3画面では、通常は文字列にしますが、問題文に指示が無いので標準のままにします。

f:id:waenavi:20190922140814j:plain

 

完了をして、既存のワークシート、セルB2になっていることを確認します。

f:id:waenavi:20190922140840j:plain

 

Excelでデータが使えるようになります。このように、テキストファイルの内容を読んで、Excelで使える状態にする作業のことを「インポート」または取り込みといいます。

f:id:waenavi:20190922140906j:plain

 

(2)TSV

問題

2枚目のシートのセルB2に、タブ区切りのテキストファイル「waenavi2」をインポートしなさい。

f:id:waenavi:20190922135827j:plain

 

参考記事

解説

最近はカンマ区切りだけでなく、タブで区切られたファイルを用いることもあります。操作方法はCSVの場合と同じです。

f:id:waenavi:20190922141209j:plain

 

ただし、1つのシートに2つのファイルを取り込むことはできませんので、シートを変える必要があります。セルB2を選択します。

f:id:waenavi:20190922140229j:plain

 

データタブの外部データの取り込み、テキストファイルを選びます。

f:id:waenavi:20190922140326j:plain

 

テキストファイルを選択します。

f:id:waenavi:20190922140444j:plain

 

テキストファイルウィザード第1画面が表示されます。「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」が選択されていることを確認して、次に進みます。

f:id:waenavi:20190922140739j:plain

 

第2画面ではタブにチェックを入れます。問題文の指示が無い限り、タブ以外のチェックの状態を変えてはいけません。

f:id:waenavi:20190922141002j:plain

 

第3画面で完了をして、既存のワークシート、セルB2になっていることを確認します。

f:id:waenavi:20190922140840j:plain

 

Excelでデータが使えるようになります。

f:id:waenavi:20190922140906j:plain

 

5.検索と置換

注意点

検索や置換の画面はオプションの部分が閉じられているので操作を忘れることがあります。問題文に指示が無ければオプションを開いてはいけませんが、指示があればオプションを表示しなければなりません。オプションで何が設定できるのかある程度覚えておかなければなりません。

(1)検索

問題

ブックから「メンバー」を含むセルをすべて検索しなさい。また、シート内で「メンバー」を含むセルをすべて検索しなさい。

f:id:waenavi:20190922141315j:plain

 

参考記事

解説

ホームタブ、検索と選択の中の、検索を選びます。

f:id:waenavi:20190922141518j:plain

 

「メンバー」と入力して、オプションを開きます。

f:id:waenavi:20190922141557j:plain

 

検索場所を「ブック」にします。この操作を忘れてはいけません。

f:id:waenavi:20190922141641j:plain

 

すべて検索をします。この一覧をクリックすることで該当のセルにジャンプすることができます。

f:id:waenavi:20190922141706j:plain

 

次に、検索場所をシートにしてすべて検索します。

f:id:waenavi:20190922141734j:plain

 

(2)置換

問題

シート内で、セルの内容が「A」と完全に一致しているものを「S」にすべて置換しなさい。

f:id:waenavi:20190922141315j:plain

 

参考記事

解説

置換をすると数式の中の文字列も置換され、計算結果が変わることがあります。特に、アルファベットや数値を置換するときには注意が必要です。ホームタブ、検索と選択の中の、置換を選びます。

f:id:waenavi:20190922141814j:plain

 

検索する文字列「A」、置換する文字列「S」にします。

f:id:waenavi:20190922141840j:plain

 

オプションを開いて、検索場所がシートであることを確認します。ここで、「セル内容が完全に同一であるものを検索する」にチェックを入れます。検索(置換)は検索する文字列を含むセルを検索しますが、これにチェックを入れることで完全一致のセルだけを探します。

f:id:waenavi:20190922141928j:plain

 

すべて置換をします。

f:id:waenavi:20190922141958j:plain

 

6.名前付きのセル

注意点

MOSの出題範囲は、名前付きのセルへの移動、つまり、すでに名前がついているセルへ移動する操作だけです。名前を付ける操作は出題されませんが、「名前を付ける」ことの意味を知らないのに移動だけ覚えても仕方がないことです。「名前付きのセルって何?」というところから練習をすべきです。

(1)名前の定義

問題

セルB20に名前「小計K」を定義しなさい。また、A1:E21に名前「集計表」を定義しなさい。

f:id:waenavi:20190922142101j:plain

 

解説

セルB20を選択します。

f:id:waenavi:20190922142129j:plain

 

名前ボックスに「小計K」と入力してEnterキーを押すと、名前の定義となります。

f:id:waenavi:20190922142234j:plain

 

名前ボックスには「小計K」が登録されています。

f:id:waenavi:20190922142252j:plain

 

A1:E21を選択します。

f:id:waenavi:20190922142400j:plain

 

名前ボックスに「集計表」と入力してEnterキーを押します。

f:id:waenavi:20190922142403j:plain

 

名前ボックスには「集計表」と「小計K」が登録されています。

f:id:waenavi:20190922142406j:plain

 

(2)名前付きセルへ移動する

問題

名前ボックスで「小計K」へ移動しなさい。また、ジャンプを使って「集計表」を範囲選択しなさい。

解説

MOSではすでに名前が付いているセルがある前提で出題されます。試験では、あらためて定義する必要はありません。名前ボックスの中から「小計K」を選ぶと、セルB20へ移動します。

f:id:waenavi:20190922142520j:plain

 

このように、セルB20に別名(ブックマークのようなもの)をつけることによって、すぐに移動することができるようになります。

f:id:waenavi:20190922142552j:plain

 

名前ボックスで「集計表」を選択することもできますが、ジャンプを使う方法もあります。ホームタブ、検索と選択のジャンプを選びます。

f:id:waenavi:20190922142653j:plain

 

集計表を選びます。

f:id:waenavi:20190922142657j:plain

 

これで範囲選択になります。

f:id:waenavi:20190922142659j:plain

 

(3)名前の削除

問題

名前「小計K」を削除しなさい。

解説

間違えて名前を登録した場合など、名前の定義を取り消したいときは数式タブの「名前の管理」を開きます。

f:id:waenavi:20190922142719j:plain

 

「小計K」を選択して、削除のボタンを押します。

f:id:waenavi:20190922142812j:plain

f:id:waenavi:20190922142933j:plain

 

これで名前ボックスからも「小計K」が消えます。

f:id:waenavi:20190922143010j:plain

 

7.その他

(1)改ページ

問題

グループBとグループKの間に改ページを挿入しなさい。

解説

ウィンドウの固定や分割と同じように、設定される境界線は選択した行の上、列の左です(参考:MOS Excelスペシャリスト練習問題「ワークシートやブックの作成と管理」)。グループBとグループKの間に改ページを挿入するときに選択するのはグループKの先頭(15行目)です。15行目を選択して操作すると15行目の上にページの区切りが入ります。

f:id:waenavi:20190922143145j:plain

 

15行目またはセルA15を選択します。レイアウトタブの改ページの挿入をします。

f:id:waenavi:20190922143214j:plain

 

(2)書式の連続コピー

問題

A5:E5の書式を、A14:E14とA20:E21にコピーしなさい。

f:id:waenavi:20190922143259j:plain

 

解説

A5:E5を選択します。

f:id:waenavi:20190922143324j:plain

 

書式のコピーのボタンをダブルクリックします。ダブルクリックすると書式のコピーの状態が継続します。これによって連続して貼り付けることができます。

f:id:waenavi:20190922143355j:plain

 

A14:E14を選択します。書式のコピーとなります。このときA5:E5は点線の枠が表示されており、コピーの状態が継続していることが分かります。

f:id:waenavi:20190922143535j:plain

 

A20:E21を選択します。

f:id:waenavi:20190922143508j:plain

 

書式のコピーのボタンをクリックします。これで解除になります。解除するのを忘れてはいけません。

f:id:waenavi:20190922143355j:plain

 

(3)条件付き書式とスパークライン

条件付き書式とスパークラインについては分量が多いので、別の記事で解説しています。

 

8.さいごに

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

 


解説は以上です。


 

 


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