vba - 计算过滤数据的模式

Question

我是一名攻读地质学硕士学位的研究生(我也不是 Excel 大师)，目前我的论文工作遇到了问题。

我的论文的主要观点是传达路易斯安那州陆上地区仍有大量未发现的碳氢化合物。我试图通过显示路易斯安那州的大部分油田在 1980 年之前运营(自 1980 年以来技术在该行业真正起飞，并且有许多新技术可以帮助重新开放其中一些旧油田)来量化这一点)。

我有一个大约 1850 页的 Excel 工作簿。每页包含一个油田的井数据。一些油田已经钻了数千口井，而另一些则只有几口井。这些表格中包含的众多数据之一是井许可证的日期。我想计算这些井许可证的模式年份，以确定每个油田达到“开发高峰”的年份。

我目前正在使用过滤器来消除大量不必要的数据，并尝试让自己更轻松地进行计算/宏创建。许可日期位于 I 列。我使用 Q 列中的 Year 函数从日期中删除了日和月，以便能够计算模式年。但是，Excel 拒绝计算模式，因为它一直尝试使用过滤后的信息进行计算，从而返回错误。模式没有小计功能，我在 Internet 上找不到适合我情况的解决方案。

过滤后的工作表如下所示:



对于解决方案，我需要公式来计算整个 Q 列的模式，因为许可数据的范围会因字段而异。

Answer 1

简而言之，我认为您需要将公式从 =Year() 更改为包括一个条件来查看该行是否是“必要”行。根据您发布的最新屏幕截图，我认为最简单的解决方案可能是在您的年份(Q)列中执行类似的操作，这是 Q2 中的公式:

=if(isnumber(H2),Year(I2),"")

这将导致任何无效行出现空白，您的模式函数可能只是:

=MODE(Q:Q)

哪个会知道忽略空白。

现在，我上面的公式过于简单化并且您的过滤条件无法用公式复制的可能性大于 0%。如果必须这样做，您可以创建一个自定义 VBA 函数来判断一行是否隐藏(过滤后的行将隐藏 = true)，如下所示:

Function Hidden(CellValue As Range) As Boolean
    Hidden = Rows(CellValue.Row).Hidden
End Function

第二季度相应的年份函数将更改为:

=IF(Hidden(A2),"",YEAR(B2))

并且将应用相同的模式功能。我在一个小电子表格上对其进行了测试。在过滤之前，此数据的模式为 2006:



如果我过滤掉 C 列中的非空白，以消除我的已知模式，E1 中的公式会更改为下一个模式，这表明过滤后的值被忽略: