Excelを使った高度な集計 クロス集計(1)
データベースというのは単なるデータの集合体にすぎませんから、実際には膨大なデータの中から、必要なものだけを取りだして集計表を作成するという使い方をしています。集計表を作成する際に、1つの項目(性別あるいは出欠だけ)に基づいて集計するという場合もあれば、2つの項目(性別と出欠の両方)に基づいて集計表を作成する場合もあります。後者のように、2つの項目に基づいて集計することを「クロス集計」と呼んでいます。(下図参照)どちらが役に立つかは一目瞭然でしょう。
Excelにはクロス集計を行うツールとしてピボットテーブルという機能が設けられています。マウス操作だけでクロス集計ができる便利な機能ですが、元データが更新されても、そのままではピボットテーブルの内容に反映されないという弱点があります。(惜しいですね。)
そこで、今回と次回はピボットテーブルを使わずにクロス集計する方法をそれぞれ書いておくことにします。今回はデータベース関数とデータテーブルを組み合わせてクロス集計を行うという手法です。
まず、完成状態の図を掲載しておきます。
クロス集計のために作成したのは、図の薄く緑色で着色した部分です。意外とシンプルであることがおわかりいただけるのではないでしょうか。
最初の行には、データベースの「性別」フィールドに入力されている値である「男」と「女」が入力してあります。また、最初の列には、データベースの「出欠」フィールドに入力されている値である「○」「退職」「転出」「「着任」「×」の5種類が入力されています。
一方、セルO21には次の計算式が入っています。
=DCOUNT(B4:E30,"会費",O29:P30)
DCOUNT関数は、データベース関数の一種で、データベースの中の条件に合致したデータの個数を数えるときに使います。その書式は次の通りとなります。
データベースの範囲 B4:E30
集計するフィールド名 ”会費”
集計条件が設定されている範囲 O29:P30
この場合、「データベースの範囲B4:E30の中で、会費というフィールドについて、O29:P30に設定されている条件に合致するデータの個数を数える」という意味になります。このとき条件設定範囲であるO29:P30の1行目はデータベース範囲のフィールド名(「性別」「と出欠」)に一致していなければなりません。また、フィールド名の指定は文字列を” ”(半角)で囲む必要があります。(他にデータベース範囲の左側から何番目の列、という番号で指定することもできます。)
しかし、実際にはセルO30:P30は空白であり、何の条件も入力されていません。また、計算式もセルO21に入力されているほかは、「計」の列と行にSUM関数が使われているだけです。これで本当にクロス集計ができるのか疑問に思われるかもしれませんが、Excelには「データテーブル」という機能があって、今回はそれとデータベース関数を組み合わせて使います。
データテーブルというのは、「数式 で 1 つまたは 2 つの変数を変更したときに数式の結果にどのように影響するかを示すセル範囲です。」(オンラインヘルプより) これを利用すると1回の操作で複数の結果を表示させることができます。ちなみに、ここで変数として扱うのが、集計条件が設定されている範囲として指定したセルO29:P30になります。つまり集計条件として変数となるのが「性別」と「出欠」であり、それぞれ2通り×5通りのデータがあるのですから、合計10通りの集計結果を一度に表示してくるのがデータテーブルという機能になります。なんだか便利そうでしょ。
「計」の行と列にはそれぞれ横計と縦計と計算するためのSUM関数を入力しておきます。
(データテーブルの使い方)
(1)まず最初にデータテーブルとして使用する範囲(O21:Q26)をドラッグします。このとき、範囲の先頭セル(O21)にはデータベース関数が入力されていなければなりません。また、データテーブルの最初の行と最初の列には変数として用いる名称(「男」「女」「○」「×」など)が入力されている必要があります。
(2)Excel2007以降では、「データ」タブの「What-If分析」から「データテーブル」を選択します。Excel2003以前では、「データ」メニューから「テーブル」を選択します。
(3)すると「データテーブル」というダイヤログボックスが表示されるので、下図のように、「行の代入セル」の欄のところにカーソルが点滅している状態で、セルO30をクリックします。欄内には $O$30 と表示されます。「行の代入セル」というのは、「データテーブルの中で変数として使用するために行方向(横方向)に設定されている値を代入するセル」という意味です。ここでは、行方向で使用する変数の値として最初の行に入力されている「男」「女」が用いられるので、DCOUNT関数で集計条件が設定されている範囲として指定した O29:P30 のうち「性別」の下のセルであるO30を指定することになります。
同様に、列の代入セルにはP30をクリックして指定します。
(4)「OK」をクリックします。
(おさらい)
データベース関数は、データベースの中で条件に合致したデータについて集計するためのものです。ここではDCOUNT関数を使っていますが、他にも合計値を求めるDSUM関数などがあります。条件を設定するためには、フィールド名を2つ並べ、その下に条件とする値を入力します。(セルO29:P30)
例にO30に”男”、P30に”○”と入力すると、セルO21には15という数値が表示されます。
しかし、ここではデータテーブルで用いる変数を代入するための領域として使用するので、O30とP30は空白のままとしておきます。
データテーブルで間違いやすいのは「行の代入セル」と「列の代入セル」ではないでしょうか。「行の代入セル」とは、データテーブルの中で「行方向に設定されている値を変数の代入値としてどこに代入したらいいか」を指定するためのものです。したがって、行方向に設定されている値が含まれているデータベースのフィールド名(ここでは「性別」)に基づいて指定することになります。
イメージとして、データテーブルの中の変数の値を順番にDCOUNT関数に代入した結果をテーブルに表示させていると理解するとわかりやすいかもしれません。
そのほかのポイントとして、
・データテーブルの先頭セルには数式が入力されていること。
・データテーブルの最初の行と最初の列には、変数の代入値として用いられる値が入力されていること。
以上に気をつければ、それほど難しいものではありません。
データベース関数とデータテーブルを組み合わせたクロス集計では、データベースの値を修正すると、その結果がただちにデータテーブルに反映されるというのが特徴です。ピボットデーブルではこれができません。
また、クロス集計表のレイアウトや体裁などの集計表作成の自由度もこちらの方が高いと思います。
最初は取っつきにくいかもしれませんが、一度慣れてしまうと便利ですから、活用されることをお勧めします。
次回は、データベース関数もデータテーブルも使わずにクロス集計する方法について書くことにします。今回の手法に比べ原始的なだけに集計表を作成する手間は比較的かかりますが、今回同様、データベースの変更が反映されるうえに、集計する項目が3つ以上の場合でも使うことができるやり方です。(データテーブルは2つの項目まで集計可能)
どちらが使いやすいかは、その人次第というところでしょうか。