☰

Excel 2019：尝试查找一个数字在另一个特定数字之后出现的次数

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 2019：尝试查找一个数字在另一个特定数字之后出现的次数

我有大约 15K 行，总共 20 列。每个单元格都是一个数字。我需要找出一个数字在另一个数字之后出现的频率。例如，18 在 17 右侧的单元格中出现的频率是多少。但我需要在 15K 行中对 400 多个数字执行此操作。有什么公式化的想法吗？考虑过 =concat，但数字范围是 0-9999，所以它可能介意 1718，并在 concat 函数之后认为是 17 18。我对此持开放态度，我已经在这个问题上呆了一段时间了。

答案1

如上所述gns100，组合单元格以避免您描述的问题的标准方法是插入一个确信在数据中不存在的字符，也不应该在未来的数据中出现该字符。

实际上，“管道”符号在键盘上通常很方便。它在字体中也足够独特，如果从字面上看，它在视觉上还是有点用处的。但是，任何字体都提供了很多好的字体，选择一种后，您可以搜索它以查看它是否已经存在于数据中。在这种情况下，由于数据只有数字，所以它或任何字母都可以。

因此，您要寻找的是像“17|18”这样的字符串，而 8 前面的 171 将是“171|8”，因此不匹配。

在字符串中搜索某个部分的第二种真正标准的方法是找到字符串的长度，使用SUBSTITUTE它来将您要查找的内容更改为空（""），并从前者中减去后者。如果它等于一个值，则至少找到了一个实例。如果您想知道有多少，您可以将该结果除以搜索字符串的长度。因此，如果字符串的长度为 80，替换后的长度为 55，搜索/替换字符串长度为 5，则搜索字符串的实例为 25/5 = 5。

为此，按行，您可以使用如下公式：

=(LEN(TEXTJOIN("|",TRUE, B1:U1))  -  LEN(SUBSTITUTE(TEXTJOIN("|",TRUE, B1:U1), TEXTJOIN("|",TRUE, A1&"|"&(A1+1) ), "")))  /  LEN(TEXTJOIN("|",TRUE, A1&"|"&(A1+1) ))

并将其复制到列下。（想象一下 A 列中每行的搜索值，因此也复制到列下以匹配行。以及 B1:U26250 中的数据。）

然后……嗯，整个事情有点难以处理，你的语言表明你只想要出现在行中的配对，所以单元格 B3 和 C3 就是如此，但单元格 B3 和 B4 不是有效配对，但你确实想搜索整个数据集，以查找其中任何位置的行配对（在我的示例中是所有 26,250 行）。另一种方法是单独了解每一行的结果，这是可能的，但很难用可能性来描述，因此我不会“去那里”，除非问题中有更明确的东西。

那么，搜索“全部内容”又如何呢？这实际上要容易得多。上面的计算对于一张有 26,250 行的表格来说，乍一看是瞬间完成的。下面的计算也是如此。公式中也没有细目。

至于细分，比如TEXTJOIN，令人担忧的是 Excel 只允许单元格中的文本长度为 32,767 个字符。而许多函数的限制要短得多，有些在 6,000 个范围内，有些在 8,000 个范围内。此外，有些函数只允许参数“这么长”。(VLOOKUP例如。它的查找参数将只被视为 256 个字符，因此如果您在公式中将其作为公式其他部分的结果提供，您可能会提供更长的内容而没有意识到它正在发生......)这里的陷阱列表很长。

通常人们没有意识到的是，只要不导致函数本身超出其自身限制，Excel 就可以在公式评估中使用大量材料。因此，使用TEXTJOIN并将其最大输出保持为 6,000 个字符，这样它就不会在某些尴尬的地方切断传递给公式其余部分的数组，然后应用它，以便公式在同一计算中处理 26,250 行，即使处理的材料是 26,250*6,000 = 157,500,000 个字符，Excel 也会愉快地使用它。因此，公式长度就像是 32,767 个文本字符限制的 150 万倍。

甚至对简单的Joining数据也有效。以下公式查找您需要检查的 400 多个输入中的一个的配对：

=SUM(  ($B$1:$T$26250 & $C$1:$U$26250 = A1 & (A1+1))  *1)

注意它连接的两个范围中的偏移量。它获取从第一列到倒数第二列的配对的左侧，以及从第二列到最后一列的配对的右侧。它将这些结果字符串（每个字符串都远小于 Excel 的字符串限制，但随后有很多不太长的字符串）与搜索配对进行比较，并生成一个内部 TRUE/FALSE 结果数组（另一个似乎违反限制的巨大部分，但正如所解释的那样，实际上不受这些限制的约束）。人们可以使用来计算COUNTA其中有多少个 TRUE，但对于 Excel 用户来说，另一个非常标准的事情是强制 Excel 通过将该数组乘以 1 将它们变为 1 和 0。实际上，对这样的数组进行任何算术都可以，因此将其中两个相加或相乘就可以自己完成，但是像这样的单个数组必须将这一位添加到公式中。然后，一旦数组全部为数字，SUM就将它们加起来。

此公式查找单个单元格（此处为 A1）以查找要查找的内容。因此，您可以将 400 多个值的列表放在一列中，然后将公式复制到它们旁边以获取 400 多个值中的每一个的结果。

答案2

获取计数的最简单方法是使用COUNTIFS功能。

这COUNTIFS函数将条件应用于多个范围内的单元格并计算满足所有条件的次数。
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

仅选择 1以下 3 个公式中：

  1. 是最简单的
  2. 以 #1 为基础
  3. 以 #2 为基础

公式1）

D3您可以粘贴下面的公式D4:D10来实现您的目标。

=COUNTIFS(A:A,E3,B:B,F3)

LAMBDA 公式（#2）

上面的基本公式可以使用LAMBDA函数。这将允许你使用单一配方可以D3动态适应不断变化的行数据和查找值

用一个LAMBDA函数可创建自定义、可重复使用的函数，并使用友好名称调用它们。新函数可在整个工作簿中使用，并可像原生 Excel 函数一样调用。
=LAMBDA([parameter1, parameter2, …,] calculation)

第 1 步（共 2 步）

将上面的基本公式转换为等效公式LAMBDA公式。

1   =LAMBDA(_col1, _col2,
2           COUNTIFS( _col1, E3, _col2, F3 )
3    )( A:A, B:B )
4
5    #  Line numbers in this [code block] are only to 
6    #  allow me to refer to portions of the code below.
  • 第一行：二名称针对两者进行定义值被传递到LAMBDA函数函数。需要COUNT(names)=COUNT(values)
  • 第 3 行：二值被定义为传递到LAMBDA函数。在本例中，它们都是范围。
  • 第2行：替换原有基本公式的公式。
# Original formula compared to Line of LAMBDA based formula

  Original:  COUNTIFS(  A:A, E3,   B:B, F3)
  Line 2:    COUNTIFS(_col1, E3, _col2, F3)
第 2 步（共 2 步）

现在通过删除硬编码值E3并F3使用MAP功能。

这MAP函数返回一个数组，该数组由通过应用LAMBDA创造新的价值。
=MAP(array1, lambda_or_array<#>)

=MAP(E3:E10, F3:F10,
     LAMBDA(_val1,_val2,
            (LAMBDA(_c1,_c2,
                    COUNTIFS(_c1,_val1,_c2,_val2)
            )(A:A,B:B)
 )))

LET 公式 (#3)

如果你不想定义查找数组中的行数（查找对），你可以调整LAMBDA上面的公式LET函数。如果查找数组本身是动态生成的，或者公式在具有不同大小数组的其他工作表上重复使用，或者出于其他原因，这很有用。

这LET函数为计算结果分配名称。这允许在公式中存储中间计算、值或定义名称。这些名称仅适用于LET函数。与编程中的变量类似，LET是通过 Excel 原生公式语法完成的。

要使用LET在 Excel 中，您可以定义名称和关联值对，以及使用它们全部的计算。您必须定义至少一个名称/值对（一个变量），并且LET最多支持 126。

LET允许过滤E:F并存储在_valArr结果中，结果仅包含值为 <>"" 的行，并且排除E1:F2此电子表格中的标题行。_valArr然后，数组被拆分为两列，分别存储在_val1Col和中_val2Col。现在，最后这些列用于代替E3:E10和，F3:F10位于同一个MAP上一个公式中使用的函数。

=LET(
  _valCols, E:F,
  _valArr, FILTER(_valCols,(INDEX(_valCols,,1)<>"")*(ROW(_valCols)>2)),
  _val1Col, INDEX(_valArr,,1), _val2Col, INDEX(_valArr,,2),
  MAP(_val1Col, _val2Col,
      LAMBDA(_val1,_val2,
            (LAMBDA(_c1,_c2,
                    COUNTIFS(_c1,_val1,_c2,_val2))(A:A,B:B)))))

相关内容