1、第1章 财务建模技术Excel操作精要,【本章重点】,单元格绝对引用、相对引用、混合引用及跨工作表引用 统计函数、逻辑函数、查找引用函数、数据库函数及财务函数 柱形图、折线图、饼图、XY散点图等常用图表的制作 排序、筛选、分类汇总、数据透视表与报表汇总 单变量求解、模拟运算、规划求解与方案管理 滚动条、微调钮、组合框等窗体工具 工作簿和工作表的保护,1.1 计算机财务建模概述,1.2 电子表格软件简介,1.3 公式与函数,1.4 图表制作,本章目录,【课后习题】,1.5 数据管理,1.6 假设分析,1.7 窗体工具,1.8 数据保护,目录,本节,1.1 计算机财务建模概述,1.1.1 计算机财
2、务建模的必要性,1.1.2 计算机财务建模的定位,1.1.3 计算机财务建模的应循程序 1.需求分析 2.可行性分析 3.资料准备 4.财务建模 5.模型的使用与动态调整,目录,本节,1.2电子表格软件简介,1.2.1 常用的电子表格软件,1.2.2 电子表格软件的主要功能,1.工作簿管理 2.管理数据 3.数据共享 4.制作图表 5.开发应用系统,目录,本节,1.3 公式与函数,1.3.1公式,1.公式的概念及其构成,2.公式的创建与修改 (1)公式的创建 (2)公式的修改 3.公式的运算次序 4.公式运算结果的显示 查看公式中某步骤的运算结果：【F9】,目录,本节,1.3 公式与函数,(2
3、)公式表达式与运算结果之前的切换：【Ctrl+】,(3)将公式运算结果转换为常数：选择性粘贴,1.3.2单元格引用 1.引用的类型 相对引用 绝对引用 混合引用 2.输入单元格引用:输入“$”或按【F4】,【例1-1】,目录,本节,1.3 公式与函数,3.跨工作表单元格引用 被引用工作表名称！单元格引用,4.跨工作簿单元格引用 工作簿名称工作表名称！单元格引用 当源工作簿关闭时：工作簿所在路径工作簿名称工作表名称!单元格引用 1.3.3函数应用 1.Excel函数简介,(1)函数名称（参数1，参数2，参数n） (2)手工输入或利用函数向导 统计函数 MAX(number1,number2,nu
4、mber30) MIN(number1,number2,number30) SUM(number1,number2,number30) SUMIF(range,criteria,sum_range),目录,本节,1.3 公式与函数,【例1-2】,AVERAGE(number1,number2,number30) AVERAGEIF(range,criteria,average_range) COUNT(value1,value2,value30) COUNTIF(range,criteria) MODE(number1, number2,number30) LARGE(array,k)、SMA
5、LL(array,k),目录,本节,1.3 公式与函数,【例1-3】,文本函数 LEN(text) RIGHT(text,num_chars) LEFT(text,num_chars) MID(text,start_num,num_chars) LOWER(text) UPPER(text) TRIM(text) REPLACE(old_text,start_num,num_chars,new_text),目录,本节,1.3 公式与函数,逻辑函数 IF(logical _ test, value _ if_ true, value _ if _false),目录,本节,1.3 公式与函数,【例
6、1-4】,AND(logical1,logical2,logical30) OR(logical1,logical2,logical30) NOT(logical),查找与引用函数 LOOKUP 向量形式： LOOKUP（lookup_value,lookup_vector,result_vector） 用于在单行区域或单列区域中查找值，然后返回第二个单行区域或单列区域中相同位置的值。 数组形式：LOOKUP（lookup_value,array），用于在数组的第一行或第一列中查找指定的值，并返回数组最后一行或最后一列内同一位置的值。,目录,本节,1.3 公式与函数,VLOOKUP(looku
7、p_value,table_array,col_index_num, range_lookup),目录,本节,1.3 公式与函数,【例1-5】,INDEX(array,row_num,column_num),【例1-6】,MATCH(lookup_value,lookup_array,match_type) MATCH函数用于返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果 match_type 为 1，查找小于或等于 lookup_value 的最大数值，此时Lookup_array 必须按升序排列；如果 match_type 为 0，查找等于 lookup_value 的第一个数
8、值，此时Lookup_array 可以按任何顺序排列；如果 match_type 为 -1，查找大于或等于 lookup_value 的最小数值，此时Lookup_array 必须按降序排列，如果省略match_type，则假设为 1。,目录,本节,1.3 公式与函数,另外，查找文本值时，函数 MATCH 不区分大小写字母，如果 match_type 为 0 且 lookup_value 为文本，lookup_value 可以包含通配符“*” 和“?”，其中星号可以匹配任何字符序列，问号可以匹配单个字符。如果函数 MATCH 查找不成功，则返回错误值 #N/A。 OFFSET(referenc
9、e,rows,cols,height,width) 该函数以reference为参照系，通过给定偏移量而得到一个新的引用。,目录,本节,1.3 公式与函数,数据库函数 DAVERAGE(database,field,criteria) 该函数用于返回列表或数据清单中满足指定条件的列中数值的平均值。其中，参数database用于指定构成数据清单或数据库的单元格区域，它由若干行和若干列组成，其中第一行包含着每一列的标志项；field用于指定函数所要使用的数据列，可以是表示数据列名称的字符串，也可以是表示数据列在数据清单中的位置的数字；criteria用于指定包含特定条件的单元格区域，至少包含一个列
10、标志项和列标志项下方用于设定条件的单元格。,目录,本节,1.3 公式与函数,DSUM(database,field,criteria) DCOUNT(database,field,criteria) DMAX(database,field,criteria) DMIN(database,field,criteria) DGET(database,field,criteria),目录,本节,1.3 公式与函数,【例1-7】,日期与时间函数 NOW() TODAY() YEAR(serial_number) MONTH(serial_number) DAY(serial_number) WEEKD
11、AY(serial_number,return_type),目录,本节,1.3 公式与函数,该函数用于返回指定日期是一周中的第几天，返回值介于17或06之间。return_type取值范围如下：设为1 或省略，返回值 17，表示从星期日到星期六；设为2，返回值17，表示从星期一到星期日；设为3，返回值为06，表示从星期一到星期日。例如，输入公式“=weekday(today(),2)”,返回当前系统日期是星期几；输入公式“=weekday(date(2013,1,1),2)”将返回2，表示2013-1-1是星期二。,目录,本节,1.3 公式与函数,HOUR(serial_number) MIN
12、UTE(serial_number) HOUR(serial_number) TIME(hour，minute,second) DATE(year，month,day) DATEDIF(start_date,end_date,unit) 该函数用于返回起始日期和结束日期之间的天数、月数或年数之差。unit代表比较单位，可以取“y”、“m”、“d”，分别代表年、月、日。,目录,本节,1.3 公式与函数,数学和三角函数 ABS(number) FACT(number) INT(number) ROUND(number,num_digits) LOG(number,base) POWER(numbe
13、r,power) SQRT(number) MOD(number,divisor),目录,本节,1.3 公式与函数,RAND() 该函数用于返回大于等于0且小于等于1的平均分布随机数，每次计算工作表时都将返回一个新的随机数。例如，想得到一个1-100之间的随机数，可以定义公式“=1+rand()*(100-1)”。 SUMPRODUCT(array1,array2,array3, .) 该函数返回参数指定数组对应元素乘积的和，其中array1、array2、 array3 . 为 2 到 30 个数组。 3.基本财务函数,目录,本节,1.3 公式与函数,SLN（cost，salvage，lif
14、e） 该函数为直线法折旧计算函数。其中，参数cost为固定资产原值，salvage为预计净残值，life为预计使用期限。 DDB（cost，salvage，life，per，factor） 该函数为双倍余额递减法折旧计算函数。其中，参数cost为固定资产原值，salvage为预计净残值，life为预计使用期限，per指定要计提第几期的折旧,factor为倍率，如果缺省将取2，即双倍余额递减法。,目录,本节,1.3 公式与函数,SYD（cost，salvage，life，per） 该函数为年数总和法折旧计算函数。其中，参数cost为固定资产原值，salvage为预计净残值，life为预计使用期限
15、，per指定要计提第几期的折旧。,目录,本节,1.3 公式与函数,【例1-8】,VDB（cost，salvage，life，start_period,end_per iod,factor, no_switch）,该函数为倍率递减法折旧计算公式。start_period、end_period分别为要计算折旧的起始期间；factor为倍率，如果缺省将取2，即双倍余额递减法；no_switch 为FALSE或缺省时，当使用倍率余额递减法计算的折旧小于平均年限法计算的折旧时，函数会转换为平均年限法计算剩余期间的折旧额，no_switch为TRUE时，即使倍率余额递减法计算的折旧已小于直线法计算的折旧，
16、函数仍按倍率余额递减法计算折旧，带有no_switch参数时，factor参数不能省略。,目录,本节,1.3 公式与函数,【例1-9】,NPV（rate,value1,value2,） 此函数为净现值函数。参数rate是贴现率，value1、value2是未来各期的现金流量。使用该函数时，应该注意以下两点：一是参数value1、value2分别代表未来第1期、第2期的期末现金流量，初始投资不应出现在参数中，计算项目的净现值时，只要计算未来各期的NPV与初始投资之差即可；二是参数value1、value2的个数应在129之间。每个参数可以是数组或地址区域形式，但数组的元素个数不受此个数限制。,目
17、录,本节,1.3 公式与函数,IRR（values,guess） 此函数为内含报酬率函数。其中，参数values是连续期间的现金流量，guess是用户所猜想的接近IRR结果的数值，一般可以省略。使用该函数时应该注意以下两点：一是参数values必须是数组或地址区域，并且必须包含至少一个正数（现金流入）和负数（现金流出）；二是values中的数据的先后顺序代表了现金流量的期间顺序，并且初始投资应作为现金流出出现在values中第一个数据位置。,目录,本节,1.3 公式与函数,【例1-10】,FV（rate，nper，pmt，pv，type） 该函数为终值函数，可返回基于固定利率的、等额分期付款方
18、式的某项投资的未来值，即年金的终值。其中，参数rate是复利利率；nper是年金的期数；pmt是每期收支的金额，即年金，当pmt为负数时，函数结果为正，当pmt为正数时，函数结果为负；pv是指投资开始计算时已经入账的价值，缺省值为0；type是年金类型，当取1时表示预付年金，当取0或缺省时表示普通年金。如果缺省参数pmt，而给出pv参数，则相当于计算普通复利终值。,目录,本节,1.3 公式与函数, PV（rate，nper，pmt，fv，type） 该函数为现值函数，可返回某项投资的一系列等额分期偿还额的当前值之和，即年金的现值。其中，参数rate是复利利率；nper是年金的期数；pmt是每期
19、收支的金额，即年金，当pmt为负数时，函数结果为正，当pmt为正数时，函数结果为负；fv是在最后一次付款期后获得的一次性偿还额，缺省值为0；type是年金类型，当取1时表示预付年金，当取0或缺省时表示普通年金。如果缺省参数pmt，而给出fv参数，则相当于计算普通复利现值。,目录,本节,1.3 公式与函数,PMT（rate，nper，pv，fv，type） 此函数为年金函数，可返回固定利率下投资或贷款的等额分期偿还额，即年金，包括本金和利息。 PPMT（rate，per，nper，pv，fv，type） 此函数为年金本金函数，可返回在固定利率、期数下某项投资回报或贷款偿还的本金部分。 IPMT（
20、rate，per，nper，pv，fv，type） 此函数为年金利息函数，可返回在固定利率、期数下某项投资回报或贷款偿还的利息部分。PMT()=PPMT()+IPMT()。,目录,本节,1.3 公式与函数,NPER（rate，pmt，pv，fv，type） 此函数为期数函数，可返回每期付款金额及利率固定的某项投资或贷款的期数。例如，NPER（10%，100，248.69）=3 RATE（nper，pmt，pv，fv，type，guess） 此函数为利率函数，在已知期数、每期付款及现值或终值的条件下，返回年金的每期利率。其中，参数guess是对利率的猜测数，如果缺省，将假定为10%，如果RATE
21、函数无法收敛，应该给出不同的guess重新计算。,目录,本节,1.3 公式与函数,【例1-11】,【例1-12】,【例1-13】,【例1-14】,1.4.1图表类型 1.柱形图 2.条形图 3.折线图 4.饼图 5.圆环图 6.xy散点图 7.面积图 8.雷达图 9.气泡图,目录,本节,1.4 图表制作,1.4.2制作图表 在Excel2003及以下版本中制作图表时，先选中数据源，然后单击菜单【插入|图表】，按照图表向导完成图表的制作。 在Excel 2007及以上版本中制作图表时，先选中数据源，然后在“插入”选项卡中单击各类图表按钮，即可快速生成图表。,目录,本节,1.4 图表制作,图表生成
22、后，图表区、绘图区、水平轴、垂直轴、系列、网格线、图例、图表标题、坐标轴标题、数据标签、数据表等各种图表要素都可以根据需要单独进行更改设置，此时，只需在相应要素上点击鼠标右键，在快捷菜单选择相应命令即可。 在Excel 2007及以上版本中，当用鼠标点击图表后，功能区将自动显示“设计”、“布局”、“格式”等选项卡，用于执行图表的各项设置功能。,目录,本节,1.4 图表制作,【例1-15】,1.5.1数据清单 1.数据清单的概念 在Excel中，数据库是通过数据清单或列表来实现的。数据清单是一种包含一行列标题和多行数据且每行同列数据的类型和格式完全相同的Excel工作表。 数据清单中的列对应数据
23、库中的字段，列标志对应数据库中的字段名称，每一行对应数据库中的一条记录。,目录,本节,1.5 数据管理,2.构建数据清单的要求 为了使Excel自动将数据清单当作数据库，构建数据清单的要求主要有： 列标志应位于数据清单的第一行，用于查找和组织数据、创建报告。 同一列中各行数据项的类型和格式应当完全相同。 避免在数据清单中放置空白的行或列，但需将数据清单和其他数据隔开时，应在它们之间留出至少一个空白的行或列。 尽量在一张工作表上建立一个数据清单。,目录,本节,1.5 数据管理,1.5.2记录单 1.记录单的概念 记录单又称数据记录单，是快速添加、查找、修改或删除数据清单中相关记录的对话框。 2.
24、通过记录单处理记录清单的记录 通过记录单处理记录的优点 通过记录单处理记录的优点主要有：界面直观，操作简单，减少数据处理时行列位置的来回切换，避免输入错误，特别适用于大型数据清单中记录的核对、添加、查找、修改或删除。,目录,本节,1.5 数据管理,记录单对话框的打开 在Excel 2003及以下版本中，打开记录单对话框的方法是：输入数据清单的列标志后，选中数据清单的任一个单元格，单击菜单【数据|记录单】即可。 在Excel 2007及以上版本中，可按以下方法来添加记录单命令按钮： 单击Office 按钮【 】，在出现的菜单中单击【Excel选项】按钮，出现“Excel选项”对话框。,目录,本节
25、,1.5 数据管理,在该对话框中单击【自定义】，并在“从下列位置选择命令”下拉框中选择“所有命令”，然后在命令列表中找到并双击“记录单”命令，将其添加到快速访问工具栏。 设置完毕，单击【确定】按钮。此时，在Excel窗口中可以看到，【记录单】命令按钮已出现在快速访问工具栏中。 在快速访问工具栏中点击记录单按钮【 】即可打开记录单对话框。,目录,本节,1.5 数据管理,在记录单对话框中输入新记录 利用记录单对话框查找特定单元格 利用记录单对话框核对或修改特定记录 利用记录单对话框删除特定记录 1.5.3排序 1.快速排序 使用快速排序的操作步骤如下： 在数据清单中选定需要排序的各行记录。 执行工
26、具栏或功能区中的【排序】命令。,目录,本节,1.5 数据管理,2.自定义排序 使用自定义排序的操作步骤为： 在【数据】菜单或功能区“数据”选项卡中单击【排序】命令打开排序对话框，如图下所示。,目录,本节,1.5 数据管理,在排序对话框中设置排序的列、排序依据及次序后，单击【确定】按钮。 1.5.4筛选 1.快速筛选 2.高级筛选,目录,本节,1.5 数据管理,【例1-16】,【例1-17】,1.5.5分类汇总,目录,本节,1.5 数据管理,【例1-18】,1.5.6数据透视表 1.数据透视表的创建 2.数据透视表的设置,【例1-19】,1.5.7数据有效性 通过数据有效性设置，可以指定单元格中
27、允许输入的数据类型以、数据的有效范围以及提示信息和出错警告信息。 设置单元格数据有效性时，只需选中要设置数据有效性的单元格或区域，然后在功能区“数据”选项卡界面中的“数据工具”功能组，单击“数据有效性”，出现下拉菜单，选择其中的“数据有效性”命令。,目录,本节,1.5 数据管理,【例1-20】,1.5.8报表汇总 1.按位置汇总：各报表结构一致，可通过定义公式汇总。 2.合并计算 如果需要汇总的报表格式不同，需使用合并计算功能来完成汇总工作。在合并计算中，存放合并计算结果的工作表称为“目标工作表”，其中接收合并数据的区域称为“目标区域”，而被合并计算的各个工作表称为“源工作表”，其中被合并计算
28、的数据区域称为“源区域”。,目录,本节,1.5 数据管理,【例1-21】,【例1-22】,1.6.1单变量求解 如果已知单个计算公式的预期结果，而用于确定此公式结果的输入值未知，则可以使用单变量求解功能，可理解为已知因变量y反算某个自变量x。当进行单变量求解时，Excel会不断改变可变单元格中的值，直到目标单元格的公式返回所需的结果为止。在目标单元格中设置正确的计算公式，是进行单变量求解的前提。,目录,本节,1.6 假设分析,【例1-23】,1.6.2模拟运算表 模拟运算表（Excel2007中称之为“数据表”）是通过假设分析的方法进行数值预测，从而观察公式中某些变量的不同组合对因变量即公式结
29、果的影响。利用模拟运算表，可以在一次操作过程中完成多组不同数值的计算，并将不同结果同时显示在工作表中，便于查看、比较和分析。 1.单变量模拟运算 单变量模拟运算可以考察某一个自变量的不同取值对一个或多个因变量即公式的影响。,目录,本节,1.6 假设分析,【例1-24】,2.双变量模拟运算 双变量模拟运算可以考察两个自变量不同取值组合对一个因变量即公式的影响，但操作时一定要注意，计算公式一定位于行变量和列变量左上角交叉单元格，否则无法正确地进行模拟运算。,目录,本节,1.6 假设分析,【例1-25】,1.6.3方案管理器 方案是Excel保存在工作表中并可进行自动替换的一组值。用户可以使用方案来
30、预测工作表模型的输出结果。同时，还可以在工作表中创建并保存不同的数组值，然后切换到任意新方案以查看不同的结果或者通过创建方案汇总报告来对比各种方案。 1.建立方案 2.显示方案结果 3.对比方案 4.删除、编辑方案,目录,本节,1.6 假设分析,【例1-26】,1.6.4规划求解 1.规划求解概述 (1)在经济决策中，经常会遇到一些最优解的问题。例如，如何安排生产会使得利润最大化、什么情况下成本最低、现金持有量为多少时现金持有总成本最低、订货量为多少时存货总成本最低等等。Excel提供了规划求解功能，利用规划求解工具，只要输入基本数据并定义好相关计算公式，就可以快速简便地得到最优解及其条件值。
31、,目录,本节,1.6 假设分析,(2)利用规划求解，可求得工作表上某个单元格（目标单元格）中公式的最优值。规划求解将对直接或间接与目标单元格公式相关联的一组单元格（可变单元格）中的数值进行调整，最终在目标单元格公式中求得最优解。 (3)求解最优解的问题，大致可以分为两类：线性问题和非线性问题。所谓线性优化问题，是指自变量与因变量间的函数关系是线性的。所谓非线性优化问题，是指自变量与因变量间的函数关系是非线性的。规划求解工具可以同时解决线性和非线性优化求解问题，只要设置相关参数即可。,目录,本节,1.6 假设分析,(4)规划求解功能需要事先加载后才能使用。在Excel2007选项对话框中，在左侧
32、选择“加载项”，在右侧单击“转到”按钮，可出现加载宏对话框，选中其中的“规划求解加载项”，然后单击“确定”按钮进行安装。加载后，在功能区“数据”选项卡中出现一个新的命令组“分析”，其中便含有“规划求解”命令。 2.规划求解需设置的参数 (1)目标单元格,目录,本节,1.6 假设分析,目标单元格是希望求解其最优解的单元格，如利润、成本所在的单元格。目标单元格应当定义与可变单元格相关的计算公式，这样才能使得目标单元格与可变单元格联动，进而才能够求解目标单元格最优解以及可变单元格相应值。 (2)可变单元格 可变单元格是影响目标单元格值的那些决策变量所在的单元格，这些单元格的值是可变的。规划求解正是通
33、过不断调整各可变单元格的值来逐步找出目标单元格的最优解。 (3)约束条件,目录,本节,1.6 假设分析,在现实的经济决策中，肯定会面临着各式各样的约束条件，没有这些约束条件也就无法求的问题的最优解。在规划求解过程中，可以设置多个约束条件，约束条件可以针对任何单元格，但应与可变单元格存在直接或间接的运算关系。 3.建立规划求解报告 应用Excel中的“规划求解”功能不仅能够在工作表中显示求解结果，而且能够产生分析报告以供用户作为参考。规划求解可以产生“运算结果报告”、“敏感性报告”和“极限值报告”三种分析报告。,目录,本节,1.6 假设分析,【例1-27】,Excel 提供了两种类型的控件：Ac
34、tiveX 控件和表单控件。ActiveX 控件与Visual Basic语言中的控件相似，可以添加到 Visual Basic 编辑器自定义窗体中，适用范围更加广泛，功能也更加强大，例如可以响应各种事件，但是使用起来也相对较为复杂，需要用户熟悉VBA知识。对于一般用户来说，表单控件更加简单实用，即使不懂得VBA知识的用户也可以直接在工作表中使用这些控件。表单控件主要包括标签、分组框、按钮、复选框、单选钮、列表框、组合框、滚动条和微调项。,目录,本节,1.7 窗体工具,在Excel 2007中使用表单工具，需要事先在Excel选项对话框中的“常用”项中，选中“在功能区显示开发工具选项卡”。设置
35、该选项后，功能区会出现“开发工具”选项卡，在该选项卡中单击“插入”按钮会出现下拉菜单，其中就包括了表单控件工具栏和ActiveX控件工具栏。 1.7.1标签 1.功能 标签一般用于显示提示信息，只用于显示信息而不能进行编辑。,目录,本节,1.7 窗体工具,2.使用方法 在工作表中使用标签控件时，只要在表单工具栏中单击“ ”按钮，然后在工作表中按下鼠标左键并拖动鼠标即可画出一个标签。 直接用鼠标左键单击标签即可选中该标签。当选中标签时，可以直接更改标签的显示内容；将鼠标指针指向标签边框并按下鼠标左键即可移动标签；当标签处于编辑状态时，如果处于文字编辑状态，先用鼠标单击其边框退出文字编辑状态，然后
36、按键即可删除标签。,目录,本节,1.7 窗体工具,1.7.2分组框 1.功能 分组框是一个容器对象，可以容纳一个或多个其它对象。特别是当多个单选钮放置在同一分组框之中时，这多个单选钮将被视为一组，用户只能选择其中之一，而不同分组框之内的单选钮是无关的。 2.使用方法,目录,本节,1.7 窗体工具,在工作表中使用分组框控件时，只要在表单工具栏中单击“ ”按钮，然后在工作表中按下鼠标左键并拖动鼠标即可画出一个分组框。 直接用鼠标左键单击分组框即可选中该分组框。当选中分组框时，可以直接更改分组框的标题；将鼠标指针指向分组框边框并按下鼠标左键即可移动分组框；当分组框处于编辑状态时，如果处于文字编辑状态
37、，先用鼠标单击其边框退出文字编辑状态，然后按键即可删除分组框。,目录,本节,1.7 窗体工具,1.7.3按钮 1.功能 按钮一般用于执行某个宏。当按钮处于运行状态时，用鼠标左键单击按钮将自动执行为该按钮所指定的宏。 2.使用方法 在表单工具栏中单击“ ”按钮，然后在工作表中按下鼠标左键并拖动鼠标即可画出一个按钮。此时，会要求用户为其指定一个宏或新录制一个宏。将来可在按钮上单击鼠标右键，出现快捷菜单，然后从快捷菜单中选择“指定宏”。,目录,本节,1.7 窗体工具,当按钮处于运行状态（未选中状态）时，直接用鼠标左键单击按钮时将执行为其指定的宏；右键单击按钮即可使该按钮转为编辑状态，同时会出现快捷菜
38、单，从快捷菜单中选择“编辑文字”可以编辑按钮所显示的文本内容，直接按键可以取消快捷菜单；当选中按钮时，将鼠标指针指向按钮边框并按下鼠标左键即可移动按钮；当按钮处于编辑状态时，如果处于文字编辑状态，先用鼠标单击其边框退出文字编辑状态，然后按键即可删除按钮。,目录,本节,1.7 窗体工具,1.7.4复选框 1.功能 复选框用于为用户提供若干选项，并且这些选项之间的选择与否是无关的，即用户可以任意从中选择一个、多个选项或者一个也不选择。 2.使用方法 在工作表中使用复选框控件时，只要在表单工具栏中单击“ ”按钮，然后在工作表中按下鼠标左键并拖动鼠标即可画出一个复选框，不同类型的复选框可以放置在不同的
39、分组框中。,目录,本节,1.7 窗体工具,3.参数设置 右键单击复选框，然后从快捷菜单中选择“设置控件格式”，可设置复选框的各种参数。控制选项卡参数： 当前值。当复选框处于未选择状态时，其值为“FALSE”；当复选框处于已选择状态时，其值为“TRUE”；当复选框处于混合状态时，表示用户尚未做出任何选择，其值为“#N/A”。当复选框处于运行状态时，只能在已选择和未选择两个状态间切换。,目录,本节,1.7 窗体工具,单元格链接。可以将复选框的值链接到某个单元格中，这样将来可以通过引用该单元格的值来判断用户所做的选择。 1.7.5单选钮 1.功能 一组单选钮可为用户提供若干选项，并且这些选项之间的选
40、择是互斥的，即用户只能从同一组单选钮中选择某一个单选钮。 2.使用方法,目录,本节,1.7 窗体工具,在工作表中使用单选钮控件时，只要在表单工具栏中单击“ ”按钮，然后在工作表中按下鼠标左键并拖动鼠标即可画出一个单选钮，不同类型的单选钮可以放置在不同的分组框中。 3.参数设置 右键单击单选钮，然后从快捷菜单中选择“设置控件格式”，可设置单选钮的各种参数。控制选项卡参数： 当前值。单选钮的状态有两种：未选择和已选择。在设置同一组单选钮时，可以将某个单选钮的初值设置为已选择，此时其它单选钮自动被设置为未选择。,目录,本节,1.7 窗体工具,单元格链接。可以将单选钮的值链接到某个单元格中，这样将来可
41、以通过引用该单元格的值来判断用户所做的选择。需要注意的是，由于在同一时刻只能选中同一组单选钮中的一个，所以同一组单选钮所链接到的单元格会自动被设置为同一个单元格。 1.7.6列表框 1.功能 列表框以列表的形式将各备选项显示出来供用户选择，用户可以从中选择某一个或多个选项。,目录,本节,1.7 窗体工具,2.使用方法 在工作表中使用列表框控件时，只要在表单工具栏中单击“ ”按钮，然后在工作表中按下鼠标左键并拖动鼠标即可画出一个列表框。 3.参数设置 右键单击列表框，然后从快捷菜单中选择“设置控件格式”，可设置列表框的各种参数。控制选项卡参数： 数据源区域 单元格链接,目录,本节,1.7 窗体工
42、具,选定类型。选定类型包括三种：单选、复选和扩展。如果选定类型为单选，将来在该列表框中用户同时只能选择一个选项；如果选定类型为复选，将来在该列表框中用户同时可以选择多个选项，只要依次单击要选择的选项即可；如果选定类型为扩展，将来在该列表框中用户同时可以选择多个选项，如果要选择多个选项的话，需要按下键，然后再依次单击要选择的选项，或者按下键，然后再分别单击要选择的第一个选项和要选择的最后一个选项，此时第一选项和最后一个选项之间的各选项将同时被连续选中。,目录,本节,1.7 窗体工具,1.7.7组合框 组合框的使用与列表框类似，只不过是在下拉框中显示各选项，而不是像列表框那样将所有选项同时在列表中
43、显示出来。另外，在组合框中同时只能选择一个选项。组合框控件的的按钮是“ ”，具体用法同前。 1.7.8滚动条 1.功能 当某个项目的数值在一定范围内变化时，可以利用滚动条来设置该项目值，而不必从键盘输入。,目录,本节,1.7 窗体工具,2.使用方法 在工作表中使用滚动条控件时，只要在表单工具栏中单击“ ”按钮，然后在工作表中按下鼠标左键并拖动鼠标即可画出一个滚动条。 当滚动条处于运行状态时，可直接用鼠标左键单击滚动条的两个箭头按钮来调整值（变化量称之为步长），或用鼠标左键来拖动滚动条的滑块来调整值，或在滑块和箭头按钮之间单击鼠标左键来调整值（变化量称之为页步长）。,目录,本节,1.7 窗体工具
44、,3.参数设置 右键单击滚动条，然后从快捷菜单中选择“设置控件格式”，可设置滚动条的各种参数。控制选项卡参数： 当前值。当前值用于设置或显示滚动条的初值。 最小值。即滚动条所能设置的最小数值，默认为0，只能是介于0至30 000之间的整数。 最大值。即滚动条所能设置的最大数值，默认为100，只能是介于0至30 000之间的整数且不能小于最小值。,目录,本节,1.7 窗体工具,步长。步长是指当用鼠标左键单击滚动条的两个箭头时值的变化量，默认值为1，只能是介于0至30 000之间的整数。 页步长。页步长是指在滑块和箭头按钮之间单击鼠标左键时值的变化量，默认值为10，只能是介于0至30 000之间的
45、整数。 单元格链接。可以将滚动条的值链接到某个单元格，这样将来通过直接或间接引用链接单元格的值可得到预期结果。 4.数据范围设置技巧,目录,本节,1.7 窗体工具,因为滚动条的数值范围只能是0至30 000之间的整数，所以当希望的数值范围不在该区间之内时需要进行一些转换。 现举例如下： (1)某项目的数值范围是20至20，此时可以将滚动条的数值范围设置为0至40，链接单元格（假定为“A1”）的值作为中间值，在项目单元格定义公式“=A1-20”，那么项目单元格的数值范围就符合要求了。,目录,本节,1.7 窗体工具,(2)某项目的数值范围是1.0%至20.0%的百分数且有一位小数，此时可以将滚动条
46、的数值范围设置为1至200、步长设置为1、页步长设置为10，链接单元格（假定为“A1”）的值作为中间值，在项目单元格定义公式“=A1/1000”，那么项目单元格的数值范围就符合要求了,并且给人的感觉是步长为0.1%，而页步长为1。,目录,本节,1.7 窗体工具,1.7.9微调项 微调项的使用与滚动条类似，只不过微调项控件不具有滑块，不能按页步长变化，只能按步长变化，一般用于小范围数值的设置。微调项控件的的按钮是“ ”。,目录,本节,1.7 窗体工具,【例1-28】,1.8.1保护工作簿 1.保护工作簿结构和窗口 通过对工作簿结构的保护，可以限制用户在该工作簿中插入、删除、移动和复制、隐藏和恢复
47、工作表，也可以限制对工作表重命名。通过对工作簿窗口的保护，可以限制用户对该工作簿窗口进行放大、缩小、移动等操作。,目录,本节,1.8 数据保护,【例1-29】,2.设置工作簿打开权限密码和修改权限密码 Excel可以设置Excel工作簿文件的打开权限密码或修改权限密码。对于设置了打开权限密码的Excel文件，只有输入了正确的密码才能打开文件；对于设置了修改权限密码的Excel文件，只有输入了正确的密码才能修改文件，否则只能以只读的方式打开文件，即只能浏览而无法修改文件。,目录,本节,1.8 数据保护,【例1-30】,1.8.2保护工作表 1.保护整张工作表,目录,本节,1.8 数据保护,【例1
48、-31】,2.保护工作表中的部分区域,【例1-32】,3.设置允许用户编辑区域,【例1-33】,目录,本节,1.某集团产品销售面向全国市场，该集团设有四个工厂，生产产品供全国销售，分别在北京、上海、深圳、广州设有销售公司。每个销售公司负责本地区的市场销售，各区域的最大市场需求和销售价格，每个工厂的单位产品成本、固定成本和产能，以及从各工厂到各销售公司的运输成本资料分别如下表所示。 要求：利用规划求解功能求解实现集团利润最大化的生产和运输安排。,思考题,目录,本节,2.如下图所示。现进行一项关于期望房价的调查，调查内容包括：性别、年龄、年收入、婚否和期望房价。其中年龄分为25岁以下、25-35岁
49、、35-50、50以上；年收入分为3万以下、3-5万、5-10万、10-20万、20万以上；期望房价范围在5000-20000元之间。要求如下： 设置各控件属性，并在调查结果区域定义公式显示调查结果。 保护工作表，只允许用户通过窗体工具进行相关操作，而不能在工作表中直接输入任何数据。,目录,本节,思考题,目录,本节,思考题,3.科目发生额余额表如下表所示。要求：在某单元格输入科目后，自动显示该科目的期初余额、借方发生额、贷方发生额和期末余额。,目录,本节,思考题,目录,本节,思考题,4.固定资产信息如下表所示。按要求进行以下操作： 设置数据有效性，要求：购置日期只能输入截至当日的当月有效日期；设备名称长度介于1至10之间；原值为大于等于1000的数；预计净残值大于等于0小于原值；预计使用年限为大于等于2的整数；折旧方法设置为序列，选项包括：年限平均法、年数总和法和双倍余额递减法。 根据固定资产信息，制作折旧表，计算各年折旧和累计折旧
