爱睡猪的学习笔记整理（请勿灌水！谢绝“谢谢分享”）

爱睡猪

发现在爬贴的过程中一些心得和收获要及时整理，否则容易丢掉了。
1、2楼作为分类目录

函数与公式

欢迎路过的多看多指正。为了界面整洁方便大家阅读，
谢绝一切诸如“不错、支持、谢谢分享”之类的跟帖，
我心领了，谢谢大家！

[ 本帖最后由 爱睡猪 于 2010-5-7 10:52 编辑 ]

爱睡猪

其他方面的

meimei206

楼主，怎么没有看到内容？？是附件没有跟上吗？

爱睡猪

最近参加了胡版的数组特辑班，感觉比我想象得要难，从另一方面看，坚持下来，取得的进步也会比想象的大。
=========================================================================================
一、数组和数组公式的区别和联系

1、数组：我的理解，按照有序的形式组织起来的数据元素的集合称为数组。一个数组可以分解为多个数组元素，这些数组元素可以是单行数组、单列数组、也可以是区域数组。

2、数组公式：数组公式可以认为是Excel对公式和数组的一种扩充，换一句话说，是Excel公式在以数组为参数时的一种应用。

3、两者的联系：数组就是单元的集合或是一组处理的值集合。可以写一个以数组为参数的公式，即数组公式，就能通过这个单一的公式，执行多个输入的操作并产生多个结果——每个结果显示在一个单元中。数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元。数组的元素可多达6500个。

4、作用：Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。在胡版的引领下，我还意识到它的参数驱动的重要作用和意义，也就是要返回什么样的内存数组就构建什么样的数组参数。

5、特点：数组公式的特点就是所引用的参数是数组参数，包括区域数组和常量数组。执行多重计算,它返回的是一组数据结果。
　　详细的说：数组公式的参数是数组，即输入有多个值；输出结果可能是一个，也可能是多个（这一个或多个值是公式对多重输入进行复合运算而得到的新数组中的元素）。

6、输入：输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl＋Shift＋Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。

7、输出：由于数组公式是对数组进行运算，数组可以是一维的也可以是二维的。一维数组可以是垂直的也可以是水平的。经过运算后，得到的结果可能是一维的，也可能是多维的，存放在不同的单元格区域中。
　　在公式或函数中使用数组常量时，其它运算对象或参数应该和第一个数组具有相同的维数。必要时，Microsoft Excel 会将运算对象扩展，以符合操作需要的维数。每一个运算对象的行数必须和含有最多行的运算对象的行数一样，而列数也必须和含有最多列数对象的列数一样。
　　例如: = SUM({1,2,3}+4}）,则第二个数据并不是数组，而是一个数值，为了要和第一个数组相加，Excel 会自动将数值扩充成1 ×3 的数组。使用=SUM({1,2,3}+{4,4,4})做计算，得到的结果为1+4、2+4和3+4的和, 即18。
　　将数组公式输入单元格区域中时，所使用的维数应和这个公式计算所得数组维数相同。这样，Microsoft Excel 才能把计算所得的数组中的每一个数值放入数组区域的一个单元格内。
　　如果数组公式计算所得的数组比选定的数组区域还小，则 Microsoft Excel会将这个数组扩展，以便将它填入整个数组区域内。例如：={1,2;3,4}*2扩充后的公式就会变为={1,2;3,4}*{2,2;2,2}，则相应的计算结果为“2,4,6,8”。再如：输入公式={1,2;3,4}*{2,3}扩充后的公式就会变为={1,2;3,4}*{2,3;2,3} ，则相应的计算结果为“2,6,6,12”。
　　如果 Microsoft Excel 将一个数组扩展到可以填入比该数组公式大的区域内，而没有扩大值可用的单元格内，这样就会出现#N/A错误值。
　　例如：={1,2;3,4}+{1,2,3} 扩充后的公式就会变为={1,2,#N/A;3,4,#N/A}+{1,2,3} ，而相应的计算结果为“2,4,#N/A;4,6,#N/A”。
　　如果数组公式计算所得的数组比选定的数组区域还要大，则超过的值不会出现在工作表上。
　　由于一个单元格内只能储存一个数值,所以当结果是一组数据时,单元格只返回第一个值,，在其他不同的单元格上能够看到不同元素参与运算得到的不同结果。这一点正是我们所需要的。

8、编辑：数组包含数个单元格，这些单元格形成一个整体，所以，数组里的某一单元格不能单独编辑。在编辑数组前，必须先选取整个数组。移到数据编辑栏上按[F2]键或单击左键，使代表数组的括号消失，之后就可以编辑公式了。可以按F3调出事先定义好的名称，编辑完成后，三键结束。

[ 本帖最后由 爱睡猪 于 2010-5-7 10:59 编辑 ]

爱睡猪

rows()返回的是一个单值常量，row()返回的是一个单值数组，min(),max(),large(x,1),small(x,1),可将row()产生的单值数组转换成常量。
row(1:10)和row（a1:a10)都返回一个内存数组{1;2;3;4;5;6;7;8;9;10}，暂未发现区别
相关链接：http://club.excelhome.net/viewth ... p;page=2#pid1028496

column()和row()大同小异，后者产生列数组，前者产生行数组；另外当如要构建的内存数组较大时，利用转置可将列数组变成行数组，比如输入column(a:f)就不如transpose(row(1:6))直观
=ROW(INDIRECT("1:"&LEN(A13)))生成任意长度的连续序列，常与LEN配合使用,如MID提取指定字符串中每个字符时 =MID(A13,ROW(INDIRECT("1:"&LEN(A13))),1)

ROW()或COLUMN()有多种写法：                                       
        假定A1:A10里存放了10个数字，要在B11:B20里把它从小到大排列出来，有以下几个写法（自定义名称DATA1=A1:A10)：                               
        1, SMALL(DATA1,ROW(A1)),下拉
        2, SMALL(DATA1,ROW(1:1)),下拉
        3, SMALL(DATA1,ROW(INDIRECT("A"&ROW()-19))),下拉
        4, 多单元格数组公式：SMALL(DATA1,ROW(INDIRECT("1:10"))
        5, 多单元格数组公式：SMALL(DATA1,ROW(DATA1))
公式1和2,3的计算速度应该比4和5要慢，因为它有10个公式，要计算10次，而后两个公式只计算一次，就填入10个单元格；
另外，更致命的是，一旦删除数据区的某一行，将造成显示结果错误。特别是第一个公式，只要删除A1，就会错误。
公式4与5，删除数据区不会造成错误，速度快。
而公式4与5相比：公式4使用了易失函数INDIRECT，会引发重算，速度受到影响，而公式5则没有这个问题。

结论：在可能的情况下，使用多单元格数组公式SMALL(DATA1,ROW(DATA1))应该是最好的。

[ 本帖最后由 爱睡猪 于 2010-5-7 20:18 编辑 ]

爱睡猪

上面说过最简单的莫过于创建自然数内存数组了，如row(1:10),transpose(row(1:10))，不再赘述；

全1数组则利用指数运算创建：如row(1:10)^0

要得到如{1000,2000,3000,4000。。。}就可以用1000*row(1:n)

要得到如{1,10,100,1000...}可以用10^(row(1:n)-1)
同理，如{...,1000,100,10,1}可以用10^(n-row(1:n))

若需要二维数组也不难：如要得到{1,2,3,4,5,6;1,2,3,4,5,6;1,2,3,4,5,6;1,2,3,4,5,6;1,2,3,4,5,6}可以用=ROW(1:5)^0+TRANSPOSE(ROW(1:6))-1
要得到{1,2,3,4,5,6;7,8,9,10,11,12;13,14,15,16,17,18;19,20,21,22,23,24;25,26,27,28,29,30}可以用=(ROW(1:5)-1)*6+TRANSPOSE(ROW(1:6))
根据需要的结果构建参数。

[ 本帖最后由 爱睡猪 于 2010-5-7 12:06 编辑 ]

爱睡猪

当vlookup在调用第一参数左边的数据的时候需要用if({1,0}...或者choose({1,2}构建内存数组，其实也是运用的参数驱动，及根据需要改变if或者choose的第一参数。

这个发现就已经很让人欣喜了，当在胡版的数组功力修炼中看到lookup构建内存数组的能力的时候，真是有种醍醐灌顶豁然开朗的感觉。简述如下：
如a列的a1：a12是从a,b,c,d...l的12个字母，
=LOOKUP((ROW(1:2)-1)*6+TRANSPOSE(ROW(1:6)),ROW(1:12),A1:A12)构建2行6列的内存数组因为lookup的第一参数是{1,2,3,4,5,6;7,8,9,10,11,12}，第一参数决定了最后返回的数组结构为，{"a","b","c","d","e","f";"g","h","i","j","k","l"}；

=LOOKUP((ROW(1:3)-1)*4+TRANSPOSE(ROW(1:4)),ROW(A1:A12),A1:A12)构建的是3行4列的内存数组，
因为lookup的第一参数是{1,2,3,4;5,6,7,8;9,10,11,12}，第一参数决定了最后返回的数组结构为{"a","b","c","d";"e","f","g","h";"i","j","k","l"}。
我发现，构建内存数组时，无论是if、choose、还是lookup，关键就在于第一参数。

同理，offset函数只能创建内存数组，但是不能接收传给他的内存数组，因为他的的第一参数是Refence，单元格或区域引用，不是array（数组）

目前接触的函数有限，但是看得出来，把握好这些函数的第一参数就抓住了关键，掌握了主动权，第一参数是王道！

[ 本帖最后由 爱睡猪 于 2010-5-7 17:22 编辑 ]

冻豆腐

泼点冷水，这就是矩阵。线性代数

alwstr

提几个建议，希望不要被楼主认为是灌水
1  大篇大篇的文章看了很累，希望楼主在重点部分加特别颜色标出！
2  希望楼主在学习完数组，再多点其他的学习笔记。坚持哦！
（另，后续有意见，我会统一提在这一个回复上）

爱睡猪

感谢8楼50技术分和9楼双勋章的前辈！
========================================
讲课过程中，抛开内容，发现胡版的思路很清晰，逻辑关联很强。

我们遇到的问题好比一个大面包，当我们无从下口的时候，要看是不是他已经切好片了，如果没有，那就把他切开。这个过程也就是把大问题细化成模块，模块还可以细化成小模块，然后各个击破的过程。

这其中要有一点逆向思维，也就是如下三部曲：
1、先从终点回推，搞清楚想要的是什么；
2、从起点开始，目前的条件能创建出什么样的内存数组，如果没有条件，是不是可以自己创造条件；
3、从起点开始的思路和从终点逆推的思路如何交叉，找到结合点。

这中间有个小技巧就是可以根据需要自定义名称，然后在编辑公式的时候按F3调出名称，把需要的内存数组封装进名称，可以使编写公式时思路不被打断，也使写出来的公式结构更清晰。

[ 本帖最后由 爱睡猪 于 2010-5-7 17:45 编辑 ]