エクセル２００７を使用してタイムに応じたポイント表を作成しています。

質問

エクセル２００７を使用してタイムに応じたポイント表を作成しています。
１位のタイムを入力すると１００Pでその後は１位のタイムから１０秒以内の遅れが９５P、２０秒以内の遅れは９０P、３０秒遅れは８５Pといった表を作成したいのですがどうしたらいいかご教授お願いします。
また、タイムを入力するセルはmm:SSと入力を行いたいです。

回答No.3

tom04 · ベストアンサー

こんばんは！
一例です。
↓の画像で説明させていただきます。

B列のセルの表示形式はユーザー定義から
[m]:ss　として、仮に 「49:59」（49分59秒）の場合は　0:49:59　と入力すればちゃんと表示されるはずです。

そして、右側に別表を作成しておきます。
今回は差が「○秒以下」という条件なので降順に表を作成します。
（○以上～△未満の範囲であれば昇順で表を作成します）
実際必要なのは水色部分だけですが、判りやすくするためにE・F列も表示させています。

そして、C2セルの数式を
=IF(B2="","",IF(B2=MIN($B$2:$B$100),100,INDEX($H$2:$H$5,MATCH(SECOND(B2-MIN($B$2:$B$100)),$G$2:$G$5,-1))))
としてオートフィルで下へコピーすると画像のような感じになります。
尚、数式は100行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてください。
余計なお世話かもしれませんが、トップから差が30秒を超えた場合は
「0」になるような表にしてみました。

以上、長々と書きましたが、参考になれば幸いです。
他に良い方法があれば読み流してくださいね。m(__)m

お礼

回答いただきました皆様ありがとうございました。
全て試してみましたがtom０４さんの方法がイメージしたのと一番近かったのでベストアンサーとさせて頂きました。まだ、未完成で修正をしているところですがまた、何かありましたら色々とご教授ください。

回答No.4

KURUMITO

Ａ１セルに氏名、Ｂ１セルにタイム入力セル、Ｃ１セルにポイントの文字列が入っており、Ａ列とＢ列のデータは２行目から下方に並んでいるとします。
Ｂ列では0:49:50のように入力して表示形式でmm:ssのようにしているものとします。
そこでＣ２セルには次の式を入力して下方にオートフィルドラッグします。
=IF(B2="","",IF(B2=MIN(B:B),"100P",(100-ROUNDUP((B2-MIN(B:B)-"0:00:00.01")/"0:00:10"*1,0)*5)&"P"))
この式では計算誤差を防ぐために0.01秒を考慮しています。なお、１０秒以内とはもちろん１０秒差についても９５Ｐになるように考慮しています。

回答No.2

Cupper

時間を入力する範囲に対して MIN関数を使えば、一番小さい数字（時間）を検索できます
　=MIN(A1:A100)
など

比較したい値に対して計算したい値がどれだけ離れているかを算出すれば良いですよね
10秒以内で5ポイント引くのであれば、
　=IF(A1-MIN(A1:A100)<"00:00:05",95,IF(～))
といった IF関数を使った計算式を使っても良いですが
ここは数学的に処理することをお奨めしてみます

　=100-INT((A1-MIN(A1:A100)+"00:00:09")/"00:00:10")*5

これで良いと思います
　A1-MIN(A1:A1100)　で、A1セルの値から範囲内の一番小さい数字を引くことで差を求めます
この差を10秒で割ることで、10秒以内なら０、20秒以内なら１などと値付けされますので、
最も小さい入力単位が小数点を含まない秒である場合 9秒を加えても10秒以内なら１、20秒以内なら２…としています
この加える 9秒は、最も小さい数値の場合、割り算部分で０が返るようにする意味も含んでいます
2番目に小さい数字は一番小さい数字より1秒以上大きいということですからね
また、一番小さい数字より10秒【以内】であれば－５と言うことですので、これなら
　INT((10+9)/10)=1
と、【以内】に対して値付けすることができます
このように INT関数は、割ったときの余りを切り捨てています
その数値を5倍して100から引けばよいでしょう

数式をコピーして使うのでしたら、はじめに入力した MIN関数の括弧内を絶対参照にすると良いでしょう
　MIN($A$1:$A$100)

時間を入力するセルには
　[mm]:ss
と書式設定すると、60分以上の値を "1:00:00" などと時間単位が表示される場合に "60:00" と表示されます
（入力は 0:60:00 と時間単位から入力してくださいね）

なお、Excelではダブルクォーテーション（"）で囲われた時間表記は、シリアル値として扱われるので
そのまま計算に使用することができるので、数式を見やすくできます

前の人が答えられている MAX関数を使って、０以下にならないようにすることも検討しましょう
（自分もそのことに気が付いていませんでした）

回答No.1

zap35

Ｃ２の式は
　=MAX(100-ROUNDUP((B2-MIN(B:B))*3600*24,-1)/10*5,0)
になります。下方向にコピーしてください。

１０秒遅れる毎に-10点になるようですが、最低点は０点になるようにしています。

なお
＞タイムを入力するセルはmm:SSと入力を行いたいです。
とのことですが、これは貼り付けた画像のE列、F列を見てください
Ｆ２セルの式は
　=MAX(100-ROUNDUP((E2-MIN(E:E))*60*24,-1)/10*5,0)
になります。