[[20220802165733]] 『複数の判定分けを効率良く作成したいです。』(c3po) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『複数の判定分けを効率良く作成したいです。』(c3po)

水泳のタイムで15段階にランク・判定分けをしたいと思います。

 =IF(H3<='資格級男子+01'!C4,"15",
IF(H3<='資格級男子+01'!C5,"14",
IF(H3<='資格級男子+01'!C6,"13",
IF(H3<='資格級男子+01'!C7,"12",
IF(H3<='資格級男子+01'!C8,"11",
IF(H3<='資格級男子+01'!C9,"10",
IF(H3<='資格級男子+01'!C10,"9",
IF(H3<='資格級男子+01'!C11,"8",
IF(H3<='資格級男子+01'!C12,"7",
IF(H3<='資格級男子+01'!C13,"6",
IF(H3<='資格級男子+01'!C14,"5",
IF(H3<='資格級男子+01'!C15,"4",
IF(H3<='資格級男子+01'!C16,"3",
IF(H3<='資格級男子+01'!C17,"2",
IF(H3<='資格級男子+01'!C18,"1","―")))))))))))))))

 IFで一つ作ったのはいいのですが(15がタイムが早く、下がるにつれタイムが遅くなります。
関数は間違ってるかもしれない)、これが50m・100m・200m等の距離別。
さらに19歳以上〜8歳までの年齢別でトータル180種のクラス分けです。
効率良く作成する方法はありますでしょうか?

 また、その判定結果をBestタイムに対して反映したいのですが年齢がバラバラです。
来年になれば判定分けの年齢別も変わってしまいます。

 まずIFの関数を別に作って、それをさらに年齢別に振り分けるようにすればいいのかと思いますが、
やり方が分かりません。

 50m自由形
 氏名   年齢   タイム   クラス
 炭治朗  19   00:25.37        ?
 禰豆子  15   00:29.08        ?
 善逸   13   00:29.14        ?
 伊之助  10   00:35.59        ?

 資格級 男⼦19歳以上				
 自由形50〜200m				
 クラス		50m	    100m	    200m
   15	   00:20.57	 00:45.54	01:40.58
   14	   00:21.01	 00:46.42	01:42.28
   13	   00:21.45	 00:47.29	01:43.98
   12	   00:21.89	 00:48.17	01:45.69
   11	   00:22.33	 00:49.05	01:47.39
   10	   00:23.24	 00:51.02	01:51.55
    9	   00:24.14	 00:52.98	01:55.71
    8	   00:25.05	 00:54.95	01:59.87
    7	   00:25.96	 00:56.91	02:04.03
    6	   00:26.86	 00:58.88	02:08.19
    5	   00:27.77	 01:00.84	02:12.36
    4	   00:28.68	 01:02.80	02:16.52
    3	   00:29.59	 01:04.77	02:20.68
    2	   00:30.49	 01:06.74	02:24.84
    1	   00:31.40	 01:08.70	02:29.00

 この様な表となっております。ご教授お願い致します。

< 使用 Excel:Office365、使用 OS:Windows10 >


 =COUNTIF('資格級男子+01'!C$4:$C$18,">="&$H3)

年齢別の表の方は例がなくてよくわかりませんが、
表の中でタイムより大きい数値がいくつあるかを数えれば、いちおう希望の結果になるはずです。
("−"は数値の表示形式で0を−と表示するようにする)
(下手の横好き) 2022/08/02(火) 19:06


     |[A]      |[B]     |[C]           |[D]    
 [1] |クラス   |50m     |100m          |200m   
 [2] |       15|00:20.6 |00:45.5       |01:40.6
 [3] |       14|00:21.0 |00:46.4       |01:42.3
 [4] |       13|00:21.4 |00:47.3       |01:44.0
 [5] |       12|00:21.9 |00:48.2       |01:45.7
 [6] |       11|00:22.3 |00:49.1       |01:47.4
 [7] |       10|00:23.2 |00:51.0       |01:51.5
 [8] |        9|00:24.1 |00:53.0       |01:55.7
 [9] |        8|00:25.1 |00:55.0       |01:59.9
 [10]|        7|00:26.0 |00:56.9       |02:04.0
 [11]|        6|00:26.9 |00:58.9       |02:08.2
 [12]|        5|00:27.8 |01:00.8       |02:12.4
 [13]|        4|00:28.7 |01:02.8       |02:16.5
 [14]|        3|00:29.6 |01:04.8       |02:20.7
 [15]|        2|00:30.5 |01:06.7       |02:24.8
 [16]|        1|00:31.4 |01:08.7       |02:29.0
 [17]|         |        |              |       
 [18]|50m自由形|        |              |       
 [19]|氏名   |年齢  | タイム   |クラス 
 [20]|炭治朗  |      19|00:25.4       |      8
 [21]|禰豆子  |      15|00:29.1       |      4 
 [22]|善逸   |      13|00:29.1       |      4
 [23]|伊之助  |      10|00:35.6       |      1

 D20=INDEX($A$2:$B$16,MATCH($C20,$B$2:$B$16,1),1)
 年齢は無視してください。そのデータがないため。
 とりあえずタイムによってクラスが分かればいいかなと。
 >この様な表となっております。
 他年齢別も同じような表になっているのですか。
(nm) 2022/08/02(火) 19:15

ありがとうございます。
 参考画像となります。
 https://imgur.com/a/jtnAQiv

 この様な資格級の表があります。
資格級の表の下にさらに平泳ぎ・背泳ぎ・バタフライ・個人メドレーの項目があります。
男子だけでトータル180の関数。女子もあるので倍の360の関数を作らなくてはならないです。

 19歳以上男子50m自由形を一つ作って残り359種を作らなくてはいけないかと思うと
心が折れてしまいました。それを年齢ごとに振り分けれるようにしないといけないと考えたら
完全に頭はショートです。

(c3po) 2022/08/03(水) 08:04


 おはようございます。	
 まずは、情報の提供が不足しています。	

 情報を全て開示されたとしても、できるかどうかも	
 まだ誰もわかりません。	

 でも、されれば...していただける方がもしかしたら	
 いらっしゃるかもです。	

 ◇質問.1 ( BOOKの構成について )	

 参考画像を閲覧すると、シートが5枚あるのですが	
 その説明からしてください。	

 ・短Best表	
 ・長Best表	
 ・資格級男子+01	
 ・資格級女子+01	
 ・名簿	

 長Best表( 長水路 )、短Best表( 短水路 )において	
 これが、選手個々の記録をされているシートでしょうか?

 判定結果をBestタイムに対して反映するのがこの表でしょうか?	

    |[A]      |[B] |[C]     |[D]   	
 [1]|50m自由形    	
 [2]|氏名     |年齢|タイム  |クラス	
 [3]|炭治朗   |  19|00:25.37| ?   	
 [4]|禰豆子   |  15|00:29.08| ?    	
 [5]|善逸     |  13|00:29.14| ?    	
 [6]|伊之助   |  10|00:35.59| ?    	

 ※もし違うのなら、上記の表は、	
 別にシートがあるのでしょうか?	

 ◇質問.2 ( 資格級男子+01、資格級女子+01 について )	

 日本水泳連盟が制定している、全国統一の泳力評価基準	
 の年度毎のデータですか?	

https://swim.or.jp/swim/#qualification

 ◇質問.3 ( 名簿について )	

 登録名簿ですが、男女が ( 年齢共に )バラバラな状態で	
 リスト化されているものですか?	

 また、人数はどれくらいいますか?	
 公開し難いなら、約で大丈夫です。	

 ◇質問.4 ( 関数を希望されているのでしょうか? )	

 ピボット / VBAでも可ですか?	

(あみな) 2022/08/03(水) 10:51


・男/女
・年齢階層
・泳法
・距離
・級位
・タイム

資格級の表をこの6列でまとめられれば同じ関数でできそうですが
そっちの作業が大変、かな?
(下手の横好き) 2022/08/03(水) 11:26


 >参考画像となります。
 拝見しました。
 >効率良く作成する方法はありますでしょうか?
 自由形50〜200m、自由形400〜1500mと別れているのでまとめる。
 >それを年齢ごとに振り分けれるようにしないといけないと
 セル範囲、表に名前を付けて切り替える。
 私だったらこのようにします。
(nm) 2022/08/03(水) 12:01


表があるならMatch関数で検索をするのがコツですね。

照合の種類が 0:完全一致、-1:以上、1:以下

とありますので、ちょっと使いかたを勉強してみてはいかがでしょうか?

あと各表を参照するのは、各表に名前の定義をして、名前で
参照する方法がありますのでそちらを勉強してみてはいかがでしょうか?

https://sakusaku-office.com/excel/post-9009/
https://office-hack.com/excel/indirect/
(まっつわん) 2022/08/03(水) 15:42


 1.マクロでやっちゃった方が簡単だとは思いますが、
  他人が作ったマクロはメンテナンスが出来ない難点があります。

  お仲間に聞かれても「何故か出来ちゃうんです。合っているかどうか私自身は分かりませーん」
  なんて展開になりそう。

  ・・とはいっても、頭がショートしているなら、それで救われるかも。

  それとも、初心に帰って手作業(数式)でやりますか(それも出来ないことはないです)

 2.あと、ランク分け表に性別の欄が無いですが、どうやって判別するんですか?

 3.資格級の表で、背泳ぎとかは、自由形の下方にあるんですね?(それとも右方向?)

(半平太) 2022/08/03(水) 16:51


https://nagasaki.swim.or.jp/wp-content/uploads/2022/03/2022_%E8%B3%87%E6%A0%BC%E7%B4%9A.pdf
これでしょうか
PDFをダウンロードして、ReaderじゃないAcrobatでエクセル形式で書き出してごにょごにょすると...

(とおりすがり) 2022/08/03(水) 16:58


あみな様、ご指摘ありがとうございます。また、情報不足失礼しました。

 質問1と2に関してはご推察通りです。
短水路(25m)と長水路(50m)のプールでは、同じ距離でもターンの回数が多い短水路の方が
Bestタイムが早い結果となります。ですので、短水路と長水路の記録は一緒にする事ができません。
しかし、資格級のクラス分け(ランク分け)の表は男子と女子で分けますが、短・長で同じものを使います。名簿にはメインとして生年月日をから年齢を算出し、資格級の表と検索する為です。
あと、性別と学校名を入れてテーブルしているだけのものです。

日本水泳連盟が制定している、全国統一の泳力評価基準で間違いありません。毎年微妙にタイムが
変わっていて、前年度のを使っているとクラスが変わってしまう事もあります。なので毎年更新が必要となります。

短Best表のシートに50m自由形の○○秒に対して18歳ならクラスが〇。13歳なら△。の様に表示しよう
かと考えてます。

 質問3.チームは全員で22名ですが、増えたり辞めたりで、表の様に男子は青。女子は赤。
で年齢は上から高い順にしています。

 質問4.ピボット / VBAのこだわりはありません。来年度以降も表を置き換えれば算出できる様な
効率的出来れば文句はございません。

(c3po) 2022/08/03(水) 17:55


とおりすがり様ありがとうございます。

 この表です。しかも年齢が横にきている。
私の知っている表とは違いますが、こっちの方がやりやすそうです。
結局、同じですか?
(c3po) 2022/08/03(水) 18:08

まっつわん様ありがとうございます。

 Match関数は使った事が無く盲点でした。
少し勉強させていただきます。
(c3po) 2022/08/03(水) 18:11

 参考画像と同じ、日本水泳連盟が制定している、全国統一の泳力評価基準データを						
 落としたマクロとなります。(必要性があるかどうかはわかりませんが)						

 資格級男子+01 のシート分だけですが						
 女子も必要であれば、マクロの一部を...数ヵ所変更すれば						
 Web掲載のデータを落とすことが可能となります。						

 手順1. EvaluationCriteria_Update を実行						
 手順2. auxiliary_heading を実行						

 Sub EvaluationCriteria_Update()						
    Dim i As Long, parse_str As String						
    Dim ws As Worksheet						
    Set ws = Worksheets("資格級男子+01")						
    Dim nm As Name						
    On Error Resume Next						
    For Each nm In ActiveWorkbook.Names						
        nm.Delete						
    Next						
    On Error GoTo 0						

    For i = 1 To 10						
        Select Case i						
        Case 1						
            parse_str = "https://swim.or.jp/swim/qualification/mens_over19/"						
            Call EvaluationCriteria_Inport(parse_str, 1)						
            ws.Cells(1, "A").Value = "資格級 男子19歳以上"						
        Case 2						
            parse_str = "https://swim.or.jp/swim/qualification/mens_17-18/"						
            Call EvaluationCriteria_Inport(parse_str, 7)						
            ws.Cells(1, "G").Value = "資格級 男子17〜18歳"						
        Case 3						
            parse_str = "https://swim.or.jp/swim/qualification/mens_15-16/"						
            Call EvaluationCriteria_Inport(parse_str, 13)						
            ws.Cells(1, "M").Value = "資格級 男子15〜16歳"						
        Case 4						
            parse_str = "https://swim.or.jp/swim/qualification/mens_14/"						
            Call EvaluationCriteria_Inport(parse_str, 19)						
            ws.Cells(1, "S").Value = "資格級 男子14歳"						
        Case 5						
            parse_str = "https://swim.or.jp/swim/qualification/mens_13/"						
            Call EvaluationCriteria_Inport(parse_str, 25)						
            ws.Cells(1, "Y").Value = "資格級 男子13歳"						
        Case 6						
            parse_str = "https://swim.or.jp/swim/qualification/mens_12/"						
            Call EvaluationCriteria_Inport(parse_str, 31)						
            ws.Cells(1, "AE").Value = "資格級 男子12歳"						
        Case 7						
            parse_str = "https://swim.or.jp/swim/qualification/mens_11/"						
            Call EvaluationCriteria_Inport(parse_str, 37)						
            ws.Cells(1, "AK").Value = "資格級 男子11歳"						
        Case 8						
            parse_str = "https://swim.or.jp/swim/qualification/mens_10/"						
            Call EvaluationCriteria_Inport(parse_str, 43)						
            ws.Cells(1, "AQ").Value = "資格級 男子10歳"						
        Case 9						
            parse_str = "https://swim.or.jp/swim/qualification/mens_09/"						
            Call EvaluationCriteria_Inport(parse_str, 49)						
            ws.Cells(1, "AW").Value = "資格級 男子9歳"						
        Case 10						
            parse_str = "https://swim.or.jp/swim/qualification/mens_under08/"						
            Call EvaluationCriteria_Inport(parse_str, 55)						
            ws.Cells(1, "BC").Value = "資格級 男子8歳以下"						
        End Select						
    Next						

 End Sub						

 Sub EvaluationCriteria_Inport(ByVal parse_str As String, ByVal Target As Long)						

    Dim ws As Worksheet						
    Dim qt As QueryTable						
    Dim url As String						
    Set ws = Worksheets("資格級男子+01")						
    Const Row_Start = 3&, Row_last = 103&						
    Const Col_Start = 1&, Col_last = 59&						

    On Error GoTo Err						
    url = parse_str						
    ws.Range(ws.Cells(Row_Start, Col_Start), ws.Cells(Row_last, Col_last)).Clear						
    Set qt = ws.QueryTables.Add(Connection:="URL;" & url, _						
    Destination:=ws.Cells(Row_Start, Target))						
    With qt						
        .RefreshOnFileOpen = True						
        .Name = "泳力評価基準"						
        .WebFormatting = xlWebFormattingNone						
        .WebSelectionType = xlAllTables						
        .Refresh						
    End With						
    Exit Sub						
 Err:						
    MsgBox "!"						
    Err.Clear						
 End Sub						

 Sub auxiliary_heading()						

    Dim q As Long, k As Long, i As Long, x As Long, n As Long						
    Dim ws As Worksheet						
    Set ws = Worksheets("資格級男子+01")						

    For q = 1 To 59 Step 6						
        With ws						
            .Cells(2, q).Value = "自由形50〜200m"						
            .Cells(19, q).Value = "自由形400〜1500m"						
            .Cells(36, q).Value = "背泳ぎ"						
            .Cells(53, q).Value = "平泳ぎ"						
            .Cells(70, q).Value = "バタフライ"						
            .Cells(87, q).Value = "個人メドレー"						
        End With						
    Next q						

    For i = 0 To 54 Step 6						
        For k = 3 To 88 Step 17						
            ws.Range(ws.Cells(k, 1), ws.Cells(k, 5)).Offset(0, i).Interior.Color _						
            = RGB(202, 231, 238)						
        Next k						
    Next i						

    For x = 0 To 85 Step 17						
        For n = 1 To 60 Step 6						
            ws.Range(ws.Cells(4, n), ws.Cells(18, n + 1)).Offset(x, 0).Interior.Color _						
            = RGB(235, 246, 249)						
        Next n						
    Next x						

 End Sub						

 ※私がお手伝い出来るのは、ここまでです。						
 明日から、少々忙しいのでご了承を…						

(あみな) 2022/08/04(木) 12:08


 折れた心は回復しましたか?
 多分、無理でしょうね。私でさえこれは大変だなぁ・・と思っています。
 Match関数の知識一つで何とかなる話ではないです。

 本気で解決策を考えましょう。

 方針としては、
 1.現在の資格級の表に「性別・年齢層別・泳法別・距離別」の基準で
   それぞれのエリアに個別の名前定義を行う。
   この名前定義の設定は、マクロで実施する。(一瞬で終わります)

 2.ランク判定表のクラス欄(列)に数式を埋めて判定結果を算出する。
   数式は、上記1の名前定義を活用する。
   数式は、全員共通(一種類)なので、当該数式を全員のクラス欄にコピーすればいいだけ。

 方針は以上ですが、不明点を先に解決しておく必要があります。

 1.ランク判定表は、日本水泳連盟が制定している全国統一の泳力評価基準で間違いありません、との事ですが、
   現実に少し違いますよね。

   連盟の「男子19歳以上自由形50m」の15では20.56秒ですが、
   c3poさんの方は、00:20:57で、100分の1秒差があります。

   これは何故ですか? c3poさんの情報が既に古くなっていると言う理解でいいですか?

     連盟のが正しい場合は、あみなさんが作ってくれたマクロがありますので、
   男女とも連盟のデータを自動的に取り込めるように改変することは可能です。

   ただ、連盟のデータは1分未満は、時刻データでなく、単なる小数点数なので(例:20.56)、
     利用上、少し工夫が必要になります。
   逆に言うと現在あるランク判定表は、どなたが時刻データに変換したのですか、ちょっと気になります。

 2.ランク判定表の泳法が表示されている行番号は、以下でいいですか?
   2行目(自由形50〜200m)、19、36、・・・、87行目(個人メドレー)
   つまり、ピッタリ17行ずつで、中間に隙間なし。

 3.長/短Best表に性別がありません。
   一種類の数式で処理するためには、性別が自動的に分かる方法が必要です。

   名簿シートを見ればわかる様なので、名簿シートの構成を具体的に説明いただくか、
   長/短Best表に性別欄を設けて貰って、そこをそちらで埋めていただくかです。(年齢と同じような手法でいいはずです)

   列を増やしたくなければ、「年齢欄に合体させて出す」でもいいです。
   例えば、「17」→「男子-17」と言った調子。

 4.長/短Best表に書かれている泳法は、2文字のアルファベットの様ですが、全て教えてください。
   Fr 自由形
   Ba 背泳ぎ
   Br 平泳ぎ(?)
   Bu  バタフライ(?)
   Me  個人メドレー(?)

(半平太) 2022/08/05(金) 20:51


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.