指定範囲の条件に合致する順位

質問

EXCELのVBAで以下のことをやりたいのですが、分かりません。
アドバイス頂けますでしょうか。

例として、1から40行固定の表があります。A、B、C、D列とあり、
A列は数値が必ず入っているセル（１～40）、
B列は時間のシリアル値で、値が入っているセルと入っていないセルの混在、
C列は文字列で値が必ず入っているセル、
D列は数値で、、値が入っているセルと入っていないセルの混在、という表があるとします。

この時に、
「”Bに値が入っていて、かつ、Dに値が入っていない行”の、”B列のトップ３”の、AとBのデータ」が
欲しいのですがどのようにしたらいいか分かりません。

お願いいたします

回答No.6

mt2008

ANo.2です。
他の方の回答にもありますが、別シートにコピーしてからやればいいです。

以下のサンプルでは、Sheet2の101行目以降にコピーしてANo.2と同様のソートをして、結果をSheet2の先頭に貼り付けています。
ユーザフォームには、Sheet2のA2:A4とB2:B4の内容を表示させればよいでしょう。
Sheet2を見せたくないなら非表示にしておきましょう。

Sub Sample()
　　With Sheets("Sheet2")
　　　　'-----Sheet2の101行目以降にコピー
　　　　.Cells.ClearContents
　　　　Sheets("Sheet1").Range("A1:D41").Copy .Range("A101")
　　　　'-----Sheet2でオートフィルターを掛ける
　　　　.Range("A101:D141").AutoFilter
　　　　.Range("$A$101:$D$141").AutoFilter Field:=4, Criteria1:="="
　　　　.AutoFilter.Sort.SortFields.Clear
　　　　.AutoFilter.Sort.SortFields.Add Key:=.Range("B101:B141"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
　　　　With .AutoFilter.Sort
　　　　　　.Header = xlYes
　　　　　　.Orientation = xlTopToBottom
　　　　　　.Apply
　　　　End With
　　　　'-----フィルタの結果をSheet2のA1にコピー
　　　　.Range("A101:D141").Copy .Range("A1")
　　　　.Range("A101:D141").AutoFilter
　　End With
End Sub

B列の時間のトップ3が早い時間のトップ3なのか遅い時間のトップ3なのかわからなかったので、「早い時間（昇順）」にしてあります。
降順にする場合は、Order:=xlAscending　→　Order:=xlDescending　と、変更してください。

回答No.5

MackyNo1

>説明不足でしたが、そのトップ3の値をユーザーフォーム上に表示させたいのでVBAでやりたいのですが。

ユーザーフォームに表示したいなら、単純にその数式セルの値をテキストボックスなどに代入するだけでよいと思います。

ちなみに、VBAで対応する場合でも、すべてマクロコードで対応するのではなく、パフォーマンスの優れた関数などを併用して（隠しシートに記載して）、その値を取得するほうが実戦的にも有効な方法です。

回答No.4

nao-y

> これですと実際のシートの表示が変わってしまいますよね？
> シートの表示を変えずに、トップ３のデータだけ欲しいのですが。。可能でしょうか？

シートをコピーして、コピーしたシートで作業して、終わったらシートを削除
すれば良いと思います。

回答No.3

nao-y

横ですみません。

> 頂いた方法を表示上は変えずVBAで可能でしょうか？

動作をマクロ記録されればわかると思います。

VBAならもう少し簡潔に書けそうですね。

Range("A1").Currentregionに対して
1.B列をKeyに昇順に並び替え
2.オートフィルタでD列空白が空白のセルのみ表示
3.結果として取得できるセルの上から3行のA,B列を表示

という感じでしょうか。

補足

ありがとうございます。
これですと実際のシートの表示が変わってしまいますよね？
シートの表示を変えずに、トップ３のデータだけ欲しいのですが。。可能でしょうか？

回答No.2

mt2008

オートフィルタで出来ます。

A:D列の1行目には項目名が入っているものとします。
1.A:D列を選択してフィルタを掛けます。
2.D列のフィルタリング条件を空白にします。これでD列が空白の行だけが表示されます。
3.次にB列を降順、または昇順の条件でフィルタリングします。

これでお望みの順番に並んだデータが出来たはずです。

補足

ありがとうございます。
説明不足でしたが、そのトップ3の値をユーザーフォーム上に表示させたいのでVBAでやりたいのですが。
頂いた方法を表示上は変えずVBAで可能でしょうか？

回答No.1

MackyNo1

トップ3のデータを表示するだけなら、関数で対応するほうが簡単かもしれません。

トップ3の意味が時間が大きい順に3つという意味でそのB列の時間を表示したいなら、例えばG2セルに以下の式を入力して下方向に3つオートフィルしてください(セルの書式は時刻にしてください）。

=LARGE(INDEX(($B$1:$B$40<>"")*($D$1:$D$40="")*$B$1:$B$40,),ROW(A1))

対応するA列のデータを表示するにはF2セルに以下の式を入力して下方向にオートフィルしてください。

=INDEX(A:A,MAX(INDEX(($B$1:$B$40<>"")*($D$1:$D$40="")*($B$1:$B$40=G2)*ROW($B$1:$B$40),)))

補足

ありがとうございます。
説明不足でしたが、そのトップ3の値をユーザーフォーム上に表示させたいのでVBAでやりたいのですが。
頂いた方法を表示上は変えずVBAで可能でしょうか？