EXCEL大全中级教程学习教案

1、会计学1EXCEL大全大全(d qun)中级教程中级教程第一页，共68页。算术运算符号算术运算符号运算符含义运算符含义示示 例例+(加号加号)加加2+3=5-(减号减号)减减3-1=2*(星号星号乘乘3*2=6/(斜杠斜杠)除除6/2=3%(百分号百分号)百分号百分号50%(脱字号脱字号)乘方乘方43=43=64第1页/共68页第二页，共68页。比较运算符比较运算符运算符含义运算符含义示示 例例=(等号等号)相等相等B1=C1,若若B1中单元格内的值确实与中单元格内的值确实与C1中的值相中的值相等等,则产生逻辑真值则产生逻辑真值TRUE,若不相等若不相等,则产生逻辑则产生逻辑假值假值FALSE

2、(小于号小于号)小于小于B1(大于号大于号)大于大于B1C1,若若B1中数值为中数值为6,C1中数值为中数值为4,则条件成则条件成立产生逻辑真值立产生逻辑真值TRUE,否则产生逻辑假值否则产生逻辑假值FALSE=(大于等于号大于等于号)大于等于大于等于B1=C1(不等号不等号)不等于不等于B1C1=(小于等于号小于等于号)小于等于小于等于B1=C1第2页/共68页第三页，共68页。引用运算符引用运算符运算符含义运算符含义示示 例例:区域运算符区域运算符,产生对包产生对包括在丙相引用之间的括在丙相引用之间的所有单元格的引用所有单元格的引用(A5:A15),联合运算符联合运算符,将多个引将多个引用

3、合并为一个引用用合并为一个引用SUM(A5:A15,C5:C15)(空格空格)交叉运算符交叉运算符,产生对两产生对两个引用构有的单元格个引用构有的单元格的引用的引用(B7:D7 C6:C8)例:A4=B4+C4+C4+E4+F4,可写为: A4=SUM(B4:F4)第3页/共68页第四页，共68页。第4页/共68页第五页，共68页。:A$4第5页/共68页第六页，共68页。第6页/共68页第七页，共68页。第7页/共68页第八页，共68页。逻辑值: FALSE :需要完全一致 TRUE: 相似(xin s)值亦可转换第8页/共68页第九页，共68页。语法语法作用作用SUM(number1,nu

4、mber2, )返回单元格区域中所有数值的和返回单元格区域中所有数值的和ISPMT(Rate,Per,Nper,Pv)返回普通返回普通(无提保无提保)的利息偿还的利息偿还AVERAGE(Number1,Number2, )计算参数的算术平均数计算参数的算术平均数,参数可以是数值或包含数值参数可以是数值或包含数值的名称数组和引用的名称数组和引用IF(Logical_test,Value_if_true,Value_if_false)执行真假判断执行真假判断,根据对指定条件进行逻辑评价的真假根据对指定条件进行逻辑评价的真假而返回不同的结果。而返回不同的结果。HYPERLINK(Link_locat

5、ion,Friendly_name)创建快捷方式创建快捷方式,以便打开文档或网络驱动器或连接连以便打开文档或网络驱动器或连接连接接INTERNETCOUNT(Value1,Value2, )计算参数表中的数字参数和包含数字的单元格的个数计算参数表中的数字参数和包含数字的单元格的个数MAX(number1,number2, )返回一组数值中的最大值返回一组数值中的最大值,忽略逻辑值和文字符忽略逻辑值和文字符SIN (number)返回纪念品定角度的正弦值返回纪念品定角度的正弦值SUMIF (Range, Criteria, Sum_range)根据指定条伯对若干单元格求和根据指定条伯对若干单元格

6、求和PMT(Rate,Nper,Pv,Fv,Type)返回在固定利率下返回在固定利率下,投资或贷款的等额分期偿还额投资或贷款的等额分期偿还额第9页/共68页第十页，共68页。注:Rate 贷款利率。Nper 该项贷款的付款总数。Pv 现值,或一系列未来付款的当前值的累积和,也称为(chn wi)本金。Fv 为未来值,或在最后一次付款后希望得到的现金余额,如果省略 fv则假设其值为零,也就是一笔贷款的未来值为零。Type 数字 0 或 1,用以指定各期的付款时间是在期初还是期末。第10页/共68页第十一页，共68页。主要用于分析和处理日期值和时间(shjin)值,包括DATE、DATEVALUE

7、、DAY、HOUR、TIME、TODAY、WEEKDAY 和 YEAR等。例：函数DATA语法：DATE(year,month,day)第11页/共68页第十二页，共68页。第12页/共68页第十三页，共68页。主要用于进行各种各样的计算(j sun),包括ABS、ASIN、COMBINE、COSLOG、PI、ROUND、SIN、TAN 和TUUNC等。例：ABS函数语法：ABS(number)第13页/共68页第十四页，共68页。第14页/共68页第十五页，共68页。3.在上图的对话框中,选择函数分类下面(xi mian)的函数类型及函数名下面(xi mian)的对应函数名。4. 单击确定按

8、钮,弹出如图所示的相应的调用函数对话框：第15页/共68页第十六页，共68页。第16页/共68页第十七页，共68页。第17页/共68页第十八页，共68页。第18页/共68页第十九页，共68页。函数函数(hnsh)(hnsh)的使用的使用 (COUNTIF & SUMIF) (COUNTIF & SUMIF)SUMIFSUMIF函数函数函数名称：函数名称：SUMIFSUMIF主要功能：计算符合指定条件的单元格区域内的数值和。主要功能：计算符合指定条件的单元格区域内的数值和。使用使用(shyng)(shyng)格式：格式：SUMIFSUMIF（Range,Criteria,Sum_

9、RangeRange,Criteria,Sum_Range）参数说明：参数说明：RangeRange代表条件判断的单元格区域；代表条件判断的单元格区域；CriteriaCriteria为指定条件表达式；为指定条件表达式；Sum_RangeSum_Range代表需要计算的数值所在的单元格区域。代表需要计算的数值所在的单元格区域。第19页/共68页第二十页，共68页。应用举例：如图应用举例：如图7 7所示所示, ,在在B64B64和和C64C64单元格中分别输入公式：单元格中分别输入公式：=SUBTOTAL(3,C2:C63)=SUBTOTAL(3,C2:C63)和和=SUBTOTAL103,C2

10、:C63),=SUBTOTAL103,C2:C63),并且将并且将6161行隐藏起来行隐藏起来, ,确认后确认后, ,前者显示为前者显示为6262（包括隐藏的行）（包括隐藏的行）, ,后者显示为后者显示为61,61,不包括隐藏的不包括隐藏的行。行。特别提醒：如果采取自动筛选特别提醒：如果采取自动筛选, ,无论无论function_numfunction_num参数选用什么类型参数选用什么类型(lixng),SUBTOTAL(lixng),SUBTOTAL函数忽略任何不包括在筛选结果中的行；函数忽略任何不包括在筛选结果中的行；SUBTOTALSUBTOTAL函数适用于数据列或垂直区域函数适用于数

11、据列或垂直区域, ,不适用于数据行或水平区域。不适用于数据行或水平区域。第20页/共68页第二十一页，共68页。第21页/共68页第二十二页，共68页。第22页/共68页第二十三页，共68页。TRUETRUE返回返回(fnhu)(fnhu)逻辑值逻辑值 TRUE TRUE。语法语法TRUE( )TRUE( )说明说明可以直接在单元格或公式中键入值可以直接在单元格或公式中键入值 TRUETRUE，而可以不使用此函数。函数，而可以不使用此函数。函数 TRUE TRUE 主要用于与其他电子表格程序兼主要用于与其他电子表格程序兼容。容。第23页/共68页第二十四页，共68页。第24页/共68页第二十五

12、页，共68页。第25页/共68页第二十六页，共68页。第26页/共68页第二十七页，共68页。第27页/共68页第二十八页，共68页。第28页/共68页第二十九页，共68页。第29页/共68页第三十页，共68页。第30页/共68页第三十一页，共68页。第31页/共68页第三十二页，共68页。第二步：选定需要筛选的数据清单中任一单元格；第三步：选择菜单”Data / Filter / Advanced Filter”第四步：在“条件区域”编辑框中，输入条件区域的引用，并包括条件标志。第五步：若要更改(gnggi)筛选数据的方式，可更改(gnggi)条件区域中的值，并再次筛选数据。注：如果需要将筛

13、选结果放到另一位置，请选择”Copy to another Location”第32页/共68页第三十三页，共68页。请使用若要查找?（问号）任何单个字符例如，sm?th 查找“smith”和“smyth”*（星号）任何字符数例如，*east 查找“Northeast”和“Southeast”（波形符）后跟 ?、* 或 问号、星号或波形符例如，“fy91?”将会查找“fy91?”通配符通配符以下通配符可作为筛选以下通配符可作为筛选(shixun)以及查找和替换内容时的比较条件以及查找和替换内容时的比较条件 :第33页/共68页第三十四页，共68页。第34页/共68页第三十五页，共68页。要分类

14、(fn li)汇总的列分类(fn li)汇总步骤步骤:第35页/共68页第三十六页，共68页。要分类(fn li)汇总的列分类(fn li)汇总第36页/共68页第三十七页，共68页。第37页/共68页第三十八页，共68页。第38页/共68页第三十九页，共68页。数据透视表是交互式报表，可快速合并(hbng)和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。 源数据第三季度高尔夫汇总的源值数据透视表C2 和 C8 中源值的汇总第39页/共68页第四十页，共68页。何时应使用数据透视表何时应使用数据透视表如果要分析相关的汇总值，尤其是在要合计较大的列表并对每

15、个数字进行多种比较时，可以使用数据透视表。在上面所述报表中，用户可以很清楚地看到单元格如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表。在上面所述报表中，用户可以很清楚地看到单元格 F3 中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的。由于数据透视表是交互式的，因此中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的。由于数据透视表是交互式的，因此(ync)，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或

16、平均值。 数据是如何组织的数据是如何组织的在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，“运动运动”列成为列成为“运动运动”字段，高尔夫的每条记录在单个高尔夫项中进行汇总。字段，高尔夫的每条记录在单个高尔夫项中进行汇总。数据字段（如数据字段（如“求和项：销售额求和项：销售额”）提供要汇总的值。上述报表中的单元格）提供要汇总的值。上述报表中的单元格 F3 包含的包含的“求和项：销售额求和项：销售额”值来自源数据中值来自源数据中“运动运动”列包含列包含“高尔夫高尔夫”和和“季度季度

17、”列包含列包含“第三季度第三季度”的每一行。的每一行。第40页/共68页第四十一页，共68页。第41页/共68页第四十二页，共68页。第42页/共68页第四十三页，共68页。第43页/共68页第四十四页，共68页。第44页/共68页第四十五页，共68页。创建图表图表是Excel为用户提供的强大功能,通过(tnggu)创建各种不同类型的图表,为分析工作表中的各种数据提供更直观的表示结果。使用图表向导 下面我们先在工作薄中建立数据表如下页图所示,根据工作表中的数据,创建不同类型的图表。图表的编辑 1.插入数据标志及增加图表标题 2.改变图表的文字、颜色和图案第45页/共68页第四十六页，共68页。

18、第46页/共68页第四十七页，共68页。操作步骤如下： 1)选定工作表中包含所需数据的所有单元格。 2)单击常用工具栏中的图表(tbio)向导按钮 ,弹出图（1-16）所示的对话框： 图表图表(tbio)的使用的使用第47页/共68页第四十八页，共68页。第48页/共68页第四十九页，共68页。图表图表(tbio)(tbio)的使用（续的使用（续2 2）4）然后继续(jx)单击下一步弹出图对话框 选择图表存放的位置,若要将图表放到另一个新的工作表上,请选中As new sheet单选框,然后作为新工作表插入文本框,键入(jin r)新工作表的名字,默认为,如果选择As object in单选框

19、,可将： 第49页/共68页第五十页，共68页。第50页/共68页第五十一页，共68页。图表(tbio)标题分类X轴数值Y轴第51页/共68页第五十二页，共68页。第52页/共68页第五十三页，共68页。数据标志数据标志说明说明category name category name 显示指定到数据点的分类显示指定到数据点的分类valuevalue显示数据点的值显示数据点的值percentage percentage 对于饼图、圆环图显示出对整体的百分比对于饼图、圆环图显示出对整体的百分比bubble sizebubble size对于离散图、气泡图显示出气泡的百分比对于离散图、气泡图显示出气泡

20、的百分比第53页/共68页第五十四页，共68页。若要对标题进行编辑,可将鼠标移至标题的左下角处双击,即会出现右图所示对话框,用该对话框可以对标题的字体(zt)、字体(zt)的背景色等进行修改。第54页/共68页第五十五页，共68页。若要对图表区域(qy)进行修改,可将鼠标移至图表的空白区域(qy)双击鼠标,则出现图（1-23）对话框,在此对话框中可选定要修改的内容。第55页/共68页第五十六页，共68页。若要对图例进行(jnxng)修改,可将鼠标移至图例区双击鼠标,则出现如图（1-24）对话框。在此对话框中可对图例进行(jnxng)编辑,如图案、字体及位置。第56页/共68页第五十七页，共68

21、页。复选框复选框 可通过选中或清除来打开或关闭的选项。您可以在一个工作表中同时选中可通过选中或清除来打开或关闭的选项。您可以在一个工作表中同时选中多个复选框。多个复选框。 文本框文本框 可以向其中键入文字的框。可以向其中键入文字的框。命令按钮命令按钮 单击可启动某项操作的按钮。单击可启动某项操作的按钮。选项按钮选项按钮 用于从一组选项中选择某一选项的按钮。用于从一组选项中选择某一选项的按钮。列表框列表框 包含项目列表的框。包含项目列表的框。组合框组合框 含有含有(hn yu)下拉列表框的文本框。您可以从列表中选择下拉列表框的文本框。您可以从列表中选择,或在框中键入或在框中键入所需内容。所需内容

22、。切换按钮切换按钮 单击后保持按下状态单击后保持按下状态,再单击时又弹起的按钮。再单击时又弹起的按钮。微调按钮微调按钮 可附加在单元格上或文本框上的按钮。若要增加数值可附加在单元格上或文本框上的按钮。若要增加数值,请单击向上箭头；请单击向上箭头；若要减小数值若要减小数值,请单击向下箭头。请单击向下箭头。滚动条滚动条 可通过单击滚动箭头或拖动滚动块来滚动数据区域的一种控件。单击滚动可通过单击滚动箭头或拖动滚动块来滚动数据区域的一种控件。单击滚动箭头与滚动块之间的区域时箭头与滚动块之间的区域时,可以滚动整页数据。可以滚动整页数据。标签标签 添加到工作表或窗体中添加到工作表或窗体中,用以提供有关控件

23、、工作表或窗体信息的文本。用以提供有关控件、工作表或窗体信息的文本。图像图像 将图片嵌入到窗体中的控件。将图片嵌入到窗体中的控件。其他控件其他其他控件其他 ActiveX 控件的列表。控件的列表。第57页/共68页第五十八页，共68页。关于宏关于宏如果经常在如果经常在 Microsoft Excel 中重复某项任务中重复某项任务,那么可以用宏自动执行该任务。宏是一系列命令那么可以用宏自动执行该任务。宏是一系列命令(mng lng)和函数和函数,存储于存储于 Visual Basic 模块中模块中,并且在需要执行该项任务时可随时运行。并且在需要执行该项任务时可随时运行。例如例如,如果经常在单元格

24、中输入长文本字符串如果经常在单元格中输入长文本字符串,则可以创建一个宏来将单元格格式设置为文本可自动换行。则可以创建一个宏来将单元格格式设置为文本可自动换行。录制宏录制宏 在录制宏时在录制宏时,Excel 在您执行一系列命令在您执行一系列命令(mng lng)时存储该过程的每一步信息。然后即可运行宏来重复所录制的过程或时存储该过程的每一步信息。然后即可运行宏来重复所录制的过程或“回放回放”这些命令这些命令(mng lng)。如果在录制宏时出错。如果在录制宏时出错,所做的修改也会被录制下来。所做的修改也会被录制下来。Visual Basic 在附属于某工作薄的新模块中存储每个宏。在附属于某工作薄

25、的新模块中存储每个宏。第58页/共68页第五十九页，共68页。使宏易于运行使宏易于运行 可以在可以在“宏宏”对话框的列表中选择所需的宏并运行宏。如果希望通过单击特定按钮或按下特定组合键来运行宏对话框的列表中选择所需的宏并运行宏。如果希望通过单击特定按钮或按下特定组合键来运行宏,可将宏指定给某个工具栏按钮、键盘快捷键或工作表中的图形对象。可将宏指定给某个工具栏按钮、键盘快捷键或工作表中的图形对象。管理宏管理宏 宏录制完后宏录制完后,可用可用 Visual Basic 编辑器查看宏代码以进行改错或更改宏的功能。例如编辑器查看宏代码以进行改错或更改宏的功能。例如,如果希望用于文本换行的宏还可以将文本

26、变为粗体如果希望用于文本换行的宏还可以将文本变为粗体,则可以再录制另一个将单元格文本变为粗体的宏则可以再录制另一个将单元格文本变为粗体的宏,然后将其中的指令复制到用于文本换行的宏中。然后将其中的指令复制到用于文本换行的宏中。“Visual Basic 编辑器编辑器”是一个为初学者设计是一个为初学者设计(shj)的编写和编辑宏代码的程序的编写和编辑宏代码的程序,而且提供了很多联机帮助。不必学习如何编程或如何用而且提供了很多联机帮助。不必学习如何编程或如何用 Visual Basic 语言来对宏进行简单的修改。利用语言来对宏进行简单的修改。利用“Visual Basic 编辑器编辑器”,您可以编辑

27、宏、在模块间复制宏、在不同工作簿之间复制宏、重命名存储宏的模块或重命名宏。您可以编辑宏、在模块间复制宏、在不同工作簿之间复制宏、重命名存储宏的模块或重命名宏。第59页/共68页第六十页，共68页。宏安全性 Excel 对可通过宏传播的病毒提供安全保护。如果您与其他(qt)人共享宏,则可使用数字签名来验证其他(qt)用户,这样就可保证其他(qt)用户为可靠来源。无论何时打开包含宏的工作簿,都可以先验证宏的来源再启用宏。第60页/共68页第六十一页，共68页。创建宏创建宏 录制宏录制宏将安全级设置为将安全级设置为“中中”或或“低低”。 操作方法操作方法在在“工具工具”菜单上菜单上,单击单击“选项选

28、项”。 单击单击“安全性安全性”选项卡。选项卡。 在在“宏安全性宏安全性”之下之下,单击单击“宏安全性宏安全性”。 单击单击“安全级安全级”选项卡选项卡,再选再选择所要使用的安全级。择所要使用的安全级。 在在“工具工具”菜单上菜单上,指向指向“宏宏”,再单击再单击“录制录制新宏新宏”。 在在“宏名宏名”框中框中,输入宏输入宏 的名称。的名称。 注意注意宏名的首字符必须是字母宏名的首字符必须是字母,其他字符可以是字母、数字或下划线。宏名中其他字符可以是字母、数字或下划线。宏名中不允许有空格；可用下划线作为分词符。不允许有空格；可用下划线作为分词符。宏名不允许与单元格引用重名宏名不允许与单元格引用

29、重名,否则会出现错误信息显示宏名无效。否则会出现错误信息显示宏名无效。如果要通过按键盘上的来运行宏如果要通过按键盘上的来运行宏, 请在请在“快捷键快捷键”框中框中,输入一个字母。输入一个字母。可用可用 Ctrl+字母（小写字母）或字母（小写字母）或 Ctrl+Shift+字母（大写字母）字母（大写字母）,其中其中字母可以是键盘上的任意字母键。快捷键字母不允许是数字或特殊字字母可以是键盘上的任意字母键。快捷键字母不允许是数字或特殊字符（如符（如 或或 #）。）。 注释注释 当包含宏的工作簿打开时当包含宏的工作簿打开时,宏快捷键优先于任何相当宏快捷键优先于任何相当(xingdng)的的 Microsoft Excel 的默认快捷键。的默认快捷键。第61页/共68页第六十二页，共68页。在“保存在”框中,单击要存放宏的地址。 如果要