发布时间 : 星期日 文章佛山小老鼠说Ecxel函数更新完毕开始阅读
佛山小老鼠说Excel函数
第九讲 数组基础知识和从文本里提取数字
一.数组公式
a)
数组就是一组数据,数组公式可以进行多重运算,减少了多次写于单元格的过程,可以实现常用公式较烦锁的操作,一步到位
二.删除数组公式
a)
因为数组公式不能删除一个,我们要删除数组公式要全部选中,当我们有时修改一个单元格的数组公式,不能退出时,大家记得按ESC键
三.一个单元格显示数组的情况
a)
由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值
四.数组分类
a)
横向数组;纵向数组,区域数组(多行多列),也可以按维来分,横向数组和纵向数组都属于一维数组,区域数组属于二维数,像这种{1,2,3,1}就是常量数组
五.怎样查看数组的结果,当然是独孤九剑F9
六.数组成员中间有时有分号,有时用逗号是怎么回事?,答:横向数组用逗号分开,纵
向数组用分号分开 七.常量数据在函数里的应用
a) b) c) d)
23 =INDEX({23,24,25,22},1,1) 24=INDEX({23,24,25,22},1,2) 25=INDEX({23,24,25,22},1,3) 22=INDEX({23,24,25,22},1,4)
八.要么用某些函数来取其共性,如SUM Max/Min,Small/Large等
a) b) c) d)
94=Sum({23,24,25,22}) 25=Max({23,24,25,22}) 22=Min({23,24,25,23})
25=Large({23,24,25,22},ROW(A1))
第 26 页 共 41 页
佛山小老鼠说Excel函数
e) f) g) h) 九..参数
a)
数组公式最大的特征就是所引用的参数是数组参数,包括区域数组和常量数组。 b) c) d) 十.输入
a)
同时按下CTRL+SHIFT+ENTER,数组公式的外面会自动加上大括号{}予以区分
十一.
a)
实例一:求表一区域F42:G44大于10数据和 公式:{=SUM((F42:G44>10)*(F42:G44))}
22 =SMALL({23,24,25,22},ROW(A1)) 23=SMALL({23,24,25,22},ROW(A2)) 24=SMALL({23,24,25,22},ROW(A3)) 25=SMALL({23,24,25,22},ROW(A4))
区域数组,是一个矩形的单元格区域,如 $A$1:$D$5
常量数组,是一组给定的常量,如{1,2,3}或{1;2;3}或{1,2,3;1,2,3} 数组公式中的参数必须为\矩形\如{1,2,3;1,2}就无法引用了
公式解释: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 十二.
a)
实例二:求表二产品AA的总价 (如图25) 公式:=SUM((C50:C54=\
图 8
第 27 页 共 41 页
佛山小老鼠说Excel函数
b)
运算过程如图26,27所示
图 9
图 10
c)
公式解释:=SUM((C50:C54=\先用判断区域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 十三.
a)
Row()函数在数组公式中的运用
谈到数组公式,我们不得不说一下ROW()这个函数,它在数组公式中起到了很大的作用,许多公式中都需要用到它来作为参数 b)
我们先来做一个题目:求正整数列1,2,3,4??100这100个数字之和(首先假设你不知道等差数列求和公式,呵呵),=SUM(ROW($A$1:$A$100)) c)
我们利用Row(A1:A100)来产生1到100自然数,然后用Sum求和。我们都知道,ROW()是用于返回单元格行号的函数,通常它只能引用一个参数。但是在数组公式中,该函数就能引用多个单元格作为参数,对于整个引用区域进行分别运算,从而就能返回一组数据
第 28 页 共 41 页
佛山小老鼠说Excel函数
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
图 11
b)
使用的公式
=--MID(D7,MIN(FIND(ROW($A$1:$A$10)-1,D7&5/19,1)),LEN(D7)-(LENB(D7)-LEN(D7)))
公式解释:先输入函数Mid从中间提取字符,这个函数有三个参数,第一个参数好说,从那里取,从D7单元格取,第二参数从几个位置提取,这个头痛,为什么头痛呢,因
第 29 页 共 41 页