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

Excel基础函数：

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

学习资源：

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

基础函数使用：

sum求和

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

• 知识点

1. 在进行1月和8月GMV这列求和的时候，需要用到冻结窗格，方便观看数据位置
2. ！表示不是同一个sheet
   
   这里的冻结窗格有三种可选项：
   自己随便打开一个Excel的数据文件，进行测试即可
3. 逗号分割选取 函数中拥有多个参数

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)

• 知识点

1. “>=”：任何中文字和数学符合 要引用
2. 条件后的语句也要加&才可以使用⭐">="&DATE(YEAR(B42),MONTH(B42),1)
3. 理解函数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

=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)

• 知识

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

• 多占位符
  

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

和透视表联动

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

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