パワー ピボット。 Power Pivot(パワーピボット)入門 #01 | Power Pivotの概要

PowerPivotのインストールとデータのインポート

パワー ピボット

こんにちは、会計士KOです。 今回は、 絶対に誰でも分かるPowerQuery,PowerPivotに関する記事の第2回を更新していきたいと思います。 はPowerQueryの基本的な使い方を説明しました。 PowerQueryを使えば、データの取り込みから加工がかなり楽になったかと思います。 特に、 基礎データを毎月、定常的に加工するなどの作業はPowerQueryで一瞬で解決できますね。 今回は、これに加えて、 PowerQueryで複数のデータを連携させる方法とPowerPivotを使って一瞬で必要なテーブルを作成したり、グラフとして視覚化することを目標にしていきます。 なお、この記事はPowerQueryについても復習を兼ねていますが、 PowerQueryが分からないとどうにもなりませんので、是非、まずは以下の記事からPowerQueryについて勉強してください。 今回の成果物イメージ まずは、成果物をお見せします。 PowerQueryとPowerPivotを組み合わせれば、読み込むデータを更新するだけで、一瞬でテーブルやグラフを更新することが出来ます。 従来は、基データを手作業で加工して、そして加工したデータをグラフにして、テーブルを組んで、、、といったことが必要になるかと思いますが、 以下のように、 データを最新版のものに置き換え、「すべてを更新」を押すだけで、チームごとの売上を示すグラフを一瞬で半期分更新したり、 「すべてを更新」を押すだけで、取引先別の売上推移のテーブルを一瞬で更新したり出来るんです。 これ、やっていることは、 読み込むデータを最新のものに更新し、SDのフォルダに格納して更新 しているだけです。 必要な集計やグラフ化、テーブル化など一度、PowerQueryとPowerPivotをちゃんと組んでしまえば、それ以降はデータを更新し続ければ良いのです。 これがPowerQueryとPowerPivotの使い方です。 取引先別の分析や、取締役会への報告資料など、毎月、グラフやテーブルを作っていらっしゃる方、ぜひ、その作業をPowerQueryとPowerPivotに置き換えましょう。 PowerQueryの使い方 さて、PowerQueryの復習も兼ねて、 今回は複数の外部データを取り扱う方法も紹介していきます。 PowerQueryのすごいところの1つは、 取り扱えるデータが単一ではないこと。 裏でやっていることの基本はデータベース操作と同じなので、データ間の連携さえ、ちゃんと設定されていれば、 いくらでもデータを連携して抽出したり、集計したりすることが出来ます。 複数の外部データに接続する では、PowerQuery を使って複数の外部データに接続してみましょう。 基礎データの前提 (今回使う基データ:前回と同じ) 取り扱うデータは前回の記事と変わっていませんが、前提の確認です。 青字で前提となっているところを確認してくださいね。 チーム構成という設定の追加 今回はこれに加えて、 担当者ごとに各社横断的なチームに所属している という設定があったとします。 イメージは以下です。 しかしながら、 基データには、チームというデータ列はありません。 また、 チームの人数もバラバラです。 このため、 この計算にはどこかのタイミングで担当者ごとのチーム列を追加する必要があるのですが、かなり手間ですよね。 今回はデータの都合上、人数はかなり少ないですが、これが100人、1,000人となっていくとさらに集計は面倒くさくなります。 この点、 PowerQueryを使えば、簡単にそのデータを結合することが出来ます。 以上のチーム構成表を以下にリンクしておきました、今回はこちらを使っていきます。 基データを保存する まずは、 上記のデータをSDデータと同じフォルダに保存して下さい。 このSDというフォルダからすべてのデータを読み込ませていきます。 実行エクセルでチーム構成データを連携する 次に前回の記事で作成した実行エクセルを使って、前回と同様に チーム構成データを取り込みます。 データタブから、データの取得を選んで、ブックからを選びましょう。 データの取り込みウインドウが開いたら、以下のようにSDフォルダに格納したデータを選んでインポートします。 チーム構成. xlsxにはSheet1しかありませんので、該当のシートを選択し、読込を実行します。 読込が完了するかと思うので、クエリと接続という画面に以下の通り、 Sheet1が読み込まれたことが分かりました。 しかし、 相変わらず要らないデータまで入ってしまうので、このデータを削除していきましょう。 データとクエリを右クリックすると、編集ボタンが出てくるかと思いますので、編集ボタンを押して、以下の通りの画面を選んでください。 不要なデータを削除する まずは、 不要なデータが入っているので、該当のデータを削除しましょう。 エディターウインドウが開いているかと思いますので、 ホームタブから行の削除を選び、上位の行の削除を選択してください。 5行分が余計なので、5行分を削除すればよいですね。 しかし、まだ ヘッダー(見出し)が変ですね。 本来は1行目が見出しの役割を果たすべきです。 次に1行目をヘッダーとして使いたいので、 左上のテーブルマークのアイコンを右クリックして1行目をヘッダーとして使用を押してあげましょう。 このデータで、閉じて読み込むを押せば、必要なデータになったんじゃないでしょうか。 ほら、こんな感じですね。 必要十分なテーブルになっているかと思います。 クエリとシートの名前を変更する 読み込ませるデータが増え、クエリが多くなると訳が分からなくなってくるので、読み込まれたデータのクエリの名前を変更しておきましょう。 クエリのタブからプロパティを選択して、 クエリ名を変更すれば、 名前が変更できるかと思います。 以下のような感じですね。 また、 シート名についても変更をしておきましょう。 イメージとしては、以下の通りです。 担当者というキーに紐づけてチーム構成シートを参照すれば、その担当者のチームが分かりますよね。 PowerQueryを使えば、このように複数のデータを連携することもできます。 これを行うことによって、PowerPivotとの連携が出来ることになります。 クエリタブから、読み込み先を選んで、 出てきたウインドウから、このデータをデータモデルに追加するを選びます。 なにやら沢山ウインドウが出てきますが、無視して取り込んでください。 なお、Pivotテーブルを作った後にこの操作をするとせっかく作ったデータが初期化される可能性があるので、ご留意ください。 リレーションシップを作成する では、いよいよ 2つのデータ間を連携させていきましょう。 ク エリツールから、以下のようなリレーションシップというボタンを押してください。 そうすると、以下のようなウインドウが開くので、新規作成ボタンを押しましょう。 新規作成を押すと、さらに以下のようなウインドウが開きますので、 テーブルに データモデルのテーブルであるSDと、連携させる担当者列を選び、 そして、 関連テーブルにおいて、チーム構成テーブルと、関連列に担当者列を選択してください。 そうすると以下の通り、リレーションが出来上がります。 イメージとしては、 最初に示した以下の通り、2つのテーブルがつながった感じです。 ちょっとした型のデータ修正をしておきます PowerPivotを使う前に、SDデータに修正したいデータが発見されたので、ここで修正をしておきます。 実は SDデータの粗利のデータ型が数値データではなく、文字列データになっていました。 後程Pivotテーブルの集計をしたいので、ここで数値データに修正をしておきます。 SDデータを開いて、クエリの編集を選び、粗利列を右クリック、型の変更から、整数を選択しておいてください。 PowerQueryはこのようにいつでも事後的な変更が出来ます。 連携が完了したらいよいよ、PowerPivotの領域です。 PowerPivotの使い方 ピボットテーブルを作成し、PowerQueryで作成したデータベースと接続する では、 いよいよピボットテーブルを作成し、PowerQueryで作成したデータベースと接続していきましょう。 ピボットテーブルの挿入 では、まず ピボットテーブルを作成します。 挿入タブからピボットテーブルを選びましょう。 こちらをクリックすると、以下の画面が開くかと思いますので、 このブックのデータモデルを使用するにチェックを入れてください。 このデータモデルを使用するにチェックを入れることで、ピボットテーブルが常にPowerQuery で作ったデータモデルとデータ連携することとなります。 また、作成時には、新規ワークシートを作成するにチェックを入れてください。 必要であれば、既存のシートから挿入を選んでくださっても大丈夫です。 新しいピボットテーブルが出来ましたね、以下の通りです。 テーブル間連携は出来ている? テーブル間連携が出来ているか、確認してみましょう。 ピボットテーブルの編集画面を見ると、以下のようになっているかと思います。 データモデルのインポートだけではなく、それに紐づいたチーム構成データについても取り込まれていることが分かりますね。 チームごとの売上テーブルを作成しよう 次に、 出来たピボットテーブルを利用して、適当にデータを選び、データをテーブル表示させてみましょう。 例えば、以下のように選ぶと、 チームごとの売上が月次で表示できますね。 ピボットテーブルの使い方の詳細については今回作成するもの以外は省略するので、ぜひ色々試してみてください。 視覚的かつ直感的にデータを扱えるようにしている機能ですので、ピボットテーブルについては、 習うより慣れろ です。 もし分からない方がいれば、是非色々と検索してみてくださいね。 なお、 上記のチームごとの売上のデータは後程、データを更新する項目で利用するので、別シートで取っておくと楽かもしれません。 チームごとの一人当たり粗利を集計しよう-基礎となるテーブルの作成練習 では、 今回の目的の一つであるチームごとの一人当たり粗利を集計してみましょう。 ピボットテーブルのフィールドから、以下の通り、表示項目を選択しましょう。 すると、 月ごとの合計粗利がチームごとに集計されるかと思います。 チームという項目はそもそもSDデータにはありませんでしたが、上記のデータ連携をしたことによって追加できるようになっていますね。 もし、期間のデータが正しく表示されていない方は、 ピボットテーブルの上で右クリックをして、以下のように年と月のデータでグループ化をすると同じようなデータが得られるかと思います。 グループ化を選んで、月と年を単位に選択します。 チームごとの一人当たり粗利を集計しよう-基礎となるグラフの作成練習 次に、マストではないですが、練習がてら ピボットグラフを作成します。 ピボットグラフとは、その名の通り、ピボットテーブルに連動したグラフのことです。 ピボットテーブルのタブから、ピボットグラフを選択しましょう。 今回は 棒グラフを選択いたしましたが、そのまま選択すると、以下の通りとなります。 色が分かりにくいので、 右クリックをして色を更新しましょう。 これでわかりやすくなりましたね。 これで 月ごとのチームごとの粗利の推移が確認できます。 あれでも待ってください。 このままでは、1人当たりの粗利が計算できませんね。 正直、この程度であれば、計算された粗利の合計額からチーム人数で電卓で計算してしまったほうが早いのですが、今回はPowerQueryとPowerPivotの使い方を知ることがテーマです。 PowerQueryとPowerPivotの機能を利用して、ピボットテーブルに1人当たりの粗利を計算できる項目の追加をします。 これがメジャーです。 チームごとの一人当たり粗利を集計しよう-メジャーを追加する 今回は上記の問題を解決するために、 ピボットテーブルに、 メジャー という計算データを足していきます。 こちらを押すとメジャーの編集画面が開きます。 そこで、 「' シングルクオーテーション)」を打ち込むと必要なデータを選ぶことが出来ます。 (DAX式については正直、これくらい出来ればあんまり困りません。 ) 簡単な計算や集計であれば、 ここから使えますので必要そうなデータを選択して式を完成させましょう。 シングルクォーテーションを選ぶと、合計粗利と、担当者数のカウントというのがありましたので、こちらを選択し、割り算していきます。 以下のような画面ですね。 メジャーの名前についても、「1人当たり粗利」と記載してOKを押しましょう。 そうすると、メジャーが追加されています。 以下の通りにピボットテーブルに表示させると、一人当たり粗利が計算できました。 これで ブルーチームが1番稼いでいることが分かりましたね。 では、 最後に冒頭に見せたようにデータを更新したら自動的にグラフやテーブルが更新されるように設定していきましょう。 PowerPivotのデータを一瞬で更新する 今回は、 チームごとの売上データを表すグラフと、取引先別の売上推移テーブルを一瞬で更新していきます。 想定としては、 2021年3月末時点までだった基データが半年分、つまり2021年9月時点まで更新された例を想定しています。 以下の通りです。 更新後のデータを置いておきました。 こちらです。 やることはPivotテーブルを組んで、データを更新するだけ とはいっても、やることは、データを格納しなおして、更新するだけです。 使用したデータは、上記の 「チームごとの売上テーブルを作成しよう」で作ったピボットテーブル に加えて、 ピボットグラフを表示したものです。 更新後SDデータを、SDフォルダに格納しましょう。 そして、 すでに組んである実行済エクセルを開き、すべて更新を選択です。 そうすると、 以下の通り、古いSDではなく、最新のデータまで更新されたSDに読み取りデータが置き換わり、グラフが自動で更新されます。 また、 得意先別のデータについても、ピボットテーブルで以下のように事前に得意先別の売上推移テーブルを作成しておけば、 最新のデータを格納して、すべて更新を押せば、すべて更新されます。 こんな感じですね。 このように、PowerQuery、PowerPivotは、 一度、操作手順や、テーブル、グラフの作成手順を決めてしまえば、あとはデータを更新するだけ で、 超簡単に集計やテーブル、グラフを作成できます。 まとめ ちょっと長くなってしまいましたが、 PowerQueryの使い方、PowerPivotの使い方、少しは伝わったでしょうか。 なんとなく、すごさが伝わってくれれば、そして無駄な作業をしている方が少しでも減ってくれれば幸いです。 詳しく知りたい方は、以下も是非ご参考ください。

次の

Excelでピボットテーブル使う人に全力でPowerQuery(パワークエリ)をお勧めしたい

パワー ピボット

毎月や毎四半期の定常業務として・・・ ・毎月、販売データを販売管理システムからダウンロードして、 ・納品完了・失注などの案件ステータスをリスト機能で抽出し、 ・商材や会社名をIF関数やCOUNTIF関数で振り分け、 ・粗利や利益率を示す列を計算式で追加し、 ・そして取引先別の売上や分析を行う 等の煩雑な作業をしていたりしませんか? 定常業務だけれども意外と面倒くさい。 一回だけ作業するならあんまり面倒ではないと思うのですが、例えば、毎月、毎四半期となると無駄な時間がかかるように思います。 この点、エクセルの隠れた 超優秀機能であるPowerQuery,PowerPivotを利用すると、利用するデータは更新されていくけれど、その加工が面倒、、、といった状況を一瞬で解決できます。 しかも、 特別な知識は特に必要ありません。 VBAやマクロには取っつきにくいという人も超簡単に使えるので是非習得して欲しいと思っています。 今回の記事ではまず先に、 PowerQueryの使い方について説明していきます。 また、想定としてはExcel2018を想定して記載していきますが、基本的にExcel2016では同じような画面での操作が可能なので、ぜひ参考にしつつ、適宜読み替えて確認をしてください。 PowerQuery(パワークエリ)とは PowerQuery(パワークエリ)とは、MicrosoftOfficeのExcelが提供しているエクセルの機能の一つで、エクセルシートと、外部データとの連携や、連携の際のデータの加工、列の追加や抽出等を定型化する機能です。 連携するデータとしては、 xlsxファイルや xlsファイルだけではなく、 CSVファイルやWeb上のAPIで提供されている JSONデータなどとの連携も簡単に行うことが出来ます。 また、マクロやVBAなどとは違い、データの連携や変更という点に特化し、グラフィカル(視覚的)に操作を行うことが出来るため、初心者でも非常にわかりやすく操作することが出来ます。 パワークエリの主な機能は、データの接続、変換、結合、管理です。 今回は、そのうち、 接続と変換について説明をしていきたいと思います。 PowerQueryで出来ること 最初に実例をお見せします。 想定は、毎月、販売管理ソフトから生成される販売データをダウンロードし、必要な情報(会社区分、粗利)を追加したうえで、会社別の月次売上推移分析、取引先別の月次売上分析を行うといった事例です。 要件は以下の通りです。 クリックで別ウインドウが開くのでぜひ拡大してみてください。 これ、この加工が一回だけだったらいいと思うんですが、毎四半期同じことを繰り返すとなるとかなり馬鹿らしくないですか? 冒頭にも説明した通り、PowerQueryを使えば一瞬で解決が出来ます。 例えば、 以下は基データにないA00000というデータが追加された場合に再度、同期を行い、データを処理しているものです。 基データ(下のエクセル)には、会社判定カラム、粗利カラムはありませんが、同期を行ったことにより、会社判定列、粗利列を追加したうえでデータを取得できていることが分かります。 今回サンプルでお見せしているのは1行だけですが、 実質的に何行足されようと更新ボタンを押せば任意のデータを取り出すことが可能です。 ちょっとだけ便利さが伝わったのではないでしょうか。 では、以下で説明をしていきますね。 PowerQueryの使い方 今回、PowerQueryの使い方として説明するのは以下の4点です。 今回、説明する内容 ・外部データの接続 ・外部データの変換 ・任意の列の追加 ・操作の順番変更、取り消し では、実際に始めていきましょう。 外部データに接続する データに接続します。 どのようなデータを利用してもいいのですが、 同じように作成したい方は、以下に同じデータを用意しました。 今後、 以下のエクセルは基データと呼ぶことにします。 手順を学べば、色々なデータで同じことが出来るので、眺めた後に自分の手元のファイルで試せそうな場合は、そちらで実行してみてください。 基データを保存する まずは、 基データを分かりやすいように保存します。 今回は PowerQuerySD. xlsxという名前で保存することとします。 なお、SDはソースデータの略です。 また、シート名も分かりやすいように保存をしておきましょう。 こんな感じですね。 作業フォルダと実行用エクセルを作成する 次は、 基データを格納する任意のフォルダを作りましょう。 今回はデスクトップ上に PowerQueryというフォルダを作成し、その フォルダの中にSDというフォルダを作成し、上記の基データを格納しています。 また、その次に、 PowerQuery実行. xlsxという新しいエクセルを作成し保存しています。 このエクセルが実際に作業を行う用のエクセルです。 今回はこのエクセルを 実行エクセルと呼ぶことにします。 実行エクセルで外部データを連携する では、次に 実行エクセルで外部データ(=基データ)を連携していきましょう。 実行エクセルを開くと、白紙のシートが出ているかと思いますので、以下の画面のように データタブを押してください。 そうすると次に、 データの取得というボタンがあるので、以下のようにブックからを選択します。 データの取り込みというウインドウが開きます。 データを格納したアドレスを指定して、基データエクセルをインポートしましょう。 データの連携(仮)完了 インポートすると以下のような画面が現れます。 これで取り急ぎ、 データの連携は完了です。 でも待ってください、 前提や要求などといった不要な要素がそのまま取り込まれてしまっていますね。 このため、見出しには(仮)をつけています。 以下にて、使えるようにデータの変換をしていきましょう。 不要なデータを削除する まずは、 不要なデータが入っているので、該当のデータを削除しましょう。 エディターウインドウが開いているかと思いますので、 ホームタブから行の削除を選び、上位の行の削除を選択してください。 今回は上位3行が邪魔なので削除してあげましょう。 そうなると、以下の通りとなると思います。 しかし、まだ ヘッダー(見出し)が変ですね。 本来は1行目が見出しの役割を果たすべきです。 左上のテーブルマークのアイコンを右クリックして1行目をヘッダーとして使用を押してあげましょう。 これで、 必要なデータになったはずです。 そうすると、 納品完了のみの列となったはずです。 データ型を変換する また、現状のステータス確定日付が数値表記になっていますね。 すると、以下の通りになったはずです。 ここまでできた方は、 左上から を押して閉じましょう。 以下のようにデータが変換され取り込まれていると思います。 データの 変換については、以上です。 次は、 必要な列を追加しましょう。 任意の列を追加する 次は、 任意の列を追加する必要があります。 要件に記載したように、 ・会社分類を行い、 ・粗利の計算をしたい ですね。 条件列を追加する-会社分類を判定する また、 クエリと接続画面で右クリックし、編集をクリックしましょう。 同じようにPowerQueryエディターが開いたかと思います。 まずは、 列の追加タブを押して、条件列の追加ボタンを押します。 ボタンを押すと、以下の画面が開きますので、必要な条件を選んでいきます。 今回は、案件番号が、 ・Aから始まれば、会社A ・Bから始まれば、会社B ・Cから始まれば、会社C という分類なので、以下のように条件を追加していきます。 これでOKを押すと、以下の通りになるかと思います。 案件番号A〇〇〇〇〇とB〇〇〇〇〇の境目で正しく会社の分類が判定されていることが分かります。 カスタム列を追加する 次は、 粗利を計算する列を追加してあげましょう。 条件列の追加をした際と同じように、 列の追加を押して、カスタム列を押して下さい。 出てきた ウインドウに必要な関数を打ち込んでいきます。 注意点としては、使用できる列は限られており、セルを選択したような計算をしたい場合は、右の「使用できる列」というウインドウから選択を行う必要があるという点です。 また、実際にはDAXという計算式が利用されているのですが、今回DAXの話は省略します。 計算式を打ち込んで、 OKを押すと、粗利列が追加されているかと思います。 計算された結果も正しいですよね。 ここまでできた方は、 左上から を押して閉じましょう。 これで 会社判定、粗利が計算された列が追加されたデータを作成することが出来ました。 操作の順番を変更する、操作を取り消す 実は今まで適用した操作は、 PowerQueryエディターのクエリの設定>適用したステップにすべて保存されています。 ドラッグアンドドロップで適用する順番を変更したり、 ボタンを押せば、誤って操作した際の操作を取り消すことも可能です。 データを更新する データを更新したい場合には、 基データを更新し、データタブから、すべて更新をクリックすれば上記の適用したステップが更新後のデータに反映され、新しいデータを得ることが出来ます。 試しに好きなデータをランダムに打ち込んで、再度読み込ませてみてください。 冒頭でお見せした以下のGifは、案件番号A00000から始まるデータを追加し、データを更新したものです。 ちゃんと、 会社判定が自動で走り、粗利の計算がされたうえでシートが作成されているのが分かりますね。 まとめ 次回は、 PowerQueryを利用して取り込んだデータを基に、PowerPivotを使って毎月のグラフを毎月、自動更新したり、取引先別の分析を毎月、自動更新したりといった手順をご紹介します。 VBAやマクロを利用するよりも何倍も簡単に使える機能、PowerPivot、PowerQuery、ぜひこの記事を参考に使いこなしてみてください。 自分でより深く学んでみたいという人は、以下の書籍もどうぞ。

次の

作業が倍速になる、本当に使うべきエクセルショートカット7選

パワー ピボット

2020. お客さまにはご迷惑をおかけいたしますが、何卒ご理解賜りますようお願い申し上げます。 詳しくは営業時間変更詳細をご覧ください。 店頭の告知等もあわせて、ご確認をお願いいたします。 古川駅• 陸前原ノ町駅• 福島駅• 郡山駅• 会津若松駅• ニュース・イベント• 2020. 対象店舗は下記ポスターが目印です。 あらかじめご了承ください。 ニュース・イベント• 2020. お客さまにはご迷惑をおかけいたしますが、何卒ご理解賜りますようお願い申し上げます。 【対象店舗・変更営業時間】 下記営業時間変更詳細をご覧ください。 【対象期間】 店舗により異なります。 下記営業時間変更詳細をご覧ください。 ご了承ください。 福島駅• 郡山駅• 新白河駅• 会津若松駅• 七日町駅• ニュース・イベント• 2020. お客さまにはご迷惑をおかけいたしますが、何卒ご理解賜りますようお願い申し上げます。 なお、開館再開につきましては、当社ホームページ及び公式Twitter等で今後お知らせさせていただきます。 仙台駅• ニュース・イベント• ちょっと手を加えるだけで、食べきれず余った食材を無駄にしない保存レシピや、作り置き食材で作る簡単アレンジレシピを紹介しています。 福島駅• 料理の専門家の目線から見たピボットの良さをご紹介いただいている他、先生厳選のいいものベスト5もご紹介いただいています。 福島駅• 秋の味覚を楽しもう。 福島駅.

次の