《21天Excel新手到高手》效率篇：事半功倍的小技巧

这是一篇关于效率的思维导图，《21天Excel新手到高手》效率篇：事半功倍的小技巧 ，提升你的excel处理能力。

21天Excel新手到高手 效率篇：事半功倍的小技巧

1.输入身份证总是乱码怎么办？

1）身份证号码准确录入

原因：excel只能识别15位数据，因此输入后再改变文本格式无法达到目的，同时15位之后的数据均变为0.（注意：这种错误是不可逆的，因此输入数据时要注意先调整单元格格式，再输入数据）

解决方法：选中需要输入身份证号码的区域，设置成文本格式，然后再去输入身份证号码（完成后数据左上角出现绿色小三角，点击出现感叹号，选择忽略错误即可去除小三角）

2）数字格式的样式设置

设置单元格格式（Ctrl+1）

选中需要调整的区域-设置单元格格式，根据需要进行选择小数位数，数字或中文等格式（在特殊中可以修改中文大小写）

3）日期格式的样式设置

选中数据，设置为短日期或长日期

从设置单元格格式中调整

4）自定义格式样式设置

实现不同位数数据对齐

：打开设置单元格格式-自定义-输入几个0就代表几位数

电话号码分段显示

自定义-输入例如“0000-0000-000”

通过代码输入性别

自定义-例如输入[=1]"男";[=0]"女"（相当于if判断，[]为判断条件，之后为判断输入值）

2.收集信息总是出错怎么办？

标准数据录入表要求

特定输入下输入符合要求的数据

一级下拉菜单的制作

先制作每个标题下能填写的数据，制作选择区域

1.选中需要填写数据的区域，找到数据-数据验证（有效性）-序列-来源区域-选择制作的区域，即可再每个单元格通过下拉列表选择输入信息（无论是数字，还是文本，均可选择序列完成选择输入）

2.数据验证-输入信息（或提醒和警告）-勾选选定单元格时显示输入信息-可以输入当选择数据时出现文字（或出错时的提醒警告）

功能

特定大小位数的录入

选中需要填写的区域，找到数据-数据验证（有效性）-设置-整数-选择可输入数据范围

也可设置输入信息以及出错警告等

特定文本长度的录入

选中需要填写的区域，找到数据-数据验证（有效性）-设置-文本长度-设置文本的长度（可用于防止身份证或电话等输入位数错误）

3.批量插入图片如何操作最快？

步骤

（1）设置excel表格属性

对姓名这个区域进行排序，这样excel和文件夹中照片形成一一对照的形式，调整行高和列宽，使其能匹配照片

（2）批量导入图片素材

选中需要插入图片的第一个单元格区域-插入图片-全选图片-插入

（3）姓名与图片区域匹配

-再图片格式中调整图片大小-将第一个图片放置到位-将最后一张也放置到位-同时选中第一张与最后一张进行水平居中对齐-调出定位条件-对象-选中所有图片-图片格式-对齐和分布-左对齐-纵向分布

（4）设置图片大小位置属性

定位条件-定位对象-右键设置图片大小和属性-随单元格改变位置但不改变大小（或选择嵌入单元格也可达到同样效果）

4.重复信息如何快速标记删除筛选及统计

快速标记重复信息

选中需要查看重复的内容后，将鼠标移到单元格区域，右下角出现小图标，单击之后，在格式化中选择 重复的值，即可快速标记出带有重复值的内容（注意：重复项，只有在非数据内之中才会出现，如果选中的位置有数字，日期等数据，则该位置不会出现重复值标记选项）

可以在条件设置-设置突出选项-重复值，实现同样的效果

快速删除重复信息

数据-删除重复值-选择需要作为删除信息标记的列

快速筛选重复值信息

选择数据后，单击数据选项卡排序筛选中的高级选项，勾选在原有区域中显示，列表区域和条件区域均选择数据区域，勾选选择不重复的记录，点击确定（仅是筛选出，并没有删除）

快速统计重复值信息

选择表格任意区域，单击插入下的数据透视表，在弹出的对话框中，直接点击确定，弹出新表后，将学历拖拽到行和值，即可达成统计相同学历人数目的。

5.动动手指轻松搞定数据拆分合并

使用分列功能完成规律数据拆分

按分割符号

选择需要拆分的区域，在数据-分列选项-文本分列向导对话框-选择使用分隔符号-选择分割标志-确认预览无误后点击完成

按固定宽度

选择需要拆分的区域，在数据-分列选项-文本分列向导对话框-选择使用固定宽度分割-在数据预览中用鼠标直接选出分割区域-选择需要导入的列和数据格式-确定导入（如从身份证中提取生日）

使用分列功能完成文本的数据化 (实际上使用的是分列中的数据格式功能)

文本金额转数值

选择不规范数据，使用分列功能直接确定即可

不规范日期转规范

选择不规范数据，使用分列功能-在列数据格式位置设置为日期即可

数据合并

使用&连接符实现数据合并操作

使用shift+7（&键实现），选择需要输入合并数据的单元格，输入等号，点选各个需要的单元格区域，用&连接，即可实现

使用TEXTJOIN函数实现多文本合并

选择需要输入合并数据的单元格，输入等号，输入TEXTJOIN，第一个用英文状态下的双引号输入需要插入的符号，第二个选项选择忽略空单元格，第三个选则需要合并的区域（=TEXTJOIN（"、",TRUE,C3:C11））（注意：此方法只能OFFICE2019以上版本才能使用）

区别：&无法使用分隔符，是直接连成一句，而text join可以加入分隔符

6.用好智能填充从此告别文本拆分函数？

提取数字和字符串

在目标单元格内输入需要提取的部分，在下方单元格按下CTRL+E，即可快速提取整列的信息

提取合并一步实现

在目标单元格内输入需要提取合并的部分，在下方单元格按下CTRL+E，即可快速提取合并整列的信息（例如：张三丰，真人可合并成张真人）

调整字符串的顺序

在目标单元格内输入调整正确顺序的部分，在下方单元格按下CTRL+E，即可快速改变整列中的信息顺序

从身份证提取出生日期

在目标单元格内输入需要提取的部分，在下方单元格按下CTRL+E，即可快速提取整列的信息（即提取数字，故应先提取数字，然后使用分列功能将其转化为日期）

7.Power Query瞬间搞定数据逆透视

一维表和二维表的区别：一维表是单方向即可确定数据，二维表是横纵两个方向共同确定数据

第一步，将数据导入PQ编辑器

选中数据源任意单元格，点击数据-获取和转换数据-自表格区域-点击确定进入PQ编辑器

第二步，逆透视数据列

选择需要作为分类项的列-单击转换-逆透视列

第三步，修改标题及数据类型

修改各项标题名称，把日期，数字等数据从文本改为相应的日期，数字等格式

第四步，将数据上载回Excel

点击主页-关闭并上载

8.Power Query实现数字文本复杂数据处理

第一步，将数据导入PQ编辑器

第二步，拆分信息字段

选择需要拆分数据的列-单击拆分列-按分隔符，清除默认的分隔符，高级选项修改为行，使用特殊字符进行拆分，选择特殊字符，点击确定

第三步，透视列完成数据转换

点击仍需要拆分的字段，进行拆分。

选择应为标题的列，点击菜单栏转换-透视列，在值列选择标题下的数据列，点击高级选项，在聚合函数中选择不要聚合，点击确定，即可实现数据整理

修改各个字段名称和类型

第四步，将数据上载回Excel

优势：数据有增加或减少时，刷新数据即可

9.Word和Excel联合三分钟制作1000张奖状

邮件合并功能

第一步，准备模板文件

将奖状模板（word）和人员信息（excel）放在同一个文件夹中，方便合并邮件时调用

第二步，开始邮件合并

打开“奖状”模板，单击工具栏的邮件（或引用-邮箱），开始文件合并-普通word文档（目的：确保最后制作出来的文档格式）

第三步，导入数据到word

单击选择收件人-使用现有列表-选择已经保存在本机的数据源-选择名单表，预览时看看内容是否正确，然后单击确定即可

第四步，插入合并域

把需要变动的内容删除并插入对应的合并域数据

在需要变动的地方选择插入合并域

第五步，完成并合并文档

单击完成并合并-编辑单个文档-合并全部

预览无误后保存