6步制作Excel动态销售年报可视化看板，图报自动生成，直接套用
一、效果图
二、数据源
数据源与月报一样，为公司2018年1月1日至12月31日的所有销售订单数据，其中月份公式为I2=MONTH(B2)，函数MONTH可以返回日期中的月份。因作图计算需要，还模拟了上年的数据。
三、年报看板的制作思路
1、 明确数据分析和展示要素；
2、 求出作图所需数据，制作图表；
3、 设计数据分析看板布局；
4、 插入组合框窗体控件和照片，实现动态查询；
5、将相关的数据和图表放在看板合适的位置；
6、 调整配色，美化看板。
7、工作表【数据源】为数据源存放处，【年报计算】为年报看板图表所需数据计算和图表制作工作表，【业务员照片】为业务员照片存放工作表。
四、操作步骤
第一步：明确数据分析和展示要素
案例展示的要素和展示方式为：
1、用大字报的方式展示本年订单数和销售额，本年单月最高和最低销售金额、订单情况。
2、与上年相比订单和销售额的变化情况，增加显示红色向上的三角形，数据为红色，减少显示绿色向下的三角形，数据为绿色，相等显示白色等号，数据为白色。由于要在一个单元格显示三角形和数据两项内容，所以用粘贴链接的图片的形式展示。
3、 根据筛选的销售业务员情况，显示业务员的照片。
4、用柱形和折线组合图展示销售员每月的销售金额和与上年的同比情况。
5、用柱形和折线组合图对比展示销售员本年和上年月订单情况。
6、用条形图展示本年度的商品排行版、区域排行版、城市排行版，业务员销售排行版和渠道排行版情况。
第二步：根据展示要素要求设计公式求出作图数据，制作图表
1、用C2单元格与后续组合框连接，C2单元格数据将根据组合框不同的选项而变化，从而实现动态效果。
2、用INDEX函数计算出组合框选项所选的销售业务员。
业务人员D2=INDEX(B13:B22,C2)
3、用IF、SUM和SUMIF函数统计本年销售额。
本年总销售额C5=IF(D2='所有人员',SUM(数据源!E:E),SUMIF(数据源!F:F,年报计算!D2,数据源!E:E))，用if函数判断，选择【所有人员】时，计算所有金额。
4、用IF、COUNT和COUNTIF函数统计本年总订单。
本年总订单C6=IF(D2='所有人员',COUNT(数据源!E:E),COUNTIF(数据源!F:F,年报计算!D2))
5、用IF函数实现本年销售额和订单与上月相比，不同情况显示不同。
即增加时显示红色向上三角形，相同时显示等号，减少时显示绿色向下三角形，并显示增加和减少比例数据。公式为：
E5=IF(C5>I5,'▲',IF(C5=I5,'=','▼'))
E6=IF(C6>I6,'▲',IF(C6=I6,'=','▼'))
F5=IF(C5>I5,(C5-I5)/C5,IF(C5=I5,0,(C5-I5)/C5))
F6=IF(C6>I6,(C6-I6)/C6,IF(C6=I6,0,(C6-I6)/C6))
想了解SUMIF、COUNTIF和IF等函数详细用法的可以回看我往期分享的详细教程。
6、用条件格式实现增加比例和向上三角形为红色，减少比例和向下三角形显示为绿色，相同时等号和0显示白色。
① 设置增加显示红色数据：选择E5和F5单元格→【开始】→【条件格式】→【新建规则】→【使用公式确定格式的单元格】→在【为符合此公式的值设置单元格】中输入公式=$C$5>$I$5→点【格式】→【字体】→选择红色，确定即可。
② 设置减少显示绿色数据：同样的方法设置E5和F5单元格当$C$6=$I$6时显示白色，当$C$6<$C$6时显示为绿色。
③ 同样的方法设置E6、F6单元格的条件格式。
7、定义名称，为实现选择不同销售人员显示对应照片做准备。
单击【公式】→【定义名称】→弹出【新建名称】对话框→设置名称为“照片”，范围选择【工作簿】引用位置可选用以下2个公式中的任一公式：
名称【照片】公式为=INDIRECT('业务员照片!B'&MATCH(年报计算!$D$2,业务员照片!$A:$A,0))
名称【照片1】公式为=INDEX(业务员照片!$B:$B,MATCH(年报计算!$D$2,业务员照片!$A:$A,0))
具体如何实现动态查询照片可看我前面分享的教程《Excel动态查询员工基本信息和照片，这样做，轻松搞定！》
8、设置本年每月销售金额和订单公式，制作销售金额柱形图和与上年同比折线图，以及上年订单柱形图和本年订单折线图。
① 本年1月金额公式：D13=IF($D$2='所有人员',SUMIF(数据源!J:J,年报计算!C13,数据源!E:E),SUMIFS(数据源!E:E,数据源!J:J,年报计算!C13,数据源!F:F,年报计算!$D$2))
② 本年1月订单E13=IF($D$2='所有人员',COUNTIF(数据源!J:J,年报计算!C13),COUNTIFS(数据源!J:J,年报计算!C13,数据源!F:F,$D$2))
9、设置排行版公式，制作排行版条形图。
① C55=IF($D$2='所有人员',SUMIF(数据源!C:C,年报计算!B55,数据源!E:E),SUMIFS(数据源!$E:$E,数据源!$C:$C,$B55,数据源!$F:$F,年报计算!$D$2))
② F55=LARGE($C$55:$C$59,ROW(A1))，其中，LARGE(array,k)，返回数据集中第K个最大值。ROW(A1)返回是1，当公式往下填充时依次得出第1、第2个……最大值。
③ 用LOOKUP函数根据F列的数据查找出第1、第2个……最大值对应的商品情况。E55=LOOKUP(1,0/($C$55:$C$59=F55),$B$55:$B$59)
④ 用E55:F59数据插入条形图，并设置好图形的格式和标签等。
⑤ 同样的方法可以制作区域排行版、城市排行版和业务员销售排行版排行版条形图。
⑥ LOOKUP函数的详细教程可关注后回看我前面分享的详细教程，条形图的美化设置可回看我前面分享的图表教程。
第三步：设计年报看板布局。
根据展示要求和美观考虑，设计如下所示的布局。
第四步：根据布局情况，插入组合框窗体控件、照片、相关数据和图表。
1、 插入组合框窗体控件。
点【开发工具】→【插入】→点击【组合框（窗体控件）】→在放置位置划出调节按钮→右键→【设置控件格式】→在弹出的【设置控件格式】对话框中点【控制】→【数据源区域】点选【年报计算】表中的B13:B22单元格→【单元格链接】再点选【年报计算】表的C2单元格。
2、插入大字报数据。
大字报数据用文本框实现，其中显示业务员姓名的文本引用的是【年报计算】表中D2单元格的数据。同样的方法将本年销售额、订单数、单月最高和最低的金额、订单都用文本框实现。与上年对比情况用粘贴为链接的图片实现。
3、 插入照片。
①将所有销售业务员的照片放在一个单独的工作表【业务员照片】，如果照片比较少可以手动插入照片到单元格，如果照片比较多，建议用快速插入方法，具体方法见我前面分享的课程《Excel插入3000名员工一一对应的照片，原来只要2分钟？》
②把光标放在插入照片的单元格位置→点【插入】→图片→任选一个图片插入→调整图片大小与单元格大小→选中图片→在编辑栏输入【=照片1】→回车即可。
第五步：将【年报计算】工作表中已经做好的排行版图复制粘贴到相应位置。
（略）
第六步：调整配色，美化看板。