EXCEL实用技巧培训专题培训ppt课件.ppt

EXCEL实用技巧培训,技巧一,排版篇,Excel 中的颜色要怎么搭配，视觉上更能接受且区分度高？,首行、首列最深间行留白,三色原则,有必须重复信息的行，那么如果没有制作色差的话很容易看走眼，而且会较快的眼睛疲劳。所以很简单的改动就能让你的表单显得更为友好。,为什么是三色？,1、三色原则能最大范围地适用“对比色配色方案“、”互补色配色方案“或者”邻近色配色方案“。2、最主要的原因是省油墨。办公室绝大部分企业都是黑白打印+双面利用以节省成本。如果只有三色，即白色+2主色，另外2个主色会以不同灰度的形式印刷出来。,为什么要错行配色？,*以上内容引用自知乎用户Chaotic Good,上图为苹果 iwork的numbers自带样式，饼图一类的统计图用色系根据明度递减做配色；表单依旧是三色配色方案。,1、全选（Ctrl+A）需要处理的数据,2、开始-套用表格格式,强烈建使用EXCEL的最新版本，目前最新是2013版,（因为它的模板真的很强大、很省事！）,做完两行请使用格式刷,不使用模板？你想要自己做？也可以！,【样式】-【套用表格格式】-【新建表格样式】-设置【第一行条纹】【第二行条纹】颜色-【套用表格样式】其中，【第一行条纹】为偶数行（包含标题行算起，即第二行），【第二行条纹】为奇数行。如果不设置标题行则反之。点击后选【格式】，然后设置填充颜色即可。,技巧二,操作篇,Alt+Enter，表格内换行,1,有时收到同事的工作总结,我们手贱，调整了下列宽,错误案例,正确案例,使用“空格”调整格式的,使用“Alt+Enter”调整格式的,F4，重复上一步操作,2,插入行,神技能！,节省操作步骤,删除列,字体加粗,填充背景色,（分号后面那个）,3,主要解决的问题,录入身份证号，变成科学计算法录入“0”开头的编号，自动省略0,当然，也可以通过设置单元格格式来解决这个问题,注意要使用英文状态下的输入法，否则EXCEL无法识别,复制，选择性粘贴,4,方式一,方式二,复制完后，点左上角粘贴,粘贴后，点右下角图标,分列,5,做微信运营的同事后台导出的活动数据经常是这样的,为什么分隔符号里明明有逗号，EXCEL却无法识别，而需要在其他那里输，才可以呢？答案是：逗号也有英文和中文的区别，EXCEl只能识别英文状态下的逗号。,经过分列处理后,冻结单元格,6,主要用途就是在移动表格时，方便能看到行表头或列表头,技巧三,公式篇,想要固定行或列，就在公式中给“$”！按F4也有同样效果（此符号仅在填充单元格复制公式时适用）,$单元格绝对引用,1,未设置单元格绝对引用时，设置D2=B2+C2，往下填充公式，结果默认：D3=B3+C3 D4=B4+C4,加“$”后再进行填充,D3=B3+C D4=B4+C,想要固定加同一个数作为奖金,这几个公式完全可以一起学！,Ifcountifsumifcountifssumifs,2,if,countif,countifs,sumif,sumifs,条件计数,条件求和,逻辑判断,判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值,计算某个区域中满足给定条件的单元格数目,统计一组给定条件所指定的单元格数,对满足条件的单元格求和,对一组给定指定的单元格求和,在D2设置公式，然后进行填充后，即可以获得下列结果,实际案例：迟到3次以下，即可获得全勤奖；否则，只有0元,if,公式含义：统计C列不为0的数量。注意：如果是不等于某个数字，或表示大于、小于等关系时，公式中需要加“”,实际案例：统计迟到人数,countif,公式含义：统计C列不为0，且B列大于5000的行的数量。,实际案例：统计工资5000元以上的迟到人数,countifs,公式含义：如果C列的值为0，则将B列的值进行求和,实际案例：统计未迟到人员的工资总额,sumif,以上的例子举得很无聊，谁有事没事拿工资说事？！目的是为了帮助大家理解公式,下面介绍的这个函数，简直就是神器！,Vlookup，数据匹配,3,表一,行政部MM给了我两张表，说要将离职人员从通讯录中删除,行政部MM给了我两张表，说要将离职人员从通讯录中删除,表二,选中单元格,公式,插入函数,vlookup,确定,5,1,2,3,4,不理解，没关系，记住最后两项，填1和0就行了！,需要查询和匹配的内容（例如表1通讯录的A列）,需要删除的值（例如表2离职人员的A列）,数字1表示返回表2中第“1”列的值,数字0表示精确匹配,最终得出来的结果就是这样子的！再应用“筛选”或是“排序”就能够很轻松地将离职人员删除,最典型的应用场景：从表A的数据中找出表B的数据,&，可在目标单元格后面增加某些字符,4,假设我们公司会给员工下发个工资短信（实际不会发），内容是：*，你本月的工资是*元，全勤奖是*元，总计*元。,技巧四,数据透视表,数据透视表最适合做这种“分类汇总计算、求和”的活,这是典型应用场景,为了简单说明问题，拿出一小部分数据来做演示,这才是原始数据,勾选需要分析的字段，并扔到区域中,注意勾选相应类别，及调整行、列、值的选项,更改计数类别，可以右键单击计数项-值汇总依据,除了两个维度的数据透视表，还可以做三个维度、四个维度的数据透视表。大家感兴趣可以自己试一试，原理类似。,一个数据透视表顶很多个公式啊！,技巧五,下拉菜单设计,有时，我们提供表格给别的同事填写的时候，希望限定单元格内可填写的内容，例如只能按我们规定的选项来填。或者是自己填写时，图省事只希望有那么几个选项。这时候下拉菜单就非常有用了！,记住这里如果是手输文字选项的话，一定要用英文状态下的逗号分隔开,还有一些高阶用法，大家可以自己钻研一下，篇幅关系不展开讲了。,在用户输入内容时，给出提示或警告，可以在以下两个选项中进行设置,三级下拉菜单：最典型的用法莫过于，在一级菜单“省”选了省后，二级菜单“市”的选项也能根据省的情况进行相应的改变，需要用到Indirect函数,技巧六,“宏”的使用,当你把机械化的一套操作通过录制宏实现，眼看表格自动化操作，在两秒内给你返回原来每天固定要做十几分钟的数据分析结果时，那个鸡皮疙瘩,当你学会了前面介绍的所有的基本操作，仍不满足。当你每天有很多重复的、机械的、简单的操作。恭喜你！宏是可以满足你的。,宏的应用场景,宏的定义,Excel中的宏，指的就是实现单一功能或复杂功能的VBA代码的集合。使用宏功能，可以让我们在处理电子表格的时候，仅通过一步的操作，就能实现需要通过多步才能完成的操作。,还是以一个实际例子好理解一点：假设我们要处理多个类似的表格，例如对表格的数据进行美化，我们一般拿到的未经处理的数据是这样子的,1,录制宏路径：视图宏录制宏,2,定义宏的内容,这里要选择保留在个人宏工作簿,假设我们固定需要做的操作是：1、调整列宽至自动适合；2、调整字体为微软雅黑；3、设置表头；4、去掉网络线；5、设置灰色边框,3,开始正常的操作在表1中，做以下操作1、调整列宽至自动适合；2、调整字体为微软雅黑；3、设置表头；4、去掉网络线；5、设置灰色边框,4,停止录制录制完成后，点击宏中的停止录制,见证奇迹的时刻到了！,5,使用宏选取一个新的表格-表2。然后直接到宏当中去调用，或者使用刚才我们自定义好的快捷键。,最后，再强调一次,请使用最新版本的OFFICE！,掌握office基本技巧，不是为了酷炫,而是为了提升工作效率！,