一、分析数据
首先对于excel数据集中的数据进行分析,了解每一列数据速所代表的含义。并寻找数据间的关系。以下图数据为例,
(1)GMV:表示网站的成交金额。
(2)cpc:付费广告
(3)曝光人数:网站内的广告让多少用户看见
二、核心函数的实战应用
(一)求和函数
1、sum
用法:sum(number1,number2)
2、sumif
含义:单条件的求和函数
用法:sumif(条件检索区域,条件,求和区域)
例:=SUMIF(A8:A14,A8,B8:B14)
注:区域不能是单独的一个单元格
3、sumifs
含义:多条件求和
用法:sumifs(求和区域,条件检索区域,条件,条件检索区域,条件)
例:=SUMIFS('源数据0701-0816'!J:J,'源数据0701-0816'!A:A,A20,'源数据0701-0816'!H:H,"美团")
4、subtotal
含义:对数据区域进行筛选求和
用法:SUBTOTAL(function_num,ref1,ref2, ...) 其中function_num如下图所示,1-10都有对应的含义,ref1,ref2都是数据区域。
例:=SUBTOTAL(1,B3:B5)
(二)if(条件函数)
含义:对数据进行条件分析,当结果为真是返回某值,条件为假时返回某值。
用法:if(条件判断,结果真,结果假)
例:=IF(B19=0,"zhen","jia")
注:此函数在实际使用中多为条件的嵌套。
(三)查找函数
1、vlookup(纵向查找数据)
含义:按列查找,最终返回该列所需查询序列所对应的值
用法:vlookup(所要寻找的值,要查找的区域和返回值所在列的区域,返回数据在查找区域的第几列数,精确(模糊)匹配)
例:=VLOOKUP(A2,A$27:B$30,2,0)
注:要查找的区域和返回值所在列的区域,在该区域,必须是查找区域在返回值区域前面
2、match函数
含义:返回指定数值在指定数组区域中的位置
用法:match(查找的值,要搜索的单元格区域,精确(模糊)搜索)
例:MATCH(B$12,'源数据0701-0816'!$1:$1,0)
(四)index函数
1、index函数
含义:返回索引值所在单元格的值
用法:=index(要查找的区域,返回值行,返回值列)
例:INDEX('源数据0701-0816'!$A$1:$AJ$144,1,1))
注:当行的值为0时,则最终返回整列内容;列的值为0时,返回整行内容。
三、制作周报
本周报主要包括三个部分,分别是开头,过程指标,结果指标
(一)开头
(1)平台下拉框
选中单元格——>数据——>序列——>将选项输到来源中,并用半角逗号隔开
(二)结果指标
在该版面中,优点:更改列名,其数据也会更改;更改其日期数据,其列明也同样会更改;同时会跟随开头版面的平台的更改二更改。
1、GMV编写代码
=IF($H$5="全部",SUMIF(INDEX('源数据0701-0816'!$A:$AJ,0,MATCH($A$12,'源数据0701-0816'!$1:$1,0)),$A13,INDEX('源数据0701-0816'!$A:$AJ,0,MATCH(C$12,'源数据0701-0816'!$1:$1,0))),SUMIFS(INDEX('源数据0701-0816'!$A:$AJ,0,MATCH(C$12,'源数据0701-0816'!$1:$1,0)),INDEX('源数据0701-0816'!$A:$AJ,0,MATCH($A$12,'源数据0701-0816'!$1:$1,0)),$A13,INDEX('源数据0701-0816'!$A:$AJ,0,MATCH("平台I",'源数据0701-0816'!$1:$1,0)),$H$5))
2、其他列说明
其他列的代码更可以参考以上代码进行编写,与上述类似,不再提供相似代码。
(三)过程指标
优点:更改列名,其数据也会更改;更改其日期数据,其列明也同样会更改;同时会跟随开头版面的平台的更改二更改。
1、曝光人数
=IF($H$5="全部",SUMIF(INDEX('源数据0701-0816'!$A:$AJ,0,MATCH($A$12,'源数据0701-0816'!$1:$1,0)),$A13,INDEX('源数据0701-0816'!$A:$AJ,0,MATCH(C$24,'源数据0701-0816'!$1:$1,0))),SUMIFS(INDEX('源数据0701-0816'!$A:$AJ,0,MATCH(C$24,'源数据0701-0816'!$1:$1,0)),INDEX('源数据0701-0816'!$A:$AJ,0,MATCH($A$12,'源数据0701-0816'!$1:$1,0)),$A13,INDEX('源数据0701-0816'!$A:$AJ,0,MATCH("平台I",'源数据0701-0816'!$1:$1,0)),$H$5))
2、营销占比
=IF($H$5="全部",SUMIF(INDEX('源数据0701-0816'!$A:$AJ,0,MATCH($A$12,'源数据0701-0816'!$1:$1,0)),$A17,INDEX('源数据0701-0816'!$A:$AJ,0,MATCH("cpc总费用",'源数据0701-0816'!$1:$1,0))),SUMIFS(INDEX('源数据0701-0816'!$A:$AJ,0,MATCH("cpc总费用",'源数据0701-0816'!$1:$1,0)),INDEX('源数据0701-0816'!$A:$AJ,0,MATCH($A$12,'源数据0701-0816'!$1:$1,0)),$A17,INDEX('源数据0701-0816'!$A:$AJ,0,MATCH("平台I",'源数据0701-0816'!$1:$1,0)),$H$5))/C17
3、其他列说明
其他列的代码更可以参考以上代码进行编写,与上述类似,不再提供相似代码。
四、excel可视化
(一)变化趋势体现
插入——>迷你图中的折线——>选择数据范围与位置范围——>在工具中选择标记
(二)格式颜色设置
开始——>条件格式下拉——>新建规则
补充:分析数据由网上提供