Excelのピボットテーブル操作にて、意外と知られていないのが、上の画像のグループ化の空白を埋めて全項目をフル表示する操作です。
私は会社の同僚から何度も聞かれたことがあり、また、取引先の会社から、グループ化・空白のままの大量のピボットテーブルを値貼付で毎月送付され、関数を組んで空白を埋めています。
今回はこれらのピボットテーブルの空白を埋めるレイアウトの変更と、関数について記載します。
空白を埋めるレイアウトの変更
グループ化した項目の空白を埋めて全表示する変更は、Excelの項目（「レポートのレイアウト」、「アイテムのラベルをすべて繰り返す」）に組み込まれており、簡単に行うことができます。
▼ピボットテーブル内のセルにフォーカスすると、赤枠の「デザイン」のメニュー項目が表示されますので、これを押下します。
▼左の「レポートのレイアウト」のプルダウンを開き、「アイテムのラベルをすべて繰り返す」を選択します。
▼左は変更前、右は変更後ですが、「デバイスの種類」の空白のアイテムを繰り返し、全表示となりました。サンプルデータはシンプル化しているため、グループ化の項目は一つのみですが、グループ化した項目が複数ある場合にも、空白のアイテムを繰り返して表示されます。
ピボット値貼付データに関数を入れ、アイテムを全表示
会社の取引先から、毎月 大量・データ項目の多いピボットテーブルを値貼付で受領しています。値貼付のために上記のレイアウトの変更は使えず、関数を入れて対応しています。
▼上記も含めてシンプル化していますが、E列からG列がピボットテーブルの値貼付、C列とD列が関数を入れてアイテムを繰り返したものです。本来はD列の関数のみでよいのですが、シンプル化のためにC列も表示しています。
▼C列の関数は、E列が空白の場合には上の項目を表示、空白でない場合にはE列を表示しています。なお、E6セルが空白であることを考慮していません。
=IF(ISBLANK(E6),C5,E6)
▼D列の関数は、「総計」が表示されるのを回避するため、IF / ISBLANK関数を2個づつ組み合わせています。
=IF(ISBLANK(F6),””,IF(ISBLANK(E6),D5,E6))
よりよい方法があるとも思われますが、私の場合、上記の関数により対応しています。また、実際に取引先から受領するデータは、ピボットテーブルの値貼付が複数シートあるのですが、別のシートにリンクを張り、リンク先のシートに関数を組み込み、ピボットテーブルの値貼付で完了するようにしています。
コメント