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

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

MOS一般(スペシャリスト)のExcel関数の練習問題これができたら合格レベル!

MOS公式サイトで公表されているMOSのExcel2016 一般(スペシャリスト)レベルの出題範囲には「数式や関数を使用した演算の実行」と記載されています。過去問が公開されていないので具体的にどのようなレベルの問題が出題されるのかは不明ですが、履歴書に「MOS合格」と書くのであれば最低限の関数が使えたほうが良いです。

そこで、今回は、MOS Excel一般(スペシャリスト)レベルに合格するにはこれくらいできたほうが良いという、Excel関数の問題を出題します。

※なお、MOSの出題形式とは異なりますので、合格は保証しません。合格したければテキストを買って練習したほうが早いです。

 

 

目次

0.MOSの概要について

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

 

1.セルの参照

(1)相対参照、絶対参照、複合参照

問題

次の第1表~第4表について、合計金額または税込価格を求めなさい。

f:id:waenavi:20190915225328j:plain

 

参考記事

解説

Excel2016出題範囲によると「セル参照を追加する」と記載されています。まずは、「参照」という言葉を理解しなければなりません(上記の記事参照)。他のセルを計算式に利用できることです。同じ向きに並んでいる数値同士の演算の場合、相対参照が使えます。

f:id:waenavi:20190915225608j:plain

 

パーセンテージを用いた割増、割引についてはExcelではなく算数の範囲ですが、割増は(1+割増率)、割引は(1-割引率)を掛けます(参考:割合の計算が分からない原因は、かけ算による変化を理解していないこと)。

f:id:waenavi:20190915225745j:plain

 

複写(オートフィル)の方向が下向きで、税抜価格も下向きに入力されているのに、税率は1か所しかありません。このような場合は、オートフィルによる相対参照の効果を止める必要があります(絶対参照)。

f:id:waenavi:20190915225842j:plain

 

複写(オートフィル)の方向が下と右の両方で、税抜価格が下向き、税率が右方向に入力されている場合は典型的な複合参照です。下向きに入力されているものが列固定、右方向に入力されているものが行固定です。

f:id:waenavi:20190915225917j:plain

 

(2)別のシートを参照する

問題

さきほどの問題でB列の数値がシート「価格表」にあった場合はどうか。

元の表

f:id:waenavi:20190915230118j:plain

 

シート「価格表」

f:id:waenavi:20190915230234j:plain

 

参考記事

解説

セルの参照は同じシートだけでなく、別のシートのセルを参照することも可能です(上記の記事参照)。このとき、「シート名!セル番地」となります。

f:id:waenavi:20190915230351j:plain

f:id:waenavi:20190915230433j:plain

 

第3表は計算するためのデータがすべて別のシートにあります。消費税は絶対参照になります。

f:id:waenavi:20190915230519j:plain

 

絶対参照にするか、複合参照にするかの判断は、別のシートにあっても同じです。オートフィルの方向と、演算で使用するデータの並ぶ方向で決まります。

f:id:waenavi:20190915230552j:plain

 

2.オートSUM

(1)SUM・MAX・MIN・COUNT・AVERAGE

問題

受験者が教科を自由に選択して受験することができるテストを実施した。採点が終了したので得点を入力したが、受験していない科目は空欄とした。合計・平均・最大・最小・出席者数・欠席者数・受験科目数・受験者数・科目数を求めなさい。

f:id:waenavi:20190915231603j:plain

 

参考記事

解説

Excel2016出題範囲によると「SUM関数」「MIN、MAX関数」「COUNT関数」「AVERAGE関数を使用して計算を行う」と記載されています。要するに、オートSUMです(上記の記事参照)。オートSUMボタンを使えば計算できますが、オートSUMによって自動的に挿入される式の意味を理解しなければなりません。

縦横の合計については、合計を含めた範囲を選択して一括で合計を出す方法と、
オートフィルを用いる方法がありますが、両方練習しておいたほうが良いでしょう。

f:id:waenavi:20190915230929j:plain

 

平均を求めるときに合計の欄を選択してはいけません。点線の枠を自分で修正する必要があります

f:id:waenavi:20190915231012j:plain

 

最大・最小・数値の個数についても同じです。

f:id:waenavi:20190915231659j:plain

 

欠席者は空欄を数えますが、COUNTBLANK関数です。COUNTを挿入してから「BLANK」を手入力したほうが早いです。

f:id:waenavi:20190915231756j:plain

 

受験者数は日本語を数えますのでCOUNTA関数です。これもCOUNTを挿入してから「A」を追記したほうが早いです。

f:id:waenavi:20190915231846j:plain

 

科目数も同じです。

f:id:waenavi:20190915231919j:plain

 

(2)離れた範囲の合計

問題

さきほどの問題で、受験者1~3と受験者4~6が分かれて入力されている場合の合計・最大・最小を求めなさい。

f:id:waenavi:20190915232019j:plain

 

解説

合計範囲が分かれている場合は、複数選択となります。B2:B4を選択した後、Ctrlキーを押しながら、I2:I4を選択します。

f:id:waenavi:20190915232056j:plain

 

このとき、2つの範囲の間に、自動的にカンマが入ります。

f:id:waenavi:20190915232205j:plain

 

3.IF

(1)IFの基本

問題

各担当者の売上目標と実績について次の判定をしなさい。

  • D列:実績が4万円以上であればA、4万円未満はB
  • E列:実績が目標に到達していれば目標達成

f:id:waenavi:20190915233840j:plain

 

参考記事

解説

Excel2016出題範囲によると「IF関数を使用して論理演算を実行する」と記載されています。IF関数のように、オートSUMではない関数については、「全角/半角」キーで、全角と半角を切り替えて数式を入力する練習をしましょう。また、イコールが必要なのか不要なのかをしっかりと判断できるようにしましょう。

4万以上でA、それ以外がBなので「=IF(C2>=40000,"A","B")」です。

f:id:waenavi:20190916000316j:plain

 

実績が目標以上であれば目標を達成したことになるので「=IF(C2>=B2,"目標達成","")」です。

f:id:waenavi:20190916000338j:plain

 

(2)ネスト

問題

今期実績についての目標達成率と、昨年比の伸び率をパーセント小数第1位まで表示し、次の判定をしなさい。

  • 伸び率5%のときA、目標達成でB、それ以外はC

f:id:waenavi:20190916000437j:plain

 

解説

達成率は実績/目標です。

f:id:waenavi:20190916000505j:plain

 

昨対は今期実績/昨年なので、伸び率は今期実績/昨年-1です。1を引くだけです(参考:増減率をパーセントで求めるには、増減を表す比率から1を引くだけ【Excel割合の問題、就職活動】)。

f:id:waenavi:20190916000623j:plain

 

MOSの一般レベル(スペシャリストレベル)で難しい関数の入れ子(ネスト)が出題されることは無いと思いますが、とりあえず「=IF(条件式,"A",IF(条件式,"B","C"))」のパターンは暗記したほうがいいでしょう。

f:id:waenavi:20190916000651j:plain

 

4.SUMIF・AVERAGEIF・COUNTIF

※SUMIF・AVERAGEIF・COUNTIFは、MOS Excel2019では上級レベルへ移行される予定です。

(1)グループ別集計

問題

上位メンバー7人のチームと得票数から、チーム別の得票数合計・得票数平均・人数をそれぞれ求めなさい。

f:id:waenavi:20190916000831j:plain

 

解説

Excel2016出題範囲によると「SUMIF関数」「AVERAGEIF関数」「COUNTIF関数を使用して統計的演算を実行する」と記載されています。

  • =SUMIF(検索範囲,検索条件,合計する範囲)
  • =AVERAGEIF(検索範囲,検索条件,平均する範囲)

f:id:waenavi:20190916001024j:plain

 

検索範囲と集計範囲は固定なので、絶対参照にするのを忘れてはいけません。

f:id:waenavi:20190916001116j:plain

 

COUNTIFは検索範囲と条件だけです。

  • =COUNTIF(検索範囲,検索条件)

f:id:waenavi:20190916001218j:plain

 

(2)ワイルドカード・比較演算子

問題

さきほどの問題で、前回順位が圏外でない人について、得票数合計・得票数平均・人数を求めなさい。

f:id:waenavi:20190916001340j:plain

 

解説

半角のアスタリスク「*」でワイルドカード(任意の文字列)を表します。圏外以外の人は「*位」とすればよいです。

f:id:waenavi:20190916001425j:plain

 

別解

圏外以外は不等号を用いて「"<>圏外"」とすることもできます。

f:id:waenavi:20190916001501j:plain

 

5.RIGHT・LEFT・MID・UPPER・LOWER・PROPER・CONCATENATE

問題

社員名・英字・国・生年月日の情報から、関数を用いて次の操作をしなさい。

  • 頭文字(英字の左端から1文字分を取り出す)
  • 大文字(頭文字を大文字に変換する)
  • 先頭だけ大文字(英字の先頭だけ大文字にする)
  • 4桁(生年月日の3文字目から4文字分取り出す)
  • 右2(国の右端から2文字分を取り出す)
  • 小文字(右2を小文字に変換する)
  • メルアド(英字+4桁+「@waenavi.」+小文字)

f:id:waenavi:20190916001551j:plain

 

解説

Excel2016出題範囲によると「RIGHT、LEFT、MID関数」「UPPER、LOWER、PROPER関数」「CONCATENATE関数を使用して文字の書式を設定する」と記載されています。基本的な使い方が分かっていれば良いです。

左端から文字列を取り出すのはLEFTです。

f:id:waenavi:20190916001914j:plain

 

大文字に変換するのはUPPERです。

f:id:waenavi:20190916001917j:plain

 

先頭だけ大文字にするのはPROPERです。

f:id:waenavi:20190916001920j:plain

 

文字列の指定した位置(途中)から文字数分だけ取り出すのはMIDです。

f:id:waenavi:20190916001924j:plain

 

右端から文字列を取り出すのはRIGHTです。

f:id:waenavi:20190916001928j:plain

 

小文字に変換するのはLOWERです。

f:id:waenavi:20190916001933j:plain

 

文字列連結の関数はCONCATENATEです。

  • =CONCATENATE(英字,4桁,"@waenavi.",小文字)

f:id:waenavi:20190916002811j:plain

f:id:waenavi:20190916002839j:plain

 

別解

問題文に「関数を使って」とあればCONCATENATE関数を使わなければなりませんが、そのような指示が無ければ&を用いることも可能です。&は演算子であり、関数ではありませんので注意してください。

f:id:waenavi:20190916002023j:plain

 

6.数式の表示

数式を表示するには、数式タブの「数式の表示」ボタンを押します。各セルに入力されている数式が表示されます。ちなみに、ショートカットキーはCtrl+Shift+@です。

f:id:waenavi:20190916002402j:plain

 

もう一度押すと元に戻ります。数式の表示は一時的に使うものであり、使用したら必ず元に戻すようにします。

f:id:waenavi:20190916002427j:plain

 

7.さいごに

MOS一般レベルの数式と関数について紹介しましたが、Excel関数は出題範囲の2割を占める重要分野です。詳しくは、FOM出版「よくわかるマスターMOS Excel 2016 対策テキスト&問題集」第4章173~199ページをご覧ください。

 


解説は以上です。


 

 


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