财务EXCEL操作技巧培训课程.pptx

财务EXCEL操作技巧培训目录EXCEL基本用途常见数据计算EXCEL快捷技巧124EXCEL输出 第一部分EXCEL 基本用途PART 013创建统计表格1.14表至表 IT提数：.unl格式txt格式 外部获取数据创建统计表格1.15IT提数：.unl格式直接拖入EXCEL 处理步骤：“完成”分析与筛选数据-数据透视表创建1.260505数据透视表:一种交互式的表，可以进行某些计算，如求和 与计数等。分析与筛选数据-数据透视表布局1.270505 说明分析与筛选数据-数据透视表1.280505注 意 事 项分析与筛选数据-数据透视表布局1.290505 注意事项1.不能出现同一字段既是筛选字段又是行或列字段2.同一数值字段可以拖放多个到数值字段区域3.每次拖放字段到数据透视表中时，EXCEL都会重新更新（可推迟 更新）分析与筛选数据-数据重复项1.310数据重复项 删 除查 找删除重复项：数据删除重复项查找重复项：数据透视表（计数项大于1的项为重复项）分析与筛选数据-数据重复项1.311数据重复项 删 除查 找查找重复项：开始条件格式重复值分析与筛选数据-数据排序1.312数据排序清单排序高级排序数据清单排序 如果按照单列的内容进行排序，可直接“数据”选项卡“”进行排序高级排序 按照多个条件对清单进行排序分析与筛选数据-数据筛选1.313数据筛选自动筛选自定义筛选高级筛选自动筛选 字段名称会变成一个下拉框自定义筛选 当自带的筛选条件无法满足需要时，可根据需要自定义筛选条件 分析与筛选数据-数据筛选1.314数据筛选自动筛选自定义筛选高级筛选高级筛选 使用高级筛选功能，必须先建立一个条件区域，用来指定筛选所满足的条件。条件区域的第一行是所有作为筛选条件的字段名，这些字段名与数据清单中的字段名必须完全一样。分析与筛选数据-分类汇总1.315分类汇总 在数据清单中自动计算分类汇总及总计值。用户只需指定需要进行分类汇总的数据项、待汇总的数值和用于计算的函数(例如“求和”函数)即可。汇总单元格排序选项卡“数据”“分类汇总”分析与筛选数据-分类汇总1.316分类汇总注意事项：汇总前排序 在进行分类汇总前，一定要把需要按其汇总的字段进行排序，否则无法实现汇总。不足之处 如有多列行字段头，分类汇总之后，则只保留汇总 字段，其他字段无法体现，相比之下，当需求字段 多时，使用数据透视表。分析与筛选数据-数据分级显示1.317分级显示 有时，我们既想看到汇总数据，又想看到每一个汇有时，我们既想看到汇总数据，又想看到每一个汇总数据下的明细数据，那么势必导致在一张表里数据总数据下的明细数据，那么势必导致在一张表里数据行较多。行较多。但电脑屏幕有限，不停的上下翻页非常的不便利，但电脑屏幕有限，不停的上下翻页非常的不便利，也不直观，有没有其他好办法呢？也不直观，有没有其他好办法呢？分级显示分析与筛选数据-数据分级显示1.318操作方法：数据组合选中组数进行分级显示分析与筛选数据-数据有效性1.319数据有效性数据有效性：防止重复信息防止人工输入错误快速揪出无效数据自定义序列分析与筛选数据-数据有效性1.320数据有效性选定目标单元格数据数据验证在数据有效性窗口中选择-设置允许里面选择“序列”选项输入锁定来源（注意逗号要为全英文半角状态下显示）或直接选择列分析与筛选数据-数据分列1.321数据分列 一列分多列 对一列需要根据共同字符（含空格）分列的数据进行分列，也可以强制指定字符宽度进行分列。去除乱字符 对外部引入的数据进行处理，去除TAB符、单分号符、占位符、部分位于首尾的空格符。分析与筛选数据-数据分列1.322分隔符号 分列 选项卡“数据”分列点 击”完 成”正常情况下，按图二界面，其他选项保持默认，然后点击正常情况下，按图二界面，其他选项保持默认，然后点击“完成完成”结束数据分列。结束数据分列。分析与筛选数据-数据分列1.323固定宽度 分列 选项卡“数据”分列点 击”完 成”可以通过鼠标设置一条或多条分隔线，将字条按任意方式进行分隔处理可以通过鼠标设置一条或多条分隔线，将字条按任意方式进行分隔处理正常情况下，按图二界面，其他选项保持默认，然后点击正常情况下，按图二界面，其他选项保持默认，然后点击“完成完成”结束数据分列。结束数据分列。分析与筛选数据-快捷组合键1.424快捷组合键快捷组合键：Ctrl+C 复制选定的单元格 Ctrl+X 剪切选定的单元格Alt+;选定指定单元格Ctrl+A 选择整个工作表Ctrl+V 粘贴选定单元格Ctrl+D 使用“向下填充”命令将选定范围内最顶层单元格的内容和格式复制到下面的单元格Ctrl+R 使用“向右填充”命令Ctrl+F 数据查找Ctrl+H 数据替换Ctrl+Z 撤销 第二部分常用数据计算PART 02252.1公式引用-参考值“A1”26绝对引用相对引用混合引用231被引用的单元格随位置变化而变化相对地址：B1(复制时，相对位置不变）被引用单元格不随位置改变绝对地址：$A$1(复制时，始终不变）相对地址：$A1、A$1(上述相对，绝对混合）公式中的错误2.227常用函数 求和函数2.328SUMSUMIF/SSUM函数：无条件求和=SUM（参数1，参数2，.参数N)Tips:求和快捷键快速求和“ALT+=”SUMIF函数：条件求和=SUMIF（range，criteria，sum_range)条件区域 条件 条件求和范围 SUMIFS函数：多条件求和=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,.)条件求和范围 条件区域1 条件1 条件区域2 条件2常用函数 求和函数2.329案例1.SUMIF函数:求各支公司保费合计数2.SUMIFS函数:求各支公司不同销售渠道的保费合计数1.输入公式 =SUMIF(A2:A12,A2,C2:C12)核算单位 目标单位 求和范围2.输入公式 =SUMIFS(C2:C12,B2:B12,B2,E2:E12,E2)求和范围 条件区域1 条件1 条件区域2 条件2常用函数 数学函数2.330SUMAVERGAE求N N个数的平均值=AVERGAE(参 数 1，参 数2.)MAX MIN ABS求N N个数的最大值求N N个数的最小值求某数的绝对值 =MAX(参数1，参数2.)=MIN(参数1，参数2.)=ABS(参数1)常用函数 计数函数2.331SUMIF/COUNTIF函数：区域内满足给定条件的单元格数目=COUNTIF（range，criteria)区域 条件COUNTIF COUNTACOUNTA函数：返回单元格内非空值的个数=COUNTA（参数1，参数2，.)常用函数 计数函数2.332SUMIF/案例COUNTIF函数：计算保费金额大于500的保单数输入公式=COUNTIF(C2:C12,500)区域 条件常用函数 条件函数和逻辑函数2.333SUMIF/COUNTIF COUNTAIF函数：条件函数，它根据参数条件的真假返回不同的结果值。=IF（条件表达式，TRUE,FALSE)AND函数：“与”，当所有条件满足，AND函数返回TRUE，有一个条件不满足则为FALSE。=AND（条件1，条件2，.,条件N)OR函数：“或”，只要有一个条件满足，OR函数返回TRUE，所有条件不满足则为FALSE。=OR（条件1，条件2，.,条件N)注意：这三个函数常常联合使用常用函数 条件函数和逻辑函数2.334 案例IF函数:1、判断A列与B列核算单位是否一致 2、算出奖励政策（保费收入大于500，奖励政策为保费的 8%，小于500为3%1、输入公式=if(A1=B1,0,1)语义：如果单元格A1等于B1，显示为真（0），否则为假（1）2、输入公式=IF(D2500,D2*$D$14,D2*$D$15)语义：如果总保费大于500，则总保费乘以8%，否则总保费乘以3%常用函数 日期函数2.335SUMIF/DATEDIF/VALUE函数：计算两个日期的相隔年数、月数、天数 应收保费账龄计算中经常会引用 例如：计算应收保费逾期天数=DATEDIF(D2,G15,d)或=VALUE(G15-D2)DATEDIF/VALUE日期显示COUNTA函数：返回单元格内非空值的个数=COUNTA（参数1，参数2，.)常用函数 查找匹配函数2.336VLOOKUPVLOOKUP函数：在表格或数值组的首列查找制定的数值，并由此返回表格或数组当前列制定行处的数值。函数语法规则如下：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)常用函数 条件函数和逻辑函数2.337案例：VLOOKUP函数:根据表2匹配出表1的被保险人名称输入公式：=VLOOKUP(A2,F1:H12,3,0）两张表共同点：保单号A列与F列A2 目标查找值F1：H12 查找区域3：需要匹配的项为该区域第三列0：模糊查找常用函数 文本函数2.338解决方法：分列：指定列分列完成CLEAN函数：删除文本中不能打印的字符TEXT函数：将数值转换为按指定数字格式表示的文本TRIM函数：取消字符串两侧的空格示文本函数问题：两个单元格完全一样，但却无法匹配？表象蒙蔽数据清理两个看上去完全一样的单元格，其实往往包含了不同的字串符！常用函数 四舍五入、整数、文本函数2.339 MIN ABS求某数的绝对值ROUND函数:返回某个数字制定返回位数后的数字=Round(参数，2）INT函数函数:取整数LEFT函数、函数、RIGHT函数函数:获取字串符指定的左边或右边个数的文本=LEFT(TEXT,N)截取保单号机构代码截取保单号机构代码使用使用MID函数函数：获取字串符中间指定个数的文本=MID(text,start_num,num_chars)常用函数 文本函数2.340SEARCH函数:用来返回指定的字符串在原始字符串中首次出现的位置,从左到右查找，忽略英文字母的大小写。=SEARCH(find_text,within_text,start_num)find_text：要查找的文本字符串within_text：要在哪一个字符串查找start_num：从within_text的第几个 字符开始查找LEN函数：反映文本字串符个数 这两个函数常常混合使用！常用函数 文本函数2.341目的：根据客户信息提取保单号（客户信息为A列，保单号为B列）方法：单击B2单元格，在编辑栏输入公式：=RIGHT(A2,LEN(A2)-SEARCH(P,A2,6)+1)RIGHT函数：表示从右边取数LEN函数：整个文本字串符个数SEARCH函数：返回指定符号字串符的位置常用函数 排序函数2.342SUMIF/Rank LARGERANK 排名函数语义规则：=RANK(number,ref,order)其中，number为要排名的单元格或数字，ref为区域范围，order为排名的顺序，即降序（用0表示）或升序（用1表示）。LARGE：返回数据集中的第K个最大值。语义规则：=LARGE(array,k)其中，array 为需要找到第 k 个最大值的 数组或数字型数据区域，k 为返回的数据在 数组或数据区域里的位置(从大到小)。SMALL函数同理常用函数 条件函数和逻辑函数2.343案例：根据表一信息，给各分公司保费收入进行排名结果方法：创建数据透视表，求出各个分公司保费收入引入RANK函数:=RANK($L$2:$L$9,$K$2:$L$9,0)需要排序的值 排序引用的范围 升序或降序 第三部分EXCEL快捷技巧PART 0444EXCEL快捷技巧3快速转换不同的工作表：”CRTL(+SHIFT)+PgDn/PgUp”快速移动：按下Ctrl+相应的方向键或方向键组合。例如按下“Crtl+”即可到达选中最下方的单元格快速选取：按下 Ctrl+Shift+相应的方向键或方向键组合，再复制即可自动换行：如果出现多个保单号，需拆分为不同的列采用行列转置：选择需要转置的数据（包括所有行或列标签）,按下Ctrl+C，选择性粘贴中的转置即可45EXCEL快捷技巧3快速恢复E+的数字格式显示：数字长度大于11为会以科学计数显示，分列选择文本格式显示即可。快速求行与列合计数：Ctrl+G定位条件空值“ALT+=“多单元格批量输入相同的内容：将想要输入内容的单元格一起选中，然后在编辑栏中输入想要的内容，最后按下”Ctrl+Enter”（编辑栏不显示选项高级显示显示编辑栏）对比同行数据是否相同：Ctrl+G定位条件行内容差异单元格填充颜色快速删除错误值：选择需要定位的区域，按下快捷键 Ctrl+G,打开“定位”对话框选择“定位条件”中的“公式”“错误”，成功定位后批量清除46EXCEL快捷技巧3利用选择性粘贴快速加减乘除47常用常忽视少用 第四部分EXCEL输出PART 0448EXCEL表格保护449 有时，要求分公司、支公司、业务员、出单员等报送一定的表格，为了简化，表格全部设好，只需要相关人员往指定的空格录入即可。可是表格发下去，经常被改的面目全非。公式、格式、汇总数据都不对，怎么办？保护工作表只允许选定指定区域EXCEL表格保护450 步骤：确保单元格锁定状态：选中全表，点击“设置单元格格式”右键“保护”选”锁定“选项卡中点”审阅“，选择”保护工作表“选定要锁定的列或行（输入密码）EXCEL表格保护451步骤：结果显示说明：说明：想锁定哪行或哪列，只需在设置单元格的时候锁定即可，其余行列取消锁想锁定哪行或哪列，只需在设置单元格的时候锁定即可，其余行列取消锁定。结果为锁定行列被锁定，其他行列可填写。定。结果为锁定行列被锁定，其他行列可填写。EXCEL表格打印452如何把多数据放在一张表上？视图分页预览鼠标指向垂直分页符当其变为双向箭头时拖至打印最右一列EXCEL表格打印453每一页打印行标题或列标题问题咨询问题咨询问题咨询问题咨询 EMAIL: TEL :0791-86631202谢 谢！