如何计算一个值的数量？

I am dealing with 956 values in a row. I have a problem in getting the row distances in each value because there are too many of them. Is there a way to achieve this using Excel-VBA with a button?

我连续处理956个值。我在获取每个值中的行距离时遇到问题,因为它们太多了。有没有办法使用带按钮的Excel-VBA实现这一目标?

For example: This shows the number of times where the distance of a row is counted.

例如:这显示计算行距离的次数。

1 个解决方案

#1

Create a user-defined function in a module, with this code:

使用以下代码在模块中创建用户定义的函数:

Option Explicit

Public Function Distance(matrix As Range, value As Long, rowCount As Long)
    Dim cell As Range
    Dim lastRow As Long
    Dim result As Long

    lastRow = 1
    For Each cell In matrix
        If cell.value = value Then
            If cell.row - lastRow + 1 = rowCount Then result = result + 1
            lastRow = cell.row
        End If
    Next
    Distance = result
End Function

That is all the code you need. Now in the Sheet put this formula in cell I5, i.e. the top-left cell of your results table:

这就是您需要的所有代码。现在在Sheet中将此公式放在单元格I5中,即结果表的左上角单元格:

=Distance($A$1:$F$11, $H5, I$4)

Depending on how many rows are in your source table on the left, you may need to adjust the 11 in that formula to some larger number, so the whole table is referenced there.

根据左侧源表中的行数,您可能需要将该公式中的11调整为更大的数字,以便在那里引用整个表。

Then copy this formula to the rest of your output table: drag/copy horizontally, then select that row, and drag/copy downwards.

然后将此公式复制到输出表的其余部分:水平拖动/复制,然后选择该行,然后向下拖动/复制。

The table will fill with lots of zeroes as well. To get rid of those, use cell formatting. Use this custom format:

该表还将填充许多零。要摆脱这些,请使用单元格格式。使用此自定义格式:

0;-0;

This is the result (ignore that you see semi-colons in the formula; those are related to my regional settings):

这是结果(忽略您在公式中看到分号;这些与我的区域设置有关):

There is no need of a button. The results are updated like any other standard formula would do.

不需要按钮。结果会像任何其他标准公式一样更新。

Note that you could name your function with another name, if you want, but make sure to do the same change in the formulas you have in the sheet.

请注意,如果需要,可以使用其他名称命名函数,但请确保对工作表中的公式进行相同的更改。

#1