下载

1下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 excel+高级教程(高级应用)

excel+高级教程(高级应用).doc

excel+高级教程(高级应用)

Baisance
2010-09-25 0人阅读 举报 0 0 暂无简介

简介:本文档为《excel+高级教程(高级应用)doc》,可适用于职业岗位领域

目录第一节EXCEL公式及函数的高级应用数组公式及其应用…………………………………………………………………数组公式的输入、编辑及删除…………………………………………………一.数组公式的输入………………………………………………………………二.编辑数组公式…………………………………………………………………三.删除数组公式……………………………………………………………………数组公式的应用…………………………………………………………………一.用数组公式计算两个数据区域的乘积…………………………………………二.用数组公式计算多个数据区域的和…………………………………………三.用数组公式同时对多个数据区域进行相同的计算…………………………常用函数及其应用…………………………………………………………………SUM函数、SUMIF函数和SUMPRODUCT函数……………………………一.无条件求和SUM函数……………………………………………………………二.条件求和SUMIF函数……………………………………………………………三.SUMPRODUCT函数……………………………………………………………AVERAGE函数……………………………………………………………………MIN函数和MAX函数…………………………………………………………COUNT函数和COUNTIF函数………………………………………………IF函数……………………………………………………………………………AND函数、OR函数和NOT函数………………………………………………LOOKUP函数、VLOOKUP函数和HLOOKUP函数………………………一.LOOKUP函数……………………………………………………………………二.VLOOKUP函数……………………………………………………………………三.HLOOKUP函数……………………………………………………………MATCH函数…………………………………………………………………INDEX函数………………………………………………………………………一.返回数组中指定单元格或单元格数组的数值………………………………二.返回引用中指定单元格……………………………………………………ADDRESS函数………………………………………………………………INDIRECT函数………………………………………………………………矩阵函数TRANSPOSE函数、MINVERSE函数和MMULT函数……一.TRANSPOSE函数………………………………………………………………二.MINVERSE函数………………………………………………………………三.MMULT函数……………………………………………………………………ROUND函数…………………………………………………………………第二节EXCEL数据分析处理数据排序…………………………………………………………………………数据排序的规则…………………………………………………………………数据排序步骤……………………………………………………………………自定义排序………………………………………………………………………数据的查找与筛选……………………………………………………………记录单查找………………………………………………………………………一.查找数据记录………………………………………………………………二.修改或删除记录…………………………………………………………………三.添加新的记录……………………………………………………………………自动筛选与自定义筛选…………………………………………………………一.自动筛选…………………………………………………………………………二.自定义筛选方式…………………………………………………………………高级筛选……………………………………………………………………………一.一般情况下的高级筛选…………………………………………………………二.计算条件情况下的高级筛选……………………………………………………数据的分类与汇总………………………………………………………………进行分类汇总…………………………………………………………………分类汇总的撤消………………………………………………………………数据透视表………………………………………………………………………建立数据透视表……………………………………………………………………数据的透视分析…………………………………………………………………第节​ EXCEL图表处理图表类型………………………………………………………………………………图表的建立……………………………………………………………………………图表的编辑、修改及格式化…………………………………………………………一.设置坐标、标题、图例等的格式………………………………………………二.改变图表大小……………………………………………………………………三.移动或复制图表…………………………………………………………………四.添加数据标志…………………………………………………………………五.改变图表颜色、图案、边框……………………………………………………地区销售分布图表的建立…………………………………………………………动态图表的建立……………………………………………………………………第四节EXCEL数据分析工具的应用模拟运算表…………………………………………………………………………单变量模拟运算表…………………………………………………………………双变量模拟运算表………………………………………………………………单变量求解………………………………………………………………………规划求解…………………………………………………………………………求解优化问题……………………………………………………………………求解方程组………………………………………………………………………方案分析……………………………………………………………………………建立方案…………………………………………………………………………显示方案……………………………………………………………………………修改、删除或增加方案……………………………………………………………建立方案报告………………………………………………………………………数据分析工具库…………………………………………………………………第一节EXCEL公式及函数的高级应用公式和函数是Excel最基本、最重要的应用工具是Excel的核心因此应对公式和函数熟练掌握才能在实际应用中得心应手。数组公式及其应用数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。在数组公式中使用两组或多组数据称为数组参数数组参数可以是一个数据区域也可以是数组常量。数组公式中的每个数组参数必须有相同数量的行和列。数组公式的输入、编辑及删除一.数组公式的输入数组公式的输入步骤如下:()选定单元格或单元格区域。如果数组公式将返回一个结果单击需要输入数组公式的单元格如果数组公式将返回多个结果则要选定需要输入数组公式的单元格区域。()输入数组公式。()同时按“CrtlShiftEnter”组合键则Excel自动在公式的两边加上大括号{}。特别要注意的是第()步相当重要只有输入公式后同时按“CrtlShiftEnter”组合键系统才会把公式视为一个数组公式。否则如果只按Enter键则输入的只是一个简单的公式也只在选中的单元格区域的第个单元格显示出一个计算结果。在数组公式中通常都使用单元格区域引用但也可以直接键入数值数组这样键入的数值数组被称为数组常量。当不想在工作表中按单元格逐个输入数值时可以使用这种方法。如果要生成数组常量必须按如下操作:()直接在公式中输入数值并用大括号“{}”括起来。()不同列的数值用逗号“,”分开。()不同行的数值用分号“”分开。★输入数组常量的方法:例如要在单元格A:D中分别输入和这个数值则可采用下述的步骤:()选取单元格区域A:D如图所示。图 选取单元格区域A:D()在公式编辑栏中输入数组公式“={,,,}”如图所示。图 在编辑栏中输入数组公式()同时按CtrlShiftEnter组合键即可在单元格A、B、C、D中分别输入了、、、如图所示。假若要在单元格A、B、C、D、A、B、C、D中分别输入、、、、、、、则可以采用下述的方法:图 同时按CtrlShiftEnter组合键得到数组常量()选取单元格区域A:D如图所示。图 选取单元格区域A:D()在编辑栏中输入公式“={,,,,,,}”如图所示。图 在编辑栏中输入数组公式()按CtrlShiftEnter组合键就在单元格A、B、C、D、A、B、C、D中分别输入了、、、和、、、如图所示。图 同时按CtrlShiftEnter组合键得到数组常量★输入公式数组的方法例如在单元格A:D中均有相同的计算公式它们分别为单元格A:D与单元格A:D中数据的和即单元格A中的公式为“=AA”单元格B中的公式为“=BB”…则可以采用数组公式的方法输入公式方法如下:()选取单元格区域A:D如图所示。()在公式编辑栏中输入数组公式“=A:DA:D”如图所示。图 选取单元格区域A:D图 在编辑栏中输入数组公式()同时按CtrlShiftEnter组合键即可在单元格A:D中得到数组公式“=A:DA:D”如图所示。图 同时按CtrlShiftEnter组合键得到数组公式二.编辑数组公式数组公式的特征之一就是不能单独编辑、清除或移动数组公式所涉及的单元格区域中的某一个单元格。若在数组公式输入完毕后发现错误需要修改则需要按以下步骤进行:()在数组区域中单击任一单元格。()单击公式编辑栏当编辑栏被激活时大括号“{}”在数组公式中消失。()编辑数组公式内容。()修改完毕后按“CrtlShiftEnter”组合键。要特别注意不要忘记这一步。三.删除数组公式删除数组公式的步骤是:首先选定存放数组公式的所有单元格然后按Delete键。数组公式的应用一.用数组公式计算两个数据区域的乘积【例】如图所示已经知道个月的销售量和产品单价则可以利用数组公式计算每个月的销售额步骤如下:图 用数组公式计算销售额()选取单元格区域B:M。()输入公式“=B:M*B:M”。()按“CrtlShiftEnter”组合键。如果需要计算个月的月平均销售额可在单元格B中输入公式“=AVERAGE(B:M*B:M)”然后按“CrtlShiftEnter”组合键即可如图所示。在数组公式中也可以将某一常量与数组公式进行加、减、乘、除也可以对数组公式进行乘幂、开方等运算。例如在图中每月的单价相同故我们也可以在单元格B:M中输入公式“=B:M*”然后按“CrtlShiftEnter”组合键在单元格B中输入公式“=AVERAGE(B:M*)”然后按“CrtlShiftEnter”组合键。在使用数组公式计算时最好将不同的单元格区域定义不同的名称如在图中将单元格区域B:M定义名称为“销售量”单元格区域B:M定义名称为“单价”则各月的销售额计算公式为“=销售量*单价”月平均销售额计算公式为“=AVERAGE(销售量*单价)”这样不容易出错。二.用数组公式计算多个数据区域的和如果需要把多个对应的行或列数据进行相加或相减的运算并得出与之对应的一行或一列数据时也可以使用数组公式来完成。【例】某企业年销售的种产品的有关资料如图所示则可以利用数组公式计算该企业年的总销售额方法如下:图 某企业的月销售总额计算()选取单元格区域C:N。()输入公式“=C:N*C:NC:N*C:NC:N*C:N”。()按“CrtlShiftEnter”组合键。三.用数组公式同时对多个数据区域进行相同的计算【例】某公司对现有三种商品实施降价销售产品原价如图所示降价幅度为则可以利用数组公式进行计算步骤如下:图 产品降价计算()选取单元格区域G:I。()输入公式“=B:D*()”。()按CrtlShiftEnter组合键。此外当对结构相同的不同工作表数据进行合并汇总处理时利用上述方法也将是非常方便的。有关不同工作表单元格的引用可参阅第章的有关内容关于数据的合并计算可参阅本章节的内容。常用函数及其应用在第节中介绍了一些有关函数的基本知识本节对在财务管理中常用的一般函数应用进行说明其他有关的专门财务函数将在以后的有关章节中分别予以介绍。SUM函数、SUMIF函数和SUMPRODUCT函数在财务管理中应用最多的是求和函数。求和函数有三个:无条件求和SUM函数、条件求和SUMIF函数和多组数据相乘求和SUMPRODUCT函数。一.无条件求和SUM函数该函数是求个以内参数的和。公式为=SUM(参数参数…参数N)当对某一行或某一列的连续数据进行求和时还可以使用工具栏中的自动求和按钮。例如在例中求全年的销售量则可以单击单元格N然后再单击求和按钮按回车键即可如图所示。图 自动求和二.条件求和SUMIF函数SUMIF函数的功能是根据指定条件对若干单元格求和公式:=SUMIF(range,criteria,sumrange)式中range用于条件判断的单元格区域criteria确定哪些单元格将被相加求和的条件其形式可以为数字、表达式或文本sumrange需要求和的实际单元格。只有当range中的相应单元格满足条件时才对sumrange中的单元格求和。若省略sumrange,则直接对range中的单元格求和。利用这个函数进行分类汇总是很有用的。【例】某商场月份销售的家电流水记录如图所示则在单元格I中输入公式“=SUMIF(C:C,,F:F)”单元格I中输入公式“=SUMIF(C:C,,F:F)”在单元格I中输入公式“=SUMIF(C:C,,F:F)”单元格I中输入公式“=SUMIF(C:C,,F:F)”即可得到分类销售额汇总表。图 商品销售额分类汇总SUMIF函数的对话框如图所示。图 SUMIF函数对话框当需要分类汇总的数据很大时利用SUMIF函数是很方便的。三.SUMPRODUCT函数SUMPRODUCT函数的功能是在给定的几组数组中将数组间对应的元素相乘并返回乘积之和。公式为=SUMPRODUCT(array,array,array,…)式中array,array,array,为至个数组。需注意的是数组参数必须具有相同的维数否则函数SUMPRODUCT将返回错误值#VALUE!。对于非数值型的数组元素将作为处理。例如在例中要计算年产品A的销售总额可在任一单元格(比如O)中输入公式“=SUMPRODUCT(C:N,C:N)”即可。AVERAGE函数AVERAGE函数的功能是计算给定参数的算术平均值。公式为=AVERAGE(参数参数…参数N)函数中的参数可以是数字或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格则忽略其值。但是如果单元格包含零值则计算在内。AVERAGE函数的使用方法与SUM函数相同此处不再介绍。MIN函数和MAX函数MIN函数的功能是给定参数表中的最小值MAX函数的功能是给定参数表中的最大值。公式为=MIN(参数参数…参数N)=MAX(参数参数…参数N)函数中的参数可以是数字、空白单元格、逻辑值或表示数值的文字串。例如MIN()=MAX()=。 COUNT函数和COUNTIF函数COUNT函数的功能是计算给定区域内数值型参数的数目。公式为:=COUNT(参数参数…参数N)COUNTIF函数的功能是计算给定区域内满足特定条件的单元格的数目。公式为:=COUNTIF(rangecriteria)式中 range需要计算其中满足条件的单元格数目的单元格区域criteria确定哪些单元格将被计算在内的条件其形式可以为数字、表达式或文本。COUNT函数和COUNTIF函数在数据汇总统计分析中是非常有用的函数。 IF函数IF函数也称条件函数它根据参数条件的真假返回不同的结果。在实践中经常使用函数IF对数值和公式进行条件检测。公式为=IF(logicaltest,valueiftrue,valueiffalse)式中 logicaltest条件表达式其结果要么为TRUE要么为FALSE它可使用任何比较运算符valueiftruelogicaltest为TRUE时返回的值valueiffalselogicaltest为FALSE时返回的值。IF函数在财务管理中具有非常广泛的应用。【例】例如某企业对各个销售部门的销售业绩进行评价评价标准及各个销售部门在年的销售业绩汇总如图所示评价计算步骤如下:图 销售部门业绩评价()选定单元格区域C:C。()直接输入以下公式:“=IF(B:B<,"差",IF(B:B<,"一般",IF(B:B<,"好",IF(B:B<,"较好","很好"))))”。()按“CrtlShiftEnter”组合键。则各个销售部门的销售业绩评价结果就显示在单元格域C:C中。也可以直接在单元格C中输入公式“=IF(B<,"差",IF(B<,"一般",IF(B<,"好",IF(B<,"较好","很好"))))”后将其向下填充复制到C~C单元格中。AND函数、OR函数和NOT函数这个函数的用法如下:=AND(条件条件…条件N)=OR(条件条件…条件N)=NOT(条件)AND函数表示逻辑与当所有条件都满足时(即所有参数的逻辑值都为真时)AND函数返回TRUE否则只要有一个条件不满足即返回FALSE。OR函数表示逻辑或只要有一个条件满足时OR函数返回TRUE只有当所有条件都不满足时才返回FALSE。NOT函数只有一个逻辑参数它可以计算出TRUE或FALSE的逻辑值或逻辑表达式。如果逻辑值为FALSE函数NOT返回TRUE如果逻辑值为TRUE函数NOT返回FALSE。这个函数一般与IF函数结合使用。【例】某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额若销售额大于元且销售费用占销售额的比例不超过则奖金提取比例为否则为则计算过程如下(如图所示):()在单元格D中输入公式“=IF(AND(B>,CB<),,)”将其向下填充复制到D~C单元格中。()选取单元格区域E:E输入公式“=B:B*D:D”按“CrtlShiftEnter”组合键。则各销售部门的销售奖金提成比例及奖金提取额如图所示。图 奖金提成比例及提取额的计算LOOKUP函数、VLOOKUP函数和HLOOKUP函数一.LOOKUP函数LOOKUP函数的功能是返回向量(单行区域或单列区域)或数组中的数值。函数LOOKUP有两种语法形式:向量和数组。函数LOOKUP的向量形式是在单行区域或单列区域(向量)中查找数值然后返回第二个单行区域或单列区域中相同位置的数值函数LOOKUP的数组形式在数组的第一行或第一列查找指定的数值然后返回数组的最后一行或最后一列中相同位置的数值。()向量形式:公式为=LOOKUP(lookupvalue,lookupvector,resultvector)式中 lookupvalue函数LOOKUP在第一个向量中所要查找的数值它可以为数字、文本、逻辑值或包含数值的名称或引用lookupvector只包含一行或一列的区域lookupvector的数值可以为文本、数字或逻辑值resultvector为只包含一行或一列的区域其大小必须与lookupvector相同。()数组形式:公式为=LOOKUP(lookupvalue,array)式中 array包含文本、数字或逻辑值的单元格区域或数组它的值用于与lookupvalue进行比较。例如:LOOKUP(,{,,,,})=。注意:lookupvector的数值必须按升序排列否则函数LOOKUP不能返回正确的结果。文本不区分大小写。如果函数LOOKUP找不到lookupvalue则查找lookupvector中小于或等于lookupvalue的最大数值。如果lookupvalue小于lookupvector中的最小值函数LOOKUP返回错误值#NA。二.VLOOKUP函数VLOOKUP函数的功能是在表格或数值数组的首列查找指定的数值并由此返回表格或数组当前行中指定列处的数值。公式为:=VLOOKUP(lookupvaluetablearraycolindexnumrangelookup)式中 lookupvalue需要在数据表第一列中查找的数值lookupvalue可以为数值、引用或文字串tablearray需要在其中查找数据的数据表可以使用对区域或区域名称的引用例如数据库或数据清单如果rangelookup为TRUE则tablearray的第一列中的数值必须按升序排列否则函数VLOOKUP不能返回正确的数值如果rangelookup为FALSEtablearray不必进行排序。tablearray的第一列中的数值可以为文本、数字或逻辑值且不区分文本的大小写colindexnumtablearray中待返回的匹配值的列序号colindexnum为时返回tablearray第一列中的数值colindexnum为时返回tablearray第二列中的数值以此类推。如果colindexnum小于函数VLOOKUP返回错误值#VALUE!如果colindexnum大于tablearray的列数函数VLOOKUP返回错误值#REF!。rangelookup逻辑值指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果其为TRUE或省略则返回近似匹配值也就是说如果找不到精确匹配值则返回小于lookupvalue的最大数值如果rangevalue为FALSE函数VLOOKUP将返回精确匹配值。如果找不到则返回错误值#NA。VLOOKUP函数在财务管理与分析中是一个经常用到的函数因此熟悉它将会带来很大便利。在以后的有关章节中会经常用到它。例如假设单元格A:A中的数据分别为、、和单元格B:B中的数据分别为、、和则有:VLOOKUP(,A:B,)=VLOOKUP(,A:B,)=VLOOKUP(,A:B,)=VLOOKUP(,A:B,)=。三.HLOOKUP函数HLOOKUP函数的功能是从表格或数值数组的首行查找指定的数值并由此返回表格或数组当前列中指定行处的数值。公式为:=(lookupvaluetablearrayrowindexnumrangelookup)式中 rowindexnumtablearray中待返回的匹配值的行序号。rowindexnum为时返回tablearray第一行的数值rowindexnum为时返回tablearray第二行的数值以此类推。如果rowindexnum小于函数HLOOKUP返回错误值#VALUE!如果rowindexnum大于tablearray的行数函数HLOOKUP返回错误值#REF!。式中的其他参数含义参阅VLOOKUP函数。HLOOKUP函数与VLOOKUP函数的区别是:当比较值位于数据表的首行并且要查找下面给定行中的数据时使用函数HLOOKUP当比较值位于要进行数据查找的左边一列时使用函数VLOOKUP。VLOOKUP函数在首列进行检索先得到的是行号然后根据colindexnum参数指定的列标返回指定的单元格数值而HLOOKUP函数在首行进行检索先得到的是列标然后根据rowindexnum参数指定的行号返回指定的单元格数值。 MATCH函数MATCH函数的功能是返回在指定方式下与指定数值匹配的数组中元素的相应位置。公式为:=MATCH(lookupvalue,lookuparray,matchtype)式中 lookupvalue需要在数据表中查找的数值可以是数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用lookuparray可能包含所要查找的数值的连续单元格区域可以是数组或数组引用matchtype数字、或它指明Excel如何在lookuparray中查找lookupvalue。查找方式如下:当matchtype为时lookuparray必须按降序排列函数MATCH查找大于或等于lookupvalue的最小数值当matchtype为时lookuparray可以按任何顺序排列函数MATCH查找等于lookupvalue的第一个数值当matchtype为时lookuparray必须按升序排列函数MATCH查找小于或等于lookupvalue的最大数值。例如MATCH(,{,,,},)=MATCH(,{,,,})=。EXCEL公式及函数的高级应用()INDEX函数INDEX函数的功能是返回表格或区域中的数值或对数值的引用。INDEX函数有以下两种形式:​ 返回数组中指定单元格或单元格数组的数值。公式为=INDEX(array,rownum,columnnum) 式中 array单元格区域或数组常数rownum数组中某行的行序号函数从该行返回数值。如果省略rownum则必须有columnnumcolumnnum数组中某列的列序号函数从该列返回数值。如果省略columnnum则必须有rownum。需要注意的是:如果同时使用rownum和columnnum函数INDEX返回rownum和columnnum交叉处的单元格的数值。如果数组只包含一行或一列则相对应的参数rownum或columnnum为可选。如果数组有多行和多列但只使用rownum或columnnum函数INDEX返回数组中的整行或整列且返回值也为数组。如果将rownum或columnnum设置为函数INDEX则分别返回整个列或行的数组数值。如果需要使用以数组形式返回的数值时请在一个水平单元格区域中将函数INDEX作为数组公式输入。此外rownum和columnnum必须指向array中的某一单元格否则函数INDEX返回错误值#REF!。例如:INDEX({,,},,)=。如果作为数组公式输入则:INDEX({,,},,)={}​ 返回引用中指定单元格。公式为:INDEX(reference,rownum,columnnum,areanum)式中 reference对一个或多个单元格区域的引用如果为引用输入一个不连续的选定区域必须用括号括起来。如果引用中的每个区域只包含一行或一列则相应的参数rownum或columnnum分别为可选项。例如对于单行的引用可以使用函数INDEX(reference,columnnum)。rownum引用中某行的行序号函数从该行返回一个引用columnnum引用中某列的列序号函数从该列返回一个引用areanum选择引用中的一个区域并返回该区域中rownum和columnnum的交叉区域。选中或输入的第一个区域序号为第二个为以此类推。如果省略areanum函数INDEX使用区域。说明:rownum、columnnum和areanum必须指向reference中的单元格,否则函数INDEX返回错误值#REF!。如果省略rownum和columnnum函数INDEX返回由areanum所指定的区域。函数INDEX的结果为一个引用且在其他公式中也被解释为引用。根据公式的需要函数INDEX的返回值可以作为引用或是数值。例如公式CELL("width",INDEX(A:B,,))等价于公式CELL("width",B)。CELL函数将函数INDEX的返回值作为单元格引用。而在另一方面公式*INDEX(A:B,,)将函数INDEX的返回值解释为B单元格中的数字。 ADDRESS函数ADDRESS函数的功能是按照给定的行号和列标建立文本类型的单元格地址。公式为=ADDRESS(rownum,columnnum,absnum,a,sheettext)式中 rownum在单元格引用中使用的行号columnnum在单元格引用中使用的列标absnum指明返回的引用类型其中:当为或省略时为绝对引用当为时为绝对行号相对列标当为时为相对行号绝对列标当为时为相对引用a用以指明A或RC引用样式的逻辑值。如果A为TRUE或省略函数ADDRESS返回A样式的引用如果A为FALSE函数ADDRESS返回RC样式的引用sheettext一文本指明作为外部引用的工作表的名称如果省略sheettext则不使用任何工作表名。例如ADDRESS(,)等于“$C$”ADDRESS(,,)等于“C$”。 INDIRECT函数INDIRECT函数的功能是返回由文字串指定的引用。此函数立即对引用进行计算并显示其内容。当需要更改公式中单元格的引用而不更改公式本身时可使用此函数。公式为:=INDIRECT(reftext,a)式中 reftext对单元格的引用此单元格可以包含A样式的引用、RC样式的引用、定义为引用的名称或对文字串单元格的引用如果reftext不是合法的单元格的引用函数INDIRECT返回错误值#REF!a逻辑值指明包含在单元格reftext中的引用的类型如果a为TRUE或省略reftext被解释为A样式的引用如果a为FALSEreftext被解释为RC样式的引用。需要注意的是如果reftext是对另一个工作簿的引用(外部引用)则那个工作簿必须被打开。如果源工作簿没有打开函数INDIRECT返回错误值#REF!。例如:如果单元格A包含文本"B"且单元格B包含数值则:INDIRECT($A$)=。上述介绍的几个查找函数LOOKUP、VLOOKUP、HLOOKUP、MATCH、INDEX、ADDRESS、INDIRECT等在财务分析与决策、预测及建立动态图表等中是非常有用的。矩阵函数TRANSPOSE函数、MINVERSE函数和MMULT函数一.TRANSPOSE函数TRANSPOSE函数的功能是求矩阵的转置矩阵。公式为=TRANSPOSE(array)式中Array需要进行转置的数组或工作表中的单元格区域。函数TRANSPOSE必须在某个区域中以数组公式的形式输入该区域的行数和列数分别与array的列数和行数相同。【例】假设矩阵A中的值如图中单元格区域A:C求其转置矩阵的步骤如下:图 求转置矩阵()选取存放转置矩阵结果的单元格区域如E:H。()单击工具栏上的【粘贴函数】按钮在【粘贴函数】对话框中选取函数TRANSPOSE在该函数对话框中输入(可用鼠标拾取)单元格A:C按“CrtlShiftEnter”组合键即得转置矩阵如图所示。利用TRANSPOSE函数可以把工作表中的某些行(或列)排列的数据转换成列(或行)排列的数据。例如由于工作需要要把工作表中的某些行数据改为列数据若一个一个地改动数据将是很麻烦也很费时的而利用TRANSPOSE函数则可以很轻松地进行这项工作。但需要注意的是利用TRANSPOSE函数对行(列)数据进行转换则无法单独修改其中转换单元格区域中的某单元格的数据。二.MINVERSE函数MINVERSE函数的功能是返回矩阵的逆矩阵。公式为=MINVERSE(array)式中array具有相等行列数的数值数组或单元格区域。MINVERSE函数的使用方法与TRANSPOSE函数是一样的。在求解线性方程组时常常用到MINVERSE函数。三.MMULT函数MMULT函数的功能是返回两数组的矩阵乘积。结果矩阵的行数与array的行数相同列数与array的列数相同。公式为=MMULT(array,array)式中 array,array要进行矩阵乘法运算的两个数组。array的列数必须与array的行数相同而且两个数组中都只能包含数值。array和array可以是单元格区域、数组常数或引用。如果单元格是空白单元格或含有文字串或是array的行数与array的列数不相等时则函数MMULT返回错误值#VALUE!。同样地由于返回值为数组公式故必须以数组公式的形式输入。以例的原矩阵和其转置矩阵为例它们的乘积矩阵求解方法如下:()选取存放乘积矩阵结果的单元格区域如J:L。()单击工具栏上的【粘贴函数】按钮在【粘贴函数】对话框中选取函数MMULT在该函数对话框中的array栏中输入(可用鼠标拾取)单元格区域A:C在array栏中输入单元格区域E:H然后按“CrtlShiftEnter”组合键即得矩阵的乘积如图所示。 ROUND函数ROUND函数的功能是返回某个数字按指定位数舍入后的数字。公式为=ROUND(number,numdigits)式中 number需要进行舍入的数字numdigits指定的位数按此位数进行舍入。如果numdigits大于则舍入到指定的小数位如果numdigits等于则舍入到最接近的整数如果numdigits小于则在小数点左侧进行舍入。利用ROUND函数可以防止利用格式工具栏上的【增加小数位数】或【减少小数位数】所带来的看起来“假数据”问题的出现使得工作表上显示的数据真实可靠。实际上如果需要调整数据的小数位数最好使用ROUND函数而不要使用格式工具栏上的【增加小数位数】或【减少小数位数】按钮。例如若单元格A中的数据为若使用格式工具栏上的【减少小数位数】按钮将小数位数设为两位则单元格A中的数据显示为看起来似乎单元格A的数据为但实际上仍为。若在单元格B中输入公式“=*A”则单元格B中的数据显示为也许“不明真相”的人认为单元格B的数据算错了(乘以应该等于)但实际上单元格的数据为这种看起来的“假”数据可能会对实际工作带来不便。因此正确的方法应是:单元格B中应输入公式“=ROUND(*ROUND(A,),)”结果为即先将单元格A的数据用函数ROUND四舍五入然后再对计算后的数据四舍五入。第节​ EXCEL数据分析处理Excel提供了强大的数据分析处理功能利用它们可以实现对数据的排序、分类汇总、筛选及数据透视等操作。在进行数据分析处理之前首先必须注意以下几个问题:()避免在数据清单中存在有空行和空列。()避免在单元格的开头和末尾键入空格。()避免在一张工作表中建立多个数据清单每张工作表应仅使用一个数据清单。()工作表的数据清单应与其他数据之间至少留出一个空列和一个空行以便于检测和选定数据清单。()关键数据应置于数据清单的顶部或底部。数据排序数据排序的规则Excel允许对字符、数字等数据按大小顺序进行升序或降序排列要进行排序的数据称之为关键字。不同类型的关键字的排序规则如下:数值:按数值的大小。字母:按字母先后顺序。日期:按日期的先后。汉字:按汉语拼音的顺序或按笔画顺序。逻辑值:升序时FALSE排在TRUE前面降序时相反。空格:总是排在最后。数据排序步骤()单击数据区中要进行排序的任意单元格。()单击【数据】菜单选择【排序】项系统将弹出【排序】对话框如图所示。图 【排序】对话框()在【排序】对话框中用下拉列表框选择要排序的关键字关键字有“主要关键字”、“次要关键字”和“第三关键字”根据需要分别选择不同的关键字()单击【确定】按钮数据就按要求进行了排序。当只有一个关键字时可以单击工具栏上的升序按钮或降序按钮进行自动排序。自定义排序在有些情况下对数据的排序顺序可能非常特殊既不是按数值大小次序、也不是按汉字的拼音顺序或笔画顺序而是按照指定的特殊次序如对总公司的各个分公司按照要求的顺序进行排序按产品的种类或规格排序等等这时就需要自定义排序。利用自定义排序方法进行排序首先应建立自定义序列其方法可参阅第章的有关内容。建立好自定义序列后即可对数据进行排序方法是:单击数据区中要进行排序的任意单元格单击【数据】菜单选择【排序】项在弹出的【排序】对话框中单击【选项】按钮系统弹出【排序选项】对话框如图所示在【自定义排序次序】的下拉列表中选择前面建立的自定义序列然后单击【确定】按钮即可对数据进行自定义排序。图 【排序选项】对话框数据的查找与筛选企业的管理人员经常需要在数据库或数据清单众多的数据中找出需要的数据Excel提供了功能强大的数据查找与筛选工具。数据查找是指从原始数据中提取满足条件的数据记录源数据不会改变也不会被隐藏数据筛选是指把数据库或数据清单中所有不满足条件的数据记录隐藏起来只显示满足条件的数据记录。常用的数据查找与筛选方法有:记录单查找、自动筛选和高级筛选。下面结合实例说明各种查找方法的具体应用。【例】图为某公司的部分商品销售记录清单。图 某公司的商品销售明细清单根据图中的有关资料可以分别采用记录单查找、自动筛选或高级筛选的方式查找或选择所需要的信息如下所述:记录单查找记录单是查找和编辑数据的最简单的方法利用记录单不仅可以查找数据记录还可以修改和删除记录、添加新的数据记录等。一.查找数据记录利用记录单查找数据记录的步骤如下:()用鼠标单击数据清单或数据库中的任一非空单元格。()单击【数据】菜单选择【记录单】项则系统弹出如图所示的记录单。图 记录单()单击记录单中的【条件】按钮则弹出记录单条件对话框如图所示。图 记录单条件对话框()输入条件比如要查找“张三”的销售记录则在【销售人员】栏中输入“张三”然后单击【上一条】按钮或【下一条】按钮系统就逐次显示满足条件的记录行。还可以使用多个条件联合查找记录此处不再叙述。二.修改或删除记录在图所示的记录单中即可对某一记录的各字段进行修改。若要删除显示的记录只需单击记录单上的【删除】按钮即可。三.添加新的记录在图所示的记录单中单击记录单上的【新建】按钮则出现各字段均为空白的新建记录单在记录单中输入各字段的值输入完毕后单击【新建】按钮即完成添加新记录。自动筛选与自定义筛选一.自动筛选记录单检索数据每次只能显示一个数据行当查询的数据较多或要把查询的结果汇总成表时就需要使用筛选工具了。自动筛选提供了快速检索数据清单或数据库的方法通过简单的操作就能筛选出需要的数据。利用自动筛选查找数据的步骤如下:()用鼠标单击数据清单或数据库中的任一非空单元格。()单击【数据】菜单选择【筛选】项在【筛选】子菜单中选择【自动筛选】则系统自动在数据清单的每列数据的标题旁边添加一个下拉列标标志如图所示。图 自动筛选的下拉列表标志()单击需要筛选的下拉列表系统显示出可用的筛选条件从中选择需要的条件即可显示出满足条件的所有数据。例如要查找所有彩电的销售记录单击“商品”右边的下拉列表从中选择“彩电”项则所有的彩电销售记录就显示出来而其他的数据则被隐藏如图所示。图 彩电销售清单的筛选结果如果有关彩电的销售记录很多超过了个当需要只显示个记录时可单击“单价”、“数量”、“金额”等右边的下拉列表中的“前个”项系统弹出【自动筛选前个】对话框如图所示。这里在【显示】下拉列表中“最大”表示最大(最好)的前个记录“最小”表示最小(最差)的前个记录。中间的编辑框中的数值表示显示的记录行数系统默认值为但可以修改根据需要输入数值即可。图 【自动筛选前个】对话框若要恢复所有的记录则单击“商品”右边的下拉列表中的“全部”项。若要取消【自动筛选】状态则单击【数据】菜单选择【筛选】项在【筛选】子菜单中再次选择【自动筛选】。二.自定义筛选方式当在图所示的下拉列表中选择“自定义”项时则会弹出【自定义自动筛选方式】对话框如图所示用户可根据具体条件对各栏进行设置。如要查找销售金额大于或等于“”且小于或等于“”的所有记录则单击左上角的下拉箭头选择“大于或等于”右上角的条件值输入“”单击左下角的下拉箭头选择“小于或等于”右下角的条件值输入“”单击【确定】按钮并选择“与”条件则满足这些条件的所有记录就显示出来了如图所示。图 【自定义自动筛选方式】对话框图 【自定义自动筛选方式】筛选的结果高级筛选高级筛选可以使用较多的条件来对数据清单进行筛选这些条件既可以是与条件也可以是或条件或与条件与或条件的组合使用还可以使用计算条件。一.一般情况下的高级筛选利用高级筛选对数据清单进行筛选的步骤如下:()首先应建立一个条件区域。在条件区域中同一行中的条件是与条件也就是这些条件必须同时满足不同行中的条件是或条件也就是这些条件只要满足其一即可。如需要查找张三销售彩电的所有记录则建立条件区域如图所示。图 建立条件区域()单击数据清单或数据库中的任一非空单元格然后单击【数据】菜单选择【筛选】子菜单中的【高级筛选】项则系统弹出如图所示的【高级筛选】对话框。图 【高级筛选】对话框()一般情况下系统将自动给出了数据区域用户只需在【条件区域】栏中输入条件区域(本例中为B:C也可以用鼠标拾取单元格区域此时在条件区域中将显示“销售明细清单!$B$:$C$”。()高级筛选结果可以显示在数据清单的原有区域中也可以显示在工作表的其他空白单元格区域系统默认的方式是在数据清单的原有区域中显示结果。若需要在工作表的其他空白单元格区域显示结果则

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/33

excel+高级教程(高级应用)

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利