Task10-Excel看板

最新推荐文章于 2024-05-20 08:30:00 发布

OohMuYi

最新推荐文章于 2024-05-20 08:30:00 发布

阅读量329

点赞数

分类专栏： Free-Excel 文章标签： excel

本文链接：https://blog.csdn.net/OohMuYi/article/details/128848406

版权

9 篇文章 0 订阅

订阅专栏

一分析思路

确定日期所在的周数用：【WEEKNUM函数】
- WEEKNUM（serial_num，return_type）
- 参数Seria_num 必须。代表要确定它位于一年中的几周的特定日期
- 参数Return_type 可选。为一数字，它确定星期计算从哪一天开始，其默认值为1
确定日期是第几周可以用：【WEEKDAY函数】
- WEEKDAY(serial_number，return_type)
- serial_number 是要返回星期数的日期
- return_type为确定返回值类型的数字，数字1 或省略则1 至7 代表星期天到星期六，数字2 则1 至7 代表星期一到星期天，数字3则0至6代表星期一到星期日
- 如果想显示星期几，则=TEXT(WEEKDAY(A1),"aaaa")；如果想将中文直接转换成英文=TEXT(WEEKDAY(A1),"dddd")

请添加图片描述

分析思路：确定动态数据的变量为周数，并确定需要计算的指标，本周开始日期、介绍日期、销售额、订单数、上周销售额、上周订单数、销售额变化、订单数变化

【1】根据周数确定起始日期
方法1：简单方法xlookup函数

在C3单元格输入公式=XLOOKUP(C2,数据源!H1:H50001,数据源!B1:B50001)
在C4单元格输入公式=XLOOKUP(C2,数据源!H1:H50001,数据源!B1:B50001,0,0,-1)

方法2：lookup和VLOOKUP函数
首先把【数据源】里的【周数】一列，移到首列
在C3单元格输入公式=VLOOKUP(C2,数据源!A:C,3,0)
在C4单元格输入公式=LOOKUP(1,0/(数据源!A2:A50001='计算过程-参考'!C2),数据源!C1:C50001)（LOOKUP函数默认为升序，所以默认就会返回最后一个结果为0的值）

【2】根据起始日期计算销售额和订单数

sumifs函数（销售额）
- sumifs（要求和的区域，条件区域1，条件1，条件区域2，条件2）
- 在C5单元格中输入=SUMIFS(数据源!$G:$G,数据源!$C:$C,">="&$C$3,数据源!$C:$C,"<="&$C$4)
- 注意逻辑运算符“>=”、“<=”和单元格引用之间需要用&连接
countifs函数（订单数）
- countifs(条件区域1，条件1，条件区域2，条件2)
- 在C6单元格中=COUNTIFS(数据源!C:C,">="&C3,数据源!C:C,"<="&C4)

【3】根据起始日期计算上周销售额和订单数

上周开始和结束日期，可以使用本周开始结束日期-7，在E3单元格中输入=C3-7，在E4单元格中输入=C4-7
上周销售额，可以使用SUMIFS函数获得，在E5单元格中输入=SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$E$3,数据源!$B:$B,"<="&$E$4)
上周的订单数，可以使用COUNTIFS函数获得，在E6单元格中输入=COUNTIFS(数据源!$B:$B,">="&$E$3,数据源!$B:$B,"<="&$E$4)

【4】销售额与订单数与上周的对比
Text函数（和上周进行比较）
- TEXT(文本内容,转换成的格式)
- 相比上周销售额变化，可以使用百分比计算，在C7单元格中输入=TEXT(((C5-E5)/E5)*100,"0.00")&"%"
- 相比上周订单数变化，在C8单元格中输入=TEXT(((C6-E6)/E6)*100,"0.00")&"%"
突出显示：设置条件格式新建规则
- 选择C6单元格并设置条件格式，判断C5单元格与E5单元格值的大小，如果C5>=E5，那么C6单元值为红，否则为绿色

【1】统计每天下单金额和下单数

B13-B19单元格输入星期数
在C13单元格中输入公式，并向下填充公式=SUMIFS(数据源!$F:$F,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4,数据源!$I:$I,"="&'计算过程'!B13)
在C13单元格中输入公式，并向下填充公式=COUNTIFS(数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4,数据源!$I:$I,"="&'计算过程'!B13)

【2】数据可视化

思路：对于每周的数据需要做一个统计，并对每周数据的渠道分布进行整理
【1】统计每周不同渠道的销售金额和订单数
G3-G6单元格分别输入产品的电商渠道京东、拼多多、淘宝
在H3单元格中输入SUMIFS函数求不同渠道的销售金额，并进行向下填充SUMIFS(数据源!$F:$F,数据源!$E:$E,G3,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)
在I3单元格中输入COUNTIF函数求不同渠道的下单数，并进行向下填充=COUNTIFS(数据源!$E:$E,G3,数据源!$B:$B,">="&$C$3,数据源!$B:$B,"<="&$C$4)

【2】对数据进行可视化展示，将图片进行简单的美化，就可以得到下单金额饼状图与下单数的柱状图