_Excel公式教程 (2016-01-28 21:41:37)
转载
▼
标签： excel公式教程 单元格格式代码分类： Excel公式教程-原理篇
在自定义格式代码中最多可以指定四个部分，依次定义正数、负数、零和文本的格式；各部分之间用分号隔开。
当只指定了一个部分，如果用符号@指定为文本格式，则单元格的内容将全部转换为文本值；如果没有用符号@指定为文本格式，则该部分同时定义正数、负数和零的格式，文本使用常规格式。
当只指定了两个部分，如果第二部分用符号@指定为文本格式，则第一部分同时定义正数、负数和零的格式；如果第二部分没有用符号@指定为文本格式，则第一部分定义正数和零的格式，第二部分定义负数的格式，文本使用常规格式。
当只指定了三个部分，如果第三部分用符号@指定为文本格式，则第一部分同时定义正数和零的格式，第二部分定义负数的格式；如果第三部分没有用符号@指定为文本格式，则三个部分依次定义正数、负数和零的格式，文本使用常规格式。
文本格式必须是格式代码的最后一部分。
如果格式代码某部分为空格式，则单元格不显示该部分对应的值。例如：
格式“;;;”，三个分号使四个部分都是空格式，该格式隐藏单元格的值，但不能隐藏错误值。
格式“G/通用格式;-G/通用格式;G/通用格式;”，最后一部分为空格式，隐藏文本值不显示。
格式“G/通用格式;;;”，后面三个部分都是空格式，仅显示正的数字值。
格式“;-G/通用格式;;”，仅显示负的数字值。
格式字符不区分字母大小写（但科学记数形式里的E必须用大写）。详述如下：
【1】G/通用格式 就是常规格式。
【2】. 小数点符。在数字部分中，如果没有小数点符，数字将四舍五入为整数显示；如果有多个小数点符，则以第一个为小数点定位符，其余的视作在小数部分插入的字符。例如：
格式“0”为整数格式，输入56.7显示“57”。
格式“0.000.000”的第一个小数点符为小数点定位符，输入123.45678显示“123.456.780”。
【3】#、0、? 数字占位符。
（1）如果数字小数点右侧的位数大于所设定格式中占位符的位数，则该数字将按占位符位数进行四舍五入；如果数字小数点左侧的位数大于占位符的位数，那么超出的位数也会显示出来。
（2）如果数字小数点两侧的位数小于占位符的位数，占位符#不显示无意义的零，而占位符0显示无意义的零，占位符?则以空格代替无意义的零。
（3）如果所设定的格式中小数点左侧全部是#，那么小于1的数字将以小数点开始。如果格式中小数点右侧是#，则小数点可能不对齐。
如果单元格的值为0，设置格式“0”显示“0”；设置格式“#”或“?”显示空白，相当于在“选项”对话框“视图”选项卡中设置了零值不显示。
格式“0.00”为两位小数格式，输入45.678显示“45.68”，输入345.6显示“345.60”。
格式“0-00-00”把8位数字以日期形式显示，输入20031225显示“2003-12-25”。
【4】, 千位分隔符或以一千为单位表示数字的数量级。例如：
格式“#,##0”为带千位分隔符的整数格式，输入1234.5显示“1,235”。
格式“#,##0”为带千位分隔符的整数格式，输入1234.5显示“1,235”，输入1234567显示“1,234,567”。
格式“#,##0.00”为带千位分隔符的两位小数格式，输入1234.5显示“1,234.50”。
格式“0,,百万”以百万为单位保留整数，输入5678900显示“6百万”。
【5】% 百分号，相当于把数字除以100，并在后面添加百分号。例如：
格式“0.00%”为两位小数的百分比格式，输入70.2显示“70.20%”。
【6】分数形式。例如：
格式“# ?/?”，输入1.5显示“1 1/2”。
格式“# ?/4”，输入.5显示“2/4”。可以任意指定分母数字。
也可在单元格直接以分数形式输入，例如输入“0 1/2”，即相当于输入0.5并自动设置成分数格式。
【7】E 、E- 科学记数形式。E必须是大写。例如：
格式“0.00E 00”，输入.5显示“5.00E-01”，输入123456789显示“1.23E 08”。
格式“0.00E-00”，输入.5显示“5.00E-01”，输入123456789显示“1.23E08”。
【8】货币格式。例如：
格式“￥#,##0.00;￥-#,##0.00”为人民币格式，输入12345.67显示“￥12,345.67”。
格式“$#,##0.00;$-#,##0.00”，输入12345.67显示“$12,345.67”。
格式“"US$"#,##0.00;-"US$"#,##0.00”为美元格式，输入12345.67显示“US$12,345.67”。
格式“[$HKD] #,##0.00;[$HKD] -#,##0.00”为港币格式，输入12345.67显示“HKD 12,345.67”。
格式“[$HK$-C04]#,##0.00;-[$HK$-C04]#,##0.00”为港币格式，输入12345.67显示“HK$12,345.67”。
货币格式有很多种形式，详见单元格格式对话框、货币格式中“货币符号（国家/地区）”下拉菜单。
【9】_ 留下一个和下一个字符同等宽度的空格。例如设置格式“0.00_);(0.00)”可使正数和括号内的负数对齐。
【10】@ 显示输入的文本。只能在格式代码的最后一部分使用@。如果格式代码只有一个部分且使用了@，则输入的值都将保存为文本。使用多个@可重复显示输入的内容。例如：
格式“@”，就是文本格式，单元格设置该格式后，输入的所有内容（包括公式）将存储为文本。而设置格式前所输入的内容并不改变。设置文本格式后，单元格自动默认左对齐。
格式“@-@”，输入“温州”，显示“温州-温州”。
【11】"" 显示双引号内的字符。
在格式中可以直接输入格式代码以外的字符而无需使用引号，例如：
格式“0万”，输入“12”，显示“12万”。
格式“0!.0,万”以万为单位保留1位小数，输入125670显示“12.6万”。
格式“0!.0000万”以万为单位保留4位小数，输入12567显示“1.2567万”。
要把单元格设置为以万为单位保留两位小数，可设置格式“0.00,,万%”，在%前按住Alt，用小键盘输入10（即相当于输入了一个换行符），并把单元格设置为自动换行。
格式“0!.00,,亿”以亿为单位保留两位小数，输入18653471415显示“186.53亿”。
如果想单元格显示格式符号，可以用括号括住。例如：
格式“0"mm"”，输入“23”，显示“23mm”。
【12】\ 显示下一个字符及其后的字符，直到遇到格式符号为止。例如：
格式“0\ml”，输入“23”，显示“23ml”。
【13】! 显示下一字符。一般用于显示1个格式符号。例如：
格式“!"0!ml!"”，输入“123”，显示“"123ml"”。
【14】* 重复下一字符，使单元格内容充满列宽。
格式“**;**;**;**”模仿密码保护，输入任何值都显示“********”，可惜在输入过程中还是显示的。
格式“*- 0”模仿目录中页码之前、与标题之间的分隔符，输入“2”，显示“------ 2”。
格式“@ *-”模仿目录中标题之后、与页码之间的分隔符，输入“绪论”，显示“绪论 ---”。
【15】日期格式。
（1）y 年份。
y或yy 显示两位年份。
yyy或yyyy 显示四位年份。
（2）e 显示四位年份。E也是科学计数形式的代码符。
（3）b 佛历BE（Buddist Era）年份，即以公元前543年为纪年元年，对1900年以后的日期有效。
b或bb 显示两位佛历年份。
bbb或bbbb 显示四位佛历年份。
（4）b1、b2 以公历、回历解析其后日期格式的y、m、d。
（5）m 月份。M在时间格式中代表分钟。
m 显示月份的阿拉伯数字形式：1～12。
mm 显示月份的两位阿拉伯数字形式：01～12。
mmm 显示月份的英文简写形式：Jan～Dec。
mmmm 显示月份的英文形式：January～December。
mmmmm 显示月份的第一个英文字母：J～D。
（6）d、a 日期或星期。
d 显示日期的阿拉伯数字形式：1～31。
dd 显示日期的两位阿拉伯数字形式：01～31。
ddd 显示星期的英文简写形式：Mon～Sun。
dddd 显示星期的英文形式：Monday～Sunday。
aaa 显示星期的中文简写形式：一～日。
aaaa 显示星期的中文形式：星期一～星期日。
（7）-、/ 日期分隔符。可在控制面板的“区域和语言选项”里设置默认的日期分隔符。
（8）g 日本年号。明治年间为1868～1911年；大正年间为1912～1925年；昭和年间为1926～1988年；1989年之后是平成。
g 显示大写字母的日本年号：M、T、S、H。
gg 显示汉字简称的日本年号：明、大、昭、平。
ggg 显示汉字的日本年号：明治、大正、昭和、平成。
例如，单元格输入“2015-8-20”，
设置格式“e年mm月dd日（aaa）”，显示“2015年08月20日(四)”。
设置格式“e年”“yyy年”或“yyyy年”，显示“2015年”。
设置格式“y年”或“yy年”，显示“15年”。
设置格式“佛历bbb年”或“佛历bbbb年”，显示“佛历2558年”。BE=AD 543。
设置格式“b2回历yyy年m月d日”，显示“回历1436年11月6日”。
设置格式“m月”，显示“8月”。
设置格式“d”，显示“20”。
设置格式“aaaa”，显示“星期四”。
设置格式“e"年1月-"e年m月”，显示“2015年1月-2015年8月”。
设置格式“[$-411]ggg e年m月d日”，显示“平成 27年8月20日”。
【16】时间格式。
（1）h 小时。
h 显示0～23。
hh 显示00～23。
（2）m 分钟。
m 显示0～59。
mm 显示00～59。
（3）s 秒。
s 显示0～59。
ss 显示00～59。
（4）: 时间分隔符。可在控制面板的“区域和语言选项”里设置默认的时间分隔符。
（5）[h][m][s] 小时、分钟、秒的发生数。
例如，单元格输入“16:3:36”，
设置格式“h:mm:ss”，显示“16:03:36”。
设置格式“h时m分s秒”，显示“16时3分36秒”。
设置格式“[$-409]h:mm am/pm”或“[$-409]h:mm a/p”，显示“4:03 PM”。
设置格式“[$-804]h:mm 上午/下午”，显示“4:03 下午”。
设置格式“上午/下午h时m分s秒”，显示“下午4时3分36秒”。
又如，单元格输入：
="16:50:36"-"12:10:16"
设置格式“[h]小时”，显示“4小时”，注意是截尾舍入。
设置格式“[h]小时m分”，显示“4小时40分”，注意m不能再加“[]”。
设置格式“[m]分种”，显示“280分种”。
另外，单元格可以输入并显示百分之一秒，而编辑栏只能四舍五入显示到秒。例如：
设置格式“h:mm:ss.00”，输入16:03:36.75，单元格显示“16:03:36.75”，编辑栏只显示“16:03:37”。
【17】[颜色] 可用颜色名称[黑色][白色][红色][蓝色][黄色][绿色][洋红][蓝绿色]设置八种颜色，或用[颜色1]-[颜色56]调用调色板上的颜色。
设置格式“[红色]0;[绿色]0”，输入正数显示为红色字体，输入负数显示为绿色字体。
【18】[条件] 可以在格式代码中设置简单的条件判断。
1、条件由比较运算符、数字常量构成，例如：[>=60]。
2、条件只比较数字，不比较文本。如果比较运算符后面是文本、引用，或不指定比较对象，一律视作数字0。例如：[<>A]、[<>A1]、[<>]都相当于[<>0]。
3、格式代码中最多可以设置两个条件，但格式代码可包含四个部分：
[条件1]数字格式1;[条件2]数字格式2;数字格式3;文本格式
设置格式“[>=60]合格”，输入大于等于60的数字显示“合格”；输入小于60的数字或文本值，则使用常规格式显示。
设置格式“[>=60]合格;;不合格;缺考”，输入大于等于60的数字显示“合格”；输入负数则不显示（使用第二部分的空格式）；输入小于60的数字显示“不合格”；输入文本“没考试”则显示“缺考”。
设置格式“[红色][>=85]A;[洋红][>=60]!B;[蓝色]C;[绿色]!D”，输入大于等于85的数字，则显示红色的A；输入小于85且大于等于60的数字，则显示洋红色的B；输入小于60的数字，则显示蓝色的C；输入文本则显示绿色的D。
【19】[DBNum] 中文数字或全角数字。
例如，单元格输入“105000.8”，
设置格式“[DBNum1]”显示中文小写数字“一十万五千.八”；
设置格式“[DBNum1]0.00”显示中文小写数字“一○五○○○.八○”；
设置格式“[DBNum2]”显示中文大写数字“壹拾万伍仟.捌”；
设置格式“[DBNum2]0.00”显示中文大写数字“壹零伍零零零.捌零”；
设置格式“[DBNum3]”显示全角数字“１十万５千.８”；
设置格式“[DBNum3]0.00”显示全角数字“１０５０００.８０”。
又如，单元格输入日期“2003-02-15”，
设置格式“[DBNum1]e年m月d日”显示中文小写日期“二○○三年二月十五日”。
设置格式“[DBNum2]e年mm月dd日”显示中文大写日期“贰零零叁年零贰月壹拾伍日”。
【20】[$] 设置数字、货币、日期和时间格式所属的国家/地区。比如：[$-804]代表中国，[$-409]代表美国，[$-809]代表英国，等等。
