Excel高级使用...ppt

,Excel 使用技巧探讨,Michael Jin,Agenda,Excel 你会下面几手吗？给别人看，但不给别人动如何实现每页的行头打印如何设定条件格式公式和函数的使用工作表的保护和模板的制作图表的应用数据管理和分析附加工具：工具/宏的简单应用,Excel XP 使用技巧点滴,给别人看，但不给别人动单击右键,设置单元格,将保护标签中的锁定复选框清除，然后对该工作表设置保护要保护工作表,可按以下选择:工具保护保护工作表选择密码如何实现每页的行头打印对于某些较长的表格，需要在每页的顶端显示其标题要实现每页标题打印,可按以下选择:文件页面设置工作表选择打印标题顶端或左端标题行如何设定条件格式格式条件格式，按照你所需要的条件来设置格式,在Excel中，如何在一个单元格中分行填写？答：很简单！当您需要重起一行写入内容时，按住Alt键回车就可以了！,在Excel中，如何在一个单元格中分行填写？,很简单！当您需要重起一行写入内容时，按住Alt键回车就可以了！,如何才能快速复制公式。,通过拖动填充柄复制公式人人都会，不过如果复制的行太多，操作起来并不方便。此时可以选择双击填充柄完成复制。,人事部经常要在Excel表格里录入部门、性别、城市等数据，这些数据重复性很高，比如，在【部门】这一列，可能需要多次输入【财务部】。要是能从下拉列表中选择数据就方便了，还不容易出错。,Excel当然可以实现这个功能，如图2所示。只要从【数据】菜单选择【有效性】，再按图3所示进行设置即可,何将Excel电子表格中的数据进行行列互换？,如何将Excel电子表格中的数据进行行列互换？,出于种种考虑，需要将电子表格中的行和列进行互换，这时只需复制需要行列互换的表格区域，在需要放置的单元格上执行【编辑】菜单中的【选择性粘贴】命令，然后在【选择性粘贴】对话框的【粘贴】选项区域中选择【全部】选项；在【运算】选项区域中选择【无】；并选择【转置】复选框，完成后单击【确定】按钮即可。,Excel 公式和函数的使用,返回参数组中非空值的数目。利用函数COUNTA可以计算数组或单元格区域中数据项的个数。,CountA,Excel 公式和函数的使用,COUNTIF：计算给定区域内满足特定条件的单元格的数目。COUNTIF（range,criteria）Range:为需要计算其中满足条件的单元格数目的单元格区域。Criteria:为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为1500、“1500”、“1500”、“经理”。示例：假设C5C14中的内容分别为“总经理”、“经理”、“主管”、“职员”: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中的单元格求和。例：假设A1A4的内容分别为下列分属于四套房子的属性值：￥100，000￥200，000，￥300，000，￥4000，000。B1B4的内容为下列与每个属性值相对应的销售佣金；￥7，000，￥14，000，￥21，000，￥28，000。SUMIF（A1A4,“160，000”，B1B4）等于$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可以嵌套七层，例:假调用有一张费用开支工作表，B2B4中有一月、二月和三月的“实际费用”，其数值分别为1，500、500和500。C2C4是相同期间内的“预算经费”，数值分别为900、900和925。可以通过公式来检测某一月份是否出现预算超支，下列的公式将产生有关的信息文字串：IF(B2C2,“超过预算”，“OK”)等于“超过预算”IF(B3C3,“超过预算”，“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)双变量模拟运算表分列:数列/分列 使用“分列”向导，可将工作表某一单元格中的文字分隔为列。“分析”向 导帮您指定如何用分隔符或分界符(如制表符或逗号)将文字分成列。单变量求解:工具/单变量求解 调整指定的单元格的值，直到依赖该单元格的公式达到某个目标值。方案:工具/方案(1)方案的添加与删除(2)方案的编辑与合并,附加工具：工具/宏的简单应用,自动保存：工具/选项/保存/自动保存（指定时间间隔）宏：录制宏控件工具箱实例（计算业代奖金）代码分析,