Microsoft Excelでは平均化が簡単です。AVERAGEIF()関数とAVEREAGEIFS()関数を使えば、条件付き平均も簡単に計算できます。ただし、条件を動的にしたい場合は別です。例えば、月ごとの売上高の表を管理していて、過去n年間の平均化を求められたら、頭を悩ませ、少し戸惑うかもしれません。
幸いなことに、n個のリクエストに対応するのは想像以上に簡単です。このチュートリアルでは、よく使われる機能と関数を組み合わせて、過去n年間の月平均を返す方法を紹介します。この手法はダッシュボードにも最適です。
Windows 10 64ビットシステムでMicrosoft 365デスクトップを使用していますが、以前の.xlsxバージョンも使用できます。Web版Excelはこの手法を完全にサポートしています。ご参考までに、.xlsxデモファイルをダウンロードしていただけます。
参照: 機能比較: 時間追跡ソフトウェアとシステム (TechRepublic Premium)
Excelで平均を素早く求める方法
一度限りのタスクで平均売上を求める場合、このチュートリアルの大部分は必要ありません。代わりに、関数、数式、ピボットテーブルなどを使わずに、必要な情報を簡単に取得する方法があります。任意の期間の平均売上を取得するには、次の手順に従います。
- 平均化したい年の売上高を選択します。例えば、過去2年間（2021年と2020年）の平均売上高を求める場合は、C3:C26を選択します。
- タスクバーには、選択した値の平均が表示されます。この場合、図Aに示すように、1,356,157ドルです。
- タスクバーの平均をダブルクリックします。
- この平均を入力するセルを選択し、Ctrl + V を押します。
図A
図Bに示すように、タスクバーからシートに平均値をコピーできます。これは比較的新しい機能なので、まだご存知ない方もいるかもしれません。この値を再度計算する必要がない場合は、選択した値の平均値を返すこの簡単な方法をご利用ください。
図B
n 年間の平均売上を定期的に返す必要がある場合は、より永続的なものが必要になります。
Excelで日付から年を生成する方法
運が良ければ、データには年だけが単独で含まれているはずです。サンプルシート（図A）には、月ごとの日付と各月の売上値の2つの列があります。つまり、まず最初に、各日付に対応する年を列に入力する必要があります。実際には数十年分のデータが存在する可能性がありますが、ここでは簡潔にするために4年までとします。
列 B の各日付値の年を返すには、次の関数を D3 に入力し、残りのレコードにコピーします。
=YEAR([@Month])
テーブルを使用していない場合は、この関数を使用します。
=YEAR(B3)
図Cに結果を示します。この単純な関数がエラーを返した場合は、日付の値を確認してください。おそらく有効な日付ではないからです。次のステップでは、年の値の一意のリストに基づいて行列を追加します。
図C
ここで、評価される売上値を制限する n コンポーネントを操作する方法が必要です。
どのように対応すればいいですか？
次のステップは、評価対象となる年数を決定するn要素に対応することです。図Dには、いくつかの新しいラベルと式が示されています。列Iの式は、列Gで使用されている式です。
図D
簡単に言うと、G3は入力値であり、評価したい過去2年間の数を表します。例えば、「2」と入力した場合、過去2年間は2021年と2020年、3と入力した場合、過去3年間は2021年、2020年、2019年となります。
MAX() 関数は、売上テーブル (B2:D50) の最新の年を返します。最新の年をリテラル値として入力することもできます。どちらの方法でも機能しますが、Max() を使用すると、最終年を知る必要がなくなります。すべての処理を実行するためにデータをソートする必要はありません。データの量や取得元によっては、最新の年を視覚的に判断するのが難しい場合があります。
式 =G3-(G2-1) は、評価対象となる売上テーブルの中で最も古い年を返します。G3 は常に現在のデータが含まれる最後の年です。この式は、G2 の最新の年を減算することで、平均に含まれる最も古い年を決定します。
最後に見ていく式は=IFERROR(AVERAGEIF(Sales[Year],">="&$G$4,Sales[Sales]),"")、重要な処理の大部分を担います。AVERAGEIF() 関数は、条件要素「>=」&$G$4 を使用して、n で表される年を決定します。例えば、2 と入力した場合、この要素は次のように評価されます。
">="&$G$4
">="&2020
>=2020
したがって、AVERAGEIF() 関数は、列 D の年が 2020 以上の売上値のみを評価します。このテーブルでは、これは 2020 年と 2021 年を意味します。IFERROR() は、入力値が 1、2、3、または 4 でない場合に生成されるエラーをキャッチします。これは、テーブルに 4 年分のレコードしか含まれていないためです。
データ検証コントロールを使用して、1、2、3、4以外の入力値を拒否することもできます。G2を選択し、「データ」タブをクリックして、「データツール」グループから「データの検証」を選択します。表示されるダイアログで、「入力値許容範囲」ドロップダウンから「整数」を選択し、「データ」ドロップダウンから「範囲内」を選択し、「最小値」と「最大値」フィールドにそれぞれ1と4を入力します（図Eを参照） 。
図E
図Fに示すように、過去2年間の平均月間売上高は1,356,156.88ドルです。過去1年間、過去3年間、過去4年間を表すために、それぞれ1、3、4を入力してみてください。その後、0または5を入力してみてください。データ検証コントロールにより、無効な入力値は拒否されます。
図F
G2の値を削除して、何が起こるか見てみましょう。エラーが発生することを予想していましたか？ISERROR()関数はエラーをキャッチし、エラーではなく空の文字列（“”）を返します。
元のデータにテーブルオブジェクトを使用している限り、すべてが動的になります。そうでない場合、n に対応する式はレコードの追加や削除を行っても更新されません。
