自定义格式(学习).doc

用自定义格式进行数字缩放&自定义功能及技巧介绍
用自定义格式进行数字缩放&自定义功能及技巧介绍

贴前插播个广告，欢迎广大财务同行加入19648285 QQ群讨论excel在财务中的运用，人员有限，先到先加。
请到本人博客下载excel在财务中的经典运用《[email=
推荐

     在财务人员的实际工作中经常要用到数值的缩放，如果要将数值改为缩放后的值，可用选择性粘贴--运算的功能，如果仅仅是希望将数值显示为绽放后的值，而不改变原数据，则应使用自定义格式的功能。关于自定义格式的功能请参见本文后的关于自定义功能的介绍。本文主要对数字缩放的各种自定义格式进行总结。

缩小显示：

原始数据
显示的值
自定义格式

123,456,789.56
123456790
#
按四舍五入取整显示

123,456,789.56
12345679.0
0"."0
按十显示

123,456,789.56
1234567.90
0"."00
按百显示

123,456,789.56
123456.79
0.00,
按千显示

123,456,789.56
123456.790
0.000,
按千显示

123,456,789.56
123456.790
0"."000
按千显示

123,456,789.56
12345.7
0"."0,
按万显示

123,456,789.56
12345.6790
0"."0000
按万显示

123,456,789.56
1234.57
0"."00,
按十万显示

123,456,788.56
123.5
0.0,,
按百万显示

123,456,789.56
123.46
0.00,,
按百万显示

123,456,790.56
123.457
0.000,,
按百万显示

123,456,789.56
12.3
0"."0,,
按千万显示

123,456,789.56
1.23
0"."00,,
按亿显示

123,456,789.56
0.123
0"."000,,
按十亿显示

放大显示：

1234.567
12350
#"0"
按四舍五入去掉小数后乘以10显示
1234.567
123500
#"00"
按四舍五入去掉小数后乘以100显示
1234.567
1235000
#"000"
按四舍五入去掉小数后乘以1000显示

附：自定义格式功能介绍。
下文摘自《龙逸凡excel培训手册之潜龙在渊》

下面重点讲解一下数字格式的自定义功能：
完整的格式代码由四个部分组成，这四部分顺序定义了格式中的正数、负数、零和文本。格式代码各部分以分号分隔。常用格式如下图：

它们如果只指定两个部分，则第一部分用于表示正数和零，第二部分用于表示负数。如果只指定一个部分，则该部分可用于所有数字。如果要跳过某一部分，则使用分号代替该部分即可。
以下为excelhome网站版主gdliyy的补充:
单元格自定义格式，一般是：[正数];[负数];[零值];文本，准确地说应该是：[条件1格式];[条件2格式];[条件1与条件2的补集];文本　的格式，只是默认是0为分界点，所以通常认为是[正数];[负数];[零值];文本。
另外，通常情况下，建议用户使用“大于条件在前，小于条件在后”的方式进行设置，虽然Excel支持使用：[红色][<=100];[蓝色][>100]　这种格式，但不建议这样使用，因为如格式设置为：[红色][<=10]0;[蓝色][>=5]0;[绿色]0　这样的格式，那么没有数值会显示为绿色，而只有使用：[蓝色][>=10]0;[红色][<=5]0;[绿色]0　才能使(5,10)之间的数值显示为绿色。
可以自定义格式代码来定义如何显示数字、日期或时间、货币、百分比或科学计数以及文本或空格等，具体方法请参见Excel的联机帮助“创建或删除自定义数字格式”。

特殊符号在自定义格式中的使用
在自定义格式中可以使用多种符号，它们的作用各不相同，下面分别介绍一些常用符号的用法。在自定义格式中，下面的字符不必用引号括起来：$、-、+、/、( )、:、!、^、&、'（左单引号）、'（右单引号）、~、{ }、=、<、> 和空格符。
，作为千位分隔符或以一千为单位表示数字的数量级，请参见本专题技巧7的实例。
"" 显示双引号之间的文本。
“\”或“!”：显示下一个字符。和""用途相同。且输入后会自动转变为双引号表达。不同的是“\”显示后面的文本，双引号是显示双引号中间的文本；“\”或“!”只能强行将紧跟其后的单个字符显示成文本，而双引号则可以使引号间的多个字符显示为文本。此设定此格式后。如果单独使用“\”或“!”来自定义格式则将输入的数字显示为所设定的文本，但对输入的文本无效。
# 只显示有效数字而不显示无效的零。
0（零）如果数字的位数少于格式中的零，则显示无效的零。
? 在小数点两边添加无效的零，以便当按固定宽度字体设置格式（例如 Courier New）时，小数点可对齐。还可对具有不等长数字的分数使用?。
* 可使星号之后的字符填充整个列宽。例如，格式“0*A”可在数字后包含足够的A以填充整个单元格，一直把整个单元格塞满为止，即使列宽增加，它还是会自动塞满的。
[] 中括号在自定义格式中有两个用途：使用颜色代码、使用条件。如自定义格式“[红色][<100];[蓝色][>100]”表示以红色字体显示小于和等于 100 的数字，而以蓝色字体显示大于 100 的数字。
@
若要在数字格式中包括文本部分，请在要显示输入单元格中的文本的地方加入符号 @，自定义格式“@"逸凡账务系统"”，如在单元格输入123，则显示“123逸凡账务系统”。
逸凡注：在格式中使用@与使用""有区别。@可用于数字或文本，如用于数字也是以文本形式存储的，而类似“0.00"逸凡账务系统"”的格式仅适用于数字，对文本无效。如下图：

技巧1：根据数值的正负自动判断加载不同的“前后缀”：如将格式设置为“"盈余"0.00 ;"短缺"0.00”、或“$0.00 "剩余";$-0.00"短缺"”，分别输入正负数你便可体会到两者的区别。
技巧2：数字格式中的智多星——自动设置满足指定条件的数字格式。
在格式代码中加入带中括号的条件，条件由比较运算符和数值两部分组成。例如，下面的格式以红色字体显示小于和等于 100
的数字，而以蓝色字体显示大于 100
的数字：
[红色][<=100];[蓝色][>100]
逸凡注：此功能有点类以于条件格式。但此条件仅限于由比较运算符和数值两部分组成（此类运算符包括：= 等于、>
大于、<
小于、>=
大于等于、<= 小于等于和 <> 不等于）。而条件格式的适用范围更广，还可使用由函数组成的公式组成的条件，格式范围也更多更广。
技巧3：自动显示固定宽度：如将格式自定义成000000，则输入小于6位的数字，自动在前面以0填充，输入大于6位的数字或文本则不变。
技巧4：如果单元格区域既含有五位数的邮政编码又含有九位数的邮政编码，则可以使用自定义格式以便同时正确显示这两种类型的邮政编码。方法是：单击“分类”列表中的“自定义”，然后在“类型”框中键入下列内容： [<=99999]00000;00000-0000
技巧5:Excel中的障眼法
1、在【格式】菜单上，单击〖单元格〗子菜单，再单击“数字”选项卡,
在“分类”列表中，单击“自定义”,
在“类型”框中键入“"逸凡账务系统"”试试,不管输入什么,显示出来的都是“逸凡账务系统”了。
2、如需将输入的所有数字均以*显示，自定义格式为“**;**;**”，同理，将输入的所有数字均以A显示，自定义格式为“*A;*A;*A”。
技巧6: Excel中的隐身术
方法1:将单元格字体颜色设置成与底色一样的颜色
方法2:
【格式】→〖单元格〗→“数字”选项卡,
在“分类”列表中，单击“自定义”,
在“类型”框中键入“;;;”（三个分号）（此格式隐藏单元格所有的数值或文本）；格式“;;”（两个分号）隐藏数值而不隐藏文本。格式“##;;;”只显示正数；格式“;;0;”只显示零值。格式“""”隐藏正数和零值，负数显示为-，文字不会隐藏；格式“???”仅隐藏零值,而不隐藏非零值和文本。（格式“???”有四舍五入显示的功能，故格式“???”不仅隐藏0值，连小于0.5的值都隐藏了，同理,它也将0.6显示为1，将1.8显示为2。建议用：工具>选项>视图>窗口选项，零值前的对勾去掉，但此方法的缺点是整个表不显示零值，而不是某区域不显示零值。）
技巧7：Excel中的变形术
1、在输入数字前加一个单引号’，数字就“变形”为文本了。输入的单引号不会显示出来，也不计入字符串的长度（你可用len函数试试）。如果要再次将该文本格式的数字转换为数字格式，请“使用选择性粘贴→乘”的功能（请参见“选择性粘贴→粘贴时运算”部分）。
2、将数字直接以百元为单位显示，自定义格式为：“#!.00"百元"”或“0".00""百元"”；直接以千元为单位显示，自定义格式为：“#,"千元"”或“0.00,"千元"”；直接以万元为单位显示，自定义格式为：“#!.0,"万元"
”或“#!.0000"万元"”；将数字直接以百万元显示，自定义格式为“#,,"百万元"”或“0.00,,"百万元"”。财务人员、统计人员一定要掌握这几个自定义格式，很有用哦。