EXCEL实战：统计与分析数据，制作周报

一、分析数据

首先对于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可视化

（一）变化趋势体现

插入——>迷你图中的折线——>选择数据范围与位置范围——>在工具中选择标记

（二）格式颜色设置

开始——>条件格式下拉——>新建规则

补充：分析数据由网上提供