【Excel関数】率の平均はAVERAGEでなくSUM/SUM

部署別の有給取得率の表を作り、最下行に「平均」を入れました。取得率の列をAVERAGE関数で平均すると62.0%です。ところが、取得日数と付与日数をそれぞれ合計して割ると44.2%になりました。同じデータなのに、なぜ数字が合わないのでしょうか。

この記事では、率の平均をAVERAGEで出してはいけない理由と、全体の率を正しく出す方法を解説します。率の列がある集計表で、合計・平均の行を作る方が対象です。

率の平均はAVERAGEでずれる

次の表（サンプルデータ）で考えます。取得率は「取得日数÷付与日数」です。

部署	付与日数	取得日数	取得率
営業	200	120	60.0%
経理	20	18	90.0%
総務	10	9	90.0%
製造	300	90	30.0%
開発	70	28	40.0%

取得率の列にAVERAGEを使うと、結果は62.0%です。

=AVERAGE(D2:D6)

全社では600日が付与され、265日が取得されています。実際の取得率は44.2%です。18ポイント近くもずれています。

全体の率は分子の合計÷分母の合計

全体の率は、分子と分母をそれぞれ合計してから割ります。

=SUM(C2:C6)/SUM(B2:B6)

結果は44.2%（265÷600）で、「全社の付与日数のうち、何割が取得されたか」と一致します。

なぜずれるのか

AVERAGEは、どの行の率も同じ重さで数えます。付与10日の総務（90%）も、付与300日の製造（30%）も、1票ずつです。

SUM/SUMは、日数に応じて重みがつきます。付与日数の多い部署が結果に強く効くので、全体の実態に近づきます。つまり加重平均です。

分母が全行で同じなら、2つの結果は一致します。実際に、全部署の付与日数を100日にそろえて試すと、AVERAGEもSUM/SUMも62.0%で同じでした。ずれの大きさは、分母のばらつきで決まります。

率と分母しかないときはSUMPRODUCT

他の人が作った表で、取得日数の列がなく、率と付与日数だけの場合もあります。この場合は、率に分母を掛けて分子を復元します。

=SUMPRODUCT(B2:B6,D2:D6)/SUM(B2:B6)

結果は44.2%で、SUM/SUMと一致します。SUMPRODUCTは、範囲を行ごとに掛けてから合計する関数です。詳しくはSUMPRODUCT 関数 – Microsoft サポートを参照してください。

ただし、率が丸められた値だと誤差が出ます。付与日数が3・7・9日、取得日数が1・2・4日の3行で試しました。

• 正しい全体の率：7÷19＝36.8%
• 各行の率を小数第2位に丸めてから復元：36.7%

差は小さいですが、丸めた率からの復元は正確な値になりません。可能なら、率だけでなく分子と分母も表に残しておくのが安全です。丸めの扱いは、時間の丸めは順序で結果が変わる理由と対処でも触れています。

よくある失敗

1. 分母が0の行でエラーになる

率を「=C2/B2」で出していると、付与0日の行は#DIV/0!になります。AVERAGEは、範囲にエラーが1つあると全体がエラーになります。サンプルに付与0日の行を足して試すと、AVERAGEはエラーでしたが、SUM/SUMは44.2%のままでした。

2. IFERRORで空文字にして気づかない

「=IFERROR(C2/B2,””)」でエラーを隠すと、AVERAGEは空文字の行を数えません。6行あるのに5行分の平均になっていても、見た目では分かりません。同じ条件で試すと、COUNTは5で、行数は6でした。

IFERRORで値を差し替えるときは、ORやANDの中でも、第2引数の選び方で結果が変わります。【Excel関数】OR・ANDは短絡評価されない原因と対処で実測しました。

空文字に置き換える前に、エラーの種類を確認しておくと、原因を見落としにくくなります。確認の手順は、【Excel関数】#N/A・#VALUE!・#REF!が出たら最初に確認にまとめています。

3. 空欄と0を混同する

AVERAGEは、空欄や文字列を無視しますが、0は数えます。60%と90%の間の行が空欄なら平均は75%、0なら50%です。「未入力」と「0%」で結果が変わります。

Power Queryでも、無い月を0で埋めると、前月に行が無いことと前月が0円だったことの区別がつかなくなり、状態の判定が崩れます。詳しくは、【Power Query】Table.Pivotの月の列は固定リストで渡すで紹介しています。

4. 最下行の名前が「平均」のまま

「平均」には、単純平均と全体の率の2つの意味があります。最下行の名前は「全体の率」にしておくと、取り違えが減ります。

応用例：拠点ごとの全体の率

拠点の列（E列）を使って拠点別の全体の率を出す場合は、SUMIFSを2つ割ります。

=SUMIFS(C2:C6,E2:E6,"東")/SUMIFS(B2:B6,E2:E6,"東")

サンプルでは、東が57.2%（166÷290）、西が31.9%（99÷310）です。AVERAGEIFSで東を出すと63.3%になり、同じ問題が起きます。

単純平均を使ってよい場面

「規模に関係なく、部署の典型的な取得率はどのくらいか」を知りたいなら、単純平均が目的に合います。大きい部署に引っ張られないからです。問題はAVERAGEそのものではなく、どちらを出したいかを決めずに使うことです。

検証環境

この記事の数値は、Excel 16.0（Windows、日本語環境）で確認しました。他のバージョンやMac版は未検証です。

まとめ

• 全体の率は「SUM(分子)/SUM(分母)」で出す
• 率と分母しかないときは、SUMPRODUCTで分子を復元する。ただし丸め済みの率は誤差が出る
• 拠点別などの条件つきは、SUMIFSを2つ割る
• 集計表には、率だけでなく分子と分母も残す