excel - 获取大小不同的众多组的组平均值

Question

我觉得这几乎是不可能的，但是就这样。我的数据结构如下:

Test Group 1
pass
fail
pass
fall

Test Group 2
pass
fail

Test Group 3

pass 
fail
fail
pass

我希望能够将其粘贴到 Excel 中并让 Excel 汇总每个测试组的百分比。所以它最终看起来像这样:

Test Group 1               Percentage Pass: 50%
pass
fail
pass
fall

Test Group 2              Percentage Pass: 50%
pass
fail

Test Group 3              Percentage Pass: 50%

pass 
fail
fail
pass

但是正如您所见，未设置测试组长度，并且可能会有所不同。我希望我可以创建一个遵循这个逻辑的公式:

if( A<n> contains "Test")

count A<n> +1 until A<n> contains "Test"

似乎我对 Excel 公式提出了很多要求。我今天花了一些时间编写一个小型 C# 应用程序，它将配置拆分为单独的文件，以便我可以将单独的文件复制并粘贴到 Excel 中。但是如果步骤少一点就好了!

- - 更新 - -

有三种非常有趣的方法可以解决这个问题，证明没有什么是不可能的:p

我想要一个允许我复制和粘贴结果并看到大量百分比弹出的解决方案，所以我一直在寻找一个公式解决方案但是，请花点时间看看 pnuts 和 Jerrys 的答案，因为它们揭示了一些有用的Excel的特点!

chuffs 的答案是我正在寻找的答案，它开箱即用。对于任何想深入研究它的工作原理和原因的人，我将公式分解为几个步骤并填写了一些帮助信息。这些公式的关键是结合 MATCH、OFFSET 和稍微更明显的 SEARCH/FIND/LEFT(我曾经使用 IFERROR(FIND 类型的方法，LEFT 看起来更干净:))

请查看这些公式的文档，但要通过一些示例查看所有内容，请参阅我创建的 Google 电子表格详细说明了 chuffs 答案:

https://docs.google.com/spreadsheet/ccc?key=0AqODI11eAjtldDhDd2dBcFhpZW9SXzEybGtMUWMwM3c#gid=0

---附注---
作为记录，我确实创建了一个 C++ 程序来美化我的数据并将其输出为 .csv 文件。如果我有这个信息，我就不会打扰，但很高兴我尝试了两条路线，这是一次有趣的学习冒险。

Answer 1

这个问题的单一数组公式解决方案是可能的。它只需要在数据列的底部添加一个停止值“Test”。

假设您的数据在 A2:A18 范围内，这里是计算单元格B2中的平均值的公式:

  =IF(LEFT(A1,4)="Test",
      COUNTIF(OFFSET(A1,1,0,MATCH("Test*",$A2:$A$18,0)-1,1),"pass")
          / COUNTA(OFFSET(A1,1,0,MATCH("Test*",$A2:$A$18,0)-1,1)),
      "")

公式的关键部分是计算两个计数函数范围的表达式， COUNTIF和 COUNTA .

COUNTIF 函数 - 计算组中的通过次数 - 接受两个参数，即计数范围和计数单元格要满足的条件。

我使用 OFFSET函数提供计数范围。 OFFSET接受五个参数:

anchor 单元格(如果提供了范围，则该函数仅使用范围中左上角的单元格)。

要返回的范围将开始的 anchor 单元格下方 (+) 或上方 (-) 的行数。

返回范围开始的定位单元右侧 (+) 或左侧 (-) 的列数。

要返回的行数。

要返回的列数。

例如， OFFSET(A1,5,2,4,1)将返回范围 C6:D9 .

在公式中， anchor 单元是 A1 ，行偏移量为1，列偏移量为0，也就是说，要返回的范围的开始是 A2 .

返回的行数由 MATCH 计算。功能。对于第一个测试组的平均值计算， MATCH看起来在 A2:A18 范围内对于以单词“Test”开头的第一个单元格的行。 A2是开始测试组的单元格下方的通过/失败列中的单元格。

列中的最后一个单元格是 A18 ，已添加到数据中并包含单词“Test”。这可以防止公式返回最后一个测试组的错误。否则，匹配将返回错误，因为它找不到另一个 Test 的匹配项。在最后一组开始之后。

另请注意，匹配范围的 anchor ，即 $A2:$A$18 ，使行引用未锚定到范围的顶部( A2 中的 2)。这样，随着公式被向下复制，范围将向下调整，以便找到下一个，而不是上一个，出现的“测试”。

最后，我将返回行数减 1 以排除 Text那 MATCH找到，属于下一组。
COUNTA函数 - 计算组中通过和失败的总数 - 使用相同的 OFFSET/MATCH 表达式来获取组的范围。

这是一个数组公式，因此必须使用 Control-Shift-Enter 组合键输入。将公式复制到数据的底部(不包括具有“测试”停止值的单元格)以计算每组的平均值。

为方便起见，这里是没有上述中断的公式。可以直接粘贴到工作表中(记得用Control-Shift-Enter确认)。

  =IF(LEFT(A1,4)="Test",COUNTIF(OFFSET(A1,1,0,MATCH("Test*",$A2:$A$18,0)-1,1),"pass")/COUNTA(OFFSET(A1,1,0,MATCH("Test*",$A2:$A$18,0)-1,1)),"")