活用、巧用数字自定义格式设置及其代码

p81h86f

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）带单位符号显示的数字计算
要求：在“数量”列显示的数量后加单位“条”；在“进价”列的进价后显示单位“元/条”；在“金额”列能对“数量”列和“进价”列进行运算，并在该列金额后单位“元”，即金额=数量*进价。
代码：“数量”列：#“条”        “进价”列：#“元/条”        “金额”列：#“元”
代码说明：代码中的文本可以在单元格中显示，即带引号的文本作为单元格添加的显示内容，“#”来代表单元格中实际数字，其实运算还是在数字之间进行的。
用途：当遇到单元格中的内容为运算的数字，但又需要显示数字加文本时就可以用到，在数值单元格中添加单位。

p81h86f

自己顶个 ，打了一下午的字

wnianzhong

下边这个好象不对，代码就为：“0!”.“#，”自己试一下。

1）设置万元显示
要求：把“12345”显示为“1.2”
代码：“0”.“#，”

biaotiger1

码字很辛苦滴
谢谢分享

另回复二楼，是不是为如下啊，，，，
0"."#,

良凤精品

整理的不错.
如果输入  ;;; 则什么都不显示.

提拉米苏08

要求：把“12345”显示为“1.2”
代码：“0”.“#，”好像不对
应该是这个样子的=“0!.#，”

佛山小老鼠

谢谢楼主，辛苦了，好好学习一下。

123qmy

整理得真好，学习了

poembs_001

很辛苦滴
谢谢分享

caotor

好东西啊，收藏了，在显示万元单位时，如何保持千分号也显示呢？