Excel一行单元格中有文字也有数字将数字提取出来自动求和的方法.docx
《Excel一行单元格中有文字也有数字将数字提取出来自动求和的方法.docx》由会员分享,可在线阅读,更多相关《Excel一行单元格中有文字也有数字将数字提取出来自动求和的方法.docx(6页珍藏版)》请在冰豆网上搜索。
Excel一行单元格中有文字也有数字将数字提取出来自动求和的方法
Excel一行单元格中有文字也有数字将数字提取出来自动求和的方法
如:
下面是EXCEL表格中的一行,有多个数据:
M2M8N5N5X6X8……需要将数字提取出来求和,但由于列数太多(大约30个),无法将提取出来的数值使用SUM(2,8,5……)来求和,如:
下面是EXCEL表格中的一行,有多个数据:
M2M8N5N5X6X8……
需要将数字提取出来求和,但由于列数太多(大约30个),无法将提取出来的数值使用SUM(2,8,5……)来求和,是否有方法能整行提取数值且自动求和
新浪微博
QQ空间
可选中1个或多个下面的关键词,搜索相关资料。
也可直接点“搜索资料”搜索整个问题。
excel
单元格
文字
数字
求和
搜索资料
解决方法1:
例如数据放在B列的B1至B30区域里面,1、在C1中输入=IF(ISNUMBER(B1),B1,"")这样就会把数字筛选出来,文本单元格变成空单元格,2、向下填充C1至C30,3、在其他单元格用=sum(C1:
C30)求和。
即可
解决方法2:
假设数据在A1单元格:
如果文字在前,B1=left(A1,lenb(A1)-len(A1))可得文字,C1=right(A1,2*LEN(A1)-LENB(A1))可得数字;
如果数字在前,B1=right(A1,lenb(A1)-len(A1))可得文字,C1=left(A1,2*LEN(A1)-LENB(A1))可得数字。
解决方法3:
B1=LEFT(A1,FIND(C1,A1)-1)
C1=LOOKUP(9^9,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&5^19)),ROW(1:
99)))
D1=SUBSTITUTE(A1,B1&C1,)
实例:
在EXCEL中,一行中每个单元格有数字和文字组合,求和
如图,数据在B2:
D2中,在B3得到3个月的总数量:
B3=-SUM(-TRIM(LEFT(SUBSTITUTE(B2:
D2,"/",REPT("",15)),15)))
同时按Ctrl+Shift+Enter三键输入数组公式
把“/”替换为15个空格,然后取前15个字符,以保证取到是前面的数字加上一些空格(15-数字个数)。
再用TRIM删除多余的空格。
-号转换为负的数字,求和后再加-号变为正数。
15可为比较大的任意数字,这里用15是考虑Excel的有效数字位数为15位。
Excell表格常用的七类公式
一、数字处理
1、取绝对值函数
公式:
=ABS(数字)
2、取整函数
公式:
=INT(数字)
3、四舍五入函数
公式:
=ROUND(数字,小数位数)
二、判断公式
1、如果计算的结果值错误那么显示为空
公式:
=IFERROR(数字/数字,)
说明:
如果计算的结果错误则显示为空,否则正常显示。
2、IF语句的多条件判定及返回值
公式:
IF(AND(单元格(逻辑运算符)数值,指定单元格=返回值1),返回值2,)
说明:
所有条件同时成立时用AND,任一个成立用OR函数。
三、常用的统计公式
1、统计在两个表格中相同的内容
公式:
B2=COUNTIF(数据源:
位置,指定的,目标位置)
说明:
如果返回值大于0说明在另一个表中存在,0则不存在。
如果,在此示例中所用到的公式为:
B2=COUNTIF(Sheet15!
A:
A,A2)
2、统计不重复的总数据
公式:
C2=SUMPRODUCT(1/COUNTIF(A2:
A8,A2:
A8))
说明:
用COUNTIF函数统计出源数据中每人的出现次数,并用1除的方式把变成分数,最后再相加。
四、数据求和公式
1、隔列求和的应用
公式:
H3=SUMIF($A$2:
$G$2,H$2,A3:
G3)或=SUMPRODUCT((MOD(COLUMN(B3:
G3),2)=0)*B3:
G3)
说明:
如果在标题行中没有规则就可以用第2个公式
2、单条件应用之求和
公式:
F2=SUMIF(A:
A,C:
C)
说明:
这是SUMIF函数的最基础的用法
五、查找与引用公式
1、单条件查找
说明:
VLOOKUP是excel中最常用的查找方式
六、字符串处理公式
1、多单元格字符串的合并
说明:
Phonetic函数只能合并字符型数据,不能合并数值
2、截取结果3位之外的部分
说明:
LEN计算总长度,LEFT从左边截总长度-3个
七、日期计算相关
1、日期间相隔的年、月、天数计算
A2是开始日期(2011-12-2),B2是结束日期(2013-6-11)。
计算:
相差多少天的公式为:
=datedif(A2,B2,d)其结果:
557
相差多少月的公式为:
=datedif(A2,B2,m)其结果:
18
相差多少年的公式为:
=datedif(A2,B2,Y)其结果:
1
不考虑年份相隔多少月的公式为:
=datedif(A1,B1,Ym)其结果:
6
不考虑年份相隔多少天的公式为:
=datedif(A1,B1,YD)其结果:
192
不考虑年份月份相隔多少天的公式为:
=datedif(A1,B1,MD)其结果:
9
datedif函数第3个参数说明:
Y时间段中的整年数。
M时间段中的整月数。
D时间段中的天数。
MD日期中天数的差。
忽略月和年。
YM日期中月数的差。
忽略日和年。
YD日期中天数的差。
忽略年。
用以下方法在字符串中提取出数字后再相加求和:
1、字符串中的数字在特定汉字后。
示例公式:
=-lookup(0,-mid(a1,find("某特定汉字",a1)+LEN("某特定汉字"),row(1:
99)))公式结果为提取A1单元格中某特定汉字之后的数字。
2、字符串中的数字在整个字符串的两端。
示例公式:
数字在前,公式=left(a1,2*len(a1)-lenb(a1));数字在后,公式=right(a1,2*len(a1)-lenb(a1))
3、数字在字符串中中间。
公式=-lookup(0,-mid(a1,min(find(row(1:
10)-1,a1&1/17)),row(1:
99)))按组合键ctrl+shift+enter结束公式。
在D2中输入公式:
=IF(C2="","",SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(C2,")",REPT("",9)),ROW($1:
$100),10),),2,9),";;;!
0")))
下拉填充。
精心搜集整理,只为你的需要