Excel自定义格式规则及其用法

自定义格式规则及其用法一、自定义格式的进入开始——单元格——格式——设置单元格格式－－数字——自定义二、基本原理在格式代码中，最多可以指定四个节；每个节之间用分号进行分隔，这四个节顺序定义了格式中的正数、负数、零和文本；只指定两个节，则第一部分用于表示正数和零，第二部分用于表示负数；只指定了一个节，那么所有数字都会使用该格式；如果要跳过某一节，则对该节仅使用分号即可。三、基础字符1.数字占位符,有三种：?#02.中括号[]表示条件3.逗号,千位分隔符,如用在代码最后，表示将数字缩小1000倍4.分号，隔开条件，末尾可省占位符注释自定义常规格式后G/通用格式以常规的数字显示，相当于”分类”列表中的”常规”选项。47.547.5#数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于”#”的数量，则按”#”的位数四舍五入。#,##01556.1221,556###.##1810.21810.200数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，则用0补足。00000123456712345671230012300.0001234.11234.10012.212.2002.302.300?数字占位符。在小数点两边为无意义的零添加空格，以便当按固定宽度时，小数点可对齐，另外还用于对不等到长数字的分数??.??12.123412.12???.???12.123\相当于，都是显示输入的文本，且输入后会自动转变为双引号表达。人民币#,##0,,百万1234567890人民币1,235百万￥#,##0,,/百万1234567890¥1,235百万！显示“”。由于引号是代码常用的符号。在单元格中是无法用来显示出来“”。要想显示出来，须在前加入“！”#!1212#！!12@文本占位符，如果只使用单个@，作用是引用原始文本。要在输入数字数据之后自动添加文本，使用自定义格式为：”文本内容”@；要在输入数字数据之前自动添加文本，使用自定义格式为：@”文本内容”。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。如果使用多个@，则可以重复文本。有限公司@部财务有限公司财务部@@@财务财务财务*重复下一次字符，直到充满列宽@*-123123----------------------**;**;**;***************************_在数字格式中创建N个字符宽的空格____###123123，千位分隔符#,###1212,000[]使用颜色代码或者使用条件：[红色][=100];[蓝色][100]5959§[颜色N]：是调用调色板中颜色，N是0~56之间的整数，例如：1是红色、2是黑色、3是黄色。123123[颜色4]123123§[条件]：可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他”。[0]正数；[=0];零；负数1正数-6-负数0零颜色用指定的颜色显示字符。可有八种颜色可选：红色、黑色、黄色，绿色、白色、兰色、青色和洋红。条件可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他”。条件要放到方括号中。必须进行简单的比较。空表示隐藏;;;wew四、基本用法要求设置自定义格式输入效果备注常用格式显示四个节意义：正数;负数;零;文本-65负数在原有的文本上加上新文本或数字@有限公司深圳深圳有限公司数值的大写格式1[DBNum1]G/通用格式元整1112一千一百一十二元整或选择特殊－中文小写数字数值的大写格式2[DBNum2]G/通用格式1112壹仟壹佰壹拾贰或选择特殊－中文大写数字带单位符号的数值0.00元1112.21112.20元电话区号输入[包含3或4位][=400]000;00007550755当=400时,显示3位,否则4位仿真密码保护**;**;**;**1111********************隐藏单元格中的所有值;;;12345重复的字符[用星号之后的字符填充]0*-1212------------------数字格式将1234.59显示为1234.6####.#100.2100.2将8.9显示为8.900#.0000.88.880将.631显示为0.60.#1212.12显示为12.0；4.568显示为4.57#.0#1212.0对齐小数点???.???11.分数时除号对齐#???/???2.2521/4科学计数法#.###E+0034563.456E+03千位分隔符若要放大显示可灵活设置。以万为单位：0!.0,万元千位分隔符#,###1200012,000千位分隔符[将数字放大1000倍]#,1200012千位分隔符[以百万为单位]0.0,,1200000012.0颜色若要设置格式中某一部分的颜色，请在该部分对应位置用方括号键入下列八种颜色名称之一。颜色代码必须为该部分的第一项。除了这8种颜色外，还可以调用调色板其他颜色：如[COLOR16]或[颜色16][黑色][蓝色][洋红色][红色][青色][绿色][白色][黄色]数值范围用颜色区分[红色][=100];[蓝色][100]9999日期和时间日[d]、月[m]、年[y]、小时[h]、分钟[m]、秒[s]将月份显示为1–12m=NOW()4将月份显示为01–12mm=NOW()04将月份显示为Jan–Decmmm=NOW()Apr将月份显示为January–Decembermmmm=NOW()April将月份显示为该月份的第一个字母mmmmm=NOW()A将日期显示为1–31d=NOW()3将日期显示为01–31dd=NOW()03将日期显示为Sun–Satddd=NOW()Wed将日期显示为Sunday–Saturdaydddd=NOW()Wednesday将年份显示为00–99yy=NOW()99将年份显示为1900–9999yyyy=NOW()2019将小时显示为0–23H=NOW()23将小时显示为00–23hh=NOW()23将分钟显示为0–59mm或mm必须紧跟在h或hh代码之后,或后面紧接ss代码否则,将显示月而不是分将分钟显示为00–59mm将秒显示为0–59s=NOW()29将秒显示为00–59ss=NOW()29使小时显示类似于4AMhAM/PM=NOW()11PM使时间显示类似于4:36pmh:mmAM/PM=NOW()11:24PM使时间显示类似于下午4时36分上午/下午h时mm分=NOW()下午11时24分使时间显示类似于4:36:03Ph:mm:ssA/P=NOW()11:24:29P按小时计算的一段时间[h]:mm124:00按分钟计算的一段时间[mm]:ss11440:00按秒计算的一段时间[ss]186400百分之几秒h:mm:ss.00=NOW()23:24:28.86组合与拓展应用显示数据意义0.00剩余;-0.00短缺”-112-112.00短缺正数与括号内负数对齐#,##0_);(#,##0);-0-_)44下划线+右括号:页码格式-??0-56-56-使正数和括号内的负数对齐将日期显示为年月日（数字）yyyy年m月d日2005-5-102005年5月10日将日期显示为年月日(全中文）[DBNum1]yyyy年m月d日2005-12-1二○○五年十二月一日将月份显示为一月～十二月[$-804]mmmm=NOW()4月将日期显示为一～日aaa=NOW()三或：[$-804]ddd将日期显示为星期一～日aaaa=NOW()星期三或：[$-804]dddd添加空格_￥*#,##0_;_￥*-#,##0_;_￥*-_;_@_89¥89￥在左端(空一格),输入的数字在右端(空一格)英寸（分数）###/##!2.2521/41,以此类推，还有：0!？及0.00!*等2,!符号也可用\代替角度显示[hh]°m!'s!56:12:3456°12'34角度计算[hh]°m′s″23:54:4580°7′19″角度转换为秒[ss]!23:54:4586085例一、输入1显示对号，输入2显示错号代码：[=1]√;[=2]×操作方法：步骤1：选取要输入的区域，打开单元格格式设置窗口。步骤2：数字》自定义》输入以上代码；注:√可以先在单元格中输入，然后粘到自定义格式框中。例二、电话号码分段显示（手机号码，要求显示成***********的格式）代码：00000000000操作方法：打开单元格格式窗口》数字》自定义，在右侧的文本框中输入代码。例三、自动添加上下箭头代码：[颜色50][0]↑*0.00;[颜色3][0]↓*0.00数据分析中，对比分析法是最常用的。可以自动添加上升和下降箭头的功能，非常非常的实用。正数：数字前添加绿色的上升箭头↑；负数：数字前添加红色的下降箭头↓设置方法：打开单元格格式设置窗口》数字》自定义，在右侧的代码框中输入以上代码。代码说明：1在自定义格式中可以设置条件，条件放在中括号内。如：[0]2两个条件之间用分号(;)分隔，最多可以设置2个条件：[条件1]条件1显示的格式;[条件2]条件2显示的格式;非前两种条件显示的格式。