excel课程1--实用功能分享.pptx

Excel实用功能分享（基础篇）,1、数据整理1、单元格格式；2、条件格式；3、分列与合并；3、汇总表格；4、排序和筛选；5、查找、选择及常用快捷键；,2、呈现与表达1、选择合适的图表；2、excel图表配色；3、数据展示和处理的细节；4、学习一些大神的作品；,DATA数据,INFORMATION信息,3、数据分析1、数据透视表；2、VLOOKUP;,3、常用公式“+-*/”4、常用公式求和sum；5、常用公式判断if;6、常用公式比对max、min；7、常用公式绝对引用$;8、数组公式；9、VBA入门（我会的也不多哈哈）,第一次分享,第二次分享,1.单元格格式,1、我的问题：我导出一个签收表格，结果快递单号老是出现科学计数法，我怎么批量去掉这种格式？（excel超过11位的数字会使用科学计数法显示）,2、我想要的更多：单元格常用格式和自定义功能我想要保留两位小数；我想要转换成百分数；我输入的内容要求有固定格式，比如我要输入11位数字，不够的用0填充，如00012345678，自定义“00000000000”；比如我输入数字要自动带出特定符号，如“￥55.00”，自定义“#,#0.00”,2.条件格式设置,我的问题：我想把所有12H发货率大于平均值的都“加粗”“红色字体”“黄色底纹”,2、我想要的更多：条件格式的其它功能我要显示排名前5名以及排名后5名的供应商；我要根据发货单量显示不同长度数据条；我要将8H发货率大于80%画个勾勾。,3.分列与合并,2.我的问题：我想把在库字段下的“京东已经出库”等拆分开，只要知道他在哪个仓库？,1.我的问题：我想把商品名称所有描述的单元格合并到一起,方法：使用“从右边往左数提取的字符串2位：RIGHT（A1,2）；从第3个字符提取，往左数3个字符，提取的字符串长度2：MID（A1,3，2）；,方法：使用数据分列，按分隔符号“”进行分列。数据分列也可以按照宽度进行分列。,4.筛选和排序,1.我的问题：我要筛选发货数量大于等于1000单的供应商？方法1：选中表头设置筛选，依次勾选大于1000的复选框；方法2：使用筛选，设置大于等于1000的逻辑限制。,2.我的问题：我要按发货量和12h发货率进行排序？选中单元格使用自定义排序，主要关键字“发货量”、次要关键字“12h发货率”,（小技巧：排序功能还能帮忙在隔行插入多个空行）,5.快速汇总表格,我的问题：辣鸡系统一次只能导出约3500条数据，我分成30个表导出来，怎么汇总？方法1：把表打开，ctrl+A选择，然后ctrl+c和ctrl+v复制一个表格，然后重复，然后重复，然后。；方法2：使用一段代码，新建一个表格，打开代码编辑页面，复制然后运行；,Sub合并当前目录下所有工作簿的全部工作表()DimMyPath,MyName,AWbNameDimWbAsWorkbook,WbNAsStringDimGAsLongDimNumAsLongDimBOXAsStringApplication.ScreenUpdating=FalseMyPath=ActiveWorkbook.PathMyName=Dir(MyPath&*.xls)AWbName=ActiveWorkbook.NameNum=0DoWhileMyNameIfMyNameAWbNameThenSetWb=Workbooks.Open(MyPath&MyName)Num=Num+1WithWorkbooks(1).ActiveSheet.Cells(.Range(B65536).End(xlUp).Row+2,1)=Left(MyName,Len(MyName)-4)ForG=1ToSheets.CountWb.Sheets(G).UsedRange.Copy.Cells(.Range(B65536).End(xlUp).Row+1,1)NextWbN=WbN&Chr(13)&Wb.NameWb.CloseFalseEndWithEndIfMyName=DirLoopRange(B1).SelectApplication.ScreenUpdating=TrueMsgBox共合并了&Num&个工作薄下的全部工作表。如下：&Chr(13)&WbN,vbInformation,提示EndSub,6.数据透视表格（友好高效的分析大量数据）,我的问题：我想要分析二审通过的订单，4H发货率、8h发货率、12h发货率分布特征，透视各个时间段各个仓的发货时长情况（如右图）？第一步整理数据：出库时间和二审通过时间的差值，看是属于哪个发货时效范围，使用if函数判断；第二步选择范围插入透视表；第三步筛选设置：筛选器使用“库存位置”，行使用“二审通过时间”，列使用第一步做出来的“属于哪个范围”，值使用任意一个字段进行计数；第四步调整：行标签按照小时进行分组，调整列标签顺序得到最终结果；第五步使用：可以查看不同库的发货时效分布，你也可以根据表格内容绘图，如果库存位置变了，相应的也会调整图像。（如何创建分组）,2.数据透视表其它知识点：透视表“值”常用的是“求和、计数、平均值”可以点击右边倒三角进行选择；透视表值显示的方式，可以选择百分比、设置排序（升序、降序），及设置显示格式；双击透视表的内容，可以查看数据详细情况；注意点数据透视表对数据的要求：表格第一行字段不能为空，内容不要有合并单元格，字段下的内容统一格式数字/文本，,6.VLOOKUP函数,1、我的问题：我导出一个订单明细，但是我想知道所有订单的库存位置，需要从库存管理导出的表格一一匹配过来，我该怎么操作？,方法：使用vlookupVLOOKUP(要查找的值,要查找的区域,返回数据在查找区域的第几列数,模糊匹配/精确匹配)如果能够找到则返回需要的值，如果找不到就返回错误值#N/A,使用vlookup需要注意的内容：1、返回错误值#N/A：是否有空格（trim去掉字符序列左边和右边的空格）数据类型是否统一；这个函数只能向右边查找；引用区域是否使用绝对引用。2、如果查找区域中有多个值与查找值匹配，它只能默认为匹配首个，也就是行数最小的那一个；,5.查找选择及常用快捷键,快捷键的使用不需要吭哧吭哧的死记硬背，在做中学，在用中记，成为只适合你自己的独一无二的快捷键秘籍。下表是我常用的一些快捷键，分享给大家：,7.授人以渔（分享的都是干货）,1、数据整理的关键（很多人没有留意，却很重要）,1、excel不知道怎么弄的问题，怎么办！,方法1：问度娘基本上你遇到的问题，百度上都有人遇到过，你只要搜索出来，很多问题都有解决方法。我的搜索关键词技巧：使用尽量精确的关键词，最前面加上excel限定范围；用关键词分开来进行搜索，扩大搜索返回的结果数量；猜想别人会怎么描述同样的问题。“excel一行的最大值输出标题”,相