データの取り込みと集計 （手作業での操作例と、それをVBAで。）
| ■ Excel豆知識53 |
|
53-2 データの取り込みと集計（手作業での操作例）
最初に、②の「何らかの抽出を行って取り込むデータ」で、Aの「毎月1回取り込む」パターンでの手作
業で下の手順をひと通りやってみます。
出力されたデータに対して
1.どういうデータで構成されているかを把握する
2.そのデータに適した取り込み方法を選択する
3.集計しやすい形に整形
4.集計に必要な項目が不足していれば数式等を利用して項目を追加する
5.フィルタオプションで必要なデータを抽出
6.ピボットテーブルで集計
というような手順を考えます。
1.どういうデータで構成されているかを把握する
まず、出力されたデータがどういう項目とデータで成り立っているのかを把握します。項目数が多くて、
一目で把握できない時は、項目行とデータ数行をコピーして、「形式を選択して貼り付け」の「行列を
入れ替える」にすると見やすくなります。
2.そのデータに適した取り込み方法を選択する
このようにして把握してみて、例えば「頭ゼロも表示した数字のコードをきちんと表示したい」などの
場合にはCSVファイルをExcelで開いたのでは目的のデータを取得できませんので、外部データの取り込
みで目的通りにデータを取り込んでその後の作業につなげるようにします。（③のタイプのデータとして
53-10でやってみました。）
3.集計しやすい形に整形
4.集計に必要な項目が不足していれば数式等を利用して項目を追加する
この3.と4.については、今回のサンプルデータでは必要のない部分ですので、こちらの例を。
5.フィルタオプションで必要なデータを抽出
フィルタオプションの詳細については、豆知識42、豆知識43をご覧ください。
フィルタオプションで必要な項目のみを取得するには、抽出先にその必要な項目をつながったセル範囲
に設定します。「取込用」シートを抽出先にしようと思いますので、そこに上図で水色をつけた必要項
目を欲しい順に配置します。（つながったセル範囲であれば、項目の順序は問いません。）
また、「入荷地」のデータ中にある「テスト」というデータは不要なので、「テスト」以外のデータを
取得するように、「条件」シートに条件を設定します。
これでフィルタオプションで抽出する準備は出来ましたので、操作を始めようと思いますが、操作を始
める際に、抽出したデータを表示させたいシートを選択した状態から始めることが肝腎です。
今、「取込用」シートに表示させたいと思っていますが、例えば、元データのあるシートから始めたり、
「条件」シートから始めたりすると、下図のエラーメッセージが出て抽出することが出来ません。
フィルタオプションは、毎回毎回ダイアログボックスで設定する必要がありますので、せっかく設定し
て「OK」としたとたんにこのエラーが表示されるととてもがっかりしてしまいます。
なので、ここでは「取込用」シートを選択した状態から操作を始めます。
始める段階で選択しているセルがどういう場所にあるかによって、いろいろなエラーが出ることがあり
ますので、少し注意が必要です。
「取込用」シートの、項目名の欄につながっていないセルを1か所選択した状態で「データ」-「詳細設
定」をクリックします。
すると、下図の「フィルターオプションの設定」ダイアログボックスが出ますので、ここで「リスト範
囲」と「検索条件範囲」と「抽出先と抽出範囲」を指定します。（これをいちいち手作業で設定しなければな
らないので、少し面倒です。でも、マクロにすれば何てことありません。。あとでやってみます。）
「OK」とすると、下図のように「入荷地」が「テスト」以外のデータの必要項目だけが抽出されます。
「入荷日」が一番左に来ていますので、項目の順番も元データと左右が入れ替わっていても大丈夫なの
がわかります。
*↓******************************************************************************************↓*
ここで、ちょっとフィルタオプションで文字列を条件にする時の設定の仕方と結果を見てみたい
と思います。 なお、フィルタオプションの条件の設定方法はさまざまあります。
「入荷地」のフィールドには、下の7種類のデータが入っています。
（「テストのテスト」は、ここの説明のために1行だけ「テスト」を「テストのテスト」に変更したものです。）
今回の実際の操作では、元データから「入荷地」が「テスト」以外を抽出したいので、 <>テスト
という条件を使いますが、
「テスト」のみを抽出するためにはどうするのか、などもやってみたいと思います。
「テストのテスト」というデータを試しに入れてみたのは、「テスト」だけを抽出できるように思える
条件の設定で実は「テストのテスト」まで抽出されてしまうので、それをお見せしたかったからです。^^;
*↑******************************************************************************************↑*
6-1.ピボットテーブルのデータソースを準備する
毎月出力されたデータを取り込んで、そのデータを貯めこみ、ピボットテーブルで集計をしようと思い
ます。そうすると、毎月データを取り込んで、ピボットテーブルを更新しさえすれば、月ごとの集計が
すぐに見られます。
ということで、データを貯めこむシートを用意します。
今作業をしているデータは、不要なデータもある中から必要なものをフィルタオプションで抽出してい
ますので、抽出先のシートは、項目行が設定されているだけでデータが空の状態になっていることが
必要です。なので、取込用シートとデータを貯めこむシートを別に用意します。
もし、毎月出力されるデータを、抽出をせずにそのまま取り込んで大丈夫な場合には、データを貯めこ
むシートだけを用意して、そこに次々取り込むようにします。（この例は、後程マクロを利用する所で
やってみようと思っています。）
なお、下図の左右に並んでいるのは同じブックをウインドウ2つで表示しているものです。違うシート同
士の操作がある場合、こうして並べると作業がやり易いです。
（「表示」-「新しいウィンドウを開く」として、ウィンドウを2つにして、「表示」-「整列」で整列さ
せたものです。ブック名の後ろに：1とか：2とか表示されます。Excel2013では様子が変わりました。）
「取込用」シートの項目行を除いたデータ部分をコピーして、データ貯めこみ用の「Data」シートの現
在の最下行のすぐ下の行に貼り付けます。
「取込用」シートで、全体を選択するには、データ範囲のどこかのセルを選択して、Ctrlキーを押しな
がら、テンキーの*（アスタリスク）を押します。でも、今は項目行を除いた範囲を選択したいと思いま
す。
A2セルを選択して、Shiftキーを押してデータの右下隅のセルを選択してもいいですが、範囲が大きい場
合にはスクロールとかするのが大変です。なので、A2セルを選択して、CtrlキーとShiftキーの両方を押
しながら、↓の矢印キーを押すと、まずA2セルからA列の最下行までが選択されます。そのままの状態で、
CtrlキーとShiftキーの両方を押しながら、→の矢印キーを押すと、A2セルからデータの右下隅のセルま
での全部の範囲が選択されます。こちら
その範囲をコピーして、「Data」シートの現在の最下行のすぐ下の行に貼り付けます。
下図は、最初のデータなので、項目行のすぐ下に貼り付けましたが、次月分からは、今回貼り付けた
データの下に貼り付けるようになります。
その後、次回のために「取込用」シートのデータ部分はDeleteします。
上図の状態から、右のウィンドウを選択して、そのままDeleteすれば大丈夫です。
毎月データを貯めこんでいくシートにデータが入りましたので、そのデータをデータソースにしてピボッ
トテーブルを作成したいと思います。
毎月データが増えていきますので、データが増えたら増えた分も含めてピボットテーブルのデータ範囲に
なるように考えます。やり方は何種類かありますが、手作業でやるなら、一番簡単なのは「テーブル」を
利用するものだと思います。
Dataシートのデータ範囲のセルを選択して、「挿入」-「テーブル」をクリックします。
すると、データ範囲全体が認識されますので、「OK」とします。
すると、「テーブルツール」という新しいタブが出てきます。
ここの「テーブル名」を適当に変更しようと思います。
「集計用データ」としました。
データ範囲の外のセルを選択すると、「テーブルツール」のタブは消えます。
テーブルの範囲の右下隅には小さな印がついています。
（その印をマウスで掴んで、データ範囲を狭めたり広げたりすることもできます。）
この「集計用データ」というテーブル名を、ピボットテーブルのデータソースにしようと思います。
（後でマクロでやってみる時には、テーブルを使わずにVBAでデータ範囲に名前をつけて、それを利用したいと思います。）
6-2.ピボットテーブルで集計
テーブルの中のセルを選択した状態で、「テーブルツール」-「ピボットテーブルで集計」をクリック。
「ピボットテーブルの作成」のダイアログボックスが出てきて、データソースにテーブルの名前が入っ
ています。このままOKとすると、新規のワークシートにピボットテーブルの枠が出来ますが、今回は
「既存のワークシート」にチェックを入れ「ピボット」シートに作成したいと思います。
（「ピボット」シートを作っておいたのでそうするだけです。新規シートに作成して、そのシートの名前をピボット
用にしてもかまいません。）
「既存のワークシート」にチェックを入れ、「ピボット」シートのセルを選択して「OK」とします。
ピボットテーブルの枠が出来ますので、まず「入荷日」を行ラベルエリアに置きます。
ピボットテーブルについての詳しい説明はこちらをご覧ください。
日付が並びました。
今回は、月ごとの集計をしたいと思いますので、日付をグループ化します。
日付の所で右クリックし、メニューの「グループ化」をクリックします。
「年」と「月」でグループ化すればいいのですが、年と月だけにチェックを入れると月のフィールド名
が「入荷日」になるので、一応ここで「日」も選択して「OK」とします。
「年」「月」「入荷日」というフィールド名になりましたので、このうちの「入荷日」を削除します。
すると、「年」と「月」になりました。
これを列ラベルに置きたいので、ボックス間でフィールドをドラッグして移動します。
下図は、最初に「年」を移動して、次に「月」を移動しようとしているところです。
次に、行ラベルエリアに「入荷地」と「品名」を、「値」エリアに「金額」をドラッグします。
ピボットテーブルの形式がコンパクト形式になっていますので、表形式に変えたいと思います。
（別に変えなくてもいいですが。^^;）
こんなふうになりました。
あとは、毎月データを取り込んで、ピボットテーブルを更新すれば、月の集計が一目でわかります。
最初の月の作業はこれで終わりです。
6-3.次月分のデータを取り込みピボットテーブルを更新
次に、その次の月のデータを同様にしてDataシートへ取り込んでみます。
前月と同様に、フィルタオプションで必要なデータを「取込用」シートに取り込んで、項目行を除いた
データ範囲をコピーして、
「Data」シートの前月データのすぐ下に貼り付けます。
Dataシートのデータは「テーブル」になっているので、追加したデータも「テーブル」の範囲に自動的
に取り込まれます。
次月のために「取込用」シートのデータはDeleteします。
テーブルにはデータ範囲がちゃんと最終行まで認識されているのがわかります。
ピボットテーブルのデータソースは自動的に範囲が広がっていますので、ピボットテーブルの中のセル
を選択して「更新」をクリックします。ピボットテーブルは、データソースが広がっても自動的に
は更新されませんので、必ず「更新」してください。（VBAには更新するコードも入れます。）
下図のように、追加データも反映した集計が出来ました。
2年分を取り込んだ後は下図のようなピボットテーブルになります。
このような作業を、毎月手作業で行っても良いのですが、
フィルタオプションの設定などが結構面倒なので、次回はこれをマクロにしてみたいと思います。
