为什么此Excel公式起作用? [英] Why does this Excel formula work?
问题描述
在此答案中，给出了以下公式.
{= PERCENTILE(IF(COLUMN(INDIRECT("1:"& MAX(Data！N3:N15)))< = Data！N3:N15，Data！M3:M15，")，0.25)} 应该计算具有权重的数据的第一个四分位数.
我一直在试图理解该公式的含义，但我不能.
首先，花括号是什么?(可能是一个愚蠢的问题.)
第二，< = 运算符在给定两个数据范围作为操作数时会做什么?
第三，如果不管if语句中的条件如何，返回的数据仅仅是value列，这怎么可能给出正确的答案?在我看来，公式的作用是
- 如果满足一个奇怪的条件，请计算value列的第一个四分位数.
- 如果不是，请计算"的第一个四分位数.
这似乎是完全错误的...
此外，在哪里可以找到Excel函数和运算符的完整手册?联机帮助文件对比较两个范围一无所获.
我个人不会使用引用的公式-我怀疑它是否确实符合作者的想法，并且在某些情况下会给出错误的结果.您可以看到问题所在，因为这部分
= COLUMN(INDIRECT("1:"& MAX(Data！N3:N15))
如果 MAX(Data！N3:N15)为2或200，则返回完全相同的值.如果 MAX(Data！N3:N15) = 2，则得到
= COLUMN(INDIRECT("1:2"))
并且我假设作者的意图是返回一个像{1,2} ...这样的数组，但是它不会那样做.INDIRECT("1:2")为您提供1:2，它被解释为第1行和第2行的全部，因此= COLUMN(1:2)为您提供所有列的数目(为1至256或1至16384取决于您使用的是哪个版本的excel或是否使用兼容模式.
您可以通过在单元格中使用此公式进行测试
= COUNT(COLUMN(INDIRECT("1:"& MAX(Data！N3:N15))))
已通过 CTRL + SHIFT + ENTER
您将得到256或16384，但这完全不依赖于N列中的值.
该公式很可能会为您提供正确的结果，但是如果Data！N3:N15中的任何值大于256(或16384，取决于版本)，则该公式可能无法正常工作.
此版本的公式应在所有情况下均符合预期:
= PERCENTILE(IF(TRANSPOSE(ROW(INDIRECT("1:"& MAX(Data！N3:N15)))))< =数据！N3:N15，数据！M3:M15，")，0.25)
...但是为了解释其工作原理，让我们看一下只有4行的精简版本，即
= PERCENTILE(IF(TRANSPOSE(ROW(INDIRECT("1:"& MAX(Data！N3:N6)))))< == Data！N3:N6，Data！M3:M6，")，0.25)
并假定M3:M6包含以下值-10、75、15、23并且N3:N6包含这些值1,2,3,4，
现在 MAX(Data！N3:N6) = 4，因此 INDIRECT 会给您"1:4"，并将其传递给ROW函数，以便获得数组类似于{1; 2; 3; 4} [这是值的列"]，但 TRANSPOSE 会将其转换为{1,2,3,4} [这是值的行"]-进行这种转换的原因是，当将列与行进行比较时(反之亦然)，它将一个数组中的每个值与另一个数组中的每个值进行比较，这就是这里所需要的(结果是4x4的值矩阵).
现在，当我们执行{1,2,3,4}中的每个值是< = 4(N6值)，但只有3个是< = 3(N5值)时，只有2个是< = 2(N4值)等，因此传递给 PERCENTILE 函数的数组将正确返回1的值10、2的值75、3的值15和4的值23(其他值为"PERCENTILE会忽略的空白)
我的示例的结果是15，原始公式也给出了15 .......但是如上所述，原始公式可能会得到较大数字的错误结果-例如我正在用Excel 2007中的兼容模式进行测试，如果我将N4更改为4000，将N6更改为1000，我现在期望结果为75(这是我的公式给出的结果)...但是原始公式给出的是23./p>
In this answer the following formula is given.
{=PERCENTILE(IF(COLUMN(INDIRECT("1:"&MAX(Data!N3:N15)))<=Data!N3:N15,Data!M3:M15,""),0.25)}
It is supposed to calculate the first quartile of data with weights.
I've been trying to understand what this formula says, but I can't.
First of all, what are the curly brackets? (Probably a silly question.)
Second, what does the <= operator do when it's given two data ranges as operands?
Thrid, how can this possibly give the right answer, if, regardless of what the condition in the if-statement does, the returned data is just the value column? It seems to me that what the formula does is
- if a weird condition is satisfied, calculate the first quartile of the value column.
- if it's not, calculate the first quartile of "".
This seems completely wrong...
Also, where can I find a complete manual of Excel functions and operators? The online help file says nothing about comparing two ranges.
Personally I wouldn't use the quoted formula - I doubt if it does exactly what the author thinks it does and in some circumstances it will give incorrect results. You can see the problem because this part
=COLUMN(INDIRECT("1:"&MAX(Data!N3:N15))
returns exactly the same thing if MAX(Data!N3:N15) is 2 or 200. If MAX(Data!N3:N15)=2 you get
=COLUMN(INDIRECT("1:2"))
and I assume the author's intent is to return an array like {1,2}.......but it doesn't do that. INDIRECT("1:2") gives you 1:2 which is interpreted as the whole of rows 1 and 2 so =COLUMN(1:2) gives you the numbers of all columns (which is either 1 to 256 or 1 to 16384 depending on which version of excel you are using or whether you are using compatability mode or not).
You can test that by using this formula in a cell
=COUNT(COLUMN(INDIRECT("1:"&MAX(Data!N3:N15))))
confirmed with CTRL+SHIFT+ENTER
You'll either get 256 or 16384 but that doesn't depend on the values in column N at all.
The formula may well give you the correct result but it might not work correctly if any values in Data!N3:N15 are > 256 (or 16384 depending on version).
This version of the formula should do as intended in all cases:
=PERCENTILE(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(Data!N3:N15))))
<=Data!N3:N15,Data!M3:M15,""),0.25)
.....but to explain how it works lets look at a cut down version with only 4 rows, i.e.
=PERCENTILE(IF(TRANSPOSE(ROW(INDIRECT("1:"&MAX(Data!N3:N6))))<=Data!N3:N6,Data!M3:M6,""),0.25)
and assume that M3:M6 contains these values - 10, 75, 15, 23 and that N3:N6 contains these values 1,2,3,4,
Now MAX(Data!N3:N6) = 4 so INDIRECT gives you "1:4" and that is passed to the ROW function so you get an array like {1;2;3;4} [which is a "column" of values] but TRANSPOSE converts that to {1,2,3,4} [which is a "row" of values] - the reason for that conversion is because when a column is compared to a row or vice versa it compares every value in one array with every value in the other array which is what is needed here (giving a 4x4 matrix of values as a result).
Now when we do that every value in {1,2,3,4} is <= 4 (N6 value) but only 3 are <= 3 (N5 value), only 2 are <= 2 (N4 value) etc. so the array that is passed to the PERCENTILE function correctly returns 1 value of 10, 2 values of 75, 3 values of 15 and 4 values of 23 (the other values are "" blanks which PERCENTILE ignores)
The result of my example would be 15 and the original formula also gives 15.......but as explained above the original formula might get incorrect results with larger numbers - e.g. I'm testing with compatability mode in Excel 2007 and if I change N4 to 4000 and N6 to 1000 I now expect the result to be 75 (which is the result my formula gives) .....but original formula gives 23.
这篇关于为什么此Excel公式起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
