Excel经典培训教材PPT.ppt

Microsoft ExcelMicrosoft ExcelInstructor:Instructor:董瑞营董瑞营Module 1 Excel操作技巧操作技巧Ctrl+shift+8:选择活动单元格周围的相邻区域。ctrl+shift+方向键：:选择活动单元格相邻方向上的区域移动列：选择列ctrl+x,定位光标到目标位置，Insertcut cells按shift+拖动边框斜线表头：先输入表示列的信息，再输入表示行的信息，formatcellsalignment两个对齐均设为justify 选择warp textborder,加一对角线光标定位在第一个单词前，输入几个空格长数字的输入：datatext to column分数的输入：0空格1/5单元格中的内容换行：定位光标到要换行处alt+enter Module 1 Excel操作技巧操作技巧公式填充：双击填充柄逻辑表达式的输入：进行特殊粘贴 EditPastePaste Special粘贴数值粘贴格式粘贴转置打印设置页眉与页脚的设置:filepage setup打印区域的设置:viewpage break previewInsertpage break行标题的打印:filepage setupsheetprint titlesModule 2 管理工作簿和工作表管理工作簿和工作表 Alt+shift+组合Alt+shift+取消组合工作表的选择：右击工作表导航栏 ctrl+pageup ctrl+pagedown移动、复制各选择工作表 右击工作表标签,Move or Copy Worksheet工作薄窗口的冻结与拆分 定位光标Windowssplit or freeze paneModule 2 管理工作簿和工作表管理工作簿和工作表创建报表选择某一区域中的空单元格选择区域，Editgo tospecialblank选择某一区域中的有内容的单元格选择区域，Editgo tospecialConstant行或列的隐藏选择单元格，或行或列FormatRowHideFormatColumnHideEditdelete创建视图视图视图管理器 Module 3公式和格式公式和格式公式的输入：单元格的引用：相对引用：A1 在行中复制时，行不动，列在动，在列中复制时，列不动，行在动.绝对引用：$A$1 f4不能动的要加$混合引用：$A1,A$1,适合向两个方向上复制.分别进行讨论，只讨论默认在动的，不讨论默认不动的。名称引用：定位功能(f5)，用作公式中的参数(f3),跨工作表操作。方法：选择要命名的区域，单击名称框输入名字：不能以数字命名不能以地址命名名字中间不能有空格名字写完后要enter确认删除：insertnamedefinedeleteModule 3：公式和格式公式和格式单元格的追踪：Toolsformula Auditingshow formula Auditingtrace precedents填充指令 等差填充等比填充日期填充自定义序列填充复制填充函数函数 Function Module 4：函数函数=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：求和的区域。Module 4：函数函数=countif(range,criteria)判断是否重复：DatavalidationsettingscustomToolsformula auditingshow formula auditingcircle invalid dataModule 4：函数函数=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)Module 1：公式和格式公式和格式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)Module 4：函数函数=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)Module 4：函数函数Pmt(rate,nper,pv,fv,type)返回在固定利率下，投资或贷款的等额分期偿还额.SLN(cost,salvage,life)直线法NPV(rate,value1,value2,.):通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值IRR(values,guess):返回由数值代表的一组现金流的内部收益率。这些现金流不必为均衡的，但作为年金，它们必须按固定的间隔产生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和定期收入（正值）。Module 5：工作表管理工作表管理 有效性 数值的唯一性：=COUNTIF($A$4:$A$18,A4)=1下拉列表的制作：内容来源于其他表格:选择要制作下拉列表的内容区域，命名选择要使用的区域，datavalidationallowlistsource,输入名子内容自定义：选择要使用的区域，datavalidationallowlistsource,输入内容，任意两内容之间英文半角的逗号隔开数值的效性：=SUM($H$4:$H$25)=5000日期的有效性：yyyy-mm-dd yy-mmm-d mm/dd/yyyy分列：datatext to columnfixed width=date(year,month,day)Module 5：工作表管理工作表管理条件格式 自身值决定自已的格式。由公式来决定格式。And(条件1，条件2，条件3)Or(条件1，条件2，条件3)自动套用格式 Module 5：工作表管理工作表管理格式化数字 Module 5：工作表管理工作表管理单元格安全设置单元格级的安全性：只给出允许操作的区域：选择允许别人操作的区域formatcellsprotection取消locked选择有公式的且不用修改的区域formatcellsprotection选中locked hiddentoolsprotectionprotect sheet输入密码，取消select locked cells复制框光标定位到原始数据表，formatsheethidetoolsprotectionprotect workbookstructer加密。Module 5：工作表管理工作表管理只保护指定的区域：ctrl+a选择所有单元格formatcellsprotection取消locked hidden选择要保护的单元格区域formatcellsprotection选中locked hiddentoolsprotectionprotect sheet加密，取消第一个复选框。文件级的安全：toolsoptionssecuritypassword to open and password to modifyModule 5：工作表管理工作表管理链接工作表/工作簿 超级链接:公式链接:粘贴链接:矩阵链接:在要使用数据的表格中选择要使用的区域输入=,找到有数据的表格，选择数据区域Ctrl+shift+enterModule 6：数据库入门：数据库入门 数据前提：列中数据为字段，每个字段只有一个字段名行中数据为记录表格中不能合并单元格表格中不能有空白行及空白列，可以有空白单元格。Module 6：数据库入门：数据库入门排序排行：单依据：定位光标到依据列，单击按钮多依据：datasort排列：Ctrl+x,insertcut cellsShift+拖动列的边框自定义排序排序使用:选择数据区域，datasortoptions自定义序列自定义不存在序列：toolsoptions自定义序列,导入自定义的内容。Module 6：数据库入门：数据库入门筛选：(按条件显示记录)自动筛选：datafilterauto filter指定内容筛选取极端数据top 10：（只对数值性字段有效）自定义：（最多可以定义两个条件，and or）*:表示多个字符？：只表示一个字符高级筛选：创建条件区域：将条件中用到的列的标题复制到原表格以外的某处在复制出的标题下方写条件。如果条件写在同一行中为and如果条件写在同一列中为 orDatafilteradvanced filter去除重复记录：去除重复数据：去除重复数据：光标定位到空的单元格中Datafilteradvanced filterAction:copy to another locationList range:选择要去除重复的数据Criteria range:空Copy to:指定一个空的单元格Module 7：数据库函数：数据库函数Subtotal(founction_num,ref)与筛选一起使用.1:Average2:count3:counta4:max5:min9.:sum数据库函数：=dsum(database,field,criteria)=daverage()=dmax()=dmin()=dcount()=dcounta()Module 8：宏：宏宏的制作VBACODE录制宏的执行菜单快捷键制作宏按钮调试宏 编辑宏Module 8：宏：宏宏的录制Toolsmacrorecord new macro只操作与功能有关的步骤Toolsmacrostop recording制作宏按钮Viewform按住alt在工作表区域中拖动定位光标到按钮中修改按钮名称Module 8：宏：宏调试宏：Toolsmacromacors选择一个调试的macro单击edit按F8单步执行macro code修改宏代码例：Module 8：宏：宏修改前：Sub Macro1()Range(C10).Select Range(A5:J82).AdvancedFilter Action:=xlFilterInPlace,CriteriaRange:=_ Range(D1:E2),Unique:=FalseEnd SubSub Macro1()Range(C10).Select Range(A5:J82).AdvancedFilter Action:=xlFilterInPlace,CriteriaRange:=_ Range(D1).currentregion,Unique:=FalseEnd Sub修改后：修改后：Module 8：宏：宏宏出错信息的处理：On error resume nextapplication.displayalerts=false宏的嵌套宏中录制宏在代码中调用Module 9：数据库管理：数据库管理 数据透视表（数据分析）制作透视有的方法给原始区域命名。按向导操作：透视表的布局：分析出需求中要求计算的字段（data）分析出需求中要求显示字段（除去计算的都是要显示）显示指定项page显示多项或所有项row如果row中有多项字段，可将一项放到column中形成交叉表Page,row,column中可以为空，但不能放重复的内容.data可以重复的，但不能为空.Module 9：数据库管理：数据库管理向透视表中添加及删除字段：添加：回到向导中添加(右击pivottable wizard)删除：右击字段名选择hide透视表中字段的组合：日期型：YYYY-MM-DD MM/DD/YYYY自动组合被组合的字段中每个值均为日期型被组合的字段中不能有空单元格将要组合的字段放到行中。手动组合先将行中只保留要组合的字段，其他字段放到页中生成透视表后，选择要组合的内容，右击组合。将页中的字段放到其应用有的位置Module 9：数据库管理：数据库管理数值型：自动组合被组合的字段中每个值均为数值型被组合的字段中不能有空单元格将要组合的字段放到行中。手动组合先将行中只保留要组合的字段，其他字段放到页中生成透视表后，选择要组合的内容，右击组合。将页中的字段放到其应用有的位置Module 9：数据库管理：数据库管理向透视表中添加新的计算字段。Module 9：数据库管理：数据库管理向透视表中添加计算项（针对表中记录操作）：透视表中有组合字段的不可以添加计算项将计算项放到行首，计算时光标定位在行首。Module 9：数据库管理：数据库管理更改行中的汇总方式：双击行中除最后一个字段以外的字段名。如果双击内容，展开及折叠。如果要展开或折叠所有项：右击字段名group and show detailhide detail显示极端数据：双击行中需求中为主语的字段（为定语的字段要放到主语的前方，或页中）双击为主语的字段标题，advancedtop 10 autoshow Module 9：数据库管理：数据库管理透视表中百分比的显示：光标定位在data中要百分比显的字段中，右击field settings%of total如果透视表中行中的一个字段占另一个行中字段百分比时，这两个字段要分别放到行中及列中。%of row%of columnModule 9：数据库管理：数据库管理明细数据的产生：双击data中的值：多维（最多四维）数据透视表的制作：每张原始表格都要有交叉表的结构要划分出表格的维数。Module 9：数据库管理：数据库管理数据透视图的操作（F11）X轴（分类轴）：透视表中行中的内容。Y轴（数值轴）：透视表中data中的内容。图例：透视表中column中的内容。页：透视表中的页。Module 9：数据库管理：数据库管理分析出分类比较图表柱形图看比较：（3种，图表图表类型）堆积图：分析出分类比较趋势变化图表折线图看趋势变化：分析出份额比较图表饼形图看份额比较（适合数据量较少的表格）。制作双柱线柱图表双轴图：（大数据与小数据并存）双轴折线双轴柱线图Module 9：数据库管理：数据库管理透视表的更新：数值的修改：修改原始表格，回到透视表中右击refresh data数据的插入：在原表格数据的中间插入，回到透视表中右击refresh data数据的append:insertnamedefine将原数据表名子删除ctrl+shift+8全选原表数据，以相同的名子命名。回到透视表中右击refresh dataModule 9：数据库管理：数据库管理透视表格式的操作：文本字段名子的修改：直接输入数字格式的修改：右击field settingsnumber底纹边框的修改：透视表工具栏的第二个按钮。打印的设置：打印方向：文件页面设置调整打印内容：视图分页预览打印行及列的标题：文件页面设置工作表标题Module 10图表静态图表动态图表Module 11数据预测数据预测公式预测直线预测y=mx+b对数据预测y=(c*LN(x)-b幂函数据预测y=cxb指数函数预测y=cebx多项式预测y=(c2x2)+(c1x1)+bModule 11数据预测数据预测变量求解 Module 11数据预测数据预测方案管理器 方案是用于预测结果的一组数值可以在工作表中创建并保存多组不同的数值，并且可以在这些新方案之间任意切换，查看不同的方案结果Module 11数据预测数据预测创建单变量数据表 Module 11数据预测数据预测创建双变量数据表Module 11数据预测数据预测使用Solver“规划求解规划求解”可以通过改变其它单元格的数值，可以通过改变其它单元格的数值，使目标单元格的数值达到最大值或最小值使目标单元格的数值达到最大值或最小值视图方案的管理视图方案的管理Module 11数据预测数据预测使用窗体控件制作敏感度分析模型 Module 13:自定义自定义EXCEL及其他及其他批注（建立、清除、编辑）及修订创建自定义菜单并指定宏自定义工具栏、置入内置菜单、指定宏应用模板和保存模板工作簿的属性修改Module 13 使用和建立模板使用和建立模板使用通用模板与工作表模板创建保存与修改模板使用office 2003模板库Module 13 使用和建立模板使用和建立模板制作填空式模板去除工作表中的网格线隐藏工作表中的工作表标签隐藏工作表的行号及列标窗体的使用单选按钮的制作复选框的制作单元格的安全性设置Q QA AThanks!Thanks!