☰

在 Excel 中查找基于时间的格式错误

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 conditional-formatting timestamp
在 Excel 中查找基于时间的格式错误

我有一列时间，其间隔为二十四小时（不一定从午夜或中午开始）。第一个是手动填写的，然后每个后续时间都根据公式“ [prevcell]+TIME(0,30,0)”自动填充，其中前一个单元格是当前单元格正上方的单元格。第一个单元格始终位于小时标记上，因此其格式为“n:00:00”，其中 n 为整数。因此，所有后续时间要么位于新的小时标记上，要么位于三十分钟标记上，这意味着它们将采用“n:m0:00”格式，其中 n 是整数，m 是 0 xor 3。

然后我尝试使用条件格式设置单元格，以便任何位于三十分钟标记处的单元格（即：对于整数 n，格式为“n:30:00”）具有浅灰色背景颜色（而所有其他单元格均为白色/无色）。因此，例如，包含 10:00:00 的单元格将为白色，但包含 10:30:00 的单元格将为浅灰色。

我这样做了：

  1. 规则类型：“仅格式化包含以下内容的单元格”。

  2. 使用以下方式格式化单元格：“单元格值”、“等于”、“ =TIME(HOUR([curcell]), 30, 0)”。 （其中当前单元格是被格式化的特定单元格本身。）

  3. 选择适当的背景颜色格式。

格式化对大多数单元格都有效。如果它由于某种原因对第一个或最后一个单元格不起作用，我也许可以理解，但它通常都有效。它不起作用的正是具有以下值的单元格：11:30:00、18:30:00（下午 6:30:00）、21:30:00（晚上 9:30:00）、22:30:00（晚上 10:30:00）；这些单元格在应该着色时没有着色（白色）。它适用于所有其他单元格，包括 23:30:00（晚上 11:30:00），即使它对 11:30:00（上午）的单元格不起作用。没有不应该着色的单元格；唯一应该着色时没有着色的单元格是上述单元格。

当我更改最顶层单元格中的种子值/时间时，会出现完全相同类型的不同错误（通常但并非总是针对相同的值，有时针对其他值）。

有人知道哪里出了问题或者如何解决这个问题吗？

编辑！以下是我的一些观察：

  • 无论我的第一个单元格中的时间是多少（00:00:00 除外），它在下一个午夜之后的任何时间都不起作用。对第一个单元格值的依赖似乎如下：
  • 上午 11:30 在我的第一个牢房中是特定时间。
    1. 它不适用于 00:00:00、00:30:00、1:00:00、1:30:00 和 2:00:00。
    2. 它适用于 2:30、3:00、3:30。
    3. 4:00、4:30、5:00、5:30、6:00、6:30 不起作用。
    4. 它适用于 7:00 之后的所有时间，以 30 分钟为增量，直到 11:30（含）。（该种子值之后不再考虑）。
  • 我没有进行广泛检查，但从快速浏览来看：18:30:00、21:30:00 和 22:30:00 永远不起作用。
  • 根据我的第一个单元格的值，某些其他 n:30:00 时间不起作用（对于整数 n），但我找不到模式。据我所知，对于任何 n:00:00 时间（对于整数 n），都没有错误。

更新 #1

我已将格式化条件更改为：使用“单元格值”、“不等于”、“ =TIME(HOUR([curcell]), 0, 0)”来格式化单元格。（其中当前单元格是正在格式化的特定单元格。）

这似乎在一定程度上改善了这种情况，但并不完美。它正确地格式化了所有内容，除了它不正确地用这些时间着色单元格：20:30:00（晚上 8:00:00）、23:00:00（晚上 11:30）以及（如果存在）下一个午夜（24:00:00；又称：下一个凌晨 12:00）之后的所有时间。

（编辑：它不正确着色的时间似乎取决于我的第一个单元格的值以及其他因素。但当天晚些时候总会有两个离散错误和午夜后的问题。）

更新 #2

主要问题已经解决。但是，如果有人能告诉我为什么会出现这种情况，我将不胜感激。好奇的人想知道！

答案1

检查下面的屏幕截图，显示成功对单元格中的“分钟”部分进行颜色格式化（灰色），如果是 30，正如您所要求的。

在此处输入图片描述

按着这些次序。

  1. 选择您要输入时间的数据范围。
  2. 应用单元格格式为 HH:MM:SS，然后输入时间值。
  3. 选择要应用条件格式的数据范围。
  4. 在“主页”选项卡中单击“条件格式”、“新规则”，最后单击“使用公式”来确定要格式化的单元格。
  5. 写入此公式=MINUTE(A2)=30，然后选择颜色，单击“确定”，其中 A2 是您当前选择的数据区域的一部分。

注意： 请记住，为了更好地理解，仅在 B 列中我提到了时间段，因为您还写了条件格式在午夜和其他时间之后不起作用。

还， 在 C 列中，我从 A 列的日期中找到了“分钟”，只是为了显示和匹配相邻单元格中的值，以确保我的解决方案仅在分钟值仅为 30 时才以灰色格式化单元格。

希望这对您有用。

注意：或者，按照上面写的步骤，=MINUTE(A2)=30您也可以使用这个公式来代替=TIME(0,MINUTE(A2),0)=TIME(0,30,0)。

答案2

我认为这个问题是由 excel 计算方式引起的，请参阅此链接

https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

因此，当你添加时间时，结果并不完全是分钟，而是略微大于或小于分钟，因此你无法获得与比较完全匹配的结果

MINUTE当值略高于时，使用函数会起作用，但低于时则不会。你可以使用SECOND函数来检查我的理论，看看你的数据中是否有任何值为 59

为了修复，假设您处理的是整分钟，那么您可以四舍五入到最接近的分钟，然后检查分钟值，例如 A2 中的时间值

=MINUTE(MROUND(A2,"0:01"))=30

更新：

我通过将时间值放在一列中，然后在下一列中使用公式来检查这一点，因此我的时间值从 A2 开始，我在 B2 中使用了这个公式向下复制

=A2=TIME(HOUR(A2),30,0)

对于 18:30 我得到了FALSE，当我检查值时，A2 中的值是这样的

0.770833333333334

但其价值TIME(HOUR(A2),30,0)如下：

0.770833333333333

因此，除非您按照建议在比较之前对数据进行四舍五入，否则不会匹配

相关内容