【Excel】サーティファイExcel1級のサンプル問題の実技問題を解いてみた【まとめ】

ビジネス能力検定サーティファイのExcel®表計算処理技能認定試験1級の受験に向けて、まずはサーティファイ公式からサンプル問題をダウンロードしてみました。

知識問題だけを2時間もかけて解いた後日、改めてすべてを解くことにチャレンジしてみました。わからないところを調べながらですが、3回解いたらすべてが理解できました。

※11月15日に2級の合格の通知が届きました！しかし「グラフ・オブジェクトの作成・編集」が66％と低い成績でした。トータル87％でなんとか2級合格です。グラフのところが足を引っ張る形となりました。

本日は、そんな1級サンプル問題の実技問題のまとめをしてみましたので、皆さまもお役立てください。

サンプル問題の実技問題のまとめ
最後に
おまけ

サンプル問題の実技問題のまとめ

問題1-(1) 受験番号と受験者指名

問題文

受験番号（C1）と受験者指名（C2）を左詰めで入力しなさい。

解答例

かならず受験番号と受験者指名を左詰めにするのを忘れないようにしましょう。ここで減点はもったいないです。

問題1-(2) オブジェクトの作成

問題文

＜図１＞を参照し、以下の指示に従って、表のタイトルを作成しなさい。

＜図１＞

解答例

テキストボックス　…　ここは図形とワードアートと横書きテキストボックスの3パターンのうちいずれかが出題されます。どの種類なのかを間違えないようにしましょう。

文字列　…　「登録者リスト」を誤字のないように、そして指示通り全角で入力しましょう。

文字書式　…　フォントは「MS ゴシック」を選択します。「MS Pゴシック」や「MS 明朝」など間違えやすいので、注意しましょう。サイズは「20pt」です。間違えないようにしましょう。

文字の配置　…　「垂直方向：上下中央揃え」「水平方向：中央揃え」です。設定し忘れないようにしましょう。

ワードアートのスタイル　…　これがちょっと紛らわしいのですが、ワードアートではありません。設定方法は、［図形の書式］タブの［クイックスタイル］から設定を行います。

テキストボックスの書式
図形のスタイル　…　その他のテーマの塗りつぶしを選びます。その他の中にスタイル３というのがありますのでそれを選択します。
図形の効果　…　面取りの中に丸というのがありますので、それを選択します。
サイズ　…　高さ1.2cm、幅6cmに設定します。忘れやすいので、注意しましょう。

問題1-(3) 画像の挿入

問題文

「実技用」フォルダーにある画像ファイル“book.jpg”を挿入し、＜図１＞を参考にサイズを調整し、適切な位置に配置しなさい。

＜図１＞

解答例

［挿入］タブの［図］グループの中の［画像］から［このデバイス…］を選択します。
［図の挿入］ウィンドウが開くので、テストを受ける際に作られた［実技用］フォルダーを探し、その中の“book.jpg”を選択し、［挿入］ボタンをクリックします。
挿入ができたら、＜図１＞の例に習って、大きさを変更し、位置を調整します。このとき大きさの変更で、等倍で変更するように斜めのボタンで調整するようにします。縦横比を変えてはいけません。

詳しくは、以前書いた私のブログを参照してください。

問題1-(4) セルの書式設定

問題文

以下の指示に従って、「登録者リスト」の「年齢」（H8からH37）を設定しなさい。

年齢の後ろに「歳」が表示されるように、表示形式を設定する。
（例：「62」　→　「62歳」）
文字の配置の横位置を中央揃えに設定する。

解答例

H8からH37までの「年齢」ということで、H8にカーソルを当ててからCTRL+SHIFT+↓でH8からH37までを全選択します。
表示形式を変更するので、右クリックして［セルの書式設定］を開きます。
分類の中の「ユーザー定義」を選択し、「0″歳”」と入力して［OK］ボタンを押します。
文字の配置の横位置を中央揃えに設定することも忘れないようにしましょう。ここ忘れがちです。

問題1-(5) 入力規則

問題文

以下の指示に従って、「条件別登録者数」の「住所２」（I5）に、入力規則を設定しなさい。

ドロップダウンリストから「西区」、「南区」、「中区」、「磯子区」のいずれかの文字列を選択できるように設定する。
セルの選択時に、「リストから選択」の入力時メッセージがすべて全角で表示されるように設定する。
1.でドロップダウンリストに設定した文字列以外のデータを入力した際に、「再選択してください。」のエラーメッセージが表示されるように設定する。ただし、スタイルは「停止」とする。

解答例

入力規則でリストを作成するわけですが、「磯子区（いそごく）」が読みにくい場合があります。確実に点を取るため、ここは列Eの中にある「磯子区」をコピーしておきましょう。

入力規則なので、［データ］タブの［データツール］グループの中の［データの入力規則］を使用します。

［データの入力規則］ウィンドウが開いたら、［入力値の種類］を［リスト］にします。

［元の値］に「西区,南区,中区,磯子区」と間を半角カンマで区切って入力します。

エラーメッセージの設定も忘れないように指示通り行いましょう。

問題1-(6) 複合的な関数

問題文

以下の指示に従って、「条件別登録者数」（I4からK5）を設定しなさい。

IF関数、AND関数、DCOUNTA関数を使用して、「登録者数」（K5）を設定する。
1. 「条件別登録者数」の「住所２」（I5）と「利用回数」（J5）がともに空欄の場合、空欄とする。
2. 「条件別登録者数」の「住所２」（I5）と「利用回数」（J5）に設定した条件を満たす「氏名」の個数を「登録者リスト」から表示する。
「条件別登録者数」の「住所２」（I5）に「西区」、「利用回数」（J5）に「20回以上」とする条件を設定し、「登録者数」（K5）を表示する。

解答例

関数を複合的に使用する問題で、ちょっと混乱するかも知れませんが、問題の順に解いていけばいいだけですので、落ち着いて解きましょう。

まず1.から「IF関数」「AND関数」「DCOUNTA関数」を使用することがわかります。

次に、1.-1.からI5とJ5がともに空欄の場合、空欄ということで、次のところまで式が作成できます。

IF(AND(I5=””, J5=””), “”,

そして、I5とJ5に設定した条件を満たす「氏名」の個数を「登録者リスト」から表示するとのことですので、DCOUNTA関数を用いてデータベースE7:K37、フィールドC7、条件I4:J5が導き出されますので、これを上の式に付け加えます。

IF(AND(I5=””, J5=””), “”, DCOUNTA(E7:K37, C7, I4:J5))

となります。

2.はI5をリストから「西区」を選択し、J5に「>=20」とすれば、あとは式が正しければ自動で計算を行ってくれます。

問題1-(7) 条件付き書式：データーバー

問題文

以下の指示に従って、「利用回数」（J8からJ37）に条件付き書式を設定しなさい。

「データバー」の「塗りつぶし（グラデーション）」の「オレンジのデータバー」を設定する。
最小値を数値の「0」、最大値を数値の「100」とする。

解答例

［ホーム］タブの［スタイル］グループの［条件付き書式］から［データバー］を開き、［オレンジのデータバー］を選択します。

最小値を数値の「0」、最大値を数値の「100」とするには、［ホーム］タブの［スタイル］グループの［条件付き書式］の一番下の［ルールの管理］を選択します。

［ルールの編集］をクリックします。

下図のように種類を「数値」とし、値を最小値に「0」を、最大値に「100」を入力して［OK］をクリックします。

問題1-(8) 条件付き書式：セルの強調表示ルール

問題文

以下の指示に従って、「返却遅れ回数」（K8からK37）に条件付き書式を設定しなさい。

「返却遅れ回数」（K8からK37）が1以上の場合、以下の指示に従って、セルの塗りつぶしの色を設定する。
1. 背景色を「白、背景1」（［テーマの色］1行1列目）とする。
2. パターンの色を「赤」（［標準の色］1行2列目）とする。
3. パターンの種類を「12.5％灰色」（1行5列目）とする。
「返却遅れ回数」（K8からK37）が10以上の場合、以下の指示に従って、セルの塗りつぶしの色を設定する。
1. 背景色を「白、背景1」（［テーマの色］1行1列目）とする。
2. パターンの色を「赤」（［標準の色］1行2列目）とする。
3. パターンの種類を「左下がり斜線縞」（2行4列目）とする。

解答例

これは、1.と2.の2回設定を行います。同じセルに複数の条件を設定することができます。

1.ですが、K8からK37を選択し、［ホーム］タブの［スタイル］グループの［条件付き書式］の［セルの強調表示ルール］の中の［その他のルール］を選択します。

次のセルのみを書式設定を1.の指示に従って「次の値以上」「1」と設定します。次の設定に進むため［書式］をクリックします。

［セルの書式設定］ウィンドウが開かれたら、［塗りつぶし］タブを選択し、背景色を「白、背景1」（［テーマの色］1行1列目）とし、パターンの色を「赤」（［標準の色］1行2列目）として、パターンの種類を「12.5％灰色」（1行5列目）とします。

2.も同様に行いますが、「返却遅れ回数」（K8からK37）が10以上の場合という条件と、パターンの種類を「左下がり斜線縞」（2行4列目）とするという条件が1.とは異なる点に注意しましょう。

問題2-(1) 名前の定義

問題文

以下の指示に従って、「図書管理表」ワークシートに名前を定義しなさい。

セルE3に、名前「処理日」を定義する。
セルN5からO14に、名前「区分一覧」を定義する。

解答例

1.ですが、セルE3を選択して右クリックを行い、［名前の定義］をクリックします。

［名前の定義］を開いたら、名前に「処理日」と入力します。

2.も同様に、N5:O14の範囲選択をした後、右クリックを行い、［名前の定義］をクリック、［名前の定義］が開いたら「区分一覧」と入力します。

定義した名前を使用することで、例えば$N$5:$O$14と絶対参照をすることなく、定義した範囲を使用することができます。

［名前の定義］で定義した名前は、［数式］タブの［定義された名前］グループの［名前の管理］から編集・削除することができます。

問題2-(2) VLOOKUP関数

問題文

以下の指示に従って、「図書管理表」ワークシートの「図書管理表」の「分野」（F6からF34）を設定しなさい。

VLOOKUP関数を使用して、「区分一覧」（N5からO14）から、「区分」（B6からB34）に該当する分野を表示する。
引数「範囲」は（1）で定義した名前を使用し、「検索方法」は「検索値」と完全に一致する値のみを検索するように設定する。

解答例

F6を例にとります。F6に対して、検索値はB6、範囲は「区分一覧」、列番号は2、検索方法はFALSEとなりますよね。

それをVLOOKUP関数で定義すると、

VLOOKUP(B6, 区分一覧, 2, FALSE)

となります。

私が今まで解説してきたページを見れば、特に難しいということもないと思います。

解説してきたページがわからないという方のためにご説明いたしますと、表計算処理技能認定試験2級や表計算処理技能認定試験3級というタブで検索することができます。

問題2-(3) 基礎的な関数

問題文

以下の指示に従って、「図書管理表」ワークシートの「図書管理表」の「超過日数」（K6からK34）を設定しなさい。その際、「処理日」は（1）で定義した名前を使用すること。

IF関数を使用して、「返却日」が空欄の場合、以下の1-1、1-2の指示に従って「超過日数」（K6からK34）を設定し、「返却日」が空欄でない場合は、「超過日数」は空欄にする。
1. 「返却予定日」が「処理日」より前のとき、「処理日」から「返却予定日」を減算して、超過日数を求める。
2. 「返却予定日」が「処理日」以降のとき、空欄にする。

解答例

K6を元に考えていきましょう。「もし、返却日が空欄なら1-1、1-2の指示に従い、そうでない場合は「超過日数」は空欄にする」とまとめられます。

ここまでを式にすると、

IF(J6=””, 「1-1、1-2の指示」, “”)

となり、次に「1-1、1-2の指示」の内容を式にすると、

IF(I6<処理日, 処理日-I6, “”)

となります。つまり、

IF(J6=””, IF(I6<処理日, 処理日-I6, “”), “”)

が答えとなります。

落ち着いて順番に解答を作っていくことが肝となります。

問題2-(4) コピー＆ペースト

問題文

「図書管理表」ワークシートの「図書管理表」（B5からL34）をコピーし、「抽出」ワークシートのセルB5からL34に「値と数値の書式」の形式で貼り付けなさい。

解答例

Excelの場合、コピーは普通に行えるのですが、ペースト、つまり貼り付けにはいくつかの種類が存在します。

そのうちのひとつが「値と数値の書式」です。

コピーを行うと、次はセルに貼り付けを行うことになるのですが、貼り付けを行う際に、下図から「値と数値の書式」を選択することになります。

安心してください。カーソルを当てれば「値と数値の書式」という文字がポップアップで浮かび上がります。

問題2-(5) テーブルの作成

問題文

以下の指示に従って、「抽出」ワークシートの「図書管理表（抽出）」（B5からL34)をテーブルとして設定しなさい。

先頭行をテーブルの見出しに使用する。
テーブルスタイルのオプションにおいて、［見出し行］、［縞模様（行）］、［フィルターボタン］チェックボックスをON、その他のチェックボックスをOFFに設定する。
テーブルスタイルを、「薄い青、テーブルスタイル（淡色）16」に設定する。

解答例

1.については、今まで私のサイトをご覧になってきた方なら理解している部分だと思います。

2.ですが、まれにデフォルトの設定以外の設定にする場合がありますので、慎重にONにするのはどれで、OFFにするのはどれかを見極めながらチェックしていってください。

3.のテーブルスタイルを、「薄い青、テーブルスタイル（淡色）16」に設定するでつまづくのは、「薄い青、テーブルスタイル（淡色）16」が見つからない場合です。

しかし、確実に指示された色は存在しますので、根気強く正しいものを探し出してください。必ずあります。

問題2-(6) マクロの作成

問題文

以下の指示に従って、テーブルとして設定した「抽出」ワークシートの「図書管理表（抽出）」（B5からL34）から条件に該当する行を抽出するマクロを作成しなさい。

マクロ名は「返却連絡」とする。
マクロの保存先は「作業中のブック」とする。
マクロの操作内容は、以下の通りとする。
1. 「超過日数」が「14」以上の行を抽出する。
2. 「予約」に「*」を含む行を抽出する。
3. セルA1をアクティブセルにする。

解答例

マクロのところを私のサイトを読んでいただけていたなら、楽勝ですね。

ここでは、「超過日数」が「14」以上の行を抽出するとあります。まずは、「超過日数」のフィルターボタンをクリックし、［テキストフィルター］の［ユーザー設定フィルター］を開きます。

次に、フィルターの条件の「14」「以上」を設定します。

そして、3-2の条件をクリアするために、「予約」に「*」を含む行を抽出とありますので、「予約」のフィルターボタンをクリックし、［テキストフィルター］の「指定の値を含む」を選択します。

次に、フィルターの条件の「*」「を含む」を設定します。

最後にセルA1をアクティブセルにしてマクロを終了させます。

問題2-(7) マクロの作成

問題文

以下の指示に従って、「抽出」ワークシートの「図書管理表（抽出）」（B5からL34）を元に戻すマクロを作成しなさい。

マクロ名は「解除」とする。
マクロの保存先は「作業中のブック」とする。
マクロの操作内容は、以下の通りとする。
1. 抽出によって非表示になった行をすべて表示する。ただし、フィルターは解除しないこと。
2. セルA1をアクティブセルにする。

解答例

ポイント1．非表示になった行をすべて表示させる。
ポイント2．フィルターは解除しない。

というわけで、［データ］タブの［並べ替えとフィルター］グループの［フィルター］をクリックする方法は却下となります。

ではどうやって非表示になった行をすべて表示させるのか？それは、［クリア］ボタンを使用します。これで解決です。

あとは、セルA1をアクティブセルにしてマクロを終了させて完了です。

問題2-(8) オブジェクトの作成

問題文

＜図２＞を参照し、以下の指示に従って、「抽出」ワークシートに「図形（四角形：角を丸くする」を2つ作成し、それぞれマクロを登録しなさい。

＜図２＞

解答例

まず、「図形（四角形：角を丸くする」を１つ作成します。大体の大きさは＜図２＞を見て設定します。

そして、作成した図をCTRLキーを押しながら横へスライドさせて、複製を作成します。図をALTを押しながら上へ移動させ、両方の図の上端を同じ位置に調整します。

それができたら、2つの図それぞれに「返却連絡」「解除」と入力し、と「図形のスタイル」を設定します。

ここまでできましたら、片方の図形をクリックした状態で、もう片方の図形へカーソルを持っていき、CTRLを押してもう片方の図形をクリックします。これで、2つの図形のデザインを同時に変更することができるようになりました。

これができたら、「文字書式」「フォント」「文字の配置」「図形の効果」「図形のサイズ」を一括で設定します。

マクロの登録は、それぞれ片方ずつ設定します。ボタンを右クリックし、［マクロの登録］からそれぞれのマクロを登録してこの問題は完了です。

注意点などは、以前書いたブログを参考にしてください。

問題3-(1) ピボットテーブルの作成

問題文

以下の指示に従って、「ベストリーダー貸出状況」（B4からH68）をもとに、ピボットテーブルを作成しなさい。

新しいワークシートに作成し、シート名を「ピボット」に変更する。
＜図３＞を参照し、レイアウトおよび累計の方法を設定する。
＜図３＞を参照し、必要に応じて列の幅を調整する。

＜図３＞

解答例

2．から解いていきます。

縦の行からまず解析していきます。縦には「著者名」「書名」「出版社」とあるので、行は上から「著者名」「書名」「出版社」を指定することがわかります。

次に、横の列ですが、月が設定されているので、これは「貸出日」であることがわかります。「貸出日」を列に設定すると、「月（貸出日）」「日（貸出日）」というものが列に追加されますが、日のデータはないため、「日（貸出日）」は削除します。

値には「貸出回数」が設定されているようなので、これを設定します。

次にレイアウトですが、これはすべて試していっても構いません。正解は「表敬式で表示」となります。

3．ですが、列幅がおかしいのは「書名」です。あとは「1月」～「6月」も幅が狭く設定されているように見えるので調整します。

問題3-(2) ピボットテーブルの操作

問題文

＜図４＞を参照し、以下の指示に従って、（1）で作成したピボットテーブルを変更しなさい。

ピボットテーブルスタイルを、「薄い黄、ピボットスタイル（淡色）12」に設定する。
小計を表示しない。
「貸出日」のタイムラインを挿入する。
1. 時間レベルを「四半期」に設定する。
2. 第2四半期を表示する。
3. タイムライン「貸出日」をセルC14の左上端に合わせるように移動する。
4. タイムラインのスタイルを「薄い青、タイムラインスタイル（淡色）4」に設定する。

＜図４＞

解答例

1．は簡単にできると思います。

2．はピボットテーブルをクリックした後で、［ピボットテーブルツール］の［デザイン］タブの［レイアウト］グループの［小計］から［小計を表示しない］を選択すると設定できます。

3．のタイムラインは［ピボットテーブルツール］の［ピボットテーブル分析］タブの［タイムラインの挿入］より行います。
3-1. の時間レベルを「四半期」に設定するには、下図の四半期をクリックします。

3-2. の第2四半期を表示するには、下図の第2四半期をクリックします。

3-3. は、ALTキーを押しながら移動すると、ぴったりC14の左上端に合わせることができます。
3-4. のタイムラインのスタイルを「薄い青、タイムラインスタイル（淡色）4」に設定するには、［ピボットテーブルツール］の［デザイン］タブの［ピボットテーブルスタイル］から行うことができます。

問題4-(1) グラフの作成

問題文

＜図５＞を参照し、以下の条件を満たす複合グラフを作成しなさい。

＜図５＞

解答例

＜図５＞から範囲を求めると、東野、西野、南野、北野を選択し、4月～9月までのデータを選択するということが読み取れます。

従って、B3:B7とF3:K7が範囲の複合グラフであることがわかります。

次に指示通りに、系列「東野」を縦棒、残りを折れ線として設定してグラフを作成します。

［グラフの位置］は、ざっくりとでいいのですが、一旦ALTキーを押しながら移動させ、正確に左上端をB10に右下端をN27に合わせ、その後で少し小さく設定しておきます。（はみ出ると減点のおそれがあるため）

［グラフスタイル］を設定しますが、［クイックレイアウト］と混同しないように注意しましょう。

［グラフタイトル］は文字列を一字一句間違えないように打つことを心がけましょう。ここで減点はもったいないです。フォントの設定も忘れずに、そしてここでいうワードアートは、［グラフツール］の［書式］タブの［クイックスタイル］のことですので、間違えないようにしましょう。

あとの設定は、以前私が書きました以下のサイトに例が載っていますので参考にしてください。

問題5-(1) 統合（集計）

問題文

以下の指示に従って、「東野図書館」、「西野図書館」、「南野図書館」、「北野図書館」の表を統合し、「図書館集計表（延べ）」を作成しなさい。

「図書館集計表（延べ）」のセルJ3からP10に、「東野図書館」、「西野図書館」、「南野図書館」、「北野図書館」の月ごとの合計を統合する。
統合の基準は各自考えること。
統合元データとリンクはしない。

解答例

それぞれの図書館のデータを見ると、横軸が並びに統一性がないことがわかります。

したがって、統合の基準は「上端行」となります。

以上です。

最後に

いかがでしたでしょうか？

2級のサンプル問題に比べ、1級のサンプル問題はワンランク上の難しさがありました。感覚としては、2級の応用編といった感じがしました。

皆さまのお役に立てたならば幸いです。

白川秋

ではでは、参考までに。

おまけ

サーティファイの Excel®表計算処理技能認定試験1・2級を受験するにあたり、これさえ買っておけば必ず受かるといっても過言ではない一冊の本をオススメ致します。

それがこちら。

Excel® 表計算処理技能認定試験 1・2級問題集 2021対応【オリジナルボールペン付き】エクセル

¥5,680 （2024/06/11 19:45時点 | Amazon調べ）

Amazon

ポチップ

私も購入させていただき、大変お世話になっております。

Post Views: 14