EXCEL中单元格格式的自定义详解

EXCEL 中单元格格式的自定义详解excel 单元格自定义格式 1 代码结构组成分为四个部分，中间用―;‖号分隔，具体如下： 正数格式；负数格式；零格式；文本格式 各个参数的涵义 1、 ―G/通用格式‖： 以常规的数字显示， 相当于―分类‖列表中的―常规‖选项。 例：代码：―G/通用格式‖。10 显示为 10；10.1 显示为 10.1。 2、―0‖：数字占位符。如果单元格的内容大于占位符，则显示实际数字， 如果小于点位符的数量，则用 0 补足。 例：代码：―00000‖。1234567 显示为 1234567；123 显示为 00123 代码：―00.000‖。100.14 显示为 100.140；1.1 显示为 01.100 3、―#‖：数字占位符。只显有意义的零而不显示无意义的零。小数点后数 字如大于―#‖的数量，则按―#‖的位数四舍五入。 例：代码：―###.##‖,12.1 显示为 12.10;12.1263 显示为：12.13 4、―？‖：数字占位符。在小数点两边为无意义的零添加空格，以便当按 固定宽度时，小数点可对齐，另外还用于对不等到长数字的分数 例：分别设置单元格格式为―??.??―和―???.???‖,对齐结果如下： 例：设置单元格自定义格式―# ??/???‖。―1.25‖显示为―1 1/4‖ 5、―.‖：小数点。如果外加双引号则为字符。 例：代码―0.#‖。―11.23‖显示为―11.2‖ 6、―%‖：百分比。 例：代码―#%‖。―0.1‖显示为―10%‖ 7、―，‖：千位分隔符。数字使用千位分隔符。如时在代码中 ―，‖后空， 则把原来的数字缩小 1000 倍。 例：代码：―#，###‖。―10000‖显示为―10，000‖ 代码：―#，‖。10000 显示为 10。 代码：―#， ，‖。―1000000‖显示为―1‖。 8、‖@‖：文本占位符。如果只使用单个@，作用是引用原始文本，要在输 入数字数据之前自动添加文本，使用自定义格式为：指定的颜色显示字符。可有八种颜色可选：红色、黑色、 黄色，绿色、白色、兰色、青色和洋红。 例：代码：―[青色];[红色];[黄色];[兰色]‖。显示结果为正数为青色，负数显示 红色，零显示黄色，文本则显示为兰色 13、[颜色 N]：是调用调色板中颜色，N 是 0~56 之间的整数。 例：代码：―[颜色 3]‖。单元格显示的颜色为调色板上第 3 种颜色。 14、[条件]：可以单元格内容判断后再设置格式。条件格式化只限于使用 三个条件，其中两个条件是明确的，另个是―所有的其他‖。条件要放到方括号 中。必须进行简单的比较。 例：代码：―[>0]‖正数‖；[=0];‖零‖；负数‖。显示结果是单元格数值大于零显 示正数，等于 0 显示零，小于零显示―负数‖。 15、―！‖：显示―码：―[DBNum1][$-804]G/通用格式‖。―13‖显示为―一十三‖ 中文小写数字 [DBNum2][$-804]G/通用格式 例：代码：―[DBNum2][$-804]G/通用格式‖。―1‖显示为―壹‖ 代码：―[DBNum2][$-804]G/通用格式‖。―13‖显示为―壹拾叁‖ 中文小写数字 [DBNum3][$-804]G/通用格式 例：代码：―[DBNum3][$-804]G/通用格式‖。―123‖显示为―1 百 2 十 3‖特殊说明： 因为参数的特殊性，所以自定义的参数也是有关键字的。如函数 =TEXT(A1,常需要在金额数字后加单位―元‖，这时就可以使用： 0.00、创建或删除自定义数字格式，2、 条件格式，因为这一部分在 Excel 的帮助中的解释已非常清楚，就将帮助的内 容复制于此： 创建自定义数字格式 选择要设置格式的单元格。 在―格式‖菜单上，单击―单元格‖，再单击―数字‖选项卡。 在―分类‖列表中，单击与所需格式相似的某个分类，再设置与所需格式相 似的内置格式（不必更改内置格式，可以创建一个自定义的备份） 。 在―分类‖列表中，单击―自定义‖。 在―类型‖框中，编辑数字格式代码以创建所需的格式。 在格式代码中最多可以指定四个节。这些格式代码是以分号分隔的，它们 顺序定义了格式中的正数、负数、零和文本。如果只指定两个节，则第一部分 用于表示正数和零，第二部分用于表示负数。如果只指定一个节，则该节用于 所有的数字。如果要跳过某一节，则对该节仅使用分号即可。 可以使用格式代码来描述如何显示以下内容：数字、日期或时间、货币、百分比或科学记数以及文本或空格。 格式代码 文本和空格 显示文本和数字 若要在单元格中同时显示文本和数字，可将文本字符括 在双引号 (舍 入。如果数字小数点左侧的位数大于位置标识符的位数，那么多余的位数也会 显示出来。 如果所设定的格式中小数点左侧只有一个数字符号 (#)， 那么小于 1 的数字将以小数点开始。 # 只显示有意义的数字而不显示无意义的零。 如果数字位数少于格式中的零的个数，则 0（零）将显示无意义的零。 ? 在小数点两边为无意义的零添加空格， 以便当按固定宽度字体设置格式 （例如 Courier New） 时， 小数点可对齐。 还可对具有不等长数字的分数使用 ?。 若要 请使用此代码 将 1234.59 显示为 1234.6 ####.# 将 8.9 显示为 8.900 #.000将 .631 显示为 0.6 0.# 将 12 显示为 12.0 以及 1234.568 显示为 1234.57 #.0# 显示 44.398、102.65 和 2.8 时对齐小数点 ???.??? 将 5.25 显示为 5 1/4，5.3 显示为 5 3/10，除号对齐 # ???/??? 千位分隔符 在数字格式中包含逗号，可使逗号显示为千位分隔符，或将数字放大一千 倍。 若要 请使用此代码 将 12000 显示为 12,000 #,### 将 12000 显示为 12 #, 将 12200000 显示为 12.2 0.0,, 颜色 若要设置格式中某一部分的颜色，请在该部分对应位置用方括号键 入下列八种颜色名称之一。颜色代码必须为该部分的第一项。 条件 若要设置满足指定条件的数字的格式，格式代码中应加入带中括号 的条件。条件由比较运算符和数值两部分组成。例如，下面的格式以红色字体 显示小于等于 100 的数字，而以蓝色字体显示大于 100 的数字。 [100] 若要对单元格应用条件格式（例如：取决于单元格值的颜色阴影） ，请使 用―格式‖菜单上的―条件格式‖命令。 货币、百分比和科学计数 货币符号 若要在数字格式中输入下面的货币符号之一，请先打开 Num Lock，然后使用数字键盘输入货币符号的 ANSI 代码。 若要输入 请按住 Alt 并键入此代码 ¢0162 £0163 ¥0165 0128 注意 自定义格式会保存在工作薄中。 若要使 Microsoft Excel 一直使用特 定的货币符号，请在启动 Excel 之前，更改在―控制面板‖的―区域设置‖中所选 定的货币符号。 百分比 若要以百分比格式显示数字，请在数字格式中包含百分号 (%)。 例如，数字 .08 显示为 8%，2.8 显示为 280%。 科学记数法 若要以科学计数法显示数字，请在相应部分中使用下列指数代码： ―E-‖、 ―E+‖、 ―e-‖或―e+‖。 在格式代码中， 如果指数代码的右侧含有零 (0) 或数字符号 (#),Excel 将按科学计数显示数字，并插入 E 或 e。右侧零或数 字符号的代码个数决定了指数的位数，―E-‖或―e-‖将在指数中添加负号。―E+‖ 或―e+‖在正指数时添加正号，负指数时添加减号。 日期和时间 日、月和年 如果―m‖紧跟在―h‖或―hh‖代码之后，或是后面紧接―ss‖代码， 那么 Microsoft Excel 将显示分钟，而不是月份。 若要 请使用此代码 将月份显示为 1–12 m 将月份显示为 01–12 mm 将月份显示为 Jan–Dec mmm 将月份显示为 January–December mmmm 将月份显示为该月份的第一个字母 mmmmm 将日期显示为 1–31 d 将日期显示为 01–31 dd 将日期显示为 Sun–Sat ddd 将日期显示为 Sunday–Saturday dddd 将年份显示为 00–99 yy 将年份显示为 1900–9999 yyyy 小时、分钟和秒 若要 请使用此代码 将小时显示为 0–23 H 将小时显示为 00–23 hh 将分钟显示为 0–59 m 将分钟显示为 00–59 mm 将秒显示为 0–59 s 将秒显示为 00–59 ss 使小时显示类似于 4 AM h AM/PM 使时间显示类似于 4:36 pm h:mm AM/PM 使时间显示类似于 4:36:03 P h:mm:ss A/P 按小时计算的一段时间，如 25.02 :mm 按分钟计算的一段时间，如 63:46 :ss 按秒计算的一段时间百分之几秒 h:mm:ss.00 AM 和 PM 如果格式中包括―AM‖或―PM‖，则按 12 小时方式计时，―AM‖或―A‖表示 从午夜十二点到中午十二点之间的时间，―PM‖或―P‖表示从中午十二点到午夜 十二点之间的时间，否则，按 24 小时计时。―m‖或―mm‖代码必须紧跟在―h‖ 或―hh‖代码之后，或后面紧接―ss‖代码；否则，Microsoft Excel 将显示月而不 是分。 提示：可以使用 &（―与‖符号）文本操作符连接两个数值。 删除自定义数字格式 在―格式‖菜单上，单击―单元格‖，再单击―数字‖选项卡。 在―分类‖列表中，单击―自定义‖。 在―类型‖框的底部，单击要删除的自定义格式。 单击―删除‖。 Microsoft Excel 会将工作簿中已删除的自定义格式单元格设置成默认的 ―常规‖格式。 假定我们要在 excel 单元格中将正数显示为―+3.56‖之类的样子，我们发现 所有预设的单元格格式都不能帮我们达到目的。其实，我们可以使用最后一项 ―自定义‖，具体使用方法及含义如下： 1、右键单击单元格 2、选择―设置单元格格式‖ 3、选择―数字‖标签 4、在左边的―分类‖列表框中选择―自定义‖ 5、在右边的―类型‖文本框中输入―+0.00;-0.00;0;@‖ 这样，我们发现在该单元格输入正数则前面有正号，输入负数则前面有负 号，输入 0 和文本则正常显示 0 和文本。 事实上，我们注意到第五步输入的字符串被三个分号分割成 4 个部分，其 中，第一部分是正数的显示格式，第二部分是负数的显示格式，第三部分是 0 的显示格式，第四部分是文本的显示格式。如果某部分为空白，则输入该种类 型的数据时不显示。 例如字符串―+0.00;-0.00;;@‖可以将数据为 0 的单元格显示 为空白。 下面列出可能使用到的字符串及其含义: 1、;