オフィスソフト

「Excel」の平均値計算の応用--最大値と最小値の除外、トリム平均など

「Excel」は特殊な平均値の計算にも対応している。最大値と最小値を除いて平均値を求める方法や、上位と下位の一定の割合の値を除外する方法などを紹介する。

 これは単純な関数だが、特定の値ではなく、指定した割合の値を除外したい場合もあるだろう。次に見ていくのがその方法だ。

Excelでトリム平均(Trimmed mean)を求める方法

 「Mean」が「Average」(平均)であることは、ご存じの人が多いだろう。ほとんどの場合、違いは用語だけだ。数学者はAverageを使用し、統計学者はMeanを使う。ただし、Meanにはさまざまな種類があるため、Meanが必ずしもAverageであるとは限らない。Arithmetic mean(算術平均)とAverageは同義語だが。

 この説明で不明点がいくつか解消されたところで、ExcelのTRIMMEAN()関数を紹介しよう。これは、指定した割合の上位の値と下位の値を除外する関数で、トリム平均とも呼ばれる。外れ値が極端すぎて結果をゆがませてしまう場合に、外れ値を除外して、もう少し現実的な平均値にするために使う。

 TRIMMEAN()は最大値付近と最小値付近の指定した割合の値を除外するため、最大値と最小値しか除外されないこともあれば、最大値付近と最小値付近の2つ以上の値が除外されることもある。平均的なユーザーがこの関数を必要とすることはなさそうだが、包括的に説明するためにここで紹介している。

 この関数は以下の形式を使用する。

TRIMMEAN(配列,割合)

 「配列」は評価される値を参照し、「割合」は最大値付近と最小値付近から除外する値の数を意味する。割合は、0.2または20%のいずれかの形式で入力できる。図Cに示すように、今回の小さなデータセットでは、AVERAGE()でもTRIMMEAN()でも同じ値が返される。この場合、割合が0.2なので、除外する値の数として返されるのは1だ(5つの値の20%は1つ)。この値は、両端、すなわち最大値と最小値の間で等分される。もちろん、0.5個の値を除外することはできないので、どの値も除外されない。2つの関数の結果は、割合を40%(0.4)に変更するまで同じままだ。

図C

TRIMMEAN()は予期せぬ結果を返す場合がある。
TRIMMEAN()は予期せぬ結果を返す場合がある。

 このように、TRIMMEAN()が今回のデータセットであまり現実的でない結果を返したことは少々残念だ。次は、現実的な結果を返す数式を見ていこう。

Excelで平均から最大値と最小値を除外する方法

 平均値の計算で、最大値と最小値、すなわち外れ値を除外するのは一般的なことだ。教師や統計学者はよくそれらを除外する。今からやることは前のセクションの関数と似ているが、TRIMMEAN()が指定した割合を除外するのに対し、この例では、指定した割合の値ではなく、最大値と最小値だけを削除する。

 図Dには以下の数式が示されている。

=(SUM(B4:B8)-(SUM(MAX(B4:B8)+MIN(B4:B8))))/(COUNT(B4:B8)-2)

 長いが複雑ではない。多数の要素を含んでいるだけだ。一言で言えば、SUM(3,10,4)/3と同じ結果を返す。評価される値から1.5と64が除外されている。

図D

この平均にはデータセット内の最大値と最小値が含まれていない。
この平均にはデータセット内の最大値と最小値が含まれていない。

 データセットを使用して、この数式を評価してみよう。

=(SUM(B4:B8)-(SUM(MAX(B4:B8)+MIN(B4:B8))))/(COUNT(B4:B8)-2)
=(82.5)-(64+1.5)/(5-2)
=17/3
5.666666667

 筆者はこれより短い数式がないと確信しているわけではないが、この数式のロジックは機能する。データセット全体の合計から、2つの外れ値の合計(64+1.5)を引く。次に、その結果である17を3で割る。これは、評価される値が3つしかないためだ。データセットから2つの値だけを除外するので、必ず値の総数から2を引くようにしよう。

 平均値を計算するこれらの解決策は少し厳格だが、常にそのような厳格な制限があるわけではない。厳格な制限がある場合も、多少のノウハウがあれば対応できるはずだ。

提供:Wachiwit/Shutterstock
提供:Wachiwit/Shutterstock

この記事は海外Red Ventures発の記事を朝日インタラクティブが日本向けに編集したものです。

「オフィスソフト」で読まれている記事

TechRepublic Japanで人気の記事

編集部オススメ

トレンドまるわかり![PR]

財務・経理
人事・労務
マーケ・営業
購買・調達
生産・製造
データ分析
コミュニケーション
通信・通話
文書・コンテンツ
PC・モバイル
PC
スマートフォン
タブレット
ウェアラブル
オフィスソフト
OCR
RPA
PCソフト
周辺機器
新興技術
ITインフラ
クラウドサービス
OS・ミドルウェア
開発
データベース
運用
セキュリティ

ホワイトペーパーランキング

  1. 5分でわかる、レポート作成の心得!成果至上主義のせっかちな上司も納得のレポートとは
  2. ノートPCは従来ながらの選び方ではダメ!新しい働き方にも対応する失敗しない選び方を徹底解説
  3. 問題だらけの現場指導を効率化!「人によって教え方が違う」を解消するためのマニュアル整備
  4. 緊急事態宣言解除後の利用率は低下 調査結果に見る「テレワーク」定着を阻む課題とその対応策
  5. たしか、あのデータは、こっちのアプリにあったはず…--業務改善のためのアプリ導入がストレスの原因に?

Follow TechRepublic Japan

このサイトでは、利用状況の把握や広告配信などのために、Cookieなどを使用してアクセスデータを取得・利用しています。 これ以降ページを遷移した場合、Cookieなどの設定や使用に同意したことになります。
Cookieなどの設定や使用の詳細、オプトアウトについては詳細をご覧ください。
[ 閉じる ]