Office中国论坛/Access中国论坛

标题: [系列]EXCEL应用技巧实战（二）数组公式 [打印本页]
作者: 老鬼    时间: 2004-12-22 04:23
标题: [系列]EXCEL应用技巧实战（二）数组公式
< align=center><v:shapetype> 数组公式简介</v:shapetype>

< align=left><v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" preferrelative="t" spt="75" coordsize="21600,21600">为了便于说明，该文我将自己编写，以自己的理解来说明。</v:shapetype>

< align=left><v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" preferrelative="t" spt="75" coordsize="21600,21600">一、数组长什么样？</v:shapetype><v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" preferrelative="t" spt="75" coordsize="21600,21600">

<;P >自有考试开始，我最讨厌的就是名词解释，所以数组的定义我就不说了。我们直接看看数组长什么样。

<;P >

<;P >数组1.{1,2,3,4,5,2,8,4}

<;P >数组2.{1;2;3;4;5;2;8;4}

<;P >数组3.{1,2,3;8,9,6}

<;P >

<;P >第一种数组，称作一维数组，可以理解成一行数据，如A1:H1，如下图：  [attach]8223[/attach]

<;P >第二种数组，也是一维数组，可以理解成一列数据，如A1:A8，如下图：[attach]8224[/attach]

<;P >第三种数组，是二维数组，可以理解成多行多列的数据，如A1:B8，如下图：</v:shapetype>[attach]8225[/attach]

（注：所谓的“维”，是一种空间的概念。一维直线，二维平面，三维立体。理论上讲，数组是无数多维的。但EXCEL是一种平面的结构，所以数组公式里的“数组”，我们理解到二维就可以了。）

<;P >二、数组怎么运算？

<;P >

<;P >数字的数组可以进行“加、减、乘、除”的运算，返加的是计算的结果；文本或数字的数组可以进行“比较”的运算，返回逻辑值“TRUE、FALSE”或“1、0”，也可以进行“&”的运算，返回合并后的结果。

<;P >

<;P >1.数字的数学运算。

<;P >

<;P >数字数组{5,2,8,4}与另一个数字数组{1,2,3,4}的加法运算如下：

<;P >

<;P >{5,2,8,4}+{1,2,3,4}={5+1,2+2,8+3,4+4}={6,4,11,8}

<;P >

<;P >其余的乘、除、减的运算与加法雷同。都是对应数字与对应数字运算后返回。

<;P >

<;P >2.文本计算。

<;P >

<;P >文本数组{a,b,c,d,e}与另一文本数组{a,d,e,d,b}的比较运算如下：

<;P >

<;P >（{a,b,c,d,e}={a,d,e,d,b}）={true,false,false,true,false}

<;P >

<;P >“&”的运算如下：

<;P >

<;P >{a,b,c,d,e}&{a,d,e,d,b}={aa,bd,ce,dd,eb}

<;P >

<;P ><FONT face="Times New Roman">(注：数组公式在输入完成后，要按CTRL+SHIFT+ENTER组合键，才能返回结果。一个单元格只能返回一个结果，如果要返回全部结果，必须选定具有与返回数组大小一样的区域，然后再输入数组公式，再按组合键。如果在要一个单元格内查看返回的整个数组，可以在输入公式后按F9键，将出现返回的结果。)

<;P >

<;P >三、常用到数组的一些函数

<;P >

<;P >1.if函数。形式是：if(A1:H1=A3:H3,A5:H5,A7:H7)，其运算原理是，A1:H1=A3:H3返回逻辑数组。TRUE时，选A5:H5中对应的数，FALSE时，选<FONT face="Times New Roman">A7:H7</F
作者: wang1950317    时间: 2004-12-24 04:51
我还没有参透其中的奥妙，也许我现在工作中的某些内容就可以用得上。伟大啊，老鬼!
作者: juyouhh    时间: 2004-12-25 01:08
关于练习题，ERASER大师的方案可能是最好的了，至今仍然没有其他方案可以望其项背。所以不必在这上面多费心思。斑竹应该出一些较为简单的练习供大家来练手，毕竟新手多、生手多。建议如有不当，还望见谅。
作者: 老鬼    时间: 2004-12-25 02:55
以下是引用juyouhh在2004-12-24 17:08:27的发言：

关于练习题，ERASER大师的方案可能是最好的了，至今仍然没有其他方案可以望其项背。所以不必在这上面多费心思。斑竹应该出一些较为简单的练习供大家来练手，毕竟新手多、生手多。建议如有不当，还望见谅。



你的提议有是道理的。其实简单的东西如果在现实中经常用到EXCEL，就会经常遇到，所以很多人都会主动提问。反而是已经有点基础的网友，往往只用到简单的东西，复杂一点的没用过，经常遇到了就不知怎么办，而且甚至以为是不能解决了。

其实我出的题目应用性不是很强，也不是很难，但对于数组的应用很有代表性。如果能解决和理解，我想对数组的认识及其运用都会加深不少。相信在网友的讨论下，大家都会取得长足的进步。

最后欢迎juyouhh提出更多的建议！
作者: juyouhh    时间: 2004-12-25 04:11
谢谢斑竹的关注。我的意思是最好先进行一些入门级的练习，循序渐进的好。
作者: 老鬼    时间: 2004-12-25 04:57
没关系，这个贴就专门说数组的。有比较简单的入门题目大家也可以提出来。我先提一个最简单的：请列出与SUMIF()函数功能雷同的的数组公式。
作者: 老鬼    时间: 2004-12-25 22:18
再来两道题：1、请列出与COUNTIF()函数功能雷同的数组公式。2、有一组产品数据A1:B10。A1:A10是产品名称，B1:B10是规格型号。请用数据公式求出产品名称是PRD、规格型号是K01的产品在这组数据中出现的次数。
作者: 情比金坚    时间: 2004-12-28 02:35
我的比较简单：左右两列数组，找出它们中重复的值
作者: juyouhh    时间: 2004-12-28 22:37
以下是引用情比金坚在2004-12-27 18:34:34的发言：



我的比较简单：

左右两列数组，找出它们中重复的值



这个其实并不简单，跟老鬼的第一个练习难度相当。提供一种解法：

=INDEX(B$1:B$10,SMALL(IF(((COUNTIF(A$1:A$10,B$1:B$10)>0)*ROW($110))=ROW($110),ROW($110),"0"),ROW(A1)))
作者: 情比金坚    时间: 2004-12-29 01:03
你想复杂了吧，=COUNTIF($A$1A$10,B1)，复制到B列全部，谁>0,就重复了。

[此贴子已经被作者于2004-12-28 17:13:50编辑过]

作者: juyouhh    时间: 2004-12-29 01:24
没看清题意，你说的是“找出”，我的是“列出”，不重复的筛掉。这个帖子是探讨数组公式的，所以“想复杂了”。
作者: 一个人的夜    时间: 2005-1-3 03:12
以下是引用juyouhh在2004-12-28 14:37:27的发言：







这个其实并不简单，跟老鬼的第一个练习难度相当。提供一种解法：

=INDEX(B$1:B$10,SMALL(IF(((COUNTIF(A$1:A$10,B$1:B$10)>0)*ROW($110))=ROW($110),ROW($110),"0"),ROW(A1)))

对于B列重复出现的数还是取出来了.跟情比金坚的犯了同样的一个错误.

其实题目应该这样出:

取出B列重复于A列的不重复的值.
作者: 一个人的夜    时间: 2005-1-3 03:17
问题是：

用数组公式筛选出一组带有重复值的数组中的不重复部分。如数组{A,B,C,A,A,C,B,C,A},用数组运算后返回不重复的部分{A,B,C}。注意，必须用数组与函数运算，不能使用EXCEL中的高级筛选、透视表或其他相关功能！这个方法有很多了,如LOOKUP(),INDEX+MATCH都可以
作者: 一个人的夜    时间: 2005-1-3 03:25
2、有一组产品数据A1:B10。A1:A10是产品名称，B1:B10是规格型号。请用数据公式求出产品名称是PRD、规格型号是K01的产品在这组数据中出现的次数。这个问题的答案,我相信在你传上来的文件里应该有吧.SUM()数组或SUMPRODUCT()都可以
作者: 老鬼    时间: 2005-1-3 05:02
欢迎一个人的夜。能不能把你的答案和解题的思路写出来，这样大家可以相互学习。
作者: wang1950317    时间: 2005-1-12 01:23
数组公式看了真是晕，我下面图中的费用合计是否可以使用这些公式来做？这只是示例A-E列，实际的表中有近30列，表中的数据与计算无关。单元格中只要有数字的均为一项，一项价格在图中示明了。

第二个问题：数组公式可否在Access中使用？

[attach]8551[/attach]

[em06]
作者: 老鬼    时间: 2005-1-12 04:24
你的要求用数组公式解最好不过了，你原来的答案好像是错的：[attach]8553[/attach]



数组公式是不能在ACCESS中使用的。
作者: wang1950317    时间: 2005-1-12 06:39
老鬼版主：我的公式不对，但费用列结果是对的，单元格中有数据的算一项，其中数据不参与计算。那么各行怎样“挑出”有数据的单元格来计算呢？[attach]8556[/attach]

作者: 老鬼    时间: 2005-1-12 07:25
那就对了：[attach]8557[/attach]

作者: wang1950317    时间: 2005-1-12 17:21
真好!数组公式的确很奥妙!可能我的许多计算能用得上!谢谢老鬼版主!
作者: xdcow    时间: 2005-3-19 18:40
版主,多讲一点数组吧,我是新手,想学习数组.请推荐一本这么方面的教程吧
作者: 老鬼    时间: 2005-3-21 04:14
至今我好像还没在市场上看到有专门介绍数组公式的书
作者: zzm7152    时间: 2005-6-17 06:32
老鬼，你好！你第一道题，我想到一个公式，但结果不是很理想，望指教！公式是：在A1:A9建数组{A,B,C,A,A,C,B,C,A},在B1:B9建数组{1,2,3,4,5,6,7,8,9}，在C1:C9输入公式{=INDEX(A1:A9,MATCH(B1:B9,COUNTIF(A1:A9,A1:A9),0))}但结果中有N/A错误值和字母顺序不定。请指点。
作者: 亚双木    时间: 2006-11-10 20:42
好帖不要给埋了，拉上来先。
作者: liaurn    时间: 2006-11-14 06:35
回答个老鬼在7楼提出的第二个问题



{=SAM(($A1A10="RD")*($B1B10="K01"))}

其实老鬼的第一个问题是别有用心的,能作出来对理解数组有帮助呀,想学数组的就做下看看
作者: liaurn    时间: 2006-11-14 07:00
看了老鬼留的3个例子  回个"写个与COUNTIF() 作用相同的数组

{=SUM(IF(A1:A10="RD",1,0))}

好贴呀 别沉





欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/) Powered by Discuz! X3.3