品名に通し番号がついてしまっている。これを集計したい（エクセル・関数）

毎日の売上データ(DBから吐き出したCSV)に対して、「品名ごとの平均値」の集計を関数で行いたいです。その際にネックになっているのが【品名に通し番号が付いている】（例：あああー１、あああー２、いいいー１）こと。

品名 　　件数 部数
ううう-１ 5 500
えええ-１ 4 500
おおお-１ 3 500
おおお-２ 2 400
おおお-３ 4 500
おおお-４ 5 200
おおお-５ 6 500
いいい-１ 6 100
いいい-２ 4 500
いいい-３ 3 500
あああ-１ 5 500
あああ-２ 3 500
あああ-３ 2 1000

これを、以下のように集計したいのです。
（品名でくくって、平均とって、品名で並び替え）

品名 件数平均 部数平均
あああ 3 667
いいい 4 367
ううう 5 500
えええ 4 500
おおお 4 420
（平均は小数点切り捨て）

どのような関数を用いれば良いのでしょうか？どなたかご教授の程、どうぞ宜しくお願いいたします！
尚、商品名は固定ではなく、毎月変わっていきます。通し番号は多くても10くらいまでです。

データサンプル（エクセル）は、以下のＵＲＬからＤＬいただけます
http://firestorage.com/download/0006f2b0e1d58fc0 …

質問者からの補足コメント

• ＜訂正＞
  平均は小数点切り捨て⇒四捨五入の間違えでした！

    補足日時：2018/02/23 12:30

• 出来ました！なのですが、大変にうっかりしていて、前提のサンプル間違えてました・・・。
  件数は平均なのですが【部数はトータル】を出したかったのです・・・。
  二度手間で本当に申し訳ございません、こちらのご教授も頂けますと大変幸いです。
  
  ソースをコピペすれば再現出来る状況までレクチャーを頂き、本当に嬉しく思っております。
  有難うございます。

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/02/23 13:11

No.1 ベストアンサー 回答者： tom04 回答日時： 2018/02/22 21:09

こんばんは！

＞平均は小数点切り捨て
となっていますが、画像の結果を見ると
小数点以下第1位を四捨五入になっていますね。
（小数点以下を切り捨ての場合は「666」とか「366」になると思います）

手っ取り早くVBAでの一例です。
↓の画像のような配置で元データはSheet1にあり、Sheet2に表示するとします。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
Alt＋F8キー　→　マクロ　→　マクロ実行です。

Sub Sample1() '//この行から//
Dim myDic As Object
Dim i As Long, lastRow As Long
Dim myStr As String, wS As Worksheet
Dim myR, myKey
Set myDic = CreateObject("Scripting.Dictionary")
Set wS = Worksheets("Sheet2")
Application.ScreenUpdating = False
lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row
If lastRow > 1 Then
Range(wS.Cells(2, "A"), wS.Cells(lastRow, "C")).ClearContents
End If
With Worksheets("Sheet1")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
myR = Range(.Cells(2, "A"), .Cells(lastRow, "C"))
For i = 1 To UBound(myR, 1)
myStr = Left(myR(i, 1), InStr(StrConv(myR(i, 1), vbNarrow), "-") - 1)
If Not myDic.exists(myStr) Then
myDic.Add myStr, ""
End If
Next i
myKey = myDic.keys
For i = 0 To UBound(myKey)
wS.Cells(i + 2, "A") = myKey(i)
Next i
Set myDic = Nothing
For i = 2 To wS.Cells(Rows.Count, "A").End(xlUp).Row
.Range("A1").AutoFilter field:=1, Criteria1:=wS.Cells(i, "A") & "*"
wS.Cells(i, "B") = WorksheetFunction.Round(WorksheetFunction.Subtotal(1, .Range("B:B")), 0)　　'//★//
wS.Cells(i, "C") = WorksheetFunction.Round(WorksheetFunction.Subtotal(1, .Range("C:C")), 0) 　'//★//
Next i
wS.Range("A1").CurrentRegion.Sort key1:=wS.Range("A1"), order1:=xlAscending, Header:=xlYes
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
wS.Activate
MsgBox "完了"
End Sub '//この行まで//

※　質問文通り「切り捨て」にしたい場合はコード内の「★」の2行を
wS.Cells(i, "B") = Int(WorksheetFunction.Subtotal(1, .Range("B:B")))
wS.Cells(i, "C") = Int(WorksheetFunction.Subtotal(1, .Range("C:C")))
に変更してください。

※　関数でないので、データ変更があるたびにマクロを実行する必要があります。m(_ _)m

この回答へのお礼

早々に有難うございます！凄い。
当方マクロをやったことがないので、まずはチャレンジしてみます！
取り急ぎ、御礼までに。

お礼日時：2018/02/23 12:31

No.2 回答者： tom04 回答日時： 2018/02/23 14:44

No.1です。

＞【部数はトータル】を出したかったのです・・・

前回のコードの1行だけ手を加えてみてください。
＞wS.Cells(i, "C") = WorksheetFunction.Round(WorksheetFunction.Subtotal(1, .Range("C:C")), 0)
を
＞wS.Cells(i, "C") = WorksheetFunction.Round(WorksheetFunction.Subtotal(9, .Range("C:C")), 0)
にすれば大丈夫だと思います。

※　SUBTOTAL関数の集計方法を「9」に変更するだけです。m(_ _)m

この回答へのお礼

できましたー！！！素晴らしいです。本当にありがとうございました！！！

お礼日時：2018/02/23 16:14