41楼 gouweicao78 楼主
2006-5-5 17:44to 66楼：数组——可以这么理解：是由单个或多个元素按照行和列组成的具有“矩形”尺寸的元素的集合。比如A1:C4是一个4行3列的具有12个元素的数组，比如{1,2,3;3,4,5}是一个2行3列的具有6个元素的数组。
引用: 多单元格数组公式数组公式可以同时进行多个计算并返回一种或多种结果。
计算单个结果此类数组公式通过用一个数组公式代替多个公式的方式来简化工作表模式。
计算多个结果一些工作表函数返回多组数值，或需要将一组值作为一个参数。如果要使数组公式能计算出多个结果，则必须将数组输入到与数组参数具有相同的列数和行数的单元格区域中。——这个就是多单元格数组公式了。
首先，我们在A1输入=small({1,2,3,6},{2,4})直接回车或者按三键回车形成单个数组公式，可以看到返回的结果是2，实际上——返回的结果是{2,6}（用前面介绍的“独孤九剑”F9键在编辑栏可以查看得到的。把公式下拖右拖都一样，显示的都是2——这是因为普通公式只能显示返回数组的左上角的第一个元素。（注意：数组的尺寸、形状概念）
戏说Excel规定：在一个单元格只能显示一个元素，于是元素们要打架，谁都想争着先上镜头（一个单元格像是一个镜头），那怎么行！于是Excel还规定，元素按照自己的行、列的“位置”上镜头，谁也不许抢，各就各位！按下Ctrl+shift+enter结束就是电脑操作者跟Excel说“嘿！我是数组，要按照数组公式给我待遇哦！”
当选择多个单元格输入公式再按下三键结束时，也就是电脑操作者跟Excel说“嘿！我们是多单元格数组，注意给我留好镜头，兄弟们都想露脸呢！”
于是——假如=small({1,2,3,6},{2,4})在一个单元格输入，那么永远只有左上角那个抢得到镜头，假如在多个单元格输入而没有组成多单元格数组公式，那么也是永远只有左上角那个抢得到镜头，只有当组成多单元格数组公式的单元格的形状和尺寸和公式返回的数组的形状尺寸一模一样时，各个元素才能照出“全家福”来，如果尺寸多了，就会多出一些错误值或者别的。
再回来看楼主的问题A1:A4——这是一个4行1列的矩形，而=small({1,2,3,6},{1,2,3,4})则是1行4列的矩形，照不出全家福的，呵呵。应该是A1:D1才对。
不少人对同一个公式三种不同方式（即普通公式、数组公式、多单元格数组公式）返回的3个结果表示疑惑，这里，再用“摄影镜头”的角度来说一说吧：当它是普通公式的时候这个结果遵循隐含交叉的返回对应行的结果。——相当于你请了一些独立的摄影师想拍全家福，结果摄影师们的镜头转座生锈了，只能拍到他们各自镜头看得见的那个人（隐含交叉到了），如果镜头看不到，拍出来当然是错的。
当它是单个单元格数组公式的时候——相当于你请了一些独立的摄影师想拍全家福，结果摄影师们都只对着“数组左上角的元素”——他最抢镜头。所以洗出照片一看——大家都是一样。
当它是个多单元格数组公式的时候——相当于你请了1个摄影师想拍全家福，而且这个摄影师的镜头不错，全都包进去了（如果摄影师技术差一些[数组返回结果的个数与组成多单元格数组公式的单元格格数不一致]，就会漏掉几个人[单元格设少了]或者多拍到了边上的闲杂人[单元格设多了]）
42楼 gouweicao78 楼主
2006-5-8 16:05得到含有备注的算式的计算结果
我们知道宏表函数EVALUATE(formula_text)可以用来将文字计算式的转化为公式并计算结果，且常用于工程预算等
比如A1单元格输入=3+5*2-6^2之类的，我们既想在A列显示公式，又想兼得计算结果，这时候就可以用Evaluate函数了，方法：在B1单元格，插入〉名称〉定义一个名称比如Result=Evaluate(A1) 然后在B1输入=Result就可以得到上式的计算结果“-23”了。这并不难理解。
然而，出于一些实际需要，往往写公式的人要做一些文字说明让人清楚地了解计算式的由来，比如A1单元格 =20[長]*35[高]+5.5[長]*23[高]-0.9*1.5[扣門洞]，让人一目了然地知道计算依据，并且不容易犯漏项等错误，那么怎么得到去掉这些文字备注之后的计算结果呢？
同样，在B1插入〉名称〉定义名称：X=Evaluate(SUBSTITUTE(SUBSTITUTE(A1,'[','*ISTEXT(''['),']',']'')'))，然后在B1输入=X就可以了。
这么一个公式到底是什么意思呢，不要看着一堆的'['之类的符号发晕：首先Evaluate我们是知道的，Substitute也是知道的，不妨，在B1输入一个公式=SUBSTITUTE(SUBSTITUTE(A1,'[','*ISTEXT(''['),']',']'')')——既Evaluate里面的部分，看看得到了什么：
20*ISTEXT('[長]')*35*ISTEXT('[高]')+5.5*ISTEXT('[長]')*23*ISTEXT('[高]')-0.9*1.5*ISTEXT('[扣門洞]')
就是——再回头看公式：黄色部分就是把A1里的'['左边的中括号替换成*ISTEXT(['，绿色部分，就是把A1里的']'右中括号替换成]')，仅此而已！比如[高]变成了*istext('[高]')；
也许你会说——明明公式里是'*istext(''['（蓝色为一对双引号，把里面的东西作为字符串输入）红色部分是一对双引号，怎么得到的结果是单引号呢？呵呵：试想一下，加入红色部分是1个双引号，那么，它首先要和左边的蓝色双引号配对，就没办法把后面的左中括号包进去了。用这个反过来想就知道为什么这里是2个双引号而不是1个了。
通过上式解说，我们知道定义名称的Evaluate要计算些什么了，因为istext('[高]')是判断[高]这么个东西是否文本，当然答案是True了，20*True*35*True+……就是20*1*35*1……所以返回的正是我们想要得到的计算结果了。
引用: 再次对“庖丁解牛”说一下，要找准关节入手！括号的配对是一个函数成为完整个体的表现；逗号的配对，是一个函数的参数成为完整个体的表现；英文双引号的配对，是一串字符串成为完整个体的表现。
43楼 gouweicao78 楼主
2006-5-9 14:47三维引用跨多表查询
首先，读了43楼的Sumif三维引用跨多表合并之后，我们对Indirect引用多个表的能力有了较多的了解。Sumif条件求和可以了，那么根据关键字查询的Vlookup可不可以呢？下面我们就[实例1]和[实例2]来做简单解释吧：
=VLOOKUP($A3,INDIRECT(LOOKUP(1,0/(COUNTIF(INDIRECT({2,3,4}&'M!B5:B883'),$A3)),{2,3,4})&'M!B5:E883'),COLUMN(),)
关键部位（绿色部分），其中：Indirect({2,3,4}&'M!B5:B883')——引用了2M!B5:B883、3M!B5:B883、4M!B5:B883三个区域
countif(Range,$A3)——即Range里是否包含A3，返回的是0或者包含的个数，是一个1行3列的数组。
1/0返回#Div/0!错误，1/其他正数返回的是小于1的值（这部分在55楼及Lookup查找策略[函数讨论5]一帖中有了较详细的论述），
所以Lookup能依据2M、3M、4M表中到底哪个包含了A3的内容来返回对应的{2,3,4},如果不止一个表包含A3，则返回最后一个满足条件的记录。得到的是单值！
接着再用Indirect(Lookup得到的单值&'M!B5:E883')来引用Vlookup所要查找的区域，这个就不难理解。
44楼 ysw2 LV2
2006-5-16 09:37某日在坛中求得公式一只，如下
{=HYPERLINK('#库!b'&MATCH(2,1/(库!B1:B65000<>''))+1,'录入数据')}
可是俺只会照虎画猫，还有两处不明，请G老师指点
1、为何要用数组？
2、公式中MATCH(2,1/(库!B1:B65000<>''))+1是什么意思，为何将这个公式中的2改成其它值（如3或4）都不影响其结果？
另：在自定义格式中万元格式应是：#'.'#,可是这样只保留了一位小数，请问如何才能设置成为保留两位小数的万元格式？我试过了#'.'#,0却不行。请老师指点。
还：我在坛中看过一关于易失函数，贴版主说:易失函数有sumif、indirect、offset等等，另外如rand、now、today等也是，且有这类函数的工作表打开后没有任何变化关闭是仍问你是否保存，为什么在我的一个表中没有上述那些函数，打开后没做任何修改，关闭时仍询问是否保存？
45楼 gouweicao78 楼主
2006-5-16 10:32to ysw2：
1、{=HYPERLINK('#库!b'&MATCH(2,1/(库!B1:B65000<>''))+1,'录入数据')}——这是一个跳转单元格链接的公式，意在跳转到本工作簿（#号的用法）库表B列最后一个记录的下一行。
为什么用数组——match(2,1/(条件))——条件是个数组运算，match需要以数组形式来输入。可以改为普通公式，利用Lookup支持第二参数为数组的特性：=HYPERLINK('#库!b'&Lookup(2,1/(库!B1:B65000<>''),row(1:65000))+1,'录入数据')
2、Match(2,1/（条件）)与Lookup(2,1/(条件)……)或者Lookup(1,0/(条件)……)语法类似，因为“条件”数组返回的是逻辑值True和False组成的数组，n/True=n，n/False=#DIV/0！错误，——所以n/(条件)返回的是由n和错误组成的数组，在此数组中用match升序查找或者Lookup升序查找“一个比n大的值”，返回的是最后一个n出现的位置，最后一个n出现的位置意味着最后一个n/True，意味着最后一个True的位置，即最后一个满足“条件”的记录。所以你就是把2改为2.001都可以，呵呵，为了简洁，习惯上大家写2，1/或者1，0/
3、“在自定义格式中万元格式应是：#'.'#”不是吧？这个格式只能把最后一个数字显示为小数的效果！并不能达到万元格式，比如输入123显示为12.3，输入1234，显示为123.4；#是个占位符，如果你要显示2位则#'.'##。（我不太理解你关于“万元格式”的说法）
不好意思，刚才没注意格式#','#,后面的逗号了，误以为不是万元格式，惭愧。平时用的比较少，这个逗号是千分位，所以如果你用#','#,0则破坏了这个规则，相当于在“有千分位”的格式里再加上'.'小数点。可以显示1位（即原来那种）和4位小数，比如#'.'####4、你的表格有哪些函数？没附件我就不好判断了。会不会在定义名称里面。关于函数的易失性，请看http://club.excelhome.net/dispbbs.asp?boardid=3&id=108514一帖的讨论
46楼 hbhfgh4310 LV16
2006-5-16 23:28问题链接：http://club.excelhome.net/viewthread.php?tid=166704&px=0
楼主的问题复杂在款号列有空格，如果没有空格的话公式就可以写成C2=sumproduct((sheet2!A2:A17=A2)*(sheet2!B2:B17=B2)*(sheet2!C1:H1=c1)*sheet2!C2:H17)。
上述公式的运行机理是这样的，即表2中A2:A17等于A并且表2中B2:B17等于红，即(sheet2!A2:A17=A2)*(sheet2!B2:B17=B2)，它形成的是个列数组，即找出符合这两个条件的行次来。然后再用(sheet2!$C$1:$H$1=c$1)找出符合条件的列次，将两个公式相乘，即(sheet2!A2:A17=A2)*(sheet2!B2:B17=B2)*(sheet2!C1:H1=c1)得到的结果是16行乘6列的二维数组，在这个数组中凡是符合条件位置是“1”，不符合条件的是“0”，下面再用这个数组乘上sheet2!C2:H17，得到的结果就是所有符合条件的值，再用sumproduct进行求和处理，就得到了最后的结果。
楼主的问题没有这么简单，需要首先解决sheet2表的A列有空格的问题，我用lookup函数组建了一个进行填充款号的列数组。=LOOKUP(ROW(x),IF(x<>0,ROW(x)),x)，这里的x是动态引用区域的定义名称，x=OFFSET(Sheet2!$A$2,,,COUNTA(Sheet2!$B:$B)-1,)。关于=LOOKUP(ROW(x),IF(x<>0,ROW(x)),x)的原理是这样的，IF(x<>0,ROW(x))解释为如果x内(即A列)有空，就等于其对应的行号。=LOOKUP(ROW(x),IF(x<>0,ROW(x)),x)解释为查找行号在IF(x<>0,ROW(x))中的位置，返回x值。由于lookup的查找原理是查找小于等于被查找值的最大值，因此得到的结果就是填充过的一列数组了，即：{'A';'A';'B';'B';'C';'C';'B';'B';'A';'A';'D';'D';'B';'B';'C';'C'}。有了它剩下的工作就容易多了。
关于a_1、B_1、q_1是定义名称，a_1的结果就是刚才计算出的数组，B_1等同于sheet2!B2:B17，q_1等同于sheet2!C2:H17。
47楼 xg_an LV2
2006-5-19 09:02引用: 请参考：http://club.excelhome.net/dispbbs.asp?boardid=1&id=69018不打开源文件的跨工作簿引用
48楼 听风看雪 LV4
2006-5-19 22:54引用: 哎，该怎么说你好呢？前面尽量不要重开楼层发帖，现在你把原问题删除了，我楼下的解释不就没有“问题源”了吗？况且，这个新问题——你只要看看41楼非常相似的解释就能明白了，或者11楼附件的分析。如果还不行的话再问好吗？
49楼 gouweicao78 楼主
2006-5-19 23:09先使“独孤九剑”！
=SMALL(IF(FREQUENCY($C$2:$C$22,$C$2:$C$22),$C$2:$C$22),ROW(1:1))——在编辑栏“抹黑”Frequency部分如果左，按F9键得：
{6;7;7;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0}——共22行1列（别奇怪，看看帮助文件），表示C2:C22中数据的分布情况，6表示C2的值出现6次等等。
公式很短，基本不用庖丁解牛了，if({6;7;7;0;0……——Excel中True=1、False=0，反过来在if中0=False，任何非0值=True，所以利用Frequency对数值（只适用数值，所以文本可以用Frequency+match等转化）的分布频率求出不重复数值对应的位置。
帮助文件
引用: FREQUENCY(data_array,bins_array)Data_array 为一数组或对一组数值的引用，用来计算频率。如果 data_array 中不包含任何数值，函数 FREQUENCY 返回零数组。
Bins_array 为间隔的数组或对间隔的引用，该间隔用于对 data_array 中的数值进行分组。如果 bins_array 中不包含任何数值，函数 FREQUENCY 返回 data_array 中元素的个数。
说明
- 在选定相邻单元格区域（该区域用于显示返回的分布结果）后，函数 FREQUENCY 应以数组公式的形式输入。
- 返回的数组中的元素个数比 bins_array（数组）中的元素个数多 1。返回的数组中所多出来的元素表示超出最高间隔的数值个数。例如，如果要计算输入到三个单元格中的三个数值区间（间隔），请一定在四个单元格中输入 FREQUENCY 函数计算的结果。多出来的单元格将返回 data_array 中大于第三个间隔值的数值个数。
- 函数 FREQUENCY 将忽略空白单元格和文本。
联系客服
