[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『RANK関数の範囲に変数を入れたいです』（ねろ）
初めて質問させていただきます。
今、インプットボックスで開始と終了の範囲を決めてランダムに混ぜるファイルをつくっています。
インプットボックスは何とかでき、範囲指定の動作を登録したマクロをいじりながら格闘しているのですが、インプットボックスのマクロとうまくつながらず、シャッフルできない状態です。以下が作ってみたマクロです。
Sub Macro1()
'
' Macro1 Macro
'
Dim ans As String
ans = InputBox("開始", "範囲指定")
If ans <> "" Then
Range("L1").Value = ans + 1
x = Range("L1").Value
End If
ans = InputBox("終わり", "範囲指定")
If ans <> "" Then
Dim y As Variant
Range("L2").Value = ans + 1
y = Range("L2").Value
End If
'
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],R[-& x &+1]C[-1]:R[-& y &+1]C[-1])"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E61"), Type:=xlFillDefault
Range("E2:E61").Select
ActiveWindow.SmallScroll Down:=-68
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G6"), Type:=xlFillDefault
Range("G2:G6").Select
End Sub
混ぜたいものについている番号が「セル＋１」なので、インプットボックスに入れた値に１を足したものをL1とL2に一度出すようにしています。
乱数表示している中で、開始と終わりを指定する際、D列の上からL1番目を開始、D列の上からL2番目を終わりにしたいです。
助言いただければ幸いです。
＜ 使用 Excel：Excel2016、使用 OS：unknown ＞
「混ぜたいものについているセル番号が、順番+1です。(2段目からスタートしています」
（ねろ） 2017/08/14(月) 13:09
ちょっと、よく分からないです。
シンプルなデータで、簡単なレイアウト図をアップできませんか？
（半平太） 2017/08/14(月) 13:54
能力不足で図のアップはできないです。申し訳ありません。
分かりにくくて恐縮ですが、言葉で説明を加えさせていただきます。
A列は、A1に「No.」A2以降、A3,A4…に番号が1,2,3…と振ってあります。
B列はそれに対応する名前がA2,A3,A4…に
１列あいてD列に =RAM() で乱数表示
E列は、E2に =RANK(D2,$D$5:$D$30) を入れてコピーで、乱数の順位付けをしています。
また１列あいてG列の、G2から下に、指定したA列についている番号の範囲の中で乱数の大きいものから持ってきたいです。
マクロでなく、マウスでコピーすればできるのですが、これ、マクロでできないでしょうか…
（ねろ） 2017/08/14(月) 15:34
＞=RANK(D2,$D$5:$D$30)
~~↑~
,$D$2 じゃないですか？
マクロが必要なんですか？
普通に下記数式を2行目から30行目までコピーすればいいんじゃないですか？
※29人分ですよね？ 人数が都度変わって困る、とか言う問題なんでしょうか？
(1) D2セル =RAND() (2) E2セル =RANK(D2,$D$2:$D$30) (3) G2セル =MATCH(30-ROW(A1),$E$2:$E$30,0)
行 _A_ ___B___ _C_ ____D____ _E_ _F_ __G__ 1 No. 順番 2 1 name01 0.77779 3 4 3 2 name02 0.448748 7 2 4 3 name03 0.835582 12 7
（半平太） 2017/08/14(月) 15:59
今は30で止まってるんですが、今後どんどん増えていって、数もその都度変わり頻繁に使うので、マクロがあったら便利だと思い質問させていただきました。
（ねろ） 2017/08/14(月) 16:47
Sub Macro1()
Dim ans As String
Dim x, y, zz
ans = InputBox("開始", "範囲指定")
If ans <> "" Then
Range("L1").Value = ans + 1
End If
ans = InputBox("終わり", "範囲指定")
If ans <> "" Then
Range("L2").Value = ans + 1
End If
x = Range("L1").Value '開始行
y = Range("L2").Value '終了行
zz = Abs(y - x) + 1 '件数
'更地化
Range("D2:E10000,G2:G10000").ClearContents
With Cells(x, "D").Resize(zz)
.Formula = "=RAND()"
.Value = .Value ’値化
End With
Cells(x, "E").Resize(zz).Formula = "=RANK(D" & x & ",$D$" & x & ":$D$" & y & ")"
Cells(x, "G").Resize(zz).Formula = "=INDEX($A$" & x & ":$A$" & y & ",MATCH(ROW(A1),$E$" & x & ":$E$" & y & ",0))"
End Sub
<結果図> 行 _A_ ___B___ _C_ ____D____ _E_ _F_ __G__ _H_ _I_ _J_ _K_ _L_ 1 No. 順番 4 2 1 name01 9 3 2 name02 4 3 name03 0.451303 3 8 5 4 name04 0.198446 5 7 6 5 name05 0.041042 6 3 7 6 name06 0.434827 4 6 8 7 name07 0.461402 2 4 9 8 name08 0.856341 1 5 10 9 name09
（半平太） 2017/08/14(月) 17:41
じゃ、その手順のように順次自動で作業してもらえるようVBA語で書けばいいだけです。
Sub test()
'B2セルからB列の最後のデータまでのセル範囲に対して
With Application.Range(Range("B2"), Cells(Rows.Count, "B").End(xlUp))
'1列右に数式入力
.Offset(, 1).Formula = "=Rand()"
'2列右に値転記
.Offset(, 2).Value = .Value
'範囲を2列分に拡張して右に1列ずらしたセル範囲を並び替え
.Resize(, 2).Offset(, 1).Sort .Cells(1, 2)
'1列右の列（数式を入れた列）をクリア
.Offset(, 1).ClearContents
End With
End Sub
ちなみに、元にしたマクロの記録が以下
Sub Macro2()
'
' Macro2 Macro
' マクロ記録日 : 2017/8/14 ユーザー名 : ma
'
'
Range("C2:C4").Select
Selection.FormulaR1C1 = "=RAND()"
Range("B2:B4").Select
Selection.Copy
Range("D2").Select
ActiveSheet.Paste
Range("C2:D4").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
Range("C2:C4").Select
Selection.ClearContents
End Sub
あ、エクセル2003で記録しましたので、並び替えのコードがそれ以降のバージョンと
書き方が変わってますが、2003のコードでも動きます。
（まっつわん） 2017/08/14(月) 18:09
マクロを書いてくださりありがとうございます。
はじめから最後までではなく、開始も終わりも自由に設定したくてインプットボックスなど使って探っています。
（ねろ） 2017/08/14(月) 18:14
>はじめから最後までではなく、 >開始も終わりも自由に設定したくてインプットボックスなど使って探っています。 ほー、、、じゃ、飛び飛びはなしですか？ 番号の何番から何番でいいのですか？
（まっつわん） 2017/08/14(月) 18:50
おっしゃる通りです。
とびとびは無いです。
半平太さんがかいてくださった図の、A列の何番から何番です。
（ねろ） 2017/08/14(月) 18:58
行き当たりばったりで書いたから変なコードになったorz
入力時点で何番から何番までが選択できるかわかったほうが親切だなぁ。。。（きっと）
Sub test()
Dim v As Variant
Dim Rng As Range
Dim i As Long
v = InputBox("連番入力(例5-10)", "番号")
If Len(v) = 0 Then Exit Sub 'キャンセルなら終わり
v = Split(v, "-") '文字をハイホンで分解
If UBound(v) <> 1 Then Exit Sub '分解した文字が2つ別れたか確認
If IsNumeric(v(0)) = False Then Exit Sub '分解した文字が数値として認識可能かチェック
If IsNumeric(v(1)) = False Then Exit Sub
'対象セル範囲の取得
Set Rng = Application.Range(Range("B2"), Cells(Rows.Count, "B").End(xlUp))
'番号が範囲内かチェック
For i = 0 To 1
If v(i) <= 0 Or v(i) > Rng.Count Then Exit Sub
Next
'B2セルからB列の最後のデータまでのセル範囲に対して
With Application.Range(Range("B2"), Cells(Rows.Count, "B").End(xlUp))
'指定のセル範囲の中で何番目のセルから何番目のセルまでにセル範囲を限定
With Application.Range(.Cells(v(0)), .Cells(v(1)))
'1列右に数式入力
.Offset(, 1).Formula = "=Rand()"
'2列右に値転記
.Offset(, 2).Value = .Value
'範囲を2列分に拡張して右に1列ずらしたセル範囲を並び替え
.Resize(, 2).Offset(, 1).Sort .Cells(1, 2)
'1列右の列（数式を入れた列）をクリア
.Offset(, 1).ClearContents
End With
End With
End Sub
（まっつわん） 2017/08/15(火) 07:14
お手数おかけしました。
本当にありがとうございます！！
シャッフルできました。
RANKでなくてもよいのですね。
（ねろ） 2017/08/15(火) 09:38
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.