みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

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

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

投稿日時 - 2019-05-01 11:59:10

QNo.9612431

困ってます

質問者が選んだベストアンサー

不正な文字ですか…
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

投稿日時 - 2019-05-01 19:20:54

お礼

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

投稿日時 - 2019-05-01 22:01:50

ANo.5

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

データ量が膨大で、こちらで作って、結果を確かめることができない。
勘違い(や間違い)がある、かもしれない、がその際はすみません。
ーー
小生は、どちらかというと、VBA派なのだが、本件は操作でやってみたもの。
いわば、Filter操作利用法。
データはG5:G16、H6:H16、・・・N5:N16まで。下記。
G4の式は=SUM(G5:G16)
右方向に式複写して、H・・N列まで同じように合計を入れる
結果 G5:N18までは
283285287289291293295297
23456789
3132333435363738
4343434343434343
2323232323232323
3434343434343434
2121212121212121
1212121212121212
2727272727272727
6464646464646464
1111111111111111
77777777
88888888
上記データ例は、偏りのある例ですが、(手抜きですが)許してください。
ーー
G5:N16を範囲指定し、コピー。
G18を選択しておく。
形式を選択して貼り付け
その際、「行列を入れ替える」
G17に「合計」と文字列を入れる。フィルターの「項目見出し」を作るため。
結果 G17:S25は
ーー
合計
283231432334211227641178
285332432334211227641178
287433432334211227641178
289534432334211227641178
291635432334211227641178
293736432334211227641178
295837432334211227641178
297938432334211227641178
ーーー
G17:S25を範囲指定して、次の操作を行う。
データーフィルター
「合計」の▼をクリック
数値フィルタを選ぶ
「トップテン」になっているが、
「上位」を「下位」に変更指定
順位数を5にセット
OK
結果
略。
これをコピーして適当な列に貼り付ける。合計数だけならG列だけで、コピー
ー貼り付けを行う。
その際、「形式を選択して貼り付け」で
「行列を入れ替える」を指定して貼り付け。元に戻すことになる。
====
実際では、列数が1000列もあると操作(セル範囲の指定)も、しにくい。
少数列でマクロの記録を取り、列数と行数を実際にのものに、マクロの記録VBAコード内の行・列の数字で指定して合わせる、などすることに、なるかな(注)。
フィルタなどは、行でできたことが、列ではできないことがあるようで(対称でない)、「行列を入れ替える」操作を行っている。
(注)データの最右列は
Sub test01()
col = Cells(6, 10000).End(xlToLeft).Column
MsgBox col
End Sub
私の例ではデータの最右列が、N列なので 14。
のようなコードで取得できる。上記では10000列が限界。
最下行も同様な方法をよく使う、がある。

投稿日時 - 2019-05-01 20:19:02

お礼

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

投稿日時 - 2019-05-01 22:06:19

ANo.4

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

.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にしてみてください。

投稿日時 - 2019-05-01 18:45:47

お礼

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

投稿日時 - 2019-05-01 19:17:17

ANo.3

多少時間がかかるけど単純な方法です。
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

投稿日時 - 2019-05-01 16:05:42

補足

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

投稿日時 - 2019-05-01 17:44:26

お礼

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

投稿日時 - 2019-05-01 18:25:47

ANo.2

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

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

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

投稿日時 - 2019-05-01 14:53:22

お礼

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

投稿日時 - 2019-05-01 22:08:55

ANo.1

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

投稿日時 - 2019-05-01 12:52:13

お礼

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

投稿日時 - 2019-05-01 14:12:50

あなたにオススメの質問