Excel一个表格中有文字和数字怎么求和? | 您所在的位置:网站首页 › wps怎么转换数字求和 › Excel一个表格中有文字和数字怎么求和? |
可以通过函数嵌套实现。 1、实现效果=SUM(IFERROR(--MID(SUBSTITUTE(TRIM(CONCAT(IFERROR(--MID(A1:A6,TRANSPOSE(ROW($1:$99)),1)," ")))," ",REPT(" ",98)),ROW($1:$99)*99-98,99),0)) *数组公式,需要三键结束输入(CTRL+SHIFT+ENTER)。 *需要支持CONCAT函数,否则公式会提示“#NAME?”错误。 3、公式简析①将目标区域内容每个字符拆分开,将文本、标点符号变为空格,数字内容不变。 ②将所有内容拼接成一行。 ③删除多余空格并插入定量的空格。 ④通过固定间隔将数字提取出来。 ⑤对所有提取出来的数字进行求和。 4、计算范围①支持的单个单元格最大字符数:99。 ②支持的目标区域最大数字数量:99。 ③支持的最大“数值总字符数”减去“数值数量”:99。 具体参数见下图,示例数据在公式计算范围之内,该公式可以正确求和: 实际情况可能会超过计算范围,则需要相应修改公式中的参数,修改点和修改规则如下: ①红色标记处A1:A6:求和数据所在的范围,根据实际情况修改; ②蓝色标记处ROW($1:$99):单次提取的字符数量。当单个单元格最大字符数超过99时,需要相应增加这个数值。 比如,单个单元格最大字符数量为276,则ROW($1:$99)可以改为ROW($1:$299)。 ③绿色标记处ROW($1:$99):总提取的数字数量。当区域内的数字超过99时,需要相应增加这个数值。 比如,目标区域共有876个数字(2位数、3位数等多位数字算1个数字),则ROW($1:$99)可以改为ROW($1:$899)。 ④红色标记处REPT(" ",98)和*99-98,99:误差控制。当区域内“数字总字符数”减去“数字数量”超过99时,需要相应增加这个数值(第1、3个数比第2、4个数固定小1)。 比如,目标区域有400个数,其中有80个2位数、50个3位数,那么“数字总字符数”减去“数字数量”为80*(2-1)-50*(3-1)=180,则REPT(" ",98)和*99-98,99可以改为REPT(" ",198)和*199-198,199。 6、修改范例①参数超过范围,计算错误。 见下图,右边的数字与左边的文字一一对应,求和值应该为7473。 ②修改对应参数后,计算正常。 参数范围过小,计算容易出错;参数范围过大,会拖慢计算速度。需要根据实际情况统筹选择参数范围。 有问题请留言。 |
CopyRight 2018-2019 实验室设备网 版权所有 |