☰

需要一个 Excel 公式，从列中最旧的 5 个值中减去最新的 5 个值

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
需要一个 Excel 公式，从列中最旧的 5 个值中减去最新的 5 个值

我是一名体能教练，需要一些帮助来分解我的运动员评估结果。我需要帮助编写一个公式，从最近的 5 次测试结果中减去最旧的 5 次测试结果。该工作表有近 3000 行，每个运动员各有 5 行（第 1、2、3、4 学期和夏季），无论他们是否实际参加了上述测试。因此，公式基本上需要沿着列向下，从 5 个单元格中取出第一个可用值，然后从 5 个单元格中的最后一个可用值中减去它。工作表设置如下：

截屏

请帮忙！如果这没有提供足够的信息，我很抱歉，因为我是这个网站的新手。

答案1

我有一个公式，当你把它放到标题为“更改”的灰色行中时，它就会起作用

它是一件奇妙的东西，它看起来像这样：

=INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), ROWS(Values)) - ROW(StartValue) + 1,1)- INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), COUNTIF(Values, "")+1) - ROW(StartValue)+1,1)

在哪里

  • Values是值的范围，在我的例子中是H5:H9
  • StartValue是值范围内的第一个单元格，对我来说这是H5

怎么运行的

  • 该语句INDEX((Values<>0)*ROW(Values), 0)返回一个数组，0当单元格为空白时，返回单元格的行号，当单元格不为空白时，返回单元格的行号
  • 该SMALL()函数按升序对这些进行排序，然后返回数组中与其第二个参数相对应的值

因此在下面的示例电子表格中，INDEX((Values<>0)*ROW(Values), 0)返回

0
6
7
8
0

SMALL()排序后看起来如下：

0
0
6  <- Row of first nonzero value, second argument to SMALL(): =COUNTIF(Values, "")+1 = 3
7
8  <- Row of last nonzero value, second argument to SMALL(): ROWS(Values) = 5
  • 现在我们知道了行，我们使用INDEX()来获取值。该行将- ROW(StartValue) + 1返回的行转换SMALL()为索引
  • 因此，我们剩下第一次调用时最近的非零值，INDEX()并从中减去第二次调用时最远的非零值INDEX()

截屏

计算 CHANGE 行

链接至电子表格

这里是下载此示例电子表格的链接。我通常使用 FormulaChop 来解释这些公式，但它不适用于 ROW() 函数。（完整披露：我编写了 FormulaChop）。因此，我提供了一个可以工作的公式版本，您可以在 FormulaChop 选项卡上看到它。

答案2

使用 A 列（A2:A3001）中的日期、B 列中的名称和 C 列中的值，将要查找的名称放在 F2 中，并在 G2 中使用此公式。

=SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(14, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))-
 SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(15, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))

答案3

希望这就是您所寻找的？

[ 图片1]

在图片中，我们有两个用户。黄色数据是您工作表中的数据（我认为有很多用户），其他列用于使用简单公式对这些数据进行评估。

使用的公式如下图所示

答案4

这可能是你正在寻找的 图片显示结果如下

公式就是这样的，使用起来非常简单 图片显示公式

如何创建公式？按 Alt+F11 并复制粘贴以下内容

公共函数 Diff2（n1 作为 Double，n2 作为 Double，n3 作为 Double，n4 作为 Double，n5 作为 Double）

索引1 = 0 索引2 = 0

'查找第一个数字 如果 n1 > 0 则 FirstNum = n1 index1 = 1 ElseIf n2 > 0 则 FirstNum = n2 index1 = 1 ElseIf n3 > 0 则 FirstNum = n3 index1 = 3 ElseIf n4 > 0 则 FirstNum = n4 index1 = 4 End If

'查找第二个数字 如果 n5 > 0 则 SecNum = n5 index2 = 5 ElseIf n4 > 0 则 SecNum = n4 index2 = 4 ElseIf n3 > 0 则 SecNum = n3 index2 = 3 ElseIf n2 > 0 则 SecNum = n2 index2 = 2 End If

'错误检查 如果 (index1 = 0) 或 (index2 = 0) 或 (index1 - index2 = 0) 则 Diff2 = "ERROR" 否则 Diff2 = SecNum - FirstNum End If

结束函数

图片显示 excel VBA

相关内容