EXCEL技能培训课件

——EXCEL讲师小组EXCEL技能培训——EXCEL讲师小组EXCEL技能培训11234小技巧&函数数据透视表简单SQLVBA入门课程内容1234小技巧&函数数据透视表简单SQLVBA入门课程内容2小技巧&函数Q1:条件格式使用小技巧&函数Q1:条件格式使用3小技巧&函数这是已经设置好的小技巧&函数这是已经设置好的4小技巧&函数根据自我需求，自己制作小技巧&函数根据自我需求，自己制作5小技巧&函数Q2:冻结窗口小技巧&函数Q2:冻结窗口6遇到什么难题哦遇到什么难题哦7小技巧&函数Q3:如何粘贴数据不走样小技巧&函数Q3:如何粘贴数据不走样8小技巧&函数小技巧&函数9小技巧&函数Q4:计算空格时显示#DIV/0！有什么办法不显示#DIV/0小技巧&函数Q4:计算空格时显示#DIV/0！有什么办法不显10函数IFERROR0102定位小技巧&函数可根据数据变化可适用面积大函数0102定位小技巧&函数可根据数据变化可适用面积大11小技巧&函数Q5:如何查找重复数据小技巧&函数Q5:如何查找重复数据12查找替换countif数据透视删除重复项1234查找替换countif数据透视删除重复项123413Ctrl+C复制Alt+D+P透视向导Ctrl的妙用其它快捷键Ctrl+V粘贴Ctrl+X剪切Ctrl+A选择全部Ctrl+F查找Ctrl+Z撤销上一步Ctrl+G定位Ctrl+Shift+↑↓←→

快速选择范围F1帮助F4

重复上一步操作/引用切换键精选1-常用快捷键小技巧&函数Ctrl+CAlt+D+PCtrl的妙用其它快捷键Ctrl+14为什么要使用数据有效性？？？1、数据录入规范化；2、简化录入

一、启用数据有效性的方法1、依次单击【数据】→【数据有效性】，如图所示122、【数据有效性】对话框界面精选2-数据有效性小技巧&函数为什么要使用数据有效性？？？1、数据录入规范化；2、简化录入15二、制作【数据有效性】1234567数据有效性小技巧&函数二、制作【数据有效性】1234567数据有效性小技巧&函数16数据有效性三、使用【数据有效性】小技巧&函数数据有效性三、使用【数据有效性】小技巧&函数17一：选中整个表单，右击设置单元格格式

取消“锁定”二：选中需保护的区域，右击设置单元格格式，

单击“锁定”

需保护区精选3-工作表保护12小技巧&函数一：选中整个表单，右击设置单元格格式二：选中需保护的区域，右18数学计数类查找与引用逻辑函数文本函数Sum/Sumif/SumifsCount/Counta/Countif/CountifsAverage/averageif/averagesMin/maxIf、Iferror、And、OrLen、Left、Right、Mid、Text、Value精选4-常用函数分类Today、Month、Year、DatedifVlookup、Column、Row、match、offset日期函数小技巧&函数数学计数类查找与引用逻辑函数文本函数Sum/Sumif19SUMIFS函数功能对区域中满足多个条件的单元格求和。语法SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)参数sum_range

必需。对一个或多个单元格求和，包括数值或包含数值的名称、区域或单元格引用，忽略空白和文本值。criteria_range1

必需。在其中计算关联条件的第一个区域。criteria1

必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对criteria_range1参数中的哪些单元格求和。criteria_range2,criteria2,…

可选。附加的区域及其关联条件。最多允许127

个区域/条件对。示例求货品等级A品种芒果的销售金额=SUMIFS($D:$D,B:B,”A”,A:A,”芒果”)=126

多条件求和函数小技巧&函数SUMIFS函数功能对区域中满足多个条件的单元格求和。语法20查找引用函数VLOOKUP函数功能在表格或数值数组的首列查找指定的数值，并在表格或数组中指定列的同一行中返回一个数值。语法VLOOKUP(lookup_value,table_array,Col_index_num,[range_lookup])参数Lookup_value

为需要在数据表第一行中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。Table_array

为需要在其中查找数据的数据表。使用对区域或区域名称的引用Col_index_num

为table_array中待返回的匹配值的列序号。Range_lookup

为一逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。【备注：TRUE（可用任意“非0”值替代）

或省略：则返回小于lookup_value的最大数值。Table_array的首行必须按升序排列。FALSE（可用“0”替代）：函数VLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A。】示例

如果G2单元格变成了芒果那么值会变成什么？？往右3位查找西瓜的销售单价G2单元格=VLOOKUP(F2,$A:$D,3,0)结果是3Vlookup正确使用的前提之一是所找的列里没有重复项小技巧&函数查找引用函数VLOOKUP函数功能在表格或数值数组的首列21字符串函数MID函数功能MID返回文本字符串中从指定位置开始的特定数目的字符语法MID(text,start_num,num_chars)参数Text

是包含要提取字符的文本字符串。Start_num

是文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推。Num_chars

指定希望MID从文本中返回字符的个数。示例小技巧&函数字符串函数MID函数功能MID返回文本字符串中从指定位置22小技巧&函数逻辑函数IF函数功能如果指定条件的计算结果为TRUE，IF函数将返回某个值；如果该条件的计算结果为FALSE，则返回另一个值。语法IF(logical_test,[value_if_true],[value_if_false])参数logical_test

必需。计算结果可能为TRUE或FALSE的任意值或表达式value_if_true

可选。logical_test

参数的计算结果为TRUE时所要返回的值value_if_false

可选。logical_test

参数的计算结果为FALSE时所要返回的值示例

把货品等级为A定义为“好”，其它定义为“一般”小技巧&函数逻辑函数IF函数功能如果指定条件的计算结果为23日期函数Datedif函数功能可以计算两个日期间的间隔，它能够显示天数、月数语法=DATEDIF(第一日期,第二日期,“差异")参数第一日期/两个日期中较早的一个日期第二日期/两日期中距今天最近的日期差异/dorm示例见excel小技巧&函数日期函数Datedif函数功能可以计算两个日期间的间隔，它241234小技巧&函数数据透视表简单SQLVBA入门课程内容1234小技巧&函数数据透视表简单SQLVBA入门课程内容25创建数据透视表的三步法数据透视表第一步：选中透视对象，单击“插入——数据透视表”。1234创建数据透视表的三步法数据透视表第一步：选中透视对象，单击“26创建数据透视表的三步法数据透视表第二步：创建数据透视表。注意点1、数据源的选择：可以是区域，也可以是几个整列（小贴士：源数据要更改刷新的，建议选择几列）。2、数据透视表的位置：可以和源数据放一起，也可以新建工作簿。123创建数据透视表的三步法数据透视表第二步：创建数据透视表。注意27创建数据透视表的三步法数据透视表第三步：创建数据透视表字段。按需求将字段移入的方框中创建数据透视表的三步法数据透视表第三步：创建数据透视表字段。28数据透视表功能简介（值汇总选项）数据透视表数据透视表功能简介（值汇总选项）数据透视表29数据透视表功能简介（值显示方式）数据透视表数据透视表功能简介（值显示方式）数据透视表30作用：一些简单运算可以通过在数据透视表中进行，使得一些重复制作的报表刷新更快。范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表123第一步：选中数据透视表中任何一单元格，单击“开始——插入——插入计算字段作用：一些简单运算可以通过在数据透视表中进行，使得一些重复制31范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表第二步：填入需要计算的字段名称与公式。12345注意点1、计算的字段名称与原字段名称不可重复；2、公式填写不仅可以用加减乘除，还可使用函数。范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据32范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表第三步：结果如图所示：范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据33数据透视表应用小技巧（数据透视表函数）数据透视表数据透视表与GETPIVOTDATA函数的使用GETPIVOTDATA函数是配合数据透视表运用的函数，编写简单，配合相对引用和绝对引用范例：根据提供的源数据配合使用GETPIVOTDATA函数各水果的品种等级分布。数据源结果图数据透视表应用小技巧（数据透视表函数）数据透视表数据透视表与34数据透视表应用小技巧（数据透视表函数）数据透视表用IFERROR函数去除引不到乱码锁行锁列注意点

GETPIVOTDATA函数不需要手工写入，直接写”=“点击透视表中要引用的数据（如汇总）任何一个单元格+Enter,即可生成函数。数据透视表应用小技巧（数据透视表函数）数据透视表用IFERR351234小技巧&函数数据透视表简单SQLVBA入门课程内容1234小技巧&函数数据透视表简单SQLVBA入门课程内容36通过SQL与数据透视表结合使用，可快速分析不同工作表数据，并且可以随时刷新37数据透视表SQL语句+=数据处理利器报表人士必杀技数据分析人士工具箱通过SQL与数据透视表结合使用，可快速分析不同工作表数据，并select片区,销售公司名称,经销商简称,经销商进货数量,经销商库存数量,0as两周销售from[C:\Users\sony\Desktop\EXCEL中级-信息主管\经销商进销存.xlsx].[经销商进销存$]unionallselect片区名称as片区,销售公司名称,经销商简称,0as经销商进货数量,0as经销商库存数量,销售数量as两周销售from[C:\Users\sony\Desktop\EXCEL中级-信息主管\经销商销售明细表.xlsx].[经销商销售明细表$]核心内容select片区,销售公司名称,经销商简称,经销商进货数量381234小技巧&函数数据透视表简单SQLVBA入门课程内容1234小技巧&函数数据透视表简单SQLVBA入门课程内容39货号B1201034B1201026B1201018销售排名123产品图片40货号B1201034B1201026B1201018销售排41step1:操作代码41step1:操作代码42网络（度娘、EXCELHOME…..)EXCEL开发人员自己编写（书籍）ForEachoSlideInActivePresentation.SlidesForEachoShapeInoSlide.ShapesSetoTxtRange=oShape.TextFrame.TextRange宏批量导入图片/操作代码42网络（度娘、EXCELHOME…..)EXCEL开发人43step2:存放位置43step2:存放位置44宏批量导入图片/保存位置144宏批量导入图片/保存位置145宏批量导入图片/保存位置245宏批量导入图片/保存位置246step3:如何调用46step3:如何调用47宏批量导入图片/创建按钮147宏批量导入图片/创建按钮148宏批量导入图片/创建按钮248宏批量导入图片/创建按钮24949Sub图片()

Dimi,c,rAsLong,strImgPathAsStringstrImgPath="C:\Users\sony\Desktop\EXCEL中级-信息主管\产品图片库\"'图片路径

c=ActiveCell.Column'货号列

r=ActiveCell.Row'货号行

i=r

WhileTrim(Cells(i,c).Text)<>""'货号不为空继续

IfCells(i,c)<>0ThenActiveSheet.Pictures.Insert(strImgPath&Trim(Cells(i,c).Text)&".jpg").Select'插入图片并选中

Selection.ShapeRange.Height=72'设置图片大小

Selection.ShapeRange.Width=64'设置图片大小

Selection.CutCells(i,c+1).SelectActiveSheet.PasteEndIfi=i+1

Wend

EndSub核心内容Sub图片()核心内容50追求卓越永不满足！追求卓越永不满足！51演讲完毕，谢谢观看！演讲完毕，谢谢观看！52——EXCEL讲师小组EXCEL技能培训——EXCEL讲师小组EXCEL技能培训531234小技巧&函数数据透视表简单SQLVBA入门课程内容1234小技巧&函数数据透视表简单SQLVBA入门课程内容54小技巧&函数Q1:条件格式使用小技巧&函数Q1:条件格式使用55小技巧&函数这是已经设置好的小技巧&函数这是已经设置好的56小技巧&函数根据自我需求，自己制作小技巧&函数根据自我需求，自己制作57小技巧&函数Q2:冻结窗口小技巧&函数Q2:冻结窗口58遇到什么难题哦遇到什么难题哦59小技巧&函数Q3:如何粘贴数据不走样小技巧&函数Q3:如何粘贴数据不走样60小技巧&函数小技巧&函数61小技巧&函数Q4:计算空格时显示#DIV/0！有什么办法不显示#DIV/0小技巧&函数Q4:计算空格时显示#DIV/0！有什么办法不显62函数IFERROR0102定位小技巧&函数可根据数据变化可适用面积大函数0102定位小技巧&函数可根据数据变化可适用面积大63小技巧&函数Q5:如何查找重复数据小技巧&函数Q5:如何查找重复数据64查找替换countif数据透视删除重复项1234查找替换countif数据透视删除重复项123465Ctrl+C复制Alt+D+P透视向导Ctrl的妙用其它快捷键Ctrl+V粘贴Ctrl+X剪切Ctrl+A选择全部Ctrl+F查找Ctrl+Z撤销上一步Ctrl+G定位Ctrl+Shift+↑↓←→

快速选择范围F1帮助F4

重复上一步操作/引用切换键精选1-常用快捷键小技巧&函数Ctrl+CAlt+D+PCtrl的妙用其它快捷键Ctrl+66为什么要使用数据有效性？？？1、数据录入规范化；2、简化录入

一、启用数据有效性的方法1、依次单击【数据】→【数据有效性】，如图所示122、【数据有效性】对话框界面精选2-数据有效性小技巧&函数为什么要使用数据有效性？？？1、数据录入规范化；2、简化录入67二、制作【数据有效性】1234567数据有效性小技巧&函数二、制作【数据有效性】1234567数据有效性小技巧&函数68数据有效性三、使用【数据有效性】小技巧&函数数据有效性三、使用【数据有效性】小技巧&函数69一：选中整个表单，右击设置单元格格式

取消“锁定”二：选中需保护的区域，右击设置单元格格式，

单击“锁定”

需保护区精选3-工作表保护12小技巧&函数一：选中整个表单，右击设置单元格格式二：选中需保护的区域，右70数学计数类查找与引用逻辑函数文本函数Sum/Sumif/SumifsCount/Counta/Countif/CountifsAverage/averageif/averagesMin/maxIf、Iferror、And、OrLen、Left、Right、Mid、Text、Value精选4-常用函数分类Today、Month、Year、DatedifVlookup、Column、Row、match、offset日期函数小技巧&函数数学计数类查找与引用逻辑函数文本函数Sum/Sumif71SUMIFS函数功能对区域中满足多个条件的单元格求和。语法SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)参数sum_range

必需。对一个或多个单元格求和，包括数值或包含数值的名称、区域或单元格引用，忽略空白和文本值。criteria_range1

必需。在其中计算关联条件的第一个区域。criteria1

必需。条件的形式为数字、表达式、单元格引用或文本，可用来定义将对criteria_range1参数中的哪些单元格求和。criteria_range2,criteria2,…

可选。附加的区域及其关联条件。最多允许127

个区域/条件对。示例求货品等级A品种芒果的销售金额=SUMIFS($D:$D,B:B,”A”,A:A,”芒果”)=126

多条件求和函数小技巧&函数SUMIFS函数功能对区域中满足多个条件的单元格求和。语法72查找引用函数VLOOKUP函数功能在表格或数值数组的首列查找指定的数值，并在表格或数组中指定列的同一行中返回一个数值。语法VLOOKUP(lookup_value,table_array,Col_index_num,[range_lookup])参数Lookup_value

为需要在数据表第一行中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。Table_array

为需要在其中查找数据的数据表。使用对区域或区域名称的引用Col_index_num

为table_array中待返回的匹配值的列序号。Range_lookup

为一逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。【备注：TRUE（可用任意“非0”值替代）

或省略：则返回小于lookup_value的最大数值。Table_array的首行必须按升序排列。FALSE（可用“0”替代）：函数VLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A。】示例

如果G2单元格变成了芒果那么值会变成什么？？往右3位查找西瓜的销售单价G2单元格=VLOOKUP(F2,$A:$D,3,0)结果是3Vlookup正确使用的前提之一是所找的列里没有重复项小技巧&函数查找引用函数VLOOKUP函数功能在表格或数值数组的首列73字符串函数MID函数功能MID返回文本字符串中从指定位置开始的特定数目的字符语法MID(text,start_num,num_chars)参数Text

是包含要提取字符的文本字符串。Start_num

是文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推。Num_chars

指定希望MID从文本中返回字符的个数。示例小技巧&函数字符串函数MID函数功能MID返回文本字符串中从指定位置74小技巧&函数逻辑函数IF函数功能如果指定条件的计算结果为TRUE，IF函数将返回某个值；如果该条件的计算结果为FALSE，则返回另一个值。语法IF(logical_test,[value_if_true],[value_if_false])参数logical_test

必需。计算结果可能为TRUE或FALSE的任意值或表达式value_if_true

可选。logical_test

参数的计算结果为TRUE时所要返回的值value_if_false

可选。logical_test

参数的计算结果为FALSE时所要返回的值示例

把货品等级为A定义为“好”，其它定义为“一般”小技巧&函数逻辑函数IF函数功能如果指定条件的计算结果为75日期函数Datedif函数功能可以计算两个日期间的间隔，它能够显示天数、月数语法=DATEDIF(第一日期,第二日期,“差异")参数第一日期/两个日期中较早的一个日期第二日期/两日期中距今天最近的日期差异/dorm示例见excel小技巧&函数日期函数Datedif函数功能可以计算两个日期间的间隔，它761234小技巧&函数数据透视表简单SQLVBA入门课程内容1234小技巧&函数数据透视表简单SQLVBA入门课程内容77创建数据透视表的三步法数据透视表第一步：选中透视对象，单击“插入——数据透视表”。1234创建数据透视表的三步法数据透视表第一步：选中透视对象，单击“78创建数据透视表的三步法数据透视表第二步：创建数据透视表。注意点1、数据源的选择：可以是区域，也可以是几个整列（小贴士：源数据要更改刷新的，建议选择几列）。2、数据透视表的位置：可以和源数据放一起，也可以新建工作簿。123创建数据透视表的三步法数据透视表第二步：创建数据透视表。注意79创建数据透视表的三步法数据透视表第三步：创建数据透视表字段。按需求将字段移入的方框中创建数据透视表的三步法数据透视表第三步：创建数据透视表字段。80数据透视表功能简介（值汇总选项）数据透视表数据透视表功能简介（值汇总选项）数据透视表81数据透视表功能简介（值显示方式）数据透视表数据透视表功能简介（值显示方式）数据透视表82作用：一些简单运算可以通过在数据透视表中进行，使得一些重复制作的报表刷新更快。范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表123第一步：选中数据透视表中任何一单元格，单击“开始——插入——插入计算字段作用：一些简单运算可以通过在数据透视表中进行，使得一些重复制83范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表第二步：填入需要计算的字段名称与公式。12345注意点1、计算的字段名称与原字段名称不可重复；2、公式填写不仅可以用加减乘除，还可使用函数。范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据84范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表第三步：结果如图所示：范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据85数据透视表应用小技巧（数据透视表函数）数据透视表数据透视表与GETPIVOTDATA函数的使用GETPIVOTDATA函数是配合数据透视表运用的函数，编写简单，配合相对引用和绝对引用范例：根据提供的源数据配合使用GETPIVOTDATA函数各水果的品种等级分布。数据源结果图数据透视表应用小技巧（数据透视表函数）数据透视表数据透视表与86数据透视表应用小技巧（数据透视表函数）数据透视表用IFERROR函数去除引不到乱码锁行锁列注意点

GETPIVOTDATA函数不需要手工写入，直接写”=“点击透视表中要引用的数据（如汇总）任何一个单元格+Enter,即可生成函数。数据透视表应用小技巧（数据透视表函数）数据透视表用IFERR871234小技巧&函数数据透视表简单SQLVBA入门课程内容1234小技巧&函数数据透视表简单SQLVBA入门课程内容88通过SQL与数据透视表结合使用，可快速分析不同工作表数据，并且可以随时刷新89数据透视表SQL语句+=数据处理利器报表人士必杀技数据分析人士工具箱通过SQL与数据透视表结合使用，可快速分析不同工作表数据，并select片区,销售公司名称,经销商简称,经销商进货数量,经销商库存数量,0as两周销售from[C:\Users\sony\Desktop\EXCEL中级-信息主管\经销商进销存.xlsx].[经销商进销存$]unionallselect片区名称as片区,销售公司名称,经销商简称,0as经销商进货数量,0as经销商库存数量,销售数量as两周销售from[C:\Users\sony\Desktop\EXCEL中级-信息主管\经销商销售明细表.xlsx].[经销商销售明细表$]核心内容select片区,销售公司名称,经销商简称,经销商进货数量901234小技巧&函数数据透视表简单SQLVBA入