EXCEL基础篇.ppt

Excel高级财会应用,清华大学出版社.北京交通大学出版社吕志明 编著,EXCEL基础篇,1、EXCEL简介2、表格设计3、图表制作4、数据管理5、假设分析工具6、窗体工具7、导入数据8、常用函数9、应用举例,一、EXCEL简介（1）,1、EXCEL的启动2、EXCEL的退出3、熟悉EXCEL的窗口4、EXCEL的文件结构,EXCEL简介（2）,5、工作簿操作：新建（CTRL+N）、保存（CTRL+S）、关闭、打开（CTRL+O）6、工作表操作：切换（CTRL+PageDown）、插入、删除、移动、复制、重命名7、打印操作8、工具栏操作9、窗口操作：（1）在不同工作簿窗口间的切换（2）重排窗口（3）隐藏与取消（4）拆分与冻结10、系统选项（1）最近使用的文件列表（2）新工作簿内的工作表数（3）默认文件位置（4）按ENTER键后移动方向（5）是否显示网格线（6）是否显示零值（7）是否保存自动恢复信息（8）密码设置（9）自定义序列,二、表格设计,编辑表格单元格引用单元格、行、列操作设置表格样式绘图工具插入对象保护工作簿和工作表,2.1编辑表格,输入数据1、输入基本数据（1）数值型文本的输入（先输入“”）（2）日期、时间的输入（3）连续在若干单元格输入数据的操作2、数据公式3、输入序列（1）输入数值序列（等差、等比序列）（2）输入日期序列（3）输入文本序列（自定义序列“工具”-“选项”-“新序列”）修改数据选择区域：选择一个单元格、矩形区域、一行或一列、连续的若干行或列、不连续的若干行或列、选择集合区域、选择整张工作表删除数据移动与复制查找与替换,2.2单元格引用,单元格引用方式 1、通过地址引用（相对地址、绝对地址）2、通过名称引用（名称在整个工作簿范围内有效，故同一工作簿内名称不重复）引用同一工作表的单元格（1）直接输入公式“=单元格地址”（2）输入“=”后单击被引用的单元格（3）通过单元格名称引用引用同一工作簿不同工作表的单元格（1）直接输入公式“=工作表名！单元格地址”（2）输入“=”后切换到被引用工作表，单击被引用的单元格（3）通过单元格名称引用引用不同工作簿的单元格（1）直接输入公式“=路径文件名工作表名！单元格地址”（2）同时打开两个工作簿，通过鼠标单击选择在同一工作簿的引用，源单元格值的改变回立即导致目标单元格值的改变。而在不同工作簿中的引用，只有在源工作簿和目标工作簿都处于打开状态时，目标单元格才会与源单元格保持同步。另外，当打开目标工作簿时，如果源工作簿处于打开状态，目标单元格回自动更新；若处于打开状态，可根据弹出的对话框决定是否更新。,2.3单元格、行、列操作,插入与删除单元格合并与 取消单元格设置行高和列宽插入与删除行、列隐藏和取消隐藏行、列设置表格样式设置条件格式绘图工具的使用插入对象（批注、图片、公式）,2.4保护工作簿和工作表,保护工作表（1）设置单元格的锁定状态（2）输入保护密码允许用户编辑区域保护工作簿,三、图表制作,图表类型(见例题1.1)图表的制作和编辑1、柱形图：用于反映不同序列的差异或者是各序列的变化。2、条形图：可以看作是柱形图的90度旋转，纵轴表示分类，横轴表示数值，更适用与数据间的比较分析。3、折线图：将同一序列的数值在图表中的坐标点连接成一条直线，因而更适合反映数据的变化趋势，此时X轴一般是时间轴。4、饼图：只能显示一个序列，用于反映部分占总体的比例情况。5、XY散点图：X轴和Y轴一般都表示数值，用若干不同颜色的点代表不同的序列，适合反映不连续的数据。6、面积图：将每一序列的数值用线段连起来，并将每条线以下的区域用不同颜色填充，显示的是各个序列图形的叠加。与折线图相比，面积图更强调变化幅度，同时可以反映部分与整体的关系，而折线图更强调变化速度。7、圆环图：用于反映部分和整体的关系，但与饼图不同的是，饼图只能有一个序列，而圆环图可以反映多个序列，每一个序列分别用一个圆环表示。8、雷达图：在雷达图上每一种分类的 数据都有它自己的数值轴，各从中心点放射出一条线，各条线上每个序列的点用折线连接起来构成面积，而多个序列的图叠加在一起形状就象雷达，一般更适合表示序列的整体值，而不是各个数据点的情况。9、气泡图：实际上也是一种XY散点图，只不过用气泡表示数据标记，其大小反映了第三变量的大小。,四、数据管理,数据有效性（见例题1.2）记录单排序筛选高级筛选条件的设置在进行高级筛选之前，先要在被筛选数据区之外设置筛选条件，注意筛选条件与数据区域不能直接相连，要空有若干行或列。注意同一行的条件之间是“并且”关系，而不同行的条件之间是“或者”关系。分类汇总数据透视表和数据透视图利用数据透视表和透视图可以对数据进行全方位、多角度的汇总和对比分析。数据透视表是交互式报表，可快速合并和比较大量数据。数据透视表和数据透视图是连动的，在数据透视表中设置的页字段和行字段、列字段的筛选条件自动传递到数据透视图，反之亦然。,分类汇总,若要插入分类汇总，请先将列表排序，以便将要进行分类汇总的行组合到一起。然后，为包含数字的列计算分类汇总。外部分类汇总嵌套分类汇总（清除“替换当前分类汇总”复选框）用图表形式表示分类汇总数据,数据透视表,简介数据透视表可以从某字段列表交互式地创建和构造交叉表报表。这些字段可继承自另一个工作表、某个SQL或ORACLE数据库、文本文件、OLAP多为数据集或某些其它外部数据源。创建好数据透视表形状后，用户可把字段移动到报表中的不同的位置上、修改汇总类型、运用筛选来确定显示报表字段中的那些项以及运用基于报表的汇总值或其它字段的复杂排序。使用数据透视表，报表用户可完成：生成许多不同的视图和报表把字段移动到报表中的不同位置上确定显示哪一个字段用各种方法对数值型字段进行汇总用筛选来控制显示字段中的哪些值 挖掘展示数值型数据的底层数据集从单个数据透视表创建多个报表,五、假设分析工具,单变量求解如果已知单个公式的预期结果，而用于确定此公式结果的输入值未知，则可使用单变量求解功能，可理解为已知因变量Y反算单个自变量X。方案方案是保存在工作表中并可自动替换的一组值。拥护可以使用方案来预测工作表模型的输出结果。同时还可以在工作表中创建并保存不同的数值组，然后切换到任意新方案以查看不同的结果或通过创建方案汇总报告来对比方案。模拟运算表主要用于分析两个自变量不同取值组合下因变量的值，自然也可以分析单变量取不同值时因变量的值。规划求解在经济决策当中，我们经常会遇到一些求解最优解问题，如在什么情况下产品利润最大、在什么现金持有量水平下持有现金的总成本最低、在什么定货批量下存货总成本最低等。这些求解最优解的问题，使用规划求解工具，只要我们输入基本数据，并作简单的设置，很快就会把最优解及其相应条件值计算出来。,PMT函数,语法：PMT(rate,nper,pv,fv,type)功能：基于固定利率及等额分期付款方式，返回贷款的每期付款额。Rate 贷款利率。Nper 该项贷款的付款总期数。Pv 现值，或一系列未来付款的当前值的累积和，也称为本金。Fv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。Type 值支付时间0 或省略为期末支付，1为期初支付PMT 返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12%的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。如果要计算贷款期间的支付总额，请用 PMT 返回值乘以 nper。,单变量求解,“单变量求解”是一组命令的组成部分，这些命令有时也称作假设分析工具。如果已知单个公式的预期结果，而用于确定此公式结果的输入值未知，则可使用“单变量求解”功能，通过单击“工具”菜单上的“单变量求解”即可使用“单变量求解”功能。当进行单变量求解时，Microsoft Excel 会不断改变特定单元格中的值，直到依赖于此单元格的公式返回所需的结果为止。,关于数据表,数据表是一组命令的组成部分，这些命令有时也称作假设分析工具。数据表是一个单元格区域，用于显示公式中某些值的更改对公式结果的影响。数据表提供了一种快捷手段，它可以通过一步操作计算出多种情况下的值；同时它还是一种有效的方法，可以查看和比较由工作表中不同变化所引起的各种结果。单变量数据表 例如，用户可以使用单变量数据表来显示不同利率对购房贷款月还款的影响。双变量数据表 例如，双变量数据表可显示不同利率和贷款期限对购房贷款分期付款的影响。,单变量数据表,例如，用户可以使用单变量数据表来显示不同利率对购房贷款月还款的影响。,单创建单变量数据表,创建单变量数据表1、所设计的单变量数据表应当为：其输入数值被排列在一列中（列方向）或一行中（行方向）。单变量数据表中使用的公式必须引用输入单元格。2、在一列或一行中，键入要替换工作表上的输入单元格的数值序列。如果数据表是列方向的，请在第一个数值的上一行且位于数值列右边的单元格中键入公式。在第一个公式的右边，可分别键入其他公式。如果数据表是行方向的，请在第一个数值左边一列且位于数值行下方的单元格中键入公式。在第一个公式的下方，可分别键入其他公式。3、选定包含公式和需要被替换的数值的单元格区域。4、在“数据”菜单上，单击“模拟运算表”。如果数据表是列方向的，请在“输入引用列的单元格”框中，为输入单元格键入单元格引用。如果数据表是行方向的，请在“输入引用行的单元格”框中，为输入单元格键入单元格引用。将数据表的计算结果转换为常量在数据表中选中所有计算结果。单击“常用”工具栏上的“复制”，并选中粘贴区域的左上角单元格。单击“粘贴”旁的箭头，再单击“值”。（注释由于计算结果为数组的形式，所以必须将所有值转换为常量。）,双变量数据表,例如，双变量数据表可显示不同利率和贷款期限对购房贷款分期付款的影响。,创建双变量数据表,创建双变量数据表1、双变量数据表中的两组输入数值使用同一个公式。这个公式必须引用两个不同的输入单元格。2、在工作表的某个单元格内，输入引用了两个输入单元格的公式。3、在公式下方的同一列中键入一列输入值。4、在公式右边的同一行中键入第二列输入值。5、选定包含公式以及数值行和列的单元格区域。在“数据”菜单上，单击“模拟运算表”。6、在“输入引用行的单元格”框中，请输入由行数值替换的输入单元格的引用。7、在“输入引用列的单元格”框中，请输入由列数值替换的输入单元格的引用。单击“确定”。,规划求解概述,借助规划求解，可求得工作表上某个单元格（目标单元格）中公式的最优值。规划求解将对直接或间接与目标单元格中公式相关联的一组单元格（可变单元格）中的数值进行调整，最终在目标单元格公式中求得期望的结果。规划求解通过调整可变单元格中的值，从目标单元格公式中求得所需的结果。在求解过程中，可以为可变单元格设置约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。这些求解最优解的问题，大致可以分为两类：线性问题和非线性问题。所谓线性优化问题是指问题所涉及的各要素之间的关系是线性的，一般只是一些加、减运算；非线性优化问题是指问题所涉及的各要素之间的关系是非线性的，如各要素之间存在相乘、相除或指数运算关系。规划求解的参数设置1、目标单元格：是希望求解其最优解的单元格。在一般情况下，目标单元格当中应当含有与可变单元格有关的公式，目标单元格与可变单元格通过公式联系在一起，因而才能求得目标单元格的最优解以及各可变单元格。2、可变单元格：是影响目标单元格值的那些决策变量所在的单元格，这些单元格的值是可调的。规划求解正式通过不断调整各可变单元格的值来逐步找出目标单元格的最优解的。3、约束条件：在规划求解工具中，可以设置各种约束条件，约束条件可以针对任何单元格进行，但被引用的单元格往往与可变单元格存在直接或间接的函数关系。,六、窗体工具,窗体工具简介窗体工具应用,七、导入数据,从数据库导入数据从WEB页导入数据,八、常用函数,日期与时间函数文本函数数学和三角函数统计函数逻辑函数查询和引用函数数据库函数,8.1日期与时间函数,1、NOW（）：返回当前系统日期和时间 2、TODAY（）：返回当前系统日期 3、YEAR(serial_number)：返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。Serial_number 为一个日期值。4、MONTH(serial_number)：返回以序列号表示的日期中的月份。月份是介于 1（一月）到 12（十二月）之间的整数。Serial_number 表示一个日期值5、DAY(serial_number)返回以序列号表示的某日期的天数，用整数 1 到 31 表示。Serial_number 为要查找的那一天的日期6、WEEKDAY(serial_number,return_type)：返回某日期为星期几。默认情况下，其值为 1（星期天）到 7（星期六）之间的整数。Return_type返回的数字1 或省略：数字 1（星期日）到数字 7（星期六），同 Microsoft Excel 早期版本。2：数字1（星期一）到数字 7（星期日）。3：数字 0（星期一）到数字 6（星期日）。7、HOUR(serial_number)：返回时间值的小时数。即一个介于 0(12:00 A.M.)到 23(11:00 P.M.)之间的整数。Serial_number 表示一个时间值。8、MINUTE(serial_number)：返回时间值中的分钟，为一个介于 0 到 59 之间的整数。9、SECOND(serial_number)：返回时间值的秒数。返回的秒数为 0 到 59 之间的整数。10、DATE(year,month,day)：返回代表特定日期的序列。11、TIME(hour,minute,second)：返回某一特定时间。12、NETWORKDAYS(start_date,end_date,holidays)：返回参数 start-data 和 end-data 之间完整的工作日数值。工作日不包括周末和专门指定的假期。,8.2文本函数,1、LOWER(text)：将一个文本字符串中的所有大写字母转换为小写字母。2、UPPER(text)：将文本转换成大写形式。3、LEFT(text,num_chars)：基于所指定的字符数返回文本字符串中的第一个或前几个字符。4、RIGHT（text,num_chars)：根据所指定的字符数返回文本字符串中最后一个或多个字符。5、MID(text,start_num,num_chars)返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。6、TRIM(text)：除了单词之间的单个空格外，清除文本中所有的空格。7、LEN(text)：返回文本字符串中的字符数。8、FIND(find_text,within_text,start_num)：用于查找其他文本字符串(within_text)内的文本字符串(find_text)，并从 start_num字符开始返回 find_text 的起始位置。9、REPLACE(old_text,start_num,num_chars,new_text)：使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。Old_text 是要替换其部分字符的文本。Start_num 是要用 new_text 替换的 old_text 中字符的位置。Num_chars 是希望 REPLACE 使用 new_text 替换 old_text 中字符的个数。New_text 是要用于替换 old_text 中字符的文本。,8.3数学和三角函数（1）,1、ABS(number):取绝对值2、FACT(number):求阶乘3、INT(number):取整4、ROUND(number,num_digits):四舍五入5、LOG(number,base):根据给定基数base返回给定参数值number的对数6、POWER(number,power):返回给定参数值number的power次幂7、SORT(number):求平方根8、MOD(number,divisor):取余数9、RAND():返回大于0小于1的平均分布随机数。10、SUM(number1,number2):求和。最多30个。,数学和三角函数（2）,11、SUMIF(range,criteria,sum_range),Range 为用于条件判断的单元格区域。Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、32、32 或 apples。Sum_range 是需要求和的实际单元格。只有在区域中相应的单元格符合条件的情况下，sum_range 中的单元格才求和。如果忽略了 sum_range，则对区域中的单元格求和。,数学和三角函数（3）,1、SIN（number）:返回给定弧度的正弦值2、COS（number）:返回给定弧度的余弦值3、TAN（number）:返回给定弧度的正切值4、ASIN（number）:返回参数的反正弦值5、ACOS（number）:返回参数的反余弦值6、ATAN（number）:返回参数的反正切值,8.4统计函数(1),1、AVERAGE(number1,number2,.)Number1,number2,.为需要计算平均值的 1 到 30 个参数。参数可以是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值被忽略；但包含零值的单元格将计算在内。2、COUNT(value1,value2,.)返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。Value1,value2,.为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。函数 COUNT 在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。如果要统计逻辑值、文字或错误值，请使用函数 COUNTA。,统计函数(2),3、COUNTIF(range,criteria)计算区域中满足给定条件的单元格的个数。Range 为需要计算其中满足条件的单元格数目的单元格区域。Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32”、“32”或“apples”。4、MAX(number1,number2,.)：返回一组值中的最大值。Number1,number2,.是要从中找出最大值的 1 到 30 个数字参数。可以将参数指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误。如果参数为数组或引用，则只有数组或引用中的数字将被计算。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果逻辑值和文本不能忽略，请使用函数 MAXA 来代替。如果参数不包含数字，函数 MAX 返回 0（零）。5、MIN(number1,number2,.)：返回一组值中的最小值。,统计函数(3),6、MODE(number1,number2,.)：返回在某一数组或数据区域中出现频率最多的数值。同 MEDIAN 一样，MODE 也是一个位置测量函数。Number1,number2,.是用于众数计算的 1 到 30 个参数，也可以使用单一数组（即对数组区域的引用）来代替由逗号分隔的参数。参数可以是数字，或者是包含数字的名称、数组或引用。如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。如果数据集合中不含有重复的数据，则 MODE 数返回错误值 N/A。在一组数值中，众数是出现频率最高的数值，而中位数是位于中间的值，平均数是平均后的值，所有这些求中函数都不能单独地完全描绘所有数据。例如，假设数据分布在三个区域中，其中一半分布在一个较小数值区中，另外一半分布在两个较大数值区中。函数 AVERAGE 和函数 MEDIAN 可能会返回位于数据点稀疏处的中间值；而函数 MODE 则会返回位于数据点密集处的较小值。,统计函数(4),7、LARGE(array,k)：返回数据集中第 k 个最大值。使用此函数可以根据相对标准来选择数值。例如，可以使用函数 LARGE 得到第一名、第二名或第三名的得分。Array 为需要从中选择第 k 个最大值的数组或数据区域。K 为返回值在数组或数据单元格区域中的位置（从大到小排）。如果数组为空，函数 LARGE 返回错误值#NUM!。如果 k 0 或 k 大于数据点的个数，函数 LARGE 返回错误值#NUM!。如果区域中数据点的个数为 n，则函数 LARGE(array,1)返回最大值，函数 LARGE(array,n)返回最小值。8、SMALL(array,k)：返回数据集中第 k 个最小值。使用此函数可以返回数据集中特定位置上的数值。,8.5逻辑函数（1）,1、AND(logical1,logical2,.)：所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。Logical1,logical2,.表示待检测的 1 到 30 个条件值，各条件值可为 TRUE 或 FALSE。参数必须是逻辑值 TRUE 或 FALSE,或者包含逻辑值的数组或引用。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的单元格区域内包括非逻辑值，则 AND 将返回错误值#VALUE!。2、OR(logical1,logical2,.)：在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。Logical1,logical2,.为需要进行检验的 1 到 30 个条件，分别为 TRUE 或 ALSE。参数必须能计算为逻辑值，如 TRUE 或 FALSE，或者为包含逻辑值的数组或引用。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的区域中不包含逻辑值，函数 OR 返回错误值#VALUE!。可以使用 OR 数组公式来检验数组中是否包含特定的数值。若要输入数组公式，请按 Ctrl+Shift+Enter。,逻辑函数（2）,1、NOT(logical)：对参数值求反。当要确保一个值不等于某一特定值时，可以使用 NOT 函数。2、IF(logical_test,value_if_true,value_if_false)：执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数 IF 对数值和公式进行条件检测。Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式Value_if_true logical_test 为 TRUE 时返回的值。如果 logical_test 为 TRUE 而 value_if_true 为空，则本参数返回 0（零）。如果要显示 TRUE，则请为本参数使用逻辑值 TRUE。Value_if_true 也可以是其他公式。Value_if_false logical_test 为 FALSE 时返回的值。如果 logical_test 为 FALSE 且忽略了 Value_if_false（即 value_if_true 后没有逗号），则会返回逻辑值 FALSE。如果 logical_test 为 FALSE 且 Value_if_false 为空（即 value_if_true 后有逗号，并紧跟着右括号），则本参数返回 0（零）。Value_if_false 也可以是其他公式。如果函数 IF 的参数包含数组，则在执行 IF 语句时，数组中的每一个元素都将计算。,8.6查询和引用函数（1）,1、INDEX函数数组形式：INDEX(array,row_num,column_num)Array 为单元格区域或数组常量。如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选。如果数组有多行和多列，但只使用 row_num 或 column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。Row_num 数组中某行的行序号，函数从该行返回数值。如果省略 row_num，则必须有 column_num。Column_num 数组中某列的列序号，函数从该列返回数值。如果省略 column_num，则必须有 row_num。如果同时使用 row_num 和 column_num，函数 INDEX 返回 row_num 和 column_num 交叉处的单元格的数值。如果将 row_num 或 column_num 设置为 0，函数 INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以数组公式形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，请按 Ctrl+Shift+Enter。Row_num 和 column_num 必须指向 array 中的某一单元格；否则，函数 INDEX 返回错误值#REF!。,查询和引用函数（2）,引用形式：INDEX(reference,row_num,column_num,area_num)Reference 对一个或多个单元格区域的引用。如果为引用输入一个不连续的区域，必须用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数 row_num 或 column_num 分别为可选项。Row_num 引用中某行的行序号，函数从该行返回一个引用。Column_num 引用中某列的列序号，函数从该列返回一个引用。Area_num 选择引用中的一个区域，并返回该区域中 row_num 和 column_num 的交叉区域。选中或输入的第一个区域序号为 1，第二个为 2，以此类推。如果省略 area_num，函数 INDEX 使用区域 1。在通过 reference 和 area_num 选择了特定的区域后，row_num 和 column_num 将进一步选择指定的单元格：row_num 1 为区域的首行，column_num 1 为首列，以此类推。函数 INDEX 返回的引用即为 row_num 和 column_num 的交叉区域。如果将 row_num 或 column_num 设置为 0，函数 分别返回对整个列或行的引用。Row_num、column_num 和 area_num 必须指向 reference 中的单元格；否则，函数 INDEX 返回错误值#REF!。如果省略 row_num 和 column_num，函数 INDEX 返回由 area_num 所指定的区域。函数 INDEX 的结果为一个引用，且在其他公式中也被解释为引用。根据公式的需要，函数 INDEX 的返回值可以作为引用或是数值。,查询和引用函数（3）,OFFSET(reference,rows,cols,height,width)：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值#VALUE!。Rows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height 高度，即所要返回的引用区域的行数。Height 必须为正数。Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值#REF!。如果省略 height 或 width，则假设其高度或宽度与 reference 相同。函数 OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数 OFFSET 可用于任何需要将引用作为参数的函数。例如，公式 SUM(OFFSET(C2,1,2,3,1)将计算比单元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的区域的总值。,查询和引用函数（4）,2、VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP 代替函数 HLOOKUP。Lookup_value 为需要在数组第一列中查找的数值。可以为数值、引用或文本字符串。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列；否则，函数 VLOOKUP 不能返回正确的数值。如果 为 FALSE，table_array 不必进行排序。Table_array 的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回 table_array 第一列中的数值；col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num 小于 1或大于 table_array 的列数，函数 VLOOKUP 返回错误值#REF!。Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。如果函数 VLOOKUP 找不到 lookup_value，且 range_lookup 为 TRUE，则使用小于等于 lookup_value 的最大值。如果 lookup_value 小于 table_array 第一列中的最小数值，函数 VLOOKUP 返回错误值#N/A。如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE，函数 VLOOKUP 返回错误值#N/A。,RANK,RANK(number,ref,order)返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。Number 为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。函数 RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。由于某些原因，用户可能使用考虑重复数字的排位定义。在前面的示例中，用户可能要将整数 10 的排位改为 5.5。这可通过将下列修正因素添加到按排位返回的值来实现。该修正因素对于按照升序计算排位（顺序=非零值）或按照降序计算排位（顺序=0 或被忽略）的情况都是正确的。重复数排位的修正因素=COUNT(ref)+1 RANK(number,ref,0)RANK(number,ref,1)/2。在下列示例中，RANK(A2,A1:A5,1)等于 3。修正因素是(5+1 2 3)/2=0.5，考虑重复数排位的修改排位是 3+0.5=3.5。如果数字仅在 ref 出现一次，由于不必调整 RANK，因此修正因素为 0。,财务管理篇,第2章 财务分析模型第3章 投资决策分析模型设计第4章 流动资金模型设计第5章 筹资分析与决策模型设计第6章 销售与财务预测模型设计第7章 利润规划模型设计第8章 财务预算模型设计第9章 计算机财务管理系统的建立,第二章 财务分析模型设计,2.1 财务分析概述财务分析-财务报表分析一、财务分析目的 1、评价企业的财务状况、经营成果2、评价企业的资产管理水平3、评价企业的获利能力4、评价企业的发展趋势二、财务报表分析的方法（一）比较分析1、比率分析-报表有关项目进行对比2、趋势分析本期与以前不同期对比3、综合分析将各种指标放在一起进行综合分析(杜邦指标体系)（二）因素分析 1、差额分析法2、指标分析法3、连环替代法4、定基替代法,2.2财务分析数据准备,财务分析数据的获取、会计核算数据A.数据源BASE 数据库 SQL SERVER 数据库ACCESS数据库 RACLE 数据库 B.获取方法：（1）直接在EXCEL中编制财务报表（2）利用财务软件提供的数据接口直接生成EXCEL报表（3）利用EXCEL的Microsoft Query功能从财务软件数据库中获取科目的发生额及余额信息，然后再利用单元格连接生成财务报表。2、辅助分析数据通过查阅杂志、报刊，输入模型。,2.3 财务比率模型设计,一、比率分析主要指标二、比率分析模型建立1、从外部获取数据库利用Microsoft Query(1)新建工作簿，重名名工作表准备接收总帐数据(2)执行如下菜单命令数据获取外部数据新建数据库查询2、应用动态链接技术，完成财务报表数据的采集 3、比率分析模型设计 4、数据更新,数据动态链接技术的应用,1、尽量应用名字优点如下（1）更加容易阅读、理解和记忆（2）每个工作簿可以共用一组名字（3）公式定义不受工作表结构变化的影响2、链接的形式（1）同一工作表内链接公式 C3的公式=C1+C2（2）工作表之间链接公式 C3的公式=sheet1!c1+sheet1!c2（3）立体表数据链接 sum(sheet1:sheet5!b17,sheet1:sheet5!c17),第三节 趋势分析模型的设计方法一、趋势分析的基本方法1、多期比较财务报表分析2、结构百分比分析3、定基百分比趋势分析4、图解法分析二、趋势分析模型的设计1、各年历史数据的获得2、绘制趋势分析图,第四节 综合分析模型的设计方法一、财务比率综合分析法 杜邦分析法二、杜邦分析模型建立1、选择工作表2、设计分析框3、分析框中数据链接的定义4、关掉工作表的网格线5、杜邦系统图的缩放,上机作业1、建立一个ACCESS数据库“科目余额”，结构如109页。2、通过UFO报表另存建立两个工作簿，名字任意。3、建立CW01工作簿，插入3张表页：“帐簿”从“科目余额”库获取数据“资产负债表”除“帐簿”表页提供的数据外，自行填写数 据和公式。“利润表”自行填写数据和公式“财务分析”计算指标：流动比率、速动比率、资产负债率、流动资产周转率、产权比率销售净利率4、改变