Excelにおいて複数のリスト・テーブルを1つに結合して集計する手順をご紹介します。
Accessのユニオンクエリに近い作業をなすことができます。
準備
集計対象とするサンプルExcelファイル(ブック)は「レジ100,レジ200,レジ300」とします(TEST_Aフォルダーに用意)。
各ブックには「07月,08月,09月」という3つのシートを用意し、リストを書き込みます(3ブック×3シートで9つのリストを作成した)。
リストの開始位置はバラバラですが、項目名(=フィールド名)は「販売日 販売時間 商品名 販売数 レジ番号 金額」の6つで共通のものを採用します。
これらのリストを結合した結果を表示させる空のブックは「TEST_X」フォルダーで「集計データ00」という名前で用意しておきます。
元データをテーブルとして登録する
9つのリストを結合させるために、それぞれを「テーブル」として登録しておきましょう。「テーブル」ではのちの行の追加などにも自動対応が可能です。
リスト内をアクティブにし、【挿入】タブの『テーブル』ボタンを使います。
(サンプル図は『ブック「レジ100」、シート「07月」』での処理)
テーブルの範囲は自動的に認識されます。OKします。
リストをテーブルとして登録したあとは【テーブル デザイン】タブの『テーブル名』にテーブル名を付けておきましょう。
のちに認識させるときにわかりやすくなります。ここでは『ブック「レジ100」、シート「07月」』にあるシートに「T_レジ100_07月」と付けています。
各リストに以下のようなテーブル名を付けておきます(太字がテーブル名)。
- ブック「レジ100」
- シート「07月」→T_レジ100_07月
- シート「08月」→T_レジ100_08月
- シート「09月」→T_レジ100_09月
- ブック「レジ200」
- シート「07月」→T_レジ200_07月
- シート「08月」→T_レジ200_08月
- シート「09月」→T_レジ200_09月
- ブック「レジ300」
- シート「07月」→T_レジ300_07月
- シート「08月」→T_レジ300_08月
- シート「09月」→T_レジ300_09月
集計ブックでテーブルを読み込む
集計用の空のブック「集計データ00」を開きます。【データ】タブの『データの取得』『ファイルから』『Excel ブックから』を用いて分析データと接続します。
「レジ100」から取り込むようにします。
『複数のアイテムの選択」をオンにし、３つのテーブル「T_レジ100……」を選択します。次に『読み込み・読み込み先』を使用します。
「このデータをブックでどのように……」を『接続の作成のみ』としてOKします(『テーブル』も使えるが画面がシンプルになる)。
「クエリと接続」作業ウィンドウが表示され、3つのテーブルの読み込みが完了したことが表示されます。
他の2つのブックのテーブル(6つ)も読み込みましょう。
読み込んだテーブルを結合する
それではついに結合した表を作成します。【データ】タブの『データの取得』『クエリの結合』『追加』を使います。
「3つ以上のテーブル」を用いて読み込んだテーブルをすべて追加の対象としてください。
完成テーブルの名前を「プロパティ」に登録します。ここでは「結合形001」としています。最後に「閉じて読み込む」とします。
結合表が作成できました。上書き保存して閉じます。
元データの更新を集計結果に反映させる
レコード(行)の追加にも対応しています。ここでは「レジ300・09月」にデータを追加して上書きしてみました(21/9/30 15:00分を追加)。
「結合形001」を開いて「販売日・降順」として確認しても元データの更新はまだされていないことが目視できます。最新の情報を取得するには【テーブルデザイン】タブの『更新』を使います。
めでたく反映されました。
参考資料のダウンロード
次回……
コメント