excel图表将所有数字分别提取到不同的单元格
发布时间:2023-01-17 06:21:29
前两篇文章分别解了提取位于字符串开头和末尾的数字的公式技术,本文研究从字符串中提取所有数字的技术:
1. 字符串由数字、字母和特殊字符组成
2. 数字在字符串的任意地方
3. 字符串中的小数也一样提取
3. 想要的结果是将所有数字返回独立的单元格
例如,在单元格A1中的字符串:
81;8.75>@5279@4.=45>A?A;
返回:
单元格B1:81
单元格C1:8.75
单元格D1:5279
单元格E1:4
单元格F1:45
解决方案
首先,确保活动单元格处于工作表行1中,然后定义下面两个名称。
名称:Arry1
引用位置:=ROW(INDIRECT(“1:”&LEN(“α”&$A1&”α0”)-1))
名称:Arry2
引用位置:=MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(“α”&$A1&”α0″,”/”,”α”),Arry1+{0,1},1)))>6)*{2,1},{1;1})
在单元格B1中输入数组公式:
=IFERROR(0+MID(“α”& $A1 &”α0″,1+SMALL(IF(Arry2=2,Arry1),COLUMNS($A:A)),SUM(SMALL(IF(ISNUMBER(MATCH(Arry2,{1,2},0)),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})),””)
向右拖放直至出现空单元格为止。
原理解析
1. 先看看这两个定义的名称。对于Arry1:
=ROW(INDIRECT(“1:”&LEN(“α”&$A1&”α0”)-1))
生成由整数构成的数组。注意,在单元格A1的字符串前面添加了一个非数字字符“α”,在末尾添加了一个非数字字符和一个数字“α0”。为什么这样处理?具体原因在后文详述。
上述公式转换为:
=ROW(INDIRECT(“1:”&27-1))
结果为:
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26}
2. 对于Arry2:
=MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(“α”&$A1&”α0″,”/”,”α”),Arry1+{0,1},1)))>6)*{2,1},{1;1})
(1)公式通过引用ASCⅡ字符代码来测试单元格A1里的数字。
(2)要识别数字子字符串,必须找到字符串里两个不同的位置:一个对应着数字的起始位置,另一个对应着数字的结束位置。
(3)公式中的第一部分将给MID函数提供参数start_num,在生成的结果之间的减法提供相应的参数num_chars。
(4)对于0-9范围的整数的ASCⅡ编码从48到57,小数点是46。因此,如果首先从字符串中排除与ASCⅡ编码47相对应的任何字符(“/”),那么可以确定字符串中ASCⅡ编码在46-57范围内的任何字符要么是数字要么是小数点。
(5)使用字符的ASCⅡ编码减51.5,判断其结果的绝对值,如果小于或等于6,则可以判断该字符是数字或小数点。(这里运用的技巧等价于通常要使用的两个单独的条件判断,即一个来比较ASCⅡ编码大于45,另一个来比较ASCⅡ编码小于58。)
(6)注意,为了发现数字的开始位置和结束位置,这里查找字符串里的两对字符:一对中的第一个字符是非数字字符而第二个是数字字符(提供数字字符串的开始),另一对中的第一个字符是数字字符而第二个是非数字字符(提供数字字符串的结尾)。
(7)当然,如果字符串中的第一个或最后一个字符与上述标准相符,那么需要确保有一些字符在它们的前面或后面,这就是我们在A1的开头和结尾连接合适的字符串的原因。于是,就有了你所看见的”α”&和&”α0″。
这样,Arry2公式转换为:
MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(“α81;8.75>@5279@4.=45>A?A;α0″,”/”,”α”),Arry1+{0,1},1)))>6)*{2,1},{1;1})
转换为:
MMULT(0+(ABS(51.5-CODE(MID(“α81;8.75>@5279@4.=45>A?A;α0”,Arry1+{0,1},1)))>6)*{2,1},{1;1})
将Arry1代入,得到:
MMULT(0+(ABS(51.5-CODE(MID(“α81;8.75>@5279@4.=45>A?A;α0”,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}+{0,1},1)))>6)*{2,1},{1;1})
接下来是值得关注的技术之一。因为希望从字符串里考虑成对的字符,所以需要将字符串里位置1中的字符和位置2中的字符比较、位置2中的字符和位置3中的字符比较,依此类推。为了实现这个目的,需要生成传递给MID函数作为参数start_num的数组:{1,2;2,3;3,4;4,5;5,6;…}。
由于Arry1为{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24},是一个24行1列的数组,将其与一个1行2列的数组{0,1}相加,结果是一个24行2列的数组:
MMULT(0+(ABS(51.5-CODE(MID(“α81;8.75>@5279@4.=45>A?A;α0”,{1,2;2,3;3,4;4,5;5,6;6,7;7,8;8,9;9,10;10,11;11,12;12,13;13,14;14,15;15,16;16,17;17,18;18,19;19,20;20,21;21,22;22,23;23,24;24,25},1)))>6)*{2,1},{1;1})
转换为:
MMULT(0+(ABS(51.5-CODE({“α”,”8″;”8″,”1″;”1″,”;”;”;”,”8″;”8″,”.”;”.”,”7″;”7″,”5″;”5″,”>”;”>”,”@”;”@”,”5″;”5″,”2″;”2″,”7″;”7″,”9″;”9″,”@”;”@”,”4″;”4″,”.”;”.”,”=”;”=”,”4″;”4″,”5″;”5″,”>”;”>”,”A”;”A”,”?”;”?”,”A”;”A”,”;”}))>6)*{2,1},{1;1})
转换为:
MMULT(0+(ABS(51.5-{63,56;56,49;49,59;59,56;56,46;46,55;55,53;53,62;62,64;64,53;53,50;50,55;55,57;57,64;64,52;52,46;46,61;61,52;52,53;53,62;62,65;65,63;63,65;65,59})>6)*{2,1},{1;1})
转换为:
MMULT(0+({11.5,4.5;4.5,2.5;2.5,7.5;7.5,4.5;4.5,5.5;5.5,3.5;3.5,1.5;1.5,10.5;10.5,12.5;12.5,1.5;1.5,1.5;1.5,3.5;3.5,5.5;5.5,12.5;12.5,0.5;0.5,5.5;5.5,9.5;9.5,0.5;0.5,1.5;1.5,10.5;10.5,13.5;13.5,11.5;11.5,13.5;13.5,7.5}>6)*{2,1},{1;1})
转换为:
MMULT({1,0;0,0;0,1;1,0;0,0;0,0;0,0;0,1;1,1;1,0;0,0;0,0;0,0;0,1;1,0;0,0;0,1;1,0;0,0;0,1;1,1;1,1;1,1;1,1}*{2,1},{1;1})
下面来看看现在得到的这个数组中的值代表的意思,我们高亮显示4组数字为例:
{1,0;0,0;0,1;1,0;0,0;0,0;0,0;0,1;1,1;1,0;0,0;0,0;0,0;0,1;1,0;0,0;0,1;1,0;0,0;0,1;1,1;1,1;1,1;1,1}
从MID函数中得到的字符数组:
{“α”,”8″;”8″,”1″;“1”,”;”;”;”,”8″;”8″,”.”;”.”,”7″;”7″,”5″;”5″,”>”;“>”,”@”;”@”,”5″;”5″,”2″;”2″,”7″;”7″,”9″;”9″,”@”;“@”,”4″;”4″,”.”;”.”,”=”;”=”,”4″;“4”,”5″;”5″,”>”;”>”,”A”;”A”,”?”;”?”,”A”;”A”,”;”}
(1)第1个高亮显示的对是{0,1},相应的字符是{“1”,”;”},因为“1”是数字而“;”不是。
(2)第2个高亮显示的对是{1,1},相应的字符是{“>”,”@”},因为”>”和”@”都不是数字。
(3)第3个高亮显示的对是{1,0},相应的字符是{“@”,”4″},由非数字和数字组成。
(4)第4个高亮显示的对是{0,0},相应的字符是{“4″,”5”},都是数字。
现在需要一种方法来区分这4对,等价于:
{0,0}:该对中的两个都是数字
{1,0}:该对中第一个是非数字,第二个是数字
{0,1}:该对中第一个是数字,第二个是非数字
{1,1}:该对中的两个都是非数字
显然,我们感兴趣的是中间的两对,因为这告诉我们字符串中数字与非数字的交界点。为此,将得到的由0/1组成的数组乘以一个由两个元素(2和1)组成的1行2列的数组。这样,公式转换为:
MMULT({2,0;0,0;0,1;2,0;0,0;0,0;0,0;0,1;2,1;2,0;0,0;0,0;0,0;0,1;2,0;0,0;0,1;2,0;0,0;0,1;2,1;2,1;2,1;2,1},{1;1})
得到:
{2;0;1;2;0;0;0;1;3;2;0;0;0;1;2;0;1;2;0;1;3;3;3;3}
数组中的0-3对应于上述四对组合。例如,得到3的唯一方式是1加2,而乘以{2,1}后得到由1和2组成的数组的对是{1,1},其中的值都是非数字,因此3代表的都是非数字;值2来源于{2,1}乘以{1,0},代表非数字后跟着一个数字;值1来源于{2,1}乘以{0,1},代表数字后跟着一个非数字,等等。
因此,Arry2后生成的数组让我们可以知道字符串中的字符从数字变为非数字或者从非数字变为数字的位置。
3. 现在来看看单元格B1中的公式:
=IFERROR(0+MID(“α” & $A1& “α0″,1+SMALL(IF(Arry2=2,Arry1),COLUMNS($A:A)),SUM(SMALL(IF(ISNUMBER(MATCH(Arry2,{1,2},0)),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})),””)
看看这里传递给MID函数的两个参数。要提取的字符串的起始位置参数start_num:
1+SMALL(IF(Arry2=2,Arry1),COLUMNS($A:A))
可以看到,我们基于Arry2等于2创建了一个数组,对应着由非数字字符和数字字符组成的对,即:
1+SMALL(IF({2;0;1;2;0;0;0;1;3;2;0;0;0;1;2;0;1;2;0;1;3;3;3;3}=2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}),COLUMNS($A:A))
转换为:
1+SMALL(IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}),COLUMNS($A:A))
转换为:
1+SMALL({1;FALSE;FALSE;4;FALSE;FALSE;FALSE;FALSE;FALSE;10;FALSE;FALSE;FALSE;FALSE;15;FALSE;FALSE;18;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},COLUMNS($A:A))
可以看到,生成的数组中的数值1、4、10、15、18分别为指定字符串中每个数字的起始位置。在B1中,COLUMNS函数返回1,公式可转换为:
1+SMALL({1;FALSE;FALSE;4;FALSE;FALSE;FALSE;FALSE;FALSE;10;FALSE;FALSE;FALSE;FALSE;15;FALSE;FALSE;18;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},1)
结果为(因为我们事先在A1中的字符串之前添加了一个字符):
2
对于传递给MID函数的获取要提取的字符数的参数num_char:
SUM(SMALL(IF(ISNUMBER(MATCH(Arry2,{1,2},0)),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})
我们对Arry2中的值1或2感兴趣,因为它们对应着字符串中的非数字/数字对。
要确定提取的每个子字符串的长度,需要计算每个连续的非数字/数字和数字/非数字的间隔之间的字符数,因为它们代表每组连续数字的开始和结束位置。将Arry2值代入后,上述公式转换为:
=SUM(SMALL(IF(ISNUMBER(MATCH({2;0;1;2;0;0;0;1;3;2;0;0;0;1;2;0;1;2;0;1;3;3;3;3},{1,2},0)),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})
转换为:
=SUM(SMALL(IF(ISNUMBER({2;#N/A;1;2;#N/A;#N/A;#N/A;1;#N/A;2;#N/A;#N/A;#N/A;1;2;#N/A;1;2;#N/A;1;#N/A;#N/A;#N/A;#N/A}),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})
转换为:
=SUM(SMALL(IF({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})
代入Arry1的值:
=SUM(SMALL(IF({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}),2*COLUMNS($A:A)+{-1,0})*{-1,1})
转换为:
=SUM(SMALL({1;FALSE;3;4;FALSE;FALSE;FALSE;8;FALSE;10;FALSE;FALSE;FALSE;14;15;FALSE;17;18;FALSE;20;FALSE;FALSE;FALSE;FALSE},2*COLUMNS($A:A)+{-1,0})*{-1,1})
上面生成的数组中的数值代表着字符串中从非数字到数字或者从数字到非数字的位置。
现在,需要指定SMALL函数的参数k,当我们向右拖拉公式时可以提取一对相应位置的数字。第一对是第1和第2个值,即1和3;第二对是第3和第4个值,即4和8;依此类推。然后,由每一对中第2个值减去第1个值得到想要的长度。因此,在B1中,公式可转换为:
=SUM(SMALL({1;FALSE;3;4;FALSE;FALSE;FALSE;8;FALSE;10;FALSE;FALSE;FALSE;14;15;FALSE;17;18;FALSE;20;FALSE;FALSE;FALSE;FALSE},2*1+{-1,0})*{-1,1})
转换为:
=SUM(SMALL({1;FALSE;3;4;FALSE;FALSE;FALSE;8;FALSE;10;FALSE;FALSE;FALSE;14;15;FALSE;17;18;FALSE;20;FALSE;FALSE;FALSE;FALSE},{1,2})*{-1,1})
转换为:
=SUM({1,3}*{-1,1})
转换为:
=SUM({-1,3})
得到:
2
我们将上面的中间结果代入单元格B1的公式:
=IFERROR(0+MID(“α”&$A1,2,2),””)
转换为:
=IFERROR(0+MID(“α81;8.75>@5279@4.=45>A?A;”,2,2),””)
转换为:
=IFERROR(0+”81″,””)
结果为:
81
值得一提的是,这个公式也适用于提取任何字母数混合的字符串中的数字。虽然平时从字符串中提取多个连续的数字的需求并不常见,但该技术仍然值得细细研究。
猜你喜欢
- 很多用户在使用电脑的时候,总是会有遇到电脑长时间不用的情况,这时候将其进行休眠是最好的方法,不仅能够让电脑寿命更长,还不会费电,但一些新用户
- excel坐标轴刻度不均匀怎么设置?1.添加辅助列1.点击图表工具——选择数据。2.添加数据系列。X轴系列值——即你所需的刻度值Y轴系列值—
- 平常我们在使用Word进行文档编辑时,总要重复输入一些内容,如自己的姓名、地址、电子邮件和电话,这些枯燥的劳动是不是让您感到厌倦和乏味呢?其
- 突然断电?软件崩溃?系统中毒电脑突然关掉?手颤抖点击不保存就关闭,导致资料丢失?几小时甚至几天几个月的资料瞬间丢失?怎么破?这里教你破!很简
- 简介:word与ppt文档提供了快速更改大小写的功能,无需一个一个字母的改。第一步:输入英文 how are you,并选中。如图第二步:点
- 很多小伙伴在升级系统之后就碰到了无法开启快速启动功能的问题。他们想要开启快速启动的时候碰到了系统提示0xC00000D4错误代码,这个代码是
- word文档中的文字头部被切掉该怎么显示?word中文字可能因为字体太大,所以出现了字的头部被切掉了部分的现象,该怎么办呢?下面我们就来看看
- iOS 14.5 可以说是 iOS 14 系统自发布以来最大的一次更新,包括支持通过 Apple Watch 解锁 iPhone,
- 在之前的教程中,我们为大家带来了搜狗双拼的使用方法,给大家分享了使用搜狗双拼打出文字的拼音和声调的方法。不过,一些网友表示,不想下载搜狗双拼
- 近日受到大多数用户反映,发现有关于Win10系统设置电脑关机指令的问题是很多用户的困扰,有些用户不明白该如何操作。今天小编就为大家解决这个问
- 在Word中图片也可以有很多的操作技巧,好比说组合、对齐、分布、叠放等等,但是对于这些方面,一般用户都很少去学习的,不过又是极
- 目录,就是一本书或者是一篇长文档,第一页就能看到的文章编排次序,方便阅读者查阅,检索。而在我们平时在编辑常文档也可以插入目录。但是小编发现大
- 在Word中,设置行间距、对其方式等,都属于段落设置,真正的Word高手,这些操作中,只要有快捷键的,都可以用快捷键来完成,如果您也希望自己
- 我们平常在使用浏览器的时候很少有使用到IE浏览器,所以想要将桌面上的IE图标删除掉,可在删除的时候却发现IE图标无法删除,那遇到这个问题应该
- 使用Shift键与下表中的组合键组合使用,可以选择单元格区域,例如,按Ctrl+Shift+Home组合键将选择从单元格A1开始到当前单元格
- Media Creation Tool 2004是Win10官方的升级程序,官方的媒体创建工具页面还没有更新,但新的链接已经上线了,只需要在
- 本例主要向大家介绍如何快速比较excel中两列数据中的相同数据、不同数据,如果需要,将上述相同或不同数据提取出来。首先,在C1输入公式=MA
- 有时我们需要从网页上复制一些文章到Word中,但奇怪的是往往会带有好多向下箭头的符号,这就是软回车符号(Word中软回车是同时按住shift
- 办公中经常会制作流程图/组织结构图,可以使用插入下的SmartArt,直接制作流程图。今天给大家分享下word组织结构图怎么做,一起来看看吧
- 升级iOS14.5测试版后大家都遇到过哪些bug?iOS14.5虽然已经发布到了第三个测试版,但毕竟只是测试版,还不够稳定,bug也不少。下