種目別タイムでの判定をエクセル関数で表現するには？

質問

頭がこんがらがってきましたので助けてください。
(1)距離別で数種目があり、(2)各々の種目内でのタイムがデーター内のタイムを上回れば(3)各バッジ級が決まります。
ＶＬＯＯＫＵＰ関数でいけるかな？と思ったのですが、ん？検索条件が二つ必要ではないか？その際の関数は？と分からなくなってしまいました。
画像を添付します。

「種目」と「タイム」を手入力した時に、緑のセルに該当の「級」が表示される様に作ろうと思っていましたが行き詰まりました。

回答No.8

msMike · ベストアンサー

１．範囲 J2:K7 を選択⇒[名前ボックス]に「男子500」を上書き
￣￣入力して Enterキーをパシーッ
２．ステップ１を下記の要領で繰り返し
￣￣範囲 J8:K13　⇒ 「男子1000」
￣￣範囲 J14:K19 ⇒ 「男子1500」
￣￣範囲 J20:K22 ⇒ 「女子500」
３．式 =VLOOKUP(D2,INDIRECT(B2),2) を入力したセル E2 を
￣￣下方にズズーッとオートフィル

お礼

ありがとうございました。
助かりました。

回答No.7

tsubu-yuki

失礼、間違えてますね。

添付した図の場合、
名前をつける範囲はＫ：Ｌ列でした。

回答No.6

msMike

[No.2お礼]へのコメント、
》 データの部分をもう少し変更した方が良いでしょうか？
そんなことより、貴方の「超えて」「以下」を、私が書いたように、それぞれ「以上」「未満」に変更しませんか？
VLOOKUPやHLOOKUP関数の性格をご存じないのでせうか？
変更できないならサヨナラです。

お礼

コメントありがとうございます。
「以上」「未満」に変更してみます。言葉の意味合いでタイムに若干変更があります。

男子１０００の場合のタイムの解釈は下記の通りです。
A級⇨1:40.001未満（1:40.000から速いタイムで該当）
B級⇨1:40.001以上～1:50.001未満（1:50.000から速いタイムで該当）
C級⇨1:50.001以上～2:05.001未満（2:05.000から速いタイムで該当）
D級⇨2:05.001以上～2:20.001未満（2:20.000から速いタイムで該当）
E級⇨2:20.001以上～2:56.001未満（2:56.000から速いタイムで該当）

関数に関しては詳しくありません。色々見ながらやっている状況でした。

回答No.5

bunjii

>データー内のタイムは、０も表示しなければならないと思い文字列設定していました。
データー側の時間と判定側のタイムを数値化（時間のシリアル値）して質問の画像で提示の範囲で検証してみました。
IF関数とVLOOKUP関数の組み合わせでは入れ子の階層が深くなるのでINDEX関数を使う方法が分かり易いと思います。
この回答の添付画像のように行番号を含めた表で説明させていただきます。
E3セルの数式は下記のとおりです。
=INDEX(K$1:K$19,(MAX(INDEX((I$2:I$19=B3)*(J$2:J$19<=D3)*ROW(K$2:K$19),0))))
内側のINDEX関数では判定側の種目とデーター側の種目が一致し、然も、データー側の時間が判定側のタイム以下である組み合わせの行番号をMAX関数へ配列値で渡します。（複数条件を1つの数式内で処理するとき乗算はANDと等価になります）
外側のINDEX関数はデーター側の級別の配列値からMAX関数で抽出した行番号を使って判定の結果を取り出しています。
E3セルを下へコピーすれば目的の結果を得られるでしょう。

お礼

関数にあまり詳しくなく、数式を入れて頂きありがとうございます。

各種目でデーターのタイムから速いタイムが、各A~F級に該当します。

数式を参考にしてみます。

回答No.4

tsubu-yuki

「時間」の扱いについての指摘は既出ですので、そこは省略。
全角だと例えば「０：４８．５００」としてやらないと
エクセルは「何分何秒何」として見てくれません。
　※「．５００」まで画面上で見せておくには
　　数値の表示形式で「[m]:ss.000」と指定してやります。
ここは直しておきましょう。

で、本題。
お示しの図のような構成でＶＬＯＯＫＵＰ関数を有効利用しつつ
命題を解決するには「範囲の名前」「ＩＮＤＩＲＥＣＴ関数」を
理解してやると事が早く進みます。
　※それぞれ細かい解説はしませんので、ちょっと調べてみてください。
ＶＬＯＯＫＵＰ関数の第２引数「参照範囲」は、
セル番地を入力（Ｊ２：Ｌ７など）して指定してやる方法のほかに
「範囲の名前」を指定してやる方法があります。
ソレを有効活用してやろう、という寸法です。

まず、準備。
添付図の通り、「※」欄を用意します。
ＶＬＯＯＫＵＰ関数は「超えない近似値」を返してくるので、
「自行以上次行未満ならこの評価」の形が必要です。
　※例えば男子５００ｍで４８秒５ちょうどが「Ａ」評価なら
　　「※」欄は「０：４８．５０１」「０：５３：００１」などに
　　してやってください。
　　そのまま使うと４８秒５は「Ｂ」評価されます。
Ｅ評価も取れなかった時のために「評価なし」も入れておきます。

評価表の表に、種目別の範囲の「名前」を付けてやります。
男子５００ｍの範囲（図ではＪ２：Ｌ７）を選択しておいて、
「数式」タブから「名前の定義」をクリックします。
「名前」を「男子５００」としてやり、
参照範囲がずれていないことを確認し、ＯＫ。
コレを種目の数だけ繰り返してやりましょう。
名前が整いました。

定義した「名前」はＢ列に入力（プルダウンで選択）した「種目」と
ほぼ間違いなく一致するはずです。
なので、ＩＮＤＩＲＥＣＴ関数で「セルの値」を
ＶＬＯＯＫＵＰ関数の第２引数に「文字列」として渡してやります。
　　Ｅ２セル：=VLOOKUP(D2,INDIRECT(B2),2,TRUE)
としてやり、タイムを検索値として、Ｂ２セル（種目）と合致する範囲から
近似一致（超えない近似値）で範囲の２列目を返してね、としてやります。

長々ときましたが、そんな感じです。

お礼

回答ありがとうございます。

＞例えば男子５００ｍで４８秒５ちょうどが「Ａ」評価なら
　「※」欄は「０：４８．５０１」「０：５３：００１」などに
　してやってください。
　そのまま使うと４８秒５は「Ｂ」評価されます。
　Ｅ評価も取れなかった時のために「評価なし」も入れておきます。

　データ内にあります評価タイムから、速いタイムだと評価が該当します。
　関数に詳しくないので、できるだけ短い関数でできるかな？とも思っていました。

　教えて戴いた関数を参考にしてみます。

回答No.3

imogasi

最近同じようなパターンの質問があったので、その解決法の類推でできると思う。
要点は、「表引きをする表のセル範囲」に、別々の（エクセルの）「名前の定義」をして、実際に関数で
使うときには　INDIRECT（名前文字列）で、参照表（表全体の該当一部分）を指定する引数部分に指定するのです。
「別々の」のとは本質問の場合、男子５００、男子１０００、男子１５００、女子５００のようなカテゴリです。
そして
＝INDEX(範囲、MATCH(成績タイム、INDIRECT(表部分の定義した名前）））　　のような式のパターンで、「〇級」を導き出す。
ー
質問に対し注意事項
（１）そのほかに本質問ではL列に１列に並んでいるが
男子５００
・・
男子１０００
・・
男子１５００
・・
女子５００
・・のように
ーー
しかし
男子５００　男子１０００　男子１５００　女子５００　・・・
のようにカテゴリ別に、別列群にして、横に流す表でやってみます。
１カテゴリあて、タイム区画列＋〇級列を記述した２列です。
ただしエクセルの「名前定義」操作で、「最上端」を使うときはこのやり方が必須。
（第１行目に男子５００　男子１０００　男子１５００　女子５００にして
タイム区切りークラス（級）の対応２列の表を作る。４カテｘ２項目＝１０列作る。）
（注）今のままI列に複数カテを上下に連ねる状態でも、名前定義の際にセル範囲に、気を付けて指定すればできるとは思うが。
　　市販解説書の例などは、わかりやすくするため、「上端列」の名前定義を使う為もあって、このパターンを使っているものが多い。
（２）実際の表では「人間の能力の限界を常識的に理解している」ので、０に近い、表のアイテムはいらないと思うだろうかもしれないが、
VLOOKUP（TRUE型）やMATCH関数（第３引数１の型）では、０ー「（適当な）人類達成最小記録近くまで」のアイテムも作っておくべき、となると思う。
（３）本件は、データが（整数や「小数点以下ありかつ定桁」の小数ではないので）、日付シリアル値で表を作るべき（注）と思うので、うまく行くか充分テストをやることが必要
　　文字列などで、テーブルの時間部分（列）を作ると、MATCH関数やVLOOKUPのTRUE型が、思ったように結果を出せない恐れがあると思う。
（注）時刻シリアル値は１２：２３：００（半角数字と記号）のようにシートセルに入れないといけない。
ーー
以上で考え方の概略は尽きる。
あとは質問者が、表と関数式を具体化してくれることが望ましい。
文章が長くなるのでここでうちきる。
具体例での関数式などは時間があれば、後刻回答。

お礼

回答ありがとうございました。
簡単にできると思いましたが結構関数を使うようですね。

回答No.2

msMike

確認させてください。
》 男子1000 1.40.000 A級
》 男子1000 1.50.000 B級
》 男子1000 2.05.000 C級
》 男子1000 2.20.000 D級
》 男子1000 2.56.000 E級

データー表内の上記部分(男子1000 )の場合、下記のように理解してＯＫですか？
違うのなら、どのように解釈すべきか補足願います。

￣￣￣￣￣￣￣0:1:40.000未満⇒A級
0:1:40.000以上0:1:50.000未満⇒B級
～～～～～～～～～～～～～～～～
0:2.20.000以上0:2:56.000未満⇒E級
0:2:56.000以上は該当級なし

お礼

回答ありがとうございます。
男子１０００のタイムの解釈は下記の通りです。
A級⇨1:40.000以下（1:40.000から速いタイムで該当）
B級⇨1:40.000超えて～1:50.000以下（1:50.000から速いタイムで該当）
C級⇨1:50.000超えて～2:05.000以下（2:05.000から速いタイムで該当）
D級⇨2:05.000超えて～2:20.000以下（2:20.000から速いタイムで該当）
E級⇨2:20.000超えて～2:56.000以下（2:56.000から速いタイムで該当）
データの部分をもう少し変更した方が良いでしょうか？

回答No.1

bunjii

>ＶＬＯＯＫＵＰ関数でいけるかな？と思ったのですが、ん？検索条件が二つ必要ではないか？その際の関数は？と分からなくなってしまいました。
VLOOKUP関数で抽出できますが、男子と女子で評価が異なりますので、IF関数で条件分岐すれば良いと思います。
但し、検索範囲の値が数値では無いようなので、作り直しが必要になるでしょう。
男子500の1.06.000は文字列ですよね？
1分06秒.000はExcelのシリアル値と判断させるには1:06.000のように入力しなければなりません。（1:06.000→0.000763888888888889）

お礼

回答ありがとうございます。

データー内のタイムは、０も表示しなければならないと思い文字列設定していました。

そこから間違っていたようですね。

補足

表示し直してみました。

関数あまり詳しくないので難儀していました。