Excelで最高値と最低値を含めた平均化と除外した平均化の方法 - TechRepublic

Excelでより高度な平均化を行う方法という記事では、Microsoft Excelの様々な平均化関数について解説しています。ここでは、これらの関数がゼロ値、ブール値、空の文字列値をどのように評価するかに焦点を当てています。ただし、これらの関数が常に適切であるとは限りません。実際、平均化関数を全く使用しない式が必要になる場合もあります。この記事では、いくつかのユニークな平均化ソリューションを見ていきます。

参照:すべてのユーザーが習得すべき Excel のヒント 69 選(TechRepublic)

Windows 10 64ビットシステムでMicrosoft 365を使用していますが、以前のバージョンでもご利用いただけます。デモ用の.xlsxファイルと.xlsファイルをダウンロードするか、ご自身のデータで作業することもできます。すべての機能はブラウザでサポートされています。

Excelの平均化の簡単な復習

AVERAGE() 関数についてよくご存知ない方のために、まずはこの関数について簡単に説明します。ご想像のとおり、この関数は参照先の値を合計し、その合計値を値の個数で割ります。セル、範囲、さらにはリテラル値を参照できます。この関数が非伝統的な値をどのように処理するかを覚えておくことが重要です。

テキストは無視されます
論理値、つまりブール値 TRUE と FALSE は無視されます。
空のセルは無視されます。
関数がエラー値を参照すると、エラーが返されます。

図Aに示すように、AVERAGE()は単純なデータセットの平均値を返します。得られる平均値は、合計と割り算をした場合と同じになります：=SUM(B4:B8)/5。このデータセットを少し複雑にしながら、引き続き操作を進めていきましょう。

図A

Excelで最高値と最低値のみを平均化する方法

データセット内の最高値と最低値を平均化するという要件はわかりにくいように思われますが、AVERAGE() が他の関数とどのように連携するかを確認するために、ここで取り上げます。このソリューションでは、AVERAGE()、MAX()、MIN() を組み合わせます。図Bは、この関数の結果を示しています。

=AVERAGE(MAX(B4:B8),MIN(B4:B8))

そして、式 (1.5+64)/2 が返す値と同じ 32.75 という値を返します。

図B

この関数がどのように動作するかは簡単にわかりますが、サンプルデータセットを使用して評価してみましょう。

=AVERAGE(MAX(B4:B8),MIN(B4:B8))

=平均(64,1.5)

32.75

この関数はシンプルですが、特定の値ではなく、ある割合の値を除外したい場合もあります。次は、その点について考えてみましょう。

Excelでトリム平均を取得する方法

おそらく「平均」は「平均値」であることはご存知でしょう。ほとんどの場合、違いは用語だけです。数学者は「平均」を使い、統計学者は「平均」を使います。しかし、平均には様々な種類があるため、すべての平均が「平均値」というわけではありません。ただし、「算術平均」と「平均」は同義語です。

参照：Windows 10：音声認識とディクテーションの音声コマンド一覧（無料PDF）（TechRepublic）

ここまでの議論でいくつか不足点が解消されたので、ExcelのTRIMMEAN()関数を紹介しましょう。この関数は、最大値と最小値の指定された割合を除外します。これは、トリム平均とも呼ばれます。これは、外れ値が極端に大きく、結果が歪んでいる場合に使用されます。値を除外することで、平均値はより現実的なものになります。

TRIMMEAN() は両端からパーセンテージを減算するため、最大値と最小値のみが減算される場合もありますが、両端から2つ以上の値が減算される場合もあります。平均的なユーザーがこの関数を必要とする可能性は低いですが、説明を分かりやすくするためにここに記載しています。

この関数は、

TRIMMEAN(範囲,パーセンテージ)

ここで、範囲は評価される値を参照し、パーセンテージは両端から削除する値の数を示します。パーセンテージは、0.2 または 20% のいずれかの形式で入力できます。図 Cに示すように、この小規模なデータセットでは、AVERAGE() と TRIMMEAN() の両方で同じ値が返されます。この場合、パーセンテージ 0.2 は削除される値 1 つを返します (5 つの値を 20% で割ると 1 になります)。この値は両端、つまり最高値と最低値に分割されますが、当然ながら値の半分を削除することはできないため、削除される値はありません。パーセンテージを 40% (0.4) に変更するまで、2 つの結果は同じままです。

図C