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

1、EXCEL中单元格格式的自定义详解1、G/通用格式:以常规的数字显示,相当于分类列表中的常规选项。例:代码:G/通用格式。10显示为10;10.1显示为10.1。2、#:数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于#的数量,则按#的位数四舍五入。例:代码:#.#,12.1显示为12.1;12.1263显示为:12.13 3、0:数字占位符。如果单元格的内容大于占位符,则显示实际数字,如果小于点位符的数量,则用0补足。例:代码:00000。1234567显示为1234567;123显示为00123代码:00.000。100.14显示为100.140;1.1显示为01.100

2、4、:文本占位符,如果只使用单个,作用是引用原始文本,要在输入数字数据之后自动添加文本,使用自定义格式为: "文本内容"要在输入数字数据之前自动添加文本,使用自定义格式为:"文本内容"。符号的位置决定了Excel 输入的数字数据相对于添加文本的位置。如果使用多个,则可以重复文本。例:代码"集团""部"财务显示为:集团财务部代码"",财务显示为:财务财务财务5、*:重复下一次字符,直到充满列宽。例:代码:*-。ABC显示为ABC-可就用于仿真密码保护:代码*;*;*;*,123显示为: *6、,:

3、千位分隔符例:代码#,#,12000显示为:12,0007、:用这种格式显示下一个字符。"文本",显示双引号里面的文本。:显示下一个字符。和用途相同都是显示输入的文本,且输入后会自动转变为双引号表达。例:代码"人民币"#,#0,"百万",与人民币#,#0,百万,输入1234567890显示为:人民币1,235百万8、?:数字占位符。在小数点两边为无意义的零添加空格,以便当按固定宽度时,小数点可对齐,另外还用于对不等到长数字的分数例:分别设置单元格格式为?.?和?.?,对齐结果如下:输入12.1212 显示12.12 12.121 9、

4、颜色:用指定的颜色显示字符。可有八种颜色可选:红色、黑色、黄色,绿色、白色、兰色、青色和洋红。例:代码:青色;红色;黄色;兰色。显示结果为正数为青色,负数显示红色,零显示黄色,文本则显示为兰色颜色N:是调用调色板中颜色,N是056之间的整数。例:代码:颜色3。单元格显示的颜色为调色板上第3种颜色。、10、条件:可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件,其中两个条件是明确的,另个是所有的其他。条件要放到方括号中。必须进行简单的比较。例:代码:>0正数;=0;零;负数。显示结果是单元格数值大于零显示正数,等于0显示零,小于零显示负数。11、!:显示"。由于引号是

5、代码常用的符号。在单元格中是无法用"""来显示出来"。要想显示出来,须在前加入!例:代码:#!"。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-10excel单元格自定义格式1代码结构组成代码码分为四个部分,中间用;号分隔

6、,具体如下:正数格式;负数格式;零格式;文本格式各个参数的涵义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显

7、示为: 12.13?:数字占位符。在小数点两边为无意义的零添加空格,以便当按固定宽度时,小数点可对齐,另外还用于对不等到长数字的分数例:分别设置单元格格式为?.?和?.?,对齐结果如下:例:设置单元格自定义格式# ?/?。1.25显示为1 1/4.:小数点。如果外加双引号则为字符。例:代码0.#。11.23显示为11.2%:百分比。例:代码#%。0.1显示为10%,:千位分隔符。数字使用千位分隔符。如时在代码中,后空,则把原来的数字缩小1000倍。例:代码:#,#。10000显示为10,000代码:#,。10000显示为10。代码:#,。1000000显示为1。/:显示下一个字符。和用途相同都

8、是显是输入的文本,且输入后会自动转变为双引号表达。不同的/是显后面的文本,双引号是显示双引中间的文本。例:代码ABC或ABC。均显示为ABC*:重复下一次字符,直到充满列宽。例:代码:*-。ABC显示为ABC-_(下划线:留下一个和下一个字符同等宽度的空格文本:显示双引号中的文本。例:代码:中国显示为中国河南颜色:用指定的颜色显示字符。可有八种颜色可选:红色、黑色、黄色,绿色、白色、兰色、青色和洋红。例:代码:青色;红色;黄色;兰色。显示结果为正数为青色,负数显示红色,零显示黄色,文本则显示为兰色颜色N:是调用调色板中颜色,N是056之间的整数。例:代码:颜色3。单元格显示的颜色为调色板上第3

9、种颜色。条件:可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件,其中两个条件是明确的,另个是所有的其他。条件要放到方括号中。必须进行简单的比较。例:代码:>0正数;=0;零;负数。显示结果是单元格数值大于零显示正数,等于0显示零,小于零显示负数。!:显示"。由于引号是代码常用的符号。在单元格中是无法用"""来显示出来"。要想显示出来,须在前加入!例:代码:#!"。10显示10"代码:#!"!"。10显示10"" 2.1.1.2 时间和日期代码常用日期和时间代码YYYY

10、或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-10AAAA:日期显示为星期。H或HH:以一位(023或两位(0123显示小时M或MM:以一位(059或两位(0159显示分钟S或SS:以一位(059或两位(0159显示秒例:代码:HH:MM:SS。23:1:15显示为23:01:15H或M或SS:显示大于24小时的小时或显示大于60的分或秒。下面介绍几

11、个常遇到的实例把12345显示为1.2代码:0.,设置千元显示且四舍五入保留两位小数要求:把12345显示为:12.35代码:#.00,在数字中任意插入字符要求:把20050512设置为: 2005-05-12代码:0000-00-00在文本或数字前添加字符要求:郑州市显示为河南省郑州市代码:"河南省"要求:1035010121显示为手机号码1034010121代码:"手机号码"或"手机号码"#另外日期格式的显示方法"e":显示四位年份,2003版本有效。"bbbb":显示四位佛历年份,即以公元

12、前543年为纪年元年,对1900年以后的日期有效。"mmm":显示英文月份的简称。"mmmm":显示英文月份的全称。"ddd":显示英文星期几的简称。"dddd":显示英文星期几的全称。关于特殊数字的显示中文小写数字DBNum1$-804G/通用格式例:代码:DBNum1$-804G/通用格式。1显示为一代码:DBNum1$-804G/通用格式。13显示为一十三中文小写数字DBNum2$-804G/通用格式例:代码:DBNum2$-804G/通用格式。1显示为壹代码:DBNum2$-804G/通用格式。13显示为壹

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"

14、。在自定义格式中定义就是"b"0000。其它的关键字自己体会如:d、e.单元格自定义格式2在Excel中自定义数字格式虽然Excel为用户提供了大量的数字格式,但还是有许多用户因为工作、学习方面的特殊要求,需要使用一些Excel 未提供的数字格式,这时我们就需要利用Excel的自定义数字格式功能来帮助实现用户的这些特殊要求。一、在Excel中创建自定义数字格式的方法1、选择要设置格式的单元格或单元格区域。2、单击格式菜单中的单元格命令,然后单击数字选项卡。3、在分类列表中,单击自定义选项。4、在类型框中,编辑数字格式代码以创建所需的格式。在Excel自定义数字格式使用如下的

15、表达方式:正数的格式负数的格式零的格式文本的格式#,#0.00 Red-#,#0.00 0.00 "T EXT"在Excel自定义数字格式的格式代码中,用户最多可以指定四个节;每个节之间用分号进行分隔,这四个节顺序定义了格式中的正数、负数、零和文本。如果用户在表达方式中只指定两个节,则第一部分用于表示正数和零,第二部分用于表示负数。如果用户在表达方式中只指定了一个节,那么所有数字都会使用该格式。如果在表达方式中要跳过某一节,则对该节仅使用分号即可。二、自定义数字格式实例(一自动添加文本(1要在输入数据之后自动添加文本,使用自定义格式为: "文本内容"要在

16、输入数据之前自动添加文本,使用自定义格式为:"文本内容"。符号的位置决定了Excel输入的数字数据相对于添加文本的位置,双引号得用英文状态下的。实例一:在学生姓名前添加初二(2班字样,就可以创建:"初二(2班"(2在输入数字之后自动添加文本。实例二:如在日常财务工作中,常常需要在金额数字后加单位元,这时就可以使用:0.00"元"或0"元"(二在自定义数字格式中使用颜色要设置格式中某一部分的颜色,只要在该部分对应位置用方括号键入颜色名称或颜色编号即可。Excel中可以使用的颜色名称有黑色、蓝色、青色、绿色、洋红、红色

17、、白色、黄色八种不同的颜色,此外Excel还可以使用颜色X的方式来设置颜色,其中X为1-56之间的数字,代表了56种不同的颜色。例如:当用户需要将单元格中的负数数字用蓝色来表示,只要使用#,#0.00;蓝色-#,#0.00自定义数字格式,用户在单元格中录入负数时,Excel就会将数字以蓝色显示。(三在自定义数字格式中使用条件格式在Excel自定义数字格式中用户可以进行条件格式的设置。当单元格中数字满足指定的条件时,Excel可以自动将条件格式应用于单元格。Excel自定义数字格式中可以使用如下六种标准的比较运算符:运算符含义= 等于> 大于< 小于>= 大于等于<= 小

18、于等于<> 不等于例如:在学生成绩工作表中,当我们想以红色字体显示大于等于90分的成绩,以蓝色字体显示小于60分的成绩时,其余的成绩则以黑色字体显示,这时只需将自定义数字格式设置为红色>=90;蓝色<60;黑色即可。值得注意的是,当你在以后需要继续使用刚才所创建的成绩条件自定义数字格式时,你会发现在单元格格式的自定义分类类型中找不到红色>=90;蓝色<60;黑色格式,这是因为Excel自动将你所创建的红色>=90;蓝色<60格式修改成红色>=90G/通用格式;蓝色<60G/通用格式;黑色G/通用格式,你只需选择此格式即可达到同样的使用

19、效果。实例一:在工人奖金列表中,当我们想以小于50的显示低,50至100间显示中等,大于100的显示高。这时只需将自定义数字格式设置为:<50"低">100"高""中等"实例二:把数学、语文成绩中90分以上替换成优:>=90"优"(四隐藏单元格中的数值在Excel工作表中,有时为了表格的美观或者别的因素,我们希望将单元格中的数值隐藏起来,这时我们使用;(三个分号的自定义数字格式就可达到此目的。这样单元格中的值只会在编辑栏出现,并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其他

20、单元格正常引用。在Excel中要想设置满足指定条件数字的格式,在自定义数字格式代码中必须加入带中括号的条件,条件由比较运算符和数值两部分组成。自定义格式:自定义格式主要说两点:1、创建或删除自定义数字格式,2、条件格式,因为这一部分在Excel的帮助中的解释已非常清楚,就将帮助的内容复制于此创建自定义数字格式选择要设置格式的单元格。在格式菜单上,单击单元格,再单击数字选项卡。在分类列表中,单击与所需格式相似的某个分类,再设置与所需格式相似的内置格式(不必更改内置格式,可以创建一个自定义的备份。在分类列表中,单击自定义。在类型框中,编辑数字格式代码以创建所需的格式。在格式代码中最多可以指定四个节

21、。这些格式代码是以分号分隔的,它们顺序定义了格式中的正数、负数、零和文本。如果只指定两个节,则第一部分用于表示正数和零,第二部分用于表示负数。如果只指定一个节,则该节用于所有的数字。如果要跳过某一节,则对该节仅使用分号即可。可以使用格式代码来描述如何显示以下内容:数字、日期或时间、货币、百分比或科学记数以及文本或空格。格式代码文本和空格显示文本和数字若要在单元格中同时显示文本和数字,可将文本字符括在双引号("" 中,或在单个字符前加反斜线(。应将字符放在格式代码的合适部分中。例如,键入格式$0.00 "剩余"$-0.00 "短缺"来显

22、示一个$125.74 剩余的正值和一个$-125.74 短缺的负值。下面的字符不用引号:$、-、+、/、( 、:、!、&、(左单引号、(右单引号、 、=、<、>和空格符。包括文本输入部分如果包含文本部分,则文本部分总是数字格式的最后一个部分。若要在数字格式中包括文本部分,请在要显示输入单元格中的文本的地方加入符号(,如果文本部分中没有,所输入文本将不会显示出来。如果要一直显示某些带有输入文本的指定文本字符,则应将附加文本用双引号("" 括起来,例如,"gross receipts for"。如果格式中不包含文本部分,那么输入的文本将不

23、受格式代码的影响。添加空格若要在数字格式中创建一个字符宽的空格,请在字符前加上一条下划线(_,例如,在下划线后跟上一个右括号(_,可使正数和括号内的负数对齐。重复的字符在数字格式代码中使用星号(*,可使星号之后的字符填充整个列宽。例如,键入0*- 可在数字后包含足够的短划线以填充整个单元格。小数点、空格、颜色和条件小数点位置和有效数字若要设置分数或带小数点的数字的格式,请在格式代码的相应部分中包含下面的数字位置标识符。如果某一数字小数点右侧的位数大于所设定格式中位置标识符的位数,该数字将按位置标识符位数进行舍入。如果数字小数点左侧的位数大于位置标识符的位数,那么多余的位数也会显示出来。如果所设

24、定的格式中小数点左侧只有一个数字符号(#,那么小于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、.25 显示为5 1/4,5.3 显示为5 3/10,除号对齐# ?/?千位分隔符在数字格式中包含逗号,可使逗号显示为千位分隔符,或将数字放大一千倍。若要请使用此代码将12000 显示为12,000 #,#将12000 显示为12 #,将12200000 显示为12.2 0.0,颜色若要设置格式中某一部分的颜色,请在该部分对应位置用方括号键入下列八种颜色名称之一。颜色代码必须为该部分的第一项。Black BlueCyan GreenMagenta RedWhite Yellow条件若要设置满足指定条件的数字的格式,格式代码中应加入带中括号的条件。条件由比较运算符和数值两部分组成。例如,下面的格

26、式以红色字体显示小于等于100 的数字,而以蓝色字体显示大于100 的数字。Red<=100;Blue>100若要对单元格应用条件格式(例如:取决于单元格值的颜色阴影,请使用格式菜单上的条件格式命令。货币、百分比和科学计数货币符号若要在数字格式中输入下面的货币符号之一,请先打开Num Lock,然后使用数字键盘输入货币符号的ANSI 代码。若要输入请按住Alt 并键入此代码¢ 0162£ 0163¥ 01650128注意自定义格式会保存在工作薄中。若要使Microsoft Excel 一直使用特定的货币符号,请在启动Excel 之前,更改在控制面板的区

27、域设置中所选定的货币符号。百分比若要以百分比格式显示数字,请在数字格式中包含百分号(%。例如,数字 .08 显示为8%,2.8 显示为280%。科学记数法若要以科学计数法显示数字,请在相应部分中使用下列指数代码:E-、E+、e-或e+。在格式代码中,如果指数代码的右侧含有零(0 或数字符号(#,Excel 将按科学计数显示数字,并插入 E 或e。右侧零或数字符号的代码个数决定了指数的位数,E-或e-将在指数中添加负号。E+或e+在正指数时添加正号,负指数时添加减号。日期和时间日、月和年如果m紧跟在h或hh代码之后,或是后面紧接ss代码,那么Microsoft Excel 将显示分钟,而不是月份。若要请使用此代码将月份显示为112 m将月份显示为0112 mm将月份显示为JanDec mmm将月份显示为JanuaryDecember m