ขนาดวิดีโอ: 1280 X 720853 X 480640 X 360
แสดงแผงควบคุมโปรแกรมเล่น
เล่นอัตโนมัติ
เล่นใหม่
▼Zoomを使ったExcelに関するオンライン個別相談を実施しています。bit.ly/3IZTwxF================================================【目次】00:00 イントロ00:14 この動画のゴール00:37 従来のデータ集計の方法の概要01:55 PowerPivotを使ったデータ集計の方法の概要05:02 従来の方法で実際にデータを集計する11:27 PowerPivotを使い実際にデータを集計する11:55 PowerPivotを使えるようにする12:47 データモデルにテーブルを読み込む14:50 テーブルのリレーションを構築する18:27 データモデルからピボットテーブルを作成する21:32 データモデルに計算列を追加する23:18 データモデルに計算列を追加することによる問題点27:52 データモデルでメジャーを作成する28:51 DAX関数31:47 メジャーをピボットテーブルに反映する32:44 ワークシート関数のSUMとDAX関数のSUMの違い35:44 メジャーを使ってメジャーを作る37:10 DIVIDE関数38:45 計算列とメジャーとでピボットテーブル上での見え方の違い42:04 PowerPivotを使わずにリレーションを構築する方法43:38 PowerPivotを使わずにメジャーを作成する方法45:29 ピボットテーブルフィールドのアクティブタブ46:35 演習問題54:39 さいごに
😊
この内容がただで動画で見れる。いい時代になった。
Excelの中で、Accessみたいな機能があったなんて初めて知りました。本当にありがとうございました。練習してから業務に少しずつ活かしていきたいと思います。
1年半ほど前にPower Queryを習得したくて千葉さんの動画を拝見し、その後ある程度扱えるようになってきたのですが（ありがとうござます）、Accessみたいにリレーション組めたらなぁと思っていたところ、やはり千葉さんにたどり着きました。また勉強させていただきます。
SuperThanksありがとうございます！
懇切丁寧に説明していただきありがとうございました。最近はエクセルを使用する頻度が減っていたため機能の勉強をしていませんでした。この動画をきっかけとして身近な業務の改善、効率化を図りたいと思います。今後も素晴らしい動画を配信していただけることを楽しみさせていただきます。
本を見ながら勉強中（2巡目）で、あんまり理解できずモヤモヤしていましたが、この動画を観てとてもシンプルなので理解できました。本だとリレーションもDAXも「難しいやり方」で説明していますが、確かにダイアグラムビューと計算領域の方が分かりやすいですよね。ありがとうございました。
リレーションしてもピボットテーブルで使えず困っていたのですが、視聴したすぐに解決できました。PowerQueryで作成したテーブルと連携させれば、膨大なデータも簡単に分析できそうです。有益な投稿に感謝します。
こちらの動画はPowerPivot単体に関する内容のため、PowerQueryとの連携については触れていませんが、↓の動画では両機能を使ったデータ集計方法についても解説していますので、ぜひ参考にしてみてください。th-cam.com/video/S_Wa_puqpD0/w-d-xo.html
いつも拝見しております、おかげさまでパワークエリやパワーピボットがだいぶ使えるようになってきました。動画にして欲しい内容があります！パワーピボットでメジャーを作る際いつもやりたくて断念してしまうことがあります。例えばA列から順に「年月」「勘定科目」「金額」という風に並んだ元データがあり、これで横軸に「年月」縦軸に「勘定科目」を並べたピボットテーブルを作ります。（月次の試算表を過去数年分パワークエリで結合してデータベース化して、それを時系列で指標を並べる作業です。）その際、「勘定科目」の「売上総利益」÷「売上高」で「売上総利益率」というメジャーを作る方法を教えてください。初めから「売上総利益」と「売上高」という列があればその列名でメジャーを作ることはできるのですが、「勘定科目」という列の中に含まれる項目でメジャーを作るのができません。動画等も探してるのですが未だに成功していません。。
とても参考になります。有難うございます。最高です。
導入として非常にわかりやすかったです。ありがとうございます。
わかりやすい！
重い腰を上げて初めて使ってみた。おかげさまで理解できました(一人一)データモデル使った場合はピボットテーブル上で手動グループ化が使えないようで…整ったデータでのルーティンより都度集計が多い業務なので残念すぎる
確かに、データモデルをソースに作成したピボットテーブルの場合は、手動で行・列項目をグループ化することができませんね。私も始めて知りました。
なんと… フィールドセクションの表示形式は、パワーピボットの野望を含んだデザインだったのですね… その必要性に初めて納得がいきました。先のパワークエリーとの違いも分かった。もしかして、参照データがExcelシート内では無くてもイケそうな気がする… そう、100万行でもパワーピボットで扱えるならば、画期的だと…
どの動画も凄い勉強になります、部門別月別予算資料などあなただと速攻で仕上げそう、、、。うちのシート100個以上ある、、部門追加や削除あると本当に大変、、。アドバイス下さい、、。
100シートというのが凄まじいですね。。。キレイに整形された元データとなるテーブルがあれば集計自体は簡単なので、大量にあるシートを結合するためのクエリをどうやって作るかがポイントかなと。Excelドカタの集会所で、複数の同じフォーマットのシートを結合する方法は紹介していますので、よければ参考にしてみてください。#02.Excelブックの複数シートをまとめて取得して結合するexcel-dokata.mish.tv/ja/videos/vUEZXTMwFu09SqIzrMCmUBS6もし各シートを整形しないと結合できない場合は、クエリでカスタム関数を作れば対応できるかもしれません。
@@ExcelDokataCH アドバイス有難うございます、動画参考にしてバカな頭使ってみます、、。本当に100以上あるんですよ、普通予算、特別予算、合計が全部門であるので、、組織編制あると相当キツイ、、。
サイコーだ
大変勉強になりました最初に普段使いの例が示されているので、理解しやすいです。
2回目見させて頂いて、PowerQuery と Powerpibot がごっちゃになっていたのが、区別できるようになりました。少し進歩したかな。
出版されている書籍だとPowerQueryとPowerPivotをいきなり両方とも使うような内容が多いので、最初の時点で結構ごっちゃになっちゃうんですよね
AccessでしかできなかったことがExcelでもできるようになっていたのですね。操作画面が類似してるし百万行まで扱えるので、もうExcelで十分かな。
最終的にピボットテーブルでデータを集計できればOKなのであればExcelで充分ですが、Excelだとデータの入力画面を作り込んだり、出力する帳票をレイアウトしたりといったことができませんので、さすがにAccess代わりとまではいきませんね。
@@ExcelDokataCH なるほど。入出力設計が必要ならAccessですね。
@@ExcelDokataCH その辺はExeclVBAで何とかなります。ユーザーフォームまで作らんでもExeclで入力画面レイアウトし、そのセルをVBAに食わせればなんとか。帳票も同じです。
これは実務的に使えるな。勉強なります。残業が減りますね。
テーブル同士のデータ接続のためにvlookupを頻繁に使っているようであれば、リレーションによって効率アップが見込めますねDAX関数で様々なメジャーを作ることができれば、ピボットテーブルで複雑なデータ集計を行うこともできるようになります
＠売上高と合計/売上高の違いは無いといういことでしょうか？
マスターテーブルをいちいちブックにコピーしてテーブルを作成すると、マスターが更新された時にまたやり直しになるので外部においたままpowerpivotが使えないでしょうか？もしくはpowerqueryで外部接続したものを更新後にpowerpivotで集計するなど
メン限コンテンツになってしまいますが、以下の動画で解説しています。th-cam.com/video/uOr0vDEuxpc/w-d-xo.htmlsi=H-U8aOnKGWRQCiTH
@@ExcelDokataCH ありがとうございますpowerpivotにも外部データの取り込みというボタンがありますが、powerqueryで取り込むとテーブル化されるので、そのままpowerpivotのデータモデルに追加できましたこれで外部のマスターが更新されても大丈夫そうです
不勉強でした。とすると、パワークエリーでテーブルそのものを別のDBからもって来るとすると、数多くのリレーションと多段階のグループ化の集計がエクセルで割と簡単にできてしまう、ということなんでしょうかね。必死になって長いSQLを書かなくてもよい、ということなんでしょうか。すごいことです。
ExcelだとどうしてもVLOOKUPとかでしかデータの接続ができませんでしたが、テーブル同士でリレーションできればそのあたりの手間は省けますね。DAX関数を使ったメジャーは少し難易度高いかもしれませんが、SQLをゴリゴリ書くよりはまだマシかなと。メジャーなしで、リレーションだけ使っても十分便利ですし。
動画とても参考になります。Excel2010ではデーターモデルを選択できないのでしょうか？
Excel2010にはPowerPivotが搭載されていませんが、以下のファイルをダウンロード＆インストールすることに使えるようにはなるようです。support.microsoft.com/ja-jp/office/power-pivot-%E3%81%A8%E3%81%AF-aa64e217-4b6e-410b-8337-20b87e1c2a4bただ、私の環境で試すことはできませんので、動作や仕様、今のバージョンとの機能の違いについてはなんともいえませんが。。。
@@ExcelDokataCH ありがとうございます！！
たいへん有益な動画をありがとうございます。お手数ですが、一点ご教示ください。従来の方法ですと、例えばIFERRORとVLOOKUP関数を使って、マスターデータのテーブルにない値がキーになった場合は、"該当なし"、という文字列を返す、という方法が使えたのですが、PowerPivotの場合はきれいなリレーションの構築がマストのようで、IFERRORを使ったエラー対応ができないと思います。このようにマスターテーブルに存在しないデータを参照した際、特定の文字列を返したい場合、PowerPivotではどのような設定をすれば良いでしょうか？
もしリレーションしたデータモデルを元に作成したピボットテーブルで（空白）と表示されてほしくないのであれば、ピボットテーブル上で（空白）と表示されているセルに「該当なし」と直入力すれば、リレーションしないデータは「該当なし」として表示されます。
ありがとうございました！やってみます！
リレーションすると繋がった線に矢印が付きますが、それはどちら向きでも実務上は何も変わらないのでしょうか？
PowerPivotでリレーションすると、ユニークなキーをもつテーブルの方が自動的にプライマリになるみたいなので、マスタテーブルでキーとなる列の値がユニークになるように作成しておけば、矢印の向きは特に意識しなくても正しく設定されます。ただ、1：1のリレーションになっている場合は、主キー ---> 外部キーとなるように矢印の方向を意識する必要があると思います。
とても参考になりました。ありがとうございます。もし質問ができたらですが、PowerPivotでとりこんだデータの文字列8桁を日付変換することは可能でしょうか
ワークシート関数と同じくDAX関数にもDATE,LEFT,MID,RIGHT関数があるので、その組み合わせで計算列を作ればよいかと。もしくは、一旦PowerQueryで取得して日付のデータ型に設定してからデータモデルに読み込み、とかですかね。
わかりやすかったです、ありがとうございます。メジャーの作り方ですが、ピボットテーブルに追加した時に%が0になってしまってたのを見ると、書式を維持できる点では2つ目のやり方の方が優れていますかね？
2つ目のやり方というのは、パワーピボットを使わずにメジャーを作成するやり方、のことでしょうか？個人的には、書式の設定が維持されるかどうかよりも、メジャーの作りやすさを優先しますので、パワーピボットでのメジャー作成一択ですね。それにパワーピボット上で設定した％の書式設定が、ピボットテーブルに読み込んだ時に反映されないのはバグっぽい感じがするので、そのうち改修されるかもしれません。改修されなかったとしても、書式の設定程度であれば秒で終わりますし。
いつもお世話になっております。データモデルに計算式を追加すると、全ての行に対して、設定の計算式が入ると思いますが、特定の行に対して他の条件を設定することはできないでしょうか？
IF関数で「●行目の時だけ」のようにすれば、行ごとに適用する数式を設定することはできます。
お忙しいのに初めての投稿にご丁寧にご返信本当にありがとうございます。助かりました。
分かりやすい動画ありがとうございます。パワーピボット上で商品の販売データと解約データが異なるレコード上にあり、日付や担当者でソートした純増（販売-解約）の計算したいのですが、メジャーで計算できますでしょうか。普通のピボットテーブルでしたら、集計フィールドで計算できるはずなのですが、、
FILTER関数を使って販売データを集計するためのメジャーAと解約データを集計するためのメジャーBを作り、A－Bのメジャーを作れば、それが純増になりますね。
@@ExcelDokataCH 学習不足ですみません。filter関数を調べてみましたがうまくいかず再度の質問失礼します。【テーブル1】日付｜担当者｜販売額【テーブル2】日付｜担当者｜解約額となっていますが、【テーブル1】と【テーブル2】の計算領域にはどのようなメジャーを入力すればよいでしょうか。
販売額と解約額がテーブルで分かれているのであれば、 販売額合計:=sum('テーブル1'[販売額]) 解約額合計:=sum('テーブル2'[解約額]) 純額:=[販売額合計]-[解約額合計]この3つのメジャーだけでいいような気がしますが。。。
PowerPivotでテーブルのリレーションを構築するとき、関連するテーブルは1ブックの中に全て格納しなければならないのでしょうか。今、思考してる事は、1ブック1シートで各種マスタを構築し、売上データに該当するテーブルも1ブックに1シートでテーブル化させ、そのブック内でデータモデル・リレーションシップを構築させる作業です。実業務ではデータ量が相当数有り、1ブックの中に関連するテーブルを全て置くことは無理ではないかと思うのです。つまりは、1ブック1シートでのマスタテーブルやトランテーブルからRDBモデルを構築できるか、という疑問です。それが可能なら素晴らしいのですが。
通常のピボットですと、1ブック1シートに全てのデータが必要ですが、パワーピボットの場合、テーブル参照はリンクだと思いますので、「テーブル実体」がどこに有ろうと出来そうなのですが、実験してみると思うようにいきません。
PowerPivotだけだと１つのブックにマスタテーブル、データテーブルをおいておかないといけませんが、PowerQueryと組み合わせることで1ブック１シート１テーブルの複数のブックのデータをまとめてデータモデルでリレーションすることができます。PowerQueryについてはth-cam.com/video/ya5X9_YC3u0/w-d-xo.htmlPowerQueryとPowerPivotの組み合わせについてはth-cam.com/video/S_Wa_puqpD0/w-d-xo.htmlでそれぞれ解説していますので、参考にしてみてください。
パワークエリで一対多のAとBのリレーションをくんだ表を、それぞれリレーションの対象ラベルを行に持って行くとラベルの全組み合わせが表示されるのですがこれは仕様ですか？これを表組みで使いたかったのですが
値の項目になにかのフィールドを配置してみてください。集計する値がなにもないと全組み合わせだけが表示されてしまいます。
@@ExcelDokataCH 承知しました、もう一点いいでしょうか。一対多の表で一側のフィールドを集計においたのですが（個数）、一側の総レコード数が計算されます。これは行のフィールド設定がよくないのでしょうか
どんなテーブルをリレーションしていて、結果としてどんな表ができてほしいのか、現在どんな表になってしまっているのか分からないため、ちょっと回答が難しいです。なんとなくですが、２つのテーブルの組み合わせによる表組みを行いたいのであれば、パワーピボットでリレーションしてからのピボットテーブルよりも、パワークエリのマージの方がよさそうな気がします。
@@ExcelDokataCH 了解です、ご回答ありがとうございます！
:＝を使ってもメジャーが作成できませんでした、、、何が原因なのでしょうか
・パワーピボットウインドウの計算領域のどこかをマウスで選択してから、数式バーにメジャーを入力しましたでしょうか？・「:=」の前にメジャー名は入力しましたでしょうか？・「:=」は半角で入力しましたでしょうか？・試しに「AAA:=1」というメジャーをこのまま数式バーにコピペして作成してみてください。・一旦Excelブックを全て閉じて、再度パワーピボットウインドウでメジャーを作成してみてください。（タスクマネージャーの詳細タブでExcel.exeのプロセスが確実に実行されていないことを確認してください。たまにゾンビのようにExcel.exeが残っていることがありますので）こちらを試しても、やはりメジャーが作成できないようなら、何らかのExcelまたはパワーピボットの不具合かもしれませんので、マイクロソフトのサポートに連絡してみてください。
PowerQueryのマージとテーブルのリレーションとはどう違うんだろ。
リレーションはテーブル同士を関連付けているだけですが、クエリのマージは実際には２つのテーブルを結合した新たなテーブルを作成しています。
@@ExcelDokataCH ありがとうございます。大雑把に言えば、powerQueryのほうが上位互換というイメージでしょうか。
SUMとDAXの違い、しびれました。
普通にExcelで使うワークシート関数とDAX関数では、文字だけ見れば同じものが結構ありますが、その仕様はかなり異なることが多いです。この『SUM関数』における動きの違いを理解すると、他のDAX関数の働きを理解する場合にも役立つと思います。
▼Zoomを使ったExcelに関するオンライン個別相談を実施しています。
bit.ly/3IZTwxF
================================================
【目次】
00:00 イントロ
00:14 この動画のゴール
00:37 従来のデータ集計の方法の概要
01:55 PowerPivotを使ったデータ集計の方法の概要
05:02 従来の方法で実際にデータを集計する
11:27 PowerPivotを使い実際にデータを集計する
11:55 PowerPivotを使えるようにする
12:47 データモデルにテーブルを読み込む
14:50 テーブルのリレーションを構築する
18:27 データモデルからピボットテーブルを作成する
21:32 データモデルに計算列を追加する
23:18 データモデルに計算列を追加することによる問題点
27:52 データモデルでメジャーを作成する
28:51 DAX関数
31:47 メジャーをピボットテーブルに反映する
32:44 ワークシート関数のSUMとDAX関数のSUMの違い
35:44 メジャーを使ってメジャーを作る
37:10 DIVIDE関数
38:45 計算列とメジャーとでピボットテーブル上での見え方の違い
42:04 PowerPivotを使わずにリレーションを構築する方法
43:38 PowerPivotを使わずにメジャーを作成する方法
45:29 ピボットテーブルフィールドのアクティブタブ
46:35 演習問題
54:39 さいごに
😊
この内容がただで動画で見れる。いい時代になった。
Excelの中で、Accessみたいな機能があったなんて初めて知りました。本当にありがとうございました。練習してから業務に少しずつ活かしていきたいと思います。
1年半ほど前にPower Queryを習得したくて千葉さんの動画を拝見し、その後ある程度扱えるようになってきたのですが（ありがとうござます）、Accessみたいにリレーション組めたらなぁと思っていたところ、やはり千葉さんにたどり着きました。また勉強させていただきます。
SuperThanksありがとうございます！
懇切丁寧に説明していただきありがとうございました。最近はエクセルを使用する頻度が減っていたため機能の勉強をしていませんでした。この動画をきっかけとして身近な業務の改善、効率化を図りたいと思います。今後も素晴らしい動画を配信していただけることを楽しみさせていただきます。
本を見ながら勉強中（2巡目）で、あんまり理解できずモヤモヤしていましたが、この動画を観てとてもシンプルなので理解できました。
本だとリレーションもDAXも「難しいやり方」で説明していますが、確かにダイアグラムビューと計算領域の方が分かりやすいですよね。
ありがとうございました。
リレーションしてもピボットテーブルで使えず困っていたのですが、視聴したすぐに解決できました。
PowerQueryで作成したテーブルと連携させれば、膨大なデータも簡単に分析できそうです。
有益な投稿に感謝します。
こちらの動画はPowerPivot単体に関する内容のため、PowerQueryとの連携については触れていませんが、↓の動画では両機能を使ったデータ集計方法についても解説していますので、ぜひ参考にしてみてください。
th-cam.com/video/S_Wa_puqpD0/w-d-xo.html
いつも拝見しております、おかげさまでパワークエリやパワーピボットがだいぶ使えるようになってきました。
動画にして欲しい内容があります！
パワーピボットでメジャーを作る際いつもやりたくて断念してしまうことがあります。
例えばA列から順に「年月」「勘定科目」「金額」という風に並んだ元データがあり、これで横軸に「年月」縦軸に「勘定科目」を並べたピボットテーブルを作ります。（月次の試算表を過去数年分パワークエリで結合してデータベース化して、それを時系列で指標を並べる作業です。）
その際、「勘定科目」の「売上総利益」÷「売上高」で「売上総利益率」というメジャーを作る方法を教えてください。
初めから「売上総利益」と「売上高」という列があればその列名でメジャーを作ることはできるのですが、「勘定科目」という列の中に含まれる項目でメジャーを作るのができません。
動画等も探してるのですが未だに成功していません。。
とても参考になります。有難うございます。最高です。
導入として非常にわかりやすかったです。ありがとうございます。
わかりやすい！
重い腰を上げて初めて使ってみた。おかげさまで理解できました(一人一)
データモデル使った場合はピボットテーブル上で手動グループ化が使えないようで…整ったデータでのルーティンより都度集計が多い業務なので残念すぎる
確かに、データモデルをソースに作成したピボットテーブルの場合は、手動で行・列項目をグループ化することができませんね。私も始めて知りました。
なんと… フィールドセクションの表示形式は、パワーピボットの野望を含んだデザインだったのですね… その必要性に初めて納得がいきました。
先のパワークエリーとの違いも分かった。もしかして、参照データがExcelシート内では無くてもイケそうな気がする… そう、100万行でもパワーピボットで扱えるならば、画期的だと…
どの動画も凄い勉強になります、部門別月別予算資料などあなただと速攻で仕上げそう、、、。うちのシート100個以上ある、、部門追加や削除あると本当に大変、、。アドバイス下さい、、。
100シートというのが凄まじいですね。。。
キレイに整形された元データとなるテーブルがあれば集計自体は簡単なので、大量にあるシートを結合するためのクエリをどうやって作るかがポイントかなと。
Excelドカタの集会所で、複数の同じフォーマットのシートを結合する方法は紹介していますので、よければ参考にしてみてください。
#02.Excelブックの複数シートをまとめて取得して結合する
excel-dokata.mish.tv/ja/videos/vUEZXTMwFu09SqIzrMCmUBS6
もし各シートを整形しないと結合できない場合は、クエリでカスタム関数を作れば対応できるかもしれません。
@@ExcelDokataCH アドバイス有難うございます、動画参考にしてバカな頭使ってみます、、。本当に100以上あるんですよ、普通予算、特別予算、合計が全部門であるので、、組織編制あると相当キツイ、、。
サイコーだ
大変勉強になりました
最初に普段使いの例が示されているので、理解しやすいです。
2回目見させて頂いて、PowerQuery と Powerpibot がごっちゃになっていたのが、区別できるようになりました。
少し進歩したかな。
出版されている書籍だとPowerQueryとPowerPivotをいきなり両方とも使うような内容が多いので、最初の時点で結構ごっちゃになっちゃうんですよね
AccessでしかできなかったことがExcelでもできるようになっていたのですね。操作画面が類似してるし百万行まで扱えるので、もうExcelで十分かな。
最終的にピボットテーブルでデータを集計できればOKなのであればExcelで充分ですが、Excelだとデータの入力画面を作り込んだり、出力する帳票をレイアウトしたりといったことができませんので、さすがにAccess代わりとまではいきませんね。
@@ExcelDokataCH なるほど。入出力設計が必要ならAccessですね。
@@ExcelDokataCH
その辺はExeclVBAで何とかなります。ユーザーフォームまで作らんでもExeclで入力画面レイアウトし、そのセルをVBAに食わせればなんとか。
帳票も同じです。
これは実務的に使えるな。勉強なります。残業が減りますね。
テーブル同士のデータ接続のためにvlookupを頻繁に使っているようであれば、リレーションによって効率アップが見込めますね
DAX関数で様々なメジャーを作ることができれば、ピボットテーブルで複雑なデータ集計を行うこともできるようになります
＠売上高と合計/売上高の違いは無いといういことでしょうか？
マスターテーブルをいちいちブックにコピーしてテーブルを作成すると、マスターが更新された時にまたやり直しになるので
外部においたままpowerpivotが使えないでしょうか？
もしくはpowerqueryで外部接続したものを更新後にpowerpivotで集計するなど
メン限コンテンツになってしまいますが、以下の動画で解説しています。
th-cam.com/video/uOr0vDEuxpc/w-d-xo.htmlsi=H-U8aOnKGWRQCiTH
@@ExcelDokataCH
ありがとうございます
powerpivotにも外部データの取り込みというボタンがありますが、
powerqueryで取り込むとテーブル化されるので、そのままpowerpivotのデータモデルに追加できました
これで外部のマスターが更新されても大丈夫そうです
不勉強でした。
とすると、パワークエリーでテーブルそのものを別のDBからもって来るとすると、数多くのリレーションと多段階のグループ化の集計が
エクセルで割と簡単にできてしまう、ということなんでしょうかね。
必死になって長いSQLを書かなくてもよい、ということなんでしょうか。
すごいことです。
ExcelだとどうしてもVLOOKUPとかでしかデータの接続ができませんでしたが、テーブル同士でリレーションできればそのあたりの手間は省けますね。
DAX関数を使ったメジャーは少し難易度高いかもしれませんが、SQLをゴリゴリ書くよりはまだマシかなと。
メジャーなしで、リレーションだけ使っても十分便利ですし。
動画とても参考になります。
Excel2010ではデーターモデルを選択できないのでしょうか？
Excel2010にはPowerPivotが搭載されていませんが、以下のファイルをダウンロード＆インストールすることに使えるようにはなるようです。
support.microsoft.com/ja-jp/office/power-pivot-%E3%81%A8%E3%81%AF-aa64e217-4b6e-410b-8337-20b87e1c2a4b
ただ、私の環境で試すことはできませんので、動作や仕様、今のバージョンとの機能の違いについてはなんともいえませんが。。。
@@ExcelDokataCH ありがとうございます！！
たいへん有益な動画をありがとうございます。お手数ですが、一点ご教示ください。
従来の方法ですと、例えばIFERRORとVLOOKUP関数を使って、マスターデータのテーブルにない値がキーになった場合は、"該当なし"、という文字列を返す、という方法が使えたのですが、PowerPivotの場合はきれいなリレーションの構築がマストのようで、IFERRORを使ったエラー対応ができないと思います。このようにマスターテーブルに存在しないデータを参照した際、特定の文字列を返したい場合、PowerPivotではどのような設定をすれば良いでしょうか？
もしリレーションしたデータモデルを元に作成したピボットテーブルで（空白）と表示されてほしくないのであれば、ピボットテーブル上で（空白）と表示されているセルに「該当なし」と直入力すれば、リレーションしないデータは「該当なし」として表示されます。
ありがとうございました！やってみます！
リレーションすると繋がった線に矢印が付きますが、それはどちら向きでも実務上は何も変わらないのでしょうか？
PowerPivotでリレーションすると、ユニークなキーをもつテーブルの方が自動的にプライマリになるみたいなので、マスタテーブルでキーとなる列の値がユニークになるように作成しておけば、矢印の向きは特に意識しなくても正しく設定されます。
ただ、1：1のリレーションになっている場合は、主キー ---> 外部キーとなるように矢印の方向を意識する必要があると思います。
とても参考になりました。ありがとうございます。
もし質問ができたらですが、PowerPivotでとりこんだデータの文字列8桁を日付変換することは可能でしょうか
ワークシート関数と同じくDAX関数にもDATE,LEFT,MID,RIGHT関数があるので、その組み合わせで計算列を作ればよいかと。
もしくは、一旦PowerQueryで取得して日付のデータ型に設定してからデータモデルに読み込み、とかですかね。
わかりやすかったです、ありがとうございます。
メジャーの作り方ですが、ピボットテーブルに追加した時に%が0になってしまってたのを見ると、書式を維持できる点では2つ目のやり方の方が優れていますかね？
2つ目のやり方というのは、パワーピボットを使わずにメジャーを作成するやり方、のことでしょうか？
個人的には、書式の設定が維持されるかどうかよりも、メジャーの作りやすさを優先しますので、パワーピボットでのメジャー作成一択ですね。
それにパワーピボット上で設定した％の書式設定が、ピボットテーブルに読み込んだ時に反映されないのはバグっぽい感じがするので、そのうち改修されるかもしれません。
改修されなかったとしても、書式の設定程度であれば秒で終わりますし。
いつもお世話になっております。データモデルに計算式を追加すると、全ての行に対して、設定の計算式が入ると思いますが、特定の行に対して他の条件を設定することはできないでしょうか？
IF関数で「●行目の時だけ」のようにすれば、行ごとに適用する数式を設定することはできます。
お忙しいのに初めての投稿にご丁寧にご返信本当にありがとうございます。助かりました。
分かりやすい動画ありがとうございます。
パワーピボット上で商品の販売データと解約データが異なるレコード上にあり、日付や担当者でソートした純増（販売-解約）の計算したいのですが、メジャーで計算できますでしょうか。
普通のピボットテーブルでしたら、集計フィールドで計算できるはずなのですが、、
FILTER関数を使って販売データを集計するためのメジャーAと解約データを集計するためのメジャーBを作り、A－Bのメジャーを作れば、それが純増になりますね。
@@ExcelDokataCH 学習不足ですみません。filter関数を調べてみましたがうまくいかず再度の質問失礼します。
【テーブル1】
日付｜担当者｜販売額
【テーブル2】
日付｜担当者｜解約額
となっていますが、【テーブル1】と【テーブル2】の計算領域にはどのようなメジャーを入力すればよいでしょうか。
販売額と解約額がテーブルで分かれているのであれば、
販売額合計:=sum('テーブル1'[販売額])
解約額合計:=sum('テーブル2'[解約額])
純額:=[販売額合計]-[解約額合計]
この3つのメジャーだけでいいような気がしますが。。。
PowerPivotでテーブルのリレーションを構築するとき、関連するテーブルは1ブックの中に全て格納しなければならないのでしょうか。
今、思考してる事は、1ブック1シートで各種マスタを構築し、売上データに該当するテーブルも1ブックに1シートでテーブル化させ、そのブック内でデータモデル・リレーションシップを構築させる作業です。
実業務ではデータ量が相当数有り、1ブックの中に関連するテーブルを全て置くことは無理ではないかと思うのです。
つまりは、1ブック1シートでのマスタテーブルやトランテーブルからRDBモデルを構築できるか、という疑問です。
それが可能なら素晴らしいのですが。
通常のピボットですと、1ブック1シートに全てのデータが必要ですが、パワーピボットの場合、テーブル参照はリンクだと思いますので、「テーブル実体」がどこに有ろうと出来そうなのですが、実験してみると思うようにいきません。
PowerPivotだけだと１つのブックにマスタテーブル、データテーブルをおいておかないといけませんが、PowerQueryと組み合わせることで1ブック１シート１テーブルの複数のブックのデータをまとめてデータモデルでリレーションすることができます。
PowerQueryについては
th-cam.com/video/ya5X9_YC3u0/w-d-xo.html
PowerQueryとPowerPivotの組み合わせについては
th-cam.com/video/S_Wa_puqpD0/w-d-xo.html
でそれぞれ解説していますので、参考にしてみてください。
パワークエリで一対多のAとBのリレーションをくんだ表を、それぞれリレーションの対象ラベルを行に持って行くとラベルの全組み合わせが表示されるのですがこれは仕様ですか？これを表組みで使いたかったのですが
値の項目になにかのフィールドを配置してみてください。
集計する値がなにもないと全組み合わせだけが表示されてしまいます。
@@ExcelDokataCH 承知しました、もう一点いいでしょうか。一対多の表で一側のフィールドを集計においたのですが（個数）、一側の総レコード数が計算されます。これは行のフィールド設定がよくないのでしょうか
どんなテーブルをリレーションしていて、結果としてどんな表ができてほしいのか、現在どんな表になってしまっているのか分からないため、ちょっと回答が難しいです。
なんとなくですが、２つのテーブルの組み合わせによる表組みを行いたいのであれば、パワーピボットでリレーションしてからのピボットテーブルよりも、パワークエリのマージの方がよさそうな気がします。
@@ExcelDokataCH 了解です、ご回答ありがとうございます！
:＝を使ってもメジャーが作成できませんでした、、、
何が原因なのでしょうか
・パワーピボットウインドウの計算領域のどこかをマウスで選択してから、数式バーにメジャーを入力しましたでしょうか？
・「:=」の前にメジャー名は入力しましたでしょうか？
・「:=」は半角で入力しましたでしょうか？
・試しに「AAA:=1」というメジャーをこのまま数式バーにコピペして作成してみてください。
・一旦Excelブックを全て閉じて、再度パワーピボットウインドウでメジャーを作成してみてください。（タスクマネージャーの詳細タブでExcel.exeのプロセスが確実に実行されていないことを確認してください。たまにゾンビのようにExcel.exeが残っていることがありますので）
こちらを試しても、やはりメジャーが作成できないようなら、何らかのExcelまたはパワーピボットの不具合かもしれませんので、マイクロソフトのサポートに連絡してみてください。
PowerQueryのマージとテーブルのリレーションとはどう違うんだろ。
リレーションはテーブル同士を関連付けているだけですが、クエリのマージは実際には２つのテーブルを結合した新たなテーブルを作成しています。
@@ExcelDokataCH ありがとうございます。大雑把に言えば、powerQueryのほうが上位互換というイメージでしょうか。
SUMとDAXの違い、しびれました。
普通にExcelで使うワークシート関数とDAX関数では、文字だけ見れば同じものが結構ありますが、その仕様はかなり異なることが多いです。
この『SUM関数』における動きの違いを理解すると、他のDAX関数の働きを理解する場合にも役立つと思います。