Excel应用技巧培训

Excel应用技巧培训培训内容一、数据快速录入二、文本信息处理三、数据管理与分析四、应用图表一、数据快速录入数据录入技巧巧用下拉列表快速输入数据快捷键的使用Ctrl+；输入当前日期Ctrl+Shift+；输入当前时间Alt+0165输入人民币符号￥Ctrl+1显示“单元格格式”对话框Alt+=用SUM函数插入自动求和公式函数、公式的应用日期时间函数：date();time();now();today()逻辑判断函数：if()实例员工档案管理实例员工档案管理（1）输入当前时间函数：=now()实例员工档案管理（2）输入性别、学历时，巧用下拉列表快速输入数据实例员工档案管理（2）输入性别、学历时，巧用下拉列表快速输入数据实例员工档案管理（3）输入计算公式年龄:(today()-D4)/365；工龄:(today()-E4)/365+1实例员工档案管理（4）逻辑判断函数判断是否退休：IF(C4=男,(IF(H4=60,已退休,)),(IF(H4=55,已退休,)))文本合并（&命令）A&BAB实例：合并姓与名两列字符提取函数（leftrightmid命令）left(D3,3)表示从D3单元格中提取3个字符；mid(D3,2,3)表示从D3单元格中第2个字符开始提取后面的3个字符；实例：从姓名列中提取姓，然后与职务结合成新列=left(D3,1)&G3二、文本信息处理替换（substitute命令）substitute(D3,”old-text”,”new-text”)实例：市场部改名为销售部；员工编号001升级为60001=substitute(F3,”市场部”,”销售部”)=substitute(A3,A3,60&A3)字符转换（properupperlower命令）proper(D3)将字符串第一个字母大写，其余小写实例：修改员工英文名字二、文本信息处理文本比较（exact命令）exact(A3,B3)结果为true，表示匹配，用于相同表格核对；可核对是否有大小写不同或有无空格exact(A3,B$3:B$21)结果为true，表示A3在B列中存在实例:二、文本信息处理三、数据管理与分析数据统计分析数据排序数据筛选（自动、高级筛选）格式的灵活运用数据分类汇总数据透视表多表合并数据查询数据统计分析常用的计数与求和函数COUNTIF条件统计函数SUMIF条件求和函数FREQUENCY频率统计函数排名统计应用工作表单元格计数计数函数函数函数功能说明COUNT返回范围中数值型单元格的数量(包括日期)COUNTA返回范围中非空的单元格数量COUNTBLANK返回某个区域中空单元格的数量工作表单元格计数高级计数公式：使用COUNTIF函数COUNTIF：计算某个区域中满足给定条件的单元格数目=COUNTIF(Range,Criteria)计数单元格范围表达式（单筛选条件）条件统计函数对出现频率最高项目的计数=COUNTIF(Range,MODE(Range))备注：1、MODE函数可以返回某个范围中最高出现频率的值；2、MODE函数只能处理数值型单元格，它会简单的忽略包含文本的单元格条件求和函数使用单个条件求条件和=SUMIF(Range,Criteria，SUM_Range)备注：1、Range：要进行计算的单元格范围；2、Criteria：以数字、文本和表达式定义的条件；3、sum_range：用于求和计算的实际单元格。如果此参数被忽略，函数则使用第一个参数确定的范围。频率统计函数统计指定单元格的区域中数据的频率分布=FREQUENCY(data_array,bins_array)备注：1、data_array：用来计算频率的数组；2、bins_array：进行频率计算的分段点注意：统计时先选定输出区域，输完公式后按Ctrl+Shift+Enter组合键；{}不能手工输入排名统计应用返回某个数字在数字列表中的排名=RANK(number,ref,order)备注：1、number：指定需要排位的数字；2、ref：表示数字列表数组或对数字列表的引用；3、order：表示排位的方式。如果为0或忽略，按降序排列的排名；非零值，按升序排列的排名。数据排序数值型、日期型排序；字符型排序自定义排序：工具\选项\自定义系列，然后数据\排序\选项\选择自定义序列实例：分别按参加工作时间、姓名、学历排序数据筛选（自动、高级筛选）要求：1.将电子邮件为sina.com的客户资料找出来2.将电子邮件为sina.com或163.com的客户资料找出来3.将电子邮件为sina.com且性别为男的客户资料找出来4.将所有姓李的的客户资料找出来5.将年龄小于40且文化程度为大学本科或年龄30且文化程度为大专的客户资料找出来格式的灵活运用格式的设置：先选定设置区域，然后设置格式\条件格式实例1：将低于科目平均分的学生分数以蓝色突出显示。公式：C3average(C$3:C$62)，颜色设置为蓝色实例2：自动标识。公式：C3=large($C:$C,3)，C列里前三大数字自动突出显示注意：$表示绝对引用，C$3表示列可以变动而行不变数据分类汇总创建嵌套分类汇总：在一级分类汇总的基础上，继续进行分类汇总，应取消替换当前分类汇总复选框的选择注意：使用“分类汇总”功能时，一定要按分类对象进行排序！实例：按职称、性别进行两级分类汇总数据透视表创建数据透视表利用页字段创建分页显示创建多字段的数据表修改透视表数据的显示方式显示数据项的明细数据多表合并相同格式的多表合并1.数据\合并计算2.运用sum()函数合并不同格式的多表合并1.countif()函数2.sumif()函数3.indirect()函数:返回文本字符串所指定的值4.row()函数:row($1:$4)表示产生1至4的行数组数据查询常用的查找函数VLOOKUP函数HLOOKUP函数常用的定位函数判断制定数据是否存在判断一列数据是否有重复？LOOKUP函数LOOKUP函数的语法结构：LOOKUP(lookup_value,lookup_vector,result_vector)1.Lookup_value：为函数LOOKUP在第一个向量中所要查找的数值；2.Lookup_vector：为只包含一行或一列的区域；3.Result_vector：只包含一行或一列的区域，其大小必须与lookup_vector相同注意:Lookup_vector的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则函数LOOKUP不能返回正确的结果。文本不区分大小写。VLOOKUP函数VLOOKUP函数的语法结构：(列查找)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value：查找值在查找表的第一列；Table_array：包含查找表的范围；Col_index_num：匹配值存在的表中的列号；Range_lookup：可选项目，TRUE或FALSE备注：如果range_value参数为TRUE或被忽略，查找表的第一列应该是按升序排列的；如果lookup_value小于table_array中第一列的最小值，VLOOKUP将返回错误的提示VLOOKUP函数示例：HLOOKUP函数HLOOKUP函数的语法结构：(行查找)HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)1.Lookup_value：查找值在查找表的第一行；2.Table_array：包含查找表的范围；3.Col_index_num：匹配值存在的表中的行号；4.Range_lookup：可选项目，TRUE或FALSE；HLOOKUP函数示例：INDEX+MATCH函数INDEX函数：根据坐标返回范围中的单元格值INDEX(array,row_num,column_num)INDEX+MATCH函数MATCH函数：返回指定范围中的一个单元格的相对位置（返回值为相对行号或列号）MATCH(lookup_value,lookup_array,match_type)实例固定资产报表实例固定资产报表实例固定资产报表（1）数据排序：按购买时间排序实例固定资产报表（2）数据筛选实例固定资产报表（3）分类汇总：实例固定资产报表（4）数据透视表：实例固定资产报表（4）数据透视表：实例固定资产报表（4）数据透视表：四、应用图表常用图表制作复合图表制作甘特图制作技巧专业图表制作专业图表遵循一定的格式标准安捷软件公司2007年度产品销售收入年中统计￥-￥50.00￥100.00￥150.00￥200.00￥250.00￥300.00网络软件操作系统办公软件杀毒软件教育软件游戏软件预测后续收入（万元人民币）目前收入（万元人民币）年初预计收入（万元人民币）（数据来源：安捷软件公司销售部销售报表，2007年8月11日）注：销售部门截止至8月份的相关数据显示，网络和游戏两类产品的销量无法达成年初的既定目标。标题，直观的表现图表的主要信息脚注，对重点内容进行说明数据来源，赋予数据可信度。图例，对数据项进行说明非数据类图表组织结构图地图甘特图流程图概念图矩阵图董事会董事长总经理副总经理副总经理副总经理市场部销售部客户服务中心研发中心产品设计中心网络管理中心人力资源部财务部行政部开始操作系统WindowsServer2003安装USB与索引服务Windows9xWindowsNTWindows2000WindowsXPz低高高低市场增长率市场相对份额明星问号？金牛瘦狗数据类图表0102030405060708090第一季度第二季度第三季度第四季度020406080100第一季度第二季度第三季度第四季度0102030405060708090100第一季度第二季度第三季度第四季度0102030405060708090100012345部门销售额成本部门一65876188部门二7510300部门三58963000合计199939488表格柱形图条形图饼图折线图散点图图表制作的三个步骤0.00%10.00%20.00%30.00%40.00%联想方正惠普戴尔联想方正惠普戴尔2003年第四季度26.50%11%4%7%2006年第四季度36%13%9%8.50%1.确定要表达的主题2.确定对比类型3.选择图表类型成分项类时间序列频率分布相关性饼形图柱形图折线图散点图图表类型条形图比较类型常用的图表图表是展示数据的最有效工具常用的图表主要分为：柱形图条形图折线图饼形图面积图散点图气泡图224.4210.9189.3201.8177.1165.2115.1129.3168.1188.6228.0237.648.266.167.388.3130.035.201002003004005006001996199719981999(E)2000(E)2001(E)LowtierMiddletierHightierUnit:thousandtons374.7388.4423.4457.7493.4532.8堆积柱状图表示按价格细分的高、中、低端产品的产量随时间的变化。CAGR(1996-2001E)7%30%15%-6%柱形图案例条形图案例某企业信息化项目工作任务时间表12-291-51-121-191-262-22-92-162-233-23-93-16定购设备设备供货设备验收机房建设OA系统调研财务系统调研临时任务邮件系统导调研平台集成调研需求评审系统设计折线图0102030405060708090100year1year2year3year4year5year6year7year8year9year10salesRMBMM年增长率(Yr1-5)(Yr5-10)34%4%引进新产品引