Excel经典培训教材.ppt

MicrosoftExcel,Instructor:董瑞营Mail:dongruiyingMSN:dry123_9,Module1Excel操作技巧,Ctrl+shift+8:选择活动单元格周围的相邻区域。ctrl+shift+方向键：:选择活动单元格相邻方向上的区域移动列：选择列ctrl+x,定位光标到目标位置，Insertcutcells按shift+拖动边框斜线表头：先输入表示列的信息，再输入表示行的信息，formatcellsalignment两个对齐均设为justify选择warptextborder,加一对角线光标定位在第一个单词前，输入几个空格长数字的输入：134134123412datatexttocolumn分数的输入：0空格1/5单元格中的内容换行：定位光标到要换行处alt+enter,Module1Excel操作技巧,公式填充：双击填充柄逻辑表达式的输入：进行特殊粘贴EditPastePasteSpecial粘贴数值粘贴格式粘贴转置打印设置页眉与页脚的设置:filepagesetup打印区域的设置:viewpagebreakpreviewInsertpagebreak行标题的打印:filepagesetupsheetprinttitles,Module2管理工作簿和工作表,Alt+shift+组合Alt+shift+取消组合工作表的选择：右击工作表导航栏ctrl+pageupctrl+pagedown移动、复制各选择工作表右击工作表标签,MoveorCopyWorksheet工作薄窗口的冻结与拆分定位光标Windowssplitorfreezepane,Module2管理工作簿和工作表,创建报表选择某一区域中的空单元格选择区域，Editgotospecialblank选择某一区域中的有内容的单元格选择区域，EditgotospecialConstant行或列的隐藏选择单元格，或行或列FormatRowHideFormatColumnHideEditdelete创建视图视图视图管理器,Module3公式和格式,公式的输入：单元格的引用：相对引用：A1在行中复制时，行不动，列在动，在列中复制时，列不动，行在动.绝对引用：$A$1f4不能动的要加$混合引用：$A1,A$1,适合向两个方向上复制.分别进行讨论，只讨论默认在动的，不讨论默认不动的。名称引用：定位功能(f5)，用作公式中的参数(f3),跨工作表操作。方法：选择要命名的区域，单击名称框输入名字：不能以数字命名不能以地址命名名字中间不能有空格名字写完后要enter确认删除：insertnamedefinedelete,Module3：公式和格式,单元格的追踪：ToolsformulaAuditingshowformulaAuditingtraceprecedents填充指令等差填充等比填充日期填充自定义序列填充复制填充,函数Function,Module4：函数,=sum(a1,b5)=sum(a1:b5)=average()=max()=min()=count()统计数字单元格的个数=counta()统计数字及文本的个数=countblank()统计空单元格的个数=if(logical_test,Value_if_true,Value_if_false)if嵌套（一个IF最多可以嵌套7个if）共8个ifround(number,num)=sumif(range,criteria,data_range)Range:条件所在的区域Criteria:条件，表达式用”data_range：求和的区域。,Module4：函数,=countif(range,criteria)判断是否重复：DatavalidationsettingscustomToolsformulaauditingshowformulaauditingcircleinvaliddata,Module4：函数,=Vlookup(lookup_value,table_array,col_index_num,range_lookup)lookup_value:查找的依据，它一定在数据源的第一列，且值唯一。table_array：数据源，命名数据源。col_index_num：返回被找信息所在的列数。range_lookup:是否精确匹配：true:-1相似匹配。false:0精确匹配。=IF($D4=,VLOOKUP($D4,data,5,0)isna(value):判断某单元格中的值是否是#N/A=TRUE:是#N/A=false:不是#N/A=IF(ISNA(VLOOKUP(A2,data,1,0)=FALSE,yes,no)=trim():去除多余的空格。=Clean():去除非打印符号=VLOOKUP(CLEAN(TRIM(A2),产品!A:A,1,0)=VLOOKUP(trim(A2),data,1,0),Match：返回被找信息所在的位置,=Match(lookup_value,table_array,type)lookup_value:查找依据，一定要被找表格的第一列，且值唯一table_array:一般要绝对引用Type:1:返回小于或等于lookup_value的最接近的值，且Table_array中一定要升序排放0:精确匹配：Table_array可以没有顺序-1:返回大于或等于lookup_value的最接近的值，且Table_array中一定要降序排放=IF($B22=,VLOOKUP($B22,产品!$A:$J,MATCH(E$21,产品!$A$1:$J$1,0),0),Module1：公式和格式,SEARCH(find_text,within_text,start_num)Find_text是要查找的文本。可以在find_text中使用通配符，包括问号(?)和星号(*)。问号可匹配任意的单个字符，星号可匹配任意一串字符。如果要查找真正的问号或星号，请在该字符前键入波形符()。Within_text是要在其中查找find_text的文本。Start_num是within_text中开始查找的字符的编号。Iserror():判断单元格中的内容是否有错True:此单元格中有错False:此单元格没错=IF(ISERROR(SEARCH(奶酪,A2,1)=TRUE,F2),Module4：函数,=right(text,num)=left(text,num)=Mid(text,start,num)=len():计算单元格中字符个数。=IF(RIGHT(TRIM(A2),1).,VLOOKUP(TRIM(A2),data,1,0),VLOOKUP(LEFT(TRIM(A2),LEN(TRIM(A2)-1),data,1,0),Module4：函数,Pmt(rate,nper,pv,fv,type)返回在固定利率下，投资或贷款的等额分期偿还额.SLN(cost,salvage,life)直线法NPV(rate,value1,value2,.):通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值IRR(values,guess):返回由数值代表的一组现金流的内部收益率。这些现金流不必为均衡的，但作为年金，它们必须按固定的间隔产生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。,Module5：工作表管理,有效性数值的唯一性：=COUNTIF($A$4:$A$18,A4)=1下拉列表的制作：内容来源于其他表格:选择要制作下拉列表的内容区域，命名选择要使用的区域，datavalidationallowlistsource,输入名子内容自定义：选择要使用的区域，datavalidationallowlistsource,输入内容，任意两内容之间英文半角的逗号隔开数值的效性：=SUM($H$4:$H$25)=5000日期的有效性：yyyy-mm-ddyy-mmm-dmm/dd/yyyy分列：datatexttocolumnfixedwidth=date(year,month,day),Module5：工作表管理,条件格式自身值决定自已的格式。由公式来决定格式。And(条件1，条件2，条件3)Or(条件1，条件2，条件3)自动套用格式,Module5：工作表管理,格式化数字,Module5：工作表管理,单元格安全设置单元格级的安全性：只给出允许操作的区域：选择允许别人操作的区域formatcellsprotection取消locked选择有公式的且不用修改的区域formatcellsprotection选中lockedhiddentoolsprotectionprotectsheet输入密码，取消selectlockedcells复制框光标定位到原始数据表，formatsheethidetoolsprotectionprotectworkbookstructer加密。,Module5：工作表管理,只保护指定的区域：ctrl+a选择所有单元格formatcellsprotection取消lockedhidden选择要保护的单元格区域formatcellsprotection选中lockedhiddentoolsprotectionprotectsheet加密，取消第一个复选框。文件级的安全：toolsoptionssecuritypasswordtoopenandpasswordtomodify,Module5：工作表管理,链接工作表/工作簿超级链接:公式链接:粘贴链接:矩阵链接:在要使用数据的表格中选择要使用的区域输入=,找到有数据的表格，选择数据区域Ctrl+shift+enter,Module6：数据库入门,数据前提：列中数据为字段，每个字段只有一个字段名行中数据为记录表格中不能合并单元格表格中不能有空白行及空白列，可以有空白单元格。,Module6：数据库入门,排序排行：单依据：定位光标到依据列，单击按钮多依据：datasort排列：Ctrl+x,insertcutcellsShift+拖动列的边框自定义排序排序使用:选择数据区域，datasortoptions自定义序列自定义不存在序列：toolsoptions自定义序列,导入自定义的内容。,Module6：数据库入门,筛选：(按条件显示记录)自动筛选：datafilterautofilter指定内容筛选取极端数据top10：（只对数值性字段有效）自定义：（最多可以定义两个条件，andor）*:表示多个字符？：只表示一个字符高级筛选：创建条件区域：将条件中用到的列的标题复制到原表格以外的某处在复制出的标题下方写条件。如果条件写在同一行中为and如果条件写在同一列中为orDatafilteradvancedfilter去除重复记录：,去除重复数据：,光标定位到空的单元格中DatafilteradvancedfilterAction:copytoanotherlocationListrange:选择要去除重复的数据Criteriarange:空Copyto:指定一个空的单元格,Module7：数据库函数,Subtotal(founction_num,ref)与筛选一起使用.1:Average2:count3:counta4:max5:min9.:sum数据库函数：=dsum(database,field,criteria)=daverage()=dmax()=dmin()=dcount()=dcounta(),Module8：宏,宏的制作VBACODE录制宏的执行菜单快捷键制作宏按钮调试宏编辑宏,Module8：宏,宏的录制Toolsmacrorecordnewmacro只操作与功能有关的步骤Toolsmacrostoprecording制作宏按钮Viewform按住alt在工作表区域中拖动定位光标到按钮中修改按钮名称,Module8：宏,调试宏：Toolsmacromacors选择一个调试的macro单击edit按F8单步执行macrocode修改宏代码例：,Module8：宏,修改前：,SubMacro1()Range(C10).SelectRange(A5:J82).AdvancedFilterAction:=xlFilterInPlace,CriteriaRange:=_Range(D1:E2),Unique:=FalseEndSub,SubMacro1()Range(C10).SelectRange(A5:J82).AdvancedFilterAction:=xlFilterInPlace,CriteriaRange:=_Range(D1).currentregion,Unique:=FalseEndSub,修改后：,Module8：宏,宏出错信息的处理：Onerrorresumenextapplication.displayalerts=false宏的嵌套宏中录制宏在代码中调用,Module9：数据库管理,数据透视表（数据分析）制作透视有的方法给原始区域命名。按向导操作：透视表的布局：分析出需求中要求计算的字段（data）分析出需求中要求显示字段（除去计算的都是要显示）显示指定项page显示多项或所有项row如果row中有多项字段，可将一项放到column中形成交叉表Page,row,column中可以为空，但不能放重复的内容.data可以重复的，但不能为空.,Module9：数据库管理,向透视表中添加及删除字段：添加：回到向导中添加(右击pivottablewizard)删除：右击字段名选择hide透视表中字段的组合：日期型：YYYY-MM-DDMM/DD/YYYY自动组合被组合的字段中每个值均为日期型被组合的字段中不能有空单元格将要组合的字段放到行中。手动组合先将行中只保留要组合的字段，其他字段放到页中生成透视表后，选择要组合的内容，右击组合。将页中的字段放到其应用有的位置,Module9：数据库管理,数值型：自动组合被组合的字段中每个值均为数值型被组合的字段中不能有空单元格将要组合的字段放到行中。手动组合先将行中只保留要组合的字段，其他字段放到页中生成透视表后，选择要组合的内容，右击组合。将页中的字段放到其应用有的位置,Module9：数据库管理,向透视表中添加新的计算字段。,Module9：数据库管理,向透视表中添加计算项（针对表中记录操作）：透视表中有组合字段的不可以添加计算项将计算项放到行首，计算时光标定位在行首。,Module9：数据库管理,更改行中的汇总方式：双击行中除最后一个字段以外的字段名。如果双击内容，展开及折叠。如果要展开或折叠所有项：右击字段名groupandshowdetailhidedetail显示极端数据：双击行中需求中为主语的字段（为定语的字段要放到主语的前方，或页中）双击为主语的字段标题，advancedtop10autoshow,Module9：数据库管理,透视表中百分比的显示：光标定位在data中要百分比显的字段中，右击fieldsettings%oftotal如果透视表中行中的一个字段占另一个行中字段百分比时，这两个字段要分别放到行中及列中。%ofrow%ofcolumn,Module9：数据库管理,明细数据的产生：双击data中的值：多维（最多四维）数据透视表的制作：每张原始表格都要有交叉表的结构要划分出表格的维数。,Module9：数据库管理,数据透视图的操作（F11）X轴（分类轴）：透视表中行中的内容。Y轴（数值轴）：透视表中data中的内容。图例：透视表中column中的内容。页：透视表中的页。,Module9：数据库管理,分析出分类比较图表柱形图看比较：（3种，图表图表类型）堆积图：分析出分类比较趋势变化图表折线图看趋势变化：分析出份额比较图表饼形图看份额比较（适合数据量较少的表格）。制作双柱线柱图表双轴图：（大数据与小数据并存）双轴折线双轴柱线图,Module9：数据库管理,透视表的更新：数值的修改：修改原始表格，回到透视表中右击refreshdata数据的插入：在原表格数据的中间插入，回到透视表中右击refreshdata数据的append:insertnamedefine将原数据表名子删除ctrl+shift+8全选原表数据，以相同的名子命名。回到透视表中右击refreshdata,Module9：数据库管理,透视表格式的操作：文本字段名子的修改：直接输入数字格式的修改：右击fieldsettingsnumber底纹边框的修改：透视表工具栏的第二个按钮。打印的设置：打印方向：文件页面设置调整打印内容：视图分页预览打印行及列的标题：文件页面设置工作表标题,Module10图表,静态图表动态图表,Module11数据预测,公式预测直线预测y=mx+b对数据预测y=(c*LN(x)-b幂函数据预测y=cxb指数函数预测y=cebx多项式预测y=(c2x2)+(c1x1)+b,Module11数据预测,变量求解,Module11数据预测,方案管理器方案是用于预测结果的一组数值可以在工作表中创建并保存多组不同的数值，并且可以在这些新方案之间任意切换，查看不同的方案结果,Module11数据预测,创建单变量数据表,Module11数据预测,创建双变量数据表,Module11数据预测,使用Solver“规划求解”可以通过改变其它单元格的数值，使目标单元格的数值达到最大值或最小值视图方案的管理,Module11数据预测,使用窗体控件制作敏感度分析模型,Module13:自定义EXCEL及其他,批注（建立、清除、编辑）及修订创建自定义菜单并指定宏自定义工具栏、置入内置菜单、指定宏应用模板和保存模板工作簿的属性修改,Module13使用和建立模板,使用通用模板与工作表模板创建保存与修改模板使用office2003模板库,Module13使用和建立模板,制作填空式模板去除工作表中的网格线隐藏工作表中的工作表标签隐藏工作表的行号及列标窗体的使用单选按钮的制作复选框的制作单元格的安全性设置,QA,Thanks!,