エクセルデータ集計

質問

エクセルデータ集計の仕方がわからないので教えて下さい。

A列・・・月（○月）
B列・・・月日（2012/12/17）
C列・・・数量
D列・・・単位（kg・袋…など）
E列・・・商品名
F列・・・購入金額（C列×単価）
G列・・・単価

商品は100品ぐらいあり、入力データは2000件ぐらいあります。
一覧を商品名別で月ごとの平均単価を出したいのですが、ピボットテーブルで試してみてもなかなかうまくいきません。
ピボットテーブルじゃなくても構いません。
どなたかお分かりになるかたよろしくお願いします。

回答No.3

KURUMITO

シート1のA1セルに月、B1セルに月日、C1セルに数量、D1セルに単位、E1セルに商品名、F1セルに金額、G1セルに単価の文字列がそれぞれ入力されており各データは2行目から下方に入力されているとします。
A列の月の表示については1月といっても2011年の1月か2012年の1月かはっきりしませんので次の式をA2セルに入力して下方にドラッグコピーします。

=IF(B2="","",DATE(YEAR(B2),MONTH(B2),1))

その後にA列を選択して右クリックし「セルの書式設定」から「表示形式」の「ユーザー定義」で種類の窓には m"月" と入力してOKします。
これでA列には月が表示されます。

作業列としてH2セルには次の式を入力して下方にドラッグコピーします。

=IF(E2="","",IF(COUNTIF(E$2:E2,E2)=1,MAX(H$1:H1)+1,""))

お求めの一覧をシート2に表示させるとしてA1セルには商品名と入力し、A2セルから次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!H:H),"",INDEX(Sheet1!E:E,MATCH(ROW(A1),Sheet1!H:H,0)))

B1セルから横方向には月を表示させるために例えば2011年の1月から右横に月を表示させるとしたらB1セルには2011/1/1と入力し、C1セルには2011/2/1と入力します。その後にB1セルとC1セルを選択してそれらの式を右横方向にドラッグコピーします。その後にそれらの範囲を右クリックしてシート1のA列と同じようにして月が表示されるようにします。

B2セルには次の式を入力して右横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。

=IFERROR(SUMIFS(Sheet1!$G:$G,Sheet1!$A:$A,B$1,Sheet1!$E:$E,$A2)/COUNTIFS(Sheet1!$A:$A,B$1,Sheet1!$E:$E,$A2),"")

月の平均単価が商品ごとに表示されます。
小数点以下の数字が多くなりますので「ホーム」タブの「数値」リボンの「小数点以下の表示桁数を減らす」ボタンなどで表示の桁数を揃えます。

下方には商品目の一覧を入力します。

回答No.2

yosifuji20

先ずでデータを商品名で昇順に並べ替えをします。

その後で下記の式を入れます
H列　=IF(E10=E11,0,SUM($C2:C10)-SUM($H2:H10))　商品名毎の数量合計　
I列　=IF(E10=E11,0,SUM($F2:F10)-SUM($I2:I10))　商品名毎の金額合計　
J列　=IF(H10=0,0、I10/J10)　　商品名毎の平均単価

なお1行目はタイトル行でデータは無いこと、この式は10合目の場合でそれ以外はこの式をコピーすれば行が自動的に変わります。
これで同一商品番号の最後の行に平均単価が出ます。

回答No.1

chonami

>月ごとの平均単価

とは、具体的にどういった計算をするのですか？
ピボットでうまくいかなかったのは、どう駄目だったのでしょうか？
どういった表を作られたのでしょうか？