ExcelHome技术论坛

 找回密码
 免费注册
QQ登录 只需一步,快速开始
   
高效办公必会的Office99uu优优 永久免费,网表让Excel秒变数据库 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel函数公式学习大典 资深财务总监教您玩转Excel 免费下载Excel行业应用视频
300集Office 2010微99uu优优 Tableau-数据可视化工具 突破Excel限制,用活字格提高效率 13门Excel免费公开课任你学
你的Excel 201099uu优优学习锦囊 欲罢不能, 过目难忘的 Office 新界面 免费的Excel考勤计算系统
楼主: gouweicao78
打印 上一主题 下一主题

[推荐] [答疑解惑]函数公式解释专用帖

    [复制链接]
TA的精华主题TA的得分主题
31
发表于 2006-4-28 13:16 | 只看该作者
本帖已被收录到知识树中,索引项:公式基础
zxq11142006-4-28 12:12:48 求助: 如果我想把A列和B列的位置对调一下,除了插入一列的方法还有别的方法吗?

to 48楼:

选中A列,按住shift,同时鼠标点中选中的A列的边缘托动,你会看到一个虚线的“工”字,托到需要的地方,松开鼠标就可以了;

这个操作对单个,多个单元格区域同样有效。

[此贴子已经被gouweicao78于2006-6-6 16:26:00编辑过]
TA的精华主题TA的得分主题
32
 楼主| 发表于 2006-4-29 16:43 | 只看该作者

to cwl75

首先:这是一个“多条件计数”公式=sum(条件1*条件2)数组公式,即求满足两个条件的个数。

E35:E43>=96即语文成绩96分及以上。

I35:I43是班级——用Left公式得到的,属性:文本型数字。

B47也是班级——属性:数值型数字。

所以用Text(B47,0)将B47转化为文本。当然,也可以用*(--(I35:I43)=B47)即将I列的班级名转化为数值型数字。

或者用""&B47或者B47&""将B47转化为文本。

比如:=SUM(($E$35:$E$43>=96)*($I$35:$I$43=""&$B47))——数组公式。

完整理解就是:班级名=B47、语文成绩>=96分的人数。

关于文本、数值的阐述请参看本帖第4楼。

to 53楼:为节省空间在此楼回答 sum——求和函数,没错。条件1*条件2*…… “条件”——即逻辑判断,产生的是逻辑值,逻辑值的运算遵循True+True=2、True+False=1、True*True=1、True*False=0、True/False=#DIV/0!错误、False/True=0等。 所以条件1*条件2*……得到的是有True和False组成的数组相*,如果多个条件都满足即True*True=1、有不满足的就是0 然后Sum就对{1,0,1,……}求和得到满足所有条件的个数——也就是计数了。 如果=sum(条件1*条件2*……*求和区域)得到的就是多条件加总的公式了。
[此贴子已经被作者于2006-4-29 20:09:08编辑过]
TA的精华主题TA的得分主题
33
发表于 2006-4-30 00:21 | 只看该作者

To cw175:

假定A1:A10有如下数值 1 2 3 4 5 6 7 8 9 10

那么,我想知道大于5的值有多少个?

可以输入公式 =sum(--(A1:A10>5)) 当然要以数组公式结束(Ctrl + Shift + Enter)

如果想知道 >2 且 <8 的值有几个?

输入: =sum((A1:A10>2)*(A1:A10<8)) 也是数组公式

TA的精华主题TA的得分主题
34
 楼主| 发表于 2006-4-30 09:24 | 只看该作者

2006-4-29 22:28:49cwl75给您发送的消息!

想问一下,此贴第21楼的一个问题,其中的I2=LOOKUP(1,0/(材料名称=H2),单位)这个怎么理解,谢谢!就是LOOKUP的查找机制,我没搜到。

为何不用=LOOKUP(H2,材料名称,单位)呢? 1,0/(材料名称=H2)怎么理解呢?

首先,你要认真再看看Lookup查找策略一帖,尤其是8楼的流程图和47楼的流程图修正及模拟。我想,那边讲的会比这里更清楚。

第二个问题:为何不用=LOOKUP(H2,材料名称,单位),这个是Lookup函数的向量语法。不管是向量语法或者数组语法都要求第2参数按升序排序,因为实际上表格里的“材料名称”并未排序,所以不能用这个简单、直接的公式来完成。

理解:首先看分子——(材料名称=H2)——这是一个“条件”即产生逻辑值数组{True,False……}之类的,0/True=0,0/False=#DIV/0!,也就是说0/(条件)返回的只有一些0和错误值组成的数组,0永远比1(第1参数)小,Lookup忽略错误值,它在0和错误值的数组中寻找1,如果数据是无穷多的话,它将永远地找下去,而数据当然是有限的,找呀找[em30],找不着1只能认数组里出现的最后一个0了。——最后一个0意味着什么,意味着最后一个0/True,意味着最后一个“条件”返回True的情况。

所以=Lookup(1,0/(条件……),引用区域)返回的是最后一个满足条件的记录。

与Vlookup精确查找(最后参数为False)不同的是,Vlookup返回第一个满足条件的记录。(如果有多个满足条件的记录就知道区别了)

TA的精华主题TA的得分主题
35
 楼主| 发表于 2006-4-30 11:41 | 只看该作者

to 57楼:=INDIRECT("Sheet2!B4"),注意1楼的规则,这里是“解惑”不是“解题”,呵呵。请尽量少占用楼层,可以用前面你比较空的楼层来发出新的提问,并用短信息通知我或者他人来此解答。谢谢配合。 这个问题的原因看Inidrect函数帮助:

INDIRECT(ref_text,a1):Ref_text 为对单元格的引用,此单元格可以包含 A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。
因为你的sheet2!B4单元格的字符串是C1,所以相当于=Inidrect("C1")=C1单元格的值,C1为空,所以返回0,若要返回空,加&"",前面41楼有说明。

to 57楼新补充:你sheet2!B4单元格存放的是"C1"字符,所以要返回sheet2!C1单元格的话,应该把sheet2!B4单元格的字符改为"sheet2!C1",然后在sheet1用=indirect(sheet2!B4)才可以引用到那个200。

to 56楼等:注意1楼的规则,要感谢的话就把钱打到我卡里[em33][em07],呵呵。要感谢的话——请认真学习并在论坛帮助他人就可以了。

to 楼下的“折翼蚊子”——请看41楼。
[此贴子已经被作者于2006-4-30 15:53:44编辑过]
TA的精华主题TA的得分主题
36
发表于 2006-4-30 15:15 | 只看该作者

=INDEX(年段!B:B,SMALL(IF(年段!$A$1:$A$550=1,ROW($1:$550),65536),ROW(1:1)))&""

帮我解释下,特别是最后的&""的含义

to “折翼蚊子”——请看本帖41楼。——删除部分楼层后在27楼
[此贴子已经被gouweicao78于2006-6-6 16:34:00编辑过]
TA的精华主题TA的得分主题
37
发表于 2006-5-2 08:28 | 只看该作者
关于“隐含交叉” “交叉”有两种,一个是“显式交叉”,就是在公式本身就体现了引用区域的交叉。 我们做个最简单的例子: 在A1:C10中存放有数据(数值),下面这个公式:SUM(A1:C10)会计算所有的数值之和;而SUM(A1:B5 B2:C10)【注意这个公式中A1:B5与B2:C10之间有一个空格】却只计算B2:B5之和,这是因为A1:B5与B2:C10这两个区域之间只有B2:B5这一部分是交叉的,点击编辑栏里的公式,就可以看到这种交叉的情况,SUM计算的就是交叉范围内的数值之和。 这种情况就是公式本身指定了的,也是我们所期望得到的“显式交叉”。 而另外一种交叉,在公式本身并不体现,它只和公式所在的行列位置有关。 比如,令A1:A5={1,2,3,4,5},在B1写公式=A1:A5,不按三键就会得到结果1,而同样的公式如果写在B3里,会得到结果3。这就是因为“隐含交叉”的原因:B1的公式与引用区域A1:A5在第一行交叉了,所以它只显示A1,B3同理。如果同样的公式按数组公式,按ctrl+shift+enter结束就不会这样。 一般来说,这不是我们期望得到的结果,但也有人在特殊情况下利用这个特性来简化公式,很久以前的一个函数测试题好像就有人用过。 现在再来看chenjun的OFFSET函数的帖子,就应该明白了:chenjun为什么把列公式用箭头引到M20:M25区域来显示,因为只有这个区域和数据源区域是有隐含交叉的。如果把这个公式放到其他的行上,比如N30:N35,由于不存在这种隐含交叉,显示结果也不是这样了。如果我们把这个公式按数组公式输入,就不存在隐含交叉,因而也能按我们的期望来显示了——不管公式放在哪里。
[此贴子已经被作者于2006-5-2 12:31:25编辑过]
fmmrdi1X.jpg (16.18 KB, 下载次数: 138) [答疑解惑] 函数公式解释专用帖
[答疑解惑] 函数公式解释专用帖
TA的精华主题TA的得分主题
38
发表于 2006-5-2 17:25 | 只看该作者
类似于公式结果同单元格区域之间的隐含交叉引用,在函数内,参数间也存在着隐含交叉计算,是数组参数之间一一对应的运算关系。
函数参数也可这样划分为两大类:其中一类只支持单元格引用,不能输入数组或常量;另一类只支持常量或数组,把单元格区域引用作为常量或数组看待。
交叉计算,实际上是同类或两类参数之间的数组对应运算。几乎每个函数都存在交叉计算的情况。例如,在SUM(A1:B10*C2:D11)这个公式里,就存在参数的交叉计算,是各行各列的单元格一一对应,属于第二类参数内部的隐含交叉计算。
又如,Rank(A1:F5,offset(A1:F1,ROW(A1:F5)-ROW(A1:F1),)),这个公式的情况是,第一类参数和第二类参数之间存在着交叉计算。其中offset的多维引用被看作是,多个连续引用区域组成的“区域数组”,这个概念在[浅谈引用函数产生的多维引用及其应用]中已有提及。
还有一些情况,我们以后再说。需要强调的是,交叉计算的实质是两个或多个“数组”的行列对应计算。这里的说的数组,可以是“区域数组”,也可以是“纯数组”。
TA的精华主题TA的得分主题
39
 楼主| 发表于 2006-5-4 23:37 | 只看该作者
to 稻草人367:原帖链接
这是SUMif三维引用的经典用法=SUM(SUMIF(INDIRECT({"A","B","C","D"}&"!L4:L100"),$A4&$B4,INDIRECT({"A","B","C","D"}&"!R4C"&COLUMN()&":R100C"&COLUMN(),)))主要在Sumif——第1参数黄色,第2参数绿色,第3参数红色:我们知道=Sumif(A4:A100,B4,C4:C100)——这么一个最常见的Sumif的基本用法,表示对“A列中项目和B4单元格相同所对应的C列的数值”进行条件求和。第一参数:Indirect("A!L4:L100")——引用的是A表的L4:L100区域,Indirect({"A","B","C","D"}&"!L4:L100")引用的是A、B、C、D表的L4:L100区域,是4个区域。注意:我在ABCD这4个表的L列设置了辅助列,将序号和颜色合并文本。第二参数:A4&B4——序号和颜色合并文本。第三参数:还是Inidrect引用,只不过引用样式改成了R1C1引用样式。比如Indirect("A!R4C3:R100C3",0)——表示引用A表的“第4行第3列到第100行第3列”即C4:C100区域。为了右拖复制的时候,第3列能够自动变为第4列、第5列……,则把R4C3改为"R4C"&column()——column()取得当前单元格所在列号。所以:第三参数引用的是A、B、C、D表的C4:C100,并且可以随着向右拖动复制变为D4:D100、E4:E100等区域。这么一来,Sumif得到的就是4个表中每个表分别对应序号和颜色的数量总数,然后再用SUM对这4个表的总数(相当于每个表的小计)求和,就是4个表——满足序号和颜色与A4、B4单元格一致的数量汇总了。

结合gvntw版主的Lookup在内存数组中的处理,可以用Lookup和宏表函数取得无规律工作表名(除去汇总表)的其他工作表名数组,并由此替代上面的{"A","B","C","D"}之类的,则应用范围更广!
实例见http://club.excelhome.net/viewthread.php?tid=165500&px=0
[此贴子已经被作者于2007-5-10 8:36:27编辑过]
TA的精华主题TA的得分主题
40
发表于 2006-5-5 17:12 | 只看该作者

楼主解释这个多单元格数组公式的运算过程:

选定a1:a4={small({1,2,3,6},{1,2,3,4})}我用公式审核——公式求值看了一下,在这四个单元格中,每一步的计算过程都相同,如={small({1,2,3,6},1)},但结果为什么就不同呢?a1=1,a2=2,a3=3,a4=6.

waqh67972006-5-5 23:47:55 谢谢楼主,经过你的解释,对数组的理解清晰多了,辛苦你了.
[此贴子已经被gouweicao78于2006-6-6 16:39:16编辑过]
您需要登录后才可以回帖 登录 | 免费注册
本版积分规则
关闭

最新热点上一条 /1 下一条

关注官方微信,每天学会一个新技能
手机版|关于我们|联系我们|ExcelHome GMT+8, 2018-4-24 09:01 , Processed in 0.078951 second(s), 9 queries , Gzip On, MemCache On.
Powered by Discuz! X3.4 © 2001-2017 Wooffice Inc.
    沪公网安备 31011702000001号 沪ICP备11019229号 本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师
快速回复 返回顶部 返回列表
99uu优优