Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2013)
セット商品を単品で集計
投稿日時: 20/10/14 11:34:05
投稿者: twodogs

お世話になります。宜しくお願いします。
 
セットで販売している商品と単品で販売している商品の販売数をを全て単品ごとで集計したいと思っています。
 
例えば
A(販売商品名)      B(販売数)
えんぴつ&消しゴムセット  5セット
えんぴつ6本セット     25セット
えんぴつ          1本
 
というピボットで作成した表から       
A(単品商品名)      B(販売数)
えんぴつ         156本
消しゴム         1個
 
という具合に集計したいです。
 
現状はセット内容の一覧シートなども作成していません。
ピボットの表から参照しようとすると、表を畳んだ際にはエラーが出てしまうため、集計時には
 
「えんぴつ&消しゴムセット」はえんぴつ1本と消しゴム1個のセット。
「えんぴつ6本セット」はえんぴつ6本のセット
なので、えんぴつを集計する際は
=5+(25*6)
と入力している次第です。
 
少ない量なら手入力でもいいのですが、過去5年分程の量になると辛いので、何か良い方法はないかとこの場で質問させてもらいました。
 
どうぞ宜しくお願い致します

回答
投稿日時: 20/10/14 14:04:33
投稿者: Suzu

引用:
というピボットで作成した表から

 
どうやったらピボットへ繋がるのか想像できないのですが
項目名 「えんぴつ&消しゴムセット」や「えんぴつ6本セット」から
それぞれの商品単品 が何個 なのかを関数で処理するのは面倒ではありませんか?
 
私なら素直に
引用:
現状はセット内容の一覧シートなども作成していません。
を作成します。

回答
投稿日時: 20/10/14 14:09:52
投稿者: んなっと

             A     B  C D     E  F
3          商品名 合計 / 数 積     商品 数
4         えんぴつ     3  3   えんぴつ 96
5 えんぴつ&消しゴムセット     9  9   消しゴム 23
6     えんぴつ6本セット     10 60         
7    えんぴつ12本セット     2 24         
8     消しゴム2個セット     7 14         
9           総計     31           
 
C4
=IF(OR(A4="",A4="総計"),"",B4*IFERROR(LOOKUP(10^9,RIGHT(LEFT(A4,MATCH(1,INDEX(0/MID(A4,ROW($1:$50),1),))),ROW($1:$50))*1),1))
下方向・↓
F4
=SUMIF(A:A,"*"&E4&"*",C:C)
下方向・↓

投稿日時: 20/10/14 14:34:57
投稿者: twodogs

Suzu様
 
コメントありがとうございます。
元々は売上明細データがありまして、そこからピボットで販売商品と販売数の集計をしました。
セット内容のシートを作成は出来ますが、それをどう活用すると単品の数量を集計できるようになるか教えて頂けますか?

投稿日時: 20/10/14 14:36:53
投稿者: twodogs

んなっと様
 
コメントありがとうございます。
まず、積の列を作れば良いことに目からウロコでした!
教えて頂いた関数は内容が良く理解できませんが、試してみたいと思います。
勉強になりました。
ありがとうございます。

回答
投稿日時: 20/10/14 15:14:19
投稿者: Suzu

シート リストに

	A	B	C	D
1	商品名	鉛筆	消しゴム	赤えんぴつ
2	えんぴつ	1		
3	えんぴつ&消しゴムセット	1	1	
4	えんぴつ6本セット	6		
5	えんぴつ12本セット	12		
6	消しゴム2個セット		2	
7	えんぴつ2本消しゴム3個セット	2	3	
8	えんぴつ2本消しゴムセット	2	1	
9	赤えんぴつ2本セット			2

 
の様な表を作ったとして
 
	A	B	C	D	E	F
3	商品名	セット数		鉛筆	消しゴム	赤えんぴつ
4	えんぴつ	3		3	0	0
5	えんぴつ&消しゴムセット	9		9	9	0
6	えんぴつ6本セット	10		60	0	0
7	えんぴつ12本セット	2		24	0	0
8	消しゴム2個セット	7		0	14	0
9	えんぴつ2本消しゴム3個セット	10		20	30	0
10	えんぴつ2本消しゴムセット	1		2	1	0
11	赤えんぴつ2本セット	1		0	0	2

 
セル D3
=VLOOKUP($A4,リスト!$A$2:$E$9,COLUMN()-2,0)*$B4
 
の様にできます。
(灰色の部分はそのままコピペしてExcel貼付を行い 行列を揃えてみてください)

投稿日時: 20/10/14 15:45:13
投稿者: twodogs

Suzu様
 
早々に教えて頂いてありがとうございました。
上手く集計できました!
勉強になりました。本当にありがとうございます