☰

计算一行中第一个 1 出现后 [x] 个 0 的出现次数

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
计算一行中第一个 1 出现后 [x] 个 0 的出现次数

我有一张只有 0 和 1 的表格，大约有 50 列和 13000 行。我想要计算在第一个 1 出现后，每行中一系列特定数量的 0 出现了多少次。

示例：在给定行中，第 5 列中出现了一行中的第一个 1。我希望 excel 从第 5 列开始计数直到行尾（在这种情况下为第 50 列），相邻列中 x 个零相邻出现的次数。最终效果是一个表格，我可以在表格中看到：

  • 恰好有 1 个零相邻的系列数
  • 恰好有 2 个零相邻出现的系列数
  • 恰好有 n 个零相邻出现的序列数

然后我需要知道在上述情况下，有多少次发生了以下情况：

  • 1 连续在某个时间点再次发生
  • 1 直到行末才出现（直到第 50 列）我猜这两个事件是互斥的，所以如果我有给定系列的发生次数，我可以计算两者，同时只有 1 个。任何帮助都将非常感谢，谢谢！

答案1

据我了解您的任务描述，从整行来看，您只对第一个 1 之后的第一个连续 0 系列的长度感兴趣 - 不是所有可用的系列，而只是第一个。

您已将问题标记为 #microsoft-excel-2007。这让解决方案变得有些复杂。正如 @gns100 在其评论中指出的那样，现代版本提供了比 15 年前的程序更灵活的工具。但是，即使是最简单的方法也可以解决问题。

首先我们列出在大表的行中可能遇到的三种情况：

  1. 行中没有一个 1 - 这意味着没有一个系列。换句话说，此字符串中零的运行长度为 0（或 -1？）。

  2. 整行只有一个 1 - 这意味着从它之后直到行尾都有一条实心的 0 条带。此条带的长度等于整行的长度减去 1 的位置。

  3. 该行中有多个 1 - 第一个系列的长度将等于第二个 1 的位置减去第一个 1 的位置减 1。例如，对于行 {0,0,1,0,0,1,0...}第二个1的位置为6，第一个1的位置为3，它们之间的一串0的长度为6-3-1=2。如果两个1相邻，则序列的长度为0。

为了了解我们在下一行遇到了哪些选项，我们只需计算这一行中 1 的数量即可。COUNTIF() 函数将提供我们所需的数字。

现在我们使用SIGN() 函数将所有可能的选项减少到上述三个。类似于的构造SIGN(COUNTIF(A1:AX1;1)-1)+2将返回 1、2 或 3 - 列表中的选项数量。

现在我们使用选择（）函数来处理每个选择。

第一个选项最简单——它只是0。

对于第二个选项，要找到行中的位置 1，请使用MATCH() 函数

我们可以直接写50-MATCH(1;A1:AX1;0)。但是你~50在问题描述中使用了 ，也就是说，行的长度可能不同。因此，我们使用常数 50 来代替它COUNT() 函数

COUNT(A1:AX1)-MATCH(1;A1:AX1;0)

（对于我的例子，我使用斧头列作为要处理范围的右边界 - 您可能需要将其更改为另一个）

为了找到第二个和第一个 1 的位置，我们使用以下技巧：将所有 1 替换为它们的列号，并将所有 0 替换为空字符串 - IF(A1:AX1=1;COLUMN(A1:AX1);"")。因此行{0,0,1,0,0,1,0...}变成{,,3,,,6,,...}。

现在，使用SMALL() 函数，很容易得到所需的值：

SMALL(IF(A1:AX1=1;COLUMN(A1:AX1);"");2)-SMALL(IF(A1:AX1=1;COLUMN(A1:AX1);"");1)-1

让我们把所有部分都放到一个公式中。写成BE1

=CHOOSE(SIGN(COUNTIF(A1:AX1;1)-1)+2;0;COUNT(A1:AX1)-MATCH(1;A1:AX1;0);SMALL(IF(A1:AX1=1;COLUMN(A1:AX1);"");2)-SMALL(IF(A1:AX1=1;COLUMN(A1:AX1);"");1)-1)

并按以下方式完成输入Ctrl+Shift+Enter

将此单元格复制到数据末尾 - 现在在 BE 列中，您有每行第一个系列 0 的长度

要计算每个长度出现的次数，您可以使用数据透视表。或者你也可以选择其他方式 - 使用FREQUENCY() 函数：

  • 在单元格中写入BH1：BI1 模式未来表的标题，例如，[X]和數字 [X]
  • 填充单元格BH2：BH52其价值观来自0到50（例如，将公式写=ROW()-2在BH2并将其向下拖动）
  • 选择范围BI2:BI52，输入公式=FREQUENCY(BE1:BE13000;BH2:BH51)并按 Ctrl+Shift+Enter

现在您知道了计算的操作顺序，在 VBA 中以用户定义函数的形式陈述这一点并不困难：

Option Explicit

Function SeriesFrequency(rSource As Range) As Variant
Dim aResult As Variant
Dim rRow As Range
Dim nLenRow As Long, i As Long
Dim iLenSeries As Integer
Dim FirstPos As Long
    nLenRow = rSource.Columns.Count
    ReDim aResult(0 To nLenRow, 0 To 1)
    For i = 0 To nLenRow
        aResult(i, 0) = i
        aResult(i, 1) = 0
    Next i
    For Each rRow In rSource.Rows
        Select Case Application.WorksheetFunction.CountIf(rRow, 1)
        Case 0
            aResult(0, 1) = aResult(0, 1) + 1
        Case 1
            iLenSeries = nLenRow - Application.WorksheetFunction.Match(1, rRow, 0)
            aResult(iLenSeries, 1) = aResult(iLenSeries, 1) + 1
        Case Else
            FirstPos = Application.WorksheetFunction.Match(1, rRow, 0)
            iLenSeries = Application.WorksheetFunction.Match(1, rRow.Offset(0, FirstPos), 0) - 1
            aResult(iLenSeries, 1) = aResult(iLenSeries, 1) + 1
        End Select
    Next rRow
    SeriesFrequency = aResult
End Function

只需选择所需的范围，例如 BH2:BI52，输入类似的内容=SeriesFrequency(A1:AX13000)并点击Ctrl+Shift+Enter

现在您知道了如何使用COUNTIF(<range>,1)值，您可以轻松地解决任务的第二部分：如果范围内有多个单位，则系列 0 在到达行末尾之前结束。

相关内容