☰

在 Excel 中，使用中间空格和可选小数格式化数字

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting cells cell-format
在 Excel 中，使用中间空格和可选小数格式化数字

Excel 中有一些数字，我想以某种方式格式化它们，我需要一些帮助。

背景：

我从事建筑行业，正在使用美国建筑规范协会 (CSI) 的 Masterformat 系统对项目中使用的建筑材料进行分类。该标准化系统的工作原理如下：任何类型的建筑材料或组件都会被赋予一个 6 位（或偶尔是 8 位）代码。这些数字的结构是这样的：前两位数字表示材料类型的总体类别（例如混凝土、金属、砖石等），其余数字成对出现时，您会越来越具体。

例如，03 52 16.13表示Lightweight Cellular Insulating Concrete，其各个部分分解如下，每个部分逐渐变得更加具体。

0300 00 是Concrete

03 5200 是Concrete Insulation

03 52 16是Lightweight Insulating Concrete

03 52 16.13是Lightweight Cellular Insulating Concrete

如您所见，这些数字具有特定的格式，这一点很重要。规则如下：

  • 规则 1 — 数字必须是 6 位数字（例如03 52 16），或者是 6 位数字后跟两位小数（例如03 52 16.13）
  • 规则 2 — 每对数字后必须有空格。 （03 52 16正确 ，035216错误）
  • 规则 3 — 对于以 开头的数字0，必须保留前导零。 （03 52 16是正确的，3 52 16是不正确的）
  • 规则 4 — 仅有六位数字的数字末尾不能有小数零。 （03 52 16正确，03 52 16.00错误）

这让我想到了我的问题：

我的项目中的 Excel 表格中有一长串数字，这些数字是手动输入的（不是通过公式）。如何设置允许这样做的通用单元格格式？

例子：

  • 键入12345 -------> 应该变成01 23 45
  • 键入123456 ------> 应该变成12 34 56 （不是12 34 56.00）
  • 键入123456.78 ---> 应该变成12 34 56.78 （不是12 34 56）

对于单元格格式...

00 00 00 .00满足规则 1+2+3，但违反规则 4。

00 00 00 .##几乎可以满足所有四条规则，但最后会留下一个奇怪的小数。

该怎么办？有没有不用条件格式的方法？谢谢。

答案1

确切的答案是NO。仅使用输入的数值和普通的英文版自定义数字格式工具无法完成所有需要的操作。

您可以使用条件格式来实现，但您要求不要这样做。事实上，这是处理有时需要小数点，但没有小数位时又不想要小数点的经典方法。

您还可以通过几种方法实现此目的，即使用辅助列中的公式来准备数字的文本版本。

因此，例如，如果您可以在某处维护一个格式不完美的列表，该列表可以通过公式引用，其中形式为“03 52 00。”可能会出现，因为没有人看到它，并且在向代码用户呈现时使用公式，然后使用代码呈现文本结果，IF并TEXT使用两种不同的格式字符串来表示两种可能的IF结果（因此，如果IF检测到没有小数，TEXT则将使用显示## 00 00 00，而如果它看到需要小数，TEXT则将使用显示## 00 00 00.##。

（我在每个数字的开头都添加了“##”，因为看起来您是在说可能会出现八位数字，我认为它位于小数点之前，但多读几遍后，我认为您的意思是“6”或“6.2”，后者是“八”的表示方式。因此，我提供了它们“以防万一”，但如果“八”表示“6.2”而不是“8”，则从格式字符串中删除这三个字符。）

如果计划就地编辑这些代码（可能是通过插入和删除代码），辅助表和文本输出可能会出现问题。不过，这似乎不太可能，所以它应该是合适的。而实际维护所在的单独数据表似乎更为可取。如果不是你的选择，那就不是你的选择——这是你的电子表格，不是我的——但它似乎更可取。

不过说实话，考虑到只有一条规则可以覆盖一个单元格区域，而这个区域可能有数千甚至数万个条目，很难看出条件格式会有多糟糕。不过，再说一遍，这是你的电子表格，不是我的，所以你自己选择吧。

但同样，虽然确实有方法可以实现所需的输出外观，但除了条件格式或每次选择并退出这些单元格时运行的宏，分析内容并以某种方式格式化，没有其他方法可以通过英文版 Excel 中的任何内置数字格式字符串实现您的愿望。人们对此已经哀叹了 30 多年。

如果您坚持只使用内置数字格式化工具，那么您可能需要考虑的最后一点是，Excel 允许您使用针对不同语言/国家文化的数字格式化工具。例如，选择“日本”，您会在其中找到各种日期格式化工具，而这些工具在任何“常见嫌疑人”网站上都没有提及。它们包括指示统治时期和年份的方法，不一定对任何非日本用途有用，但也包括日期部分的一些不同字母代码，这些代码似乎也适用于英语 Excel，有时会产生一些可能有用的奇怪效果。因此，您可以在 Excel 内部或互联网上或两者中逐一搜索，也许会找到一种文化，MS 已为其创建了一个格式化字符串工具，该工具将根据您在此处的需要抑制小数点。

我并不是想把最后的结果给予人类同胞，但是你知道......这不仅会给你带来快乐，而且如果你与我们大家分享，你就解决了 Excel 中最古老的问题之一。

答案2

可以尝试一下，也许并不完美，但可能效果足够好：
（这是在 Libreoffice Calc 6 中创建的，因此在 Excel 中可能略有不同）

应用格式并将CTRL-1其设置为00 00 00.##，
并在单元格上设置右对齐。

然后看看有缺陷的物品显然，将条件格式设置为
=NOT(OR(LEN(TEXT(C1,"000000.##"))=6,LEN(TEXT(C1,"000000.##"))=9))
Menu: Format > Conditional > Condition（在 Libreoffice 6 Calc 中设置）

下面的 *.csv 创建的示例显示。

在此处输入图片描述


使用任何文本编辑器将其保存special-format.csv，然后使用 OpenOffice Calc（其制作地点）或 Excel 打开它。

12345,"=TEXT(A1,""00 00 00.##"")",=A1,"=LEN(TEXT(C1,""000000.##""))","=NOT(OR(LEN(TEXT(C1,""000000.##""))=6,LEN(TEXT(C1,""000000.##""))=9))"
123456,"=TEXT(A2,""00 00 00.##"")",=A2,"=LEN(TEXT(C2,""000000.##""))","=NOT(OR(LEN(TEXT(C2,""000000.##""))=6,LEN(TEXT(C2,""000000.##""))=9))"
123456.7,"=TEXT(A3,""00 00 00.##"")",=A3,"=LEN(TEXT(C3,""000000.##""))","=NOT(OR(LEN(TEXT(C3,""000000.##""))=6,LEN(TEXT(C3,""000000.##""))=9))"
123456.78,"=TEXT(A4,""00 00 00.##"")",=A4,"=LEN(TEXT(C4,""000000.##""))","=NOT(OR(LEN(TEXT(C4,""000000.##""))=6,LEN(TEXT(C4,""000000.##""))=9))"

相关内容