电子表格应用实务(zy版).ppt

2020 3 16 1 电子表格操作实务 张英 2020 3 16 2 指导思想 结合常用的具体操作技能 探讨利用电子表格解决实务工作的方法论 重点在于方法 而非技能 授之以鱼乎授之以渔乎 2020 3 16 3 课程目的 在日常工作中 低成本充分发挥电子表格的功能优势 极大 成倍数 的提高数据处理效率 培养自己成为数据处理高手 结合常见数据处理案例 大幅提高电子表格的应用能力 2020 3 16 4 什么叫高手 运用大量的工具 函数 熟悉大量功能 能够解决别人不能解决的问题 多 熟练掌握常见功能 能够快速的进行大量的数据处理 快 2020 3 16 5 高手是怎么练成的 教科书能否使你成本为高手 以工作实际应用为基础 日积月累做个有心人 常思考 学会举一反三 高度灵活 2020 3 16 6 一个问题 请你 重新认识键盘与鼠标 尽可能的用键盘完成Excel工作 键盘与鼠标哪一个更快 2020 3 16 7 鼠标的功能再认识 鼠标的基本功能 鼠标的学习功能 特别功能绍介 填充柄a复制和填充 左 右键填充 b插入和删除b快速移动 2020 3 16 8 键盘的功能再认识 记住常用的快揵键是提高速度的关键认识特殊的字符功能 a 定义绝对地址b 在函数中用c 快捷键介绍 2020 3 16 9 Excel界面再认识 菜单栏工具栏 菜单栏 视图 工具栏 菜单栏 工具 选项 2020 3 16 10 功能劣势 多岗位协同能力不足设计规范在执行时难以得到保证 约束性 数据标准不易统一数据容量过小 运算效率瓶颈 2020 3 16 11 界面 2020 3 16 12 操作界面 工作表256列数256 ctrl 行数65336 ctrl 单元格1600万个 有的很类似excel的电子表格达到200行 就很慢了 2020 3 16 13 表页管理 2020 3 16 14 数据录入 Ctrl 多表页的ctrl选定单元格内录入同样内容ctrl enter熟练使用填充柄单元格内的换行alt enter 2020 3 16 15 填充与复制 选择性复制a 不同文本间的复制b 转置复制c 格式复制 将公式变为值 d 巧用 乘 功能填充 自定义序列董事长总经理部门经理主管专员文员 2020 3 16 16 单元格地址 相对地址 a1绝对地址 a 1行绝对 a1列绝对 a 1 2020 3 16 17 单元格 数据格式 真正的值 样式格式 美观 可视 自定义格式 障眼法 条件格式 合并单元格的负面影响 不同格式对数据运算产生的影响 2020 3 16 18 2020 3 16 19 自定义单元格式 自定义格式代码中最多可以指定四个以分号分隔的节 它们顺序定义了格式中的正数 负数 零和文本 可以使用格式代码来描述如何显示以下内容 数字 日期或时间 货币 百分比或科学记数以及文本或空格 2020 3 16 20 排序 升序数字 字母 逻辑值 错误值 空格A99前 A100前 汉字的排序 拼单 笔划 多关键字 例 预付账款 2020 3 16 21 自定义排序 2020 3 16 22 排序规则 建议编码长度尽可能统一 A999与A1000 分栏排序 如 2020 3 16 23 条件格式 对满足条件的单元格指定字体与底纹 如 对标准以外的数据预定其格式 以区别于其它正常数据 使其更为醒目 将资产负债表中的负数以红色显示 或将净利润增长未达到20 的企业以红色显示 2020 3 16 24 自动筛选 从数据表中找出满足条件的记录 暂时隐藏不满足条件的记录 例 配合排序的打印物料卡 工资条等 2020 3 16 25 高级筛选2 将筛选结果独立出来适用于条件复杂或筛选字段较多的情况 例 多条件查询预付账款 可用于抽取不重复记录 2020 3 16 26 高级筛选 或 某一列满足两个以上的条件即可 与 同时满足不同条件的多个列的数据 2020 3 16 27 分类汇总 树状方式分级查询a 字段名 列标题 b 关键列排序 汇总多家公司货币资金 2020 3 16 28 快数求和 2020 3 16 29 数据透视表 对工作表行 列重新组合例 将凭证直接生成多栏式明细账例 成本结转 数据透视图 2020 3 16 30 合并计算 跳出二维的限制 使Excel具备了255张表间的穿透性 例 合并资产类项目 2020 3 16 31 图表 图表类型及用途图表的建立与调整通过修改图表对数据的修改格式化 2020 3 16 32 复杂图表 图表的单变量求解趋势线 通过回归分析模型 根据历史数据预测未来数据 2020 3 16 33 动态图表 2020 3 16 34 公式与函数 公式的组成 a 运算符b 单元格c 参数 如函数等 一个公式至多容纳2 10 即1024 个字符 Excel的灵魂 2020 3 16 35 可编辑 2020 3 16 36 函数 一组预先定义的特殊命令 根据给定参数得出结果 其实质为数据工具 可带参数或不带参数 其参数可以是 单元格 函数 数组 整行 整列或区域等 2020 3 16 37 公式 由用户自行设计对工作表进行计算和处理的式子 其内部可以包括函数 引用 运算符和常量等 公式 SUM E1 H1 A1 26 的结构为 SUM E1 H1 是函数 A1 则是对单元格A1的引用 使用其中存储的数据 26 则是常量 和 则是算术运算符 2020 3 16 38 算术运算符 2020 3 16 39 比较运算符 2020 3 16 40 逻辑运算符 And 所有参数的逻辑值为真时返回TRUE 真 Or 所有参数中的任意一个逻辑值为真时即返回TRUE 真 2020 3 16 41 其它运算符 文本运算符 如30 元 则为30元区域运算符 对其所引用的所有单元格进行引用 如A1 B2联合操作符 将多个引用合并为一个引用 如 SUM B15 D5 2020 3 16 42 运算符的优先集 2020 3 16 43 函数的语法与嵌套 函数名 参数 参数可以是常量 公式或函数等当一个函数是另一个函数的参数时 我们称为嵌套 如 REPT INT B5 rept函数使用了int函数 并将后者返回的结果作为其参数 2020 3 16 44 嵌套实例 简单进度条 2020 3 16 45 逻辑运算符 if 根据参数的真假 执行或返回相应的数值或公式 语法 IF logical test value if true value if false 参数 Logical test为判别条件 Value if true是Logical test为TRUE时函数的返回值 value if false反之 参考所得税的计算与格式整理 2020 3 16 46 查找函数 Vlookup 在区域首行查找指定的数值 找到该数值后 确定该数值所在行号 返回以该行号与指定列号确定的单元格的值 激动人心的函数 思考 打印工资单的新方法 2020 3 16 47 查找函数 Vlookup 语法 VLOOKUP lookup value table array col index num range lookup 参数 Lookup value为需要在数据表第一列中查找的数值 Table array为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 Col index num为table array中待返回的匹配值的列序号 Range lookup为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range value为FALSE 函数VLOOKUP将返回精确匹配值 如果找不到 则返回错误值 N A 2020 3 16 48 查找函数 2020 3 16 49 统计函数 COUNTIF用途 统计某一区域中符合条件的单元格数目 语法 COUNTIF range criteria 参数 range为需要统计的符合条件的单元格数目的区域 Criteria为参与计算的单元格条件 其形式可以为数字 表达式或文本 如36 160 和 男 等 其中数字可以直接写入 表达式和文本必须加引号 2020 3 16 50 统计函数 SUMIF用途 根据指定条件对若干单元格 区域或引用求和 语法 SUMIF range criteria sum range 参数 Range为用于条件判断的单元格区域 Criteri是由数字 逻辑表达式等组成的判定条件 Sum range为需要求和的单元格 区域或引用 2020 3 16 51 统计函数 RANK用途 返回一个数值在一组数值中的排位 语法 RANK number ref order 参数 Number是需要计算其排位的一个数字 Ref是包含一组数字的数组或引用 其中的非数值型参数将被忽略 Order指明排位的方式 注意 函数RANK对重复数值的排位相同 但重复数的存在将影响后续数值的排位 如在一列整数中 若整数8出现两次 其排位为5 则9的排位为7 没有排位为6的数值 2020 3 16 52 参数中的行列号 2020 3 16 53 一个多功能函数 唯一能忽略筛选中已隐藏内容的函数 2020 3 16 54 RightLeftTextValue 字符函数 2020 3 16 55 信息函数 IserrorIsnumberIstext 宽度不足 VALUE 无效数值 如 文本 DIV 0 NAME N A REF 2020 3 16 56 type返回值 2020 3 16 57 递归 因公式自已调用自己 而引申出的递归运算 例题中的A1究竟是多少 2020 3 16 58 Excel实务 库存管理 2020 3 16 59 库存管理 需求分析实现仓库的动态管理 通过进出库单的及时录入与打印得出及时库存 实现价格管理与客户资料管理 实现配货人员工资计算 特别事项 600余个品种分别进库最终将汇总为9个品种出库 2020 3 16 60 Excel实务 用户分析1 物控部制单文员通过此软件打印配货单与送货单 2 仓库主管根据此软件按排配货与收货等业务 3 财务会计月末结转成本 2020 3 16 61 Excel实务 框架规划数据来源分类与编码 2020 3 16 62 Excel实务 功能亮点 1 数据的一次性生成与打印转换 2 随心所欲的自定义方式 3 与实务管理的实时性 4 管理与分析功能 2020 3 16 63 Excel实务 合并报表个别报表模版设置个别报表勾辑关系检查自动生成工作底稿手工生成调整与抵销分录自动生成合并报表 2020 3 16 64 公式心得 公式运算时 对于相同的数据可引用单元格 而非A1 1 17 为什么 数字 字母代码易于统一 而文本型的中文等容易产生掺入空格等不易查找的字符 为什么以下两值不相等 临时区域的灵活设置和应用嵌套的分层级书