Excel中快速定位的技巧实例

Excel中快速定位的技巧实例

在Excel中，我们需要到达某一单元格，一般是使用鼠标拖动滚动条来进行，但如果数据范围超出一屏幕显示范围或数据行数非常多时，想快速定位到某一单元格可要有点麻烦了。其实我们可以使用“定位”功能迅速到达想要的单元格。

例1：需要选中Y2008单元格（或快速移动到Y2008单元格），我们可以使用“编辑/定位”菜单，在引用位置里输入“Y2008”后按回车即可。

例2：需要选中Y列的2004～2008行的单元格，我们按照相同的方法，在引用位置里输入“Y2004:Y2008”按回车即可。

例3：需要选中2008行的单元格，我们可以在引用位置里输入“2008:2008”按回车即可。

例4：需要选中2004～2008行的单元格，我们可以在引用位置里输入“2004:2008”按回车即可。

Office技巧:用好Excel强大的排序功能

在用Excel制作相关的数据表格时，我们可以利用其强大的排序功能，浏览、查询、统计相关的数字。下面，我们以图1所示的“员工基本情况登记表”为例，来全面体验一番Excel

的排序功能。

一、快速排序

如果我们希望对员工资料按某列属性（如“工龄”由长到短）进行排列，可以这样操作：选中“工龄”列任意一个单元格（如I3），然后按一下“常用”工具栏上的“降序排序”按

钮即可（参见图1）。

小提示：①如果按“常用”工具栏上的“升序排序”按钮，则将“工龄”由短到长进行排序。②如果排序的对象是中文字符，则按“汉语拼音”顺序排序。③如果排序的对象是西

文字符，则按“西文字母”顺序排序。

图1

二、多条件排序

如果我们需要按“学历、工龄、职称”对数据进行排序，可以这样操作：选中数据表格中任意一个单元格，执行“数据→排序”命令，打开“排序”对话框（图2），将“主要关键词、次要关键词、第三关键词”分别设置为“学历、工龄、职称”，并设置好排序方式（“升

序”或“降序”），再按下“确定”按钮就行了。

图2

三、按笔划排序

对“姓名”进行排序时，国人喜欢按“姓氏笔划”来进行：选中姓名列任意一个单元格，执行“数据→排序”命令，打开“排序”对话框（参见图2），单击其中的“选项”按钮，打开“排序选项”对话框（图3），选中其中的“笔划排序”选项，确定返回到“排序”对

话框，再按下“确定”按钮即可。

图3

小提示：如果需要按某行属性对数据进行排序，我们只要在上述“排序选项”对话框中选

中“按行排序”选项即可。

四、自定义排序

当我们对“职称”列进行排序时，无论是按“拼音”还是“笔划”，都不符合我们的要求。

对于这个问题，我们可以通过自定义序列来进行排序：

先把相应的职称序列按需要排序的顺序输入到相应的单元格区域（如N2至N18）中（图4）；执行“工具→选项”命令，打开“选项”对话框，切换到“自定义序列”标签下，在“从单元格中导入序列”右侧的方框中输入“$N$2:$N$18”（也可以用鼠标选择输入），

然后单击“导入”按钮，将相应的序列导入到系统中，确定返回。

图4

小提示：序列导入后，原来N2至N18区域中输入的数据可以删除，导入的序列在其他

Excel文档中均可直接使用。

选中“职称”列任意一个单元格，执行“数据→排序”命令，打开“排序”对话框，单击其中的“选项”按钮，打开“排序选项”对话框（参见图3），按“自定义排序次序”选项右侧的下拉按钮，在随后弹出的下拉列表中，选中上述“导入”的序列，确定返回到“排

序”对话框，再按下“确定”按钮即可。

五、用函数进行排序

有时，我们对某些数值列（如“工龄、工资”等）进行排序时，不希望打乱表格原有数据的顺序，而只需要得到一个排列名次。对于这个问题，我们可以用函数来实现（以“工龄”为例）：在“工龄”右侧插入一个空白列（J列），用于保存次序（图5），然后选中J2单元格，输入公式：=RANK(I2,$I$2:$I$101)，然后再次选中J2单元格，将鼠标移至该单元格右下角成“细十字线状”时（这种状态，我们通常称之为“填充柄”状态），按住左键向下

拖拉至最后一条数据为止，次序即刻显示出来（图5）。

图5

小提示：若要升序排序，可在公式最后增加一个“非零”参数，如将上述公式改为：

=RANK(I2,$I$2:$I$101,1)。

六、让序号不参与排序

当我们对数据表进行排序操作后，通常位于第一列的序号也被打乱了，如何不让这个“序号”列参与排序呢？我们在“序号”列右侧插入一个空白列（B列），将“序号”列与数据

表隔开。用上述方法对右侧的数据区域进行排序时，“序号”列就不参与排序了。

小提示：插入的空列会影响表格的打印效果，我们可以将其隐藏起来：选中B列（即插入

的空列），右击鼠标，再选择“隐藏”选项即可。

常规方法求和引起的名次错乱

学期末，学生科长把我叫到他的办公室，让我为他重新计算一下本学期各班的量化分数。因为他计算出的各班量化分数有差错，造成各班名次不准确，失去了评比的公正性。并说这个班级名次急用，在今天下午的放假会上公布。

一、学校基本情况

我们学校共有初一、初二、初三三个年级，每个年级十个教学班，共有三十个教学班，本学期共二十周。学生科长建立的各班量化分数表请见图1（第一周二年级、三年级各班量化分数以及第二周到二十周各班量化分数均已隐藏）。

图1

二、量化出错原因

学生科长是按这样的方法来统计各班量化总分的：他在N列新建班级代号101、102……310，然后把活动单元格放在O3后，再选中L3：L667单元格区域，选取“ 工具栏”上求和按钮“ ∑”，按着键盘上的Ctrl键，分别选取每周的101班级，这样就求出了101班级的量化总分。再按同样的方法求出102、103……310班级的量化总分后排序，就得出了班级量化总分排名，然而这样求和得出的结果却有差错。学生科长这样求和的方法肯定是正确的，错误可能出现在按着Ctrl键，分别选取每周的班级时由于看错班级代号造成了部分班级的量化总分有错误。的确，由于班级多、周次多，选取每周的班级求和时造成的数据错误也在所难免，有没有更好的方法来求出量化总分而避免出错的机会呢？我分析了上表后，采用“ 数组”求和的方法，很快就正确地求出了各班量化总分并排出了各班量化总分的名次。请见图2。

图2

三、具体操作规程

在图2的O3单元格中输入数组公式：=sum(if(n3=a3:a667,L3:L667)) ，然后按Ctrl+Shift+Enter组和键，就会在O3单元格求出本学期101班的量化总分数。再按同样的方法，在O4、O5……O32单元格中输入数组公式。所输入的数组公式只要把上述公式中的n3分别改n4、n5……n32即可。由于相邻单元格中所输入的数组公式具有规律性，所以可避免出错。分别求出各班的量化总分后，还要把O3:O32单元格区域中的数据“ 输入”到P3:P32单元格区域（请不要复制数据），然后以P3:P32单元格区域中的数据排序，算出各班本学期量化总分数的名次。

注意：利用P3:P32单元格区域中的数据排序后，你会发现O3:O32单元格区域中的数据会发生变化，这是因为排序后O3:O32单元格区域中的数组公式发生了变化，但这并不影响P3:P32单元格区域中数据的正确性。

这样，一个求和容易出错的问题，利用数组公式就轻易解决了，并且也减少了求和的

工作量。特别是利用数组求和，可以替代很多重复的公式，因而能节省内存，它是在小空间中进行大量计算的强有力的方法。

Excel中也可避免复制隐藏的内容

几天前，学校一位会计找到我，让我帮她解决一个Excel问题：每月打印员工工资条时，都要从员工信息工作表中（其中很多内容与工资无关）把工资部分单独摘录出来，另存到一个工作表中。因为员工很多，这样做起来有点繁琐。 （点击查看更多软件使用技巧）

她也曾试着把不需要的行、列隐藏起来进行，通过复制的方法来实现，但当粘贴到另一工作表时，隐藏的部分又自动显示出来。有没有办法避免把隐藏部分的内容进行复制呢？经过短暂摸索，问题很快得到解决，下面请看解决办法。

首先把不需要的行或列隐藏起来，并把显示的内容全部选中。然后单击菜单栏中的“编辑\\定位”命令，在弹出的对话框中点按〔定位条件〕按钮，进入到“定位条件”对话框（如图所示），单选“可见单元格”后确定。接下来指向所选内容进行“复制”，切换到另一工作表中，执行“粘贴”操作即可。

解决Excel数值进位问题

一天，学校会计找我，说最近要按工资比例给每位教师增发公积金。他请了两位计算机的老师帮他计算，可是都没能解决问题。全校有200多位老师，要是用手工计算的话，不但要花很多时间，而且容易误算。所以他想请我帮他计算一下。我想，这无非是在Excel中用公式进行加减乘除的问题，应该不会太难，因此我就爽快地接受了这个任务。

会计首先向我具体介绍了公积金增发方案。根据文件规定，本次公积金增发额是各位教师工资的前五项（职务工资、职务津贴、综合补贴、岗位津贴、教龄补贴）之和乘以4.9%，得到的增资额取整数。我一看，这还不简单？首先选定“增发”栏H列，将单元格格式设置为整数，然后定位到H2单元格，输入“SUM(B1:F1)*0.049”，按回车确认后，再将鼠标移到此单元格右下角，当出现实心加号时双击，所有增发额即刻就算好了。不料，会计看后却说，文件还有附加说明，凡增资额余数在0.1元以上的，应进位到元，不满0.1元的尾数则舍去。而采用设置单元格格式为整数的方法，只能将增发单元格中的数据作四舍五入处理，当余数是0.1～0.4时，就无法进位到元了。

真没碰到过这样古怪的算法！平时我们都用四舍五入法取整，今天这种异常的进位去余方法，也难怪那两位老师解决不了了。仔细分析计算要求，可分两步来计算。第一步是去除1位小数后的余数，第二步是见余进1，得到整数。可是用什么函数才能完成这两步操作呢？我立刻按F1打开帮助菜单，在索引标签的“关键词”框中试着输入“四舍五入”一词，按击[搜索]按钮后，立即找到了与“四舍五入”相关的函数。经仔细阅读，又找到了

Roundup

和

Rounddow

这两个函数。于是得到了如下公式：

“Roundup(Rounddow(G2*0.049,1),0) ”。即先将工资前五项总和“G2”乘以0.049，并用Rounddow()函数去掉1位小数后的余数，保留到1位小数，再用Roundup()函数以见余进1的方法取整。在H2单元格输入这一公式，确定后再双击单元格右下角的实心加号，所有增发额瞬间计算完毕。取样验证，正确无误。

会计见我一会儿就将200多位教师的公积金增发数额算好了，连说“高手，高手”。其实，这是什么“高手”啊？只不过是比别人更善于运用帮助罢了。要是大家平时碰到问题时，也能注意多运用联机帮助，不也能成为“高手”吗？

轻松删除Excel表格中的空行

在数据统计与分析过程中，由于记录的添加、删除以及数据表的合并等原因，经常会在表格中出现一些空行（如图1）。这些空行的存在既不美观，同时也影响了数据分析的结果。如果一行一行地删除，费时费力，笔者这里总结了以下三种方法，让你轻松删除Excel表格中的空行。

图1 Excel数据表格

一、定位删除法

利用Excel中提供的“定位”功能，一次性定位表格中的所有空行，然后将其“一网打尽”。

依次选择“编辑→定位”菜单命令，在出现的“定位”对话框中单击“定位条件”按钮，在接着弹出的“定位条件”对话框中点选“空值”单选项（如图2），最后单击“确定”按钮，这样就可以将表格中所有的空行全部选中了。然后鼠标右击选中区域，在弹出的快捷菜单中依次选择“删除→整行”命令即可。

图2 “定位条件”对话框

Cico提示：使用该方法删除时要确保其他非空行中的所有单元格内均填有数值，否则会出现误删除记录的现象。

使Excel不显示0值的三招

在Excel中当单元格计算结果为0时，默认结果会显示0。如果你希望单元格为0时显示为空白，那么你可以试试以下三种方法，其中第二和第三种方法还可以随意决定不显示负值、正值或0值。

1． 设置选项

选择菜单栏的“工具→选项”在“视图”选项卡中单击取消“0值”复选项前的“√”，确定后当前工作表中的值为0的单元格将全部显示成空白。不过很多时候我们还需要在一部分单元格中显示0值，那就不能用这个方法了。

2． 条件格式

选中不想显示0值的单元格区域，选择菜单栏的“格式→条件格式”，设置条件格式为“单元格数据”、“等于”、“0”（请见图1），单击[格式]按钮，在弹出“单元格格式”的窗口“字体”选项卡中单击颜色的下拉箭头选择“白色”（若选定单元格已设置了底色则应选择与底色相同的颜色），单击[确定]按钮完成设置。这样当单元格的值为0时其文字颜色会变成与底色相同，让我们看不到也就等于消失了。通过设置单元格数据小于（或大于）0时的格式，还可以不显示负值（或正值）。

图1

3． 自定义数字格式

选中不想显示0值的单元格区域，右击选择“设置单元格格式”，在“数字”选项卡的分类列表中选择“自定义”，在“类型”文本框中输入“G/通用格式;G/通用格式;;”（请见图2），单击[确定]按钮后选定单元格的0值将不显示（不含引号且中间的符号均为半角）。

图2

若类型中改输入“G/通用格式;;G/通用格式;”可使单元格不显示负值；输入“;G/通用格式;G/通用格式;”则不显示正值。

你用过\"自动计算\"吗

在使用Excel时，有时可能需要快速查找某些数据值，例如在统计学生考试成绩时，查找某个范围的最大值、最小值等。如果使用公式就显得比较烦琐，这时，可以使用Excel

提供的“ 自动计算”功能，例如附图找出C2:C10中的最大值，操作步骤如下：

1. 选取C2:C10单元格区域。

2. 在状态栏中的“ 自动计算”处单击鼠标右键，系统显示出一个简单的函数列表如上如附图所示，选择其中的“ 最大值”选项，则可自动找出最大值并显示在状态栏中。

轻点鼠标做报表

每到年终，加班加点地编制财务报表成了我们这些财会人员的家常便饭。如果我们利用永中Office 2004提供的“时间序列”功能，只要轻点几下鼠标，几分钟的时间即可完成一份财务报表（如图1）。

图1 财务报表

1． 定义行字段

（1）启动永中Office 2004，新建一电子表格，取名保存（如“财务报表.eio）。

（2）执行“数据→时间序列→定义字段”命令，打开“定义字段”对话框（如图2）。

图2 “定义字段”对话框

（3）在“字段名”后面的方框中输入第一个行字段（如“公司收入”），然后单击“添加”按钮。

（4）重复上述操作，仿照图1的样式，完成其他行字段的设置。

2． 定制计算公式

（1）选中“公司收入”行字段，再次打开“定义字段”对话框。

（2）将光标定在“字段公式”后面的方框中，然后按右边的按钮，弹出“公式生成器”对话框。

（3）展开其中的“字段”节点，选中下面的“所有”选项，在右侧区域中显示出所有行字段。

（4）双击“主营业务”字段，让其出现在“公式生成器”上方的方框中，然后按一下中间“+”号按钮；双击“兼营收入”字段，按一下“+”号按钮；再双击“其他收入”字段，按一下“+”号按钮。

（5）公式编辑完成后，单击“确定”按钮返回“定义字段”对话框，再单击“修改”按钮。

（6）仿照上述4、5步的操作，设置好“公司支出”的计算公式。

在EXCEL中使用函数计算后的结果往往无法独立移动到其他工作薄中，比如编制报表后，无法将原应用单元格删除。例如：C=A+B计算出C后如何能使C值不再变动，而可以删除A、B单元格，谢谢各位高手指教一...

可选择复制,并通过选择粘贴中选\"数值\"而非\"全部\数据复制后可将A.B删除，或通

过这种方式将以Ｃ单元数值移动到其它工作表．

Excel中三表“嵌套”成一表

问题的提出：期末考试完后，学校领导要我出一份简报，以反映全校的教学情况（简报的式样见表一）。我已经在Excel中存储有：全校各班各科任课教师名单（见表二）、全校各班各科平均成绩（见表三）、全校各班各科及格率（见表四）等基本数据，可以说只要把这后三张表的数据综合到一起也就完成了简报的制作。全校有50多个班，考试科目又多，把上述数据再输一遍，工作量之大是可想而知的。好在这三种表格的式样基本相同，于是我先采用逐级逐科“复制→粘贴”的方法来工作。但是这要不断地选、不断地复制、不断地在窗口间切换，费时费力且易出错。“如果后三种表格能向Flash中的透明图层一样相互嵌套就好了”，在这种理念的驱动下，我大胆探索，终于找到了解决Excel表格“嵌套”的方法。

解决的方法：怎样才能实现Excel中表格的“嵌套”呢？方法其实很简单，下面我们一起来看看吧！

1. Excel中新建一名为“简报”的文件，并按式样绘制表一。

表一

2. 打开表二，在各科目的后面插入两个空列（这主要是为了与表一的式样相同）。

表二

3. 选定各学科的任课教师名单，执行“复制”命令。

4. 将窗口切换到表一，选择相应的目标单元格，执行“编辑→选择性粘贴”命令。

5. 在“选择性粘贴”对话框的最下面选中“跳过空单元”选项（这一步可是表格“嵌套”的关键），单击“确定”。这样我们就完成了表二“嵌套”到表一的工作。

6. 分别打开表三、表四，重复执行2—5步骤，将表三、表四也“嵌套”到表一中。简报的制作就这样轻松完成了。

表三

表四

当我在短时间内将简报清样送到领导手中时，他们的吃惊程度是可想而知的。其实我想说：“这没什么，精彩的还在以后呢！呵呵

Excel中排名用函数会更简单。”

当班主任，总免不了要给学生排个名次什么的。比如如图1所示成绩表，如果需要保持原表的姓名顺序不变并给所有学生排定名次的话，那么以往的一个可行做法是：先利用工具栏中的［自动求和］按钮计算出每个学生的总分，然后点击菜单命令“数据→排序”，打开“排序”对话框，“主要关键字”选择“总分”，并选中右侧的“降序”单选项。点击［确定］按钮，就可以实现将所有人的成绩按总分以降序排列了。

之后要做的就是在名次列中手工依次输入每名学生的成绩。如果数据量比较大时，可以先输入1、2、3后，选中这三个单元格，然后把鼠标移到单元格右下角的小方块上，待鼠标变成十字形后向下拖动，自动产生数字。

最后，点击菜单命令“数据→排序”，打开“排序”对话框。这次以“编号”为主要关键字，并选中右侧的“升序”单选项。此时才出现我们想要的结果。

不过，这个办法还是有些麻烦，主要表现在两个方面：

1. 完成全部的排序工作要分别以“总分”和“编号”为关键字进行两次排序。如果不幸在数据表中忘记设置“编号”列，那么第一次排序之后，想再回到原来的序列顺序麻烦就大了（呵呵，您也甭笑，当初我就多次遇到过这种事，可谓前车之鉴）。

2. 最麻烦的是排名次。以总分为关键字排好序后，需要人工填入名次，这当然会很麻烦。虽然，您可以使用填充柄自动输入数列的功能完成快速输入，但这也会带来另外一个麻烦，那就是如果总分相同，按通常的情况，他们的名次应该是相同的，不过，自动填充功能是不会做到这一点的。所以还需要手工修改有关的名次。如果有成百上千的数据，岂不会弄得头都大了？

现在，我们来看看如何更快、更好地排出这个无规律的数列。

1. 在“总分”列的F2单元格输入“=SUM(C2:E2)”，然后按下回车键，第一个学生的总分就会计算出来了。

图1 原始成绩表

2. 在“名次”列的“G2”单元格输入“=RANK(F2,$F$2:$F$7)”，然后按下回车键，第一位学生的名次也就出来了。怎么，名次不对？不要紧，往后看。对了，还要说一句，那就是上面公式中那个“$F$7”是“总分”栏中的最后一个数据的位置，实际中可以根据表格中的实际情况来填写。

3. 选中F2和G2单元格，然后拖动填充柄向下至最后一个单元格，看到了吗？所有人的总分和名次都在瞬间完成了（如图2）。而且更妙的是，按照这种方法，如果总分相同，名次也是相同的。数据越多，这种方法就越能体现出它的优势。试试看吧！

图2 排好序的表

最后要提醒的是，在用于排名次的“=RANK(F2,$F$2:$F$7)”公式中，可千万不要忽略了符号“$”呀！名次到底能不能排得准确无误，它可是起到很重要的作用呢。

用Excel制作工资报表的注意点

某单位会计在用Excel制作工资报表的过程中碰到两个难题，以致每次制作工资报表都要花较长时间加以调整。该会计找到笔者，希望能提供帮助。笔者经分析，发现用Excel

制作工资报表须注意两点。

一、 问题的提出

1. 所得税计算问题。按照个人所得税有关规定，课税工资小于等于1000元时，不纳税；工资大于1000元且小于等于1500元时，税率为5%；工资大于1500元时，税率为10%。该会计应用逻辑函数IF( )对所得税额作两段处理，在“所得税”P3中输入公式“=IF(O3>1500, (O3-1500)*0.1+500*0.05, (O3-1000)*0.05)”，以致课税工资小于等于1000元时无法得出税额为零。其处理情况如图1（原始报表很大，不便观察，该图系对原始报表的缩简）。图中“应税工资”O8、O12分别为908.66、838.26，故“所得税”P8、P12中的值应为0，而现在却分别为4.57和8.09。为了使课税工资小于等于1000元时税额为零，只得重新核查报表并在相关单元格输入零。

图1 工资报表

2. 工资表平衡问题。该会计每次制作工资表总是难以平衡，误差少则几分钱，多则几角。图1中Q10中的公式为“=O10-P10”，应为1316.82，可表格计算却为1316.83，原因何在？百思不得其解。为了平衡工资表，又得核查修改。

上述两个问题的出现，耗费了会计大量的时间，以至于制作一张工资报表需好几天，有人建议她改用其它软件，可她又不想因改学其它软件而支付学习成本。基于这种考虑，她迫切需要解决以上难题，

二、 问题的解决

1. 所得税的计算。用逻辑函数IF( )计算所得税的思路是正确的，但利用单层IF( ) 函数，无法处理两种以上的状态，单层函数只能按给定表达式的值或真或假，返回两种状态中的一种，以致课税工资小于等于1000元时无法得出税额为零。而利用IF( ) 函数的嵌套，则可实现对多种状态的处理。所谓函数嵌套，指函数的参数包含子级函数，Excel函数嵌套最多可含7层。鉴于此，可用嵌套函数改写 “所得税”P3中的公式，公式为：“=IF(O3>1500,（O3-1500）*0.1+500*0.05 , IF(O3>1000, (O3-1000)*0.05,0 ))，并将该公式复制到“P4：P12”。这样处理，课税工资小于等于1000时的税额即可为零，如图2中P8、P12的值为0。

2. 工资表的平衡。工资表不平衡源于对所得税小数位数的取舍。按实际意义，所得税应为两位小数，而按税率公式计算的所得税却为三位小数。图1所得税为两位小数只是一种形式，是通过格式化单元格而得到的，实际上它是三位小数。其中，P10形式为16.68，实为16.675，Q10中的值应为：“1333.50-16.675=1316.825”，由于取两位小数，于是出现了：“1333.50-16.68=1316.83”的误差。这种误差只出现于所得税小数第三位为5 的情况，至于其它情况则不会出现。一个单位职工人数多达数百人，所得税小数第三位为5的对象肯定不止一个，这样的对象越多，则误差越大。如何解决这一问题？利用舍入函数ROUND（），将所得税由形式上的两位小数变为实际的两位小数，即可解决这一问题，即将

“

所

得

税

”

P3

中

的

公

式

改

为

：

“=IF(O3>1500,ROUND((O3-1500)*0.1+500*0.05,2),IF(O3>1000,ROUND((O3-100

0)*0.05,2),0))，并将该公式复制到“P4：P12”。由此，工资表的平衡问题得到圆满解决。

图2

工资表的平衡问题,另一种解决方案,自己认为简单:1、工具菜单->选项->重新计算->工作薄选项->以显示值为准(选取)；2、计算的单元格格式设置为：数值->小数位数->2

以显示值为准(选取)；非常实用，谢谢。另外如何让所有数据自动保留两位，而且0不要，

如35.00-->35, 48.10-->48.1,这样做工资表的时候方便打印排...

用Excel实现定时提醒

如果您从事设备管理工作，有近千台机械设备需要定期进行精度检测，那么，就得每天翻阅“设备鉴定台账”来寻找“到期”的设备——实在是太麻烦了！用Excel建立一本“设备鉴定台账”是不是方便得多？方法是：用Excel的IF函数嵌套TODAY函数来实现设备“到期”自动提醒。

首先，运行Excel，将“工作簿”的名称命名为“设备鉴定台账”，输入各设备的详细信息、上次鉴定日期及到期日期（日期的输入格式应为“年-月-日”，如：2003-10-21，

如图1）。

图1

然后，选中图1所示“提示栏”下的F2单元格，点击插入菜单下的函数命令，在“插入函数”对话框中选择“逻辑”函数类中的IF函数，点击[确定]按钮，就会弹出“函数参数”对话框，分别在Logical_test行中输入E2=TODAY()、value_if_true行中输入“到期”、Value_if_false行中输入“\" \"”(如图2)，并点击[确定]按钮。这里需要说明的是：输入的 \"\" 是英文输入状态下的双引号，是Excel定义显示值为字符串时的标识符号，即IF函数在执行完真假判断后显示此双引号中的内容。为了醒目，可在“单元格属性”中将F2单元格的字体颜色设置为红色。

图2

最后，拖动“填充柄”，填充F列以下单元格即可。

我们知道Excel的IF函数是一个“条件函数”，它的语法是

“IF(logical_test,value_if_true,value_if_false)”，具体地说就是：如果第一个参数logical_test返回的结果为真，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果；Excel的TODAY函数［语法是TODAY()］是返回当前系统日

期的函数。

实际上，本文所应用的IF函数语句为IF(E2=TODAY(),\"到期\，解释为：如果E2单元格中的日期正好是TODAY函数返回的日期，则在F2单元格中显示“到期”，否则就不显

示，TODAY函数返回的日期则正好是系统当天的日期。

Excel的到期提醒功能就是这样实现的。

还不如改为E2=TODAY()-n其中n取整数，表示你希望在几天前提醒，例如你希望前三天

提醒就是E2=TODAY()-3

Excel妙用-公式结果我都要

Excel 最重要的应用就是利用公式进行计算。无论输入是纯粹的数字运算，还是引用其他单元格计算，只要在一个单元格中输入公式，就能得到结果。这个直接显示结果的设计对于绝大多数场合来说都是适用的，但某些情况下就不那么让人满意了。比如说在做工程施工的预结算编写，使用Excel，既要写出工程量的计算式，也要看到它的结果，于是这样相

同的公式在Excel里面要填两次，一次在文本格式的单元格中输入公式，一次是在数据格

式的单元格中输入公式让Excel计算结果。

如何既能看到公式又能看到结果呢？这个问题笔者认为可以从两个方面考虑：一种方法是所谓“已知结果，显示公式”，先在数据格式单元格中输入公式让Excel计算结果，然后在相邻的单元格中看到公式；另一种方法所谓“已知公式，显示结果”，就是先在一个文本格

式的单元格中输入公式，在相邻的单元格中看到结果。

已知结果，显示公式

假设C列为通过公式计算得到的结果（假设C1为“=A1+B1”，或者直接是数字运算“=2+3”），而相邻的D列是你需要显示公式的地方（即D1应该显示为“=A1+B1”或

者“=2+3”）。

1. 打开“工具”菜单选择“选项”命令，出现“选项”对话框。

2. 在“常规”选项卡中，选中“R1C1引用方式”选项。

3. 定义名称，将“引用位置”由“=GET.CELL(6,Sheet1!RC[-1])”即可。这里的RC[-1]含义是如果在当前单元格的同行前一列单元格中有公式结果，则在当前单元格中得到公式内容，即在含公式结果单元格的同行后一列单元格显示公式内容；如果将RC[-1]改为

RC[1]，则在公式结果的同行前一列单元格显示公式内容。

4. 如果“引用位置”中含有“RC[-1]”，则在含公式结果单元格的同行后一列单元格中输入“=FormulaofResult”即可得到公式；如果“引用位置”中含有“RC[1]”，则在含公式

结果单元格的同行前一列单元格中输入“=FormulaofResult”即可得到公式。

提示：如果想要在含公式结果单元格的同行后数第2列中显示公式内容，则需要把“引用

位置”中的“RC

－1

”改为“RC

－2

”。

已知公式，显示结果

假设C列为输入的没有等号公式（假设C1为“A1+B1”），而相邻的D列是你需要存放公

式计算结果的地方（即D1显示A1和B1单元格相加的结果）。

1. 选中D1，然后打开“插入”菜单选择“名称”命令中的“定义”子命令，出现“定义

名称”对话框。

2. 在“在当前工作表中的名称”输入栏中输入定义的名称“ResultofFomula”，在下方的

“引用位置”编辑栏中输入“=EVALUATE(Sheet1!C1)”，单击[确认]按钮退出。

3. 在 D1中输入“=ResultofFomula”，然后选中按住右下角的填充柄向下拉动填充即可。

提示：EVALUATE是Eexcel 4．0版的宏表函数，Excel 2000和Excel 2002中还支

持，但只可用于名称定义中。

4. 填充后要按[F9]进行重算，如果C列的公式有改动，也需要及时按[F9]进行重算。

巧施妙计，就能让公式和结果在Excel中和平共处了，你也试试吧。

Excel的数据筛选功能

Excel中提供了两种数据的筛选操作，即“自动筛选”和“高级筛选”。如何区分这两种筛选模式，以便熟练掌握和应用，让我们来看看吧：

自动筛选

“自动筛选”一般用于简单的条件筛选，筛选时将不满足条件的数据暂时隐藏起

来，只显示符合条件的数据。图1为某单位的职工工资表，打开“数据”菜单中“筛选”子菜单中的“自动筛选” 命令，以“基本工资”字段为例，单击其右侧向下的列表按钮，可根据要求筛选出基本工资为某一指定数额或筛选出基本工资最高（低）的前10个（该数值可调整）记录。还可以根据条件筛选出基本工资在某一范围内符合条件的记录，“与”、“或”来约束区分条件。如图2，根据给定条件筛选出基本工资大于等于300且小于350的记录。另外，使用“自动筛选”还可同时对多个字段进行筛选操作，此时各字段间限制的条件只能是“与”的关系。如筛选出“基本工资”和“职务工资”都超过380的记录。

高级筛选

“高级筛选”一般用于条件较复杂的筛选操作，其筛选的结果可显示在原数据表

格中，不符合条件的记录被隐藏起来；也可以在新的位置显示筛选结果，不符合的条件的记录同时保留在数据表中而不会被隐藏起来，这样就更加便于进行数据的比对了。

例如我们要筛选出“基本工资”或“职务工资”超过380且“实发”工资超过700

的符合条件的记录，用“自动筛选”就无能为力了，而“高级筛选”可方便地实现这一操作。如图3所示，将“基本工资”、“职务工资”和“实发”三字段的字段名称复制到数据表格的右侧（表格中其他空白位置也可以），在图中所示位置输入条件，条件放在同一行表示“与”的关系，条件不在同一行表示“或”的关系。图4即为上述操作在新的位置（B20起始位置）筛选的结果。

两种筛选操作的比较

由此我们不难发现，“自动筛选”一般用于条件简单的筛选操作，符合条件的记录

显示在原来的数据表格中，操作起来比较简单，初学者对“自动筛选”也比较熟悉。若要筛选的多个条件间是“或”的关系，或需要将筛选的结果在新的位置显示出来那只有用“高级筛选”来实现了。 一般情况下，“自动筛选”能完成的操作用“高级筛选”完全可以实现，但有的操作则不宜用“高级筛选”，这样反而会使问题更加复杂化了，如筛选最大或最小的前几项记录。

在实际操作中解决数据筛选这类问题时，只要我们把握了问题的关键，选用简便、正

确的操作方法，问题就能迎刃而解了。

利用Excel VBA查找同名学生

每个学期都会出现同名同姓学生报到而发生一些张冠李戴的事情，给学校的一些管理带来了一定的麻烦。本人曾尝试使用Excel的既有功能解决这个问题，但都没有收到很好的效果，于是考虑使用VBA。

2． 解决问题的目的

假设图1是学生的名单，然后从学生名单中提取出同名同姓学生的姓名及班别，复制到Sheet2中（图2），学校可以通过Sheet2的信息，辨别出共有多少同名同姓的学生及其所在的班级 。

3． 解决思路

使用CountIf()函数对学生姓名进行判断，如果CountIf()函数的值大于1，则表示该姓名有重复，然后将其复制到表Sheet2中。

4． 解决问题的办法

点击“工具→宏→Visual Basic编辑器”，进入VBA开发环境，点击“插入→模块”，这时

出现代码窗口，在其中输入下列代码：

Sub tmtx()

Sheets(\"sheet2\").[a1] = \"班别\"

Sheets(\"sheet2\").[b1] = \"姓名\"

a=sheets(\"sheet1\").[a1].CurrentRegion.Rows.Count ‘判断学生名单的行数。

For i = 2 To a

k = Sheets(\"sheet2\").[a1].CurrentRegion.Rows.Count + 1

If Application.WorksheetFunction.CountIf([c1:c65530], Cells(i, 3)) > 1 Then

Sheets(\"sheet2\").Cells(k, 2) = Sheets(\"sheet1\").Cells(I, 3)

Sheets(\"sheet2\").Cells(k, 1) = Sheets(\"sheet1\").Cells(I, 2)

End If

‘使用CountIf()函数对每个学生名字进行判断，如果CountIf()值大于1，则将其复制到

Sheet2。

Next

Sheets(\"sheet2\").[a1].CurrentRegion.Sort Key1:=Sheets(\"sheet2\").[b1],

Order1:=xlAscending, Header:=xlGuess

‘对复制到Sheet2的内容以“姓名”作为关键字排序，从而实现同名同姓学生的姓名排

在一起，以方便查阅。

If Sheets(\"sheet2\").[a2] = \"\" Then

MsgBox“无同名同姓学生！”，“报告”

End If

‘如果没有同名同姓的学生，则跳出报告信息窗口。

Sheets(\"sheet2\").Select

End sub

为了便于以后每个学期都能使用该程序，我们可这样做：点击“文件/导出”，将其保存为.bas

文件，要使用时，点击“文件/导入”就可以了。

避开Excel的误差

微软的Excel软件在财务应用领域使用得非常广泛、被许多单位用来处理财务数据。笔者就是用Excel来为单位做工资表的，不过笔者在使用过程中发现，当用Excel进行复杂算术运算时，其计算出来的结果有时会与电子计算器的结果有些微小的差别，特别是在进行多项除法运算而留有余数的情况下，算术“误差”很容易产生，究其原因，就是Excel在除不尽的情况下，不会自动对数值进行四舍五入的操作。这在要求严格的财务报表上当然

是不允许的，避开这种“误差”的途径有两种：

1． 利用Round函数对中间值四舍五入

Excel中Round函数的格式为：Round(数值，保留的小数位数)，它的作用就是对指定数值进行四舍五入的操作并保留指定小数位数。比如Round(8.8888,2)的意思就是对8.8888的千分位进行四舍五入、结果就是8.89，当然为工作方便，此函数括号内的数值也可以是单元格地址或别的算术公式，每个中间值经过这样处理后得到的最终运算结果就和计算器

算出来的一致了。

2． 正确设置Excel选项来避开“误差”

上述函数方法虽然方便，不过遇上大型Excel表格时，如果每个单元格都要设置这个函数，工作量未免太巨大，这时我们就可以进入Excel的[工具]→[菜单]→[选项]→[重新计算]，在“工作簿选项”处勾选“以显示值为准”，上述Excel“误差”也可有效避免，而默认情况

下这个小选项是没有被勾选的。

在Excel中统计与上报两不误

因为在Excel中就能实现单元格中数据的动态更新，所以我们只需在Excel文档中建立两个工作表，分别取名为“出勤统计表”和“汇总表”。把要进行数据输入与统计的“学生出勤情况统计表”建在“出勤统计表”工作表中（如图1），而把作为上报材料的“学生出勤情况汇总表”建在“汇总表”工作表中（如图2）。然后把“学生出勤情况汇总表”与“学生出勤情况统计表”中的有关表格进行链接，就可实现数据的动态更新了。

图1

图2

如果上报材料除了此汇总表以外还有许多其他文字，只需在“汇总表”工作表中插入OLE Word对象即可，这样我们在“出勤统计表”工作表中输入数据进行统计后，马上就可在

“汇总表”中打印上报材料，而无需运动Word了。

■ 数据统计的动态更新

其实，实现单元格中数据的动态更新就是把要实现数据动态更新的单元格与源数据单元格

进行链接，链接方法以“汇总表”中“汉语言”专业的出勤率为例：

1. 打开“出勤统计表”工作表，激活“汉语言”专业的出勤率单元格H5，进行复制。

2. 打开“汇总表”工作表，激活“汉语言”专业的出勤率单元格B3，单击[粘贴]按钮，这时粘贴入单元格里的数据会无效。我们只要单击单元格旁边的“粘贴”图标，选择“链接单元格”（如图3）。这时函数编辑框中显示为“=出勤统计表!$H$5”，说明此单元格已与

“出勤统计表”工作表中的H5单元格进行了链接。

图3

■ 数据的上报

如果需要上报的“汇总表”中还需输入许多其他文字，如何在“汇总表”工作表中插入OLE Word对象呢？这里的OLE Word对象实际就是嵌入在Excel工作表中的Word文档，它可弥补Excel对文字处理能力的不足。方法很简单，首先单击菜单中“插入/对象”命令。然后在弹出的“对象”对话框中的“新建”选项卡里的“对象类型”栏中选择“Micrsoft Word 文档”，单击[确定]按钮（如图4）。最后只需在插入的OLE Word对象中对文字进行输入

并编辑，方法与在Word中一样。

图4

巧用IF函数计算结构工资

我校最近做了一些工资方面的改革，根据课程的性质不同、课时多少等设置了几种不同的层次：文化课与音体美专业课课时费不同，超过标准课时，课时费翻番等。结构工资制的实行，确实大大调动了教师们的工作积极性，但我校有200余位专任教师，统计、计算教师的结构工资便成了一项既费力又劳神的工作。后来我们用Excel摸索出来了计算和管理教师结构工资的方法，一起来看看吧，也许会有所收获。

1． 建立数据表

启动Excel，新建一文档，其结构如图所示。“类别”一项是为区分文化课和专业课而设的：文化课为1，专业课为2。根据当月教学统计的实际情况，再分别将“周课时数”、“周自习数”、“周活动课数”和“其他”（当月其他加或扣工资情况）填写清楚。

2． 运用IF函数计算“周课时金额”

首先对IF函数做一简单介绍：IF函数执行真假值判断，根据逻辑测试的真假值返回不同的结果，可以使用函数IF对数值和公式进行条件检测。其语法为：IF(logical_test,value_if_true,value_if_false)，“Logical_test”表示计算结果为“true”或“false”的任意值或表达式。例如“A10=100”就是一个逻辑表达式，如果单元格A10中的值等于100，表达式即为“true”，否则为“false”，本参数可使用任何比较运算符。“Value_if_true”“logical_test”：为“true”时返回的值，可以是其他公式。“Value_if_false”：logical_test为“false”时返回的值，当然也可以是其他公式。

小提示：函数IF可以嵌套7层，用“value＿if＿false”及“value＿if＿true”参数可以

构造复杂的检测条件。

在计算“周课时金额”时，用一个IF函数区分出文化课和专业课情况（即B=1，执行4.5元/节，其余执行4.2元/节。若还有第三种、第四种课型，可在其中继续嵌套IF函数）。然后在这两种情况中再各用一个IF函数分别区分出超课时与不超课时两种情况（即C>15部

分，每超出一节分别执行9元/节和8.4元/节），即形成嵌套IF函数：IF(B=1,IF(C>15,4.5*15+9*(C-15),4.5*C),IF(C>15,4.2*15+8.4*(C-15),4.2*C))。

3． 合计

“周课时金额”计算出来，“合计”就很简单了：“D*本月周数+(E*3+G*2)*本月周数”。“合

计”加“其他”也就是“实发总额”了。

4． 完成整个表格

算完一个人的，分别选中其“周课时金额”、“合计”和“实发总额”所在单元格，将光标置于其右下角，当光标变为“十”字形时，一拖到底，200多人的结构工资就计算完成了。

把此表保存为模板，以后再算结构工资就会更轻松。

在Excel中快速输入特定文本的技巧

因工作的需要，我经常用Excel 2000来处理大量的表格，常常要输入一些特定的文本（如标准规范的名称、施工企业的名称等），为了准确、统一、快速地输入这些特定文本，我摸

索出了几个好方法。

1、自动更正法

选择“工具→自动更正”命令，打开“自动更正”对话框，在“替换”下面填入“G300”，在“替换为”下面填入“《建筑安装工程质量检验评定统一标准》[GBJ300-88]”（不含引号），然后按“确定”按钮。以后只要在单元格中输入“G300”及后续文本（或按“Enter”键）后，系统会自动将其更正为“《建筑安装工程质量检验评定统一标准》[GBJ300-88]”。

小提示：

①你可以依照上述方法将有关文本一条一条定义好，方便以后使用；

②在Excel中定义好的自动更正词条在Office系列其他应用程序（如Word、PowerPoint）

中同样可以使用；

③如果确实需要输入文本“G300”时，你可以先输入“G3000”及后面的文本，然后删除

一个“0”即可。

2、查找替换法

有时候我们要在多个单元格中输入相同的文本，特别是要多次输入一些特殊符号（如※），

非常麻烦，这时我们可以用“替换”的方法来进行：

先在需要输入这些符号的单元格中输入一个代替的字母（如X，但要注意的是，该字母不能与单元格中需要的字母相同，也可以选择其他的特殊字符），等表格制作完成后，用“编辑→替换”命令（或直接按Ctrl+H快捷键），打开“替换”对话框，在“查找内容”下面的方框中输入代替的字母“X”，在“替换值”下面的方框中输入“※”，将“单元格匹配”

前面的“∨”号去掉（否则会无法替换），然后按“替换全部”按钮即可。

小提示：

在按住Ctrl键的同时，用鼠标左键选定多个不连续的单元格（或区域），输入文本（如※），

然后按下“Ctrl+Enter”键，也可将该文本同时输入上述选定的单元格中。

3、函数合并法

我经常需要输入一些施工企业的名称（如“某某市第九建筑安装工程公司”、“某某市华夏建筑安装工程公司”等），这些文本大同小异，我通常用 Excel中的CONCATENATE函数

来实现这些特定文本的输入：

假定上述文本需要输入某一工作薄的Sheet1工作表的D列中（如D2单元格），我们先在Sheet2工作表中的两个单元格（如A1和B1）中分别输入文本“某某市”和“建筑安装

工程公司”，然后在Sheet1的D2单元格中输入公式：

=CONCATENATE(Sheet2!$A$1,C2,Sheet2!$B$1)，以后我们只要在C2单元格中输入“第

九”，则D2单元格中将自动填入文本“某某市第九建筑安装工程公司”。

4、剪贴板法

由于Office 2000中的多重剪贴板一次可以保存12条内容，可以利用这一功能反复输入

一些特定的文本。

①用“视图→工具栏→剪贴板”命令，打开“剪贴板”工具栏。

②将特定的短语（如“《中华人民共和国建筑法》”、“《建设工程质量管理条例》”、“建设工程质量监督工作”等）分别输入到不同单元格中，然后一个一个地选中，用“复制”命令

将它们逐条加到“剪贴板”中。

③以后某一单元格中需要上述短语时，单击“剪贴板”工具栏上相应短语的图标就可以一

次性输入该短语。

小提示：

①如果短语较多，记不清某条短语在“剪贴板”上的具体位置也没关系，当你将鼠标指向

某条短语时，在左下方即可完整地显示出该短语，点击你需要的短语即可；

②Office XP中的多重剪贴板中最多可以容纳24条剪贴信息，如果大家需要容纳更多的剪

贴信息，可以使用第三方的剪贴板工具，如ClipMate等。

5、选择列表法

如果需要输入的文本在同一列中前面已经输入过，且该列中没有空单元格，我们就可以右击下面的单元格，选“选择列表”选项，则上面输入过的文本以下拉菜单形式出现，用左

键选中你需要的文本，即可将其快速输入到选定的单元格中。

6、格式定义法

有时我们需要给输入的数值加上单位（如“万元”等），少量的我们可以直接输入，而大量的如果一个一个地输入就显得太慢了。我们通过“自定义”单元格格式的方法来达到自动给数值添加单位的目的：我们选中需要添加单位的单元格，选择“格式→单元格”命令，打开“单元格格式”对话框，在“数字”卡片中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入“#平方米”，按下“确定”按钮，以后在上述单元格中输入数值（注意使用此法仅限于数值！）后，单元（本例为“平方米”）会自动加在数值的后

面。

说了这么多，我们都是以Excel 2000为例进行介绍的，其实，经过我们的试用发现，在

Excel XP中使用起来方法几乎完全一样，大家可以放心使用。