Excel数组
Excel数组
2009-09-03 12:55:02| 分类：excel|字号订阅
数组就是单元的集合或是一组处理的值集合。能写一个数组公式，即输入一个单个的公式，他执行多个输入的操作并产生多个结果??每个结果显示在一个单元中。数组公式能看成是有多重数值的公式。和单值公式的不同之处在于他能产生一个以上的结果。一个数组公式能占用一个或多个单元。数组的元素可多达6500个。
1 、了解数组
首先我们通过几个例子来说明数组是怎么工作的。我们能从图7-35中看到，在“B”列中的数据为销售量，在“C”列中的数据是销售单价，需求计算出每种产品的销售额和总的销售金额，一般的做法是计算出每种产品的销售额，然后再计算出总的销售额。不过如果我们改用数组，就能只键入一个公式来完成这些运算。
输入数组公式的步骤为：
选定要存入公式的单元格，在本例中我们选择“D4”单元格。输入公式
=SUM(B2:B4*C2:C4)，但不要按下[Enter]键（输入公式的方法和输入普通的公式相同）。按下[Shift]+[Ctrl]+[Enter]键。我们就会看到在公式外面加上了一对大括号“{}”，如图7-36所示。
在单元格“D”中的公式“=SUM(B2:B4*C2:C4)”, 表示“B2: B4”范围内的每一个单元格和“C2：C4”内相对应的单元格相乘，也就是把每个地区的销售量和销售单价相乘，相乘的结果共有3个数字，每个数字代表一个地区的销售额，而“SUM”函数将这些销售额相加，就得到了总的销售额。
下面我们再以使用数组计算3种产品的销售额为例，来说明怎么产生多个计算结果。其
操作过程如下：
(1) 选择“D2：D4”单元格区域，该区域中的每个单元格保存的销售金额。如图7-37所示。
(2) 在“D2”单元格中输入公式“=B2:B4*C2:C4”（不按[Enter]键）按下[Shift]+[Ctrl]+[Enter]”键，我们就能从图7-38中看到执行后的结果。同时我们能看到“D2”到“D4”的格中都会出现用大括弧“{ }”框住的函数式，这表示“D2” 到“D4”被当作一个单元格来处理，所以不能对“D2”到“D4”中的任一格作所有独立处理，必须针对整个数组来处理。
2、使用数组常数
我们也能在数组中使用常数值。这些值能放在数组公式中使用区域引用的地方。要在数据公式中使用数组常数，直接将该值输入到公式中并将他们放在括号里。例如，在图7-39中，就使用了数组常数进行计算。
常数数组能是一维的也能是二维的。一维数组能是垂直的也能是水平的。在一维水平数组中的元素用逗号分开。下面是个一维数组的例子。例如数组：{10,20,30,40,50}。在一维垂直数组中的元素用分号分开。在下面的例子是个6×1的数组，{100;200;300;400;500;600}。
对于二维数组，用逗号将一行内的元素分开，用分号将各行分开。下一个例子是“4 ×4”
的数组（由4行4列组成）：{100,200,300,400;110, … … ;130,230,330,440}。
注意：不能在数组公式中使用列出常数的方法列出单元引用、名称或公式。例如：
{2*3,3*3,4*3}因为列出了多个公式，是不可用的。{A1,B1,C1}因为列出多个引用，也是不可用的。不过能使用一个区域，例如{A1:C1}。
对于数组常量的内容，可由下列规则构成：
数组常量能是数字、文字、逻辑值或错误值。
数组常量中的数字，也能使用整数、小数或科学记数格式。
文字必须以双引号括住。
同一个数组常量中能含有不同类型的值。
数组常量中的值必须是常量，不能是公式。
数组常量不能含有货币符号、括号或百分比符号。
所输入的数组常量不得含有不同长度的行或列。
3、数组的编辑
数组包含数个单元格，这些单元格形成一个整体，所以，数组里的某一单元格不能独立编辑。在编辑数组前，必须先选取整个数组。
选取数组的步骤为：
(1) 选取数组中的任一单元格。
(2) 在“编辑”菜单中选择“定位”命令或按下[F5]键，出现一个“定位”对话框。按下“定位条件”按钮，出现一个定位条件对话框，如图7-40所示。
选择“当前数组”选项，最后按下“确定”按钮，就能看到数组被选定了。
编辑数组的步骤为:
选定要编辑的数组，移到数据编辑栏上按[F2]键或单击左键，使代表数组的括号消失，之后就能编辑公式了。编辑完成后，按下[Shift] +[Ctrl] + [Enter]键。
若要删除数组，其步骤为：选定要删除的数组，按[Ctrl]+[Delete]或选择编辑菜单中的“清除”命令。
4、数组的扩充
在公式或函数中使用数组常量时，其他运算对象或参数应该和第一个数组具有相同的维数。必要时，Microsoft Excel 会将运算对象扩展，以符合操作需要的维数。每一个运算对象的行数必须和含有最多行的运算对象的行数相同，而列数也必须和含有最多列数对象的列数相同。
例如: = SUM({1,2,3}+{4,5,6}）内的第一个数组为1×3,得到的结果为1+4、2+5和3+6的
和,也就是21。如果将公式写成= 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,#/A;1.2.#N/A} ，而相应的计算结果为“2,4,#N/A,4,6,#N/A”。
如果数组公式计算所得的数组比选定的数组区域还要大，则超过的值不会出目前工作表上。
EXCEL的六大“条件”功能
2009-09-03 14:04:20| 分类：excel|字号订阅
EXCEL的六大“条件”功能一、条件求和。
1、单条件求和：统计C1公司施工的工程总建筑面积，并将结果放在E18单元格中，我们只要在E18单元格中输入公式“=SUMIF（D2：D17,"C1公司",E2：E17）”即完成这一统计。
友情提醒：如果对EXCEL的函数不太熟悉，在单元格中直接输入公式有困难，我们可以用“插入函数”命令（或直接按工具栏上的“粘贴函数”命令按钮），选中你需要的函数后，按其提示操作即可完成公式的输入。
2、多条件求和：统计C2公司施工的质量等级为“合格”的工程总建筑面积，并将结果放在E19单元格中，我们用“条件求和”功能来实现：
①选“工具→向导→条件求和”命令（若没有此命令选项，可以用“加载宏”的方式来增加这一命令选项），在弹出的对话框中，按右下带“―”号的按钮（此时对话框变成类似工具条的窗口形式浮于桌面上），用鼠标选定D1：I17区域，并按窗口右边带红色箭头的按钮（恢复对话框状态）。
②按“下一步”，在弹出的对话框中，按“求和列”右边的下拉按钮选中“建筑面积”项，再分别按“条件列、运算符、比较值”右边的下拉按钮，依次选中“施工单位”、“=”（默认）、“C2公司”选项，最后按“添加条件”按钮。重复前述操作，将“条件列、运算符、比较值”设置为“质量等级”、“=”、“合格”，并按“添加条件”按钮。
③两次点击“下一步”，在弹出的对话框中，按右下带“―”号的按钮，用鼠标选定E19单元格，并按窗口右边带红色箭头的按钮。
④按“完成”按钮，此时符合条件的汇总结果将自动、准确地显示在E19单元格中。
友情提醒：上述操作实际上是输入了一个数组公式，我们也可以先在E19单元格中直接输入公式：=SUM(IF(D2:D17="C2公司",IF(I2:I17="合格",E2:E17)))，然后在按住Ctrl+Shift 键（非常关键！！！）的同时按下Enter键，也可以达到上述多条件求和之目的。
二、条件计数。
统计质量等级为“合格”工程的数目，并将结果存放在I18单元格中，在I18单元格中输入公式：=COUNTIF(I2:I17,"合格")，当按下确定按钮后，统计结果――数字5即自动在I18单元格中显示出来。
三、条件格式。
将工程造价在500万元（含500万元）以上的工程造价数值以红颜色显示出来：
①选中F2至F17单元格；②用“格式→条件格式”命令，打开“条件格式”对话框；
③按第二个方框旁的下拉按钮，选中“大于或等于”选项，再在后面的方框中输入数字500；
④按上述对话框中的“格式”按钮，打开“单元格格式”对话框，在“文字”卡片下，按“颜色”旁的下拉按钮，将文字颜色设置成红色后，按“确定”按钮关闭“单元格格式”对话框，回到“条件格式”对话框中；
⑤按“确定”按钮。此时造价大于500万元的工程，其造价数值将以红色显示在单元格中。
友情提醒：继续按上述“条件格式”对话框中的“添加”按钮，可以设置多级“条件”，如“工程造价大于500万元以红色显示，大于1000万元以蓝色显示”等（可以对一个单元格设置三个条件）。
四、条件函数。
我们在对竣工工程观感质量进行评定后，当观感得分率超过85%，该工程质量等级可以评定为“优良”。
①在I2单元格中输入公式：=IF(H2>=85,"优良",IF(H2>0,"合格","未竣工"))；
②选中I2单元格，将鼠标移至该单元格右下角成细十字线时（我们称之为“填充柄”），按住左键向下拖拉，将上公式复制到I列下面的单元格中；
③以后当工程竣工后，我们将该工程的观感得分率填入H列内相应的单元格中，则质量等级自动显示在相应工程的I列单元格中（若没有填观感得分率，则I列相应单元格中显示“未竣工”字样）。
友情提醒：在第①步输入公式时，条件判断数字按从大到小的顺序排列，否则会出错。
五、条件查找。
当工程基本情况表中登记的工程非常之多时，如果我们用普通浏览的方式查找某个具体的工程是非常困难的，此时我们可以用“查找”功能来快速定位：
①用“编辑查找”命令，打开“查找”对话框；
②在“查找内容”下面的方框中输入该工程的关键词；
③对“搜索方式、搜索范围”作适当设置后，连续按“查找下一个”按钮，符合模糊条件的单元格依次被选中，即可快速查找到某个具体的工程。
友情提醒：在“查找内容”下面的方框中输入的内容不一定非得与某一单元格数值完成相同的文本，如查找“C1公司”的某个工程，只要输入“C1”即可以进行模糊查找了。
六、条件筛选。
如果要打印C1公司施工的工程基本情况一览表，我们可以用“自动筛选”功能来实现：
①选中表格中的任一单元格，用“数据→筛选→自动筛选”命令，启动“自动筛选”功能（列标题旁出现一个下拉按钮）；
②按“施工单位”旁的下拉按钮，选中C1公司，则表格中只显示C1公司所施工的工程；
③接上打印机，装上打印纸，按“打印”按钮，即可打印出C1公司施工的工程基本情况一览表；
④打印结束后，重复第①的操作关闭“自动筛选”功能，则全部工程又重新显示在表格中。
友情提醒：此处的“条件筛选”可以进行“多级”筛选，如再上述筛选的基础上，再按“进度”旁的下拉按钮，选中“在建”选项，则表格中只剩下“C1公司”施工的所有“在建”工程。
其实，EXCEL的条件功能还有很多，如果有感兴趣的朋友不妨一试，若发现其什么更好的功能，别忘了告诉一声，我在此先谢过了。[
这是一位网友提给我的问题，说给大家看看；
工作簿内一共有两个工作表Sheet1和Sheet2，先说Sheei1，如下
ＡＢＣ
货号序号名称
1011车背带
1012合前片
1013合后片
1014车手带
1015车边片
1016合前袋
1017车后手带
2021车前片链
2022车前袋
2023合包
2024车后片
2025车手垫
2026合前袋
表Sheet2如下：
ＡＢＣ
货号序号名称
1015[此单元格空]
1012[此单元格空]
1013[此单元格空]
1017[此单元格空]
2023[此单元格空]
2021[此单元格空]
要求根据Sheet2内容，查找Sheet1，在Sheet2的名称列填写入正确的内容。
题目分析：
根据要求，如果要在Sheet2某一行n填入正确的名称，首先需要在Sheet1找到这样的行：该行第一列内容等于Sheet2某行n第一列，该行第二列内容等于Sheet2某行n 第二列。
因为涉及到数据的查找，拟采用数组公式实现。
1、首先定义名称：
（定义名称使用菜单：插入-名称）
dataA=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
dataB=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
这样做的好处是在于无论Sheet1添加了多少行，dataA始终包括A列有内容的行，这样比起直接使用A:A整列，要节约很多的计算时间。dataB同理。
2、匹配条件
先匹配A列，选中Sheet2！D2:D13（因为原始数据从2~13行），在编辑栏输入=IF(dataA=A2,1,0)
用Ctrl+Shift+回车，这样可以看到凡是与Sheet2！A2匹配的Sheet1的行，在这里都变成了1，而不匹配的则是0。
同理，在Sheet2！E2:E13输入
=IF(dataB=B2,1,0)
这样我们在D、E两列就得到了分别与Sheet2!A和Sheet2!B列匹配的数组。
3、合并匹配条件
我们需要的是两个条件的与运算，因此使用乘法是最好的，只有两个条件同时成立，1*1=1，否则两个乘数里面至少有1个为0，结果为0。
well，在Sheet2!F2:F13中输入数组公式：
=IF((dataA=A2)*(dataB=B2),1,0)
如此一来，就得到了一个新的由1和0组成的数组，某元素对应Sheet1的行如果能够匹配，该元素为1，否则为0。
4、检索行号
有了这个数组，我们需要得到匹配出来的行号，因为数组是由若干0和一个1组成的，所以我们只需要使用MATCH函数，就可以得到与Sheet2!An匹配的Sheet1的行号。
在Sheet2!G1输入
=MATCH（1，IF((dataA=A2)*(dataB=B2),1,0),1）
注意此处虽然仅仅是在一个单元格里面输入公式，但仍然是一个数组公式，需要使用Ctrl+Shift+Enter。
结果就是Sheet1中匹配的行号。
5、检索结果
有了行号，检索结果就很容易了，我们使用INDIRECT函数。
在Sheet2!C2输入数组公式：
=INDIRECT("Sheet1!$C$" & MATCH（1，
IF((dataA=A2)*(dataB=B2),1,0),1）)
注意依然使用Ctrl+Shift+Enter，正确的结果就出现了。
对于下面的行，只需要拖动填充句柄复制公式就可以了。
另外别忘了删除掉用来演示的D、E、F、G列的已经不再需要的公式。