エクセル関数について教えてください。

質問

エクセル関数について教えてください。
例：ロードレース大会の順位によって５段階の評価を自動的につけたいんですが・・・
１位：Ａさん・・・１０位：Ｂさん・・・３６位：Ｃさん・・・
を上位から評価をつける（５段階）
評価５：１０％　　評価４：３０％　　評価３：５０％　　評価２：１０％
総人数から１位から何位までは「５」、何位までが「４」というように自動的に入るようにしたいんですが、わかる方教えてください。

回答No.4

noname#79209 · ベストアンサー

#1です。
#3に書いた「#2です」は「#1」の間違いです。mshr1962さんゴメンナサイ・

さて、#1の補足に対してですが、
> この表の数値になる意味
VLOOKUPの特性と関連しているので、以下の方で解説します。

> 何故、（５５－１）になるのか？とか・・・
「データが2行目から55行目に入っているとして」と断り書きをいれたはずですが、
全参加人員の数を出したかったので「55-1」としましたが、
#3でも言及したように、不定な（毎回変わる）数値はキメ打ちすべきでは無かったです。
COUNTIF等を使って、その都度自動的に分母（参加人数）を算定すべきでしょう。
> =VLOOKUP(INT(RANK(B2,$B$2:$$B55,1)*100/(55-1)),Sheet2!$A$1:$B$5,2,TRUE)
> この数式の意味を教えてもらえたら助かるんですが・・・
これも、#3で言及したようにRANK関数の部部は不要で、かつ計算式がうまくないです。

そこで書き直すと、
=VLOOKUP(ROUNDUP(B2*100/(55-1),0),Sheet2!$A$1:$B$5,2,TRUE)
となります。
要は、上位10%の順位に対して「5の評価」をしたいわけです。
棄権、失格等が無く100人の参加者があったとして、
1位～10位が10%以内で、評価5
11位～40位が30%（10％超～40%）で、評価4
41位～90位が50%（40％超～90%）で、評価3
91位～100位が10%（90％超～100%）で、評価2
「評価1」はないので、#3で言及したように#1で示したものはうまくないのです。
Sheet2
　　　A　　　B
１　 0　　 5
２　 10.1　 4
３　 40.1 　 3
４　 90.1 　 2
５　 100.1 　 1
とすれば一応うまくいきます、※100.1に対する「1」はダミーです。
VLOOKUPの特性として、検索範囲の中に見つからなかった場合、
この例ではA列のなかで見つからないと、検索値より小さい最大数にポイントされます。
5%なら0、15%なら10.1、94%なら90.1がポイントされ、VLOOKUPの結果はそれぞれ、評価5と評価4と評価2が返されます。
ROUNDUP関数によって、検索値は必ず整数ですから、10.1や40.1がポイントされること決して無く、
必ずそれ以下の部分がポイントされます。
チェックの表に10や40を設定してしまうと、
Sheet2
　　　A　　　B
１　 0　　 5
２　 10　 　5
３　 10.1 　4
４　 40　 　4
５　 40.1 　3
６　 90　 　2
７　 90.1 　2
８　 100.1　 1
と無駄な行を設定しなくてはならなくなります。

お礼

どうもありがとうございました！
丁寧に教えていただいてとても感謝しています。
ついでで申し訳ありませんが・・・

１　　日本Ａ夫　男
２　　日本Ｂ子　女
３　　日本Ｃ子　女
４　　日本Ｄ夫　男
５　　日本Ｅ子　女
６　　日本Ｆ夫　男
このような名簿を作ったときに、別のシートに”男女別”に、しかも”自動的”に振り分けられていく設定ってあるんでしょうか。

回答No.7

noname#79209

#1です。
やはり教職関係の方でしたか。
実は家内がこの質問への回答を書くところを横から見ていて、
「何だか、学校の通信簿の評価みたい。」
「やっぱり、未だにパーセンテージで評価分けをしているのかしらね。」
と言っていました。
やっぱり女性の「カン」はコワイです。

それはそうと、エクセルの関数専用の参考書（VBAと書いてないモノ）を入手され、
拾い読みでも構わないので、日頃から目を通すことをおすすめします。
そして、その関数の具体的な使い方より、どんな結果が得られるかをを考えながら読むことです。
例えば、MODと言う関数は、割り算の余りを出すための関数です。
MOD（9,4）は「9÷4」の余りである「1」が算出できます。
しかし、「MOD関数は、割り算の余りを出す関数」とだけ憶えてしまうと、
ある数値から一の位を抜き出すのに「MOD(A1,10)」が使えるという発想が出てきません。
このように、関数のエフェクトをグローバルな視点で見ながら習得していくことをお勧めします。

お礼

一応、誤解の無いように申し上げておきますが、今やろうとしていることは、最終的な評定を出すための１つの材料とするものです。
現在は、『絶対評価』になっていますので、あくまでも通信簿は、一人一人の「伸び」を評価して５段階の評定を出します。
ですから、運動の苦手な子は成績が悪くなるというものではないことは理解してください。
ただ、保護者や子どもからの、成績に関する苦情もあったりするので、数値できちんと示せれば、説明責任を果たせるのかなということです。
ありがとうございました。

回答No.6

noname#79209

追加質問、特にこの場合は別の質問になるので、
他の質問者諸氏が同様の悩みで解法を探す際に探せなくなるので、
できれば新規の質問にしていただきたいです。
そうすれば、この質問に関係した回答者だけでなく、
より多くの回答者諸氏の目に止まって、よい回答が得られるチャンスが増えます。

自動でといわれていますが、まずは手動の方法。
オートフィルタを掛けて、男女それぞれを抽出したうえで
データのある行を全てコピぺするという手があります。
でもオートフィルタで男女に分けて表示できるのですから、
わざわざ別シートに貼り付ける必要はないのでは？

ああ、競技なので　男子の部、女子の部があるので、
RANKが一緒になると困るのですね。
となるとその後は年齢の別に分けたいという要求が有るのでは？
やりたいことは、今はまだやらなくても、全体の構想をお話しいただいたほうが、
手戻りにならなくて済みます。

私は個人でSEのまねごとを仕事としていますが、クライアントには常に
「今はやらなくても、構想は全て話してください」とお願いしています。
そうでないと、「あ、それもやりたかったのなら、ここはこうしておいたのに」
と言うことになってしまいます。

お礼

こういったサイトを使うのは初めてだったんですが、アドバイスありがとうございました。
実は、私、教員をしておりまして、生徒の成績処理のためにこのようなことを始めました。
まずは、新規で質問をしてみます。
たくさんの回答、本当にありがとうございました。

回答No.5

noname#79209

#1です。ゴメンナサイ、#4の最後に記載ミスがありました。
=======ここから======
チェックの表に10や40を設定してしまうと、
Sheet2
　　　A　　　B
１　 0　　 5
２　 10　 　5
３　 10.1 　4
４　 40　 　4
５　 40.1 　3
６　 90　 　3　←「2」になっていました。
７　 90.1 　2
８　 100　 　2　←　追加
９　 100.1　 1　←　行番号変更
と無駄な行を設定しなくてはならなくなります。
=======ここまで=======

解らないことがあれば、補足で質問してください。

回答No.3

noname#79209

#2です。ゴメンナサイ。私の方式はうまくないです。
また、既に順位がでているのでRANK関数は不要でしたね。

ただ、質問者さんが言われている評価基準が解らなくなりました。
参加者が充分多ければいいのですが、
10人未満だと、評価の基となる正しいパーセンテージ得られません。

さらに、ロードレースなので開催毎にエントリー人数が異なるでしょうから、
分母をキメ打ち（55-1などと）するのはマズイですね。
また、棄権、失格、不参加（参加登録をはしたが来なかった...棄権扱い？）をどこにどう入力するのか。
「0:00:00」と入力するのか、タイムを入力しない（ブランクのまま）のか、その旨（棄権、失格などと）を入力するのかで、
後の計算式が微妙に異なって来ます。

回答No.2

mshr1962

全体人数がわかればそれで比率が出せるのでは？
たとえば50人なら、
01～05 評価5
06～20 評価4
21～45 評価3
46～50 評価2
ですよね。
=LOOKUP(順位/COUNTA(順位の範囲),{0,0.11,0.41,0.91},{5,4,3,2})
でできると思います。

お礼

回答、どうもありがとうございました。
さっそく試してみます。
今、エクセルの関数について勉強しているところです。
また何かの時にお世話になるかもしれませんが、よろしくお願いします。
ありがとうございました。

回答No.1

noname#79209

A2～A55に名前、B2～B55に成績があったとして、
=RANK(B2,$B$2:$$B55,1)
で順位が得られます。
=INT(RANK(B2,$B$2:$$B55,1)*100/(55-1))
で整数化したパーセントが得られます。
別シートか別エリアに（ここではSheet2とします）
Sheet2
　　　A　　　B
１　 0　　 5
２　 10.1　 4
３　 30.1 　 3
４　 50.1 　 2
５　 90.1 　 1
で
=VLOOKUP(INT(RANK(B2,$B$2:$$B55,1)*100/(55-1)),Sheet2!$A$1:$B$5,2,TRUE)
とすれば可能かと．．．

お礼

回答、どうもありがとうございました。
さっそく試してみます。
今、エクセルの関数について勉強しているところです。
また何かの時にお世話になるかもしれませんが、よろしくお願いします。
ありがとうございました。

補足

たびたびすみません・・・

　　A　 　　B
１　 0　　　 5
２　 10.1 　 4
３　 30.1 　 3
４　 50.1 　 2
５　 90.1 　 1
この表の数値になる意味と
=VLOOKUP(INT(RANK(B2,$B$2:$$B55,1)*100/(55-1)),Sheet2!$A$1:$B$5,2,TRUE)
この数式の意味を教えてもらえたら助かるんですが・・・
何故、（５５－１）になるのか？とか・・・