CRZMASTER的EXCEL学习

EXCEL相关应用与函数的学习

1.1熟悉数据

*拿到数据之后记得先备份数据，右键工作表可以隐藏备份表，再右键可以取消隐藏。

*点击A列和1行，在右下角可以查看数据有几行几列。

1.2基础概念

*在源数据插入——新建数据透视表，可构建数据透视表，

1.双击字段可以修改字段；

2.数据透视表中——分析——字段，项目和集，可以插入计算字段，输入好名称和公式后点击修改即可将其添加入字段中；

3.数据透视表中——分析——插入切片器，切片器可以当做筛选工具，透视表内部也有筛选功能，但切片器可以复制到数据透视表外进行切换，而数据透视表内的筛选功能只能在数据透视表内部工作。用途：在其他工作表中引用数据透视表，可以加入切片器进行切换。

4.数据透视表中——分析——数据透视图，点击某一项数据后可以插入图表，右键图表可以更改图表类型，组合图可以直观体现两个数值，切片器也可以控制。

1.3相关函数学习

1.sum函数

SUM(number1,number2,...)

SUM(点击某行或某列）

SUM(一个或多个单元格）

2.sumif函数

SUMIF(range,criteria,[sum_range])

SUMIF(条件判断的范围，条件，求和项的范围）

e.g：求2020年8月1日GMV和

SUMIF(日期列，8月1日，GMV列）

3.sumifs函数（多条件求和）

SUMIFS(sum_range,[criteria_range1],[criteria1],[criteria_range2],[criteria2]…)

SUMIFS(求和的值的区域,条件1判断所在的区域1,条件1,条件2判断所在的区域2,条件2…)

e.g：求2020年8月1日美团的GMV和

SUMIFS(GMV列，日期列，2020年8月1日，平台列，美团）

4.sum和subtotal的区别

SUBTOTAL(function_num,ref1,[ref2],...)

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

sum对指定区域求和后无法随筛选改变

随便对源数据进行筛选，subtotal都可以跟随筛选进行改变，相当于一个自动根据筛选进行的求和

5.IF函数嵌套

IF(logical_test, value_if_true, [value_if_false])

IF(逻辑比较条件, 为真时返回的值, [为假时返回的值])

[value_if_false]：该参数选填，没有该参数时，返回值False

e.g：判断A,B是否为0的情况

IF(A=0,IF(B=0,"A,B均为0","A为0，B不为0），IF(B=0,"A不为0，B为0","A，B均不为0）)

绿色是A=0为真时，红色是A=0为假时。

6.VLOOKUP函数

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

VLOOKUP (要查找的数据、要查找的位置和要返回的数据的区域、要返回的数据在区域中的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE)

第一个参数：要匹配的数据

第二个参数：我们要告诉Excel从哪里去找我们要匹配的数据

注：第一列必须为要匹配数据所在的列，不然会报错

第三个参数：我们根据匹配数据想得到的数据在所扩选区域的第几列

第四个参数：匹配模式

e.g：我们根据门店id007查找他的店名

VLOOKUP("007",扩选门店id以及店名列，店名列在所选区域的第几列，0）0表示精确匹配

对于VLOOKUP的模糊匹配，需要加上通配符

e.g：查找a开头对应的数值：VLOOKUP("a"&"*",...),加上&英文双引号"*"

        查找b开头共三位对应的数值：VLOOKUP（"b"&"??",...）,??表示两位占数符。

注：若有多个符合匹配，VLOOKUP只会返回第一个符合条件的对应的值。

7.match函数

函数的功能是，在你选定的行或列里查找你给出的数值，然后返回数值在行列内的位置

MATCH(lookup_value, lookup_array, [match_type])

MATCH(查找项, 查找区域,0) 0表示精确查找

match的目的是让Excel找出你想要计算的数值的位置

这样就不需要像vlookup一样手动输入数据在哪一列，match会自己去找

8.index函数

匹配文本数据

让index在源数据中根据match返回的位置找到对应的数据

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

match找到你想要的数据的位置，index负责返回这个位置的数值

匹配聚合运算数据

如果要进行聚合运算的话，只需要和sumifs函数一起使用即可

因为index除了通过行列位置返回一个单元格的数值，还可以返整行和整列

比如，行位置为0，返回整列；列位置为0，返回整行；

这样就可以让match识别所需计算的列所在的位置

然后让index函数返回位置上的整列给sumifs函数用于计算即可

先用index写出计算所用到的列

准备好聚合运算所用到的列，接下来我们将这一列作为sumifs进行计算的列

最后，回车，我们的函数就可以实现基于列名和条件的自动求和啦，随便拖拽都可以计算

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

1.4周报实战练习

1.4.1周报筛选器

1.4.2添加进度条

1.4.3添加箭头指示

1.4.4小于平均值高亮显示

1.4.5美化操作

视图——取消网格线

合并单元格放大居中标题

插入——折线，可以插入迷你图

1.5小操作

按住ctrl可以多选单元格

F4可以锁定

crtl+x 为剪切

视图——新建窗口，win+←→可以将一份表分屏操作

视图——冻结窗口，可以在移动表时候冻结选中行列

DATE（YEAR(),MONTH(),DAY()）日期函数

当月第一天把day写为1，最后一天用下个月第一天-1

1.6 excel方法大全

小技巧：

单元格内换行：alt+enter

锁定标题行：视图->冻结窗格

查找重复值：选定数据区域->开始->条件格式->突出显示单元格规则->条件格式

删除重复值：选定数据区域->数据->删除重复值->选中相关字段

输入✔：选定单元格->按住alt键输入41420->松开alt键

以10000为单位：输入10000->格式设为数字型并复制->选中区域，选择性粘贴->选择除

隐藏0值：文件-选项-高级-在具有0值的单元格显示0

隐藏单元格的值：选中区域-右键改变单元格格式-数字-自定义-类型处输入;;;

以0开头的数字：若想输入00001，先输入'后输入00001即可，若想对数字不足5位左边补满0，右键-设置单元格格式-数字-自定义-00000

以月填充：先往下拖拉一格，再选择以月填充再拖拉

合并多个单元格内容：先调整列宽，再选中内容，开始-填充-内容重排

防止数据重复录入：选取数据列，数据-数据验证-数据验证-设置，自定义-公式：=countif(A:A,A1)=1

公式格式转数值：复制，粘贴-选择性粘贴-数值

小数直接取整:选中数据区域-ctrl+h打开替换窗口，将.*替换为空点击确定即可

快速插入多行：在插入行右下侧停留出十字准星按住shift下拉多少行就插入多少行

快速移动行列：选中行，鼠标移至边缘处出现四角十字，按住并按住shift拖动即可

批量求和：选中数据内容和求和列，按住alt+=键即可

同时查看多窗口：视图-新建窗口-全部重排

同时修改多个sheet的内容：按住shift选中多个sheet，在一个中修改即可

恢复未保存文件：文件-选项-保存-自动恢复文件位置  打开相应的路径即可

给excel加密：文件-信息-保护工作簿-用密码加密

下拉式菜单制作：选取区域-数据-数据验证-数据验证-允许处选择序列-来源处选中相关的菜单内容即可

关闭所有excel文件：按住shift关闭一个即可

删除看不见的换行符：选中区域-ctrl+h打开替换-按住alt输入10替换为空白即可

删除空白行：选取区域-ctrl+g打开定位窗口-定位条件为空值-开始里的删除-删除工作表行

设置表格不可修改：选中可修改区域-ctrl+1打开单元格设置-保护-锁定取消打钩-审阅-保护工作表-默认点击确定

不合并跨单元格居中显示：选中跨的单元格-右键-单元格格式-对齐-水平对齐-跨列居中

批注添加图片：选取单元格-审阅-新建批注-在批注边框右键-设置批注格式-颜色与线条-颜色-填充效果-图片

显示/隐藏所有批注：审阅-显示所有批注

数字不能求和问题：选中数据-点击右上角三角形-转换为数字

调整列最合适宽度：选中列，在任意一列边缘双击

快速复制公式：双击第一个右下角的加号

快速拆分数据：如A-001，选中列-数据-分列-分隔符号点击下一步-选择其他输入‘-‘-完成即可

每页都打印标题：页面布局-打印标题-顶端标题行-选中标题

快速拆分数字：如1000人，在后一格输入1000-数据-快速填充，后后一格输入人-数据-快速填充

记录身份证变0或科学计数法：录入前加上'或者格式改成文本类

快捷键：

ctrl+e：智能拆分

手动拆分一列，其余按ctrl+e自动填充

alt+方向👇：自动生成下拉列表

生成已经输入过的数据列表

shift+f2：快速批注

ctrl+f：快速查找

ctrl+g：快速定位

ctrl+t：快速美化表格 

 ctrl+shift+l：快速筛选

ctrl+a：快速全选

ctrl+h：快速替换   

ctrl+z/y：快速撤销/恢复 

alt+=：快速求和 

ctrl+方向键：快速移动 

ctrl+shift+方向键 ：快速框选 

ctrl+shift+加号：快速插入行/列

ctrl+减号：快速删除行/列

crtl+enter：批量填充 

ctrl+q：快速分析

f2：快速编辑