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

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

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

たかちゃんさんの投稿

(投稿ID: 4894)

発展編１が一通り終了したので、次へ進む前に復習がてら基本編に戻って見ました。最初は、For each と.Offset(c,cyoko)の様な感じで書こうかと思ったのですが、For eachではcやcyokoなどの変数を使ってピンポイントで指定して書くことはできないのだと気付きました。（コレクションのプロパティを指定する時に使うものだから。）　
for構文とOffsetを使って書いてみましたので投稿します。他の方のコメントにあったランキングにも挑戦してみました。
繰り返せば繰り返すほど、新たな学びがあって本当に良い講座です。

Sub rensyu()
    Dim c As Long
    Dim cyoko As Long
    Dim mx As Long
    Dim cMX As Long
    Dim cyokoMX As Long
    
    mx = Range("H1048576").End(xlUp).Row - 6
    With Range("C6")
    cMX = 0
    cyokoMX = 0
    For c = 0 To mx
        For cyoko = 0 To 5
            If .Offset(c, cyoko).Value > .Offset(cMX, cyokoMX).Value Then
                cMX = c
                cyokoMX = cyoko
            End If
        Next
    Next
    Range("K4").Value = .Offset(cMX, -1).Value
    Range("L4").Value = .Offset(-1, cyokoMX).Value
    Range("M4").Value = .Offset(cMX, cyokoMX).Value
    End With
    
End Sub

2020/10/13 15:24

小川慶一さんのコメント

(コメントID: 6856)

たかちゃんさん:

おはようございます。

発展編１でworksheetfunctionの活用を学びます(テキスト参照)。それを活用すると以下のような感じです。

> For eachではcやcyokoなどの変数を使ってピンポイントで指定して書くことはできないのだと気付きました。（コレクションのプロパティを指定する時に使うものだから。）

セルを取得すればその .Row, .Column プロパティを取得できますので、これを元にして、あるセルを起点にそのセルについて示したラベルの値(この事例であれば、名前とか月とか)を取得可能です。

Sub set_max_value_data()
    '最大値を取得 worksheetfunction.max 使用
    Dim rgAll As Range
    Set rgAll = Range("C6:H33")
    
    Dim rg As Range
    Dim mx As Double
    mx = WorksheetFunction.Max(rgAll)
    
    Set rg = rgAll.Find(What:=mx, LookAt:=xlWhole)
        
    Range("K4").Value = Range("B" & rg.Row).Value
    Range("L4").Value = Range("A5").Offset(, rg.Column - 1).Value
    Range("M4").Value = mx
End Sub

Sub get_ranking_from_1_to_10()
    'ランク取得 worksheetfunction.rank 使用
    '5位以降は複数のデータが同順の場合があるので説明略(発展編2まで学習が進むと効率的なやり方があります)
    Range("O5").Value = "順位"
    Range("P5").Value = "数値"
    Dim rgAll As Range
    Set rgAll = Range("C6:H33")
    
    Dim rg As Range
    Dim iRank As Long
    For Each rg In rgAll
        iRank = WorksheetFunction.Rank(rg.Value, rgAll, 0)
        If iRank < 6 Then
            Range("O5").Offset(iRank).Value = iRank & "位"
            Range("P5").Offset(iRank).Value = rg.Value
        End If
    Next
End Sub

2020/10/13 18:37

受講生さんのコメント

(コメントID: 6857)

小川慶一さん:

先生、ありがとうございます。頂いたコードを読み解きながら手を動かしているうちに記憶が蘇ってきました。
毎日、End(xlup).rowを使っているにも関わらず、.rowや.colomnのプロパティをすっかり忘れていました。

For EachとOffsetでなんとかできないかインターネットで調べていた時に、どこかのサイトで「できない」と断言されていて、すっかり出来ないのだと思い込んでいました。

Worksheetfunction.maxを使えばこんなにもあっさり出来てしまうんですね。ついついFor文などで一つ一つ調べることに気を取られて発展編１の最後で勉強したのにも関わらず、このアイデアは思いつきませんでした。
ランキングも、自力で作成した時は、変数ichiban 、niban、、、と作ってIf文で振り分けて格納してから並べていました。
Range("O5").Offset(iRank).Value = iRank & “位”を見た時、発展編１のあの方法か！と気付きました。つい２日位前まで毎日書いていたのに、こちらも全く気付きませんでした。これからは並び替えに困ったら、この方法で出来ないか１番に考える様にします。

実践は全然違いますね。。。（^^;;　今回頂いたコードは忘れないよう印刷して暫く毎日練習します。毎回、ありがとうございます。

> たかちゃんさん:
>
> おはようございます。
>
> 発展編１でworksheetfunctionの活用を学びます(テキスト参照)。それを活用すると以下のような感じです。
>
> > For eachではcやcyokoなどの変数を使ってピンポイントで指定して書くことはできないのだと気付きました。（コレクションのプロパティを指定する時に使うものだから。）
>
> セルを取得すればその .Row, .Column プロパティを取得できますので、これを元にして、あるセルを起点にそのセルについて示したラベルの値(この事例であれば、名前とか月とか)を取得可能です。
>
>

Sub set_max_value_data()
>     '最大値を取得 worksheetfunction.max 使用
>     Dim rgAll As Range
>     Set rgAll = Range("C6:H33")
>     
>     Dim rg As Range
>     Dim mx As Double
>     mx = WorksheetFunction.Max(rgAll)
>     
>     Set rg = rgAll.Find(What:=mx, LookAt:=xlWhole)
>         
>     Range("K4").Value = Range("B" & rg.Row).Value
>     Range("L4").Value = Range("A5").Offset(, rg.Column - 1).Value
>     Range("M4").Value = mx
> End Sub
> 
> Sub get_ranking_from_1_to_10()
>     'ランク取得 worksheetfunction.rank 使用
>     '5位以降は複数のデータが同順の場合があるので説明略(発展編2まで学習が進むと効率的なやり方があります)
>     Range("O5").Value = "順位"
>     Range("P5").Value = "数値"
>     Dim rgAll As Range
>     Set rgAll = Range("C6:H33")
>     
>     Dim rg As Range
>     Dim iRank As Long
>     For Each rg In rgAll
>         iRank = WorksheetFunction.Rank(rg.Value, rgAll, 0)
>         If iRank < 6 Then
>             Range("O5").Offset(iRank).Value = iRank & "位"
>             Range("P5").Offset(iRank).Value = rg.Value
>         End If
>     Next
> End Sub

2020/10/14 10:35

小川慶一さんのコメント

(コメントID: 6858)

受講生さん:

おはようございます。

> 毎日、End(xlup).rowを使っているにも関わらず、.rowや.colomnのプロパティをすっかり忘れていました。
> 今回頂いたコードは忘れないよう印刷して暫く毎日練習します。

まあ...こうやってアイデアがだんだんついてくるものです。
何回か書けばしっくりくるかと思います。

ひきつづき、よい学びを☆

2020/10/15 18:58