我们在工作中,经常会使用一些从数据库或其他软件导出的数据,也可能是他人发给你的表格做一些数据统计,例如下表中单元格A2:A11中的数据,这是从数据库软件导出的,需要对A2:A11中的数据做求和计算。vp5检测VBA
vp5检测VBA
在A12单元格输入公式=SUM(A2:A11),发现结果为“0”,因为这种混合型的数据Excel默认为文本,文本求和得到就是“0”。这里可以采用增加辅助列的方式进行计算。vp5检测VBA
辅助列1:在B2单元格输入=--SUBSTITUTE(A2,"t",""),拖动下拉填充公式。SUBSTITUTE是将原数据中的"t"替换为空,前面加“--”是将文本转换成数值的计算,负负得正,这样可以不改变原数据里数值的大小。然后再B12单元格输入求和公式=SUM(B2:B11).vp5检测VBA
辅助列2:在C2单元格输入=SUBSTITUTE(A2,"t",""),拖动下拉填充公式。这里没有将原数据装换成数值,但是可以在使用求和函数+数组公式一起转换,C12单元格输入=SUM(--(C2:C11)),数组公式[Shift] +[Ctrl] + [Enter]三键组合。vp5检测VBA
辅助列3:在D2单元格输入=MID(A2,1,LEN(A2)-1)*1,拖动下拉填充公式。用MID函数提取文本里的数据,我们发现原数据只有最后一个字符是英文字母“t”,其他都是数字,用LEN函数,返回文本字符串中的字符数,MID函数从第一个字符开始提取,提取的字符数量为字符总数数减去1,MID函数提取后得到的依然是文本,将结果乘以1把文本转换成数值。vp5检测VBA
辅助列4:在E2单元格输入=LEFT(A2,LEN(A2)-1)-0,拖动下拉填充公式。英文LEFT是“左边”的意思,LEFT函数:从一个文本字符串的第一个字符开始返回指定个数的字符函数。提取字符个数和MID函数公式里一样,字符总数数减去1,LEFT函数提取后得到的依然是文本,将结果减去0把文本转换成数值。vp5检测VBA
还可以不采用辅助列直接计算。vp5检测VBA
公式1:=SUM(--SUBSTITUTE(A2:A11,"t","")),数组公式[Shift] +[Ctrl] + [Enter]三键组合。vp5检测VBA
公式2:=SUMPRODUCT(--SUBSTITUTE(A2:A11,"t",""))vp5检测VBA
这两个公式是使用SUBSTITUTE函数把"t"替换为空,也可以使用前面辅助列3、辅助列4中提取字符的方法。vp5检测VBA
公式3:=SUM(MID(A2:A11,1,LEN(A2:A11)-1)*1)数组公式[Shift] +[Ctrl] + [Enter]三键组合。vp5检测VBA
公式4:=SUMPRODUCT(MID(A2:A11,1,LEN(A2:A11)-1)*1)vp5检测VBA
前面的原数据比较规律,提取数字比较简单,有时候可能是一些复杂的数据,以下图为例:vp5检测VBA
vp5检测VBA
1/2 1 2 下一页 尾页 |