数値が入っているセルをカウントして表示できる？

質問

評価　　　前一走　二走　　三走　　四走☆同　　　ハロン　ポイント
Ｄ//　　　　34.5//33.7// // ☆2 ・1 ・3
Ｃ//　　　　34//33.4// // 　☆2 ・2 ・4
Ｂ//　　　　34// 34// //34.8 ☆3 ・2 ・5
Ａ//　　　　34.5//33.5//34.8//34.2 ☆4 ・2 ・6

競馬の能力評価にエクセルを使っていますが、データが多いので、自動化する方法がありましたら教えてください。今は手で入力しています。

1.２行目から馬４頭ですが、前一走から四走まで、数値が入っているだけで、カウントして、同の列のセルに自動的に表示させたいです。

2.ここでは４頭ですが、全馬の一番速い（数値の小さい）のから１秒以内
　に条件付き書式で色を付けています。各馬で色の付いたのをハロンの列
のセルに表示させたいです。最速の秒数から１秒以内で何回走っているか
を表示させたいので、色で無理なら他の方法でもＯＫです。

ちなみに、ここで、一番速いのは33.4なので、34.4までに色が付いています。

3.ポイントの列には同とハロンを足したものが表示されます。これは、相対
指定でできています。このポイントの列で一番数字が大きいものをＡとして
評価の列に表示させたいのです。ここでは、6からＡＢＣとなっていますが、
他のレースで8が最高で７がなかったら、8はＡ、6はＣとなります。

以上ですが、情報不足の点がありましたら、指摘してください。ちなみに
ＥＸＣＥＬ97です。

また、スペースが上手く表示できないので、//,☆,・,をセルの区切りに使っています。

以上、宜しくお願いします。

回答No.4

noname#79209 · ベストアンサー

#1です。
行の削除によって数式の参照範囲が少なくなる件ですが、
OFFSET関数を使えば回避できます。
10000行分確保したい場合は、
=SUMPRODUCT(((ABS(MIN($B$2:$E$5)-$B2:$E2))<=1)*1)
や
「数式が」、「=ISNUMBER(B2)*((B2-MIN($B$2:$E$10000))<=1)=1」
は、B2からE10001の10000×4のセルを確保するので
=CHAR(MAX($H$2:$H$10000)-$H2+65)=SUMPRODUCT(((ABS(MIN(OFFSET($B$2,0,0,10000,4))-$B2:$E2))<=1)*1)
「数式が」、「=ISNUMBER(B2)*((B2-MIN(OFFSET($B$2,0,0,10000,4)))<=1)=1」

=CHAR(MAX($H$2:$H$10001)-$H2+65)
H2からH10001の10000×1のセルを確保するので
=CHAR(MAX(OFFSET($H$2,0,0,10000,1))-$H2+65)
とします。

また、Ａ列は
=IF(COUNT($B2:$E2)>0,CHAR(MAX(OFFSET($H$2,0,0,10000,1))-$H2+65,
'')
としたほうがよいでしょう。

あと、おかしくなったり、エラーが出るのは式をよーく見てください。
手入力ですか、コピペですか？
違う場所に式を入力してませんか？

まとめです。
　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　Ｆ　　Ｇ　　　Ｈ　　
１　評価　一走　二走　三走　四走　☆同　ハロン　ポイント
２　Ｄ　　34.5　　3.7　　　　　　　　　　　2　　　　1　　　　3
３　Ｃ　　34　　　33.4　　　　　　　　　　 2　　　 2　　　　 4　　
４　Ｂ　　34　　　34　　　34.8　　　　　　3 　　　2　　　　 5
５　Ａ　　34.5　　33.5　　34.8　　34.2　4 　　　2　 　　　6
という状態で、
A2に
=CHAR(MAX(OFFSET($H$2,0,0,10000,1))-$H2+65)
F2に
=COUNT(B2:E2)
G2に
=SUMPRODUCT(((ABS(MIN($B$2:$E$5)-$B2:$E2))<=1)*1)
H2に
=$F2+$G2

B2からE2を選択して、条件付書式に
「数式が」を選択
=ISNUMBER(C2)*((C2-MIN(OFFSET($B$2,0,0,10000,4)))<=1)=1

として全て下へコピー

ただし途中の行削除した場合、数式は影響ありませんが、
条件付書式は、削除した行数分だけ「指定なし」の行が繰り上がるので
設定し直しになります。
たとえば、途中の2行を削除したら、10000行目と10001行目の条件付書式
は再設定しなければなりません。

お礼

丁寧な回答をありがとうございます。できました。アスキーコードとは知りませんでした。200ポイント以上あげたいくらいです。

回答No.3

noname#79209

> 今後、列が追加されることもあるので、すべて絶対参照指定でＯＫでしょうか？
「列が追加され」は「行」の書き間違いですよね？
「列（横方向）」は絶対参照。
「行（縦方向）」は相対参照にすべきです。
例：＄A1．．．列は絶対、行は相対
コピーは縦に行われるのですから、行は相対参照にしなければなりません。
また、MAXやMIN関数の中は行列共に絶対参照です。
この範囲は充分大きくとっておくほうが良いです。
どのくらいの行が必要になるかは、質問者さん以外には判らないので、
たとえば
=SUMPRODUCT(((ABS(MIN($B$2:$E$10000)-$B2:$E2))<=1)*1)
や
「数式が」、「=ISNUMBER(B2)*((B2-MIN($B$2:$E$10000))<=1)=1」
や
=CHAR(MAX($H$2:$H$10000)-$H2+65)
と充分に大きな行番号を指定しておくべきです。

また、行を削除すると、数式内の参照範囲が自動的に減りますので、ご注意を。
例：
=CHAR(MAX($H$2:$H$10000)-$H2+65)
の状態で、100行目～101行目ｍの二行分を削除すると
=CHAR(MAX($H$2:$H$9998)-$H2+65)
と自動的にに数式が変化してしまいます。

回答No.2

noname#79209

> 最後の「+65」は「+＄5」ですよね
違います。数値の「65」（六十五）です。

MAX($H$2:$H$5)
でポイントの最大値を拾います

MAX($H$2:$H$5)-$H2
で、その馬のポイントとポイントの最大値の差を得ます。
仮に最大値が8、その馬のポイントが2なら6が算出されます。
ポイント最大値の評価を「A」とするので、この例での2の評価は「G」です。
「A」という文字のコード（アスキーコードと言います）は65です。
65→「A」、66→「B」、67→「C」、68→「D」、69→「E」、70
→「F」、71→「G」、72→「H」

もし最大値の馬だったら、最大値との差は0なので65を足せば、文字「A」のコードになります。
上記のポイント2の馬の評価は差が6なので　6+65=71で71というコードは「G」です。
関数CHARはこのコードを文字に変換する関数です。
従ってCHAR(71)は「G」に変換されます。

回答No.1

noname#79209

1.
=COUNT(B2:E2)

2.
=SUMPRODUCT(((ABS(MIN($B$2:$E$5)-$B2:$E2))<=1)*1)
条件付書式：秒数を入力するセルを全て選択して
「数式が」、「=ISNUMBER(B2)*((B2-MIN($B$2:$E$5))<=1)=1」

3.
ポイントの列
=$F2+$G2 として下にコピー
評価の列
=CHAR(MAX($H$2:$H$5)-$H2+65)　として下にコピー

で如何でしょう

お礼

失礼しました。相対参照でした。

補足

ありがとうございます。1と２の条件付書式、3のポイントの列はできました。

2のSUMPRODUCT は「・」が表示されます。評価の列も同じでコピーすると「＃VALUE!」と表示されます。評価の列の最後の「+65」は「+＄5」ですよね。

今後、列が追加されることもあるので、すべて相対指定でＯＫでしょうか？