[[20150630114528]]　『RANK関数を使ったマクロを組みたいです。』（ぺもと）　ページの最後に飛ぶ

『RANK関数を使ったマクロを組みたいです。』（ぺもと）

 いつもお世話になります。

 今回、RANK関数を使ったマクロを組みたいと思っています。
 以下に例を載せます。

 　　　A　　　 　B

 1　　　　　　　500

 2 　　　　　　 489

 3　　　　　　　321

 4　　　　　　　 0

 5　　　　　　　 0

 6　　　　　　　 0

 7　　　　　　　 0

 8　　　　　　　 0

 9　　　　　　　 0

 10　　　　　　　0

 こんな表があります。

 空白のA列にB列の順位を表示したいと思っています。
 B列の数値は毎月変わるので、どこから0になるのかは毎月決まっていません。

 ここで迷っているのが、「0」の所は順位を表示させないようにしたいということです。

 　　　A　　　  　B

 1　　 1　　   　500

 2 　　2　　  　 489

 3　　 3　　 　　321

 4　　　　　　 　 0

 5　　　　　　 　 0

 6　　　　　　 　 0

 7　　　　　　 　 0

 8　　　　　 　　 0

 9　　　　　 　　 0

 10　　　　　 　　0

 こんな感じです。
 RANK関数のマクロは以下のように組めましたが、(上記の例とセルが異なりますが同じ意味です)
 「0」のところに表示させないという組み方が分かりません。
 なにかヒントをいただけないでしょうか。
 よろしくお願いいたします。

 Sub Macro1()

    Range("A5").Formula = "=RANK(F5,$F$5:$F$41,0)"
    Range("A5").Copy
    Range("A5:A41").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A5:A41").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1:F1").Select
 End Sub

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 回答ではなく質問です。 
 アップされたサンプルは１行目からですけど、コードでは５行目からにしているのはなぜですか？
 また、サンプルでは　Ｂ列のランクですけど、コードでは、Ｆ列のランクになっていますよね？

 >>上記の例とセルが異なりますが同じ意味です

 これを読み飛ばしていました。サンプル通りでいいのですね。

（β） 2015/06/30(火) 12:17

 とりあえずサンプル通りだとして。

 マクロを使わず、関数で問題ないとも思いますが。

 Sub Sample()
    With Range("B1", Range("B" & Rows.Count).End(xlUp))
        .Offset(, -1).Formula = "=IF(B1=0,"""",RANK(B1," & .Address & "))"
        .Offset(, -1).Value = .Offset(, -1).Value
    End With
 End Sub

（β） 2015/06/30(火) 12:29

 βさん、お返事ありがとうございます。

 サンプルで実行してみたら私の希望通りに実行してくれました。

 コードでF列とかになっているのは、他にも色々上記のような表があり、その内のひとつのコードをアップしたためです。
 まぎらわしくてすみません。
 先に説明しておけばよかったのですが、順位を入れたい列がA列ではなくてB列の表もあるし、
 順位を決める値が入力されている列がB列ではなくF列の表の場合もあるということです。
 さらに言えば、始まりが1行目でもありません。

 さて…βさんのコードをもとに、他の表に合わせてコードを変えてみましたがうまくいきません。

 順位を入力したいセルはB4:B40で、順色を決める値が入力されているセルはF4:F40の表を、以下のコードで試しました。
 エラーこそ出ませんが、表になんの変化もありません。
 どのあたりに工夫が必要になりますか。

 Sub Macro1()

    With Range("F4", Range("F" & Rows.Count).End(xlUp))
        .Offset(, -3).Formula = "=IF(F4=0,"""",RANK(F4," & .Address & "))"
        .Offset(, -3).Value = .Offset(, -3).Value
    End With
 End Sub

 毎月の作業であることと、表がたくさんあるため、マクロでどうにかならないかと思ってやっています。
（ぺもと） 2015/06/30(火) 14:21

 返信してすぐに気づきました…。

 Offset(, -3)にしていたのは、（作業上）C列を非表示にしていたためです。
 C列を再表示してみてみたら、しっかり順位が出ていました。
 F列からB列に表示するには-4ですね。

 実行したら問題なく思い通りにできました。

 お騒がせしました。ありがとうございます。
（ぺもと） 2015/06/30(火) 14:28

 …と思ったら問題が発生しました。

 表にはすべて、行の一番下に合計の値があります。
 βさんのコードを実行すると、合計の値も含めて順位を決めてしまうので、合計がいつも1位になってしまいます。

 順位を決めたいセルの範囲を指定するには、どこをどのように変えたらよいですか。
（ぺもと） 2015/06/30(火) 15:20

 ひとりで連投すみません。

 Range("F4", Range("F" & Rows.Count).End(xlUp))
 ⇒Range("F4:F40")

 こうしたらいけました。
 単純なことにパッと気づかずすぐ頼ってしまいます…。

 精進していきたいと思います。今後ともご指導よろしくお願いいたします。

（ぺもと） 2015/06/30(火) 16:07

 自己解決してるようですが範囲を固定にして解決したのですかね？
一応
Range("F" & Rows.Count).End(xlUp).Offset(-1)
とかの書き方でも良いのかなと思います。
最終行が変動してもフォローできるのかなと。
（ご近所PG） 2015/07/01(水) 11:49

 ご近所PGさん
 少しお返事が遅くなって申し訳ありません。
 たしかに、そうすれば範囲が変わっても対応できます。
 ご助言ありがとうございました。大変助かります。
（ぺもと） 2015/07/02(木) 09:43

コメント返信：

[ 一覧(最新更新順) ]

[[20150630114528]] 『RANK関数を使ったマクロを組みたいです。』（ぺもと） ページの最後に飛ぶ

[[20150630114528]]　『RANK関数を使ったマクロを組みたいです。』（ぺもと）　ページの最後に飛ぶ