EXCEL技能提升培训讲座.pptx

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

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

3、内置函数那样使用。,1.1 函数和公式,什么是公式：函数与公式既有区别又互相联系。如果说函数时Excel预先定义好的特殊公式，公式就是由用户自行设计对工作表进行计算和处理的公式。例：算术运算符：加减乘除等,=sum(E1:H1)*A1+26,比较运算符：大于、小于、等于、不等于等文本运算符：比如连接符号“&”连接多个文本 引用运算符：如SUM(A1:A30)中的冒号等,开始,函数,对单元格A1的引用,算术运算符,常量,1.2 函数的参数,函数右边括号中的部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。如=sum(number1,number2,),1.常量：

4、直接输入到单元格或公式中的数字或文本；=SUM(E1:H1)+262.逻辑值：TRUE（1）或FALSE（0）;=VLOOKUP(A1,$A$1:$F$7,2,0)3.数组：例SUM(A1:A5,B1:B5)错误值：#N/A，乱码；单元格引用：(例如E1:H1)；嵌套函数：一个函数是另一个函数参数=SUM(SUMIF(DBOM!$F:$F,$A12,DBOM!H:H)名称和标志：单元格或区域直接命名。如将$A:$F命名为1,1.3 函数输入方法,1.“插入函数”对话框,2.编辑栏/单元格直接输入,1.4 常用函数,文本函数：mid、left、right查找和定位函数：vlookup、match

5、计数与求和函数：count、sum、max、average逻辑函数：if、countif、sumif、sumifs,1.4.1 文本函数：MIDMID(text,start_num,num_chars)从文本字符串中指定的起始位置起返回指定长度的字符。,1.4.1 文本函数：LEFTLEFT(text,num_chars)从一个文本字符串的第一个字符开始返回指定个数的字符,1.4.1 文本函数：RIGHTRIGHT(text,num_chars)从一个文本字符串的最后一个字符开始返回指定个数的字符,1.4.2 查找函数：vlookup,Vlookup(lookup_value,table_ar

6、ray,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:

7、E6区域中包含数字的单元格的个数=count(D2:E6),1.4.5 求和函数：sumSUM(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 求平均值函数：av

8、erage,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)计算某个区域中满足给定条件的单元格数目。,例：计

9、算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,

10、=50),函数基础及常用函数,1,目录,数据处理,2,创建图表,3,排序筛选分类汇总分列数据有效性条件格式数据透视表,2.数据处理,2.1 排序,排序是为了快速的查找自己想要的数据，并按照自己想要的顺序排列，使用排序。,常用的几种排序：升序、降序；多条件排序；笔画排序、按行排序。,2.1 排序升序、降序、,选中【数据】，单击【排序和筛选】，选择【升序】、【降序】或【自 定义排序】。,单击【自定义排序】，弹出如图对话框。,2.1 排序多条件排序,选中【数据】，单击【自定义排序】。,单击【添加条件】，增加次要关键字。,2.1 排序笔画排序、按行排序,选中【数据】，单击【自定义排序】。,单击【选项】

11、，选择【笔划排序】、【按行排序】。,2.2 筛选,筛选是为了快速的挑出自己想要的数据，并按照自己想要的顺序排列，使用筛选。,常用的几种筛选：简单筛选；数字筛选；多条件筛选；自定义筛选。,2.2 筛选简单筛选,选中【数据】，单击【排序和筛选】，选择【筛选】。,点击筛选下拉标志，筛选想要的数据。,2.2 筛选数字筛选,点击筛选下拉标志，选择【数字筛选】。,2.2 筛选多条件筛选,添加辅助区域，并根据需求输入公式。,形成辅助区域。,2.2 筛选多条件筛选,选中任一单元格，在工具栏中找到【数据】中【排序和筛选】一 栏，选择【高级】。,出现如图所示对话框，在【列表区域】选中全部需要筛选的 数据。,2.2

12、 筛选多条件筛选,在【条件区域】中选中制作完成的辅助条件区域。,完成筛选。,2.2 筛选自定义筛选,点击筛选下拉标志，选择【自定义筛选】。,在自定义自动筛选方式界面设置筛选要求，设置后点击 确定即可筛选出符合要求的 数据。,2.3 分类汇总,在使用excel时常常会输入一些具有类似特性的数据，当数据的量特别大的时候，就无法在同一个屏幕中看到所有的数据类型，这时就可以将其按照各自的特性进行分组，对大量数 据进行快速分类汇总，然后在需要的时候点开某一个组的数据进行编辑。,常用的分类汇总：创建组与取消分组；自动分类汇总。,2.3 分类汇总-创建组与取消分组,通过分组的方式，把所有输入的数据按照其特性

13、“市场部”“行政部”“研发部”进行 分类。,首先选中“市场部”栏目下的所有数据的行，注意是选择整行，然后从【数据】选项卡 中找到【创建组】按钮，单击【创建组】在 所选数据行的左侧会出现一个黑色的竖线，竖线底端会出现一个减号。单击黑色竖线或 者减号即可将这个分组的分数折叠起来。,2.3 分类汇总-创建组与取消分组,依次对“行政部”和“研发部”的数据创建分组。创建好之后，可以将所有分组折叠起 来，这样显示的是三个标题”市场部“、”行政部“和”研发部“。当需要查看某一个分组中的数据时，只要单击相应的加号即可。通过创建分组，使数据表中的数据非常简 洁直观。,取消分组，将相应的数据选中，然后在【数据】选

14、项卡中单击【取消分组】即 可。,2.3 分类汇总-自动分类汇总,选择【数据】选项卡中【分类汇总】，就会出现一个对话框，按需求来选择想要汇总的 内容，选择好了直接按确定系统会自动把数 据给汇总好。,2.4 分列,编辑大量有规律的单列数据，使数据规范化，按照自己的要求进行多列表示和显示。,2.4 分列,选中数据，选择【数据】选项卡下的【分列】。,按照文本分列向导操作，选择数据分列格式，完成分列。,2.4 分列-指定长度分列,选中目标区域，选择【数据】选项卡下的【分列】调出文本分列向导,点击选中“固定宽度（w）”后，点击下一步,2.4 分列-指定长度分列,鼠标左键点击插入两条分隔线（身份证号码的7-

15、14位位置），点击下 一步。,忽略不在返回值中显示的字符段：在数据预览中选中要忽略的列，点不导入此列（跳过）（I）;对中间出生日期设定为日期格式目标区域选定=$B$6(分列返回 值在B2单元格位置列示）点击完成，返回结果,2.5 数据有效性,制作表格时，避免输入错误数据，规范输入的数据，并提醒填表人如何填写。,2.5 数据有效性,选择需要设置的区域或整行或整列，单击【数据】选项卡下的【数据有效性】按钮。,在弹出的【数据有效性】对话框 中的【设置】选项卡下，选择”允许”下面的“序列。根据需要设置是否“忽略空值”，在下面的“来源”文本框中输 入待选的列表项，各数值间以半 角逗号分隔，不要加引号，然

16、后 点击“确定”。,2.5 数据有效性,返回工作表，刚才选择的区域中已经有下拉箭头了，点击这个箭 头可弹出下拉列表。,2.6 条件格式,将一定要求的数据按照自己要求的格式显示，便于识别和提示。,选中区域，单击【开始】选项卡标签，点击【样式】功能区的“条件格式”，选择或新建单元格 数据规则。,2.7 数据透视表,选中数据区域，点击【插入】选项卡中【数据透视表】,如选中了数据区域，在【创建数据 透视表】中最上面的选择区域框可 以看到选中的单元格区域，如果没 有选中，可以在此选取。数据透视表可以选载在新工作表创 建和在当前工作表创建，如数据透 视表较大，内容很多，建议在新工 作表中生成透视表。完成设置后，点击“确定”。,2.7 数据透视表,excel中出现一个新工作表。左边是空白的透视表区域，右边是数据透视表 字段列表，可以拖动和设置。,把【姓名】字段拖到行标签位置，【剩余经费】拖动到【数值】位置，左边的数据透视表则按照不同的姓名 将剩余经费进行了汇总。行标签处可以插入多个，但是前后 顺序要根据自己的需求来选择,透视表数据 显示区域,2.7 数据透视表,数据透视表数据可以生成数据透视图；选中