如何设置自定义数字格式代码

如何设置自定义数字格式代码

Excel提供了大量样式的内部数字格式，但仍然不能满足部分人的需要，于是我们就需要使用自定义数字格式来处理问题。

1.创建自定义格式

例如：单元格中内容显示为“张三”，要求将“张三”显示为“李四”。

操作步骤：选取”张三”的单元格，打开【单元格格式】对话框，选取【数字】选项卡，在【分类】列表框中选取【自定义】选项，在右侧的【类别】框中输入“;;;“李四””。

2.自定义格式代码基本结构

人们可以指定一系列代码作为数字格式来创建自定义格式，如上面的例题，“;;;“李四””就是一组代码。代码分为四部分，中间用“;”号分隔，具体如下：正数格式；负数格式；零格式；文本格式

3.自定义格式常用的代码

代码是设置自定义格式必不可少的部分，主要有数字和文本代码，以及日期和时间代码两种。

1）数字和文本代码

“G/通用格式”：以常规的数字显示，相当于【分类】列表中的【常规】选项。如输入10.001就显示10.001.

“0”：数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于占位符，则用0补位。如代码“000”时，12则显示为012.

“#”：数字占位符。只显示有意义的零而不显示无意义的零。如代码为“###”显示12，而不是显示012，此处12前的0即为无意义的零。小数点后数字大

于“#”的数量，则按“#”的位数四舍五入。

“？”：数字占位符。在小数点两边为无意义的零添加空格，以便当按固定宽度是小数点可对齐。

“，”：千位分割符号。数字使用千位分割符号。如果在代码中“，”后，则把原来的数字缩小1000倍。如设置代码：“0，”，1000则显示为1.

“文本”：显示双引号中的文本。

[颜色]：用指定的颜色显示字符。有八种颜色可选，红色、黑色、黄色、绿色、白色、蓝色、青色和洋红。

[条件]：可以先判断单元格内容，然后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另一个是除了这两个条件外的其他情况，条件要放到方括号中。如代码设置：“[>100]”“超额完成”；[=100]“基本完成”；“未完成””,意义为大于100显示“超额完成”，等于100显示为“基本完成”，其他的显示为“未完成”。

2）日期和时间代码

下面是常用的日期和时间代码。

“YYYY”或“YY”：按四位（1900~9999）或两位（00~99）显示年。“MM”或“M”：以两位（01~12）或（1~12）显示月

“DD”或“D”：以两位（01~31）或一位（1~31）来显示天

“H”或“HH”：以一位（0~23）或两位（01~23）来显示小时

“M”或“MM”：以一位（0~59）或（01~59）显示分钟

“S”或“SS”：以一位（0~59）或（01~59）显示秒。

4.数字自定义格式综合应用实例

1）设置万元显示

要求：把“12345”显示为“1.2”

代码：“0”.“#，”

代码说明：由于是万元显示，所以小数点要向前移动四位，这里用“，”向前移三位，用“#”占一位，总共是四位。把“.”插入到一个小数的倒数第五位，然后用“0”确保“.”插入的是数字中间，完成了万元显示。

注意：用“#”.“#”也可以完成万元显示，但当数字小于1万时，小数点前没有显示零，如5000，显示为“.5”。

用途：在上报的财务报表中，有报送的对象只须知道大概总数，而不必知道某个数字是几元几角时，这时把报表做成万元显示是一个理想选择。

2）小数位对齐

要求；将一组数字对齐。

代码：“？？？.？？？”

代码说明：“.？？？”可以在小数点后对无意义的零添加空格，在某一列使用自定义格式，小数点后无论几位，都会用空格补充，以达到小数点对齐的效果。用途：在工作表中对齐某列无规则小数位数。

3）设置文本型代码

要求：“1”显示为“000001”。

代码：“000000”

代码说明：“0”是数字占位符，当数字的实际位数小于“0”的位数时，会显示这些无意义的零。

用途：在商品管理中，一般都要设置物品代码，而这些物品代码可以通过设置文

本格式后再来输入代码来实现，但无意义的零还是要手工输入。用零作为占位符的自定义数字代码设置数字格式，则可以让单元格自动添零。

3）数字串显示为日期格式

要求：把“20090811”设置为“2008-08-11”。

代码：“0-00-00”

代码说明：0是数字占位符，“0-00-00”中的最后两个零是为11占位，中间的两个零为08占位，第一个0是为前面的2009占位。

用途：在很多专业软件导出的日期为YYYYMMDD格式，而在Excel中则不是标准的日期格式，这时用插入“-”的方法可显示为标准的日期格式。

4）带单位符号显示的数字计算

要求：在“数量”列显示的数量后加单位“条”；在“进价”列的进价后显示单位“元/条”；在“金额”列能对“数量”列和“进价”列进行运算，并在该列金额后单位“元”，即金额=数量*进价。

代码：“数量”列：#“条” “进价”列：#“元/条” “金额”列：#“元”代码说明：代码中的文本可以在单元格中显示，即带引号的文本作为单元格添加的显示内容，“#”来代表单元格中实际数字，其实运算还是在数字之间进行的。用途：当遇到单元格中的内容为运算的数字，但又需要显示数字加文本时就可以用到，在数值单元格中添加单位。

绝对引用和相对引用的区别

绝对引用

单元格中的绝对单元格引用（例如$F$6）总是在指定位置引用单元格F6。如果公式所在单元格的位置改变，绝对引用的单元格始终保持不变。如果多行或