はじめに
受験が終わって卒業シーズンになりました。我が公立小学校でも2割位の児童が私立中学に進学します。
進学状況をExcelとパワークエリを使って集計してみます。
使うExcelのテーブルは、卒業生進路、中学カテゴリ、児童名簿の3種類です。このテーブル（Excelの表）を使って男女別の進学先(同一地区中学、国立、都立、私立中学)の人数を出します。
今回は、VLOOKUP関数やINDEX/MATCH関数の組み合わせといったワークシート関数を使わないで、パワークエリを使って集計表を作ります。
手順は次のようになります。
- 各テーブルのパワークエリを作ります。
- パワークエリとパワークエリをつなげます。
- ピポットテーブルで集計します。
使用する3つのテーブルを紹介します。
使用するテーブル
卒業生進路・テーブル
各児童の進学先の一覧表です。
名前は疑似データ、私立中学の名前は実在する中学の名前を借用しました。
卒業生のクラスは3クラスで、99名です。
中学校カテゴリ
悪中学校を区内公立中学、国立中学などに分けました。
名前は疑似データ、私立中学の名前は実在する中学の名前を借用しました。
児童名簿
集計で男女別の小計を求めるので男女の区分けがある児童の名簿を利用します。
卒業生進路のパワークエリ
パワークエリは表示する方法
- テーブル内のセルを選択
- データタブをクリック
- 「テーブルまたは範囲からデータの取得と変換」をクリック
はじめにテーブル内のどセルをクリックしてください。
パワークエリは表示する方法
- データタブをクリック
- 「テーブルまたは範囲からデータの取得と変換」をクリック
この手順を実行すると左図のような卒業生進路のパワークエリのエディタが立ち上がります。
今回はここまで行って、リボンの左端の「閉じて読み込む」をクリックして次のクエリに移ります。
中学カテゴリのパワークエリ
左図のテーブルを表示させたら、前回と同じに操作します。
- データタブをクリック
- 「テーブルまたは範囲からデータの取得と変換」をクリック
この手順を実行すると左図のような中学カテゴリのパワークエリのエディタが立ち上がります。
卒業生進路のときと同じように、今回はここまで行って、リボンの左端の「閉じて読み込む」をクリックして次のクエリに移ります。
児童のパワークエリ
左図のテーブルを表示させたら、前回と同じに操作します。
- データタブをクリック
- 「テーブルまたは範囲からデータの取得と変換」をクリック
この手順を実行すると左図のような中学カテゴリのパワークエリのエディタが立ち上がります。
最終的に必要なデータは、児童の氏名と性別なので、この2つの列を残して他のデータを削除します。削除するのはパワークエリのデータ場酒なので、元のテーブルには影響はありません。
コントロールキーを使って、氏名と性別の列を選択します。
リボン左の列の削除メニューから他の列の削除をクリックします。
氏名と性別だけが抽出されました。
ここにあるのは、全校児童の名前と性別のデータです。
次に各パワークエリを結びつけます。順序は
- 卒業生進路と中学カテゴリを中学校で結合します。
- 上の手順で作ったパワークエリの名前と児童クエリの名前を結合します。
クエリの結合
卒業生進路と中学カテゴリの結合
氏名と性別だけが抽出されました。
ここにあるのは、全校児童の名前と性別のデータです。
次に各パワークエリを結びつけます。順序は
- 卒業生進路と中学カテゴリを中学校で結合します。
- 上の手順で作ったパワークエリの名前と児童クエリの名前を結合します。
クエリのマージをクリックすると図のような画面が表示されます。
赤丸のプルダウンメニューをクリックします。
中学カテゴリのクエリを選びます。
2つのテーブルを結合させる列を選びます。
卒業生進路は進学先
中学カテゴリは中学校を選びます。
選んだら「OK」をクリックします。
「OK」ボタンを押すと2つのテーブルを結合され、左図の除隊になります。
ここで中学カテゴリの赤丸の位置をクリックします。
クリックすると何を表示させるかのメニューが表示されます。
ここでカテゴリを選んで「OK」をクリックします。
クリックすると中学校カテゴリにカテゴリが表示されます。
このあとは結合させたクエリに児童クエリを結合させて、性別を表示できるようにします。
その前に、ここまでの手順を動画で見てください。
一連のクエリ結合の動画
できたクーリエと児童のクーリエの結合
クエリのマージをクリックして、児童テーブルのクエリと結合しますす。
児童クエリを結合させて、性別を表示できるようにします。
卒業生進路の氏名を選択
児童クエリを結合させて、性別を表示できるようにします。
結合相手は児童を選択して、氏名をクリックします。
両方のクエリの結合相手を選択したら、「OK」をクリック
結合できたら、右上角のアイコンをクリックします。
結合できたら、右上角のアイコンをクリックした後に表示されるメニューから性別だけを選んで、「OK」をクリックします。
これで進路集計表を作るデータが揃いました。
リボンの左側の「閉じて読み込む」をクリックして終了します。
この後は、クーリエから進路集計表を作れますが、ここでは一旦進路テーブルを作ってから集計表を作ることにします。
クエリエディタの右にクエリと接続という窓があります。
前回の「閉じて読み込む」をクリックすると新しいクエリが表示されます。左図の場合は「卒業生進路（3）」です。
「閉じて読み込む」をクリックしても何かが表示されることはありません。
その代わり「卒業生進路（3）」を右クリックっするとプルダウンメニューが表示されます。
そのメニューの中から、「読み込み先…」をクリックします。
そのメニューの中から、「読み込み先…」をクリックして表示されるのが、左図のメニューです。
その中からテーブル、新規ワークシートを選んで「OK」をクリックします。
卒業生進路のテーブルが完成しました。
次はピポットテーブルを作成します。
卒業生進路のテーブルから、「挿入」「ピポッt-テーブル」「テーブルまたは範囲から」の順にクリックします。
次はピポットテーブルを作成します。
卒業生進路のテーブルから、「挿入」「ピポッt-テーブル」「テーブルまたは範囲から」の順にクリックすると、左図のメニューが表示されます。
テーブル名かテーブルの範囲をドラッグして指定します。
ピポットテーブルを表示するのは新規ワークシートを選んで。「OK」をクリックします
ピポットテーブルの設定画面が表示されました。
行に男女の性別、列に中学カテゴリカテゴリ、値に氏名を入れて人数が表示されるようにしました。
以上で、パワークエリを利用してた卒業生進路の表作りは完成です。
ピポットテーブル作成の動画
まとめ
これまでは、卒業生進路の最終テーブルを作るのはINDEX/MATCH関数の組み合わせを使っていました。
パワークエルは関数を使わずに同じことができるのが不思議な感じがします。これからもパワークエリの色々な応用を紹介できればと思います。
