EXCEL技能提升PPT课件

,EXCEL技能提升,人力资源部时间：2018年3月15日,遇到这个问题应该怎么办？,要把每个人的订单汇总起来，应该怎么办？,遇到这个问题应该怎么办？,又要按计算器加班到天亮了！,加班青年,遇到这个问题应该怎么办？,简单，选中汇总表，然后“=”，一个一个表加过去呗！=张三!D2+李四!D2+王五!D2+赵六!D2+陈七!D2,文艺青年,遇到这个问题应该怎么办？,走你青年,选中汇总表中的D2单元格，输入“=SUM(张三:陈七!D2)”下拉、右拉填充公式，搞定！,Excel软件提升了数据处理的效率,Excel软件提升了数据处理的效率,操作效率,数图呈现,数据分析,表格规范,掌握EXCEL常用函数及数据处理技能，提高工作效率。,培训目标,目录,1.函数基础,函数和公式函数的参数函数输入方法常用函数,1.1函数和公式,什么是函数：Excel函数即使预先定义，执行计算、分析等处理数据任务的特殊公式。如=sum(number1,number2,)其中“sum”称为函数名称，一个函数只有唯一一个名称，它决定了函数的功能和用途。函数名称后面一定是括号，里面的参数必须用逗号分割。,Excel函数可以分为内置函数和扩展函数两大类：内置函数只要启动了Excel，用户就可以使用它们；扩展函数必须通过启用“宏”命令加载，然后才能像内置函数那样使用。,1.1函数和公式,什么是公式：函数与公式既有区别又互相联系。如果说函数时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.名称和标志：单元格或区域直接命名。如将$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)从一个文本字符串的第一个字符开始返回指定个数的字符,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,lookup_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,)返回一组数值中的最大值，忽略逻辑值及文本,例：找出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)判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。,例：判断同一行中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,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排序升序、降序、,选中【数据】，单击【排序和筛选】，选择【升序】、【降序】或【自定义排序】。,单击【自定义排序】，弹出如图对话框。,2.1排序多条件排序,选中【数据】，单击【自定义排序】。,单击【添加条件】，增加次要关键字。,2.1排序笔画排序、按行排序,选中【数据】，单击【自定义排序】。,单击【选项】，选择【笔划排序】、【按行排序】。,2.2筛选,筛选是为了快速的挑出自己想要的数据，并按照自己想要的顺序排列，使用筛选。常用的几种筛选：简单筛选；数字筛选；多条件筛选；自定义筛选。,2.2筛选简单筛选,选中【数据】，单击【排序和筛选】，选择【筛选】。,点击筛选下拉标志，筛选想要的数据。,2.2筛选数字筛选,点击筛选下拉标志，选择【数字筛选】。,2.2筛选多条件筛选,添加辅助区域，并根据需求输入公式。,形成辅助区域。,2.2筛选多条件筛选,选中任一单元格，在工具栏中找到【数据】中【排序和筛选】一栏，选择【高级】。,出现如图所示对话框，在【列表区域】选中全部需要筛选的数据。,2.2筛选多条件筛选,在【条件区域】中选中制作完成的辅助条件区域。,完成筛选。,2.2筛选自定义筛选,点击筛选下拉标志，选择【自定义筛选】。,在自定义自动筛选方式界面设置筛选要求，设置后点击确定即可筛选出符合要求的数据。,2.3分类汇总,在使用excel时常常会输入一些具有类似特性的数据，当数据的量特别大的时候，就无法在同一个屏幕中看到所有的数据类型，这时就可以将其按照各自的特性进行分组，对大量数据进行快速分类汇总，然后在需要的时候点开某一个组的数据进行编辑。常用的分类汇总：创建组与取消分组；自动分类汇总。,2.3分类汇总-创建组与取消分组,通过分组的方式，把所有输入的数据按照其特性“市场部”“行政部”“研发部”进行分类。,首先选中“市场部”栏目下的所有数据的行，注意是选择整行，然后从【数据】选项卡中找到【创建组】按钮，单击【创建组】在所选数据行的左侧会出现一个黑色的竖线，竖线底端会出现一个减号。单击黑色竖线或者减号即可将这个分组的分数折叠起来。,2.3分类汇总-创建组与取消分组,依次对“行政部”和“研发部”的数据创建分组。创建好之后，可以将所有分组折叠起来，这样显示的是三个标题”市场部“、”行政部“和”研发部“。当需要查看某一个分组中的数据时，只要单击相应的加号即可。通过创建分组，使数据表中的数据非常简洁直观。,取消分组，将相应的数据选中，然后在【数据】选项卡中单击【取消分组】即可。,2.3分类汇总-自动分类汇总,选择【数据】选项卡中【分类汇总】，就会出现一个对话框，按需求来选择想要汇总的内容，选择好了直接按确定系统会自动把数据给汇总好。,2.4分列,编辑大量有规律的单列数据，使数据规范化，按照自己的要求进行多列表示和显示。,2.4分列,选中数据，选择【数据】选项卡下的【分列】。,按照文本分列向导操作，选择数据分列格式，完成分列。,2.4分列-指定长度分列,选中目标区域，选择【数据】选项卡下的【分列】调出文本分列向导,点击选中“固定宽度（w）”后，点击下一步,2.4分列-指定长度分列,鼠标左键点击插入两条分隔线（身份证号码的7-14位位置），点击下一步。,1.忽略不在返回值中显示的字符段：在数据预览中选中要忽略的列，点不导入此列（跳过）（I）;2.对中间出生日期设定为日期格式3.目标区域选定=$B$6(分列返回值在B2单元格位置列示）4.点击完成，返回结果,2.5数据有效性,制作表格时，避免输入错误数据，规范输入的数据，并提醒填表人如何填写。,2.5数据有效性,选择需要设置的区域或整行或整列，单击【数据】选项卡下的【数据有效性】按钮。,在弹出的【数据有效性】对话框中的【设置】选项卡下，选择”允许”下面的“序列。根据需要设置是否“忽略空值”，在下面的“来源”文本框中输入待选的列表项，各数值间以半角逗号分隔，不要加引号，然后点击“确定”。,2.5数据有效性,返回工作表，刚才选择的区域中已经有下拉箭头了，点击这个箭头可弹出下拉列表。,2.6条件格式,将一定要求的数据按照自己要求的格式显示，便于识别和提示。,选中区域，单击【开始】选项卡标签，点击【样式】功能区的“条件格式”，选择或新建单元格数据规则。,2.7数据透视表,选中数据区域，点击【插入】选项卡中【数据透视表】,如选中了数据区域，在【创建数据透视表】中最上面的选择区域框可以看到选中的单元格区域，如果没有选中，可以在此选取。数据透视表可以选载在新工作表创建和在当前工作表创建，如数据透视表较大，内容很多，建议在新工作表中生成透视表。完成设置后，点击“确定”。,2.7数据透视表,excel中出现一个新工作表。左边是空白的透视表区域，右边是数据透视表字段列表，可以拖动和设置。,把【姓名】字段拖到行标签位置，【剩余经费】拖动到【数值】位置，左边的数据透视表则按照不同的姓名将剩余经费进行了汇总。行标签处可以插入多个，但是前后顺序要根据自己的需求来选择。,透视表数据显示区域,2.7数据透视表,数据透视表数据可以生成数据透视图；选中透视表上任意一个单元格，选择【选项】选项卡中【数据透视图】，弹出【插入图表】。,选择一个合适的图表类型，点击“确定“按钮。就可以自动生成数据透视图。,目录,3.1创建图表,1.方法一：一键生成法选择制作图表的数据按下。2.方法二：利用图表向导（推荐）单击【插入】选项