Excel条件格式自动标识特定的记录

【 tulaoshi.com - excel 】

Excel条件格式自动标识特定的记录

 　　Excel的条件格式功能是一项十分强大且便利的功能。下面介绍自已在使用Excel过程中，利用条件格式功能的其中一个应用场景。

　　在一个Excel工作表中，有许多条记录，如何使Excel自动标识出满足特定条件的记录呢?

　　例如：下图所示的工作表中有多条记录，要求当字段是否完成列中为否时，突出标识该行(本例中将该行突出显示为红色)，使用户清楚没有完成的工作;当为是时，突出标识的背景色自动消失。

　　现在开始实现这样的效果，先看看下图：

　　①选择工作表的A列至E列。

　　②单击菜单格式条件格式。

　　③在条件格式对话框中，选择条件1下拉框中的公式项。

　　④在公式框右侧的文本框中输入公式=$E1=是。

　　⑤单击下方的格式按钮，在单元格格式对话框的图案选项卡中，选择颜色，这里是红色。

　　⑥单击确定按钮，关闭条件格式对话框。此时的效果如下图：

　　此时，当将E列中单元格的数据改为是时，该行的红色底纹会自动消失;如果为否，则该行会自动加上红色底纹。

Excel统计信息重复出现次数

 　　考试在学校中是经常的事，监考人员通常也是不固定的，考试次数一多，监考工作量的统计就足够统计人员费心了。要知道，津贴是按照监考次数发放的，算错了，可对不起老师们的劳动哟!本人以前采用的是传统的笨办法，点着监考安排表逐一计数，头晕脑胀、眼花缭乱不说，稍不留神还容易出错。直到在Excel中翻翻捡捡，找到了两个函数，才算完全脱离苦海，一劳永逸。

　　第一步，因为要利用Excel进行统计，所以监考安排中的监考人员数据要复制到Excel中。其实，Excel制作表格的功能也是相当不错，不妨直接就用Excel进行监考安排的排制。此例将监考安排数据存放在Sheet1中。如图1。

　　第二步，插入一个新工作表，更名为统计。将全部的可能监考的教师名单输入一列，如A列。

　　第三步，在一人名右侧相邻的单元格内输入公式，例如，B3单元格内姓名为张三，则在C3内输入 =COUNTIF(Sheet1!$1:$65536,CONCATENATE("*",B3,"*"))(也可以利用公式面板输入)。这里面用了 COUNTIF和CONCATENATE两个函数，第一个是计算一个区域中满足给定条件的单元格数目;第二个是条件，即包含B3中姓名的字符串。回车后即出现计算后的结果。单击C3单元格可见编辑栏公式，如图2。

　　第四步，将公式复制到其他姓名对应的单元格，也可用填充柄拖动填充。可以看到，每个姓名后面的单元格中自动出现了其监考的次数。如图3。

　　这里有两个地方需注意：引用区域要使用绝对应用，这样在填充时才不会出现公式错误;其次，要使用CONCATENATE在查找的姓名前后都加上一个通配符*号，目的是为了使该姓名出现在一个单元格的哪个部位均能被统计到。

　　当然，还有一点也很重要，那就是考试安排和监考次数统计中的人名一定要一模一样，否则，可要前功尽弃了。

Excel数据透视表分类求学生成绩平均值

 　　期末统考结束后，教育局要求全市学生的成绩都汇总在如图1的同一个Excel工作表中，并要求求出各学校、各专业、各学科的平均分。

　　全市三个学校、每个学校都有六个专业、每个专业都有五门课、各专业人数不等，总计有1000余人，工作量巨大。但如果采用Excel数据透视表来完成的话，呵呵，就简单多了。

　　一、创建数据透视表

　　点击菜单命令数据→数据透视表和数据透视图，打开数据透视表和数据透视图向导对话框。

　　第一步，如图2所示，选择Microsoft Excel数据列表或数据库及下面的数据透视表单选项。

　　第二步，如图3所示，在选定区域输入全部数据所在的单元格区域，或者点击输入框右侧的压缩对话按钮，在工作表中用鼠标选定数据区域。

　　第三步，在对话框中选定新建工作表单选项，以便将创建的数据透视表放到一个新的工作表中，再点击完成按钮，如图4所示。

　　这样，就可以建立一个空www.Tulaoshi.com的数据透视表，并同时显示数据透视表工具栏和数据透视表字段列表对话框，如图5所示。

Excel技巧:输入新数据时图表自动更新

 　　我有一朋友，是做销售的。他利用Excel图表来记录每天的销售成绩。那天，他打来电话问了这样一个问题：用Excel来记录每天的销售非常的方便，能够直观地表示出每天的销售成绩，但是因为每天都有新的数据，所以我不得不每天手动更改图表来使其包含新的数据。有没有一种方法可以让我输入新的数据时，图表能自动更新。下面的方法将为他来解决这个问题。

　　在Excel 97及以后版本中，当选中一个图表数据系列时，工作表中与该数据系列对应的数据区域周围就会出现边框，这时可以通过简单地拖拽区域边框的角点来扩展数据区域。本文采用的方法是用公式来定义一个动态的数据范围以创建图表，从而实现在输入数据时图表能够自动更新，而不必手动更改数据区域的范围。

　　具体操作步骤如下：

　　1.输入数据并创建图表，如图1所示(下载练习用Excel工作簿)。

　　图1

　　2.选择菜单命令插入名称定义，打开定义名称对话框。在在当前工作薄中的名称下方输入框中输入日期，在引用位置下方输入框中输入公式=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1! $A:$A)-1)，单击添加按钮来添加日期，如图2所示。

　　图2

　　注意：OFFSET函数中引用的是第一个数据点(单元格A2)并且用COUNTA函数来取得A列数据点的个数。因为A列中包含一个非数值数据日期，所以在公式中减去1。

　　3.在定义名称对话框继续定义名称。在在当前工作薄中的名称下方输入框中输入销售，在引用位置下方输入框中输入公式 =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)，单击添加按钮，如图3所示。然后单击确定按钮关闭对话框。

　　图3

　　4.激活图表并选中数据系列，可以看到在编辑栏中的未更改公式是这样的：=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1)，下面这一步很关键，我们要做一个替代，现将公式更改如下：=SERIES(,Sheet1!日期,Sheet1!销售,1)，如图4所示。在更改后的公式中可以看到我们在第二步和第三步中定义的名称：日期和销售。

　　图4

　　做完以上工作，朋友的问题便得到了解决。当我们输入新数据时，图表会自动更新，赶紧试一下吧。如图5所示，加上了2月10日的销售，图表就自动更新了。最后需要注意的是，在使用OFFSET函数时，一定要将COUNTA函数指向第一个数值数据，如果指向了第一个非数值数据，那么自动更新就无从谈起了。

Excel使用中常见的七种公式错误及其解决方法

 　　我们在日常使用excel办公软件时,可能会遇到一些错误信息,例如：#value!、# n/a!、 #div/o!等等。这也使得一些只懂得应用该软件的朋友们,常常是一头雾水,不知道出了什么问题,或者该如何处理。其实,出现这些错误的原因有很多种,今天软件直销网就着重为大家介绍几种常见的错误信息以及其解决方法。

　　一：#value!

　　当我们在使用excel时，如果使用了错误的参数或运算对象类型时,或者当公式自动更正功能不能更正公式时,将产生错误值#value!。主要原因有以下三种:

　　1：在需要数字或逻辑值时输入了文本,excel不能将文本转换为正确的数据类型;

　　解决方法：确认公式或函数所需的运算符或参数正确,并且公式引用的单元格中包含有效的数值。例如：如果单元格a1包含一个数字,单元格a2 包含文本,则公式="a1+a2"将返回错误值#value!。可以用sum工作表函数将这两个值相加(sum函数忽略文本：)=sum(a1:a2);

　　2：将单元格引用、公式或函数作为数组常量输入;

　　解决方法：确认数组常量不是单元格引用、公式或函数。

　　3：赋予需要单一数值的运算符或函数一个数值区域;

　　解决方法：将数值区域改为单一数值。修改数值区域,使其包含公式所在的数据行或列。

　　二：#n/a

　　当在函数或公式中没有可用数值时,将产生错误值#n/a。

　　解决方法：如果工作表中某些单元格暂时没有数值,请在这些单元格中输入"#n/a",公式在引用这些单元格时,将不进行数值计算,而是返回#n/a。

　　三：#####!

　　如果单元格所含的数字、日期或时间比单元格宽,或者单元格的日期时间公式产生了一个负值,就会产生#####!。这个看起来比较简单,大家应该都了解吧。

　　解决方法：如果单元格所含的数字、日期或时间比单元格宽,可以通过拖动列表之间的宽度来修改列宽。如果使用的是1900年的日期系统,那么excel中的日期和时间必须为正值。如果公式正确,也可以将单元格的格式改为非日期和时间型来显示该值。

　　四：#div/o!

　　当公式被零除时,将会产生错误值#div/o!。在具体操作中主要表现为以下两种原因。

　　1：在公式中,除数使用了指向空单元格或包含零值单元格的单元格引用(在excel中如果运算对象是空白单元格,excel将此空值当作零值：。

　　解决方法：修改单元格引用,或者在用作除数的单元格中输入不为零的值。

　　2：输入的公式中包含明显的除数零,例如：公式=1/0。

　　解决方法：将零改为非零值。

Excel技巧:将图表另存为GIF文件

 　　我们有时需要在网页中插入一个tuLaoShi.com图表，或者在其它非Office程序中用到图表，有没有一种办法可以将Excel工作簿中的图表插入到其它程序中呢?其实可以通过把工作表存为一个HTML文件的方式来实现，在另存为网页时，Excel会自动将图表转换为GIF文件。如果你觉得这样做麻烦的话，也可以利用Office自带的VBA编辑器来编写一个简单的宏来完成这个工作。

　　具体操作步骤如下：

　　1.创建练习用的图表

　　启动Excel,默认的工作簿为Book1,首先我们在工作表Sheet1中输入如图1所示的数据。这儿要注意，数据的起伏不要太大，以免创建的图表有失协调。

　　图1

　　选中A1到E5单元格，然后执行菜单命令插入图表，打开图表向导对话框，在对话框中直接单击完成按钮，则会创建出如图2所示的图表。

　　图2

　　2.创建宏

　　按快捷键Alt+F11键，打开Visual Basic编辑器。在工程窗口中选中图表所在的工作簿，然后执行菜单命令插入模块，打开模块1窗口，在窗口中输入以下四行代码，如图3所示。

　　Sub SaveChartAsGIF ()

　　Fname = ThisWorkbook.Path & "" & ActiveChart.Name & ".gif"

　　ActiveChart.Export FileName:=Fname, FilterName:="GIF"

　　End Sub

　　图3

　　代码输入完成后，不要忘记存盘。存盘后关闭Visual Basic编辑器。

　　3.执行宏

　　回到Excel窗口，将工作簿保存到某一文件夹中，选中图表，然后按快捷键Alt+F8键，打开宏对话框。选中SaveChartAsGIF宏，并单击执行按钮，如图4所示。

　　图4

　　运行宏之后所生成的GIF文件会以图表的名称来命名，并被保存到该工作簿所在的文件夹，打开该文件夹，就可以看到生成的GIF文件，使用任一图象浏览软件即可浏览该文件，在其它程序当中可以很方便地插入这个GIF文件www.Tulaoshi.com。

　　注意：由于这个简单的宏没有错误检查机制，所以如果在执行宏之前没有选中图表或者工作簿未存盘，都会出现错误提示。

Excel技巧:处理折线图表的缺失数据

 　　当在Excel中创建了一个折线图表时，如果数据区域有些单元格数据缺失，也就是该单元格为空白，则折线图中就会出现缺口，这样就导致折线图不美观，或者不太能够直观地反映出数据的变化趋势。

　　如图1所示为存在缺失数据的一个折线图，可以看到折线上的缺口。

　　图1

　　Excel提供了两种方法来处理折线图中的缺失数据：

　　以零值代表：将空白单元格作为零处理，对于零值数据点，行跌落至零。

　　以内插值替换：用内插值数据点代替空白单元格，填充空距以连接行。

　　我们先来看一下这两种方法所实现的效果。如图2所示为以零值代表的折线图。如图3所示为以内插值替换的折线图。

　　图2

　　图3

　　是怎么实现的呢?请按照如下步骤进行操作。

　　1.在Excel中选中要处理缺失数据的折线图表。

　　2.选择菜单命令工具选项，打开选项对话框。

　　3.单击图表选项卡。

　　4.选择空单元格的绘制方式为中的以零值代表或以内插值替换，如图4所示。

　　图4

　　5.单击确定按钮，就可以看到图表的变化了。以上选择会作用到所选图表的所有数据系列。

　　还有一种办法是在空白单元格中输入公式=NA()。这样一来图表就会为包含该公式的单元格使用内插值，而不管在选项对话框中选择的是什么