レッスン-投稿一覧パソコン仕事５倍塾ポータル

投稿/コメントを表示します。

残業時間がいちばん多い従業員の情報を出力する。Max関数、Min関数より便利なマクロ

受講生さんの投稿

(投稿ID: 2811)

小川先生

最大値が複数存在した場合についてのコードを投稿します。
よろしくお願いします。

【課題を以下の通り、設定しています。】
　問題[1]　一番得点の高い人が複数いた場合でも抽出可能にする
　問題[2]　残業時間が一番多い人が複数いた場合でも抽出可能にする
　問題[3]　残業時間が一番多い人が複数いた場合でも抽出可能にする

'**************************************************************
'問題[1]　一番得点の高い人が複数いた場合でも抽出可能にする
'**************************************************************

Sub katinuki()
    
    Dim gyou As Long
    Dim kati As Long
    
    '処理1
    kati = 2
    For gyou = 2 To 11
        If Range("G" & kati).Value < Range("G" & gyou).Value Then
            kati = gyou
        End If
    Next gyou
'    Range("J" & kati).Value = "最高点です" '←不要かも

    '処理2
    For gyou = 2 To 11
        If Range("G" & kati).Value = Range("G" & gyou).Value Then
            Range("J" & gyou).Value = "最高点です"
        End If
    Next gyou
End Sub

'**************************************************************
'問題[2]　残業時間が一番多い人が複数いた場合でも抽出可能にする
'**************************************************************

Sub kati2()

    Dim gyou As Long
    Dim Migi As Long
    Dim kati As Long
    
    '処理1
    kati = 6
    Migi = 4
    For gyou = 6 To 33
        If Range("C" & kati).Value < Range("C" & gyou).Value Then
            kati = gyou
        End If
    Next gyou
'    Range("K" & Migi).Value = Range("B" & kati).Value '←不要かも
'    Range("L" & Migi).Value = Range("C" & "5").Value  '←不要かも
'    Range("M" & Migi).Value = Range("C" & kati).Value  '←不要かも
   
    '処理2
    For gyou = 6 To 33
        If Range("C" & kati).Value = Range("C" & gyou).Value Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("C" & "5").Value
            Range("M" & Migi).Value = Range("C" & gyou).Value
            Migi = Migi + 1
        End If
    Next gyou
End Sub

'**************************************************************
'問題[3]　残業時間が一番多い人が複数いた場合でも抽出可能にする
'**************************************************************

Sub kati3()
    
    Dim gyou
    Dim Migi As Long '追加
    Dim kati
    Dim retu
    Migi = 4 '追加
    
    retu = "C"
    kati = 6
    
    '処理1
    For gyou = 6 To 33
        If Range(retu & kati).Value < Range("C" & gyou).Value Then
            retu = "D"
            kati = gyou
        End If
    Next gyou
    
    For gyou = 6 To 33
        If Range(retu & kati).Value < Range("D" & gyou).Value Then
            retu = "D"
            kati = gyou
        End If
    Next gyou
    
    For gyou = 6 To 33
        If Range(retu & kati).Value < Range("E" & gyou).Value Then
            retu = "E"
            kati = gyou
        End If
    Next gyou
    
    For gyou = 6 To 33
        If Range(retu & kati).Value < Range("F" & gyou).Value Then
            retu = "F"
            kati = gyou
        End If
    Next gyou
    
    For gyou = 6 To 33
        If Range(retu & kati).Value < Range("G" & gyou).Value Then
            retu = "G"
            kati = gyou
        End If
    Next gyou
    
    For gyou = 6 To 33
        If Range(retu & kati).Value < Range("H" & gyou).Value Then
            retu = "H"
            kati = gyou
        End If
    Next gyou
    
'    Range("K" & Migi).Value = Range("B" & kati).Value　'←不要かも
'    Range("L" & Migi).Value = Range(retu & "5").Value　'←不要かも
'    Range("M" & Migi).Value = Range(retu & kati).Value　'←不要かも
    
    '処理2
    For gyou = 6 To 33
        If Range(retu & kati).Value = Range("C" & gyou).Value Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("C" & "5").Value
            Range("M" & Migi).Value = Range("C" & gyou).Value
            Migi = Migi + 1
        End If
    Next gyou

    For gyou = 6 To 33
        If Range(retu & kati).Value = Range("D" & gyou).Value Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("D" & "5").Value
            Range("M" & Migi).Value = Range("D" & gyou).Value
            Migi = Migi + 1
        End If
    Next gyou

    For gyou = 6 To 33
        If Range(retu & kati).Value = Range("E" & gyou).Value Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("E" & "5").Value
            Range("M" & Migi).Value = Range("E" & gyou).Value
            Migi = Migi + 1
        End If
    Next gyou

    For gyou = 6 To 33
        If Range(retu & kati).Value = Range("F" & gyou).Value Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("F" & "5").Value
            Range("M" & Migi).Value = Range("F" & gyou).Value
            Migi = Migi + 1
        End If
    Next gyou

    For gyou = 6 To 33
        If Range(retu & kati).Value = Range("G" & gyou).Value Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("G" & "5").Value
            Range("M" & Migi).Value = Range("G" & gyou).Value
            Migi = Migi + 1
        End If
    Next gyou

    For gyou = 6 To 33
        If Range(retu & kati).Value = Range("H" & gyou).Value Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("H" & "5").Value
            Range("M" & Migi).Value = Range("H" & gyou).Value
            Migi = Migi + 1
        End If
    Next gyou
End Sub

2017/02/05 23:09

ゲストさんのコメント

(コメントID: 4269)

受講生さん:

拝見しました。

まずは、ご自身でコードの内容について評価いただけますか。
それを読んだ上でコメントしたいと思います。

>小川先生
>
>最大値が複数存在した場合についてのコードを投稿します。
>よろしくお願いします。
>
>【課題を以下の通り、設定しています。】
>　問題[1]　一番得点の高い人が複数いた場合でも抽出可能にする
>　問題[2]　残業時間が一番多い人が複数いた場合でも抽出可能にする
>　問題[3]　残業時間が一番多い人が複数いた場合でも抽出可能にする
>
>

>'**************************************************************
>'問題[1]　一番得点の高い人が複数いた場合でも抽出可能にする
>'**************************************************************
>

2017/02/06 19:29

受講生さんのコメント

(コメントID: 4273)

小川先生

構成としては、２つのパート（「処理１」と「処理2」）に分けて
コードを書きました。
「処理１」は、データの先頭行から最大値を調べる。
「処理２」は、「最大値が見つかった行」を含めて、最大値含む行を調べる。
これにより、最大値が複数あった場合でも、最大値含む行の抽出にも対応可能
です。

だだ、処理２に関しては、データの先頭行から調べているため、
無駄だな～と思ったため、本日、もう一度、コードを見直ししました。
問題[1]と問題[2]のコードは、「処理２」for next構文の開始値を
「最大値が見つかった行（変数kati）」に変更して、動作確認をしました。

変更前

For gyou = 2 To 11

変更後

For gyou = kati To 33

問題[3]ですが、for next構文の開始値の見直しを保留としました。
難しそうです・・・

以上、よろしくお願いします。

2017/02/07 08:39

ゲストさんのコメント

(コメントID: 4275)

複雑なプログラムだなと思って読んでいましたが...そのような工夫をしたいという意図があったのですね。

この程度のデータ件数であればたいした時間の差になりません。
なので、僕なら、単純に、「処理1」では、最大値を取得してそれを変数に格納することだけを目指すかと思います。
「処理2」では、改めて全件調査して、その値と一致した場合のみリストに追記します。

では、データ数が多い場合はどうするか？ということになりますが...。
以下の2つですね。
[a] 「時間はかかるもの」と割り切ってじっと待つ
[b] 澤崎さんがしたようななんらかの工夫をする（パフォーマンス向上目的なら、もっと他にやり方ありそうです）
[c] 「処理2」では、発展編1で紹介する ForEach構文や.Findメソッドを利用し、効率的に該当セルを見つけるようにする。

[a]は、最初に「僕なら...」と言って示したやり方。基礎編ですし、「できないよりはいいじゃん」ということで。
[b]は、基礎編レベルの知識で無理やりパフォーマンス向上を目指すなら、ということで。煩雑になるのは避けられませんが、やりきれれば腕力はつくでしょう。
[c]は、「腕力でなく技で勝つなら」という感じかな。

よろしければ[a]の方法のマクロを書いてまたここにアップしてください。

2017/02/07 22:57

受講生さんのコメント

(コメントID: 4276)

小川先生

コメント有難うございます。[a]の方法のマクロを投稿します。
最大値を変数（Most）に入れようにしました。
最初に投稿したものよりは、シンプルなコードになったような気が
します。

'********
'問題[1]
'********

Sub katinuki()
    Dim gyou As Long
    Dim Most As Long '最大値用の変数
    
    '処理1→最大値を取得
    For gyou = 2 To 11
        If Most < Range("G" & gyou).Value Then
            Most = Range("G" & gyou).Value
        End If
    Next gyou

    '処理2→全件調査
    For gyou = 2 To 11
        If Range("G" & gyou).Value = Most Then
            Range("J" & gyou).Value = "最高点です"
        End If
    Next gyou
End Sub

'********
'問題[2]
'********

Sub kati2()
    Dim gyou As Long
    Dim Migi As Long
    Dim Most As Long '最大値用の変数
    
    '処理1→最大値を取得
    Migi = 4
    For gyou = 6 To 33
        If Most < Range("C" & gyou).Value Then
            Most = Range("C" & gyou).Value
        End If
    Next gyou
   
    '処理2→全件調査
    For gyou = 6 To 33
        If Range("C" & gyou).Value = Most Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("C" & "5").Value
            Range("M" & Migi).Value = Most
            Migi = Migi + 1
        End If
    Next gyou
End Sub

'********
'問題[3]
'********

Sub kati3()
    
    Dim gyou As Long
    Dim Migi As Long
    Dim Most As Long '最大値用の変数
    
    Migi = 4 '追加
    
    '処理1→最大値を取得
    For gyou = 6 To 33
        If Most < Range("C" & gyou).Value Then
            Most = Range("C" & gyou).Value
        End If
        If Most < Range("D" & gyou).Value Then
            Most = Range("D" & gyou).Value
        End If
        If Most < Range("E" & gyou).Value Then
            Most = Range("E" & gyou).Value
        End If
        If Most < Range("F" & gyou).Value Then
            Most = Range("F" & gyou).Value
        End If
        If Most < Range("G" & gyou).Value Then
            Most = Range("G" & gyou).Value
        End If
        If Most < Range("H" & gyou).Value Then
            Most = Range("H" & gyou).Value
        End If
    Next gyou
    
    '処理2→全件調査
    For gyou = 6 To 33
        If Range("C" & gyou).Value = Most Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("C" & "5").Value
            Range("M" & Migi).Value = Most
            Migi = Migi + 1
        End If
        If Range("D" & gyou).Value = Most Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("D" & "5").Value
            Range("M" & Migi).Value = Most
            Migi = Migi + 1
        End If
        If Range("E" & gyou).Value = Most Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("E" & "5").Value
            Range("M" & Migi).Value = Most
            Migi = Migi + 1
        End If
        If Range("F" & gyou).Value = Most Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("F" & "5").Value
            Range("M" & Migi).Value = Most
            Migi = Migi + 1
        End If
        If Range("G" & gyou).Value = Most Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("G" & "5").Value
            Range("M" & Migi).Value = Most
            Migi = Migi + 1
        End If
        If Range("H" & gyou).Value = Most Then
            Range("K" & Migi).Value = Range("B" & gyou).Value
            Range("L" & Migi).Value = Range("H" & "5").Value
            Range("M" & Migi).Value = Most
            Migi = Migi + 1
        End If
    Next gyou
End Sub

以上、よろしくお願いします。

2017/02/08 08:55

ゲストさんのコメント

(コメントID: 4279)

受講生さん:

とてもよいと思います。
基礎編レベルの技術で書くならこれがベストかと v(^^

一点だけあえて言うなら、僕が基礎編、発展編１レベルの知識しかない受講生だったとしたら、変数名は「Most」にはしません。
英単語１語の言葉って、エクセルVBAの予約語で使われている可能性が高いからです。

SortとかLeftとか、Midとか、そうですね。

初心者の方が、

Sub Left()
    ...
    ...
    ...
End Sub

なんていうプロシージャ名のプログラムを書いて、ハマる例をよく見かけます。
その状態でLeft関数を呼び出そうとすると想定外の挙動をする可能性大。

ということで、僕なら、 Saidai とかそういう名前にするかな。
発展編１まで受講済で「ハンガリアン記法」を学んでいらっしゃるなら、 dMax とかそんな名前もいいかも。先頭のdは As Double の意味。

>小川先生
>
>コメント有難うございます。[a]の方法のマクロを投稿します。
>最大値を変数（Most）に入れようにしました。
>最初に投稿したものよりは、シンプルなコードになったような気が
>します。

2017/02/08 17:47