Excel函数应用技巧培训讲座.pptx

《Excel函数应用技巧培训讲座.pptx》由会员分享，可在线阅读，更多相关《Excel函数应用技巧培训讲座.pptx（48页珍藏版）》请在三一文库上搜索。

1、1 1 MS Office Excel 2007应用技巧 陈如鸿 2012-3-17 2 目录 II II IIIIII IVIV常用技巧常用技巧 V V 学习方法学习方法 I I 基本规范基本规范 数据透视数据透视 常用函数常用函数 3 详细目录(学员使用) 14. 和积函数：根据一组数量、单价，一步到位求总金额 和积函数：求绩效总得分(二例) 1. 文件命名规则 2. Excel文档编制要求 3. 玩转数据透视表 4. 怎样检查重复项 15. 统计函数：求和、计数、条件求和、条件计数 16. 最大值最小值第几大第几小：晚会节目评比名次 13. 文本函数：身份证号的奥秘 12. 条件函数：按

2、成绩求分数段 11. 求整函数的应用：求工龄、年龄 17. 条件格式：突出显示特定条件的内容 18. 突出显示即将到期的合同 突出显示双休日 自动提示重复值 19. 行列对调 25. 人民币金额大写 20. 随机函 数：随机抽样 21. 数据引用：两个表之间引用数据 5. 四舍五入：为什么工资总和差1分钱? 6. 求整：怎样抹掉工资零头? 7. 应收帐款到期日 8. 劳动合同还有多久到期 9. 月份的最后一天：劳动合同到期日 10. 星期函数：制作自己的工作日志 22. 数据有效性 23. 单元格保护 24. 零值显示为“-” 4 学员基础 本课程假定学员已经理解并熟练应用以下概念、知识和技能

3、： 工作表sheet，工作簿book 单元格cell，行row，列column，相对地址，绝对地址 单元格格式，文本型、数字型、日期型 格式刷 公式的复制，拖曳引用公式 5 I I 基本规范基本规范 1 1 文件名文件名 2 2 外观外观 6 怎样取一个有意义的文件名？ 最新版盘点表.xls 盘点表-20111130.xls 禁止使用“最新”二字 帐龄分析表.xls 帐龄分析表-20110930A 费用预算.xls 工程部费用预算月报表(201110)B 哪个部门？什么时候? 用括号表示所属月份 生产日报表.xls 二车间生产日报表(201110).xls 7 赏心悦目的Excel文档外观 1

4、. 当内容超过一个屏幕时，要进行行或/和列的冻结，避免滚动屏 幕时看不到表格标题 2. 文档退出时要停留在左上方能看得见的地方，避免阅读者打开 时只看到文件的下部分而看不到文件头在哪里 3. 文档退出时一定要先预览： (1)避免有些格式屏幕上能看到内容，但打印时宽度不够； (2)看看是否适合打印，例如横向、竖向； (3)避免多余的空白页面。如有空页，要删除 8 4. 排版时要尽量排到整页内，不要出现孤页现象(即，最后一页只 有一两行的内容)。 5. 字号大小： - 如果内容较多，通常用9号字（中文五号字） - 如果内容较少，通常用12号字（中文四号字） - 实在内容太多，最小用8号字 - 要在

5、会议报告中投影的Excel文件，不必设置大号字，可以用 显示比例来调整显示大小 6. 字体格式： - 中文用宋体或黑体，全文选Times New Roman字体或Arial字 体，这样，数字和英文占用宽度较小 9 7. 数字格式： (1)数字达到4位数时，通常用千位符，这样便于读数。 例如：7381039 写成7,381,039 (2)如果有小数点，则统一小数位数，并且让数字居右对齐 8. 半角全角 - 所有字符用半角，不用全角 9. 文本型字段中的连字符 - 文档中如有姓名，两个字的姓名中间不要留空格 - 括号、中横线一定要统一用半角 10 10. 对齐 - 数字居右对齐。若位数相同，可居中

6、对齐。 - 文本居左对齐。若只有二三字，如姓名，可居中对齐 - 一般不使用居中对齐 11. 页面设置 - 不要使用“缩放比例”进行打印 - 超过一页的文件，要设置页码和总页数 - 超过一页的表格，要进行表格“顶端行标题”设置 返回目录 11 II II 数据透视数据透视 IIIIII 常用函数常用函数 IVIV常用技巧常用技巧 V V 学习方法学习方法 I I 基本规范基本规范 12 数据透视表的应用 你用过SUBTOTAL()吗? 你用过 数据分类汇总吗? 数据透视表的应用举例 注意事项： 当数据表数据修改后，透视表结果不会自动修改，要手动刷新 在透视表中右键：“刷新数据” 1313 检查重

7、复项 有时为了避免明显错误，应该检查非重复项是否重复 方法：数据_数据透视表和数据透视图 有时为了了解相同名称的数量，也需做重复检查 例： 思考：我想在输入时如果有重复 值就直接用颜色表示，怎么做? （答案在第36页） 返回目录 14 I III II 常用函数常用函数 1 1 文本函数文本函数 2 2 时间函数时间函数 3 3 条件函数条件函数 4 4 统计函数统计函数 5 5 其他函数其他函数 各种函数的具体应用请看第IV部分：应用技巧 15 常用函数：文本函数 函数说明 &把两个字段连接起来 (是一个操作符，并非函数） LEFT()返回左边字符 RIGHT()返回右边字符 MID()返回

8、中间字符 LEN()返回字符长度（包含所有空格） FIND()找到字符的位置 TRIM()删除头尾空格 红字部分建议熟练掌握 16 常用函数：时间和日期函数 函数返回值 YEAR(日期)日期所属年份 MONTH(日期)日期所属月份 DAY(日期)日期所属日 DATE(yyyy,mm,dd)日期 TODAY()今天的日期 NOW()现在的日期和时间 TEXT(日期,”aaa”)文本型星期 17 常用函数：条件函数 函数说明 IF(条件,表达式1,表达式2)返回条件满足时与不满足时的值 IF(A,a,IF(B,b,IF(C,c,d)多重条件求值 18 常用函数：统计函数 函数说明 SUM()求和

9、AVERAGE()求平均 COUNT()计算个数（只对数值型） COUNTA()计算个数（对所有类型） SUMPRODUCT()把两组数相乘后相加 SUMIF()对满足某一条件的一组数求和 COUNTIF()对满足某一条件的一组数计数 19 常用函数：统计函数 函数说明 MAX()求最大值 MIN()求最小值 LARGE(数组,k)求第k个最大值 SMALL(数组,k)求第k个最小值 20 常用函数：其他函数 函数说明 VLOOKUP() 特别重要查找对应数据 ROUND()4舍5入 INT()向下取整 CEILING()向上舍入 RAND()返回0-1间的随机数 RANK()从大到小排序 A

10、ND()逻辑和 （见36页“劳动合同到期提 示”中的示例说明 21 II 数据透视数据透视 IIIIII 常用函数常用函数 V V 学习方法学习方法 I I 基本规范基本规范 IV 常用技巧常用技巧 2222 为什么工资总和差1分钱? 四舍五入函数: ROUND() 格式：ROUND(参数，小数位数） 例： 返回目录 23 怎样抹掉工资零头 某公司规定，工资的零头不发放，全都取整数到元。 “抹掉”的意思不是四舍五入，而是向下取整。 向下取整函数：INT() 返回目录 24 工资向上取整 函数：CEILING(数值,整数) 例1: A公司规定，员工工资计发到角，1分至9 分的零头都计为1角。 例

11、2: B公司规定，员工工资向上取到元，1分到 9角9分 返回目录 2525 应收账款到期日 根据发票日期和付款期计算到期日 公式：日期相加或相减 26 劳动合同还有多久到期 根据到期日计算到期剩余天数 函数：TODAY() 27 劳动合同终止日期 某公司规定，第一次签劳动合同，合同期为3年。为了管理上 的方便，终止日期都定为3年后同一月的最后一天 函数：YEAR(), MONTH(), DAY(), DATE() 28 制作自己的工作日志 公式：日期加法 函数：星期函数 返回目录 29 求员工工龄 函数：INT() 公式：日期减法 某公司为了减少员工流失率，设置了每 半年工龄的工龄津贴，每满半

12、年工龄津 贴30元。 求半： 3.43.0, 3.63.5, 3.93.5, 4.14.0 返回目录 30 根据成绩求分数段 函数：条件函数IF() 简单IF()函数：单重 嵌套IF()函数：多重 注意：IF()函数最多只允许7重 返回目录 31 身份证号码的奥秘 文本函数：LEFT(), RIGHT(),MID(),LEN() 数学函数：求余函数 MOD(被除数,除数) 日期函数：DATE(yyyy,mmmm,dd) 返回目录 32 和积函数：sumproduct() 格式：=sumproduct(乘数列，被乘数列） 返回目录 33 统计函数：不及格人数有多少 计数函数 COUNT(), C

13、OUNTA() 条件求和函数 SUMIF() 条件计数函数 COUNTIF() 条件统计函数与数据透视表的区别 思考：本月毛利率大于25%的订单有多少笔? 返回目录 34 晚会节目评比名次 迎春晚会节目评比：去掉一个最高分，去掉一个最低分，取平 均，取前三名 最大值函数MAX(), 最小值函数MIN() 最大第n位函数LARGE(), 最小第n位函数SMALL() 返回目录 35 条件格式：突出显示特定条件 的内容 条件格式：按照不同条件突出不同的单元格格式 应用：突出显示(Highlight)特定项目 思考：突出显示毛利大于25%的条目 把含有“贸易”二字的条目加强显示 提示：条件格式可以做

14、到： 某栏的值符合某一个条件时，突出显示该值 某栏的值符合某一个条件时，突出显示该值对应的一整行 条件可以用等于、大于、小于、介于、公式 条件中用公式时，公式的最左边要用等号 可以最多同时应用三个条件 2007版 ： 2003版 ： 36 条件格式：形象表示数值大小 1.铜价走势 2.成绩分数段 返回目录 37 条件格式：突出显示过期的合同 1.突出显示生产任务未完成 2.劳动合同到期提示 3.突出显示双休日 4.输入重复值时直接用颜色提示 返回目录 38 行列对调 返回目录 39 简单的抽奖工具 函数：随机函数RAND() 返回目录 40 数据引用 vlookup() 函数：vlookup(

15、) 注意： 索引字段值在被查找的数据区域应是唯一的，要检查是否重复 注意公式中被查找数据区域应采用绝对位置表示 返回目录 4141 数据有效性 单元格中只允许输入某一范围的数值或日期，则可对单元格设 定数据有效性 方法：数据_有效性 例： 返回目录 4242 单元格保护 某些单元格，尤其是设定好的公式，不希望被误操作而破坏，则可进行单 元格的保护 也可以对工作表和工作簿进行保护 例： 操作请看下一页 返回目录 4343 保护前“锁定”的设置 工作表默认为“锁定”，保护前先去除工作表所有单元格的锁定 ，然后再对要保护的单元格进行锁定 4444 显示或隐藏0值 4545 零值显示为“-” 46 V. 学习方法 三字法则：问、搜、练 不建议去学宏、VBA 不求甚解，亦可照搬应用 47 人民币金额大写 注：WPS表格中的单元格格式可以直接设置人民币大写，但与 MS Excel不兼容，建议按这里的方法使用 返回目录 4848 Q & A 有什么问题吗？ 联系： 13923837249 谢谢！