EXCEL技能培训课件(PPT 51页)

1、1EXCEL讲师小组讲师小组21 12 23 34 4小技巧小技巧& &函数函数数据透视表数据透视表简单简单SQLSQLVBAVBA入门入门课程内容3小技巧小技巧& &函数函数Q1:Q1:条件格式使用条件格式使用4小技巧小技巧& &函数函数这是已经设置好的这是已经设置好的5小技巧小技巧& &函数函数根据自我需求，自己制作根据自我需求，自己制作6小技巧小技巧& &函数函数Q2:Q2:冻结窗口冻结窗口7遇到什么难题哦遇到什么难题哦8小技巧小技巧& &函数函数Q3:Q3:如何粘贴数据不走样如何粘贴数据不走

2、样9小技巧小技巧& &函数函数10小技巧小技巧& &函数函数Q4:Q4:计算空格时显示计算空格时显示#DIV/0#DIV/0！有！有什么办法不显示什么办法不显示#DIV/0#DIV/011函数函数IFERROR0102定位定位小技巧小技巧& &函数函数可根据数据变化可适用面积大12小技巧小技巧& &函数函数Q5:Q5:如何查找重复数据如何查找重复数据13查找替换查找替换countif数据透视数据透视删除重复项删除重复项123414Ctrl+C复制复制Alt+D+P透视向导透视向导Ctrl的妙用其它快捷键Ctrl+V粘贴粘贴Ctrl

3、+X剪切剪切Ctrl+A选择全部选择全部Ctrl+F查找查找Ctrl+Z撤销上一步撤销上一步Ctrl+G定位定位Ctrl+Shift+ 快速选择范围快速选择范围F1帮助帮助F4 重复重复上一步操作上一步操作 / /引用引用切换键切换键精选精选1-常用快捷键常用快捷键小技巧小技巧& &函数函数15为什么要使用数据有效性？为什么要使用数据有效性？1、数据录入规范化；规范化；2、简化简化录入一、启用数据有效性的方法一、启用数据有效性的方法1、依次单击【数据】【数据有效性】，如图所示1 12 22、【数据有效性】对话框界面精选精选2-数据有效性数据有效性小技巧小技巧& &

4、;函数函数16二、制作二、制作【数据有效性数据有效性】1 12 23 34 45 56 67 7数据有效性数据有效性小技巧小技巧& &函数函数17数据有效性数据有效性三、使用三、使用【数据有效性数据有效性】小技巧小技巧& &函数函数18一：选中整个表单整个表单，右击设置单元格格式 取消“锁定”二：选中需保护的区域，右击设置单元格格式， 单击“锁定” 需保护区需保护区精选精选3-工作表保护工作表保护1 12 2小技巧小技巧& &函数函数19数学计数类查找与引用逻辑函数文本函数Sum/ Sumif / Sumifs/ SumifsCount/Coun

5、ta/Count/Counta/CountifCountif/Countifs/CountifsAverage/averageif/averagesMin/maxIf If、IferrorIferror、And、OrLen、LeftLeft、RightRight、MidMid、Text、Value精选精选4-常用函数分类常用函数分类Today、Month、Year、DatedifDatedifVlookupVlookup、Column、Row、match、offset日期函数小技巧小技巧& &函数函数20SUMIFS 函数函数功能对区域中满足多个条件的单元格求和。语法SUMIF

6、S(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, )参数sum_range 必需。对一个或多个单元格求和，包括数值或包含数值的名称、区域或单元格引用，忽略空白和文本值。criteria_range1 必需。在其中计算关联条件的第一个区域。criteria1 必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对 criteria_range1 参数中的哪些单元格求和。criteria_range2, criteria2, 可选。附加的区域及其关联条件。最多允许 127 个区域/条件对。示例 求货品

7、等级A品种芒果芒果的销售金额 =SUMIFS($D:$D,B:B,”A”,A:A,”芒果”)=126 多条件求和函数多条件求和函数小技巧小技巧& &函数函数21查找引用函数查找引用函数如果如果G2单元格变成了芒果单元格变成了芒果那么值会变成什么？那么值会变成什么？往右往右3位位查找西瓜的销售单价G2单元格=VLOOKUP(F2,$A:$D,3,0)结果是3Vlookup正确使用的前提之一正确使用的前提之一是是所找的列里没有重复项所找的列里没有重复项小技巧小技巧& &函数函数22字符串函数字符串函数小技巧小技巧& &函数函数23小技巧小技巧&

8、; &函数函数逻辑函数逻辑函数把货品等级为A定义为“好”，其它定义为“一般”24日期函数日期函数Datedif Datedif 函数函数功能可以计算两个日期间的间隔，它能够显示天数、月数语法=DATEDIF(第一日期,第二日期,“差异)参数第一日期/两个日期中较早的一个日期第二日期/两日期中距今天最近的日期差异/d or m示例见见excelexcel小技巧小技巧& &函数函数251 12 23 34 4小技巧小技巧& &函数函数数据透视表数据透视表简单简单SQLSQLVBAVBA入门入门课程内容26创建数据透视表的三步法创建数据透视表的三步法数据透视表

9、第一步：选中透视对象，单击“插入数据透视表”。1 12 23 34 427创建数据透视表的三步法创建数据透视表的三步法数据透视表第二步：创建数据透视表。注意点注意点1、数据源的选择：可以是区域，也可以是几个整列（小贴士：源数据要更改刷新的，建议选择几列）。2、数据透视表的位置：可以和源数据放一起，也可以新建工作簿。1 12 23 328创建数据透视表的三步法创建数据透视表的三步法数据透视表第三步：创建数据透视表字段。按需求将字段移入的方框中29数据透视表功能简介（值汇总选项）数据透视表功能简介（值汇总选项）数据透视表30数据透视表功能简介（值显示方式）数据透视表功能简介（值显示方式）数据透视表

10、31作用：一些简单运算可以通过在数据透视表中进行，使得一些重复制作的报表刷新更快。范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表功能简介（计算字段）数据透视表1 12 23 3第一步：选中数据透视表中任何一单元格，单击“开始插入插入计算字段32范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表功能简介（计算字段）数据透视表第二步：填入需要计算的字段名称与公式。1 12 23 34 45 5注意点注意点1、计算的字段名称与原字段名称不可重复；2、公式填写不仅可以用加减乘除，还可使用函数。33范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表功能

11、简介（计算字段）数据透视表第三步：结果如图所示：34数据透视表应用小技巧（数据透视表函数）数据透视表应用小技巧（数据透视表函数）数据透视表数据透视表与数据透视表与GETPIVOTDATA函数的使用函数的使用GETPIVOTDATA函数是配合数据透视表运用的函数，编写简单，配合相对引用和绝对引用范例：根据提供的源数据配合使用GETPIVOTDATA函数 各水果的品种等级分布。数据源结果图35数据透视表应用小技巧（数据透视表函数）数据透视表应用小技巧（数据透视表函数）数据透视表用IFERROR函数去除引不到乱码锁行锁列注意点注意点 GETPIVOTDATA函数不需要手工写入，直接写”=“点击透视表

12、中要引用的数据（如汇总）任何一个单元格+Enter,即可生成函数。361 12 23 34 4小技巧小技巧& &函数函数数据透视表数据透视表简单简单SQLSQLVBAVBA入门入门课程内容37通过SQL与数据透视表结合使用，可快速分析不同工作表数据，并且可以随时刷新数据透视表数据透视表SQL语句语句+=数据处理利器数据处理利器报表人士必杀技报表人士必杀技数据分析人士工具箱数据分析人士工具箱38select 片区,销售公司名称,经销商简称,经销商进货数量,经销商库存数量,0 as 两周销售 from C:UserssonyDesktopEXCEL中级-信息主管经销商进销存.xls

13、x.经销商进销存$ union all select 片区名称 as 片区,销售公司名称,经销商简称,0 as 经销商进货数量,0 as 经销商库存数量,销售数量 as 两周销售 from C:UserssonyDesktopEXCEL中级-信息主管经销商销售明细表.xlsx.经销商销售明细表$核心内容核心内容391 12 23 34 4小技巧小技巧& &函数函数数据透视表数据透视表简单简单SQLSQLVBAVBA入门入门课程内容4041step 1:操作代码42网络（度娘、网络（度娘、EXCEL HOME.)EXCEL HOME.)EXCELEXCEL开发人员开发人员自己编写

14、（书籍）自己编写（书籍）For Each oSlide In ActivePresentation.SlidesFor Each oShape In oSlide.ShapesSet oTxtRange = oShape.TextFrame.TextRange宏批量导入图片/操作代码43step 2:存放位置44宏批量导入图片/保存位置145宏批量导入图片/保存位置246step 3:如何调用47宏批量导入图片/创建按钮148宏批量导入图片/创建按钮24950Sub 图片() Dim i, c, r As Long, strImgPath As String strImgPath = C:UserssonyDesktopEXCEL中级-信息主管产品图片库 图片路径 c = ActiveCell.Column 货号列 r = ActiveCell.Row 货号行 i = r While Trim(Cells(i, c).Text) 货号不为空继续 If Cells(i, c) 0 Then ActiveSheet.Pictures.Insert(strImgPath & Tr