2022-06-02 excel 零基础到精通

提示：ctrl+F，快速查找文本内容。

Excel电子表格：

数据处理的常用步骤：1、表格搭建 2、数据处理 3、数据分析 4、数据呈现

表格在搭建时要整体规划，完整的记录详细的数据，一般公司的数据收集用到的表格字段有20个甚至更多。需要时有备无患。

做数据处理时要使用一维表格，避免使用二维表格。一维表格与二维表格的区分：如果表格的表头字段是独立的，没有同类项可以合并，这样的表格就是一维表格。如果表头的字段部分的多个字段可以合并为一项，这样的表格就是二维表格。例如，表格1中表头字段只有【科目】这一个，就是一维表格。表格2中表头部分有【语文】【数学】多个字段，他们可以合并为【科目】这一个字段，就是二维表格。

数据格式要规范，表格中不要加空行、空值。表格不能随意进行拆分。

Excel中【快速填充】和【内容重排】功能可以快速进行填充数据。

快速填充：我们给表格一个规律，按ctrl+E进行智能填充。可以利用他快速拆分和快速合并数据。

内容重排：在一个单元格中有多个人名，要把他们改为一列，每行一个名字，就要用到内容重排，先选中单元格调整宽度至一个姓名的宽度，然后点击【开始】---【填充】---【内容重排】，逆操作也可以。

 

【排序】功能的使用：

可以自定义排序

通过辅助列来排序，按ctrl+shift+=，给楼层-房号这样的特殊数值进行排序，10-2,  10-1,  3-3…… 然后用【分列】功能把数据拆分开，楼层成为单独一列，房号成为单独一列，然后用多条件排序来进行，主要关键字为【楼层】，次要关键字为【房间】。

利用排序功能进行【工资条】制作，先复制【序号】列，在最右侧添加一列辅助列，将【序号】列在辅助列中粘贴2遍，然后复制表头，将复制的内容粘贴到下面有序号辅助列的区域，然后选中辅助列进行【升序排序】。

利用【排序】删除空行，面对表格中每一行都含有不同数量的空值，可以利用2个辅助列进行删除空行，在最右侧加2个辅助列1和2，在辅助列1中用【counta】函数，找出每一行非空单元格的数量，其中值为0的行就是空行，然后在辅助列2中添加一个升序的序列，在辅助列1用【降序排序】后所有的空行就都到表格的最后，然后进行删除。删除完成后，对辅助列2进行【升序排序】。辅助类2的作用是，在利用降序排序找出辅助列1中的空行后，表格的内容也随之被打乱，再次利用辅助列2进行一个升序排序找回原来表格的顺序。

利用辅助列可以进行单元格中字符长度的排序，在辅助列中用【LEN()】函数计算出需要排序的【姓名】列的字符个数，再进行排序。

 

【筛选】功能：

点击漏斗图标进入【筛选】模式：

常规筛选：文本、日期、数字

精确筛选：点击表头的下拉按钮，在搜索框中输入。

模糊筛选：通配符 ？ *

要筛选出10多（>=10）和30多（>=30）的内容，第一次点击下拉按钮在搜索框中输入：1？，第二次点击下拉按钮输入：3？ 并勾选上【将当前所选内容添加到筛选器】

利用【高级筛选】进行自定义筛选：自定义条件区域：

1、多个字段是【与】的关系：将对应的条件同行排列。

2、多个字段是【或】的关系：将对应的条件错行排列。

利用【筛选】删除空行：这时不能只点击表格中的任意单元格进入筛选模式，因为有空行的存在，Excel会把它视为多个表格，所以我们要先利用shift键快速选中整个大表格区域，在点击【筛选】进入此模式，然后点击表头上的筛选下拉按钮，找到空行（勾选），此时右键删除。

 

【查找和替换】功能的使用：

精确查找：输入确定的不含有通配符的内容。

模糊查找：通配符：？ *

*表示通配一个或多个任意字符，而一个？表示只通配一个任意字符。

如果要查找*或？本身，就在符号前加一个小波浪号。

按ctrl+H ，用替换功能去除掉单元格中的换行符，先选中区域，然后在【查找内容】中输入alt+1+0 这是换行符的快捷输入方式。

也可以应用替换功能，批量去除掉单元格中的空格。

可以自定义查找和替换的内容，单元格颜色，边框等。

 

【数据验证】在Excel2010之前叫【数据有效性】：

可以对日期，序列，数字，时间，文本长度，进行范围限制和格式限制。也可以自定义公式进行特殊的限制。 

可以自定义【出错信息】和【提示信息】

利用【数据验证】制作下拉菜单，打开【数据验证】对话框，在验证条件中选择【序列】，在【来源】中输入自定义内容：男,女

也可以在【来源】中直接选择数据区域，如果数据区域有新添加进来的内容，如何让下拉菜单中的内容保持一致，先选中数据区域，按 Ctrl+T生成【超级表】，这样在数据区域中新添加的内容也会出现在下拉菜单中。

制作二级下拉菜单：【班级】---【学生姓名】，让各自班级对应各自的学生姓名：

1、定义名称：先选中数据来源区域，利用【定位】---【常量】，将数据区域中有值的单元格全部选中，点击【公式】---【根据所选内容创建】---【首行】，打开【名称管理器】可以看到这一步骤的作用是将表格首行的内容 作为 表格标题进行创建。

2、然后制作 一级下拉菜单，选中班级区域，点击【数据验证】---【序列】，在【来源】中直接框选数据来源表格的表头，也就是各个班级。然后在一级下拉菜单中先填充上内容，这一步很重要。

3、然后制作二级下拉菜单，在【数据验证】【来源】中输入公式：indirect($A2)   ，其中A2单元格是班级列中已经填充的单元格。

制作三级下拉菜单时，步骤同制作二级下拉菜单相同。

 

在做【数据报表】需要注意的问题：

避免在Excel工作簿中出现数据源表，避免出现合并单元格（影响计算），避免使用双层表头，不保留合计行。

原始数据，不对外报送。

数据源表，不对外报送。

计算分析表，不对外报送。

结果报告表，对外报送。

 

单元格中多位数据的输入，前置单引号或改为文本类型。

输入1/2，自动转为1月2日。

常用的【日期格式】：Y,M,D,A  

YYYY-MMMM-DDDD :  2019-January-Sunday

YYY-MMM-DDD : 2019-Jan-Sun

YY-MM-DD : 2019-2-3

AAAA : 星期六

常用的时间格式：H,M,S

h:mm  12:24:00

h:mm:ss　　12:25:14

mm:ss　　10时56分22秒

h"时" mm"分"ss"秒"　　12时25分14秒

 

【自定义格式】的使用：

将序号显示为【0001】【0002】……四位数字的格式：右键【设置单元格格式】---【自定义】在【类型】中输入：0000

给数字加单位并且不影响计算：先选中需要加单位的单元格区域，右键【设置单元格格式】---【自定义】在【类型】中输入：G/通用格式"个"

手机号码使用分段显示：使用自定义格式：000-0000-0000 

给单元格中的文本加前缀或后缀：右键【设置单元格格式】---【自定义】在【类型】中输入："乔丹"@

@: 表示原单元格中的内容。

将单元格的数据隐藏：先选中要隐藏的区域，右键【设置单元格格式】---【自定义】在【类型】中输入：;;;  （英文状态下的3个分号）

快速输入性别：右键【设置单元格格式】---【自定义】在【类型】中输入：[=1]"男"; [=0]"女"

在Excel单元格中输入内容自动添加下划线：利用【单元格格式】，先将需要设置的单元格选中，然后右键【设置单元格格式】--【自定义】，输入：  @*_     

 

【快速定位】：

快速查找表格中2列的不同：先选中，按ctrl+\  ，这是【定位】功能下的一个小的功能，他是有方向性的，他是以先选中的一列作为参照的，将找出的不同显示到另外一列上。面对比较简单的2列数据可以使用定位来找出不同，如果表格比较复杂且无序，我们还可以使用【条件格式】找出不同，或者利用countif函数，或者利用2张表格相减。

利用【定位】可以快速定位出常量，公式（变量），空值，可见单元格。

我们在复制，粘贴Excel表格时，被隐藏的列也会在粘贴后显示出来，如何在复制粘贴时不把隐藏列也粘贴过来：方法1：选中表格，按alt+;  再进行粘贴。  方法2：利用【定位】---【可见单元格】，然后再复制粘贴。

利用【定位】进批量填充：先选中合并单元格区域，【取消合并单元格】，Ctrl+G调出【定位】---【定位条件】---【空值】，然后再编辑栏中输入：=“这个区域的第一个单元格”，再按ctrl+enter

利用【定位】快速删除空行。

 

【分数输入】：单元格中前置o+1/2 ，设置格式。

以8为分母，可输入0.5，或4/8 。

快捷输入符号，特殊符号：<插入>--> <符号>--> <字体>（Wingdings ）

使用公式，Fx=CODE() 获取ASC码 。

使用alt + ASCII 码，例如：alt + 41420 。

冻结窗格：ALT+W --> F + F 。

定位：F5

查找和替换：ctrl+h

excel中上标与下标的输入方法：比如在单元格中 A2 想改为上/下标的形式 A² ，首先选中数字2 ，然后在右键打开设置单元格格式，勾选上上标/下标的选项。使用公式编辑器可以同时设置上下标：<插入>---<对象>---<Microsoft公式3.0>---<上标和下标模板>

按enter 键跳转单元格的方向设置：文件---选项---高级---编辑自定义列表。

enter 键：向下（跳转到下面的一个单元格）

shift+tab键：向左（跳转到左面的一个单元格）

tab键：向右（跳转到右面的一个单元格）

使用记录单功能，文件---选项---自定义功能区---不在功能区中的命令。使用记录单命令需要先把包含表头的数据区域选中。

如果公司的名称太长不方便录入，可以使用自动更正选项，录入一个简称或者拼音首字母就可以更正为公司全名。

文件---高级---校对---自动更正选项---自动更正。

使用数据有效性的命令时需要使用"，" 隔开汉字。

圈释无效数据：数据有效性---圈释无效数据； 使用条件格式---新建规则---使用公式确定要设置格式的单元格。

名称管理器：ctrl+F3 。

批量操作：批量删除表中的计算结果（也就是含有公式的单元格）：选中区域---F5---定位---定位条件---公式---右键---清除。

批量计算公式结果：选中操作区域---F5---定位条件---空值---求和。

批量复制可见单元格：选中要复制的区域---F5---定位条件--可见单元格。

快速定位到表格的最左/右/上/下单元格：ctrl+方向键。

快速选中：ctrl+shift+方向键。

隐藏单元格整行/列：选中---右键---隐藏。

查找：ctrl+F。

替换：ctrl+H。

如果勾选了单元格匹配等于执行精确查找。

使用替换命令，可以换掉公式中的值，先用公式计算出结果，再选中对应的区域，ctrl+H---把公式复制进去再修改等于把公式修改后再应用到对应区域。

把文本状态的数值转换为可用于公式计算的数字格式。

使用分列命令进行批量转换：数据---分列---用符号分列。

处理不规范的日期：使用分列---进入文本分列向导---进入第三步---日期。

不规范的日期格式仅仅是一个文本，规范的日期是数字格式可以进行计算。

删除重复项：选中区域---数据---删除重复项。

录入数据时使用空格虽然比较美观，但是在查找时造成影响。推荐使用文本对齐方式中的分散对齐：ctrl+1---对齐---分散对齐。

当数字和文本同时处于单元格中，但是要求其中的数字可以参与计算：使用自定义格式：设置单元格格式---数字---自定义---G/通用格式+“公斤”。

手机号码使用分段显示：使用自定义格式：000-0000-0000 

快速录入邮箱，使用自定义邮箱的后缀实现快速录入：【设置单元格格式】--【数字】--【自定义】在【类型】中输入@“@qq.com”

在性别中 1 表示男，2 表示女，把1和0批量改为男和女：使用自定义格式：在类型中输入：[=1]"男"; [=0]"女" 。

录入产品的代码加序号，批量快速录入：使用自定义格式，类型中输入：“代码”序号 ， “CSNB”000 。

前缀+中缀+后缀：上海三联化妆品有限公司+地区+分公司，使用自定义格式：类型中输入：上海三联化妆品有限公司@分公司。

分类显示不同成绩格式：使用条件格式中的图标集，管理规则---编辑格式规则。

文件---选项---公式---公式记忆输入。

创建超级表：ctrl+t 。

创建超级表后，再勾选汇总行，再点击【视图】---【拆分单元格】，可以让行首与行尾同时显示。

相对引用与绝对引用：快捷键F4，行相对引用，列相对引用。

使用名称框自定义所选中的区域，定义后可以在公式中引用。

例如：在单元格中输入：=SUM(名称框) ，进行求和。

以选中的区域的值创建名称，公式选项卡---根据所选内容创建---首行，最左列。

对应列的名称以首行名称命名，对应行的名称以最左列的名称命名。

表格中公式错误，自动检查机制：文件---选项---公式---错误检查---允许后台错误检查。在勾选情况下会有错误检查标志。

使用公式：成绩分类：Fx=IF(B2>=90,"优秀",IF(B2>=80,"良好",IF(B2>=60,"及格","不及格")))

从邮箱中提取出名字：MID(LEFT(A2,FIND("@",A2)-1),FIND(".",A2)+1,99) 。

逻辑判断公式：IF(逻辑值，逻辑值为TRUE时的返回结果，逻辑值为FALSE时返回的结果) 

公式提取18位身份证号中提取出生年月日：常规公式：IF(LEN(A2)=18,MID(A2,7,8),MID(A2,7,6)) 

简化公式：MID(A2,7,6+LEN(A2)=18*2)

使用数组公式：ctrl+shift+enter 三键得出结果：{SUM(B2:B6*C2:C6)} 。

不同尺寸的数组运算会得出错误值。

数组的运算类型：1、单值与一个数组。2、同尺寸的行/列数组运算。3、同尺寸的行数组与列数组的运算。4、一维数组与二维数组运算。5、二维数组与二维数组运算。

引用函数：OFFSET(起点 可以是单元格或者区域，行方向的偏移，列方向的偏移，返回引用区域的行数，返回引用区域的列数) 。

日期函数：计算上个月的天数：DAY(TODAY()-DAY(TODAY())) 

计算上个月天数：DAY(DATE(YEAR(NOW)),MONTH(NOW()),0))

判断某一年是平年还是闰年：IF(DAY(DATE(A4,2,29))=29,"闰年"，"平年") 

 

数据的录入：

1、利用专业ocr光学扫描录入软件：ABBYY。

2、利用“简道云”收集表单信息，自动导入到Excel中。

3、在【数据】选项卡下的【自网站】，在弹出的对话框中输入含有表格数据信息的网站链接，Excel会自动分析出网页中包含的表格。

 

数据的输入规范：

1、假如需要统计表格中的男女各有多少人，利用【countif】函数统计，但是在性别字段中含有带空格的男，女，在统计时会造成错误。可以用【查找替换】功能去除掉空格。

2、如果表格中存在“合并单元格”，在用【排序】功能时会产生错误。

 

Excel中的数字与文本格式：
在单元格中，文本格式比较稳定，默认是左对齐的。数字格式比较容易变化，默认是右对齐的。

 

制作【数据透视表】时不符合规范的现象要避免：

1、为了更方便的制作数据透视表，在处理原表时最好加入序号，以确保每一行数据都是唯一的。序号应加入到表格的首字段中。

2、并且在处理原表格时，最好不要有【合并单元格】存在，合并单元格的存在会让表格的任意一个区域在选择时不方便选择。

3、在表格标题行（表头）部分，不能有斜线表头和多个元素复合的表头。要保证表头的单一性和准确性。

4、在表格的末尾不要加入统计项，例如【合计】，这会导致表格在生成数据透视表时出现不必要的错误。

5、相同类别的数据要归纳到同一列中，比如【季度】列中一，二，三季度都把他们归入同一列，而不是把一二三季度分别拆分成不同的列。

6、单元格中该填0就填，空的单元格在计算平均数时不会计入。可以在【Excel选项】--【高级】--【在此工作表的显示选项】--【在具有零值的单元格中显示零】勾选。

7、不能故意空行或者空列，这样系统会将表格当做多个表格处理。

8、在单元格中录入的数据过长，会显示为科学记数法的形式，要将数字文本化，改为【文本】类型。保证日期等数据格式正确。

9、【单元格非原子化】就是一个单元格中存在不同的类型的数据，比如把【省份地区】归到了同一列中，在制作数据透视表前，应该把【省份】和【地区】拆分开，利用Excel的【分列】。

10、表格中的内容不统一，比如在同一列中【QC】与【质量检测】意思相同，但只能用一种表达形式。

 

键盘依次按键：alt-->D-->P  创建数据透视表：

制作数据透视表时，源表的数据要规范，上面已经说明。在选定区域创建数据透视表后，在弹出的【数据透视表字段】对话框中有4个区域：行区域，列区域，筛选区域，值区域。其中行区域和列区域中在拖入字段时要把比较大的字段靠上，比如在行区域依次拖入：【年】【季度】【月份】这几个字段。在行区域和列区域中不允许拖入相同的字段，而在值区域中就可以。在值区域中拖入相同字段后数据透视表会显示出相同的汇总，这时点击值区域中的字段下拉按钮，可以将重复的字段更改为计数、平均值等。

拖入筛选区域的字段不能重复，将字段拖入筛选区域后，在数据透视表的顶端出现可供筛选的下拉菜单。

 

Excel中阻止类似网址的文本转为【超链接】的格式：

【Excel选项】--【校对】--【自动更正】--【键入时自动套用格式】--【internet及网络路径替换为超链接】取消勾选。

在Excel中实现下拉菜单输入，先选中要制作下拉菜单的区域，点击【数据验证】--【序列】，在来源中选择下拉菜单的数据来源。

Excel中 ALT键+向下方向键，快速定位历史输入的内容，实现快速输入。

快速定位单元格中使用公式的内容，快捷键：ctrl+G, 或【定位】功能。定位公式。

输入开头为0 的文本数据：在数字前加入一个英文输入法状态下的单引号，或 设置单元格格式，将类型改为【文本】。

多个0 的输入：例如输入 12000 ，输入时可以输入 12**3 。

快速填充复制单元格的内容，例如不用复制粘贴，将第一行的内容直接复制填充到第二行，可以直接只选中第二行，按ctrl+D 。

ctrl+D是向下填充，ctrl+R 是向右填充。

同组工作表多个工作表快速录入数据：按住ctrl 键点击多个工作表，然后再录入数据，这几个工作表就都同时录入进去了。

快速删除空行：利用【定位】--【空值】，在【开始】--【删除】--【删除工作表行】。

用公式批量提取工作表中身份证号的出生日期：TEXT(MID(A2,7,8),"0000-00-00")

 

【公式】的应用：

在公式中加入【绝对引用】，快捷键按shift+4 ，或 按F4

利用公式可以制作乘法口诀表。

【SUM()】求和函数：

1、多区域求和

2、定位求和：如果表格中有很多空单元格，先用【定位】到空值，再alt+= 

3、一键求和：ALT+=

4、合并单元格求和，需要用到的特殊计算方法：=SUM(D2:D12)-SUM(E3:E13)

5、多表求和：SUM{'一月'！B4,'二月'！B4,'三月'！B4}     ，

如果多个表格是连续的，也可以按住shift键连续选择多个表格，再选择要求和的单元格。用公式表示就是：SUM('一月:八月'！，B4)

6、【数据】--【合并计算】：在合并计算对话框中把多个需要合并的单元格进行添加，选择表格时要注意包含‘标题行’，在合并计算对话框中勾选上首行和最左列。如果首行是标题就需要勾选上【首行】，如果最左列是非数据的名称列，就需要勾选上【最左列】。如果不勾选，Excel会把有数据区域的第一行当成标题行，计算就会出现错误。

7、条件求和：用SUMIF()

多条件求和：SUMIFS()函数。

SUMPRODUCT(条件1*条件2*条件3*求和区域……) ，求和区域不能整列引用。

8、求乘积：SUMPRODUCT(区域1，区域2，区域3……)  ，每个区域大小一致。

9、特殊求和函数：SUBTOTAL()，它可以根据筛选条件的变化，求和项也随之变化。

        SUBTOTAL(限制功能的参数，区域) 

　     限制功能的参数不同的值对应不同的功能（不包含隐藏值）：101：求平均值  102：非空数值计数    103：非空单元格计数    　　　　　　 　　104：最大值　　105：最小值 　　106：乘积　　107：标准偏差　　108：总体标准偏差　　109：求和　　110：方差　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　        111：总体方差

　　  包含隐藏值：1：求平均值     2：非空数值计数      3：非空单元格计数      4：最大值　　5：最小值 　　6：乘积　　7：标准偏           差　　8：总体标准偏差　　9：求和　　10：方差　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　                 11：总体方差

多重嵌套  IF()  函数：1、编辑栏输入法　　2、函数工具输入法

Excel2016版本之后加入了一个新的函数：IFS()  

　　IFS(逻辑1，真值返回1，……逻辑127，真值返回127)

 

【打印】：

相关的设置：【页面布局】和【打印预览】ctrl+P

1、缩放打印，让表格显示到一张纸上。

2、重复标题行，让多页面的表格每页显示标题行。点击【页面布局】---【打印标题行】

3、打印前几行、后几行，先选中需要打印的区域，点击【页面布局】--【打印区域】--【设置打印区域】。也可以在【打印预览】--【设置】--【打印选定区域】。如果按住ctrl键选中前几行和后几行区域，在打印时Excel默认是分页打印的。

4、只打印工作表中的图表，不打印表格，只需要选中图表，按ctrl+p 。

5、表格中有数据信息和图片，如何设置不打印图片，先选中表格中的任意一张图片，再按ctrl+A 就可以选中所有的图片，右键【大小和属性】--【属性】--【打印对象】取消勾选。

6、分页打印：【视图】--【分页预览】，虚线就是页面分隔线。如果想自定义分页，点击鼠标定位到表格外部的空白区域，点击【页面布局】--【分隔符】--【插入分页符】，插入之后可以拖动框线进行调整。 也可以利用【分类汇总】功能进行分页打印，先将鼠标定位到表格内，然后点击【数据】--【分类汇总】，勾选【每组数据分页】。

7、打印页眉页脚：设置公司的logo,文件名，公司名。【页面设置】点击命令启动器--【页眉/页脚】，插入内容。

设置奇偶页不同：【页面设置】点击命令启动器--【页眉/页脚】--【自定义页脚】

8、同时打印多个工作表：按住ctrl键点选，或按住shift键连续选择。

9、多页面的打印，设置【对照打印】或【非对照打印】，对照打印就是页面按照1、 2、 3的顺序打印，非对照打印就是1、 1、 1、          2、 2、 2的方式打印。按ctrl+p进行设置。

10、打印备注：先让所有的备注都显示出来，点击【开始】--【查找和选择】--【选择窗格】点击【全部显示】

 

 

end------------------------------------------------------------------------------------------------------------------