[[20190619150233]]　『指定した期間内で商品ごとに集約したうえで順位を』（みく）　ページの最後に飛ぶ

『指定した期間内で商品ごとに集約したうえで順位を表示』（みく）

下記データー域から商品ごとに集積したうえで順位ごとに抽出し並べ変えたいのですが、手動？でSUMIF集計後テキストに置き換え、重複の削除をしてRANK関数か並べ替えて順位を付けるぐらいしか頭に浮かびません・・・バカですみません。
拙い知識で困っております。お助けいただければ幸いです。

      A        B         C        D        E      F

 1      日付     商品名	個数	 売上	  経費   粗利
 2    2019/4/1     A	 1  	   200	    70	   130
 3    2019/4/1     B	 4	 4,000	 2,000	 2,000
 4    2019/4/3     B	 1	 1,000	   500	   500
 5    2019/4/3     C	56	11,200	 3,920	 7,280
 6    2019/4/3     A	 1	   200	    70	   130
 7    2019/4/3     D	 3	 2,100	 1,200	   900
 8    2019/4/8     E	 1	   500	   200	   300
 9    2019/4/8     A	 1	   200	    70	   130
 10   2019/4/9     F	 5	10,000	 2,500	 7,500
 11   2019/4/10    G	12	18,000	 7,200	10,800
 12   2019/4/11    E	 1	   500	   200	   300
 13   2019/4/12    G	10	15,000	 6,000	 9,000
 14   2019/4/12    H	 2	 6,000	 3,000	 3,000
 15   2019/4/14    E	 3	 1,500	   600	   900
 16   2019/4/15    A	 4	   800	   280	   520
 17   2019/4/25    E	 5	 2,500	 1,000	 1,500
 18   2019/4/26    G	45	67,500	27,000	40,500
 19   2019/4/30    G	 7	10,500	 4,200	 6,300
 20   2019/5/1     E	25	12,500	 5,000	 7,500
 21   2019/5/5     A	 9	 1,800	   630	 1,170
 22   2019/5/11    B	30	30,000	15,000	15,000
 23   2019/5/15    G	11	16,500	 6,600	 9,900
 24   2019/5/15    E	50	25,000	10,000	15,000
 25   2019/5/31    C	 5	 1,000	   350	   650

期間を区切って商品ごとを集積したうえで個数順位ごとに並べる。
※同数は同じ順位を付ける

抽出域

2019/4/1=> <=2019/4/30 ←任意のセルに期間入力すると

    A     B      C       D       E       F

   順位	商品名	個数	売上	経費	粗利
1   1	  G	 74	70,000	44,400	66,600
2   2	  C	 56	   500	 3,920	 7,280
3   3	  E	 10	91,800	 2,000	 3,000
4   4	  A	  7	 1,400	   490	   910
5   5	  B	  5	12,200	 2,500	 2,500
6   5	  F	  5	67,500	 2,500	 7,500
7   6	  D	  3	   500	 1,200	   900
8   7	  H	  2	 2,000	 3,000	 3,000

のように自動的に表示される。夢のようなことはできないでしょうか？

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

 >2019/4/1=> <=2019/4/30 ←任意のセルに期間入力すると

 なら、フィルタオプションを調べてみては。
 並べ替えは知らないけど。
（BJ） 2019/06/19(水) 16:01

Sub main()

    'Sheet1をSheet2に集約
    Dim dic As Object, k As Variant, c As Range, r As Range, ip1 As String, ip2 As String
    Set dic = CreateObject("Scripting.Dictionary")
    ip1 = InputBox("期間開始日入力", , Format(Date, "yyyy/m/d"))
    ip2 = InputBox("期間終了日入力", , Format(Date, "yyyy/m/d"))
    If Not (IsDate(ip1) And IsDate(ip2)) Then MsgBox "日付不正", vbCritical: Exit Sub
    For Each c In Sheets("Sheet1").Range("A:A").SpecialCells(2)
        If DateValue(ip1) <= c.Value And DateValue(ip2) >= c.Value Then
            dic(c.Offset(, 1).Value & "_1") = c.Offset(, 1).Value
            dic(c.Offset(, 1).Value & "_2") = dic(c.Offset(, 1).Value & "_2") + Val(c.Offset(, 2).Value)
            dic(c.Offset(, 1).Value & "_3") = dic(c.Offset(, 1).Value & "_3") + Val(c.Offset(, 3).Value)
            dic(c.Offset(, 1).Value & "_4") = dic(c.Offset(, 1).Value & "_4") + Val(c.Offset(, 4).Value)
            dic(c.Offset(, 1).Value & "_5") = dic(c.Offset(, 1).Value & "_5") + Val(c.Offset(, 5).Value)
        End If
    Next c
    Sheets("Sheet2").Cells.ClearContents
    Set r = Sheets("Sheet2").Range("A2")
    r.Offset(-1).Resize(, 6).Value = Array("順位", "商品名", "個数", "売上", "経費", "粗利")
    For Each k In dic
       r.Formula = "=RANK(RC[2],C[2])"
       r.Offset(, Val(Split(k, "_")(1))).Value = dic(k)
       If Val(Split(k, "_")(1)) = 5 Then Set r = r.Offset(1)
    Next k
    With Sheets("Sheet2").Sort
        .SortFields.Add Key:=Sheets("Sheet2").Range("A1"), Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
End Sub
（mm） 2019/06/19(水) 16:24

BJさんありがとうございます。
フィルタオプション調べてみます！でも難しそうですぅ。
下のプログラムみたいなのはせっかく書いてもらいましたが、ちょっと難しいです。
（みく） 2019/06/19(水) 16:55

ピボットテーブルはどうでしょうか。

（マナ） 2019/06/19(水) 20:02

まなさん！ありがとうございます。
ピボットテーブル初めて使いました。スゴク便利です！今までボーっと生きてました！

朝からいろいろやってみて
例題の表(全体)をピボットして、行に「商品名」Σ値に「個数」「売上」「経費」「粗利」を入れると下記のように表示され

 行ラベル▼ 合計 / 個数 合計 / 売上 合計 / 経費 合計 / 粗利
   A	        16         3200        1120	   2080
   B	        90        90000       45000	  45000
   C	        71        14200	       4970	   9230
   D             3         2100	       1200	    900
   E	       160        80000	      32000	  48000
   F	         5        10000	       2500	   7500
   G	        85       127500	      51000	  76500
   H	         2         6000	       3000	   3000

  総計	       432       333000	     140790	 192210

ここで合計／個数のセルで右クリック→並べ替え→降順で、商品ごとの個数ランキング！そのほか売上・経費・粗利での並べ替えも簡単にできてスゴク便利です！
ありがとうございました！

ただし、【2019/4/1=> <=2019/4/30】任意のセルに期間入力みたいな感じで、任意期間の集計がしたくてフィルターに「日付」を入れてみたところ、2019/4/1、2019/4/2・・・・・2019/5/30のように記載の日付が全部表示され、4/1～4/30以外の日付をプチプチ外さないとなんで、ちょっと効率悪いです。
ピボットする時に、任意の日付だけをすればいいのかなぁと思っております。
（みく） 2019/06/20(木) 10:49

 日付を行ラベルの商品名の上に追加。
 ピボットテーブルの日付部分を右クリックしてグループ化。
 日だけを選択すると右下の日数部分を入力可能になるので実際の期間より大きいに数を指定。

（ねむねむ） 2019/06/20(木) 11:04

 その後日付部分を右クリックでグループ化で上の部分で開始日、最終日のチェックを外し、抜き出したい期間を入力
 ではどうか？
（ねむねむ） 2019/06/20(木) 11:10

 おっと、上記に追加で日付を右クリック、フィルター-日付フィルターで一番右を「の間」にして上記で指定した
 期間を設定してくれ。
（ねむねむ） 2019/06/20(木) 11:41

ねむねむさん！ありがとうございます。
グループ化！気がつきませんでした！
グループ化って画像系でしか使わない言葉だと思ってましたが、これもスゴク便利です！
「月」でグループ化して、月の横の+-で合計、内訳展開と、これです。これです！夢のような機能！

皆さんありがとうございました！
（みく） 2019/06/20(木) 13:49

コメント返信：

[ 一覧(最新更新順) ]

[[20190619150233]] 『指定した期間内で商品ごとに集約したうえで順位を』（みく） ページの最後に飛ぶ

[[20190619150233]]　『指定した期間内で商品ごとに集約したうえで順位を』（みく）　ページの最後に飛ぶ