[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『指定した期間内で商品ごとに集約したうえで順位を表示』(みく)
下記データー域から商品ごとに集積したうえで順位ごとに抽出し並べ変えたいのですが、手動?で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
'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
(マナ) 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
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.