我希望 Excel 单元格中具有以下格式:
123g 99s 99c
100 铜(c)= 1 银(s)
100 银 = 1 金(g)
因此,如果我输入,12394
它将显示为“1g 23s 94c”。
这可能吗?
答案1
尝试一下这种自定义格式:
#0"g" #0"s" 00"c"
请注意,无论实际金额是多少,这都会显示所有三个“单位”,因此12
将显示为“0g 0s 12c”。如果您只想显示金额非零的最大“单位”(因此12
将是“12c”,598
将是“5s 98c”等),您可以按如下方式进行调整,以根据值实际应用不同的格式:
[>=10000]#0"g" #0"s" 00"c";[>=100]#0"s" 00"c";#0"c"
它并不完美,例如10245
将显示为“1g 02s 45c”,而不是“1g 2s 45c”,因为它只是拆分单元格值而不是将其除以 100 来获得单独的金额,但除此之外它应该可以做您想要的事情。
要处理负值,您必须将上述格式与条件格式相结合,因为 Excel 的单一数字格式最多只能有 3 个条件。请尝试以下操作(我假设 Excel 2010,也应该适用于 2007,不确定早期版本是否适用):
- 选择单元格并转到“条件格式”→“新规则......”(在“主页”选项卡下)
- 选择“使用公式确定要格式化的单元格”
- 在公式框中输入
=A1>=0
,然后单击“格式...” - 选择“数字”选项卡,然后选择“自定义”,然后输入上面的数字格式:
[>=10000]#0"g" #0"s" 00"c";[>=100]#0"s" 00"c";#0"c"
- 单击两次“确定”。
- 重复步骤 1 和 2。
- 在公式框中输入
=A1<0
,然后单击“格式...” - 选择“数字”选项卡,然后选择“自定义”,输入以下数字格式(基本与第一个相同,除了处理负值):
[<=-10000]-#0"g" #0"s" 00"c";[<=-100]-#0"s" 00"c";#0"c"
- 单击两次“确定”。
如果您需要修改或删除格式规则,或者扩展它们以应用于更多单元格,只需转到“条件格式”→“管理规则”。
另一个选项是使用 VBA 函数来格式化值。按Alt+F11打开 VBA 编辑器,在项目窗口中单击鼠标右键,然后选择“插入”→“模块”,然后粘贴以下代码(当然,这只是编写此类函数的众多方法之一):
Function MyFormat(intValue)
Dim strReturn As String
If intValue < 0 Then strReturn = "-"
intValue = Abs(intValue)
If intValue > 10000 Then
strReturn = strReturn & Fix(intValue / 10000) & "g"
intValue = intValue - (Fix(intValue / 10000)) * 10000
End If
If intValue > 100 Then
If Len(strReturn) > 0 Then strReturn = strReturn & " "
strReturn = strReturn & Fix(intValue / 100) & "s"
intValue = intValue - (Fix(intValue / 100)) * 100
End If
If Len(strReturn) > 0 Then strReturn = strReturn & " "
strReturn = strReturn & intValue & "c"
MyFormat = strReturn
End Function
与使用自定义数字格式相比,此方法为您提供了更大的灵活性,并且比管理条件格式规则更易于使用 - 只需键入=MyFormat(12345)
而不是12345
。然而,缺点是您失去了对数据执行数学运算的能力,因为 Excel 现在将值视为文本,而不是数字。
答案2
以下自定义格式将满足您的要求:
??\g ??\s ??\c