[北-基础技巧]单元格格式

bbwsj

    一个漂亮的E表，少不了对表格进行美化，而对表格美化就不能不利用单元格格式，另利用单元格格式还能达到其他的效果。

    现在，大家和北一起学习怎么用单元格格式吧

         03版的单元格格式调用，部分功能可以利用格式工具栏上的小图标快速达到完成效果，但要调用全部功能就需要调出单元格格式窗口（本文主要讨论利用单元格格式窗口的操作），调用单元格格式窗口有3种方式：

选中单元格后：

方法1、菜单——格式——单元格

方法2、CTRL+1(大键盘的数字1)

方法3、鼠标右键——设置单元格格式

03版的单元格格式窗口分为“数字”、“对齐”、“字体”、“边框”、“图案”、“保护”6个子窗口，

“数字”子窗口可以设置单元格数据的显示值，制造北常说的眼见非实的“假象”。

“对齐”子窗口可以设置单元格数据在单元格里的对齐显示外观。

“字体”子窗口可以设置单元格数据的字体、大小、字体颜色、下划线、上下标等和字体有关的设置。

“边框”子窗口可以设置单元格的外边框和内部框线的格式

“图案”子窗口可以设置单元格的填充颜色和纹理

“保护”子窗口是当工作表被保护时，是否需要在编辑栏显示单元格的实际值和需要进行被锁定保护设置

这里给大家两个北觉得比较实用的帖子，帮助大家更好的使用单元格格式

1、新手学excel：单元格格式的设置（视频下载）

http://www.excelpx.com/forum.php?mod=viewthread&tid=137893&highlight=%B5%A5%D4%AA%B8%F1%B8%F1%CA%BD%2B

2、巧用快捷键轻松设置Excel单元格格式

http://www.excelpx.com/forum.php?mod=viewthread&tid=74992&highlight=%B5%A5%D4%AA%B8%F1%B8%F1%CA%BD

     单元格格式的内容很多，一一单独叙述太过啰嗦，也不利于新手们化入实际中灵活运用，现用各种实际常用的实例来说明它的各种用法。

---

[hide
]

[hide=2100]

[/hide
]

[/hide
]

bbwsj

    在说实例前，首先需要先扫盲，一起学习一下单元格格式的基础——自定义单元格式的基本组成规则和所用特殊符号的意义。
一、自定义单元格格式的基本组成规则
1.完整的格式代码由四个区段组成，按顺序为：正数；负数；零；文本，格式代码各区段以分号分隔。如：　0.00;-0.00;0;@　

2.上述前三项是以“0”为分界点，但也有的单元格式对其进行了变通，以一个特定条件数值为分界点，按：大于条件值；小于条件值；条件值；文本。分为四个区段。
自定义单元格格式里可以用“比较运算符+数值“来表示条件值，如：　[>1]0;[<1]0.00;# ;@

3.因为单元格格式的应用中，自定义格式只允许在前两个区段里加比较运算符，第三个区段自动以“除自之外作为其条件值，就出现了第3种组成规则：条件值1；条件值2；同时不满足条件值1、2的数值；文本 。　　如：　[＝10000]0;[<0]-0.00;0.00 ;@

4.在实际中，我们不必每次都按四个区段编写格式代码，只写几个或1个区域是被允许的，但包含条件值的格式代码至少要写2个区段以保证覆盖不符合条件的其他情况，不写电脑会自动添加默认的通用格式。如果希望对应的区段不显示，必须用按序用“;”标出对应的空格式代码，并不得遗漏前区段的格式代码。

二、自定义格式中各常用特殊符号的意义

      此外还有很多与日期、时间、货币、会计符号的代码，这里我就不细诉了，大家可以到单元格格式——“数字”里去一一慢慢体会。
     注：所有EXCEL单元格格式代码里有特殊意义的符号都要用西文，在中文下输入上述符号，电脑是不认识的。

bbwsj

设置凹凸立体感的单元格

原表如下：

第1步：选定整表，菜单——格式——单元格，调出“单元格格式”对话框——“图案”窗口。设置“灰色”背景色，确定。
第2步：选定B2:F20单元格区域，“图案”窗口。设置“浅青绿色”背景色，确定。
第3步：选中E4、E6、F8单元格，“单元格格式”对话框——“图案”窗口。设置“淡兰色”背景色；“边框”窗口，给下边框和右边框添黑色双线条，给上边框和左边框添白色双线条。“字体”窗口，设置字体为“华文新魏”，字号“16”,字形“加粗”；“对齐”窗口，设置为水平对齐为“分散对齐”，缩进“1”，垂直对齐“居中”，确定。
第4步：选中C3:C9单元格区域，“单元格格式”对话框——“图案”窗口。设置图案“50％灰色”；“边框”窗口，给上边框和左边框添黑色粗实线，给下边框和右边框添白色粗实线。“字体”窗口，设置字体为“华文彩云”，字号“22”,字形“加粗”，颜色“淡兰色”；“对齐”窗口，设置为水平对齐为“居中”，垂直对齐“居中”，文本控制设置“合并单元格”，方向设置为“竖立”，确定。
第5步：拖动行高、列宽到合适的位置，如图，

一个漂亮的有凹凸立体感的表做好了。
如果再让单元格建立起超链接，隐去工作表标签，就能成为一个类似程序一样，点击就能跳到相应表了。这个工作表封面应该也能唬唬单位里的EXCEL菜鸟们吧！

bbwsj

制作斜线表头
      EXCEL表，经常会用到斜线表头，北觉得用单斜线表头用EXCEL做比较方便，但双斜线或多斜线表头，用EXCEL做就不方便了，北建议利用WORD的“绘制斜线表头”功能，用WORD做好了，再粘入EXCEL，具体方法见以下链接。方法见论坛帖子http://www.excelpx.com/forum.php?mod=viewthread&tid=142250&highlight=%B1%ED%CD%B7的2楼

这里说说最常见的制作单斜线表头：
如果表头中只需要一条斜线，可以利用Excel的边框设置来画斜线，然后填入文字内容。在单元格内画斜线的方法有如下几种。
方法1、用1个单元格，制作单斜线表头
第一步：选中单元格，调出“单元格格式”对话框—“边框”窗口，如图，设置斜线表头

第二步：添列标和行标文字。添列标和行标文字有3个方法，
a、用空格+自动换行添行标、列标；
在单元格里输入行标和列标文字，调出“单元格格式”对话框—“对齐”窗口，在“文本控制”选项勾选“自动换行”，再在行标和列标前输入适当的空格，再调整字体大小，如图让其显示成你满意的单斜线表头样式

b、用上下标添行标、列标
第1步：在单元格里输入列标和行标文字（列在前，字符数不同添空格以保证行标、列标字符数一致），
第2步：在编辑栏选中列标。调出“单元格格式”对话框—“字体”窗口，如图在“特殊效果”选项勾选“下标”，同样方法，在编辑栏选中列标。调出“单元格格式”对话框—“字体”窗口，在“特殊效果”选项勾选“上标”。
第3步：设置单元格行列对齐“居中”，调整字体大小，一个斜线表头就做好了
c、文本框添加
就是制作两个写有行标、列标的文本框，把这两个文本框，放在行标、列标相应的位置就OK了
方法2、用4个单元格，用方法1同样的方法给左上、右下单元格加斜线，把其他的行标、列标部分相应合并，完成如图的单斜线表头表格

利用这个方法，还可以制作更复杂的表头，如以下论坛帖子里的5区域表头：
http://www.excelpx.com/forum.php?mod=viewthread&tid=154446&highlight=%B1%ED%CD%B7

bbwsj

上面所说的，都是单元格格式在表格美化方面的运用，下面我们来说说，利用单元格格式来达到其他的效果。有句老话叫耳听为虚、眼见为实，但EXCEL的单元格格式就是能制造出很多所见非真的“假象”

隐藏不想看到的数据
在EXCEL表里，常常会用到公式，用公式得到的值常常会有0值，一般有3种处理方法：
1、会用IF函数让运算结果为“0”值变成“空”值。但这样做有个缺点，公式运算得到的“空”值是文本，对这个结果进行再引用时，会给某些函数运算带来麻烦。
2、用菜单——工具——选项——视图窗口里，窗口选项的“零值”的勾选去除，让0值不显示。但这个选项是针对整个文件的，文件的其他工作表里如果有你想显示0值的部分，因为它也无法显示了
3、就是我要说的利用单元格格式。具体方法是：选中单元格区域，调出“单元格格式”——“数字”窗口，选择类型为“自定义”输入“G/通用格式;-G/通用格式;;@”
如图：

利用 单元格格式的自定义格式，不但能隐藏0值，还是隐藏很多你不想显示的值
如：
你要 只显示正数值，0值和负数值都不显示，就在自定义格式里输入“G/通用格式;;;@”
你要 只显示正数值，0值和负数值和文本都不显示，就在自定义格式里输入“G/通用格式;;;”
你要正值、0值和负数值和文本都不显示，就在自定义格式里输入“;;;”
注意：用单元格格式，是无法隐藏 错误值 “#N/A"、"#VALUE!"、"#REF!"、"#DIV/0!"、"#NUM!"、"#NAME? ”的。

bbwsj

    批量给实际值加补丁
    我们在输入时，常常会输入123456…..的顺序号，常常到打印时发现，因为位数的不同，序号打印出来很难看，如果当初输入时，就输入“001”、“002”……“099”、“100”……就好了。其实这个用单元格格式非常容易解决
    选中序号数据区域，调出“单元格格式”——“数字”窗口在自定义格式里输入“000”，所有的数都自动补足3位

    那如果，我是公司里给固定资产编号，原本是按顺序号编的，现在要给已经编好号的固定资产加 GDZC的帽子后面跟3位数字，那这个补丁该怎么加呢？
同样用上面的方法：选中要改变的固定资产编号区域，调出“单元格格式”——“数字”窗口在自定义格式里输入“"GDZC"000”
如图，补丁打好了

   
      大家注意到没有，我用蓝圈标出来的部分，虽然显示和打印的值是“GDZC099”，但在实际值还是原来的值“99”。可见单元格格式只能改变显示值，制造“所见非真的假象”，而非真正的改变实际值。因此，如果需要让单元格格式的显示值成为实际值，还需要用其他方法批量处理，方法有利用函数转换，利用VBA转换，利用剪切板转换、利用其他文本文件转换。北觉得利用利用剪切板或其他文本文件批量转换比较方便，论坛有很多这样的帖子。大家去论坛搜索学习吧，这里主要说单元格格式，不跑题了。

bbwsj

根据不同的输入显示不同的颜色
例1、在财务记账中，常常以红色字，表示负数，以“-”表示0值，单元格格式具有按不同值显示不同颜色的功能。EXCEL已经给我们一个“会计专用”的数字格式，但它不能改变颜色，在这基础上略改动一下。
选中需要设置的单元格区域，调出“单元格格式”——“数字”窗口在自定义格式里输入“_ ￥* #,##0.00_ ;[红色]_ ￥* -#,##0.00_ ;_ ￥* "-"??_ ”，如图（根据不同的输入显示不同的颜色-1）一个会随着输入值的不同而改变显示字体颜色的会计专用格式，完成了。


例2、我们在登记学生考试成绩时，希望90～100的用绿色显示，60～90用常规色显示，不满60的用红色显示。同上方法，在自定义格式里输入“[红色][<60]G/通用格式;[<90]G/通用格式;[绿色]G/通用格式”即可
甚至于，我们还可以如图不显示数字，用“优秀”、“及格”、“不及格”，来表示。

bbwsj

用单元格格式添边框
     在WORD的页面设置里，可以给文字添漂亮的艺术边框。如图

但EXCEL没有这个功能。如何让EXCEL表也能漂亮一点呢？让我们和北一起，尝试用单元格格式模拟个边框效果吧

方法一：
根据单元格日期格式的原理，日期不能为负，在日期格式下输入负数，会用“#”号填充整个单元格。
第一步，给表格上下左右都空一行一列，以用来放边框
第二步：选中边框区域，调出“单元格格式”——“数字”窗口，任意设置个日期格式，
第三步，在边框区域，输入“-1”，现在所有的边框区域单元格都被“#”填充了
第四步：除4个顶角外，合并横边和竖边的单元格，并把竖边的单元格设置——对齐——方向设置为“竖立”。
第五步：调整行高、列宽。改变一下字体颜色。一个边框完成。

大家会说，用“#”做边框，不好看，那和北尝试一下，整个好看些的边框吧
方法二：
我在前面《自定义格式中各常用特殊符号的意义》里说过“*”号可以把“*”号后面的符号，填充满整个单元格，现在我们就用这点来做另一个边框
第一步，在菜单——插入——符号里，选中一个你喜欢的符号做边，选中一个你喜欢的符号做顶角，记住这个符号对应的字体是什么（这里，北用“Wingdings 2”字体下的2个符号，对应从编辑栏复制出对应的实际值“ｅ”，和“&#147;）
第二步：同方法一的第一步
第三步：选中边区域，调出“单元格格式”——“数字”窗口，在自定义格式里输入“*e”；选中顶角区域，设置自定义格式为“*&#147;”
第四步，在边框区域，输入任意正数，并设置边框区域为“Wingdings 2”字体
第五、六步同方法一的第四、五步。如图一个漂亮的边框做好了。


大家还可以尝试用其他字母，线段，图片符号来给自己的E表加边框。

bbwsj

日期和时间的不同格式
    在EXCEL里，数值型日期实质是用1～2958101（包含头尾2个数）的正整数表示从1900-1-1日至9999-12-31之间的日期。时间则用1被24、24*60、24*60*60除后的正小数来表示一天里的1小时、1分钟、1秒种。
    在EXCEL里提供了很多日期、时间的现成样式代码，这样个我就不多说了大家爬一下单元格，一个个尝试一下就会了。北在盖此单元格格式大楼贴时，参考了不少资料，1楼附件里有一个关于“日期显示”的分表，是论坛X.F.Zhao的资料，汇总的比较详细，给大家参考。

这里主要说说，在论坛答疑群和论坛提问中常常问到的关于在计算加班工资的问题
在计算员工的累计加班时间时，因为时间显示不超过24小时，就会导致4个7小时25分的加班合，成为5小时40分。这个很让人哭笑不得，然而翻别日期、时间格式都没有显示所要的了29小时40分的显示格式，不少人就此去N麻烦的求助于函数公式
其实这个问题用单元格格式还是能解决的，选区域，调出“单元格格式”——“数字”窗口，在自定义格式里输入“[h]"时"mm"分"”如图就解决了这个麻烦事，

同理，我们也可以用[ｍ]、[ｓ]打破60分、60秒的限制，计算总分种和总秒数

bbwsj

大写数字小写数字
     财务人员常常需要开支票，而现在的开票日期都需要中文大写。论坛上有很多人自己用EXCEL做了支票套打文件，那他们是字母解决这个中文大写日期问题的呢？EXCEL现成的格式没有大写日期啊？
     这同样也是可以用单元格格式完成的，选区域，调出“单元格格式”——“数字”窗口，在自定义格式里输入“[DBNum2][$-804]yyyy"年"mm"月"dd"日"”，如图，OK了


      公司销售员常常会做报价单，为方便计算，现在一般的报价单都是以元为最小单位。报价单大多总计需要写上大小写金额，用手工一个个的输入中文大写金额数字，真是件痛苦的事情，一不小心还会写错。如果我们输入小写合计，能自动变成中文大写，那该多好。
这个交给单元格格式，让它帮你吧。
      选中大写金额区域，调出“单元格格式”——“数字”窗口，在自定义格式里输入“[DBNum2][$-804]G/通用格式"元""整";[DBNum2][$-804]"负"G/通用格式"元""整";[DBNum2][$-804]G/通用格式"元"”，就自动变中文繁体大写了