数据分析师-Excel基础操作

鱼跃渊

于 2023-11-05 17:37:28 发布

阅读量510

点赞数 1

文章标签： excel 数据分析

本文链接：https://blog.csdn.net/qq_58405919/article/details/134218053

版权

一、源数据

得到源数据后，首先需要创建一个新的工作表将源数据进行备份:

CPC(Cost Per Click):每点击一次所产生的成本

CTRL+SHIFT+L:筛选

二、作图

作图功能：插入-数据透视表-默认确定——创建新的工作表

添加计算字段：分析-字段项目和集

插入切片器：用于筛选平台数据

透视表内部也有筛选器，切片器与其的不同点在于：可以联动不同工作表，进行同步筛选（例如使不同工作表呈现统一平台的数据。）

创作图表：点击功能区，分析-数据透视图-选择目标图表(柱状图饼图折线图和组合图)

三、常用函数

1.sum函数

视图-新建窗口——将全部工作表复制出来，不用切屏，方便计算。通过WIN+↑、↓、←、→键可将excel视图铺满整个屏幕。

在单元格中输入"=SUM('拌客源数据1-8月'!J:J)"。其中， "!"表示引用数据非本工作表，其前缀为数据所引用的工作表名称，可以用过空格键或 ") + ENTER"结束运算。

在计算时，会出现由于工作表过窄而无法有效看到字段名称的问题，需要使用“冻结字段”功能——视图-冻结窗口，通常点击B2单元格冻结首行和首列。

计算不同区域单元格，通常使用“,”隔开：

=SUM('拌客源数据1-8月'!J2:J6,'拌客源数据1-8月'!J10:J16)

2.sumif函数——sumif(判断区域,条件,用于求和的数值区域)

通过单元格下拖，快速填充计算所求：

如果想固定一个单元格数值进行计算+快速填充，应使用美元符号：$。(也可以选中目标区域按F4)。例：上下左右拖动时：$B15:列定行变；B$15:列变行定。

拖动时，不同格式转化逻辑：数字1=日期1900-1-1；数字2=1900-1-2，以此类推。例：在C2是日期条件下，当设置C13 =C12时，从右向左拖动，直至C3=C2时，C3显示的数字格式的数字就是引用日期减去1900-1-1的天数。(日期格式下，C3和C2单元格的日期会相同)

3.sumifs函数——sumifs(求和区域,条件判断区域1,条件1,条件判断区域2,条件2)，用于在多个条件下的求和。

四、计算环比；同比

1.概念

日环比：该日数据 / 前一日数据 - 1

月环比：该月数据 / 前一月数据 - 1

日同比：某年某月某日数据 / 去年同月同日数据 - 1

月同比：某年某月数据 / 去年同月数据 - 1

年比：某年数据 / 去年数据 - 1

2.计算

计算同比环比过程中，需要引用日期，在此提出YEAR,MONTH,DAY,DATE四个函数，可以使引用日期更方便。

计算月GMV(数据)时，Excel日期形式不论是"2022""2022-2"都有具体确定的年月日对应，因此计算时间段数据不能单单引用"2022-2"的形式，2022-2对应着2022-2-1或其他日期，应采用如下格式：

注意，条件判断语句要使用双引号""括起来，然后加一个&符号与条件值相联，如上。

计算结果如下：

1月的月环比无去年12月数据，所以会出现0除错误。

四、sum和subtotal函数区别

subtotal可以求平均值，最值，总和等。参数为：subtotal(number,区域)

五、if函数及其嵌套

if函数——if(条件,条件成立返回值,条件不成立返回值)

if嵌套——if(条件1,if(条件2,通过2的返回值,不通过2的返回值),不通过1的返回值)

其中，"if(条件2,通过2的返回值,不通过2的返回值)"为通过1的返回值，当if条件2判断后才会输出结果。

六、vlookup函数和数据透视表聚合

1.vlookup查找函数

函数结构——vlookup(查找数据,查找区域和返回的数据的区域,返回值所在列,1/0)

其中，返回值所在列表示在第二个参数表示的区域中，返回值在该区域的第几列；

1/0表示是否进行精确查找，也可用"true/false"表示。

在模糊查找中，有时会用到通配符"?"和"*"，例如：

两个字符的区别在于，"?"也叫占位符，它表示I99+任意一位数值，"??"表示I99+任意两位数值，以此类推，而"*"表示I99+任意位数值，然而"数值"只能是数字字母，如果是空格则会出现上图报错。注意，在上图中，I99&"*"只会返回第一位值，而非全部值。

2.聚合运算

聚合运算是在数据透视表中，将某一类别的值进行求和。

例如：

计算GMV时，拖动时要固定第二个参数(搜索区域及返回区域)。

七、index和match函数

index(数据区域,行,列)；match(查找项,查找区域,0/1)(0/1表示是否模糊查找)。

两个函数的嵌套用法是：

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

其中，index作用是在某一区域里索引参数1对应的行和参数2对应的列的单元格的内容，当行/列参数为0时，返回整列或整列行；

match是搜索该单元格在某一区域里的第几个——在一行里的第几列；在一列里的第几行。

要注意那些行列不变，加$符号。

接下来，计算GMV，使用sumifs函数和index函数、match函数做嵌套。

复习一下sumfis用法：sumifs(area,条件area1,条件1,条件area2,条件2)

计算GMV时，sumifs(A,B,B')==SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(G$111,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$I:$I,$B112)

A：INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(G$111,'拌客源数据1-8月'!$1:$1,0))——行参数为0表示返回整列，MATCH函数查找G111单元格内字符串在源数据中的列行数。此时只有GMV列被返回；

B：'拌客源数据1-8月'!$I:$I——判断条件所在列；

B'：$B112——在筛选列中查找B112单元格数据。

常用函数总结：

八、报表制作

本文使用外卖案例，平台为美团，饿了么，指标为GMV，商家实收，到手率等。

1.数据验证功能——方便区分美团、饿了么的平台数据。

打开方式：数据-数据验证；

来源填写为：全部,美团,饿了么，字段用英文逗号隔开。

2.求GMV等各种数据

在求GMV时，编码逻辑是：if(判断平台,sumif(日期列,日期,求和列),sumifs(求和列,日期列,判断日期,平台列,判断平台))，而GMV在本文第7章已经求出，所以在求其他条件下的其他指标例如不同日期下的商家实收时，可直接将日期列、求和列、平台列编码编号后直接带入该逻辑代码，这三个数列应使用index和match函数灵活嵌套求出来。日期列：index(源数据,0，match("日期"字符串,首行字段行,0))；求和列：index(源数据,0，match(“商家实收”字符串,首航字段行,0))；平台列：index(源数据,0，match("平台",首行字段行,0))，分别将该三列和相关单元格代入进去即可。