|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
「StiLL」デザイン情報118 ピボットテーブルの活用2 |
|
|
|
|
|
ピボットテーブルの基本的な作り方と操作について |
|
|
|
データのグループ化 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
■ 明細データを月別に集計したい場合、「グループ化」の設定を行うことで簡単に月別の集計結果を得ることができます。集計する単位を変更することで、四半期等の集計へ簡単に変更することができます。 |
|
|
■ 4月〜翌年3月のデータを使って集計表を作成すると、翌年1月〜3月のデータが表の左側に配置されてしまいます。項目を並べ替えるには「フィールドのオプション」で、「自動並べ替えオプション」を「手動」にします。 |
|
|
■
グループ化の機能はグループ化するフィールドのデータが日付型でなくてはなりません。日付型でない場合はExcel関数でデータを変換したフィールドを作成します。StiLLの「レコード取得」機能を使えば、ボタン一つで変換したデータを作成できます。 |
|
|
|
|
対応Ver.
:「StiLL-]」Ver1.5以降
対応種類:StiLL Dev |
|
|
対応Ver.
:「StiLL-]」Ver1.5以降
対応種類:StiLL Dev |
|
|
対応Ver.
:「StiLL-]」Ver1.5以降
対応種類:StiLL Dev |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
■ 「グループ化」の設定で簡単に明細データを集計できます。 |
|
|
|
|
|
今回はピボットテーブルの「グループ化」についてご紹介します。 |
|
|
|
例えば、下図のような売上の明細データを月別に集計することを考えます。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ピボットテーブルを作成したら、フィールドを下図のように配置します。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
もちろんこれでは日別の集計となってしまいます。 |
|
|
そこで、月別の集計とするための「グループ化」の設定をします。 |
|
|
|
@ フィールド名「年月日」のところで右クリックし、[グループと詳細の表示]→[グループ化]をクリックします。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Excel2007以降の場合は、フィールド名のところで右クリックして、[グループ化]をクリックします。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A 下図ダイアログでグループ化する単位を選択します。 |
|
|
今回は月別にしますので「月」を選択して[OK]をクリックします。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
B すると、下図のように月別に集計された結果に変わります。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
集計する単位を変更したい場合は、上記@〜Bの手順を行い、Aでグループ化する単位を変更します。 |
|
|
下図のような四半期ごとの集計表にもすることができます。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
■ 任意の並び順に並べ替えたい場合は項目名をドラッグして並べ替えができます。 |
|
|
|
|
|
|
|
|
|
前述の通り、明細データを簡単にグループ化できるのがピボットテーブルの最大の特徴なのですが、 |
|
|
下記のような問題もあります。 |
|
|
|
|
|
例えば、年度の区切りが4月〜翌年3月で、年度単位の集計表を作成するとします。 |
|
|
上記の手順で月別にグループ化すると下図のような集計表が出来上がりますが、1月〜3月が列の左側に |
|
|
なってしまっています。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
これはグループ化したときの標準の表示形式が「○月」であり、その順番に並べられてしまっているためです。 |
|
|
1月から3月までを12月の後ろに表示させるには、下記の操作を行います。 |
|
|
|
@ 「年月日」の項目名のところで右クリックし、[フィールドの設定]をクリックします。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A 下図のダイアログにて「詳細」をクリックします。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
B 「自動並べ替えオプション」で「手動(アイテムをドラッグして並べ替える)」を選択して[OK]をクリックします。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
C [1月]のセルを選択し、[12月]の右側へドラッグすると、[1月]の項目が[12月]の右側に移動します。 |
|
|
2月、3月も同様にドラッグして移動させます。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
また、四半期でグループ化すると下図のような表が作成されますが、ここでの「第1四半期」は翌年1月〜3月の |
|
|
集計となってしまいます。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
この場合、上記@〜Cの手順で「第1四半期」の列を「第4四半期」の右側へドラッグします。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
その後、「第2四半期」を「第1四半期」、「第3四半期」を「第2四半期」、…「第1四半期」を「第4四半期」を |
|
|
手入力で修正しますが、同じ名称を重複して入力することができないため、「第2四半期」の後ろにスペースを |
|
|
追加するなどして、すでに存在している名称と重複しないようにしてください。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
このように項目の配置を変更したピボットテーブルをテンプレートとして、別のデータの集計を行う場合も、 |
|
|
並び順が変更されてしまうことはありません。 |
|
|
|
|
|
■ 日付型データの追加はStiLLの「レコード取得」機能で。ボタン1つで作成可能です。 |
|
|
|
|
|
今回ご紹介した「グループ化」の機能を使う上で重要なのは、グループ化するフィールドのデータが必ず日付型 |
|
|
でなければならないという点です。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
他のシステム等からデータを取得するような場合、日付のデータが「20100401」のように、数値や文字列として |
|
|
登録されていることがあります。 |
|
|
このような場合、今回の「グループ化」の機能を使うためには工夫が必要になります。 |
|
|
|
下図のように、集計する明細データに項目(下図では「日付」という項目名にしています。)を追加し、 |
|
|
ExcelのDATE関数を使って、数値や文字列となっているデータを日付型に変換したデータを作成します。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ピボットテーブルを作成する際は「使用するデータの範囲」を追加したデータの項目を含めた範囲(上図の |
|
|
例の場合A列からQ列まで)を指定し、ピボットテーブルには下図のようにフィールドを配置します。 |
|
|
後は先にご紹介したように、下図の「日付」のフィールドをグループ化します。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
この方法の注意としては、データの件数が増えた場合、増えた件数分数式をコピーする必要があります。 |
|
|
|
|
|
|
|
|
増えたデータ |
|
|
|
|
|
|
|
|
StiLLの機能「レコード取得」を使えば、日付型に変換したデータをボタン一つで簡単に作成することができ、 |
|
|
データの件数が増えるたびに数式をコピーする手間が省けます。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
さらに、「レコード取得」機能で取得したデータのある範囲に範囲名をつけ、ピボットテーブルの「使用するデータの |
|
|
範囲」を範囲名で指定することにより、データの集計漏れを無くすことができます。 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Copyright(C)
アイエルアイ総合研究所 無断転載を禁じます |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|