エクセルピボットをBIツールのようにつかう – 1.2 –エクセルブック内 データ接続
エクセルをBIツールのように活用する方法を解説しています。使用するのはエクセルの標準機能 (マクロは使用ナシ) だけ!
今回は、エクセルブック内のデータソースへ接続します。
>サンプルデータ (テキストファイル) はこちらからダウンロードできます。
目次
接続方法
![](https://otukai.jp/wp-content/uploads/2021/06/xlsx-pivot-bi-data-connection_50-1024x522.png)
① エクセルブック内接続 (データソースがエクセルブック内にある状態)
・データソースファイルをエクセルブックで開きます。または、コピーしてエクセルへ貼り付けます。
・クエリでデータソースをテーブルデータへ変換します。接続の編集 (列の追加や削除など) はパワークエリエディターをつかいます。
・分析用のシートを追加してデータソースのシート (テーブル化されたデータ) へ接続したピボットを作成します。
・データの更新はエクセルブック内にあるデータソースのシート (テーブル化されたデータ) を更新します。
<メリット>
・データの参照がひとつのエクセルブック内で完結しているので、エクセルブックをシェアできます。エクセルブックをメールなどで送受信して利用できます。
<デメリット>
・データ更新のたびに、そのデータソースを使用しているすべてのエクセルブックのデータソースを更新する必要があります。シェアしたエクセルブックも同様の更新が必要になります。
② ネットワーク内 (データソースがアクセス権があるネットワーク内のどこかにある状態)
・ネットワーク内の (自分のパソコンも含めて) どこかに保存してあるデータソースファイルをエクセルへインポートします。
・インポートしたデータソースをクエリでテーブルデータへ変換します。接続の編集 (列の追加や削除など) はパワークエリエディターでおこないます。
・分析用のシートを追加してデータソースのシート (テーブル化されたデータ) へ接続したピボットを作成します。
・データ更新はインポートもとのデータソースファイルを更新します。
<メリット>
・もとデータソースファイルを更新することで接続しているすべてのエクセルブックのデータを更新できます。(更新をクリックする必要があります)
<デメリット>
・もとデータソースファイルへのアクセス権限がないひとへのシェアはセキュリティーの観点からオススメできません。シェアしたとしても、もとデータの更新は反映されません。
今回の投稿は「① ひとつのエクセルブック内接続」です。
手順のポイント
<データソースファイルをエクセルで開く>
データソースのファイル形式の多くは、テキスト形式かCSV形式だろうと思います。これらのファイル形式をエクセルで開いてエクセルで保存します。
<編集はパワークエリエディターをつかう>
パワークエリエディターというと何だか上級者向けのように感じる方がいらっしゃるかもしれませんが、難しいものではありません。エクセルの標準機能 (Vr.2016以降) で、手順のほとんどはクリックです。
パワークエリエディターはクエリを編集するための道具です。
パワークエリエディターを使用することで、データソース本体を加工・編集する必要がなくなります。パワークエリエディターで、データ形式変更や行列の削除を行ってもデータソース本体は完全に無傷です。
データソースをエクセルで開く方法
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_25-1024x542.png)
・新規エクセルブックを開きます。
・「ファイル」タブ
・「開く」
・「参照」をクリックしてデータソースファイル保管ディレクトリを指定します。
・「すべてのExcelファイル」の部分をクリック
・テキストファイルを選択します。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_26-1024x543.png)
・データソースファイルを選択します。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_27-1024x528.png)
・「コンマなどの区切り記号」のラジオボタン
・プレビューで文字化けを確認します。
・文字化けがあれば「元のファイル」を変更します。
・「次へ」
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_28-1024x529.png)
・「コンマ」をチェック
・プレビューで縦線の位置を確認します。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_29-1024x530.png)
・データ形式はすべて「標準」のまま
・「完了をクリック」
テキストファイルをエクセルへ変換する方法はこちら
クエリからテーブル化
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_30-1024x532.png)
・「データ」タブ
・「テーブル」をクリック
・範囲を確認してOKです。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_31-1024x543.png)
パワークエリエディタが開きます。日付が時間まで表示されているので、日付のみへ変更します。
・列名「日付」のアイコンをクリック
・「日付」を選択します。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_32-1024x543.png)
・「現在のものを置換」します。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_33-1024x545.png)
・「商品コード」列は「文字列」です。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_34-1024x543.png)
・クエリの名前をタイプします。
・「閉じて読み込む」をクリックします。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_41-1024x532.png)
新規シートへテーブルデータが作成されます。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_42-1024x532.png)
データソース (テキストをエクセルで開いたシート) へ戻って確認すると、パワークエリエディターで編集 (データ形式を文字列へ変更) した「商品コード」列のデータ形式は「標準」のままです。つまりパワークエリエディターで編集してもデータソース本体は完全に無傷といえます。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_35-1024x543.png)
テキストファイルをエクセルで開いたため、現在のファイル形式はCSVです。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_35-1-1024x543.png)
・ファイル形式をエクセルへ
・保存します。
ピボットグラフ
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_36-1024x527.png)
・「挿入」タブ
・「ピボットグラフ」を選択します。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_37-1024x530.png)
左側の窓が開きます。
・「外部データソースを使用」のラジオボタン
・「接続の選択」をクリック
右側の窓が開きます
・「テーブル」タブ
・クエリで作成したテーブルを選択します。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_38-1024x532.png)
ピボットグラフが開けば成功です。
・はじめにテキストファイルを変換したシート「data_sales_1」がデータソース
・パワークエリエディターでデータ形式変換したシート「sheet1」がデータ加工・編集
・「分析用pivot」のシートが加工・編集データへ接続しているシートです。
データ追加・更新
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_39-1024x530.png)
データの追加・更新はデータソースになっているシートで行います。
・追加する行を貼り付けます。
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_40-1024x528.png)
・「データ」タブ
・「すべて更新」をクリックします。
エクセルブックの構造
![](https://otukai.jp/wp-content/uploads/2021/05/xlsx-pivot-bi-data-connection_49-1024x568.png)
<データソース>
データソースへデータを追加するとテーブルデータのデータも追加されます。データソースを更新するとテーブルデータも更新されます。
<パワークエリエディタ>
テーブルデータはクエリを通過して作成されます。
・パワークエリエディターで編集した内容はテーブルデータへ反映されます。
・パワークエリエディターで編集した内容が手順を遡りデータソースへ反映されることはありません。
<テーブルデータ>
・テーブルデータを編集するとピボットへ反映されます。
・テーブルデータを編集してもクエリへは反映されません。
<ピボット>
・ピボットで計算軸を追加したり、列を複製してもテーブルデータへ反映されることはありません。