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

asd6512 · 发表于 2009-6-29 12:51

=TEXT(MOD((ROW()-1),10),"0;0;!10") 这个"0;0;!10"这个怎么理解

[ 本帖最后由 gouweicao78 于 2009-7-1 11:26 编辑 ]

gouweicao78 · 发表于 2009-7-1 11:26

原帖由 asd6512 于 2009-6-29 12:51 发表
=TEXT(MOD((ROW()-1),10),"0;0;!10") 这个"0;0;!10"这个怎么理解

一、解析
单元格格式代码一般可视为：正数；负数；零；文本——四个节段。
因此：0;0;!10——表示正数返回整数，负数返回为正整数，零返回为10，其中!是强制返回其后面的1个字符（这里就是!后面的1）
二、公式简化
附件中：
1、MOD的第2参数（除数为10）是正数，因此MOD不可能得到负数结果，也就是第2个节段的0是多余的；
2、数字1没有必要用强制占位符!。
可以用=TEXT(MOD(ROW()-1,10),"0;;10")即可。
三、解法更换
要得到1~10的循环序列号，只需要下面这个公式即可，无需TEXT（Text得到的是文本型数字，MOD得到的是数值）：
=MOD(ROW()-2,10)+1

半个月亮 · 发表于 2009-7-12 01:01

在学习论坛资料时，碰到一个公式有点不明白。好像是山菊花解答的问题，我是下载下来看的，不记得帖子在哪了，只好到这里问问。呵呵。
这只是一个三级数据有效性，我用别的方法也同样能做到，就是有个公式不明白。望解惑。谢谢！
=INDEX(机楼,MATCH(,COUNTIF(AB$1:AB1,机楼)+(局名称<>Sheet1!$B$2),))
这个公式为什么将“局名称”换成引用A2：A7，就不行了？？

gouweicao78 · 发表于 2009-7-12 01:13

原帖由 半个月亮 于 2009-7-12 01:01 发表
在学习论坛资料时，碰到一个公式有点不明白。好像是山菊花解答的问题，我是下载下来看的，不记得帖子在哪了，只好到这里问问。呵呵。
这只是一个三级数据有效性，我用别的方法也同样能做到，就是有个公式不明白。望 ...

交箱资料!AB2单元格输入：

=INDEX(机楼,MATCH(,COUNTIF(AB$1:AB1,机楼)+(A$2:A$8<>Sheet1!$B$2),))

复制代码

并按CTRL+SHIFT+ENTER结束后，向下复制，可以得到正确结果啊。
注意：
1、是否按了CTRL+SHIFT+ENTER形成数组公式？
2、是否用A2:A8相对引用，下拖复制会改变单元格区域，而非用A$2:A$8？

futurews · 发表于 2009-7-27 06:16

INDEX(数据!D:D,SMALL(IF(数据!$A$2:$A$674=$C$2,ROW(数据!$2:$674),4^6),ROW($A1)))&""

版主有时间给我讲讲这个公式的原理，还有那个4~6 为什么那样用，这样用法应用范围多吗？

gouweicao78 · 发表于 2009-7-27 09:08

这个公式的原理在本帖已经讲了多次，你先阅读一下首页。比如第26楼：
http://club.excelhome.net/viewth ... p;page=2#pid1094349
4^6就是4的6次方，一般用4^8返回65536——是喜欢公式简短的人常用的一个方法。

半个月亮 · 发表于 2009-7-27 11:41

不是所有的sumif函数都能改成if函数吗？
我想将这个sumif函数改为if函数，但不知为什么结果不正确？
=sumif(B$2:I$23,K12,B$3)
={SUM(IF(B$2:I$23=K12,B$3))}

gouweicao78 · 发表于 2009-7-28 04:13

{=SUM(IF(B$2:I$23=K12,B$3:I$24))}
因为=sumif(B$2:I$23,K12,B$3)是第3参数简写的用法，其实质是：=sumif(B$2:I$23,K12,B$3:I$24)

pandi83 · 发表于 2009-7-31 14:02

我看到一个从A列中筛取B列里面没有的数据并在C列中由大到小具体显示出来。公式如下：
=INDEX(A:A,SMALL(IF(COUNTIF($B$1:$B$7,$A$1:$A$7),4^8,ROW($1:$7)),ROW(1:1)))&""
以上的公式只是C1里填写的，C2把最后的ROW(1:1)改成ROW（2：2）。我想问的是如果C1公式运用的没有问题，那么是否在运行C2的公式中系统把刚才C1得出的最小值自动刨除在外了？
另外一个问题，如果是把本题中的数据换成文本或不需要按照顺序排列的话，是否只要剔除公式里面的SMALL函数？
希望楼主能解答下我的问题，谢谢。

gouweicao78 · 发表于 2009-7-31 17:32

类似这样的公式，本帖已有解答，请先看一下本帖首页的有关知识。
1、IF(条件,4^8,row)——将满足条件的返回为4^8也就是65536，不满足的返回Row($1:$7)，这里的条件是B列“有”的数据。——因此，IF得到的是65536和B列没有的数据所在的行号组成的数组。
比如，B列没有的数据是A1、A3、A5、A7，那么IF返回的是{1;65536;3;65536;5;65536;7}
2、ROW(1:1)是向下复制公式时利用相对引用特性自动变化的，是作为SMALL的第2参数，在第1行就是SMALL(行号数组,1)，取得最小的IF得到的行号；在第2行就是SMALL(行号数组,2)，取得第2小的IF得到的行号；……
明白了这两点，你就知道SMALL根本与你说的“数据换成文本或不需要按照顺序排列”无关，它是用来取出行号的。

希望您能认真阅读一下本帖首页以及相关的解释。

[ 本帖最后由 gouweicao78 于 2009-7-31 17:33 编辑 ]

		自动登录	找回密码
密码			免费注册

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

请大侠批点

数组公式中4~6什么意思

回复 385楼 futurews 的帖子

sumif和if函数有什么区别？

回复 387楼半个月亮的帖子

请帮忙解释下这个问题

回复 389楼 pandi83 的帖子