本帖最后由 佛山小老鼠 于 2012-10-20 15:30 编辑 第九讲 数组基础知识和从文本里提取数字
一．数组公式
a) 数组就是一组数据，数组公式可以进行多重运算，减少了多次写于单元格的过程，可以实现常用公式较烦锁的操作，一步到位
二．删除数组公式
a) 因为数组公式不能删除一个，我们要删除数组公式要全部选中，当我们有时修改一个单元格的数组公式，不能退出时，大家记得按ESC键
三．一个单元格显示数组的情况
a) 由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值
四．数组分类
a) 横向数组；纵向数组，区域数组（多行多列），也可以按维来分，横向数组和纵向数组都属于一维数组，区域数组属于二维数，像这种｛1,2,3,1}就是常量数组
五．怎样查看数组的结果，当然是独孤九剑F9
六．数组成员中间有时有分号，有时用逗号是怎么回事？，答:横向数组用逗号分开，纵向数组用分号分开
七．常量数据在函数里的应用
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)
八．要么用某些函数来取其共性,如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))
九．.参数
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}就无法引用了
十．输入
a) 同时按下CTRL+SHIFT+ENTER，数组公式的外面会自动加上大括号{}予以区分
十一． 实例一：求表一区域F42:G44大于10数据和
a) 公式：{=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
十二． 实例二：求表二产品AA的总价 （如图25）
a) 公式：=SUM((C50:C54="AA")*(D50:D54)*(E50:E54))
a) 公式解释：=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
十三．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(),这就要看具体情况了。
十四．从文本里提取数字
a) 效果图28
=--MID(D7,MIN(FIND(ROW($A$1:$A$10)-1,D7&5/19,1)),LEN(D7)-(LENB(D7)-LEN(D7)))
公式解释：先输入函数Mid从中间提取字符，这个函数有三个参数，第一个参数好说，从那里取，从D7单元格取，第二参数从几个位置提取，这个头痛，为什么头痛呢，因为数字的位置没有规律，怎么办呢？当然要想办法，多看看，再想想，无非就是要找到第一个数字出现的位置，数字有那么多个，有10个阿拉伯数字（0123456789），想到了Find函数去找，以前我们学得时候是一个一个找，刚才我们不是学了数组，我们把10个数字全部一起放进去找，这就对了，这就是数组优势，怎样才能产生10个阿位伯数字呢，我们可以输入常量数组{0,1,2,3,4,5,6,7,8,9} 这个打的辛苦，因此我们可以用Row(A1:A10)-1,记得要给它绝对引用，10个数字一起拿进去找，有的单元格没有这10个阿拉伯数字公式会报错，因此用了D7&5/19，因为5/19会产生这个10个阿拉伯数字，当然1/17也会产生这10个阿拉伯数字，怎么记住它了，我叫大家一句话“我要喝酒”，也就5/19，Find查找之后，会得到一个数组{6;3;8;10;16;4;9;13;14;15}，也就是这10个阿位伯数字的位置，然后我们用Min函数把这个新数组{6;3;8;10;16;4;9;13;14;15}取出来是3，这个3就是数字1出现的位置。回过头来看Find函数三个参数，第一个查找用了Row($A$1:$A$10)-1,
第二个参数D7&5/19，第三鼐参数用1，表示从第一个位置开始查找。现在我们来看Mid函数的第三个参数，也就是要得到数字的个数，前面我们学过计算字符个数的函数Len和Lenb这两个函数，这两个函数区别是，Lenb函数汉字算2个，字母和数字算一个，因些我们得知有一个汉字就会多1，有二个汉字就会多2，这样可以用Lenb(D7)-Len(D7)得到汉字的个数，现在知道字的总数用Lenb(D7)，数字的个数就等于字的总数减去汉字的个数就是数字的个数，计算公式：
LEN(D7)-(LENB(D7)-LEN(D7)) ，函数Mid这个三个函数我们都解决了
最后有的学生可能会问：老师你的Mid前面怎么会还有两个负号呢？
呵呵，函数高手都是这样的，都会给新手，徒弟留一手，让新朋友，新手看懂的地方，不和大家开玩笑了，言归正传了，打个比方给大家听一下，9乘以-1等于-9，然后-9再乘以-1得到9，经过运算还是还来的9呢？，没有变，对，确是没有变，但是经过乘法运算，而我们的文本型数字经过运算就会转为数值型数字。这一点在大家要记住它，另外大家还要记住一点，一般情况我们的文本是左对齐，而数字是右对齐。我们的文本函数Right，Left，Mid取出来的数字都是文本型的数字，所以要记得把它转为数值型数字，不转的化，你求和结果都是0，你做图表时，做不出图表。切记
