統計データなどで、詳細データを取り纏めて、「詳細データ>小計>合計」などの形に作表することがよくありますね。このように作表した場合、中項目の中項目の合計だけ、あるいは大項目の合計だけなど、必要に応じて表の見せ方を変えたいときもあると思います。
例えば、日次のデータを月単位>四半期単位>年単位に纏めるなどで集計した表を作成するなどは、皆様もきっとされたことがあるでしょう。この時、日次データを非表示にして、四半期およに年単位の集計データを見たいということもありますね。
もちろん、行や列の非表示/再表示で処理しても構わないのですが、その都度、行(または列)を選んで非表示にしたり、再表示したりするのは、特に大きな表の場合にはかなり面倒です。また、ピボットテーブルを作成するのもちょっと難しい、と感じる方も居られるでしょう。
Excelには行や列をグループ化して表示/非表示を切り替えることができる機能が用意されていて、これをアウトラインと呼んでいます。アウトラインを使うことで、簡単な操作で表を折りたたみ(データを非表示に)したり、展開(データを再表示)したりできます。
Excelの複数の行(または列)をグループ化すれば、アウトラインが作成されます。また「アウトラインの自動作成」機能を用いれば、行(および/または列)がグループ化されて、アウトラインが作成されます。
任意の複数の連続する行(または列)を選択しておいて、グループ化する方法です。以下、行の例で解説します。
Excelデータのグループ化の手順は以下の通りです。
グループ化がされると、シートの行番号の左(または列番号の上)にアウトライン表示欄が出現し、グループ化された行(または列)の間に傍線が引かれます。さらに「-」(折りたたみ)ボタンが出現します(図2)。
【便利知識】
グループ化が行(または列)単位に行います。一部のセルだけを選択してグループ化しようとすると、「グループ化」のダイアログボックスが現れて、「行」と「列」のいずれかをグループ化するか問われます。
【便利知識】
グループ化できるのは、連続する行(または列)のデータだけです。「Ctrl」キーを使って離れている複数の行(または列)を範囲選択してグループ化しようとしても、「複数の選択範囲に対してこの機能を実行することはできません」というエラーメッセージが出て、はじかれてしまいます。
前述のグループ化の手順を繰り返せば、別のグループを作成することができます(図3)。
【便利知識】
但し、別にグループ化した行(または列)の範囲が既にグループ化されている範囲に隣接している場合には、別のグループにならずに、既存のグループと一体化してしまいます(図4)。
仮に、間にダミーの行を挿入してグループ化をすれば別のグループになりますが、そのダミーの行を削除すると、やはり一体化してしまいます。
したがって、グループ化は任意の連続する行(または列)に対して行うことができるのですが、一般的には、図5のように、小計欄などの何らかの区切りの行(または列)が存在して、グループ化された行(または列)とその次のグループにしたい行(または列)が隣接しない場合に用いられます。
行をグループ化すると、「-」(折りたたみ)ボタンは通常傍線の下側(列の場合は右側)に現れます。これは、多くの場合データを集計した行が下側(列の場合は右側)に設けられているからです(図6)。
図6で、行番号2~5を折りたたむと。行番号6が残って、そこに「+」(展開)ボタンが表示され、その上に折りたたまれたグループのデータがあることが分かります(図7)。
作表の仕方によっては、集計した行を先に表示してあとから詳細データを続けるということもあります
(図8)。
この様な時にそのままグループ化すると、折りたたまれたデータが次の行(または列)に関連する形になってしまい、おかしなことになります(図9)。
【便利知識】
上述のように集計データが先に表示されて後から詳細データが続く時は、「アウトラインの設定」を変更します。
具体的には、
これにより、「-」(折りたたみボタン)は傍線の上側に表示され、折りたたみのされ方が正常化します(図10)。
図11の例のように。月ごとの集計に加えて、四半期ごとの集計もしている場合には、四半期単位のグループ化の下に、月単位のグループ化を行う、という風に、グループ化も階層化しておくと便利です。
複数階層をグループ化する手順は、下位の階層から先にグループ化しても、逆に上位の階層から先にグループ化しても、どちらでも構いません。
複数階層のグループ化を行うと、アウトライン表示欄も複数行になります(図12)。
【便利知識】
Excelでは、最大8階層までグループ化ができます。
ただし、あまり階層が深いと、かえって見づらい表になってしまいますので、階層の見せ方はほどほどにすることをお勧めします。
「-(折りたたみ)」ボタンをクリックすればグループ化された行(または列)は非表示となり、代わりに「+」(展開)ボタンが出現します(図13)。
「+」ボタンをクリックすると、非表示であったグループ化された行(または列)が元の形に再表示されます(図14)。
グループ化されると、アウトライン表示欄には、階層を示す数字ボタン(アウトライン記号)が表示されます(図15)。
最上位の階層が「1」で、表示されている数字の最も大きなものが最下位、つまり詳細データの階層です。
最下位の階層以外の数字ボタンを押すと、その階層よりも下位の階層のグループが、一気に折りたたまれます。
図15の例で、行のアウトライン表示欄の数字で説明します。
【便利知識】
最下位の階層のアウトライン記号(つまり表示されている最大の数字ボタン)をクリックすると、すべてのグループが一気に再展開されます。
【便利知識】
折りたたまれたグループを個々に再展開するには、「+」ボタンを押します。
上位レベルの「+」ボタンを押して展開すると、下位のグループも合わせて展開されます。
行範囲(あるいは列範囲)を選択して、いちいちグループ化の操作をしていくのは、表が大きくなればなるほど大変です。
Excelには、「アウトラインの自動作成」機能が用意されていて、条件さえ整っている表であれば、行および列のグループ化をいっぺんに行うことができます。
「アウトラインの自動作成」の手順は以下の通りです。
すると、Excelが自動的に表の構成や数式の有無などを判断して、自動的に行および列のグループ化を行います(図17)。
【便利知識】
「アウトラインの自動作成」では、Excelは数式のある行と列を認識して、複数階層の判断を行います。該当すれば自動的に複数階層のグループが作成されます。
「アウトラインの自動作成」機能は便利なのですが、残念ながら以下のような時には使えません。
表がテーブル化されている時
テーブル内の任意のセルを選択して、「グループ化」のボタンを展開しても、右図のように「アウトラインの自動作成」はグレイアウトされています(図18)。
但し、個々の行(または列)グループを選択してグループ化することはできます。
表のデータに数式が含まれていない時
「アウトラインの自動作成」をクリックしても、「アウトラインを作成することができません。」というメッセージウィンドウが表示されることがあります。
これは、その表の中に数式が設定されている行と列が無いからです。
どこからどこまでをひとつのグループにするかも、Excelは数式を解析して判断しているのです。
設定したグループを解除する手順は以下の通りです。、
これにより、その行(または列の)グループ化は解除され、アウトライン表示欄から傍線と「-」(または「+」)ボタンが消えます。
シート内のグループをすべて解除すれば、アウトライン表示欄そのものも表示されなくなります。
【便利知識】
グループ化する際は、連続する行(または列)を選択する範囲毎に、都度グループ化する操作が必要でしたが、グループ解除は、複数のグループをまとめて(極端に言えば、表全体を)グループ解除を行うことができます。
【便利知識】
複数階層でグループ化されている場合、上位階層のグループの行(または列)範囲を指定してグループ解除すれば上位階層のグループが、下位階層のグループの行(または列)範囲を指定してグループ解除すれば回階層のグループが解除されます。
また、表全体の行(または列)を選択してグループ解除を行うと、上位階層から順に、1つの階層の全グループが解除されます。つまり複数階層がある場合には、少なくともその階層の数だけグループ解除の操作を行わなければ、すべてのグループを消すことはできません。
グループ化されている行(および/または列)を含む表で、いずれかのセルを選択しておいて、「データ」タブの「アウトライン」欄にある「グループ解除」項目右の「▼」をクリックして「アウトラインのクリア」を選択すればすべてのグループが解除されます。、
「日付データとタイムライン」のページで解説した通り、Excel2016からは、日付けデータが自動的に「月」、「四半期」、「年」単位にグルーピングされるようになりました。
ピボットテーブルでは、その他にも任意の複数項目をグルーピングしたり、数値フィールドをグルーピングしたりできます。
上述のグループ化は、行(または列)をまとめて折りたたんだり(非表示)、再展開したり(再表示)するための機能でしたが、ピボットテーブルのグループ化は複数の項目などをまとめて上位の項目を作る機能で、性質がまったく異なります。
ソースデータに遡って複数の項目データを集計する列を作るなどをしなくても済むので、上手に使えば大変便利といえます。
行または列の任意の複数の項目をグルーピングして、上位のフィールドにすることができます。
具体的な手順は以下の通りです。
2の代わりに、「ピボットテーブル分析」タブの「グループ」グループの「グループの選択」をクリックしても構いません。
以上で、グルーピングが終わり、項目名・フィールド名とも分りやすいものに設定できました。
【便利知識】
同じソースデータで、同じフィールドを対象としたピボットテーブル(ピボットグラフ)が複数ある時は、いずれかのピボットテーブルでグループ化したら他のピボットテーブルにもそのグループ化が反映されます。但し、項目名とフィールド名はExcelが自動生成したままですので、必要に応じてそれぞれのピボットテーブルごとに変更してください。
グループ化を解除するには、グループ化された数値範囲のセルを選択して、「ピボットテーブル分析」タブの「グループ」グループの「グループ解除」をクリックするか、あるいは、右クリックし、ショートカットメニューから「グループ解除」を選びます。
ピボットテーブルの行(または列)の項目が数値データの場合には、数値の範囲別にグルーピングができます。
図25(ソースデータ)と図26(ピボットテーブル)の例で解説します。
図26のピボットテービルは、4教科の平均点とその点数に該当する人数(個数)というものです。
このままのデータでは分析がしにくいので、平均点の範囲別にグループ化するという例です。
手順は以下となります。
2の代わりに、「ピボットテーブル分析」タブの「グループ」グループの「グループの選択」をクリックしても構いません。
ピボットテービスが平均点の範囲でグルーピングされました(図29)。
グループ化を解除するには、グループ化された数値範囲のセルを選択して、「ピボットテーブル分析」タブの「グループ」グループの「グループ解除」をクリックするか、あるいは、右クリックし、ショートカットメニューから「グループ解除」を選びます。