文章讲述了数据分析中会用到的相关函数的实际应用场景,有需要的朋友可以参考。
一、求和函数
1.sum函数
功能:可以求固定区域、某列、某行的和
题目:数据源为1-8月各门店信息表,计算1-8月月份各门店的GMV
答案
2.subtotal函数
题目:计算1月和8月GMV
答案:先在日期列用筛选键选中1月份和8月份,然后输入函数,第一个函数在9以内,不会忽略隐藏值。
3.sumif
功能:单条件求和,sumif(条件所在列,条件,需要求和的列)
题目:计算/07/01这一天所有门店的GMV值
答案:
4.sumifs
功能:多条件求和(对满足给定多个条件的值求和),sumifs(需要求和的列,条件1所在列,条件1,条件2所在列,条件2,。。。。)
(1)题目:求7月31日这一天美团平台所有门店GMV值总和,两个条件:日期和美团(数据源里面的数据包含了美团和饿了么两个平台)
答案:
(2)题目:计算某个日期的日环比
答案:本期/上期-1:(今天GMV值-昨天GMV值)/昨天GMV值,仅需在日期处-1即可构建公式
(3)题目:计算某个日期的日同比(与上个月同一天进行比较)
答案:本期/同期-1:(本期GMV值-同期GMV)/同期GMV值,仅需在月份处-1即可,此处使用了date函数、YEAR、MONTH、DAY函数。
(4)题目:计算某个月份美团平台的GMV值(提示,条件为大于等于这个月第一天,小于等于这个月最后一天)
答案:每个月最后一天=下个月第0天,大于等于要加"",之后要加&。
(5)题目:计算月环比
答案:本月GMV值/上月GMV值-1,复制上一题公式,在月份对应处-1即可
二、if函数
功能:if(条件,符合条件即输出,不符合条件输出)
1.单个条件
题目:根据GMV判断是否达标
答案:
2.多个条件:if嵌套
题目:GMV大于月目标10万且cpc花费少于5千的为达标
答案:
三、VLOOKUP函数
功能:VLOOKUP(用谁找,在哪找,在哪列,精确/模糊)
题目:根据门店ID匹配对应的门店名称
答案:VLOOKUP(2001104355,源数据区域,门店名称在第二列,0/1)
注意:门店ID必须作为已选区域的首列!!
模糊查找被查找区域需要升序排列。
四、INDEX与MATCH函数搭配使用
返回坐标对应的数值:INDEX(所在区域,行号,列号)
返回坐标(某单元格对应数值在源数据表中行或列的位置)MATCH(某单元格,行,0)MATCH(某单元格,0,列)
1.题目:根据某家店名称找到对应的门店ID(满足这个条件的只有一个值)
采用index嵌套match函数
index(源数据区域,门店ID所在行,门店ID所在列)
门店ID所在行=火锅店所在行=match(火锅店名称,火锅店名称所在列,0)
2.题目:计算这家门店所有的GMV值和
方法1:直接计算
sumifs(求和列,准则列,准则)
方法2:
源数据过多时不方便从源数据看到GMV在哪一列,并且按照方法一不能快速用于其他单元格的计算。需要将SUMIFS的第一个参数区域设定为GMV列区域,求值采用index 函数,
(满足这个条件的有多个值,并且需要求和,先用INDEX函数识别出GMV对应的那一列,再用sumifs对条件值求和)
GMV列区域=index(源数据区域,0,match(GMV,GMV所在行,0))
对GMV列满足条件的行求和:sumifs(index(源数据区域,0,match(GMV,GMV所在行,0)),条件1,条件1所在列)