データを扱う時のいろいろな操作 2
| ■ Excel豆知識40 |  |
|---|
40-2 複数のワークシート範囲からのピボットテーブルを普通のピボットテーブルに変える
<普通のピボットテーブル作成用のデータを作成する>
こんどは全部の点数が10では違いがわかりませんので、いろいろな点数の入ったデータでやっ てみます。（平均の数字はセルの書式で小数点以下第一位までの表示にしてあります。） 複数のワークシート範囲から作成したピボットテーブルはどうしても自由度が低く使いにくい ような気がします。それで、ピボットテーブルの数字のセルをダブルクリックするとそれを構 成しているデータの詳細が別シートに表示されるのを利用します。
（１）ピボットテーブルの「総計」と「総計」の交差するセルを選択します。
[図04016]
（２）そのセルでダブルクリックすると、新しいシートにそれを構成する全データが表示され ます。この時、「ページ1」のフィールドには「第１回」のような設定したアイテム名が入りま す。
[図04017]
（３）１行目の項目を適当なものに訂正します。
[図04017-2]
ふりがな情報が不要の場合は上の[図04017]の状態から普通のピボットテーブルが作成できます。
（４）並べ替えのための「フリガナ」の列を設定します。 この時、E2セルに =PHONETIC(A2) と入力すると 「阿武 雅子」と漢字で表示されます。 これはピボットテーブルの詳細で表示されたデータにフリガナデータがないことによります。
[図04018]
なので、A列にマクロでフリガナをセットします。
１. Alt+F11キーで VBEの画面を出します。
２. 左側のプロジェクト-VBAProjectで、このブックを選択します。
３. 「挿入」-「標準モジュール」で出てきた白いところに下記の５行のコードをコピー貼り
付けします。
４. ５行のコードの中にカーソルを置き、「Sub/ユーザーフォームの実行」をクリックします。
Sub フリガナをセット()
With Range("A:A")
.SetPhonetic
End With
End Sub
[図04019]
すると、下図のようにE2セルの表示が「アンノ マサコ」に変わりました。
[図04019-2]
E2セルの右下隅のマウスポインタが十字になったところでダブルクリックして、数式を下へコ ピーします。
[図04020]
これで準備が出来ましたので、このデータをもとに普通のピボットテーブルを作成します。
<普通のピボットテーブルを作成>
（１）リスト中の一つのセルを選択してピボットテーブルウィザードを出し、「完了」をク リック。
[図04023]
（２）ピボットテーブルの枠と、普通の項目が並んだフィールドリストが表示されます。
[図04023-2]
（３）「ふりがな」と「氏名」を行エリアへドラッグ。
[図04024]
（４）「アンザイ イチロウ 合計」とあるセルで右クリックし、「表示しない」をクリック。
[図04025]
（５）「科目」を列のエリアにドラッグ。
[図04026]
（６）「点数」をデータエリアにドラッグ。
[図04026-2]
（７）「回」をページエリアへドラッグ。
[図04027]
（８）「合計/点数」のセルで右クリックし、「フィールドの設定」をクリック。
[図04027-2]
（９）「集計の方法」で「平均」を選択し、「OK」。
[図04028]
（１０）下図のように小数点以下がうるさいので、「表示形式」をクリックし、
[図04028-2]
（１１）「セルの書式設定」で「数値」を選択し、「小数点以下の桁数」を「１」にして「OK」 そして「OK」。
[図04029]
（１２）並び順のためにこの位置においてあるふりがなを一応見えないようにする。 「ふりがな」という項目名は「 」（スペース）に置き換える。
[図04030]
（１３）フリガナの部分を選択し、文字色を白に。
[図04031]
（１４）いろいろな設定がピボットテーブルの「更新」により戻ってしまわないように、ピボ ットテーブルのオプションで、
[図04032]
（１５）「表のオートフォーマット」のチェックをはずし、「書式の保持」のチェックが入っ
ているのを確認して「OK」。
列幅が自動調整されたほうがよければ「表のオートフォーマット」のチェックは入れたままにしてください。
[図04033]
（１６）これで内容的には「複数のワークシート範囲」からのピボットテーブルとほぼ同じも のが出来上がり、「ふりがな」データがあることにより「氏名」もアイウエオ順にならんだ表 が出来ました。
[図04034]
（１７）普通のピボットテーブルになりましたから、あとは自由自在に項目を動かすことが出 来ます。 「回」を行エリアにドラッグすれば、
[図04035]
このようになりますし、
[図04036]
その「回」を行エリアの一番左にもってくればこのようになります。 この時、ふりがなのB列は非表示にしています。
[図04037]
ふりがなを利用した並べ替えのことを無視すれば、上記の形の集計は「複数のワークシート範囲」から
のピボットテーブルで作ることが出来ます。
サイト内FAQ
12. クロス表の形になっているアンケート結果のようなものをピボットテーブルで集計するには？
に説明を書いております。