EXCEL做下拉菜单

EXCEL怎么做2级下拉菜单啊？

比如：分2大类，有“水果”和“蔬菜”，水果里分“苹果”和“桔子”，蔬菜里分“青菜”和“黄瓜”。一栏里如果选了“水果”，那么另一栏只能选“苹果”或“桔子”。要是选了“蔬菜”，那么另一栏就只能选“青菜”或“黄瓜”。

答案

先做张基础表

全选A列，在名称框输入“大类”两字，将A列取名“大类”，

再将C列取名水果，D列取名蔬菜……

在A列输入水果、蔬菜……（注意，输入的类别一定要和后面列的名称相同，实质上输入的是后面各列的名称）

在C列输入苹果、橘子……

在D列输入青菜、黄瓜……

进正式表A2，数据/有效性，在来源中输入

=INDIRECT("大类")

在B2，数据/有效性，在来源中输入

=INDIRECT(A2)

再将A2和B2单元格分别下拉即可

再有不明白可发消息

其他回答

需要一个辅助表(假定为SHEET1),在A列输入“苹果”和“桔子”等所有水果名；在B列输入“青菜”和“黄瓜”等所有蔬菜名。

另一个是你要建立二级下拉菜单的表（假定为SHEET2），假定A1为一级菜单，内容为“水果”和“蔬菜”，先用数据->有效性设置好。B1单元格为对应的二级菜单。

在SHEET1工作表的C1单元格输入公式：

=IF(Sheet2!A$1="水果",A1,IF(Sheet2!A$1="蔬菜",B1,""))

将公式向下复制。

用鼠标选定C列，在“名称”栏（左上角显示C1的地方）输入“类别”（不含引号）。

单击SHEET2工作表B1单元格，点"数据"->"有效性"->"设置",在"允许"下面选择"序列",在"来源"框中输入:

=类别

点"确定"。

Excel 二级下拉菜单的3种方法

把excelhome的论坛关于下拉菜单的贴看了不少。发现实现2级的下拉菜单的方法好像也有好几种，我想我就做一个总结，自己也提高一下。

通过2级菜单，倒是可以比较深入熟悉 INDIRECT, INDEX, OFFSET, 这三个函数，是你要想学excel函数，估计你肯定过的关。

在论坛里看帖子，有一点比较累，很多都是直接用excel文件里做说明，你需要下载文件。我这里就全部用图来说明。

不过二级菜单的第一级的做法，都是相同的，大家可以参考上面的文章创建一个一级菜单。

第一种做法：使用函数INDIRECT

看看例子

很明显，我们希望在一级选择机房故障，那么2级就出现他下面的列表。

要实现这个目标并不复杂。

1：建立“名称

3个一级选项，我们需要建立3个“名称”：机房故障 线路故障 用户端故障。

这个时候，你就创建了3个名称

还需要建立一个名称，叫做“故障类型”这个名称包括“机房故障 线路故障 用户端故障”这3项。

这个时候，你就可以在“名称管理器”看到4个名称

2：创建1级菜单

1级菜单的办法就简单了

这样就解决了一级的下拉菜单。

3：2级菜单

这个地方你需要注意，=INDIRECT($F2)

如果你写成 =INDIRECT($F$2)，

如果是这样的话，你确定会出现一个报错

这个时候，你就实现了2级菜单

第二种做法：使用函数INDEX

还是上面的例子，用另外一种方式来实现。这个例子里，你只需要建立2个名称。

1：建立名称

建立：故障类型 名称，方法和上面一样。

建立：2级 名称

创建上面两个名称。

2：创建下拉菜单

第一下拉菜单和上面一样，我就不重复了，现在是演示第二级下拉菜单

=INDEX(二级,0,match(I$2,故障类型,0))

这样你就搞定2级菜单里。

第三种做法：使用函数OFFSET

学这个offset函数半天，才搞定。现在只是可以实现这个功能。不过还有需要改进的地方

还是上面的例子，

=OFFSET(A2,,MATCH(L2,故障类型,)-1,6,1)

这样也是可以实现2级的下拉菜单。

以上的方式其实是不太完善的，因为我们的2级菜单刚好都是6项，那么如果是不等的，那你就只能取最多的。

完善的做法，当然是动态的，根据2级菜单的数量来显示。需要解决的问题，就是如果动态计算出每项2级菜单的数量。

2级菜单数量=COUNTA(OFFSET(A:A,,MATCH(L2,故障类型,0)-1))-1

那么我们的公式就变成

=OFFSET(A2,,MATCH(L2,故障类型,)-1,COUNTA(OFFSET(A:A,,MATCH(L2,故障类型,0)-1))-1,1)

这样就更加完美。

Excel中日期与时间的快速处理

1、任意日期与时间的输入数字键与“/”或“-”配合可快速输入日期，而数字键与“：”配合可输入时间：如输入“3/25”，然后回车即可得到“3月25日”。又如输入“9:25”，回车即得到“09:25”。

2、当前日期与时间的快速输入选定要插入的单元格，按下“Ctrl”键与分号键“；”，然后回车即可插入当前日期。而要输入当前时间，同时按住“Ctrl”键、“Shift”键与 分号键，然后回车即可。

3、日期与时间格式的快速设置如果对日期或时间的格式不满意，可以右击该单元格，选定“设置单元格格式→数字→日期”或“时间”，然后在类型框中选择即可。

4.日期转换为中英文的星期几。工作表中有一列日期数据（如C3单元格的日期为“2006-12-25”）如何将它转换为中文或英文的星期几呢？如果要转换为中文的星期几可输入公式“=TEXT（WEEKDAY（C3），"aaaa")”来实现，如果要转换为英文的星期几可输入公式“=TEXT（WEEKDAY（C3），"dddd")”来实现.

5.用函数求工龄。Datedif函数是Excel函数表中未曾提及的函数，利用它可以方便地求出两个日期间的相差的年数，月数和天数，如果要计算工龄，这个函数是再方便不过了。如C3单元格中日期是“1995-9-1”利用公式“=datedif(c3,today(),"y"）”就可方便地计算出工龄了。

一、 Excel时间计算的基础

和日期一样，Excel处理时间的方式并不像我们看到的那样——时间是由小时、分钟和秒钟组成的，有特殊格式的数据。Excel 在内部把每 24 小时的时间周期作为一个从 0 至 1的小数存储(图 1)。

图 1

说明：把A列中的时间数据复制到C列，然后通过“单元格格式”对话框，将C列的格式设置为“常规”即可看到如图所示的效果。

从图1中可以看出，每 24 小时的周期都从午夜零点(0:00:00)开始，存储为0。正午（12:00:00）则存储为 0.5，因为从午夜零点至正午恰好是一个整天的一半。到下一个午夜零点时(0:00:00)，Excel把它存储为1。从上一个午夜零点开始至下一个午夜零点开始，正好经历了24小时，这就是我们所说的，Excel处理时间的一个24小时周期。

将时间作为小数存储使得 Excel 可以对时间进行运算。例如,要计算两个时间之间的间隔长度，执行结束时间对应的小数减去开始时间对应的小数的操作即可，具体应用下面会有实例介绍。

巧用[分列]更改日期

刚进办公室，小赵就急急忙忙地跑过来说：“快帮兄弟一把！”我走到他的电脑前，看到的是一张名为“职工简况”的Excel工作表。他说：“领导让我根据表上面的‘出生年月’，快速算出截止到2004年5月31日每个人的年龄，下午就得交差。”这有何难，我胸有成竹地坐到了电脑前，立马在“出生年月”的右面插入了一列，在E3单元格里输入公式“=DATEDIF（D3，"2004-5-31"，"y"）”，

一回车，E3单元格里竟是“#VALUE！”。仔细一看，原来图1表格中“出生年月”里的数据不符合要求，程序不能识别出这是日期，因此不能利用这些数据进行有关日期的操作。如何将它们改为程序能够识别的日期形式呢？一个一个地改，不是办法，好几百人呢！

如果“出生年月”中的数据最右面都没有小数点（如68.9.26），只需用“替换”将数据中的小数点“。”替换为“-”，程序就会识别出这些数据是日期。

数据最右面有一个小数点，即使用上面的方法替换，程序也不认为这些数据是日期，可恰恰数据里有好多是这样的。提取字符吧！需要的字符又多少不一；删除最右面一个小数点吧，一时还找不到这样的函数。“天无绝人之路”，几经摸索还真找到了一个好办法，虽然还是有些麻烦，但总比一个一个地改要好的多。思路是将数据分为年、月、日三部分（如59.10.5），每部分各占一列，然后再将这三部分用“-”，连接起来。显然我们应以小数点“。”作为分隔标志，即分隔符号。

1. 因为数据的最右面还有一个小数点，所以应在“出生年月”列的右面插入3个空白列(请见图1)。

2. 选择“出生年月”下面的所有数据，即D3:D8，单击“数据→分列→下一步”，在“分隔符号”中勾选“其他”，在其右面的空白处输入一个小数点“.”，去掉“分隔符号”中的其他所有选项。

3. 单击[下一步→完成]按钮。数据按要求分成了三列(请见图2)。

4. 选中G3单元格，输入公式“=（IF（F3>0，D3&"-"&E3&"-"&F3，D3&"-"&E3））+0”，回车后，G3单元格里是“21575”用填充柄向下拖动至G8.经过上述操作，字符串已转成数值，只不过还不是我们需要的形式。公式中“+0”，就是为了使文本型的数字转化为数值型的数字（请见图3）。

因为G列的数据是由左面单元格引用过来的，所以我们不能删除D、E、F列，要使G列的数据不受D、E、F的制约，可再做如下操作。

5. 选定G3:G8，单击“复制”，选择D3单元格，再单击菜单“编辑→选择性粘贴”，在打开的对话框的“粘贴”中选择“数值”，然后单击[确定]按钮。

6. 选中D3:D8，单击菜单“格式→单元格”，在打开的“单元格格式”对话框中，单击“数字”标签，在“分类”中选择“日期”，在右面的“分类”中选择上级要求的或是您喜欢的日期格式，删除不需要的E、F、G列，就大功告成了(请见图4)。按照本文开头的方法输入“DATEDIF”函数，任务就完成了。

快速创建自己的行事年历

习惯使用Year Plan的用户通过Excel函数功能和条件格式可以快速创建自己的行事年历。

1）使用Excel函数自动判断本月最后日期是28、29、30或31

方法：=IF(MONTH(DATE(年,月,日))=月,日,"")

说明：其中“年”“月”“日”指存放“年”“月”“日”数值的单元格，使用Month函数自动提取“月”数值，通过IF函数选择

2）条件格式用于自动显示星期六、日的日期

方法：=WEEKDAY(DATE(年,月,日),2)>=6

说明：使用函数weekday功能提取星期值

举例

日期计算公式（设单元格C2已输入当前日期）

1) 今日=TODAY()

2) 明日=TODAY()+1

3) 次日=C2+1

4) 下一个月=DATE(YEAR(C2),MONTH(C2)+1,DAY(C2))

5) 本月月底=DATE(YEAR(C2),MONTH(C2)+1,1)-1

=DATE(YEAR(C2),MONTH(C2)+1,0)

6) 下月月底=DATE(YEAR(C2),MONTH(C2)+2,1)-1

=DATE(YEAR(C2),MONTH(C2)+2,0)

7) 季度末=DATE(YEAR(C2),INT((MONTH(C2)+2)/3)*3+1,1)-1

=DATE(YEAR(C2),INT((MONTH(C2)+2)/3)*3+1,0)

8) 本半年底=DATE(YEAR(C2),INT((MONTH(C2)+5)/6)*6+1,1)-1

=DATE(YEAR(C2),INT((MONTH(C2)+5)/6)*6+1,0)

9) 本年底=DATE(YEAR(C2),12,31)

10) 当前日期时间=NOW()

测试如下

Excel显示内容

2006-12-15

2006-12-16

2006-12-16

2007-1-15

2006-12-31

2006-12-31

2007-1-31

2007-1-31

2006-12-31

2006-12-31

2006-12-31

2006-12-31

2006-12-31

单元格公式

=TODAY()

=TODAY()+1

=C2+1

=DATE(YEAR(C2),MONTH(C2)+1,DAY(C2))

=DATE(YEAR(C2),MONTH(C2)+1,1)-1

=DATE(YEAR(C2),MONTH(C2)+1,0)

=DATE(YEAR(C2),MONTH(C2)+2,1)-1

=DATE(YEAR(C2),MONTH(C2)+2,0)

=DATE(YEAR(C2),INT((MONTH(C2)+2)/3)*3+1,1)-1

=DATE(YEAR(C2),INT((MONTH(C2)+2)/3)*3+1,0)

=DATE(YEAR(C2),INT((MONTH(C2)+5)/6)*6+1,1)-1

=DATE(YEAR(C2),INT((MONTH(C2)+5)/6)*6+1,0)

=DATE(YEAR(C2),12,31)

今日

明日

次日

下一个月

本月月底

下月月底

季度末

本半年底

本年底

当前日期时间

2006-12-15 18:25

=NOW()

Excel快速输入时间值法

如果您对Excel的时间显示方式不满意的话，您还可以在“单元格格式”里进行修改，Excel内置了10种时间显示方式，调一款您喜欢的吧！ 大家都知道，在Excel 中输入时间是需要带上冒号的，如果有大量的时间值等待输入的话，这个冒号可得耽误不少功夫，而且又容易输入错误。

有什么好办法可以解决这个难题呢？当然是用嵌套在Excel里的利器VBA了！以下就是所有源代码，只要您按照我的说法一步一步做下去，即使对这些代码一点也不懂，也能依葫芦画瓢，迅速实现在Excel中不必输入冒号就能输入时间值的效果！

一、 启动Excel，打开您需要大量输入时间值的那个工作簿，然后按Alt-F11，启动VBA编辑器。

二、 选择菜单中“插入”──“添加模块”命令。这时，在屏幕左上方“工程”窗口中，会出现一

个“模块”的子目录，在它的右侧，显示着刚刚添加进来的模块“Module1”，您可以将它改名为您所喜欢的名字如“fasttime”。

三、 双击模块“fasttime”（如果您修改过模块名称的话），在右侧的代码编辑器中输入以下代码（或者从我的示例工作簿中复制）。

Option Explicit

Sub Auto_Open()

y = "Fast" '每当工作表内容发生变化时就运行指定过程

End Sub

Sub Fast()

On Error GoTo EnterError '如果有错误就跳往EnterTimeError语句

If Intersect(, Range("fasttime")) Is Nothing Then

Exit Sub '如果是非"fasttime"区域的单元格被改变，则结束过程

End If

'如果输入值小于1或者大于2400，则显示对话框用户输入值非法

If < 1 Or > 2400 Then

MsgBox "对不起，您的输入值非法！", vbExclamation

= ""

Exit Sub

End If

' 将输入值改成 HH:MM格式

= Format(, "00:00")

Exit Sub

EnterError:

Exit Sub

End Sub

四、把您将要大量输入时间值的单元格区域命名为“fasttime”。命名方法为，先将需要命名的单元格区域抹黑，然后选择Excel的菜单命令“插入”──“命名”──“定义”，在“在当前工作簿中的名称”栏中输入“fasttime”，最后按确定即可。

好了，完工！现在往“fasttime”区域里输入时间值试试看，是不是不用冒号了！？这里需要注意的是，输入值必须介于1到2400之间，否则将被视作非法值。举个例子来说，如果您需要输入上午九点二十分这样一个时间值，您就只需要输入0920这样一个数字就行了，Excel将在单元格中显示为“09:20

AM”；如果您需要输入晚上一十一点一十五分这样一个时间值，您就只需要输入2315这样一个数字就行了，Excel将在单元格中显示为“11:15 PM”。是不是很方便？

Excel让表头自动填写当前月份和日期

假设我们要做一个标题为“某公司6月份电费统计表”的Excel表格，其中“6”为进行电费统计的当月月份，第二行有制表日期。

电费每月公布一次，那么每月打开此表修改编辑时，都要对标题中的月份和制表日期进行修改，万一只改动了表内数据，而忘记以上两个时间的改动，会出现表头时间与表内数据的矛盾。

我们采取一个一劳永逸的措施来解决这个问题，任何时候打开此工作表，都让表头自动填写当前月份和日期。这要借助Excel三个函数来实现，在标题单元格输入“="实验中学"&& MONTH(TODAY())&&"月份电费统计表"”，函数“MONTH(TODAY())”表示当前月份，在第二行制表时间单元格输入“=TODAY()”。函数“TODAY()”表示当前日期，就出现图中结果。

完成上述设置，任何时候打开该表，我们大可放心地去做表内数据的处理工作了，不必担心表头时间会出现什么差错，无需对表头做任何改动。

EXCEL数据分析处理

Excel提供了强大的数据分析处理功能，利用它们可以实现对数据的排序、分类汇总、筛选及数据透视等操作。

在进行数据分析处理之前，首先必须注意以下几个问题：

（1）避免在数据清单中存在有空行和空列。

（2）避免在单元格的开头和末尾键入空格。

（3）避免在一张工作表中建立多个数据清单，每张工作表应仅使用一个数据清单。

（4）在工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。

（5）关键数据应置于数据清单的顶部或底部。

2.3.1 数据排序

2.3.1.1 数据排序的规则

Excel允许对字符、数字等数据按大小顺序进行升序或降序排列，要进行排序的数据称之为关键字。不同类型的关键字的排序规则如下：

数值：按数值的大小。

字母：按字母先后顺序。

日期：按日期的先后。

汉字：按汉语拼音的顺序或按笔画顺序。

逻辑值：升序时FALSE排在TRUE前面，降序时相反。

空格：总是排在最后。

2.3.1.2 数据排序步骤

（1）单击数据区中要进行排序的任意单元格。

（2）单击【数据】菜单，选择【排序】项，系统将弹出【排序】对话框，如图2-35所示。

图2-35 【排序】对话框

（3）在【排序】对话框中用下拉列表框选择要排序的关键字，关键字有“主要关键字”、“次要关键字”和“第三关键字”，根据需要分别选择不同的关键字；

（4）单击【确定】按钮，数据就按要求进行了排序。

当只有一个关键字时，可以单击工具栏上的升序按钮2.3.1.3 自定义排序

在有些情况下，对数据的排序顺序可能非常特殊，既不是按数值大小次序、也不是按汉字的拼音顺序或笔画顺序，而是按照指定的特殊次序，如对总公司的各个分公司按照要求的顺序进行排序，按产品的种类或规格排序等等，这时就需要自定义排序。

利用自定义排序方法进行排序，首先应建立自定义序列，其方法可参阅第1章的有关内容。建立好自定义序列后，即可对数据进行排序，方法是：单击数据区中要进行排序的任意单元格，单击【数据】菜单，选择【排序】项，在弹出的【排序】对话框中单击【选项】按钮，系统弹出【排序选项】对话框，如图2-36所示，在【自定义排序次序】的下拉列表中，选择前面建立的自定义序列，然后单击【确定】按钮，即可对数据进行自定义排序。

或降序按钮，进行自动排序。

图2-36 【排序选项】对话框

2.3.2 数据的查找与筛选

企业的管理人员经常需要在数据库或数据清单众多的数据中找出需要的数据，Excel提供了功能强大的数据查找与筛选工具。数据查找是指从原始数据中提取满足条件的数据记录，源数据不会改变，也不会被隐藏；数据筛选是指把数据库或数据清单中所有不满足条件的数据记录隐藏起来，只显示满足条件的数据记录。常用的数据查找与筛选方法有：记录单查找、自动筛选和高级筛选。

下面结合实例说明各种查找方法的具体应用。

【例2—11】图2-37为某公司的部分商品销售记录清单。

图2-37 某公司的商品销售明细清单

根据图2-37中的有关资料，可以分别采用记录单查找、自动筛选或高级筛选的方式查找或选择所需要的信息，如下所述：

2.3.2.1 记录单查找

记录单是查找和编辑数据的最简单的方法，利用记录单，不仅可以查找数据记录，还可以修改和删除记录、添加新的数据记录等。

1．查找数据记录

利用记录单查找数据记录的步骤如下：

（1）用鼠标单击数据清单或数据库中的任一非空单元格。

（2）单击【数据】菜单，选择【记录单】项，则系统弹出如图2-38所示的记录单。

图2-38 记录单

（3）单击记录单中的【条件】按钮，则弹出记录单条件对话框，如图2-39所示。

图2-39 记录单条件对话框

（4）输入条件，比如要查找“张三”的销售记录，则在【销售人员】栏中输入“张三”，然后单击【上一条】按钮或【下一条】按钮，系统就逐次显示满足条件的记录行。

还可以使用多个条件联合查找记录，此处不再叙述。

2．修改或删除记录

在图2-38所示的记录单中，即可对某一记录的各字段进行修改。若要删除显示的记录，只需单击记录单上的【删除】按钮即可。

3．添加新的记录

在图2-38所示的记录单中，单击记录单上的【新建】按钮，则出现各字段均为空白的新建记录单，在记录单中输入各字段的值，输入完毕后，单击【新建】按钮，即完成添加新记录。

2.3.2.2 自动筛选与自定义筛选

1．自动筛选

记录单检索数据每次只能显示一个数据行，当查询的数据较多，或要把查询的结果汇总成表时，就需要使用筛选工具了。自动筛选提供了快速检索数据清单或数据库的方法，通过简单的操作，就能筛选出需要的数据。利用自动筛选查找数据的步骤如下：

（1）用鼠标单击数据清单或数据库中的任一非空单元格。

（2）单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中选择【自动筛选】，则系统自动在数据清单的每列数据的标题旁边添加一个下拉列标标志，如图2-40所示。

图2-40 自动筛选的下拉列表标志

（3）单击需要筛选的下拉列表，系统显示出可用的筛选条件，从中选择需要的条件，即可显示出满足条件的所有数据。例如，要查找所有彩电的销售记录，单击“商品”右边的下拉列表，从中选择“彩电”项，则所有的彩电销售记录就显示出来，而其他的数据则被隐藏，如图2-41所示。

图2-41 彩电销售清单的筛选结果

如果有关彩电的销售记录很多，超过了10个，当需要只显示10个记录时，可单击“单价”、“数量”、“金额”等右边的下拉列表中的“前10个”项，系统弹出【自动筛选前10个】对话框，如图2-42所示。这里，在【显示】下拉列表中“最大”表示最大（最好）的前10个记录，“最小”表示最小（最差）的前10个记录。中间的编辑框中的数值表示显示的记录行数，系统默认值为10，但可以修改，根据需要输入数值即可。

图2-42 【自动筛选前10个】对话框

若要恢复所有的记录，则单击“商品”右边的下拉列表中的“全部”项。若要取消【自动筛选】状态，则单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中再次选择【自动筛选】。

2．自定义筛选方式

当在图2-40所示的下拉列表中选择“自定义”项时，则会弹出【自定义自动筛选方式】对话框，如图2-43所示，用户可根据具体条件对各栏进行设置。如要查找销售金额大于或等于“150000”且小于或等于“200000”的所有记录，则单击左上角的下拉箭头，选择“大于或等于”，右上角的条件值输入“150000”，单击左下角的下拉箭头，选择“小于或等于”，右下角的条件值输入“200000”，单击【确定】按钮，并选择“与”条件，则满足这些条件的所有记录就显示出来了，如图2-44所示。

图2-43 【自定义自动筛选方式】对话框

图2-44 【自定义自动筛选方式】筛选的结果

2.3.2.3 高级筛选

高级筛选可以使用较多的条件来对数据清单进行筛选，这些条件既可以是与条件，也可以是或条件，或与条件，与或条件的组合使用，还可以使用计算条件。

1．一般情况下的高级筛选

利用高级筛选对数据清单进行筛选的步骤如下：

（1）首先应建立一个条件区域。在条件区域中，同一行中的条件是与条件，也就是这些条件必须同时满足；不同行中的条件是或条件，也就是这些条件只要满足其一即可。如需要查找张三销售彩电的所有记录，则建立条件区域如图2-45所示。

图2-45 建立条件区域

（2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【筛选】子菜单中的【高级筛选】项，则系统弹出如图2-46所示的【高级筛选】对话框。

图2-46 【高级筛选】对话框

（3）一般情况下，系统将自动给出了数据区域，用户只需在【条件区域】栏中输入条件区域（本例中为B19:C20，也可以用鼠标拾取单元格区域，此时在条件区域中将显示“销售明细清单!$B$19:$C$20”。

（4）高级筛选结果可以显示在数据清单的原有区域中，也可以显示在工作表的其他空白单元格区域，系统默认的方式是在数据清单的原有区域中显示结果。若需要在工作表的其他空白单元格区域显示结果，则在【方式】项中选中“将筛选结果复制到其他位置”，并在【复制到】栏中输入需要显示筛选结果的单元格（开头的一个单元格即可）。图2-47为在原有区域显示的高级筛选结果。

图2-47 在原有区域显示的高级筛选结果

当需要显示原始的全部数据时，可以单击【数据】菜单，选择【筛选】子菜单中的项目，在【筛选】子菜单中选择【全部显示】即可。

同样的方法可以进行建立或条件、与条件与或条件的组合使用情况下的高级筛选。

2．计算条件情况下的高级筛选

在有些情况下，筛选的条件不是一个常数，而是一个随数据清单中数据变化的计算结果，此时无法直接利用高级筛选进行数据筛选。不过，我们可以通过计算条件的方法解决。以例2-20为例（见图

2-37），这里要找出销售额大于平均销售额的所有记录。步骤如下：

（1）在数据清单以外的任一空单元格内输入平均值计算公式，比如在单元格H20中输入公式“=AVERAGE(E3:E16)”，这里要特别注意的是存放平均值计算公式的单元格的列标不能与数据清单的任一列标相同，如图2-48所示。

图2-48 计算条件情况下的高级筛选

（2）设置条件区域，条件区域的列表可以是除数据清单中数据标题以外的任何文本，而筛选条件可在单元格B20中输入“=E3>$H$20”，这里要特别注意：必须以绝对引用的方式引用销售额平均值，以相对引用的方式引用数据清单中的数据。

（3）按照前面介绍的步骤进行高级筛选，其中高级筛选的数据区域为$A$2:$G$16；高级筛选的条件区域为$B$19:$C$20，则筛选结果如图2-48所示。

2.3.3 数据的分类与汇总

在对数据进行分析时，常常需要将相同类型的数据统计出来，这就是数据的分类与汇总。在对数据进行汇总之前，应特别注意的是：首先必须对要汇总的关键字进行排序。

2.3.3.1 进行分类汇总

例如，在例2-11中，要按地区进行自动分类汇总，其步骤如下：

（1）首先对“地区”进行排序，排序方法见前面所述。

（2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【分类汇总】项，系统弹出如图2-49所示的【分类汇总】对话框。

图2-49 【分类汇总】对话框

（3）在【分类汇总】对话框中，【分类字段】选项下选择“地区”，【汇总方式】选项下选择“求和”，【选定汇总项】选项下选定“数量”和“金额”，单击【确定】按钮，则分类汇总的结果如图2-50所示。

图2-50 按地区分类汇总结果

在图2-50中，左上角有3个按钮，按钮1表示1级汇总，显示全部的销售数量和销售金额汇总；按钮2表示2级汇总，显示各地区的全部销售数量和销售金额汇总；按钮3表示3级汇总，显示各地区的销售数量和销售金额的汇总明细及汇总额（即图2-50所示的汇总结果）。

图2-50中，左边的滑动按钮为隐藏明细按钮，单击此按钮，则将隐藏本级的明细数据，同时变为显示明细按钮，再单击按钮，则将显示本级的全部明细数据，同时变为。

在上述自动分类汇总的结果上，还可以再进行分类汇总，例如再进行另一种分类汇总，两次分类汇总的关键字可以相同，也可以不同，其分类汇总方法与前面的是一样的，此处不再介绍。

2.3.3.2 分类汇总的撤消

如果不再需要分类汇总结果，可在图2-49所示的【分类汇总】对话框中单击【全部删除】，即可撤消分类汇总。

2.3.4 数据透视表

数据透视表是用于快速汇总大量数据的交互式表格，用户可以旋转其行或列以查看对源数据的不同汇总，也可以通过显示不同的页来筛选数据，还可以显示所关心区域的数据明细。通过对源数据表的行、列进行重新排列，使得数据表达的信息更清楚明了。

2.3.4.1 建立数据透视表

以例2-11的数据为例，建立数据透视表的步骤如下：

（1）首先，要保证数据源是一个数据清单或数据库，即数据表的每列必须有列标。

（2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【数据透视表和图表报告】项，则系统弹出【数据透视表和数据透视图向导—3步骤之1】对话框，如图2-51所示，根据待分析数据来源及需要创建何种报表类型，进行相应的选择，然后单击【下一步】按钮，系统弹出【数据透视表和数据透视图向导—3步骤之2】对话框，如图2-52所示；

图2-51 【数据透视表和数据透视图向导—3步骤之1】对话框

图2-52 【数据透视表和数据透视图向导—3步骤之2】对话框

（3）默认情况下，系统自动将选取整个数据清单作为数据源，如果数据源区域需要修改，则可直接输入“选定区域”，或单击【浏览】按钮，从其他的文件中提取数据源。确定数据源后，单击【下一步】按钮，系统弹出【数据透视表和数据透视图向导—3步骤之3】对话框，如图2-53所示。

图2-53 【数据透视表和数据透视图向导—3步骤之3】对话框

（4）在【数据透视表和数据透视图向导—3步骤之3】对话框中，单击【版式】按钮，出现【数据透视表和数据透视图向导—版式】对话框，如图2-54所示。

（5）【数据透视表和数据透视图向导—版式】对话框中，再根据需要，将右边的字段按钮拖到左边的图上，这里，将“销售人员”拖到“行(R)”图上，将“商品”拖到“列(C)”图上，将“数量（台）”和“金额（元）”拖到“数据(D)”图上，如图2-55所示。

图2-54 【数据透视表和数据透视图向导—版式】对话框

图2-55 设置数据透视表的版式

（6）设置好版式后，单击【确定】按钮，则系统就返回到图2—44所示的【数据透视表和数据透视图向导—3步骤之3】对话框，然后单击【完成】按钮，数据透视表就完成了，如图2-56所示。

这样，通过图2-56的数据透视表，即可看出每个销售人员所销售商品的种类、数量、销售额及其合计数，从而以此为基础可很方便地对每个销售人员的销售业绩进行评价。

图2-56 各个销售人员销售商品的数据透视表

2.3.4.2 数据的透视分析

在图2-56所建立的数据透视表上，可以很方便地进行多角度的统计与分析。比如要了解李四所销售商品的情况，可在“销售人员”下拉列标中只选中“李四”，然后单击“确定”按钮，则李四的销售情况如图2-57所示。

图2-57 李四的销售情况汇总

还可以建立透视图，方法是：单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【数据透视图】项，则系统自动显示出数据透视图，从而得到每个销售人员的更为直观的销售情况。

Excel中的基本求和与条件求和

大家都知道，在Excel诸多函数中，使用最多的是求和类函数。今天我们就以图1所示的数据表格为例详细讨论一下求和的问题。

本文所涉及函数如下：

1、SUM（Number1,Number2……）

2、SUMIF（Range,Criteria,Sum_Range）

3、数组公式

（图片较大，请拉动滚动条观看）

按图2所示的要求，我们来看看具体的统计过程：

（图片较大，请拉动滚动条观看）

一、基本求和——用SUM函数

1、选中F307单元格，输入公式：=SUM(F2:F302)，即可求出所有工程的“建筑面积”之和。

2、用“填充柄”将公式复制到G307单元格中，就可以求出所有工程的“工程造价”之和。

注意：如果要临时查看一下某些数据的和，可以这样操作：鼠标右击状态栏，在随后出现的快捷菜单（如图3）中，选定“求和”选项（通常是默认选项）。

然后选中需要查看“和”的数值单元格（区域），状态栏中即刻显示出相应的和（如图4）。

避免Excel产生求和误差

在用Excel进行有关数据汇总时，往往会出现误差，即自动求和结果比实际手工求和的结果大。

在财务工资表中，涉及到“工资税金”的计算问题，笔者依照“工资税金”的计算方法，制定出Excel的计算公式，根据每个职工的收入项进行相关的计算后，将运算结果自动填入到每个职工的“税金”单元格内。但在进行“税金栏目”纵向汇总求和时，却发现自动求和的结果比手工实际求和的结果多几分钱。

经过分析发现，在用Excel求和计算时（尤其是含有小数位的数据），一般会在单元格的“格式属性”中设置保留两位小数，而Excel的自动计算功能往往会精确到小数点后两位数以上，只不过是由于用户的设置原因，在表中看不出来而已。这样，Excel在求和时是按精确位相加的，而不是按显示的两位小数相加，这就产生了舍入误差。

解决办法：在工资表的每个职工“税金栏目”计算公式里，事先加入“四舍五入”函数，即：工资税金=ROUND（税金计算公式，2），计算结果保留两位小数。使得Excel在数据计算时，按“显示位”得出计算结果，避免按“精确位”相加所带来的隐性误差，使出现的问题迎刃而解。

Excel中用宏和VBa自动统计成绩

在学校的教学活动中，通过统计分析学生的考试成绩，了解教学情况，为改进以后的教学工作提供依据，是整个教学工作中非常重要一环。但经常按各种不同的指标统计分析成绩，又是一件很枯燥，很繁琐的事。哪么，能否对需统计的各种指标，无需人工干预，让计算机自动完成呢？答案是肯定的，笔者通过Excel中自带的Vba，实现了这个设想。为能给被统计成绩困扰的朋友提供一些帮助，下面以我校高中三年级理科成绩统计为例，介绍其作法，供大家参考。

1． 设计流程,编制程序源代码：为便于说明做法，同时代码简短一点，这里以我校高三理科1-4班成绩统计为例，说明其做法。另外为使编写代码效率高一些，这里采用先录制一部分为宏，再在vba中打开编辑相结合的办法编写。

① 从外部的“考试成绩.XLS”工作簿中将1-4班的成绩拷贝，依次粘贴到“成绩统计表.XLS”工作簿中的“理科”表中；

程序源代码：

Sub 拷外部理科0()

State = xlMinimized

Windows("考试成绩.xls").Activate

State = xlMaximized

Sheets("1班").Select

Range("A1:S60").Select

State = xlMinimized

Windows("成绩统计表.xls").Activate

State = xlMaximized

Sheets("理科").Select

Row = 1

Range("A1").Select

拷贝“考试成绩簿”中的1班成绩表中数据，粘贴到到成绩统计表工作簿的理科表A1

Range("S22").Select

croll Down:=1

Range("S44").Select

croll Down:=1

Range("S66").Select

Windows("考试成绩.xls").Activate

State = xlNormal

Sheets("2班").Select

Range("A2").Select

croll Down:=43

Range("A2:S58").Select

yMode = False

Windows("成绩统计表.xls").Activate

State = xlNormal

Range("A61").Select

拷贝考试成绩簿中的2班成绩表中数据，粘贴到“成绩统计”工作簿的理科表A61

Windows("考试成绩.xls").Activate

State = xlNormal

Sheets("3班").Select

Range("A1:S59").Select

yMode = False

Windows("成绩统计表.xls").Activate

State = xlNormal

Range("S58").Select

croll Down:=1

Range("S80").Select

croll Down:=1

Range("S102").Select

croll Down:=1

Range("A118").Select

拷贝成绩簿中的3班成绩表中数据，粘贴到成绩统计表工作簿的理科表A118

Rows("118:118").Select

yMode = False

Shift:=xlUp

Range("D122").Select

Windows("考试成绩.xls").Activate

State = xlNormal

Sheets("4班").Select

Range("A2:S71").Select

Windows("成绩统计表.xls").Activate

State = xlNormal

croll Down:=1

Range("D144").Select

croll Down:=1

Range("D166").Select

croll Down:=1

Range("D188").Select

croll Down:=-1

Range("A176").Select

‘拷贝四班成绩

End Sub

② 利用Excel的统计函数“RANK”将理科表中学生的各科成绩排名次；目的是让每位学生了解自己在全年级学生中的排名的位置。

程序代码如下：

Sub 理名次()

Range("D2").Select

aR1C1 = "=RANK(RC[-1],R2C3:R246C3,0)"

ll Destination:=Range("D2:D246"), Type:=xlFillDefault

Range("D2:D246").Select

croll Down:=-13

Row = 1 ’在工作表的D2单元格输入公式，并用拖的方式在D2-D246列复制

Range("F2").Select

yMode = False

e What:="$c$2:$c$246", Replacement:="$e$2:$e$246", LookAt _

:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

拷贝D列粘贴到F列F2

Range("H2").Select

yMode = False

e What:="e", Replacement:="g", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False