数据分析3——excel常用函数

本文介绍了Excel中常用的几个功能,包括sum函数的基本用法,如何进行跨工作表求和;sumif和sumifs函数在单条件和多条件求和的应用,以及如何防止拖拽填充时的数据错误;还涉及到同比、环比的计算方法。此外,讨论了subtotal的筛选求和特性,if条件判断的嵌套使用,以及vlookup函数在模糊查询中的应用。最后提到了index和match函数的组合使用,以及在数据聚合和透视表中的作用。
摘要由CSDN通过智能技术生成

一、sum函数

在单元格内先敲=,然后输入sum

 选中要求和的单元格(可以跨工作表,点击试图,新建窗口,可以通过两个窗口进行操作,不用来回换)

敲回车,得到结果 

选不挨着的单元格区域

视图→冻结窗格→冻结首行

选完一部分,敲英文逗号,再选下一部分

二、sumif -单条件求和

如果要七月一日的GMV,则第一个参数是7.1所在区域(如哪一列)

第二个参数是条件,直接选写着2020/7/01的地方

第三个参数是求和区域,选GMV所在列即可

下面可以使用拖拽进行自动填充

 注:为防止拖拽偏移产生的数据错误,可以对条件单元格B15加上$符号

在B前加$,列就不会变了,拖到左边也还是B15

在15前面加$(B$15),行就不会变了,拖到下面还是B15

可以在D15单元格直接写等于C15 下拉和C15下拉效果一样

三、sumifs 多条件求和

公式:SUMIFS(求和区域,区域一,条件一,区域二,条件二…)

注意也用英文逗号隔开,区域可以直接选列,如果条件是中文,要用英文双引号包住

 下拉也可以补全其它条件下的

同比、环比概念:

2020年环比=2020年数据/2019年数据-1

2020年7月环比=7月数据/ 6月数据-1

2020年7月同比=20年7月数据/19年7月数据

计算日环比:

把当天的选中(B30的)/当天前一天的GMV(B30-1的):

 可以改为百分比格式

 日期本质是数字

当天GMV/上周这天-1,即(当天GMV)/(当天-7GMV)-1

当计算年月时,可以使用year、month、day函数

date函数组合,括号里放年月日

计算日同比

不要用excel的日期格式存储日期

当要使用每个月最后一天,不要填准确日期,用下个月第一天减一天即可

公式中用大于等于/小于等于需要双引号,且date前加上&

计算月环比:本月GMV/上个月GMV-1

注意分母是否为0

 

 

四、sum与subtotal的区别

 

 

需要先选上9 ,subtotal可以根据源数据的筛选求和,如只选了美团算的就是美团的

五、if条件判断

if嵌套

 

大于月目标10万且花费少于5千的为达标:

 六、vlookup函数

VLOOKUP(lookup_value,table_array,col_index,index_num,[range_lookup])

VLOOKUP(要查找的数据、要查找的位置和要返回数据的区域、要返回的数据在区域中的列号、返回近似匹配或精确匹配-指示为1/TRUE或0/FALSE)

查找条件必须在第二个参数中选定的第一列!!

 

模糊查询

可以使用&"*"作为通配符

vlookup只会返回查找到的第一个值(如图,第一个a后面有空格,事实上其它a开头也能找到)

 

 查找b开头且是三个字符对应的数值

使用&"??" 表示后面还有两位

聚合(分类汇总)举例

聚合:当多行内容一样 计算对应多个值的求和/平均数等

 

选择值字段设置

 

在数据源选择插入数据透视表

 

通过数据透视表对门店GMV进行匹配 

 

(数据表参数可以选数据透视表,但需要加绝对引用)

在行列之前加上$

七、index和match函数 

match函数:MATCH函数说明MATCH 函数会返回 lookup_array 中匹配值的位置而不是匹配值本身。例如,MATCH("b",{"a","b","c"},0) 会返回 2,即“b”在数组 {"a","b","c"} 中的相对位置

match(lookup_value,lookup_array,[match_type])

match(查找信息,查找区域,0)

index函数:基于位置信息返回特定的值

INDEX(array,row_num,col_num)

index(区域,行号,列号)

 

区域:选源数据全部区域即可,行号:和对应门店名称在同一行(选择区域是一列),列号:和源数据中的门店id是一列(选择区域选一行)

注意数据锁定,所在行的查找信息(一列)不能跟着往左走,所在列的查找信息(一行)不能跟着往下走,否则会查不到

sumifs与index的结合使用:

行位置填0,返回的就是整列,则这一列可以作为sumifs的求和区域

查找对应门店的总GMV

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值