Excel2007で最小値から5順位内の抜き出し

質問

Excel2007で最小値から5順位内の列を抜き出し、コピペしたいです。

(G6:G15000)に数値が入ってまして、G4にその合計数値が入ってます。

G列から1000列以上に上記と同じ形(数値は違います)が入ってます。

合計数値の最小から5番目までの列をA列～E列にコピペしたいです。

A4、B4、C4、D4、E4にそれぞれの合計数値
A6、B6、C6、D6、E6から
ABCDEそれぞれの15000行目合計数値になる前の数値
理解頂けますかね？

よろしいお願いいたします。

回答No.5

kkkkkm · ベストアンサー

不正な文字ですか…
Range("G4"),
の後ろは半角スペース　半角のアンダーバーですよね…。
コードはコピペしてるんですよね。

こちらで試してみてください。手順としてはなんか姑息な気のするものですが…先のものよりは早いです。
Sub Example2()
Dim i As Long, j As Long
Dim buff As Variant, tmp As Variant

With Sheets("Sheet5")
buff = .Range(.Cells(5, "G"), .Cells(4, .Columns.Count).End(xlToLeft))
For i = LBound(buff, 2) To UBound(buff, 2)
buff(2, i) = .Range("G4").Column + i - 1
Next
For i = LBound(buff, 2) To UBound(buff, 2)
For j = UBound(buff, 2) To i Step -1
If buff(1, i) > buff(1, j) Then
tmp = buff(1, i)
buff(1, i) = buff(1, j)
buff(1, j) = tmp
tmp = buff(2, i)
buff(2, i) = buff(2, j)
buff(2, j) = tmp
End If
Next j
Next i
For i = 1 To 5
.Range(.Cells(4, i), .Cells(15000, i)).Value = .Range(.Cells(4, buff(2, i)), .Cells(15000, buff(2, i))).Value
Next
End With
End Sub

お礼

凄い！！！例え1000列でもサクッとできました。逆に1000列にしてしまって、容量不足みたいなメッセージに出会って不安がいっぱいでした。いつも手書きでソースを移させて貰ってます。本当にまだまだまだまだ駆け出しでパソコンに詳しくないんです(^_^ゞ
本当にありがとうございました。
よろしければ、またご協力お願いいたします。

回答No.6

imogasi

データ量が膨大で、こちらで作って、結果を確かめることができない。
勘違い（や間違い）がある、かもしれない、がその際はすみません。
ーー
小生は、どちらかというと、VBA派なのだが、本件は操作でやってみたもの。
いわば、Filter操作利用法。
データはG5:G16、H6:H16、・・・N5:N16 まで。下記。
G4の式は=SUM(G5:G16)
右方向に式複写して、H・・N列まで同じように合計を入れる
結果　G5:N18までは
283 285 287 289 291 293 295 297
2 3 4 5 6 7 8 9
31 32 33 34 35 36 37 38
43 43 43 43 43 43 43 43
23 23 23 23 23 23 23 23
34 34 34 34 34 34 34 34
21 21 21 21 21 21 21 21
12 12 12 12 12 12 12 12
27 27 27 27 27 27 27 27
64 64 64 64 64 64 64 64
11 11 11 11 11 11 11 11
7 7 7 7 7 7 7 7
8 8 8 8 8 8 8 8
上記データ例は、偏りのある例ですが、（手抜きですが）許してください。
ーー
G5:N16を範囲指定し、コピー。
G18を選択しておく。
形式を選択して貼り付け
その際、「行列を入れ替える」
G17に「合計」と文字列を入れる。フィルターの「項目見出し」を作るため。
結果　G１７：S25 は
ーー
合計
283 2 31 43 23 34 21 12 27 64 11 7 8
285 3 32 43 23 34 21 12 27 64 11 7 8
287 4 33 43 23 34 21 12 27 64 11 7 8
289 5 34 43 23 34 21 12 27 64 11 7 8
291 6 35 43 23 34 21 12 27 64 11 7 8
293 7 36 43 23 34 21 12 27 64 11 7 8
295 8 37 43 23 34 21 12 27 64 11 7 8
297 9 38 43 23 34 21 12 27 64 11 7 8
ーーー
G１７：S25を範囲指定して、次の操作を行う。
データーフィルター
「合計」の▼をクリック
数値フィルタを選ぶ
「トップテン」になっているが、
「上位」を「下位」に変更指定
順位数を５にセット
OK
結果
略。
これをコピーして適当な列に貼り付ける。合計数だけならG列だけで、コピー
ー貼り付けを行う。
その際、「形式を選択して貼り付け」で
「行列を入れ替える」を指定 して貼り付け。元に戻すことになる。
＝＝＝＝
実際では、列数が１０００列もあると操作（セル範囲の指定）も、しにくい。
少数列でマクロの記録を取り、列数と行数を実際にのものに、マクロの記録VBAコード内の行・列の数字で指定して合わせる 、などすることに、なるかな（注）。
フィルタなどは、行でできたことが、列ではできないことがあるようで（対称でない）、「行列を入れ替える」操作を行っている。
（注）データの最右列は
Sub test01()
col = Cells(6, 10000).End(xlToLeft).Column
MsgBox col
End Sub
私の例ではデータの最右列が、N列なので 14。
のようなコードで取得できる。上記では１００００列が限界。
最下行も同様な方法をよく使う、がある。

お礼

imogasiさん熱のこもったレクチャーありがとうございました。上手いことしたら上手くいきそうですね。暖まりました。

回答No.4

kkkkkm

なんか小文字になってしまっているのが原因かも…。

.Range(.Cells(4, "G"), .Cells(15000, .Cells(4, .Columns.Count).End(xlToLeft).Column)).Sort key1:=.Range("G4"), _
order1:=xlAscending, _

ここの
key1とorder1
のkとoを大文字のKとOにしてみてください。

お礼

なんか凄い残念なんですが通らないです。今まで使わなかった記号なんで文字化けとかではないですかね。でもなんか不思議です。

回答No.3

kkkkkm

多少時間がかかるけど単純な方法です。
Sheet1は実際のシート名で
シートXはそのままで(シートXという名前がすでにあれば無い名前に変更してください)

Sub Example()
Sheets("Sheet1").Copy After:=Sheets(Worksheets.Count)
ActiveSheet.Name = "シートX"
With Sheets("シートX")
.Range(.Cells(4, "G"), .Cells(15000, .Cells(4, .Columns.Count).End(xlToLeft).Column)).Sort key1:=.Range("G4"), _
order1:=xlAscending, _
Header:=xlNo, _
MatchCase:=False, _
Orientation:=xlSortRows, _
SortMethod:=xlStroke
.Range(.Cells(4, "G"), .Cells(15000, "K")).Copy Sheets("Sheet5").Range("A4")
End With
Application.DisplayAlerts = False
Sheets("シートX").Delete
Application.DisplayAlerts = True
End Sub

お礼

Sort key1:=.Range("G4"), _の_が不正な文字としてエラーになります。どうしたらよろしいですか？

補足

Range(.Cells(4, "G"), .Cells(15000, .Cells(4, .Columns.Count).End(xlToLeft).Column)).Sort key1:=.Range("G4"), _
の下線 _ で書きこみ最中にエラーになるんですが？

回答No.2

msMike

[No.1お礼]へのコメント、
》 私も説明がもっと上手かったらなぁと思います
チコちゃんに叱られないような説明を心がけてください。

》 合計数値の最小から5番目までの列をA列～E列にコピペ
てのも、難解です。「列をA列～E列にコピペ」ってどういうこと？

「合計数値」が這入ってる範囲 G4:ALR4 の値の中から「最小から」5番目までの値を、例えば 範囲 A4:E4 に表示しろ、ということではないのですか？
そういうことなら、次式を入力したセル A4 を右４列にオートフィル(貴方好みの表現ならコピペ？)すれば御の字ですけどォ～
=SMALL($G4:$ALR4,COLUMN(A1))
【お断り】上式は必ず配列数式として入力のこと

お礼

やり方は色々あるんですね。
チコちゃんによろしくです。

回答No.1

msMike

》 ABCDEそれぞれの15000行目合計数値になる前の数値
》 理解頂けますかね？
いいえ、「合計数値になる前の数値」てのが、理解できません。
どういうことですか？
なぜそんな広大な範囲を使って質問しているのですか？
例えば、100x100くらいのセル範囲のデータで質問して、貴方自身はそれを応用すれば良さそうなものを、と思っています。何か特別な理由でも？

お礼

すみません。合計数値の元となった数値がG6:G15000に入ってたんで。
私も説明がもっと上手かったらなぁと思います。(^_^ゞ