英文:
Excel number formatting thousands is not working
问题
使用Excel时，我试图将数字格式化为以千为单位显示。
1000 应显示为 1 千。
通常，我使用Excel的数字格式。根据语言的不同，在我的格式化数字的最后一位后添加一个空格或逗号，以便进行 x1000 的除法运算。
我以前这样设置过：
格式化千位数字：0, "K"（美国）或 0 空格 "K"（例如法国）
格式化百万位数字：0,,"M"（美国）或 0 双空格 "M"（例如法国）
然而，现在它不再起作用，而是只显示我的数字后面有一个逗号/两个逗号，而不是以千或百万为单位。
你知道这可能与什么有关，以及如何设置单元格格式吗？
在我的高级设置中，我使用逗号作为小数分隔符，空格作为千位分隔符。我取消了地区参数。
以下是我的设置详细信息：
高级设置<br>
以下是几个示例显示的内容：
不起作用案例的详细信息<br>
注意：我希望避免使用公式，因为我只想对单元格格式进行操作。目前，我不太满意的解决方法是创建一个“镜像”页面，使用 =ROUND(Cell/1000000;0) 来显示百万，例如。
谢谢你的帮助。
英文:
With Excel, I am trying to format my numbers to display as thousands.
1000 should be 1 k.
I usually use the format numbers from Excel. Depending on the language, I add a "space" or a "," after the last digit of my formatting in order to get a x1000 division.
I used to have:
Format numbers in thousand: 0, "K" (US) or 0 space "K" (French, for instance)
Format numbers in millions: 0,,"M" (US) or 0 double-space "M" (French, for instance)
However, it is not working anymore and now it just displays my numbers with a comma/two commas at the end instead of having thousands or millions.
Do you know what it could be related to and how to parameter the cells format?
In my Advanced settings, I am using the "," as Decimal separator, and the " " as Thousands separator. I unchecked the regional parameters.
Here are the details of my settings:
Advanced settings<br>
Here is what is shown on several examples:
Details of cases not working<br>
Note: I would like to avoid having a formula as I only want to act on the cells format. For now, my workaround (not satisfying) is creating a "mirror" page using =ROUND(Cell/1000000;0) to display Millions, for instance
Thank you for your help
答案1
得分: 1
为此，请按照以下步骤操作：
1- 选择要更改格式的单元格。
2- 按下Ctrl+1以打开格式弹出窗口（数字选项卡）。
3- 选择'自定义'。
4- 输入以下模式以将数字转换为千位数（K）：
#,##0.0, "K"
5- 以及以下模式以将数字转换为百万位数（M）：
#,##0.0,,"M"
英文:
to do so, follow these steps:
1- choose cells you want to change format.
2- Press Ctrl+1 to open formatting popup (Number tab)
3- select 'Custom'
4- Enter this pattern for converting into thousands with K:
#,##0.0, "K"
5- And this pattern for converting into millions with M:
#,##0.0,,"M"
答案2
得分: 0
Number Format (Excel Advanced settings: Use System Separators)
-
If Region is set to French (France):
<1000]# ##0 \€;[<1000000]# ##0 k\€;# ##0 \m\€
-
If Region is set to English (United States)
[<1000]#,##0 €;[<1000000]#,##0, k€;#,##0,, \m€
And if you change the region, and close and re-open the workbook, the settings will switch.
Opened with Region = US english settings
enter image description here
Closed then Re-opened with Region = French France
enter image description here
You may find it simpler to set the number format using the US region settings to avoid confusion between <space> as a space vs <space> as a thousands separator.
英文:
Number Format (Excel Advanced settings: Use System Separators
-
If Region is set to French (France):
<1000]# ##0 \€;[<1000000]# ##0 k\€;# ##0 \m\€ -
If Region is set to English (United States)
[<1000]#,##0 €;[<1000000]#,##0, k€;#,##0,, \m€
And if you change the region, and close and re-open the workbook, the settings will switch.
Opened with Region = US english settings
Closed then Re-opened with Region = French France
You may find it simpler to set the number format using the US region settings to avoid confusion between <space> as a space vs <space> as a thousands separator
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论