视频连接:【课程2.0】Excel基础操作|大厂周报制作|常用函数_哔哩哔哩_bilibili
课程主体分为:单元格与基础操作,常用函数,大厂周报制作,数据透视图表,飞机图山峰图,气泡矩阵图,自动化报表搭建。
2.1表格数据的准备和基础知识
2.1.1原始数据的准备
ctrl+shift+l 表格进入筛选模式
GMV(Gross Merchandise Volume):商品总交易额(总成交金额,含已付款和未付款等),电商业常用。
GMV=销售额+取消订单金额+拒收订单金额+退货订单金额,看来只要是流水就能计入
UV(Unique visitor):是指通过互联网访问、浏览这个网页的自然人。访问网站的一台电脑客户端为一个访客。00:00-24:00内相同的客户端只被计算一次。一天内同个访客多次访问仅计算一个UV。
PV(Page View):即页面浏览量或点击量,用户每1次对网站中的每一个网页访问均被记录一个PV,用户对同一页面的多次访问,PV会累计。
CPC(Cost Per Click):每产生一次点击所花费的成本。
2.1.2数据透视表和切片器
数据透视表是Excel中一个强大的数据分析工具,它能够将大量数据进行汇总、分析和比较,帮助用户快速获取所需的信息。而切片器(Slicer)则是数据透视表的一个辅助工具,它允许用户通过图形界面来筛选数据透视表中的数据,从而更直观地查看和分析数据。
以下是数据透视表和切片器的一些基本操作步骤:
-
创建数据透视表:
- 选择包含要分析的数据的单元格区域。
- 转到“插入”选项卡,点击“数据透视表”。
- 选择创建数据透视表的位置,可以是新工作表或现有工作表。
-
设置数据透视表字段:
- 将需要分析的字段拖放到数据透视表的“行标签”、“列标签”、“值”等区域。
-
使用切片器:
- 在数据透视表工具的“分析”选项卡中,点击“插入切片器”。
- 选择你想要用来筛选数据透视表的字段。
- 点击“确定”后,切片器会出现在工作表中。
-
筛选数据:
- 点击切片器中的按钮或下拉列表,选择你想要筛选的数据项。
- 选择后,数据透视表会立即更新,只显示与所选筛选条件相关的数据。
-
管理切片器:
- 你可以通过点击切片器右上角的按钮来管理切片器,比如删除切片器、更改切片器样式等。
-
多切片器联动:
- 如果你添加了多个切片器,它们可以联动工作,即通过一个切片器的筛选会影响到其他切片器的显示。
-
切片器的高级设置:
- 右键点击切片器,选择“切片器设置”可以进行更详细的设置,如更改切片器的标题、排序方式等。
2.1.3日环比和日同比
- 同比=(本期数-同期数)/同期数=本期数/同期数-1
- 环比=(本期数-上期数)/上期数=本期数/上期数-1
2.1.3日期函数
- YEAR(serial_number) YEAR(日期)
- MONTH(serial_number) MONTH(日期)
- DAY(serial_number) DAY(日期)
- DATE(year,month,day) DATE(代表年份的数值,代表月份的数值,代表日份的数值)
- 每个月第一天:date(year(日期),month(日期),1)
- 每个月最后一天:date(year(日期),month(日期)+1,1)-1
2.2常用函数
2.2.1SUM函数-求和
使用格式:SUM(number1,munber2,...)SUM(列名) SUM(行名)
SUM(单个或多个单元格) SUM(列的名称 如:A:A) SUM(行的名称 如:1:1)
2.2.2SUMIF函数-条件求和
使用格式:SUMIF(range,criteria,[sum_range])
SUMIF(条件判断所在的区域,条件,[用来求和的数值区域])
2.2.3SUMIFS函数-多条件求和
使用格式:SUMIF(sum_range,[criteria_range1],[criteria1],[criteria_range2],[criteria2])
SUMIF(用来求和的数值区域,条件1判断所在的区域1,条件1,条件2判断所在的区域2,条件2)
举例:求美团的GMV=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!H:H,"美团",'拌客源数据1-8月'!A:A,">="&DATE(YEAR(B39),MONTH(B39),1),'拌客源数据1-8月'!A:A,"<="&(DATE(YEAR(B39),MONTH(B39)+1,1)-1))
- 像"美团"这样的字符串是文本格式,需要加英文双引号
- 条件参数直接引用单元格或者使用函数则不需要英文双引号
- 然后大于等于等符号也需要添加英文双引号"",并使用&符号才能与后面的条件值相连
2.2.4SUM和subtotal的区别
SUBTOTAL使用格式:SUBTOTAL(function_num,ref1,[ref2],...)
SUBTOTAL(指定函数,选择区域1,[选择区域2],......)
subtotal可以根据原数据的筛选进行求和。
2.2.5IF函数
- IF(logical_test,value_if_true,[value_if_false])
- IF(逻辑比较条件,结果成立时返回的值,[结果不成立时返回的值])
- [value_if_false]:该参数选填,没有该参数时,返回值False