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

一、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