Excel中级培训教程

用公式简化你的Excel工作 了解Excel提供的各种功能 绘制图表辅助数据阅读
2.
3.
目录
1.
1. 2. 3. 4. 5. 6. 7. 8. 9.
用公式简化你的Excel工作
公式简介 常用的计算公式和函数 一次键入和自动填充 引用格式 逻辑公式 文字处理公式 与时间相关的公式 其他 选择性粘贴
2. 3.
lookup(查找的数值，查找的数所在列或行，返回值所在列或行) 查找的数值所在的列必须为升序排列； 为不精确查询。
与时间相关的公式
您所看到的日期与 Excel 的内部日期表示是不同的。对您来说，2005 年 8 月 22 日是一个带有年、月、日的日期。而对 Excel 来说，2005 年 8 月 22 日是序列 数 38586。 对于 Excel，凌晨 12:00 点则是 0，而中午 12:00 点则是小数 0.5。
查找更多的函数
2.
填写函数参数。
填写参数
参数帮助
打印公式
公式在一般情况下仅显示结果。 也可以显示工作表中的所有公式， 然后再隐藏所有公式。 “工具” “公式审核” “公式 审核模式”。要隐藏公式，再单击 “公式审核模式”。 快捷键CTRL+`（“1”键旁边）组 合键来显示和隐藏公式。
灵活运用公式的案例
If(A1>60,”pass”,”fail”). If(and(A1>60,A2>60),”all pass”, ”fail”).
If可以与其他的命令组合使用，例如：
Sumif：sumif(区域，”>0”，求和区域） Countif： countif(区域，”>0”）
逻辑公式
True False {1，0} And()，OR(), Not() 在Excel公式中，逻辑判断中使用到and或or的格式是： and（ ， ）；or（ ， ）;not( ) 例如 If(and(A1>1,B1>1,C1>1),1,0) If(or(A1>1,B1>1,C1>1),1,0) If(not(A1>1),1,0)
与时间相关的公式
Now(),返回当前的时间。 Today(),返回当前的日期。 A1-B1:计算两个日期之前的天数。 Year(A1),moth(A1),day(A1),hour(A1),minute(A1),Second (A1):返回某事件表示的各细项。 Networkdays(A1,B1):计算两个日期之前的工作日天数。 Workday(A1,B1,C1)：从某日期开始，经过指定数目的工 作日之后的日期 。
给区域命名
为了方面的撰写公式，Excel 可以给区域进行命名
命名： 选择区域，在名称框内写入名称即可。 或者 插入 名称 定义，选择区域与名称 删除命名： 插入 名称
定义， 选择名称，然后删除
查找更多的函数
1.
“插入”
”函数”，选择函数
搜索需要 的函数
函数分类
选择具体的函数 选中函数的 简易帮助 选中函数的 详细帮助
省份 安徽 安徽 安徽 安徽 安徽 安徽 安徽 安徽 安徽 安徽 北京 北京 甘肃 甘肃 甘肃 甘肃 ASNAME 邓中亚 邓中亚 邓中亚 邓中亚 邓中亚 邓中亚 邓中亚 邓中亚 邓中亚 邓中亚 孙春渊 孙春渊 张志学 张志学 张志学 张志学 Q8.1_HR 3 4 4 4 4 4 4 4 1 5 4 4 5 4 5 5 Q8B_HR 5 3 5 3 3 5 3 5 5 5 5 5 5 5 5 3 Q8C_HR 3 3 5 3 3 5 3 5 5 5 5 5 5 5 5 3 Q8D_HR 5 1 5 2 2 5 2 5 5 5 5 5 3 4 5 5 Q23_HR . . 4 4 . 4 . . 5 . 5 4 4 . 4 4 Q24_HR 2 . 4 0 . . . . 1 . 2 1 1 . 5 2
必须记住的函数
常用计算公式和函数：
运算符号：+、-、*、/、^ 逻辑判断符号：=, >,<, <>, >=, <= 逻辑运算函数：And( , ) , Or( , ) Not( ) 条件函数：If( , , ) 数字计算 Average(A1:A10) 平均数 Sum(A1:E10) 总数 max(A1:E10) 最大值 min(A1:E10) 最小值 Count(A1:E10) 计数 ABS() 绝对值 Rnd() 随机数 + * 也可以用于逻辑运算
用公式处理文本
Upper: 把文本全部转换为大写。 Lower: 把文本全部转换为小写。 Proper: 把文本转换为“恰当”形式 （每一个单词的第一个字母大写）。
Left:从字符串的开始返回特定数量的字符。
Left(“Add012”,3)=“ADD”
Right:从字符串的尾部返回特定数量的字符。
注意：分列会由一列生成多列，所以在分之前一定要预留空白位置， 以防止覆盖掉后面的数据。
高级筛选
“数据区域”选择所有的数 据和表头； “条件区域”选择筛选条件， 如例题； 可以选择不重复的记录； 可以选择将筛选结果复制到 其他位置。
条件格式
条件格式可以突出显示符合条件的单元格。例如，可以通 过设置使区域内的所有负数背景颜色为红色。 条件格式为动态的，如果把单元格写入一个负数，既可以 出现红色背景，改为正数则背景消失。
Left(A2,find(“”,A2)-1) Right(A2,len(A2)-find(“”,A2))
查找数值——Vlookup,Hlookup,lookup
目的：查找一个值，返回另一个值。例如，查找姓名，返回电话号码。 查询分成两种：
精确查询：查询的数值如果不存在则输出不存在的标志； 不精确查询：查询的数值如果不存在则输出小于此数的最大值所对应的值。
图表向导第二步
可以通过选择系 列产生在“行” 或“列”，来选 择数据比较的方 式。
“系列”选项卡
在该选项卡上，您可以为图表删 除或添加数据系列。例如，您可 能决定只将其中两个月的数据插 入图表，而不是将您在工作表上 选择的所有三个月的数据插入图 表。通过该选项卡可以进行更改， 而无须返回到工作表，并且可以 预览所做的更改。 注意 在该选项卡上删除或添加 数据系列时，并不会改变工作表 上的数据。
1，在两个名单里面查找匹配 2，在同一个名单里面找出重复的样本 3 3，用公式完成报告 4，随机抽选样本
目录
1. 2.
1. 2. 3. 4. 5.
公式和函数 其他功能
分列 高级筛选 条件格式 分类汇总 数据有效性
3.
图表的制作
分列
分列可以将一条数据转换为多条数据： 操作步骤
Step 1:”数据” ”分列”。 Step 2:选择分列方式：分割符号 或者 固定宽度 Step 3:在相应的位置填写分列的符号，例如本例在“其他”栏中填写“.” 。 Step 4:下一步 完成，即可。
Excel培训—中级
Excel
中级培训目标----Excel方法论
能够熟练的运用各种公式来完成Excel计算任务。 方便的处理Excel中的文字信息。 对Excel提供的各种功能有所了解，并且能够灵活的在日 Excel 常工作中运用。 绘制图表，美化图表。
中级培训内容----Excel方法论
1.
公式错误
有时 Excel 会由于公式错误而无法进行计算。如果出现这种情况，您 将在单元格中看到一个错误值，而不是结果。以下是三个常见的错误 值：
##### 列的宽度不够显示该单元格中的内容。可以通过下列方法进行改 正：增加列宽、缩减内容以适应列宽或者应用其他数字格式。 #REF! 单元格引用无效。单元格可能被删除或粘贴覆盖。
目录
1. 2. 3.
1. 2. 3. 4.
公式和函数 其他功能 图表的制作
创建图表 选择合适的图表类型 复杂图表制作 选择具有专业外观的图表
创建图表
图表可以分为内嵌图表和独立图表两种。 嵌入图表是指该图表作为“对象”与数据放置在同一张 工作表上。它可以移动和调整大小。它还可以与源数据 一起打印。 独立图表为单独的一页，可以直接打印 创建图表的两种方法 1．用图表向导创建图表 2．用“图表”工具栏创建简单的图表
vlookup:进行垂直查询
Vlookup(查找的数值，查找的数列区域，返回值所在第几列，True(False)) True或确省，代表不精确匹配，查找列必须为升序排列。 False，代表精确匹配，不需按顺序排列。
Hlookup：进行水平查询
同Vlookup相同，只不过把列操作变为行操作。
Lookup:查询
注意：
& 不是逻辑运算符号
自动计算部分
点击右键在表格下方区域，同时可以完成相同的命令。
高级计算
Sumproduct：乘积和
Sumproduct（数据列1：数据列2பைடு நூலகம் 主要用于加权计算、知道样本量与百分比求总体等
Rank:排序
rank(number,区域，0或1)，0为降序，1为升序。
条件函数
If是Excel常用的命令。它的基本语句是: If（逻辑判断，true-value，false-value） 例如：
条件格式的使用
“格式” ”条件格式”，打开下列对话框。
选择是数值 还是公式
选择条件，如大于， 小于，介于等
条件限制
选择满足 条件的格式
添加其他条件， 最多三个条件
条件格式的使用
条件格式可以用于：
自动标记出错误、异常、值得注意的数据 自动描绘整体表格格式
分类汇总
选择要进行分类汇总的单元格区域。如下列数据
公式和函数简介
公式由下列几个元素组成：
运算符，如“+”，“*”； 单元格（或区域）引用； 数值或文本； 工作表函数，如“sum”， “average”；
例如：
=sum(A1*B1,A2*B2,A3*B3)/3
函数是可以嵌套的。 例如：
=sum(average(A1： C1),average(A2:C2))
使用图表向导创建图表
图表用于将数据转化成图片。
图表向导第一步
Step1：选择希望插入图表中的 数据，同时选择列标题和行 标题 。 Step2：单击工具栏上的“图表 向导” 按钮打开“图表向 导”。 Step3：当该向导打开时，会自 动选择柱形图类型。您可以 方便地选择其他图表类型 。 Step4：接下来，您可以单击向 导底部的“完成”按钮 。
Right(“Add012”,3)=012
Mid:从字符串的任意位置开始返回特定数量的字符。
Mid(“Add012”,2,2)=“dd”
用公式处理文本
Trim：除了两个单词之间的单个空格，可以去掉文本参数中的所有空 格。 Clean：从字符串中去掉不能打印的字符。当引入某种类型数据时， 常会出现这些“垃圾”字符。 Exact：比较两个字符串是否完全相同。 Len：返回字符串中的字符个数。 Find: 查找某个字符在文本中的位置。 Concatenate：将两个单元格中的信息合并到一个单元格中 。 使用Left和Find函数分割名字
#NAME? 函数名拼写错误或者使用了 Excel 不能识别的名字。您应该知 道，带有错误值（如 #NAME?）的单元格可能显示一个彩色的三角形。
引用类型
相对 公式中的每个相对引用 单元格在公式被沿列或跨行复 制时都自动改变。 绝对 一个绝对单元格引用是 固定的。绝对引用在您将一个 公式从一个单元格复制到另一 个单元格时不发生变化。绝对 引用中包含美元符号 ($)，如 $D$9。 混合型 混合单元格引用既可 以包含一个绝对列和一个相对 行，也可以包含一个绝对行和 一个相对列。例如，$A1 是一 个到列 A 的绝对引用和到行 1 的相对引用。
其他功能 图表的制作
公式和函数简介
公式和函数使得电子表格变得非常有用。尤其是在处理大量数据的时候。 使用Excel中的公式来计算电子表格中的数据以得到结果。当数据更新后，无 需做额外的工作，公式将自动更新结果。
函数可以大大加强公式的功能和进行普通运算符不能完成的计算。Excel包括 300多个函数，甚至可以从第三方供应商处购买额外的专用函数甚至可以创 建自己的定值函数（VBA），但是通常使用的也只有十几个函数。
分类汇总
“数据” ”分类汇总” ，弹出如下对话框。
选择分类的标准 选择数据汇总的方式， 如求和，求平均值。 定需要汇总的数据列 汇总结果的 输出位置
分类汇总
汇总结果见右图。 注意分类字段必 须要排序。
有效性
Excel的数据有效性特性在很多方面类似于条件格式特性。 这个特性使用户可以建立一定的规则，它规定可以向单元 格中输入的内容。 例如，在某些指定单元格中只能输入。