ARTICLE · 982280
Excel报表制作
这篇文章主要包括以下内容：
根据现有月份（“月度销售成交汇总-截至5月.xlsx“），完成月度成交报表的制作，并可以根据之后更新月份的文件内容自动更新。
根据所有处理好的数据完成月度成交仪表盘的制作，并且可以根据不同的主题颜色自动改变仪表盘的样式颜色。
PART 1
Excel现有月份（5月）月度成交报表
我们需要根据下面的内容制作报表，”月度销售成交汇总-截至5月.xlsx“（part 1的所有操作都只在这个excel中操作）部分内容入下：
做好的报表内容如下：
接下来拆解一下操作：
5月成交金额同比=20年5月成交金额/19年5月成交金额-1
周同比=12月第一周/11月第后一周-1
月周同比=12月第一周/10月第一周-1
年周同比=18年第一周/16年第一周-1
实现下面的两张表格
这部分的内容需要用到条件聚合函数：
1.求和功能：sumif(条件判断所在的区域,条件,用来求和的数值区域)
sumifs(用来求和的数值区域,条件1所在的区域,条件1,条件2所在的区域,条件2)
2.计数功能：countif(),countifs()
3.平均：averageif(),averageifs()
4.最大值：maxifs()
5.最小值： minifs()
实现条件聚合的其他方法：数据透视表。
上图中的区域，省份列：
产品成交情况类似。
美化的步骤：
选中所有数值，单元格格式修改为数值，添加千分位符，小数保留0位。
选中所有环比，占比，单元格格式 修改为百分比，保留0位小数。
这个箭头以及发绿发红的数字：箭头的操作，开始-条件格式-图标集-其他规则-图表样式选择箭头
发红，发绿的操作：添加条件格式-突出显示单元格规则-大于 填写0-设置为选择其他格式 配置绿色字体。（红色类似）
对成交金额的数据条：条件格式-数据条
上面的小三角怎么添加：数字-自定义：[颜色10]0%▲;[红色]-0%▼。单元格格式只改变值显示方式，不改变数值本身。
PART 2
Excel新增月份（6月）月度成交报表
上面做的是5月份的报表，到了6月，得到”6月_每日销售成交汇总数据“，没有5月的数据标准，需要将其清洗为类似于5月的标准数据，才能进行下面的操作。
要先把上面的表变为下面的形式：
具体操作：（批量填充合并单元格）查找和选择-定位条件-空值-=上一个单元格的值-ctrl+enter将上述表格变成下面的表格还需要一些操作。
从”产品“中提取”产品类型“以及”期数“：
=left(文本，2)表示从左开始，向右提取2个字符。
=mid(文本，要提取的第一个字符的位置，提取字符的长度)，如果需要提取到最后一个字符，可令提取字符长度=999一个很大的数，或者
=mid(文本，要提取的第一个字符的位置，len(文本))。
上面完成好的有完整信息6月份的表格中的区域，省份，小组，业务组，职务类别信息在”销售人员表-截止7月1日.xlsx“中，需要使用vlookup函数，我们需要根据销售工号在销售人员表中查找数据。
=vlookup(需要填充的表格的匹配数据（这里是销售工号单元格）,在哪个数据区域内查找（销售人员表-截止7月1日.xlsx）,返回的数据在区域的第几列，精确匹配0/1)
”销售人员表-截止7月1日.xlsx“部分内容如下：
几个小技巧：
文本*1，转化为数值；数值&"",转化为文本，并且excel中靠左的是文本，靠右的是数值。
year(),mounth(),提取年份，月份。
IF(logical_test,value_if_true,value_if_false)
最后将6月份的数据变为与5月数据一样的标准数据后，把六月的数据粘贴到五月数据的下面。改变一下下面画框的位置即可。
但是发现更新上的数据（左边的日期是文本格式）与原有的数据（右边的日期是数值格式）成交日期列的数据结构不一致。如下图所示。
文本日期（靠左）转化为数值日期（靠右）：数据-数据功能-分列功能。
PART 3
Power Query自动化数据处理
如果之后月份的数据都是像6月份的数据不完整，可以使用Power Query完成重复的操作。
（输入原始数据—PQ自动处理—输出标准数据）
自动化处理每次需要处理相同的文件夹，把相关数据都放在一个文件夹中。下面的”6月_每日销售成交汇总数据.xlsx“,”7月_每日销售成交汇总数据.xlsx“,都是未经处理的非标准的数据。
打开月度销售数据监控.xlsx：
导入历史数据
接下来需要配置一下PQ读取数据的文件路径，让PQ能从指定的文件夹中直接获取到后续更新的相同结构的每月数据：
选择数据处理文件夹。可以读取数据处理文件夹中的所有文件，点击转换数据。
后续需要PQ合并所有文件名中包含”每日销售成交“的数据，在name点击筛选下拉框
这样能筛选出符合要求的表。
接下来加载数据，点击Content列右键-删除其他列。最后点击如下图，Content列右上角的按钮合并文件
点击sheet1-点击确定,这样就成功加载了筛选文件名后的数据。
接下里处理数据，先填充为成交日期为null的数据，销售工号为null的列类似。
计算客单价
对于时间列(年份，月份)：先添加列-复制列，然后操作如下图，最后将列名称改为成交年份。（月份同理可得）
产品列的分割：
拆分列实现上面的left,mid函数功能。
接下来需要匹配人员表数据：主页-新建查询-新建源-选文件-文件夹-选择数据处理文件夹-打开-转换数据重命名为销售人员表
依旧在Name列点击文本筛选器-选择包含-填入销售人员。Content列右键删除其他列，点击右上角合并文件，选择Sheet1，点击确认。
接下来点击数据处理这张表，从而将人员表连接到数据处理这张表中，具体操作如下图。
接下来点击销售人员表右上角的展开按钮
接下里需要将处理好的数据添加到历史数据中，需要修改数据处理中的列名，使得与历史数据的列名相对应。
接下来添加业务组列，
将职务类别从数字转化为对应的中文：添加列-条件列
接下来需要与历史数据上下并表
将新表命名为“汇总数据”。将客单价按照下面的内容，保留两位小数。
最后要将数据加载至excel的工作表中，主页 -关闭-关闭并上载-关闭并上载至
之后在最右侧查询&连接中找到汇总数据，右键加载到，选择表，点击确定。最后将该工作表的Sheet1命名为源数据。这样就完成了PQ的所有数据处理。
之后再有新的"7月_每日销售成交汇总数据.xlsx","销售人员表-截止8月1日.xlsx",直接放在数据处理文件夹中，然后去月度销售数据监控中刷新（）即可将7月的数据更新到数据表中。
PART 4
Excel月度成交仪表盘
仪表盘的成果：
仪表盘关键部分的制作过程：
月份和区域的筛选器筛选：用下面的2
sumifs和数据验证联动的筛选
数据透视表插入筛选器
根据上面的操作可以得到
这样存在一个问题，上述成交月份选择一个月的时候，会没有月环比。为了解决这个问题，引入了getpivotdata(从数据透视表中返回可见的数据)。
=getpivatdata(数据透视表字段名称data_field,数据透视表的位置 pivot_table,字段名称1 field1,字段需要满足的查询条件1item1)
为了解决前面提到的月环比消失的问题，可以让切片器控制一个单元格，然后让getpivotdata函数引用这个单元格，这样可以用切片器去控制多个getpivotdata函数。灵活返回各种查询结果，从而避免因筛选导致的数据缺失。
将区域放进数据透视表中的行，将成交月份放入筛选，可以得到如下内容，后续只需用getpivotdata引用单元格，实现筛选
下面的计算字段功能，可以新加入字段
数据透视表实现月环比：值显示方式-差异百分比-基本字段：成交月份；基本项：上一个
（差异百分比=（当前值-基准值）/基准值）
最后选取不同的主题色可以得到不同的配色：
注：
1.2020-07-01—>2020.06.01:date(year(日期)，month(日期)，day(日期))
2.每个月的第一天：date(year(日期)，month(日期)，1）
3.每个月的最后一天：date(year(日期)，month(日期)+1，1）-1
4.excel中 a&"*" , * 表示通配符，代表不定数量的字符；excel中 a&"?" , ? 表示占位符，代表一个字符
5.match(查找项，查找区域，0)—>返回的是数字(math)
6.index(区域，行号(放match函数的结果)，列号(放match函数的结果))—>根据数字（行号列号）找到具体的内容
7.index可以返回整行的内容：index(区域，0，列号)；，index返回整列的内容：index(区域，行号，0)
参考内容：https://b23.tv/mTcbjwP，https://b23.tv/LNsHKy4
END
