EXCEL技能提升

1、,EXCEL技能提升,人力资源部 时间：2018年3月15日,遇到这个问题应该怎么办？,要把每个人的订单汇总起来，应该怎么办？,遇到这个问题应该怎么办？,又要按计算器加班到天亮了！,加班青年,遇到这个问题应该怎么办？,简单，选中汇总表，然后“=”，一个一个表加过去呗！ =张三!D2+李四!D2+王五!D2+赵六!D2+陈七!D2,文艺青年,遇到这个问题应该怎么办？,走你青年,选中汇总表中的D2单元格， 输入“=SUM(张三:陈七!D2)” 下拉、右拉填充公式，搞定！,Excel软件提升了数据处理的效率,Excel软件提升了数据处理的效率,操作 效率,数图 呈现,数据 分析,表格 规 范,掌握E

2、XCEL常用函数及数据处理技能，提高工作效率。,培训目标,目 录,1. 函数基础,函数和公式 函数的参数 函数输入方法 常用函数,1.1 函数和公式,什么是函数： Excel函数即使预先定义，执行计算、分析等处理数据任务的特殊公式。 如=sum(number1,number2,) 其中“sum”称为函数名称，一个函数只有唯一一个名称，它决定了函数的功能和用途。 函数名称后面一定是括号，里面的参数必须用逗号分割。,Excel函数可以分为内置函数和扩展函数两大类： 内置函数只要启动了Excel，用户就可以使用它们； 扩展函数必须通过启用“宏”命令加载，然后才能像内置函数那样使用。,1.1 函数和公

3、式,什么是公式： 函数与公式既有区别又互相联系。 如果说函数时Excel预先定义好的特殊公式， 公式就是由用户自行设计对工作表进行计算和处理的公式。 例： =sum(E1:H1) * A1 + 26,算术运算符：加减乘除等 比较运算符：大于、小于、等于、不等于等 文本运算符：比如连接符号“=VLOOKUP(A1,$A$1:$F$7,2,0) 3. 数组：例SUM(A1:A5,B1:B5) 4. 错误值：#N/A，乱码； 5. 单元格引用：(例如E1:H1)； 6. 嵌套函数：一个函数是另一个函数参数=SUM(SUMIF(DBOM!$F:$F,$A12,DBOM!H:H) 7. 名称和标志：单元

4、格或区域直接命名。如将$A:$F命名为1,1.3 函数输入方法,1. “插入函数”对话框,2. 编辑栏/单元格直接输入,1.4 常用函数,文本函数：mid、left、right 查找和定位函数： vlookup、match 计数与求和函数：count、sum、max、average 逻辑函数： if、countif、sumif、sumifs,1.4.1 文本函数：MID,MID(text, start_num, num_chars) 从文本字符串中指定的起始位置起返回指定长度的字符。,1.4.1 文本函数：LEFT,LEFT(text, num_chars) 从一个文本字符串的第一个字符开始返

5、回指定个数的字符,1.4.1 文本函数：RIGHT,RIGHT(text, num_chars) 从一个文本字符串的最后一个字符开始返回指定个数的字符,1.4.2 查找函数：vlookup,Vlookup(lookup_value,table_array,clo_index_num,range_lookup) 搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。默认情况下，表是以升序排序的。,例：根据考试成绩查找成绩评价=VLOOKUP(B2,$F$2:$G$5,2,TRUE),1.4.3 定位函数：match,Match(lookup_value,lo

6、okup_array,match_type) 返回符合特定值特定顺序的项在数组中的相对位置,=MATCH(22,H2:H6,0),1.4.4 计数函数：count,Count(value1, value1) 计算区域中包含数字的单元格的个数,例：计算D2:E6区域中包含数字的单元格的个数 =count(D2:E6),1.4.5 求和函数：sum,SUM(number1, number2) 计算单元格区域中所有数值的和,例：计算D2:E6区域中所有数值的和 =SUM(D2:E6),1.4.6 求最大值函数：max,Max(number1,number2,) 返回一组数值中的最大值，忽略逻辑值及文

7、本,例：找出D2:E6区域中最大的数值 =MAX(D2:E6),1.4.7 求最小值函数：min,Min(number1,number2,) 返回一组数值中的最小值，忽略逻辑值及文本,例：找出D2:E6区域中最小的数值 =MAX(D2:E6),1.4.8 求平均值函数：average,Average(number1,number2,) 返回其参数的算术平均值；参数可以是数值或包含数值的名称、数组或引用,例：计算D2:E6区域数值的算术平均数 =Average(D2:E6),1.4.9 逻辑函数：if,IF(logical_test,value_if_true,value_if_false) 判

8、断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。,例：判断同一行中D列数字是否大于E列数字 =IF(D2E2,“OK”,“NG),1.4.10 逻辑函数：countif,countif(range,criteria) 计算某个区域中满足给定条件的单元格数目。,例：计算D2:E6区域中出现数字“5”的数目 =COUNTIF(D2:E6,5),1.4.11 逻辑函数：sumif,sumif(range,criteria,sum_range) 对满足条件的单元格求和,例：计算销售额不小于50万的区域总净利润 =SUMIF(B2:B15,=50),1.4.11 逻辑函数：sumifs

9、,sumifs(sum_range,criteria_range1,criteria1,riteria_range2,criteria2.) 对一组给定条件指定的单元格求和,例：计算A、B产品销售额都不小于50万的区域总净利润 =SUMIFS(D2:D15,B2:B15,=50,C2:C15,=50),目 录,排序 筛选 分类汇总 分列 数据有效性 条件格式 数据透视表,2. 数据处理,2.1 排序,排序是为了快速的查找自己想要的数据，并按照自己想要的顺序排列，使用排序。 常用的几种排序： 升序、降序； 多条件排序； 笔画排序、按行排序。,2.1 排序升序、降序、,选中【数据】，单击【排序和筛

10、选】，选择【升序】、 【降序】或 【自定义排序】。,单击【自定义排序】，弹出如图对话框。,2.1 排序多条件排序,选中【数据】， 单击【自定义排序】。,单击【添加条件】， 增加次要关键字。,2.1 排序笔画排序、按行排序,选中【数据】， 单击【自定义排序】。,单击【选项】， 选择【笔划排序】、【按行排序】。,2.2 筛选,筛选是为了快速的挑出自己想要的数据，并按照自己想要的顺序排列，使用筛选。 常用的几种筛选： 简单筛选； 数字筛选； 多条件筛选； 自定义筛选。,2.2 筛选简单筛选,选中【数据】，单击【排序和筛选】，选择【筛选】。,点击筛选下拉标志， 筛选想要的数据。,2.2 筛选数字筛选,

11、点击筛选下拉标志， 选择【数字筛选】。,2.2 筛选多条件筛选,添加辅助区域，并根据需求 输入公式。,形成辅助区域。,2.2 筛选多条件筛选,选中任一单元格，在工具栏中找到【数据】中【排序和筛选】一栏，选择【高级】。,出现如图所示对话框，在【列表区域】选中全部需要筛选的数据。,2.2 筛选多条件筛选,在【条件区域】中选中制作完成的辅助条件区域。,完成筛选。,2.2 筛选自定义筛选,点击筛选下拉标志， 选择【自定义筛选】。,在自定义自动筛选方式界面 设置筛选要求，设置后点击 确定即可筛选出符合要求的 数据。,2.3 分类汇总,在使用excel时常常会输入一些具有类似特性的数据，当数据的量特别大的

12、时候，就无法在同一个屏幕中看到所有的数据类型，这时就可以将其按照各自的特性进行分组，对大量数据进行快速分类汇总，然后在需要的时候点开某一个组的数据进行编辑。 常用的分类汇总： 创建组与取消分组； 自动分类汇总。,2.3 分类汇总-创建组与取消分组,通过分组的方式，把所有输入的数据按照其特性“市场部”“行政部”“研发部”进行分类。,首先选中“市场部”栏目下的所有数据的行，注意是选择整行，然后从【数据】选项卡中找到【创建组】按钮，单击【创建组】在所选数据行的左侧会出现一个黑色的竖线，竖线底端会出现一个减号。单击黑色竖线或者减号即可将这个分组的分数折叠起来。,2.3 分类汇总-创建组与取消分组,依次

13、对“行政部”和“研发部”的数据创建分组。创建好之后，可以将所有分组折叠起来，这样显示的是三个标题”市场部“、”行政部“和”研发部“。当需要查看某一个分组中的数据时，只要单击相应的加号即可。通过创建分组，使数据表中的数据非常简洁直观。,取消分组，将相应的数据选中，然后在【数据】选项卡中单击【取消分组】即可。,2.3 分类汇总-自动分类汇总,选择【数据】选项卡中【分类汇总】，就会出现一个对话框，按需求来选择想要汇总的内容，选择好了直接按确定系统会自动把数据给汇总好。,2.4 分列,编辑大量有规律的单列数据，使数据规范化，按照自己的要求进行多列表示和显示。,2.4 分列,选中数据， 选择【数据】选项

14、卡下的 【分列】。,按照文本分列向导操作，选择 数据分列格式，完成分列。,2.4 分列-指定长度分列,选中目标区域， 选择【数据】选项卡下的 【分列】调出文本分列向导,点击选中“固定宽度（w）”后，点击下一步,2.4 分列-指定长度分列,鼠标左键点击插入两条分隔线（身份证号码的7-14位位置），点击下一步。,1. 忽略不在返回值中显示的字符段：在数据预览中选中要忽略的列，点不导入此列（跳过）（I）; 2. 对中间出生日期设定为日期格式 3. 目标区域选定=$B$6(分列返回值在B2单元格位置列示） 4. 点击完成，返回结果,2.5 数据有效性,制作表格时，避免输入错误数据，规范输入的数据，并提

15、醒填表人如何填写。,2.5 数据有效性,选择需要设置的区域或整行或整列，单击【数据】选项卡下的【数据有效性】按钮。,在弹出的【数据有效性】对话框中的【设置】选项卡下，选择”允许”下面的“序列。 根据需要设置是否“忽略空值”，在下面的“来源”文本框中输入待选的列表项，各数值间以半角逗号分隔，不要加引号，然后点击“确定”。,2.5 数据有效性,返回工作表，刚才选择的区域中已经有下拉箭头了，点击这个箭头可弹出下拉列表。,2.6 条件格式,将一定要求的数据按照自己要求的格式显示，便于识别和提示。,选中区域，单击【开始】选项卡标签，点击【样式】功能区的“条件格式”，选择或新建单元格数据规则。,2.7 数

16、据透视表,选中数据区域，点击【插入】选项卡中 【数据透视表】,如选中了数据区域，在【创建数据透视表】中最上面的选择区域框可以看到选中的单元格区域，如果没有选中，可以在此选取。 数据透视表可以选载在新工作表创建和在当前工作表创建，如数据透视表较大，内容很多，建议在新工作表中生成透视表。 完成设置后，点击“确定”。,2.7 数据透视表,excel中出现一个新工作表。左边是空白的透视表区域，右边是数据透视表字段列表，可以拖动和设置。,把【姓名】字段拖到行标签位置， 【剩余经费】拖动到【数值】位置，左边的数据透视表则按照不同的姓名将剩余经费进行了汇总。 行标签处可以插入多个，但是前后顺序要根据自己的需求来选择。,透视表数据显示区域,2.7 数据透视表,数据透视表数据可以生成数据透视图； 选中透视表上任意一个单元格，选择 【选项】选项卡中【数据透视图】， 弹出【插入图表】。,选择一个合适的图表类型，点击 “确定“按钮。就可以自动生成数据透视图。,目 录,3.1 创建图表,1. 方法一：一键生成法 选择制作图表的数据按下。 2. 方法二：利用图表向导（推荐） 单击【插入】选项卡