Excel自定义格式详解.pdf

Excel 自定义格式详解 ” G/ 通用格式 ” ：以常规的数字显示，相当于” 分类” 列表中的 ” 常规” 选项。例：代码：”G/ 通用格式 ” 。10 显示为 10 ；10.1 显示为 10.1 。 2、 “ #”：数字占位符。只显有意义的零而不显示无意义的零。小数 点后数字如大于 ”#” 的数量，则按 ”#” 的位数四舍五入。例：代 码：”#.#”,12.1显示为 12.10;12.1263显示为： 12.13 3、” 0”：数字占位符。如果单元格的内容大于占位符，则显示实际 数字，如果小于点位符的数量，则用0 补足。例：代码： ”00000” 。 1234567显示为 1234567； 123 显示为 00123代码：” 00.000 ”。 100.14显示为 100.140；1.1 显示为 01.100 4、” ”：文本占位符，如果只使用单个，作用是引用原始文本， 要在输入数字数据之后自动添加文本，使用自定义格式为：” 文本内 容”；要在输入数字数据之前自动添加文本，使用自定义格式为： ”文本内容 ” 。 符号的位置决定了Excel 输入的数字数据相对于添 加文本的位置。如果使用多个，则可以重复文本。例：代码 ” ； “集团“ 部“ “ ，财务显示为：集团财务部代码” ； “ ，财 务显示为：财务财务财务 5、” * ”：重复下一次字符， 直到充满列宽。 例：代码：” * -” 。” ABC” 显示为 ”ABC -” 可就用于仿真密码保护：代 码”*;*;*;*”，123 显示为： * 6、” ，” ：千位分隔符 例：代码 ” #,# “，12000显示为： 12,000 7、: 用这种格式显示下一个字符。“文本“，显示双引号里面的文本。 “” ：显示下一个字符。和 “”用途相同都是显示输入的文本，且输 入后会自动转变为双引号表达。例：代码“人民币 “#,#0,“百万“， 与 人民币百万，输入 1234567890显示为 :人民币 1,235百万 8、“ ？” ：数字占位符。在小数点两边为无意义的零添加空格，以便 当按固定宽度时， 小数点可对齐， 另外还用于对不等到长数字的分数 例：分别设置单元格格式为”?.?”和”?.?”,对齐结果如下：输 入 12.1212 显示 12.12 12.121 9、颜色：用指定的颜色显示字符。可有八种颜色可选：红色、黑色、 黄色，绿色、白色、兰色、青色和洋红。例：代码：“ 青色; 红色; 黄 色; 兰色 ” 。显示结果为正数为青色，负数显示红色，零显示黄色， 文本则显示为兰色 颜色 N ：是调用调色板中颜色，N 是 056之 间的整数。例：代码： “ 颜色 3 ” 。单元格显示的颜色为调色板上第 3 种颜色。 10 、条件：可以单元格内容判断后再设置格式。条件格式化只限于 使用三个条件，其中两个条件是明确的，另个是“ 所有的其他 ” 。条件 要放到方括号中。必须进行简单的比较。例：代码：“0 ” 正数” ； =0;” 零” ；负数” 。显示结果是单元格数值大于零显示正数，等于 0 显示零，小于零显示 “ 负数” 。 11 、 “ ！” ：显示“ “ ”。由于引号是代码常用的符号。在单元格中是无 法用“ 来显示出来 “”。要想显示出来，须在前加入“ ！” 例：代码： “ #!“ ”。“ 10”显示“ 10“ ”代码： “ #!“!“”。“ 10”显示“ 10“ ” 12 、时间和日期代码常用日期和时间代码 “ YYYY”或“ YY”：按四位（ 19009999）或两位（ 0099）显示年 “ MM ” 或“ M ”：以两位（ 0112）或一位（ 112 ）表示月。 “ DD ” 或“D”：以两位（ 0131）或一位（ 1-31 ）来表示天。例：代码： “ YYYY -MM- DD ”。 2005 年 1 月 10 日显示为：“ 2005 -01- 10” excel 单元格自定义格式1 代码结构组成代码码分为四个部分，中间用 “ ；” 号分隔，具体如下：正数格式；负数格式；零格式；文本格式 各个参数的涵义 “ 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 -” “ _”（下划线）：留下一个和下一个字符同等宽度的空格“ 文本” ：显 示双引号中的文本。例：代码：“”中国” ” 显示为 “ 中国河南 ” 颜色 ：用指定的颜色显示字符。可有八种颜色可选：红色、黑色、 黄色，绿色、白色、兰色、青色和洋红。例：代码：“ 青色; 红色; 黄 色; 兰色 ” 。显示结果为正数为青色，负数显示红色，零显示黄色， 文本则显示为兰色 颜色 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/通用格式 例：代码： “DBNum3$-804G/通用格式 ” 。“123” 显示为 “1百 2 十 3” 特殊说明 因为参数的特殊性，所以自定义的参数也是有关键字的。如函数 =TEXT(A1,“b0000“)就会显示错误。因为 “ b”就是保留的关键字， 在自定义格式输入 “b”系统就会自动填入 “bb” 。bb 就是佛历年份， 即以公元前 543 年为纪年元年，对 1900 年以后的日期有效。“bbbb” 就是四位佛历年份。 要解决=TEXT(A1,“b0000“)的错误问题， 需要 这样定义函数 =TEXT(A1,“b“0000“)。在自定义格式中定义就是 “ “b“0000”。其它的关键字自己体会如：“ d”、“ e” . . 单元格自定义格式2 在 Excel 中自定义数字格式 虽然 Excel 为用户提供了大量的数字格式， 但还是有许多用户因为工 作、学习方面的特殊要求，需要使用一些Excel 未提供的数字格式， 这时我们就需要利用Excel 的自定义数字格式功能来帮助实现用户 的这些特殊要求。 一、在 Excel 中创建自定义数字格式的方法 1、选择要设置格式的单元格或单元格区域。 2、单击 “ 格式” 菜单中的 “ 单元格 ” 命令，然后单击 “ 数字” 选项卡。 3、在“ 分类” 列表中，单击 “ 自定义” 选项。 4、在“ 类型” 框中，编辑数字格式代码以创建所需的格式。 在 Excel 自定义数字格式使用如下的表达方式： 正数的格式负数的格式零的格式文本的格式 #,#0.00 Red-#,#0.00 0.00 “TEX T“ 在 Excel 自定义数字格式的格式代码中，用户最多可以指定四个节； 每个节之间用分号进行分隔， 这四个节顺序定义了格式中的正数、负 数、零和文本。 如果用户在表达方式中只指定两个节，则第一部分用 于表示正数和零， 第二部分用于表示负数。 如果用户在表达方式中只 指定了一个节， 那么所有数字都会使用该格式。如果在表达方式中要 跳过某一节，则对该节仅使用分号即可。 二、自定义数字格式实例 (一)自动添加文本 （1）要在输入数据之后自动添加文本，使用自定义格式为：文 本内容；要在输入数据之前自动添加文本，使用自定义格式为： 文本内容 。 符号的位置决定了Excel 输入的数字数据相对于添 加文本的位置，双引号得用英文状态下的。 实例一： 在学生姓名前添加 “ 初二（ 2）班” 字样，就可以创建： “初二（ 2）班“ （2）在输入数字之后自动添加文本。 实例二： 如在日常财务工作中， 常常需要在金额数字后加单位“ 元” ，这时就可 以使用： 0.00“ 元“ 或 0“ 元“ (二)在自定义数字格式中使用颜色 要设置格式中某一部分的颜色， 只要在该部分对应位置用方括号键入 颜色名称或颜色编号即可。Excel 中可以使用的颜色名称有黑色、 蓝色 、青色 、绿色 、 洋红 、红色、 白色、 黄色八种不 同的颜色， 此外 Excel 还可以使用 颜色 X 的方式来设置颜色， 其中 X 为 1-56之间的数字，代表了56 种不同的颜色。 例如：当用户需要将单元格中的负数数字用蓝色来表示，只要使用 “ #,#0.00;蓝色-# ,#0.00” 自定义数字格式， 用户在单元格中 录入负数时， Excel 就会将数字以蓝色显示。 (三)在自定义数字格式中使用条件格式 在 Excel 自定义数字格式中用户可以进行条件格式的设置。当单元格 中数字满足指定的条件时，Excel 可以自动将条件格式应用于单元 格。Excel 自定义数字格式中可以使用如下六种标准的比较运算符： 运算符含义 = 等于 大于 = 大于等于 不等于 例如：在学生成绩工作表中，当我们想以红色字体显示大于等于90 分的成绩，以蓝色字体显示小于60 分的成绩时，其余的成绩则以黑 色字体显示，这时只需将自定义数字格式设置为“ 红色=90;蓝 色=90;蓝色=90;蓝色=90G/通用格式 ; 蓝色100“高“;“ 中等“ 实例二： 把“ 数学” 、“ 语文” 成绩中 90 分以上替换成 “ 优” ： =90“优“ (四)隐藏单元格中的数值 在 Excel 工作表中，有时为了表格的美观或者别的因素，我们希望将 单元格中的数值隐藏起来，这时我们使用“; ”(三个分号 )的自定义 数字格式就可达到此目的。 这样单元格中的值只会在编辑栏出现，并 且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值 可以被其他单元格正常引用。 在 Excel 中要想设置满足指定条件数字 的格式，在自定义数字格式代码中必须加入带中括号的条件，条件由 比较运算符和数值两部分组成。