Excel自定义格式进阶教程.doc

Excel自定义格式进阶教程说到EXCEL中使用自定义格式,首先想到的是快捷键:先打开单元格式对话框“Ctrl+1”(大键盘的数字键1),再使用“Tab,”来选择 要显示负数和零,正数则不显示,自定义为:”；#；0” 要隐藏所有的输入值,自定义为:格式”；” 自定义格式的通用模型相当于这样的条件格式:0正数格式;=60G/通用格式;红色G/通用格式 即大于等于60为绿小于为红，可以为条件格式的一个补充（条件格式只能有三种） 5、将00000000格式的数据转化为日期格式。 0000.00.000000-00-00 6、小写中文时1119会变成一十一一十九，我要的是十一十九，怎么办？ 20DBNum1;DBNum1d 7、怎么让新工作表中有自定义的格式做成模板！ 8、VBA中的可以用Format格式化输出，同样也可以利用WorksheetFunction.TEXT函数输出一些自定义的格式。数字格式是单元格格式中最有用的功能之一，专门用于对单元格数值进行格式化。单击菜单“格式”“单元格”，或者按组合键，用户可以在“单元格格式”对话框的“数字”选项卡中看到有关数字格式的各项设置，如图68-1所示。通过选择不同的格式设置，甚至应用自定义的格式，能够让单元格的显示更加符合它的表达目标。图681 “单元格格式”对话框中的“数字”选项卡注意无论为单元格应用了何种数字格式，都只会改变单元格的显示形式，而不会改变单元存储的真正内容。反之，用户在工作表上看到的单元格内容，并不一定是其真正的内容，而可能是原始内容经过各种变化后的一种表现形式。如果用户需要在改变格式的同时也改变实际内容，需要借助TEXT或其他函数来实现。 681 Excel内置的数字格式Excel内置的数字格式多种多样，能够满足用户在一般情况下的需要。下面用几个例子来说明同样的数字被设置为不同的数字格式后，显示效果会有哪些改变。假设一个单元格中有数字1023.4，在默认情况下，Excel不对单元格设置任何数字格式，此时的格式名称为“常规”，数值按照它的真实面貌显示出来。用户在“单元格格式”对话框的“数字”选项卡中可以看到一个“示例”项目，它可以在用户单击“确定”按钮以前就显示当前单元格的数值在应用了指定的数字格式以后的样式，如图682所示。图682 “常规”数字格式就是没有任何格式在“分类”列表框中选择“数值”项，然后勾选“使用千位分隔符”复选框，示例内容会发生改变，如图683所示。图683 “数值”数字格式在“分类”列表框中选择“货币”项，然后在“货币符号（国家/地区）”列表框中选择“￥中文（中国）”，则示例内容将再次发生改变，如图684所示。图684 “货币”数字格式以下是在“分类”列表框中选择“百分比”项后的示例内容，如图685所示。图685 “百分比”数字格式在“分类”列表框选择“分数”项，示例内容中的数值以分数形式显示，如图68-6所示。图686 “分数”数字格式“分类”列表框中还有许多其他项目，每个项目中又有多个参数可选，读者可以自己进行尝试，以找到符合需要的最佳格式。682 创建自定义数字格式如果Excel内置的数字格式无法满足用户在实际工作中的需求，还可以创建自定义数字格式。在“单元格格式”对话框中，选择“分类”为“自定义”，如图687所示。图687 自定义数字格式在“类型”文本框中输入自定义的数字格式代码，或者修改原有格式代码。单击“确定”即完成。在“类型”下方的列表框中，已经有许多的格式代码，这些代码就是Excel内置的数字格式所对应的格式代码，或是由用户成功创建的自定义数字格式的格式代码。如果用户先在“分类”列表框中选定一个内置的数字格式，然后再选定“自定义”项，就能够在“类型”文本框中看到与之对应的格式代码。在原有格式代码的基础上进行修改，能够更快速地得到自己的自定义格式代码。683 自定义数字格式的代码组成规则许多Excel用户可能不了解自定义数字格式能够让他们几乎随心所欲地显示单元格数值，或者因为害怕面对长长的格式代码而放弃这个有用的工具。而实际上，自定义数字格式代码并没有想像中那么复杂和困难，只要掌握了它的规则，就很容易读懂和书写格式代码来创建自定义数字格式。自定义格式代码可以为4种类型的数值指定不同的格式：正数、负数、零值和文本。在代码中，用分号来分隔不同的区段，每个区段的代码作用于不同类型的数值。完整格式代码的组成结构为：“大于条件值”格式;“小于条件值”格式;“等于条件值”格式;文本格式在没有特别指定条件值的时候，默认的条件值为0，因此，格式代码的组成结构也可视作：正数格式;负数格式;零值格式;文本格式用户并不需要每次都严格按照4个区段来编写格式代码，只写1个或2个区段也是可以的。表681中列出了没有按4区段写代码时，代码结构的变化。表681 自定义数据格式代码结构规则区 段 数代 码 结 构1格式代码作用于所有类型的数值2第1区段作用于正数和零值，第二区段作用于负数3第1区段作用于正数，第二区段作用于负数，第三区段作用于零值下面有一个自定义数字格式代码的例子，它针对4种不同类型的数值定义了不同的格式：#,#0.00_;红色 #,#0.00;绿色G/通用格式;“”图688是Excel中应用了这种数字格式的单元格的显示。图688 自定义数字格式的单元格的显示684 常用自定义数字格式的代码与示例表682 常用自定义数字格式的代码与示例代 码注释与示例G/通用格式不设置任何格式，按原始输入的数值显示#数字占位符，只显示有效数字，不显示无意义的零值0数字占位符，当数字比代码的数量少时，显示无意义的0从上图可见，可以利用代码0来让数值显示前导零，并让数值固定按指定位数显示。下图是使用#与0组合为最常用的带小数的数字格式?数字占位符，需要的时候在小数点两侧增加空格；也可以用于具有不同位数的分数.小数点%百分数,千位分隔符E科学计数符号显示格式里的下一个字符续表代 码注释与示例*重复下一个字符来填充列宽_留出与下一个字符等宽的空格利用这种格式可以很容易地将正负数对齐“文本”显示双引号里面的文本文本占位符，如果只使用单个，作用是引用原始文本如果使用多个，则可以重复文本颜色颜色代码颜色可以是black黑色、white白色、red红色、cyan青色 、blue蓝色、yellow黄色、magenta紫红色或green绿色要注意的是，在英文版用英文代码，在中文版则必须用中文代码颜色 n显示Excel调色板上的颜色，n是056之间的一个数值条件值设置格式的条件有关日期与时间方面的自定义格式方法，请参阅技巧69。Excel自定义格式参数2006-11-12 15:50代码结构组成代码码分为四个部分，中间用“；”号分隔，具体如下： 正数格式；负数格式；零格式；文本格式各个参数的涵义“G/通用格式”：以常规的数字显示，相当于“分类”列表中的“常规”选项。 例：代码：“G/通用格式”。10显示为10；10.1显示为10.1。 “0”：数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，则用0补足。 例：代码：“00000”。1234567显示为1234567；123显示为00123 代码：“00.000”。100.14显示为100.140；1.1显示为01.100 “#”：数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于“#”的数量，则按“#”的位数四舍五入。 例：代码：“#.#”,12.1显示为12.10;12.1263显示为：12.13 “？”：数字占位符。在小数点两边为无意义的零添加空格，以便当按固定宽度时，小数点可对齐，另外还用于对不等到长数字的分数 例：分别设置单元格格式为“?.?“和“?.?”,对齐结果如下： 例：设置单元格自定义格式“# ?/?”。“1.25”显示为“1 1/4” “.”：小数点。如果外加双引号则为字符。 例：代码“0.#”。“11.23”显示为“11.2” “%”：百分比。 例：代码“#%”。“0.1”显示为“10%” “，”：千位分隔符。数字使用千位分隔符。如时在代码中“，”后空，则把原来的数字缩小1000倍。 例：代码：“#，#”。“10000”显示为“10，000” 代码：“#，”。10000显示为10。 代码：“#，”。“1000000”显示为“1”。 “/”：显示下一个字符。和“”用途相同都是显是输入的文本，且输入后会自动转变为双引号表达。不同的“/”是显后面的文本，双引号是显示双引中间的文本。 例：代码“ABC”或“”ABC”。均显示为“ABC” “*”：重复下一次字符，直到充满列宽。 例：代码：“*-”。“ABC”显示为“ABC-” “_”（下划线）：留下一个和下一个字符同等宽度的空格例：代码：$#,#0.00_);($#,#0.00)“文本”：显示双引号中的文本。 例：代码：“”中国”显示为“中国河南” 颜色：用指定的颜色显示字符。可有八种颜色可选：红色、黑色、黄色，绿色、白色、兰色、青色和洋红。 例：代码：“青色;红色;黄色;兰色”。显示结果为正数为青色，负数显示红色，零显示黄色，文本则显示为兰色 颜色N：是调用调色板中颜色，N是056之间的整数。 例：代码：“颜色3”。单元格显示的颜色为调色板上第3种颜色。 条件：可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他”。条件要放到方括号中。必须进行简单的比较。 例：代码：“0”正数”；=0;”零”；负数”。显示结果是单元格数值大于零显示正数，等于0显示零，小于零显示“负数”。“！”：显示“”。由于引号是代码常用的符号。在单元格中是无法用来显示出来“”。要想显示出来，须在前加入“！”例：代码：“#!”。“10”显示“10”代码：“#!”。“10”显示“10” 2.1.1.2 时间和日期代码常用日期和时间代码 “YYYY”或“YY”：按四位（19009999）或两位（0099）显示年 “MM”或“M”：以两位（0112）或一位（112）表示月。 “DD”或“D”：以两位（0131）或一位（1-31）来表示天。 例：代码：“YYYY-MM-DD”。2005年1月10日显示为：“2005-01-10” 代码：“YY-M-D”。2005年10月10日显示为：“05-1-10” “AAAA”：日期显示为星期。 “H”或“HH”：以一位（023）或两位（0123）显示小时 “M”或“MM”：以一位（059）或两位（0159）显示分钟 “S”或“SS”：以一位（059）或两位（0159）显示秒 例：代码：“HH:MM:SS”。“23:1:15”显示为“23:01:15” H或M或SS：显示大于24小时的小时或显示大于60的分或秒。 下面介绍几个常遇到的实例 把12345显示为1.2 代码：“0.,” 设置千元显示且四舍五入保留两位小数要求：把“12345”显示为：12.35 代码：“#.00,” 在数字中任意插入字符要求：把“20050512”设置为：2005-05-12 代码：“0000-00-00” 在文本或数字前添加字符要求：“郑州市”显示为“河南省郑州市” 代码：“河南省” 要求：“1035010121”显示为“手机号码1034010121” 代码：“手机号码”或“手机号码#” 另外日期格式的显示方法e:显示四位年份，2003版本有效。bbbb:显示四位佛历年份，即以公元前543年为纪年元年，对1900年以后的日期有效。mmm:显示英文月份的简称。mmmm:显示英文月份的全称。ddd:显示英文星期几的简称。dddd:显示英文星期几的全称。关于特殊数字的显示中文小写数字 DBNum1$-804G/通用格式例：代码：“DBNum1$-804G/通用格式”。“1”显示为“一”代码：“DBNum1$-804G/通用格式”。“13”显示为“一十三”中文小写数字 DBNum2$-804G/通用格式例：代码：“DBNum2$-804G/通用格式”。“1”显示为“壹”代码：“DBNum2$-804G/通用格式”。“13”显示为“壹拾叁”中文小写数字 DBNum3$-804G/通用格式例：代码：“DBN