excel从入门到精通(应用篇)

MicrosoftExcel2000从入门到精通,-实际应用和数据分析,基础篇：第1到第5章应用篇：第6到第10章分析篇：第11到第16章,第六章创建和使用公式,第一节公式的基本概念公式是指对工作表数据进行运算的算式，利用公式可以进行数学运算，可以比较工作表数据并返回布尔值等公式通常由以下五种元素组成：运算符、单元格引用、文字（数字、字符串等）、工作表函数、括号公式以等号=开始，等号后面是要进行计算的操作数、运算符、函数等公式按照运算符特定顺序从左至右进行运算，括号（）可改变运算顺序，先运算括号内，再运算括号外,应用篇,第二节公式中使用的运算符算术运算符+加、-减、*乘、/除、%百分号、乘方比较操作符=、=大于等于、不等于文本串联符若输入为AVERAGE(,1,2,3,4,5),则结果为(0+0+1+2+3+4+5)/7=2.14,因为用逗号预留的参数用缺省值0补上参与了计算第二节在公式中输入函数两种函数输入方法手工输入使用粘贴函数手工输入函数选定单元格在编辑栏单击“编辑公式”按钮单击“函数”下拉列表框，选择函数，如果函数不在下拉列表中，单击列表中“其他函数”项在粘贴函数对话框中选择函数并输入相应参数,粘贴函数选定单元格菜单“插入/函数”或单击工具栏上“粘贴函数”按钮，出现对话框选择函数的种类和具体函数后“确定”填入函数所需的参数（利用“范围”按钮可以方便快捷输入引用区域）第三节数学和三角函数SUM函数格式：SUM(number1,number2)参数为130个例:=SUM(4,6)值为10例:=SUM(“3”,6,FALSE)值为9,因为文本“3”被自动,转换为数字3,逻辑值FALSE被转换为数字0例:若F10单元格内容为文本“3”,则=SUM(F10,6)的值为6,因为对非数值型的值的引用不能被转换成数值参与计算如果参数为引用,只有其中的数字被计算,引用中的文本、逻辑值、错误值将被忽略如果参数错误或不能被转换成数字的文本，将导致错误SUMIF函数格式：SUMIF(RANGE,criteria,sum_RANGE)RANGE:用于条件判断的单元格区域criteria:为确定哪些单元格将被求和的条件sum_RANGE:为需要求和的实际单元格。只有当RANGE中相应单元格满足条件，才对sum_RANGE,中的单元格求和。若省略sum_RANGE,则直接对RANGE中的单元格求和如:=SUM(A1:A5,“1000”,B1:B5)INT函数格式:INT(number)对number取整如:=INT(12.4)的值是12如:=INT(-12.4)的值是-13RAND函数格式:RAND()返回大于等于0小于1的随机数如:=RAND()*100,生成1个0100的数如:=INT(RAND()*100),生成1个0100的整数,ROUND函数格式:ROUND(number,num_digits)根据number_digits指定的位数为number四舍五入如:=ROUND(3.52,1)值是3.5如:=ROUND(-4.276,2)值是-4.28如:=ROUND(46.5,-1)值是50PI函数格式:PI()返回圆周率3.1415926,精确到小数点后15位如:=SIN(PI()/2)值是1SIN函数格式:SIN(number)返回给定弧度的正弦值,SQRT函数格式:SQRT(number)返回number的平方根ABS函数格式:ABS(number)返回number的绝对值TRUNC函数格式:TRUNC(number,num_digits)根据num_digits指定的位数对number截取小数部分num_digits默认值为0如:=TRUNC(-9.3)值为-9LOG函数格式:LOG(number,base),返回以base为底number的对数,缺省的base位10如:=LOG(16,2)值是4COUNTIF函数格式:COUNTIF(RANGE,criteria)计算RANGE指定区域内满足criteria条件的单元格数目如:A3:A6内容分别为“apples”,“oranges”,“peaches”,“apples”,则=COUNTIF(A3:A6,“apples”)值为2如:B3:B6内容分别为32,54,75,86,则=COUNTIF(B3:B6,“55”)值为2第四节文本函数CHAR函数格式:CHAR(number),返回一个ASCII码为number(取值1255)的字符如:CHAR(65)值为“A”LEFT函数和LEFTB函数格式:LEFT(text,num_chars)格式:LEFTB(text,num_bytes)主要应用于中文字符返回text文本串中前chars个字符,缺省的num_chars为1,若其大于text总长度,返回整个字符串如:=LEFT(“Sale_Price”,4)值是“Sale”如:A1单元格内容为“Soft”,则=LEFT(A1)值是“S”如:=LEFT(“计算机”,2)值是“计算”如:=LEFTB(“计算机”,4)值是“计算”LEN函数和LENB函数格式:LEN(text),格式:LENB(text),主要应用于中文字符返回text字符串的长度,空格也作为字符参与计算如:=LEN(“”)值是0如:=LEN(“”)值是1如:=LEN(“计算机”)值是3如:=LENB(“计算机”)值是6MID函数和MIDB函数格式:MID(text,start_num,)格式:MIDB(text,start_num,num_bytes)返回指定字符串text中从start_num开始长度为num_chars或num_bytes的子字符串如果start_num),返回“”空文本如果start_numLEN(text),则返回从start_num到字符串末尾的子字符串如:=MID(“Fluidfirm”,7,15)值是“firm”如:=MID(“1234”,5,5)值是“”空文本如:=MID(“计算机”,1,2)值是“计算”如:=MIDB(“计算机”,1,2)值是“计”REPLACE函数和REPLACEB函数格式:REPLACE(old_text,start_num,num_chars,new_text)格式:REPLACEB(old_text,start_num,num_bytes,new_text)在old_text中从start_num开始用new_text替换num_char或num_bytes个字符如:REPLACE(“123456”,1,3,“$”)值是“$456”,如:=REPLACE(“计算机”,1,2,“*”)值是“*机”如:=REPLACEB(“计算机”,1,2,“*”)值是“*算机”SEARCH函数和SEARCHB函数格式:SEARCH(find_text,start_num)格式:SEARCHB(find_text,within_text,start_num)在within_text字符串中从处开始查找find_text,缺省start_num为1,在find_text中可以使用通配符*和?SEARCH函数和SEARCHB函数查找指定字符串时不区分大小写如:=SEARCH(“e”,“Statements”,6)值是7如:=SEARCHB(“建”,“福建省”)值是3如:=SEARCH(“建”,“福建省”)值是2,如:若B10单元格内容“margin”,A10单元格内容“ProfitMargin”,则=SEARCH($B$10,$A$10)值是8如:上例=REPLACE($A$10,SEARCH($B$10,$A$10),6,“Amount”)值是“ProfitAmount”FIND函数和FINDB函数格式:FIND(find_text,within_text,start_num)格式:FINDB(find_text,within_text,start_num)在within_text字符串中从处开始查找find_text,缺省start_num为1在find_text中不可以使用通配符*和?查找指定字符串时区分大小写如:=FIND(“M”,“MiriamMc”)值是1如:=FIND(“m”,“MiriamMc”)值是6如:=FIND(“M”,“MiriamMc”,3)值是8,如:=FIND(“建”,“福建省”)值是3如:=FINDB(“建”,“福建省”)值是2UPPER函数和LOWER函数格式:UPPER(text)格式:LOWER(text)将字符串转化成大写、小写形式如:=UPPER(“Total”)值是“TOTAL”如:=LOWER(“Total”)值是“total”EXACT函数格式:EXACT(text1,text2)比较第一个字符串text1和第二个字符串text2是否完全相同EXACT区别大小写,但是忽略格式的不同,如:=EXACT(“James”,“James”)值是TRUE如:=EXACT(“Diane”,“diane”)值是FALSE第五节逻辑函数IF函数格式:IF(logical_test,value_if_true,value_if_false)logical_test是值为逻辑值TRUE或FALSE的逻辑表达式,若条件成立,返回value_if_true的值,否则返回value_if_false的值,value_if_false可省略如:A10单元格中存放当前预算值,如果A10值大于100,显示“超出预算”,否则显示“预算内”=IF(A1089,“A”,IF(average79,”B”,IF(average69,”C”,IF(average59,“D”,“FAIL”)AND函数格式:AND(logical1,logical2,)所有的参数均为真,返回逻辑值“TRUE”,否则返回“FALSE”如:=AND(1+4=1,2+6=8)值是FALSEOR函数格式:OR(logical1,logical2,)所有的参数只要有一个为真,返回逻辑值“TRUE”,否则返回“FALSE”,如:=OR(1+4=1,2+6=8)值是TRUENOT函数格式:NOT(logical)如果logical为真,返回FALSE;否则,返回TRUE如:=NOT(1+3=2)值是TRUE第六节时间日期函数Excel将日期存储为一系列连续的序列数,时间被存储成小数,因为时间可以看作是天的一部分。它们被看作数值后可进行计算，如计算两个日期相差得天数等。默认情况下，1900年1月1日被看作系统的第一天，序列数是1；9999年12月31日是系统最后一天。如：2001年1月21日的序列数是36912,TODAY函数格式:TODAY()返回当前的系统日期如:假设今天是2001年1月21日,则=TODAY()的值是1/21/2000DATE函数格式:DATE(year,month,day)返回year年,month月,day日的序列号如:=DATE(2001,1,21)值是36912如果year在01899间,系统会自动在此数值上加1900,当作年份进行计算;如果year在19009999间,系统直接将此数值当作年份计算如:=DATE(100,1,2)等于=DATE(1900+100,1,2)值是36527,WEEKDAY函数格式:WEEKDAY(serial_number,return_type)返回serial_number代表的日期是星期几,return_type为确定返回值类型的数字,可省略,默认是1如:=WEEKDAY(“2001/1/21”)值是1,表示星期日如:=WEEKDAY(35981.007)值是1,表示星期日TIME函数格式:TIME(hour,minute,second),返回hour小时,minute分钟,second秒对应的小数值hour取值123,minute和second取值059如:=TIME(12,0,0)值是0.5第七节财务函数财务函数可以进行一般的财务计算,例如确定贷款的支付额,投资的未来值或净现值,以及债券或股票的价值常用的参数:未来值(fv)-在所有付款发生后的投资或贷款价值期间数(nper)-投资的总支付期间数付款(pmt)-对意向投资或贷款的定期支付数额现值(pv)-投资初的投资或贷款价值利率(rate)-投资或贷款的利率或贴现率类型(type)-付款期间内进行支付的间隔,SLN函数格式:SLN(cost,salvage,life)返回一项资产每期的直线折旧费如:一辆价值$30000的卡车,折旧期限10年,资产残值$10000,则每年折旧额为=SLN(30000,10000,10)为$2000RATE函数格式:RATE(nper,pmt,pv,type,guess)返回年金的各期利率如:金额为$8000的4年期贷款,月支付额为$200,则该笔贷款的利率为=RATE(48,-200,8000)为0.77%第八节统计函数AVERAGE函数,格式:AVERAGE(number1,number2,)返回若干个参数的算术平均值如:=AVERAGE(1,2,3)值是2如:A1A4单元格值为2,3,4,5,则=AVERAGE(A1:A4,6)值是4COUNT函数格式:COUNT(value1,value2,)返回参数个数,计数时,将把数字,空值,逻辑值,日期或以文字代表的数计算在内,错误值和无法转换成数字的文字被忽略如:B1B4单元格内容是12/8/99,空,10,#DIV/0!,则=COUNT(B1:B4)值是1MAX函数和MIN函数格式:MAX(number1,number2,),格式:MIN(number1,number2,)返回参数中最大/小值如果A1:A5中依次包含数值10，7，3，27和2，那么=MIN(A1:A5)等于2,=MIN(A1:A5,0)等于0,=MAX(A1:A5)等于27第九节函数嵌套在某些情况下,我们可能将某函数作为另一函数的参数使用,以增加公式使用的灵活性,并且加快数据处理的速度当前套函数作为参数使用时,它返回的类型必须与参数使用的数值类型相同公式中最多可以包含7级嵌套如:=IF(AVERAGE(B3:B7)SUM(C2:C4),SUM(C2:C4),0),第八章图表制作,图表具有较好的视觉效果，可方便用户察看数据的差异、图案和预测趋势第一节初步认识图表用图表表现数据可以在工作表上创建图表，或将图表作为工作表的嵌入对象使用图表的基本元素图表是与生成它的工作表数据相连接的与图表有关的工作表数据变化时，图表也会自动更新将鼠标停留在图表项上，系统会自动显示相关信息,应用篇,选定图表项的方法图表设置中，一般都是先选中某图表项，再单击右键选择快捷菜单或在“格式”菜单中执行命令1）单击所需图表项2）单击图表，然后在“图表”工具栏中单击“图表对象”下拉箭头（“视图/工具栏/图表”显示图表工具栏）,使用鼠标移动、调整图表项大小单击选中要移动或调整大小的图表项如要移动，鼠标指向后拖拽鼠标到目的位置如要调整大小，鼠标指向尺寸柄变成双向箭头时拖拽鼠标第二节创建图表利用图表向导创建图表选定待显示于图表中的数据所在单元格（可以连续也可不连续，也可以不选）单击工具栏上“图表向导”按钮或菜单“插入/图表”根据向导提示，依次选择或输入图表类型、图表数据源（可在此时选择数据区域或数据系列）、图表选项和图表位置,第三节修改图表类型常见图表类型柱形图：显示一段时间内的数据变化或说明项目之间的比较结果条形图：显示各个项目之间的比较情况折线图：显示相同间隔内数据的预测趋势饼图：显示构成数据系列的项目相对于项目总和的比例大小散点图面积图圆环图雷达图曲面图气泡图股价图圆柱图圆锥图棱锥图修改图表类型单击选中图表菜单“图表/图表类型”或鼠标右键单击图表区快捷菜单中选择“图表类型”,第四节修改图表数据更改图表数据区域单击图表菜单“图表/数据源”或在图表区域单击鼠标右键，快捷菜单中选择“数据源”选择“数据区域”卡片后，选择数据系列产生在行/列的方式，也可重新选择图表产生数据区域选择“系列”卡片后，可以增加或删除图表产生的系列向图表中添加数据通过复制和粘贴向图表中添加数据选择要添加的数据的单元格菜单“编辑/复制”或工具栏上“复制”按钮,单击图表如果要让Excel自动将数据粘贴到图表，菜单“编辑/粘贴”或工具栏上“粘贴”按钮；如果要指定数据在图表中的绘制方式，菜单“编辑/选择性粘贴”使用颜色编码区域向嵌入式图表添加、修改数据选定图表区域拖动工作表中紫色、蓝色、绿色的选择柄进行数据的添加或修改通过拖动向嵌入式图表中添加数据选定包含添加数据的单元格（工作表中必须相连）鼠标指向选定区域的边界将选定区域拖动到希望更新的嵌入土表中第五节更改图表选项包括：标题、坐标轴、网格线、图例、数据标志,修改图表选项单击图表，菜单“图表/图表选项”或在图表区域单击鼠标右键，快捷菜单中选择“