Excel高阶操作（长期更新）

1. 单元格格式问题

（1）常规操作

选中表格区域，右击并点击设置单元格格式。

（2）自定义格式

比如如下的一长串数字编号，从其他软件中导出后经常是以科学计数法表示的文本。此时想要将其变为正常的文本类型的编号，需要进行如下操作：
①首先选中表格区域，右击并点击设置单元格格式，选择自定义，在自定义格式框中输入“00000000”（和表格中需要显示的编号位数一致）。

结果如下图所示。

②一般性的需求上一步的结果已经能满足，但是一些特殊需要还需要将该列数据变为正常的文本类型的编号。还需要在表格后添加一列，在首个数据行单元格中输入以下公式：

下拉后结果如下：

③将新的数据列复制，并以“仅粘贴值”的方式粘贴到任意位置，再从该位置复制此数据列覆盖元数据，并删除多余数据。

（3）编号补零

参照自定义单元格格式的操作

（4）长串数字导入excel变科学计数或末尾变0

比如将身份证号导入进 excel，一半会变成科学计数法或者超过15位的末尾变0。这种情况可以在数字前加单引号 ’ ，或者连同单引号 ’ 和数字一起写入excel，即可避免此问题。

（5）大量单元格出现黄色感叹号警告

可先将表格数据复制到文本文档中，再从本文中粘贴到表格中，即可去除感叹号，改变数据格式。

2. 取单元格内容的部分字符串

（1）左取

（2）右取

（3）中间取

（4）拼接单元格内容

3.单元格内容的值直接保留小数点（非单元格格式中更改）

4.表格判断

下列公示的意思是：mod(b2,2),取b2的值除以2的余数，若该余数等于0，则该单元格值为1，否则为0.

5.编辑筛选的结果

（1）编辑可见单元格

下图是对表格中h2列筛选的结果。

一些版本的excel中，若直接复制筛选的数据，会将中间隐藏的数据复制过来，因此需要点击“定位条件”，勾选“可见单元格”，再复制，注意每选中一次数据。“可见单元格”的状态都会重置，因此建议先选中想要复制的筛选结果，再操作“定位条件”，然后直接ctrl+c。

（2）将数据粘贴到筛选结果

若想以其他值统一粘贴过来替换筛选出的结果，直接复制粘贴会改变其他隐藏起来的数据值，如下图所示。

这里需要用到2个辅助列，第一个辅助列h3用来对原始数据排序，用来在复制后恢复表格原来顺序，第二个辅助列h4用来区分筛选结果和其他数据。其中h4列可以在筛选后直接下拉填充。
h3的填充如下（快速填充：前2个单元格分别填1和2，然后选中这2个单元格，下拉或双击单元格右下角自动填充）

h4 在筛选后赋值

展开后，h4的值正常

表格展开后对h4排序

再将其他的数据列复制过来替换掉此时排序结果中h2的值

最后对h3排序，恢复原始数据的顺序，并删除多余的数据

6.标记重复项

excel中不仅有删除重复项的功能，还有标记重复值的功能。
原始数据如下。

要想标记出重复的值而非直接删除，需要选择该数据列，在“开始”工具栏中找到“条件格式”，点击“突出显示单元格规则”，选择“重复值”。

进一步选择重复值标记颜色，点击“确定”。

标记重复值的结果如下。WPS的操作与此类似。也可以利用此功能标记大于、小于、等于某个值的数据项。

7. 批量修改表单格式

利用excel的格式刷功能批量修改多个表单的打印格式。
首先人工设置好一个表单的格式，如下图所示。

待格式化的表单内容样式如下图所示。

在人工设置好格式的表单中左上角全选表单内容，双击“开始”菜单中的“格式刷”按钮。

进入到待格式化的表单，用双击格式刷后的鼠标状态单击其表单左上角。如下图红圈处的位置。

上一步结果如下图所示。

最后再微调表格边框。

8.表格内容匹配

已知表格中两列数据A和B，排查A列数据是否在B列中出现，使用countif函数，如下图所示：

当表格中是很长的数字时，比如身份证号等，直接匹配会因为格式原因结果错误，此时需要先截取特征最明显的部分数字内容，在进行匹配，如下图所示：

与countif函数类似的还有sumif函数，可以用来自动分类汇总计算，如下图所示：

9. 跨表单计算