Excel_德勤财务

22020/1/23ExcelforaccountingworkITSDepartment©2005DeloitteToucheTohmatsu.培训日程和目标Chapter1：Excel常用技巧引用关系调试函数保护工作表使用名称筛选条件格式及有效性验证透视图处理大型数据表Chapter2：函数基础常用函数查询函数逻辑函数统计函数数学函数©2005DeloitteToucheTohmatsu.培训日程和目标通过本次课程，您将:1.掌握常用函数的表达式，参数，功能使用；2.了解常用功能，能够对工作表进行相应处理；3.能综合应用函数来解决日常工作中的问题。HintsandTips常用技巧.©2005DeloitteToucheTohmatsu.PrivateandConfidential6Excel常用技巧（Tips）:显示公式与单元格之间的关系可显示公式的引用单元格和从属单元格。•在“Tools”菜单上，单击“Options”，再单击“View”选项卡。•选中“Objects”之下的“Showall”或“Showplaceholder”。•在“Tools”菜单上，指向“FormulaAuditing”，再单击“ShowFormulaAuditingBar”。•请执行下列操作之一。–追踪为公式提供数据的单元格–跟踪引用特殊单元格•如果要取消工作表上的所有追踪箭头，请单击“FormulaAuditing”工具栏上的“Removeallarrows”注释红色箭头显示出现错误的单元格。©2005DeloitteToucheTohmatsu.PrivateandConfidential7Excel常用技巧（Tips）:按步骤对套嵌公式求值1.选择要求值的单元格。一次只能计算一个单元格的值。2.在“Tools”菜单上，指向“FormulaAuditing”，再单击“EvaluateFormula”。3.单击“Evaluate”以验证下划线引用的值。计算结果将以斜体显示。如果公式的下划线部分是对其他公式的引用，请单击“Stepin”以在“Evaluate”框中显示其他公式。单击“Stepout”以返回以前的单元格或公式。4.继续操作，直到公式的每一部分都已求值完毕。5.若要再次查看计算过程，请单击“Restart”。若要结束求值，请单击“Close”注释当引用第二次出现在公式中，或者公式引用了另外一个工作簿中的单元格时，“Stepin”按钮不可用。©2005DeloitteToucheTohmatsu.PrivateandConfidential8Excel常用技巧（Tips）:显示或隐藏公式在工作表上显示的公式和其值之间切换•按Ctrl+~（重音符）。禁止公式在编辑栏上显示•选定要隐藏的公式所在的单元格区域。还可以根据需要选定非相邻区域或整个工作表。•在“Format”菜单上，单击“Cells”，再单击“Protection”选项卡。•选中“Hidden”复选框。•单击“OK”。•在“Tools”菜单上，指向“Protection”，再单击“ProtectSheet”。•请确保选中了“Protectworksheetandcontentsoflockedcells”复选框。©2005DeloitteToucheTohmatsu.PrivateandConfidential9Excel常用技巧（Tips）:使用名称工作创建名称：InsertNameDefine(Ctrl+F3)好处：–直观–易记–快速移动（F5）–“粘贴”到公式（InsertNamePaste）Refersto–引用或函数注意：–名称不能包含空格；–首字母必须是字母或下划线；–除了下划线和点号不能使用其他符号；–名称不区分大小写©2005DeloitteToucheTohmatsu.PrivateandConfidential10Excel常用技巧（Tips）:条件格式及有效性验证条件格式：FormatConditionalFormat•有助于辨别错误单元格输入项或特殊类型的单元格有效性:DataValidationList的使用编辑弹出错误提示©2005DeloitteToucheTohmatsu.PrivateandConfidential11Excel常用技巧（Tips）:数据录入技巧•如果要在一个范围内的所有单元格中录入相同的数据，使用Ctrl+Enter组合键；复制到选中范围，使用F2Ctrl+Enter.•选择区域，同列复制Ctrl+D，同行复制Ctrl+R•增值填充，Ctrl+手柄(数字)；Alt+手柄(字母)•自定义填充，Toolsoptionscustomlist•Alt+Enter换行•输入分数先输入0+Space•当前日期Ctrl+“；”当前时间Ctrl+Shift+“；”•使用公式作为计算器：保存公式结果而非公式本身，按F2编辑，按F9，再按Enter.也可以进行部分转换。©2005DeloitteToucheTohmatsu.PrivateandConfidential12Excel常用技巧（Tips）:大小写转换•函数方法:Upper()Lower()•Word方法:拷贝到Word中，使用Shift+F3•中文大写:FormatCell-SpecialChinese(PRC)中文大写数字©2005DeloitteToucheTohmatsu.PrivateandConfidential13Excel常用技巧（Tips）:数据分列整齐排列•DataTexttoColumns•Fixedwidth•Breakline•Format非整齐排列•函数LEFT()RIGHT()MID()©2005DeloitteToucheTohmatsu.PrivateandConfidential14Excel常用技巧（Tips）:筛选自动筛选•DataFilterAutoFilter•=Subtotal()•ViewToolbarsCustomizeEditSelectvisiblecell高级筛选•DataFilterAdvancedFilter分类汇总•DataSubtotal©2005DeloitteToucheTohmatsu.PrivateandConfidential15Excel常用技巧（Tips）:数据透视表•确定希望分析的数据，并确定如何组织它。•DataPivotTable–从“PivotTableFieldList”拖动项目。–将项目拖至数据透视表布局区域。创建数据透视表就是要将信息移来移去，看看各信息如何组合。©2005DeloitteToucheTohmatsu.PrivateandConfidential16Excel常用技巧（Tips）:大型数据表Excel工作表可以有256列65536行大型数据表的操作：•SplitandFreeze•FindandReplace•WatchWindow•快捷键：–Ctrl+PgDn/PgUp下/上一张工作表–Ctrl+Home/End移动到工作表的开头/最后一个单元格–PgDn/PgUp下/上一屏–Alt+PgDn/PgUp左/右一屏FunctionFundamental函数基础.©2005DeloitteToucheTohmatsu.PrivateandConfidential18函数基础•创建公式（以“＝”开始，可以包括：运算符、单元格引用、值或常量以及工作表函数。）•公式中的运算符–例如:^（求幂）、&（文本连接运算符）、（不等于）等•公式实例：=150*.01=A1+A2=Income-Expenses=SUM(A1:A2)=A1=C12=(B2-B3)*B4一个公式最多可以容纳1024个字符©2005DeloitteToucheTohmatsu.PrivateandConfidential19函数基础•绝对引用/相对引用（演示）引用（说明）更改为$A$1（绝对列和绝对行）$A$1A$1（相对列和绝对行）C$1$A1（绝对列和相对行）$A3A1（相对列和相对行）C3•函数使用常见的错误（如下表）©2005DeloitteToucheTohmatsu.PrivateandConfidential20函数基础•函数使用常见的错误错误值原因说明####该列宽度不够，或者包含一个无效时间或日期#DIV/0!该公式使用了0作为除数，或者公式中使用了一个空单元格#N/A公式中引用的数据对函数或公式不可用#NAME?公式中使用了Excel不能辨认的文本或名称#NULL!公式中使用了一种不允许出现相交却交叉了的两个区域#NUM!使用了无效的数字值#REF!公式引用了一个无效的单元格#VALUE!函数中使用的变量或参数类型错误©2005DeloitteToucheTohmatsu.PrivateandConfidential21如何使用函数•Function插入函数•Category函数分类•Usage使用说明•Parameter参数•Help帮助©2005DeloitteToucheTohmatsu.LookupFunctions查询函数.©2005DeloitteToucheTohmatsu.PrivateandConfidential23LookupFunctions查询函数HLOOKUPVLOOKUPLOOKUP©2005DeloitteToucheTohmatsu.PrivateandConfidential24LookupFunctions查询函数根据数据表，查询相关信息（模糊查找，精确查找）。HLOOKUPVLOOKUPLOOKUP向量型数组型*QandA问题与解答LogicalFunctions逻辑函数.©2005DeloitteToucheTohmatsu.PrivateandConfidential26LogicalFunctions逻辑函数ANDORIFNOTTRUEFALSE©2005DeloitteToucheTohmatsu.PrivateandConfidential27LogicalFunctions逻辑函数六顶思考帽Table5.1:•对于已知数字作判断，大于60的给予“PASS”，否则为“FAIL”•逻辑函数在表达式中的运用（演示）AND/NOT/OR/TRUE/FALSE/IF©2005DeloitteToucheTohmatsu.PrivateandConfidential28StatisticalFunctions统计函数.©2005DeloitteToucheTohmatsu.PrivateandConfidential29StatisticalFunctions统计函数COUNTAVERAGECOUNTIFMODEFREQUANCY©2005DeloitteToucheTohmatsu.PrivateandConfidential30StatisticalFunctions统计函数六顶思考帽•返回在某一数组或数据区域中的众数。•MODE(number1，number2，...)MODE•返回符合条件的单元格数。COUNTIF计算所有参数的算术平均值。AVERAGE(number1，number2，...)AVERAGE•以一列垂直数组返回某个区域中数据的频率分布。它可以计算出在给定的值域和接收区间内，每个区间包含的数据个数。FREQUANCYMathFunctions数学函数.©2005DeloitteToucheTohmatsu.PrivateandConfidential32MathFunctions数学函数ABSPRODUCTROUNDUPROUNDDOWNINTMODROUNDSUMSUMPRODUCTSUMIFTRUNC©2005DeloitteToucheTohmatsu.PrivateandConfidential33MathFunctions数学函数六顶思考