2024年5月14日
Microsoft 365 および Office のフォーラム トップコントリビューター
Microsoft 365 および Office のフォーラムでは、以下の方々が 2024 年 4 月のトップ 5 回答者さまでした:
simo-k2 (microsoft.com)、Hebikuzure (microsoft.com)、kt-1688 (microsoft.com)、5rou. (microsoft.com)、真山 (microsoft.com)
コミュニティの運営にご協力くださり、心よりお礼申し上げます。ありがとうございます。
2024年3月22日
コントリビューターとして Microsoft 365 および Office フォーラムに参加しませんか?
2024年2月07日
初めてコミュニティをご利用のユーザーさま、質問者のみなさまへ
Microsoft コミュニティは質問者さまから寄せられる問題の解決ができるよう、ユーザー同士が情報の提供をしあうフォーラムです。質問を投稿される際には、Microsoft コミュニティについてよく寄せられる質問 (FAQ)で、詳しいコミュニティの使用方法やルールをご確認いただけます。
ご質問の問題が解決した場合には、スレッド内でその旨を報告いただけると幸いです。同じ問題でコミュニティを訪れる他のユーザーさまにも、同様の解決法が役立つかもしれません。
【ピボット】一対多のデータテーブルのカウントで全て同じ値になってしまう
複数テーブルでリレーションを設計し、
下記■(3)のような集計、および■(4)のようなアウトプットをしたいと考えています。
リレーションの設計は (1)と(2)が一対多の関係 とし、
ピボット画面に移行、(1)のマスターテーブルのNo.数でカウントしようとすると、
□(3)のような集計(全て(1)の行の合計)、もちろんそれを叩くと□(4)のようなアウトプットになってしまいます。
これは、そもそもリレーション 一対多の関係上、なってしまうものなのでしょうか。
それともリレーションの設計なのでしょうか。それとも何か足らない点があるのでしょうか。
※ ピボットのフィールド設計画面で列と値に入力すると
「テーブル間のリレーションシップが必要である可能性」が出てきてしまいます。
でも、管理画面では既にリレーションは表示され、アクティブになっています。
※ Powerpivotで確認しても、(1)(2)のテーブルはpowerpivotリレーションテーブルで表示され、
No.同士はリレーションでつながれているものを確認できています((1)一 対 (2)多 の関係です)。
※ なお、便宜上、No.としていますが、No.は(1)(2)ともにテキスト型であることも確認できています。
ご指南のほど、宜しくお願いいたします。
----------------------------
◆ 既に作ったテーブル
【(1)マスターテーブル】
No. 説明
001 aaaaaa
002 bbbbbb
003 cccccc
【(2)分類テーブル】
No. 分類
001 A
001 B
001 C
002 A
003 B
※ いずれも、同じファイル、別シートで作成。データモデルに入れて、ピボットで分析。
■ 本当はこちらを出したいが…
【(3)ピボット集計したいもの】
分類 No.の数 (現状でも(2)のNo.をカウントすればこれが出る)
A 2
B 2
C 1
【(4)ピボット(3)をクリックして表示させたいもの】
(例えばAを叩き、Aに関連する(1)の内容を出したいが、
現状(2)のNo.を利用、カウントすると(2)のテーブルが出てきてしまい、
(1)説明列を出せないので利用していない)
No. 説明
001 aaaaaa
002 bbbbbb
□ こちらが出てきてしまう…
【(3)ピボット集計したいもの】
分類 No.の数 ((1)のNo.を使用するとこうなってしまう)
A 3
B 3
C 3
【(4)ピボット(3)をクリックして…】
(No.の数の何を叩いても全部の行が出てきてしまう)
No. 説明
001 aaaaaa
002 bbbbbb
003 cccccc
※ 使用環境
Windows10、EXCEL 2016、Powerpivot
----------------------------
不適切な発言
ありがとうございます。
報告されたコンテンツが送信されました
mmmmjjjjkkkk さん、こんにちは。
マイクロソフト コミュニティをご利用いただき、ありがとうございます。
ピボットテーブルで思うような集計ができないのですね。
書いていただいた内容を拝見すると、あとはリレーションシップを利用したピボットテーブルの作成を行うと良いと思います。
下記の手順で試してみてください。
【手順】
1. ピボットテーブルを作成するセルを選択し、[挿入] タブ > [ピボットテーブル] をクリックします。
2.「ピボットテーブルの作成」画面で [外部データ ソースを使用] をオンにし、[接続の選択] をクリックします。
3.「既存の接続」画面で [テーブル] タブを選択 > リレーションシップの設定によって作成されたデータ モデル (結合された 2 つのテーブル) を集計元データとして選択し、このデータ モデルを選択して[開く] をクリックします。
4. 集計に必要なフィールドを配置します。
藤田 香 – Microsoft Support
[この回答で問題は解決しましたか?] にて評価にご協力をお願いします。
返信が問題解決に役立った場合は、 [はい] を押すことで回答とマークされます。
問題が未解決の場合や引き続きアドバイスを求める場合は、 [返信] からメッセージを送信してください。
[いいえ] を押しても、未解決であることは回答者には伝わりません。
不適切な発言
ありがとうございます。
報告されたコンテンツが送信されました
この回答が役に立ちましたか?
お役に立てず、申し訳ございません。
素晴らしい! フィードバックをありがとうございました。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。
藤田様
早速ご教示くださり、ありがとうございます。
あいにくリレーションシップを利用したピボットの作成はできておりまして、
実際に、質問のデータを元に同じ方法を取ったところ、やはり上記□の現象になってしまいます。
【実施した手順】
1.エクセルに質問と同じ各テーブルを各シートに準備
2.パワーピボットタブで「データモデルに追加」として、それぞれのテーブルを追加
3.パワーピボット管理ウインドウを表示、「ダイヤグラムビュー」でNo.間を一((1)側のテーブル)対多((2)側のテーブル)で結合
4.パワーピボット管理ウインドウ左上、エクセルボタンで、エクセルウインドウを表示
5.以下、ご教示戴いた手順をトレース
------------------------
1. ピボットテーブルを作成するセルを選択し、[挿入] タブ > [ピボットテーブル] をクリックします。
2.「ピボットテーブルの作成」画面で [外部データ ソースを使用] をオンにし、[接続の選択] をクリックします。
3.「既存の接続」画面で [テーブル] タブを選択 > リレーションシップの設定によって作成されたデータ モデル (結合された 2 つのテーブル) を集計元データとして選択し、このデータ モデルを選択して[開く] をクリックします。
4. 集計に必要なフィールドを配置します。
------------------------
6.ピボットテーブルができるが… □になってしまう
・フィールドは「行」に(2)テーブルの”分類”、「Σ値」に(1)のNo.を設定
(以下、現象の再掲)
※ ピボットのフィールド設計画面で列と値に入力すると
「テーブル間のリレーションシップが必要である可能性」が出てきてしまいます。
でも、管理画面では既にリレーションは表示され、アクティブになっています。
□ こちらが出てきてしまう…
【(3)ピボット集計したいもの】分類 No.の数 ((1)のNo.を使用するとこうなってしまう)
A 3
B 3
C 3
■ 本当はこちらを出したいが…
【(3)ピボット集計したいもの】
分類 No.の数 (現状でも(2)のNo.をカウントすればこれが出る)
A 2
B 2
C 1
お手数おかけしますが、再度ご教示のほど、宜しくお願いいたします。
不適切な発言
ありがとうございます。
報告されたコンテンツが送信されました
この回答が役に立ちましたか?
お役に立てず、申し訳ございません。
素晴らしい! フィードバックをありがとうございました。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。
手元の環境でも試してみたのですが、書かれているように「□ こちらが出てきてしまう…」の状態になってしまいますね。
「テーブル間のリレーションシップが必要である可能性」も再現されました。
「■ 本当はこちらを出したいが…」とは少し違う状態になってしまうのですが、分類テーブルの「分類」とマスターテーブルの「説明」をともに行または列に、分類テーブルの「No.」を値欄で個数に、とすることで近い表示にできるかと思いましたので、よければ確認してみてはいかがでしょう。
ピボット テーブルのフィールド
ピボット テーブル
+ マークを展開
近藤 茂 – Microsoft Support
[この回答で問題は解決しましたか?] にて評価にご協力をお願いします。
返信が問題解決に役立った場合は、 [はい] を押すことで回答とマークされます。
問題が未解決の場合や引き続きアドバイスを求める場合は、 [返信] からメッセージを送信してください。
[いいえ] を押しても、未解決であることは回答者には伝わりません。
不適切な発言
ありがとうございます。
報告されたコンテンツが送信されました
この回答が役に立ちましたか?
お役に立てず、申し訳ございません。
素晴らしい! フィードバックをありがとうございました。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。
近藤様
ご確認の上、対応策のご案内、ありがとうございます。
おっしゃるとおり、確かに出来るのですが、、、、
今回の「説明」テーブルには(説明用モデルのため省略していますが)その他カラムも複数あります。
ご教示戴いた方法ですと、行フィールドに複数の列を盛り込む事になるので、ちょっと煩雑になりすぎてしまうかなと考えております。
(下記、少々、レイアウトを変えていますが)
近藤様にご教示戴いたことを鑑みるにやはりこのような事はエクセルでは現状出来ない、と考えたほうが良いのでしょうか。。。
不適切な発言
ありがとうございます。
報告されたコンテンツが送信されました
この回答が役に立ちましたか?
お役に立てず、申し訳ございません。
素晴らしい! フィードバックをありがとうございました。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。
実際に作成されているファイルだと、前回紹介の方法はそぐわないのですね。
他に方法が無いか調べてみたのですが、こちらでは良いアイデアは浮かばなかったです。お役に立てずごめんなさい。。。
この問題について、他に何か情報をお持ちの方がいらっしゃいましたら、書き込みをお待ちしています。
近藤 茂 – Microsoft Support
[この回答で問題は解決しましたか?] にて評価にご協力をお願いします。
返信が問題解決に役立った場合は、 [はい] を押すことで回答とマークされます。
問題が未解決の場合や引き続きアドバイスを求める場合は、 [返信] からメッセージを送信してください。
[いいえ] を押しても、未解決であることは回答者には伝わりません。
不適切な発言
ありがとうございます。
報告されたコンテンツが送信されました
この回答が役に立ちましたか?
お役に立てず、申し訳ございません。
素晴らしい! フィードバックをありがとうございました。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。
近藤様
返信ありがとうございます。
そうですね・・・残念ですが、ご容赦ください。ご教示戴きましてありがとうございました。
ご覧の各位様、引き続き、ご提案をお待ちしております。宜しくお願いいたします。
不適切な発言
ありがとうございます。
報告されたコンテンツが送信されました
この回答が役に立ちましたか?
お役に立てず、申し訳ございません。
素晴らしい! フィードバックをありがとうございました。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。おかげで、サイトの改善に役立ちます。
この回答にどの程度満足ですか?
フィードバックをありがとうございました。