エクセル関数で対応できますでしょうか？

質問

コメント頂いた皆様、申し訳ありませんでした。

初回の質問は次の通りです。

hyhttps://okwave.jp/qa/q9530568.html

添付ファイルを二つアップできるかと思いましたが、それが出来ずに悩んでいました。

chayamati様のコメントを読み二つ目の添付ファイルをアップします。

質問に補足します。

常にベストタイムを表示させる表は個人としてではありません。

団体として、過去も含めて今後参加した各種大会の各種目で、現段階のベストタイムは（いつ参加した）（なんという大会）で（どこの所属）の（誰）が（何秒）だったかという項目を表示させたいです。

初回の質問に添付したファイルに入力していき、その中から各種目のベストタイムが常に、今回の質問に添付した表（空白の部分）に表示されれば一目瞭然かな？と思っていました。

エクセル関数で対応できますでしょうか？

回答No.6

HohoPapa · ベストアンサー

>Sheet1とSheet２に修正しています
正しくは、Sheet1とSheet2です。
つまり、数字も半角です。

>HohoPapa様から頂いた添付画像に合わせてから、
>標準モジュールにコピーペストをしSheet２に計算式を入れましたが結果は#VALUE!でした。
ん．．わかりませんね。

再度、添付画像の青囲部分、
セル位置、埋まっている列名が同じかどうかを再確認してみてください。

>種別・性別・距離をプルダウンで選択する様に設定しています
オートフィルターが設定されているという意味ですか？
それとも入力規則を設定しているということ？
どちらも影響を受けていないとは思いますが
切り分けのため、きっちり、サンプルに合わせてみてください。
これらが確認出来たら、
課題ブックを保存して閉じ、開きなおしてみてください。

それでも解決できないなら
再質問し、
Sheet1、Sheet2の画像を
私が提示したサンプル画像をまねて
ポストしてみてください。

お礼

ありがとうございます。
１つ１つ潰していってみます。

補足

数字を半角に直してみましたが原因がわからず。
画像を添付するため再度質問致します。

回答No.7

HohoPapa

>>種別・性別・距離をプルダウンで選択する様に設定しています
とは、列名の話ではなく、2行目以下の話ですね。
ならば、それはエラーの原因にはなりません。

>私のエクセル画面の左下に「準備完了」と表示があり、
このクダリも問題ありません。

お礼

ありがとうございます。
謎解きみたいになってきました。
再度チャレンジしてみます

回答No.5

HohoPapa

>>#VALUE!と表示されてしまいました
エラーになってしまいますか。 　　　( ^)o(^ )

まず、
hyhttps://okwave.jp/qa/q9530568.html
の画像では、
列名の行が1行目ではなさそうです。

他方、
>>Q:入力シートのデータがどの列、何行目から開始しているのか
>>が読み取れません。
>>・入力シートのシート名を明らかにしてください。
>A:見難く申し訳ありません。
>題名の『種別』はA1のセルに入力して始めます。
>データーはA2のセルからになります。

列名が1行目で、データが2行目から始まるんですヨネ？
シート名はSheet1ですね？

続いて、
>wkSQL = wkSQL & " ([クラス(2)] = '" & class & "')" & vbCrLf
>となっていますが
> ただしくは、(2)の部分は、〇に2の埋まった丸数字です。
>つまり、
> 質問者さんが使っているクラス名列の列名です。

この補足が期待通り伝わっているかどうか怪しいので、
手を加えた画像と手直ししたコードを再掲示しますので
再挑戦してみてください。

Option Explicit

'//--------------------一位レコードのフィールド値取得関数
Function GetTopRec(TBRange As Range, _
　GetFieldName As String, _
　Syubetsu As String, _
　Seibetsu As String, _
　Kyori As String, _
　class As String) As Variant

　Dim wkSQL As String
　Dim cn As Object
　Dim rs As Object
　Dim Kiroku1 As Date
　Dim Kiroku2 As Date

　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　cn.Open ThisWorkbook.FullName

　wkSQL = "SELECT Top 2 * " & vbCrLf
　wkSQL = wkSQL & "FROM [Sheet1$A1:L10000]" & vbCrLf
　wkSQL = wkSQL & "WHERE (" & vbCrLf
　wkSQL = wkSQL & " ([種別] = '" & Syubetsu & "') and" & vbCrLf
　wkSQL = wkSQL & " ([性別] = '" & Seibetsu & "') and" & vbCrLf
　wkSQL = wkSQL & " ([距離] = '" & Kyori & "') and" & vbCrLf
　wkSQL = wkSQL & " ([クラス] = '" & class & "')" & vbCrLf
　wkSQL = wkSQL & " ) " & vbCrLf
　wkSQL = wkSQL & "Order By [記録] " & vbCrLf
　rs.Open wkSQL, cn

　GetTopRec = ""
　If rs.EOF = True Then
　　If GetFieldName = "会場" Then
　　　GetTopRec = "記録なし"
　　End If
　Else
　　GetTopRec = rs(GetFieldName)　'1レコード目を取得
　　Kiroku1 = rs("記録")
　　rs.MoveNext
　　If rs.EOF = False Then　　　　'ヒットレコード複数
　　　Kiroku2 = rs("記録")
　　　If Kiroku1 = Kiroku2 Then　 '記録値が2レコードで同じ
　　　　If GetFieldName = "会場" Then
　　　　　GetTopRec = "記録複数"
　　　　ElseIf GetFieldName = "記録" Then
　　　　　GetFieldName = rs("記録")
　　　　Else
　　　　　GetTopRec = ""
　　　　End If
　　　End If
　　End If
　End If
　rs.Close
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Function

補足

回答が遅くなり、申し訳ありません。

列名が1行目になる様に、空欄の部分を削除しています。
データ入力は2行目から始まります。
シート名は当初、個人名にしていましたが、Sheet1とSheet２に修正しています。

以上、HohoPapa様から頂いた添付画像に合わせてから、標準モジュールにコピーペストをしSheet２に計算式を入れましたが結果は#VALUE!でした。

再度、トライしてみましたが#VALUE!が表示されてしまいました…

気になった事が２つ。

HohoPapa様の添付画像に表示されていませんが、私のエクセル画面の左下に「準備完了」と表示があり、その右側にアイコン（表現が正しいか分かりませんが）があります。
そこにカーソルを合わせますと「マクロの記録は現在実行されていません。クリックすると、新しいマクロの記録を開始します」

クリックするとモジュール内にmodule２が作成されてしまいます。

この部分は、気にしなくても良い部分でしょうか？

また、種別・性別・距離をプルダウンで選択する様に設定していますが、ここも関係のない部分でしょうか？

なんども申し訳ありません。

回答No.4

HohoPapa

追記です。

wkSQL = wkSQL & " ([クラス(2)] = '" & class & "')" & vbCrLf
となっていますが
ただしくは、(2)の部分は、〇に2の埋まった丸数字です。
つまり、
質問者さんが使っているクラス名列の列名です。

回答No.3

HohoPapa

>Q：VBAは扱えますか？
>A：初めて聞きました。ググってみましたがピンときません。
ちょっと「ガンバレ」が必要かもしれません。

■まずは、
https://excel-ubara.com/excelvba1/
の
>第1回．マクロって何？VBAって何？
から
>第4回.マクロはどこに書くの（VBEの起動）
までを行ってください。

■続いて

標準モジュールに

次行以下から　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Option Explicit

'//--------------------一位レコードのフィールド値取得関数
Function GetTopRec(TBRange As Range, _
　GetFieldName As String, _
　Syubetsu As String, _
　Seibetsu As String, _
　Kyori As String, _
　class As String) As Variant

　Dim wkSQL As String
　Dim cn As Object
　Dim rs As Object
　Dim Kiroku1 As Date
　Dim Kiroku2 As Date

　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　cn.Open ThisWorkbook.FullName

　wkSQL = "SELECT Top 2 * " & vbCrLf
　wkSQL = wkSQL & "FROM [Sheet1$A1:L10000]" & vbCrLf
　wkSQL = wkSQL & "WHERE (" & vbCrLf
　wkSQL = wkSQL & " ([種別] = '" & Syubetsu & "') and" & vbCrLf
　wkSQL = wkSQL & " ([性別] = '" & Seibetsu & "') and" & vbCrLf
　wkSQL = wkSQL & " ([距離] = '" & Kyori & "') and" & vbCrLf
　wkSQL = wkSQL & " ([クラス(2)] = '" & class & "')" & vbCrLf
　wkSQL = wkSQL & " ) " & vbCrLf
　wkSQL = wkSQL & "Order By [記録] " & vbCrLf
　rs.Open wkSQL, cn

　GetTopRec = ""
　If rs.EOF = True Then
　　If GetFieldName = "会場" Then
　　　GetTopRec = "記録なし"
　　End If
　Else
　　GetTopRec = rs(GetFieldName)　'1レコード目を取得
　　Kiroku1 = rs("記録")
　　rs.MoveNext
　　If rs.EOF = False Then　　　　'ヒットレコード複数
　　　Kiroku2 = rs("記録")
　　　If Kiroku1 = Kiroku2 Then　 '記録値が2レコードで同じ
　　　　If GetFieldName = "会場" Then
　　　　　GetTopRec = "記録複数"
　　　　ElseIf GetFieldName = "記録" Then
　　　　　GetFieldName = rs("記録")
　　　　Else
　　　　　GetTopRec = ""
　　　　End If
　　　End If
　　End If
　End If
　rs.Close
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Function

前行までを貼り付けてください。＝＝＝＝＝＝＝＝

■シートの列名を添付画像に合わせてください。

提示された画像では

入力シート側　会場、開催日
集計側　　　　会場名、開催年月日
となっていましたので、前者に合わせました。

またデータシートの県名列に列名がありません。

■計算式を埋めてください。

例えば、E5なら、

=GetTopRec(Sheet1!$A$1:$L$10000,E$3,$A$5,$C5,$B5,$D5)

Sheet1!$A$1:$L$3000　は、データの範囲
E$3　は、集計したい項目名
$A$5　は、種別
$C5　　は、性別
$B5　は、距離
$D5　は、クラス(2)　です。

返答にシート名の記載がなかったので
sheet1　を前提に作成してあります。

データ量は最大10000行としました。

お礼

ありがとうございます
また、大変親切な回答申し訳ありません。
参考にして頑張ってみます。

補足

親切な回答を頂いた手前、再度質問するのは失礼と思い色々とやってみていました。
しかし、どうにもこうにも行き詰まってしまった為に補足コメントに入力させて頂きます。
VBAに関して第1回から第4回までをみて標準モジュールにコピーペーストを行いました。
添付画像にシート列名を合わせてsheet2のE5のセルに=GetTopRec(Sheet1!$A$1:$L$10000,E$3,$A$5,$C5,$B5,$D5)を入力して見ましたが#VALUE!と表示されてしまいました。
これは、第3回マクロの記録。の作業に関係がありますか？
再度、画像を添付出来ればいいのですが、文章での説明となり恐縮です。

回答No.2

HohoPapa

>エクセル関数で対応できますでしょうか？
スプレッドシート上で使える出来合いの関数（標準の関数）では、
極度に厳しいと思います。

100以上あるクラウスごとにシートを分けることで
関数式が比較的簡易になるとは思いますが
クラス横断の集計が求められたときに苦戦を強いられましょう。

入力シートの各レコードを、クラス順、記録順に並べ、
フィルターを使って確認する。
あるいは、いくつかの関数を組み合わせて
クラス別1位の行に色を染めるといったところが
妥当な解だろうと思います。

期待の集計表作成は、
VBAを使いユーザ定義関数を作成すればできるだろうと思います。
VBAは扱えますか？
以下、入力シートを見たうえでの疑問点などを列挙しますので
VBAに挑戦するのであれば、説明してみてください。

・ベストタイムが複数あり、１位が複数行になる可能性はありませんか？
　見たところ、集計先シートではそれが考慮されていないように見えます。
　それとも、開催日時のより大きい側を1位と判断すればいいですか？
　それとも、会場名などに複数ある旨の表示をしますか？
・入力シートのデータがどの列、何行目から開始しているのかが読み取れません。
・入力シートのシート名を明らかにしてください。
・クラス(1)の列は無視していいですね？
・会場に都道府県の列がありますが無視していいんですね？
・距離の列は、数値タイプで、書式設定を使い"m"を付加していますか？
　それとも文字列ですか？
・記録列は時刻形式（日付シリアル値）ですね？
・開催年月日は日付シリアル値ですね？
・開催年月日には、時刻も含まれていますか？
・数年将来を見据えたときに、
　入力シートの最大行数は、ざっくり何行程度を見積もりますか？
　数十万？、数万？、数千？
　数十万を超えるようならエクセルでは厳しいと思います。

お礼

お礼が遅くなりました。
回答有難う御座います。

挑戦してみたいと思います。

説明は次の通りです。

Q：VBAは扱えますか？
A：初めて聞きました。ググってみましたがピンときません。

Q:ベストタイムが複数あり、１位が複数行になる可能性はありませんか？
　見たところ、集計先シートではそれが考慮されていないように見えます。
　それとも、開催日時のより大きい側を1位と判断すればいいですか？
　それとも、会場名などに複数ある旨の表示をしますか？
A:会場名に複数ある旨の表示をしたいです。

Q:入力シートのデータがどの列、何行目から開始しているのかが読み取れません。
・入力シートのシート名を明らかにしてください。
A:見難く申し訳ありません。題名の『種別』はA1のセルに入力して始めます。データーはA2のセルからになります。

Q:クラス(1)の列は無視していいですね？
A:無視でいいです。

Q:会場に都道府県の列がありますが無視していいんですね？
A:無視でいいです。

Q:距離の列は、数値タイプで、書式設定を使い"m"を付加していますか？
　それとも文字列ですか？
A:文字列です

Q:記録列は時刻形式（日付シリアル値）ですね？
Q:開催年月日は日付シリアル値ですね？
A:シリアル値ではありません。（記録列は）セルの書式設定でユーザー定義でmm:ss.000と設定していました。開催年月日もセルの書式設定で日付を指定しています。

Q:開催年月日には、時刻も含まれていますか？
A:含ませれいません。

Q:数年将来を見据えたときに、
　入力シートの最大行数は、ざっくり何行程度を見積もりますか？
　数十万？、数万？、数千？
A:行数は千行を設けていました。千行以上になったら、千行まででの新記録だけを別のシートに写して再び入力を続けようかと思っていました。

こんな感じで、分かりますか？

回答No.1

kozo_k

少し複雑な式にはなると思いますが関数で可能と思います。
手順としては
(1)入力用のシートからIF関数で「種別」「距離」「性別」「クラス(2)」ごとの最小値のセルを特定する（ケースが多いのでこれが一番大変かと）。
(2)特定されたセルを参照元としてVLOOKUP関数で氏名以下を検索表示する。
でいけるかと思います。
(1)の手間を少なくするためには入力用シートを「種別」「距離」「性別」「クラス(2)」ごとのシート（または表）にしておくと特定（最小値）のセルを探すIFの関数式が簡単になるので楽かと、あるいは並べ替えとフィルターで並べ変えておくとかの方法も考えられます。(1)の作業をいかに楽にするかが肝だと思います。
実データでの検証はしておりませんのであしからず。

お礼

ありがとうございます
if関数も含めるやり方ですね。