平均より大きい、平均、平均未満の3つの区分を作りたい。

質問

今、各団体の職員数をまとめた本表を作っています。これを「全体の平均値を上回る職員数」をもつ団体数ーＡ、「全体の平均と同数の職員数」をもつ団体ーＢ、「全体の平均未満の職員数」をもつ団体ーＣ
とした場合、Ａ、Ｂ，Ｃのそれぞれの累計を、もとの本表からすぐに求められる別表を作りたいと考えていますが、何かいい関数等をご存知の方はいらっしゃいませんか。
（例）
「本表」
　　　　●社ー10人、
　　　　○社ー15人、
　　　　△社ー20人、
　　　　□社ー18人・・・
　全体の平均、15人。
「別表」
　平均以上の職員数の会社の数（Ａ）ー5社
　平均職員数の会社の数　　　（Ｂ）ー3社
　平均未満職員数の会社の数　（Ｃ）ー5社」
　　　↑この別表を作りたいのです。

回答No.3

134 · ベストアンサー

A列に会社名、B列に職員数が入っているとして
別表範囲に

　列　　　　　　　　　　　　列
下回る　　|　　=countif($B:$B,"<"&average($B:$B))
平　均　　|　　=countif($B:$B,"="&average($B:$B))
上回る　　|　　=countif($B:$B,">"&average($B:$B))

として、平均数を下回る、平均、平均を上回る…
とできそうに思います。

お礼

ご教示いただいたやり方に、元データを「round」関数で、無理やり小数点以下を切り捨てたら、できました。ありがとうございます。他の皆様も本当にありがとうございました。

回答No.5

imogasi

ピッタシの関数FREQUENCYがあると思ったのだが、この質問に無理がありませんか。と言うのは、「平均職員数の会社の数」はデータによりますが、多くの場合ピッタシのものはなくて０になりませんか。幾らか幅を持たさないと。
（例データ）A1:A15
11
23
45
67
89
23
43
52
67
89
23
45
67
42
8
D1に「数値」と言う語句、
D2にINT(AVERAGE(A1:A15))
D3に=INT(AVERAGE(A1:A15))+6この６は私が勝手に考えた
幅
D4に=MAX(A1:A15)
E1に「頻度数」と言う語句
E2に=FREQUENCY(A1:A15,D2:D4)といれ、ShiftキーとCtrl
キーを左手指で押さえつつ、Enterキーを押す。
（結果）
数値 頻度
46 9
52 1
89 5
となります。
平均を中心に平均の属する帯を適当に設定して、３区分を考えてください。
平均のある中心の幅６で考えるならD2はINT(AVERAGE(A1:A15))-3
D3はINT(AVERAGE(A1:A15))+3となるのかな。

回答No.4

murachan

A列に会社名、B列に職員数、C列にIF関数で
「平均以上の職員数の会社」
「平均職員数の会社」
「平均未満職員数の会社」
という要素を入れて上げれば、
あとはピポットテーブルを使い別表をすぐに
作れます。

IF関数としては、以下のようなものがいいかと・・
=if(b1>average($b$1:$b$500),"平均以上",if(b1=average($b$1:$b$500),"平均",if(b1<average($b$1:$b$500),"平均未満","")))

※b1～b500までに職員数が入っているものとします。

ピポットテーブルを作るには、ウィザードを使えば簡単です。

回答No.2

antpos

Ａ列１～６に社名、Ｂ列１～６に適当な人数、Ｃ列に計算用スペース、Ｄ・Ｅ列に「別表」って形で考えてみました。仮に会社数が１００社あっても式はオートフィルすればいいから、そう気にならないと思い、下のようにしてみました。
まずＢ７に平均を求めました。そしてＣ１に=IF(B1=INT($B$7),2,IF(B1>INT($B$7),1,3))といれ、Ｃ７までオートフィル。それで「別表」の平均以上の職員数の会社数を入れたい部分に=COUNTIF(C1:C6,1)、平均とほぼ同数には=COUNTIF(C1:C6,2)、平均以下には=COUNTIF(C1:C6,3)としました。これでできるとは思います。（ＩＮＴ関数で平均の小数点以下は切り捨てました。こういった表では平均と同じってほぼないと思うんですけど。）
たぶんこんな仕方しなくてもできるのでしょうけど、私はエクセルの実務経験はないので、あまり詳しくありません。ＭＯＵＳ等の試験対策レベルで解くとこうなってしまいました。もっといいやり方があるとは思いますが…。

回答No.1

shige117

実際のデータになると、平均職員数などは小数点以下が発生する可能性が多くなると思いますが、その際の処理はどの様に考えていらっしゃいますか？平均職員数の前後の正数を平均職員数の会社とするのか、小数点以下を切り捨てるのか、四捨五入するのか等々色々な処置の方法がありますが・・・