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

如何设置自定义数字格式代码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。

如果公式所在单元格的位置改变，绝对引用的单元格始终保持不变。

如果多行或多列地复制公式，绝对引用将不作调整。

默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。

例如，如果将单元格B2 中的绝对引用复制到单元格B3，则在两个单元格中一样，都是$F$6。

相对引用公式中的相对单元格引用（例如A1）是基于包含公式和单元格引用的单元格的相对位置。

如果公式所在单元格的位置改变，引用也随之改变。

如果多行或多列地复制公式，引用会自动调整。

默认情况下，新公式使用相对引用。

例如，如果将单元格B2 中的相对引用复制到单元格B3，将自动从=A1 调整到=A2。

将公式从一个单元格复制到另一时，相对和绝对之间的差异就显得重要。

默认使用相对引用.绝对引用是你选的单元格，不会变的。

相对引用则是单元格的相对位置。

比如是左边第二格，就一直是左边二格。

输入公式和函数后可能出现的错误有哪几种？分别是由于什么原因造成的？应如何解决？2.2.1 #####!原因：导致这种错误的情况一有两种情况：1 如果单元格所含的数字、日期或时间比单元格宽，就会产生#####!错误。

2 单元格的日期时间公式产生了一个负值，就会产生#####!错误。

解决方法：如果单元格所含的数字、日期或时间比单元格宽，可以拖动单元格列表的宽度来实现。

如果使用的是1900年的日期系统，那么Excel中的日期和时间必须为正值，用较早的日期或者时间值减去较晚的日期或者时间值就会导致#####!错误。

如果公式正确，也可以将单元格的格式改为非日期和时间型来显示该值。

2.2.2#VALUE!当使用错误的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生错误值#VALUE!。

原因一：在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。

解决方法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。

例如：如果单元格A1包含一个数字，单元格A2包含文本"学籍"，则公式"=A1+A2"将返回错误值#VALUE!。

可以用SUM工作表函数将这两个值相加(SUM函数忽略文本)：=SUM(A1:A2)。

原因二：将单元格引用、公式或函数作为数组常量输入。

解决方法：确认数组常量不是单元格引用、公式或函数。

原因三：赋予需要单一数值的运算符或函数一个数值区域。

解决方法：将数值区域改为单一数值。

修改数值区域，使其包含公式所在的数据行或列。

2.2.3 #DIV/O!当公式被零除时，将会产生错误值#DIV/O!。

原因一：在公式中，除数使用了指向空单元格或包含零值单元格的单元格引用(在Excel中如果运算对象是空白单元格，Excel将此空值当作零值)。

解决方法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。

原因二：输入的公式中包含明显的除数零，例如：=5/0。

解决方法：将零改为非零值。

2.2.4 #NAME?在公式中使用了Excel不能识别的文本时将产生错误值#NAME?。

原因一：删除了公式中使用的名称，或者使用了不存在的名称。

解决方法：确认使用的名称确实存在。

选择菜单"插入"&line;"名称"&line;"定义"命令，如果所需名称没有被列出，请使用"定义"命令添加相应的名称。

原因二：名称的拼写错误。

解决方法：修改拼写错误的名称。

原因三：在公式中使用标志。

解决方法：选择菜单中"工具"&line;"选项"命令，打开"选项"对话框，然后单击"重新计算"标签，在"工作薄选项"下，选中"接受公式标志"复选框。

原因四：在公式中输入文本时没有使用双引号。

解决方法：Excel将其解释为名称，而不理会用户准备将其用作文本的想法，将公式中的文本括在双引号中。

例如：下面的公式将一段文本"总计："和单元格B50中的数值合并在一起：="总计："&B50原因五：在区域的引用中缺少冒号。

解决方法：确认公式中，使用的所有区域引用都使用冒号。

例如：SUM(A2:B34)。

2.2.5 #N/A原因：当在函数或公式中没有可用数值时，将产生错误值#N/A。

解决方法：如果工作表中某些单元格暂时没有数值，请在这些单元格中输入"#N/A"，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。

2.2.6 #REF!当单元格引用无效时将产生错误值#REF!。

原因：删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。

解决方法：更改公式或者在删除或粘贴单元格之后，立即单击"撤消"按钮，以恢复工作表中的单元格。

2.2.7 #NUM!当公式或函数中某个数字有问题时将产生错误值#NUM!。

原因一：在需要数字参数的函数中使用了不能接受的参数。

解决方法：确认函数中使用的参数类型正确无误。

原因二：使用了迭代计算的工作表函数，例如：IRR或RATE，并且函数不能产生有效的结果。

解决方法：为工作表函数使用不同的初始值。

原因三：由公式产生的数字太大或太小，Excel不能表示。