1. 周报常用

数据透视表

基本操作

新建窗口

冻结窗格

冻结首行和首列

锁定$

$B15
B列不变，只变15行号
B$15
行号15不变，只变B列号

日期函数

YEAR(serial_number)
YEAR(日期)
MONTH(serial_number)
MONTH(日期)
DAY(serial_number)
DAY(日期)

N月后的日期
EDATE(start_date,months)
N月后的月底日期
EOMONTH(start_date,months)

常见指标

同比 = (本期数 - 同期数) / 同期数 = 本期数/同期数 - 1

环比 = (本期数 - 上期数) / 上期数 = 本期数/上期数 - 1

1. SUM

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

1	=SUM('拌客源数据1-8月'!J2:J25,'拌客源数据1-8月'!J496:J562)

2. SUMIF

1 2	SUMIF(range,criteria,[sum_range]) SUMIF(条件区域,条件,求和区域)

求2020-7-1的GMV:

(日期列,日期条件,求和列)

3. SUMIFS

1 2	SUMIFS(sum_range,[criteria_range1],[criteria1],[criteria_range2],[criteria2]...) SUMIFS(求和区域,条件1判断区域,条件1，条件2判断区域,条件2...)

求2020-7-1，美团的GMV:

(求和列,日期列,日期条件,平台列,平台条件)

1	=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,B30,'拌客源数据1-8月'!H:H,"美团")

日环比：当天GMV / 前一天GMV - 1

1	=C30/SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,B30-1,'拌客源数据1-8月'!H:H,"美团")-1

日同比：当天GMV / 前一周/月/年那一天的GMV - 1

上一个月的日期：MONTH() - 1

有问题，7月31日组合出来不应该是7月1日

解决：EDATE(start_date,months)

上一个月这天的GMV：

SUMIFS日期判断条件改为上一个月的这一天

日同比：

每个月第一天：date(year(日期),month(日期),1)

每个月最后一天：date(year(日期),month(日期)+1,1) - 1

或者直接EOMONTH()获得最后一天

月度GMV

时间判定条件：>= 某月第一天 AND <= 某月最后一天

SUMIFS中使用条件判断需要将<>=符号加上””,并且如果与条件相连必须要加上&

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

月环比：本月的GMV / 上月的GMV - 1

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

1月出现被零除错误，因为元数据根本没有2020/01之前的数据

4. SUBTOTAL

1 2	SUBTOTAL(function_num,ref1,[ref2],...) SUBTOTAL(指定函数,选择区域1,选择区域2)

SUBTOTAL可以根据筛选变化，SUM会计算隐藏/没有显示的所有数据

5. IF

1 2	IF(logical_test,[value_if_true],[value_if_false]) IF(逻辑条件,条件成立时返回的值,条件不成立时返回的值)

IF嵌套

大于月目标10万且花费少于5千的为“达标”，否则“不达标”

6. VLOOKUP

1
2
3

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
VLOOKUP(要查找的数据,要查找的位置和要返回的数据的区域,要返回的数据在区域中的列号,近似匹配/精确匹配)
(0精确匹配,1近似匹配)

根据门店ID查找门店名称

（门店ID,数据区域,区域中的第N列,精确匹配）

D:E区域

模糊查询

1 2	*:不定数量的字符 ?:一个英文字符

VLOOKUP只返回查找到的第一个，所以只返回a的值1

VLOOKUP联动数据透视表

锁定需要查找的区域(数据透视表)

7. INDEX&MATCH

1 2	MATCH(lookup_value,lookup_array,[match_type]) MATCH(查找项,查找区域，0/1)

1 2	INDEX(array,row_num,column_num) INDEX(区域,行号,列号)

1	INDEX(数据区域,MATCH(行查找项,index数据区域的相对区域,0),MATCH(列查找项,index数据区域的相对区域,0))

自动填充报表

1	=INDEX('拌客源数据1-8月'!$A:$X,MATCH($B112,'拌客源数据1-8月'!$I:$I,0),MATCH(D$111,'拌客源数据1-8月'!$1:$1,0))

INDEX(整个原数据,MATCH找到门店名称对应的行位置，MATCH找到title对应名称的列位置)
'拌客源数据1-8月'!$A:$X
//原数据锁定
$B112
//MATCH不同的门店名称,锁列不锁行，上下变化
$I:$I
//原数据锁定,在门店名称I列里匹配
D$111
//MATCH表头，锁行不锁列，左右变化
$1:$1
//原数据锁定,只在第一行中匹配

特殊情况

GMV,进店人数,下单人数在原表中按天记录，这里需要某个门店的总和

1	=SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(G$111,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$I:$I,$B112)

1	SUMIFS(使用INDEX和MATCH匹配到G$111整列(原表GMV整列),原表平台名称区域(I列),门店名称条件($B112))

倬倬吃三碗

Excel笔记