|
在我们的博客和课程中，经常使用到自定义格式技巧，很多朋友觉得很神奇，问为什么只输了几个 0 和 ; 符号，就会完全改变了显示？ 自定义格式是控制单元格的值的显示效果的参数，在单元格格式对话框里输入。包括4个小节： 正数的格式 ; 负数的格式 ; 零值的格式 ; 文本的格式 在网络上你可以搜到非常完整的自定义格式参数说明，可是你可能还是不知道什么时候用哪种、怎么用，因为它只罗列了知识而没有应用场景。本帖介绍在表格和图表制作中经常用到的自定义格式技巧，都有实际的运用场景，看了就可以用上。 1、【;;;】，隐藏单元格的显示值。 先看一个最简单的自定义格式，简单到只有3个分号，;;; 。 有时候出于保护目的，你不希望别人看见某些单元格，很多人会把字体设置为白色看不见，其实这时候使用自定义格式代码 ;;; 会更好，意思就是告诉Excel，不论是正数、负数、0还是文本，都不显示内容。 操作方法：选中需要隐藏的单元格，ctrl 1 设置单元格格式，数字，自定义，在 类型 下的输入框里输入3个西文的分号 ;;; ，确定，单元格里的显示就会消失了。 有时候，对表格使用色阶时，可能仅需要填充色，不需要数字，这时也可以使用 ;;; 来隐藏数字。比如本博的十字绣地图做法，用大量的小单元格的条件格式色阶绘制一个中国热力数据地图，所有单元格里的数字就是用 ;;; 隐藏了。
2、【0;0;0】，负数时不显示负号。 蝴蝶条形图是一种分析配对指标的图表工具，一般做法里左侧的条形图其实是使用负数做的，但为什么轴标签和数据标签看起来都还是正数呢？其实是使用了自定义格式 0;0;0 ，使负数也显示为正数。 在《向经济学人学图表》课程的第9.10节里，介绍了3种方法来制作蝴蝶图，完美重现专业图表，详情见：http://study.163.com/course/courseMain.htm?courseId=1337018。 （PS，重要通知：该网站网易云课堂在11月2日~11日有大促活动，可优惠购课，机会难得，值得抓住，详情见文尾） 3、【 0.00;-0.00;0.00】，【 0.0%;-0.0%;0.0%】，正数也显示正号。 与上面相反，负数时显示负号，正数时也显示正号，对增长百分比，以及会计中反映帐户增减流水会用到。 4、【0'元'】，带上单位。 也许是财务会计们会经常用到的小功能。 5、【0.0'%'】和【0.0_%】，增幅百分比列的简写方式 对于报表中的百分比列，满眼的%显然比较累赘和干扰，华尔街日报的图表非常追求简洁，一般只在第1行显示%，其余的行则省略掉%，但%号前面的数字位仍然会整齐对齐。
这个技巧也可以运用到图表的y轴，实现只在最大刻度标签显示%符号：[=100]0'%';0_% （不过右对齐使留空无效），只在最大刻度标签显示美元符号： [=100]$0;0 。
对y轴刻度标签使用此格式，可以对不及格或者不达标的刻度用红色显示。 7、【[<1.5]0.0%;0】，兼容整数指标和百分比指标的格式化 有时候我们的KPI报表或透视表是综合指标排列的，一列内既有整数格式、又有百分比格式。如果一行一行地去设置格式，比较麻烦。如果数据上能确认百分比指标都不大于1或1.5，整数指标都大于1或者1.5，那么使用这个格式代码就可以一次完成设定。 8、【[绿色]▲* 0%;[红色]▼* -0%;-】，自动分色和上下箭头的增幅百分比格式。★★★★★ 这是我最喜欢的Excel小技巧了，没有之一，强烈推荐。 虽然只有短短几个字符，可技巧性实在太强了：[绿色]、[红色]指定正负数的显示颜色，▲▼指定正负数的前缀，* （星号 空格）则智能填充空格，使单元格内三角符号左对齐、百分比右对齐。非常完美，优雅之极，显示效果比Excel内置的图标集要好，且可支持2003低版本。 由于使用[红色][绿色]指定的默认红绿色搭配并不理想，而使用正数深绿或深蓝、负数深红会显得更加专业： 【[蓝色]▲* 0%;[红色]▼* -0%;-】（指定正数蓝色、负数红色） 【▲* 0%;[红色]▼* -0%;-】（只指定负数红色，正数颜色通过单元格颜色另行设置） 【▲* 0%;▼* -0%;-】（不指定颜色，颜色通过条件格式另行设置） 然后再使用单元格格式或条件格式去实现更好的颜色。 简单起见，最终推荐使用这个格式代码【[颜色10]▲* 0%;[颜色3]▼* -0%;-】，可以显示出正数深绿、负数红色的效果，已经非常专业。 如果你使用英文版，上面的红蓝绿色应分别换成 red、blue、green，颜色二字换成 color 。 另注意，以上格式只适用于增幅百分比数字的格式化，不能应用于构成占比的百分比数字。 在向经济学人学图表第9.9节，我们详细演示了使用这种自定义格式替代内置图标集的方法，详情见：http://study.163.com/course/courseMain.htm?courseId=1337018 而且，我们还可以把这个代码写成宏，做成工具栏的按钮，当你选中增幅百分比列数据，点击按钮，一键格式化出这种效果的时候，效率高到爆。
9、【yyyy】或【'yy】，日期格式的年份简写 在制作日期或月份的时间趋势图表里，你的x轴标签是否又长有倾斜，毫无专业感？怎么破？对日期格式的日期，设置自定义格式可显示为各种想要的格式，比如 yyyy 显示4位年份，yy 显示2位的年份，'yy 显示带撇号的2位年份，简洁就是这么简单。类似的还有mm、mmm、d等。
10、【0!.0,】或 【0!.0,'万元'】，以万为单位显示数值。 这是网上流传较多的一种自定义格式应用，不过我觉得实用性不大，因为我们工作中对一个字段要么使用整数如销售量，要么使用2位小数如销售额，很少会有使用1位的小数。只有在增长百分比和占比百分比时可能使用1位小数，而这个时候也不需要这种自定义格式了。 那怎么按万元显示呢，建议使用选择性粘贴除10000就可以了。 11、【000 0000 0000】，手机号码分段显示 最后，送一条人人可以用到，特别适合办公室文员mm的小技巧，对通讯录里的手机号码字段设置这个自定义格式代码，就可以自动分段显示，189 1234 5678，阅读起来是不是方便多了。 以上自定义格式代码均是在单元格或图表的轴标签、数据标签里直接设置，如果是在单元格中制作表格或准备图表数据，必要时可使用text()函数 自定义格式代码来产生需要的格式。（本文原创：微信公众号“Excel图表之道”，id：iamExcelPro） 最后捎带个广告，请直接看图片：
（完） |
|
|
6、【[<=60][红色]0;0】，y轴刻度标签分段显示不同颜色
