Excel函数集

1.禁止自动运行宏的方法

　　你可能遇到过可恶的宏病毒，其中有一部分是在打开文件时自动运行并产生危害。

　　在您打开一个Excel文件时，可以很容易地阻止一个用VBA写成的在打开文件时自动运行的宏的运行。从“文件”菜单中选择“打开”，选择您所要打开的文件的名字，在点击 “打开”时按住［Shift］键，Excel将在不运行VBA过程的情况下，打开该工作薄。 按住［Shift］键阻止宏运行的方法同样适用于选择打开菜单底部的文件（最近打开的几个文件） 。同样，在您关闭一个Excel文件时，也可以很容易地阻止一个用VBA写成， 在关闭文件时自动运行的宏的运行。从“文件”菜单中选择“关闭”，在点击 “关闭”时按住［Shift］键，Excel将在不运行VBA过程的情况下关闭这个工作薄。 （按住［Shift］键同样适用于点击窗口右上角的“×”关闭工作薄时阻止宏的运行。

2.显示更多工作表的技巧

　　在屏幕上显示许多工具栏使得你不能显示更多的数据。你并不需要将工具条从屏幕中移去和极大化Excel窗口，而只需在“视图”菜单中选择“全屏显示”。Excel工作表充满整个窗口时，工具条将消失，只在屏幕顶部显示菜单。（要想一次看到更多的单元格，可以从“视图”菜单中选择“显示比例”，选定一个较小的百分比，然后点击“确定”。）恢复原始的窗口大小和工具条显示，只需通过点击“全屏显示”工具条中的“关闭全屏显示”或从“视图”再次选择“全屏显示”关闭全屏幕窗口。

3.计算Excel公式的一部分

　　下一次你需要在Excel中调试一个复杂的公式或者只是想知道一个在公式中引用的单元格的值时，试着这样做：选定含有公式的单元格并按［F2］（或者简单地双击那个单元格） 。 然后，拖亮等式中需要检查的部分或公式引用的单元格，按［F9］Excel就会将被拖亮的部分替换成计算的结果。按［Ctrl＋Z］可以恢复刚才的替换。

　　你可以用同样的方式替换其他部分直到你找到那个出现错误的公式结果为止。 （按［F2］后马上按［F9］，或者拖过整个公式按［F9］。）

　　例如，假设你选定了含有公式“Κ（12×12）／（1＋Rate）λA20的单元格。按［F2］，拖亮等式12×12，并按［F9］。Excel将公式的那部分转换为144。这时，公式就变成Κ（144）／（1＋Rate）λA20。同样，拖亮单元格引用A20，并按［F9］，Excel将其替换成工作表中的值。 当你完成了公式的计算部分，如果想保留原来的公式， 按［Esc］，如果想替换公式，按［Enter］。你还可以按［Ctrl＋Z］来恢复修改。

4.使一个单元格合适全部字体

　　有时你并不希望单元格随着其中的内容放大或缩小（如在使用折行排列时），需要时数据恰好充满单元格事先设置好的高度和宽度。不用尝试的方法去达到合适的字体大小， 你可以使得Excel的单元格自动地调整字体来变得合适。只需选择一个或多个单元格选择“格式－单元格”。选择“对齐”标签，选中“缩小字体填充”选项。但你不能同时选择“缩小字体填充”和“自动换行”，因为他们是对立选项。

5.在拖放操作中使用［Alt］键的作用

当拖动单元格时，按住［Alt］键能够起到以下两个作用：

　　1．防止当你在工作表边缘拖动时屏幕的滚动失去控制。

　　2．这样你可以将单元格拖动到另外一个工作表中。按住［Alt］键，将你所要移动的单元格拖到目标工作表的标签处。Excel会激活那个工作表使你能够在其中选择拖放点。在不同的单元格中进行复制，需要在拖动时同时按住［Ctrl］和［Alt］键（这种操作对拖动图表和对象时无效）。

6.自动格式化工作表

　　EXCEL提供了16种格式供选择， 在格式化之前要先选择范围，如果要对整个工作表格式化可不选择， 因为用户不选择范围时，EXCEL自动选择整个工作表。方法是执行“格式”菜单下的“自动套用格式”，将出现“自动套用格式”对话框，每选择一种格式可在右边的示例中观看效果，若满意可单击“确定”按钮。

7.利用“自动套用格式”定制自已的格式

　　如果用户要定制的格式和EXCEL提供某一格式类似但有少许不同， 可首先选择某一个格式，然后单击“选项”按钮，将需要自己定制的格式不选择，比如如果你想自己定义字体， 不使用EXCEL提供的字体，可将字体前的复选框清除，然后单击“确定”按钮，回到工作表中再定义自己的字体，这样可减少手工定制格式的工作量。

8.自定义数字显示格式

　　可自定义数字格式，方法是选择好范围之后，单击鼠标右键，在快捷菜单中选择“单元格格式”，在对话框中选择“数字”选项卡，单击自定义，在右边“类型”中可输入自定义的数字格式，此处你需要了解自定义格式中常用符号的意义，可以通过选择其它已有分类观看“示例”来得知符号的意义。例如，笔者在对日期进行格式化时，想找到这样的格式97－06－01，但已有格式中没有找到这种格式，于是采用自定义的方法， 首先通过观察发现y－表示年，m－表示月，d－表示日，然后在自定义框中输入yy－mm－dd，这样所有日期都变成8个字符了，便于查看。

9.定制数据对齐格式

　EXCEL提供的格式工具栏上提供了 “左对齐”、“右对齐”、“居中”、“跨行居中”四种方式，这只是常用的四种方式。如果你想使用其它的方式，可选择好范围之后，单击鼠标右键，在快捷菜单中选择“单元格格式”，在对话框中选择“对齐”选项卡，此处可提供水平对齐格式八种，垂直对齐格式五种，也可利用“格式”菜单中的“单元格格式”命令来完成。在“对齐”对话框中还有一个“数据自动换行”复选框，此选项主要用来当某一项单元格的内容较长时，为了数据表的美观，将超过单元格列宽内容的字符串移到下一行。注意：自动换行对数字无效，若数字长度超过列宽，将出现＃＃＃＃字样，以科学计数法表示。

10.格式拷贝技巧

　　格式拷贝的功能是将某一格式化操作复制到另一部分数据，具体方法是：选择含有所需格式的单元，单击工具条上的“格式刷”按钮，此时鼠标变成了刷子形状，然后选择要格式化的数据，放开鼠标即可将格式拷贝过去。如果要用此格式进行多部分相同的复制操作，可以选定格式后双击格式“格式刷”按钮，此后可进行多次复制操，直到再单击“格式刷”按钮或按ESC键退出当前格式化操作。

11.定制自己的特有格式

　　如果用户想的数据表经常使用某一格式，可定义自己的样式。方式是：执行“格式”菜单下的样式命令，在样式对话框中的样式名文本框中输入自己的样式名称，比如“我的样式”，在下面的各种选项中可选择需要自己定义的各种样式，如果需要修改某一选择的样式，可单击“再改”按钮，将会弹出“单元格格式”对话框，在此处修改样式后单击“确定”返回，然后单击“确定”按钮保存自定义样式，以后可随时使用。

12.用特定函数实现快速输入

　　在工作中，无论是进行文字处理或是制作报表，都可能要输入大量重复的数据。

　　利用Word的“自动更正”命令(“工具”菜单)可以巧妙地实现数据的快速输入，但是在Excel中却没有类似的菜单命令。但它自带的VLOOKUP函数可巧妙地解决这个问题。 比如处理产品的销售数据时， 往往要输入大量同一单位的名称，使用VLOOKUP函数可以使我们只需键入一个字母就可实现单位名称的快速输入。

　　1.创建源工作表

　　进入Excel 7， 单击“文件”菜单，再单击“新建”命令，创建一个新工作簿。在工作表Sheet1上建立产品销售报表，方法是在A1单元格内输入“日期”，在B1单元格内输入 “代码” ，在C1单元格内输入“购货单位”，在D1单元格内输入“产品型号”，在E1单元格内输入“购货数量”，在“F1单元格内输入“单价”，在G1单元格内输入“总价”。

　　2.创建代码工作表

　　在工作表Sheet2上建立购货单位的代码表，方法是单击Sheet2，在A1单元格内输入 “代码”，在B1单元格内输入“购货单位名称”，在A2单元格内输入“A”，在B2单元格内输入“上海煤科机电技贸有限公司中煤液压气动技术中心”。按同样的方法依次输入各购货单位的名称及其相应的代码(本例假设有49个单位)。

　　3.实现数据的快速输入

　　单击工作表Sheet1，在A2、A3、A4……单元格内输入相应的日期，在B2、B3、B4……单元格内输入相应的单位代码，在D2、D3、D4……单元格内输入相应的产品型号，在E2、E3、E4……单元格内输入相应的产品购货数量，在F2、F3、F4……单元格内输入相应的产品单价，在G2单元格内输入公式“=E2＊F2”，在C2单元格内输入函数“=VLOOKUP (B2,Sheet2!＄A＄2:＄B＄50,2,0)”，用鼠标单击C3单元格右下角的填充句柄不放，向下拖动进行公式的复制，这时在“购货单位”项下各单元格内已全部输入了相应的购货单位名称。用同样的方法向下拖拽G3单元格右下角的填充句柄即可。

13.SUMIF函数实现自动动态统计的方法

　　当我们用Excel 7对产品的销售数据进行管理时， 常常需要知道各类产品的当前销售情况以及当前库存情况， 尽管Excel 7中的“分类汇总”命令(“数据”菜单)可以帮助我们方便地统计出各类产品的销售情况，但对于当前库存情况，用该命令统计就显得有些力不从心了。其实只需用Excel 7中的SUMIF函数，就可以实现当前库存情况的自动动态统计。

　　1.创建销售工作表

　　进入Excel 7单击 “文件”菜单，再单击“新建”命令，创建一个新工作簿。在工作表Sheet1上建立产品销售报表，方法是在A1单元格内输入“日期”，在B1单元格内输入 “购货单位”，在C1单元格内输入“产品型号”(为了介绍的方便，本例假设只有5种产品， 其型号分别为AA、 BB、CC、DD、EE。)，在D1单元格内输入“购货数量”，然后输入相应的数据，本例假设一年的销售记录不会超过1000个数据。用鼠标双击工作表Sheet1标签，改名为“销售”。

　　2.创建进货工作表

　　在工作表Sheet2上建立各类产品进货情况表，方法是单击工作表标签Sheet2，进入工作表Sheet2，在A1单元格内输入“日期”，在B1单元格内输入“产品型号”，在C1单元格内输入“进货数量”，再输入相应的数据，本例假设一年的进货记录不会超过50个数据。用鼠标双击工作表Sheet2标签，改名为“进货”。

　　3.创建库存工作表

　　单击工作表标签Sheet3，进入工作表Sheet3，在A1单元格内输入“产品型号”，在B1单元格内输入“进货数量”，在C1单元格内输入“销售数量”，在D1单元格内输入“当前库存量”，在A2、A3、A4、A5、A6单元格内依次输入各产品型号的名称AA、BB、CC、DD、EE，用鼠标双击工作表Sheet3标签，改名为“库存”。

　　4.实现自动动态统计

　　在 “库存”工作表中的B2单元格内输入函数“=SUMIF(进货!＄B＄2：＄B＄51，“AA” ，进货!＄C＄2：＄C＄51)”，用鼠标单击B2单元格右下角的填充句柄不放，向下拖动至B6单元格进行公式的复制，然后将B3单元格内公式中的“AA”改为“BB”，将B4单元格内公式中的“AA”改为“CC”，将B5单元格内公式中的“AA”改为“DD”，将B6单元格内公式中的 “AA” 改为“EE”。同理，在C2单元格内输入函数“=SUMIF(销售!＄C＄2：＄C＄1001，“AA”，销售!＄D＄2：＄D＄1001)”，用鼠标单击C2单元格右下角的填充句柄不放，向下拖动至C6单元格进行公式的复制，然后将C3单元格内公式中的“AA”改为“BB”，将C4单元格内公式中的“AA”改为“CC”，将C5单元格内公式中的“AA”改为“DD”，将C6单元格内公式中的“AA”改为“EE”。在D2单元格内输入公式 “=B2－C2”，用鼠标单击D2单元格右下角的填充句柄不放，向下拖动至D6单元格进行公式的复制。

　　至此，当前库存情况的自动动态统计工作便完成了，以后，每当您在“销售”工作表或“进货”工作表中输入一个数据，在“库存”工作表中便自动统计出每一种产品的“进货数量”、“销售数量”和“当前库存量”。

　 SUMIF函数是将给定条件所给定的单元格相加在公式 “=SUMIF(进货＄B＄2：＄B＄51，“AA”，进货＄C＄2：＄C＄51)”中，进货!＄B＄2：＄B＄51表示希望计算的单元格区域，“AA”表示累加单元格的条件，进货!＄C＄2：＄C＄51表示求和的实际单元格。仅当区域中相应的单元格满足条件时，进货!＄C＄2：＄C＄51中的单元格才被求和。

14.让表格动起来的方法

　　在单位员工众多， 又无别的动画软件可用的情况下，为了能将每日的数据(如工资核算情况)不停地循环显示给员工们，可利用EXCEL的表格功能和运算功能，先把表格制好，再通过EXCEL的宏制作，编写EXCEL BASIC程序，此表格就会循环滚动，放至LED大屏幕上，效果相当不错。

　　首先，把表格的标题、表头、日期等制好，再输入内容，如：姓名、组别、计划产量、实际产量、日资、实得工资、本月累计等，然后把标题、表头、日期冻结起来，以便在滚动时能看见。接着，进入“插入”菜单，选择“宏”选项，此时增加了一个MODULE1模块，于是我们便可以在此模块下输入BASIC程序了。输完以后，使表格回至头， 选择 “工具”菜单下的“宏”选项，弹出一窗口，选择AUTO－OPEN宏名，再选“运行”按扭，表格便可从头运行了，等它运行至尾，又会从尾到头方向运行。就这样不断地滚动，直至按下ESC键中止它。以后只要每次打开此工作表，便可自动运行。如对下面的程序稍做修改，也可使其左右滚动。

　　程序清单如下(假设有1000名员工)：

Sub AUTO－OPEN( )

oldtime=Timer( )

newtime=Timer( )

A=0

While 1=1

newtime=Timer( )

If newtime－oldtime>2 Then

oldtime=newtime

If A<1000 Then

ActiveWindow.SmallScroll DOWN:=1

A=A＋1

End If

If A>=1000 Then

ActiveWindow.SmallScroll UP:=1

A=A＋1

If A>=2000 Then

A=0

End If

End If

End If

Wend

End Sub

　　注意：newtime和oldtime(即新、旧时间)不能相差太小，否则移动很快，无法看清楚；也不能相差太大，否则每移一行要等待很长的时间。

15.用VBA一次性取消隐藏工作表的方法

　　Excel可以完成工作簿中多个工作表的一次性隐藏， 但是却不能使他们一次性消除隐藏，这里介绍一种使用VBA使逆过程一次性完成的方法。

　　1.隐藏工作表

　　一次隐藏一个工作簿中的多个工作表十分简单。如果选择一个工作表，只需点击它的标签。要选择多个时，需要选得一组连续工作表的最后一个标签点击，然后再按住 ［Shift］ 键， 点击第一个标签。 选择不连续的工作表时， 需要在点击时，按［Ctrl］键。选好后，选择菜单“格式－工作表－隐藏”便可一次隐藏这些工作表。

　　2.重现一个工作表　

　　拉下“格式”菜单，选择“工作表”并选择“取消隐藏”命令。操作后，会出现一个对话框，取消隐藏列表框中会出现活动工作簿中已经隐藏的工作表的名字。要取消隐藏，选择其中一个名字，点击“确定”。Excel将重现那个工作表并关闭对话框。

　　3.重现所有工作表

　　不幸的是， Excel不能一次取消隐藏所有对话框中的工作表。这样，如果你需要重现多个工作表，就要多次发出“格式－工作表－取消隐藏”的命令，对于工作表不多时，可能不是十分麻烦，但如果你想重现工作簿中所有被隐藏的工作表，你就不得不多次发出同样的命令， 下面是一个简单的VBA程序可以重现所有被隐藏的工作表这个问题。

Sub UnhideAllSheets（）

For Each Sheet In ActiveWorkbook．Sheets

Sheet．Visible=True

Next

EndSub

　　4.如何编制程序

　　要生成UnhideAllSheets过程，首先要按［Alt］［F11］切换到VisualBasic编辑器。 通常你需要在你的Personal．xls工作簿中生成它，以便于在每次打开工作簿时都能够出现。然后，按［Ctrl］R显示项目窗口，选择与Personal．xls相关的工作簿的名字。现在你可以通过在“插入”菜单下选择“模块”项在项目中插入模块。在新的模块代码窗口输入上面的程序。 最后， 点击“保存”工具条上的按钮保存项目。（注意，保存项目的同时也将保存与其相关的工作簿Personal．xls。）

　　5.程序解析

UnhideAllSheets过程使用了一个简单的循环来取消隐藏活动工作簿中的隐藏工作表。这个语句就是：

ForEachSheetInActiveWorkbook．Sheets

　该命令使Excel在活动工作簿中的每一个工作表中循环一次， 进行如下的命令操作：

SheetVisible=True

　　将每个工作表的可视属性设置为True，这将显示多个工作表。如果程序遇到的工作表的可视属性已经是True，那么命令就不起作用。第三句：Next

　　该语句同第一句共同组成循环完成显示任务。

17. 查找数据公式两个(基本查找函数为VLOOKUP,MATCH)

（1）、根据符合行列两个条件查找对应结果

=VLOOKUP(H1，A1：E7，MATCH(I1，A1：E1，0)，FALSE)

（2）、根据符合两列数据查找对应结果(为数组公式)

=INDEX(C1：C7，MATCH(H1&I1,A1:A7&B1:B7,0))

18、关于COUNTIF

COUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1:A10,">=90")

介于80与90之间需用减，为 =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90")

19. 将多个列中的文本联接起来

可以使用 & 运算符或 CONCATENATE 函数将多个列中的文本连缀或合并起来

$D$2:=CONCATENATE(A2," ",B2," ",C2＂)

$D$2:=A1&" "&B2&" "&C2＂

注意：单元格间的空格 (" ") 用于在显示文本间插入空格。

20. 使用条件求和对数据求和

假设您在单元格 A1:A10 中创建了一列数据，而且希望对所有大于 50 且小于 200 的值求和。为此，请使用以下数组公式：

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,A1:A10,0),0))

注意：请务必按下 Ctrl+Shift+Enter，这样才可以将公式作为数组输入。执行此操作后，您会看到公式被花括号 {} 括起。不要尝试手动输入括号。

此公式对范围内的每个单元格使用嵌套的 IF 函数，并且仅当两个测试条件同时满足时才追加单元格数据。

21. 使用条件求和对数据计数

假设您在单元格 A1:A10 中创建了一列数据，而且希望统计所有大于 50 且小于 200 的值的数目。为此，请使用以下数组公式：

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,1,0),0))

注意：请务必按下 Ctrl+Shift+Enter，这样才可以将公式作为数组输入。执行此操作后，您会看到公式被花括号 {} 括起。不要尝试手动输入括号。

此公式对范围内的每个单元格使用嵌套的 IF 函数，并且仅当两个测试条件同时满足时才向总数中追加一。

22. 使用 INDEX 函数和 MATCH 函数查找数据

假设您在单元格 A1:C5 中创建了以下信息表，且此表包含单元格 C1:C5 中的年龄 (Age) 信息：

假设您希望根据某人的姓名 (Name) 查找此人的年龄 (Age)。为此，请按如下公式示例，配合使用 INDEX 函数和 MATCH 函数：

=INDEX($A$1:$C$5, MATCH("Mary",$A$1:$A$5,),3)

此公式示例使用单元格 A1:C5 作为信息表，并在第三列中查找 Mary 的年龄 (Age)。公式返回 22。

23. 利用函数COUNTA统计本班的应考人数(总人数)

　　因为函数COUNTA可以计算出非空单元格的个数，所以我们在利用此函数时，选取本班学生名字所在单元格区域(B3～B12)作为统计对象，就可计算出本班的应考人数(总人数)。

　　1．选取存放本班总人数的单元格，此单元格是一个经过合并后的大单元格(C18～G18)；

　　2．选取函数；单击菜单“插入/函数”或工具栏中的函数按钮f＊，打开“粘贴函数”对话框，在“函数分类”列表中选择函数类别“统计”，然后在“函数名”列表中选择需要的函数“COUNTA”，按“确定”按钮退出“粘贴函数”对话框。

　　3．选取需要统计的单元格区域；在打开的“函数向导”对话框中，选取需要计算的单元格区域B3～B13，按下回车键以确认选取；“函数向导”对话框图再次出现在屏幕上，按下“确定”按钮，就可以看到计算出来本班的应考人数(总人数)了。

24、利用COUNT、COUNTBLANK和COUNTIF函数分别统计各科参加考试的人数、统计各科缺考人数、统计各科各分数段的人数

　　我们在输入成绩时，一般情况下，缺考的人相应的科目的单元格为空就可以了，是0分的都输入0。

　　(一)统计语文科的参加考试人数、缺考人数、各分数段的人数。

　　1．用函数COUNT统计语文科的参加考试人数。单击存放参加语文科考试人数的单元格C19，然后按照前面的操作步骤，首先在“函数分类”列表中选择函数类别“统计”，在“函数名”列表中选择需要的函数“COUNT”；其次按照上面“一、3”选取单元格区域的操作方法，选取需要统计的单元格区域(C3～C12)，然后回车确认，单击“函数向导”对话框“确定”按钮，就可以看到计算出来的结果。

2．用函数COUNTBLANK统计语文科的缺考人数。单击存放语文科缺考人数的单元格C20，然后按照上面的操作方法，在“统计”类别中选取函数COUNTBLANK，并进行需要统计单元格区域(C3～C12)的选取，直到得出结果。

　 3．用函数COUNTIF分别统计出语文科各分数段的人数。

　　(1) 统计90分(包括90分)以上的人数(表中为“90分以上”)：单击存放此统计人数的单元格C21，然后选取函数,即选取“统计”类别中的函数“COUNTIF”，然后单击“函数向导”对话框中的“Ragane”右侧的按钮，以选取统计单元格的区域(C3～C12)后，回到“函数向导”对话框中，再输入统计的条件：“$#@62;=90”,如图2。单击“确定”按钮，就可以计算出结果了。

　　(2) 统计大于或等于80分而小于90分的人数(表中为“80～89分”)：双击单元格C21进入编辑状态，可以看到统计90分以上的分数段的人数的公式如图3所示是：=COUNTIF(C3:C12,″$#@62;=90″)，

　　要统计本分数段人数，我们只要双击C22，在其中输入计算公式：

　　=COUNTIF(C3:C12,″$#@62;=80″)－COUNTIF(C3:C12,″$#@62;=90″)

　　回车后，即可计算出此分数段的人数。

　　(3)用同样方法，只要在C23、C24、C25三个单元格中，分别输入公式(可以通过复制粘贴后,修改数字快速完成)：

　　=COUNTIF(C3:C12,″$#@62;=70″)－COUNTIF(C3:C12,″$#@62;=80″)

　　=COUNTIF(C3:C12,″$#@62;=60″)－COUNTIF(C3:C12,″$#@62;=70″)

　　=COUNTIF(C3:C12,″$#@60;60″)

　　输入完毕后，注意一定要以回车确定，即可分别统计出“大于或等于70分而小于80分”(表中为“70～79分”)、“大于或等于60分而小于70分”(表中为“60～69分”)、“小于60分”(表中为“不及格”)，这三个分数段的各自的人数。

　　(二)统计其余各科的参加考试人数、缺考人数、各分数段的人数。

　　如前一期所述，用复制公式的方法，可以快速计算出其余各科的有关数据。以上已经计算出语文科的应考人数、缺考人数及各分数段的人数，选取范围(C19～C25)，把鼠标指向刚才选取的单元格区域的右下方(即填充句柄)，待光标变为小黑十字时，按下鼠标左键，并向右拖动，至G25松开鼠标，各科要统计的结果都出来了。

前两期对班级成绩，分别作了总分、平均分、最高分、最低分、应考人数、缺考人数、分数段等数据统计,这些数据可以用来衡量这个班的成绩的情况。这一期，将首先介绍用函数“RANK”以最快的速度把本班的名次排出来，作为衡量学生个人在本班的学习情况；另外再介绍用“MEDIAN”、“MODE”、“STDEVP”函数分别统计出各科成绩的“中位数”、“众数”、“标准差”，以此衡量各科试题的质量(如试题的难易程度、离散程度等)情况。

25、用函数“RANK”对总分排名次

　　(一)单元格区域的命名

　　先打开上期制作的表格（如图1）， 如果用“总分”来排名次，首先选取所有“总分”数据的单元格区域(H3～H12)， 然后单击菜单中的“插入/名称/定义”，在弹出的“定义名称”对话框中，在“当前工作簿的名称”中输入或修改名称为“总分”；在“引用位置”栏中显示的就是刚才选取的单元格区域(H3～H12)，当然也可以通过单击其右侧的按钮重新选取单元格区域。如果只定义一个名称，则可按“确定”按钮退出；如果还要添加其它区域名称，可单击“添加”按钮，待命名完毕后，再按“确定”按钮结束单元格区域的命名。 在此我们可以比较一下前两期用“选取”和现在用“命名”区域的不同方法及用途：利用“选取”确定区域，预选区域不是固定的，如果需要相对固定的区域，可以利用“命名”，则以后的操作会比较简便，如果对某个区域一旦命名，利用函数的时候，就可以按以下的方法确定单元格的区域，无须再去选取区域了。

　　(二)选取函数确定排名

　　1．在图1的“平均分”右边的单元格（J2）中输入“名次”。

　　2．单击选取单元格J3，再选择“统计”类的“RANK”函数，则在弹出的“粘贴函数”对话框中，一切设置如图2(图中的“H3”是存放第一个学生总分的单元格，“总分”则是刚才命名的单元格区域名称。此时不能在“粘贴函数”对话框中，单击图2中“Ref”右边的按钮去选取单元格区域，否则后面利用复制方法统计其余各人的名次时，单元格的区域会发生变化；利用命名的单元格区域，复制时其区域不会发生变化；如果只看其中一个人的名次，则可以利用“选取”的方法)，单击“确定”按钮，即可得出第一个学生的成绩排名。然后选取单元格J3,拖动其填充句柄至最后一名学生，马上得出全班的成绩排名。而且名次是可以动态变化的，如果某人的某科成绩发生变化，所有排名也会随数据的变化而变化。如果想把名次按从低到高的顺序进行排列，只要先选取范围(J3～J12),然后利用菜单中的“数据/排序”命令，对“名次”进行“递增”排序即可。

26、用“MEDIAN”、“MODE”、“STDEVP”函数分别计算各科成绩的“中位数”、“众数”、“标准差”

　　“中位数”、“众数”、“标准差”是三个“统计类”的函数，也是统计学中三个十分常用的概念，它是分析数据的分布、离散程度等标志的重要依据，下面通过对学生成绩的分布情况，分析每科试题的有关情况。

　　1．计算“语文”的“中位数”

　　先选取存放数据的单元格C26，然后在“统计”类函数中选取函数“MEDIAN”，在弹出的“粘贴函数”对话框中，单击“Nuber1”右边的按钮，选取需计算的单元格区域(C3～C12)，然后单击“粘贴函数”对话框中的“确定”按钮，就可计算出语文科的中位数(在选取的数据中，中位数是它们的平均数)。

　　2．计算“语文”的“众数”、“标准差”

　　利用“MODE”、“STDEVP”两个函数，按照上述的方法，即可计算出“语文”的“众数”、“标准差”。

　　3．计算其余各科的“中位数”、“众数”、“标准差”

　　按照前两期介绍的复制方法，相信各位可以熟练地操作，计算出其余各科的“中位数”、“众数”、“标准差”了，最后结果如图3。

27、数据分析

　　学会使用Excel中的一些常用函数，是为了使我们的工作更科学、更迅速、更轻松。那么上述对成绩统计出来的数据，如何体现为教学服务呢？下面简单介绍一下Excel中“图表”的应用，以便我们对各科的试题进行分析：

　　在Excel中“图表”是反映表格数据的直观表现，通过图表可以非常迅速直观地对数据产生总体上的认识，这正是统计学中，最常用的对数据分布的表现方式。

　　(一)使用“图表向导”建立“语文”分数的分布图表

　　1． 选取图表类型：可以先选取表格中某个空白的单元格，单击菜单中的“插入图表”，在弹出的“图表向导－步骤1”对话框中，选择一种图表类型和子图表类型，如我们选择“折线图”。单击“确定”按钮后，进入“图表向导－步骤2”。

　　2．选择图表源数据：在弹出的步骤2对话框中，单击数据区域右侧的按钮，对话框消失，按前面介绍过的方法选取数据区域(b21～c25),回车确定选取后，单击“下一步”进入“图表向导－步骤3”。

　　3．设置图表选项：在弹出的步骤3对话框中，可以简单设置如图4，单击“下一步”进入“图表向导－步骤4”。

　　4．选择图表位置：在弹出的对话框中，需要选择生成图表放置的位置，此时单击“完成”按钮，把图表嵌在当前的工作表中，图5为完成的“语文”分数分布图表。

　　具体的数据分析留给有兴趣的读者。

　　下期将用一个货物销售的例子，说明总计(SUMIF)和分类汇总(SUBTOTAL)两个函数的用法。　　

前面介绍过求和函数(SUM)，它能对选取区域内的数据进行按行(或列)求和，但在实际应用中，经常需要进行有条件的数据汇总。本期介绍的总计函数(SUMIF)，将为你解决这个问题；另外介绍的分类汇总函数(SUBTOTAL)，可以很容易地计算分类汇总。这两个函数的作用都十分强大，利用它可以简化条件汇总和分类汇总。

　　如图1的样表，是某百货公司下属的四个门市部，都销售同样的商品；假设其中的四个售货员，轮流在四个门市部工作，样表是他们在一段时间内的营业销售情况，(其中的“金额”可以用公式计算，方法是在F2中输入公式“=D2＊E2”进行计算，然后按照前面讲述过的拖动进行复制的方法，即可求出各门市部各种商品的销售金额)。下面将举例说明用总计函数(SUMIF)，分别按门市部、售货员、某一商品、某一范围进行有关的汇总；用分类汇总函数(SUBTOTAL)，分别计算某门市部商品数量的平均值、某门市部的商品数量之和、某门市部货物类别的数目、某门市部最大销售量和最便宜的单价。28、对固定的单元格区域进行命名

　　上期已介绍过对一定的单元格区域进行命名的方法，应该值得注意和学会运用，因为命名单元格区域对灵活运用函数是十分重要的，它将为运用函数带来极大的方便。这里先按上期介绍过的方法，利用菜单中的“插入/名称/定义”命令，在“定义名称”的对话框中，分别添加对如下区域的命名：把“A2～A17”命名为“门市部”，把“B2～B17”命名为“售货员”，把“C2～C17”命名为“类别”，下面的几项依次命名为“数量”、“单价”、“金额”。

28、用总计函数(SUMIF)分别按门市部、售货员、商品类别、某一条件进行有关的汇总

　　1． 按门市部进行汇总

　　以“门市部3”进行金额的汇总为例：按我们都已熟悉的方法，首先选取存放数据的单元格，然后选取函数“SUMIF”，在弹出的“粘贴函数”对话框中，其中的“Range”为选取的区域，这个区域(前面已定义为“门市部”)是下面条件判断的依据、“Criteria”为计算条件，它将判断选取区域中哪些单元格(即“门市部3”)符合计算要求、“Sum_range”为求和区域，这个区域中的数值(“金额”)用于真正的求和。

　　2． 按售货员进行汇总

　　以“刘芳”的销售数量为例，与上述方法一样，只要在“粘贴函数”对话框图中，在“Range”项输入“售货员”，在“Criteria”项中输入“″刘芳″”，在“Sum_range”项中输入“数量”。

　　3． 按商品类别进行汇总

　　以“茶叶”的销售金额为例，只要在“粘贴函数”对话框图中，在“Range”项输入“类别”，在“Criteria”项中输入“″茶叶″”，在“Sum_range”项中输入“金额”。

　　4．按一定条件进行汇总

　　如计算除“矿泉水”之外的货款：在“粘贴函数”对话框图中，在“Range”项输入“类别”，在“Criteria”项中输入“″$#@60;$#@62;矿泉水″”、在“Sum_range”项中输入“金额”。

以上按不同的条件进行了“数量”或“金额”的汇总，如果双击存放“门市部3”金额的单元格，其中的计算公式为：“=SUMIF(门市部,″门市部3″,金额)”，如果需要计算其它门市部的汇总金额，只须把计算公式利用“复制”和“粘贴”命令，复制至相对应的单元格后，把“门市部3”修改为其它门市部即可汇总出其它门市部的金额了。 同样方法，可完成对各“售货员”的汇总、各类商品的汇总。图3是以上各项汇总数据的样表，剩余的各项(如各门市部、各种商品的销售数量)的汇总，大家不妨一试。不难看出，利用函数进行计算时，相对固定的单元格区域命名后，减少了频繁选取单元格区域的次数，给我们操作带来了极大的方便。

29、分类汇总函数(SUBTOTAL)的应用

　　分类汇总函数(SUBTOTAL)不仅仅是一个求和函数，还能够对给定区域内的数值进行其它计算(见图4)，它的语法结构为：

　　SUBTOTAL(function_num,ref1)

　　其中的“function_num”是计算类型编号，为一个1到11的数字，它规定所要进行的计算类型，图4为计算类型编号及具体含义；“ref1”为进行汇总数据的单元格区域。

　　应用举例如下：

　　1． 计算“门市部2”的“数量”平均值

　　选定函数后，弹出“粘贴函数”对话框中，在“Function_num”项中输入计算类型“1”，单击“Ref1”右侧的按钮，选取“门市部2”对应的“数量”单元格区域(D6～D9)，如图5，按“确定”按钮完成。

　　2． 计算“门市部4”中的数量之和

　　与计算“门市部2”的“数量”平均值相似，计算类型(“Function_num”)为“9”，汇总单元格区域(“Ref1”)为“D14:D17”。

　　3． 计算“门市部2”的商品“类别”数目

　　与上例相似的操作，计算类型(“Function_num”)为“2”，汇总单元格区域为“C6:C9”。

　　4． 计算最大的销售数量

　　计算类型(“Function_num”)为“4”，汇总单元格区域(“Ref1”)为“D2:D17”。

　　5． 计算 “门市部2”中的最便宜的单价

　　计算类型(“Function_num”)为“5”，汇总单元格区域(“Ref1”)为“E6:E9”。

　　分类汇总函数的应用是十分灵活的，这是它与使用菜单中“数据/分类汇总”命令的最大差别，如果应用菜单中的分类汇总命令，往往是对于有标题的某个区域而进行的分类汇总，这样的应用有时缺乏必要的灵活性，不利于实际中的灵活运用，所以应该学会利用分类汇总函数进行分类汇总，这在我们的实际工作中是非常有用的。

　　总结：对指定条件的区域进行汇总和分类汇总，这是我们在实际工作中经常遇到的，利用总计函数(SUMIF)和分类汇总函数(SUBTOTAL)一般就能够处理这些工作，而且具有比较强的灵活性。

　　说明：本文转自电脑报电子版，未将实例图片加上，若有不明白的地方，请参照电脑报原文。

 

 

30、IF函数 

　　主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。

　　使用格式：=IF(Logical,Value_if_true,Value_if_false)

　　参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。

　　应用举例：在C29单元格中输入公式：=IF(C26>=18,"符合要求","不符合要求")，确信以后，如果C26单元格中的数值大于或等于18，则C29单元格显示“符合要求”字样，反之显示“不符合要求”字样。特别提醒：本文中类似“在C29单元格中输入公式”中指定的单元格，读者在使用时，并不需要受其约束，此处只是配合本文所附的实例需要而给出的相应单元格，具体请大家参考所附的实例文件。

31. SUMPRODUCT这个函数的意义和用法

   1、该函数的意义是：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和

如：=SUMPRODUCT（A1:A9，B1:B9，C1:C9）=A1*B18C1+A2*B2*C2+......+A9*B9*C9

2、=SUMPRODUCT(D6:AH6,LOOKUP(D6:AH6,{0,600,800},{0.048,0.05,0.052}))

首先，先说LOOKUP(D6:AH6,{0,600,800},{0.048,0.05,0.052})：

1）当D6:Ah6中的数值<600时，返回0.048，

2）当D6:Ah6中的数值大于等于600,但<800时，返回0.05，

3）当D6:Ah6中的数值大于等于800时，返回0.052，

共返回与数组D6:Ah6相同个数的数值即31个数

其次，就是D6:Ah6中的数值依次与刚返回大的31个数值相乘求和即乘积和

3.

=SUM(IF(D6:AH6<600,D6:AH6*0.048,IF(D6:AH6<800,D6:AH6*0.05,IF(D6:AH6>=800,D6:AH6*0.052,0))))

最后这个0没有意义，对结果没有影响

该公式的意义是：

1）如果D6:AH6<600时，D6:AH6*0.048

2）如果D6:AH6>=600且<800时，D6:AH6*0.05

3）如果D6:AH6>=800，D6:AH6*0.052

该范围已包括了(-∞,600),[600,800),[800,∞)的所有范围，所以0没有意义！

33. LOOKUP是个什么函数

  1、LOOKUP函数的意思是：从单行或单列区域或者从一个数组返回值

2、LOOKUP(D6:AH6,{0,600,800},{0.048,0.05,0.052})这里面的0不能缺省的原因是

当0=<D6:AH6<600时，返回0.048，而不是仅仅的D6:AH6<600时，返回0.048，

{0,600,800}属于条件数组，{0.048,0.05,0.052}是结果数组，两者的元素个数应该是对应的，并且条件数组中的的值必须以升序顺序放置，否则，LOOKUP 可能无法提供正确的值。

3、SUMPRODUCT与looukup函数组合应用的时候应当注意：

两者数据的个数应该相等，否则会出现错误。

32. VLOOKUP函数

在表格或数值数组的首列查找指定的数值，并由此返回表格或数组中该数值所在行中指定列处的数值。

这里所说的“数组”，可以理解为表格中的一个区域。数组的列序号：数组的“首列”，就是这个区域的第一纵列，此列右边依次为第2列、3列……。假定某数组区域为B2:E10，那么，B2:B10为第1列、C2:C10为第2列……。

语法：

VLOOKUP（查找值，区域，列序号，逻辑值）

“查找值”：为需要在数组第一列中查找的数值，它可以是数值、引用或文字符串。

“区域”：数组所在的区域，如“B2:E10”，也可以使用对区域或区域名称的引用，例如数据库或数据清单。

“列序号”：即希望区域（数组）中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP 返回错误值 #VALUE!；如果大于区域的列数，函数VLOOKUP返回错误值 #REF!。

“逻辑值”：为TRUE或FALSE。它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值；如果“逻辑值”为FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。如果“查找值”为文本时，“逻辑值”一般应为 FALSE 。另外：

•如果“查找值”小于“区域”第一列中的最小数值，函数 VLOOKUP 返回错误值 #N/A。

•如果函数 VLOOKUP 找不到“查找值” 且“逻辑值”为 FALSE，函数 VLOOKUP 返回错误值 #N/A。

下面举例说明VLOOKUP函数的使用方法。

假设在Sheet1中存放小麦、水稻、玉米、花生等若干农产品的销售单价：

           A                 B

1     农产品名称       单价

2         小麦           0.56

3         水稻           0.48

4         玉米           0.39

5         花生           0.51

…………………………………

100      大豆         0.45

Sheet2为销售清单，每次填写的清单内容不尽相同：要求在Sheet2中输入农产品名称、数量后，根据Sheet1的数据，自动生成单价和销售额。设下表为Sheet2：

         A                 B         C           D  

1    农产品名称     数量     单价     金额  

2    水稻             1000     0.48       480  

3    玉米             2000     0.39       780  

    …………………………………………………  

在D2单元格里输入公式：

=C2*B2  ；

在C2单元格里输入公式：

=VLOOKUP（A2，Sheet1!A2:B100，2，FALSE） 。

如用语言来表述，就是：在Sheet1表A2:B100区域的第一列查找Sheet2表单元格A2的值，查到后，返回这一行第2列的值。

这样，当Sheet2表A2单元格里输入的名称改变后，C2里的单价就会自动跟着变化。当然，如Sheet1中的单价值发生变化，Sheet2中相应的数值也会跟着变化。

其他单元格的公式，可采用填充的办法写入。

33.