EXCEL教程.docx
《EXCEL教程.docx》由会员分享，可在线阅读，更多相关《EXCEL教程.docx（132页珍藏版）》请在冰点文库上搜索。
EXCEL教程
教你四招--轻松让你成为Excel函数使用高手
Excel是目前使用比较广泛的数据管理分析软件，其强大的内置函数几乎无所不能，但由于许多用户不知道函数的作用，不熟悉函数使用的格式，即使用错了，也不知错在哪里。
Excel2002/2003帮助我们解决了许多这方面的问题，与Excel2000及以前的版本相比，其提供的智能公式功能将引领我们进入它的函数世界，轻松让你成为Excel的使用高手，从而可以更加方便快捷地满足我们日常办公的需要。
1．常见公式用按钮
在Excel中，我们经常遇到求和、求平均值、求最大/小值的问题。
早期的Excel版本在工具栏中提供了“自动求和（∑）”按钮，通过它可以很方便地对单元格区域求和，但求平均值和最大/小值一般只能通过手工输入公式实现。
在Excel2002/2003中，单击“自动求和”按钮右侧的下拉按钮，我们会发现多出了“平均值”、“计数”、“最大值”、“最小值”等命令，通过这些命令，我们可以很方便地求出所选单元格区域的平均值（average）、单元格个数（count）、最大值（max）和最小值（min）。
将原本要用函数来计算的数据，现在通过工具栏按钮就轻松实现了（如图1）。
图1“自动求和”下拉菜单
2．插入函数帮你搜
如果我们在数据分析时要用到某个功能，但不知用什么函数才能实现这一操作，我们可以借助Excel2002/2003中提供的“搜索函数”功能来实现。
假设我们想了解与“贷款”有关的函数，只需单击“插入”菜单中的“函数”命令，弹出“插入函数”对话框，在“搜索函数”框中输入要搜索函数功能的关键字，单击“转到”按钮就可以看到Excel推荐你使用的函数了。
3．应用格式看清楚
对一些不常用的Excel函数，用户在使用时往往容易出现格式输入错误，这时Excel2002/2003会人性化地提醒你，从而确保公式输入的正确性。
在图2中我们也可以看到，输入公式时，系统会将当前引用的单元格（或单元格区域）用蓝框选中，以便于用户检查输入时有没有引用错误。
另外，在套用函数格式时（如“参数”），系统也会在函数的下方用粗体将当前参数标记出来，这样就一目了然了。
图2检查格式输入错误
4．提示信息查错误
即使有了人性化的提醒，在输入公式时也难免会出现一些如“#NAME？
”、“#VALUE！
”等错误的提示，用户往往不知道错在哪儿，Excel2002/2003中我们会发现，在出错单元格的左侧（或右侧）多出一个带有错误警告提示的“！
”号（如图3），单击其右侧的下拉按钮，我们就可通过系统帮助知道错在哪儿了。
图3查看单元格错误
巧妙处理有多个合并单元格的复杂Excel表格
Excel是功能非常强大的表格处理软件，特别是其表格计算能力，方便、快捷和自动化运算，使越来越多的人选择采用EXCEL来处理日常办公表格。
但是，当碰到一些“不规则”的表格时，许多初学者感到十分棘手。
表格“不规则”，主要表现为合并单元格太多，单元格或上下不对齐，或左右不对齐，复杂的甚至多行、多列不对齐。
例如图1中的中美MBA报名表：
图1
细看上表，发现“职务”单元格和“性别”单元格上下不对齐、“手机”和“行业”不对齐、“企业性质”右边线和“E-mail”左边线不对齐等等。
许多初学者做这样的表格时，总习惯于“性别”、“职务”、“手机”、“行业”、“邮编”、“家庭电话”、“办公电话”、“企业性质”、“E-mail”都按1行1列算，“照片”按4行1列来算，做出来的表格结果就是:
“性别”与“职务”上下对齐，“手机”、“行业”、“邮编”上下对齐，“家庭电话”、“办公电话”、“企业性质”、“E-mail”上下对齐，无论我们再怎么调整列宽，上述单元格都是对齐的，达不到我们想要的表格效果。
见下图:
图2
那么，到底怎样才能做成上下不对齐的效果呢?
其实并不难，我们现在以调整“性别”和“职务”为例，当“性别”和“职务”对齐的时候，这两个单元格所占的空间是1行1列（见图2），当它们不对齐的时候，细心的人会很快发现，它们的行列已然发生了变化，为了方便读者观察，我们将图1中的这两个单元格的左右竖线分别用虚线画出延长线，效果如下:
图3
当“性别”与“职务”不对齐时，“性别”前空格、“性别”、“职务”都是占1行2列，“职务”后空格占1行3列。
因此，我们可以在图2表格中的“性别”单元格前后分别各插入1列，效果如下;
图4
然后，将“性别”前两个空格合并，“性别”往右合并1格，“职务”往左合并1格，“职务”后两个空格合并，效果如下图:
图5
将上图中相应的列调到合适的宽度，就可以实现“性别”和“职务”的上下不对齐效果了。
见下图:
运用相同的办法，将“行业”与“手机”、“企业性质”与“E-mail”都调成上下不对齐，就可完成整个表格的制作了。
效果如下图;
好用的工具最深入分析Excel强大的公式函数
Excel之所以具备如此强大的数据分析与处理功能，公式（包括函数）起了非常重要作用。
要想有效的提高自己的Excel应用水平和工作效率，提高公式（包括函数）的应用能力是非常有效的途径之一。
本课介绍使用Excel公式的相关基本知识，后面课程中我们会详细介绍相关的两个重要工具：
引用和函数，再通过几个典型实际任务的操作锻炼我们的实际应用能力。
一、初步了解Excel公式
1.什么是公式
前面已经介绍过，我们把“公式”列为不同于“数值”和“文本”之外的第三种数据类型。
公式的共同特点是以“=”号开头，它可以是简单的数学式，也可以是包含各种Excel函数的式子。
2.公式由哪些元素组成
输入到单元格中的公式均由等号开头，等号后面由如下五种元素组成：
运算符：
例如“+”或者“*”号。
单元格引用：
它包括单个的单元格或多个单元格组成的范围，以及命名的单元格区域。
这些单元格或范围可以是同一工作表中的，也可以是同一工作薄其他工作表中的，甚至是其他工作薄工作表中的。
数值或文本：
前面介绍过的两种数据类型。
例如，“100”或“新悦国际教育集团”。
工作表函数：
可以是Excel内置的函数，如SUM或MAX，也可以是自定义的函数。
括号：
即“（”和“）”。
它们用来控制公式中各表达式被处理的优先权。
了解公式的组成是创建公式必备的基础。
另外，Excel工作表中的公式最多可以由1024个字符组成，我们日常所创建的公式，超过100个字符的已经非常少，因此这个长度足够我们使用了。
看看公式长什么样子
二、公式能做什么?
简单地说，我们建立一个公式，提供给它相关的数据信息，目的是希望公式给我们一个答案或计算结果。
从这一点上看，Excel公式和我们在数学中学过的公式的功能是一样的。
所以，在以后的工作中，可以看到很多公式都是我们所熟知的数学式。
除此之外，由于Excel的公式还可以包含Excel的各种函数。
因此，Excel公式除了可以实现传统的数学式具有的计算功能外，还可以执行很多特殊的任务。
比如，结合IF函数，可以让公式具有选择性，根据不同的条件，得出不同的结果。
后面的“计算业绩奖金”实际任务中，将会详细介绍在公式中嵌套IF函数解决问题。
Excel的内置函数有300多个，在公式中灵活使用这些函数，可以极大地提高公式解决问题的能力，轻松胜任各种复杂的任务。
比如下图所示的三个公式功能举例：
第一个公式自动计算出单价与数量的乘积得到金额，第二个公式自动计算所有销售额的和，第三个公式将姓和名合并在一起得到全名。
公式功能举例
下面我们通过一些简单的具体实例，加强对公式的感性认识
三、公式的简单应用实例
下面我们通过一些简单的实例，加强对公式的感性认识。
1.计算商品金额数值
下图所示的工作表中的A2和B2单元格分别存放着某种商品的“单价”和“数量”数值，为了计算“金额”数值，可以在D2单元格中输入公式“=250*8”（图1），通过后面的学习我们会知道，像这样使用具体数值建立的公式，适应性很差，因此用得也很少。
图1
在D3单元格中输入公式“=A2*B2”，也可计算出“金额”数值（图2），这个公式使用了引用，使得公式得适应性加强。
关于在公式中使用引用的知识后面会详细介绍。
图2
2.计算销售额总和
下图所示的工作表中的B2和B11单元格分别存放10天的销售额，为了计算这些天的销售额总和，可以在D2单元格中输入公式“=B2+B3+B4+B5+B6+B7+B8+B9+B10+B11”（图3），这个公式和数学中的求和公式没什么两样，不过由于需要求和的项目太多，所以公式看起来比较长。
图3
为了简化公式，我们也可以在公式中使用函数实现同样的功能。
选中D3单元格，输入公式“=SUM（B2:
B11）”，可以看到得出的计算结果是一样的（图4），其中SUM为Excel的内置函数，功能为求出各项数值之和。
图4
.用公式合并字符
下图所示的工作表中的A2和B2单元格分别存放着“姓”和“名”字符，为了得出“姓名”字符串，可以在D2单元格中输入公式“=A2&B2”（图5），其中“&”是一个运算符号，我们称它为连接符号，它的功能为把两个文本字符或文本字符串连接起来。
图5
除了这种方法外，我们也可以使用函数达到同样的目的。
选中D3单元格，输入公式“=CONCATENATE（A2,B2）”，回车后得到的结果和前面的公式是一致的（图6）。
图6
其中函数CONCATENATE的功能为将几个文本字符串合并为一个文本字符串。
应用Excel公式的技巧
Excel2000是办公室中常用的软件，我们用它制作表格的时候，经常需要进行增加一列、删除一列等操作，这时就需要调整表格中的公式。
如果人工逐个去调整相关的公式，工作量很大而且容易出错。
笔者在使用Excel2000制作表格、填写公式的时候，摸索到以下技巧：
为了叙述方便，我们以工资表为例。
表格的结构如下：
序
号
姓
名
基
本
工
资
岗
位
工
资
补
贴
扣
除
大
病
统
筹
住
房
养
老
税
金
应
发
实
发
最好使用函数
使用公式最大的好处在于，日后修改表格结构的时候，公式会自动进行修改，而函数还可以完成一些复杂的操作。
在上表中税金的公式就必须利用函数，由于税金要根据不同的情况进行不同的处理，这时就只能利用IF（）函数进行判断了。
有的人也许会觉得使用函数麻烦，其实不必担心，使用Excel中的函数向导功能可以帮助你完成函数的填写。
先把光标定位在需要使用函数的单元格中，单击“常用工具栏”中的“粘贴函数”按钮；或使用菜单中的“插入/函数”选项，
在目标单元格中就会出现一个“=”号表示这是一个公式，同时会出现“粘贴函数”对话框（如图1），框中左边为函数的分类，右边为每一类函数中具体的函数。
在右边选中一个函数，下边会出现该函数的功能解释，单击“确定”。
图1
选定了一个函数并“确定”后，会出现如图2所示的引导界面，在这里函数向导会一步一步地指导我们填入函数所需要的各个参数，给出每个参数的含义以及需要的数据类型，并给出最终的计算结果。
相对公式与绝对公式
所谓相对公式和绝对公式是指在公式中如何引用一个单元格。
图2
在相对公式中，引用单元格的时候，直接填写单元格的坐标即可。
其特点是，当我们复制一个公式的时候，它的坐标会发生变化。
例如：
在A5中填入SUM（A1:
A4）的意思是求A1到A4所有单元格中数值的总和，我们把它复制到B5的时候，公式就会变成SUM（B1:
B4）。
复制公式时变化的规律是，相对于当前单元格，向右移动几列，公式的列坐标就增加几，向下移动几行，行坐标就增加几；如果向左或向上移动，坐标就减掉移动的行数或列数。
使用相对公式的好处在于，当我们复制或移动公式的时候，公式中的坐标会自动发生变化而不需要我们再去修改它。
但有时候，我们在几个公式中都需要引用同一个单元格，这时，采用相对公式就不合适了。
我们可以采用绝对公式：
绝对公式是指公式中的行坐标或列坐标被固定的坐标。
我们都知道，在Excel中引用一个单元格的方法是指明它的列坐标和行坐标。
例如：
A1表示A列第一行。
如果我们在一个单元格的行坐标或列坐标前面加上一个“狈牛蛳嘤Φ男凶昊蛄凶昃捅还潭ㄏ吕戳恕＞怨降奶氐闶牵诟粗乒交蛞贫降氖焙颍还潭ㄗ〉男凶昊蛄凶瓴换岱⑸浠＠缭贐7中的公式为：
=**B6，如果复制到C7中公式变化为：
=**C6。
由于公式中的“B6”没有被固定，所以发生了变化，而“B1”和“B2”被固定了，因而没有发生变化。
需要注意的是，在使用绝对公式的时候，如果需要固定为某一个单元格
需要在行坐标和列坐标前面均加上“薄Ｈ绻辉谛凶昊蛄凶甑那懊婕由稀氨表明只固定行坐标或列坐标。
添加一列时公式的变化
当我们在表格中增加一列而新增加的列要添加到公式中参与计算的时候，需要调整公式，运用以下规律，可使得在增加列时不必调整公式。
（1）适当增加一些空白列，而且这些空白列放到公式中。
（2）增加新列时，不要添加到公式包括的范围之外，而应该放在公式包括的范围之间，不过这只在使用函数的时候起作用。
（3）尽量使用函数，而不要使用诸如“A1+A3”这种形式的公式。
例如：
在工资表中，应发工资出的公式为：
=SUM（C2:
E2）-SUM（F2:
I2）。
其中SUM（C2:
E2）计算的是所有应发项的和；SUM（F2:
I2）计算的是所有扣除项的和。
每年都需要发放煤火费，这样就需要增加一列。
在增加新列的时候，注意一下加入的位置：
如果我们加在E2和F2之间的话就必须要调整应发工资处的公式，以便把煤火费一项加入公式；但如果我们在C2到E2之间添加一列的话，则新增加的列会自动添加到公式中。
这时应发工资处的公式自动调整为：
=SUM（C2:
F2）-SUM（G2:
K2）。
（4）公式的保护
当做好一个表格，填写好各种公式后，一定不希望公式被误操作而破坏。
要保护一个单元格的内容（包括公式）需要做到：
这些单元格处于“锁定”状态，方法是：
选中单元格，使菜单“格式/单元格/保护”中的“锁定”处于选中状态；发出保护工作表命令，方法是：
点击“工具/保护/保护工作表”。
注意：
在默认状态下，所有的单元格都处于锁定状态。
如果直接点击“保护工作表”命令，则所有的单元格都无法输入数据。
所以应该先把所有的单元格解除“锁定”，再把需要保护的单元格进行“锁定”。
Excel函数应用之函数简介
Excel的数据处理功能在现有的文字处理软件中可以说是独占鳌头，几乎没有什么软件能够与它匹敌。
在您学会了Excel的基本操作后，是不是觉得自己一直局限在Excel的操作界面中，而对于Excel的函数功能却始终停留在求和、求平均值等简单的函数应用上呢？
难道Excel只能做这些简单的工作吗？
其实不然，函数作为Excel处理数据的一个最重要手段，功能是十分强大的，在生活和工作实践中可以有多种应用，您甚至可以用Excel来设计复杂的统计管理表格或者小型的数据库系统。
请跟随笔者开始Excel的函数之旅。
这里，笔者先假设您已经对于Excel的基本操作有了一定的认识。
首先我们先来了解一些与函数有关的知识。
一、什么是函数
Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。
用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。
例如，SUM函数对单元格或单元格区域进行加法运算。
术语说明
什么是参数？
参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的错误值或单元格引用。
给定的参数必须能产生有效的值。
参数也可以是常量、公式或其它函数。
参数不仅仅是常量、公式或函数，还可以是数组、单元格引用等：
1.数组--用于建立可产生多个结果或可对存放在行和列中的一组参数进行运算的单个公式。
在MicrosoftExcel有两类数组：
区域数组和常量数组。
区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式；常量数组将一组给定的常量用作某个公式中的参数。
2.单元格引用--用于表示单元格在工作表所处位置的坐标值。
例如，显示在第B列和第3行交叉处的单元格，其引用形式为"B3"。
3.常量--常量是直接键入到单元格或公式中的数字或文本值，或由名称所代表的数字或文本值。
例如，日期10/9/96、数字210和文本"QuarterlyEarnings"都是常量。
公式或由公式得出的数值都不是常量。
函数是否可以是多重的呢？
也就是说一个函数是否可以是另一个函数的参数呢？
当然可以，这就是嵌套函数的含义。
所谓嵌套函数，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。
例如图1中所示的公式使用了嵌套的AVERAGE函数，并将结果与50相比较。
这个公式的含义是：
如果单元格F2到F5的平均值大于50，则求F2到F5的和，否则显示数值0。
图1嵌套函数
在学习Excel函数之前，我们需要对于函数的结构做以必要的了解。
如图2所示，函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。
如果函数以公式的形式出现，请在函数名称前面键入等号（=）。
在创建包含函数的公式时，公式选项板将提供相关的帮助。
图2函数的结构
公式选项板--帮助创建或编辑公式的工具，还可提供有关函数及其参数的信息。
单击编辑栏中的"编辑公式"按钮，或是单击"常用"工具栏中的"粘贴函数"按钮之后，就会在编辑栏下面出现公式选项板。
整个过程如图3所示。
图3公式选项板
二、使用函数的步骤
在Excel中如何使用函数呢？
1.单击需要输入函数的单元格，如图4所示，单击单元格C1，出现编辑栏
图4单元格编辑
2.单击编辑栏中"编辑公式"按钮，将会在编辑栏下面出现一个"公式选项板"，此时"名称"框将变成"函数"按钮，如图3所示。
3.单击"函数"按钮右端的箭头，打开函数列表框，从中选择所需的函数；
图5函数列表框
4.当选中所需的函数后，Excel2000将打开"公式选项板"。
用户可以在这个选项板中输入函数的参数，当输入完参数后，在"公式选项板"中还将显示函数计算的结果；
5.单击"确定"按钮，即可完成函数的输入；
6.如果列表中没有所需的函数，可以单击"其它函数"选项，打开"粘贴函数"对话框，用户可以从中选择所需的函数，然后单击"确定"按钮返回到"公式选项板"对话框。
在了解了函数的基本知识及使用方法后，请跟随笔者一起寻找Excel提供的各种函数。
您可以通过单击插入栏中的"函数"看到所有的函数。
图6粘贴函数列表
三、函数的种类
Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。
1.数据库函数--当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库工作表函数。
例如，在一个包含销售信息的数据清单中，可以计算出所有销售数值大于1,000且小于2,500的行或记录的总数。
MicrosoftExcel共有12个工作表函数用于对存储在数据清单或数据库中的数据进行分析，这些函数的统一名称为Dfunctions，也称为D函数，每个函数均有三个相同的参数：
database、field和criteria。
这些参数指向数据库函数所使用的工作表区域。
其中参数database为工作表上包含数据清单的区域。
参数field为需要汇总的列的标志。
参数criteria为工作表上包含指定条件的区域。
Excel进阶
●Excel进阶技巧
（一）
●Excel进阶技巧
（二）
●Excel进阶技巧（三）
●Excel进阶技巧（四）
●Excel进阶技巧（五）
2.日期与时间函数--通过日期与时间函数，可以在公式中分析和处理日期值和时间值。
3.工程函数--工程工作表函数用于工程分析。
这类函数中的大多数可分为三种类型：
对复数进行处理的函数、在不同的数字系统（如十进制系统、十六进制系统、八进制系统和二进制系统）间进行数值转换的函数、在不同的度量系统中进行数值转换的函数。
4.财务函数--财务函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。
财务函数中常见的参数：
未来值（fv）--在所有付款发生后的投资或贷款的价值。
期间数（nper）--投资的总支付期间数。
付款（pmt）--对于一项投资或贷款的定期支付数额。
现值（pv）--在投资期初的投资或贷款的价值。
例如，贷款的现值为所借入的本金数额。
利率（rate）--投资或贷款的利率或贴现率。
类型（type）--付款期间内进行支付的间隔，如在月初或月末。
5.信息函数--可以使用信息工作表函数确定存储在单元格中的数据的类型。
信息函数包含一组称为IS的工作表函数，在单元格满足条件时返回TRUE。
例如，如果单元格包含一个偶数值，ISEVEN工作表函数返回TRUE。
如果需要确定某个单元格区域中是否存在空白单元格，可以使用COUNTBLANK工作表函数对单元格区域中的空白单元格进行计数，或者使用ISBLANK工作表函数确定区域中的某个单元格是否为空。
6.逻辑函数--使用逻辑函数可以进行真假值判断，或者进行复合检验。
例如，可以使用IF函数确定条件为真还是假，并由此返回不同的数值。
7.查询和引用函数--当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。
例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用VLOOKUP工作表函数。
如果需要确定数据清单中数值的位置，可以使用MATCH工作表函数。
8.数学和三角函数--通过数学和三角函数，可以处理简单的计算，例如对数字取整、计算单元格区域中的数值总和或复杂计算。
9.统计函数--统计工作表函数用于对数据区域进行统计分析。
例如，统计工作表函数可以提供由一组给定值绘制出的直线的相关信息，如直线的斜率和y轴截距，或构成直线的实际点数值。
10.文本函数--通过文本函数，可以在公式中处理文字串。
例如，可以改变大小写或确定文字串的长度。
可以将日期插入文字串或连接在文字串上。
下面的公式为一个示例，借以说明如何使用函数TODAY和函数TEXT来创建一条信息，该信息包含着当前日期并将日期以"dd-mm-yy"的格式表示。
11.用户自定义函数--如果要在公式或计算中使用特别复杂的计算，而工作表函数又无法满足需要，则需要创建用户自定义函数。
这些函数，称为用户自定义函数，可以通过使用VisualBasicforApplications来创建。
Excel函数应用之数学和三角函数
一、与求和有关
升级会员 