Question

在 Excel 中，我有一些每天都会发生、频率不同的事件。带有公式的列 (B) 跟踪事件编号，当列 A 中的日期发生变化时，从第一个事件开始，并以 #1 为编号，如下所示：

日期	事件＃	日总计
2024 年 10 月 30 日	1
2024 年 10 月 30 日	2
2024 年 10 月 30 日	3
2024 年 10 月 30 日	4
2024 年 10 月 30 日	5	5
2024 年 10 月 31 日	1
2024 年 10 月 31 日	2
2024 年 10 月 31 日	3	3
2024 年 11 月 1 日	1
2024 年 11 月 1 日	2
2024 年 11 月 1 日	3
2024 年 11 月 1 日	4	4

我想要一个公式：

计算每天的事件数，即 10/30 总共 5 个，10/31 总共 3 个，11/1 总共 4 个。最简单的方法可能是一直计数直到值 1（重新）出现。然后继续计数直到 1 再次出现。
然后我想查看每天的最大、最小、平均和中值频率。如果我们满足第一个要求，那么只需先将每日总数放在辅助列 C 中，即可轻松完成此操作。
但我希望最大值、最小值等统计数据能够在输入 11/2/24 及以后的新数据时自动更新，因此范围/数组应该不断扩大。

尝试了各种 countif、index、match 等组合，但均未成功。计数直到遇到第一个 1 是基本操作。无法弄清楚如何在不循环的情况下继续执行该操作，更不用说在将新数据添加到列时自动扩展范围了。最简单的可能是 do-while 循环，但我不知道任何 VBA。提前致谢。

听起来你需要一个数据透视表。我无法检查，因为我不会点击该链接 —

Answer 1

假设没有Excel Constraints按照帖子标签，可以尝试使用以下内容：

=LET(
     a, MAP(B2:B13,LAMBDA(x,SUM(N(x:B2=x)))),
     b, XLOOKUP(B2:B13,B2:B13,a,,,-1),
     HSTACK(a, IF(a<b,"",b)))

如果您希望在添加新数据时显示计数和最大计数，则可以根据您所使用的 MS365 版本使用下列方法之一：

• 使用功能的帮助TRIMRANGE()：

=LET(
     a, DROP(B.:.B,1),
     b, MAP(a,LAMBDA(x,SUM(N(x:B2=x)))),
     c, XLOOKUP(a,a,b,,,-1),
     HSTACK(b, IF(b<c,"",c)))

• 或者不使用TRIMRANGE()

=LET(
     a, B2:INDEX(B:B,MATCH(2,1/(B:B<>""))),
     b, MAP(a,LAMBDA(x,SUM(N(x:B2=x)))),
     c, XLOOKUP(a,a,b,,,-1),
     HSTACK(b, IF(b<c,"",c)))

对于问题 2 的一个简单解决方案是使用AGGREGATE()：

=VSTACK({"Min","Max","Average","Median"},
 AGGREGATE({5,4,1,12},7,DROP(C2#,,1)))

通过更改分隔符来根据您的喜好更改方向：

=HSTACK({"Min";"Max";"Average";"Median"},
 AGGREGATE({5;4;1;12},7,DROP(C2#,,1)))

嘿，伙计。一如既往的可靠答案。不过，匹配函数是我花了不少功夫才搞定的。如果我们确定列中的最后一个值应该是数字，那么这个方法会快得多……（只要确保使用大数字即可！）=MATCH(9.99E+99,B:B,1) — 
@pgSystemTester 先生，好的，我会继续这样做。一如既往地感谢您的提醒。 — 
不客气。我浪费了太多生命来尝试找到最后一行的最佳方法。你的方法总是有效的，但速度差异足以值得考虑各种可能性。保重。 — 
@pgSystemTester 先生，是的，现在明白了。我也看到了你关于这个的 vba 帖子。 — 
@pgSystemTester Excel 允许输入的最大正数是 9.99999999999999E+307。但它小于最大正双数。要获得最大值，请使用 9.99999999999999E+307 和 7,97693134E+307 之和。最后一个数字可以指定更多位数。 —

Answer 2

一旦搞清楚了第 1 部分，问题的第二部分和第三部分就很简单了。使用最新的数组函数，很容易获得像 c 列中的动态列表。Mayukh Bhattacharya 的答案做到了这一点，但下面的答案在性能上可能略胜一筹。

将其粘贴到单元格 c2 中，它将动态地为您提供该信息。

=LET(dateCol,A:A,EventCol,B:B,iRng,SEQUENCE(COUNT(dateCol),1,ROW(),1),IF(INDEX(dateCol,iRng+1,1)=INDEX(dateCol,iRng,1),"",INDEX(EventCol,iRng,1)))

引用第 2000 行或 20k 行等位置的答案应该被忽略。

Answer 3

自动将范围更新到最后一个非空白单元格
获取频率，而不是查看事件 #
请调整a2:a2000以匹配您的数据集
将第一列（事件日期）格式化为您喜欢的日期格式

=LET(
    last, XLOOKUP(TRUE, A2:A2000 <> "", A2:A2000, , , -1),
    dates, A2:last,
    counts, COUNTIFS(dates, dates),
    UNIQUE(HSTACK(dates, counts))
)

如果可用，

=LET(
    last, XLOOKUP(TRUE, A2:A2000 <> "", A2:A2000, , , -1),
    dates, A2:last,
    GROUPBY(dates, dates, COUNT, 0, 0)
)

符对结果进行计算，这里是C3#：

=LET(
    freq, INDEX(C3#, , 2),
    VSTACK(
        {"Min", "Max", "Avg", "Median"},
        HSTACK(MIN(freq), MAX(freq), AVERAGE(freq), MEDIAN(freq))
    )
)

Answer 4

在单元格 C2 中输入 1 在单元格 C3 中，您可以输入公式

=如果(B3=B2,1+C2,1)

在单元格 D2 中输入公式

=IF(C3=1,C2,””)

然后将它们拖到末尾即可。您必须手动输入 D 列中的最后一个数字。

谢谢。我已经按照您建议的第一点做了，即 =IF(B3<>B2,1,C2+1)，它给出了 C 列，现在 D 中也有每日总数。当这些数据每天从其他地方自动导入时，我可以简单地在 14B 中自动插入另一个 1，因为 14 是 B 中的第一个空白单元格。这样就无需手动输入 D 中的最后一个数字。但我们仍然没有自动扩展范围。基本上，希望将范围扩展到 B 中的第一个空白单元格。 —

Answer 5

我想向您提出下一个解决方案，您只需输入下一个日期值：

[B2]=IF(A2=A1,B1+1,1)
[D2]=LET(u,UNIQUE(Table1[Date]),HSTACK(u,MAP(u,LAMBDA(I,COUNTIF(Table1[Date],"="&I)))))
[H2]=LET(d,TAKE(D2#,,-1),VSTACK(MIN(d),MAX(d),AVERAGE(d),MEDIAN(d)))

操作步骤：

创建输入标题、第一个日期和 B2 公式的数据范围。
选择 A1 并按 Ctrl+T 将范围转换为表格。
输入 D2 公式。
输入 G2:G5 标题和 H2 公式。

然后您只需输入下一个日期值。其他所有内容将自动更新。

Answer 6

假设 A 列中的日期始终按顺序排列（看起来是这种情况），并且日期范围内没有空白单元格，则可以在单元格中尝试以下动态数组公式，将事件编号和天总数B2的结果一起显示：

=LET(
    rng, A2:INDEX(A:A,XMATCH(,A:A)-1),
    acc, rng=VSTACK(0,DROP(rng,-1)),
    num, SCAN(0,acc,LAMBDA(a,v,a*v+1)),
    HSTACK(num,IF(VSTACK(DROP(acc,1),0),"",num))
)

即使有数万行数据，这种方法仍然相对有效。

注意：在这种情况下，单元格中带有日期A1标题，rng和acc变量也可以定义如下：

=LET(
    rng, A1:INDEX(A:A,XMATCH(,A:A)-1),
    acc, DROP(rng,1)=DROP(rng,-1),
    ...
)

Answer 7

这也是一个选择：
=IF(B2:B13=MAXIFS(B2:B13,A2:A13,A2:A13),B2:B13,"")

Excel-如何连续计数单元格直到达到相同的值 – Thinbug

7 个回答
7

7 个回答 7

7 个回答
7