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

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

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

,财务EXCEL操作技巧培训,目录,EXCEL基本用途,常见数据计算,EXCEL快捷技巧,1,2,4,EXCEL输出,第一部分EXCEL基本用途,PART01,3,创建统计表格,1.1,4,表至表IT提数：

.unl格式txt格式,外部获取数据,创建统计表格,1.1,5,IT提数：

.unl格式直接拖入EXCEL,处理步骤：

“完成”,分析与筛选数据-数据透视表创建,1.2,6,数据透视表:

一种交互式的表，可以进行某些计算，如求和与计数等。

分析与筛选数据-数据透视表布局,1.2,7,说明,分析与筛选数据-数据透视表,1.2,8,注意事项,分析与筛选数据-数据透视表布局,1.2,9,注意事项,1.不能出现同一字段既是筛选字段又是行或列字段2.同一数值字段可以拖放多个到数值字段区域3.每次拖放字段到数据透视表中时，EXCEL都会重新更新（可推迟更新）,分析与筛选数据-数据重复项,1.3,10,数据重复项,删除,查找,删除重复项：

数据删除重复项,查找重复项：

数据透视表（计数项大于1的项为重复项）,分析与筛选数据-数据重复项,1.3,11,数据重复项,删除,查找,查找重复项：

开始条件格式重复值,分析与筛选数据-数据排序,1.3,12,数据排序,清单排序,高级排序,数据清单排序如果按照单列的内容进行排序，可直接“数据”选项卡“”进行排序,高级排序按照多个条件对清单进行排序,分析与筛选数据-数据筛选,1.3,13,数据筛选,自动筛选,自定义筛选,高级筛选,自动筛选字段名称会变成一个下拉框,自定义筛选当自带的筛选条件无法满足需要时，可根据需要自定义筛选条件,分析与筛选数据-数据筛选,1.3,14,数据筛选,自动筛选,自定义筛选,高级筛选,高级筛选使用高级筛选功能，必须先建立一个条件区域，用来指定筛选所满足的条件。

条件区域的第一行是所有作为筛选条件的字段名，这些字段名与数据清单中的字段名必须完全一样。

分析与筛选数据-分类汇总,1.3,15,分类汇总,在数据清单中自动计算分类汇总及总计值。

用户只需指定需要进行分类汇总的数据项、待汇总的数值和用于计算的函数（例如“求和”函数）即可。

汇总单元格排序选项卡“数据”“分类汇总”,分析与筛选数据-分类汇总,1.3,16,分类汇总,注意事项：

汇总前排序在进行分类汇总前，一定要把需要按其汇总的字段进行排序，否则无法实现汇总。

不足之处如有多列行字段头，分类汇总之后，则只保留汇总字段，其他字段无法体现，相比之下，当需求字段多时，使用数据透视表。

分析与筛选数据-数据分级显示,1.3,17,分级显示,有时，我们既想看到汇总数据，又想看到每一个汇总数据下的明细数据，那么势必导致在一张表里数据行较多。

但电脑屏幕有限，不停的上下翻页非常的不便利，也不直观，有没有其他好办法呢？

分级显示,分析与筛选数据-数据分级显示,1.3,18,操作方法：

数据组合选中组数进行分级显示,分析与筛选数据-数据有效性,1.3,19,数据有效性,数据有效性：

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

去除乱字符对外部引入的数据进行处理，去除TAB符、单分号符、占位符、部分位于首尾的空格符。

分析与筛选数据-数据分列,1.3,22,分隔符号分列选项卡“数据”分列,点击”完成”,正常情况下，按图二界面，其他选项保持默认，然后点击“完成”结束数据分列。

分析与筛选数据-数据分列,1.3,23,固定宽度分列选项卡“数据”分列,点击”完成”,可以通过鼠标设置一条或多条分隔线，将字条按任意方式进行分隔处理正常情况下，按图二界面，其他选项保持默认，然后点击“完成”结束数据分列。

分析与筛选数据-快捷组合键,1.4,24,快捷组合键,快捷组合键：

Ctrl+C复制选定的单元格Ctrl+X剪切选定的单元格Alt+;选定指定单元格Ctrl+A选择整个工作表Ctrl+V粘贴选定单元格Ctrl+D使用“向下填充”命令将选定范围内最顶层单元格的内容和格式复制到下面的单元格Ctrl+R使用“向右填充”命令Ctrl+F数据查找Ctrl+H数据替换Ctrl+Z撤销,第二部分常用数据计算,PART02,25,公式引用-参考值“A1”,26,绝对引用,相对引用,混合引用,被引用的单元格随位置变化而变化相对地址：

B1（复制时，相对位置不变）,被引用单元格不随位置改变绝对地址：

$A$1（复制时，始终不变）,相对地址：

$A1、A$1（上述相对，绝对混合）,公式中的错误,2.2,27,常用函数求和函数,2.3,28,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,常用函数求和函数,2.3,29,案例,SUMIF函数:

求各支公司保费合计数SUMIFS函数:

求各支公司不同销售渠道的保费合计数,输入公式=SUMIF（A2:

A12,A2,C2:

C12）核算单位目标单位求和范围2.输入公式=SUMIFS（C2:

C12,B2:

B12,B2,E2:

E12,E2）求和范围条件区域1条件1条件区域2条件2,常用函数数学函数,2.3,30,SUM,MAX,MIN,ABS,求N个数的最大值,求N个数的最小值,求某数的绝对值,=MAX（参数1，参数2.）,=MIN（参数1，参数2.）,=ABS（参数1）,常用函数计数函数,2.3,31,SUMIF/,COUNTIF函数：

区域内满足给定条件的单元格数目=COUNTIF（range，criteria）区域条件,COUNTIF,COUNTA,COUNTA函数：

返回单元格内非空值的个数=COUNTA（参数1，参数2，.）,常用函数计数函数,2.3,32,SUMIF/,案例,COUNTIF函数：

计算保费金额大于500的保单数,输入公式=COUNTIF（C2:

C12,500）区域条件,常用函数条件函数和逻辑函数,2.3,33,SUMIF/,COUNTIF,COUNTA,注意：

这三个函数常常联合使用,常用函数条件函数和逻辑函数,2.3,34,案例,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.3,35,SUMIF/,DATEDIF/VALUE函数：

计算两个日期的相隔年数、月数、天数应收保费账龄计算中经常会引用例如：

计算应收保费逾期天数=DATEDIF（D2,G15,d）或=VALUE（G15-D2）,DATEDIF/VALUE,日期显示,COUNTA函数：

返回单元格内非空值的个数=COUNTA（参数1，参数2，.）,常用函数查找匹配函数,2.3,36,VLOOKUP,VLOOKUP函数：

在表格或数值组的首列查找制定的数值，并由此返回表格或数组当前列制定行处的数值。

函数语法规则如下：

=VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）,常用函数条件函数和逻辑函数,2.3,37,案例：

VLOOKUP函数:

根据表2匹配出表1的被保险人名称,输入公式：

=VLOOKUP（A2,F1:

H12,3,0）两张表共同点：

保单号A列与F列A2目标查找值F1：

H12查找区域3：

需要匹配的项为该区域第三列0：

模糊查找,常用函数文本函数,2.3,38,解决方法：

分列：

指定列分列完成CLEAN函数：

删除文本中不能打印的字符TEXT函数：

将数值转换为按指定数字格式表示的文本TRIM函数：

取消字符串两侧的空格示,文本函数,问题：

两个单元格完全一样，但却无法匹配？

表象蒙蔽数据清理,两个看上去完全一样的单元格，其实往往包含了不同的字串符！

常用函数四舍五入、整数、文本函数,2.3,39,MIN,ABS,求某数的绝对值,常用函数文本函数,2.3,40,常用函数文本函数,2.3,41,目的：

根据客户信息提取保单号（客户信息为A列，保单号为B列）方法：

单击B2单元格，在编辑栏输入公式：

=RIGHT（A2,LEN（A2）-SEARCH（P,A2,6）+1）RIGHT函数：

表示从右边取数LEN函数：

整个文本字串符个数SEARCH函数：

返回指定符号字串符的位置,常用函数排序函数,2.3,42,SUMIF/,Rank,LARGE,RANK排名函数语义规则：

=RANK（number,ref,order）其中，number为要排名的单元格或数字，ref为区域范围，order为排名的顺序，即降序（用0表示）或升序（用1表示）。

LARGE：

返回数据集中的第K个最大值。

语义规则：

=LARGE（array,k）其中，array为需要找到第k个最大值的数组或数字型数据区域，k为返回的数据在数组或数据区域里的位置（从大到小）。

SMALL函数同理,常用函数条件函数和逻辑函数,2.3,43,案例：

根据表一信息，给各分公司保费收入进行排名,结果,方法：

创建数据透视表，求出各个分公司保费收入引入RANK函数:

=RANK（$L$2:

$L$9,$K$2:

$L$9,0）需要排序的值排序引用的范围升序或降序,第三部分EXCEL快捷技巧,PART04,44,EXCEL快捷技巧,3,快速转换不同的工作表：

”CRTL（+SHIFT）+PgDn/PgUp”快速移动：

按下Ctrl+相应的方向键或方向键组合。

例如按下“Crtl+”即可到达选中最下方的单元格快速选取：

按下Ctrl+Shift+相应的方向键或方向键组合，再复制即可,自动换行：

如果出现多个保单号，需拆分为不同的列采用行列转置：

选择需要转置的数据（包括所有行或列标签）,按下Ctrl+C，选择性粘贴中的转置即可,45,EXCEL快捷技巧,3,快速恢复E+的数字格式显示：

数字长度大于11为会以科学计数显示，分列选择文本格式显示即可。

快速求行与列合计数：

Ctrl+G定位条件空值“ALT+=“多单元格批量输入相同的内容：

将想要输入内容的单元格一起选中，然后在编辑栏中输入想要的内容，最后按下”Ctrl+Enter”（编辑栏不显示选项高级显示显示编辑栏）,对比同行数据是否相同：

Ctrl+G定位条件行内容差异单元格填充颜色快速删除错误值：

选择需要定位的区域，按下快捷键Ctrl+G,打开“定位”对话框选择“定位条件”中的“公式”“错误”，成功定位后批量清除,46,EXCEL快捷技巧,3,利用选择性粘贴快速加减乘除,47,常用,常忽视,少用,第四部分EXCEL输出,PART04,48,EXCEL表格保护,4,49,有时，要求分公司、支公司、业务员、出单员等报送一定的表格，为了简化，表格全部设好，只需要相关人员往指定的空格录入即可。

可是表格发下去，经常被改的面目全非。

公式、格式、汇总数据都不对，怎么办？

保护工作表,只允许选定指定区域,EXCEL表格保护,4,50,步骤：

确保单元格锁定状态：

选中全表，点击“设置单元格格式”右键“保护”选”锁定“选项卡中点”审阅“，选择”保护工作表“选定要锁定的列或行（输入密码）,EXCEL表格保护,4,51,步骤：

结果显示,说明：

想锁定哪行或哪列，只需在设置单元格的时候锁定即可，其余行列取消锁定。

结果为锁定行列被锁定，其他行列可填写。

EXCEL表格打印,4,52,如何把多数据放在一张表上？

视图分页预览鼠标指向垂直分页符当其变为双向箭头时拖至打印最右一列,EXCEL表格打印,4,53,每一页打印行标题或列标题,问题咨询EMAIL:

TEL:

0791-86631202,谢谢！