EXCEL信息技术计算工具教程

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,-,*,-,第,1,章 计算工具,EXCEL,更有效地使用电子表软件,本章总结了,Excel,中一些很重要但又容易为人们所忽视的使用技巧和运算工具，特别是强调了本书后续内容里经常用到的一些方法。,Excel,金融计算专业教程,1.1,数,数据,输,输入,与,与运,算,算,1.3,内置,函,函数,和,和自,定,定义,函,函数,1.3.1,函数,的,的使,用,用,1.3.2,统计,函,函数,1.3.3,金融,函,函数,1.3.4,自定,义,义函,数,数,1.4,假设,分,分析,工,工具,1.4.1,方案,管,管理,器,器,1.4.2,模拟,运,运算,表,表,1.4.3,单变,量,量求,解,解,1.4.4,规划,求,求解,1.1,数据,输,输入,与,与运,算,算,1.1.1,数据,的,的导,入,入,1.1.2,数据,运,运算,与,与引,用,用,1.1.3,排序,与,与筛,选,选,1.2,图表,和,和数,据,据透,视,视表,1.2.1,图表,类,类型,1.2.2,组合,图,图表,1.2.3,趋势,线,线,1.2.4,数据,透,透视,表,表,2,1.1.1,数,数据,的,的导,入,入,在,Excel,环境,下,下进,行,行计,算,算，,所,所需,要,要的,数,数据,大,大致,有,有,3,种来,源,源：,手,手工,输,输入,、,、自,动,动生,成,成和,从,从外,部,部导,入,入。,当,当数,据,据量,很,很小,而,而且,又,又没,有,有规,律,律的,情,情况,下,下，,一,一般,采,采用,手,手工,输,输入,的,的方,法,法；,而,而对,于,于那,些,些有,规,规律,的,的数,据,据，,如,如连,续,续的,数,数字,或,或字,符,符序,列,列可,以,以用,Excel,的填,充,充命,令,令自,动,动生,成,成。,当,当数,据,据量,比,比较,大,大或,有,有现,成,成的,数,数据,来,来源,如,如网,络,络资,源,源、,数,数据,库,库资,源,源等,可,可供,应,应用,，,，就,需,需要,采,采用,导,导入,的,的方,法,法。,从,Excel,菜单,上,上的,“,“文,件,件”,“,打开,”,”命,令,令处,，,，就,可,可以,直,直接,读,读入,数,数据,库,库文,件,件、,Web,文件,、,、,XML,文件,、,、文,本,本文,件,件以,及,及其,他,他电,子,子表,格,格式,的,的文,件,件等,。,。,从外,部,部导,入,入数,据,据的,另,另一,种,种方,法,法是,建,建立,数,数据,查,查询,。,。根,据,据数,据,据源,的,的不,同,同，,查,查询,可,可以,分,分成,“,“,Web,查询,”,”和,“,“数,据,据库,查,查询,”,”两,大,大类,。,。,3,1.1.1,数,数据,的,的导,入,入,从,Web,上导,入,入少,量,量数,据,据的,简,简单,方,方法,：,：,第一,种,种方,法,法是,在,在浏,览,览器,中,中右,击,击所,需,需要,的,的数,据,据，,从,从快,捷,捷菜,单,单中,执,执行,“,“复,制,制”,(,快捷,键,键,Ctrl+C),选项,，,，然,后,后在,Excel,工作,表,表中,选,选中,要,要放,置,置数,据,据的,位,位置,(,可以,选,选中,一,一个,或,或一,组,组单,元,元格,),，从,菜,菜单,或,或常,用,用工,具,具栏,中,中执,行,行“,粘,粘贴,”,”命,令,令,(,快捷,键,键,Ctrl+V),，就,可,可以,把,把,Web,页上,的,的数,据,据拷,贝,贝到,工,工作,表,表中,。,。,第二,种,种方,法,法适,合,合于,导,导入,Web,页上,的,的表,格,格数,据,据，,在,在,IE,浏览,器,器中,打,打开,数,数据,所,所在,页,页面,，,，在,所,所需,要,要数,据,据所,在,在的,表,表格,处,处点,击,击鼠,标,标右,键,键，,从,从快,捷,捷菜,单,单中,执,执行,“,“导,出,出到,MicrosoftExcel”,，无,论,论此,时,时,Excel,是否,打,打开,，,，操,作,作系,统,统会,自,自动,在,在,Excel,中建,立,立一,个,个空,白,白工,作,作簿,文,文件,，,，将,数,数据,输,输入,。,。使,用,用这,种,种方,法,法导,入,入数,据,据，,还,还会,同,同时,建,建立,对,对源,地,地址,的,的查,询,询链,接,接，,可,可以,在,在需,要,要时,，,，执,行,行刷,新,新命,令,令以,更,更新,数,数据,。,。,4,1.1.1,数,数据,的,的导,入,入,从,Web,上导,入,入大,规,规模,数,数据,，,，更,规,规范,的,的做,法,法是,创,创建,Web,查询,。,。从,Excel,菜单,上,上执,行,行“,数,数据,”,”,“,导入,Web,数据,”,”,“,新建,Web,查询,”,”，,打,打开,创,创建,查,查询,对,对话,框,框。,5,1.1.2,数,数据,运,运算,与,与引,用,用,Excel,中的,运,运算,是,是通,过,过公,式,式或,函,函数,实,实现,的,的。,Excel,中的,公,公式,。,。它,是,是由,等,等号,打,打头,，,，包,括,括常,量,量、,对,对单,元,元格,或,或单,元,元格,区,区域,的,的引,用,用、,运,运算,符,符和,函,函数,等,等几,种,种成,分,分。,例,例如,在,在下,面,面一,行,行中,，,，,=60+AVERAGE(B2:B8)/SIGMA,等号,表,表示,所,所在,单,单元,格,格内,容,容是,一,一个,公,公式,，,，等,式,式后,面,面的,“,“,60,”,”,是常,量,量，,“,“,+”,和“,/”,是运,算,算符,，,，,AVERAGE(),是求,平,平均,值,值的,函,函数,，,，,SIGMA,是用,户,户自,己,己给,特,特定,单,单元,格,格的,命,命名,。,。,当需,要,要同,时,时对,一,一组,数,数据,进,进行,处,处理,或,或公,式,式、,函,函数,的,的计,算,算结,果,果不,只,只一,个,个数,据,据时,，,，就,需,需要,使,使用,数,数组,公,公式,或,或数,组,组函,数,数。,在,在,Excel,中用,大,大括,号,号“,”,”,表示,数,数组,，,，如,“,“,2,2,4;3,6,8;,”,”,数组,公,公式,和,和数,组,组函,数,数的,使,使用,方,方法,与,与普,通,通公,式,式基,本,本相,同,同，,但,但在,输,输入,时,时要,首,首先,选,选中,返,返回,数,数组,结,结果,的,的全,部,部单,元,元格,，,，并,使,使用,Shift+Ctrl+Enter,进行,输,输入,6,1.1.2,数,数据,运,运算,与,与引,用,用,运算类型,运算符,示例,说明,算术运算,+*/%,10000.00*(1+1.1%)2,年息,1.1,，本金,10000,元，按复利计算两年的本息合计,10221.21,。,文本运算,&,“Product A”&“Actual”,将两个文本连接，得到“,Product A Actual”,。,日期运算,“2003-5-28”“2001-2-10”,“17:30”“09:15”,求出两个日期之间的天数,837,；求出两个时间之间的间隔,8:15,。,比较运算,=,A1A2,C2C3,判断两个数据之间的比较关系，返回逻辑值，,Excel,中的,运,运算,类,类型,7,1.1.2 数据,运,运算与引,用,用,Excel,中的运算,函,函数,函数类别,示例,逻辑函数,IF(,判断,),NOT(,非,),OR(,或,),AND(,与,),TRUE(,真,),FALSE(,假,),数学函数,LOG(,对数,),POWER(,乘幂,),SQRT(,开平方,),COS(,余弦,),FACT(,阶乘,),统计函数,VAR(,方差,),MEDIAN(,中位数,),NORMSDIST(,标准正态分布,),工程函数,BESSELJ(,贝塞尔函数,),HEX2BIN(16,进制到,2,进制,),IMAGINARY(,复数的虚部系数,),金融函数,NPV(,净现值,),FV(,净现值,),INTRATE(,利率,),YIELD(,收益,),8,1.1.2 数据,运,运算与引,用,用,Excel,中引用单,元,元格的方,法,法分为：,A1,引用和,R1C1,引用。前,者,者是用字,母,母数字组,合,合标识引,用,用单元格,的,的绝对位,置,置；后者,表,表示对当,前,前选中单,元,元格相对,位,位置的引,用,用。,当需要引,用,用位于其,他,他工作表,或,或其他文,件,件中的数,据,据时，就,要,要使用三,维,维引用。,三,三维引用,的,的基本格,式,式是：,spreadsheetsheet!A1,其中，前,面,面的方括,号,号“,”,内是要引,用,用的文件,名,名，如果,源,源数据在,同,同一文件,内,内则可省,略,略；跟在,文,文件名后,面,面的是工,作,作表的名,称,称，如果,同,同时引用,多,多个工作,表,表相同位,置,置的数据,，,，则可以,使,使用冒号,“,“,:”,标识工作,表,表范围，,如,如,Sheet1:Sheet3,，注意工,作,作表名后,面,面要跟有,叹,叹号“,!”,；最后是,引,引用的单,元,元格或单,元,元格区域,的,的名称，,如,如,A1,、,B2,或,C3:D6,等等。,9,1.1.2 数据,运,运算与引,用,用,相对引用,：,：公式中,的,的相对单,元,元格引用,(,例如,A1),是基于单,元,元格的相,对,对位置。,如,如果公式,所,所在单元,格,格的位置,改,改变，引,用,用也随之,改,改变。如,果,果多行或,多,多列地复,制,制公式，,引,引用会自,动,动调整。,默,默认情况,下,下，公式,使,使用相对,引,引用。,绝对引用,：,：单元格,中,中的绝对,单,单元格引,用,用,(,格式为,$A$1,，即在行,列,列标志前,加,加“,$”,符号,),总是在指,定,定位置引,用,用单元格,。,。如果公,式,式所在单,元,元格的位,置,置改变，,绝,绝对引用,保,保持不变,。,。如果多,行,行或多列,地,地复制公,式,式，绝对,引,引用将不,作,作调整。,混合引用,：,：混合引,用,用具有绝,对,对列和相,对,对行，或,是,是绝对行,和,和相对列,。,。绝对引,用,用列采用,$A1,、,$B1,等形式，,绝,绝对引用,行,行采用,A$1,、,B$1,等形式。,如,如果公式,所,所在单元,格,格的位置,改,改变，则,相,相对引用,改,改变，而,绝,绝对引用,不,不变。如,果,果多行或,多,多列地复,制,制公式，,相,相对引用,自,自动调整,，,，而绝对,引,引用不作,调,调整。,10,1.1.2 数据,运,运算与引,用,用,Excel,中的绝对,引,引用、相,对,对引用和,混,混合引用,11,1.1.3,排序与筛,选,选,在,Excel,中对数据,进,进行整理,最,最常见的,方,方式是排,序,序和筛选,。,。,排序是按,照,照某一个,或,或某几个,特,特定字段,重,重新排列,数,数据的顺,序,序，筛选,是,是从全体,数,数据中选,择,择符合某,种,种条件的,数,数据。,Excel,提供了两种对,数,数据进行排序,的,的方法：,一种方法是选,中,中数据表中作,为,为排序依据所,在,在列中的单元,格,格，然后点击,常,常用工具栏上,的,的排序按钮,如果需要先后,按,按照若干列来,排,排序数据表，,则,则需要从菜单,上,上执行“数据,”,”,“,排序”命令，,打,打开排序对话,框,框。,例如，从,Web,下载了某股票,期,期权的报价后,，,，需要按照期,权,权期限、执行,价,价格和交易场,所,所对报价进行,排,排序，则对数,据,据进行初步整,理,理后，即可按,如,如下操作。,12,1.1.3,