excel - Countifs 没有返回正确的值

Question

我有以下全部存储为文本的数据，默认为一般单元格格式。

=ISTEXT(G4)
=TRUE
'in both spreadsheets.

样本数据

两个工作簿相似。 A1 中的不同日期，以及此处和那里的不同行条目。

+---+-----------+-------------+-----+-----+------+-------------+--------------+----------+
|   |     A     |       B     |  C  |  D  |  E   |      F      |      G       |    H     |
+---+-----------+-------------+-----+-----+------+-------------+--------------+----------+
| 1 | MTOD List Exported as at Monday, 07 October 2019         |              |          |
| 2 |           |             |     |     |      |             |              |          |
| 3 | MTOD      | Title       | Dwg | Man | CDED | Issue Date  | Impl. Date   | Status   |
| 4 | 0101.0700 | Legend      |     |     |      | Apr 1, 1994 | Apr 1, 1994  | Active   |
| 5 | 0202.0310 | Roadway - A | 0   |     |      | Dec 1, 2005 | Dec 21, 2005 | History  |
| 6 | 0202.0310 | Roadway - A | 1   |     |      | Jan 1, 2007 | Mar 15, 2007 | History  |
| 7 | 0202.0310 | Roadway - A | 2   |     |      | May 1, 2009 | Jun 11, 2009 | Obsolete |
| 8 | 0202.0320 | Roadway - D | 0   |     |      | Jan 1, 2007 | Mar 15, 2007 | History  |
+---+-----------+-------------+-----+-----+------+-------------+--------------+----------+

当我从更新的工作簿中检查唯一条目时，我在 excel 中输入以下公式:

=COUNTIFS('[MTOD 115450 List.xml]Sheet1'!$A:$A,A4,'[MTOD 115450 List.xml]Sheet1'!$G:$G,"<>"&G4,'[MTOD 115450 List.xml]Sheet1'!$H:$H,H4)

更新书中的第 4 行与示例数据完全相同。意味着条目没有改变。我正在寻找的是相同的 MTOD 编号(Col A)，不同的 Imp。日期 (Col G)，相同状态 (Col H)

由于第 4 行是完全匹配的，我希望 countifs 返回 0，但它会返回 1。让我很困惑。我将countifs分解成每个ifs，看看我是否能捕获一些东西。我得到了以下结果:

MTOD 值 1 与预期的一样，是整个列中唯一的列表。

状态值 339 符合预期，因为列表

中有许多事件文档

1048576 的 IMP 还差得很远。

IMP 的值是电子表格中的行数。当我将过滤器应用于该列时，第 4 行实际上是唯一具有该日期的行。我本可以理解 1048576 -1 的计数。当我将 <> 更改为 = 时，我得到预期的答案 1。

在我进行计数的工作表中，我使用了 COUNTA(G:G) 并得到了 1015 的答案。

为什么 COUNTIFS 给出 IMP DATE 的值？

我需要做什么才能完整 COUNTIFS公式计算为 0？

我怀疑我将日期字符串转换为 Excel 日期作为该过程的一部分，它最终尝试将字符串与整数进行比较。如果不是当我将日期从 <> 切换到 = 时返回正确的值 1，我会购买它。

在尝试调试我的 excelformula 时，这一切都引起了我的注意，它以 1 而不是 0 返回相同的结果:

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Range("A:A"), varElement.Value, _
                                                    Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "<>" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value, _
                                                    Existing.Worksheets("Sheet1").Columns(StatusCol.Column), "Active")

我的逻辑有问题吗？

更新

链接到文件

02-MTOD 200124 List.xml (来源)

MTOD 115450 List.xml (现有)

2012 年 2 月 20 日更新

我已经修改了我的程序，它现在在打开工作簿时将日期从字符串转换为序列日期。现在，当我在 excel 公式中进行计数时，我得到正确的结果 0。当我通过 VBA 运行它时，我得到错误的结果 1。 关于 VBA 与 Excel 公式发生了什么的任何想法？

在查看监 window 口中检查 countifs 标准的值时，我注意到的一件事是监 window 口中的日期是 94/04/01，当我单击单元格时，公式栏中的日期显示为 1994/04/01。

VBA watch


Excel 日期


I5 中的 0 是它上面的测试 COUNTIFS 公式的结果，给出了预期的结果。

更新二 20/02/12

所以我只对日期组件进行了一些测试。我可以看到问题，但我不知道目前的解决方法。当您将比较运算符连接到日期时，它会出现，它将其转换为字符串而不是将其保留为日期。

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value)

SearchCount 将得到 1，这是正确的，因为这是该列中的唯一日期。

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "=" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value)

搜索次数 = 0

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "<>" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value)

搜索计数 = 1048576

那么如何使用 <> DATE 执行 COUNTIFS？我可以将 VBA 中的 DATE 更改为其序列日期并改用它吗？

Answer 1

所以最终的问题是，将运算符连接到日期会将日期更改为字符串。因此，存储在列中的序列日期不能正确地与字符串进行比较。解决方案是获取显然存储在 .value2 中的序列日期。一旦 VBA 公式更改为:

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "=" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value2)

or

SearchCount = Application.WorksheetFunction.CountIfs(Existing.Worksheets("Sheet1").Columns(ImpCol.Column), "<>" & Source.Worksheets("Sheet1").Cells(varElement.Row, ImpCol.Column).Value2)

如果您正在搜索的列中的日期是 excel 序列日期而不是文本日期，您最终会得到正确的结果。