财务EXCEL操作技巧培训0227（PPT55页)

财务EXCEL操作技巧培训目录EXCEL基本用途常见数据计算EXCEL快捷技巧124EXCEL输出第一部分EXCEL基本用途PART013创建统计表格1.14表至表IT提数：.unl格式→txt格式外部获取数据创建统计表格1.15IT提数：.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撤销第二部分常用数据计算PART02252.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个数的平均值=AVERGAE(参数1，参数2..)MAXMINABS求N个数的最大值求N个数的最小值求某数的绝对值=MAX(参数1，参数2..)=MIN(参数1，参数2..)=ABS(参数1)常用函数—计数函数2.331SUMIF/COUNTIF函数：区域内满足给定条件的单元格数目=COUNTIF（range，criteria)区域条件COUNTIFCOUNTACOUNTA函数：返回单元格内非空值的个数=COUNTA（参数1，参数2，......)常用函数—计数函数2.332SUMIF/案例COUNTIF函数：计算保费金额大于500的保单数输入公式=COUNTIF(C2:C12,500)区域条件常用函数—条件函数和逻辑函数2.333SUMIF/COUNTIFCOUNTAIF函数：条件函数，它根据参数条件的真假返回不同的结果值。=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.339MINABS求某数的绝对值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/RankLARGERANK排名函数语义规则：=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快捷技巧PART0444EXCEL快捷技巧3快速转换不同的工作表：”CRTL(+SHIFT)+PgDn/PgUp”快速移动：按下Ctrl+相应的方向键或方向键组合。例如按下“Crtl+↓”即可到达选中最下方的单元格快速选取：按下Ctrl+Shift+相应的方向键或方向键组合，再复制即可自动换行：如果出现多个保单号，需拆分为不同的列采用行列转置：选择需要转置的数据（包括所有行或列标签）,按下Ctrl+C，选择性粘贴中的转置即可45EXCEL快捷技巧3快速恢复E+的数字格式显示：数字长度大于11为会以科学计数显示，分列选择文本格式显示即可。快速求行与列合计数：Ctrl+G→定位条件→空值→“ALT+=“多单元格批量输入相同的内容：将想要输入内容的单元格一起选中，然后在编辑栏