ここでは、CS検定の表計算課題2のsample問題を解いていきましょう。
紹介記事の人物
橋本 由夏（はしもと ゆか）
Excelの作家（主に、フリーランスとして活動しています。）
著書：Excel厳選テクニック本を出版コチラから購入出来ます。
職 業：Excel作家、ExcelのYouTuber、Excel・Wordの講演
出身地：熊本県
趣 味：Excelでどれだけ時短して、早く仕事が終わるかを追求する
経 歴：民間、パソコンインストラクター、公的機関で勤務経験あり
CS検定表計算2級部門 sample問題の第2問の解き方②
今回は、上記の右側の表を埋めていきます。得に重視するのは、【タイトル別予約合計】【会員区分予約件数】です。
前回の復習は下記の記事をご覧ください。
【タイトル別予約合計】
合計と聞くと、SUM関数を使って、数値の合計をするイメージですが、2級では、単純に全体の合計ではなく、タイトル名の合計を表示させます。ココでは、SUMIF関数を使います。
SUMIF関数
SUMIF関数は、範囲と検索条件を使って、それぞれのタイトル名の合計を出していきます。具体的にやってみます。
タイトル別予約合計で、SUMIF関数を使う操作は、条件を指定して数値を合計したいときに使う関数です。タイトル別予約数合計のタイトル別抽出を行って行きます。
SUMIF関数を挿入し、範囲は、タイトル名を選択し、絶対参照にします。検索条件は、タイトル別予約合計のじゃばわんこのタイトル、合計範囲は、金額の合計を絶対参照にして、OKボタンを押すと、数式の答えがでますので、そのまま下にオートフィルします。
すると、タイトル別予約合計が表示されます。
又、タイトル名の予約数の合計のほかに、問題文はこう記載されています。
その数値の右横に漢字の【本】を付けて、表示させること、ただし、表示形式は、変更しないこと、、、、
表示形式を変更しないという事は、関数を使用します。【本】を繋げて表示する時は、数式バーに追加で【＆】です。これは、文字の前後をくっつける意味があります。文字は、ダブルコーテーションでくくり文字だと、Excelに認識させます。ですので、＆”本”と最後に入力します。
すると、タイトル別予約数合計のタイトル別に、本という文字が追加されます。
COUNTIF関数
次に会員区分予約数を求めます。ココでは、COUNTIF関数を使用します。
COUNTIF関数は、Excel で指定した条件に一致するセルの数をカウントする関数です。ココでは、会員区分予約数の会員区分が、どれだけ予約されたか？を抽出します。
ここでは、範囲は、【会員区分】を選択し、F4キーを1回押して、絶対参照にして、検索条件は、プラチナを指定して、OKボタンを押します。すると、プラチナの会員区分予約件数は、5という事が分かります。下にオートフィルして、他の、会員区分予約件数も出しましょう。
ユーザー定義
この数値は、表示形式から、5件と表示させます。
キーボードの【Ctrl】+【1】を押すと、セルの書式設定ダイアルボックスが表示されます。
【表示形式】タブから【ユーザ定義】をクリックして、【種類】を【0件】に書き換え、OKボタンを押します。
セルの書式設定ダイアルボックスを使用しても、数値の後ろに文字を表示させる事が出来ます。
金額上位3位を求める
上記の、金額上位の三位の金額のタイトル名と会員区分を出します。どの順番で出すか？がとても大事です。
分かりやすくご説明します。まずは金額を出します。
LARGE関数
LARGE関数は、Excelで、指定した範囲の数値のセルの中で、大きいほうから指定した順位の値を表示する関数です。
ココでは、1位～3位を出す関数です。その逆が、SMALL関数です。覚えておきましょう。
まず、金額を選択して、次の数式を入れます。
LARGE関数を挿入して、金額の列を指定し、絶対参照にして、L23を指定します。
L23のセルは、1なので、1番の数値が出ます。2番なら2番の数値、3番なら、金額の3番の数値が表示されるので、
OKボタンを押して、下にオートフィルします。
そうすると、金額が分かるので、コチラの金額を活用して、【タイトル名】と【会員区分】を求めます。
INDEX関数・MATCT関数
まずは、タイトル名を求めます。ココでは、INDEX関数と、MATCT関数で求めます。INDEX関数は、Excelで指定した行と列が交差する位置にあるセルの内容を返す関数です。
MATCT関数は、指定した範囲のセルの中から、特定の値が何番目にあるかを調べる関数です。
INDEX関数と、MATCT関数の詳細は、下記の動画で動作を確認してください。
ココでは、この様な関数を挿入して、OKボタンを押すと、タイトル名が分かります。
残りの会員区分は、タイトル名で設定した、列番号2を6に変えると、会員区分が分かります。
CEILNG関数と、COUNTIF関数
予約特典別発注箱数は、予約特典ごとの、予約特典数を求め、1箱入る、倍数のうち、最も近い値で切り上げる
という問題です。
まずは、CEILNG関数を使います。CEILNG関数は、絶対値に換算して最も近い値に切り上げられた数値を返します。
数値は、COUNTIF関数で、予約特典に一致するセルの数の基準をカウントして、ＯＫボタンを押します。そうすると、基準値の倍数のウチ、もっとも近い値に切り上げられます。（元の数値を超えない値に切り上げられます。）
その後、1箱の入数で割ると、発注数合計が分かります。
まとめ
負担が大きいので、関数が苦手な方は、関数の練習をして、スキルUPしてください。
コメント