EXCEL应用培训

1、大连中远船务财务部1 21、EXCEL做什么用？数据的处理、统计分析和辅助决策2、菜单栏功能介绍3、EXCEL基础配置4、不在功能区的命令5、EXCEL的帮助功能（F1）3相对引用：相对引用：复制公式时，单元格引用将调整到新的位置上。(=A1)绝对引用绝对引用：复制公式时，单元格引用不会改变。(=$A$1) 混合引用混合引用：行或列中有一个是相对引用，另一个是绝对引用。（=$A1/A$1）实际录入时输入$或直接点击F4按钮主要用于复制或自动填充公式41、使用名称管理器(CTRL+F3)2、必要元素：名称、范围、引用位置、备注为避免重复、名称尽量避免使用ABC、currency等，改为 “bud

2、get_currency”等保证不重复但能同时最大程度反映范围含义的词汇；在本例中,可通过对汇率的命名，避免之后重复的查找5工作中经常遇到筛选数据的情况EXCEL提供了按值或单元格设置的排序/筛选办法6作用：突出显示所关注的单元格或单元格区域；强调异常值；使用数据条、颜色刻度和图标集来直观地显示数据。判断方法：如果条件为 True，则基于该条件设置单元格区域的格式；如果条件为 False，则不基于该条件设置单元格区域的格式。7在开始-条件格式可选择设定好的规则或根据自己需求新建规则8数据-数据工具-数据有效性功能：1、防止输入无效数据；2、提供功能了下拉框；93、提供了输入提醒、出错警告及输入

3、法状态选择功能；104、数据有效性-序列*两种方式：1、数字,数字2、=名称11数据-分级显示-组合/分类汇总1、选择分类字段；2、选择拟处理字段的计算方式；3、选择汇总项；4、不需要时选择全部删除；12审阅-更改实现功能：多用户共同编辑时，通过添加密码，实现对工作表的保护。方式：1、设置单元格格式-保护-锁定/隐藏2、审阅-更改-允许用户编辑的区域1314F1-帮助F4-引用状态切换F5-定位F11-插入图表F12-另存为CTRL+C 复制CTRL+C 剪切CTRL+V 粘贴CTRL+A 全选CTRL+S 保存CTRL +Z 撤销CTRL +F1 隐藏robbin栏CTRL + -转到表格最

4、上/下/左/右端直接使用CTRL进行多选15ALT+F1 插入图表ALT+F11 VB编辑器ALT+”+” 自动求和 ALT+D+P 数据透视表/图向导ALT+”回车” 换行（不影响公式运算）直接ALT，按提示选择其他SHIFT+F2 插入批注SHIFT+F3 插入公式SHIFT+F11 插入船号SHIFT +”空格” 选定整行直接使用SHIFT进行多选16 17录入符号；”=“运算符；（+,*,/,&,0，SUM(B1:B10),0)最多嵌套64层21用于判断设定条件是否正确，并根据条件进行相应操作22用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式

5、的条件检测任务。 语法：IF(logical_test，value_if_true，value_if_false)Logical_test （必需）计算结果为TRUE 或FALSE 的任何数值或表达式Value_if_true（必需） 是Logical_test 为TRUE 时函数的返回值；Value_if_false（非必需） 是Logical_test 为FALSE 时函数的返回值。23用途：如果公式的计算结果为错误，则返回您指定的值；否则将返回公式的结果。使用 IFERROR 函数来捕获和处理公式中的错误。语法： IFERROR(value, value_if_error)valueva

6、lue （ 必需）检查是否存在错误的参数value_if_errorvalue_if_error （必需）公式的计算结果为错误时要返回的值。24运算符含义大于=大于等于=小于等于不等于所有逻辑预算均返回逻辑值：TURE/FALSE可对数字或者关系式进行比较注意：从等式最右端开始判断如=1=23将返回TURE25用途：所有参数的逻辑值为真时返回TRUE(真)；只要有一个参数的逻辑值为假，则返回FALSE(假)语法：AND(logical1，logical2，)参数必须是逻辑值或者包含逻辑值的数组或引用，如果数组或引用内含有文字或空白单元格，则忽略它的值。如果指定的单元格区域内包括非逻辑值，AND

7、将返回错误值#VALUE！26用途：所有参数中的任意一个逻辑值为真时即返回TRUE(真)。语法：OR(logical1，logical2，.) 参数：Logical1，logical2，.是需要进行检验的1 至30 个逻辑表达式，其结论分别为TRUE 或FA LSE。如果数组或引用的参数包含文本、数字或空白单元格，它们将被忽略。如果指定的区域中不包含逻辑值，OR 函数将返回错误#VALUE！。27用途：求出一个逻辑值或逻辑表达式的相反值。如果您要确保一个逻辑值等于其相反值，就应该使用NOT 函数。语法：NOT(logical) 如果逻辑值或表达式的结果为FALSE， 则NOT 函数返回T RU

8、E；28用途：CONCATENATE 函数可将最多 255 个文本字符串合并为一个文本字符串。联接项可以是文本、数字、单元格引用或这些项的组合。语法：CONCATENATE(text1,text2, .)可以预先设定文本组合成一段话或文章29用于根据指定条件（单元格、区域），在目标区域中进行查询，并返回查询结果。30用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数VLOOKUP 代替函数HLOOKUP。语法：VLOOKUP(lookup_value，table_array，col_index_num，range_lo

9、okup)*重点公式31Lookup_value为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。Table_array 为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。Col_index_num 为table_array 中待返回的匹配值的列序号。Range_lookup为一逻辑值，指明函数VLOOKUP 返回时是精确匹配还是近似匹配。如果Range_lookup为FALSE， 函数VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。如果为TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数

10、值；32用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH 函数。 语法：MATCH(lookup_value，lookup_array，match_type)。参数：Lookup_value为需要在数据表中查找的数值，它可以是数值(或数字、文本或逻辑值)、对数字、文本或逻辑值的单元格引用。Lookup_array是可能包含所要查找的数值的连续单元格区域，Lookup_array可以是数组或数组引用；Match_type 为数字-1、0或1 :如果match_type 为1，近似的向下取最大数值；如果match_typ

11、e 为0，返回lookup_value 的精确值数值；如果match_type 为-1，近似的向上取最小数值； 注意：MATCH函数返回lookup_array 中目标值的位置，而不是数值本身。33INDEX用途：返回表格或区域中的数值或对数值的引用。函数INDEX()有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。 语法：INDEX(array，row_num，column_num)返回数组中指定的单元格或单元格数组的数值。INDEX(reference，row_num，column_num， area_num)返回引用中指定单元格或单元格区域的引用。34参数：

12、Array为单元格区域或数组常数；Row_num为数组中某行的行序号，函数从该行返回数值。Reference是对一个或多个单元格区域的引用，如果为引用输入一个不连续的选定区域，必须用括号括起来。Area_num 是选择引用中的第几个区域。35用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域，并可以指定返回的行数或列数。 语法：OFFSET(reference，rows，cols，height， width)36参数：Reference 是作为偏移量参照系的引用区域，它必须是单元格或相连单元格区域的引用；Rows是相对于偏移量参照系的左上角单元格，上

13、(下)偏移的行数。行数可为正数或负数；Cols 是相对于偏移量参照系的左上角单元格，左(右)偏移的列数。列数可为正数或负数；Height 是要返回的引用区域的行数，Height 必须为正数；Width 是要返回的引用区域的列数，Width 必须为正数。37VLOOKUPINDEX/MATCHOFFSET功能查询查询偏移查询结果单一值单一值单一值/区域条件项单项两项单项/区域查询范围选定区域选定区域内无限制38用途：返回给定引用的行号。语法：ROW(reference)/COLUMN(reference)参数：Reference 为需要得到其行号的单元格或单元格区域。39用途：返回由文字串指定的

14、引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，即可使用INDIRECT 函数语法：INDIRECT(ref_text，a1)参数：Ref_text是对单元格的引用，此单元格可以包含A1 样式的引用、R1C1样式的引用、定义为引用的名称或对文字串单元格的引用；A1为一逻辑值，指明包含在单元格ref_text 中的引用的类型。如果a1 为TRUE 或省略，ref_text 被解释为A1-样式的引用。如果a1 为FALSE，ref_text 被解释为R1C1-样式的引用40 41用途：FIND 用于查找其他文本串内的文本串语法：FIND(find_te

15、xt，within_text，start_num)，参数：Find_text是待查找的目标文本；Within_text是包含待查找文本的源文本；Start_num 指定从其开始进行查找的字符，即within_text 中编号为1 的字符。如果忽略start_num，则假设其为1。42用途：MID 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB函数可以用于双字节字符。 语法：MID(text，start_num， num_chars)参数：Text 是包含要提取字符的文本串。Start_num 是文本中

16、要提取的第一个字符的位置，文本中第一个字符的start_num 为1 ，以此类推；Num_chars指定希望MID 从文本中返回字符的个数；43用途：LEN 返回文本串的字符数。语法：LEN(text)或LENB(text)。 参数：Text待要查找其长度的文本。 注意：此函数用于双字节字符，且空格也将作为字符进行统计。44用途：将表示数字的文字串转换成数字。 语法：VALUE(text)。 参数：Text为带引号的文本，或对需要进行文本转换的单元格的引用。它可以是Excel 可以识别的任意常数、日期或时间格式。45用途：返回某一引用区域的左上角单元格的格式、位置或内容等信息，该函数主要用于保

17、持与其它电子表格程序的兼容性。 语法：CELL(info_type，reference) 参数：Info_type 为一个文本值，指明所需要的单元格信息的类型。Reference 表示要获取其有关信息的单元格。如果忽略，则在info_type 中所指定的信息将返回给最后更改的单元格。最常见的用法是用以获得工作表名称46 47COUNT 返回数字参数的个数。它可以统计数组或单元格区域中含有数字的单元格个数。COUNTA 返回参数组中非空值的数目。利用函数COUNTA 可以计算数组或单元格区域中数据项的个数。COUNTBLANK 计算某个单元格区域中空白单元格的数目。48用途：计算区域中满足给定条

18、件的单元格的个数。 语法：COUNTIF(range，criteria) 参数：Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。49 50用途：返回某一单元格区域中所有数字之和。语法：SUM(number1，number2，.)。 参数：Number1，number2，. 为需要求和的数值。快捷键：ALT + =51用途：返回数据清单或数据库中的分类汇总。如果用户使用“数据”菜单中的“分类汇总”命令创建了分类汇总数据清单，即可编辑SUBTOTAL 函数对其进行修改。 语法：SUBTOTAL(func

19、tion_num，ref1，ref2) 参数：Function_num 为1 -11 /101-111之间的自然数，用来指定分类汇总计算使用的函数Ref1、ref2则是需要分类汇总的区域或引用。52 53用途：根据指定条件对若干单元格、区域或引用求和。 语法：SUMIF(range，criteria，sum_range) 参数：Range 为判断条件的单元格区域，Criteria 是判定条件，Sum_range 为需要求和的单元格、区域或引用。54用途：1、在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。2、根据指定条件求和（多条件）语法：SUMPRODUCT(array1，arr

20、ay2，array3，.) 参数：Array1，array2，array3，.为2至30 个数组，其相应元素需要进行相乘并求和。SUMPRODUCT为又一个重要，但容易被人忽略的函数*另在例子中引用数组公式进行讲解，可适当学习。55用途：返回给定参数表中的最小/大值。 语法：MIN(number1，number2，.) MAX(number1，number2，.)参数：Number1，number2，.是要从中找出最小值的1 到30 个数字参数56ROUND用途：按指定位数四舍五入某个数字。 语法：ROUND(number，num_digits) 参数：Number是需要四舍五入的数字；Num

21、_digits为指定的位数；57注意：如果num_digits 大于0，则四舍五入到指定的小数位；如果num_digits 等于0， 则四舍五入到最接近的整数；如果num_digits 小于0，则在小数点左侧按指定位数四舍五入。实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3；=ROUND(82.149，2)返回82.15；=ROUND(21.5，-1)返回20。58ROUNDDOWN 用途：按绝对值减小的方向舍入某一数字。ROUNDUP 用途：按绝对值增大的方向舍入一个数字。INT用途：将任意实数向下取整为最接近的整数。 语法：INT(number)59用途：返回某

22、一参数的绝对值。语法：ABS(number) 参数：number是需要计算其绝对值的一个实数。 实例：如果A1=-16，则公式“=ABS(A1)”返回16。 6061 62 631、插入-数据透视表1.1选择计算的区域和生成区域1.2选择数据透视表字段：行标签、列标签、筛选区域64最后选定数据的计算方式652、使用数据透视表向导（ALT+D+P）原理同上步 66生成数据透视表如下67数据透视表生成方式二维表格为形成数据透视表的理想形式1、生产数据透视表应在二维数据表中包含清晰明确的数据标签；（如“分类”、“消耗”、“月份”）2、二维数据标签中应不纵向包含其自身的分析（如下图，明显不适合用以分析

23、：）；一般而言二维数据表较三维数据表更容易利用（如有兴趣可自行查阅“关系型数据库”及“范式”）68一维表示例二维表示例此时分类/消耗/单价均无法有效显示一维图表较二维图表更易于数据提取和分析69使用偏移公式=OFFSET(基准位置，纵向改变，横向改变，计算区域高，计算区域高）其中计算区域高可使用公式COUNTA(计算区域)70选中数据透视表此时点选ROBBIN栏在布局菜单调整表格布局在数据透视表样式选项及数据透视表样式调整表格的外观效果713、报表布局3.1以压缩形式显示721、分类汇总733、报表布局3.1以大纲形式显示743、报表布局3.1以表格形式显示754、若有其他需要可在：右键-数据

24、透视表选项进行调整765、数据透视表美化5.1直接套用已有样式5.2 右键-设置单元格格式77若要原金额不变，要求数据透视表显示为（金额：万元）可在：公式-计算字段-公式-添加公式=金额/100078选中数据透视表-ROBBIN栏-工具-数据透视图插入图表-/选择需要的图形-确定这种方式较手工制作图表更为简单79 80主标题：Excel图表的标题。副标题：补充主标题的说明。绘图区：包含数据系列图形的区域。图例：指明图表中的图形代表哪个数据系列。当只有一个数据系列时，Excel也会显示数据系列，显然是多余的。坐标轴：包括横坐标和纵坐标，一般也称为X轴和Y轴。坐标上包括刻 度线、刻度线标签。某些复

25、杂的图表也会使用次坐标轴，一个图表可以有4个坐标轴，即主X、Y轴和次X、Y轴。图表区：整个图像对象所在的区域，它就像一个容器，承载了所有其他元素，以及你添加到它里面的其他对象。网格线：它包括水平和垂直的网格线，分别对应于Y轴和X轴的刻度线。一般使用水平的网格线作为比较数值大小的参考线。 数据系列：根据数据远绘制的图形，用来形象的反映数据，是图表的核心。 脚注区：文档内容进行注释说明。内容来源于网络81 内容来源于F1帮助82 831、ALT+F1（最便捷）1.1 首先选择样例中：产品名及金额列1.2 选择ALT+F1键生成图表如右侧：1.3 在图表上右键选择更改图表类型842、通过菜单栏插入选

26、择数据-插入-图表-选择图表类型853、通过插入图表向导插入ALT+D+P 下一步-选定区域-下一步-选择位置 完成（使用这种方式可创建更为复杂的图表）861、根据3.2.2创建的图表右键选择选择数据；2、进入选择数据源；3、其中包含：3.1数据区域：（可不选择）873.2 图例项（系列）:要制作图标的数据3.2.1 添加3.2.2 编辑3.2.3 删除系列名称：行标签系列值：数据源区域系列值可为区域或名称注：系列值一定要选择对应表格，否则无法使用883.3水平(分类)轴标签:决定横坐标轴标签注：轴标签区域一定要选择对应表格，否则无法使用891、根据3.2.2创建的图表右键更改图表类型；2、根

27、据需要选择图表类型3、在图表工具-设计中对图表进行修改90图表工具-布局1、在标签选项中可对坐标轴标签进行修改2、在分析中可对图表增加趋势线913、坐标轴选项*在图表工具-布局-其他主要横/纵坐标选项中可根据需要对表格进行细致的调整92实例：制作简易线表（甘特图）931、图表工具-格式-形状样式在形状样式中可对图表填充颜色、背景、三维效果等进行设置 94图表中分为两部分：绘图区域、图表区域在对应区域单击右键-选择设置区域格式填充-对应图表的颜色（可选择透明度或插入图片）边框颜色-设置区域的线条颜色边框样式-设置区域的线型（虚线、实现等）适当使用阴影/三维格式可使图表更美观95背景：在实际工作中

28、，经常需要根据指定数据生成特定类型的图表。但当选定区域的数据较多时，将全部数据生成单一的图表反而不利于分析使用。在这种情况下，就需要选定特定的单个或多个区域进行分析。96完成方式：1、通过数据有效性下拉框、控件选定数据行/列标签。2、通过OFFSET、INDEX、VLOOKUP等公式，根据选定标签选取数据标签、数据区域。数据标签、数据区域可使用名称选取或汇总至另一表格；971、在快速访问工具栏中添加控件选项OFFICE选项-EXCEL选项-自定义-从下列位置选择命令-开发工具选项卡-插入控件-添加2、插入组合框插入控件-组合框98在此仅以组合框作为例子，其他控件方法大同小异3、设置控件选中控件

29、-右键-设置控件格式-控制数据源区域：组合框可选项单元格链接：选项在链接中的位置下来显示项数：任选几个（不推荐过多项）994、选择数据选择数据可选择使用如下两种方式：4.1 使用额外的数据区域制作图表*4.2 使用公式作为数据区域对初学者而言，一般推荐使用额外数据区域选取数据100 1014.14.1额外数据区域额外数据区域* *4.24.2使用公式使用公式优点：优点：1.数据显示较为直观1.较灵活，数据区域可变动选择2.可处理不相邻区域缺点：缺点：1.需额外占用一定表格区域1.较难处理不相邻的区域2.公式较难变更4.1 使用额外的数据区域制作图表*4.1.1在例子中创建对下拉框单元格链接的查

30、询；4.1.2 在数据区域中根据所需内容作出相应查询；4.1.3 为每个查询指定标题；1024.1.4 选中如上区域，ALT+F1；4.1.5 更改图表类型，并将复选框拖入图形内；生成动态图表如下：1034.2 使用公式作为数据区域4.2.1 创建数据区域公式-名称管理器-新建名称：EXP_DATE引用位置：=OFFSET(数据透视图!$B$1,数据透视图!$B$33,4,1,2)1044.2.2 插入-柱形图-二维柱形图-簇状柱形图得到空图表如下： 右键-选择数据-选择数据源-图例项-添加1054.2.3 新建数据区域系列名称：填写工作表名系列值：填写本文档名！EXP_DATE1064.2.4 新建坐标轴区域4.2.4.1在完成4.2.3操作后，系统会自动添加标签1、2，此时选择编辑 ；4.2.4.2 数据标签区域选择=数据透视图!$F$1:$G$1107生成图表如下108 109背景：在实际工作中需要将数字转化为大写数字可使用如下公式进行操作：=IF(ROUND(G21,2)=0,IF(ROUND(ABS(G21),2)