【10分短縮を目指す】CS 検定表計算部門2級のコツ・時間配分(課題3編)

CS検定エクセルのコツ CS検定

コンピュータサービス技能評価試験(通称:CS検定)の表計算部門2級について、課題1・2のコツ、条件付き書式、出題頻度の高い関数をご紹介してきました。

今回は、残っている課題3・4のうち、課題3の時間配分や解答テクニックについてご紹介していきます。

スポンサーリンク

表計算部門2級 課題3について

課題3は、提供されるデータを使って集計や抽出をおこなう問題です。
普段使い慣れない機能が要求されるため、最初は操作に迷うこともありますが、慣れたら10分程度で完成できる問題です。

課題3:データ処理

課題3の問題は、大きく分けて

  • フィルター機能を用いたデータの抽出と並べ替え
  • ワイルドカードを用いたデータの抽出と並べ替え
  • 集計機能を用いたデータの集計

の3つに分類できます。
1つずつ操作方法を確認していきましょう。

フィルター

フィルター機能では、「トップテン」という項目を使用する問題が出題されます。

トップテンの抽出

では「トップテン」の抽出方法を確認します。
今回は、以下のデータを用いて「契約金額の上位5位」を抽出してみましょう。

CS検定表計算2級のコツと時間配分

1. データにフィルター機能を設定します。
表の中のセルを1つ選択し、「データ」タブ→「並べ替えとフィルター」グループ→「フィルター」の順に選択すると、表にフィルターがかかります。

CS検定表計算2級のコツと時間配分

2. フィルターがかかると、表の項目名部分(契約日や契約金額が入っている部分)にボタンが表示されます。

CS検定表計算2級のコツと時間配分
赤で囲んだ部分(項目名部分)にボタンが表示されている

フィルターを設定する際、表の外を選んでいるとフィルターはかかりません

また、きちんと表内を選んでもうまくフィルターがかからない場合があります(データの状態により)。
その時は、フィルターをかけたい表全体を範囲選択してから設定してみてください。

3. 「契約金額の上位5位」を抽出するため、「契約金額」項目のボタンをクリックします。すると、下図のような選択候補が表示されます。

CS検定表計算2級のコツと時間配分

4. 「数値フィルター」→「トップテン」の順にマウスを移動し、「トップテン」の上でクリックすると、

CS検定表計算2級のコツと時間配分

トップテンオートフィルター」ダイアログボックスが表示されます。

CS検定表計算2級のコツと時間配分

5. 「トップテンオートフィルター」の最初のボックスで、「上位」か「下位」かを選択します。

CS検定表計算2級のコツと時間配分

CS検定の出題は、ほぼ「上位5位」ですが、まれに「下位5位」や「上位5%」などの場合もありますので、よく問題を見るようにしてください。

6. 真ん中のボックスで「何位までか」の数値を指定します。今回は「上位5位」なので「5」と指定しましょう。

CS検定表計算2級のコツと時間配分

7. 右端のボックスで「項目」か「パーセント」かを選択します。
上位○位下位○位」であれば「項目」を、「上位○%下位○%」であれば「パーセント」を選択してください。

CS検定表計算2級のコツと時間配分

8. 設定できたら「OK」を押して抽出完了です。

CS検定表計算2級のコツと時間配分
緑で塗った部分の金額が同じ

フィルターを使った抽出の注意点

ここで、データを見ながら確認して欲しいことが3つあります。

  • 抽出されたデータの件数
  • 抽出データの並び順
  • 「契約金額」欄のボタンの見た目

データの件数を数えると、6件あります。


緑で塗りつぶしたセルを確認すると、契約金額が同じです。このように、同じ数値がある場合、抽出後の件数が増えることがあります。

次にデータの並び順ですが、契約金額の多い順には並んでいません。
あくまで該当のデータを抽出したのみで、並べ替えはまた別途おこなう必要があります。

最後に抽出条件を設定した「契約金額」のボタンを見てください。
ほかのボタンとは見た目が違っています。

契約金額と他の項目で、ボタンの見た目が違っている

フィルター機能を使って抽出条件を設定した項目は、ボタンの見た目が変わります。
ボタンの見た目で、どの項目に抽出条件が設定されているかがわかるようになっています。

フィルターの解除

フィルターの解除には以下の2種類があります。

  • フィルター機能を使った抽出条件の解除
  • フィルター機能の解除

抽出条件の解除は、「契約金額の上位5位」の抽出をやめる、ということです。
フィルター機能自体は残りますので、ボタンはついたままです。

どこか1項目でも抽出条件が設定されていると、「データ」タブ→「並べ替えとフィルター」グループ→「クリア」ボタンが押せる状態になります

CS検定表計算2級のコツと時間配分

この「クリア」ボタンが押せるということは、どこかで抽出がおこなわれているということです。

この「クリア」ボタンを押せば、複数項目に抽出条件が設定されていても、一度に解除することができます。


フィルター機能の解除は、ボタンの表示をやめるということです。
実務では、必要な抽出が終わったら、必ずこの解除をおこないましょう。

フィルター機能の解除は、「データ」タブ→「並べ替えとフィルター」グループ→「フィルター」ボタンをクリックするだけでOKです。

その時、セルはどこを選んでいても問題ありません

抽出データ貼り付けのコツ

このあとCS検定では、抽出したデータを指定のシートに貼り付け、並べ替えをおこないます。

その際

「あえて項目名を一緒にコピー」

すると、その後の並べ替えの時に範囲選択をやり直さずにすみます。

ワイルドカード

次にワイルドカードを見ていきます。
ワイルドカードは

文字列の指定や比較、探索などをおこなう際に、任意の、あるいは特定のパターンに一致する文字列を表す特殊な記法や記号のこと。

IT用語辞典 より

エクセルで、あいまいな条件でのデータ抽出をおこなう時に使います。
ほとんどの方は実務で使いませんので、検定が終わったら忘れて構いません。

では実際のデータで見ていきます。

ワイルドカードでの抽出

ワイルドカードでの抽出手順は、

  • 抽出条件作成のための項目名をコピーする
  • 抽出条件を設定する
  • 「詳細設定」から抽出をおこなう

の3段階です。

今回の抽出条件は、

  • エリアが静岡で、契約金額が1500000円以上
  • 担当営業に松を含み、入金確認が○

でおこないます。

1. 抽出結果を貼り付けるシートに「↓抽出条件作成」という文字があるので、その下の行に表の項目名をコピーします。

CS検定表計算2級のコツと時間配分

2. コピーした項目名の下に、ワイルドカードを用いて抽出条件を設定します。
まず項目名のすぐ下の行に「エリアが静岡で、契約金額が1500000円以上」を設定します。

CS検定表計算2級のコツと時間配分

この時、入力ミスを避けるためコピーできるところはコピーしましょう。
今回は、静岡の部分を表からコピーしました。

入力された文字をコピーすると、罫線や色(CSの問題では色はついていません)が一緒にコピーされますが、抽出には何ら影響ありません
また、採点にも影響ありませんのでそのまま先に進んでください。

3. 続けて2つ目の条件「担当営業に松を含み、入金確認が○」を設定します。

CS検定表計算2級のコツと時間配分

先ほど条件を設定した行の下の行に、それぞれ条件を設定します。
「松を含む」の設定方法は「*松*」となります。

*(アスタリスク)は任意の文字列を表します。

  • 「松*」→「松で始まる」
  • 「*松*」→「松を含む」
  • 「*松」→「松で終わる」

という使い方をします。

「入金確認が○」は、表からそのままコピーします。
罫線が一緒にコピーされますが、気にせず先に進みましょう。

「まる」と入力して変換すると、「○[記号]」と「〇[漢数字]」が候補として出て来る場合があります。
見た目は同じように見えますが、エクセルの中では完全に別物です。手入力で「○」を入力する時は、「○[記号]」を選択するようにしましょう。

CS検定では、入力ミスを避けるため、○が出て来たらコピーしたほうが安心です。

抽出条件が設定できたら、抽出をおこないましょう。

4. 抽出元の表があるシートに切り替え、表内を選択して「データ」タブ→「並べ替えとフィルター」グループ→「詳細設定」を選択します。

CS検定表計算2級のコツと時間配分

5. 「フィルターオプションの設定」ダイアログボックスが表示されたら、「抽出先」を「指定した範囲」に変更します。

CS検定表計算2級のコツと時間配分

6. 「検索条件範囲」にカーソルを移動し、設定した条件を範囲選択します。

CS検定表計算2級のコツと時間配分

7. 「抽出範囲」にカーソルを移動し、抽出元の表の数行下のセルを選択したら「OK」をクリックします。

CS検定表計算2級のコツと時間配分

8. 7で指定したセルを基点とし、データが抽出されます。

CS検定表計算2級のコツと時間配分

9. あとは設問に従って、抽出データを指定の場所に移動します。

出題されやすいワイルドカード一覧

CS検定でよく出題されるワイルドカードは、以下の通りです。

○○に等しい○○ 手入力せず表からコピーする
○○で始まる○○*
○○で終わる*○○
○○を含む*○○* 含むの場合は前後を*(アスタリスク)で挟む
○○を含まない<>○○
○○以上>=○○
○○以下<=○○
○○を超える>○○
○○未満<○○
○道部(剣道部、茶道部など)?道部

(アスタリスク)は文字数の定めがない文字列の替りとして使います。
それに対し、一文字と決まっている場合は(クエスチョンマーク)を使いましょう。

ワイルドカードを使った抽出の注意点

大前提として、ワイルドカードは半角で入力します。
前後に日本語を入力すると、ついつい全角で入力してしまいがちなので、入力モードの切り替えを必ずおこなってください。

入力モードの切り替えは「半角/全角」キーでおこなえます。

慣れない操作なので、まず手順をしっかり覚えるようにしましょう。
そして、練習のときはフィルター機能などを用いて結果の確認してください。

ただし、本番では確認する時間はありません
練習の間にしっかり確認して、正しい手順を身につけてください。

集計

つぎに、集計についてご紹介します。

集計は、手順さえ覚えれば1分で終わります。
考えなくても出来るように、何度も練習してしっかり手順を身につけてください。

集計の手順

では集計の手順を確認していきましょう。

集計をするにあたり、最初に必ずデータの並べ替えをおこないます。
これを忘れたり失敗したりすると、正しい集計がおこなえませんのでご注意ください。

問題によって、一覧表シートをそのままコピーする場合と、新しいシートを挿入して一覧表だけをコピーする場合があります。

問題をよく読んで、間違わないようにデータを準備してください。

1. 今回は「一覧表」シートをコピーし、シート名を「集計」に変更してから集計作業をおこないます。
集計」シートが準備できたら「エリアの昇順」「担当営業の昇順」という条件で並べ替えをおこないます。

2. 表の中のセルをどれか1つ選択し、「データ」タブ→「並べ替えとフィルター」グループ→「並べ替え」ボタンをクリックします。

CS検定表計算2級のコツと時間配分

3. 「並べ替え」ダイアログボックスが表示されたら、「エリアの昇順」「担当営業の昇順」の順に設定し、「OK」をクリックします。
入力するボックスを増やしたい時は「レベルの追加」ボタンをクリックしましょう。

CS検定表計算2級のコツと時間配分

並べ替え後、集計作業に入ります。今回は

  • 1回目:グループの基準「エリア
  • 2回目:グループの基準「担当営業

とし、集計の方法は「合計」、集計するフィールドは「契約金額」で集計をおこないます。

4. 表内のセルを選択し、「データ」タブ→「アウトライン」グループ→「小計」をクリックします。

5. 「集計の設定」ダイアログボックスが表示されたら、

CS検定表計算2級のコツと時間配分

1回目の集計として、グループの基準「エリア」、集計の方法「合計」、集計するフィールド「契約金額」を設定し「OK」をクリックします。

CS検定表計算2級のコツと時間配分

1回目の集計が完了したら、引き続き、2回目の集計をおこないます。

6. 再度「小計」をクリックし、「集計の設定」ダイアログボックスを表示させます。
前回の集計情報が設定されていますので、一部分変更していきましょう。

7. グループの基準を「担当営業」に変更し、「現在の小計をすべて置き換える」のチェックを外し「OK」を選択しましょう。

CS検定表計算2級のコツと時間配分

これで、集計作業は完了です。
あとは、指定の場所にデータを貼り付けていきます。

集計データの貼り付け

1. 用意されている貼り付け先を確認し、集計データの絞り込みを選択します。

CS検定表計算2級のコツと時間配分

2. 集計後データの左端を見ると、「1」「2」「3」…と数字が表示されています(集計の状況により数は変化します)。

CS検定表計算2級のコツと時間配分

この数字をクリックすると、集計データの絞り込みがおこなわれますので、貼り付け先のデータと合う状態になる番号を探します。

今回は「3」を選択すると、貼り付け先と合致したデータに絞り込まれます。

CS検定表計算2級のコツと時間配分

3. 「契約金額」をコピーして、用意された貼り付け先に貼り付けます。

集計をおこなったデータは、行が折りたたまれてデータが隠れている状態になっています。そのまま範囲選択してコピーすると、折りたたまれて隠れているデータも一緒にコピーされてしまいます。

CS検定のこの課題では、「可視セル」を設定してからコピーするようにしましょう。「可視セル」とは「今見えているセル」という意味で、隠れているデータは対象外になります。

コピーしたい範囲(今回は「契約金額」の範囲)をマウスで選択し、「Ctrl」キー+「G」を押すと「ジャンプ」ダイアログボックスが表示されます。

ジャンプ」ダイアログボックスが表示されたら、「セル選択」をクリックします。

CS検定表計算2級のコツと時間配分

4. 「選択オプション」ダイアログボックスが表示されたら、「可視セル」にチェックを付け「OK」を選択しましょう。

CS検定表計算2級のコツと時間配分

5. エクセルの画面に戻ったら、そのまますぐに「Ctrl」+「C」でコピーします。

どこかをクリックすると、折角設定した可視セル」が解除になりますので、必ずすぐにコピーするようにしてください。

「可視セル」が設定された状態でコピーすると、セルとセルの間に破線が表示される場所があります。この破線が表示される場所は、セルが折りたたまれているところです。

CS検定表計算2級のコツと時間配分

6. シートを切り替え、用意されている貼り付け先にコピーしたデータを貼り付けます。
この時、貼り付けは必ず「で貼り付けてください。

CS検定表計算2級のコツと時間配分

」での貼り付けは、「ホーム」タブ→「クリップボード」グループ→「貼り付け」ボタンの「▼」をクリックし、一覧の中から「」を選択します。

」で貼り付けると、貼り付け先の表に設定されている罫線を崩さずにデータを貼り付けることができます。

実務でも使えますので、ぜひ覚えてください。

表計算部門2級 課題3まとめ

課題3で出題されるデータの抽出や並べ替え、集計などは実務でも使える機能になります。

仕事でエクセルを使う可能性のある方は、ぜひ覚えて欲しい機能です。
ただし、ワイルドカードは実務で使うことは少ないので、検定が終わったら忘れてしまっても大丈夫です。

パターンに慣れると、課題3は10~13分程度で終わります。
ここでしっかり時間短縮できるように練習しておきましょう。

それではまた。

タイトルとURLをコピーしました