当前位置：首页 > news >正文

数据分析-Excel基础函数的使用

news 2025/7/7 22:50:41

Excel基础函数：

sum:求和
sumif:单条件求和
sumifs:多条件求和
subtotal:根据筛选求和
if:逻辑判断
vlookup:连接匹配数据
match:查找数值在区域中的位置
index:根据区域的位置返回数值
match、index:一起使用：自动根据列名查找数据
sumifs、match、index:一起使用，自动根据列名的数据和条件进行求和

学习资源：

视频地址：htps:/www.bilibili.com/video/BV1ZM4y1u7uF?p=2

基础函数使用：

sum求和

sum求和
在这里插入图片描述

函数方程：可直接复制验证自己结果
=SUM(‘拌客源数据1-8月’!J:J)
=SUM(‘拌客源数据1-8月’!J:J,‘拌客源数据1-8月’!A2:A25,‘拌客源数据1-8月’!A496:A562)

知识点

在进行1月和8月GMV这列求和的时候，需要用到冻结窗格，方便观看数据位置
！表示不是同一个sheet

这里的冻结窗格有三种可选项：
自己随便打开一个Excel的数据文件，进行测试即可
逗号分割选取函数中拥有多个参数

sumif求和

在这里插入图片描述

在这里插入图片描述
函数方程：
=SUMIF(‘拌客源数据1-8月’!A:A,B15,‘拌客源数据1-8月’!J:J)

算出此值后，使用自动填充下拉即可

sumifs多条件求和

在这里插入图片描述

函数方程：
=SUMIFS(‘拌客源数据1-8月’!J:J,‘拌客源数据1-8月’!A:A,B30,‘拌客源数据1-8月’!H:H,“美团”)
即
=SUMIFS(GMV列,日期列,B30,平台i列,“美团”) 美团是自己写的
在这里插入图片描述

算出此值后，使用自动填充下拉即可

环比与同比

年 -------- 只有环比
2020年环比=(2020年数据-2019年数据)/2019年数据
=2020年数据/2019年数据-2019年数据/2019年数据
=2020年数据/2019年数据-1
月
2020年7月环比=2020年7月数据/2020年6月数据-1
2020年7月同比=2020年7月数据/2019年7月数据-1
日
2020年7月1日环比=2020年7月1日数据/2020年6月30日数据-1
2020年7月1日的月同比=2020年7月1日数据/2020年6月1日数据-1
2020年7月1日的周同比=2020年7月1日数据/2020年6月24日数据-1

在这里插入图片描述

日环比

计算公式 ：【当天GMV/前一天GMV】-1函数方程=  C30/SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,B30-1,'拌客源数据1-8月'!H:H,"美团")-1
前一天GMV：SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,B30-1,'拌客源数据1-8月'!H:H,"美团")

当天GMV 已经在上述美团GMV中计算得出 ----> C30
理解B30-1：在Excel中 1 代表1900-1-1 号开始转换之间的关系 B30代表所选单元格
你可以这样操作空白单元格输入1 格式转换为日期
计算完一个数值后使用自动补充即可！

日同比

由概念可知同比包括两种：

月同比本例计算月同比
日同比

计算公式 ：【当天GMV/前一月那天GMV】-1函数方程：     =SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,B30,'拌客源数据1-8月'!H:H,"美团")/SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,DATE(YEAR(B30),MONTH(B30)-1,DAY(B30)),'拌客源数据1-8月'!H:H,"美团")-1
当天GMV：      SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,B30,'拌客源数据1-8月'!H:H,"美团")
前一月那天GMV：SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,DATE(YEAR(B30),MONTH(B30)-1,DAY(B30)),'拌客源数据1-8月'!H:H,"美团")

在前一月那天GMV公式中注意：DATE(YEAR(B30),MONTH(B30)-1,DAY(B30))

知识点
Excel中：1 代表1900-1-1 号开始转换之间的关系

通过日期组合的关系目的是获得月份的表达，方便通过月份进行计算

=DATE(YEAR(B30),MONTH(B30)-1,DAY(B30))

上个月这一天的GMV函数方程
=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,DATE(YEAR(B30),MONTH(B30)-1,DAY(B30)),'拌客源数据1-8月'!H:H,"美团")

sumif求和的也可以用sumifs求和

月环比⭐

月环比计算

本月GMV/上月GMV-1

在这里插入图片描述

解释
由于数据的起始月份是2020/1/1，上月的GMV业绩做分母为0 分母不能为0
本月GMV

=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!H:H,"美团",'拌客源数据1-8月'!A:A,">="&DATE(YEAR(B42),MONTH(B42),1),'拌客源数据1-8月'!A:A,"<="&DATE(YEAR(B42),MONTH(B42)+1,1)-1)

知识点

“>=”：任何中文字和数学符合要引用
条件后的语句也要加&才可以使用⭐">="&DATE(YEAR(B42),MONTH(B42),1)
理解函数DATE的使用，在这里的应用痕迹更明显：MONTH(B42)+1

思维
在计算这个月的范围GMV，如2020/01，目的是寻找 本月第一天到最后一天
第一天：=DATE(YEAR(B42),MONTH(B42),1) 日对应位置置 1 即可
最后一天：由于每个月的天数不同导致并且2月份没有30 和 31天 无法通过定量的加减30/31来进行运算，因此通过下月的第一天再减1运算

错误最后一天：=DATE(YEAR(B42),MONTH(B42),30)
=DATE(YEAR(B43),MONTH(B43),31)
正确最后一天：=DATE(YEAR(B42),MONTH(B42)+1,1)-1

sum和subtotal的区别

=SUM('拌客源数据1-8月'!J:J)

subtotal

=SUBTOTAL(9,'拌客源数据1-8月'!J:J)   9 表示具体的运算方式，与数字一一对应，如还有平均计算等

在这里插入图片描述
subtotal函数需要子函数选择，subtotal函数会根据筛选后的数据进行改变

if函数

在这里插入图片描述

公式

=IF(C67>100000,"达标","不达标")  自动填充即可

嵌套用法

=IF(I83=0,IF(J83=0,"AB=0","A=0,B!=0"),IF(J83=0,"A!=0B=0","A!=0,B!=0"))
不同的位置对应不同的条件：
IF(J83=0,"AB=0","A=0,B!=0")             -----------> A=0
IF(J83=0,"A!=0B=0","A!=0,B!=0")         -----------> A!=0

在这里插入图片描述

vlookup函数

链接数据的函数
在这里插入图片描述

函数方程

=VLOOKUP(B99,'拌客源数据1-8月'!D:E,2,0)
table_array的区域条件必须在第一列 即本例中门店ID在table_array划定的区域里必须是第一列
2：含义代表`门店名称`在table_array选定的区域中的位置第二列   从1开始计数   D:E就两列
0：匹配查找方式     精确或者模糊     0：精确  1：模糊

模糊精确

在这里插入图片描述

查找
普通查找

=VLOOKUP(I99,F98:G106,2,0)

在这里插入图片描述
要查找的区域存在空格

匹配空格

=VLOOKUP(I99&"*",F98:G106,2,0)

知识

任何条件要和&搭配I99&"*"
?一个占位符
*多个占位符但只返回第一次出现的位置

多占位符

=VLOOKUP(I102&"??",F98:G106,2,0)
=VLOOKUP(I102&"?",F99:G106,2,0)
=VLOOKUP(I102&"???",F99:G106,2,0)

和透视表联动

基于聚合运算的结果进行链接
先计算透视表，再进行引用

下一篇张着重介绍index和match函数

Excel基础函数：

学习资源：

基础函数使用：

sum求和

sumif求和

sumifs多条件求和

环比与同比

日环比

日同比

月环比⭐

sum和subtotal的区别

if函数

vlookup函数

模糊 精确

和透视表联动

相关文章：

模糊精确