windyli 的BLOG
用户名：windyli
文章数：54
评论数：526
访问量：362203
注册日期：
[匿名]海：
51CTO推荐博文
&&&&&&& 上次写了篇《篇》，不觉已十多天过去了，今天补上“精通篇”。当然说“精通”可能有点过了，但是希望大家通过这两篇博文能够真正认识“数组公式”，并且在工作中使用数组公式帮我们解决实际问题。
Excel数组公式从入门到精通之精通篇
&&&&&&& 一、课程回忆
&&&&&&& 什么是数组公式呢？顾名思义就是公式中包含数组的了，详细含义请参看前文。但这里重点提醒的一点就是，如果要使用数组公式，在编辑栏输入完公式以后一定要按下“Ctrl+Shift+Enter”组合键，使编辑栏的公式处在“{}”之中。
&&&&&&& 二、数组公式继续深入
&&&&&&& 印象中是好几年前了，当时看过的一篇扫盲贴中，作者举的例子真是太实用了。具体细节记不太清楚了，大致意思就是使用函数计算1到100的和。这里同样以此为例。
&&&&&&& 1.求1到100的和
&&&&&&& 在往下看之前，大家想一下，如果让你来处理该如何来处理呢？只用一个函数解决1到100的和，当然也可以是甚至更多。讨论具体的数值没有太大意义，此处只是希望通过此例让大家更进一步的了解数组公式的用法。
&&&&&&& 解答：{=SUM(ROW(1:100))}
&&&&&&& 问题分析：求1到100的和，答案是5050（小学生都知道^-^），但Excel必须是你告诉了它正确的方法，它才能知道。计算从1到100的和，实际上就是计算1+2+3+4+……+98+99+100，好了，答案出来了，在编辑栏中输入“＝sum(1+2+3+4+……+98+99+100)”。相信聪明的一定对此答案不满意，虽然能得到正确的结果，但很明显是“错误”的方法。
&&&&&&& 要得到1到100的正确数列，最简单的方法就是使用Row()或是Column()函数，由于个人习惯，我比较习惯于Row()，所以这里以Row()函数为例。
&&&&&& 熟悉Row()函数：在A1单元格中输入“＝Row()”，使用填充柄填充至A5，看到什么结果？是不是每一个单元格中值就是其对应的行数。
650) this.width=650;" onclick='window.open("/viewpic.php?refimg=" + this.src)' border="0" alt="" src="/attachment/885404.jpg" />
&&&&&&& 惊喜：Row()表示单前行，如果使用Row(1:100)就表示一个数组，其中包含的便是第一到第一百行的行号，即1、2、3、……、98、99、100这些数值，现在我们就把这个数组应用到公式中。
&&&&&&& 在工作表的任意一个单元格中输入“=sum(Row(1:100))”，然后按Ctrl+Shift+Enter组合键，你会惊喜的发现，我们要的结果出现了。
650) this.width=650;" onclick='window.open("/viewpic.php?refimg=" + this.src)' border="0" alt="" src="/attachment/314102.jpg" />
&&&&&&& 2.在Excel2003中享受“SUMIFS”
&&&&&&& SUMIF函数应该很多人都用过，非常好用。但如果遇到多条件判断的怎么办呢？从Office 2007开始，引入了SUMIFS函数，可以解决这种多条件求和问题。但如果仍然使用Office 2003怎么办呢？其实使用SUM、IF再结合数组公式即可实现SUMIFS的效果。
&&&&&&& 如下图所示，某教师有一张任教的几个班级的学生成绩表。650) this.width=650;" onclick='window.open("/viewpic.php?refimg=" + this.src)' border="0" alt="" src="/attachment/941472.jpg" />
&&&&&&& 任务：统计出“一班”、“二班”共计多少人？
&&&&&&& 此题要如何解决？SUMIF用两次？或是COUNT用两次？这里还是演示数组公式的用法，所以先用SUM和IF组合的形式。
&&&&&&& 在任一单元格中输入“=SUM(IF((A2:A12="一班")+(A2:A12="二班"),1,0))”公式按下回车键，是不是发现结果是“#VALUE！”，再次进入编辑栏然后按下“Ctrl+Shift+Enter”快捷键，是不是发现正确的结果出来了？
650) this.width=650;" onclick='window.open("/viewpic.php?refimg=" + this.src)' border="0" alt="" src="/attachment/142112.jpg" />
&&&&&&& 这里再次解释一下这个公式“{=SUM(IF((A2:A12="一班")+(A2:A12="二班"),1,0))}”，外侧SUM没什么好用的了，就是求（）内各数的和。中间的“IF((A2:A12="一班")+(A2:A12="二班"),1,0))”的运算过程是这样的，判断A2:A12区域内单元格的值是否是“一班”，如果是则结果为1，则此公式计算的结果依次是“1、0、0、1、0、0、1、0、0、1、0”，因为第一个条件为真，第二个条件肯定就不为真了，因为一个单元格不可能同时等于“一班”和“二班”，所以第一个数组就是“1、0、0、1、0、0、1、0、0、1、0”。这时再判断A2:A12区域内单元格的值是否是“二班”，如果是结果则为1，否则为0，所以这个数组条件计算的结果就是“0、1、0、0、0、1、0、1、0、0、0”，中间的加号就是将这两个数组相加，也就是说最终的数组为“1、1、0、1、0、1、1、1、0、1、0”，然后使用SUM求和，结果就为7了。
650) this.width=650;" onclick='window.open("/viewpic.php?refimg=" + this.src)' border="0" alt="" src="/attachment/226602.jpg" />
&&&&&&& 从上面的图中标注可以看出，所以的公式我全部使用了数组（A2:A12这就是一个数组），并且上图上的没有使用数组公式的公式中的数组全部可以使用单个单元格替代，之所以全部列出，还是希望大家更好的理解一下数组。
&&&&&&& 在Excel中，数组如果不放在数组公式中使用，通常数组在特定单元格中只代表与其特定单元格所对应的一个值（数组中的一个元素），放在数组公式中使用时，通常整个数组元素都会参与运算。
&&&&&&& 三、数组公式精通
&&&&&&& 这里是一个实际工作中的例子，只是我稍微变化了一下，还是SUM应用的例子。
&&&&&&& 需求：如下图所示，现在要统计员工张三在1号加工所有机器的“实绩”，也就是说在右侧的数据中先过滤日期为1，然后再过滤人员为张三的数据，最后统计实绩的结果。如果使用一个公式完成这一需求，你能想到吗？当然SUMIFS是除外的，因为SUMIFS是Office 2007以后的产物。
650) this.width=650;" onclick='window.open("/viewpic.php?refimg=" + this.src)' border="0" alt="" src="/attachment/238256.jpg" />
&&&&&&& 答案：在上图所示的C2单元格中输入“=SUM((E2:E21=A2)*(G2:G21=B2)*(H2:H21))”公式，然后按下“Ctrl+Shift+Enter”组合键，你会发现想要的结果已经出现了。数组公式就是这么简单，解决问题也是这么简单。
&&&&&&& 这次用的公式可以看出，比之前用的公式还要简单，连IF都不要了，实际上这里的“＝”符号就是起到了一个类似IF的效果。
&&&&&&& 这里再说明一下公式的执行过程，公式中E2:E21表示数组区域，这个相信已经不需要再说明了，放到数组公式中就是依次取数组中的各个数值，也就是依次取日期中的值。E2:E21=A2，实际上就是拿日期中的每一个值依次与A2中的日期进行比对，如果相等则结果为True，即1，如果不相等则为False即为0。到了这里也许你有一点明白了，如果第一不相等，则后面的无需再继续下去了，因为公式里用的全部是“*”乘积符号，任何数乘0等于0。如果此项符合再继续判断G2:G21区域，也就是用姓名依次比对，如果和B2中的姓名相同，则为Ture，即1，如果为False，即0，继续下一个回合。如果此项也为Ture，很明显前面两项的结果为1*1＝1，再乘以H2:H21数组中对应的数字，即符合条件的“实绩”，以第一个符合条件的第一条记录为例，在数组公式运行的第一个回合为SUM(1*1*234)，结果当然为234了，然后再依次完成整个数组的运算，我们最终的目的就达到了。
&&&&&&& 数组公式非常有用，效率也高，但真正的理解、熟练掌握也不是一件很容易的事。但大家记住数组中的数据是一一对应的，放到数组公式中使用时，数组中的数据会按顺序依次参与相应的运算。
&&&&&&& 希望大家能够慢慢的理解、贯通。本文出自 “” 博客，请务必保留此出处
了这篇文章
类别：┆阅读(0)┆评论(0)
21:06:18 21:18:44 23:47:07 23:47:56 18:17:17 23:19:58 16:33:47EXCEL应用与数组_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
评价文档：
喜欢此文档的还喜欢
EXCEL应用与数组
阅读已结束，如果下载本文需要使用
想免费下载本文？
把文档贴到Blog、BBS或个人站等：
普通尺寸(450*500pix)
较大尺寸(630*500pix)
你可能喜欢查看: 1590|回复: 4
阅读权限1UID379930函数0 关最后登录经验59 点在线时间5 小时VBA0 关分享记录好友技巧0 关相册主题段位0 段金币62 个积分59精华0帖子日志注册时间
幼儿园小班, 积分 59, 距离下一级还需 41 积分
金币62 个积分59帖子
希望在sheet2里按照格式，自动填充公式，显示sheet1中每日的四个数据，谢谢大家
(14.21 KB, 下载次数: 27)
16:13 上传
点击文件名下载附件
阅读权限16UID495406函数3 关最后登录经验5850 点在线时间703 小时VBA0 关分享记录好友技巧0 关相册主题段位1 段金币3112 个积分5854精华0帖子日志注册时间
大学1年级, 积分 5854, 距离下一级还需 146 积分
金币3112 个积分5854帖子
本帖最后由 5blessyou 于
16:25 编辑
C3=OFFSET(Sheet1!$M$2,COLUMN(A1),ROW(A1))复制代码未考虑时间顺序，即两个表时间顺序完全一致
16:25 上传
点击文件名下载附件
15.91 KB, 下载次数: 20
数据处理※报表定制※数据分析
点击咨询：
阅读权限6UID3664函数3 关最后登录经验1003 点在线时间145 小时VBA0 关分享记录好友技巧0 关相册主题段位3 段金币1727 个积分1003精华0帖子日志注册时间
小学3年级, 积分 1003, 距离下一级还需 197 积分
金币1727 个积分1003帖子
用indirect函数就可以了
任意单元格输入 =INDIRECT(&SHEET1!r&&COLUMN(C1)&&c&&ROW(A14),0)
下拉 右拉就可以了 最后设置一下百分位小数格式
阅读权限1UID379930函数0 关最后登录经验59 点在线时间5 小时VBA0 关分享记录好友技巧0 关相册主题段位0 段金币62 个积分59精华0帖子日志注册时间
幼儿园小班, 积分 59, 距离下一级还需 41 积分
金币62 个积分59帖子
谢谢3楼，不过我的意思是不引用2-6行的那个表格的数据，我要用的是下面横着看每天的那些数据，请问如何取值，谢谢
阅读权限20UID440177函数3 关最后登录经验10183 点在线时间684 小时VBA0 关分享记录好友技巧0 关相册主题段位3 段金币6124 个积分10183精华0帖子日志注册时间
硕士研究生, 积分 10183, 距离下一级还需 1817 积分
金币6124 个积分10183帖子
(15.63 KB, 下载次数: 32)
23:26 上传
点击文件名下载附件
&&这个就是你想要的答案
生活就像心电图~！！！
要想一帆风顺，除非你死了~！！！
最佳答案累计大于100
Powered by您还未登陆，请登录后操作！
excel中什么时候一定要用数组公式
我觉得功能基本上函数都可以实现，只是可能步骤多一些而已。比如有两列数据分另为A和B，A为单价B为数量，要计算出总金额的话需每行的B*A后结果相加，但是用数组就一次搞定，少了些步骤。
59.58.72.*
圈着的公式是生成模拟运算表时自动生成的。
rogermoorebond
写着评论2，可我点来点去只看到我的那个，晕啊。
rogermoorebond
你画圈的是什么意思呀，晕。
您的举报已经提交成功，我们将尽快处理，谢谢！
大家还关注EXCEL中的统分数组公式
为了保证学生成绩录入和处理的公平性，学校采用混合编班统一录入的方式。也就是说，把同年级多个班的学生成绩统一录入到一个Excel工作表中，而且使同班的学生不相邻。这种情况下，想在不破坏原始工作表的情况下统计各班学生各科的总分、平均分及各科的分数段分布，咱们还得好好动一下脑子。
　　先看一下原始成绩表，如图1所示。
　　第一列为学生的考号，其中考号的第5位和第6位数字是班级代码。学生各科的成绩分别在C、D、E、F列。而我们需要统计的是各学科各班的总分、平均分及各分数段人数等项指标，如图2所示。
　　一、总分的统计
　　在不破坏原工作表顺序的情况下，总分的统计我们可以使用SUM函数及数组公式来完成。
　　首先点击L3单元格计算01班语文科的总成绩。
　　输入“=SUM(IF(MID($A$2:$A$150,5,2)=
$K3,$C$2:$C$150))”，然后按下“Ctrl+Shift+Enter”快捷键，可以看到公式的最外层添加了数组公式标记(一对大括号)，公式变身成为“{=SUM(IF(MID($A$2:$A$150,5,2)=
$K3,$C$2:$C$150))}”。切记这对大括号是不可以手工输入的。
　　按下快捷键后，L3单元格立刻显示出了01班语文科的总分。快吧?还有更快的呢。单击此单元格，向下拖动其填充句柄至L10单元格复制此公式，立刻各班的语文科总分就都有了。
　　方便吧?简单介绍一下公式中用到的相关函数，那咱们就可以举一反三了。
　　MID($A$2:$A$150,5,2)=
$K3：K3单元格是班级代码。而MID函数可以截取字符串中的指定字符，如MID(A2,5,2)的含义是从A2单元格字符串的第5个字符开始截取2个字符组成新的字符串。至于公式中所写的的MID($A$2:$A$150,5,2)就可以利用数组公式对A2至A150单元格中的字符串逐一截取了。
而IF函数当然是用作判断了。IF(MID($A$2:$A$150,5,2)=
$K3是判断截取的字符串是否与在K3单元格中的字符串相同。
　　整个公式的意思是：逐一判断A2至A150单元格中第5、6字符串是否与K3单元格的字符串相同，如果相同，那么就把同一行中C列的单元格数据进行累加。
　　二、平均分的统计
　　明白了各班总分是怎么计算出来的，那么平均分的计算就易如反掌了。仍然点击L3单元格，拖动其填充句柄向右至M3单元格，松开鼠标后在M3单元格同样可以得到01班语文科的总分。不要紧，在公式编辑栏中把公式中的“SUM”函数更改为“AVERAGE”函数，再按下“Ctrl+Shift+Enter”快捷键。好了，现在看到结果了吧?
　　选中M3单元格，向下拖动填充句柄至M10单元格复制此公式。行了，各班的语文平均分就都有了。
　　三、分数段统计
　　在混合编班而又不破坏原表顺序的情况下，我们就不能使用常用的COUNTIF函数或FREQUENCY函数来统计各分数段人数。我们可以使用SUMPRODUCT函数来完成这个任务。
　　点击N3单元格统计01班语文成绩在90分以上的人数。在此单元格输入公式“=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150&=90))”，然后按下回车键。对，是回车键，而不是前面用的“Ctrl+Shift+Enter”快捷键。按下回车键后自然可以看到统计结果了。
　　这个公式的意思是：统计考号的5、6两位字符串与K3单元格字符串相同且C2:C150单元格数据大于90的单元格个数。“*”符号前后的括号内就是我们设置的条件。如果要设置的条件更多，那就再添加“*”，并在其后添加括号，在括号内添加条件就是了。
　　如法炮制，在O3单元格输入如下公式“=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150&=80)*($C$2:$C$150&90))”，回车后就可以得到80-89分这一分数段的人数了。　选中O3单元格，向右拖动其填充句柄复制公式至S3单元格。并根据要统计的不同的分数段，修改各单元格的公式。如S3单元格的公式应修改为“=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150&50))”。
　　好了，现在01班的各分数段已经统计出来了。选中N3:S3单元格区域，向下拖动填充句柄至S10单元格复制相应公式。OK，各班分数段统计完毕。最终效果如图3所示。
　　四、其它各科的成绩统计
　　以数学科统计为例。细细想来，数学科的统计与语文科成绩唯一的不同就是语文成绩在C列而数学成绩在D列。所以，我们只要复制语文统计表然后把其中的“$C$2:$C$150”替换为“$D$2:$D$150”就可以了。
　　先选中K1:S10单元格区域，按下“Ctrl+C”快捷键复制，再单击K12单元格，然后按下“Ctrl+V”快捷键进行粘贴。把K12单元格的“语文”修改为“数学”。现在选中新复制过来的K12:S21单元格区域。然后按下“Ctrl+F”快捷键，打开“查找和替换”对话框。然后点击“替换”选项卡，在“查找内容”输入框中输入要查找的内容“$C$2:$C$150”，再在“替换为”输入框中输入要替换的内容“$D$2:$D$150”。点击下方的“全部替换”按钮。OK，数学成绩的统计就算是大功告成了。如图4所示。
　　其它各科的依此类推。
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。