计算机办公软件Excel公式函数与图表

计算机办公软件Excel公式、函数与图表宋杰鲲®经管学院管理科学与工程系目录第一讲Excel公式基础知识第二讲Excel工作表函数简介第三讲逻辑函数第四讲文本函数第五讲数学函数第六讲计数函数第七讲查找函数第八讲财务函数第九讲数据库和列表第十讲Excel图表第一讲Excel公式基础知识1.公式元素运算符：包括一些符号，例如加号、减号等；单元格引用：包括命名的单元格和范围、指向当前工作表的单元格、同一工作簿其他工作表中的单元格，甚至可以是其他工作簿工作表上的单元格；值或字符串：例如可以是7.5或“年末结果”；工作表函数和它们的参数：包括一些函数，例如Sum或Average及它们的参数；括号：它们控制着公式中各表达式被处理的次序。例如：=(E5*8+Sum(E6:E10))/3，=“平均值为:”&Average(E6:E10)第一讲Excel公式基础知识2.在公式中使用运算符运算符是公式的基本元素，一个运算符就是一个符号。Excel支持以下运算符（按照优先级）：-（负号）%（百分比）^（求幂）*和/（乘和除）+和-（加和减）&（文本连接）=,,,=,=,（比较）例如：=-5%^3*7+6&”3”2Excel还支持一些其他类型的运算符，被称为引用运算符。所谓引用运算符，即指与单元格引用一同使用的运算符，如范围运算符“:”、联合运算符“,”。例如：=average(E3:E5,E7)第一讲Excel公式基础知识3.使用运算符的公式实例连接两个文本字符串，产生一个新字符串，如“=”Part”&”A””；将两个单元格的内容连接在一起，如“=A1&B1”；使用幂运算符计算三次方或开三次根，如“=A1^3”、“=A1^(1/3)”；如果单元格A1的值小于单元格A2的值，公式“=A1A2”，可以返回True，否则返回False；如果单元格A1的值小于等于单元格A2的值，公式“=A1=A2”，可以返回True，否则返回False；第一讲Excel公式基础知识3.使用运算符的公式实例如果单元格A1的值不等于单元格A2的值，公式“=A1A2”，可以返回True，否则返回False；Excel没有逻辑AND和OR运算符，只能使用函数AND和OR来确定这些类型的逻辑运算符功能。例如，如果单元格A1大于50或者小于10，公式“=OR(A150,A110)”返回True，否则返回False；如果单元格A1大于10而且小于50，公式“=AND(A110,A150)”返回True，否则返回False。第一讲Excel公式基础知识4.单元格和范围引用大多数公式都会使用单元格或范围地址引用一个或多个单元格，单元格引用有四种类型，分别使用美元符号加以区别。按F4键进行切换。5.隐藏公式选中公式或多个公式；选择[格式][单元格]，在打开的“单元格格式”对话框中选择“保护”选项卡，核实其中的“隐藏”；选择[工具][保护][保护工作表]命令，来保护工作表。如果不希望他人解除保护工作表选项，还可以在“保护工作表”对话框中设置密码。第二讲Excel工作表函数简介所谓工作表函数就是指在公式中可以使用的一种内部工具。使用工作表函数有如下几项功能：一是可以简化公式，如Average函数；二是可以实现其他方法无法实现的计算，如Max函数；三是提高编辑任务的速度，如Proper函数；四是实现判断功能，如If函数，If(A110000,A1*5%,A1*7.5%)。所有的函数都需要括号，括号中的内容就是函数的参数。一个函数可以不带参数，如rand()；可以只带一个、固定数量、不确定数量的参数；也可以带可选参数。具体包括：名字、整行或整列、文字值、表达式、其他函数、数组等。第二讲Excel工作表函数简介函数类型包括财务函数、日期及时间函数、数学及三角函数、统计函数、查看和引用函数、数据库函数、文本函数、逻辑函数、信息函数、工程函数、用户定义函数等11种函数。其中，用户定义函数是指使用VBA创建的自定义工作表函数，这些函数不能在“粘贴函数”对话框和公式选项板中对每个参数进行描述。第三讲逻辑函数1.And逻辑函数：And(logical1,logical2,…)当所有的条件均为“真”(True)时，返回“True”；否则返回“False”。2.Or逻辑函数：Or(logical1,logical2,…)当所有的函数都为“假”(False)时，返回“False”；否则返回“True”。3.Not逻辑函数：Not(logical)当参数为“真”时，返回“假”(False)；否则返回“真”(True)。4.If逻辑函数：If(logical_test,value_if_true,value_if_false)If函数可嵌套7层关系式，可以构造复杂的判断条件从而进行综合测评。逻辑第四讲文本函数1.确定两个字符串是否相同可以采用逻辑公式，如“=A1=A2”。但是，这种方法对字母大小写并不敏感。采用Exact函数，如“=Exact(A1,A2)”，可以精确比较两个文本是否完全相同。2.从字符串中提取字符Left：从一个字符串的起始返回确定数量的字符，如“=Left(A1,3)”；Right：从字符串的结尾返回确定数量的字符，如“=Right(A1,4)”；Mid：在字符串中间的任意位置上返回确定数量的字符，格式为：Mid(text,start_num,num_chars)，如“=Mid(A1,2,5)”。文本第四讲文本函数3.统计字符串长度：Len(text)，如Len(370502)。4.替换文本中的字符Replace(old_text,start_num,num_chars,new_text)5按照指定的次数重复显示文本Pept(text,number_times)6.在文本字符串中用新文本替换旧文本Substitute(text,old_text,new_text,instance_num)7.将数值转换为美元/人民币格式文本Dollar(number,decimals)：转换为美元；RMB(number,decimals)：转换为人民币。第五讲数学函数1.绝对值函数：ABS(number)2.对数值进行向上舍入：Ceiling(number,significance)3.取整函数：Int(number)4.求余函数：Mod(number,divisor)5.乘积函数：Product(number1,number2,…)6.乘幂函数：Power(number,power)“^”运算符数学第五讲数学函数7.四舍五入函数：Round(number,num_digits)8.随机函数：Rand()：返回一个大于0小于1的随机数。Randbetween(bottom,top)9.求和函数：Sum(number1,number2,…)SumIf(range,criteria,sum_range)Sumproduct(array1,array2,…)数组是一些元素的简单集合。在Excel中，数组可以是一维的，也可以是二维的。一维数组存储在一行（横向数组）或者一列（纵向数组）的范围内，二维数组可以存储在一个矩形的单元格内。Excel不支持三维数组，但是它的VBA语言支持三维数组。实际上，一个数组不必保存在单元格范围内。这种数组被保存在内存里，被称为数组常数。列出数组的元素，并且用大括号括起来，这样就创建了一个数组常数。如下面是一个拥有5个元素的横向数组：{1,2,3,4,5}。要在一个范围内显示这个数组，可以在一行上选中五个连续的单元格，然后输入={1,2,3,4,5}，并且按Ctrl+Shift+Enter键即可。第五讲数学函数一维纵向数组中的元素用分号分开。二维数组用逗号分开它的横向元素，用分号分开它的纵向元素。①直接给定一个数组时，必须用括号把数据元素括起来。而另一方面，输入一个数组公式时，不要使用括号，而应该是按Ctrl+Shift+Enter键。②如果数组公式的结果包含多个值，必须选择结果范围内的所有单元格，否则只会返回结果的第一个元素。10.可以对数组进行运算，如每一个数组元素乘以2；使用函数，如平方。11.数组的转置：Transpose(array)。10.矩阵的逆：Minverse(array)12.矩阵的乘积：Mmult(array1,array2)第五讲数学函数第六讲计数函数（1）Count：返回范围中包含数字值的单元格的数量；（2）CountA：返回范围中非空的单元格的数量；（3）CountBlank：返回范围中空单元格数量；（4）CountIf：返回范围中符合筛选条件的单元格数量。CountIf函数对单筛选条件计数公式非常有用，其格式为：CountIf(range,criteria)其中，range为包含值的范围；criteria为筛选条件。筛选条件可以为常数、表达式、函数、单元格引用，甚至还可以是一些通配符（*和？）等。计数第六讲计数函数CountIf(range,12)、CountIf(range,”0”)、CountIf(range,”0”)、CountIf(range,A1)、CountIf(range,””&A1)；CountIf(range,”*”)：返回包含文本的单元格数量；CountIf(range,”???”)：返回包含三个字符的文本单元格数量；CountIf(range,”sjk”)：返回为单词sjk（不分大小写）的单元格的数量；CountIf(range,”*sjk*”)：返回在文本的任何位置上包含sjk（不分大小写）的单元格数量；第六讲计数函数CountIf(range,”A*”)：返回包含以字母A开头（不分大小写）文本的单元格数量；CountIf(range,””&Average(range))：返回大于平均值的单元格数量；CountIf(range,””&Average(range)+Stdev(range)*3)：返回平均值上面超过三倍标准误差的值的数量；CountIf(range,3)+CountIf(range,-3)：返回值为3或-3的单元格数量。第七讲查找函数1.Vlookup：垂直查找查找表格第一列中的某个值，并返回这个值，放在表格中指定列的相同行中。格式如下：Vlookup(lookup_value,table_array,col_index_num,range_lookup)lookup_value:查找值在查找表的第一列；table_array:包含查找表的范围；col_index_num:匹配值存在的表中的列编号；range_lookup:可选项目。如果出现TRUE或被忽略，可以显示一个近似的匹配值（如果具体匹配值没有发现，可以返回下一个低于查找值的最大值）。如果出现False，Vlookup将查找具体匹配值。如果这个函数没有找到具体匹配值，函数将返回#N/A结果。查找第七讲查找函数2.Hlookup：水平查找在表格的顶行中查找一个值，并在表格中指定的行的相同列中返回这个值。格式如下：Hlookup(lookup_value,table_array,row_index_num,range_lookup)lookup_value:查找值在查找表的第一行；table_array:包含查找表的范围；col_index_num:匹配值存在的表中的行编号；range_lookup:可选项目。如果出现TRUE或被忽略，可以显示一个近似的匹配值（如果具体匹配值没有发现，可以返回下一个低于查找值的最大值）。如果出现False，Hlookup将查找具体匹配值。如果这个函数没有找到具体匹配值，函数将返回#N/A结果。第七讲查找函数3.Lookup：