电子表格知识大全.doc

电子表格知识大全摘自：原创 作者：邹凯华EXCEL取单元格颜色、字体、格式等等函数2007-02-28 13:50函数定义: GET.CELL(类型号,单元格(或范围)比如现在我们要取得A1单元格里的数据格式?结果显示在B1步骤如下: 1.任选一个单元格-插入-名称-定义 2.在名称称输入AA(任意名称) 3.在引用位置上写入=GET.CELL(7, Sheet1!$A$1) 4.点添加 5.在B1单元格里输入=AA 看到了,显示的结果是什么?如果没修改过的话应该是G/通用格式吧! 以下是类型号及代表的意思1 参照储存格的绝对地址2 参照储存格的列号3 参照储存格的栏号4 类似 TYPE 函数5 参照地址的内容6 文字显示参照位址的公式7 参照位址的格式，文字显示8 文字显示参照位址的格式9 传回储存格外框左方样式，数字显示10 传回储存格外框右方样式，数字显示11 传回储存格外框方上样式，数字显示12 传回储存格外框方下样式，数字显示13 传回内部图样，数字显示14 如果储存格被设定 locked传回 True15 如果公式处于隐藏状态传回 True16 传回储存格宽度17 以点为单位传回储存格高度18 字型名称19 以点为单位元传回字号20 如果储存格所有或第一个字符为加粗传回 True21 如果储存格所有或第一个字符为斜体传回 True22 如果储存格所有或第一个字符为单底线传回True23 如果储存格所有或第一个字符字型中间加了一条水平线传回 True24 传回储存格第一个字符色彩数字， 1 至 56。如果设定为自动，传回 025 MS Excel不支持大纲格式26 MS Excel不支持阴影格式27 数字显示手动插入的分页线设定28 大纲的列层次29 大纲的栏层次30 如果范围为大纲的摘要列则为 True31 如果范围为大纲的摘要栏则为 True32 显示活页簿和工作表名称33 如果储存格格式为多行文字则为 True34 传回储存格外框左方色彩，数字显示。如果设定为自动，传回 035 传回储存格外框右方色彩，数字显示。如果设定为自动，传回 036 传回储存格外框上方色彩，数字显示。如果设定为自动，传回 037 传回储存格外框下方色彩，数字显示。如果设定为自动，传回 038 传回储存格前景阴影色彩，数字显示。如果设定为自动，传回 039 传回储存格背影阴影色彩，数字显示。如果设定为自动，传回 040 文字显示储存格样式41 传回参照地址的原始公式42 以点为单位传回使用中窗口左方至储存格左方水平距离43 以点为单位传回使用中窗口上方至储存格上方垂直距离44 以点为单位传回使用中窗口左方至储存格右方水平距离45 以点为单位传回使用中窗口上方至储存格下方垂直距离46 如果储存格有插入批注传回 True47 如果储存格有插入声音提示传回 True48 如果储存格有插入公式传回 True49 如果储存格是数组公式的范围传回 True50 传回储存格垂直对齐，数字显示51 传回储存格垂直方向，数字显示52 传回储存格前缀字符53 文字显示传回储存格显示内容54 传回储存格数据透视表名称55 传回储存格在数据透视表的位置56 枢纽分析57 如果储存格所有或第一个字符为上标传回True58 文字显示传回储存格所有或第一个字符字型样式59 传回储存格底线样式，数字显示60 如果储存格所有或第一个字符为下标传回True61 枢纽分析62 显示活页簿和工作表名称63 传回储存格的填满色彩64 传回图样前景色彩65 枢纽分析66 显示活页簿名称excel颜色函数问题，高手领分！悬赏分：100 - 解决时间：2007-5-21 17:12举例：A1为数据0、1、2、3、4、5、6、7、8、9 B1为随意数据 我想让当A1为0或5时C1添0，当A1为其他数值时 C1=B1+1 刚才解决的问题！ C1=IF(OR(A1=0，A1=5),0,SUM(B1+1) - 继续延伸我想让A1为0或5时C1添0，字体颜色自动变为白色，所在C1格自动填充为红色，当A1为其他数值时 C1=B1+1 这个EXCEL可以办到吗？ 如用函数做不好可以调用别的方法，或者用宏做吗？问题补充：谢谢！lb2000 但是你说的也不对！选中C1单元格，点“格式”，“条件格式”，第一个选“单元格值”，后面选“等于”后面填 =IF（OR(A1=5,A1=0)，“0”）这样才可以不过谢谢你的提醒！多对最佳答案的评论 共 2 条还有另一种也可以，就是选等于，填0,格式选字白图案红，再添加一个条件，填 5，格式同上。评论者： lb2000 - 高级魔法师 六级 我这里这两种方法都可以。我用的是OFFICE 2003，不知道是不是版本的问题？评论者： lb2000 - 高级魔法师 六级 其他回答 共 1 条选中C1“格式”，“条件格式”条件1(1)“单元格数值”右边选“等于”右边输入0后点“格式”设置字体颜色为白色后确定，点“添加”条件2(2)“单元格数值”右边选“不等于”右边输入0后点“格式”设置填充色为红色后确定两次，有问题发消息吧Excel使用技巧蔡琳收集在工作当中用电子表格来处理数据将会更加迅速、方便，而在各种电子表格处理软件中，Excel以其功能强大、操作方便著称，赢得了广大用户的青睐。虽然Excel使用很简单，不过真正能用好Excel的用户并不多，很多人一直停留在录入数据的水平，本文将向你介绍一些非常使用的技巧，掌握这些技巧将大大提高你的工作效率。快速定义工作簿格式首先选定需要定义格式的工作簿范围，单击“格式”菜单的“样式”命令，打开“样式”对话框；然后从“样式名”列表框中选择合适的“样式”种类，从“样式包括”列表框中选择是否使用该种样式的数字、字体、对齐、边框、图案、保护等格式内容；单击“确定”按钮，关闭“样式”对话框，Excel工作簿的格式就会按照用户指定的样式发生变化，从而满足了用户快速、大批定义格式的要求。&返回&快速复制公式复制是将公式应用于其它单元格的操作，最常用的有以下几种方法：一是拖动制复制。操作方法是：选中存放公式的单元格，移动空心十字光标至单元格右下角。待光标变成小实心十字时，按住鼠标左键沿列（对行计算时）或行（对列计算时）拖动，至数据结尾完成公式的复制和计算。公式复制的快慢可由小实心十字光标距虚框的远近来调节：小实心十字光标距虚框越远，复制越快；反之，复制越慢。也可以输入复制。此法是在公式输入结束后立即完成公式的复制。操作方法：选中需要使用该公式的所有单元格，用上面介绍的方法输入公式，完成后按住Ctrl键并按回车键，该公式就被复制到已选中的所有单元格。还可以选择性粘贴。操作方法是：选中存放公式的单元格，单击Excel工具栏中的“复制”按钮。然后选中需要使用该公式的单元格，在选中区域内单击鼠标右键，选择快捷选单中的“选择性粘贴”命令。打开“选择性粘贴”对话框后选中“粘贴”命令，单击“确定”，公式就被复制到已选中的单元格。&返回&快速显示单元格中的公式如果工作表中的数据多数是由公式生成的，如果想要快速知道每个单元格中的公式形式，可以这样做：用鼠标左键单击“工具”菜单，选取“选项”命令，出现“选项”对话框，单击“视图”选项卡，接着设置“窗口选项”栏下的“公式”项有效，单击“确定”按钮。这时每个单元格中的公式就显示出来了。如果想恢复公式计算结果的显示，再设置“窗口选项”栏下的“公式”项失效即可。&返回&快速删除空行有时为了删除Excel工作簿中的空行，你可能会将空行一一找出然后删除，这样做非常不方便。你可以利用“自动筛选”功能来简单实现。先在表中插入新的一行（全空），然后选择表中所有的行，选择“数据”菜单中的“筛选”，再选择“自动筛选”命令。在每一列的项部，从下拉列表中选择“空白”。在所有数据都被选中的情况下，选择“编辑”菜单中的“删除行”，然后按“确定”即可。所有的空行将被删去。插入一个空行是为了避免删除第一行数据。&返回&自动切换输入法当你使用Excel 2000编辑文件时，在一张工作表中通常是既有汉字，又有字母和数字，于是对于不同的单元格，需要不断地切换中英文输入方式，这不仅降低了编辑效率，而且让人不胜其烦。在此，笔者介绍一种方法，让你在Excel 2000中对不同类型的单元格，实现输入法的自动切换。新建或打开需要输入汉字的单元格区域，单击“数据”菜单中的“有效性”，再选择“输入法模式”选项卡，在“模式”下拉列表框中选择“打开”，单击“确定”按钮。选择需要输入字母或数字的单元格区域，单击“数据”菜单中的“有效性”，再选择“输入法模式”选项卡，在“模式”下拉列表框中选择“关闭（英文模式）”，单击“确定”按钮。之后，当插入点处于不同的单元格时，Excel 2000能够根据我们进行的设置，自动在中英文输入法间进行切换。就是说，当插入点处于刚才我们设置为输入汉字的单元格时，系统自动切换到中文输入状态，当插入点处于刚才我们设置为输入数字或字母单元格时，系统又能自动关闭中文输入法。&返回&自动调整小数点如果你有一大批小于1的数字要录入到Excel工作表中，如果录入前先进行下面的设置，将会使你的输入速度成倍提高。单击“工具”菜单中的“选项”，然后单击“编辑”选项卡，选中“自动设置小数点”复选框，在“位数”微调编辑框中键入需要显示在小数点右面的位数。在此，我们键入“2”单击“确定”按钮。完成之后，如果在工作表的某单元格中键入“4”，则在你按了回车键之后，该单元格的数字自动变为“0.04”。方便多了吧！此时如果你在单元格中键入的是“8888”，则在你结束输入之后，该单元格的数字自动变为“88.88”。&返回&用“记忆式输入”有时我们需要在一个工作表中的某一列输入相同数值，这时如果采用“记忆式输入”会帮你很大的忙。如在职称统计表中要多次输入“助理工程师”，当第一次输入后，第二次又要输入这些文字时，只需要编辑框中输入“助”字，Excel2000会用“助”字与这一列所有的内容相匹配，若“助”字与该列已有的录入项相符，则Excel2000会将剩下的“助理工程师”四字自动填入。按下列方法设置“记忆式输入”：选择“工具”中的“选项”命令，然后选择“选项”对话框中的“编辑”选项卡，选中其中的“记忆式键入”即可。&返回&用“自动更正”方式实现快速输入使用该功能不仅可以更正输入中偶然的笔误，也可能把一段经常使用的文字定义为一条短语，当输入该条短语时，“自动更正”便会将它更换成所定义的文字。你也可以定义自己的“自动更正”项目：首先，选择“工具”中的“自动更正”命令；然后，在弹出的“自动更正”对话框中的“替换”框中键入短语“爱好者”，在“替换为”框中键入要替换的内容“电脑爱好者的读者”；最后，单击“确定”退出。以后只要输入“爱好者”，则整个名称就会输到表格中。&返回&用下拉列表快速输入数据如果你希望减少手工录入的工作量，可以用下拉表来实现。创建下拉列表方法为：首先，选中需要显示下拉列表的单元格或单元格区域；接着，选择菜单“数据”菜单中的“有效性”命令，从有效数据对话框中选择“序列”，单击“来源”栏右侧的小图标，将打开一个新的“有效数据”小对话框；接着，在该对话框中输入下拉列表中所需要的数据，项目和项目之间用逗号隔开，比如输入“工程师，助工工程师，技术员”，然后回车。注意在对话框中选择“提供下拉箭头”复选框；最后单击“确定”即可。&返回&10.两次选定单元格有时，我们需要在某个单元格内连续输入多个测试值，以查看引用此单元格的其他单元格的效果。但每次输入一个值后按Enter键，活动单元格均默认下移一个单元格，非常不便。此时，你肯定会通过选择“工具”“选项“编辑，取消“按Enter键移动活动单元格标识框选项的选定来实现在同一单元格内输入许多测试值，但以后你还得将此选项选定，显得比较麻烦。其实，采用两次选定单元格方法就显得灵活、方便：单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定此单元格（此时，单元格周围将出现实线框）。 &返回&11.“Shift拖放的妙用在拖放选定的一个或多个单元格至新的位置时，同时按住Shift键可以快速修改单元格内容的次序。具体方法为：选定单元格，按下Shift键，移动鼠标指针至单元格边缘，直至出现拖放指针箭头“?，然后进行拖放操作。上下拖拉时鼠标在单元格间边界处会变为一个水平“工状标志，左右拖拉时会变为垂直“工状标志，释放鼠标按钮完成操作后，单元格间的次序即发生了变化。这种简单的方法节省了几个剪切和粘贴或拖放操作，非常方便。&返回&12.超越工作表保护的诀窍如果你想使用一个保护了的工作表，但又不知道其口令，有办法吗？有。选定工作表，选择“编辑“复制、“粘贴，将其拷贝到一个新的工作簿中（注意：一定要新工作簿），即可超越工作表保护。&返回&13.巧用IF函数(1).设有一工作表，C1单元格的计算公式为：=A1B1，当A1、B1单元格没有输入数据时，C1单元格会出现“DIV0！”的错误信息。这不仅破坏了屏幕显示的美观，特别是在报表打印时出现“DIV0！”的信息更不是用户所希望的。此时，可用IF函数将C1单元格的计算公式更改为：=IF（B1=0，A1B1）。这样，只有当B1单元格的值是非零时，C1单元格的值才按A1B1进行计算更新，从而有效地避免了上述情况的出现。(2).设有C2单元格的计算公式为：=A2B2，当A2、B2没有输入数值时，C2出现的结果是“0”，同样，利用IF函数把C2单元格的计算公式更改如下：=IF（AND（A2=，B2=），A2B2）。这样，如果A2与B2单元格均没有输入数值时，C2单元格就不进行A2B2的计算更新，也就不会出现“0”值的提示。(3).设C3单元格存放学生成绩的数据，D3单元格根据C3（学员成绩）情况给出相应的“及格”、“不及格”的信息。可用IF条件函数实现D3单元格的自动填充，D3的计算公式为：=IF（C360，不及格，及格。&返回&14.累加小技巧我们在工作中常常需要在已有数值的单元格中再增加或减去另一个数。一般是在计算器中计算后再覆盖原有的数据。这样操作起来很不方便。这里有一个小技巧，可以有效地简化老式的工作过程。 (1).创建一个宏：选择Excel选单下的“工具宏录制新宏”选项；宏名为：MyMacro；快捷键为：CtrlShiftJ(只要不和Excel本身的快捷键重名就行)；保存在：个人宏工作簿(可以在所有Excel工作簿中使用)。(2).用鼠标选择“停止录入”工具栏中的方块，停止录入宏。(3).选择Excel选单下的“工具宏Visual Basic编辑器”选项。(4).在“Visual Basic编辑器”左上角的VBA Project中用鼠标双击VBAProject(Personal.xls)打开“模块Module1”。注意：你的模块可能不是Module1 ,也许是Module2、Module3。(5).在右侧的代码窗口中将Personal.xlsModule1(Code)中的代码更改为：Sub MyMacro( )OldValue = Val(ActiveCell.Value)InputValue = InputBox(“输入数值，负数前输入减号”,“小小计算器”)ActiveCell.Value = Val(OldValueInputValue)End Sub(6).关闭Visual Basic编辑器。编辑完毕，你可以试试刚刚编辑的宏，按下ShiftCtrlJ键，输入数值并按下“确定”键。(这段代码只提供了加减运算，借以抛砖引玉。)&返回&15.怎样保护表格中的数据假设要实现在合计项和小计项不能输入数据，由公式自动计算。首先，输入文字及数字，在合计项F4至F7单元格中依次输入公式：=SUM (B4E4)、=SUM(B5E5)、=SUM(B6E6)、=SUM(B7E7)，在小计项B8至F8单元格中依次输入公式：=SUM(B4B7)、=SUM(C4C7)、=SUM(D4D7)、=SUM(E4E7)、=SUM(F4F7)。在默认情况下，整个表格的单元格都是锁定的，但是，由于工作表没有被保护，因此锁定不起作用。选取单元格A1F8，点击“格式单元格”选单，选择“保护”选项，消除锁定复选框前的对勾，单击确定。然后，再选取单元格F4F7和B8F8，点击“格式单元格”选单，选择“保护”选项，使锁定复选框选中，单击确定，这样，就把这些单元格锁定了。接着，点击“工具保护保护工作表”选单，这时，会要求你输入密码，输入两次相同的密码后，点击确定，工作表就被保护起来了，单元格的锁定也就生效了。今后，可以放心地输入数据而不必担心破坏公式。如果要修改公式，则点击“工具保护撤消保护工作表”选单，这时，会要求你输入密码，输入正确的密码后，就可任意修改公式了。&返回&16如何避免Excel中的错误信息在Excel中输入或编辑公式后，有可能不能正确计算出结果，Excel将显示一个错误信息，引起错误的原因并不都是由公式本身有错误产生的。下面我们将介绍五种在Excel中常出现的错误信息，以及如何纠正这些错误。错误信息1输入到单元格中的数据太长或单元格公式所产生的结果太大，在单元格中显示不下时，将在单元格中显示。可以通过调整列标之间的边界来修改列的宽度。如果对日期和时间做减法，请确认格式是否正确。Excel中的日期和时间必须为正值。如果日期或时间产生了负值，将在整个单元格中显示。如果仍要显示这个数值，请单击“格式”菜单中的“单元格”命令，再单击“数字”选项卡，然后选定一个不是日期或时间的格式。错误信息2DIV/0!输入的公式中包含明显的除数0，例如120/0，则会产生错误信息DIV/0!。或在公式中除数使用了空单元格(当运算对象是空白单元格，Excel将此空值解释为零值)或包含零值单元格的单元格引用。解决办法是修改单元格引用，或者在用作除数的单元格中输入不为零的值。错误信息3VALUE!当使用不正确的参数或运算符时，或者当执行自动更正公式功能时不能更正公式，都将产生错误信息VALUE!。在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。这时应确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。例如，单元格B3中有一个数字，而单元格B4包含文本，则公式=B3B4将返回错误信息VALUE!。错误信息4NAME?在公式中使用了Excel所不能识别的文本时将产生错误信息NAME?。可以从以下几方面进行检查纠正错误：(1)如果是使用了不存在的名称而产生这类错误，应确认使用的名称确实存在。在“插入”菜单中指向“名称”，再单击“定义”命令，如果所需名称没有被列出，请使用“定义”命令添加相应的名称。(2)如果是名称，函数名拼写错误应修改拼写错误。(3)确认公式中使用的所有区域引用都使用了冒号(：)。例如：SUM(A1：C10)。注意将公式中的文本括在双引号中。错误信息5 NUM!当公式或函数中使用了不正确的数字时将产生错误信息NUM!。要解决问题首先要确认函数中使用的参数类型正确。还有一种可能是由公式产生的数字太大或太小，Excel不能表示，如果是这种情况就要修改公式，使其结果在110307和110307之间。&返回&17. 不用编程-Excel公式也能计算个人所得税个人所得税的计算看起来比较复杂，似乎不用VBA宏编程而只用公式来计算是一件不可能的事。其实，Excel提供的函数公式不但可以计算个人所得税，而且还有很大的灵活：可以随意改变不扣税基数，随意改变各扣税分段界限值及其扣税税率（说不定以后调整个人所得税时就可以用到。）不管是编程还是使用公式，都得将个人所得税的方法转化为数学公式，并且最好将这个公式化简，为以后工作减少困难。以X代表你的应缴税（减去免税基数）的工薪收入（这里的个人所得税仅以工薪为例），Tax代表应缴所得税，那么：当500X2000则TAX=（X-500）*10+500*5 =TAX=X*10-25当2000X5000则TAX=（X-2000）*15+2000*10 =TAX=X*15-125 依此类推，通用公式为：个人所得税=应缴税工薪收入*该范围税率-扣除数在此,扣除数=应缴税工薪收入上一范围上限*该范围税率-上一范围扣除数其实只有四个公式，即绿色背景处。黄色背景处则为计算时输入数据的地方。各处公式设置即说明如下：E3：=C3*D3-C3*D2+E2E4-E10：根据E3填充得到，或者拷贝E3粘贴得到C15：=IF(B15$B$12,B15-$B$12,0)如果所得工薪大于不扣税基数，则应纳税工薪为工薪减去为零不扣税基数，否则，应纳税工薪零。D15：=VLOOKUP(C15,$C$2:$C$10,1)查阅应纳税工薪属于哪个扣税范围。E15：=C15*VLOOKUP(D15,$C$2:$E$10,2)-VLOOKUP(D15,$C$2:$E$10,3)查阅该扣税范围扣税税率和应减的扣除数。这里主要用到VLOOKUP函数，可查阅帮助获取更多信息。C15，D15的公式可以合并到E15中，那样可读性会差很多，但表格会清晰一些。合并后公式：=IF(B15$B$12,B15-$B$12,0)*VLOOKUP(VLOOKUP(IF(B15$B$12,B15-$B$12,0),$C$2:$C$10,1),$C$2:$E$10,2)-VLOOKUP(VLOOKUP(IF(B15$B$12,B15-$B$12,0),$C$2:$C$10,1),$C$2:$E$10,3)实际上是将公式中出现的C15，D15用其公式替代即可。&返回&18. 用EXCEL轻松处理学生成绩期末考试结束后，主任要求班主任自已统计本班成绩，尽快上报教导处。流程包括录入各科成绩计算总分、平均分并排定名次统计各科分数段人数、及格率、优秀率及综合指数打印各种统计报表制作各科统计分析图表等。有了EXCEL，我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了！我迅速地打开电脑，启动EXCEL2000，录入学生的考试成绩，如图1所示。然后在J2单元格处输入公式=sum(c2:i2),然后拖动填充柄向下填充，便得到了每人的总分。接着在k2单元格处输入公式=average(c2:i2)，然后拖动填充柄向下填充，便得到了每人的平均分。 图1fh,y tgh gut95 q m6m y平均分只需保留一位小数，多了没用。所以选中第k列，用鼠标右键单击，从弹出的快捷菜单中选设置单元格格式（F）,如图2所示，在数字标签中选中数值，小数位数设置为1位。 图2下面按总分给学生排出名次。在L2单元格处输入公式RANK(J2,J$2:J$77,0),然后拖动填充柄向下填充，即可得到每人在班中的名次（请参考图1）。 说明：此处排名次用到了RANK函数，它的语法为：RANK(number,ref,order)其中number为需要找到排位的数字。Ref为包含一组数字的数组或引用。Ref 中的非数值型参数将被忽略。Order为一数字，指明排位的方式。 如果 order 为 0 或省略，Microsoft Excel 将 ref 当作按降序排列的数据清单进行排位。如果 order 不为零，Microsoft Excel 将 ref 当作按升序排列的数据清单进行排位。 最后，单击L1单元格，然后在“工具”菜单中选“排序”“升序”，即可按照名次顺序显示各学生成绩。 另外，我们还希望把不及格的学科突出显示，最好用红色显示。于是拖拉选择C2:E78(即所有学生语、数、外三科成绩)，然后执行格式菜单下条件格式命令，弹出条件格式对话框。我们把条件设为小于72分的用红色显示（因为这三科每科总分为120分），点击格式按钮，把颜色设为红色。再按确定按钮。然后用同样的方法把理、化、政、历四科小于60分的也用红色显示（因为这四科每科总分为100分）。下面我们来统计各科的分数段以及及格率、优生率、综合指数等。下面我们来统计各科的分数段以及及格率、优生率、综合指数等。（1）60分以下人数：在C78单元格处输入公式=COUNTIF(C2:C77,=60)-COUNTIF(C2:C77,=70)，拖动填充柄向右填充；（3）70分79分人数：在C80单元格处输入公式=COUNTIF(C2:C77,=70)-COUNTIF(C2:C77,=80)，拖动填充柄向右填充；（4）80分89分人数：在C81单元格处输入公式=COUNTIF(C2:C77,=80)-COUNTIF(C2:C77,=90)，拖动填充柄向右填充；（5）90分以上人数：在C82单元格处输入公式=COUNTIF(C2:C77,=90)，拖动填充柄向右填充；（6）平均分：在C83单元格处输入公式=AVERAGE(C2:C77),拖动填充柄向右填充至I83；（7）最高分：在C84单元格处输入公式=MAX(C2:C77),拖动填充柄向右填充至I84；（8）低分率：是指各科40分以下人数与总人数的比值。在C85单元格处输入公式=COUNTIF(C2:C77,=72)/COUNT(C2:C77)*100，并拖动填充柄向右填充至E86；而理、化、政、历等四科及格分60分，所以在F86单元格处输入公式=(COUNTIF(F2:F77,=60)/COUNT(F2:F77)*100，并拖动填充柄向右填充至I86；（10）优生率：语、数、外三科96分以上为优生，所以在C87单元格处输入公式=(COUNTIF(C2:C77,=96)/COUNT(C2:C77)*100，拖动填充柄向右填充至E87；理、化、政、历等四科80分以上为优生，所以在F87单元格处输入公式=(COUNTIF(F2:F77,=80)/COUNT(F2:F77)*100，拖动填充柄向右填充至I87处；如图3所示。（11）综合指数：我们学校的综合指数的计算公式为z=(1+优生率低分率)/2+及格率+平均分/该科总分/3。所以在C88单元格处输入公式=(1+C87/100-C85/100)/2+C86/100+C83/120)/3，拖动填充柄向右填充至E88；在F88单元格处输入公式=(1+F87/100-F85/100)/2+F86/100+F83/100)/3，拖动填充柄向右填充至I88。如图3所示。 图3对了，为了让别人对各科的分数段有一个较直观的认识，可以考虑采用图表。单击“插入”菜单中“图表”命令，弹出“图表向导”对话框，在“图表类型”列表框中选择一种图型，如“饼图”，单击“下一步”，单击“数据区域”文本框右边的压缩列表框，拖拉选择B78：C82，再次点击该压缩列表框；单击“下一步”，输入图表标题，如“高一（1）班语文成绩分析图”；单击“下一步”，再单击“完成”。如图4所示。其它各科同样处理，但在拖拉选择数据区域时，因为是不连续的区域，所以要按住“Ctrl”键。好！一切OK！且慢！为了以后的考试中不再重复上述繁琐的工作，最好把上述工作表另存为一个模板。于是我把上述工作表复制一份到另一工作簿中，然后删掉所有学生的单科成绩（即表中C2:I77部分），执行文件菜单中的另存为命令，在保存类型下拉列表框中选模板（*.xlt），把它保存为一个模板文件，这下可以一劳永逸了。&返回&19. 用EXCEL轻松准备考前工作大考在即，主任要求“考务工作必须电子化”，为了万无一失，还特意提供给班主任一份考务工作流程图：考前：考场编排打印单科成绩册打印考场记录单打印准考证号考后：录入各科成绩计算总分、平均分并排定名次统计各科分数段人数、及格率、优秀率及综合指数打印各种统计报表制作各科统计分析图表既然任务已经明确，先把考前的准备工作做好吧！具体工作包括：考场编排打印单科成绩册打印考场记录单打印准考证号1、单科成绩册的编制和准考证号的自动编制（1）我负责的班级这次考试共7门课程，为了区分科目，只需在EXCEL表头处设置“科目”、“班级”和“任课教师姓名”等。考生的姓名可以从学生学籍表中提取，考号如何编制和处理呢？为了以后登分和查找方便，考号基本按学籍号顺序把整个年级的考号放在一起编码。编码由两部分组成：考场编号（三位数字），班内顺序号（三位数字）。为了加快录入速度，分别把它们放在两列中，这样可以分别进行填充。（2）新建工作表“考场编号”（如图1），从学生学籍表中复制“姓名”列置于该表A列，首先设置B（准考证号）、C（班内编号）、D（考场编号）列的数据格式为文本方式。图1（3）在B2单元格输入公式“=D2&C2”（其中的“&”为连接运算，将“考场编号”和“班内编号”连接成一个字符串），用鼠标选中B2单元格，将鼠标指向该区域右下角的填充柄，双击鼠标（这可是本人总结出来的最快的填充方法，下称“双击填充法”）即可将该公式自动填充至最后一个考生（因为此时还没有在C、D列输入数据，暂时B列中数据也没有出现）。在C2单元格中输入“001”，C3单元格输入“002”，用鼠标拖动区域C2：C3，将鼠标指向该区域右下角的填充柄，用上述“双击填充大法”即可对全班学生顺序编号。然后每隔30人（一个标准考场）插入一空行，在D2和D3单元格中全部输入“001”，同样用“双击填充大法”迅速将第一考场的编号全部填充。此时B2：B31单元格已经全部自动填上了考生的准考证号。对第二考场，只需在D33和D34格中输入“002”，并向下填充即可。2、打印考场记录单考场记录单是供监考教师监考时核对考生数目和身份时用的。在上述“考场编号”工作表中，利用Excel的“分类汇总”功能，可以快速方便的达到这一目的。操作步骤如下：（1）在表中单击任一数据单元格，在“数据”菜单中，单击“分类汇总”命令。（2）在“分类字段”下拉列表框中，单击需要用来分类汇总的数据列（“考场编号”列）。（3）在“汇总方式”下拉列表框中，选择“计数”。（4）在“选定汇总项（可有多个）”框中，复选“考场编号”框。（5） 复选“每组数据分页”选项，以便每个考场单打印一页。其他两项“替换当前分类汇总”和“汇兑结果显示在数据下方”可根据情况选定。（6）单击确定，考场记录单就已经做好了（如图2）。图2（7）为了使每一页都打印同样的表头：“2001年期末考试 第X考场 考场单”，单击菜单“文件页面设置页眉/页脚自定义页眉(C)”，进行相关设置。为了更加美观，在“页面设置”的“页边距”窗口复选“水平居中”和“垂直居中”，并定义好纸张的大小。说明：（1）“分类汇总”前，必须按“考场编号”列排序，本例中已经是排好顺序的。（2）对“分类汇总结”的结果不满意时，可以清除“分类汇总”，Excel将同时清除分级显示和插入“分类汇总”时产生的所有自动分页符。 方法是：单击任一单元格，在“数据”菜单中，选择“分类汇总”命令，单击全部删除按钮。 3、打印准考证号码条准考证号码条一般粘贴在课桌左上角，供考生寻找自己的座位用，过去主要靠手工抄写的方法完成。这里我用Word的“邮件合并”功能巧妙地完成了这一任务。方法如下：启动Word 2000，执行“工具”菜单下的“邮件合并”命令，弹出“邮件合并帮助器”，首先在当前窗口创建一个邮件标签。依次点击“创建邮件标签活动窗口”，然后点击获取数据按钮，在下拉列表中选“打开数据源”，在文件类型列表框中把文件类型改为“MS Excel工作簿”，打开刚才“考场编号”工作表，按确定按钮。接着设置主文档，点击新建标签按钮，弹出如图3所示“新建自定义标签”对话框。图3在“标签名称”文本框中填上“座位号”，在“页面尺寸”下拉列表框中选“A4横向”。按确定按钮，出现“创建标签”对话框。点击“插入合并域”，插入“准考证号”、“姓名”两个域，按确定按钮，最后按合并按钮。好，现在的考前准备工作基本完成，只等考试成绩出来了！&返回&20. Excel的图表功能Excel的图表转换功能具有更大的吸引力。Excel能够根据工作表中的数据创建图表（即将行、列数据转换成有意义的图象）。图表能帮助辨认数据变化的趋势，而在工作表中就很难辨别。我们在Excel下先简单地制作一个记录正弦函数y=sin(x-a)数据的工作表：x(度）y1(a=0度)y2(a=30度)y3(a=60度)00-0.5-o.86630-0.50-0,560 0.866 0.5 090 1 0.866 0.51200.866 1 0.866150 0.5 0.866 1180 0 0.5 0.866210 -0.5 0 0.5240 -0.866 -0.5 0270 -1 -0.866 -0.5300 -0.866 -1 -0.866330 -0.5 -0.866 -1360 0 -0.5 -0.866然后根据工作表中的部分数据制作正弦曲线y2。其步骤如下：1通过拖动鼠标选中x栏的数据。按住Ctrl键不放，拖动鼠标再选中y2栏的数据。注意，栏目标题不要选，因为它们不是数据。2选择插入 | 图表菜单项，或者直接点击工具栏?quot;图表向导按钮，调出图表类型窗口。在该窗口的标准类型页面，列出了柱形图、条形图、折线图等图表类型可供选择。这些类型大多适用于一维数据，对于二维数据表，如果想转换成折线图，不能直接选折线图，而应先选xy散点图为主类型，然后在子图表类型中选折线散点图或平滑线散点图。3按下一步按钮，进入图表源数据窗口。此时，Excel已根据你所选的数据将正弦曲线y2显示在窗口中。4按下一步按钮，进入图表选项窗口。在该窗口标题页，你可以给图表标题框输入：正弦函数y=sin(x-a)，给数值(x)轴框输入：x（度），给数值(y)轴框输入：y。在图例页，你还可以选择是否显示图例，等等。5按下一步按钮，进入图表位置窗口。我们选择选项：作为新工作表插入，这样，Excel会为你新建一个图表页。如果选择选项：作为其中的对象插入，则Excel会将新建的图表插入在原工作表页面。6按完成按钮，Excel就会按照你的设置将所选数据转换成图表。我们看到，一个新建的正弦曲线y2显示在整个屏幕上，同时，在下方工作表标签栏，新增加了图表1标签。通过鼠标点击这些标签，可以与Sheet1、Sheet2、Sheet3等工作表进行页面切换。假如，你还想把y1、y2、y3三条正弦曲线都建在一个图表上，则可以点击Sheet1标签，回到原始的工作表页面，从工作表中选择全部的数据单元格，再重复以上步骤，即可又创建一个新图表，同时工作表标签栏新增图表2标签。这时点击文件 | 保存，则工作表及其图表将作为一个Excel文档存盘。图表也是工作表，一个Excel文档最多可包含255个工作表。图表建好后，如对选择的设置不满意，还可以通过图表菜单的子菜单回到以上的任一步骤进行修改。通过格式菜单的子菜单，则可以设置图表区、绘图区、坐标轴的图案、字体、刻度。或者直接用鼠标右键单击图表的图表区、绘图区或坐标轴，调出快捷菜单来设置修改它们。我们将x轴刻度最大值由400改为360，将刻度单位值由50改为30，这样设置更为合适。如果不显示图例，则应当为三条正弦曲线加注标识y1、y2、y3（通过添加文本框）。现在，设置好的图表2如下所示： 人们在科学实验中经常需要对大量的实验数据进行处理，Excel的图表功能可以帮助我们观察和分析客观世界变量的内在规律和函数关系，特别是通过Excel的图表 | 添加趋势线功能菜单还可以帮助趋势预测和回归分析，为科学工作者的工作提供了极大的便利。&返回&21. 批量修改数据在EXCEL表格数据都已被填好的情况下，如何方便地对任一列（行）的数据进行修改呢？比如我们做好一个EXCEL表格，填好了数据，现在想修改其中的一列（行），例如：想在A列原来的数据的基础上加8，有没有这样的公式？是不是非得手工的一个一个数据地住上加？对于这个问题我们自然想到了利用公式，当你利用工式输入A1=A1+8时，你会得到EXCEL的一个警告：“MICROSOFTEXCEL不能计算该公式”只有我们自己想办法了，这里介绍一种简单的方法：第一步：在想要修改的列（假设为A列）的旁边，插入一个临时的新列（为B列），并在B列的第一个单元格（B1）里输入8。第二步：把鼠标放在B1的或下角，待其变成十字形后住下拉直到所需的数据长度，此时B列所有的数据都为8。第三步：在B列上单击鼠标右键，“复制” B列。第四步：在A列单击鼠标的右键，在弹出的对话框中单击“选择性粘贴”，在弹出的对话框中选择“运算”中的你所需要的运算符，在此我们选择“加”，这是本方法的关键所在。第五步：将B列删除。怎么样？A列中的每个数据是不是都加上了8呢？同样的办法可以实现对一列（行）的乘，除，减等其它的运算操作。原表格的格式也没有改变。此时整个工作结束，使用熟练后，将花费不到十秒钟&返回&22. 将Excel数据导入Access如果想将Excel中的数据转换到Access中，可以采取下面的直接导入法和建立链接法来完成。一、直接导入法1.启动Access，新建一数据库文件。 2.在“表”选项中，执行“文件获取外部数据导入”命令，打开“导入”对话框。3.按“文件类型”右侧的下拉按钮，选中“Microsoft Excel（.xls）”选项，再定位到需要转换的工作簿文件所在的文件夹，选中相应的工作簿，按下“导入”按钮，进入“导入数据表向导”对话框（图1）。4.选中需要导入的工作表（如“工程数据”），多次按“下一步”按钮作进一步的设置后，按“完成”按钮。注意：如果没有特别要求，在上一步的操作中直接按“完成”按钮就行了。5.此时系统会弹出一个导入完成的对话框（图1的中部），按“确定”按钮。 至此，数据就从Excel中导入到Access中。二、建立链接法1.启动Access，新建一数据库文件。2.在“表”选项中，执行“文件获取外部数据链接表”命令，打开“链接”对话框。3.以下操作基本与上述“直接导入法”相似，在此不再赘述，请大家自行操练。注意：“直接导入法”和“建立链接法”均可以将Excel数据转换到Access中，两者除了在Access中显示的图标不同（图2）外，最大的不同是：前者转换过来的数据与数据源脱离了联系，而后者转换过来的数据会随数据源的变化而自动随时更新。 &返回&23. 办公技巧：Excel定时提醒不误事如果您从事设备管理工作，有近千台机械设备需要定期进行精度检测，那么，就得每天翻阅“设备鉴定台账”来寻找“到期”的设备实在是太麻烦了！用Excel建立一本“设备鉴定台账”是不是方便得多？方法是：用Excel的IF函数嵌套TODAY函数来实现设备“到期”自动提醒。 首先，运行Excel，将“工作簿”的名称命名为“设备鉴定台账”，输入各设备的详细信息、上次鉴定日期及到期日期（日期的输入格式应为“年-月-日”，如：2003-10-21，如图1）。 然后，选中图1所示“提示栏”下的F2单元格，点击插入菜单下的函数命令，在“插入函数”对话框中选择“逻辑”函数类中的IF函数，点击确定按钮，就会弹出“函数参数”对话框，分别在Logical_test行中输入E2=TODAY()、value_if_true行中输入“到期”、Value_if_false行中输入“ ”(如图2)，并点击确定按钮。这里需要说明的是：输入的 是英文输入状态下的双引号，是Excel定义显示值为字符串时的标识符号，即IF函数在执行完真假判断后显示此双引号中的内容。为了醒目，可在“单元格属性”中将F2单元格的字体颜色设置为红色。 最后，拖动“填充柄”，填充F列以下单元格即可。 我们知道Excel的IF函数是一个“条件函数”，它的语法是“IF(logical_test,value_if_true,value_if