関数でこんなの出来ますか？

質問

ランクとは違うのですが・・
【例】
５人が競争しました。その結果(勝率)がＢ列に入っています。
Ｃ列にＢ列の勝率に合わせた点数を入力したいのです。
点数は10ポイント制になります。
Ａ列　　Ｂ列　　Ｃ列
青木　　8.22
佐藤　　7.00
清水　　7.11
鈴木　　7.67
中村　　6.28
こんな計算が出来る関数とかありますか？
知っていましたら教えて下さい。
よろしくお願いします！

回答No.4

ham_kamo · ベストアンサー

マクロで作ってみました。かなり複雑になってしまいました^^;
ややこしそうだと思いますが、コピーして貼り付けるだけなので、以下に書いている手順通りに操作してみてください。

Excelの画面でAlt+F11を押すと、マクロの入力画面が開きます。そこで左側のツリーの「VBAProject(ブック名)」というところを右クリックし、「挿入」＞「標準モジュール」を選択すると、「Module1」というのができます。その状態で、右の画面に以下のマクロをコピーして貼り付けてください。

Function GetPoint(AllRatio As Variant, Ratio As Double) As Integer
　Dim MaxRatio As Double, Rank As Integer, MaxPoint As Integer
　Dim PrevRank As Integer, PrevRankRatio As Double, PrevPoint As Integer
　MaxRatio = Application.WorksheetFunction.Max(AllRatio)
　MaxPoint = IIf(MaxRatio >= 9, 10, 9)
　Rank = Application.WorksheetFunction.Rank(Ratio, AllRatio)
　If Rank = 1 Then
　　GetPoint = MaxPoint
　ElseIf MaxRatio - Ratio <= 0.5 Then
　　GetPoint = MaxPoint - (Ratio - 1)
　Else
　　PrevRank = GetPrevRank(AllRatio, Rank)
　　PrevRankRatio = GetRatio(AllRatio, PrevRank)
　　PrevPoint = GetPoint(AllRatio, PrevRankRatio)
　　If PrevRankRatio - Ratio <= 0.5 Then
　　　GetPoint = PrevPoint - 1
　　Else
　　　GetPoint = PrevPoint - 2
　　End If
　End If
End Function

Function GetPrevRank(AllRatio As Variant, Rank As Integer) As Integer
　Dim PrevRank As Integer, TmpRank As Integer, i As Integer
　PrevRank = 1
　For i = 1 To AllRatio.Count
　　TmpRank = Application.WorksheetFunction.Rank(AllRatio(i), AllRatio)
　　If TmpRank < Rank And PrevRank < TmpRank Then
　　　PrevRank = TmpRank
　　End If
　Next i
　GetPrevRank = PrevRank
End Function

Function GetRatio(AllRatio As Variant, Rank As Integer) As Double
　Dim i As Integer
　For i = 1 To AllRatio.Count
　　If Application.WorksheetFunction.Rank(AllRatio(i), AllRatio) = Rank Then
　　　GetRatio = AllRatio(i)
　　　Exit Function
　　End If
　Next
End Function

貼り付けたらマクロの画面は閉じてかまいません。

エクセルのシートに戻って、
C1に
=GetPoint($B$1:$B$5,$B1)
と入力し、それを下までコピーすると、各自の点数が表示されます。
正常な値にならない、エラーが出る場合などは補足をお願いします。

お礼

ありがとうございました！無事出来ました。
本当に感謝です。ありがとうございました。

回答No.3

ham_kamo

条件が思ったよりも複雑でした^^;
関数を組み合わせてやろうといろいろやってみたのですが、ちょっと一筋縄ではいかないようです。

マクロを使ってユーザ定義関数を作ればなんとかできそうです。マクロを使ってもかまいませんか？

補足

はい！すみませんがよろしくお願い致します。
マクロは使った事がないので分からないのですが・・。

回答No.2

ham_kamo

No.1です。補足拝見しました。
それで、すみません、まだよくわからないので教えてください。

Ａ列　　Ｂ列　　Ｃ列
青木　　8.22　　　９
佐藤　　7.00　　　５
清水　　7.11　　　６
鈴木　　7.67　　　８
中村　　6.28　　　３

で、10点満点中、１位の8.22が９となるのは、どういう法則でしょうか。

> ５人のＣ列に入る点数は勝率の一番高い人が基準で以下の人は基準からの
> 差で点数が減っていく感じです。

とありますが、鈴木さんが８で、清水さんが６の理由がわかりません。
基準となる１位の青木さんとの勝率の差をこの表に加えると、

Ａ列　　Ｂ列　　Ｃ列　勝率の差
青木　　8.22　　　９　0
佐藤　　7.00　　　５　1.22
清水　　7.11　　　６　1.11
鈴木　　7.67　　　８　0.55
中村　　6.28　　　３　1.94

となりますが、鈴木さんは差が1.0以内なので８、清水さんは差が1.0を超えたから７でなく６、とするのでしょうか。そうすると、中村さんが勝率の差が1.94なのに３というのが、よくわからなくなります。

まとめると、
・１位の人のポイントを10点満点中の何点にするか、という法則
・２位以下の人のポイントを求める法則。１位の人との勝率差をどう計算したらポイントになるのか
がわからないと、どういう関数を使ってどういう数式にすればいいか、求めることができません。
以上の点の補足をお願いできますか？

補足

お返事おそくなりすみませんでした。
Ｃ列のポイントゎ大体で付けてしまいました・・。
法則的には・・
　・１位の人のポイントは勝率９割以上の場合は10(それ以下は9)
　・２位以下の人は１位との差が0.5以内なら次点
　　0.５より上の差がある場合は２ポイント以上の差をつけます。
　・以降順位に従って前の順位の人との差が0.5以内なら次点
　　0.5より上なら２ポイント差をつけます。
　・まったく同じ勝率でない限り同じポイントはつきません

Ａ列　　Ｂ列　　Ｃ列　勝率の差
青木　　8.22　　　９　0
佐藤　　7.00　　　４　1.22
清水　　7.11　　　５　1.11
鈴木　　7.67　　　７　0.55
中村　　6.28　　　２　1.94

何度も本当にすみません。
分かる様でしたらよろしくお願い致します。

回答No.1

ham_kamo

> Ｃ列にＢ列の勝率に合わせた点数を入力したいのです。
> 点数は10ポイント制になります。

というのは、勝率が１位の人が５０点、２位の人が４０点…、ということでしょうか。それなら、C1に

=RANK(B1,B$1:B$5,1)*10

と入力して、下にコピーすればできます。

質問の意図を読み違えていたら、補足をお願いします。（具体的に、上の例ではＣ列にどういう数字が入るか書いていただけると回答しやすいかと思います）

補足

回答ありがとうございます。
点数とは違くて・・
Ａ列　　Ｂ列　　Ｃ列
青木　　8.22　　　９
佐藤　　7.00　　　５
清水　　7.11　　　６
鈴木　　7.67　　　８
中村　　6.28　　　３
みたいな感じで10点制？で付けたいのです。
５人のＣ列に入る点数は勝率の一番高い人が基準で以下の人は基準からの
差で点数が減っていく感じです。

説明下手ですみません。