EXCEL技能提升

EXCEL技能提升人力资源部时间：2018年3月15日遇到这个问题应该怎么办？要把每个人的订单汇总起来，应该怎么办？遇到这个问题应该怎么办？又要按计算器加班到天亮了！加班青年遇到这个问题应该怎么办？简单，选中汇总表，然后“=”，一个一个表加过去呗！=张三!D2+李四!D2+王五!D2+赵六!D2+陈七!D2文艺青年遇到这个问题应该怎么办？走你青年选中汇总表中的D2单元格，输入“=SUM(张三:陈七!D2)”下拉、右拉填充公式，搞定！Excel软件提升了数据处理的效率算筹算盘计算器ExcelExcel软件提升了数据处理的效率操作效率数图呈现数据分析表格规范掌握EXCEL常用函数及数据处理技能，提高工作效率。培训目标函数基础及常用函数1目录数据处理2创建图表31.函数基础1.函数和公式2.函数的参数3.函数输入方法4.常用函数1.1函数和公式什么是函数：Excel函数即使预先定义，执行计算、分析等处理数据任务的特殊公式。如=sum(number1,number2,……)其中“sum”称为函数名称，一个函数只有唯一一个名称，它决定了函数的功能和用途。函数名称后面一定是括号，里面的参数必须用逗号分割。Excel函数可以分为内置函数和扩展函数两大类：内置函数只要启动了Excel，用户就可以使用它们；扩展函数必须通过启用“宏”命令加载，然后才能像内置函数那样使用。1.1函数和公式什么是公式：函数与公式既有区别又互相联系。如果说函数时Excel预先定义好的特殊公式，公式就是由用户自行设计对工作表进行计算和处理的公式。例：=sum(E1:H1)*A1+26算术运算符：加减乘除等比较运算符：大于、小于、等于、不等于等文本运算符：比如连接符号“&”连接多个文本引用运算符：如SUM(A1:A30)中的冒号等开始函数对单元格A1的引用算术运算符常量1.2函数的参数函数右边括号中的部分称为参数，假如一个函数可以使用多个参数，那么参数与参数之间使用半角逗号进行分隔。如=sum(number1,number2,……)1.常量：直接输入到单元格或公式中的数字或文本；=SUM(E1:H1)+262.逻辑值：TRUE（1）或FALSE（0）;=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命名为11.3函数输入方法1.“插入函数”对话框2.编辑栏/单元格直接输入1.4常用函数•文本函数：mid、left、right•查找和定位函数：vlookup、match•计数与求和函数：count、sum、max、average•逻辑函数：if、countif、sumif、sumifsABC1数据SprocommTechnologiesCo.,Ltd.2公式公式结果说明（结果）3=mid(B1,1,6)Sproco上面字符串中的6个字符，从第一个字符开始(Sproco)4=mid(B1,9,13)Technologies上面字符串中的13个字符，从第九个字符开始(空格Technologies)5=mid(B1,35,5)因为要提取的第一个字符的位置大于字符串的长度，所以返回空文本()1.4.1文本函数：MIDMID(text,start_num,num_chars)从文本字符串中指定的起始位置起返回指定长度的字符。ABC1数据SprocommTechnologiesCo.,Ltd.2公式公式结果说明（结果）3=left(B1,6)Sproco上面字符串中，从左边第一个字符开始共6个字符(Sproco)4=left(B2,11)SprocommTe上面字符串中，从左边第一个字符开始共11个字符(SprocommTe)1.4.1文本函数：LEFTLEFT(text,num_chars)从一个文本字符串的第一个字符开始返回指定个数的字符ABC1数据SprocommTechnologiesCo.,Ltd.2公式1公式结果说明（结果）3=right(B1,6).,Ltd.上面字符串中，从右边第一个字符开始共6个字符(.,Ltd.)4=right(B1,11)esCo.,Ltd.上面字符串中，从右边第一个字符开始共11个字符(esCo.,Ltd.)1.4.1文本函数：RIGHTRIGHT(text,num_chars)从一个文本字符串的最后一个字符开始返回指定个数的字符1.4.2查找函数：vlookupVlookup(lookup_value,table_array,clo_index_num,range_lookup)搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。默认情况下，表是以升序排序的。例：根据考试成绩查找成绩评价=VLOOKUP(B2,$F$2:$G$5,2,TRUE)1.4.3定位函数：matchMatch(lookup_value,lookup_array,match_type)返回符合特定值特定顺序的项在数组中的相对位置=MATCH(22,H2:H6,0)1.4.4计数函数：countCount(value1,value1…)计算区域中包含数字的单元格的个数例：计算D2:E6区域中包含数字的单元格的个数=count(D2:E6)1.4.5求和函数：sumSUM(number1,number2…)计算单元格区域中所有数值的和例：计算D2:E6区域中所有数值的和=SUM(D2:E6)1.4.6求最大值函数：maxMax(number1,number2,……)返回一组数值中的最大值，忽略逻辑值及文本例：找出D2:E6区域中最大的数值=MAX(D2:E6)1.4.7求最小值函数：minMin(number1,number2,……)返回一组数值中的最小值，忽略逻辑值及文本例：找出D2:E6区域中最小的数值=MAX(D2:E6)1.4.8求平均值函数：averageAverage(number1,number2,……)返回其参数的算术平均值；参数可以是数值或包含数值的名称、数组或引用例：计算D2:E6区域数值的算术平均数=Average(D2:E6)1.4.9逻辑函数：ifIF(logical_test,value_if_true,value_if_false)判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。例：判断同一行中D列数字是否大于E列数字=IF(D2E2,“OK”,“NG)1.4.10逻辑函数：countifcountif(range,criteria)计算某个区域中满足给定条件的单元格数目。例：计算D2:E6区域中出现数字“5”的数目=COUNTIF(D2:E6,5)1.4.11逻辑函数：sumifsumif(range,criteria,sum_range)对满足条件的单元格求和例：计算销售额不小于50万的区域总净利润=SUMIF(B2:B15,=50)1.4.11逻辑函数：sumifssumifs(sum_range,criteria_range1,criteria1,[riteria_range2,criteria2]...)对一组给定条件指定的单元格求和例：计算A、B产品销售额都不小于50万的区域总净利润=SUMIFS(D2:D15,B2:B15,=50,C2:C15,=50)函数基础及常用函数1目录数据处理2创建图表31.排序2.筛选3.分类汇总4.分列5.数据有效性6.条件格式7.数据透视表2.数据处理2.1排序排序是为了快速的查找自己想要的数据，并按照自己想要的顺序排列，使用排序。常用的几种排序：1.升序、降序；2.多条件排序；3.笔画排序、按行排序。2.1排序—升序、降序、选中【数据】，单击【排序和筛选】，选择【升序】、【降序】或【自定义排序】。单击【自定义排序】，弹出如图对话框。2.1排序—多条件排序选中【数据】，单击【自定义排序】。单击【添加条件】，增加次要关键字。2.1排序—笔画排序、按行排序选中【数据】，单击【自定义排序】。单击【选项】，选择【笔划排序】、【按行排序】。2.2筛选筛选是为了快速的挑出自己想要的数据，并按照自己想要的顺序排列，使用筛选。常用的几种筛选：1.简单筛选；2.数字筛选；3.多条件筛选；4.自定义筛选。2.2筛选—简单筛选选中【数据】，单击【排序和筛选】，选择【筛选】。点击筛选下拉标志，筛选想要的数据。2.2筛选—数字筛选点击筛选下拉标志，选择【数字筛选】。2.2筛选—多条件筛选添加辅助区域，并根据需求输入公式。形成辅助区域。2.2筛选—多条件筛选选中任一单元格，在工具栏中找到【数据】中【排序和筛选】一栏，选择【高级】。出现如图所示对话框，在【列表区域】选中全部需要筛选的数据。2.2筛选—多条件筛选在【条件区域】中选中制作完成的辅助条件区域。完成筛选。2.2筛选—自定义筛选点击筛选下拉标志，选择【自定义筛选】。在自定义自动筛选方式界面设置筛选要求，设置后点击确定即可筛选出符合要求的数据。2.3分类汇总在使用excel时常常会输入一些具有类似特性的数据，当数据的量特别大的时候，就无法在同一个屏幕中看到所有的数据类型，这时就可以将其按照各自的特性进行分组，对大量数据进行快速分类汇总，然后在需要的时候点开某一个组的数据进行编辑。常用的分类汇总：1.创建组与取消分组；2.自动分类汇总。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条件格式将一定要求的数据按照自己要求的格式显示，便于识别和提示。选中区域，单击【开始】选项卡标签，