中文Excel的应用20110724熟悉函数

1、Excel 2003的应用2011.7.24课程内容 Excel的启动与退出、窗口组成 工作表的建立与编辑 格式化表格 公式与函数 数据管理和分析 图表 显示与打印工作课程安排 总学时：18学时 界面熟悉：4-6学时 功能了解：8-6学时 财务应用：6学时Excel 2003的启动与退出 Excel 2003的启动 Excel 2003的退出 Excel 2003窗口的组成与基本概念Excel 2003的基本概念 工作簿 工作表 单元格 单元格区域Excel 2003的窗口组成Excel 2003的窗口标题栏菜单栏工具栏名称框编辑栏工作区活动单元格工作表标签状态栏1. 任务窗格Excel 20

2、03的基本操作 新建一个工作簿 打开已有的工作簿文件自动打开用户工作簿的技巧 XLSTART目录是Excel的专用启动目录，Excel每次启动时都会检查该目录，如果有文件就自动执行（或打开）该目录中的所有文件，否则就直接启动并创建一个新的工作簿供用户使用。 利用这一功能，我们只需为常用的工作簿在XLSTART目录中建立一个快捷方式即可达到让Excel在启动时自动打开这些工作簿的目的。如某用户每次启动Excel时都要处理C:MyDocuments目录中“奖金”工作簿，则可使用鼠标将C:MyDocuments目录中的“奖金.XLS”文件右拖到ProgramFilesMicrosoftOfficeO

3、fficeXLSTART目录中，然后在弹出的快捷菜单上选择“在当前位置创建快捷方式”命令，为“奖金”工作簿在XLSTART目录中创建一个快捷方式，此后每次启动时，Excel都会自动打开“奖金”工作簿供用户使用，非常方便。更改Excel系统启动目录的技巧上面提到，我们只需将有关文件保存到XLSTART目录中，Excel启动时就会自动运行该文件，不过经常对系统目录进行操作可能会对系统安全造成的影响（XLSTART目录是一个系统目录，一般情况下我们不应将过多的文件存放在该目录中）。别着急，除了专用启动目录之外，Excel还提供了替补启动目录功能，即允许用户将任意一个目录指定为Excel的替补启动目录

4、，然后在启动时自动打开该目录中的文件，从而增强了我们日常操作的灵活性。为Excel指定替补启动目录的步骤为：1.在硬盘的合适位置建立一个常用Excel工作簿保存目录（如在“C:MyDocuments下再建立一个Excel目录），将常用的Excel工作簿文件都保存到该目录中。2.启动Excel。3.执行“工具”菜单的“选项”命令，打开“选项”对话框。4.单击“常规”选项卡。5.在“替补启动目录”对话框中输入常用Excel工作簿所在目录（如C:MyDocumentsExcel）。6.单击“确定”按钮。此后Excel就会以“C:MyDocumentsExcel目录作为替补启动目录，它在每次启动时都会

5、自动打开该目录中的所有工作簿，方便了用户的使用（“替补启动目录”与XLSTART目录并不冲突，当我们设置了“替补启动目录”之后，Excel将同时打开XLSTART目录和“替补启动目录”中的所有文件，二者共同发挥作用）。 工作簿的关闭和保存建立“常用文档”新菜单在菜单栏上新建一个“常用文档”菜单，将常用的工作簿文档添加到其中，方便随时调用。1.在工具栏空白处右击鼠标，选“自定义”选项，打开“自定义”对话框。在“命令”标签中，选中“类别”下的“新菜单”项，再将“命令”下面的“新菜单”拖到菜单栏。按“更改所选内容”按钮，在弹出菜单的“命名”框中输入一个名称（如“常用文档”）。2.再在“类别”下面任选

6、一项（如“插入”选项），在右边“命令”下面任选一项（如“超链接”选项），将它拖到新菜单（常用文档）中，并仿照上面的操作对它进行命名（如“工资表”等），建立第一个工作簿文档列表名称。重复上面的操作，多添加几个文档列表名称。3.选中“常用文档”菜单中某个菜单项（如“工资表”等），右击鼠标，在弹出的快捷菜单中，选“分配超链接打开”选项，打开“分配超链接”对话框。通过按“查找范围”右侧的下拉按钮，定位到相应的工作簿（如“工资.xls”等）文件夹，并选中该工作簿文档。重复上面的操作，将菜单项和与它对应的工作簿文档超链接起来。4.以后需要打开“常用文档”菜单中的某个工作簿文档时，只要展开“常用文档”菜单，

7、单击其中的相应选项即可。提示：尽管我们将“超链接”选项拖到了“常用文档”菜单中，但并不影响“插入”菜单中“超链接”菜单项和“常用”工具栏上的“插入超链接”按钮的功能。 工作表的操作 插入和删除工作表 复制或移动工作表 重命名工作表 工作表的建立单元格的数据类型与数据输入文本的输入 连字符“&”来合并文本 用半角引号输入数字符号 限定文本大小写转换的函数，有三个它们。分别是 “=UPPER(源数据格)”，将文本全部转换为大写； “=LOWER(源数据格)”，将文本全部转换成小写； “=PROPER(源数据格)”，将文本转换成“适当”的大小写。 如让每个单词的首字母为大写等。例如，我们在一

8、张表格的A1单元格中输入小写的“excel”，然后在目标单元格中输入“=UPPER(A1)”，回车后得到的结果将会是“EXCEL”。同样，如果我们在A3单元格中输入“mr.weiwei”，然后我们在目标单元格中输入“=PROPER(A3)”，那么我们得到的结果就将是“Mr.Weiwei”了。 文本输入技巧 alt+enter拆分同一单元格内多行文字 按ALT+向下键，即可得到本列中原有文本的列表。 ALT128（小键盘）是欧元符号 创建文本直方图 利用“REPT”函数可以直接在工作表中创建由纯文本组成的直方图。它的原理就是利用特殊符号的智能重复，按照指定单元格中的计算结果表现出长短不一的比较效

9、果。比如我们首先制作一张年度收支平衡表，然后将“A列”作为直方图中“预算内”月份的显示区，将“E列”则作为直方图中“超预算”的显示区。然后根据表中已有结果“B列”的数值，用“Wingdings”字体的“N”字符表现出来。 具体步骤如下：在E3单元格中写入公式:=IF(A1,REPT(”N“,A1),”“) 拖动填充柄至G14。 数字的输入Excel将由下列21个字符：0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e，组成的字符串识别为数值型数据，中间不可有空格。Excel 将忽略数字前面的正号(+)，并将单个句点视作小数点。所有其他数字与非数字的组合均作文本处

10、理。数字输入过程中需要注意以下几个方面。（1）输入分数为了避免Excel将输入的分数视作日期，在分数前输入0(零)、空格，例如输入 0 1/2，表示该单元格内事分数1/2。（2）输入负数在Excel中，数字默认是正数，所以不需要输入正号，而负数则需要在前面输入减号 (-)。（3）对齐数字在默认状态下，所有数字在单元格中均右对齐。如果要改变其对齐方式，单击菜单【格式】-【单元格】命令，再单击“对齐”选项卡，并从中选择所需的选项。（4）数字的显示方式单元格中的数字格式决定 Excel 在工作表中显示数字的方式。如果在“常规”格式的单元格中键入数字，Excel 将根据具体情况套用不同的数字格式。例如

11、，如果键入 $14.73，Excel 将套用货币格式。如果要改变数字格式，则要先选定包含数字的单元格，再单击菜单【格式】-【单元格】命令，然后单击“数字”选项卡，根据需要选定相应的分类和格式。（5）科学计数法如果单元格使用默认的“常规”数字格式，Excel会将数字显示为整数(789)、小数(7.89)，当数字长度超出单元格宽度时以科学记数法（7.89E+08）表示，长度为 11 位，其中包括小数点和类似“E”和“+”这样的字符。如果要输入并显示多于11位的数字，可以使用自定义的数字格式。但是无论显示的数字的位数如何，Excel 都只保留15位的数字精度。如果数字长度超出了15位，Excel 则

12、会将多余的数字位转换为零 (0)。 用特殊符号补齐位数和财务打过交道的人都知道，在账面填充时有一种约定俗成的“安全填写法”，那就是将金额中的空位补齐，或者在款项数据的前面加上“$”之类的符号。其实，在Excel中也有类似的输入方法，那就是“REPT”函数。它的基本格式是“=REPT(“特殊符号”，填充位数)”。要在中A2单元格里的数字结尾处用“#”号填充至16位，就只须将公式改为“=(A2&REPT(#,16-LEN(A2)”即可；如果我们要将A3单元格中的数字从左侧用“#”号填充至16位，就要改为“=REPT(#,16-LEN(A3)&A3”如果我们想用“#”号将A4中的数值

13、从两侧填充，则需要改为“=REPT(#,8-LEN(A4)/2)&A4&REPT(#)8-LEN(A4)/2)”如果要在A5单元格数字的前面再加上“$”符号的话，那就可以改为：=TEXT(A2,$#,#0.00)&(REPT(#,16-LEN(TEXT(A2,$#,#0.00)日期时间的输入Excel中将日期和时间当做数字处理，显示方式取决于所在单元格的数字格式，默认情况下，当输入了日期或时间以后，Excel会自动的识别它并且改为相应的内置的日期或时间格式，在单元格里右对齐，如果Excel无法识别，则把它处理为文本，并在单元格中左对齐。由于被当做数字处理，所以日期或时间

14、数据之间可以进行加、减处理，二者想减可以到到一个天数，一个日期与数字相加得到另外一个日期。默认情况下，Excel以二十四小时制计算时间，如果要键入十二进制的时间，则要明确输入AM或PM来表示上午或下午。例如，如果输入5:00，则表示上午5点，即5:00 AM，如果要表示下午5:00，则可以输入5:00 PM或者17:00。如果要同时输入日期和时间，则在二者之间加一个空格即可。如果要输入当天日期，可以同时按下Ctrl和;键，如果要输入当前的时间，可以同时按下Ctrl、Shift和:键，如果要输入当天日期当前时间，则先同时按下Ctrl和;键盘，加上一个空格，再同时按下Ctrl、Shift和:键。

15、表头下面衬张图片 为工作表添加的背景，是衬在整个工作表下面的，能不能只衬在表头下面呢? 1.执行“格式工作表背景”命令，打开“工作表背景”对话框，选中需要作为背景的图片后，按下“插入”按钮，将图片衬于整个工作表下面。 2.在按住Ctrl键的同时，用鼠标在不需要衬图片的单元格（区域）中拖拉，同时选中这些单元格（区域）。 3.按“格式”工具栏上的“填充颜色”右侧的下拉按钮，在随后出现的“调色板”中，选中“白色”。 经过这样的设置以后，留下的单元格下面衬上了图片，而上述选中的单元格（区域）下面就没有衬图片了（其实，是图片被“白色”遮盖了）。 提示衬在单元格下面的图片是不支持打印的。 “自动更正”输入

16、统一的文本 你是不是经常为输入某些固定的文本，如电脑报而烦恼呢?那就往下看吧。 1.执行“工具自动更正”命令，打开“自动更正”对话框。 2.在“替换”下面的方框中输入“pcw”（也可以是其他字符，“pcw”用小写），在“替换为”下面的方框中输入“电脑报”，再单击“添加”和“确定”按钮。 3.以后如果需要输入上述文本时，只要输入“pcw”字符此时可以不考虑“pcw”的大小写，然后确认一下就成了。自动填充数据 自动重复列中已输入的内容 使用填充柄填充数据 使用“填充”命令将数据填充到工作表单元格中。还可以让Excel根据建立的模式自动继续数字、数字和文本的组合、日期或时间段序列。然而，若要快速填充

17、几种类型的数据序列，可以选中单元格并拖动填充柄 （填充柄：位于选定区域右下角的小黑方块。将用鼠标指向填充柄时，鼠标的指针更改为黑十字。） （1）自动填充文本内容对于文本内容，拖动填充柄填充以后，目标单元格内将出现和源单元格相同的内容，相当于复制、粘贴操作。（2）自动填充数字如果源单元格为一个单元格，那么拖动填充柄，效果同自动填充文本内容一样，会将源单元格的数据复制到目标单元格。如果源单元格为两个以上的单元格，例如A1、A2单元格分别存放数字1、2，这个时候用鼠标选中A1、A2单元格，拖动填充柄到A3、A4后放开，那么A3、A4将出现3、4，如果点开“自动填充选项”，会看到如图4-13所示，此时

18、自动填充将默认“以序列方式填充”，1、2、3、4为一个序列，如果A1、A2单元格存放的是2、4，那么A3、A4将显示6、8，如图4-14所示。（3）自动填充文本加数字组合如果填充的是文本和数字的组合，那么文本部分将是复制的效果，数字部分将按照序列方式填充，如图4-15所示，A列是仅仅选择A1单元格后进行自动填充，文本部分复制，数字部分1按序列进行填充，所以A2、A3、A4的内容就是文本2、文本3、文本4。B列是选择B1、B2单元格后进行填充，文本依然是文本，1、3填充后的序列是1、3、5、7，所以B3、B4的内容就是文本5、文本7。（4）自动填充日期时间前面我们已经讲过日期、时间在Excel被

19、当做数字处理，它的自动填充跟数字稍有不同，不论选择的是一个还是多个单元格，都会产生一个序列，而不像数字那样如果仅仅选择一个效果是复制。如果是文本和日期、时间的组合，那么最后面得日期或时间以序列方式填充，其他部分复制。如图4-16所示，A1是仅仅一个日期型数据，它的自动填充是按照序列方式来填充；B列是文本于日期的一个组合，它的自动填充是文本复制，日期以序列方式填充；C列增加了时间，处于最后面的时间按序列方式填充，其余部分复制；D列是选择D1、D2单元格后拖动的填充柄，所以序列是1、3、5、7、9、11了。（5）自动填充公式C1单元格内容是=A1+B1，对它进行自动填充后，C2单元格内容是=A2+

20、B2，类似于文本和数字的组合填充方式，字母不变，数字以序列方式填充，我们将在后续公式和函数章节做详细介绍。（6）自定义序列填充为了更轻松地输入特定的数据序列（如名称或销售区域的列表），可以创建自定义填充序列。自定义填充序列可以基于工作表中已有项目的列表，也可以从头开始键入列表。不能编辑或删除内置填充序列（如月和日的填充序列），但是可以编辑或删除自定义填充序列。注意：自定义列表只可以包含文字或混合数字的文本。对于只包含数字的自定义列表，如从 0 到 100，必须首先创建一个设置为文本格式的数字列表。点击菜单【工具】-【选项】，切换到“自定义序列”选项卡，可以看到系统内置的填充序列，如图4-18所

21、示，这是一个月份的填充序列，意味着如果我们在A1输入了“一月”，对它进行自动填充的话，A2、A3将分别显示二月、三月而不是按照文本的填充规则去复制一月。建立分类下拉列表填充项我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用“数据有效性”功能建了一个分类下拉列表填充项。1.在Sheet2中，将企业名称按类别（如“工业企业”、“商业企业”、“个体企业”等）分别输入不同列中，建立一个企业名称数据库。 2.选中A列（“工业企业”名称所在列），在“名称”栏内，输入“工业企业”字符后，按“回车”键进行确认。仿照上面的操作，将B、C列分别命名为“商业企业”、“个体企业”3.切换到Sheet1中

22、，选中需要输入“企业类别”的列（如C列），执行“数据有效性”命令，打开“数据有效性”对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”序列（各元素之间用英文逗号隔开），确定退出。 再选中需要输入企业名称的列（如D列），再打开“数据有效性”对话框，选中“序列”选项后，在“来源”方框中输入公式：=INDIRECT（C1），确定退出。4.选中C列任意单元格（如C4），单击右侧下拉按钮，选择相应的“企业类别”填入单元格中。然后选中该单元格对应的D列单元格（如D4），单击下拉按钮，即可从相应类别的企业名称列表中选择

23、需要的企业名称填入该单元格中。提示：在以后打印报表时，如果不需要打印“企业类别”列，可以选中该列，右击鼠标，选“隐藏”选项，将该列隐藏起来即可。数据的编辑 修改数据 敲击键盘上的Enter或Tab键来实现，取消可以按Esc键 复制数据 通过工具栏上的复制按钮，粘贴按钮。也可以通过键盘快捷键Ctrl+C复制，Ctrl+V粘贴 移动数据 通过工具栏上的剪切按钮，粘贴按钮。也可以通过键盘快捷键Ctrl+X剪切，Ctrl+V粘贴 查找与替换数据删除数据在Excel里，删除数据有两种：清除单元格、删除单元格。二者的区别在于，清除单元格是从单元格里删除掉内容，单元格本身还在；删除单元格是将单元格从工作表中

24、去掉，包括里面的数据，同时需要把相邻的单元格位置做调整，填充原来该单元格的位置。具体操作方法如下：（1）删除单元格用鼠标单击选择要删除的单元格，此时该单元格四周会出现一个黑色的方框。选择菜单【编辑】-【删除】，弹出“删除”对话框，如图4-19所示。根据提示做出一个选择，确定即可。也可以直接在单元格上点击鼠标右键，效果跟菜单命令一样。（2）清除单元格用鼠标单击选择要删除的单元格，此时该单元格四周会出现一个黑色的方框。选择菜单【编辑】-【清除】，可以选择“格式”、“内容”、“批注”或者全部都清除。如果仅仅是清除内容，可以在单元格上点击鼠标右键，选择“清除内容”或者直接按键盘上的“Delete”键或

25、“Backspace”键。同时查看不同工作表中多个单元格内的数据 有时，我们编辑某个工作表（Sheet1）时，需要查看其它工作表中（Sheet2、Sheet3）某个单元格的内容，可以利用Excel的“监视窗口”功能来实现。 执行“视图工具栏监视窗口”命令，打开“监视窗口”，单击其中的“添加监视”按钮，展开“添加监视点”对话框，用鼠标选中需要查看的单元格后，再单击“添加”按钮。重复前述操作，添加其它“监视点”。 以后，无论在哪个工作表中，只要打开“监视窗口”，即可查看所有被监视点单元格内的数据和相关信息。 工作表的建立与编辑 工作表中的数据 工作表的建立 选择操作对象 工作表的编辑成组填充多张表

26、格的固定单元格 在很多情况下，都会需要同时在多张表格的相同单元格中输入同样的内容。 首先我们单击工作表 “Sheet1”，然后按住Shift键，单击最后一张表格的标签名“Sheet3”(也可以按住Ctrl键进行点选)。此时，Excel的标题栏上的名称出现了“工作组”字样。在需要一次输入多张表格内容的单元格中随便写点什么， “工作组”中所有表格的同一位置都显示出相应内容了。 如需要将多张表格中相同位置的数据统一改变格式，我们得改变第一张表格的数据格式，再单击“编辑”菜单的“填充”选项，然后在其子菜单中选择“至同组工作表”。这时，Excel会弹出“填充成组工作表”的对话框，在这里我们选择“格式”一

27、项，点“确定”后，同组中所有表格该位置的数据格式都改变了。 格式化表格 数字的格式化 字体的格式化 改变对齐方式 设置边框和底纹 复制格式 样式的使用 自动套用格式设置条件格式让不同类型数据用不同颜色显示在工资表中，如果想让大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设置。1.打开“工资表”工作簿，选中“工资总额”所在列，执行“格式条件格式”命令，打开“条件格式”对话框。单击第二个方框右侧的下拉按钮，选中“大于或等于”选项，在后面的方框中输入数值“2000”。单击“格式”按钮，打

28、开“单元格格式”对话框，将“字体”的“颜色”设置为“红色”。2.按“添加”按钮，并仿照上面的操作设置好其它条件（大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”）。3.设置完成后，按下“确定”按钮。看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了。让中、英文输入法智能化地出现 在编辑表格时，有的单元格中要输入英文，有的单元格中要输入中文，反复切换输入法实在不方便，何不设置一下，让输入法智能化地调整呢? 选中需要输入中文的单元格区域，执行“数据有效性”命令，打开“数据有效性”对话框，切换到“输入法模式”标签下，按“模式”右侧的下拉按钮，选中“打开”选项后，“

29、确定”退出。 以后当选中需要输入中文的单元格区域中任意一个单元格时，中文输入法（输入法列表中的第1个中文输入法）自动打开，当选中其它单元格时，中文输入法自动关闭。 单元格的操作 插入行、列、单元格 删除单元格 数值、格式、格式带数据 移动和复制单元格 带格式复制、转置、仅内容、复制结果公式和函数公式的使用是将运算符、单元格引用、数值、文本等连接成的表达式。运算符：例如常见的加、减、乘、除。单元格引用：它包括单个的单元格或多个单元格组成的区域，以及命名的单元格区域。这些单元格或范围可以是同一工作表中的，也可以是同一工作薄其他工作表中的，甚至是其他工作薄工作表中的。数值或文本：前面已经介绍过的两种

30、数据类型。例如“100”或“语文”。 工作表函数：可以是Excel内置的函数，如SUM或MAX，也可以是自定义的函数。 括号：即“(”和“)”，它们用来控制公式中各表达式被处理的优先权。创建公式：以等号开始加入公式元素和函数组合单元格引用单元格引用分为A1和R1C1两种引用样式。在A1引用样式中，用单元格所在列标和行号表示其位置，如C5，表示C列第5行。在R1C1引用样式中，R表示row、C表示column，R5C4表示第5行第4列，即D5单元格。EXCEL单元格的引用包括绝对引用、相对引用和混合引用三种。 绝对引用绝对引用单元格中的绝对单元格引用（例如 $F$6）总是在指定位置引用单元格F6

31、。如果公式所在单元格的位置改变，绝对引用的单元格始终保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。例如，如果将单元格 B2 中的绝对引用复制到单元格 B3，则在两个单元格中一样，都是 $F$6。 相对引用相对引用公式中的相对单元格引用（例如 A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格 B2 中的相对引用复制到单元格 B3，将自动从 =A1 调整到 =A2。 混合引用混合引用混合引用

32、具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用 $A1、$B1 等形式。绝对引用行采用 A$1、B$1 等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从 A2 复制到 B3，它将从 =A$1 调整到 =B$1。 F4键单元格引用切换 在Excel中输入公式时，只要正确使用F4键，就能简单地对单元格的相对引用和绝对引用进行切换。现举例说明。 对于某单元格所输入的公式为“=SUM(B4:B8)”。 选中整个公式，按下F4键，该公式内容变为“=SUM($B$4:$B$8)”，表示对

33、横、纵行单元格均进行绝对引用。 第二次按下F4键，公式内容又变为“=SUM(B$4:B$8)”，表示对横行进行绝对引用，纵行相对引用。 第三次按下F4键，公式则变为“=SUM($B4:$B8)”，表示对横行进行相对引用，对纵行进行绝对引用。 第四次按下F4键时，公式变回到初始状态“=SUM(B4:B8)”，即对横行纵行的单元格均进行相对引用。 手工输入 如果要引用 请使用属于列 A 和行 10 到行 20 中的单元格区域 A10:A20属于行 15 和列 B 到列 E 中的单元格区域 B15:E15行 5 中的所有单元格 5:5从行 5 到行 10 中的所有单元格 5:10列 H 中的所有单元

34、格 H:H从列 H 到列 J 中的所有单元格 H:J从 A 列第 10 行到 E 列第 20 行的单元格区域 A10:E20 对数组区域命名引用 就是给选定的数组来取个有实际意义的名字，直观，清晰 （1） 定义代表单元格的名称：如果要用的数据没有标志或者存储在其他工作表中，可以创建名称来描述单元格或区域，公式中的描述性名称使人们更容易理解公式的含义。例如，公式 =SUM(一季度销售额) 要比公式 =SUM(销售 !C20:C30) 更容易理解。在本示例中，名称“一季度销售额”代表命名为“销售”的工作表中的区域 C20:C30。（2） 名称在所有工作表中都可以使用。 例1，如果名称“预计销售”引

35、用了工作簿中第一个工作表的区域 A20:A30，则工作簿中的所有工作表都使用名称“预计销售”来引用第一个工作表中的区域 A20:A30。 名称也可以用来代表不会改变的（常量）公式和数值。例如，可使用名称“销售税”代表销售额的税率（如 6.2%）。（3） 方法：选定要定义名称的区域然后点击窗体上的“插入”菜单中的“名称”，或指定或自定义，看你的爱好和需求了。 三维引用（1） 概念：使用三维引用来引用多个工作表上的同一单元格或区域（通俗的将就是几张表同时处理，一张表是横和列二元，几张表就有厚度，第三元！）（2） 方法：首先该工作簿必须包含多张工作表。单击需要输入公式的单元格，键入 （等号），再输入

36、函数名称，接着再键入左圆括号单击需要引用的第一个工作表标签。按住 SHIFT 键，单击需要引用的最后一个工作表标签。选定需要引用的单元格或单元格区域。回车完成公式。（3） 解释：（名字搞的很酷，其实没什么了不起），就是如何同时引用几张表上的同一位置的单元格，如所有表的单元格A1相加，可以这么操作。（4） 适用的函数：使用三维引用可以引用其它工作表中的单元格，可以定义名称，还可以通过使用下列函数来创建公式： SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MIN、等等函数。（5） 不适用：三维引用不能用于数组式中；三维用于不能与交叉引用运算符（空格）一起使用。常用函数

37、 abs countif If Int Len left max min mod averageABS函数 主要功能：求出相应数字的绝对值。 使用格式：ABS(number) 参数说明：number代表需要求绝对值的数值或引用的单元格。 应用举例：如果在B2单元格中输入公式：=ABS(A2)，则在A2单元格中无论输入正数（如100）还是负数（如-100），B2中均显示出正数（如100）。 特别提醒：如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！”。 COUNTIF函数 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(

38、Range,Criteria) 特别提醒：允许引用的单元格区域中有空白单元格出现。IF函数函数名称：IF 主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 使用格式：=IF(Logical,Value_if_true,Value_if_false) 参数说明：Logical代表逻辑判断表达式；Value_if_true表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；Value_if_false表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。 应用举例：在B1单元格中输入公式：=IF(A1=60,“及格”,“不及格”

39、)，确定以后，如果A1单元格中的数值大于或等于60，则B1单元格显示“及格”字样，反之显示“不及格”字样。 函数名称：INT 主要功能：将数值向下取整为最接近的整数。 使用格式：INT(number) 参数说明：number表示需要取整的数值或包含数值的引用单元格。 应用举例：输入公式：=INT(18.89)，确认后显示出18。 特别提醒：在取整时，不进行四舍五入；如果输入的公式为=INT(-18.89)，则返回结果为-19。LEN函数 函数名称：LEN 主要功能：统计文本字符串中字符数目。 使用格式：LEN(text) 参数说明：text表示要统计的文本字符串。 应用举例：假定A41单元格中

40、保存了“我今年28岁”的字符串，我们在C40单元格中输入公式：=LEN(A40)，确认后即显示出统计结果“6”。 特别提醒：LEN要统计时，无论中全角字符，还是半角字符，每个字符均计为“1”；与之相对应的一个函数LENB，在统计时半角字符计为“1”，全角字符计为“2”。LEFT函数 函数名称：LEFT 主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。 使用格式：LEFT(text,num_chars) 参数说明：text代表要截字符的字符串；num_chars代表给定的截取数目。 应用举例：假定A38单元格中保存了“我喜欢天极网”的字符串，我们在C38单元格中输入公式：=LEF

41、T(A38,3)，确认后即显示出“我喜欢”的字符。 特别提醒：此函数名的英文意思为“左”，即从左边截取，Excel很多函数都取其英文的意思。 MAX函数 主要功能：求出一组数中的最大值。 使用格式：MAX(number1,number2) 参数说明：number1,number2代表需要求最大值的数值或引用单元格（区域），参数不超过30个。 应用举例：输入公式：=MAX(H2:H11)，确认后即可显示出H2至H11单元和区域中的最大值。 特别提醒：如果参数中有文本或逻辑值，则忽略。MIN函数函数名称：MIN 主要功能：求出一组数中的最小值。 使用格式：MIN(number1,number2)

42、参数说明：number1,number2代表需要求最小值的数值或引用单元格（区域），参数不超过30个。 应用举例：输入公式：=MIN(H2:H11)，确认后即可显示出H2至H11单元和区域中的最小值。MOD函数 主要功能：求出两数相除的余数。 使用格式：MOD(number,divisor) 参数说明：number代表被除数；divisor代表除数。 应用举例：输入公式：=MOD(13,4)，确认后显示出结果“1”。 特别提醒：如果divisor参数为零，则显示错误值“#DIV/0!”。SUM函数 主要功能：计算所有参数数值的和。 使用格式：SUM（Number1,Number2） 参数说明：

43、Number1、Number2代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。 应用举例：在H2单元格中输入公式：=SUM(E2:G2)，确认后即可求出张华的总分。 查找函数信息AVERAE函数 函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。 使用格式：AVERAGE(number1,number2,) 参数说明：number1,number2,：需要求平均值的数值或引用单元格（区域），参数不超过30个。 应用举例：在B8单元格中输入公式：=AVERAGE(B7:D7,F7:H7,7,8)，确认后，即可求出B7至D7区域、F7至H7区域中的数值和7、8的平均

44、值。 特别提醒：如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内。 常用财务函数 财务函数 财务函数是指用来进行财务处理的函数。可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。 财务函数中常见的参数： 未来值 (fv)-在所有付款发生后的投资或贷款的价值。 未来值 (Future value) 现时的资金增长至将来的价值，资金增长以复息计算。（1） 投资计算函数 函数名称 函 数功 能 EFFECT 计算实际年利息率 FV 计算投资的未来值 FVSCHEDULE 计算原始本金经一系列复利率计算之后的未来值 I

45、PMT 计算某投资在给定期间内的支付利息 NOMINAL 计算名义年利率 NPER 计算投资的周期数 NPV 在已知定期现金流量和贴现率的条件下计算某项投资的净现值 PMT 计算某项年金每期支付金额 PPMT 计算某项投资在给定期间里应支付的本金金额 PV 计算某项投资的净现值 XIRR 计算某一组不定期现金流量的内部报酬率 XNPV 计算某一组不定期现金流量的净现值 计算实际年利息率EFFECT EFFECT（nr，np） 该函数利用给定的名义年利率和一年中的复利期次，计算实际年利率。其中nr为名义利率，np为每年的复利期数。 例如： EFFECT（6.13%,4）的计算结果为0.06272

46、4或6.2724% 计算投资的未来值 fV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。 【语法】FV(rate,nper,pmt,pv,type) rate 为各期利率。 nper 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。 pmt 为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。如果忽略 pmt，则必须包含 pv 参数。 pv 为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。如果省略 PV，则假设其值为零，并且必须包括 pmt 参数。 type 数字 0 或 1，

47、用以指定各期的付款时间是在期初还是期末。 【说明】 应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4。 在所有参数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。 【示例】 例如： FV（0.6%,12,-200,-500,1）的计算结果为￥3,032.90； FV(0.9%,10,-1000)的计算结果为￥10,414.87； FV(11.5%/12,30,-2000,1)的计算结果为￥69,

48、796.52。 又如，假设需要为一年后的一项工程预筹资金，现在将￥2000以年利4.5%，按月计息（月利为4.5%/12）存入储蓄存款帐户中，并在以后十二个月的每个月初存入￥200。那么一年后该帐户的存款额为： FV(4.5%/12, 12,-200,-2000,1) 计算结果为￥4,551.19。 计算某投资在给定期间内的支付利息 IPMT函数 IPmt 函数来计算每期的付款中有多少是属于利息 IPMT(rate,per,nper,pv,fv,type) Rate 为各期利率。 Per 用于计算其利息数额的期数，必须在 1 到 nper 之间。 Nper 为总投资期，即该项投资的付款期总数。

49、 Pv 为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。 Fv 为未来值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零）。 Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果省略 type，则假设其值为零。 Type 值 支付时间 0 期末 1 期初 说明 应确认所指定的 rate 和 nper 单位的一致性。例如，同样是四年期年利率为 12% 的贷款，如果按月支付，rate 应为 12%/12，nper 应为 4*12；如果按年支付，rate 应为 12%，nper 为 4

50、。 对于所有参数，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。 示例 A B 数据 说明 10% 年利率 1 用于计算其利息数额的期数 3 贷款的年限 8000 贷款的现值 公式 说明（结果） =IPMT(A2/12, A3*3, A4, A5) 在上述条件下贷款第一个月的利息(-22.41) =IPMT(A2, 3, A4, A5) 在上述条件下贷款最后一年的利息（按年支付）(-292.45) 注意利率除以 12 将得到月利率。现金支出的年限乘以 12 将得到应偿还的数额投资的净现值 NPV通过使用贴现率以及一系列未来支出（负值）和收入（正值），计算一项投资的净现

51、值。投资的净现值是指未来各期支出（负值）和收入（正值）的当前值的总和。 语法NPV(rate,value1,value2, .)Rateee 是某一期间的贴现率。Value1, value2, . 代表支出及收入的 1 到 29 个参数。Value1, value2, . 在时间上必须具有相等间隔，并且都发生在期末。 NPV 使用 value1, value2, . 的顺序来解释现金流的顺序。所以务必保证支出和收入的数额按正确的顺序输入。 如果参数为数字、空值、逻辑值或数字的文本表达式，则都会被计算在内；如果参数是错误值或不能转化为数字的文本，则将被忽略。 说明函数 NPV 假定投资开始于 v

52、alue1 现金流所在日期的前一期，并结束于列表中最后一笔现金流的当期。函数 NPV 依据未来的现金流来进行计算。如果第一笔现金流发生在第一个周期的期初，则第一笔现金必须添加到函数 NPV 的结果中，而不应包含在 values 参数中。有关详细信息，请参阅下面的示例。 如果 n 是值列表中的现金流的次数，则 NPV 的计算公式如下： 函数 NPV 与函数 PV（现值）相似。PV 与 NPV 之间的主要差别在于：函数 PV 允许现金流在期初或期末开始。与可变的 NPV 的现金流数值不同，PV 的每一笔现金流在整个投资中必须是固定的。有关年金与财务函数的详细信息，请参阅函数 PV。 函数 NPV

53、与函数 IRR（内部收益率）也有关，函数 IRR 是使 NPV 等于零的比率：NPV(IRR(.), .) = 0。 示例 1 在下面的示例中： Rate 是年贴现率。 Value1 一年前的初期投资。 Value2 第一年的收益。 Value3 第二年的收益。 Value4 第三年的收益。 Rate Value1 Value2 Value3 Value4 公式 说明（结果） 10% -10000 3000 4200 6800 =NPV(Rate, Value1, Value2, Value3, Value4) 此项投资的净现值 (1,188.44) 在上例中，将开始投资的 $10,000 作

54、为数值参数中的一个。因为此项付款发生在第一期的期末。示例 2在下面的示例中：Rate 年贴现率。可表示整个投资的通货膨胀率或利率。Value1 一年前的初期投资。Value2 第一年的收益。Value3 第二年的收益。Value4 第三年的收益。Value5 第四年的收益。Value6 第五年的收益。Rate Value1 Value2 Value3 Value4 Value5 Value6 公式 说明（结果） 8% 40000 8000 9200 10000 12000 14500 =NPV(Rate, Value2, Value3, Value4, Value5, Value6)+Valu

55、e1 此项投资的净现值 (1,922.06) 8% 40000 8000 9200 10000 12000 14500 =NPV(Rate, Value2, Value3, Value4, Value5, Value6, -9000)+Value1 此项投资的净现值，包括第六年中 9000 的赔付 (-3,749.47) 在上例中，一开始投资的 $40,000 并不包含在数值参数中，因为此项付款发生在第一期的期初。 例如假设第一年投资￥8,000，而未来三年中各年的收入分别为￥2,000，￥3,300和￥5,100。假定每年的贴现率是10%，则投资的净现值是： NPV(10%,-8000,20

56、00,3300,5800) 计算结果为：￥8208.98。该例中，将开始投资的￥8,000作为v参数的一部分，这是因为付款发生在第一期的期末。又如，假设要购买一家书店，投资成本为￥80,000，并且希望前五年的营业收入如下：￥16,000，￥18, 000，￥22,000，￥25,000，和￥30,000。每年的贴现率为8%（相当于通贷膨胀率或竞争投资的利率），如果书店的成本及收入分别存储在B1到B6中，下面的公式可以计算出书店投资的净现值： NPV（8%,B2:B6）+B1 计算结果为：￥6,504.47。在该例中，一开始投资的￥80,000并不包含在v参数中，因为此项付款发生在第一期的期初

57、。 假设该书店的营业到第六年时，要重新装修门面，估计要付出￥11,000，则六年后书店投资的净现值为： NPV（8%,B2:B6,-15000）+B1 计算结果为：-￥2,948.08 该项贷款的付款总期数 (nper）用途：基于固定利率及等额分期付款方式，返回某项投资的总期数。语法：NPer(rate, pmt, pv, fv, type)Rate为各期利率，是一固定值。例如，如果有一笔贷款年百分率 (APR) 为百分之十并按月付款的汽车贷款，则每一期的利率为 0.1/12 或 0.0083。Pmt 为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt 包括本金和利息，但不包括其

58、他的费用及税款。Pv 为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。 Fv 为未来值，或在最后一次付款后希望得到的现金余额。如果省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零）。Type 数字 0 或 1，用以指定各期的付款时间是在期初还是期末。如果贷款是在贷款周期结束时到期，请使用 0，如果贷款是在周期开始时到期，请使用 1。如果省略的话，缺省值为 0。示例： A2=12% 年利率 ；A3=-100 各期所付的金额 ；A4=-1000 现值；A5=10000 未来值 A6=1 各期的支付时间在期初 =NPER(A2/12, A3, A4

59、, A5, 1) 在上述条件下投资的总期数 (60) =NPER(A2/12, A3, A4, A5) 在上述条件下投资的总期数，不包括在期初的支付 (60) =NPER(A2/12, A3, A4) 在上述条件下投资的总期数，不包括未来值 (-9.578) ?对所有参数，用负数表示现金支出（如储蓄存款），而用正数表示现金收入（如红利支票）。NPerNPer 函数 返回一个 Double，指定定期定额支付且利率固定的总期数。语法NPer(rate, pmt, pv, fv, type)NPer 参数：部分描述rate必要。Double 指定每一期的利率。例如，如果有一笔贷款年百分率 (APR)

60、 为百分之十并按月付款的汽车贷款，则每一期的利率为 0.1/12 或 0.0083。pmt必要。Double 指定每一期所付金额。付款金额通常包含本金和利息，且付款金额在年金的有效期间不变。pv必要。Double 指定未来一系列付款或收款的现值。例如，当贷款买一辆汽车时，向贷方所借贷的金额为将来每月偿付给贷方款项的现值。fv可选。Variant 指定在付清贷款后所希望的未来值或现金结存。例如，贷款的未来值在贷款付清后为 0 美元。但是，如果想要在 18 年间存下 50，000 美元作为子女教育基金，那么 50，000 美元为未来值。如果省略的话，缺省值为 0。type可选。Variant 指定贷款到期时间。如果贷款是在贷款周期结束时到期