2017年08月15日

複数のシートを結合してデータチェックをしてみた / Excel / ピボットテーブル

Excelのピボットテーブルでは、複数のワークシートを結合したピボットテーブルを作ることができるのだけど、用途はデータ集計や分析と言うより予め集計が終わっているクロス集計表を複数のワークシートで結合する様な用途に使用するものなのだけど、応用例として、複数のワークシートであるデータの有無をチェックする(正しく言うと、データの個数を数える)ことができます。

こんな、ある塾の生徒の氏名とテストの得点のクロス集計表があります。このテストは毎月1回行っていて、シートが月毎に分かれています。
ここで、欠席してテストを受けなかった生徒を複数のワークシートの結合でピボットテーブルを作って調べてみようと思います。
20170815_01.JPG

1月のワークシートで、「ピボットテーブル/ピボットテーブルウィザード」のアイコンをクリックします。(このアイコンは予め表示させておいてくださいね)
20170815_02.JPG

「ピボットテーブルウィザード」画面が開きます。
「複数のワークシート範囲」にチェックを入れます。
20170815_03.JPG

“ページフィールドの作成方法”を「自動」にします。
20170815_04.JPG

“結合するワークシートの範囲”を選択します。
まず、シート“1月”のテーブルの範囲を選択して、「追加」を押します。
すると、「範囲一覧」に追加されます。
20170815_05.JPG

同じ様に2月のテーブルの範囲も追加します。
20170815_06.JPG

3つのシートのテーブルを結合するワークシート範囲として指定します。
20170815_07.JPG

ピボットテーブルの作成先を「新規」に指定します。
20170815_08.JPG

すると、この様なピボットテーブルができました。
フィールドは「行」、「列」、「値」、と自動で作成された「ページ1」と言うのが出来ています。
生徒の名前と1月から3月までの各テストの合計点数が表示されています。
20170815_09.JPG

「ページ1」の▼をクリックして展開してみると、“アイテム1〜3”と表示されています。
これでは何のことだか分かりませんが、結合したワークシートのことなので“1月”、“2月”、“3月”のことです。
20170815_10.JPG

で、“値”(得点)をフィールドリストにドラッグドロップで戻して代わりに“行”(氏名)を持ってきます。氏名は数値ではないので自動的に“データの個数”になっています。これでOKです。
また、“列”(学科)を“列ラベル”から“レポートフィルター”に移動します。
20170815_11.JPG

1回のテストでの個数を1個とカウントさせたいので、レポートフィルタの“列”(学科)を“国語”だけにフィルターをかけます。
20170815_12.JPG

するとこの様な集計になって、真木さんが1回、東出さんが2回しかテストを受けてないことが分かります。
20170815_13.JPG

んで、何月のテストを欠席したのか知りたいですよね。
「列ラベル」に“ページ1”を持ってきます。
すると、アイテム毎の回数が表示され、真木さんはアイテム1と2が欠席、東出さんはアイテム3が欠席しています。
20170815_14.JPG

でも、こんなんじゃいくらなんでも分かりずらいので、ちゃんと名前を付けましょう。
と、いうことで、ピボットテーブルにカーソルを置いて「ピボットテーブルウィザード」のアイコンをクリックします。
20170815_15.JPG

すると「ウィザード」画面が最後から開きます。
「戻る」します。
20170815_16.JPG

さらに、「戻る」します。
20170815_17.JPG

ここで、“ページフィールドの作成方法”を「指定」に変更します。
「次へ」します。
20170815_18.JPG

範囲指定の画面になります。
ここで、“ページフィールド数”を「1」にチェックします。
「範囲一覧」から、まず1月の範囲を選択して、
「フィールド1」に“1月”と手入力します。
20170815_19.JPG

同様に2月の範囲を選択して“2月”と入力
20170815_20.JPG

3月の範囲は“3月”と入力します。
これで「完了」します。
20170815_21.JPG

すると、この様に“ページ1”の中身に名前が付きました。
20170815_22.JPG

なので、“ページフィールドの作成方法”は最初から「指定」にした方がいいです!



関連ページ
複数のシートからピボットテーブルを作ってみたら使い勝手が悪かった
ピボットテーブルウィザードを起動してみた




posted by haku1569 at 17:36| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。