让格式自动添加固定的数字.doc

让格式自动添加固定的数字一、让格式自动添加固定的数字 例子： 大家知道，身份证号码的前6位，是省、市、区（县）的代号（如“340505”等），如果持证人是同一区（县）的，输入这样的的身份证号码，可以让“数字”标签帮你减轻输入的工作量：选中需要输入身份证号码的单元格（区域），打开“单元格格式”对话框，进入“数字”标签，在“分类”下面选中“自定义”选项，然后在右侧“类型”下面的方框中输入：340505，确定退出。以后输入身份证号码7位及以后的数字（如“198903040611”）后，系统会自动在前面加上相应的代码，形成完整的身份证号码（如）。 二.教你使用Excel数组公式 Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。 输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按CtrlShiftEnter组合键锁定数组公式，Excel将在公式两边自动加上花括号“”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。 选取数组公式所占有的全部区域先选中区域中任意一个单元格，然后按下Ctrl/键即可。 编辑或删除数组公式编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消失，然后编辑公式，最后按CtrlShiftEnter键。选取数组公式所占有的全部区域后，按Delete键即可删除数组公式。 数组常量的使用数组公式中还可使用数组常量，但必须自己键入花括号“”将数组常量括起来，并且用“，”和“；”分离元素。其中“，”分离不同列的值，“；”分离不同行的值。 使用不在工作表中的数组有时，公式仅占用一个单元格时也需要以数组的方式输入。其具体原则是：一个公式使用了数组，并且这个数组不在工作表上，就应该使用数组的方式输入这个公式。 下面介绍两个使用数组公式的例子。 1.如需分别计算各商品的销售额，可利用数组公式来实现。 单元格F2中的公式为：=SUM(IF(A2:A11=商品1，B2:B11C2:C11,0)。这个数组公式创建了一个条件求和，若在A2:A11中出现值“商品1”，则数组公式将B2:B11和C2:C11中与其相对应的值相乘并累加，若是其他值则加零。同时，虽然数组B2:B11和C2:C11均在工作表中，但其相乘的数组B2:B11C2:C11不在工作表中，因此必须使用数组公式。 2.假设要将A1:A50区域中的所有数值舍入到2位小数位，然后对舍入的数值求和。很自然地就会想到使用公式：=ROUND(A1,2)ROUND(A2,2)ROUND(A50,2)。 有没有更简捷的算法呢？有。因为数组ROUND(A1:A50,2)并不在工作表中，因此要使用数组的方式输入公式，即：=SUM(ROUND(A1:A50,2)。 三、自动更正 1.从工具菜单中选择“自动更正”，打开自动更正对话框。 2.在“替换”框中输入数据， 如：“4z”，在“替换为”框中输入数据，如：“枣阳市第四中学”，单击“添加”，再单击 “确定”。 之后，只需在单元格中输入“4z”两字，即会自动更正为“枣阳市第四中学”。 四、在excel工作表中输入计算公式时，如果想让储存格在显示计算结果和计算公式本身之间切换，您会采取什么方式呢？ 是不是每次都从“工具”“选项”“视图”“窗口选项”“公式”来切换呢？有没有更简便的方法呢？ 其实，我们可以利用“ Ctrl+ ”键来决定显示或隐藏公式，每按一次就可让储存格在显示计算的结果和计算公式本身之间自由切换 。 五、快速录入文本文件中的内容 现在您手边假如有一些以纯文本格式储存的文件，如果此时您需要将这些数据制作成Excel XP的工作表，那该怎么办呢？重新输入一遍，太浪费时间；将菜单上的数据一个个复制到工作表中，同样也费事。这时！您只要在Excel XP中巧妙使用其中的文本文件导入功能，就可以大大减轻需要重新输入或者需要不断复制、粘贴的巨大工作量了。使用该功能时，您只要在Excel XP编辑区中，选定菜单栏中的数据/获取外部数据/导入文本文件命令，然后在导入文本会话窗口选择要导入的文本文件，按下导入钮以后，程序会弹出一个文本导入向导对话框，您只要按照向导的提示进行操作，就可以把以文本格式的数据转换成工作表的格式了。 六、 行列转置 假如A1-A10单元格中有不同的内容，现在需要将这些内容分别排列在A1、B1、C1、D1.J1单元格中，即将原来在同一列中的内容重新排列在一行中。难道要一个一个单元格去复制粘贴吗？完全不用。 首先，选中A1-A10单元格，单击右键，在弹出的菜单中选择“复制”命令；接着单击B1单元格，再单击右键，在弹出的菜单中选择“选择性粘贴”命令，弹出对话框，选择其中的复选项“转置”，点击确定。这时你会看到B1、C1.K1单元格中已有了A1-A10单元格中的内容，将A列选中，在右键菜单中删除，此时行列转换工作就完成了。 七、在Excel2000中设计彩色数字 在你使用Excel2000对一张工作表中的数据进行处理时，有时是不是希望其中某些数据能够有自己特殊的显示格式？比如，现在你有如图的一张Excel工作表，在“总成绩”一栏中，你需要小于120的显示为蓝色，大于120的显示为红色。下述的方法，可以让你的愿望变为现实。该方法是： 1选中D25的单元格区域。 2单击“格式”菜单中的“单元格”命令，然后单击“数字”选项卡。 3单击“分类”列表中的“自定义”选项，在“类型”框中键入“蓝色120；红色120”。 4单击“确定”按钮。 怎么样，Excel按你的要求显示出不同颜色的数字了吧？ 最后，对在“类型”框中键入的数字格式代码进行简单地说明，“蓝色”是对数据显示颜色的设置，除“红色”、“蓝色”外，还可使用六种颜色，它们是黑色、青色、绿色、洋红、白色、黄色。“120”是条件设置，可用的条件运算符有：、。当有多个条件设置时，各条件设置以分号“；”间隔。 八、减少修改EXCEL参数的秘籍 在此处设计好的公式，拿到彼处用时，大部分不会完全合适，往往要修改公式中的参数特别是函数中引用的单元格区域，往往需要修改所以这里就以单元格区域为例，介绍一种减少或者是避免参数修改的办法虽然说不上万能，但将公式任意粘贴而基本不用修改是确实能办到的 一个单元格、或者由许多单元格组成的单元格集合都可以作为一个区域，并可以命名一个名字。这许多朋友都知道。但对合理恰当地使用命名区域带来的方便，许多朋友或者是不重视，或者是觉得麻烦而较少使用。不过要实现上面的目标，我建议大家还是尽可能使用命名的区域。其好处如下： 、直观易记，区域很多时，形式如（B2:M50）的名字既不好记忆，也难以一下子断定意义，反之，使用区域名字，特别是用中文作为区域名字时，能见文知义，大大减轻了记忆负担。例如，将区域(B2:M50)命名为“三甲物理分”,一见名字就明白意思 、如果某一区域需要在多个公式中使用，采用区域命名会很方便。例如函数中需要引用“三甲物理分”这个区域时，可以直接拿它当参数如果想得到那种一次设计，多次重用的“计算模板”，则更是非将区域命名不可。 、如果区域的范围需要扩大或缩小，只需要将原来的名字重新命名一下，而原来使用的函数参数不必改动。例如，要统计上面区域中有效数字的个数，用的公式是：=COUNT(三甲物理分)（注意函数括号内的区域名字不用加双引号）；当需要把该区域扩大或缩小时，可以先将原有的区域名字“三甲物理分”删除，然后重新选定合适的范围再次用这个名字命名，而所有以“三甲物理分”作参数的函数均不须修改而仍能使用。 乍一看这好像就不错了，其实，这还不算完善 前已述及，当函数移到别的地方使用时，其参数一般需要修改。比如要用上面的公式计算二乙班物理成绩的有效数字个数时（这里假设你已经将相应区域命名为“二乙物理分”），则函数中用到区域参数又都要修改为“二乙物理分”。可以推知，即使是只修改这一处参数，在处理很多学科时（因为每个班级可能进行多个学科的考试），修改参数的工作量也很大，况且实际计算中往往同时使用多个公式，更导致参数修改的工作量成几倍增加。以某中学三个年级24个教学班为例，假如每个年级考试5个学科，要求计算出每班各个学科的最高分、平均分、整个年级的前百强学生等项目这些活儿就是让你用上? 命名的法宝，手工修改参数也会磨坏你的手指！ 怎样才能减少修改的工作量、甚至做到不修改参数就能用呢？这就得需要在函数内间接引用参数。请往下看 A B C 1 三甲物理分 三乙物理分 三甲语文分 2 AVERAGE (三甲物理分) =AVERAGE (三甲物理分) =AVERAGE (三甲物理分) 3 AVERAGE (INDIRECT(A1) AVERAGE (INDIRECT(B1) AVERAGE (INDIRECT(C1) 假设先设计好EXCEL工作表，将准备处理的数据区域都进行命名，并把名字依次输入到表中第一行的各个单元格，类似下表那样为了对两种方法进行比较，我们先在A2单元格中输入公式：AVERAGE (三甲物理分),可得出三甲班的物理平均分，结果也正确，缺点就是像上面说的，将公式粘贴在B2、C2单元格中时，括号中的参数并不会自动取出第一行里对应的名字，还需要用手工一一修改！如何让它能自动引用正确的参数、即能自动引用第一行对应单元格里的文字呢？可以使用函数INDIRECT( ) 在EXCEL中，函数INDIRECT( )的作用比较特殊其作用介绍如下：例如已知存放在A1单元格中的文字三甲物理分是一个区域名字，当用INDIRECT(A1)函数时，可以通过A1单元格获得对三甲物理分区域的引用好！下面我们验证一下：请你在上表中的A3单元格中输入：AVERAGE (INDIRECT(A1) ,从得出的结果看，与A2单元格中使用的公式结果相同从公式的组成看，多了函数INDIRECT(A1)，省去了区域名字！注意，这就是激动人心的关键点了：当你用鼠标拖拽的办法向右边单元格粘贴这个公式后，会发现这些单元格中的公式已经自动地引用了相应区域的名字，你连一个参数也不用修改就得出了正确结果！？这不正是实现了我们苦苦追求的目标吗？怎么样，很爽吧？ 感谢你有耐心看到这里，下面我再送你一个秘密武器 上面介绍的那个有功劳的函数是INDIRECT( )，它是解决名字引用的，当要引用的单元格中是数值时，它就不管用了能引用单元格数值的是另一个函数VALUE()。它的用法是：如果在A2单元格中存放有数字85，那么在A3单元格输入：VALUE(A2),在A3中也能得到85这个数字。要只是这样的代换显然意义不大，但把它用在公式中后意义就大不一样了。看下表：第二行中的数字是该学科达到“优秀”的分 A B C 三乙物理分 三乙物理分 三甲语文分 85 87 80 =COUNTIF(INDIRECT(A1,),$#62;=&VALUE(A2) =COUNTIF(IN A DIRECT(B1,),$#62;=&VALUE(B2) =COUNTIF(INDIRECT(C1,),$#62;=&VALUE(C2) 数线，我们的目的是：在第三行中根据该学科的分数线求出该学科达到优秀成绩的人数由于不同学科划定优秀的分数线一般也不同要是直接了当的话，在A3中使用公式COUNTIF(INDIRECT(A1,),$#62;=85)就可以，但这样用到B列又会遇到类似上面的修改参数问题。要也像上面那样能自动引用第二行中的分数线该多好啊！别急， 把VALUE()函数用上就成了！我们把A3中的公式改成：=COUNTIF(INDIRECT(A1,),$#62;=&VALUE(A2)请你把这个公式向右面粘贴，可以发现，不用修改任何参数就能自动地引用正确的单元格了 如果你能够熟练应用INDIRECT( )和VALUE()这两个函数，再加上区域命名的法宝，会给你带来不小的方便！ 至于制作能多次使用的计算模板，我采用的是很懒也很容易的方法：将上一次用过的EXCEL文件用另存为作个备份，然后打开，只将原始数据清去，其他全部保留，就成了一个模板这样，只需要输入原始数据，立即会得到结果，免去了输入公式，设计格式等一大堆麻烦 九、技巧1：给别人看，但不给别人动 辛辛苦苦地设计了一个数据库或者表格，却被别人毛手毛脚地搞乱了，是不是很恼火？如果 是私人文件还好，只要设个密码就可以了，偏偏这是要别人往里面填数据或者是要给大家参考的，往往由于他人的“不小心”，而导致自己要重做整个表格，这种事情不知道大家遇到没有，我可是遇到过，辛苦加班加点用了7天才设计好的表格，因为没做好防护措施，致使.哎,伤心往事,不说也罢! 现在,我教大家一个好办法,让别人只能看,不能动! 为了防止别人无意改动表格的内容(尤其是一些计算公式),我们必须把这些单元格设成“只读”! 把不需要保护的的单元格选定，单击右键,设置单元格,将保护标签中的锁定复选框清除，然后对该工作表设置保护。此时，锁定的单元格是只读的，刚才已被清除锁定复选框的单元则可以正常输入。当然,如果你连某些单元的内容也不想别人看到,在确定这些单元保护标签中的锁定复 选框是选中的情况下,再选择隐藏即可,这样将不显示这些单元其中的内容。但是你必须对工作表或工作簿实施保护后,你的保护和隐藏才有效。要保护工作表,可按以下选择: 工具保护保护工作表选择密码，重复输入相同密码，这样，以后要进入这个工作表，只要输入密码即可 。 技巧2：得到偶数的单元格的相加值 因为工作需要，要得到偶数的单元格的值相加之和，以下是具体的要求和思路： 要求：得到A2+A4+A6+A8+A10+A12+A14.之和，假如是少还好办，假如有数十个相加的话就不好办了，怎样才能用一个公式来搞定它呢？ 思路：呵呵，我已得到答案了！先用一列来得到当前行的行号，你可在C1输入1，然后向下拖得到每一行的行号,再用一列来判断当前行是否为偶数行，输入公式：=IF（INT（C1/2）=C1/2，A1，0），然后再累加C列即可。 函数解析：其中的INT函数为判断C1/2是否为整数,如果C1是偶数C1/2当然是整数了,IF函数判断INT(C1/2)是否为偶数,如是,则取A列值,如果不是,就取0值。 看懂以上公式后，再教你一个更简单的方法：用ROW函数ROW（）可以得到当前行的行号，这样，判断当前行是否为偶数就不用再增加C列了，只要直接输入公式：=IF（INT（ROW（? ）=ROW（）/2，A1，0），向下拖动，累加即可。 技巧3：如何使Excel中数字计算自动四舍五入 目的：要对一个单元格中的数据（利用函数计算得出的）