Excelのピボットテーブルについて

質問

Excelで添付画像のようなピボットテーブルを作成することはできるのでしょうか。
列範囲に曜日と時限、行範囲に使用教室、値に教科名を表示させたいのですが、
値に表示されるのが「データの個数」や「合計」等の数値のみとなってしまいます。
解決法がございましたら、ご教示くださいますようお願いいたします。

回答No.8

山田 太郎（@f_a_007） · ベストアンサー

【補足】対策2とは？

　それは、[教室名]と[時限]を条件に[教科]を検索して表示することです。添付図では、まず、ピボットで表を生成しています。その後に、教室名と時限とを完全に消去しています。で、その後、参照式を書いて科目名を表示しています。これが、私の推奨方式です。

=Dlookup("SELECT 教科 FROM [Sheet3$A1:D1000] WHERE 教室='" & A2&"' AND 時限=" & B1)

　これは、エクセルの関数に容易に置き換えられます。で、一つ作ればツツーッでいかると思います。

【注意事項】全てのセルの書式を初期値に戻すこと。

お礼

ご回答いただきありがとうございます。
ベストアンサーをどなたにするか悩みましたが、
何度もご丁寧にご教示いただいたf_a_007様をベストアンサーにさせていただきました。
ややこしい箇所もありますがチャレンジしてみたいと思います。

回答No.11

山田 太郎（@f_a_007）

どうしても、マクロ一発でやりたい時は、SQLWriterの使い方を案内します。

回答No.10

山田 太郎（@f_a_007）

【何度もスマン】多分、これが一番簡単！

シコシコ作業だが、一番確実かも！

回答No.9

山田 太郎（@f_a_007）

【念のために】曜日＋時限の場合を！

回答No.7

山田 太郎（@f_a_007）

【お詫び】ピボットでは無理が真相みたいですね。

　今、ピボットテーブルに初挑戦。やっていること、集計クエリ―ウィザードでした。が、マイクロソフトは、丁寧にも型変換をしているようですね。それが、[科目]が表示されない原因です。

対策１、SQLWriter()を利用する。
対策２、Excelの機能を使って《科目》を表示する。

　対策１は、添付図のように可能。対策２も、割と簡単に実現できると思います。私のお勧めは、対策２です。対策１は、たった１行のマクロで実行可能。でも、対策2がお勧めです。なぜなら、対策2は、質問者でも簡単に実現できるからです。

回答No.6

山田 太郎（@f_a_007）

もちろん、Max()でもFirst()でも一緒だと思います。

回答No.5

imogasi

#3です。VBAの初歩的なロジックしか、使えてませんが、参考に。
コード行数でどの程度でできるか興味があったのでやってみた。
ーー
例データ
Sheet1（シート名はこれに合わせてください）　A1:D42
教室 曜日 時限 科目
A 月 1 図
A 火 1
A 水 1 数
A 木 1 国
A 金 1 理
B 月 1 理
B 火 1 理
B 水 1 図
B 木 1 理
B 金 1 数
C 月 1 英
C 火 1 国
C 水 1 図
C 木 1
C 金 1 音
D 月 1 社
D 火 1 音
D 水 1
D 木 1 社
D 金 1 国
A 月 2 数
A 火 2 社
A 水 2 国
A 木 2
A 金 2 図
B 月 2 国
B 火 2 図
B 水 2 英
B 木 2 数
B 金 2 社
C 月 2 数
C 火 2 理
C 水 2 理
C 木 2 図
C 金 2 技
D 月 2 理
D 火 2 技
D 水 2 国
D 木 2 技
D 金 2 技
手を抜いて、第2時限までしか作ってません。
質問者の第２、第３時限の例を取り出し、真似たつもり。
ーー
結果表　Sheet7（シート名は合わせてください）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 ←列番号　　　　　　　　　　　　　　　　　　　　　　　　　　　　＜－1行目
2
月 火 水 木 金 3
1 2 1 2 1 2 1 2 1 2 4
A 図 数 社 数 国 国 理 図 5
B 理 国 理 図 図 英 理 数 数 社 6
C 英 数 国 理 図 理 図 音 技 7
D 社 理 音 技 国 社 技 国 技 8
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　行番号↑
４、７，１０、１３列は空白
見出しの部分は、あらかじめ手作業で作成しておく（A列と第１、３、４行目の文字） （VBAでもできるが、本筋とずれて、複雑になるので略）
標準モジュールに
Sub test02()
Set sh1 = Worksheets("Sheet1")
Set sh7 = Worksheets("Sheet7")
'--最終行
Lr = sh1.Range("A100000").End(xlUp).Row
'--行データを対象に繰り返し
For i = 2 To Lr
x = sh1.Cells(i, "A") '教室
y = sh1.Cells(i, "B") '曜日
Z = sh1.Cells(i, "C") '時限
'－－
r = sh7.Range("A:A").Find(x).Row
c1 = sh7.Range("A3:X3").Find(y).Column
c2 = sh7.Range("A4:X4").Find(Z).Column
'MsgBox x & " " & y & " " & Z & "=" & r & " " & (c1 + C2 - 2)
sh7.Cells(r, c1 + c2 - 2) = sh1.Cells(i, "D")
Next i
End Sub
上記を実行する。
データ行数は、時限などのデータが増えても影響ない。
同じ項目のデータがダブってあったら、最後の出現分が残る。
見出しの、教室、曜日、時限、のSheet7での出現セルの場所は、列内・行内で動かしてもOKのつもり。Findで文字を探しているから。
「時限」の数字は半角でやったが、全角でも、プログラムを少々変えれば（全角変換）動く予想。

お礼

ご回答いただきありがとうございます。
また後程試してみたいと思います。

回答No.4

山田 太郎（@f_a_007）

【補足の補足】ピボットテーブルの考え方だけですが・・・

　私は、ピボットテーブルの作成メニューがどこにあるのかも知らないど素人。それなのに、考え方を講釈するのはおこがましいのですが・・・。そこは、恥もなにも捨て去った70の爺。怖い物しらずで、以下駄文を書き連ねます。

　私は、ピボットテーブル機能ってのは《クロス集計クエリ―生成ツール》の一種に過ぎないと推察します。質問の場合は、次のようです。

TRANSFORM Count(教科) AS 教科のカウント
　　SELECT 教室, Count(教科) AS [合計 教科]
　　　　FROM [時間割$A1:Z100]
　　　　GROUP BY 教室
PIVOT 時限;

　これが、[教室]が縦軸で横軸が[時限]の表構造を生成し《GROUP BY 教室》毎の集計を表示させるSQL文です。ですが、このSQL文が表示するのは《Count(教科)》です。質問者は、ここでストップしている状態だと推察します。

＞アレレ、こりゃー困った！

ということで、先のSQL文を修正。

修正１、《Count(教科) AS [合計 教科]》・・・消す
修正２、《Count(教科) AS 教科のカウント》を
　　　　《Max(教科) AS 教科の最大》に置き換える。

TRANSFORM Max(教科) AS 教科の最大
　　SELECT 教室
　　FROM [Sheet3$A1:Z100]
　　GROUP BY 教室
PIVOT 時限;

※※※※※※※※※※※※※※※※※※※※※
　'国'という文字列の最大値は'国'である。
※※※※※※※※※※※※※※※※※※※※※

　で、無事に私のテストは成功したってことです。まあ、考え方だけですが、何某かの解決のヒントになれば幸いです。なお、添付図は、上のSQL文の実行結果を示すものです。

回答No.3

imogasi

小生の勉強不足で、恥をかく結果になる、かもしれませんが、
ピボットテーブルの「値フィールドの設定」のΣ値のところに、集合関数のなどの「計数」（SUM,COUNTなど、結果は１つの数値）を設定するのが、ピボットは主目的で、たとえ、そのグループでは、１つしかなくても、文字のような値を「足したりの加工せずに」そのまま持ってくるのは、できないのではないですか。
ーー
私もエクセルには、「表の組み換え」的な機能のサポートが不十分で、VBAなどに頼らざるを得ないようだ。これができれば、適した場合には、便利になるのに、と思ったことは、あります。
＝＝
既出の回答で、これが出来そうな、様子をおっしゃっていますので、私も勉強させてもらいます。

回答No.2

山田 太郎（@f_a_007）

【補足】何気なくやってみたら・・・

　添付図のような[Sheet4]が出来上がりました。質問者も、同じことをやっているんですよ。これは、ピボットテーブル初心者なら、誰でも経験することと推察します。ですから、（[Sheet3]を提示されたら）容易に解決する案件です。

回答No.1

山田 太郎（@f_a_007）

Q、解決法がございましたら・・・
A、それは不明だが・・・

　ちょいと、質問者がやっていることを再現してみました。

質問者：値ではなくて「データの個数」や「合計」等が表示される。
　　私：あらら、本当だ。
　　　：[XX　の合計]も、邪魔だな・・・。
　　　：おい、教科のカウントじゃーねーよ。
　　　：教科名を表示しろよ。

と、スッタモンダ。まあ[時限]は、テストを簡略化するために数字にしています。普通は、リストボックスでしょうから同じこと。上が[Sheet3]で、下が[Sheet4]です。私の場合は、ピボットテーブルを作成する手順を省いていきなり[Sheet4]を作成してみました。（って、いうか、それしかできない！）

　まあ、過去に一度も、エクセルを操作したことも門外漢。私が、行った、小さなテスト。そういうテスト題を出されて質問されたらどうでしょうか？で、このテストで成功することが解決への第一歩だと思いますよ。今の状態では、逆算にて[Sheet3]を考え、そこから[Sheet4]での同じ現象の再現と修正の仕方を検証することになります。それまで、回答者に要求するのはどうかな？

　そういうことで、私が示した[Sheet3]に相当する表を示しての再質問されることをお勧めします。