【excel数组怎么显示】理解从Excel| |文本中提取数字的小实例数组和数组公式

Excel工作表可以解释为由行和列组成的二维数组。Excel公式支持数组和数组公式。数组可以是常量数组，也可以是对单元格区域的引用。使用数组和数组公式时，可以按数组元素或单元格处理，然后返回结果。

下面从一个例子开始讨论相关知识点。

1例(从文本中提取数字)

相关数据和要实现的效果：

序列号

记录

金额

1

吃饭15元。

15

2

朋友生日礼物300韩元

300

3

买95米

95

4

火车票200元回家

200

5

房租900元

900

6

水费200韩元

200

7

电费250韩元

250

关键是要得到第一个数字出现的位置。

请使用以下公式

=FIND(1，B2，1)

可以返回3，表示字符串“饭15元”中字符1的位置。

如果你要找0、1、2、3、4、5、6、7、8、90个字符呢？此时，数组和数组公式可能很有用。您可以使用数组{0，1，2，3，4，5，6，7，8，9}作为参数，逐步处理每个数字在字符串中出现的位置，并返回数组(10个值)。因此，选择具有10个单元格的连续区域D2:M2，然后输入=find ({0，1，2，3，4，5，6，7，8，9}，b2，1)

无效值#VALUE！表示字符串中不存在数字，因此无法返回位置值。

为了避免无效值，您可以在字符串后面连接包含10位数字字符的数字字符串。

上表每行中最小的数字是字符串中每个数字出现的位置，因此可以使用MIN()函数。=min (find ({0，1，2，3，4，5，6，7，8，9}，B2' 0123456789 '，1))

下一步是获取数字长度。

LEN()函数返回字符数，LENB()函数可以返回字符串中的字节数、数字1字节、汉字1字节2字节，通过组合可以得到数字的长度。=len (B2)-(lenb (B2)-len

您可以使用Mid函数截断数字，或在公式前面使用两个减号，将文本转换为数字。

=-mid (B2，min (find ({0，1，2，3，4，5，6，7，8，9}，b 2’0123456789))

53be77ae48afbbbef9?from=article.detail&_iz=31825&index=4" width="640" height="182"/>

以下公式可以做一个改良：

1 {0,1,2,3,4,5,6,7,8,9}可以用ROW($A$1:$A$10)-1代替；

2 "0123456789"可以用5/19代替。

5/19 = 0.26315789474

得到的下面公式具有同样效果，更通用和简洁：

=--MID(B5,MIN(FIND(ROW($A$1:$A$10)-1,B5&5/19,1)),LEN(B5)-(LENB(B5)-LEN(B5)))

2 数组及数组公式相关知识

2.1 数组公式

数组就是一组数据，数组公式可以进行多重运算（对每一个数组元素或区域的每一个单元格都应用操作），减少了多次写于单元格的过程，可以实现常用公式较烦锁的操作，一步到位。

2.2 删除数组公式

当我们对一个区域应用数组公式时，如D2:D6，使用此区域，输入{=B2:B6*C2:C6}，按CTRL+SHIFT+ENTER，会在D2:D6的每个单元格输入{=B2:B6*C2:C6}，都是一样，此时数组公式是一个整体，不能更改数组的一部分，也就是不能单独删除其中的一个单元格的数组公式，我们要删除数组公式要全部选中，当我们有时修改一个单元格的数组公式，不能退出时，大家记得按ESC键。

2.3 一个单元格显示数组的情况

由于一个单元格内只能储存一个数值，所以当结果是一组数据时，单元格只返回第一个值。

2.4 数组分类

可分为横向数组、纵向数组、区域数组（多行多列），也可以按维来分，横向数组和纵向数组都属于一维数组，区域数组属于二维数，像这种｛1,2,3,1}就是常量数组

2.5 怎样查看数组的结果（也就是选择公式的一部分求结果），当然是独孤九剑F9或使用“公式求值”来按顺序求值复合公式的每一部分。

2.6 数组成员中间有时有分号，有时用逗号是怎么回事？横向数组用逗号分开，纵向数组用分号分开。

2.7 常量数据在函数里的应用

a) 23 =INDEX({23,24,25,22},1,1)

b) 24=INDEX({23,24,25,22},1,2)

c) 25=INDEX({23,24,25,22},1,3)

d) 22=INDEX({23,24,25,22},1,4)

2.8 要么用某些函数来取其共性,如SUM Max/Min,Small/Large等

a) 94=Sum({23,24,25,22})

b) 25=Max({23,24,25,22})

c) 22=Min({23,24,25,23})

d) 25=Large({23,24,25,22},ROW(A1))

e) 22 =SMALL({23,24,25,22},ROW(A1))

f) 23=SMALL({23,24,25,22},ROW(A2))

g) 24=SMALL({23,24,25,22},ROW(A3))

h) 25=SMALL({23,24,25,22},ROW(A4))

2.9 参数

a) 数组公式最大的特征就是所引用的参数是数组参数，包括区域数组和常量数组。

b) 区域数组，是一个矩形的单元格区域,如 $A$1:$D$5

c) 常量数组，是一组给定的常量,如{1,2,3}或{1;2;3}或{1,2,3;1,2,3}

d) 数组公式中的参数必须为"矩形"，如{1,2,3;1,2}就无法引用了

2.10 输入

同时按下CTRL+SHIFT+ENTER，数组公式的外面会自动加上大括号{}予以区分。

3 更多实例

3.1 求表一区域F42:G44大于10数据和

公式：{=SUM((F42:G44>10)*(F42:G44))}

公式解释：F42:G44>10会返回{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}，在运算时True=1 False=0。

然后数组{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}和区域数组(F42:G44))相对应的数据进行相乘，相乘之后得到一个新的数组{20,0;0,50;30,0}，然后用求和函数Sum进行求和，最后记得三键一齐下CTRL+SHIFT+ENTER

3.2 求表二产品AA的总价

公式：=SUM((C50:C54="AA")*(D50:D54)*(E50:E54))

c) 公式解释：=SUM((C50:C54="AA")*(D50:D54)*(E50:E54))

先用判断区域C50:C54是否有等于AA的，结果返回 {TRUE;FALSE;FALSE;TRUE;FALSE}，因为在运算过程中True=1，False=0这样就得到{=SUM({TRUE;FALSE;FALSE;TRUE;FALSE}*(D50:D54)*(E50:E54))}，然后因为任何数字和0相乘都等于0，也就是符合为AA的就不为0，不符合的就为0上面三个数组相乘最后得到一个新的数组{100;0;0;250;0}，最后用Sum求和，三键一齐下得到结果为350

4 Row()函数在数组公式中的运用

a) 谈到数组公式，我们不得不说一下ROW()这个函数，它在数组公式中起到了很大的作用，许多公式中都需要用到它来作为参数。

b) 我们先来做一个题目：求正整数列1,2,3,4……100这100个数字之和(首先假设你不知道等差数列求和公式，呵呵)，=SUM(ROW($A$1:$A$100))。

c) 我们利用Row(A1:A100)来产生1到100自然数，然后用Sum求和。我们都知道，ROW()是用于返回单元格行号的函数，通常它只能引用一个参数。但是在数组公式中，该函数就能引用多个单元格作为参数，对于整个引用区域进行分别运算，从而就能返回一组数据。

ROW(A1)=1

ROW(A2)=2

……

ROW(A100)=100

ROW($A$1:$A$100)={1;2;3……100}

知道了这一点以后,我们就能在数组公式中利用这一个功能来得到一组连续的正整数。

当然COLUMN()的作用和ROW()是相同的，上面的计算也能用以下公式：

{=SUM(COLUMN($A$1:$CV$1))}

但是相对于ROW()的引用方式来说，A1:A100要比A1:CV1更直观地体现出所引用的是100行还是100列，所以ROW()一般来说使用得更普遍些，当然也不排除有时候需要用到COLUMN(),这就要看具体情况了。

－End－