数据分析-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)

        • 理解函数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函数