分类：纠错篇

由于 Excel 内置的合并工具对一个区域执行合并后会丢失部分数据，因此使用 SUMIF 或COUNTIF等函数统计数据时会遗漏部分数据。

图 5.40 是合并前的数据，图 5.41 是合并后的数据，合并后使用公式“=SUMIF（B2：B20，G2，E2）”统计湖北省的职工捐款时会遗漏大部分数据，原本合计结果应该为2000，实际合计结果却是535。

如何才能实现合并单元格的状态下执行数据运算而不遗漏部分数据呢？

图5.40 合并前

图5.41 合并后计算出错

解题步骤

直接对区域执行合并会导致区域中左上角单元格以外的所有单元格都丢失数据。

在辅助区域中执行合并，然后将辅助区域的格式粘贴到需要合并的区域，最后删除辅助区域的值，此方法既可合并目标单元格，又能保留每个单元格的值，从而不影响公式运算。

具体操作步骤如下。

1.将B2：B20区域复制到G2：G20。

2.对G2：G20的值按内容合并，相邻且相同的单元格合并一次，合并效果如图5.42所示。

图5.42 合并相同且相邻的单元格

3.选择G2：G20，并按下组合键＜Ctrl+C＞执行复制。

4.右键单击B2单元格，从右键菜单中选择“选择性粘贴”，然后在弹出的对话框中选择“格式”，最后单击“确定”按钮返回工作表界面。图5.43为“选择性粘贴”的设置界面。

图5.43 设置选择性粘贴选项

5.删除G列的所有单元格。

6.在G1、H1和G2单元格分别输入“省”、“捐款数量”和“湖北省”，然后在H2单元格输入公式“=SUMIF（B2：B20，G2，E2）”，此时可以发现公式的计算结果不再产生任何遗漏，运算结果如图5.44所示。

图5.44 重新合并后的公式计算结果正常

知识扩展

1.直接合并单元格一定会丢失数据。假设A1：A3单元格的值都是“湖南省”，对A1：A3区域执行合并后，A2：A3的值会丢失，图5.45可以明确地说明此问题。

图5.45 用公式证明丢失数据

如果采用本例的方法，通过辅助区域来实现合并，A2 和 A3 的值会保留下来，通过公式可以证明，效果如图5.46所示。

图5.46 用公式证明未丢失数据

2.通过本例的步骤执行合并，区域中会保留所有值，因此选中该区域并单击“开始”选项卡中的“合并后居中”菜单，当单元格取消合并之后可以看到合并之前的所有数据。

工作表中数据较多，偶然在其中一个单元格中有空格，空格影响了后续的公式运算。由于不知道还有哪些单元格中存在空格，因此打算删除所有单元格中的空格。

有什么方法可以一次性删除工作表中的所有空格呢？

解题步骤

Excel并没有提供删除某字符的工具，但是在替换字符时将目标字符设置为空文本即可实现删除字符的功能，因此本例中的问题可以通过替换来实现，具体操作步骤如下。

1.按组合键＜Ctrl+A＞全选工作表中的所有数据区域。

2.按组合键＜Ctrl+H＞，弹出“查找和替换”对话框。

3.将“查找内容”设置为空格，然后观察“单元格匹配”复选框是否已选择，如果已选择则需要手动消掉，然后单击“全部替换”按钮，Excel会提示替换成功的数量。图5.38是替换选项的设置界面，而图5.39则是替换数量提示框。

图5.38 设置替换选项

图5.39 提示替换成功的数量

知识扩展

1.单元格中意外产生了空格时对于表格的运算、查找都有较大的影响。例如，将数值123.45输入成123.45，单元格会由于空格的干扰变成文本，而文本是不能参与SUM函数求和的。此外，如果文本的前面或后面意外产生了一个空格，那么VLOOKUP、COUNTIF等函数引用单元格时都会判断失误。

2.每一次执行替换前都有必要观察“单元格匹配”复选框是否选择，选择时表示等于关系，不选择时则表示包含关系。本例如果在选择状态下执行替换，那么单元格中只有一个空格没有其他字符才能替换成功，如果不选择则单元格中不管有多少字符，都会将其中的空格删除。

图5.35中C12的公式计算结果不正确，因为C2：C11区域中部分单元格的值是文本，而不是数值。如何才能快捷地将C2：C11区域中每个单元格的值都转换成数值呢？

图5.35 文本型数字导致求和出错

解题步骤

将文本型数字转换成数值有很多方法，最快捷地批量转换方法是分列，按以下步骤调用分列工具。

1.选择C2：C12区域。

2.单击功能区的“数据”→“分列”，弹出“文本分列向导”对话框。

3.不做任何设置，单击“完成”按钮，选区中的所有文本型数字都会转换成数值，C12 的公式也会相应地调整运算结果。图5.36是调整后的效果。

图5.36 转换效果

知识扩展

1.数值在单元格中显示为文本形式，其成因有两个：一是将单元格的数字格式设置为“文本”，然后再输入数值，二是先输入半角状态的撇号，然后再输入数值。不管哪种方式产生的文本型数字都可以通过分列转换成数值。

2.分列仅对列有效，一次转换一列数据。如果一个多行多列的区域都需要转换，如B2：G200，那么可以在旁边的一个空白单元格中输入数值0，并复制该单元格，然后选择B2：G200区域，调用“选择性粘贴”对话框，选中“数值”和“加”单选按钮，最后单击“确定”按钮。粘贴选项的设置界面如图5.37所示。

图5.37 设置粘贴选项

图 5.18 中由于部分职工的产量数据还没有输入，计算产量排名时就只能得到错误值。现要求在不修改公式的前提下隐藏所有错误值，要如何实现？

图5.18 显示错误值的公式

解题步骤

隐藏错误值最简单的手段是让字符与单元格的背景色一致，主要有三种方法：第一种方法是采用条件格式实现，步骤如下。

1.选择C2：C11区域，然后单击功能区的“开始”→“条件格式”→“新建规则”，弹出“新建格式规则”对话框。

2.单击“使用公式确定要设置格式的单元格”，然后在下方的公式栏输入“=iserror（C2）”，最后单击“格式”按钮，弹出“设置单元格格式”对话框，操作界面如图5.19所示。

3.打开“字体”选项卡，将字体颜色修改为白色，然后打开“填充”选项卡，将单元格的填充颜色也修改为白色，最后单击“确定”按钮保存设置，并返回工作表界面，图 5.20 为设置字体颜色界面。

图5.19 指定公式条件

图5.20 设置颜色

设置好条件与格式后，返回工作表可以看到图 5.21 所示的效果，其中原本为错误值的单元格已经显示为空白。

图5.21 添加条件格式后的效果

第二种方法是采用定位工具直接选中包含错误值的单元格，然后修改单元格的字体颜色与背景色，让二者统一。这种方法操作比较简单，但是通用性不强，当区域中有新的单元格产生错误值时必须再次定位、设置颜色，因此不再详细阐述操作过程。

第三种方法是设置单元格的数字格式达成需求，步骤如下。

1.选择C2：C11区域，然后按组合键＜Ctrl+1＞，弹出“设置单元格格式”对话框。

2.单击分类项目表中的“自定义”，然后在右方的格式代码框中输入“[黑色]”，操作界面如图5.22所示。

图5.22 设置单元格的数字格式

3.单击“确定”按钮保存设置，同时返回工作表界面。

4.在“开始”选项卡中为选区指定字体颜色为白色，此时选区中的错误值将自动隐藏起来，效果如图5.23所示。

图5.23 将字体颜色改为白色后的效果

知识扩展

1.本例所讲的3种方法都是通过修改字符的颜色来实现隐藏的，因此选中单元格后，在编辑栏仍然可以看到单元格的原值。如果要求编辑栏中也无法看到，那么需要选择单元格的“锁定”复选框，然后保护工作表。

2.本例用到的格式代码“[黑色]”表示让单元格中的文字显示为黑色，但是此代码对错误值无效。然后将单元格的字体颜色设置为白色，它对所有值都生效。由于格式代码的权限高于单元格的字体色，因此单元格的执行顺序是先应用字体颜色，将所有单元格的值都显示为白色，然后应用格式代码，让非错误值显示为黑色，而错误值则仍然显示为白色。

3.格式代码“[黑色]”仅对中文版Office有效，如果使用英文版Office，只能用英文的格式代码。

将单元格向下填充时，除了列宽以外，其他所有格式信息都会一并复制到新单元格中，格式信息包含背景颜色、字体名称、字体大小、对齐方式、边框、是否锁定、是否有条件格式、是否加粗、是否倾斜及行高等信息。

有时只需要将单元格的数值或公式向下填充，不保留公式，有没有办法实现此需求？

解题步骤

使用左键向下填充，或者使用组合键＜Ctrl+D＞向下填充单元格都会填充格式，只有使用右键填充才能做到忽略格式，步骤如下。

1.在B2单元格输入样本A20，并设置填充颜色、字体边框等格式。

2.选择B2，将鼠标移至右下角，当白色的空心十字光标变成黑色的实心十字光标时，按下右键向下拖到A12，然后松开右键，从而调用右键菜单，界面如图5.16所示。

3.从右键菜单中单击“不带格式填充”，填充效果如图5.17所示。

图5.16 按下右键填充从而调用右键菜单

图5.17 填充结果

在图5.17中，B2后面的单元格只有填充产生的字符，没有任何格式信息。

知识扩展

1.填充后再单击功能区的“开始”→“清除”→“清除格式”，此操作功能达到“不带格式填充”的相同功能。

2.当工作簿中有较多的工作表时，工作表单元格中应尽可能少设置格式，格式信息也会占用内存。当太多的单元格都设置了多种格式信息时，打开工作簿会需要更长的时间。

打字时经常手误，以五笔输入法为例，“嘘唏”的编码为khkq，有可能拆分成kqkh从而输入了“吹嘘”；“钳子”的五笔编码为 qabb，然后按空格，有可能拆分成 qab，然后按空格；“产品”的五笔编码为utkk，有可能拆分成uttk从而成为“产程”……

不同人的易出错字符是不一样的，笔者最易出错的是将“产品”误录为“产程”，以及将“钳子”误录为“错了”，有何方法纠正这种错误？

解题步骤

Excel中处理常见的字符输入错误比Word更简单，直接通过自动更正来处理即可，具体步骤如下。

1.按组合键＜Alt+T+O＞，弹出“Excel选项”对话框。

2.打开“校对”选项卡，然后再单击右方的“自正更正选项”，操作界面如图5.13所示。

图5.13 打开“自动更正选项”

3.将“替换”设置为“错了”，将“为”设置为“钳子”，然后单击“添加”按钮，操作界面如图5.14所示。

图5.14 添加一对更正字符

4.继续添加要更正的字符，将“替换”设置为“产程”，将“为”设置为“产品”，然后单击“添加”按钮，并单击“确定”按钮保存设置。

5.在单元格中输入“产程”，当单击Enter键后，Excel会自动将它替换成“产品”。同理，在单元格中输入“错了”，单击Enter键后Excel会将它替换成“钳子”。

知识扩展

1.前面说的在Excel中处理这类问题比在Word中简单，是因为Word中可能会用到任何一个词语，既可以用到“钳子”也可能用到“错了”，既可能用到“产程”也可能用到“产品”，因此不能直接使用自动更正来处理这类问题。而在Excel中，制作报表时是不会用到“错了”和“产程”这类字样的。

2.对于汉字，在更正对象的前面有其他汉字时仍然会执行自动更正，如设置将“错了”更正为“钳子”，当输入“错了”和“我错了”时都会执行更正，但是在“错了”右方还有汉字时不会执行更正，如“错了吗”。

3.对于英文，只更正单词，不更正单词中间的部分字符，如将“abotu”更正为“about”，输入“Excel abotu VBA”时会自动更正为“Excel about VBA”，但是输入“ExcelabotuVBA”时不会更正为“ExcelabotuVBA”。

4.自动更正只针对手工录入的有效，通过粘贴产生的字符无效。

5.除了纠错以外，也可以使用自动更正来提升生僻字或不常用符号的输入速度。例如，按图5.15所示的方式将“替换”设计成“\qian”，将“为”设置成“‰”，在单元格中录入“523\qian”时会自动更正为“523‰”。

图5.15 设置替换选项