☰

Microsoft Excel：条件数字格式

tag-icon • tag-icon microsoft-excel conditional-formatting
Microsoft Excel：条件数字格式

格式中有一列包含时间段hh:mm:ss。秒数未使用。三小时两分钟的时间段将是03:02:00。如何有条件地格式化单元格以获得以下结果？

00:00:00→0 minutes

00:01:00→1 minute

00:02:00→2 minutes

01:00:00→1 hour

02:00:00→2 hours

03:01:00→3 hours, 1 minute

01:03:00→1 hour, 3 minutes

我尝试了如下自定义格式字符串，但没有成功：

[<60]hh:mm:ss "minutes";[<120]hh:mm:ss "minute";[<3600]hh:mm:ss "minutes";hh "hour(s)," mm "minute(s)"

答案1

我不知道有任何内置的“标准”日期/时间格式可以以这种方式很好地格式化，所以是的，您尝试自定义格式似乎是合适的。

但是，它对您不起作用的原因是时间值不是像您假设的那样以秒表示 - 它们以十进制值存储，其中 1 代表 24 小时。

因此，您必须使用与所需断点等价的十进制值。不幸的是，Excel 似乎将断点数限制为三个，因此我无法获得“1 分钟”（单数）等的额外断点。即使有更多断点，您也永远无法实现“N 小时”，因为您需要无限的断点（没有条件检测零分钟组件）。

因此，我可以用自定义格式获得最接近的结果：

[<0.000694]"0 minutes";[<0.041666][m] "minutes";h "hours", m "minutes"

得出以下结论：

00:00:00    0 minutes
00:01:00    1 minutes
00:02:00    2 minutes
00:03:00    3 minutes
01:00:00    1 hours, 0 minutes
01:01:00    1 hours, 1 minutes
02:00:00    2 hours, 0 minutes
02:01:00    2 hours, 1 minutes
01:03:00    1 hours, 3 minutes
23:59:59    23 hours, 59 minutes

我可能会建议编写一个 VBA 函数并格式化公式中的字段。

但同时，我建议您首先考虑该值是否应该是时间。以特定单位存储经过的时间会更好（例如，在您的情况下为分钟）。您仍然无法拥有无限的断点，但可以避免上述一个明显问题 - 它会在 23 小时 59 分钟后滚动。

编辑：为了好玩，我制作了一个公式，可以产生您想要的结果。这不是您的问题所要求的格式，但因为它没有给出所需的结果，所以我认为这可能是一个很好的替代方案：

=if(hour(a1)=0,if(MINUTE(A1)=0,"0 minutes",if(minute(a1)=1,"1 minute",minute(a1) & " minutes")),if(hour(a1)=1,if(MINUTE(A1)=0,"1 hour",if(minute(a1)=1,"1 hour, 1 minute","1 hour, " & minute(a1) & " minutes")),if(MINUTE(A1)=0,hour(A1) & " hours",if(minute(a1)=1,hour(A1) & " hours, 1 minute",hour(A1) & " hours, " & minute(a1) & " minutes"))))

00:00:00    0 minutes
00:01:00    1 minute
00:02:00    2 minutes
00:03:00    3 minutes
01:00:00    1 hour
01:01:00    1 hour, 1 minute
02:00:00    2 hours
02:01:00    2 hours, 1 minute
01:03:00    1 hour, 3 minutes
23:59:59    23 hours, 59 minutes

答案2

选择时间列并简单地应用此格式。h" hours and "m" minutes"您的日期列将如下所示。

在此处输入图片描述

答案3

您可以尝试TEXT()在包含大量嵌套 的公式中使用IF()。这对您来说会非常麻烦，因为您有太多可能的格式。我数了一下，有 8 种不同的可能性。

例如，这个公式

=IF(HOUR(A1)=1,IF(MINUTE(A1)=1,TEXT(A1,"h"" hour, ""m ""minute"""),TEXT(A1,"h"" hour, ""m ""minutes""")),TEXT(A1,"h"" hours, ""m ""minutes"""))

进行一些您想要的格式化，如下表所示。

函数内部的格式TEXT()必须用单引号括起来，但小时和分钟标签周围的引号必须使用双引号进行“转义”。

在此处输入图片描述

另外，我无法正确显示分钟数。此公式为 A 列中的每个值给出“1 分钟”：

=TEXT(A1,"m"" minute""")

答案4

您无法使用常规单元格格式来实现这一点，因为 Excel 只会通过其常规单元格格式采用其中两种条件格式。您有四种变体。

因此，请将常规单元格格式设置为最容易编写的格式，因为在常规格式中，表达条件的方式相当有限，但在条件格式规则中测试起来却最困难。这将是 1 小时 1 分钟的变化：

hh" 小时, mm" 分钟"

因此，无需进一步工作，您就会得到 23 小时 14 分钟的结果。不过不用担心，因为您还要做进一步的工作。

然后启动条件格式。设置一条规则，测试小时中的“单一性”，但测试分钟中的“复数性”。将格式设置为：

hh" 小时, mm" 分钟"

（为了“精确”，您可以只使用单个“h”，因为您从测试中知道它将是一个数字，准确地说是 1。但这两种方法都有效，而且单个“h”方法可能会掩盖工作某些方面的失败。

然后是另一种类似的变化，小时复数，分钟单一，格式如下：

hh" 小时, mm" 分钟"

最后，既不具有单一性，也不具有以下格式：

hh” 小时，“mm” 分钟”

您可以通过多种方式测试每个元素，但最干净（尽管不是最简单的）的方法是通过使用函数进行转换来测试每个元素，TEXT()以便将其真正转换为输出，测试文本结果与字面上的“01”，而如果它是数字，则需要测试“普通”1。使用函数AND()：

=AND(TEXT(A1,"HH")="01",TEXT(A1,"MM")<>"01")

（请注意 HH 和 MM，以免掩盖数据或过程中的错误而导致事情出错。该TEXT()函数产生一个真实值，而不是仅显示其内容的一部分，而实际上是其他东西。因此，如果时间是 3:34，则它会为完全过去 34 分钟的小时产生 03，为完全过去 3 小时的分钟产生 34。

对于第二种格式，反转比较，并对最后一种格式的两个比较都进行“<>”。

最后一个要素，但极其重要： 确保选中“如果条件满足则停止！”复选框。

这看起来确实微不足道，但请考虑一下这种方法与一组非常复杂的问题，比如 8-10 个用于为文本着色的类别，同时希望可以添加填充颜色或仅使用填充颜色。您将得到一个庞大的公式，如如果这个和如果那个等等。但是这种技术呢？只需一个接一个地测试 7-9 条简单规则（请记住第一个规则是以正常格式设置的）。哪一个更容易维护？添加条件？更改条件？让其他人接手并快速了解您的工作？

太棒了！就这个！

哦，在这个例子中，您不必在复杂的嵌套 IF 中进行任何测试，因此单元格的基本公式可以是“所有业务” - 仅仅是其基本计算所需的材料 - 因为格式化所需的所有 IF（在某些情况下是强制结果）都在简单的 CF 规则中。

相关内容