财务excel操作技巧培训课程.ppt

《财务excel操作技巧培训课程.ppt》由会员分享，可在线阅读，更多相关《财务excel操作技巧培训课程.ppt（55页珍藏版）》请在金锄头文库上搜索。

1、,财务EXCEL操作技巧培训,2,目录,EXCEL基本用途,常见数据计算,EXCEL快捷技巧,1,4,EXCEL输出,3,第一部分 EXCEL 基本用途,PART 01,4,创建统计表格,1.1,表至表 IT提数： .unl格式txt格式,外部获取数据,5,创建统计表格,1.1,IT提数： .unl格式直接拖入EXCEL,处理步骤：,“完成”,6,分析与筛选数据-数据透视表创建,1.2,数据透视表:一种交互式的表，可以进行某些计算，如求和 与计数等。,7,分析与筛选数据-数据透视表布局,1.2,说明,8,分析与筛选数据-数据透视表,1.2,注 意 事 项,9,分析与筛选数据-数据透视表布局,1

2、.2,注意事项,1. 不能出现同一字段既是筛选字段又是行或列字段 2. 同一数值字段可以拖放多个到数值字段区域 3. 每次拖放字段到数据透视表中时，EXCEL都会重新更新（可推迟 更新）,10,分析与筛选数据-数据重复项,1.3,数据重复项,删 除,查 找,删除重复项：数据删除重复项,查找重复项：数据透视表（计数项大于1的项为重复项）,11,分析与筛选数据-数据重复项,1.3,数据重复项,删 除,查 找,查找重复项：开始条件格式重复值,12,分析与筛选数据-数据排序,1.3,数据排序,清单排序,高级排序,数据清单排序 如果按照单列的内容进行排序，可直接“数据”选项卡“ ” ”进行排序,高级排序

3、 按照多个条件对清单进行排序,13,分析与筛选数据-数据筛选,1.3,数据筛选,自动筛选,自定义筛选,高级筛选,自动筛选 字段名称会变成一个下拉框,自定义筛选 当自带的筛选条件无法满足需要时，可根据需要自定义筛选条件,14,分析与筛选数据-数据筛选,1.3,数据筛选,自动筛选,自定义筛选,高级筛选,高级筛选 使用高级筛选功能，必须先建立一个条件区域，用来指定筛选所满足的条件。条件区域的第一行是所有作为筛选条件的字段名，这些字段名与数据清单中的字段名必须完全一样。,15,分析与筛选数据-分类汇总,1.3,分类汇总,在数据清单中自动计算分类汇总及总计值。用户只需指定需要进行分类汇总的数据项、待汇总

4、的数值和用于计算的函数(例如“求和”函数)即可。汇总单元格排序选项卡“数据”“分类汇总”,16,分析与筛选数据-分类汇总,1.3,分类汇总,注意事项： 汇总前排序 在进行分类汇总前，一定要把需要按其汇总的字段进行排序，否则无法实现汇总。 不足之处 如有多列行字段头，分类汇总之后，则只保留汇总 字段，其他字段无法体现，相比之下，当需求字段 多时，使用数据透视表。,17,分析与筛选数据-数据分级显示,1.3,分级显示,有时，我们既想看到汇总数据，又想看到每一个汇总数据下的明细数据，那么势必导致在一张表里数据行较多。 但电脑屏幕有限，不停的上下翻页非常的不便利，也不直观，有没有其他好办法呢？,分级显

5、示,18,分析与筛选数据-数据分级显示,1.3,操作方法：数据组合选中组数进行分级显示,19,分析与筛选数据-数据有效性,1.3,数据有效性,数据有效性： 防止重复信息防止人工输入错误 快速揪出无效数据 自定义序列,20,分析与筛选数据-数据有效性,1.3,数据有效性,选定目标单元格数据数据验证,在数据有效性窗口中选择-设置允许里面选择“序列”选项输入锁定来源（注意逗号要为全英文半角状态下显示）或直接选择列,21,分析与筛选数据-数据分列,1.3,数据分列,一列分多列 对一列需要根据共同字符（含空格）分列的数据进行分列，也可以强制指定字符宽度进行分列。,去除乱字符 对外部引入的数据进行处理，去

6、除TAB符、单分号符、占位符、部分位于首尾的空格符。,22,分析与筛选数据-数据分列,1.3,分隔符号 分列 选项卡“数据” 分列,点击”完成”,正常情况下，按图二界面，其他选项保持默认，然后点击“完成”结束数据分列。,23,分析与筛选数据-数据分列,1.3,固定宽度 分列 选项卡“数据” 分列,点击”完成”,可以通过鼠标设置一条或多条分隔线，将字条按任意方式进行分隔处理 正常情况下，按图二界面，其他选项保持默认，然后点击“完成”结束数据分列。,24,分析与筛选数据-快捷组合键,1.4,快捷组合键,快捷组合键： Ctrl+C 复制选定的单元格 Ctrl+X 剪切选定的单元格 Alt+; 选定指

7、定单元格 Ctrl+A 选择整个工作表 Ctrl+V 粘贴选定单元格 Ctrl+D 使用“向下填充”命令将选定范围内最顶层单元格的内容和格式复制到下面的单元格 Ctrl+R 使用“向右填充”命令 Ctrl+F 数据查找 Ctrl+H 数据替换 Ctrl+Z 撤销,25,第二部分 常用数据计算,PART 02,26,公式引用-参考值“A1”,绝对引用,相对引用,混合引用,被引用的单元格随位置变化而变化 相对地址：B1(复制时，相对位置不变）,被引用单元格不随位置改变 绝对地址：$A$1(复制时，始终不变）,相对地址：$A1、A$1(上述相对，绝对混合）,27,公式中的错误,2.2,28,常用函数

8、 求和函数,2.3,SUM,SUMIF/S,SUM函数：无条件求和 =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,29,常用函数 求和函数,2.3,案例,SUMIF函数:求各支公司保费合计数 SUMIF

9、S函数:求各支公司不同销售渠道的保费合计数,输入公式 =SUMIF(A2:A12,A2,C2:C12) 核算单位 目标单位 求和范围 2. 输入公式 =SUMIFS(C2:C12,B2:B12,B2,E2:E12,E2) 求和范围 条件区域1 条件1 条件区域2 条件2,30,常用函数 数学函数,2.3,SUM,MAX,MIN,ABS,求N个数的最大值,求N个数的最小值,求某数的绝对值,=MAX(参数1，参数2),=MIN(参数1，参数2),=ABS(参数1),31,常用函数 计数函数,2.3,SUMIF/,COUNTIF函数：区域内满足给定条件的单元格数目=COUNTIF（range，cri

10、teria) 区域 条件,COUNTIF,COUNTA,COUNTA函数：返回单元格内非空值的个数=COUNTA（参数1，参数2，),32,常用函数 计数函数,2.3,SUMIF/,案例,COUNTIF函数：计算保费金额大于500的保单数,输入公式 =COUNTIF(C2:C12,“500“) 区域 条件,33,常用函数 条件函数和逻辑函数,2.3,SUMIF/,COUNTIF,COUNTA,注意：这三个函数常常联合使用,34,常用函数 条件函数和逻辑函数,2.3,案例,IF函数:1、判断A列与B列核算单位是否一致 2、算出奖励政策（保费收入大于500，奖励政策为保费的 8%，小于500为3%

11、,1、输入公式 =if(A1=B1,0,1) 语义：如果单元格A1等于B1，显示为真（0），否则为假（1） 2、输入公式 =IF(D2500,D2*$D$14,D2*$D$15) 语义：如果总保费大于500，则总保费乘以8%，否则总保费乘以3%,35,常用函数 日期函数,2.3,SUMIF/,DATEDIF/VALUE函数：计算两个日期的相隔年数、月数、天数 应收保费账龄计算中经常会引用 例如：计算应收保费逾期天数 =DATEDIF(D2,G15,“d“) 或 =VALUE(G15-D2),DATEDIF /VALUE,日期显示,COUNTA函数：返回单元格内非空值的个数=COUNTA（参数1

12、，参数2，),36,常用函数 查找匹配函数,2.3,VLOOKUP,VLOOKUP函数：在表格或数值组的首列查找制定的数值，并由此返回表格或数组当前列制定行处的数值。 函数语法规则如下： =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),37,常用函数 条件函数和逻辑函数,2.3,案例：VLOOKUP函数:根据表2匹配出表1的被保险人名称,输入公式： =VLOOKUP(A2,F1:H12,3,0） 两张表共同点：保单号A列与F列 A2 目标查找值 F1：H12 查找区域 3：需要匹配的项为该区域第三列 0：模糊查找,38

13、,常用函数 文本函数,2.3,解决方法： 分列：指定列分列完成 CLEAN函数：删除文本中不能打印的字符 TEXT函数：将数值转换为按指定数字格式表示的文本 TRIM函数：取消字符串两侧的空格示,文本函数,问题：两个单元格完全一样，但却无法匹配？ 表象蒙蔽数据清理,两个看上去完全一样的单元格，其实往往包含了不同的字串符！,39,常用函数 四舍五入、整数、文本函数,2.3,MIN,ABS,求某数的绝对值,40,常用函数 文本函数,2.3,41,常用函数 文本函数,2.3,目的：根据客户信息提取保单号（客户信息为A列，保单号为B列） 方法：单击B2单元格，在编辑栏输入公式： =RIGHT(A2,L

14、EN(A2)-SEARCH(“P“,A2,6)+1) RIGHT函数：表示从右边取数 LEN函数：整个文本字串符个数 SEARCH函数：返回指定符号字串符的位置,42,常用函数 排序函数,2.3,SUMIF/,Rank,LARGE,RANK 排名函数 语义规则： =RANK(number,ref,order)其中，number为要排名的单元格或数字，ref为区域范围，order为排名的顺序，即降序（用0表示）或升序（用1表示）。,LARGE：返回数据集中的第K个最大值。 语义规则： =LARGE(array,k) 其中，array 为需要找到第 k 个最大值的 数组或数字型数据区域，k 为返回

15、的数据在 数组或数据区域里的位置(从大到小)。 SMALL函数同理,43,常用函数 条件函数和逻辑函数,2.3,案例：根据表一信息，给各分公司保费收入进行排名,结果,方法： 创建数据透视表，求出各个分公司保费收入 引入RANK函数: =RANK($L$2:$L$9,$K$2:$L$9,0) 需要排序的值 排序引用的范围 升序或降序,44,第三部分 EXCEL快捷技巧,PART 04,45,EXCEL快捷技巧,3,快速转换不同的工作表：”CRTL(+SHIFT)+PgDn/PgUp” 快速移动：按下Ctrl+相应的方向键或方向键组合。例如按下“Crtl+”即可到达选中最下方的单元格 快速选取：按下 Ctrl+Shift+ 相应的方向键或方向键组合，再复制即可,自动换行：如果出现多个保单号，需拆分为不同的列采用 行列转置：选择需要转置的数据（包括所有行或列标签）,按下Ctrl+C，选择性粘贴中的转置即可,46,EXCEL快捷技巧,3,快速恢复E+的数字格式显示：数字长度大于11为会以科学计数显示，分列选择文本格式显示即可。 快速求行与列合计数：Ctrl+G定位条件空值“ALT+=“ 多单元格批量输入相同的内容：将想要输入内容的单元格一起选中，然后在编辑栏中输入想要