パワー ピボット。 PowerPivot for Excel 2010を利用する:エクセル2010基本講座

PowerPivot for Excel 2010を利用する:エクセル2010基本講座

パワー ピボット

たとえば、売上情報を管理している [売上] テーブルがあるとします。 Access などのデータベースでは、このように管理されていることが多いです。 このテーブルを集計元データとしてピボットテーブルで集計をすると、A のように社員番号ごとの金額の集計はできますが、テーブルに社員の氏名情報はないので、B のように社員の氏名を表示することはできません。 B のピボットテーブルを作りたいとき、売上とは別に用意されている担当者情報のテーブルがあれば、[売上] テーブルに新しい列を追加して VLOOKUP 関数などを使った数式によって、[担当者] テーブルから社員の氏名を表示できます。 この加工ができれば集計元データに氏名を含められるため、B のピボットテーブルを作成できます。 テーブルについては ちょこテクでも何度かご紹介しているので、よろしければ などを参照してください。 たとえば、下図では、[担当者] テーブルが「主テーブル」、[売上] テーブルが「リレーション テーブル」です。 2 つのテーブルは、共通のデータを持つ [社員番号] フィールドで関連付けることができます。 フィールド名は一致していなくてもよし 専門的な言い方をすると、共通のフィールドはそれぞれ、主テーブルの「主キー」とリレーション テーブルの「外部キー」です。 重複しません 「外部キー」は日々増えていく明細データの一部なので、何度も出てくる可能性があります。 外部キーを主キーと関連付けることで、リレーション テーブルから主テーブルの情報を参照できるようになります。 リレーション テーブルから、「S001 さーん。 お名前を教えてくださーい。 」っていったら、主テーブルから「はーい。 徳川です」っていう返事が返ってくる、そのやり取りをするためのつながり、という感じでしょうか。 このあとの手順で出てきますが、リレーションシップの設定は、[リレーションシップの作成] ダイアログ ボックスの [テーブル] に「リレーション テーブル」を、[関連テーブル] に「主テーブル」を指定します。 どっちがどっちなの?ということが理解できていないと、この設定が思うようにできません。 リレーションシップの設定 操作 テーブルにリレーションシップを設定するには、次のように操作します。 しつこいようですが、リレーションシップを設定するリスト 一覧表 は、事前にテーブルに変換しておいてください。 ここでは、[売上] テーブルの [社員番号] フィールドと [担当者] テーブルの [社員番号] フィールドを関連付けて、リレーションシップを設定します。 リボンの [データ] タブの [データ ツール] グループの [リレーションシップ] を クリックします。 [リレーションシップの管理] ダイアログ ボックスの [新規作成] をクリックします。 [テーブル] と [関連テーブル] はどちらを先に選択してもいいのですが、[テーブル] でリレーション テーブル [売上] テーブル を選択して、[列 外部 ] で関連付けるフィールド [社員番号] フィールド を選択し、 [関連テーブル] で主テーブル [担当者] テーブル を選択して、[関連列 プライマリ ] で関連付けるフィールド [社員番号] フィールド を選択して、[OK] をクリックします。 [閉じる] をクリックします。 2 つのテーブルにリレーションシップを設定したということは、2 つのテーブルのデータが、分析用データとして「データ モデル」というワークシートとは別の場所に準備された、とイメージしてください。 おまけ データ モデルの確認 Office Professional Plus 2016 または Office 365 ProPlus の Excel 2016 の場合は、リボンの [データ] タブの [データ ツール] グループに [データ モデルの管理] コマンドがあり、クリックすると [Power Pivot for Excel] が起動します。 [Power Pivot for Excel] ではデータ モデルの中身を確認できる、と思えばよいです。 [Power Pivot for Excel] ウィンドウの [ホーム] タブの [ダイアグラム ビュー] をクリックし、リレーションシップを表している線にマウス ポインターを合わせると、各テーブルのどのフィールドが関連付けられているのかがわかります。 確認が終わったら [Power Pivot for Excel] ウィンドウを閉じます。 他方のテーブルのデータを参照するピボットテーブルを作成するには リレーションシップの設定されている 2 つのテーブルのデータ =データ モデル を使ってピボットテーブルで集計します。 [ピボットテーブルの作成] ダイアログ ボックスの [外部データ ソースを使用] をオンにして、[接続の選択] をクリックします。 [既存の接続] ダイアログ ボックスの [テーブル] タブを選択し、[このブックのデータ モデル] で使用するデータ モデルを選択して、[開く] をクリックします。 [ピボットテーブルの作成] ダイアログ ボックスで、集計元データとしてデータ モデルが選択され、事前に指定した作成場所が表示されています。 [OK] をクリックします。 ピボットテーブルが作成されます。 [ピボットテーブルのフィールド リスト] で [すべて] Excel 2013 の場合は [すべてのフィールド] が選択されているとき、フィールド セクションには、ブック内のテーブルやリレーションシップが設定されているテーブルが表示されています。 VLOOKUP などを使って加工しなくても、2 つのテーブルを関連付けてピボットテーブルによる集計ができました。 事前にリレーションシップを設定していなくても、ピボットテーブルを作成しているときに、「リレーションシップがありませんよ、作りますか?」と聞いてくれて、そのときに設定することができるし、Excel 2016 であれば、[自動検出] という機能を使って、データの特徴から自動的にリレーションシップを設定することができたりします。 それでもやっぱり、ちゃんと事前に、どちらのテーブルがどういったデータを管理していて、どの列とどの列が関連付けられているのか、などを理解したうえで使うべきだと思います。 なんとなくやったらできちゃったでは、やりたいことができないときがきます。 たとえば、いつもすべてのデータが 1 つの表にまとまっているわけではないでしょうし、データ分析を行うときに、別の場所で集められたデータと組み合わせる必要が出てきたりします。 Access などをお使いでない方は、リレーショナル データベースとか、リレーションシップなどという言葉や仕組みになじみがないかもしれませんが、今後は Excel でもこんなことができるのが当たり前になっていきますので、どこかでお勉強しておくとよいかもしれません。 石田かのこ•

次の

パワーピボットのドリルスルー

パワー ピボット

PowerPivot パワーピボット で計算式を作成する:エクセル2013基本講座• PowerPivot パワーピボット で計算式を作成する スポンサードリンク• ここでは、 で作成したファイルを使って説明します。 Excelファイルに2つのシートが有り、それぞれは下図のようなデータになっています。 Sheet1は日付順に作成された売り上げデータです。 Sheet2は型番と商品名と単価の対照表になっています。 この2つのデータは「型番」でリレーションシップを作成しています。 ダイアグラムビューでのリレーションシップの設定方法は をご覧ください。 リレーションシップの設定方法は をご覧ください。 PowerPivot には、Data Analysis Expressions DAX が用意されています。 Excelの関数と同じように計算式を作成することができますが、若干異なります。 計算式を作成してみます。 消費税の計算 掛け算 を入力してみます。 「列の追加」となっている部分をクリックします。 列全体が選択されます。 08 と入力して[Enter]キーを押します。 消費税額は切り捨てにするために INTを使うのを忘れていました。 08 と修正して、[Enter]キーを押しました。 「CaluculatedColumn1」をダブルクリックして、「消費税」と入力しました。 ほかの表を参照する計算式を作成したい• Excelの関数ならVLOOKUP関数を使うのですが、PowerPivotの DAXでは使用できないようです。 DAX では RELATED を利用します。 数式オートコンプリート機能と同じです RELATED を選択して、[Tab]キーを押します。 [Enter]キーを押して計算式を確定します。 計算列を使ったピボットグラフを作成した例です。 PowerPivotでも数式オートコンプリートの機能が使えますので、キーボードから数式バーに直接数式を入力するのが手早いと思います。 関数を選択するとポップアップヒントで関数の簡単な説明を読むことができます。 PowerPivot にも[関数の挿入]ボタンがありますので、そちらを使いたい場合は以下のような操作になります。 数式バーの左端とデザインタブの計算グループにあります。 [列の追加]をクリックして、計算式を入力する列を選択します。 [関数の挿入]ボタンをクリックすると、関数の挿入 ダイアログボックスが表示されます。 入力したい関数を選択します。 ここではMONTHを選択しました。 [OK]ボタンをクリックします。 関数の選択のリストボックス内をアクティブにして、半角で関数の頭文字を入力すると、その頭文字の先頭へスクロールすることができます。 数式バーに選択していた関数が入力されました。 日付列内のセルをクリックして引数に[日付] を入力しました。 この状態で、[Enter]キーを押すと計算式がエラーと表示されました。 PowerPivotでは最後のかっこ を自動で入力してくれませんので、最後の を入力してから、[Enter]キーを押して数式の入力を確定します。 列見出し部分をダブルクリックして、「月」と入力しました。 下図では列幅をドラッグして調整しました。 スポンサードリンク |||PowerPivotで計算式を作成する.

次の

Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本【PDF版】 | ua.nfhsnetwork.com | 翔泳社の通販

パワー ピボット

毎月や毎四半期の定常業務として・・・ ・毎月、販売データを販売管理システムからダウンロードして、 ・納品完了・失注などの案件ステータスをリスト機能で抽出し、 ・商材や会社名を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、ぜひこの記事を参考に使いこなしてみてください。 自分でより深く学んでみたいという人は、以下の書籍もどうぞ。

次の