提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
Excel基础操作
符号名称解释:
- GMV=商品交易总额(商品原价值)
- UV vs PV/(去重后的访问人数)
- CPC(cost per click)=每次点击的成本
数据透视表:是一种可以快速汇总、分析大量数据表格的交互式分析工具
数据透视图:数据透视图是数据透视表的另一种视觉形式,他们之间可以相互转换
切片器:筛选器,使数据更加地易于筛选,类似于按钮的功能,可以筛选的功能更加地方便,也可以做一些简单的数据的可视化。
- 切片器的使用:选择数据区域的任意一个单元格,单击【插入】-【切片器】,选择要筛选的字段
- 切片器的筛选方法与取消:
在切片器中一般可以单个筛选一个或者多个符合条件的值,选择一个值的时候只要鼠标单击就行,选择多个值的时候则按按住Ctrl键然后鼠标单击多个值即可,取消筛选的时候单击切片器上的红色的叉号即可 - 切片器的设置方法:
切片器除了使用纵向的以外还可以自定义横向的,也可以改变切片器的大小与样式。选择切片器,然后单击菜单栏里的【设计】按钮,按需要进行设置 - 在数据透视表中使用切片器
除了在超级表中使用切片器,在数据透视表中也可以使用切片器。下图是一个数据透视结果,插入切片器的方法为:单击透视结果中的任意一个单元格,单击【透视表工具选项卡】-【插入切片器】
切片器也可以一次插入多个,其具有联动使用的功能。其他的设置的方法同超级表的切片器是一样的
提示:以下是本篇文章正文内容,下面案例可供参考
一、求和函数sum()
sum(number1,number2,…) sum(行名)sum(列名)
=sum(单个或多个单元格)
=sum(列的名称 如A:A)
=sum(行的名称 如1:1)
注意事项:
原始数据备份并隐藏!
字符串形式储存日期!
打开:视图—冻结窗口
“$”行/列:固定数值
range=一整行/列或区域,criteria =选定的值
在函数的蓝色超链接中寻求帮助
二、条件求和函数sumif()
=sumif(range,criteria,[sum_range])
=sumif(条件判断所在的区域,条件,[用来求和的数值区域])(条件列,条件,目标数值列)
- 同比=(本期数-同期数)/同期数=本期数/同期数-1
- 环比=(本期数-上期数)/上期数=本期数/上期数-1
三、多条件求和函数sumifs()
- =sumifs(sum_range,[criteria_range1],[criteria1],[criteria_range2],[criteria2],…)
- =sumifs(用来求和的数值区域,条件1判断所在区域1,条件1,条件2判断所在区域2,条件2,…)(条件列,条件,目标数值列)
- =sumifs(目标数值列,条件列1,条件1,条件列2,条件2,…)
四、sum()和subtotal()的区别
- subtotal(function_num,ref1,ref2,…)
- subtotal(指定函数,选择区域1,选择区域2,…)
subtotal(含多种函数)【随原数据变化而变化】
五、日期函数
- year函数:year(serial_number) year(日期)
- month函数: month(serial_number) month(日期)
- day函数: day(serial_number) day(日期)
日期问题
一、不同单元格提取日期:=year、=month、=day
二、组合日期:=date(year,month,day)
[年份]:去年=year(日期)-1
[月份]:跨年=month(日期)-1
大小月=edate(“2020-7-31”,-1)
[天数]:每个月的总天数
- 每个月的第一天=date(year(日期),month(日期),1)
- 每个月的最后一天=date(year(日期),month(日期)+1,1)-1(校招考题=下个月第一天减一)
- 每个月的总天数=(时间数列,">=“&DATE(这个月第一天),时间数列,”<=“&DATE(这个月最后一天))
[环比]:(本期数-上期数)/上期数=本期数/上期数-1
连续/相邻时间单位(2020.07vs 2020.06)
= 本期数/上期数-1
- 年环比=年同比=去年=year(日期)-1
- 月环比=上月
没有月份总额:先计算每月月初到月末数值的总和
有月份总额:=EDATE((日期),-1)[比month-1精确]
- 日环比=昨天=日期-1
[同比]:(本期数-同期数)/同期数=本期数/同期数-1
非连续/相同时间单位(2020.07vs 2019.07)
本期数/同期数-1
- 月同比=去年今月=year(日期)-1
- 日同比=上个月的今天=EDATE((日期),-1)
注意:0/false=精确查找
通配符/不限字符:查找项&"*"
占位符/不限字符:查找项&"?"
聚合运算=分类后汇总:插入-数据透视表
- 像“美团”这样的字符串是文本格式,需要加英文双引号
- 条件参数直接引用单元格或者使用函数则不需要英文双引号
- 大于等于符号也需要加英文双引号,并使用&才能与后面的条件值相连
六、if函数
- if(logical_test,value_if_true,[value_if_false])
- if(逻辑比较条件,结果成立时返回的值,[结果不成立时返回的值])
- [value_if_false]:该参数选填,没有该参数时,返回值false
七、vloolkup函数
=vloolkup(查找项,查找项+返回值所在区域,返回值在选定区域的列数,0)
一般返回找到的第一个值
vloolkup(lookup_value,table_array,col_index_num,[range_lookup])
vloolkup(要查找的数据,要查找的位置和要返回的数据的区域,哟啊返回的数据在区域中的列号,返回近似匹配或者精确匹配-提示为1/TRUE或0/FALSE)
八、match、index函数
提示:重难点
1、index(返回值区域,指定行数,指定列数)
返回值区域:可以是一行或一列、还可以同时包含行列
=index(目标区域,行,列)
返回整行=index(目标区域,行,0)
返回整列=index(目标区域,0,列)
2、match(查找值,查找区域,匹配方式)
返回结果为行号或列号
- 查找区域:只能是一列或者一行,不能同时包含行列
- 匹配方式:有3种,通常用0来表示精确匹配
=match(目标数,目标数所在的行/列,0)
注意:
合并单元格情况
时间之间用“-“:2020-03-29
3、index、match嵌套函数(返回值区域,match(),match())
- =index(目标区域,match(目标数,目标数所在的行,0),match(目标数,目标数所在的列,0))
- =sumifs(目标区域/index(全部单元格,0,match(目标数,原数据目标数所在列,0)),条件区域一,条件一)
重难点:大厂周报
九、大厂周报
注意事项:全部链接唯一的原数据
- 到手率 = 商家实收/GMV
- 客单价 = GMV/有效订单
- 进店转化率 = 进店人数/曝光人数
- 下单转化率 =下单人数/进店人数
- 营销占比 = cpc总费用/GMV
- =@index(可能发生隐式交集的地方)
- 业务进度=截止今日这个月GMV总和/目标
注意:营销占比求和、周环比到手率
双击格式刷 = 多个单元格匹配格式
- 数据验证:数据-数据验证-序列-“全部,美团,饿了吗”
- 迷你图:选数据-插入-迷你图-目标位置-标记
【嵌套逻辑】
=if(平台=“全部”,sumifs(GMV,日期列,日期),sumifs(GMV,日期列,日期,平台列,平台))
- 先做最简单的数据嵌套
- 向下/右移动:绝对引用"$"
- 增加灵活度:match+index—替换求和列/日期列/平台列
【表格美化】
- 视图:取消网格线
- 单元格数据渐变:条件格式/新建规则/格式样式/数据条/最小值-数字0,最大值-数字1/渐变填充/颜色
- 单元格正负数据区别:…包含以下内容/单元格值/大于0/绿色 + …小于等于0/红色 + …图标集/数字
- 单元格数据对比:…使用公式去确定单元格, = $C13<=average( $C$13: $C$19)
总结
以上就是今天要讲的内容,本文仅仅简单介绍了常用excel函数的使用。Excel函数是一个强大而功能丰富的工具,可以在各种场景下,帮助我们处理和分析数据,提高工作效率和准确性。无论是在商业领域中的预测和决策分析,还是在教育和研究领域中的数据处理和统计分析,Excel函数都是一个不可或缺的工具。