文本 – 第2页

Excel 文本型数字能否参与求和？

SUM 函数用于对数值、数组或区域中的数值求和，如果求和对象是文本型数字，则会求和失败。例如，在图7.50中A1是数值格式，B1单元格是文本格式，因此C1的公式“=SUM（A1：B1）”求和时会忽略B1的值。

图7.50 sum函数对文本型数字求和失败

当数据源区域太大，无法确定区域中是否有文本时，如何才能确保公式求和的准确性呢？

解题步骤

SUM函数无法直接对文本型数字求和，通过乘以1将文本型数字转换成数值，然后用数组公式就可以正常求和。以汇总图7.51中的捐款数量为例，具体步骤如下。

图7.51 待汇总的捐款表

1.在A12单元格输入“求和”。

2.在B12单元格输入公式“=SUM（B2：B11*1）”，然后按组合键＜Ctrl+Shift+Enter＞结束，当编辑栏中的公式前后自动生成了“{}”符号时表示数组公式输入成功，此时不管B2：B11区域中是否存在文本型数字，公式都能确保运算结果一定准确，结果如图7.52所示。

图7.52 对包含文本型数字的区域求和

知识扩展

1.本例中B4和B8是文本格式，因此直接用公式“=SUM（B2：B11）”求和只能得到2785，较正确结果3475的差值刚好等于B4加B8之和，表明公式“=SUM（B2：B11）”运算结果出错是由文本格式所导致。

2.使用SUM函数对包含文本型数字的区域求和时，要得到正确的结果必须使用数组公式，即输入公式后按＜Ctrl+Shift+Enter＞组合键结束。组合键的使用方法是先同时按下Ctrl和Shift键，然后在不松开的情况下按Enter键，最后三者同时松开。

3.使用SUMPRODUCT函数代替SUM函数求和可以免按＜Ctrl+Shift+Enter＞组合键。由于工作中绝大多数公式都是普通公式，不需要按＜Ctrl+Shift+Enter＞组合键，因此不常用数组公式者及 Excel 新手会经常忘记按键方式，导致公式计算结果出错，此时用 SUMPRODUCT 函数代替SUM函数能防错。使用SUMPRODUCT函数的公式如下：

它的计算结果与数组公式“=SUM（B2：B11*1）”一致，不需要按＜Ctrl+Shift+Enter＞组合键。

Excel 能否将文本型数字转换成数值？

图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 设置粘贴选项

Excel 如何检查数值区域是否存在文本内容？

数值区域中混有文本时会干扰数值运算，绝大多数函数在运算时都会忽略文本，导致运算结果不准确。

在一大片区域中找出所有文本仅凭肉眼是没有办法完成的。例如，在图3.66所示的B2：G11区域中有两个文本，仅凭双眼观察是无法找出它们的，如何才能瞬间找到所有文本单元格呢？

图3.66 成绩区域中混有文本的成绩表

解题步骤

Excel提供了定位工具，用于定位符合各种条件的单元格，其中包含文本所在单元格，具体操作步骤如下。

1.选择B2：G11区域，按F5键或＜Ctrl+G＞组合键，弹出“定位”对话框。

2.在“定位”对话框中单击“定位条件”按钮，取消选择“定位条件”对话框，操作界面如图3.67所示。

3.在“定位条件”对话框中选中“常量”单选按钮，然后取消选择下方的“数字”、“逻辑值”和“错误”3个复选框，设置界面如图3.68所示。

图3.67 “定位”对话框

图3.68 设置定位条件

4.单击“确定”按钮保存设置，同时返回工作表界面，此时可以看到定位结果——C5 和D7处于选中状态，表示这两个单元格都是文本，效果如图3.69所示。

图3.69 定位B2∶G11区域的所有文本

知识扩展

1.“定位条件”对话框可以区分文本、数值、逻辑值、错误值、有公式的单元格、空值、可见的单元格、设置了条件格式的单元格等各种对象，在工作中会经常需要用到“定位条件”。本例要求找出所有文本，采用“定位条件”是唯一的快速解决问题的方法。

2.文本有两种：其一是常量文本，即直接在单元格中输入的文本，“Excel”、“VBA”、“E 灵插件”和“你好”都是文本常量；其二是公式文本，即通过公式计算出来的文本，如 A1单元格是文本，那么B1单元格的公式“=a1”直接引用A1的值，那么B1就是公式文本。

3.本例的成绩区域都是手工输入的，因此仅需定位常量即可。假设不确定该区域中是否包含公式，应该定位两次才行，第一次定位常量文本，第二次定位公式文本。

4.本例B2：G11区域中包含两个文本：一个是在单元格处于文本格式状态下输入的数字，从而使数字转换为文本，另一个是将字母o当作数字0输入，从而变成了文本。肉眼很难判断这种错误，使用定位工具找出混在数值区域中的文本最准确也最快捷。

5.定位时不能公式和常量同时定位，必须分两次执行。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

Excel 美元货币符转换文本格式：DOLLAR函数详解

DOLLAR函数的功能是使用“$”（美元）货币格式，及给定的小数位数，将数字转换成文本。其语法是：

DOLLAR(number,decimals)

其中，number参数是数、数值的公式，或对含有数值单元格的引用；decimals参数是小数的位数。如果省略decimals，则假设其值为2；如果decimals为负数，则在小数点左侧进行舍入。下面通过实例具体讲解该函数的操作技巧。

打开“DOLLAR函数.xlsx”工作簿，本例中的原始数据如图12-42所示。要求使用DOLLAR函数，将工作表中的数值分别按整数、小数点后7位及小数点后两位转换成文本格式。具体操作步骤如下。

STEP01：选中B2单元格，在编辑栏中输入公式“=DOLLAR(A2,-2)”，然后按“Enter”键即可返回按整数转换的计算结果，如图12-43所示。

图12-42　原始数据

图12-43　返回A2单元格转换结果

STEP02：选中B3单元格，在编辑栏中输入公式“=DOLLAR(A3,7)”，然后按“Enter”键即可返回按小数点后7位转换的计算结果，如图12-44所示。

STEP03：选中B4单元格，在编辑栏中输入公式“=DOLLAR(A4,2)”，然后按“Enter”键即可返回按小数点后两位转换的计算结果，如图12-45所示。

图12-44　返回A3单元格计算结果

图12-45　返回A4单元格计算结果

Excel 判断数据否为文本：ISTEXT函数详解

ISTEXT函数用于判断指定数据是否为文本，语法为：

ISTEXT(value)

其中，value参数为指定的数值，如果value为文本，返回TRUE；否则，返回FALSE。下面通过实例详细讲解该函数的使用方法与技巧。

STEP01：新建一个空白工作簿，重命名为“ISTEXT函数”。切换至“Sheet1”工作表，并输入原始数据，如图12-1所示。

图12-1　原始数据

STEP02：选中B2单元格，在编辑栏中输入公式“=ISTEXT(A2)”，然后按“Enter”键返回，即可检测出A2单元格中的数值是否为文本。此时，工作表显示检测结果为“TRUE”，即A2单元格中的数值是文本，如图12-2所示。

STEP03：选中B2单元格，利用填充柄工具向下复制公式至B6单元格，通过自动填充功能来检测A3:A6单元格区域中的数值是否为文本，最终结果如图12-3所示。

图12-2　检测A2单元格数据是否为文本

图12-3　检测结果

如果需要检验数据是否为非文本，可以使用ISNOTTEXT函数来实现，其使用方法与ISTEXT一样。