Excel　CS検定表計算2級部門　sample問題の課題2の解き方（part1）

CS検定（コンピュータサービス技能評価試験）は、全国工業高等学校長協会が主催する資格試験で、表計算ソフトの操作スキルを評価する「表計算部門」があります。この試験は、主に学生や初心者を対象とした内容ですが、社会人が自分のスキルを再確認するためにも利用されています。ここでは、CS検定の表計算課題2のsample問題を解いていきましょう。

CS検定表計算2級部門　sample問題の第2問の解き方

受験番号・氏名、保存

課題2は、上記の表を全て埋める事を目的にしています。その為には、関数を使用していきます。

まずは、受験Noと、氏名を書きます。受験Noは全角の数値で書きます。氏名は、仮名文字にしておきます。

キーボードのファンクションキーの【F12】で名前を付けて保存しておきます。ここでは、解凍フォルダに【課題２ＰＲ】と言う名前で保存しておきます。

予約コード

ここでは、予約コードの列から、タイトル名や、ジャンル、ソフト形態などを、1つにまとめた文字列があります。
この、課題2の問題では、上記の予約コードから、読み解き、他の情報を抽出して表示していきます。

そして、抽出した内容から、【金額】【予約特典】などをまとめていきます。

リスト範囲に名前を定義する

もう一つのシートには、リスト一覧があります。コチラの青枠で囲んだ、データの部分の範囲に名前を付けていきます。

この様に、範囲指定して、右上の【名前ボックス】に【商品】と入力して、キーボードの【Enter】キーを押します。
ポイントは、下向き矢印が右側にでるまで、【Enter】キーを押します。

残りの2つの表も【年代】と【会員】と名前をつけます。この名前を付ける作業にも点数は含まれますので、確実に名前を付けてください。

タイトル名（1）

まず、問題文にはこの様に記載されています。

タイトル名とは何か？

リスト一覧シートの、上記の赤枠がタイトル名です。その為には、B列の商品コードが分からないといけません。

VLOOKUP関数

とりまとめ表の【予約コード】の数値の、下3ケタは【785】なので、「じゃばわんこ」という文字列を入力しなければいけません。そこで、VLOOKUP関数を使用していきます。

【C8】セルを選択して、【数式】バーの【FX】をクリックして選択します。

すると、【関数の挿入】ダイアルボックスが表示されます。ここでは、【関数の分類】は【すべて表示】を選択します。

次に、後ろに青帯がついている状態で、キーボードの【V】を押します。すると、【V】の関数名の所にジャンプします。そこから、下にカーソルを下ろして、VLOOKUP関数を探していきます。

VLOOKUP関数を選択して、【OK】ボタンを押します。

すると、この様な、引数の【ダイアルボックス】が表示されます。

RIGHT関数

商品コードから、【785】を抽出したいので、ココでは、RIGHT関数を使用します。

【検索値】をクリックして、左上の▼をクリックすると、【その他の関数】とありますので、クリックして確定します。

すると、【関数の挿入】ダイアルボックスが表示されますので、RIGHT関数を選んで、【OK】ボタンを押します。

すると、RIGHT関数の引数ダイアルボックスが表示されます。
矢印の箇所を選択して、後ろから3文字、ここでは、【785】を指定したいので、文字数は、3と入力します。
すると、＝”785”となってしまいます。

VALUEで文字列を数値にする

一旦、数式バーの【VLOOKUP】にマウスポインタを合わせると、VLOOKUP関数の引数が表示されます。

”785”と表示されてます。この状態は、文字として、認識して、数値としては認識していません。この””を取る方法をご紹介します。【VALUE】と入力してかこいます。

そうすると、ダブルコーテーションは消えて、文字が数値として扱われます。

後は、商品のデータの範囲を選択して、【列番号】は2列目にありますので【2】と入れ、完全一致なので、0と入力します。

そうすると、【C8】セルにタイトル名が入力されます。【C8】セルの右下にマウスポインタをもってきて、ダブルクリックすると、他のタイトル名も反映します。

ここでのポイントは、VALUEを使って、ダブルコーテーションを外して表示形式を変えてあげる事です。
コチラが、アルファベットの文字だけならば、必要ないのですが、今回は、下3ケタは数値でしたので、アルファベットを除外してあげることが必要なので、覚えておきましょう。

ジャンル

次に、タイトルが出来ていれば、ジャンルは、タイトル名に使用した、VLOOKUP関数の数式をカスタマイズしていくだけです。

ジャンルは、商品別一覧表の中の、3列目に書いてあるものです。

つまり、タイトル名をVLOOKUP関数で求めた、上記の矢印、2列目の2の数値を3列目の3に変更するだけです。

数式を、コピーして、ジャンルにカスタマイズして貼り付けましょう。後は、下にオートフィルして、ジャンルの完成です。数式は、カスタマイズして使用できるという事を覚えておきましょう。

HLOOKUP+LEFT

次に年代です。

年代のデータは、【リスト区分】シートの年代区分一覧表にあります。コチラは横並びになった、リストを使ったリストです。ココでは、HLOOKUP関数を使います。

又、予約コードから、左の1文字を抜き出す、LEFT関数を入れ子にします。

上記の数式を用います。今回は、検索地はB（文字）を抜き取っているので、VALUE関数で数値に表示形式を変える事をしません。

下に、オートフィルすると、年代が分かります。

ソフト形態【CHOOSE+MID】

予約コードから、ソフト形態を求めていきます。ここでは、【パッケージ版】又は【DL版】と区分します。
ソフトは、予約コードの左から2番目で1がパッケージ版、2がDL版と問題ではなっています。コチラを求めていきます。

今回は、CHOOSE関数と、MID関数を入れていきます。

CHOOSE関数は、Excelで数値が入力されたセルを参照して、指定したテキストを表示する関数です。最大254個の値から指定する事が出来る関数です。ココでは、CHOOSE関数関数を挿入し、【値1】には、【パッケージ版】、【値2】には、【DL版】と入力します。【関数の引数】ダイアルボックスを表示させると、入力した値にダブルコーテーションが付き、自動で文字として、認識します。

インデックスの箇所には、MID関数を使用します。

MID関数は、セルに入力されている文字列の指定した位置から、指定した数の文字を取り出す関数です。

この様な数式になります。OKボタンをクリックして、下にオートフィルしましょう。

すると、ソフト形態がわかります。

会員区分【VLOOKUP+MID+LEN】

次に会員区分を数式を使って表示させていきます。

リスト一覧シートの【会員区分一覧表】を使用します。

まず、VLOOKUP関数を挿入して、予約コードの文字数を数えます。すると10文字あります。この中の、会員区分一覧表の、S057の4文字を差し引きます。すると、残り6文字になります。

MID関数は、セルに入力されている文字列の途中から指定した文字数を取りだし、LEN関数で、文字列の文字数を抜き取ります。ココでは、6抜き取ります。

ここでは、この様な、VLOOKUP関数の数式となり、OKボタンを押すと、会員区別が分かります。

金額【VLOOKUP*予約数】

コチラの金額は、リストを参照した価格に予約数を掛けて計算します。ココでは、価格×予約数の掛け算という事がわかります。

価格とは、商品情報の一覧表の表の4列目に書いてあります。ですので、VLOOKUP関数を挿入します。

ですので、【タイトル名】のじゃばわんこと出た数式をコピーして、金額の所に貼り付け、2行目が4行目になっているので、矢印の箇所を4に書き換えます。その数式の【予約数】を掛け算します。

すると、この様な数式になり、金額が分かります。下にオートフィルして、他の金額も算出してください。

ANDとORの追加のシーン

上記の予約特典は、

「金額」が11,000以上の場合は、フォトブック
上記以外で、「ソフト形態」か「DL版」で、
かつ、会員区分が、「プラチナ」会員、「シルバー会員」の場合は「ノート」
上記以外の会員＝何も表示されない

と問題文に記載されています。

プラチナ会員、シルバー会員、一般会員は、リストに記載されています。

ここでは、金額が、11,000以上の場合は、フォトブックという事はIF関数を使います。

そうじゃない場合は、どうすれば良いのか？と言うと、IF関数を又、追加します。未だ、DL版、プラチナ、シルバーという、選択肢が残っているので、値が偽の場合は、又IF関数を挿入します。ポイントは、1回目のIF関数を追加する事、問題文で【DL版】、かつ、と書いてありますので、点に着目して、かつの言葉に分けておきます。

もう一つは、プラチナ又は、シルバーという形ですので、かつは、【OR】関数を使います。

AND関数の中でOR関数を使ったので、AND関数で確認して、IF関数に戻ります。

値が真の場合はメモ帳、そうではない場合は、何も表示しないので、キーボードの【ダブルコーテーション】を入力します。

すると、予約特典が分かります。下にオートフィルすると、全ての予約特典が分かります。