Excel函数应用技巧培训讲座.pptx
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. 条件函数:按成绩求分数段 11. 求整函数的应用:求工龄、年龄 17. 条件格式:突出显示特定条件的内容 18. 突出显示即将到期的合同 突出显示双休日 自动提示重复值 19. 行列对调 25. 人民币金额大写 20. 随机函 数:随机抽样 21. 数据引用:两个表之间引用数据 5. 四舍五入:为什么工资总和差1分钱? 6. 求整:怎样抹掉工资零头? 7. 应收帐款到期日 8. 劳动合同还有多久到期 9. 月份的最后一天:劳动合同到期日 10. 星期函数:制作自己的工作日志 22. 数据有效性 23. 单元格保护 24. 零值显示为“-” 4 学员基础 本课程假定学员已经理解并熟练应用以下概念、知识和技能: 工作表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. 当内容超过一个屏幕时,要进行行或/和列的冻结,避免滚动屏 幕时看不到表格标题 2. 文档退出时要停留在左上方能看得见的地方,避免阅读者打开 时只看到文件的下部分而看不到文件头在哪里 3. 文档退出时一定要先预览: (1)避免有些格式屏幕上能看到内容,但打印时宽度不够; (2)看看是否适合打印,例如横向、竖向; (3)避免多余的空白页面。如有空页,要删除 8 4. 排版时要尽量排到整页内,不要出现孤页现象(即,最后一页只 有一两行的内容)。 5. 字号大小: - 如果内容较多,通常用9号字(中文五号字) - 如果内容较少,通常用12号字(中文四号字) - 实在内容太多,最小用8号字 - 要在会议报告中投影的Excel文件,不必设置大号字,可以用 显示比例来调整显示大小 6. 字体格式: - 中文用宋体或黑体,全文选Times New Roman字体或Arial字 体,这样,数字和英文占用宽度较小 9 7. 数字格式: (1)数字达到4位数时,通常用千位符,这样便于读数。 例如:7381039 写成7,381,039 (2)如果有小数点,则统一小数位数,并且让数字居右对齐 8. 半角全角 - 所有字符用半角,不用全角 9. 文本型字段中的连字符 - 文档中如有姓名,两个字的姓名中间不要留空格 - 括号、中横线一定要统一用半角 10 10. 对齐 - 数字居右对齐。若位数相同,可居中对齐。 - 文本居左对齐。若只有二三字,如姓名,可居中对齐 - 一般不使用居中对齐 11. 页面设置 - 不要使用“缩放比例”进行打印 - 超过一页的文件,要设置页码和总页数 - 超过一页的表格,要进行表格“顶端行标题”设置 返回目录 11 II II 数据透视数据透视 IIIIII 常用函数常用函数 IVIV常用技巧常用技巧 V V 学习方法学习方法 I I 基本规范基本规范 12 数据透视表的应用 你用过SUBTOTAL()吗? 你用过 数据分类汇总吗? 数据透视表的应用举例 注意事项: 当数据表数据修改后,透视表结果不会自动修改,要手动刷新 在透视表中右键:“刷新数据” 1313 检查重复项 有时为了避免明显错误,应该检查非重复项是否重复 方法:数据_数据透视表和数据透视图 有时为了了解相同名称的数量,也需做重复检查 例: 思考:我想在输入时如果有重复 值就直接用颜色表示,怎么做? (答案在第36页) 返回目录 14 I III II 常用函数常用函数 1 1 文本函数文本函数 2 2 时间函数时间函数 3 3 条件函数条件函数 4 4 统计函数统计函数 5 5 其他函数其他函数 各种函数的具体应用请看第IV部分:应用技巧 15 常用函数:文本函数 函数说明 &把两个字段连接起来 (是一个操作符,并非函数) LEFT()返回左边字符 RIGHT()返回右边字符 MID()返回中间字符 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()求和 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()从大到小排序 AND()逻辑和 (见36页“劳动合同到期提 示”中的示例说明 21 II 数据透视数据透视 IIIIII 常用函数常用函数 V V 学习方法学习方法 I I 基本规范基本规范 IV 常用技巧常用技巧 2222 为什么工资总和差1分钱? 四舍五入函数: ROUND() 格式:ROUND(参数,小数位数) 例: 返回目录 23 怎样抹掉工资零头 某公司规定,工资的零头不发放,全都取整数到元。 “抹掉”的意思不是四舍五入,而是向下取整。 向下取整函数:INT() 返回目录 24 工资向上取整 函数:CEILING(数值,整数) 例1: A公司规定,员工工资计发到角,1分至9 分的零头都计为1角。 例2: B公司规定,员工工资向上取到元,1分到 9角9分 返回目录 2525 应收账款到期日 根据发票日期和付款期计算到期日 公式:日期相加或相减 26 劳动合同还有多久到期 根据到期日计算到期剩余天数 函数:TODAY() 27 劳动合同终止日期 某公司规定,第一次签劳动合同,合同期为3年。为了管理上 的方便,终止日期都定为3年后同一月的最后一天 函数:YEAR(), MONTH(), DAY(), DATE() 28 制作自己的工作日志 公式:日期加法 函数:星期函数 返回目录 29 求员工工龄 函数:INT() 公式:日期减法 某公司为了减少员工流失率,设置了每 半年工龄的工龄津贴,每满半年工龄津 贴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(), COUNTA() 条件求和函数 SUMIF() 条件计数函数 COUNTIF() 条件统计函数与数据透视表的区别 思考:本月毛利率大于25%的订单有多少笔? 返回目录 34 晚会节目评比名次 迎春晚会节目评比:去掉一个最高分,去掉一个最低分,取平 均,取前三名 最大值函数MAX(), 最小值函数MIN() 最大第n位函数LARGE(), 最小第n位函数SMALL() 返回目录 35 条件格式:突出显示特定条件 的内容 条件格式:按照不同条件突出不同的单元格格式 应用:突出显示(Highlight)特定项目 思考:突出显示毛利大于25%的条目 把含有“贸易”二字的条目加强显示 提示:条件格式可以做到: 某栏的值符合某一个条件时,突出显示该值 某栏的值符合某一个条件时,突出显示该值对应的一整行 条件可以用等于、大于、小于、介于、公式 条件中用公式时,公式的最左边要用等号 可以最多同时应用三个条件 2007版 : 2003版 : 36 条件格式:形象表示数值大小 1.铜价走势 2.成绩分数段 返回目录 37 条件格式:突出显示过期的合同 1.突出显示生产任务未完成 2.劳动合同到期提示 3.突出显示双休日 4.输入重复值时直接用颜色提示 返回目录 38 行列对调 返回目录 39 简单的抽奖工具 函数:随机函数RAND() 返回目录 40 数据引用 vlookup() 函数:vlookup() 注意: 索引字段值在被查找的数据区域应是唯一的,要检查是否重复 注意公式中被查找数据区域应采用绝对位置表示 返回目录 4141 数据有效性 单元格中只允许输入某一范围的数值或日期,则可对单元格设 定数据有效性 方法:数据_有效性 例: 返回目录 4242 单元格保护 某些单元格,尤其是设定好的公式,不希望被误操作而破坏,则可进行单 元格的保护 也可以对工作表和工作簿进行保护 例: 操作请看下一页 返回目录 4343 保护前“锁定”的设置 工作表默认为“锁定”,保护前先去除工作表所有单元格的锁定 ,然后再对要保护的单元格进行锁定 4444 显示或隐藏0值 4545 零值显示为“-” 46 V. 学习方法 三字法则:问、搜、练 不建议去学宏、VBA 不求甚解,亦可照搬应用 47 人民币金额大写 注:WPS表格中的单元格格式可以直接设置人民币大写,但与 MS Excel不兼容,建议按这里的方法使用 返回目录 4848 Q & A 有什么问题吗? 联系: chenruhong126.com 13923837249 谢谢!