エクセルピボットをBIツールのようにつかう – 1.1 – データ接続とは

エクセルピボットをBIツールのようにつかう – 1.1 – データ接続とは

エクセルをBIツールのように活用する方法を解説しています。使用するのはエクセルの標準機能 (マクロは使用ナシ) だけ!
今回は、データソースを加工・編集する手順を省略できる「データへの接続」という考え方を紹介します。

エクセルピボットからデータへ接続する方法
ピボットのデータがどこにあるのか

 エクセルのピボットから接続できるデータソースの保管場所を2通り考えることができます。
① ピボットがあるエクセルのブック内
 データソースのシートが分析用ピボットと同一のブック内にあります。データソースのシートをクエリで編集、テーブルデータ化してピボットで分析します。

② アクセス権があるネットワーク内
 自分のパソコン内、社内ネットワーク内、外部サーバー内、いずれにしても常時アクセス可能なネットワーク内のどこかにデータソースファイルが保管されています。
 エクセルの外部接続機能でデータソースへ接続し、クエリで編集、テーブルデータ化してピボットで分析します。

 ②の接続方法でよりBI的にピボットをつかうことができます。

データソースへ接続するメリット
データソースへ接続

 11月の実績を分析しているエクセルブックへ12月の実績データを追加して分析したいと考えています。

<データへ接続していれば>
・接続している11月実績のデータソースへ12月実績を追加する。

 データソースを更新するだけで11月を分析しているピボットへ12月のデータを追加できます。
 データーソースへの接続という考え方を採用すれば、加工・編集、チャート・表作成のような手順は、最初に一度やっておけばよいわけです。

 いわゆる作業的な手順にかかる時間を短縮し、最新・大量のデータを、より深く・早く理解しようとするのがBIツール的な考え方なのだろうと思います。

ポイントは接続データの加工・編集をしないこと

 エクセルピボットでデータを分析・見える化するとき、接続しているデータソースの列を加工・編集してはいけません!
 例えば、POSシステムからテキスト形式・CSV形式で出力できるデータソースは加工・編集せずに、そのままの状態でエクセルから接続します。

何も足さない、何も引かない

 POSシステムから画像のデータソースをCSV出力できるとします。

<列を削除しない>
・取引時刻
・レジ番号
・グループコード
 仮に、これらを分析の対象にしないとしても削除しないことが原則です。
 

<列を追加しない>
・一品単価
・商品名
 あるいは、顧客名などの計算列を追加しないことが原則です。

追加するのは行だけ
行を追加

 システムの日付が更新されて11月8日のデータソースを出力したとき、列名を除くすべてを最終行以下へ追加します。
 データを追加するときに列の削除が行われていたら、計算列が追加されていたらどうでしょうか。ややこしいですね。

列を削除・追加しないと困る?

 データの編集はエクセルのクエリ、または、テーブルデータで行います。

<削除しない>
 不要の列を削除するのではなく、必要な列だけをつかいます。行についても同様です。クエリで削除します。

<追加しない>
・計算列
 一品単価のような計算列はピボットで計算フィールドを作成します。
・参照列
 リレーションシップ、マージ (結合) します。VLOOKUPのような関数を使用してデータソースへ列を追加する必要はありません。
・列の分割・ドッキング
 ピボット、あるいはパワークエリエディタをつかいます。データソースを加工する必要はありません。
・グループ化
 ピボット、パワークエリエディタ、またはマスタを作成してリレーションします。