day10 Excel看板

1. 业务背景

想知道商品在不同渠道的每周销售情况，做一个数据周报看板

2. 实现分析

1. 周报看板：需要确定每天日期所在周数与每周的第几天；
2. 根据周数对销售金额进行汇总；
3. 对销售金额进行动态展示

3.1 确定周数

确定日期所在的周数：weeknum函数

确定日期是第几周:weekday函数

解答：在H2中输入：=WEEKNUM(B2:2)
在I2中输入：=WEEKDAY(B2:11)

如果想得到中文星期几 可以输入 =TEXT(B2,"aaaa")

3.2 销售基础整理

• 解题思路：确定动态数据的变量为周数，并确定需要计算的指标，本周开始日期、结束日期、销售额、订单数、上周销售额、上周订单数、销售额变化、订单数变化

【1】根据周数确定起始日期

• XLOOKUP函数(查找值，查找数组 ，返回数组，[匹配模式]，[搜索模式]）
  • 查找值：必填项
  • 查找数组：要搜索的数组或区域，必填项
  • 返回数组：要返回的数组或区域（必填项）

1. 开始日期：=XLOOKUP(C2,数据源!H1:H50001,数据源!B1:B50001)

结果若是44200，则是格式不正确，只需要将单元格的格式转为日期即可

2. 结束日期：=XLOOKUP(C2,数据源!H1:H50001,数据源!B1:B50001,0,0,-1)

【2】销售额

• sumifs（要求和的区域，条件区域1，条件1，条件区域2，条件2）
  • =SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$C$4,数据源!$B:$B,"<="&$C$5)
  • 数据源!$F:$F：金额区域；数据源!$B:$B：日期；后面两个是条件

逻辑运算符“>=”、“<=”和单元格引用之间需要用&连接

【3】订单数

• COUNTIFS(条件区域1，条件1，条件区域2，条件2)
  • =COUNTIFS(数据源!$B:$B,">="&$C$4,数据源!$B:$B,"<="&$C$5)
    

【4】相比上周销售额、订单数变化

• TEXT(文本内容,转换成的格式)

• 相比上周销售额变化=TEXT(((C6-F6)/F6)*100,"0.00")&"%"
  

• 相比上周订单数变化=TEXT(((C7-F7)/F7)*100,"0.00")&"%"

3.3 周销售数据变化趋势

解题思路：对于每周的每天数据需要做一个统计，并对每周数据的下单金额与下单数进行整理
【1】统计每天下单金额和下单数

• 每天下单金额：=SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$C$4,数据源!$B:$B,"<="&$C$5,数据源!$I:$I,"="&演练计算!B13)

• 每天下单数：=COUNTIFS(数据源!$B:$B,">="&$C$4,数据源!$B:$B,"<="&$C$5,数据源!$I:$I,"="&演练计算!B13)
  

【2】可视化展示
步骤：选择数据区域（不带标题）--> 插入工具栏 --> 折线图

3.4 周销售渠道整理

思路：对每周的数据做一个统计，并对每周数据的渠道分布进行整理
【1】统计下单金额及下单数

• 下单金额：=SUMIFS(数据源!$F:$F,数据源!$E:$E,H4,数据源!$B:$B,">="&$C$4,数据源!$B:$B,"<="&$C$5)

• 下单数：=COUNTIFS(数据源!$E:$E,H4,数据源!$B:$B,">="&$C$4,数据源!$B:$B,"<="&$C$5)
  
  【2】可视化
  步骤：选择数据区域（不带标题）--> 插入工具栏 --> 自己所需要的图
  

3.5 看板绘制

任务：将绘制的图结合在一起进行展示

• 可直接引用过来的信息
  举例：本周销售额：=演练计算!$C$6

本周的变化情况带有条件单元格式，因此不能直接引用，需要将其转化成链接图片的形式

• 步骤如下：复制 --> 黏贴为图片格式
• 对于本周的周数，需要设置按钮方便调节：
  • 步骤：开发工具 --> 插入，选择数值调节按钮