【数据分析】Excel知识点总结（第一部分）

本文链接：https://blog.csdn.net/m0_46314771/article/details/133611795

Excel学习记录

王佩丰Excel24讲

王佩丰老师24讲的b站视频评论区笔记整理。收藏以方便复习使用。

王佩丰Excel24讲

第1讲：认识Excel

excel能做什么：数据存储、处理、分析和显示。

基础操作：

1. 交换两列数据的位置：选中整列，按shift（鼠标变成小手），拖动到目标列右侧

2. 选中多个工作表：选中第一个，按shift，连续选择多个表格

3. 调整列宽度： 在边框线双击，即可自动调整列宽度。选择多列，再调整列宽度，可以同时一致调整多列宽。

4. 迅速到达表格最后或最前 选中任一单元格，鼠标在下(上)边线变成十字方向标志/小手，双击，即可到达最后(前)。或者Ctrl+ 上下方向键。

5. 冻结窗格

冻结首行：视图-冻结窗格-冻结首行
同时冻结行和列：选中某一单元格，选择冻结拆分窗格格命令，总是冻结当前点选的单元格上面和左面的窗格。

6. 填充柄（鼠标变成实心加号）

按住Ctrl再左键拖拽，可以反转自动填充的逻辑。例如，顺序填充变复制填充。
按右键拖拽，会弹出快捷菜单，提供丰富的填充选项。
自定义序列：选项-高级-编辑自定义列表

7.选择一个范围的多行 在名称框输入起始单元格：目标单元格，如：A2：B900，回车

第2讲：Excel单元格格式设置

1. 合并单元格

多个小单元格合并成一个大单元格，常用于标题。
选中多个单元格，开始>合并后居中
将多行一键按照行合并：选择跨越合并

2. 边框线颜色

注意若不设置，打印不会有边框。
通过设置单元格格式>边框可以单独设置一个或几个单元格。

3. 斜线

设置斜线：设置单元格格式-边框。选择斜线。
填充文字：通过双行文字实现，回车应用软回车（Alt+Enter/option+enter）。若不用软回车，会跳到下一个单元格。然后通过左对齐、空格设置格式。
为什么要应用空格而不能设置左对齐和右对齐？因为对齐是针对整个单元格内的文字而言的，并不能同时设置一行左对齐、另一行右对齐。
如果需要三个斜线，应使用插入>形状>直线，鼠标绘制斜线。文字也可以通过插入文本框编辑。

4. 格式刷

先在一个表格（已经完成格式设置的）应用格式刷，可以刷到另一个新表格，并且应用一样的格式。
双击格式刷按钮，可以保持格式刷状态，直到按下Esc退出。这也可以应用到Word里。要善用格式刷。

5. 设置数字格式

设置单元格格式-数字，选择“数值”，可以选择小数位数，千位分隔符。
日期的本质是数值，微软使用1900计年方法。例如1表示1900/1/1。如果对日期复制粘贴后变成数值，应设置数字格式为日期。
mmm表示英文简写月份，mmmm表示英文月份全称，aaa表示（周）几，aaaa表示星期几。
设置格式，不会改变数值，只是改变显示样式。（化妆）
自定义数字格式，可以实现在所有数值前面或后面加内容，如在数值后加“元”字，并且这些数值还可以作相加等运算（因为设置格式不改变值）。
相加的快捷键是Alt + “=”。(SUM函数)。
文本比较特殊，不能和其他格式自由切换，如果需要切换成其他格式的话可以巧用分列操作，因为分列可以直接设置列的格式。
思考：一列数据中，既有文本数字，又有数值数字，怎么快速地把他们全部转换为数值数字？方法：另起一个单元格，输入数字1，复制，选择性粘贴到目标列中，选择乘法运算，即可强制转换为数值（为了乘1）。

6. 分列

可以使用分隔符号分列，点击下一步可以设置分列之后每列的格式。（非常方便）
注意：文本里的逗号可能是中文逗号，Excel里提到的是英文半角逗号。

第3讲：查找替换定位

1. 查找与替换：按值查找 位置：开始>查找和选择，替换选项卡里有“选项”功能，可以选择“单元格匹配”，避免一些不必要的替换

2. 查找与替换：按格式查找 如：按颜色查找替换

3.模糊查找通配符

快捷键：查找Ctrl+F 替换Ctrl+H
一个 $*$ 号无论几个字的模糊查找，如：张 $*$ 表示张字后全为模糊匹配
英文状态下的?号：一个?号代替一个模糊字，如：张?? 表示两个模糊字
注意：用?号时，常常需要勾选单元格匹配，为了限制字符。
规避通配符的作用，用波浪线，使得通配符失效。

4. 定位：使用名称框

选区域：如，A9000:B10000 用冒号表示“到”，其中 A9000和B10000是所选区域对角线的两个端点。
选行：如，9000:10000，则选中9000行到10000行，无关列
还可以为某个单元格区域自定义名称，以后可以直接使用这个名称查找。通过输入名称，或运用“查找和替换”>“转到”。

5. 定位：定位条件

“查找和替换”>“转到”>“定位条件”或“查找和替换”>“定位条件”。如：可以定位所有有批注/有公式的单元格，定位到的目标会变成蓝色背景。

6. 批注

标志：右上角红色小三角。
插入批注：右键，插入批注。
显示所有批注：在审阅选项卡。
删除所有批注：选中区域，右键删除批注。
在批注中插入图片：设置批注格式-颜色与线条-填充-颜色-填充效果-图片

7. 合并单元格后空白内容处理

先取消单元格合并，定位条件中选择空值，在一个空的单元格内输入=并按下“上方向键”，之后进行软回车（Ctrl+Enter）。就可以实现把合并单元格中的每一行都填入所对应的属性，方便后续统计。
注意：选中一个区域，在一个单元格输入数据，按下软回车（Ctrl+Enter）,则该区域所有单元格都被填上相同的数据。

8. 选中Excel表内的所有图片

定位条件：对象（就是图片！）
通过“选择对象”，就可以实现类似于PowerPoint的框选图片。

第4讲：排序和筛选

1. 简单排序

选中一个单元格，点“排序与选择”中的升序/降序，即可实现整个表格按照该单元格所在列升序/降序的结果。

2.自定义排序

多条件（区分主要和次要标准）的自定义排序第一种：打开自定义排序对话框，添加条件；第二种：分别进行单列排序：先依次排次要标准，再排主要标准。
按颜色排序：排序依据是单元格颜色
中文汉字排序：默认按拼音首字母排序。若要改变，如排序“一二三四五”，那么要选择次序为自定义序列。

2.复制表头——制作工资条 思路，利用数字排序：

复制表头（有几个人的工资条就复制几行）
增加一列数字（工资后面123，表头1.5，2.5，3.5）
对数字列排序（实现了表头和工资条穿插排序）

3.打印要求每页都有表头

页面布局-页面设置-工作表-设置顶端标题行，然后再进行打印。设置后，第一行名称栏会显示Print Titles。

4.筛选

打开筛选开关后，就会出现下拉箭头。
复制筛选后的内容，利用普通复制粘贴，如果出错（旧版本Office），可以尝试定位条件-可见单元格。
同时筛选多列：分别进行筛选，在上一列的筛选结果中再筛选。（5）文本筛选：利用“开头是”“结果是”或通配符。

5.高级筛选

选择不重复的记录，复制到新的列。应用于找出不重复数据的数量。
条件区域：注意：条件区域的写法，以下图片进行了示例，注意输入表头。
“且”的条件写法：写在同一行；“或”的条件写法：错开位置
全选的快捷键：先选择A1单元格，按下键盘的Ctrl/command和Shift，再按下方向键的→和↓。
条件也可以输入公式

第5讲：分类汇总、数据有效性

1. 分类汇总工具

分类汇总：按什么分类，汇总方式，把什么汇总…
使用分类汇总前先排序，把同一类的放在一起（不排序的话就会造成一个类多次统计）
取消分类汇总：数据_分类汇总_全部删除
分类汇总的嵌套：注意还是要排序，二级分类汇总取消替换当前分类汇总方式
复制分类汇总的结果区域 ：定位到可见单元格
使用分类汇总批量合并内容相同的单元格：排序，计数方式分类汇总需要合并项目，定位选中计数列空值_合并单元格_格式刷_刷要合并的项目

2. 设置数据有效性/数据验证

设置整数数据有效性：大于小于介于
设置文本长度数据有效性：文本长度
设置序列数据有效性：序列_选中区域或手动输入

第6讲：认识数据透视表

1.创建数据透视表

选中任意一个单元格，选择【插入-数据透视表-确定】
点中生成的数据透视表，点击右键，选择【数据透视表选项-显示】-勾选【经典数据透视表布局】
根据需要将右侧的字段导入数据透视表中
数据的默认汇总方式是求和，可以双击字段名称更改汇总方式。双击表中汇总后的某个数据可以得到一张新表，查看详细记录。
可以将字段拖入"报表筛选字段"中，相当于根据该字段对透视表做筛选。（用的不多）

2. 数据透视表中的组合

将多个字段拉入数据透视表中的"行字段"中，注意放的左右位置
无需汇总的字段可以双击该字段，将【分类汇总】勾选为【无】。
可以选中分的过于详细的日期，单击右键，选择**【创建组】**，选择步长，对数据进行划分区间的统计。

3.在数据表中使用计算

将多个不同的数据字段拉入"值字段"中，选择计算字段，编辑名称、输入公式。
比率值可单击右键-单元格格式，设置百分号。
-美化有错误值的表格，选中表中任意单元格，点击右键，选择【数据透视表选项-布局和格式】，勾选"对于错误值，显示"。

4.利用筛选字段自动创建工作表

插入数据透视表，分别将字段拖入"报表筛选字段"和"值字段"中。选择【数据透视表工具-选项(分析)-显示报表筛选页-确定】，可根据"报表筛选字段"中的项生成多张工作表。
借助shift键选中所有生成的工作表，复制空白区域覆盖生成的透视表。

第7讲：认识公式与函数

1.认识Excel公式

Excel公式必须以"="开始
"&"连字符，用法:单元格1&单元格2，连接两个单元格的内容
用SUM函数对文本类型的数进行求和运算的结果是0，但可以用"＋"、“–”、“×”、"/"将文本格式的数字进行运算（结果为数值）

2. 比较运算符

“＜＞”:不等于
比较运算的结果一定是逻辑值"TRUE"(运算中当做"1")或者"FALSE"(运算中当做"0")。

3. 单元格的引用

相对引用:A1(引用的是相对位置的单元格)
绝对引用:$ A $ 1 (引用的是固定单元格)
混合引用:$A1 A$1(只有行或列被锁定,既要横向又要纵向拖拽)

4. 常用函数

SUM:对括号内的数值求和
AVERAGE:求平均值
MIN:求最小值
MAX:求最大值
COUNT与COUNTA:计数
RANK:排名(括号内的参数为参与排名的当前数值和所有数值区域，中间用","隔开，排序的区域应该用绝对引用)

5. 利用定位工具选择输入公式的位置

选中目标区域，点击【查找和选择-定位条件-空值】，再点击自动求和工具，实现跳跃式的求和。
选中目标区域，点击【查找和选择-定位条件-空值】，输入公式函数，用"Ctrl+Enter"进行填充。

第8讲：IF函数逻辑判断

1. 使用IF函数

基本用法(只有两种情况时)：=if(条件，如果符合则A，如果不符合则B)
嵌套(有多种情况时)：=if(条件1，如果符合则A，if(条件2，如果符合则B，如果不符合则C))(文本应用"“引用，参数间用逗号”,"隔开)
尽量避免IF函数的嵌套：当IF函数嵌套超过四五层了，应考虑是不是用错了或者改用VLOOKUP函数(以后讲)。
可以并列使用多个IF函数。若打印的值为数值，则"IF1(…)+IF2(…)+…"(若为FALSE打印0)；若为文本，“IF1(…)&IF2(…)&…”(若为FALSE则不打印)。

2. ISERROR函数

判断公式运行是否出错
ISERROR(A):如果运算A出错，则返回TRUE，否则返回FALSE。

3. AND函数与OR函数

AND函数:表示"且"的关系表示需要满足多个条件: AND(条件1,条件2,条件3…)
OR函数:表示"或"的关系表示需要满足至少一个条件: OR(条件1,条件2,条件3…)

第9讲：COUNTIF函数

1. COUNT函数

用于对给定数据集合或者单元格区域中数据的个数进行计数，只能对数字数据进行统计，对于空单元格、逻辑值或者文本数据将被忽略。

2. COUNTIF函数

语法：=COUNTIF(range,criteria) range范围:数据集合、数值区域 criteria标准:数值、文本或条件等。
计算数值区间：计数默认最多只能计算数值的前15位，若需计算超过15位，应在后面加上&“＊”。
找重复：检查A列各数据是否存在于B列中，在C1中输入公式"=COUNTIF(B:B,A1)"，往下拖拽，存在为1，不存在为0。(然后可结合IF公式和条件格式着重标出。)
与数据有效性结合使用，禁止输入重复数据 选中A列，点击【数据-数据有效性(数据验证)】，将有效性条件设置为【自定义】，输入公式"=COUNTIF(A:A,A1)＜2(或=1)"即可。
注意： 1)公式中计数时会把当前单元格计入，所以是"＜2(或=1)" 2)若目标区域不是一整列，要注意使用绝对引用。 3. 条件格式
选中目标区域，选择【开始-条件格式-新建规则】功能，根据需要选择规则类型，再为符合条件的单元格设置特别的格式。（如颜色等）

4.COUNTIFS函数

表示需要满足一个或不止一个条件时:
=COUNTIFS(range1,criteria1,range2,criteria2…)(只有一个条件则和COUNTIF函数一样。)

第10讲： SUMIF函数

1.使用SUMIF函数

语法
=SUMIF(range,criteria,sum_range)
查找符合条件的项并相加 (1)range指查找的单元格区域 (2)criteria指条件 (3)sum_range指相加的范围。
超过15位字符时的错误查找数据时默认最多只能计算数据的前15位，若需计算超过15位，应在后面加上&“＊”。(和COUNTIF一样)

2.第三参数的简写

参数内range与sum_range的范围必须一样大。
SUMIF有很大的自由度和容错率，当输入的sum_range范围较小时，会默认补充至和range一样大，因此可以简写第三参数。
简写第三参数应保证第三参数的第一行要与range的第一行相对应。

3. 在多列中使用SUMIF函数

选中整个表作为参数中的range，仅选择一个或几个数值作为sum_range

4.使用辅助列处理多条件的SUMIF

当需要满足两个或以上条件时，可新增一列作为辅助列，输入公式"=条件1&条件2&…"，拖拽，以此列作为range，以"条件1所在的单元格&条件2所在的单元格&…"作为criteria。

5.SUMIFS函数

语法
=SUMIFS(sum_range,criteria_range1,criteria1【,criteria_range2,criteria2…】)
(1)sum_range:求和区域 (2)criteria_range1,cruteria1:条件区域1,条件1

6.复习数据有效性(数据验证)

用SUMIF函数做产品出库数量的限制(不能大于库存数量)。
在产品列做一个只包含产品名称的下拉序列: 点击【数据-数据有效性】，设置【验证条件】为【序列】，以产品名称作为来源。
在出库数量列设置数据有效性，设置【验证条件】为【自定义】，输入公式=SUMIF(range1,criteria1,sum_range1)<=SUMIF(range2,criteria1,sum_range2)
即同一产品在出库表中的数量总和不能大于它在库存表中的数量。

第11讲： VLOOKUP函数

1.VLOOKUP函数

语法：
=VLOOKUP(lookup_value,table_array,col_index_num【,range_lookup】)
参数： (1)查找(匹配)的值
(2)查找的区域
(3)返回的值在当前区域的第几列
(4)“0"精确匹配"lookup_value”，非"0"近似匹配。
※"table_array"必须包含查找的值和返回的值所在的区域，且以查找的值所在的列作为第一列。(注意使用绝对引用)
※若查找的值在列表中有重复，函数只会返回查找到的第一个记录所对应的值。

2.VLOOKUP函数的跨表引用

在需要接收返回值的单元格内输入公式，当需要跨表引用时，直接点击切换到工作簿内的其他工作表进行选择，在输入完逗号后方可切换回来。

3.VLOOKUP中使用通配符

要查找的数据是列表区域中的部分关键字(参数1与参数2中的值不能完全匹配)，可以使用通配符" $*$ “: =VLOOKUP(lookup_value&” $*$ ", table_array,col_index_num,0)

4.VLOOKUP模糊查找

找到的是小于等于查找值本身的最大值，适用于找数值区间的划分。
模糊匹配用的是"二分法"，在使用时需要查找区域的值从小到大排序。

5.使用ISNA函数处理数字格式引起的错误

VLOOKUP函数不能匹配储存格式不一样的数值。
解决方法：
（1）在函数中将数值转换成以文本形式储存的数值:=VLOOKUP(A1&“”,…)
（2）在函数中将以文本形式储存的数值转换成数值(常规)格式:=VLOOKUP(A1*1(或A1+0、–A1),…)。(即做一次不改变值大小的运算)
（3）当一列数据中文本格式和数值(常规)格式都存在时，将VLOOKUP函数参数1全部转为其中一种格式进行运算，再利用ISNA函数判断是否会出现#N/A错误，结合IF函数，将有格式错误的再转化为另一种格式。
=IF(ISNA(VLOOKUP1()),VLOOKUP2(),VLOOKUP1())
上述方法比较复杂，只是为了探讨函数的可行性。较好的做法还是先将两列转化为相同的储存格式。

6.HLOOKUP函数

用法与VLOOKUP函数基本一样
VLOOKUP函数适用于以一行为一条记录的表格，而HLOOKUP用于以一列为一条记录的表格。

第12讲 MATCH()和INDEX()函数

1.MATCH()函数

语法：=MATCH(lookup_value,lookup_array,【match_type】)，函数返回的是查找的值在所选区域的第几个位置。
参数：（1）需要查找的值（2）在哪列或哪行查找(可以不是一整列或一整行) （3）"0"精确匹配

2. INDEX()函数

语法：=INDEX(array,row_num,【column_num】)，函数返回的是对应单元格的具体值
参数：（1）引用哪一区域（2）引用第几行的值（3）引用第几列的值
将MATCH的返回值作为INDEX的第二个参数: =INDEX(A,MATCH())

3. MATCH+INDEX与VLOOKUP函数的比较

VLOOKUP函数只会查找选中区域的最左列，而且引用列在查找列的右边，不能做从右向左的查找引用。MATCH与INDEX函数的查找和引用是分开进行的，不存在列序的矛盾。
VLOOKUP只能查询返回一个值，不能引用照片，INDEX可以。

4.认识COLUMN函数

语法：=COLUMN()
作用：查询单元格在第几列，若无参数则返回当前单元格在第几列。

5.VLOOKUP函数嵌套返回多列结果

与COLUMN函数嵌套使用
=VLOOKUP(lookup_value,table_array,COLUMN(),0)
与MATCH函数嵌套使用
=VLOOKUP(lookup_value,table_array,MATCH(),0) 通过MATCH函数查找出需要引用的值所在的列号，返回给VLOOKUP函数做第三个参数。 ※在需要进行拖拽时要注意混合引用。

6.INDEX函数引用图片

在工作表中添加图片。
点击【公式-定义名称】，如定义名称为"图片"，在【引用位置】中输入引用图片的函数。
a.在【文件-选项-自定义功能区】中添加【照相机】功能到【新建选项卡】。选中用于接收图片的单元格，点击【新建选项卡-照相机】，画一个框，在编辑栏中输入"=图片"，回车。
b.复制图片，粘贴到用于接收图片的单元格，点中图片，在编辑栏中输入"=图片"，回车。 ※按住alt移动图片可以让照片自动贴近单元格边框。