Excelを使ってグループの平均年齢を計算する3つの方法 - TechRepublic

Excelではデータの集計はよく行われるタスクですが、通常は複数の方法があります。Susan Harkinsが、複数のグループの平均を返す3つの方法を説明します。

3行以上のデータがある場合、それらの値を何らかの方法で集計する必要がある可能性が高いでしょう。集計は、Excelで最も頻繁に行うタスクの一つです。「集計」という言葉は、グループを意味します。このグループはデータセット全体の場合もあれば、複数のグループの場合もありますが、概念は同じです。データセット全体を1つのグループとして集計するのは簡単で、おそらく実装に特別な知識は必要ありません。複数のグループを集計するには、もう少し手間がかかります。

この記事では、フィルター機能、小計機能、そしてピボットテーブルの作成という3つの集計方法を紹介します。さらに、複数のグループを条件として、より複雑な集計方法も紹介します。具体的には、これらのツールを使用して、図Aに示すレコードのグループ別の平均年齢を算出します。

図A

特定のグループの平均年齢を返します。

ご参考までに、サンプルデモの.xlsまたは.xlsxファイルをダウンロードできます。手順はExcel 2013に基づいていますが、大きく異なる場合は他のバージョンの手順も記載します。

フィルター

まずはExcelの組み込みフィルタリング機能から見ていきましょう。良くも悪くも、一度にフィルタリングできるのは1つのグループだけです。まず必要なのは平均化関数です。AVERAGE()関数の使用も考えられますが、おすすめしません。データセット全体を評価するには便利ですが、データセットをフィルタリングしても結果は更新されません。代わりに、レコードをフィルタリングするたびに更新されるSUBTOTAL()関数を使用してください。

この関数は引数を使用して、構文を使用して参照された値を数学的に評価する方法を決定します。

SUBTOTAL(関数番号, 参照1, 参照2, …)

ここで、functionnumber は表 Aのいずれかの値です。

図 B は、データセットの上に数行を挿入し、関数 =SUBTOTAL(1,B4:B13) を追加した結果を示しています。

図B

SUBTOTAL() を使用します。

表A

SUBTOTAL() を作業に適用するときに知っておく必要がある動作がいくつかあります。

SUBTOTAL() は、二重カウントを避けるためにネストされた小計を無視します。
SUBTOTAL() は、「行を非表示」コマンドによって非表示になっている行の値も計算に含めます。このように非表示になっている行の値を SUBTOTAL() で無視したい場合は、関数の数値に 100 を加算します。例えば、平均を求めるのに 1 ではなく 101 を使用します。
SUBTOTAL() は、関数の番号に関係なく、フィルターによって非表示になっている行を無視します。これが私たちのやっていることです。
SUBTOTAL() は複数シートの参照をサポートしていません。

SUBTOTAL() 関数を挿入したら、次のようにフィルタリングできるようになります。

データセット内の任意のセルをクリックし、「データ」タブ（「並べ替えとフィルター」グループ内）で「フィルター」をクリックします。または、[Ctrl]+[Shift]+[L]キーを押すことでもフィルターを適用できます。Excel 2003では、「データ」メニューから「フィルター」を選択し、「オートフィルター」を選択します。Excelは各列のヘッダーセルにドロップダウンリストを表示します。
「年齢」ドロップダウンをクリックします。
表示されたリストで「数値フィルター」を選択し、「範囲」を選択します（図C）。Excel 2003では、「（カスタム）」を選択します（
図C）。
最初のコントロールに「10」と入力し、2番目のコントロールに「19」と入力します（図D）。デフォルトの演算子は「AND」なので、変更しないでください。Excel 2003では、条件を指定する必要があります。最初のドロップダウンから「以上」を選択し、2番目のドロップダウンから「以下」を選択します。
図D
「OK」をクリックすると、年齢の値が10歳から19歳までのレコードのみが表示されます。図Eに示すように、B1のSUBTOTAL()関数は、フィルター処理されたグループの平均年齢である16歳を返します。
図E

フィルタリングは簡単に実装できますが、必要なときに毎回グループごとにフィルタリングする必要があります。その一方で、予期せぬ詳細を尋ねられたときに真価を発揮します。例えば、次回の会議で上司から「20～29歳グループの平均はいくらですか？」と尋ねられた場合、わずか数クリックで答えが得られます。素晴らしいですね！

小計

フィルタリングは、単一のグループを素早く表示するには適しています。すべてのグループの評価結果を一度に確認したい場合は、Excelの小計機能を使用してください。ただし、最初にデータセットを並べ替える必要があるため、これは一部の人にとっては面倒な場合があります。また、この機能にはアンカー、つまりグループの開始位置と終了位置を決定する値が必要です。現時点では、データをグループ化するための基準がないため、グループはまだ定義されていません。フィルターの例に沿って、年齢グループを0から10を基準に、0～9、10～19、20～19、というように分類します。

この時点で、各年齢をそれぞれのグループに関連付ける方法が必要です。そのためには、図Fに示すグループデータテーブルを追加し、LOOKUP()を使用して各レコードの適切な年齢グループラベルを返します。

図F

年齢層テーブルを作成します。

E列の値は各グループの最低年齢を表しており、LOOKUP()関数で適切に処理されています。F列のラベルには、最低年齢と最高年齢がテキストで含まれています。この表は手動で生成する必要がありますが、E列に0と10を入力すれば、オートフィル機能を使ってその列を補完できます。Excel 2013では、フラッシュフィル機能を使ってF列を補完できます。フラッシュフィルの詳細については、「Excel 2013のフラッシュフィルでニーズを即座に予測」をご覧ください。（完璧さは期待できませんが、便利です。）

データセットにグループ化ラベル (列 C) を追加するには、最初のレコード (C4) に次の式を入力し、残りのレコードにコピーします。

=LOOKUP(B4,$E$3:$F$13)

この関数は、列Bの年齢値を参照し、グループデータテーブル（列Eと列F）の最初の列（列E）で、年齢値と一致するかそれより小さい最初の値を検索します。次に、対応する列Fの値を返します。例えば、最初の年齢値は3です。列Eの2番目の値は10なので、LOOKUP()は前の値0を評価し、グループラベル0～9を返します。正しく機能するには、列Eの参照値が数値順に並んでいる必要があります。前述のように、グループ化値も並べ替えられている必要があります。

次に、Excel の小計機能を使用して、新しいグループ化値でデータを評価します。

小計機能が動作するには、これらの値を並べ替える必要があるため、「年齢」列の任意のセルをクリックします。
[ホーム] タブの [編集] グループで [並べ替えとフィルター] をクリックします。
「小さい順から大きい順に並べ替え」を選択します。Excel 2003では、「標準」ツールバーの「昇順で並べ替え」を使用します。
データセットを並べ替えた後、「データ」タブをクリックし、「アウトライン」グループの「小計」をクリックします。Excel 2003では、「データ」メニューから「小計」を選択します。
最初のドロップダウンからグループを選択して、グループ化値を識別します。
「関数の使用」ドロップダウンから「平均」を選択します。
最後のドロップダウンから「年齢」を選択します（図G）。必要に応じて他の列の選択を解除します。
図G
「OK」をクリックすると結果が表示されます（図H）。
図H

図Iに示すように、集計行のみを表示するには、左側の「小計」ペインで「2」をクリックします。「1」をクリックすると合計行が表示され、「3」をクリックするとすべてが表示されます。B20のエラーが見えるように、年齢の値は意図的に空白にしています。この場合、これは問題ではありません。小計行を削除するには、データセット内をクリックします。「データ」タブの「小計」をクリックし、表示されるダイアログで「すべて削除」をクリックします。

図I

詳細レコードを非表示にします。

ピボットテーブル

ピボットテーブルを挿入することは、グループ化された平均を評価するもう一つの方法です。ピボットテーブルを使用すると、基になるデータの構造を変更することなく、データを整理し、わかりやすく視覚的に要約することができます。.xlsx形式でピボットテーブルを生成するには、次の手順に従います。

データセット内の任意の場所をクリックします。最初に並べ替える必要はありませんが、前のセクションで使用したグループデータテーブルとグループ化ラベル（LOOKUP()関数）は必要です。
「挿入」タブをクリックし、「テーブル」グループの「ピボットテーブル」をクリックして、ドロップダウンリストから「ピボットテーブル」を選択します。表示されるダイアログボックスで、範囲「ピボットテーブル設定!$A$3:$C$13」が正しく、「新しいワークシート」オプションが選択されていることを確認します（図J）。「OK」をクリックします。（「ピボットテーブル設定」は、データセットが保存されているシートの名前です。）
図J
「年齢」フィールドを「値」リストにドラッグします。ドロップダウンから「値フィールドの設定」（図K）を選択します。
図K
表示されるダイアログで「平均」（図L）を選択し、「OK」をクリックします。
図L
グループフィールドを行リストにドラッグします（図M）。
図M

Excel 2003 をご使用の場合は、「データ」メニューから「ピボットテーブルとピボットテーブルグラフ」を選択し、「完了」をクリックしてウィザードを起動します。ピボットテーブルのフィールドリストを使用して、データ領域に「年齢」を追加します。表示されたフィールドを右クリックし、「フィールドの設定」を選択して「平均」を選択し、「OK」をクリックします。次に、行領域に「グループ」フィールドを追加します。

この時点で作業は完了しているかもしれません。ただし、図Nに示すように、「名前」フィールドを「行ラベル」リストにドラッグすることで、詳細レコードをテーブルに追加できます。

図N

詳細レコードを表示します。

ピボットテーブルは最も効率的な方法のように思えるかもしれません。別のシートに配置して、データとは別に使用できます。詳細レコードの表示/非表示も簡単に切り替えられます。一方、データセットの値を更新した場合は、新しい値を反映するためにピボットテーブルを更新する必要があります。これが最大のデメリットです。基になるデータを変更した後、ピボットテーブルの更新を忘れてしまう人が後を絶ちません。

グループの要約

複数のグループでデータを集計する際に、フィルター、小計機能、ピボットテーブルの作成という3つの組み込み機能を使うと、素早く結果を得ることができます。それぞれに長所と短所があります。データの内容と、それをどのように評価・報告したいかを考慮して、適切なものを選んでください。この記事では説明していませんが、数式を使用することもできます。

Officeに関するご質問をお送りください

Microsoft Officeに関する読者からの質問には可能な限りお答えしますが、必ずお答えできるとは限りません。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングをして、問題点を修正してください」といった質問ではおそらく回答が得られないでしょうが、「この数式が期待どおりの結果にならない理由を教えていただけますか？」といった質問なら、回答が得られるかもしれません。ご使用のアプリとバージョンを明記してください。TechRepublicは、私の時間や専門知識に対して報酬を支払っていませんし、読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。

記事をシェア