エクセルのＲＡＮＫ関数だと思うのですが…教えてください。

質問

エクセルについての質問です。

　　Ａ　　　　Ｂ　　　　Ｃ　　　　　Ｄ　　　　Ｅ
１　日付　コード　　商品名　　個数　　金額
２　5/1　　001　　　○○　　　　1　　　1,000
３　5/1　　002　　　××　　　　2　　　10,000
４　5/2　　001　　　○○　　　　1　　　1,000
５　5/3　　003　　　△△　　　　5　　　3,000
６　5/3　　004　　　■■　　　　10　　 5,000
７　5/5　　004　　　■■　　　　5　　　2,500

という表を現在作っております。
これのランキングだと思うのですが
同じコードの場合売上金額を合計して下記みたいな
別表を作りたいと思っています。

★　売上ベスト１０
　　１位　　　002　　　　××　　　2　　　10000
　　２位　　　004　　　　■■　　　15　　　7500

どこから手をつけていいのかわからないので
教えて頂けませんでしょうか。よろしくお願いします。

回答No.4

Wendy02

こんにちは。

>１ヶ月単位で日毎に入力を追加しているのですが、

ということは、データ範囲が変わるということですよね。

>入力する度に統合で金額のソートする方法とかないのでしょうか？

今の段階では、リンクは出来ませんから、リンクさせるようなことをお望みなのですね。

こういう場合、時々、データベースでという人がいますが、今、考えてみると、仕組みは違っても、しょせん、計算しなおさないといけないようです。

そこで、以下は、単なる記録マクロの延長です。Excelの本来の機能を組み合わせただけですから、極めて単純な内容の積み重ねしかありません。根気さえあれば、誰でも作れる種類の内容です。

しかし、1,000個程度のデータでは、ほとんどストレスを感じずに、瞬時に出していただくことが可能だと思います。

決められた場所に書き込みしていただくだけで、可能です。別のシートにも書き出すことが出来ます。しかし、現在のマクロでは、他のブックに出力することは出来ません。

単純な内容ですが、コントロールツールのボタンやフォームのボタンにも付けられるように、細かいところで工夫が施してあります。

コントロールツールボタンを取り付けましたら、そのまま、シートタブを右クリックで、コードの表示

現在のところ、同数の時のランキングの処理がされておりません。それは、マクロでの処理可能ですが、数式で可能なものは、出来れば数式で処理してしまいたいと思いますが、ご意見をいただければ、考えたいと思います。

Sub Combine_Sort() を、

Private Sub CommandButton1_Click()

に上書きして、以下を貼り付けます。
フォームの場合は、そのまま、Combine_Sort 名を、マクロ名として登録してくだされば出来ます。しかし、なるべく、コントロールツールボタンのほうがよいです。

'<シートモジュール>
'---------------------------------------------------
Sub Combine_Sort()

'No.3046713 Jun,1,2007
'No.3046713 Jun,1,2007
Const データ元シート As String = "SHEET1"
Const データ元セル As String = "B1" '左上端

Const 転送先シート As String = "SHEET2"
Const 転送先セル As String = "A1"

Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim Rng1 As Range
Dim Rng2 As Range
Dim strSRng As String
Dim RngName As Name
　 '定数の切り替え
　 Set Sh1 = Worksheets(データ元シート)
　 Set Sh2 = Worksheets(転送先シート)
　 Set Rng1 = Sh1.Range(データ元セル)
　 With Rng1.CurrentRegion
　　 Set Rng1 = Sh1.Range(Rng1, .Cells(.Count))
　　 Application.Names.Add "tmp_1", "=" & Sh1.Name & "!" & Rng1.Address(1, 1)
　 End With
　 Set Rng2 = Sh2.Range(転送先セル)

　 'ソース元のアドレス設定
　 strSRng = "'" & ThisWorkbook.Path & "\[" & ThisWorkbook.Name & "]" & _
　　　　　　　 Sh1.Name & "'!" & Rng1.Address(1, 1, xlR1C1)

　　'設定のチェック
　　If Sh1 Is Sh2 Then
　　　　If Not Intersect(Rng2.CurrentRegion, Rng1) Is Nothing Then
　　　　　　　MsgBox "元のデータを消そうとしています。設定を見直してください", vbInformation, "設定エラー"
　　　　　　Exit Sub
　　　　End If
　　End If
　　Application.ScreenUpdating = False

　　Sh2.Select
　　'前回のデータをクリア
　　Rng2.CurrentRegion.ClearContents
　　'統合
　　With Rng2
　　.Consolidate Sources:=strSRng, _
　　　　　　　　 Function:=xlSum, _
　　　　　　　　 TopRow:=True, _
　　　　　　　　 LeftColumn:=True, _
　　　　　　　　 CreateLinks:=False

　　 Rng1.Cells(1, 1).Copy .Cells(1, 1)

　　 '数式の代入
　　　With .CurrentRegion
　　 .Cells(2, 2).Resize(.Rows.Count - 1).FormulaLocal = "=VLOOKUP(RC[-1],tmp_1,2,0)"
　　 .Cells(2, 2).Resize(.Rows.Count - 1).Value = .Cells(2, 2).Resize(.Rows.Count).Value
　　　Application.Names("tmp_1").Delete

　　　'並べ替え
　　　.CurrentRegion.Sort Key1:=.Cells(2, 4), _
　　　　　　　Order1:=xlDescending, _
　　　　　　　Header:=xlyess, _
　　　　　　　OrderCustom:=1, _
　　　　　　　MatchCase:=False, _
　　　　　　　Orientation:=xlTopToBottom
　　　'ランキングの列の挿入
　　　 .Columns(1).Insert Shift:=xlShiftToRight
　　　End With

　　　.Cells(1, 1).Offset(, -1).Value = "ランキング" 'タイトル
　　　.Cells(1, 1).Offset(1, -1).Value = "1位"　'半角
　　　.Cells(1, 1).Offset(1, -1).HorizontalAlignment = xlRight '書式は右揃え
　　End With

　　'フィルハンドルコピー
　　With Rng2.CurrentRegion
　　　　 .Cells(2, 1).AutoFill _
　　　　　Destination:=Sh2.Range(.Cells(2, 1), .Cells(.Rows.Count, 1)), _
　　　　　Type:=xlFillDefault
　　　　.Columns(1).AutoFit
　　End With
　　 Application.ScreenUpdating = True
　　Set Sh1 = Nothing: Set Sh2 = Nothing
　　Set Rng1 = Nothing: Set Rng2 = Nothing
End Sub

回答No.3

noname#204879

[ピボットテーブルレポート](ピボテ)による方法を紹介しておきます。

合計/金額
コード　　 合計
001　　　　 2,000
002　　　　10,000
003　　　　 3,000
004　　　　 7,500

最初に上のピボテを作成して次のように編集して行くのです。

　　　　　　↓(「合計」欄を降順に[並べ替え])

合計/金額
コード　　 合計
002　　　　10,000
004　　　　 7,500
003　　　　 3,000
001　　　　 2,000

　　　　　　↓(「商品名」を行フィールドに追加)

合計/金額
コード　　 商品名　合計
002　　　　××　　10,000
004　　　　■■　　 7,500
003　　　　△△　　 3,000
001　　　　○○　　 2,000

　　　　　　↓(「個数」をデータエリアに追加)

コード　商品名　データ　　 合計
002　　 ××　　合計/個数　　　 2
　　　　　　　　合計/金額　10,000
004　　 ■■　　合計/個数　　　15
　　　　　　　　合計/金額　 7,500
003　　 △△　　合計/個数　　　 5
　　　　　　　　合計/金額　 3,000
001　　 ○○　　合計/個数　　　 2
　　　　　　　　合計/金額　 2.000

　　　　　　↓(データを横並びに変更)

　　　　　　　　データ
コード　商品名　合計/個数　合計/金額
002　　 ××　　　　　　2　　 10,000
004　　 ■■　　　　　 15　　　7,500
003　　 △△　　　　　　5　　　3,000
001　　 ○○　　　　　　2　　　2,000

回答No.2

Wendy02

こんばんは。

>別表を作りたいと思っています。

別表をつくるなら、データ－統合をすればよいです。

移したい場所に、マウスカーソルを置いてから、メニューのデータ－統合。日付を省いたコードを左端として、「統合範囲元」に入れて、
「統合の基準」にチェックを入れ、後は、OK で、まとまるはずです。

後は、RANK関数というより、まず、金額で並べ替えて、後は、レイアウトを整えればよいです。商品名は、「統合」では来ませんから、VLOOKUP関数などを使うとよいです。

例：　H列に、コードがあるとします。
=VLOOKUP(H2,$B$2:$C$7,2)

順位は、「1位」を入れて、フィルハンドル（＋）でドラッグで連続数値が入ります。関数が必要ないかもしれません。

お礼

統合リンクでいけるのですね。
ありがとうございました。

補足

ありがとうございます。
統合でできました。
ついでに教えて欲しいのですが、
現在、表の行が600位で設定していて、１ヶ月単位で日毎に入力を
追加しているのですが、入力する度に統合で金額のソートする方法とか
ないのでしょうか？

回答No.1

hikari_ab

RANKを使わなくてもA1からE7の範囲をアクティブにして
データ　ソート　金額で降順
ではだめなのですか？

補足

早速の回答ありがとうございます。

範囲をアクティブという意味があまりよくわからないのですが、
データソートだけだと売上金額が合算されないので、
結局、どの商品が一番売上あったのかわからないのです。