excel 教学案例

excel 教学案例上传: 汪娜更新时间：2012-5-16 11:55:35 第四章巧用公式、函数4.0 （标题）巧用公式、函数（概述）excel作为一个电子表格系统，除了进行一般的表格处理外，最主要的还是它的数据计算能力。

在excel中，我们可以在单元格中输入公式或者使用excel提供的函数来完成对工作表的计算。

在本章我们将学习有关在单元格中输入公式，单元格地址的引用，如何使用excel 函数以及使用自动求和按钮等。

本章的学习目标：掌握输入、编辑、复制公式的方法掌握为单元格或单元格区域命名的方法了解从身份证号码中提取个人信息的操作4.1输入公式（标题）输入公式（概述）使用公式有助于分析工作表中的数据。

公式可以用来执行各种运算，如加法、乘法或者对工作表中的数据进行比较。

当我们要向工作表输入计算的数值时，就可以使用公式。

若要在工作表单元格中输入公式，则可以在编辑栏中键入这些元素的组合。

输入公式的操作类似于输入文字型数据。

不同的是我们在输入一个公式的时候总是以一个等号“=”作为开头，然后才是公式的表达式。

在一个公式中可以包含各种算术运算符、常量、变量、函数、单元格地址等输入公式的方法主要有两种：利用函数向导输入和手动输入，下面我们分别来学习这两种输入公式的方法1、利用函数向导输入excel为我们提供了很多函数，而且提供了函数向导，使用函数向导可以很方便的向单元格内输入公式，下面我们就来学习利用函数向导输入函数首先我们选中要插入函数的单元格“f2”然后单击【插入】菜单选择【函数】弹出“插入函数”窗口在这个窗口中选择要插入的函数以及函数参数我们要计算的是“外语”、“数学”、“语文”三科成绩的平均分，所以我们要选择平均值函数在“选择函数”列表中选择“average”“average”函数计算平均值的函数，还有一些函数，如“sum”是求和函数，“count”是计数函数，“max”是求最大值函数，“min”是求最小值函数等，大家可以在以后的应用中逐渐熟悉单击【确定】按钮打开“函数参数”窗口，在这个窗口进行函数参数的选择这里“number1”和“number2”是需要求平均值的数值或引用单元格区域，参数可以有多个，但是最多不超过30个单击“number1”后面的按钮，进入区域选择状态在工作表上拖动鼠标选择函数参数范围“b2：d2”再次单击“number1”后面的按钮，恢复“函数参数”窗口单击【确定】按钮这样我们就将“外语”、“数学”和“语文”三科的平均分填到单元格“f2”中使用average函数可以快速求出函数的平均值，使计算更加方便2、手动输入除了使用函数向导向单元格中插入函数，还可以手动输入函数对单元格内容进行计算这里我们要计算的是三科成绩的平均值，我们使用四则运算的方法计算选择单元格“f3”在单元格编辑窗口输入公式“＝（b3+c3＋d3）/3”我们输入的公式中，“b3＋c3＋d3”（读为b3加c3加d3）是计算三科成绩的和，“/3”（读为除3）计算的是三科的平均值单击编辑栏上的【输入】按钮单元格“f3”中的就会显示出我们所求的平均分，而且f3中数值有6位小数，这里小数的位数可以通过【格式】→【单元格】命令进行修改4.2复制公式（标题）复制公式（概述）我们在编辑文档的过程中，会遇到在多个单元格中输入相同公式的操作，多次重复输入公式会浪费很多时间，我们可不可以使用“复制”命令复制公式呢？复制公式的操作和复制普通文档内容的操作有什么区别吗？复制公式即是将公式应用于其他单元格的操作，最常用的有以下几种方法，我们分别来进行学习1、拖动复制选中存放公式的单元格将鼠标移动到单元格右下角当光标变成“实心十字”形状时按住鼠标左键沿列(对行计算时)拖动到数据结尾这样我们就完成了复制公式的操作接下来我们学习另一种复制公式的方法2、输入复制这种方法是在公式输入结束后立即完成公式的复制，所以操作的步骤和上面的有些区别，具体的操作步骤是：选中需要应用公式的所有单元格在公式编辑窗口中输入公式“=average (b2:d2)”完成后同时按下键盘上的【ctrl】和【enter】键这个公式就会被复制到已选中的所有单元格中复制的公式的方法还有一种，利用“选择性粘贴”来复制公式，下面我们来学习3、选择性粘贴法复制首先选中存放要复制公式的单元格单击工具栏中的【复制】按钮然后选中需要使用该公式的所有单元格在选中区域内单击鼠标右键右键右键右键在弹出的快捷菜中单选择【选择性粘贴】弹出“选择性粘贴”窗口我们在“选择性粘贴”窗口设置粘贴内容，在这里可以设置粘贴内容为公式、数值、格式等，还可以对粘贴内容进行简单的加、减、乘、除运算等这里我们要粘贴的内容是公式所以选中“粘贴”中的“公式”单选钮单击【确定】按钮选中的公式就被复制到已选中的所有单元格4.3绝对引用和相对引用（标题）绝对引用和相对引用（概述）在我们创建的公式中，会将单元格或者单元格区域作为函数参数，参数的引用有相对引用和绝对引用，什么是相对引用、绝对引用呢？它们有什么区别？什么情况下应该使用相对引用，哪些情况下又需要使用绝对引用呢？我们先来了解绝对引用和相对引用的概念：相对引用是指在公式被复制粘贴到新位置时会针对新位置发生相应变化的单元格引用；绝对引用是指在公式被复制粘贴到其他位置时不会变化的单元格引用。

绝对引用用$符来表示单元格d11中的引用就是绝对引用（在画面上有显示，用鼠标指到公式上）相反，相对引用则不使用$符单元格d3中的引用就是相对引用（在画面上有显示，用鼠标指到公式上）我们在公式中对单元格或区域的引用绝大多数是相对引用那么，什么时候使用绝对引用呢？我们在复制公式的时候希望粘贴单元格中的内容与复制的单元格中的内容不变时，可以使用单元格的绝对引用下面我们以一个例子来更好的理解相对引用和绝对引用的概念单元格d3中的公式表示的是总价格“=b3*c3”（在画面上有显示，用鼠标指到公式上）这个公式使用的是相对单元格引用我们将单元格复制到d4中公式会变成“=b4*c4”如果d11中的单元格引用采用绝对引用，结果会是什么样呢？我们把单元格d11中的公式“=$b$11*$c$11”复制到d12中在这种情况下，单元格d12中的公式会与单元格d11中的公式完全相同，会产生与d11中同样的结果，这并不是我们希望得到句结果，所以是错误的使用下面我们把刚才的例子扩展到计算销售税的问题上我们在d5中输入如下公式“=b5*c5*$b$7”d5单元格中公式的含义是：价格乘以数量，再乘以存储在单元格b7中的销售税税率，这里对b7的引用是绝对引用我们将d5中的公式复制到d6单元格d6单元格显示的公式为“=b6*c6*$b$7”这里对单元格b5和c5的引用发生了变化，但是对单元格b7的引用没有发生变化——这也正是我们想要的结果4.4自动求和技巧（标题）自动求和技巧（概述）几乎每个excel用户都知道位于标准工具栏上的【自动求和】按钮，单击这个按钮，excel 会分析活动单元格周围的数据，然后给出一个sum公式，再次单击这个按钮，公式便会插入到工作表中，这是【自动求和】的标准用法。

其实这个小小的按钮中还有一些其它的技巧，下面我们就一起来学习【自动求和】的几个技巧。

1、插入其它功能的公式我们来看一下，【自动求和】按钮的右面有一个小箭头，它是干什么的呢？单击【自动求和】按钮右面的小箭头可以看到另外5个选项菜单，单击其中平均值、计数、最大值、最小值的任意一项，excel 都会给出相应的公式这里特别要说明的是“其他函数”的选项单击【其他函数】可以显示出“插入函数”窗口，应用不同的函数进行计算2、同时为多项求和使用【自动求和】按钮，还可以同时为表格中的多项数值求和我们选中工作表中的“b2：e12”区域这里第12行和第e列是我们多选出来的单元格，分别用来存放行与列求和后的数值单击【自动求和】按钮excel自动将行、列数值的和存放到第12行和第e列的单元格中4.5为数值排队（标题）为数值排队（概述）很多时候，对一个区域内的数值进行排队能对我们的工作提供很大的帮助。

例如，我们对公司中20位销售人员的销售情况进行排位，那么一眼就可以看出每个人的销售成绩。

那么在excel中怎么对区域数值进行排队呢？这一节我们就一起来学习解决办法。

excel为我们提供了一个函数——rank函数来帮助我们对一定数据区域内的数据进行排队我们先来了解一下rank函数rank函数是excel进行排序的主要工具，它的语法为：rank (number，ref，order)，其中number为参与计算的数字或含有数字的单元格，ref是对参与计算的数字单元格区域的引用，order是用来说明排序方式的数字，如果order为零或省略，则以降序方式给出结果，反之按升序方式下面我们就来利用rank函数对学生的外语成绩进行排序这里判断b2单元格内的成绩在所有成绩中的名次如果要把学生的名次放到c列中，这里选择单元格c2然后单击【插入】菜单选择【函数】弹出“插入函数”窗口在“选择函数”列表中选择“rank”单击【确定】按钮这时候excel会弹出“函数参数”窗口在这个窗口中我们设置rank函数的参数“number”中的数值应该是需要排位的数字这里需要排位的数字是b2单击b2单元格，b2中的数值就出现在“number”后面的文本框中当然，这里我们也可以直接输入数值，但是直接输入的时候容易出现错误，所以建议大家还是采用选择单元格形式进行参数的选择“ref”中的参数应该是一个序列这里参与计算的单元格区域是“b2：b11”序列，所以向“ref”文本框中输入“b2:b11”单击【确定】按钮可以看到c2单元格的内容就是b2中的成绩在整体成绩中的位次这里，我们将“order”值省略，所以默认的排序方式是降序，如果“order”值为1或者其他自然数，排序方式则为升序排列我们可以通过复制公式的方法对其他数值进行排序4.6利用身份证号码提取个人信息（标题）利用身份证号码提取个人信息（概述）身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。

如果在实际操作中可以从这些身份证号码中将上述个人信息提取出来，不仅快速简便，而且不容易出错。

核对时也只需要对身份证号码进行检查，肯定可以大大提高工作效率。

可是怎么进行提取操作呢？我们先来分析一下身份证号码的组成我国的身份证号码目前有15位和18位之分，我们来分别分析一下：15位号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期18位号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、第14位代表出生日期例如，身份证“320521************”中，“1981”为出生年份，“08”为出生月份，“07”为出生日期分析完身份证号码的组成之后，我们开始从身份证中提取个人信息这里需要使用if、len和mid函数我们先来看这三种函数的定义和语法先来看if函数：if函数是一个条件函数，它的语法结构是：if(logical_test,value_if_true,value_if_false)，它有三个参数：第一个参数是一个条件，第二个和第三个参数是结果，这个结果可以是数值也可以是公式。