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

如何设置自定义数字格式代码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”：按四位（19009999）或两位（0099）显示年。“MM”或“M”：以两位（0112）或（112）显示月“DD”或“D”：以两位（0131）或一位（131）来显示天“H”或“HH”：以一位（023）或两位（0123）来显示小时“M”或“MM”：以一位（059）或（0159）显示分钟“S”或“SS”： 以一位（059）或（0159）显示秒。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不能表示。解决方法：修改公式，使其结果在有效数字范围之间。2.2.8 #NULL!当试图为两个并不相交的区域指定交叉点时将产生错误值#NULL!。原因：使用了不正确的区域运算符或不正确的单元格引用。解决方法：如果要引用两个不相交的区域，请使用联合运算符逗号(，)。公式要对两个区域求和，请确认在引用这两个区域时，使用逗号。如：SUM(A1:A13，D12:D23)。如果没有使用逗号，Excel将试图对同时属于两个区域的单元格求和，但是由于A1:A13和D12:D23并不相交，所以他们没有共同的单元格。提高输入数据的准确性为了提高输入数据的准确性，其基本的功能要求是：输入操作简单、轻松；输入效率高，即具有重复内容自动复制和简易代码输入替代功能；输入格式美观大方；具有醒目的提示等。然而，仅有这些功能要求是不够的，它们不能从根本上提高输入数据的准确性。因为，大量的原始数据的输入是件繁琐而又单调的工作，难免出错。所以，必须要有更严格、更有效的科学方法和手段来提高输入数据的准确性。本文给出了提高输入数据准确性的方法，供数据库管理系统的开发者参考与借鉴。一、输入数据的校验与查错方法通常情况下输入数据的校验与查错方法有两种，其中第一种是边输入边校验法。在这种方法中，假若输入数据有错，则要求数据录入者立即更正错误。这种方法常常用于所输入的数据具有某种规律和特征，若数据录入者键入的数据违背了这个规律和特征，即立即给出输入出错警告，并强制性要求数据录入者对当前输入的数据给予修正。例如，在财务管理系统中，一张“记帐凭证”一般有借方金额和贷方金额两栏数字。会计制度要求同一张凭证中借方金额合计和贷方金额合计必须相等。根据这一特征，所以在开发“记帐凭证”数据输入程序时，程序应能自动判断，在一张“记帐凭证”的数据输入结束后，借方金额合计与贷方金额合计是否相等，若不相等，应强制要求数据输入者立即重新输入。又例如，在每年的高考中，考生的成绩数据有一部分要通过人工阅卷评分后，然后由专人输入计算机。对于考生成绩数据，它所具有的特征是：每题的最高分和最低分(零分)是确定的，并且均为数字字符。根据这个特征，所以在开发的考生成绩数据管理系统的输入模块中，应具有如下功能，即在每题数据输入结束后，自动判断输入的分数值是否符合上述规律，若不符合，则应立即发出警告，并强制要求录入者重新输入。除此以外，第二种数据校验与查错的方法是双工输入比较法。所谓双工输入比较法，就是将同一批数据由两个输入人员在不同的时间和不同的终端上分别录入，并且形成两个临时数据库文件，然后由第三个人在程序的作用下对两个库文件中的数据进行逐项比较并进行确认或修改。在这种方法中，尽管同一批数据被录入了两次从而造成了数据冗余和影响了录入进度，但对于被录入的数据不存在明显的规律和上述第一种方法不能查出输入出错的场合，以及对输入数据的准确性要求很高的场合，是一种不可缺少的和行之有效的方法，因为，两个数据录入者都同时在某处出错的机会极少，故这种方法可以极大地减少出错率。根据概率论原理，如果两个数据录入者各自的出错率为百分之一，则双工输入法的出错率仅为万分之一。双工输入比较法在FoxPro环境下的基本算法是：将同一批数据由两个录入者在不同时间和不同的终端上录入，并存入两个不同名的库文件中。输入“”中产生的两个库文件名。在两个工作区中分别打开一个库文件，并按记录号使两个库文件建立关联。对两个库文件中的对应记录进行逐项比较，若不相等，则进行登记，包括位置的登记。若记录未结束，则记录指针加1，转。打印输出所有不相等的数据项及其位置。确认和修改。在上述算法“”中