« パワーポイントで整った印刷物を作ろうとするな! | トップページ | Excelの表から重複するデータを省く。 »

Excelで、0や未入力のセルを無視して平均を出す。

この記事は、自分用の覚え書きです。

エクセルで、セルに数字の0(ゼロ)が入っていたり、未入力のセルがある表の中で平均を求める。

■みなが理解しやすいやり方が一番よいやり方。

なんであれ、「どんなやり方をするべき」かというものはない。関わる人たちが理解しやすいやり方をするのが一番よい。

どんなやり方をするにしろ、業務に関わる人たちができるかぎり理解しやすいやり方をすること。

業務で作るファイルは自分のものではない。
みんなのものなので、自分以外の人が見てもわかりやすいように作っておくのが大事。

■文字列を入れておくことを検討する。

「空白のままでなければならない」、「0 を入れておかねばならない」といった具体的な理由がないのであれば、文字列を入れておくやり方がある。

空白のセルや 0 を入れているセルに、「不明」や「NA (Not Applicable)」、「ゼロ」といった文字列を入れておけば、AVERAGE関数で平均を求めるときも、SUM関数で合計を求めるときも、文字列が入ったセルは無視してくれる。
数字の 0 を入れたセルは、SUM関数では無視して合計するが、AVARAGE関数では無視してくれずにデータの個数として数えられてしまう。

このやり方であれば、何をしているのかを誰もが理解しやすい。

■関数を使って処理するやり方

1.平均値を表示したいセルを選択。

2.選択したセルに、次の数式を入力。
  =SUM(平均を出したいセル範囲)/(COUNT(平均を出したいセル範囲)-COUNTIF(平均を出したいセル範囲,0))

(例)平均を出したいセル範囲がA1:A10なら
=SUM(A1:A10)/(COUNT(A1:A10)-COUNTIF(A1:A10,0))

上の式なら、指定したセル範囲のうち、0が入っているセルと、未入力のセルも除いたデータで平均が計算される。

SUM関数は、「セル範囲に含まれる数値をすべて合計」する。
COUNT関数は、「範囲内の数値が含まれるセルの個数」を返す。
COUNTIF関数は、「指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数」(ここでは数値の 0 の個数)を返す。

未入力のセルは無く、必ず0が入っているという場合は、次のような式でもよい。

=SUM(A1:A10)/COUNTIF(A1:A10,"<>0")
(この式の場合、未入力のセルがあると平均を計算するときにデータの個数として数えられてしまう。)

Excel2007以降なら AVERAGEIF関数が使える。未入力のセル、0が入っているセルが混在してても平均を返してくれる。
=AVERAGEIF(A1:A10,"<>0")

※一つのセルに式を入れることにこだわらない。

一つのセルに複数の関数を重ねた式を入れなければならない具体的な理由があるか? を考える。

それが無いのなら、「一つのセルに全部の式を入れたい」という単なる「自分の好み」でしかない。
エクセルを仕事で使う際に、「自分の好み」でファイルを作ると、周囲への迷惑になることのほうが多い。
(関数を重ねた数式は読み解きづらい。)

作業セルを用いて、
1. SUM関数で「セル範囲に含まれるすべての数値の合計」を求める。

2. COUNT関数で「範囲内の数値が含まれるセルの個数」を求める。

3. COUNTIF関数で「指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数」を求める。(数値の0の個数)

4. 2. の答えから 3. の答えを引く。

5. 1. の結果を 4. の答えで割る。

というようにセルを分けて計算したほうが、計算過程で何をしているかが、誰でも理解しやすい。

冒頭の「文字を入れる」やり方をする際にも、数値が入った列はそのままにしておきたければ、その隣に一列――作業列を――追加して、そこに同じ値をコピーし、空白のセルや 0 を入れているセルに文字列を入れてやれば、その列でAVARAGE関数を使うことができる。

|

« パワーポイントで整った印刷物を作ろうとするな! | トップページ | Excelの表から重複するデータを省く。 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック


この記事へのトラックバック一覧です: Excelで、0や未入力のセルを無視して平均を出す。:

« パワーポイントで整った印刷物を作ろうとするな! | トップページ | Excelの表から重複するデータを省く。 »