【可略过】一些源数据的说明
这是源数据，下列所有函数所引用的数据来源：
如何快速知道数据的量级
* WPS在左下角看
理解数据
拿到表格之后最好先做筛选（ctrl+shift+L)：
题外话：
在这里店铺重名可能存在关店重开的现象。每次关店重开后都会换一个平台的门店名，以此跟之前的门店名做区分，也可以方便地收集数据。
UV 独立访客数
- 通过互联网访问、浏览这个网页的自然人。访问网站的1台电脑客户端为1个访客。00:00-24:00内相同的客户端只被计算1次。
- 一天内同个访客多次访问仅计算一个UV
PV 页面浏览量
- 页面浏览量或点击量，用户每1次对网站中的每个网页访问均被记录1个PV。用户对同一页面的多次访问，PV会累计
pv是访量可多次计算
uv是一个用户只算一次，已经去重了
实操中有些数据需要去重，因为无论用什么手段获得的数据都可能会有误差，不一定完全正确
【SUM】求和
- 计算某一单元格区域中数字、逻辑值数字的文本表达式之和
- 如果参数中有错误值或为不能转换成数字的文本，将会导致错误
- 语法=SUM(数值1，数值2，...)
如图，是跨表求和的问题
【实战1】求1-8月的GMV
输入函数：
到源数据里选中要引用的数据
返回问题所在表格之后，会看到公式已经自动算出结果
公式=SUM(源1!J:J) 【源1!J:J】工作表“源1”里的GMV列 （【源1】是我自己备份的名称）
在素材里原本的公式 =SUM('拌客源数据1-8月'!J:J)
更方便查看数据、计算引用的方法：
此时就会新建一个窗口（但是不会新建复制一个表格）
窗口分屏查看：win+右键（→），选择要并列的窗口，就可以让两个窗口铺满整个屏幕：
表格太窄，数据太多怎么更轻松地查看数据：
这样首列和首行都冻结了
【实战2】求1月和8月的GMV
输入函数=sum，在并列的窗口中，拖动鼠标分别引用1月和8月的数据，两组数据中间要加英文逗号隔开
*个人经验：这一步不能用筛选来偷懒（即筛选出1月和8月的数据，再整列引用），这个方法得出来的结果是错误的
选完8月数据之后直接按回车键即可得出结果
公式=SUM('拌客源数据1-8月'!J2:J25,'拌客源数据1-8月'!J496:J562)
- 【'拌客源数据1-8月'!J2:J25】工作表“拌客源数据”里1月的GMV数据
- 【'拌客源数据1-8月'!J496:J562】工作表“拌客源数据”里8月的GMV数据
【SUMIF】单条件求和
根据指定条件对若干单元格、区域或引用求和
语法是=SUMIF(条件区域，求和条件，实际求和区域)
- 第一个参数：Range为条件区域，用于条件判断的单元格区域。
- 第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本
- 第三个参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用。当省略第三个参数时，则条件区域就是实际求和区域
【实战3】求7月1日的GMV
* 公式所在单元格太长，选不到该单元格旁边的单元格时，可以先点击单元格的上一格或者下一格，再按上下键选中所需单元格为止
算出第一个结果之后，在其单元格右下角双击，就可以自动将剩下的表格求和
公式=SUMIF(源1!A:A,B15,源1!J:J)
- 【源1!A:A】：备份数据（表名：源1）里A列是日期列
- 【B15】：你要求的这个7月1日，其所在的单元格位置
- 【源1!J:J】：备份数据J列是GMV数据列
素材里原本的公式=SUMIF('拌客源数据1-8月'!A:A,函数完成版!B15,'拌客源数据1-8月'!J:J)
【SUMIFS】多条件求和
语法=sumifs（求和区域，区域1，条件1，区域2，条件2……），没有区域2345也可以
- 求和区域：用于求和的区域
- 区域1：用于条件判断的单元格区域
- 条件1：以数字、表达式、文本定义的条件
【wps学堂拓展】求4个国家的购买量
公式=SUMIFS($C$1:$C$10,$A$1:$A$10,D2)
- 求和区域： C1:C10——因为是求购买量，所以范围只要选择“购买量”这一列的数据
- 区域1：A1:A10——要在“国家”列判断哪个是“中国”，所以选择“国家”这一列的数据
- 条件1：D2——问的是“中国”，所以选择问题里的“中国”
* 上图表格文件来自wps学堂，搜索“sumifs”
【实战4】求7月31日的美团GMV
原本公式应该是=SUMIFS(源1!J:J,源1!A:A,B30,源1!H:H,C29)
但是结果为0
因为匹配的值和列的名称不一样，一个叫“美团GMV”，一个叫“平台i”
在这里可以直接把C29改成【，“美团”】：=SUMIFS(源1!J:J,源1!A:A,B30,源1!H:H,"美团")，算出7月31日的数据
素材里的公式 =SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,函数完成版!B30,'拌客源数据1-8月'!H:H,"美团")
【实战5】求2020.7.1的日环比、日同比
2020.7.1的日环比
=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,函数完成版!B30,'拌客源数据1-8月'!H:H,"美团")/SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,函数完成版!B30-1,'拌客源数据1-8月'!H:H,"美团")-1
- 【主体】SUMIFS/SUMIFS-1
- 【日环比公式】2020年7月1日环比 = 2020年7月1日数据 / 2020年6月30日数据 - 1
- 【GMV列】'拌客源数据1-8月'!J:J
- 【日期列】'拌客源数据1-8月'!A:A
- 【2020/7/1所在单元格】函数完成版!B30
- 【“平台i”列】'拌客源数据1-8月'!H:H
- 【在“平台i”列找出“美团”的数据】"美团"
- 【算出2020.6.30这个日期】B30-1
2020.7.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
- 【主体】SUMIFS/SUMIFS-1
- 【日同比公式】2020年7月1日同比 = 2020年7月1日数据 / 2020年6月1日数据 - 1
- 【GMV列】'拌客源数据1-8月'!J:J
- 【日期列】'拌客源数据1-8月'!A:A
- 【2020/7/1所在单元格】函数完成版!B30
- 【“平台i”列】'拌客源数据1-8月'!H:H
- 【在“平台i”列找出“美团”的数据】"美团"
- 【算出2020.6.1这个日期】DATE(YEAR(B30),MONTH(B30)-1,DAY(B30)）
【实战6】求2020.1的美团GMV
=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!H:H,"美团",'拌客源数据1-8月'!A:A,">="&DATE(YEAR(B39),MONTH(B39),1),'拌客源数据1-8月'!A:A,"<="&(DATE(YEAR(B39),MONTH(B39)+1,1)-1))
【主体】'拌客源数据1-8月'!J:J
【“平台i”列】'拌客源数据1-8月'!H:H
【在“平台i”列找出“美团”】"美团"
【日期列】'拌客源数据1-8月'!A:A
【大于、等于、小于等，要加英文双引号，并使用“&”才能与后面的条件值相连】">="
【连接符，连接后面的函数结果】&
【求某月的第一天】DATE(YEAR(B39),MONTH(B39),1)
【与">="&同理】"<="&
【求某月的最后一天，&后面多加一对括号】(DATE(YEAR(B39),MONTH(B39)+1,1)-1)，计算下个月的第1天，结果再【-1】就是在下个月第1天倒推1天，即所求月日在这个月的最后一天
【实战7】求2020.2的月环比
=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!H:H,"美团",'拌客源数据1-8月'!A:A,">="&DATE(YEAR(B40),MONTH(B40),1),'拌客源数据1-8月'!A:A,"<="&(DATE(YEAR(B40),MONTH(B40)+1,1)-1))/SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!H:H,"美团",'拌客源数据1-8月'!A:A,">="&DATE(YEAR(B40),MONTH(B40)-1,1),'拌客源数据1-8月'!A:A,"<="&(DATE(YEAR(B40),MONTH(B40),1)-1))-1
【主体】SUMIFS/SUMIFS-1
【月环比公式】例如：2020年7月环比 = 2020年7月数据 / 2020年6月数据 - 1
【GMV列】'拌客源数据1-8月'!J:J
【平台i】'拌客源数据1-8月'!H:H
【在平台i找出美团数据】"美团"
【日期】'拌客源数据1-8月'!A:A
【连接符】">="&
【算出来是2020.2.1】DATE(YEAR(B40),MONTH(B40),1)
【算出来是2020.2】(DATE(YEAR(B40),MONTH(B40)+1,1)-1)
【算出来2020.1.1】DATE(YEAR(B40),MONTH(B40)-1,1
【算出来2020.1】(DATE(YEAR(B40),MONTH(B40),1)-1)
【大于等于2020.2.1小于等于2月，实际就是求整个2月份的日期（有时候可能存在2月29日的情况）】">="&DATE(YEAR(B40),MONTH(B40),1),'拌客源数据1-8月'!A:A,"<="&(DATE(YEAR(B40),MONTH(B40)+1,1)-1)
一些日期的计算公式
只提取年 / 月 / 日的数据
- =YEAR / MONTH / DAY (B30)
* 不要用excel的日期格式去存储日期，要用字符串（文本）格式
输入日期时不要改成日期格式，保持数字即可
每个月第一天
- date(year( 日期),month(日期),1)
求这个月最后一天
- ＝下个月第一天-1
求每个月最后一天
- =DATE(年,月+1,1)-1
- =DATE(年,月+1,0)
- =EOMONTH(日期,0)
- =DATE(year( 日期),month(日期)+1,1)-1
求某一天的前一天
直接引用该日期-1
输入公式时需要加英文双引号的情况
| 像"美团"这样的字符串=文本格式 | 要加英文双引号 |
| 条件参数直接引用单元格 / 使用函数 | 不需要加英文双引号 |
| 【大于等于】等符号 | 要加英文双引号，并使用【&】才能与后面的条件值相连 |
环比和同比
环比
- 统计学术语，是表示连续2个统计周期（比如连续两月）内的量的变化比
比如，这个月牛肉价格是24元/斤，上个月价格是20元/斤，那么本月价格环比增长20%
- （24/20）-1 = 1.2-1 = 0.2 = 20%（月环比，公式在下面）
同比
- 第n月与过去某年第n月相比
环比公式
环比 =（本期数-上期数）/上期数 = 本期数/上期数 - 1
年环比
2020年环比 = 2020年数据 / 2019年数据 - 1
= (2020年数据-2019年数据) /2019年数据
=2020年数据/2019年数据-2019年数据/2019年数据
月环比
2020年7月环比 = 2020年7月数据 / 2020年6月数据 - 1
日环比
2020年7月1日环比 = 2020年7月1日数据 / 2020年6月30日数据 - 1
一般来说，环比可以与环比相比较，而不能拿同比与环比相比较；
而对于同一个地方，考虑时间纵向上发展趋势的反映，则往往要把同比与环比放在一起进行对照
- 到手率周环比=本周到手率/上周到手率-1
- 上周到手率=上周商家实收/上周GMV
- 到手率周环比=本周到手率/(上周商家实收 /上周GMV)-1
同比公式
- 月同比
2020年7月同比=2020年7月数据/2019年7月数据-1
- 日同比
2020年7月1日的月同比=2020年7月1日数据/2020年6月1日数据-1
2020年7月1日的周同比=2020年7月1日数据/2020年6月24日数据-1
（好像没查到有日同比，看案例里的公式，似乎就算要计算日同比，也是按（2020年7月1日数据/2020年6月1日数据-1）算
要分清楚环比和同比，分别是和哪个日期比
同比 = (本期数 - 同期数) / 同期数 = 本期数 / 同期数 - 1
环比 = (本期数 - 上期数) / 上期数 = 本期数 / 上期数 - 1
实操中可能会遇到用7月31日数据进行环比同比的情况，理论上6月没有31日应该是比不出的，想精确推导出某天上一个月的第几天，推荐使用edate函数，这个函数可以精准定位月份，加减多少月份都可以
【SUM、SUBTOTAL】的区别
subtotal
- 【特点①】如果在引用1，引用2…… 中有其他的分类汇总（嵌套分类汇总），将忽略这些嵌套分类汇总，以避免重复计算。也就是在数据区域中有SUBTOTAL获得的结果将被忽略
- 【特点②】当函数序号为从 1 到 11 的常数时，SUBTOTAL 函数将包括通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您要分类汇总列表中的隐藏和非隐藏值时，请使用这些常数。
- 当函数序号为从 101 到 111 的常数时，SUBTOTAL 函数将忽略通过“格式”菜单的“行”子菜单下面的“隐藏”命令所隐藏的行中的值。当您只分类汇总列表中的非隐藏数字时，使用这些常数。 但不论使用什么函数序号，SUBTOTAL 函数都会忽略任何不包括在筛选结果中的行。
- 而SUBTOTAL 函数不适用于数据行或水平区域。隐藏某一列不影响分类汇总。但是隐藏分类汇总的垂直区域中的某一行就会对其产生影响。
- 【特点③】SUBTOTAL可以代替上面说的11种函数，当有上面说的两种特点情况时，就可以使用SUBTOTAL来完成。
所以在需要处理隐藏数据相关的应用时，SUBTOTAL是其它函数无法代替的，也是SUBTOTAL最大最重要的特点
sum和subtotal的区别
sum是全部求和，subtotal是将筛选出来的可见部分求和。subtotal函数求取的数值，是会根据原数据筛选状态筛选数值变动而变动的，sum函数求取的数值无论源数据如何筛选变动，所求数值都不会改变的。subtotal函数还支持很多求值方式，好用
【IF】条件判断函数
IF函数
- 如果指定条件的计算结果为 TRUE，IF函数将返回某个值；如果该条件的计算结果为 FALSE，则返回另一个值
- =if（测试条件，真值，假值）。这里的真值，假值都可以用新的公式代替
【实战8】判断某月份的GMV是否大于月目标10万
=IF(C64>100000,"达标","不达标")。公式中的文字要加上双引号
IF嵌套 【实战9】判断某月份GMV大于月目标10万且CPC花费少于5千的为达标
=IF(C80>100000,IF(D80<5000,"达标","不达标"),"不达标")
- 【主体】IF(C80>100000,真值，假值)
- 【真值】在C80>100000且D80＜5000时，返回“达标”，计算结束；若C80>100000但D80＞=5000，返回“不达标”。此时针对C80>100000的判断已经完成
- 【假值】如果C80不符合大于100000的条件，直接返回“不达标”
【拓展】判断AB是否相等
基本逻辑：
=IF(I80=0,IF(J80=0,"AB都等于","A等于B不等于"),IF(J80=0,"A不等于B等于","AB都不等于"))
- 【主体】IF(I80=0,ture,false)——I80=A，即如果A=0，则ture成立，进入到ture里进行判断，否则进行false的返回
- 【ture】IF(J80=0,"AB都等于","A等于B不等于")——首先在认定A=0成立的情况下，判断B是否=0。如果B=0，则返回“AB都等于”，否则返回“A等于B不等于”，此时已经做完了A=0时的判断
- 【false】IF(J80=0,"A不等于B等于","AB都不等于")——上一个if判断完之后，就开始判断A≠0时的情况，此时如果B=0，则返回“A不等于B等于”，否则“AB都不等于”。那么公式到这里已经运算结束
【VLOOKUP】纵向查找函数
=vlookup（查找值，数据表，列序数，匹配条件）
【查找值】需要在数据表第一列中进行查找的值。
- 查找值可以为数值、引用或文本字符串。
- 当vlookup函数第一参数省略查找值时，表示用0查找。
【数据表】需要在其中查找数据的数据表。使用对区域或区域名称的引用。
【列序数】数据表中查找数据的数据列序号。
- 列序数为 1 时，返回数据表第一列的值，
- 列序数为 2 时，返回数据表第二列的值，以此类推。
- 如果列序数小于1，函数 VLOOKUP 返回错误值 #VALUE!；
- 如果列序数大于数据表的列数，函数 VLOOKUP 返回错误值#REF!。
【匹配条件】逻辑值，指明函数 VLOOKUP 查找时是精确匹配，还是近似匹配。
- 如果为FALSE或0，则返回精确匹配，如果找不到，则返回错误值 #N/A。
- 如果匹配条件为TRUE或1，函数 VLOOKUP 将查找近似匹配值
- 如果找不到精确匹配值，则返回小于查找值的最大数值。
- 应注意VLOOKUP函数在进行近似匹配时的查找规则是从第一个数据开始匹配，没有匹配到一样的值，就继续与下一个值进行匹配，直到遇到大于查找值的值，此时返回上一个数据（近似匹配时应对查找值所在列进行升序排列）。
- 如果匹配条件省略，则默认为1，模糊查找
【实战10】通过门店ID查找门店名称
=VLOOKUP(B96,'拌客源数据1-8月'!D:E,2,FALSE)
- 【查找值】B96
- 【数据表，在这里一共2列，分别是门店ID列、门店名列】'拌客源数据1-8月'!D:E
- 【列序数，在这两列中，门店名称列在第2列，所以写2】2
- 【精确查找】FALSE（或者写0）
模糊查找
模糊匹配主要用于数值查询
如图：
查找a对应的值
若输入=VLOOKUP(I96,F96:G103,2,TRUE/FALSE)，返回结果为#N/A，#N/A意思是函数或公式中没有可用数值
原因是因为I96中的值不是单纯的a，而是a和一个空格号
这时候要改成=VLOOKUP(I96&"*",F96:G103,2,TRUE)
【I96&"*"】这里的&是连接文本的符号，*是通配符，代替不定数量的字符（任何字符）。加上*之后意思就是查找a开头对应的全名，然后实际运算结果有4种，但vlookup只会返回它找到的第一个值，所以最后只呈现1种结果
查找b开头并且是三个字符所对应的数值
=VLOOKUP(I99&"??",F95:G103,2,FALSE)
【？】占位符 (英文输入状态下)。代替一个字符
【I99&"??"】b开头且一共3个字符的全名
【INDEX + MATCH】快速查找匹配表格内的数据
match（查找函数）
【匹配类型】为数字 -1、0 或 1。
- 如果匹配类型为 1，查找小于或等于查找值的最大数值
- 如果匹配类型为 0，查找等于查找值的第一个数值
- 如果匹配类型为 -1，查找大于或等于查找值的最小数值。
- 如果省略匹配类型，则假设为 1
【wps学堂拓展】求“香蕉”在第几行
=MATCH(D2,A1:A10,0)
【D2】所求“香蕉”的位置
【A1:A10】“香蕉”只有在A1-A10出现过，所以区域选择A1-A10即可，然后在这个区域中查找“香蕉”在第几行
【0】 精确查找
index
- 返回表或区域中的值或值的引用。
- 它可以返回指定连续区域数组的值，也可以返回非连续区域单元格的引用
- =INDEX(数组, 行序数, 列序数, 区域序数)
【区域序数】非必填。如果要为引用输入非相邻区域, 请将引用括在括号中。如果引用中的每个区域仅包含一行或一列, 则行序数或列序数参数分别是可选的
【wps学堂拓展】求非连续区域的数据
=INDEX((A2:B9,D2:E9),5,2,2)
- 【数组】(A2:B9,D2:E9)
- 【行序数】5
- 【列序数】2
- 【区域序数】 2——在(A2:B9,D2:E9)这2个区域中查找第2个区域
【wps学堂拓展】求A2:B9区域中，第5行、第2列的值
=INDEX(A2:B9,5,2)
- 【数组】A2:B9
- 【行序数】5
- 【列序数】 2
INDEX + MATCH的组合运用
- match是找到具体的行或者列，就是位置。
- index是根据区域内具体的位置范围值。
- match能返回所在行和列，然后再嵌入index就能找到某个数据。也可以理解match是定位好这个位置，然后index把定位完成的数据提取出来
【wps学堂拓展】求香蕉的销量
=INDEX(B1:B10,MATCH(D2,A1:A10,0)) ——在B1:B10中查找第8行里香蕉的销量
- 【数组】B1:B10
- 【行序数】MATCH(D2,A1:A10,0)——返回行序数（即香蕉在第8行）
【实战11】基于“平台门店名称”找出对应的门店ID
=INDEX('拌客源数据1-8月'!$A:$I,MATCH($B112,'拌客源数据1-8月'!$I:$I,0),MATCH(D$111,'拌客源数据1-8月'!$A$1:$I$1,0))
①【index函数里的数组参数】'拌客源数据1-8月'!$A:$I
为什么只引用到S列为止：
表格最后要求的是下单人数，而源数据里的下单人数就截止到S列，如下图
②【index函数里的行序数，算出来为2】MATCH($B112,'拌客源数据1-8月'!$I:$I,0)
- 【查找值】$B112
- 【查找区域】'拌客源数据1-8月'!$I:$I
- 【匹配类型】0
③【index函数里的列序数，算出来为4】MATCH(D$111,'拌客源数据1-8月'!$A$1:$I$1,0)
用来查找的表头和列名要使用混合引用，不能全部锁死。如果要拖拉函数，index的数据区域记得锁定，行match函数里，列要锁定，列match函数里，行要锁定
总结就是在“拌客源数据1-8月”这个工作表中，在A列-S列这个范围里找到第2行第4列的数据
此时，通过index和match嵌套函数能一下子算出门店id、品牌名称、品牌id、门店名称：
【实战12】通过某门店ID求该门店的GMV （联合SUMIFS）
=SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH('常用函数-完成版'!H$111,'拌客源数据1-8月'!$A$1:$X$1,0)),'拌客源数据1-8月'!$I:$I,'常用函数-完成版'!$B112)
①【主体】=SUMIFS(INDEX+MATCH，'拌客源数据1-8月'!$I:$I，'常用函数-完成版'!$B112）
- 【求和区域】INDEX+MATCH
- 【区域1，平台门店名称列（I列，在第9列）】'拌客源数据1-8月'!$I:$I
- 【条件1，求的是B112的GMV】'常用函数-完成版'!$B112
②【match确定行和列，index根据行和列，定位查找数值】
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(函数完成版!H$111,'拌客源数据1-8月'!$A$1:$X$1,0))
——在源数据的A-X列中，在第10列（GMV列）里找到所有关于B112（蛙小辣·美蛙火锅杯(宝山店)）的GMV数据
- 【数组】'拌客源数据1-8月'!$A:$X
- 【行序数】0——因为只需要列的数据，相当于没有具体位置，所以行序数写0就行，结果返回整列。返回整列可以让match识别该表头在源数据的第几列，把该列返回来，就可以用于sumifs的计算
- 【列序数，算出位置在第10列 】MATCH(函数完成版!H$111,'拌客源数据1-8月'!$A$1:$X$1,0)——H111是（表头）GMV所在的位置，要通过这个表头，在源数据中找到写着GMV是在哪一列
③【找出H111（GMV，是表头）在源数据A1-X1（只需引用表头行）的第几列，结果算得10】
MATCH('常用函数-完成版'!H$111,'拌客源数据1-8月'!$A$1:$X$1,0)
——算出GMV在源数据的位置是第1行第10列
- 【查找值】'常用函数-完成版'!H$111——要查找H111（GMV）在源数据的位置
- 【查找区域】'拌客源数据1-8月'!$A$1:$X$1——在A1-X1里查找
- 【匹配类型】0——查找等于查找值的第一个数值
1595
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
