跟我学EXCEL系列文章0044

亲爱的小伙伴们，跟我学EXCEL系列福利来了，从初级一直到高级学习EXCEL系列文章，结合财务实际应用讲解，配合动图细节演示，通俗易懂，是一套比较系统的不可多见学习EXCEL的好文章。持续更新中！

本系列文章包括基础篇（包括技巧、函数）、进阶篇（主要是数据透视表）、高级篇（主要是Power Query）。

希望大家喜欢，欢迎提出宝贵意见和建议！

我们今天继续学习数据透视表。

三、EXCEL进阶篇-数据透视表11

16、数据透视表实用技巧---“字段、项目和集”

我们来学习“数据透视表分析”里的一个很重要也很实用的功能---“字段、项目和集”，这个功能是数据透视表里一个非常重要的一个功能！（EXCEL版本不同，这个功能的名称有可能不同）

我们以一张库存及销售表为例。截图一（全部数据在文末）。

截图一

（1）、计算字段

以此超级表为数据源创建如下数据透视表，日期组合为年月，筛选区字段为业务类别，筛选状态为“出库”，值字段为“数量”和“销售金额”。截图二。

截图二

数据透视表不仅可以将已有的字段进行类似于“搭积木”一样的汇总，还可以进行一定的计算，比如这个案例，如果我们在这个数据透视表想得到平均售价，就可以用“字段、项目和集”的功能进行计算，这个功能提供了两种计算方式，一个是计算字段，一个是计算项。

计算字段，就是对“字段列表”里列示的字段进行直接计算。比如这个案例里计算平均售价就是计算字段，光标放置在主区域，“数据透视表分析”--->“字段、项目和集”--->"计算字段"，名称这个地方我们输入“计算平均售价”，公式里在等号后面选择“销售金额”插入字段或双击输入，再输入“/”，再选择“数量”插入字段或双击输入，确定。动图一。

动图一

设置好以后，我们看，9月-11月的平均售价就出现了，现在我们不仅可以按月看平均售价，也可以按客户查看。这些操作是轻车熟路了，就不多说了。动图二。

动图二

不仅如此，这个计算字段还可以使用函数，比如为了计算精确，保留两位小数就可以添加四舍五入公式，修改计算字段也是从这个入口修改。动图三。

动图三

如果暂时不想使用这个新添加的字段，但公式还保留，那么和正常使用字段一样，拖拽出值区域即可，如果彻底不想要了，也可以删除，也是从这个入口进入后删除计算字段。动图四。

动图四

这个地方有人可能会想，咦！？我在数据源表用函数也可以直接计算出平均售价，然后数据透视表中这个字段设置“值汇总依据”下的“平均值”不也可以实现这个需求吗？为了对比，我们将数据源表里函数计算的均价命名为“平均售价”，将这两个平均售价对比后我们会发现，数据源里用函数计算的平均售价，在数据透视表里设置为平均值的销售均价是算术平均数，是不准确的，有误差，单货品是准确的，月平均或客户平均都是有误差的；用数据透视表计算字段功能计算的销售均价是加权均价，才是真正准确的销售均价！动图五。

动图五

同样的，进价均价也可以用这个功能实现，原理一样，就不演示了。

如果我们想得到“毛利”也可以用这个功能实现。动图六。

动图六

这个毛利如果也在数据源新增一列，用函数计算毛利，再和这个数据透视表里的计算毛利对比，你会发现这两个数值一样。对比具体过程就不演示了，有兴趣的可以练习对比。

同样的，毛利率也可以用这个功能计算得到。动图七。

动图七

如果我们在数据源也用函数计算出毛利率，在数据透视表中设置“值汇总依据”下的“平均值”，得出的结论和平均售价一样，辅助列计算毛利率的平均值依然是不准确的，用数据透视表计算功能计算得出的平均毛利率才是准确的！原理和上面的一样。

由此我们得出一个结论，就是涉及到汇总后加权平均值的计算，用数据透视表计算才是准确的，用数据源函数计算的有误差；涉及到汇总金额的用两个方法得到的数据是一致的。在使用数据透视表计算字段时要特别注意！

（2）、计算项

相比较而言，计算字段应用比较多一些，用到计算项的情况比较少一些。计算项，就是对“字段列表”里列示的字段中具体明细进行计算。

比如这个案例，我们做一张月度进销存报表，将“日期”字段放入行区域，将“业务类别”字段放入列区域，将“数量”和“进价”字段放入值区域，“业务类别”字段段的明细类别分别是期初、入库、出库，我们就可以用计算项的功能计算出期末数，这就是计算项。截图三。

截图三

但是计算项有要求，字段不能组合，那该怎么办呢？如果不组合，透视表1就会变形，而且我们这个进销存表也想按月查看。（透视表1是计算平均售价的，日期已经组合为年月）。透视表2准备计算期末数的，想计算项就得把光标放置在数据透视表的“业务类别”的明细项目单元格内，计算项才会由灰变黑可用。动图八。

动图八

一般来说，如果我们在实际操作中遇到这样基于同一数据源同时需要计算字段和计算项的情形时，我们考虑添加辅助列。我们先将日期组合取消，另外将计算字段也删除，因为如果计算机的内存如果不够也可能造成EXCEL无法计算项，因为同时计算字段和计算项对计算机的性能要求就比较高了。

进销存一般是按月计算的，所以我们将辅助增加的月字段放入筛选区，选择9月，将品名放入行区域。光标放置在“出库”单元格上进行计算项的操作。动图九。

动图九

不过，这样做出的进销存也有瑕疵，比如我们想按照牛肉牛骨的大类看，增加一个辅助列“品类”，或者数据源表就是品类和品名都有的情况下，我们再将品类放入品名前面，这个时候就会发现行区域多字段的情况下，明细数据会重复所有项目，比较啰嗦也不好看。动图十。

动图十

这种情况，我建议大家不要使用辅助列，用手动组合来实现这个需求，但如果明细项目有增加，手动组合还得手工修改，稍显麻烦，但这也是没办法的事。动图十一。

动图十一

这样一个9月的进销存报表就做好了。截图四。这个演示数据是混合了好几个月的数据，如果实际工作中，按照这个样式只放入9月的数据，到了10月只放入十月的数据，就上月期末放入本月期初，刷新不就是10月的进销存了？这样一个表也可以看做一个简易版的进销存系统了！

截图三

演示数据

日期	单号	品名	业务类别	单位	数量	进价	销售金额	业务员
2019-9-1		牛肉-80牛前	期初	千克	514.42	1741.44	-	-
2019-9-1		牛骨-牛脖骨218	期初	千克	4080.05	49282.75	-	-
2019-9-1		牛骨-肩胛骨136	期初	千克	3703.14	16791.3	-	-
2019-9-1		牛骨-85肋眼骨	期初	千克	2105.91	10579.59	-	-
2019-9-1		牛骨-国产牛肋排	期初	千克	3308.98	17626.96	-	-
2019-9-1		牛骨-牛胸排	期初	千克	1543.994	17124.77	-	-
2019-9-1		牛骨-牛脖骨218	期初	千克	634.492	16431.56	-	-
2019-9-1	9194362	牛骨-牛脖骨218	出库	千克	5	69.69	125	C
2019-9-1	9194362	牛骨-85肋眼骨	出库	千克	5	25.12	135	C
2019-9-1	9194362	牛骨-肩胛骨136	出库	千克	5	22.67	135	C
2019-9-1	9194363	牛骨-牛脖骨218	出库	千克	2.5	34.85	62.5	D
2019-9-1	9194364	牛骨-牛脖骨218	出库	千克	5	69.69	125	B
2019-9-1	9194365	牛骨-牛脖骨218	出库	千克	17.45	243.23	436.25	D
2019-9-1	9194367	牛骨-牛脖骨218	出库	千克	15	209.08	375	C
2019-9-4	9738024	牛肉-80牛碎肉439	入库	千克	54.42	1741.44	-	-
2019-9-4	9194368	牛骨-牛脖骨218	出库	千克	18.95	264.14	492.7	C
2019-9-4	9194368	牛骨-牛脖骨218	出库	千克	18.2	253.68	473.2	C
2019-9-4	9194369	牛骨-牛脖骨218	出库	千克	10	139.39	260	A
2019-9-4	9194369	牛骨-肩胛骨136	出库	千克	6.4	29.02	172.8	A
2019-9-4	9194369	牛骨-85肋眼骨	出库	千克	5.6	28.13	151.2	A
2019-9-4	9194369	牛骨-国产牛肋排	出库	千克	4.85	25.84	232.8	A
2019-9-4	9194370	牛肉-80牛碎肉439	出库	千克	54.42	1795.41	1877.49	C
2019-9-5	9194371	牛骨-牛脖骨218	出库	千克	25	348.47	650	C
2019-9-5	9194372	牛骨-牛脖骨218	出库	千克	5	69.69	140	B
2019-9-6	9738025	牛肉-牛前腱	入库	千克	880.05	49282.75	-	-
2019-9-6	9738025	牛骨-四肋牛排	入库	千克	373.14	16791.3	-	-
2019-9-6	9738025	牛肉-牛胸肉	入库	千克	215.91	10579.59	-	-
2019-9-6	9738025	牛肉-肩胛肉	入库	千克	338.98	17626.96	-	-
2019-9-6	9738025	牛肉-牛腩	入库	千克	571.54	26862.38	-	-
2019-9-6	9194373	牛肉-牛前腱	出库	千克	1.95	109.2	117	C
2019-9-6	9194374	牛肉-牛腩	出库	千克	4.45	209.15	231.4	C
2019-9-6	9194375	牛肉-80牛前	出库	千克	6.05	20.48	254.1	D
2019-9-7	9194376	牛骨-85肋眼骨	出库	千克	12.5	62.8	337.5	A
2019-9-7	9194376	牛骨-肩胛骨136	出库	千克	12.5	56.68	337.5	A
2019-9-7	9194377	牛骨-牛脖骨218	出库	千克	50	696.93	1300	C
2019-9-7	9194378	牛骨-牛脖骨218	出库	千克	16.1	224.41	450.8	B
2019-9-7	9194379	牛骨-肩胛骨136	出库	千克	5.15	23.35	139.05	D
2019-9-7	9194379	牛骨-牛脖骨218	出库	千克	6.4	89.21	166.4	D
2019-9-7	9194379	牛骨-85肋眼骨	出库	千克	6.85	34.41	184.95	D
2019-9-8	9194380	牛骨-牛脖骨218	出库	千克	30	418.16	780	C
2019-9-8	9194321	牛肉-肩胛肉	出库	千克	6.6	343.2	376.2	B
2019-9-9	9738026	牛肉-80牛碎肉2035	入库	千克	1336.06	45426	-	-
2019-9-9	9738027	牛骨-脐橙板	入库	千克	2081.9	52947.5	-	-
2019-9-9	9194323	牛骨-牛脖骨218	出库	千克	4.95	69	128.7	A
2019-9-9	9194323	牛骨-85肋眼骨	出库	千克	4.7	23.61	126.9	A
2019-9-9	9194323	牛骨-肩胛骨136	出库	千克	6	27.21	162	A
2019-9-9	9194324	牛骨-牛脖骨218	出库	千克	6	83.63	156	A
2019-9-9	9194324	牛骨-肩胛骨136	出库	千克	5.55	25.17	149.85	A
2019-9-9	9194325	牛骨-牛脖骨218	出库	千克	30	418.16	780	C
2019-9-9	9194325	牛骨-国产牛肋排	出库	千克	5	26.64	230	C
2019-9-9	9194326	牛肉-80牛前	出库	千克	7.6	25.73	334.4	B
2019-9-9	9194327	牛骨-四肋牛排	出库	千克	2.65	119.25	137.8	B
2019-9-10	9738028	牛肉-90牛前	入库	千克	1028.56	44851.36	-	-
2019-9-10	9738029	牛骨-牛胸排	入库	千克	-2.2	-28.6	-	-
2019-9-10	9194328	牛肉-80牛前	出库	千克	17.18	58.16	721.56	D
2019-9-10	9194328	牛肉-90牛前	出库	千克	10.85	473.12	531.65	D
2019-9-10	9194328	牛肉-90牛前	出库	千克	9.35	407.72	458.15	D
2019-9-11	9194329	牛骨-脐橙板	出库	千克	7.5	190.74	240	C
2019-9-11	9194329	牛骨-国产牛肋排	出库	千克	8	42.62	368	C
2019-9-11	9194330	牛肉-牛腩	出库	千克	10.7	502.9	556.4	D
2019-9-12	9194333	牛肉-90牛前	出库	千克	18.86	822.41	924.14	D
2019-9-12	9194333	牛骨-四肋牛排	出库	千克	35	1575	1680	D
2019-9-12	9194334	牛骨-牛脖骨218	出库	千克	25	348.47	650	C
2019-9-12	9194334	牛骨-国产牛肋排	出库	千克	7	37.29	322	C
2019-9-12	9194334	牛骨-脐橙板	出库	千克	3	76.3	96	C
2019-9-12	9194335	牛肉-80牛前	出库	千克	11	37.24	440	C
2019-9-12	9194335	牛骨-肩胛骨136	出库	千克	11.2	50.78	291.2	C
2019-9-12	9194336	牛骨-牛脖骨218	出库	千克	5.85	81.54	152.1	E
2019-9-12	9194336	牛肉-牛腩	出库	千克	4.2	197.4	218.4	E
2019-9-12	9194337	牛骨-四肋牛排	出库	千克	3.6	162	180	B
2019-9-16	9194338	牛肉-牛腩	出库	千克	3.15	148.05	163.8	B
2019-9-17	9738030	牛肉-80牛碎肉439	入库	千克	27.22	952	-	-
2019-9-17	9194339	牛骨-肩胛骨136	出库	千克	10.78	48.88	291.06	A
2019-9-17	9194339	牛骨-85肋眼骨	出库	千克	10.33	51.9	278.91	A
2019-9-17	9194340	牛肉-牛腩	出库	千克	5.9	277.3	306.8	D
2019-9-17	9194340	牛肉-80牛前	出库	千克	5.9	19.97	253.7	D
2019-9-17	9194301	牛肉-90牛前	出库	千克	85.28	3718.72	3837.6	B
2019-9-17	9194303	牛肉-牛腩	出库	千克	21.73	1021.31	1053.91	C
2019-9-17	9194304	牛肉-90牛前	出库	千克	26.8	1168.64	1286.4	C
2019-9-18	9194305	牛肉-90牛前	出库	千克	37.02	1614.29	1665.9	C
2019-9-18	9194305	牛肉-80牛前	出库	千克	35.96	121.73	1510.32	C
2019-9-18	9194305	牛肉-80牛碎肉439	出库	千克	27.22	898.03	1007.14	C
2019-9-18	9194305	牛肉-80牛前	出库	千克	-19.38	-65.61	-813.96	C
2019-9-19	9194306	牛骨-牛脖骨218	出库	千克	8.3	115.69	215.8	C
2019-9-19	9194306	牛骨-85肋眼骨	出库	千克	5.05	25.37	136.35	C
2019-9-19	9194306	牛骨-肩胛骨136	出库	千克	6.2	28.11	167.4	C
2019-9-19	9194307	牛肉-90牛前	出库	千克	15.88	692.46	778.12	C
2019-9-19	9194307	牛肉-牛前腱	出库	千克	2.25	126	130.5	C
2019-9-19	9194308	牛肉-90牛前	出库	千克	59.68	2602.4	2685.6	C
2019-9-19	9194309	牛肉-牛胸肉	出库	千克	4.3	210.7	221.88	C
2019-9-19	9194310	牛肉-80牛前	出库	千克	-16.58	-56.13	-696.36	C
2019-9-19	9194311	牛肉-90牛前	出库	千克	5.55	242.01	271.95	C
2019-9-19	9194312	牛骨-牛脖骨218	出库	千克	6.4	89.21	166.4	C
2019-9-20	9194314	牛肉-80牛前	出库	千克	19.38	65.61	767.45	C
2019-9-20	9194314	牛肉-牛腩	出库	千克	17.53	823.91	869.49	C
2019-9-20	9194314	牛肉-牛胸肉	出库	千克	20.11	985.39	1037.68	C
2019-9-20	9194314	牛骨-脐橙板	出库	千克	16.74	425.74	485.46	C
2019-9-20	9194315	牛肉-牛腩	出库	千克	9.4	441.8	451.2	C
2019-9-20	9194316	牛肉-90牛前	出库	千克	103.48	4512.35	4656.6	C
2019-9-21	9194318	牛肉-80牛前	出库	千克	34.56	116.99	1347.84	C
2019-9-21	9194319	牛肉-80牛前	出库	千克	14.4	48.75	604.8	C
2019-9-21	9194320	牛肉-牛前腱	出库	千克	10.1	565.6	585.8	C
2019-9-21	9195803	牛肉-90牛前	出库	千克	-5.55	-242.01	-271.95	C
2019-9-21	9195804	牛肉-90牛前	出库	千克	5.55	242.01	255.3	A
2019-9-21	9195805	牛骨-脐橙板	出库	千克	217.98	5543.73	6103.44	C
2019-9-21	9195806	牛骨-四肋牛排	出库	千克	4.85	218.25	242.5	C
2019-9-21	9195806	牛骨-脐橙板	出库	千克	5.15	130.98	164.8	C
2019-9-23	9195807	牛骨-四肋牛排	出库	千克	9.25	416.25	462.5	B
2019-9-23	9195807	牛肉-肩胛肉	出库	千克	5.55	288.6	327.45	B
2019-9-23	9195807	牛肉-牛腩	出库	千克	3.55	166.85	191.7	B
2019-9-23	9195808	牛骨-四肋牛排	出库	千克	5.1	229.5	255	B
2019-9-23	9195809	牛骨-脐橙板	出库	千克	93.6	2380.46	2714.4	C
2019-9-23	9195810	牛肉-90牛前	出库	千克	17.22	750.89	826.56	C
2019-9-23	9195811	牛肉-90牛前	出库	千克	26.52	1156.43	1299.48	C
2019-9-24	9195812	牛肉-90牛前	出库	千克	2.15	93.75	103.2	E
2019-9-24	9195813	牛肉-牛腩	出库	千克	19.33	908.51	966.5	C
2019-9-24	9195813	牛骨-四肋牛排	出库	千克	17.15	771.75	857.5	C
2019-9-24	9195814	牛肉-80牛前	出库	千克	181.79	615.4	7089.81	C
2019-9-29	9738031	牛肉-牛碎肉1352	入库	千克	234.97	8106	-	-
2019-9-29	9738031	牛肉-牛碎肉2550	入库	千克	224.04	8737	-	-
2019-9-30	9738032	牛肉-牛前腱42	入库	千克	1022.55	60841	-	-
2019-10-24	9195815	牛肉-90牛前	出库	千克	-26.52	-1156.43	-1299.48	C
2019-10-25	9195816	牛肉-90牛前	出库	千克	26.52	1156.43	1272.96	C
2019-10-26	9195817	牛骨-四肋牛排	出库	千克	5.8	261	280.72	E
2019-10-27	9195817	牛肉-牛前腱	出库	千克	2.45	137.2	144.55	E
2019-10-28	9194341	牛肉-90牛前	出库	千克	16.3	710.78	782	C
2019-10-29	9195820	牛肉-90牛前	出库	千克	2.5	109.01	120	E
2019-10-30	9477642	牛肉-牛腩	出库	千克	18.95	890.65	966.45	E
2019-10-31	9477642	牛肉-牛前腱	出库	千克	21.31	1193.36	1257.29	E
2019-11-1	9477641	牛骨-肩胛骨136	出库	千克	14.5	65.75	391.5	A
2019-11-2	9477641	牛骨-85肋眼骨	出库	千克	17.76	89.22	479.52	A
2019-11-3	9477643	牛骨-四肋牛排	出库	千克	-5.8	-261	-280.72	E
2019-11-4	9477644	牛肉-90牛前	出库	千克	9.25	403.36	444	B
2019-11-5	9477644	牛肉-80牛前	出库	千克	10.9	36.9	468.7	B
2019-11-6	9477644	牛骨-牛脖骨218	出库	千克	11.05	154.02	254.15	B
2019-11-7	9477644	牛骨-国产牛肋排	出库	千克	2.5	13.32	115	B
2019-11-8	9477646	牛肉-80牛前	出库	千克	3.35	11.34	144.05	C
2019-11-9	9477646	牛肉-90牛前	出库	千克	2.2	95.93	105.6	C
2019-11-10	9477647	牛骨-牛脖骨218	出库	千克	5	69.69	115	C
2019-11-11	9477647	牛骨-肩胛骨136	出库	千克	4.4	19.95	105.6	C
2019-11-12	9477648	牛骨-牛脖骨218	出库	千克	15.15	211.17	348.45	C
2019-11-13	9477648	牛骨-肩胛骨136	出库	千克	15.56	70.55	373.44	C