Excel高级使用.ppt

Excel使用技巧探讨 MichaelJin Agenda Excel 你会下面几手吗 给别人看 但不给别人动如何实现每页的行头打印如何设定条件格式公式和函数的使用工作表的保护和模板的制作图表的应用数据管理和分析附加工具 工具 宏的简单应用 ExcelXP使用技巧点滴 给别人看 但不给别人动单击右键 设置单元格 将保护标签中的锁定复选框清除 然后对该工作表设置保护要保护工作表 可按以下选择 工具 保护 保护工作表 选择密码如何实现每页的行头打印对于某些较长的表格 需要在每页的顶端显示其标题要实现每页标题打印 可按以下选择 文件 页面设置 工作表 选择打印标题 顶端或左端标题行如何设定条件格式 格式 条件格式 按照你所需要的条件来设置格式 在Excel中 如何在一个单元格中分行填写 答 很简单 当您需要重起一行写入内容时 按住Alt键回车就可以了 在Excel中 如何在一个单元格中分行填写 很简单 当您需要重起一行写入内容时 按住Alt键回车就可以了 如何才能快速复制公式 通过拖动填充柄复制公式人人都会 不过如果复制的行太多 操作起来并不方便 此时可以选择双击填充柄完成复制 人事部经常要在Excel表格里录入部门 性别 城市等数据 这些数据重复性很高 比如 在 部门 这一列 可能需要多次输入 财务部 要是能从下拉列表中选择数据就方便了 还不容易出错 Excel当然可以实现这个功能 如图2所示 只要从 数据 菜单选择 有效性 再按图3所示进行设置即可 何将Excel电子表格中的数据进行行列互换 如何将Excel电子表格中的数据进行行列互换 出于种种考虑 需要将电子表格中的行和列进行互换 这时只需复制需要行列互换的表格区域 在需要放置的单元格上执行 编辑 菜单中的 选择性粘贴 命令 然后在 选择性粘贴 对话框的 粘贴 选项区域中选择 全部 选项 在 运算 选项区域中选择 无 并选择 转置 复选框 完成后单击 确定 按钮即可 Excel公式和函数的使用 返回参数组中非空值的数目 利用函数COUNTA可以计算数组或单元格区域中数据项的个数 CountA Excel公式和函数的使用 COUNTIF 计算给定区域内满足特定条件的单元格的数目 COUNTIF range criteria Range 为需要计算其中满足条件的单元格数目的单元格区域 Criteria 为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为1500 1500 1500 经理 示例 假设C5 C14中的内容分别为 总经理 经理 主管 职员 COUNTIF C5 C14 经理 等于3 CountIF Excel公式和函数的使用 SUMIF 根据指定条件对若士单地格求和 SUMIF range criteria sum range Range 为用于条件判断的单元格区域 Criteria 为确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为32 32 32 apples SUM range 为需要求和的实际单元格 只有当Range中的相应单元格满足的条件 才对sum range中的单元格求和 如果省略sum range 则直接对Range中的单元格求和 例 假设A1 A4的内容分别为下列分属于四套房子的属性值 100 000 200 000 300 000 4000 000 B1 B4的内容为下列与每个属性值相对应的销售佣金 7 000 14 000 21 000 28 000 SUMIF A1 A4 160 000 B1 B4 等于 63 000 SUMIF Excel公式和函数的使用 IF 执行真假值判断 根据逻辑测试的真假值 返回不同的结果 可以使用函数IF对数值和公式进行条件检测 IF logical test value if true value if false Logical test 计算结果为TRUE或FALSE的任何数值或表达式 Value if true Logical test为TRUE时函数的返回值 Value if false Logical test为FALSE时函数的返回值 说明 函数IF可以嵌套七层 例 假调用有一张费用开支工作表 B2 B4中有一月 二月和三月的 实际费用 其数值分别为1 500 500和500 C2 C4是相同期间内的 预算经费 数值分别为900 900和925 可以通过公式来检测某一月份是否出现预算超支 下列的公式将产生有关的信息文字串 IF B2 C2 超过预算 OK 等于 超过预算 IF B3 C3 超过预算 OK 等于 OK IF Excel公式和函数的使用 VLOOKUP 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 VLOOKUP lookup value table array col index num range lookup Lookup value 为需要在数据表第一列中查找的数值 Table array 为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 如果range lookup为TRUE 则table array的第一列中的数值必须按升序排列 否则 函数VLOOKUP不能返回正确的数值 如果range lookup为FALSE table array不必进行排序 Col index num 为table array中待返回的匹配值的列序号 Range lookup 为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range value为FALSE 函数VLOOKUP将返回精确匹配值 VLOOKUP Excel公式和函数的使用 将几个文本字符串合并为一个文本字符串 语法CONCATENATE text1 text2 Text1 text2 为1到30个将要合并成单个文本项的文本项 这些文本项可以为文本字符串 数字或对单个单元格的引用 说明也可以用 和号 运算符代替函数CONCATENATE实现文本项的合并 CONCATENATE LEFT RIGHT 工作表的保护和模板的制作 在格式 单元格 保护中设置锁定与公式的隐藏工作表 工具 保护 保护工作表工作簿 文件 另存为 工具 常规选项 工作表及工作簿的保护 模板的制作 工作表的制作 背景 格式等保存类型 模板 Excel图表的应用 图表类型建立图表图表的编辑 添加 删除序列等图表的格式化 图案 数轴的格式化等数据地图 数据管理和分析 排序 根据一列或多列排序筛选 数据 筛选自动筛选 自定义高级筛选 输入条件区域 可以选择不重复的记录分类汇总 1 排序 2 数据 分类汇总 3 分级显示与取消有效数据 数据 有效数据定义单个单元格或某单元格区域中所有单元格的正确数值 将数据项限制为一个特殊类型 例如 整数 分数或文字 并对正确的项设置限制 合并计算 数据 合并计算 选择引用位置从一个或多个数据源区归纳数据并显示在一个表格中 数据管理和分析 数据透视表 1 建立 数据 数据透视表 2 更新模拟运算表 1 单变量模拟运算表 2 双变量模拟运算表分列 数列 分列使用 分列 向导 可将工作表某一单元格中的文字分隔为列 分析 向导帮您指定如何用分