Excel会计与财务管理-理论、方案暨模型教学全套课件.ppt

1、,Excel会计与财务管理 理论、方案暨模型,高 等 教 育 出 版 社,第1章 Excel2003基础知识,本章教学要求： 了解Excel2003的工作窗口 掌握工作簿、工作表之间的关系 掌握工作表的各种操作 掌握窗口操作和打印操作 掌握图表的建立和编辑 了解Excel 的网络功能,第1章 Excel2003基础知识,主要内容： 1.1 Excel2003的工作窗口 1.2 工作表的基本操作（重点） 1.3 窗口操作 1.4 打印操作 1.5 图表操作（重点） 1.6 Excel的网络功能,1.1 Excel2003的工作窗口,1.2 工作表的基本操作,工作簿 工作簿是处理数据和储存数据的文

2、件，每个工作簿最多可以包含255 张类型不同的工作表，因此可在单个文件中管理不同类型的相关信息。 工作表 工作表是Microsoft Excel用来存储和处理数据的文档，包含排列成行和列的单元格，也称电子表格。 工作表能存储包含字符串、数字、公式、图表、声音等丰富的信息或数据，并可以对信息或数据进行组织和分析。 工作表的名称显示在工作簿窗口底部的工作表标签上，活动工作表（正在使用中的工作表）的名称以单下划线并反白显示。要使用哪个工作表，只要用鼠标单击相应的工作表标签即可。,单元格和单元格区域 单元格是Excel中最小的独立单位，它由纵横相交的直线相交而成，其大小可以改变。在单元格里可以输入字符

3、串、数据或日期等数据信息。 单元格区域是一组被选中的相邻或分离的单元格。单元格区域被选定后，所选范围内的单元格都高亮度显示，任何时候只要在工作表数据区内单击鼠标，则取消选定，取消后，单元格又恢复原样。对一个单元格区域的操作是对该区域内的所有单元格执行相同的操作。,单元格的选取 直接用鼠标 采用【定位】 单元格区域的选取 整行整列 全部单元格 连续的单元格区域 不连续的单元格区域,单元格和单元格区域的选取,在单个单元格中输入数据很简单，只需单击需要输入数据的单元格，然后键入数据并按Enter或Tab键即可。 输入数据时，不同的数据类型有不同的使用规定，用户需要对各种类型数据的使用规则有所了解。

4、1输入数值 在Excel中，数字只可以为下列字符： 0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e 常见的数字长度为11位，其中包括小数点和类似“E”和“+”这样的字符。如果需要输入并显示更多位数的数字，可以使用内置的科学记数格式（如5.25E+11）或自定义数字格式。,在单元格中输入数据的方法,2输入日期和时间 Excel 将日期和时间视为数字处理。工作表中的时间或日期的显示方式取决于所在单元格中的数字格式。时间和日期可以相加、相减，并可以包含到其他运算当中。 如果要在公式中使用日期或时间，请用带引号的文本形式输入日期或时间值。 公式“=“07-09-04

5、”-“06-3-10” ”显示值为543。 如果要在同一单元格中键入日期和时间，在其间要用空格分开。 如果要按 12 小时制键入时间，请在时间后留一空格，并键入AM或PM (或 A 或 P)，表示上午或下午。,3输入文本 注意：在Excel中，文本“5”与数字“5”是类型不同的数据，在没有设置单元格格式时，输入的文本将靠左对齐，数字靠右对齐，因此，在输入数字或文本时要区别对待。如果想输入的数字是文本，则要在数字前加一个单引号（），以表明不是数字而是文本，或者先对输入的区域设置“文本”格式，再输入数字。 4输入公式和函数 直接输入法。键入“=” 使用“插入函数”。 注意：公式是用等号开头的一个表

6、达式。,5输入数组 使用数组运算可以减少计算步骤。 例如，在产品销售统计表中，A4:A10单元格区域表示各产品的销售数量，B4:B10单元格区域表示相应的售价，需要计算销售总额。按常规做法是，先在C4:C10单元格区域内分别求得各产品的销售额，C4=A4*B4、C5=A5*B5、.、C10=A10*B10，再在销售总额位置上使用SUM(C4:C10)函数公式求的。若使用数组，只需要在销售总额位置上输入数组=SUM(A4:A10*B4:B10)，即可得到正确的结果。 注意：数组运算公式的两端大括号（）并不是人工输入的，而是在输入普通的公式后，同时按下Ctrl+Shift+Enter三键后自动产生

7、的。公式两端加上一对大括号，代表了数组运算。,6. 在相邻单元格中输入数据 如果相邻单元格中的数字有一定的规律，为了简化输入，可以采用简单复制和填充两种方法。 技巧：在Excel工作表中，通过拖动单元格填充柄，可以将选定单元格的内容复制到同行或同列的其他单元格中去。,7. 在多个单元格中输入数据 我们通常会碰到需要在一张工作表中的多个单元格，或者在多张工作表的多个单元格中输入相同的数据。 （1）同时在多个单元格中输入相同数据的方法 选定需要输入数据的单元格。选定的单元格可以是相邻的，也可以是不相邻的； 键入相应的数据，然后按Ctrl+Enter键。,（2）同时在多张工作表中输入相同数据的方法

8、在选定了一组工作表后，在活动工作表的选定区域中进行的任何改动，都将反映到所有其它工作表相对应的单元格中，这些工作表中的数据将被置换。操作方法如下： 选定需要输入数据的工作表； 再选定需要输入数据的单元格或单元格区域； 在第一张工作表选定单元格中键入或编辑相应的数据； 然后按Enter或Tab键，Excel在所有选定工作表的相应单元格中将自动输入相同的数据。 如果已经在某张工作表中输入了数据，可以快速将这些数据复制到其他工作表相应的单元格中。具体方法如下：先选定包含待复制数据的工作表和接收复制数据的工作表，再选定待复制数据的单元格区域，然后用鼠标指针指向“编辑”菜单中的“填充”选项，单击“至同组

9、工作表”命令。,数据的有效性,Excel能对输入的数据进行有效性检查。 数据有效性设置步骤： 单击【数据】/【有效性】 确定 提示：可以对设置数据有效性的单元格进行移动、复制、粘贴等操作,1. 修改数据 在编辑栏内修改数据 在单元格内修改数据 2. 复制数据 利用复制和粘贴命令 利用多重剪贴板工具 3. 移动数据 利用剪切和粘贴命令 利用鼠标拖动 4. 删除数据 5. 查找与替换,数据的编辑,1. 调整行高 自动调整行高 用鼠标调整行高 指定行高的精确数值 2. 调整列宽 自动调整列宽 用鼠标调整列宽 指定列宽的精确数值,工作表的格式编排调整行高和列宽,1. 插入行 一次插入一行 一次插入多行

10、 2. 插入列 一次插入一列 一次插入多列 3. 插入单元格,工作表的格式编排增加行、列和单元格,选取要删除的行或列，在【编辑】菜单中选择【删除】命令 选取要删除的行或列，按鼠标右键，从弹出的快捷菜单中选择【删除】命令 选取要删除的单元格，在【编辑】菜单中选择【删除】命令，或在选取的单元格上按鼠标右键，从弹出的快捷菜单中选择【删除】命令,工作表的格式编排删除行、列和单元格,设置数据格式 2设置对齐方式 3设置字体和字号 4设置边框和底纹 以上格式编排都可以利用如下方法： 利用格式工具栏中的按钮进行设置 利用菜单命令进行设置 5保护单元格,工作表的格式编排设置工作表格式,1行、列的隐藏 选取要隐

11、藏的行或列，在【格式】菜单中选择【行】或【列】菜单项，选择其中的【隐藏】命令。 将鼠标指针放到要隐藏行（列）的行（列）标处，直接将要隐藏行（列）的下（右）边界线向上（左）拖拽，直到它与上面（左边）相邻行（列）的边界线重合，可将行（列）隐藏起来。 2行、列的显示 可以采用鼠标拖动或菜单来进行,工作表的格式编排行、列的隐藏和显示,在工作簿内移动或复制工作表的步骤 （1）选择要移动或复制的工作表； （2）若要移动工作表，沿工作表标签拖动工作表。若要复制工作表，按下Ctrl键后拖动工作表。 （3）拖动到合适的位置，释放鼠标左键。如果是复制工作表，应先释放鼠标左键，再释放Ctrl键。,工作表的移动或复制

12、,在不同工作簿之间移动或复制工作表的步骤 （1）打开包含有要移动或复制工作表的工作簿（源工作簿），以及接收该工作表的工作簿（目标工作簿）； （2）在“窗口”菜单中，单击“重排窗口”命令，然后单击“水平并排”； （3）选择要移动或复制的工作表； （4）若要移动工作表，拖动工作表到目标工作簿里的工作表标签行中。若要复制工作表，先按下Ctrl键后再拖动工作表。 （5）拖动到合适的位置，释放鼠标左键。如果是复制工作表，应先释放鼠标左键，再释放Ctrl键。,工作表的移动或复制（续）,当移动或复制工作表到一个新工作簿时，一种更为简单的方法： 选择要移动或复制的工作表； 在“编辑”菜单上单击“移动或复制工作

13、表”命令，或者是先移动鼠标到工作表标签，单击鼠标右键，然后选择“移动或复制工作表”命令。 单击“工作簿”编辑框右侧的下拉按钮打开一个下拉列表，从中选择“新工作簿”； 如果要复制工作表，选择“建立副本”复选框； 单击“确定”按钮。,工作表的移动或复制（续）,添加或删除工作表的方法有两种： 利用菜单添加 利用快捷键添加 更名工作表 隐藏工作表 显示工作表,工作表的添加、删除、更名、隐藏和显示,窗口的切换、重排和隐藏 在多个窗口中打开同一工作簿 在不同工作簿窗口间切换 隐藏或取消隐藏窗口 重排工作簿窗口 窗口的拆分 垂直拆分 水平拆分 垂直与水平同时拆分 窗口的冻结,1.3 窗口操作,页面设置 分页

14、设置与调整 打印预览 打印输出 注意：通常需要预先安装打印机驱动程序,1.4 打印操作,1.5 图表,图表是依据选定的工作表单元格区域的数据，按一定的数据系列而生成的，它是工作表数据的图形表示方法。 所谓数据系列，是指一行或者一列具有相同的逻辑含义的单元格数据。,图表的类型,Excel2003提供了14种标准的图表类型，如柱形图、条形图、折线图、饼图、XY散点图、面积图、圆环图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图、棱锥图，每种图表类型都有几种不同的子类型。 此外，Excel2003还提供了20种自定义的图表类型，用户可以根据不同的需要选用适当的图表类型。 单击工具栏的【图表向导】按

15、钮，或单击【插入】菜单，执行【图表】命令，即可查看各种图表。,利用图表向导建立图表的方法或步骤 举例讲解 嵌入式图表 工作表图表 图表的编辑 设置坐标、标题、图例等的格式 移动或复制图表 改变图表大小 编辑图表中的数据 添加数据标志 改变图表颜色、图案和边框,图表的建立和编辑,1.6 Excel的网络功能,在Excel中创建超级链接 从网络上获取数据 在网页上发布Excel数据,第2章 Excel会计与财务管理数据处理基础,本章教学要求： 掌握公式的输入和编辑、单元格的引用以及数组公式 掌握函数的格式以及财会工作中常用的函数 掌握数据的排序、筛选、分类汇总以及数据透视表的建立 掌握数据分析工具

16、的使用,主要内容： 2.1 使用公式 2.2 使用函数 2.3 数据管理 2.4 数据分析工具,第2章 Excel会计与财务管理数据处理基础,2.1 使用公式,公式是由数字、运算符号以及Excel的内置函数等组成的数学表达式。 公式总是以等号（=）开头，后面跟一个包含数字、运算符、单元格引用和函数的表达式。 在单元格中输入公式后，单元格内存储的是公式，显示的是公式计算结果。,公式的输入与编辑,公式的输入 在单元格中输入公式 在编辑栏中输入公式 运算符及优先级,单元格和单元格区域引用 引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用数据的位置。引用不同工作簿中的单元格称为外部引

17、用。 在默认状态下，Excel使用A1引用类型。这种类型用字母标志列（AIV，共256列），用数字标志行（165536）。 引用单元格，可顺序输入列字母和行数字。例如，E18引用了列E和行18交叉处的单元格。 引用单元格区域，可输入左上角的单元格引用、冒号（：）和区域右下角单元格的引用。例如，B2:D5引用了B列2行到D列5行的单元格区域。,单元格引用,引用同一工作表中的单元格数据 引用同一工作簿中某工作表中的数据 引用不同工作簿中工作表中的数据 注意：源工作簿若不打开，其数据变化不会立即反映到引用工作簿上来,单元格的引用方式： 1. 通过地址引用 相对引用 绝对引用 混合引用。 2. 通过名

18、称引用,单元格的命名 方法一： （1）选定需要命名的单元格、单元格区域或非相邻的选定区域； （2）单击编辑栏左边的名称框； （3）为单元格或单元格区域键入名称； （4）按回车键。 方法二： （1）选定需要命名的单元格、单元格区域或非相邻的选定区域； （2）单击“插入”菜单，在“名称”子菜单中，选择“定义”命令； （3）在“定义名称”对话框中键入名称，单击“添加”按钮，关闭对话框即可。,在公式中使用名称,命名规则：,名称首字符必须是字母或者下划线。后面的字符可以是字母、数字等； 名称不能与单元格标识相同。如B$1等； 不允许出现空格，但是下划线和句点可以用来表示分隔符； 一个名称最多允许包含25

19、5个字符； 名称中可以包含大小写字母，但是Excel对其不加以区分； 所有的名称均使用绝对引用,数组公式指可以同时进行多个计算并返回一种或多种结果的公式。 在数组公式中使用的两组或多组数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。 使用数组公式能减少工作量，提高工作效率。 注意： 数组运算公式的两端大括号（）并不是人工输入的，而是在输入普通的公式后，同时按下Ctrl+Shift+Enter三键后自动产生的。公式两端加上一对大括号，代表了数组运算 不能更改数组的一部分,使用数组公式,公式中常见的错误类型,2.2 使用函数,在Excel中，函数是预定义的内置公式，使用特定数值的参

20、数，按语法的特定顺序进行计算。 函数语法：以函数名称开头，后面是括号，在括号中可有语法规定个数的参数，各参数间用逗号隔开。如果函数以公式的形式出现，在函数名称前必须加等号“=”。,函数的参数：参数是函数进行计算所必须的初始值。用户把参数传递给函数，函数按特定的指令对参数进行计算，把计算结果返回给用户。函数的参数可以是数字、文本、逻辑值或者是单元格的引用，也可以是常量公式和其他函数。,1. Excel中使用的函数可分为： 常用函数 财务函数 日期与时间函数 数学和三角函数 统计函数 查找与引用函数 数据库函数 文本函数 逻辑函数 信息函数和工程函数等,函数的分类和语法格式,2函数的语法格式 函数

21、的一般语法格式为：=函数名(参数1，参数2，参数n) 在使用函数时，要注意以下问题： （1）函数要以等号(=)开头，通常放入某个单元格中。 （2）函数名与其后的括号“(”之间不能有空格。 （3）当有多个参数时，参数之间用逗号（,）分开。 （4）参数部分总长度不能超过1024个字符。 （5）参数可以是数值、文本、逻辑值、单元格或单元格区域引用，也可以是各种表达式或函数。 （6）函数中的逗号（,）、引号（”）等都是半角字符，而非全角的中文字符。,函数的调用,1. 单一函数的调用方法： 直接输入函数 利用菜单命令或工具按钮调用函数 2. 嵌套函数的调用 嵌套函数指一个函数的运行结果作为另一个函数中的

22、参数使用的情况 注意：公式中最多可以包含七级嵌套函数，并且当某个函数被嵌套在另一个函数中作为一个参数使用时，它必须返回与当前参数的数值类型相同的数值。,1求和函数（Sum/Sumif/Product） （1）SUM函数 功能：SUM函数用于计算单个或多个参数之和。 公式：SUM(number1,number2,.) 其中，“number1,number2, .”为1到30个需要求和的参数。 在使用SUM函数时，可以计算直接键入到参数表中的数字、逻辑值及数字的文本表达式。如果参数为错误值或为不能转换成数字的文本，将会导致错误。 如果参数是单元格的引用，那么引用的空白单元格、逻辑值、文本和错误值将

23、被忽略。,财务管理中常用的函数,（2）SUMIF函数 功能：当IF后面的数值为“TRUE”时，SUMIF函数对符合指定条件的单元格求和。 公式：SUMIF(range, criteria, sum_range) “range”为用于条件判断的单元格区域。 “criteria”为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32”、“32”、“apples”。 注意：条件可以是单元格引用；当条件为不等式或字符串时， 只能用引号括起来 “sum_range”为需要求和的实际单元格。只有当 range 中的相应单元格满足条件时，才对sum_range

24、中的单元格求和。如果省略sum_range，则直接对 range 中的单元格求和。,（3）多组数据相乘求和的SUMPRODUCT函数 功能：在给定的几组数组中，将数组间对应的元素相乘，返回乘积之和。 公式：=SUMPRODUCT(array1,array2,array3,) 式中，array1,array2,array3,为1-30个数组。 注意：数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值“#VALUE!”。对于非数值型的数组元素将作为0处理。,（1）COUNT函数 功能：计算参数组中，参数为数值类型数据的个数。 公式：COUNT(value1,value2,.)

25、value1, value2, . 是包含或引用各种类型数据的参数（130个），但只有数字类型的数据才被计数。 函数COUNT在计数时，将把数字、空值、逻辑值（0、1）、日期或以文字代表的数计算进去；但是错误值或其它无法转化成数字的文字则被忽略。 如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组中或引用的空单元格、逻辑值、文字或错误值都将忽略。如果要统计逻辑值、文字或错误值，请使用函数COUNTA。,2计数函数（Count/Counta/Countblank/Countif）,（2）COUNTA函数 功能：返回参数组中非空白单元格的数目。利用函数 COUNTA 可以计算数组或单元格

26、区域中数据项的个数。 公式：COUNTA(value1,value2, .) value1, value2, . 为所要计数的值，参数个数为 130 个。在这种情况下，参数值可以是任何类型，它们可以包括空字符（“”），但不包括空白单元格（比较：空值）。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。,（3）COUNTBLANK函数 功能：计算指定单元格区域中空白单元格的个数。 公式：=COUNTBLANK(range) 式中，range为需要计算其中空白单元格数目的区域。 含有返回值为“（空文本）的公式单元格也计算在内，但包含零值的单元格不计算在内。,（4）COUNTIF函数

27、功能：计算给定区域内满足特定条件的单元格的数目。 语法：COUNTIF(range, criteria) range 为需要计算其中满足条件的单元格数目的单元格区域； criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。,（1）AVERAGE函数 功能：对所有参数计算算术平均值。 公式：AVERAGE(number1,number2,) number1,number2,为1到30个需要计算平均值的参数。参数应该是数字或包含数字的单元格引用、数组或名字。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。 注意：空

28、单元格与零值的区别,3. 平均值函数（Average/Daverage）,（2）DAVERAGE函数 功能：返回数据库或数据清单中满足给定条件的数据列中数值的平均值。 公式：=DAVERAGE(database, field, criteria) 式中，database构成数据清单或数据库的单元格区域。field指定函数所使用的数据列；criteria为一组包含给定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。,（1）ABS函数 功能：返回数字的绝对值。 公式：=ABS(number) 式中，number为需要计算其绝对

29、值的实数。 （2）SQRT函数 功能：计算一个正数的正平方根。 公式：=SQRT(number) 式中，number为需要计算其平方根的正数。如果参数number为负值，函数SQRT返回错误值“#NLIM!”。 （3）ROUND函数 功能：返回某个数字按指定位数舍入后的数字。 公式：=ROUND(numbel,num_digits) 式中，number为需要进行舍入的数字,4数学函数（Abs/Sqrt/Round）,（1）MIN函数 功能：返回给定参数表中的最小值。 公式：=MIN(numberl,number2,numberN) 式中，numberl，number2，numberN为需要从中

30、找出最小值的1到30个参数。 （2）MAX函数 功能：返回给定参数表中的最大值。 公式：=MAX(numberl,number2,numberN) 式中，numberl，number2，numberN为需要从中找出最大值的1到30个参数;函数中的参数可以是数字、空白单元格、逻辑值或表示数值的文字串。,5最大值与最小值函数（Max/Min）,功能：执行真假值判断，根据逻辑计算的真假值，返回不同的结果。可以使用IF函数对数值和公式进行条件检测。 公式：=IF(logical_test,value_if_true,value_if_false) 式中，logical_test为计算结果为TRUE或F

31、ALSE的任何数值或表达式；value_if_true是logical_test为TRUE时函数的返回值。如果logical_test为TRUE并且省略value_if_true，则返回TRUE。value_if_true 可以为某一个公式；value_if_false是logical_test为FALSE时函数的返回值。如果logical_test为 FALSE并且省略value_if_false，则返回FALSE。value_if_false可以为某一个公式。,6条件函数（If）,（1）AND函数 功能：表示逻辑与，当所有条件都满足时（即所有参数的逻辑值都为真时），AND函数返回TRUE，

32、否则，只要有一个条件不满足即返回FALSE。 公式：=AND(条件l,条件2,条件N) （2）OR函数 功能：OR函数表示逻辑或，只要有一个条件满足时，OR函数返回TRUE，只有当所有条件不满足时才返回FALSE。 公式：=OR(条件l,条件2,条件N) （3）NOT函数 功能：NOT函数的功能是对参数的逻辑值求反。 公式：=NOT(条件) 提示：这三个函数一般与IF函数结合使用。,7逻辑函数（AND/OR/NOT）,（1）LOOKUP函数 功能：返回向量（单行区域或单列区域）或数组中的数值。 向量形式的LOOKUP函数的公式为： =LOOKUP(lookup_value,lookup_vec

33、tor,result_vector) 数组形式的LOOKUP函数的公式为： =LOOKUP(lookup_value,array) lookup_value 函数LOOKUP在第一个向量中所要查找的数值，它可以为数字、文本、逻辑值或包含数值的名称或引用；lookup_vector只包含一行或一列的区域，lookup_vector的数值可以为文本、数字或逻辑值；result_vector只包含一行或一列的区域，其大小必须与lookup_vector相同。array包含文本、数字或逻辑值的单元格区域或数组,8查找函数（Lookup/Vlookup/Hlookup/Match）,（2）VLOOKUP

34、函数 功能：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP 代替函数 HLOOKUP。 公式：=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 式中，lookup_value为需要在数据表第一列中查找的数值，lookup_value可以为数值、引用或文字串；table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用；col_index_num 为 table_array中待返回的匹配值的列序号；range_l

35、ookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。,（3）HLOOKUP函数 功能：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP。当比较值位于要进行数据查找的左边一列时，请使用函数 VLOOKUP。 公式：=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 式中，row_index_num为table_array中待返回的匹配值的行序号。,（4）MATCH函数 功能：返回在指

36、定方式下与指定数值匹配的数组中元素的相应位置。 公式：=MATCH(1ookup_value,1ookup_array,match_type ) 式中，lookup_value是需要在数据表中查找的数值，可以是数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用；lookup_array可能包含所要查找的数值的连续单元格区域，可以是数组或数组引用；match_type数字-1、0或1，它指明Excel如何在lookup_array中查找lookup_value。,（1）INDEX函数 功能：返回表格或区域中的数值或对数值的引用。 INDEX函数有以下两种形式： 返回数组中指定单元格或单

37、元格数组的数值公式：=INDEX(array,row_num,column_num) 返回引用中的指定单元格公式：=INDEX(reference,row_num,column_num,area_num) array是单元格区域或数组常数；row_num是数组中某行的行序号，函数从该行返回数值。如果省略row_num，则必须有column num；column_num是数组中某列的列序号，函数从该列返回数值。如果省略column_num，则必须有row_num。 reference是对一个或多个单元格区域的引用，area_num选择引用中的一个区域，并返回该区域中row_num和column_

38、num的交叉区域。,9引用函数（Index/Address/Indirect）,（2）ADDRESS函数 功能：按照给定的行号和列标，建立文本类型的单元格地址。 公式：=ADDRESS(row_num,column_num,abs_num,al,sheet_text) 式中，row_num为在单元格引用中使用的行号；column_num为在单元格引用中使用的列标；abs_num指明返回的引用类型，当为l或省略时为绝对引用；当为2时为绝对行号，相对列标；当为3时为相对行号，绝对列标；当为4时为相对引用；a1用以指明A1或RlCl引用样式的逻辑值TRUE或FALSE。如果A1为TRUE或省略，函数

39、ADDRESS返回Al样式的引用；如果Al为FALSE，函数ADDRESS返回RlCl样式的引用； sheet_tex为文本，指明作为外部引用的工作表的名称，如果省略sheet_text，则不使用任何工作表名。,（3）INDIRECT函数 功能：返回由文字串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用此函数。 公式：=INDIRECT(ref_text,a1) 式中，ref_text为对单元格的引用，此单元格可以包含Al样式的引用、RlCl样式的引用、定义为引用的名称或对文字串单元格的引用，如果ref_text不是合法的单元格的引

40、用，函数INDIRECT返回错误值#REF!；a1参数同ADDRESS函数。 需要注意的是，如果ref_text是对另一个工作簿的引用（外部引用），则被引用的那个工作簿必须已经打开。如果源工作簿没有打开，函数INDIRECT返回错误值#REF!。,（1）TRANSPOSE函数 功能：求矩阵的转置矩阵。 公式：=TRANSPOSE(array) 式中，array为需要进行转置的数组或工作表中的单元格区域。 （2）MINVERSE函数（Matrix Inverse的简写） 功能：返回矩阵的逆矩阵。 公式：=MINVERSE(array) 式中，array是具有相等行列数的数值数组。array可以是

41、单元格区域、常数数组如1,2,3;4,5,6;7,8,9；或区域和常数数组的名称。 注意： 返回值为数组的公式，必须以数组公式的形式输入，按Ctrl+Shift+Enter结束。 对于一些不能求逆的矩阵，函数 MINVERSE 将返回错误值 #NUM！。不能求逆的矩阵的行列式值为零。,10矩阵函数（Transpose/Minverse/Mmult）,（3）MMULT函数（Matrix Multiple的简写） 功能：返回两数组的矩阵乘积。结果矩阵的行数与array1的行数相同，矩阵的列数与array2的列数相同。 公式：=MMULT(array1,array2) 式中，array1,array

42、2是要进行矩阵乘法运算的两个数组。,1. 数据排序 2. 数据筛选 自动筛选 自定义筛选 高级筛选 3. 数据的分类汇总 4. 数据的合并计算,2.3 数据管理,1. 数据透视表 数据透视表是一种特殊的工作表，它是一种对大量数据快速汇总和建立交叉列表的交互式表格。 数据透视表允许用户使用特殊的、直接的操作来分析数据库或表格中的数据，用户可以扭转和重组页字段、行字段和列字段来修改其布局，从而可以透视数据库或表格中的数据，因此称为透视表。 数据透视表作用： 用户可以在数据透视表中指定想显示的字段和数据项，以确定如何组织数据； 也可以变换行和列来查看源数据的不同汇总结果； 还可以显示不同的页面以筛选

43、数据，甚至还可以根据需要显示区域中的明细数据。,数据透视表与数据透视图,2. 建立数据透视表 由于数据透视表也是一种表格，可是这种表格是建立在Excel数据库或表格之上的。也就是说，建立数据透视表必须有数据库或表格作为数据源。 3. 编辑数据透视表 改变字段的位置 行列字段互换 添加字段 改变数据透视表的汇总方式 数据的格式化 设置公式 刷新数据 数据排序 注意：1、不能直接修改表中显示的数据 2、在编辑数据透视表时，要打开“数据透视表”工具栏,2.4 数据分析工具,1. 单变量求解 即解一元方程，通过改变一个可变单元格中的数值来满足目标单元格中的值。 注意：通常情况下，目标单元格中的数据是以

44、一个与可变单元格相关的计算公式表示的。 2. 模拟运算表 是一种数据表，它可以在工作表中显示因为公式中的某些变量的值发生变化而对计算结果造成的影响。（前面我们已用滚动条初步解决过此类问题） 模拟运算表类型 单变量模拟运算表 双变量模拟运算表,注意： Excel规定，如果可能的取值在同一列中，则含有公式的单元格必须在第一个变量取值的右上角；如果可能的取值在同一行中，则含有公式的单元格必须在第一个变量取值的左下角。 在“模拟运算表”对话框中有两个编辑框，分别为“输入引用的行的单元格”和“输入引用的列的单元格”，依情况而定： 如果变量的一组值在同一行，则在“输入引用的行的单元格”，输入变量第一个取值

45、所在单元格的左边单元格。 如果变量的一组值在同一列，则在“输入引用的列的单元格”，输入变量第一个取值所在单元格的上边单元格。,模拟运算表的操作 课后作业： 编制1515的乘法口诀表（5分钟内完成） 3. 方案分析 建立方案 显示方案 修改、删除或增加方案 建立方案摘要报告 4. 数据分析工具库,第3章 Excel在会计中应用,本章教学要求： 了解账务处理数据的组织，所涉及的所有工作表及其关系 了解账务处理系统中输入的自动化设计 掌握记账凭证的设计制作、科目汇总表和科目余额表的生成 了解会计报表的编制原理 掌握会计报表模型的设计与应用 掌握辅助生产费用分配模型的设计思路,主要内容： 3.1 账务

46、处理 3.2 会计报表 3.3 辅助生产费用分配,第3章 Excel在会计中应用,账务处理流程,教材举例 注意： VLOOKUP函数的使用 下拉按钮的使用,记账凭证的设计制作,注意： Sumif函数的使用 科目余额表中若没有借/贷栏，表中的科目余额如何计算？,科目汇总表和科目余额表的生成,1资产负债表 编制资产负债表的主要数据来源是科目余额表。 注意： “年初余额”数据来源？ “期末余额”数据来源？ 2. 利润表 利润表的“本期金额”栏反映各项目的本期实际发生数，“上期金额”栏反映上年同期实际发生数。利润表的各项目主要根据各损益类科目的本期发生额分析填列。 3. 现金流量表 现金流量表是以现金

47、为基础编制的反映企业财务状况变动的报表，它反映公司或企业一定会计期间内有关现金和现金等价物的流入和流出，表明公司或企业获得现金和现金等价物的能力。 现金流量表的编制方法比较复杂，利用计算机编制现金流量表通常采用的是“从凭证直接登录法”，即逐笔分析会计分录，并作出标志后进行编制的,会计报表的编制原理,辅助生产费用分配,1.【例3-2】假设东方股份有限公司的供电、运输和修理三个辅助生产车间某月发生的费用以及向各受益部门提供的劳务相关资料,2. 辅助生产费用分配模型的建立 “Minverse”函数 “Mmult”函数 3.辅助生产车间以外各受益单位应分配额：,第4章 Excel在财务分析中应用,本章

48、教学要求： 掌握Excel获取数据的方法 掌握比率分析法计算机模型的建立 掌握杜邦分析计算机模型的建立,主要内容： 4.1 Excel 获取外部数据的方法 4.2 比率分析 4.3 杜邦分析,第4章 Excel在财务分析中应用,财务分析流程,财务分析模型 （基于EXCEL ）,获取数据,手工,计算机,会计核算数据源,辅助数据源,分析数据的来源会计报表,资产负债表：了解企业拥有或控制的经济资源及其分布状况；分析企业资产总量及其构成；提供企业财务分析的基本资料，如资金的变现能力、偿债能力和财务实力,利润表：了解企业一定时期的生产经营成果；预测企业的盈利能力，判断企业未来的发展趋势。,现金流量表：可

49、以分析企业一定时期内现金的流入、流出信息；评价企业支付能力、偿债能力和周转能力，从现金流量角度了解净利润的质量，分析企业财务前景。,Excel获取数据的类型,Excel 能够打开的文件类型如下： （注意：不同版本略有不同） Excel自身当前和以前版本的文件。 一般文本文件，即TXT格式文件和用“，”作为数据项分隔符的文本文件，或称CSV格式文件。 Access、SQL Server、Oracle、Dbase等数据库文件。 其他电子表格软件制作的文件，如Lotus1-2-3等。 网页文件，即HTML或HTM文件等。 查询文件，即扩展名为IQY、DQY、OQY和RQY的文件。 举例：获取文本文件数据,1.从外部文本文件（包括txt、csv文件）获取数据 66020105 借吴琼报销差旅费 1250.58 5101020809 借汪鹏报销办公用品 368.22 1002 1234 贷吴琼等报销差旅费和办公用品费 1618.80,获取外部数据的方法,2. 从外部数据库文件获取数据 包括： （1）利用Microsoft Query 从外部数据库中获取数据 （2