Excel经典培训教材.ppt

Microsoft Excel,Instructor:董瑞营 Mail: MSN:dry123_9,Module 1 Excel操作技巧,Ctrl+shift+8:选择活动单元格周围的相邻区域。 ctrl+shift+方向键：:选择活动单元格相邻方向上的区域 移动列：选择列ctrl+x,定位光标到目标位置，Insertcut cells 按shift+拖动边框 斜线表头：先输入表示列的信息，再输入表示行的信息，formatcellsalignment两个对齐均设为justify 选择warp textborder,加一对角线光标定位在第一个单词前，输入几个空格 长数字的输入：134134123412 datatext to column 分数的输入：0空格1/5 单元格中的内容换行：定位光标到要换行处alt+enter,Module 1 Excel操作技巧,公式填充：双击填充柄 逻辑表达式的输入： 进行特殊粘贴 EditPastePaste Special 粘贴数值 粘贴格式 粘贴转置 打印设置 页眉与页脚的设置:filepage setup 打印区域的设置:viewpage break preview Insertpage break 行标题的打印: filepage setupsheetprint titles,Module 2 管理工作簿和工作表,Alt+shift+组合 Alt+shift+取消组合 工作表的选择： 右击工作表导航栏 ctrl+pageup ctrl+pagedown 移动、复制各选择工作表 右击工作表标签,Move or Copy Worksheet 工作薄窗口的冻结与拆分 定位光标 Windowssplit or freeze pane,Module 2 管理工作簿和工作表,创建报表 选择某一区域中的空单元格 选择区域，Editgo tospecialblank 选择某一区域中的有内容的单元格 选择区域，Editgo tospecialConstant 行或列的隐藏 选择单元格，或行或列 FormatRowHide FormatColumnHide Editdelete 创建视图 视图视图管理器,Module 3 公式和格式,公式的输入： 单元格的引用： 相对引用：A1 在行中复制时，行不动，列在动，在列中复制时，列不动，行在动. 绝对引用：$A$1 f4不能动的要加$ 混合引用：$A1 , A$1,适合向两个方向上复制.分别进行讨论，只讨论默认在动的，不讨论默认不动的。 名称引用：定位功能(f5)，用作公式中的参数(f3),跨工作表操作。 方法：选择要命名的区域，单击名称框输入名字： 不能以数字命名 不能以地址命名 名字中间不能有空格 名字写完后要enter确认 删除：insertnamedefinedelete,Module 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个if round(number,num) =sumif(range, criteria,data_range) Range:条件所在的区域 Criteria:条件，表达式用” data_range：求和的区域。,Module 4： 函数,=countif(range,criteria) 判断是否重复： Datavalidationsettingscustom Toolsformula auditingshow formula auditingcircle invalid data,Module 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 hidden toolsprotectionprotect sheet 输入密码，取消select locked cells复制框 光标定位到原始数据表，formatsheethide toolsprotectionprotect workbookstructer加密。,Module 5： 工作表管理,只保护指定的区域： ctrl+a选择所有单元格 formatcellsprotection取消locked hidden 选择要保护的单元格区域 formatcellsprotection选中locked hidden toolsprotectionprotect sheet加密，取消第一个复选框。 文件级的安全： toolsoptionssecuritypassword to open and password to modify,Module 5： 工作表管理,链接工作表 /工作簿 超级链接: 公式链接: 粘贴链接: 矩阵链接: 在要使用数据的表格中选择要使用的区域 输入=,找到有数据的表格，选择数据区域 Ctrl+shift+enter,Module 6：数据库入门,数据前提： 列中数据为字段，每个字段只有一个字段名 行中数据为记录 表格中不能合并单元格 表格中不能有空白行及空白列，可以有空白单元格。,Module 6：数据库入门,排序 排行： 单依据：定位光标到依据列，单击按钮 多依据：datasort 排列： Ctrl+x,insertcut cells Shift+拖动列的边框 自定义排序 排序使用:选择数据区域，datasortoptions自定义序列 自定义不存在序列：toolsoptions自定义序列,导入自定义的内容。,Module 6：数据库入门,筛选：(按条件显示记录) 自动筛选：datafilterauto filter 指定内容筛选 取极端数据top 10：（只对数值性字段有效） 自定义：（最多可以定义两个条件，and or） *:表示多个字符 ？：只表示一个字符 高级筛选： 创建条件区域： 将条件中用到的列的标题复制到原表格以外的某处 在复制出的标题下方写条件。 如果条件写在同一行中为and 如果条件写在同一列中为 or Datafilteradvanced filter 去除重复记录：,去除重复数据：,光标定位到空的单元格中 Datafilteradvanced filter Action: copy to another location List range:选择要去除重复的数据 Criteria range:空 Copy to:指定一个空的单元格,Module 7：数据库函数,Subtotal(founction_num,ref) 与筛选一起使用. 1:Average 2:count 3:counta 4:max 5:min 9.:sum 数据库函数： =dsum(database,field,criteria) =daverage() =dmax() =dmin() =dcount() =dcounta(),Module 8：宏,宏的制作 VBA CODE 录制 宏的执行 菜单 快捷键 制作宏按钮 调试宏 编辑宏,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:=False End Sub,Sub Macro1() Range(“C10“).Select Range(“A5:J82“).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _ Range(“D1“).currentregion, Unique:=False End Sub,修改后：,Module 8：宏,宏出错信息的处理： On error resume next application.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 column,Module 9：数据库管理,明细数据的产生：双击data中的值： 多维（最多四维）数据透视表的制作： 每张原始表格都要有交叉表的结构 要划分出表格的维数。,Module 9：数据库管理,数据透视图的操作（F11） X轴（分类轴）：透视表中行中的内容。 Y轴（数值轴）：透视表中data中的内容。 图例：透视表中column中的内容。 页：透视表中的页。,Module 9：数据库管理,分析出分类比较图表 柱形图看比较：（3种，图表图表类型） 堆积图： 分析出分类比较趋势变化图表 折线图看趋势变化： 分析出份额比较图表 饼形图看份额比较（适合数据量较少的表格）。 制作双柱线柱图表 双轴图：（大数据与小数据并存） 双轴折线 双轴柱线图,Module 9：数据库管理,透视表的更新： 数值的修改：修改原始表格，回到透视表中右击refresh data 数据的插入：在原表格数据的中间插入，回到透视表中右击refresh data 数据的append: insertnamedefine将原数据表名子删除 ctrl+shift+8全选原表