☰

Excel：在带有星号的单元格中按小数点对齐

tag-icon • tag-icon microsoft-excel format alignment
Excel：在带有星号的单元格中按小数点对齐

在 Excel 2011 for Mac 中，我有以下一列数字：

0.139**
-0.131**
-0.135*
-0.053
0.139**
0.112*
0.183***

有些项目是负面的，有些则有星级。

我的目标是对列进行格式化，以便 1）小数点前没有零，并且 2）数字在小数点处对齐，如下所示：

 .139**
-.131**
-.135*
-.053
 .139**
 .112*
 .183***

在阅读了 SuperUser 上的许多其他帖子后，我怀疑我需要从“格式 -> 单元格 -> 自定义”首选项窗格中执行此操作，但我没有成功。特别是，带有星号的单元格似乎不受格式化的影响。我很感激任何帮助。谢谢。

答案1

您可以使用自定义格式在数字上添加星号（例如 0.000“***”），但仅限于包含数字的单元格。

如果单元格包含数字后跟星号，则 Excel 会将该单元格视为文本而不是数字，因此您将无法应用数字自定义格式或对其执行算术运算。

我建议您尝试将数字和星号分成相邻的单元格（两列）。

答案2

我不相信仅使用常规自定义格式就能做到这一点。条件格式（“CF”）也不能做到这一点。

问题的关键在于，除非您可以使用 <、>、=、<= 或 >= 条件来定义可能性，否则您无法使用常规自定义格式功能来格式化数字后面（或前面或里面，无论什么）的可变数量的文本字符。然后，不能超过两个。您的星号是可变的，没有明显的规则可以使用这些条件。无论如何，在局外人看来。而且你至少需要三个。成功的另一端是您无法格式化文本以将小数点排列在 CF 之外。

CF 的问题在于它无法格式化部分内容，只能格式化整个内容。因此，虽然它可以根据单元格内容的一部分（此处的数字方面）进行应用规则的测试，但它无法仅针对该部分进行操作。它无法格式化数字部分以使小数点对齐，因为只有部分数据是数字。所以它毫无帮助。

让我稍微离题一下。如果你可以在数据旁边使用辅助列，或者在电子表格的某个角落（实际上，我猜是数据所在的位置，数据在电子表格的某个角落），下面的公式就可以解决问题：

=IF(VALUE(SUBSTITUTE(A1,CHAR(42),""))>=0,
    TEXT(VALUE(SUBSTITUTE(A1,CHAR(42),""))," #.###")  &  REPT(CHAR(42),LEN(A1)-0-LEN(TEXT(VALUE(SUBSTITUTE(A1,CHAR(42),""))," #.###"))),
    TEXT(VALUE(SUBSTITUTE(A1,CHAR(42),"")),"#.###")  &  REPT(CHAR(42),LEN(A1)-1-LEN(TEXT(VALUE(SUBSTITUTE(A1,CHAR(42),"")),"#.###"))))

是的，这看起来很悲伤，但它表面上的复杂性并不是真实的，再加上现在是 2021 年，所以任何读过这篇文章的人可能都已经了解了，LET()这会让它看起来没那么可怕。

它所做的就是：

Line 1:从数据中取出数值，并了解它是正数还是负数。 Line 2:如果是正数，则用字符串格式化该值#.###（注意开头的空格）。这使其向右突出，以允许负值数据中的负号，从而使小数点对齐。 Line 3:如果是负数，则格式几乎相同，只是没有前导空格。

它还做一件事：它找到初始字符串的长度并减去值字符串的长度以找到星号的数量（CHAR(42)因此您不必在公式中“处理”该字符）并使用将REPT()许多星号添加回到生成的字符串上。

IF()可以通过不在 的两个部分添加星号，而是在 输出值后将它们连接到末尾来“收紧”公式IF()。此外，现在可以使用范围，而不是沿列向下复制公式，然后让SPILL处理填充列。

最后，这真的很可悲……除非您使用等宽字体，否则对齐小数点将无法完美工作。否则，数字的可变宽度将确保小数点仅大部分对齐，而且几乎肯定会非常明显。虽然不是很糟糕，但可能足以毁了它。这是因为上面的结果需要左对齐才能让小数位对齐。如果还有别的，那就是每行（在您的示例数据中）有三到六个字符这一事实意味着它们根本无法对齐。

我承认，你可以使上述公式复杂化，在完成上述所有操作后添加一个步骤，在每个值的右侧用一些空白字符（如“空格”）填充，使所有值的长度相同，但比例数字字符的不同宽度会像弄乱小数点一样弄乱它，所以即使完成后，它可能也不是非常令人愉快。如果你想这样做，我会这样说：每种字体都有一些字符，它们的宽度恰到好处，可以根据需要将它们隔开。它们并不全是空白字符……但如果你能找到一种你喜欢的字体，并且有合适的空白字符，也许一切都会很好。你知道，“如果”……

最后，要将它们的值用于其他任何用途，您必须像在公式中一样删除这些值。所以不要只说“=A1”。您必须删除星号（如果您这样做，可能还需要删除其他一些字符），然后VALUE()在结果上使用并将该表达式放在您想要提取值的任何地方。或者在某个地方创建一个辅助列，填充该列的公式，您可以直接引用它。

所以，最后一件事。我只是想知道星号的意义/用途。我能想到的最好的办法是它们是作为脚注标记。如果是这样，或者性质类似，您可以考虑使用不同颜色的值。然后数据都是值，而不是字符串，简单的格式化使它们变得漂亮。有一个来自旧 Excel 4 宏语言的函数，GET.CELL()使用参数 24 作为其第一个参数，可以在命名范围（而不是单元格）中使用它来查找单元格的字体颜色，然后在单元格公式或 CF 中使用命名范围来使用单元格的字体颜色来执行操作。因此，取消星号也许是可行的，特别是如果数据输入者只输入它们，然后您按公式使用它们的计数来执行操作……您只会改变您学习所需知识的方式。

相关内容