数据分析——Excel篇

1*学习碎片知识点记录：

Ctrl+shift+L  筛选

UV（Unique visitor）：是指通过互联网访问、浏览这个网页的自然人。访问网站的一台电脑客户端为一个访客。00：00-24：00相同的客户端只被计算一次，一天内同个访客多次访问仅计算一个UV。

PV（Page visitor）：即页面浏览量或点击量，用户每一次对网站中的每个网页访问均被记录1个PV，用户对同一个页面的多次访问，PV会累计。

CPC（Cost Per Click）：每产生一次点击所花费的成本

切片器可在数据透视表外的工作表去对数据透视表的内容进行筛选；数据透视表本身的筛选只能在表内部使用。

常用函数

1.sum求和

（对行、列、区域、单元格求和），可跨表求和

视图——>新建窗口——>生成同样一个表的页面——>方便数据查找与计算

视图——>冻结窗口——>冻结首列or首行or冻结任意单元格（从它的上一行、上一列开始冻结，即左上角——>便于查看和选中数据

分区域求和时，用英文逗号隔开。

2.sumif条件求和

sumif（range，criteria，[sum_range]）

sumif（条件判断所在的区域，条件，用来求和的数值区域）

当有多个同样函数求法，可计算一次，后右下角黑十字往下拖拽即可

涉及是否锁定的问题：

例如：我们直接引用B15单元格，输入B15，然后向右拖拽，会变成C15，向下拖拽会变成B16.

若=$B15

在列号前加$，则列不会变；拖动句柄向右，列号不变，为$B15;向下拖动会变成$B16

若=B$15

在行号前加$，则行不会变；拖动句柄向右，列号则变为C$15；拖动向下，行号不变，为B$15

F4可快速锁定or解锁

3.sumifs 多条件求和

sumifs（sum_range,[criteria_range1],[criteria1],[criteria_range2],[criteria2],…）

sumifs（用来求和的数值区域，条件1判断所在的区域，条件1，条件2判断所在的区域，条件2，…）

也可用于求sumif所求的情况

环比与同比

同比=（本期数-同期数）/同期数=本期数/同期数-1

环比=（本期数-上期数）/上期数=本期数/上期数-1

年

2020年环比=（2020年数据-2019年数据）/2019年数据

                   =2020年数据/2019年数据-2019年数据/2019年数据

                   =2020年数据/2019年数据-1

月

2020年7月环比=2020年7月数据/2020年6月数据-1

2020年7月同比=2020年7月数据/2019年7月数据-1

日

2020年7月1日环比=2020年7月1日数据/2020年6月30日数据-1

2020年7月1日同比=2020年7月1日数据/2020年6月1日数据-1

2020年7月1日周同比=2020年7月1日数据/2020年6月24日数据-1

前一天日期=当天日期-1（！笔试常考）

上一周日期=当天日期-7

YEAR（serial_number）             YEAR（日期）

MONTH（serial_number）         MONTH（日期）

DAT（serial_number）               DAY（日期）

DATE（year，month，day）     DATE（代表年份的数值，代表月份的数值，代表日的数值）

永远不要用EXCEL日期格式来存储日期，要以字符串的形式存储

每一个月的最后一天：date（yuear（日期），month（日期）+1，1）-1

相当于下一个月的第一天减去一天      ！校招考试题目 

• EXCEL中有条件时，比较运算符 eg：>=需要用英文双引号引起来，且加&符号+条件
• 像“美团”这样的字符串是文本格式，需要加英文双引号
• 条件参数值直接引用单元格或者使用函数则不需要加英文双引号
• 然后大于等于等符号也需要添加英文双引号，并使用&才能与后面的条件值相连

win+右键——>可自动将表格分屏到右侧，实现两表格同时观看与切换

4.sum与subtotal的区别

SUBTOTAL（function_num,ref1,[ref2],…）

SUBTOTAL（指定函数,选择区域1,[选择区域2],…）

sum是对选中的固定区域求和，subtotal可根据原数据筛选的不同，对不同的数据进行求和，更为零和，sum较为死板一些。

5.if函数

IF（logical_test,value_if_true,[value_if_false]）

IF（逻辑比较条件,结果成立时返回的值,[结果不成立时返回的值]）

[value_if_false]该参数选填，没有该参数则返回false

6.if嵌套

例如：IF（I80=0，IF（J80=0，"AB都等于0"，“A等于0，B不等于0”），IF（J80=0，“A不等于0，B等于0”，“A，B都不等于0”

即IF的后两个参数又分别为一个IF函数

7.vlookup函数 和数据透视表聚合

vlookup（lookup_value,table_array,col_index_num,[range_lookup]

vlookup（要查找的数据，要查找的位置和要返回的数据的区域，要返回的数据在区域中的列号，返回近似匹配或精确匹配，指示为1/True或0/False)

一定是精确的一一对应才会返回，否则报错，改用近似匹配也没用

模糊查询：通配符：  *：代表不定数量的字符；?：英文状态下输入,代替一个字符

例如：=VLOOKUP（I96&"*",F95:G103,2,0）

引用时要注意引用区域是否可变，不可变要用F4锁定，否则报错

8.index和match函数

MATCH（lookup_value,lookup_array,[match_type])

MATCH（查找项，查找区域，0）：可找出某项内容所在区域的行数or列数

INDEX（array，row_num,column_num)

INDEX（区域，行号，列号)：可根据所在区域指定的行号，列号返回对应的值

二者可嵌套使用

例：=INDEX（B111：H126，MATCH（D111，D111：D126，0），MATCH（D111，B111：H111，0））

每一项分别是：区域；D111所在行号；D111所在列号

——>返回D111所对应的单元格内容

index（数据区域，match（行查找项，index数据区域的相对区域，0），match（列查找项，index数据区域的相对区域，0））

可将其用于sumifs函数

sumifs（用来求和的数值区域，条件1判断所在的区域1，条件1）

用来求和的数值区域：可用index和match得出