BSDU-EXCEL技能培训1130

EXCEL技能培训1234小技巧&函数数据透视表简单SQLVBA入门课程内容小技巧&函数Q1:条件格式使用小技巧&函数这是已经设置好的小技巧&函数根据自我需求，自己制作小技巧&函数Q2:冻结窗口遇到什么难题哦小技巧&函数Q3:如何粘贴数据不走样小技巧&函数小技巧&函数Q4:计算空格时显示#DIV/0！有什么办法不显示#DIV/0函数IFERROR定位小技巧&函数可根据数据变化可适用面积大小技巧&函数Q5:如何查找重复数据Ctrl+C复制Alt+D+P透视向导Ctrl的妙用其它快捷键Ctrl+V粘贴Ctrl+X剪切Ctrl+A选择全部Ctrl+F查找Ctrl+Z撤销上一步Ctrl+G定位Ctrl+Shift+↑↓←→快速选择范围F1帮助F4重复上一步操作/引用切换键精选1-常用快捷键小技巧&函数为什么要使用数据有效性？？？1、数据录入规范化；2、简化录入一、启用数据有效性的方法1、依次单击【数据】→【数据有效性】，如图所示122、【数据有效性】对话框界面精选2-数据有效性小技巧&函数二、制作【数据有效性】1234567数据有效性小技巧&函数数据有效性三、使用【数据有效性】小技巧&函数—18—一：选中整个表单，右击设置单元格格式取消“锁定”二：选中需保护的区域，右击设置单元格格式，单击“锁定”需保护区精选3-工作表保护12小技巧&函数—19—数学计数类查找与引用逻辑函数文本函数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日期函数小技巧&函数—20—SUMIFS函数功能对区域中满足多个条件的单元格求和。语法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多条件求和函数小技巧&函数—21—查找引用函数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正确使用的前提之一是所找的列里没有重复项小技巧&函数—22—字符串函数MID函数功能MID返回文本字符串中从指定位置开始的特定数目的字符语法MID(text,start_num,num_chars)参数Text是包含要提取字符的文本字符串。Start_num是文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推。Num_chars指定希望MID从文本中返回字符的个数。示例小技巧&函数—23—小技巧&函数逻辑函数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定义为“好”，其它定义为“一般”—24—日期函数Datedif函数功能可以计算两个日期间的间隔，它能够显示天数、月数语法=DATEDIF(第一日期,第二日期,“差异)参数第一日期/两个日期中较早的一个日期第二日期/两日期中距今天最近的日期差异/dorm示例见excel小技巧&函数1234小技巧&函数数据透视表简单SQLVBA入门课程内容—26—创建数据透视表的三步法数据透视表第一步：选中透视对象，单击“插入——数据透视表”。1234—27—创建数据透视表的三步法数据透视表第二步：创建数据透视表。1、数据源的选择：可以是区域，也可以是几个整列（小贴士：源数据要更改刷新的，建议选择几列）。2、数据透视表的位置：可以和源数据放一起，也可以新建工作簿。123—28—创建数据透视表的三步法数据透视表第三步：创建数据透视表字段。按需求将字段移入的方框中—29—数据透视表功能简介（值汇总选项）数据透视表—30—数据透视表功能简介（值显示方式）数据透视表—31—作用：一些简单运算可以通过在数据透视表中进行，使得一些重复制作的报表刷新更快。范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表123第一步：选中数据透视表中任何一单元格，单击“开始——插入——插入计算字段—32—范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表第二步：填入需要计算的字段名称与公式。123451、计算的字段名称与原字段名称不可重复；2、公式填写不仅可以用加减乘除，还可使用函数。—33—范例：将销售元转化为万元。数据透视表功能简介（计算字段）数据透视表第三步：结果如图所示：—34—数据透视表应用小技巧（数据透视表函数）数据透视表数据透视表与GETPIVOTDATA函数的使用GETPIVOTDATA函数是配合数据透视表运用的函数，编写简单，配合相对引用和绝对引用范例：根据提供的源数据配合使用GETPIVOTDATA函数各水果的品种等级分布。数据源结果图—35—数据透视表应用小技巧（数据透视表函数）数据透视表用IFERROR函数去除引不到乱码锁行锁列GETPIVOTDATA函数不需要手工写入，直接写”=“点击透视表中要引用的数据（如汇总）任何一个单元格+Enter,即可生成函数。1234小技巧&函数数据透视表简单SQLVBA入门课程内容通过SQL与数据透视表结合使用，可快速分析不同工作表数据，并且可以随时刷新37+=select片区,销售公司名称,经销商简称,经销商进货数量,经销商库存数量,0as两周销售from[C:\Users\sony\Desktop\EXCEL中级-信息主管\经销商进销存.xlsx].[经销商进销存$]unionallselect片区名称as片区,销售公司名称,经销商简称,0as经销商进货数量,0as经销商库存数量,销售数量as两周销售from[C:\Users\sony\Desktop\EXCEL中级-信息主管\经销商销售明细表.xlsx].[经销商销售明细表$]核心内容1234小技巧&函数数据透视表简单SQLVBA入门课程内容货号B1201034B1201026B1201018销售排名123产品图片4041step1:操作代码42网络（度娘、EXCELHOME…..)EXCEL开发人员自己编写（书籍）ForEachoSlideInActivePresentation.SlidesForEachoShapeInoSlide.ShapesSetoTxtRange=oShape.TextFrame.TextRange宏批量导入图片/操作代码43step2:存放位置44宏批量导入图片/保存位置145宏批量导入图片/保存位置246step3:如何调用47宏批量导入图片/创建按钮148宏批量导入图片/创建按钮249Sub图片()Dimi,c,rAsLong,strImgPathAsStringstrImgPath=C:\Users\sony\Desktop\EXCEL中级-信息主管\产品图片库\'图片路径c=ActiveCell.Column'货号列r=ActiveCell.Row'货号行i=rWhileTrim(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+1WendEndSub核心内容追求卓越永不满足！