Excel2010培训高阶

条件格式开始\条件格式可以设置64个条件，有6种做法–突出显示单元格规则（三种数据类型都有效）–项目选取规则（只对数字有效）–数据条（颜色长短表示数据大小）–图标集（用不同的符号表示数据大小）–色阶（用颜色渐变过度表示数据大小）–使用公式自定义条件格式新增的的条件格式突出显示单元格规则（三种数据类型都有效）项目选取规则（只对数字有效）如何让我的销售数据更加直观Excel2010最新提供的数据条格式让我们换一种方式看销售报表ColorBar：更直观找出数据大小想知道谁销售的最好，很容易看出来表中哪些数据是亏损的？红色的数据代表不良业绩！不同的颜色区域更能表现出用户所需的关键数据Excel2010最新提供的颜色条格式使销售报表中的关键数据更加突出！Colorscale更直观的发现数据大小的分布区域还有许多更容易表现数据的图标Excel2010最新提供的图标格式使用户更容易阅读和理解销售数据Iconsets:更多的图标示显敏感数据2010Excel函数运用的基础:单元格引用Excel2010公式与函数简介在Excel2010中，一个公式最多可以包含64层嵌套，但是在早期版本的Excel中，最大嵌套层数仅为7在Excel2010中，公式最多可以包含255个参数，但是在早期版本的Excel中，公式中最大参数数目限制仅为30。Excel2010提供的下列函数在早期版本的Excel中不可用：AVERAGEIF,AVERAGEIFS,SUMIFS,COUNTIFS,IFERROR单元格的引用相对引用：A1在行中复制时，行号不动，列标在动，在列中复制时，列标不动，行号在动.绝对引用：$A$1f4不能动的要加$混合引用：$A1,A$1,适合向两个方向上复制.分别进行讨论，只讨论默认在动的，不讨论默认不动的。–动的不加$，不动的要加$–向下复制只看行号–向右复制只看列标2010条件判断函数条件判断类=if(logical_test,Value_if_true,Value_if_false)=round(number,num_digits)–Rounddown()–Roundup()多条件判断类函数=SumifS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)最多可以有127个=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2…)最多可以有127个Countifs的用法判断重复总次数–Countifs($a$1:$a$800,A1)：只能判断15位以内数–Countifs($a$1:$a$800,a1&”*”):判断任意多个数判断第几次重复–Countifs($a$1:a1,a1):只能判断15位以内数–Countifs($a$1:A1,a1&”*”)判断任意多个数2010查找匹配类函数根据内容找内容=Vlookup(lookup_value,table_array,col_index_num,range_lookup)lookup_value:查找的依据，它一定在数据源的第一列，且值唯一。table_array：数据源，命名数据源。col_index_num：返回被找信息所在的列数。range_lookup:是否精确匹配：•true:-1相似匹配。–被找依据在被找表中要升序排放–返回小于或等于查找值的最接近值–常用于数值范围的查找•false:0精确匹配。Vlookup找不到数据原因被找的数据不在被找表格的首列中有多余的空格–Trim():去除多余空格有非打印符号–Clean():去除非打印符号数据类型不匹配：数据\分列\下一步\下一步(data\texttocolumn\next\next)–文本text–数值general–日期date根据内容找位置=Match(lookup_value,table_array,type)–lookup_value:查找依据–table_array:一般要绝对引用,只能为一行或一列数据–Type:•1:返回小于或等于lookup_value的最接近的值，且Table_array中一定要升序排放•0:精确匹配：Table_array可以没有顺序•-1:返回大于或等于lookup_value的最接近的值，且Table_array中一定要降序排放其他查找函数Index(Array,Row_num,Column_num):根据位置找内容Iferror(value,value_if_error)Date(year,month,day)Year()Month()创建数值查询类公式=ISERROR(value):判断某单元格中的值是否有错误=TRUE:有错误=false:无错误＝IFERROR(value,value_if_error)=IFerror(VLOOKUP(A2,data,1,0),”有错误”)=trim():去除多余的空格。=clean():清除非打印符号=VLOOKUP(trim(A2),data,1,0)2010Excel2010平面图表操作专业图表轻松做！静态图表图表名词–系列•系列名称：图例•系列大小：Y轴–类别•类别标签：X轴方法：（由上到下由左到右）–选择相邻数据做图–F11：在新的工作表中创建图表–选择不相邻数据做图向图表中添加数据只能添加系列不能添加类别添加系列：–选择系列数据,ctrl+c–选择数图,ctrl+v修改系列的位置–在图表中选择要修改的系列–修改公式中的最后一个参数阶梯图的制作应用1.选择数据制作平面堆积图2.设置数据系列的重叠比例3.给图表系列显示数据4.格式化阶梯图1.2796921.4852471.0109621.2056581.2700470.8866291.2616010.30882901234562001年2002年百万第四季第三季第二季第一季双轴线柱图的制作1.双轴线柱图适合展示的数据2.选择数据制作平面柱形图3.如何选择图表中的数据小的系列4.设置数据系列的坐标轴5.设置数据系列的图表类型6.设置并显示出线形系列的数据标签242723271381312191719136654767725223547404937560204060801000501001501月2月3月4月5月6月7月8月9月10月11月12月千SumofOrderAmount-UKSumofOrderAmount-USACountofOrderID-UKCountofOrderID-USA甘特图的制作技巧1.甘特图适合展示的数据数据2.选择数据制作平面条形图3.选择适合的系列更改其坐标轴4.设置两个坐标轴显示的数据一致5.设置第二坐标轴上的数据系列的分类间距0204060801000102030405060708090100ABCPlanAct瀑布图的制作技巧1.瀑布图适合展示的数据2.制作所需的表格布局3.制作三维堆积图4.修改数据及格式以显示桥形连接020406080100120StartabcdefghEnd1025155102025151001152010使用Excel2010分析数据透视表功能多条件分类汇总对分类汇总后的数据进行筛选（xlsx)对分类汇总后的数据进行字段计算数据项计算对分类汇总后的数据进行百分比分析–同比–环比–MTD–…拿多个表格进行数据汇总制作交互式图表…原始数据要求行为记录，列为字段每个字段必须有一个字段名表格中不能有合并单元格表格中不能有空列，但可以有空单元格每个字段保持数据类型一致：数据\分列–文本：字符运算–数值：算术运算，逻辑运算–日期：算术运算，逻辑运算将区域转化为表格再制作透视表：插入\表格多条件分类汇总与透视表布局的关系报表筛选：作条件的字段（要筛选数据的字段）行或列标签：分类的字段–合并同类项–分组：数值，日期数值：汇总（计算）的字段透视表进行数据组合透视表中字段的组合：右击要给合的字段\Group–日期型，数值型，文本型：•自动组合–被组合的字段中每个值均为固定类型–被组合的字段中不能有空单元格–将要组合的字段放到行中。•手动组合–行中只保留要组合的字段，其他字段先放到页中–选择要组合的内容，右击组合–组合完后将其他页字段中的字段放到该放的位置对分类汇总后的数据进行字段计算数据项计算向透视表中添加新的计算字段。–光标定位在透视表中，PivotTabletools\Options\Formulas\Calculatedfield对分类汇总后的数据进行字段计算数据项计算透视表计算项：–用过组合的透视表字段不能进行计算项–光标定位在透视表中要进行计算项的字段中。PivotTabletools\Options\Formulas\Calculatedfield行标签字段添加汇总更改行中字段的汇总方式：–双击行中除最后一个字段以外的字段名\Subtotals&filters。–如果双击内容，展开及折叠。–如果要展开或折叠所有项：右击字段名Expand/collapse,选择Expand或Collapse透视表对汇总后数据的筛选（＊.xlsx)对行或列标签中字段的筛选–同平同表格对数值的筛选–被筛选的字段：行–被筛选的依据：值–作条件的字段：报表筛选或行的被筛选的字段的前方–被查询的字段：行的被筛选的字段的后方透视表进行数据百分比分析透视表中百分比的显示：光标定位在Data中要百分比显的字段中，右击/ValueFieldSettings•%oftotal•如果透视表中行中的一个字段占另一个行中字段百分比时，这两个字段要分别放到行中及列中。–%ofrow–%ofcolumn–Runningtotalin多维数据表格分析数据明细数据的产生：双击data中的值：多维（四维）数据透视表的制作：–每张原始表格都要有交叉表的结构–要划分出表格的维数。–使用透视表向导\Multipleconsolidationranges透视图布局规律多行多列数据产生的图表–X轴：行–图例：列–Y轴：数值–图的下拉列表：报表筛选单列数据产生的图表（饼形图）–图例：行–扇区大小：数值–图的下拉列表：报表筛选透视表进行数据分析透视表的更新：–数值的修改：修改原始表格，回到透视表中右击Refresh–数据的插入：在原表格数据的中间插入，回到透视表中右击refreshdata–数据的append: