Excel在财务日常工作中的应用课件

Excel在日常财务工作中的应用1Excel在日常财务工作中的应用12233提纲基本操作技巧1基本函数2数据透视表和透视图3微软Office技巧大赛获奖作品

44提纲基本操作技巧1基本函数2数据透视表和透视图第一部分基本操作技巧5第一部分基本操作技巧5当排序含有公式的数据列表时，应该谨慎行事。简单排序

步骤：1)选择参与排序的数据区域（注：一般不包括字段名行，但包括所有列）。2)单击工具栏中的升序或降序按钮。一、排序练习:对“report.xls”进行操作,对年龄进行排序。6当排序含有公式的数据列表时，应该谨慎行事。一、排序练习:对复杂排序

步骤：1)选择参与排序的数据区域。2)[数据][排序]。3)选择关键字和排序方向。

最多可以选择3个关键字段进行排序。

一、排序7复杂排序一、排序7一、排序8一、排序8筛选指只显示满足条件的数据记录。有自动筛选和高级筛选两种方法。操作步骤：1)光标位于数据列表上。2)[数据]→[筛选]→[自动筛选]。3)单击筛选按钮，取值或选取自定义后给出筛选条件。4)取消筛选：[数据]→[筛选]→[自动筛选]二、筛选-自动筛选筛选按钮9筛选指只显示满足条件的数据记录。有自动筛选和高级筛选两练习：利用自定义筛选方式选择年龄<40岁，姓张的观测，并将其存为新的Excel文件。

二、筛选-自动筛选10练习：利用自定义筛选方式选择年龄<40岁，姓张的观测，并

二、筛选-高级筛选高级筛选能够提供以下功能：（1）可以指定更复杂的筛选条件；（2）可以指定计算的筛选条件；（3）可以从另一个地址中摘录适合条件的记录副本。操作：[数据][筛选][高级筛选]步骤：一、为高级筛选建立条件区域；至少有两行组成，在第一行中必须包括包含有数据清单中的一些或全部字段名称；条件区域的另一行必须由筛选条件构成。二、在筛选过程中使用多个条件条件在同一行中，为“AND”操作；条件在不同行中，为“OR”操作。选择不重复的记录：会隐藏所有符合指定条件的行的副本，如果没有指定条件区域，此选项会隐藏数据列表中的所有重复的行的副本。11二、筛选-高级筛选高级筛选能够提供以下功能：步骤：选择不

二、筛选-高级筛选使用计算条件进行筛选：条件区域为B1:B2，这个公式计算“发病数”列的平均值，并将它与“发病数”列的第一个单元格中的数据进行比较。注意：必须使用新的字段标题，而不使用列表中的字段标题；如果计算公式涉及到数据列表外的值，要使用绝对引用而不是相对引用；使用数据列表中首行数据创建计算条件公式（不是字段名称），使用相对引用而不是绝对引用。条件区域12二、筛选-高级筛选使用计算条件进行筛选：条件区域

二、筛选13二、筛选13操作：1)按某关键字段进行排序（分类）。2)[数据]→[分类汇总]。3)对“分类汇总”对话框进行如下操作：

分类字段。

汇总方式。

选定汇总项（可有多个）。

替换当前分类汇总。

每组数据分页/不分页。

汇总结果显示在数据下方。三、分类汇总14操作：三、分类汇总14练习：对“exp3.xls”操作，求各年的发病数平均值。操作：（1）先对数据按年份进行排序；（2）[数据][分类汇总]（3）取消分类汇总：[数据][分类汇总]，选择[全部删除]三、分类汇总15练习：对“exp3.xls”操作，求各年的发病数平均值。三、四、行列转置16四、行列转置16第二部分常用函数17第二部分常用函数17常用函数二、函数18常用函数二、函数18VLOOKUP函数应用-帮忙寻找配对只要两个表有共同的一列信息，比如姓名、身份证号码，就可以用VLOOKUP公式通过这个桥梁在一张表上寻找另一张表上的信息。=Vlookup（第一个表共同信息列的单元格，第二个表的查找范围，第几列，false）日常应用于：对数、个人所得税申报、网银批量录入例：渠道的工资表和社保缴费表的校对19VLOOKUP函数应用-帮忙寻找配对只要两个表有共同的一列信VLOOKUP函数应用-帮忙寻找配对1、Vlookup是从左向右寻找信息，因此两个表共同的信息列一定要在寻找的信息的左方。比如上例姓名一定是在工资的左方。2、选中查找的范围一定要从第二个表的共同信息列开始选起，并加绝对引用，即单元格前加$符号3、第几列的定义是从第二个表的共同信息列数起，至寻找信息列止。20VLOOKUP函数应用-帮忙寻找配对1、Vlookup是从左第三部分数据透视表21第三部分数据透视表21“数据透视表与数据透视图”有机地综合了前面介绍的排序、筛选、分类汇总、等数据分析的优点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。计算均数标准差（分类汇总）、建立列联表、计算百分比、建立新的数据子集、绘制统计图形等均可利用这一工具完成。因此，该工具是最常用、功能最全的Excel数据分析工具之一。掌握这一功能，可以帮助用户解决许多数据分析问题。Excel的数据透视表和数据透视图22“数据透视表与数据透视图”有机地综合了前面介绍的排序Excel的数据透视表－－数据透视表的创建源数据如右边的截图所示。23Excel的数据透视表－－数据透视表的创建源数据如右边的截图步骤一：在存放数据的工作表中，选取任一单元格，以便Excel自动确定用户想用的数据范围，单击“数据→数据透视表和数据透视图”，弹出“数据透视表和数据透视图向导”对话框。Excel的数据透视表－－数据透视表的创建24步骤一：在存放数据的工作表中，选取任一单元格，以便E步骤二：单击“下一步”按钮，弹出如下截图：步骤三：确定Excel已正确识别数据的单元格区域。单击“下一步”按钮，可得下面截图。该步骤指定数据透视表显示的位置。本例按默认选定新建工作表。Excel的数据透视表－－数据透视表的创建25步骤二：单击“下一步”按钮，弹出如下截图：步步骤四：单击完成键，Excel自动创建一张新的工作表。在该工作表中显示了创建的数据透视表雏形，此外，数据透视表工具栏以及数据透视表字段列表也显示在工作表界面。Excel的数据透视表－－数据透视表的创建26步骤四：单击完成键，Excel自动创建一张新的工作表数据区：数据透视表的雏形中分位4个数据区。

“数据字段区”，此处提示“请将数据项拖至此处”。为了计数或求频数，一般将个体编号变量拖入此处；为了计算定量数据描述性统计量（如平均值、标准差等），应将定量数据（如年龄、身高、体重、发病数）拖入此处。

“行字段”区，此处提示“请将行字段拖至此处”，该区域应拖入分类变量，该变量的分组类别排成一列，每个类别占一行。

“列字段”区，此处提示“请将列字段拖至此处”，该区域也应拖入分类变量，该变量的分组类别排成一行，每个类别占一列。

“页字段”区，此处提示“请将页字段拖至此处”，如果将分类变量放在此处，选择这一变量的某一类别，则产生一个数据透视表，选择这一变量的另一类别，则覆盖前次产生的表格，产生一个新的数据透视表，这样可查看在某一变量的不同值时，数据透视分析的结果。选取“（全部）”时，则忽略该变量的作用。这一区域的作用相当于统计上所说的分层，即拖入的变量分层后参看各层的数据分析结果

Excel的数据透视表－－数据透视表的结构及其工具栏27数据区：数据透视表的雏形中分位4个数据区。Excel的数据数据透视表工具栏设置报告格式图表向导隐含明细数据显示明细数据更新数据汇总包含隐含项始终显示项目字段变量设置显示字段列表编辑与组织数据透视表创建了数据透视表雏形后，应根据分析要求，设置数据透视表的版式。该步骤也是数据透视表的最关键的一步。Excel的数据透视表－－数据透视表的结构及其工具栏28数据透视表工具栏设置报告格式图表向导隐含明细数据显示明细例：查看2002年各月四种疾病的发病情况Excel的数据透视表－－数据透视表的结构及其工具栏29例：查看2002年各月四种疾病的发病情况Excel的数据透视数据透视图有两种方法可以创建数据透视图。方法一：在数据透视表的工具栏中单击“图表向导”图标；方法二：直接利用“数据透视表和数据透视图向导”对话框创建。这里我们用第一种方法，利用刚才创建的数据透视表创建数据透视图。30数据透视图有两种方法可以创建数据透视图。30Excel的数据透视图－－创建数据透视图31Excel的数据透视图－－创建数据透视图31如何消除缩位后的计算误差（微软Office技巧大赛获奖作品）

有时我们输入的数字是小数点后两位数，但是在精度要求上只要一位，缩位后显示没问题，但其计算结果却是有误差的。解决方法是:单击“工具→选项→重新计算”，选中“以显示精度为准”（），这样计算结果就没有误差了。事实上并不是计算上有误差，而是显示设置的四舍五入。采用本技巧提供的方法，可以解决显示中的问题，但同时会改变数值的精度，在使用前Excel会给你一个警告。实际运用总结如下：保留小数后2位数更加简单的方法（应用于涉及金额发放、社保缴纳等）1、选择所需设置的数据然后菜单栏先选格式--单元格--数值---小数位2位。2、在“工具——选项”，在“重新计算”标签里勾选“以显示精度为准”。这样做后合计数也没差值，其他数值也不用变。32如何消除缩位后的计算误差（微软Office技巧大赛获奖作品）同时进行多个单元格的运算（微软Office技巧大赛获奖作品）

如果我们现在有多个单元格的数据要和一个数据进行加减乘除运算，那么一个一个运算显然比较麻烦，其实利用“选择性粘贴”功能就可以实现同时运算。下面我们一起来看一个实例。我们要将C1、C4、C5、D3、E11单元格数据都加上25，那么可以这样做:首先在一个空白的单元格中输入25，选中这个单元格后点击鼠标右键选择“复制”。然后按住Ctrl键依次点击C1、C4、C5、D3、E11单元格，将这些单元格选中。接下来点击鼠标右键选择“选择性粘贴”，在“选择性粘贴”对话框中勾选“运算”框内的“加”选项，点击“确定”。现在我们可以看到，这些单元格中的数据都同时被加上了25。

33同时进行多个单元格的运算（微软Office技巧大赛获奖作品）快速选定Excel区域（微软Office技巧大赛获奖作品）在Excel中，要想在工作簿中快速选定某块区域，只需单击想选定的区域的左上角单元格，同时按住Shift键不放，再单击想选定的区域的右下角单元格即可。另外:按住Ctrl键再用鼠标可任意选定多个不相邻的区域。事实上选定区域和顺序无关，只需首先选定矩形区域的一个角的单元格，在按下Shift的同时单击对角单元格。

34快速选定Excel区域（微软Office技巧大赛获奖作品）3快速进行单元格之间的切换（微软Office技巧大赛获奖作品）在Excel中,可以用以下方法实现在一个区域内的快速输入而不用鼠标来进行单元格之间的切换。方法如下:用鼠标圈定一定区域后，按Tab键可使目标单元格向后移，按“Shift+Tab”可向前移。这样就可以在键盘上连续输入一组数据而不需用鼠标，从而提高输入速度。提示:事实上，采用此方法最大的好处是，在一行的最后一个单元格，继续按Tab，则可以转到下一行开始的单元格;在选定区域最后一行的最后一个单元格继续按Tab则会回到选定区域第一行第一个单元格。同样用Enter可以按列输入数据。

35快速进行单元格之间的切换（微软Office技巧大赛获奖作品）隐藏单元格中的所有值（微软Office技巧大赛获奖作品）

有时候，我们需要将单元格中所有值隐藏起来，这时你可以选择包含要隐藏值的单元格。单击“格式→单元格”命令，选择“数字”选项卡，在“分类”列表中选择“自定义”，然后将“类型”框中已有的代码删除，键入“;;;”(3个分号)即可（）。提示:你也许奇怪为什么会这样，其实单元格数字的自定义格式是由正数、负数、零和文本4个部分组成。这4个部分用3个分号分隔，哪个部分空，相应的内容就不会在单元格中显示。现在都空了，当然就都不显示了。

36隐藏单元格中的所有值（微软Office技巧大赛获奖作品）

彻底隐藏单元格

在很多时候我们会将一些行或列的内容隐藏起来不让别人查看，但是如果用户复制包含隐藏行或列的区域，这样隐藏的内容就会在粘贴的新的工作表中出现，降低了隐藏效果。如果要想避免这种情况，我们可以先选中要复制的区域，然后单击“编辑→定位”命令，在打开的对话框中点击“定位条件”按钮，在定位条件对话框中选中“可见单元格”选项），这样就可以避免上面所发生的情况了。

37彻底隐藏单元格

在很多时候我们会将一些行或列的内容隐藏起来使用自定义序列排序（微软Office技巧大赛获奖作品）

在Excel排序对话框中选择主要关键字后单击选项，可以选择自定义序列作为排序次序，使排序方便快捷且更易于控制。顺便说一下，排序可以选择按列或按行，如果以前排行的顺序都是选择性粘贴转置，排完序后再转置。自定义排序只应用于“主要关键字”框中的特定列。在“次要关键字”框中无法使用自定义排序。若要用自定义排序对多个数据列排序，则可以逐列进行。例如，要根据列A或列B进行排序，可先根据列B排序，然后通过“排序选项”对话框确定自定义排序次序，下一步就是根据列A排序。

38使用自定义序列排序（微软Office技巧大赛获奖作品）

在E选定超级链接文本（微软Office技巧大赛获奖作品）

如果需要在Excel中选定超级链接文本而不跳转到目标处，可在指向该单元格时，单击并按住可选定单元格。

39选定超级链接文本（微软Office技巧大赛获奖作品）如果需谢谢大家的聆听40谢谢大家的聆听40Excel在日常财务工作中的应用41Excel在日常财务工作中的应用1422433提纲基本操作技巧1基本函数2数据透视表和透视图3微软Office技巧大赛获奖作品

444提纲基本操作技巧1基本函数2数据透视表和透视图第一部分基本操作技巧45第一部分基本操作技巧5当排序含有公式的数据列表时，应该谨慎行事。简单排序

步骤：1)选择参与排序的数据区域（注：一般不包括字段名行，但包括所有列）。2)单击工具栏中的升序或降序按钮。一、排序练习:对“report.xls”进行操作,对年龄进行排序。46当排序含有公式的数据列表时，应该谨慎行事。一、排序练习:对复杂排序

步骤：1)选择参与排序的数据区域。2)[数据][排序]。3)选择关键字和排序方向。

最多可以选择3个关键字段进行排序。

一、排序47复杂排序一、排序7一、排序48一、排序8筛选指只显示满足条件的数据记录。有自动筛选和高级筛选两种方法。操作步骤：1)光标位于数据列表上。2)[数据]→[筛选]→[自动筛选]。3)单击筛选按钮，取值或选取自定义后给出筛选条件。4)取消筛选：[数据]→[筛选]→[自动筛选]二、筛选-自动筛选筛选按钮49筛选指只显示满足条件的数据记录。有自动筛选和高级筛选两练习：利用自定义筛选方式选择年龄<40岁，姓张的观测，并将其存为新的Excel文件。

二、筛选-自动筛选50练习：利用自定义筛选方式选择年龄<40岁，姓张的观测，并

二、筛选-高级筛选高级筛选能够提供以下功能：（1）可以指定更复杂的筛选条件；（2）可以指定计算的筛选条件；（3）可以从另一个地址中摘录适合条件的记录副本。操作：[数据][筛选][高级筛选]步骤：一、为高级筛选建立条件区域；至少有两行组成，在第一行中必须包括包含有数据清单中的一些或全部字段名称；条件区域的另一行必须由筛选条件构成。二、在筛选过程中使用多个条件条件在同一行中，为“AND”操作；条件在不同行中，为“OR”操作。选择不重复的记录：会隐藏所有符合指定条件的行的副本，如果没有指定条件区域，此选项会隐藏数据列表中的所有重复的行的副本。51二、筛选-高级筛选高级筛选能够提供以下功能：步骤：选择不

二、筛选-高级筛选使用计算条件进行筛选：条件区域为B1:B2，这个公式计算“发病数”列的平均值，并将它与“发病数”列的第一个单元格中的数据进行比较。注意：必须使用新的字段标题，而不使用列表中的字段标题；如果计算公式涉及到数据列表外的值，要使用绝对引用而不是相对引用；使用数据列表中首行数据创建计算条件公式（不是字段名称），使用相对引用而不是绝对引用。条件区域52二、筛选-高级筛选使用计算条件进行筛选：条件区域

二、筛选53二、筛选13操作：1)按某关键字段进行排序（分类）。2)[数据]→[分类汇总]。3)对“分类汇总”对话框进行如下操作：

分类字段。

汇总方式。

选定汇总项（可有多个）。

替换当前分类汇总。

每组数据分页/不分页。

汇总结果显示在数据下方。三、分类汇总54操作：三、分类汇总14练习：对“exp3.xls”操作，求各年的发病数平均值。操作：（1）先对数据按年份进行排序；（2）[数据][分类汇总]（3）取消分类汇总：[数据][分类汇总]，选择[全部删除]三、分类汇总55练习：对“exp3.xls”操作，求各年的发病数平均值。三、四、行列转置56四、行列转置16第二部分常用函数57第二部分常用函数17常用函数二、函数58常用函数二、函数18VLOOKUP函数应用-帮忙寻找配对只要两个表有共同的一列信息，比如姓名、身份证号码，就可以用VLOOKUP公式通过这个桥梁在一张表上寻找另一张表上的信息。=Vlookup（第一个表共同信息列的单元格，第二个表的查找范围，第几列，false）日常应用于：对数、个人所得税申报、网银批量录入例：渠道的工资表和社保缴费表的校对59VLOOKUP函数应用-帮忙寻找配对只要两个表有共同的一列信VLOOKUP函数应用-帮忙寻找配对1、Vlookup是从左向右寻找信息，因此两个表共同的信息列一定要在寻找的信息的左方。比如上例姓名一定是在工资的左方。2、选中查找的范围一定要从第二个表的共同信息列开始选起，并加绝对引用，即单元格前加$符号3、第几列的定义是从第二个表的共同信息列数起，至寻找信息列止。60VLOOKUP函数应用-帮忙寻找配对1、Vlookup是从左第三部分数据透视表61第三部分数据透视表21“数据透视表与数据透视图”有机地综合了前面介绍的排序、筛选、分类汇总、等数据分析的优点，可方便地调整分类汇总的方式，灵活地以多种不同方式展示数据的特征。计算均数标准差（分类汇总）、建立列联表、计算百分比、建立新的数据子集、绘制统计图形等均可利用这一工具完成。因此，该工具是最常用、功能最全的Excel数据分析工具之一。掌握这一功能，可以帮助用户解决许多数据分析问题。Excel的数据透视表和数据透视图62“数据透视表与数据透视图”有机地综合了前面介绍的排序Excel的数据透视表－－数据透视表的创建源数据如右边的截图所示。63Excel的数据透视表－－数据透视表的创建源数据如右边的截图步骤一：在存放数据的工作表中，选取任一单元格，以便Excel自动确定用户想用的数据范围，单击“数据→数据透视表和数据透视图”，弹出“数据透视表和数据透视图向导”对话框。Excel的数据透视表－－数据透视表的创建64步骤一：在存放数据的工作表中，选取任一单元格，以便E步骤二：单击“下一步”按钮，弹出如下截图：步骤三：确定Excel已正确识别数据的单元格区域。单击“下一步”按钮，可得下面截图。该步骤指定数据透视表显示的位置。本例按默认选定新建工作表。Excel的数据透视表－－数据透视表的创建65步骤二：单击“下一步”按钮，弹出如下截图：步步骤四：单击完成键，Excel自动创建一张新的工作表。在该工作表中显示了创建的数据透视表雏形，此外，数据透视表工具栏以及数据透视表字段列表也显示在工作表界面。Excel的数据透视表－－数据透视表的创建66步骤四：单击完成键，Excel自动创建一张新的工作表数据区：数据透视表的雏形中分位4个数据区。

“数据字段区”，此处提示“请将数据项拖至此处”。为了计数或求频数，一般将个体编号变量拖入此处；为了计算定量数据描述性统计量（如平均值、标准差等），应将定量数据（如年龄、身高、体重、发病数）拖入此处。

“行字段”区，此处提示“请将行字段拖至此处”，该区域应拖入分类变量，该变量的分组类别排成一列，每个类别占一行。

“列字段”区，此处提示“请将列字段拖至此处”，该区域也应拖入分类变量，该变量的分组类别排成一行，每个类别占一列。

“页字段”区，此处提示“请将页字段拖至此处”，如果将分类变量放在此处，选择这一变量的某一类别，则产生一个数据透视表，选择这一变量的另一类别，则覆盖前次产生的表格，产生一个新的数据透视表，这样可查看在某一变量的不同值时，数据透视分析的结果。选取“（全部）”时，则忽略该变量的作用。这一区域的作用相当于统计上所说的分层，即拖入的变量分层后参看各层的数据分析结果

Excel的数据透视表－－数据透视表的结构及其工具栏67数据区：数据透视表的雏形中分位4个数据区。Excel的数据数据透视表工具栏设置报告格式图表向导隐含明细数据显示明细数据更新数据汇总包含隐含项始终显示项目字段变量设置显示字段列表编辑与组织数据透视表创建了数据透视表雏形后，应根据分析要求，设置数据透视表的版式。该步骤也是数据透视表的最关键的一步。Excel的数据透视表－－数据透视表的结构及其工具栏68数据透视表工具栏设置报告格式图表向导隐含明细数据显示明细例：查看2002年各月四种疾病的发病情况Excel的数据透视表－－数据透视表的结构及其工具栏69例：查看2002年各月四种疾病的发病情况Excel的数据透视数据透视图有两种方法可以创建数据透视图。方法一：在数据透视表的工具栏中单击“图表向导”图标；方法二：直接利用“数据透视表和数据透视图向导”对话框创建。这里我们用第一种方法，利用刚才创建的数据透视表创建数据透视图。70数据透视图有两种方法可以创建数据透视图。30Excel的数据透视图－－创建数据透视图71Excel的数据透视图－－创建数据透视图31如何消除缩位后的计算误差（微软Office技巧大赛获奖作品）

有时我们输入的数字是小数点后两位数，但是在精度要求上只要一位，缩位后显示没问题，但其计算结果却是有误差的。解决方法是:单击“工具→选项→重新计算”，选中“以显示精度为准”（），这样计算结果就没有误差了。事实上并不是计算上有误差，而是显示设置的四舍五入。采用本技巧提供的方法，可以解决显示中的问题，但同时会改变数值的精度，在使用前Excel会给你一个警告。实际运用总结如下：保留小数后2位数更加简单的方法（应用于涉及金额发放、社保缴纳等）1、选择所需设置的数据然后菜单栏先选格式--单元格--数值---小数位2位。2、在“工具——选项”，在“重新计算”标签里勾选“以显示精度为准”。这样做后合计数也没差值，其他数值也不用变。72如何消除缩位后的计算误差（微软Office技巧大赛获奖作品）同时进行多个单元格的运算（微软Office技巧大赛获奖作品）

如果我们现在有多个单元格的数据要和一个数据进行加减乘除运算，那么一个一个运算显然比较麻烦，其实利用“选择性粘贴”功能就可以实现同时运算。下面我们一起来看一个实例。我们要将C1、C4、C5、D3、E11单元格数据都加上25，那么可以这样做:首先在一个空白的单元格中输入25，选中这个单元格后点击鼠标右键选择“复制”。然后按住Ctrl键依次点击C1、C4、C5、D3、E11单元格，将这些单元格选中。接下来点击鼠标右键选择“选择性粘贴”，在“选择性粘贴”对话框中勾选“运算”框内的“加”选项，点击“确定”。现在我们可以看到，这些单元格中的数据都同时被加上了25。

73同时进行多个单元格的运算（微软Office技巧大赛获奖作品）快速选定Excel区域（微软Office技巧大赛获奖作品）在Excel中，要想在工作簿中快速选定某块区域，只需单击想选定的区域的左上角单元格，同时按住Shift键不放，再单击想选定的区域的右下角单元格即可。另外:按住Ctrl键再用鼠标可任意选定多个不相邻的区域。事实上选定区域和顺序无关，只需首先选定矩形区域的一个角的单元格，在按下Shift的同时单击对角单元格。

74快速选定Excel区域（微软Office技巧大赛获奖作品）3快速进行单元格之间的切换