Excel2007函数应用技巧.doc

一、用Excel 2007制作能互动的函数图像在Excel 2007中画出一个函数图像并不难。但是，Excel 2007的功能绝不仅仅如此，稍加改造，我们就可以利用Excel 2007制作出有交互功能的Excel函数图像，利用滚动条来研究函数。 第一步：制作图像我们来制作一个简单的一次函数y=kx+b的图像。在Excel 2007中，在A1单元格中输入“x”。在A2:A4单元格区域中分别输入“-10”、“-9.5”、“-9”，选中这三个单元格，拖动填充句柄向下至A42单元格。这样，可以得到从-10到10的数据，间隔为0.5。在B1单元格中输入“y”，在C1、D1单元格中分别输入“k”、“b”。在B2单元格中输入公式“=A2*$C$2+$D$2”并回车。选中B2单元格拖动其填充句柄向下至B42单元格复制公式。在C2、D2单元格中分别填入数字“2”、“3”，如图1所示。 输入公式选中A1:B42单元格，点击功能区“插入”选项卡“图表”功能组中“散点图”按钮下的小三角形，再打开的菜单中点击“带平滑线的散点图”，如图2所示。就可以在当前窗口中得到函数y=2x+3的图像了，如图3所示。 插入函数图像此时，如果我们改变C2或D2单元格的数值，那么函数的图像也会相应地发生改变。但是，图表中坐标轴也会跟着发生变化，这并不符合我们的习惯。而且，C2或D2单元格的数值改变如果只通过输入的方式，那也显得很麻烦。所以，我们还必须进行下面两步操作。第二步：固定坐标轴选中图表区，此时在功能区会新增加一个“图表工具”选项卡集。点击其中的“布局”选项卡 “坐标轴”功能组中“坐标轴”按钮下小三角形，在弹出的菜单中选择“主要横坐标轴其他主要横坐标轴选项”命令，如图4所示。 选项在打开的“设置坐标轴格式”对话框中，点击左侧的“坐标轴选项”项目，然后将右侧的“最小值”、“最大值”项目都设置为“固定”，并在其后的输入框中分别输入“-10”、“10”。如图5所示。 选项用同样的方法设置主要纵坐标轴。可将“最小值”和“最大值”分别设置为“-50”、“50”。这样，坐标轴就算固定好了。第三步：添加滚动条现在，我们应该为C2单元格数据指定一个滚动条，并用滚动条来调整C2单元格数据。在Excel 2007中，我们需要首先找到添加滚动条的命令。点击左上角的“自定义快速访问工具栏”右侧的小三角型按钮，在弹出的快捷菜单中选择“其他命令”命令，如图6所示。在打开的“Excel选项”对话框中，确认左侧列表中选中“自定义”项目，在中间“从下列位置选择命令”下拉列表中选择“不在功能区中的命令”，然后在下方的列表中选中“滚动条(窗体控件)”，点击“添加”按钮，将其添加到“自定义快速访问工具栏” 中。如图7所示。 选项关闭对话框，点击左上角“自定义快速访问工具栏”上新添加的“滚动条”按钮，当鼠标指针变为十字形时，在工作表中窗口中点击产生一个滚动条。在滚动条上右击鼠标，在弹出的快捷菜单中选择“设置控件格式”命令，打开“设置控件格式”对话框。点击“控制”选项卡，在“单元格链接”右侧的输入框中输入“$C$2”，指定其与C2单元格数据链接，如图8所示。我们还可以在这个选项卡中指定滚动条的最大值和最小值。 设置对象格式同样的方法为D2单元格设置一个滚动条，并做好链接。好了，现在拖动滚动条，函数的图像就会发生相应的变化。我们自然就可以由此来发现函数图像与参数之间的关系。二、Excel 2007查询操作中的函数应用在Excel中，我们经常会需要从某些工作表中查询有关的数据复制到另一个工作表中。比如我们需要把学生几次考试成绩从不同的工作表中汇总到一个新的工作表中，而这几个工作表中的参考人数及排列顺序是不完全相同的，并不能直接复制粘贴。此时，如果使用Excel的VLOOKUP、INDEX或者OFFSET函数就可以使这个问题变得非常简单。我们以Excel 2007为例。 图1 假定各成绩工作表如图1所示。B列为姓名，需要汇总的项目“总分”及“名次”位于H列和I列（即从B列开始的第7列和第8列）。而汇总表则如图2所示，A列为姓名列，C、D两列分别为要汇总过来的第一次考试成绩的总分和名次。其它各次成绩依次向后排列。图2 一、VLOOKUP函数我们可以在“综合”工作表的C3单元格输入公式“=VLOOKUP($B3,第1次!$B$1I$92,7,FALSE)”，回车后就可以将第一位同学第一次考试的总分汇总过来了。把C3单元格公式复制到D3单元格，并将公式中第三个参数“7”改成“8”，回车后，就可以得到该同学第一次考试名次。选中C33这两个单元格，向下拖动填充句柄到最后就可以得到全部同学的总分及名次了。是不是很简单呀？如图3所示。VLOOKUP函数的用法是这样的：VLOOKUP(参数1,参数2,参数3,参数4)。“参数1”是“要查找谁？”本例中B3单元格，那就是要查找B3单元格中显示的人名。“参数2”是“在哪里查找？”本例中“第1次!$B$1I$92”就是告诉Excel在“第1次”工作表的B1:I92单元格区域进行查找。“参数3”是“找第几列的数据？”本例中的“7”就是指从“第1次”工作表的B列开始起，第7列的数据，即H列。本例中“参数4”即“FALSE”是指查询方式为只查询精确匹配值。该公式先在“第1次”工作表的B!:I92单元格区域的第一列（即B1:B92单元格区域）查找B3单元格数据，找到后，返回该数据所在行从B列起第7列（H列）的数据。所以，将参数3改成“8”以后，则可以返回I列的数据。由此可以看出，使用VLOOKUP函数时，参数1的数据必须在参数2区域的第一列中。否则是不可以查找的。二、INDEX函数某些情况下，VLOOKUP函数可能会无用武之地，如图4所示。“综合”工作表中，姓名列放到了A列，而B列要求返回该同学所在的班级。但我们看前面的工作表就知道了，“班级”列是位于“姓名”列前面的。所以，此时我们不可能使用VLOOKUP函数来查找该同学的班级。而INDEX函数就正可以一试身手。图4 在B3单元格输入公式“=INDEX(第1次!$A$1I$92,MATCH(A3,第1次!$B$1B$92,0),1)”，回车并向下复制公式就可以了，如图5所示。图5 这里用到了两个函数，INDEX和MATCH。先说说这个MATCH(A3,第1次!$B$1B$92,0)。它的意思是在“第1次”工作表的B1:B92单元格区域中查找A3单元格数据，然后返回该数据在B1:B92单元格区域中的行数。以本例而言，该公式返回的结果是“2”。这一点，只要看一下图1所示工作表就清楚了。至于该公式中最后一个参数“0”是要求精确匹配，而且使用该参数时，B1:B92单元格区域不需要排序。再来看这个INDEX函数。它的用法是INDEX(参数1,参数2,参数3)。参数1是要查找的区域。参数2是行数，参数3是列数。比如公式INDEX(A2:C6,2,3)的意思就是要返回在A2:C6这个区域中第二行第三列的数据，即C3单元格的数据。所以，本例中公式的意思就是返回“第1次”工作表A1:I92单元格区域中第二行第一列的数据，呵呵，那不正是该同学所在的班级嘛！与VLOOKUP函数相比较，INDEX函数的“限制”要少一些。三、OFFSET函数其实，我们还可以使用OFFSET函数完成这个任务的。比如我们要查A3单元格同学在第二次考试中的总分。那么只需要点击E3单元格，并输入公式“=OFFSET(第2次!$A$1,MATCH($A3,第2次!$B$1B$92,0)-1,7,1,1)”，回车后并向下复制公式，就可以得到所需要的数据了。如图6所示。图6 OFFSET函数是以指定的引用为参照系，通过给定偏移量得到新的引用，比如公式“=OFFSET(C3,2,3,1,1)”，它的意思就是返回以C3单元格为基准，向下偏移2个单元格，向右偏移3个单元格处的单元格数据，即F5单元格的数据，数一下就清楚了。所以本例公式中先由“MATCH($A3,第2次!$B$1B$92,0)”得到“第2次”工作表B1:B92单元格中与A3单元格相匹配的数据所在行数（本例结果为2），再减去1正好是它相对于“第2次”工作表A1单元格的向下偏移的行数，而后面的参数“7”，则是该同学的总分成绩所在单元格相对于A1单元格的向右偏移量。因此，本例中在E3单元格的公式其实就是“=OFFSET(第2次!$A$1,1,7,1,1)”，其意思就是返回“第2次”工作表中相对于A1单元格下移一行右移7行处单元格数据。好了，这三个查找引用函数的用法应该算说清楚了吧？只要我们认真研究它们的用法，相信会有很多妙用的。三、不用公式 让Excel按人头打印出工资条使用Excel按人头打出工资条，有用Word邮件合并功能的，也有用VBA功能的，也有采用编写公式直接产生的。可参看天极软件办公栏目的文章（Word、Excel配合按人头打印工资条、仅需一个公式让Excel按人头打出工资条）但我觉得，对于普通公司员工来说，这些方法都显得专业性太强。其实，变通一下，也可以不用公式，直接让Excel按人头打出工资条的，方法简单，适合Excel新手使用。假定公司有员工100人，工资数据在A2:R101区域。在工作表的第一行A1:R1区域为工资项目，如图1所示。我们需要的工资条是第一行为工资项目，第二行为各员工的工资，第三行为空行，以便我们打印后分割。 图1第一步：先选中A列，点击右键，在弹出的快捷菜单中点击“插入”命令，插入一个辅助列。在A2、A3、A4单元格分别输入数字“2”、“5”、“8”。选中这三个单元格，拖动填充句柄向下至A101单元格，为这些单元格添加序号。第二步：复制A1:R1单元格，然后在A102单元格单击，并粘贴。选中A102:R102单元格，将鼠标定位于填充句柄，按下右键，向下拖动填充句柄至R200单元格。松开右键，在弹出的菜单中选择“复制单元格”命令，如图2所示。这样就复制了99个工资项目，加上第一行的那个，正好100个。 图2第三步：在A1单元格中输入数字“1”，在A102、A103、A104单元格分别输入数字“4”、“7”、“10”。然后选中A102:A104单元格向下拖动填充句柄，至A200单元格，为所有的工资项目添加序号。第四步：在A201、A202、A203分别输入数字“3”、“6”、“9”。然后选中这三个单元格，向下拖动填充句柄至A300单元格。好了，现在您肯定明白了。我们在工资项目、工资数据、空行分别添加了相互间隔的序号。现在，我们只要根据A列序号，升序进行排列，那么就可以得到所需要的工资条了，如图3所示。 图3最后要做的，就是选中A列数据，点击右键，在弹出的快捷菜单中选择“隐藏”命令，将该辅助列隐藏起来，如图4所示。 图4四、Excel 2003使用技巧 特殊班级排序方法在使用Excel 2003进行成绩统计时，一般都是把成绩按班级录入并计算出个人总分，然后把所有班级成绩粘到一起，再以总分为关键字从高到低排列好，并填充上校名次，然后再按班级顺序排列，以便以班级为单位打印输出成绩单。这样一看成绩单，就能知道班级在全校前50、100名有多少学生，每个学生在全校的名次等。但是在Excel 2003中却不认识一班、二班、三班的顺序，而是以第一个汉字的拼音的第一个字母顺序排列，排完的顺序是八班、二班，让人看起来很不舒服。于是便想办法让Excel 2003认识了一班，二班，三班 1、在L1单元格中输入1，L2单元格中输入2，然后拖选上L1和L2单元格，把鼠标指针指向L2单元格右下角，等鼠标指针变成填充柄（黑色实线加号）时，双击左键，就自动完成校名次的填加了。（如图1）图1 2、单击“工具”菜单选项，在打开的“选项”对话框中选“自定义序列”选项卡，在“输入序列”列表框中输入“一班，二班，三班，四班，五班，六班，七班，八班，九班，十班”，中间用英文逗号分开，再依次点“添加”和“确定”按钮。（如图2）图2 3、单击“数据”菜单排序，在“排序”对话框中，“主要关键字”选“班级”并选择后面的“升序”，再点“选项”按钮，打开“排序选项”对话框，接着在“自定义排序次序”列表框中选“一班，二班”，再依次点“确定”按钮退出，看是不是非常听话的按一班，二班，三班的顺序排列了。（如图3）图3五、举手之劳 实现Excel 2007文档逆序打印在Word 2007中，只要依次点击左上角Office徽标打印，在打开的“打印”设置窗口中点击“选项”按钮，在接着出现的“Word 选项”窗口中切换到“高级”标签页，然后勾选“打印”项中的“逆序打印页面”选项(图1)，这样打印机打印时就会从最后一页开始逆序打印到第一页，打印完毕后，最后一页自然处于最底下，而第一页处于最上面，如此则无须逆序重排文档，直接就可以装订成册，对于长篇打印文档尤其方便。 图1 逆序打印页面选项与Word相比， Excel的打印功能就相形见绌，Excel 2007中也未见对打印功能有任何改善。但我们只要稍费心思，也能让Excel 2007轻松拥有“逆序打印”功能。一.打印机属性巧设置有些打印机只要安装了自带的驱动程序，就可以在打印属性中找到类似“逆序打印”的打印选项。以笔者的Canon PIXMA iP1000为例(事先请安装打印机附带光盘中的驱动程序)，打印时，只要打开Excel 2007工作簿，依次点击左上角Office徽标打印，打开“打印”设置窗口，在打印机名称中选中“Canon PIXMA iP1000”，然后点击“属性”按钮，调出“Canon PIXMA iP1000属性”窗口，切换到“页设置”标签页，勾选“反向”复选框再点击“确定”按钮即可(图2)。 图2 页设置二. 宏代码实现法有些打印机不具备上述属性，但通过宏照样可以实现逆序打印功能。打开该Excel文件，切换到“视图”选项卡，点击“宏”“录制宏”，出现“录制新宏”窗口，在“宏名”定义一个名称为：ReversePrint(图3)，点击“确定”退出; 图3 录制新宏再点击“宏”“查看宏”，选择“宏名”下的“ReversePrint”并点击“编辑”，打开“Microsoft Visual Basic”编辑器，用如下内容替换右侧窗口中的所有代码(图4)，然后保存关闭VBA编辑器： 图4 打开“Microsoft Visual Basic”编辑器Sub ReversePrint()Dim NumPages As Long, Page As LongNumPages = ExecuteExcel4Macro(GET.DOCUMENT(50)For Page = NumPages To 1 Step -1ActiveSheet.PrintOut from:=Page, To:=PageNext PageEnd Sub再点击“宏”“查看宏”，选择“宏名”下的“ReversePrint”并点击“执行”即可逆序打印。六、Excel 2007中批量删除超链接的两种方法Excel中某列单元格均含有超链接，用手工删除超链接的方法必须一个个进行：右击该列中任何一个含有超链接的单元格，选择“取消超链接”(图1)。由于数量巨大，因此只能考虑采用批量删除法。 图1一、宏代码去除法打开该Excel文件，切换到“视图”选项卡，点击“宏”“录制宏”，出现“录制新宏”窗口，在“宏名”定义一个名称为：RemoveHyperlinks(图2)，点击“确定”退出; 图2再点击“宏”“查看宏”，选择“宏名”下的“RemoveHyperlinks”并点击“编辑”，打开“Microsoft Visual Basic”编辑器，用如下内容替换右侧窗口中的所有代码(图3)，然后保存关闭VBA编辑器：Sub RemoveHyperlinks()Remove all hyperlinks from the active sheetActiveSheet.Hyperlinks.DeleteEnd Sub 图3再点击“宏”“查看宏”，选择“宏名”下的“RemoveHyperlinks”并点击“执行”即可去除该工作表的链接。用以下代码也可以达到相同的目的：Sub ZapHyperlinks()Cells.Hyperlinks.DeleteEnd Sub二、选择性粘贴法右击含有超链接的列并选择“复制”，接着在该列(左)右边插入一空白列，然后右击该空白列，选择“选择性粘贴”(图4)， 图4在随后出现的“选择性粘贴”窗口中，点选“数值”选项(细心的人会发现，当选择“数值”等选项时，“粘贴链接”按钮就变成灰色不可用状态，图5，自然也就不会进行超链接的粘贴)，最后保留该列，再删除原先含有超链接的列即可。 图5七、也谈Excel表格行与列交叉着色的实现对于Excel表格中行与列的各种着色方法，网上介绍的非常详细，笔者也从中受益不浅(详见天极软件的文章 /office/101/3002601.shtml 及 /office/244/3028744.shtml )。以上几种方法均涉及到函数，使用也很方便。的却，Excel中的函数非常丰富，且功能强大，是它的一大特色。但实际应用中，普通用户对于函数的掌握却仅仅局限在几个常用函数上，对其它的还是比较陌生的。不用函数能否实现以上操作呢?经过实践笔者发现不用函数照样可以实现Excel行列交叉着色，并且还可以对列进行着色。其操作花样繁多、简单易行，比使用函数还方便。例如在进行隔行填色时，先选定要着色的单元格B2:G2。然后在“绘图”或者“格式”工具栏中找到“填充颜色”按钮，单击其右侧的三角弹出下拉菜单，从中选好颜色后进行填充。 填充颜色B2:G2单元格着色后，再选定B2:G3单元格，鼠标指向选定区域右下角的填充柄，然后向下拖动鼠标即可得到隔行着色的效果。 隔行着色的效果对于两行或是多行交错着色，参照以上方法同样可以实现。先将前几行分别着色，形成一个循环规律，然后选定这几行再向下填充即可。 多行交错着色多行交错着色对于列的着色，可以参照行的着色方法实现，同样很方便。 列的着色列的着色呵呵，简单吧?试一试，你也可以做出来。八、Excel数据透视表分类求学生成绩平均值期末统考结束后，教育局要求全市学生的成绩都汇总在如图1的同一个Excel工作表中，并要求求出各学校、各专业、各学科的平均分。 全市三个学校、每个学校都有六个专业、每个专业都有五门课、各专业人数不等，总计有1000余人，工作量巨大。但如果采用Excel数据透视表来完成的话，呵呵，就简单多了。 一、创建数据透视表点击菜单命令“数据数据透视表和数据透视图”，打开“数据透视表和数据透视图向导”对话框。第一步，如图2所示，选择“Microsoft Excel数据列表或数据库”及下面的“数据透视表”单选项。 第二步，如图3所示，在“选定区域”输入全部数据所在的单元格区域，或者点击输入框右侧的“压缩对话”按钮，在工作表中用鼠标选定数据区域。 第三步，在对话框中选定“新建工作表”单选项，以便将创建的数据透视表放到一个新的工作表中，再点击“完成”按钮，如图4所示。 这样，就可以建立一个空的数据透视表，并同时显示“数据透视表”工具栏和“数据透视表字段列表”对话框，如图5所示。二、利用数据透视表得到需要的结果根据要求，我们应该得到各学校、各专业、各学科的平均分数。因此，我们应该把“学校”、“专业”作为行字段，而把各学科数据作为数据项。从“数据透视表字段列表”中，把“学校”拖到数据透视表左侧“将行字段拖至此处”位置，再将“专业”字段拖至“学校”字段的右侧，待鼠标变成“I”字形时松开鼠标，如图6所示。如此就会得到如图7所示的效果。这正是我们想要计算平均分的各个项目。 现在，我们只要从“数据透视表字段列表”中依次将学科字段拖至数据透视表中“请将数据项拖至此处”位置，可以得到各学校、各专业、各学科的成绩总和。如图8所示。 现在我们将各个求和项改为求平均值就可以了。选中透视表中第一个“求和项：语文”，如图中C4单元格，然后点击“数据透视表工具栏”中“字段设置”按钮，打开“数据透视表字段”对话框。在此对话框“汇总方式”列表中选择“平均值”。如图9所示。确定后，所有各学校、各专业的语文科就由求和改成求平均值了。 其它学科照此办理。五门课只要五次这样的操作。与此同时，我们还会得到各学校的各学科平均分及全市三所学校的各学科平均分，如图10所示。 当然，用分类汇总的办法也可以完成上面各数据的分析。但是，这需要首先对数据进行相应的排序，而且如果我们需要产生报表，那么需要自己设置相应的表格。而用数据透视表来完成，则可以自动产生相应的报表，省却了设计表格的麻烦。另外，如果拖动行字段或列字段相应的字段，则可以方便地创建另外的报表，非常方便。如图11所示表格，即只需要将行字段中的“专业”字段拖至“列字段”，就可以了。九、仅需一个公式 让Excel按人头打出工资条使用Excel按人头制作工资条，网上有两个解决方案，一个是利用Word的“邮件合并”功能(详见这里)，另一个是利用VBA来实现。用“邮件合并” 功能操作虽说不难，但数据过多时也很麻烦；用VBA处理起来倒是很方便，但几十行的程序也够初学者忙活的。经过一番试验，笔者找出了一个比较简单的方法，只需一个公式就可以按人头打印出工资条来。新建一Excel文件，在sheet1中存放工资表的原始数据，假设有N列。第一行是工资项目，从第二行开始是每个人的工资。第一行是工资项目，从第二行开始是每个人的工资。在sheet2中我们来设置工资条。根据实际情况，工资条由三行构成，一行对应工资项目，一行对应一个人的工资数据，然后是一个空行用来方便切割。这样三行构成一个工资条。工资项目处在行号除以3余数为1的行上；空行处在行号能整除3的行上。以上两行不难设置，关键是工资数据行，牵扯到sheet1与 sheet2中数据的对应，经分析不难看出“sheet1中的数据行=INT(sheet2中的数据行+4)/3)”。这样我们在sheet2的A1单元格中输入公式“=IF(MOD(ROW(),3)=0,IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$AN,INT(ROW()+4)/3),COLUMN()”。确认后选择A1单元格，把鼠标放在A1单元格的右下角，鼠标变成“+”时，向右拖动鼠标自动填充至N列，这样工资条中的第一行就出来了。选定A1：N1，把鼠标放在N1单元格的右下角，鼠标再次变成“+”时，向下拖动鼠标自动填充到数据的最后一行，工资条就全部制作完成了。该公式运用IF函数，对MOD函数所取的引用行号与3的余数进行判断。如果余数为0，则产生一个空行；如果余数为1，则固定取sheet1中第一行的内容；否则运用INDEX函数和INT函数来取Sheet1对应行上的数。最后来设置一下格式，选定A1：N2设上表格线，空行不设。然后选定A1：N3，拖动N3的填充柄向下自动填充，这样有数据的有表格线，没有数据的没有表格线。最后调整一下页边距，千万别把一个工资条打在两页上。怎么样，还满意吧?十、把Excel里显示的错误标识全部隐藏起来作者： 宋志明 原创Excel经常会因为各种原因出现错误值标识，比