跨多个数组的百分位数函数

Question

所以我现在在excel中找到了这些数据

  A             B           C
2015-1        Test 1        23    
2015-2        Test 1        12
2015-3        Test 1        43
2015-4        Test 1        32
2015-5        Test 1        3
2015-6        Test 1        90
2015-1        Test 2        200
2015-2        Test 2        123
2015-3        Test 2        21
2015-4        Test 2        40
2015-5        Test 2        17
2015-6        Test 2        138
2015-1        Test 3        160
2015-2        Test 3        55
2015-3        Test 3        30
2015-4        Test 3        74
2015-5        Test 3        67
2015-6        Test 3        89

现在，我有了它，这样用户就可以查看数据的特定时间段，而不一定是所有的日期(例如，从2015-1到2015-4)。因此，当用户选择他们想要的日期时，我希望在该日期取数据的百分位数(C列)，跨越B列中所有不同的测试场景。现在只有3种，但将有多达100个不同的测试用例。

我知道做=百分位数((test1_data，test2_data，test3_data),1)是可能的，但是我必须在100多个不同的测试用例中完成百分位数，现在设置它的方式看起来效率很低。有没有办法做到这一点，而不必输入所有100个不同的数组手工？

Answer 1

根据您的表，下面的公式应该有效。(这是一个数组公式，在将公式输入单元格以激活函数时，应该使用CTL+SHIFT+ENTER。)

{=PERCENTILE(
IF(NUMBERVALUE(LEFT($A$1:$A$18,4))<=EndYear,
IF(NUMBERVALUE(LEFT($A$1:$A$18,4))>=BegYear,
IF(NUMBERVALUE(RIGHT($A$1:$A$18,1))<=EndMonth,
IF(NUMBERVALUE(RIGHT($A$1:$A$18,1))>=BegMonth,
$C$1:$C$18)))),1)}

• EndYear是对包含最后一年的单元格的引用。
• BegYear是对单元格的引用，其中包含了您希望包含的第一年。
• EndMonth是对单元格的引用，该单元格包含了您希望包含的最后一个月(或任何其他单元)。
• BegMonth是对单元格的引用，其中包含了您希望包含的第一个月(或任何其他单元)。

只需扩展引用$A$1:$A$18和$C$1:$C$18，以包括您想要的测试用例。

公式解释

1. 前两份if声明的重点是国际年。他们将LEFT()四位数作为字符串。然后，NUMBERVALUE()将字符串转换为值。然后，可以使用if语句在逻辑上评估测试日期是否属于所需的日期范围。
2. 后两个if语句在最后一个个位数(月份？)上做的事情完全一样。
3. 如果所有语句都为true，则嵌入的if语句将从C列返回关联值的数组，如果其中一条语句不为真，则返回FALSE。
4. PERCENTILE()将接受数组，忽略作为FALSE返回的项，并为您提供所有四个if语句都为真的值范围的k个百分位数。

*作为一个注释，我不知道你的第二个数字的意义。如果它超过9，您可能需要调整您的数据。在这种情况下，您可以将所有2015-9条目替换为2015-09，并将RIGHT()函数的第二个参数更改为2，或者您可以执行类似MID($A$1:$A$18,6,2)之类的操作，或者最后一个数字可以被在该年参数之后有多少字符替换。