EXCEL进货表中,每批进货的单价和数量都不一样,如何做一张汇总表来体现每个单品的总数量及平均进价?

Question

日期 机型 数量 单价
2011-1-14 酷派D520 20 1180
2011-1-18 华为5700 10 450
2011-1-27 OKWAP A320 60 268
2011-2-10 酷派D520 10 1180
2011-2-10 OKWAP A320 4 268
2011-2-10 华为8500 57 750
2011-2-14 华为8500 30 750
2011-2-16 OKWAP A320 20 268
2011-2-16 酷派D520 25 1180
2011-2-21 OKWAP A320 10 270
2011-2-21 华为8500 40 750
2011-2-23 酷派D520 10 1180
2011-3-1 华为8500 50 750
2011-3-11 华为8500 20 745
2011-3-16 华为8500 30 745
2011-3-23 华为8500 30 730
2011-3-25 华为8500 30 750
型号非常多,无法一一筛选复制到另外一张表中

Answer 1

使用透视表，如下图：

要点：

1，按向导操作，在布局里把“数量”和“单价”两个字段都拖入到数据区域。

2，双击“单价”字段，改为“平均值”。

3，设置完了之后得到的是一个分级显示的报表。继续，把“数据”拖到“汇总”的右方，得到并列字段的表头。

Answer 2

假设你的表中有A、B、C、D列，A1、B1、C1、D1标题行对应“日期”、“机型”、“数量”、“单价”。假设共有18行数据。

做法：

1、增加E列，标题名为“总价”。E2公式=C2*D2，公式往下复制；

2、增加F1、G1、H1、I1为汇总表标题，对应“机型”、“总数”、“总价”、“平均进价”

F2公式=IF((ROW()-1)>SUM(1/COUNTIF($B$2:$B$18,$B$2:$B$18)),"",INDEX(B:B,SMALL(IF(MATCH($B$2:$B$18,$B$2:$B$18,0)+1=ROW($2:$18),ROW($2:$18),4^8),ROW()-1)))

，同时按Shift+Ctrl+Enter，做成数组公式，公式再往下复制。

G2公式=SUMIF(B2:B18,F2,C2:C18)，公式再往下复制。

H2公式=SUMIF(B2:B18,F2,E2:E18)，公式再往下复制。

I2公式=H2/G2，公式再往下复制。 

如下图所示：

追问

不止18行数据,要怎么改,可能有一两千条,谢谢
方不方便留个QQ号..

追答

就改公式中的“18”这个数字好了，例如有1000行，18改为1000。
F2公式往下复制，不一定要复制到最后，复制到出现空单元格即可。
G2、H2、I2公式则复制到F列有内容的最后一行。

Answer 3

假设原数据在sheet1,同工作簿下的汇总表的B列为各机型
汇总表的C2输入公式 =IF(B2="","",SUMIF(sheet1!B:B,B2,sheet1!C:C))
汇总表的E2输入公式 =IF(B2="","",SUMIF(sheet1!B:B,B2,sheet1!E:E))
汇总表的D2输入公式 =IF(B2="","",E2/C2)
全选C2、D2、E2下拉填充公式即可

========================分割线==========================================
回复 bosslxt：
好吧，那就修改一下，从头开始做

sheet1的E2输入公式 =C2*D2
下拉填充公式

进入“汇总表”（请注意，是在汇总表下进行下面的操作），菜单栏上 数据--筛选--高级筛选--将筛选结果复制到其他位置--列表区域：sheet1!B:B--复制到：B:B --勾选“选择不重复的记录”--确定

汇总表的C2输入公式
=IF(B2="","",SUMIF(sheet1!B:B,B2,sheet1!C:C))
汇总表的D2输入公式 =IF(B2="","",SUMIF(sheet1!B:B,B2,sheet1!E:E)/SUMIF(sheet1!B:B,B2,sheet1!C:C))

全选C2、D2下拉填充公式即可

Answer 4

直接在进货表的E列中的E2单元格插入公式=SUMIF(B:B,B2,C:C)
下拉即可求出每个单品的总数量。在F列F2单元格中插入公式=
SUMIF(B:B,B2,C1*D1)/E2下拉即可求出每个单品的平均进价

Answer 5

这个有点小难度 最省事的方法就是按品种筛选重新粘贴成新表计算了