数百个Excel图上的小数位 [英] Decimal places on hundreds of Excel graphs

问题描述

如果我正在处理数百个图形，这些图形现在会自动设置y轴，那么如何消除10.0％，20.0％，并使其简单地变为10％，20％(节省2个字符)?我正在使用Excel 2010，并且不想弄乱自动格式设置，因为有时候我需要小数位百分比，例如在失业率中(4.5％是有意义的).当然，有时我会在y轴上以百万或数十亿美元的形式绘制图形(但这是一个单独的问题).

If I'm dealing with hundreds of graphs, which are right now automatically setting the y axis, how can I eliminate 10.0%, 20.0%, and make it simply 10%, 20% (a savings of 2 characters)? I'm using Excel 2010, and don't want to mess up automatic formatting since there may be times that I need the percent decimal places, as in unemployment (4.5% is meaningful). Of course, sometimes I graph stuff on the y axis in millions or billions of dollars (but that's a separate question).

更新:我发现电子表格中的安全规则禁止宏.是否可以通过设置值而无需宏来完成?也许这是供应商(微软)将来会做的事情....

Update: I found out that security rules prohibit macros in spreadsheets. Can it be done without macros, by setting a value? Perhaps that is something that the vendor (Microsoft) would do in future years....

推荐答案

右键单击要更改的Y轴.转到设置轴格式"，然后单击数字".您可以在此处选择所需的格式类型(会计，数字，百分比等).只需选择百分比"，然后在小数位数"中输入"0"即可.

Right click the Y Axis you want to change. Go to "Format Axis", and click "Number". Here is where you can choose what type (accounting, number, percentage, etc) of formatting you want. Just choose "Percentage" and put "0" for the "Decimal Places".

宏，它将对工作表中的所有图表执行此操作:

Macro which will do so for all charts in a worksheet:

Sub Macro2()
Dim obj     As Object
For Each obj In ActiveSheet.ChartObjects
    With obj.Chart.Axes(xlValue)
       .TickLabels.NumberFormat = "0%"
    End With
Next obj

End Sub

这篇关于数百个Excel图上的小数位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！