関数・マクロ要らず！マウスでExcel検定試験の課題に挑戦！（1）ビジネススキル(サーティファイ)編
qExcelビジネススキル検定(サーティファイ)は、
２０２１年から開始された新資格です。
Excelの資格との違いは？
ピボットテーブルとパワークエリは、残念ながら、検定の対象外です。
実践では、もっと難易度の高い課題＝「累計」処理などもあります。
ピボットテーブルとパワークエリの実力を皆さんにご紹介します。
１．Excelの資格取得していますか？
Excel を実務で使いこなすために、資格を取得されている人が多いです。
代表的な資格がこの２つです。
・MOS（Microsoft Office Specialist）
Microsoftが認定する世界共通の資格試験。
延べ５００万人が受験したExcel・Word・PowerPointなどの資格試験の
代表格です。
Excelは、２つの資格です。
スペシャリスト（一般レベル）とエキスパート（上級レベル）
ピボットテーブルは「上級」高度な機能に位置付けられています。
・Excel表計算処理技能認定試験（サーテファイ）
累計合格者数も40万人を超え、データ集計からグラフ作成、関数使用まで、実務を想定した幅広い出題内容で、実用的な操作能力を測定・評価する試験で、３段階での資格認定です！
３級 ・・書式、グラフ、印刷、フィルター、並びかえ、テーブル機能
関数（基本的なもの）
２級 ・・ピボットテーブル、入力規則、名前の定義、条件付き書式、
リンクの設定
ほとんど使わない・文字列操作関数・・、VLOOKUP関数、
統合、３Ｄ集計、マクロ
１級 ・・SUMIFS関数・・、IFERROR関数
２．注目を集めるExcel®ビジネススキル検定サーテファイ！
２０２１年から開始された、ビジネスの現場に役立つ、課題解決型の
新資格検定として、注目されています。
操作だけでなく、実践的なスキルを修得して、応用力、考える力を
身につけたい方には、お薦めの資格です。
下位級のスタンダード級、上位級のエキスパート級の2種がありますが、
Excel表計算処理技能認定の資格＝知識の差では、ありません。
同じ課題でも、実務に役立つレポートは複数の解答方法が可能です。
そこで、速く、正確に、課題解決型の新資格が重視されるのです。
Excelビジネス検定公認テキスト 日本能率協会マネジメントセンター発行
から、内容について、確認してみました。
集計・編集については、様々な知識を総合的に使って行います。
2-1.集計・編集・修正するためのデータの整理方法
2-2.集計・編集・修正のためのヒント
・集計・編集・修正時によく使用する機能/関数
集計でよく利用される関数・機能が解説されています。
課題１～６ 課題の内容と解答で使用する関数・機能
解答は、自分のExcelの実力に合わせて行います。
計算等の正確性と、所要時間＝効率性で、評価されます。
３．ピボットテーブル＆パワークエリで挑戦！
ピボットテーブルとパワークエリは「想定外」
◎テーブルは集計機能としての扱いです。
◎ピボットテーブルは、範囲から作成で、テーブルから作成していない。
集計方法の変更で合計⇒平均、個数は紹介されていても、
計算の種類・・累計、構成比、順位や、
集計フィールド・・自分で数式作成 は、紹介されていません。
◎パワークエリは取り上げられていません。
パワークエリとピボットテーブルでも解答できます！
検定試験では、６０分の制限時間で３問です。１問２０分。
パワークエリとピボットテーブルでも、平均１０分で解答できました。
課題１～６ 関数・機能は、２つのツールで代替できます！
４．私の解答例：課題３ を紹介します！
家具屋さんの販売実績を集計する課題です。（データは変更しています）
★★スタンダード級で、基本的な知識で解答できます。
サンプルファイルのダウンロードできます！
前提：私は、「テーブル化」しています。
理由は、参照範囲が名前で使える⇒構造化参照
集計行の表示、メンテナンスがしやすくなります！
①基本解答・・関数を使う方法・・商品リストで集計する
販売数量の集計で、SUMIF関数を使うには、参照範囲を絶対参照します
=SUMIF(販売リスト!$E$3:$F$37,商品リスト!B4,販売リスト!$F$3:$F$37)
テーブル化すると、列の名前で設定できます！
=SUMIF(販売リスト[商品番号],[@商品番号],販売リスト[販売数])
②別解答・・VLOOKUP関数＋ピボットテーブルで集計する
販売実績（日々の明細データ）に、
商品マスターの情報をVlookup関数で検索
→ピボットテーブルで集計しました。
テーブル化すると、データ範囲が変更されても自動で設定されます！
課題の解答は、同じです。どこが違うのでしょうか ？
①基本解答・・すぐに回答できる。手順が簡単。
②別解答・・データ件数が多くなり、若干手がかかる。
私が、手がかかる方法を選らぶのは、「データ分析」です。
ピボットテーブルを使うと、総計、商品別の集計もできます。
さらに、日別、取引先別、商品別などのデータ分析が可能になります。
レポートの作り方を、一番効率的なつくり方をしたいですね。
でも、実務では、追加レポートなどが求められます。
そういう要望に応えられるのが、「ピボットテーブル」です。
③別解答の追加 ・・ パワークエリ、ピボットテーブルで解答
VLOOKUP関数は、セル毎に検索を行います。
パワークエリのマージ(結合)で代替が可能です。
照合列は複数可能です。
また、一度に複数項目の検索を行えるので、大変便利です。
④まとめ 実践での要求には十分でない！
１回限りの解答なら、関数・機能の方が早いです。
しかし、「実践」＝繰り返し行うようになると、
マクロ化など、高度な対応も必要になります。
実務では、様々なデータ分析が求められます。
ピボットテーブルは、その要求に応えることができます。
パワークエリ＆ピボットテーブルは、
元データを新しくしても、「すべて更新」クリックするだけで、
集計作業が完了します。
こうした要望に応えるものですが、検定試験の対象外です！
5. 課題の難易度をアップ⇒「累計」に挑戦！
販売実績 2024年7月に、当月８月分 データ追加・・累計で実績を把握。
当月は、随時最新データで集計するが、重複しないようにする。
レポート 月・日別、商品別、得意先別に管理できるようにする。
この課題は、関数・機能だけでは対応できません。
マクロ・プログラミングが必要な課題になります。
私は、VBAの資格は持っていませんが、セミナーなどで勉強してきました。
Excel VBAの資格検定 エキスパート
ＭＯＳエキスパート、Excelサーテファイ検定よりもは高度な検定です。
ベーシック ビジネスで一般的に利用されるレベルのスキル
スタンダード ITプロフェッショナルなどに求められるより高いレベル
CopilotでVBAマクロを提案してもらいました。
質問 日付、得意先、品名、販売数量、売上金額のデータを含む販売データを統合するためのVBAコードを以下に示します。このコードは、得意先、商品名と日付をキーとして重複を避けながら累計販売データを更新します。
手順
データの準備:
シート1に前月までの累計販売データがあるとします（例：セル範囲A1:E10）。
シート2に当月の販売データがあるとします（例：セル範囲A1:E10）。
VBAコードの作成:
ExcelでAlt + F11を押してVBAエディタを開きます。
新しいモジュールを挿入し、以下のコードを貼り付けます。
Copilot すごいですね！
理解してカスタマイズするのは大変！
最新データを１行毎に読み込んで、累計データを全部参照して、
データがあればそのまま、無ければ追加するという処理です。
データ件数が多くなると、非効率なプログラムのようです！
⇒フィルタ―やテーブルなどの機能を使うことも考えた方が良いですね。
では、パワークエリで「累計処理」を行います！
パワークエリは、マウス操作だけで、元データを編集・加工ができます。
関数・マクロを使いません。
最新累計の処理ステップ、マウス・クリックして設定！
「詳細エディター」で確認すると、Ⅿ言語で設定を確認できます！
マクロ・ＶＢＡと比べると、ものすごくシンプルです。
列単位で設定し、「ノン・コーディング」です！
ピボットテーブルも、マウス操作でデータ分析が可能です！
関数・マクロを使いません。グラフ化もできます。
５．今回の挑戦のまとめ！
サーティファイの課題に挑戦して、気づいたことがあります。
１回限りの課題＝表づくりなら、Excel関数・機能が早いです！
でも、実際の現場では、繰り返し作業や、データ分析のためにレポートを作ることが多いです。
ピボットテーブルとパワークエリの組み合わせは、非常に役立つことを、実感しました。
「実践」・・学んだ理論 や 知識 を 具体的な 行動 に移すことです。
Excel・・関数、マクロ・・沢山の知識を学びますが、個人差が大きい！
レポート表・セル単位で作成しますが、関数・機能の選択で優劣が！
エクセルＢＩ・・関数、マクロ要らず！ ピボットテーブル＆パワークエリ
マウス操作で、データ活用・データ分析 汎用性
次回は、高度なグラフ編・・ピボットグラフ！
実践事例を紹介しています！サンプルも公開！
ストアカ講座でセミナーを開催しています！
ｎｏｔｅで掲載しているサンプルや、今回の記事のサンプルの解説で、
皆さんの実践スキルを身につけるお手伝いをします！
この記事が気に入ったらサポートをしてみませんか？