在数据列表中简单分析数据_Excel 数据分析师
当用户面对海量的数据时，若要从中获取最有价值的信息，不仅要选择数据分析的方法，还必须掌握数据分析的工具。Excel 2003提供了大量帮助用户进行数据分析的功能。本篇主要讲述如何在Excel 中运用各种分析工具进行数据分析，重点介绍排序、筛选、数据透视表、Mivrosoft Query、分析工具为、单变量求解、模拟运算表等，同时配以各种典型的实例，使用户能够迅速掌握运用Excel 进行数据分析的各种功能和方法。
第２６章 在数据列表中简单分析数据
２６．１ 了解Excel 数据列表
Excel 数据列表是由一行文字字段组成的有组织的信息集合，它还有数值或文本的附加数据行。图展示了一个Excel数据列表的实例。
| 工号 | 姓名 | 性别 | 籍贯 | 出生日期 | 入职日期 | 月工资 | 绩效系数 | 年终奖金 |
| 535353 | 林达 | 男 | 哈尔滨 | 1978-5-28 | 2003-6-20 | 4750 | 0.50 | 4,275 |
| 626262 | 贾丽丽 | 女 | 成都 | 1983-6-5 | 2003-6-13 | 2750 | 0.95 | 4,703 |
| 727272 | 赵睿 | 男 | 杭州 | 1974-5-25 | 2003-6-14 | 2750 | 1.00 | 4,950 |
| 424242 | 师丽莉 | 男 | 广州 | 1977-5-8 | 2003-6-11 | 4750 | 0.60 | 5,130 |
| 323232 | 岳恩 | 男 | 南京 | 1983-12-9 | 2003-6-10 | 4250 | 0.75 | 5,738 |
| 131313 | 李勤 | 男 | 成都 | 1975-9-5 | 2003-6-17 | 3250 | 1.00 | 5,850 |
| 414141 | 郝尔冬 | 男 | 北京 | 1980-1-1 | 2003-6-4 | 3750 | 0.90 | 6,075 |
| 313131 | 朱丽叶 | 女 | 天津 | 1971-12-17 | 2003-6-3 | 3250 | 1.10 | 6,435 |
| 212121 | 白可燕 | 女 | 山东 | 1970-9-28 | 2003-6-2 | 2750 | 1.30 | 6,435 |
| 929292 | 师胜昆 | 男 | 天津 | 1986-9-28 | 2003-6-16 | 3750 | 1.00 | 6,750 |
| 525252 | 郝河 | 男 | 广州 | 1969-5-12 | 2003-6-12 | 3250 | 1.20 | 7,020 |
| 121212 | 艾思迪 | 女 | 北京 | 1966-5-4 | 2003-6-1 | 3250 | 1.20 | 7,020 |
| 232323 | 张祥志 | 男 | 桂林 | 1989-12-3 | 2003-6-18 | 3250 | 1.30 | 7,605 |
| 919191 | 岳凯 | 男 | 南京 | 1977-6-23 | 2003-6-9 | 3250 | 1.30 | 7,605 |
| 828282 | 孙丽星 | 男 | 成都 | 1966-12-5 | 2003-6-15 | 3750 | 1.20 | 8,100 |
| 616161 | 艾利 | 女 | 厦门 | 1980-10-22 | 2003-6-6 | 4750 | 1.00 | 8,550 |
| 818181 | 李克特 | 男 | 广州 | 1988-11-3 | 2003-6-8 | 3750 | 1.30 | 8,775 |
| 434343 | 邓星丽 | 女 | 西安 | 1967-5-27 | 2003-6-19 | 3750 | 1.30 | 8,775 |
| 717171 | 吉汉阳 | 男 | 上海 | 1968-1-5 | 2003-6-7 | 4250 | 1.20 | 9,180 |
| 515151 | 马豪 | 男 | 上海 | 1958-3-1 | 2003-6-5 | 4250 | 1.50 | 11,475 |
此数据列表的第一行是文字字段，下面包含若干行数据信息。它一共包含９列，分别由文字、数值、日期不同类型的数据构成，年终奖金则是通过月工资和绩效系数利用公式计算而得出。数据列表中列通常称为字段，行称为记录。为了保证数据列表能够有效地工作，它必须具备以下特点：
１）每列必须包含同类的信息。
２）列表的第一行应该包含文字字段，每个字段标题用于描述下面对应列的内容。
３）列表中不能存在重复的字段标题。
４）数据列表的列不能超过２５６列，行不能超过65 536行。
如果一个工作表中包含多个数据列表，列表间要以一空行或空列将数据信息分隔。
２６．２数据列表的使用
Excel最常用的任务之一就是管理一系列的数据列表，例如，电话号码清单、消费者名单、供应商名称等。这些数据列表都是根据用户需要而命名的。用户可以对数据列表进行如下的操作：
１）在数据列表中输入数据。
２）根据特定的条件对数据列表进行排序和筛选。
３）对数据列表进行分类汇总。
４）在数据列表中使用函数和公式达到特定的目的。
５）在数据列表中创建数据透视表。
２６．３创建数据列表
用户可以根据自己的需要创建一张数据列表来满足存储、分析数据的要求，具体创建过程参见以下步骤：
步骤１．在表格中的第一行，俗称“表头”，为其对应的每一列输入描述性的文字，如果文字过长，可以使用“自动换行”来避免列宽的增加。
步骤２．在每一列中输入相同类型的信息。
步骤３．依次选中数据列表的每一列设置相应的单元格格式。
提示：单击Ａ２单元格，单击菜单栏“窗口”——“冻结窗口”命令，这样在滚动数据列表时，始终可以看到标题行。
２６．３．１ Excel记录单功能
用户可以在数据列表下的第一个空行内输入数据来添加新的士信息。但是使用Excel记录单功能会更加方便一些，尤其是对于喜欢使用对话框输入数据的用户而言。
以上面数据列表为例，使用“记录单”添加新的数据信息：
步骤１．单击数据列表下的第一个空白行单元格。
步骤２．单击菜单栏“数据”——记录单命令。
步骤３．在出现的对话框中单击“新建”按钮。
步骤４．在“记录单”对话框的空白字段中输入相关信息，用户可以使用ＴＡＢ键在字段中快速移动。数据信息输入完毕后可以在对话框内单击“新建”按钮或直接按ＥＮＴＥＲ键，新增的数据即可显示到数据列表中。
“年终奖金”是利用公式计算出的，Excel会把它们自动添加到新记录中去。
有关“记录单”对话框中按钮的用途，请参阅表
Excel“记录单”对话框按钮的用途
|
记录单按钮
|
用 途
|
|
新建
|
单击“新建”按钮可以在数据列表中添加新记录
|
|
删除
|
删除已经显示的记录
|
|
还原
|
在没有单击“新建”按钮之前，恢复所编辑的任何信息
|
|
上一条
|
显示数据列表中的前一条记录
|
|
下一条
|
显示数据列表中的下一条记录
|
|
条件
|
用户输入设置搜索记录的条件，单击“上一条”和“下一条”按钮显示符合条件的记录
|
|
关闭
|
关闭“记录单”对话框
|
２６．３．２ 使用记录单为数据列表命名动态名称
为数据列表定义动态名称的方法有很多，如函数、列表等，使用Excel记录单功能也可以为数据列表来定义动态名称，以图所示的数据列表为例，具体参照以下步骤。
步骤１．将图所示的数据列表定义名称为“Database”，引用位置为“=记录单命名！$A$1:$F$15”。
步骤２．单击菜单栏“数据”——“记录单”命令，在出现的对话框中输入新的数据。
步骤３．单击“新建”按钮将新数据添加到所示的数据列表中。
再次查看数据列表定义的名称，引用位置变成了“＝记录单命名!$A$1:$F$16”，实现了随数据列表的扩展而随时更新该名称的定义。
注意：为数据列表定义的名称必须为”Database”，为数据列表添加新的数据也必须使用Excel记录单功能来添加，否则将不能为数据列表定义动态的名称。
２６．４ 数据列表排序
Excel提供了多种方法对工作表区域进行排序，用户可以根据需要按行或列、按升序或降序以及使用自定义排序命令。当用户按行进行排序时，数据列表中的列将被重新排列，但行保持不变；如果按行进行排序，行将被重新排列而保持不变。
２６．４．１ 一个简单排序的例子
未经排序的数据列表看上去杂乱无章，不利于用户查找分析数据。
| 月 | 日 | 科目编号 | 科目名称 | 金额 |
| 12 | 02 | 410502 | 办公费 | 24.40 |
| 01 | 08 | 410502 | 办公费 | 425.00 |
| 11 | 10 | 410502 | 办公费 | 186.96 |
| 10 | 12 | 410502 | 办公费 | 72.65 |
| 11 | 19 | 410502 | 办公费 | 110.20 |
| 07 | 19 | 410502 | 办公费 | 560.47 |
| 10 | 21 | 410502 | 办公费 | 11.00 |
| 09 | 23 | 410502 | 办公费 | 961.00 |
| 09 | 23 | 410502 | 办公费 | 80.00 |
| 05 | 24 | 410502 | 办公费 | 161.89 |
| 08 | 26 | 410502 | 办公费 | 262.80 |
| 08 | 26 | 410502 | 办公费 | 5.00 |
| 10 | 28 | 410502 | 办公费 | 450.00 |
| 07 | 30 | 410502 | 办公费 | 8.00 |
| 12 | 31 | 410502 | 办公费 | 278.00 |
| 12 | 02 | 410513 | 低值易耗品 | 569.80 |
| 03 | 05 | 410513 | 低值易耗品 | 496.00 |
| 03 | 12 | 410513 | 低值易耗品 | 88.89 |
| 05 | 13 | 410513 | 低值易耗品 | 120.77 |
| 04 | 13 | 410513 | 低值易耗品 | 267.00 |
| 02 | 18 | 410513 | 低值易耗品 | 235.19 |
| 01 | 19 | 410513 | 低值易耗品 | 125.77 |
要对图所示的数据列表按“科目名称”排序，请参照以下步骤进行：
步骤１．单击数据列表中的任意一个单元格，单击菜单“数据”——“排序”，出现“排序”对话框。
步骤２．在“主要关键字”下拉列表框中选择“科目名称”，如果用户想按“升序”排序，即按照“科目名称”中第一个汉字的笔划由简到繁的顺序排序，可以单击“选项”，出现“排序选项”对话框。
步骤３．在“排序选项”对话框中的“方法”选项中选择“笔划排序”选项按钮，单击“确定”按钮，返回“排序”对话框。
步骤４．在“排序”对话框中的“主要关键字”选项中选择“升序”，最后单击“确定”按钮完成。
２６．４．２ 按多列进行排序
如果用户希望列表中的记录按“月”的升序来排序、相同月份的记录按“科目名称”升序排序、“月”和“科目名称”都相同的记录按金额从小到大的顺序来排序，就要对列表中３个不同的列进行排序才能达到用户的要求。
数据列表的数据如果包含文本型数字，会出现“排序警告”。
如果用户数据列表中要排序的整列数据都是文本型数字，可以在“、排序警告”对话框中直接单击“确定”按钮，排序不受影响。
如果用户数据列表中要排序的整列数据有的是文本，还有的是数值，则需要在“排序警告”对话框中“您希望如何处理？”选项中选择“将任何类似数字的内容排序”。如果选择了“分别将数字和以文本形式存储的数字排序”排序将会出现另一种结果，这个结果在本例中不符合用户原有的排序需求。
对一张数据列表，Excel一次最多允许满足3个依据条件的排序，这在大多数情况下已经足够了。如果用户需要使用多于3个依据条件的排序，可以参照以下步骤实现。
步骤１．先对最次要的３列数据进行排序。
步骤２．再对次要的列进行排序，依此类推。
步骤３．最后对最重要的列排序。
２６．４．３对数据列表中的某部分进行排序
如果用户只希望对数据列表中的某一特定部分进行排序，例如对所示的数据列表中的５～２０行按“性别”排序，可以参照以下步骤进行。
步骤１．选择数据列表中区域Ａ５：Ｉ２０。
步骤２．单击菜单“数据”——‘排序’。
步骤３．在“排序”对话框中的“主要关键字”下拉列表中选择列Ｃ，单击“确定”按钮完成。
２６．４．４ 对数据列表中的行进行排序
按行进行排序就是说对数据列表中的列进行排序，而数据列表中的行保持不变，要对如图所示的数据列表按行排序。
| 项 目 | 2 | 6 | 3 | 4 | 5 | 1 | 7 | 11 | 12 | 8 | 9 | 10 | 总计 |
| 财务科 | 44,056 | 49,794 | 54,037 | 55,543 | 57,301 | 70,605 | 103,743 | 51,856 | 109,015 | 115,520 | 117,889 | 215,892 | 951,401 |
| 经理室 | 85,077 | 50,817 | 84,418 | 77,137 | 235,192 | 88,786 | 75,970 | 54,576 | 63,911 | 63,273 | 55,294 | 114,047 | 912,605 |
| 培训中心 | 58,535 | 253,534 | 69,524 | 192,114 | 208,634 | 61,204 | 77,833 | 208,133 | 209,831 | 1,227,324 | 277,616 | 30,898 | 2,563,110 |
| 人力资源科 | 115,822 | 264,139 | 144,497 | 317,560 | 257,344 | 362,294 | 109,355 | 208,320 | 172,963 | 165,556 | 149,892 | 219,315 | 2,107,098 |
| 信息办公室 | 303,490 | 633,410 | 356,276 | 642,354 | 764,554 | 582,888 | 366,901 | 529,754 | 595,966 | 1,579,404 | 619,581 | 580,153 | 6,617,831 |
| 总计 | 606,980 | 1,251,694 | 708,751 | 1,284,707 | 1,523,025 | 1,165,776 | 733,803 | 1,052,639 | 1,151,686 | 3,151,079 | 1,220,272 | 1,160,306 | 13,152,044 |
步骤１．选择数据列表中Ｂ２：Ｍ７中数据，单击菜单“数据”——“排序”。
步骤２．在“排序”对话框中单击“选项”按钮，在弹出的“排序选项”对话框中的“方向”选项中选择“按行排序”。
步骤３．单击“确定”按钮，在“排序”对话框中的“主要关键字”下拉列表中选择“行１”。
步骤４．单击“确定”按钮完成。
２６．４．５使用自定义排序
Excel可以根据数字顺序或字母顺序进行排序，但它并不局限于使用标准的排序顺序。如果用户想用一种特殊的非字母顺序的排序方法对一系列数据进行排序，则可以使用自定义排序次序。用户要想查看自定义排序次序有哪些内容，可以单击要排序的数据列表，单击菜单“数据”——“排序”，在“排序”对话框中单击“选项”按钮，在弹出的“排序选项”对话框中单击“自定义排序次序”的下拉列表按钮。
Excel默认的自定义序列如下。
缩写英文星期：Sun,Mon,Tue,Wed,Thu,Fri,Sat
英文星期：Sundqy,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday
缩写月份：Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
月份：January,February,March,April,May,June,July,August,September,October,November,December
周：日，一，二，三，四，五，六
星期：星期日，星期一，星期二，星期三，星期四，星期五，星期六
月份：一月，二月，三月，四月，五月，六月，七月，八月，九月，十月，十一月，十二月
地支：子丑，寅，卯，辰，巳，午，未申，酉，戌，亥
天干：甲，乙，丙，丁，戍，己，庚，辛，壬，癸
如果Excel默认的自定义序列仍不能满足排序的需要，用户可以自己创建一个新的自定义排序次序，例如图所示的表二按照表一的标识顺序排序，可以参见以下步骤。
表一
标识 单价 数量
JBH1670N01
JBH1673N01
JBH1674N01
JBH1675N01
JHH1705N03
JHH1747N01
JJH1584N01
JWH1404N01表二
标识 单价 数量
JBH1670N01 46
JBH1673N01 3 28
JBH1674N01 55
JBH1675N01 53
JHH1705N03 5 1
JHH1747N01 3 4
JJH1584N01 49 46
JWH1404N01 16
标识 单价 数量
JBH1670N01
JBH1673N01
JBH1674N01
JBH1675N01
JHH1705N03
JHH1747N01
JJH1584N01
JWH1404N01表二
标识 单价 数量
JBH1670N01 46
JBH1673N01 3 28
JBH1674N01 55
JBH1675N01 53
JHH1705N03 5 1
JHH1747N01 3 4
JJH1584N01 49 46
JWH1404N01 16
步骤１．单击菜单栏工具——选项，出现“选项”对话框，单击“自定义序列”选项卡。
步骤２．用户可以在“输入序列”的编辑框内手工输入自定义的排序次序，按ＥＮＴＥＲ键分隔每个自定义排序的数据标志，但这样手工输入是比较繁琐的，建议采用“导入”功能。单击“导入”按钮旁边的引用编辑框，选择表一中的Ａ３：Ａ１１区域，再单击“导入”按钮。
单击“确定”按钮完成自定义排序次序。
步骤３．单击表二中的任意单元格，单击菜单栏“数据”——排序，单击“排序”对话框中“主要关键字”下拉列表并选择“标识”，最后单击“确定”按钮。
如果用户想要删除一个自定义序列，可以选择“自定义序列”中的序列，然后单击“删除”按钮。Excel默认的自定义序列则不可以删除。
对数据列表进行排序时，要注意含有公式的单元格。如果是对行进行排序，则在排序之后，数据列表中对同一行的其他单元格的引用可能是正确的，但对不同行的单元格的引用却不再是正确的。
同样，如果是对列排序，则排序后，数据列表中对同一列的其他单元格的引用可能是正确的，但对不同列的单元格的引用却是错误的。
以下是对含有公式的数据列表排序前后的对照图，它显示了对含有公式的数据列表进行排序存在的风险。数据列表中第６行“利润差异”是用来计算利润的年差值变化的，使用了相对引用公式。例如，单元格Ｃ６“２００５年利润差异”使用了公式“＝Ｃ５－Ｂ５”来计算２００６年和２００５年的利润差。
| 年份 项目 |
2006 | 2005 | 2004 | 2003 | 2002 | 2001 | 2000 |
| 收入 | 545,508.00 | 417,106.00 | 564,008.00 | 471,229.00 | 488,584.00 | 193,797.00 | 153,134.00 |
| 成本 | 409,131.00 | 312,829.50 | 423,006.00 | 353,421.75 | 366,438.00 | 145,347.75 | 114,850.50 |
| 期间费用 | 109,101.60 | 83,421.20 | 112,801.60 | 94,245.80 | 97,716.80 | 38,759.40 | 30,626.80 |
| 利润 | 27,275.40 | 20,855.30 | 28,200.40 | 23,561.45 | 24,429.20 | 9,689.85 | 7,656.70 |
| 利润差异 | -6,420.10 | 7,345.10 | -4,638.95 | 867.75 | -14,739.35 | -2,033.15 |
按行排序后“２００５年利润差异”年差值数据改变了，为了能正确计算年差值，Ｇ６单元格“２００５年利润差异”的公式应为“＝Ｇ５：Ｈ５”第６行的其他公式也是错误的。
为了愕然在对含有公式的数据列表中排序出错，可以遵守以下规则：
数据列表单元格的公式中引用了数据列表外的单元格数据，请使用绝对引用。
对行排序，避免使用引用其他行的单元格的公式。
对列排序，避免使用引用其他列的单元格的公式。
２６．５筛选数据列表
筛选数据列表的意思就是将不符合用户特定条件的行隐藏起来。Excel提供了两种筛选数据列表的命令。
自动筛选：适用于简单的筛选条件。
高级筛选：适用于复杂的筛选条件。
２６．５．１自动筛选
要使用Excel的“自动筛选”命令，首先单击数据列表中的任意单元格，然后依次单击菜单栏“数据”——“筛选”——“自动筛选”。单击数据列表任何一列标题行的下拉箭头，选择希望显示的选定行的信息，Excel会自动筛选出包含这个特定行信息的全部数据。
以文件“２６．１数据列表的实例．xls”为例I此文件可从Excel Home网站下载），筛选出“性别”为“男”的所有记录，可以参照以下步骤进行。
步骤１．单击数据列表中的任意单元格。
步骤２．单击菜单栏“数据”——“筛选”——“自动筛选”。
步骤３．单击数据列表标题“性别”的下拉列表，选择“男”，完成。筛选完成后，被筛选列“性别”标题的下拉箭头会改变颜色，同时数据列表中的行号也会改变颜色。
１．对多列使用自动筛选
用户可以对数据列表中的任意多列同时指定“自动筛选”条件。也就是说，行以数据列表中某一列为条件进行筛选，然后在筛选出的记录中以另一列为条件进行筛选，依此类推。例如，只要显示数据列表中“月工资”为４７５０的男性记录，可以参照以下步骤进行。
步骤１．在菜单栏上依次单击“数据”——“筛选”——“自动筛选”，进入“自动筛选”模式。
步骤２．将“性别”为“男”的记录筛选出来。
步骤３．单击标题“月工资”的下拉按钮，选择“４７５０”，完成。
２．使用“自动筛选”查找前１０条记录
在数据列表中的数字字段上使用“自动筛选”命令中的“自动筛选前１０个”功能，可以显示数据列表中的前Ｎ个最大值或最小值，还可以查找那些占某列前百分之几或后百分之几的数据。例如，要想在数据列表中筛选出“月工资”最高的前１１个人的记录，可以这样操作：
步骤１．单击数据列表进入“自动筛选”模式。
步骤２．单击“月工资”下拉按钮，在下拉列表中选择“前１０个”，在弹出的“自动筛选前１０个”对话框中，“显示”选择“最大”，第２个框中将１０改为１１，或使用微调按钮将数字１０调到１１。
步骤３．单击“确定”按钮完成筛选。
如果要筛选出数据列表中“年终奖金”最低的５０％的数据记录。可以在‘自动筛选前１０个’对话框中，“显示”选择“最小”，第２个框中输入数字５０，第３个框中选择“百分比”。
注意：在“自动筛选前10个”对话框中第2个框中只能输入1~500之间的任意数值，否则会出现错误提示。
３．使用“自动筛选”查找空白单元格
如果数据列表中的某列含有空白的单元格，则会在其“自动筛选”的下拉列表的底部出现“空白”和“非空白”。如果要筛选出此列不含数据项所在的行，则可将“空白”指定为“自动筛选”的条件。如果想从数据列表中取消含有空白数据项的行，则应指定“非空白”为“自动筛选”的条件。
４．使用自定义筛选
一般情况下，“自动筛选”只能筛选出某列中当前包含的某个单一的数据。而利用“自定义自动筛选方式”，用户可以通过多个条件筛选或查询数据列表中含有某一特定范围的值所在的行，使筛选数据列表获得较大的灵活性。
在数据列表中单击筛选下拉箭头，筛选的下拉列表中选择“自定义”项，就会出现“自定义自动筛选方式”对话框。
例如，要在图所示的数据列表中将“年终奖金”在５０００～８０００元的数据筛选出来，可以这样操作：
步骤１．首先，在菜单栏上依次单击“数据”——筛选——自动筛选，进入“自动筛选”模式。
步骤２．然后，单击“年终奖金”下拉按钮，在下拉列表中选择“自定义”。
步骤３．在弹出的“自定义自动筛选方式”对话框左上角的下拉列表框，选择“大于等于”，在右上方的下拉列表框中输入“50
00”；在左下角的下拉列表框，选择“小于等于”，在右下方的下拉列表框中输入“8000”。
步骤４．单击“确定”按钮完成筛选。
５．在“自定义筛选方式”中使用通配符
“自定义自动筛选方式”对话框允许使用两种通配符条件：
使用星号“＊”代表任意系列的字符串
使用问号“？”代表任意一个字符
如果要表示“＊”或“？”字符本身，需要在他们之前加上波形符“～”，如“～＊”、“～？”。
有关通配符使用的说明请参见表
通配符使用的说明
| 条 | 件 | 符合条件的数据 |
| 等于 | Sh?ll | Shall,Shell |
| 等于 | 杨？伟 | 杨大伟。杨鑫伟 |
| 等于 | H？？t | Hart，Heit，Hurt |
| 等于 | L*n | Lean，Lesson，Lemon |
| 包含 | ~？ | 可以筛选出数据中含有？的数据 |
| 包含 | ~* | 可以筛选出数据中含有*的数据 |
６．取消自动筛选
如果用户想要取消对指定列的“自动筛选”，则可以单击该列的下拉列表框并选择“（全部）”；如果用户想要删除当前数据列表中的所有“自动筛选”条件，则可以单击菜单“数据”）——筛选——单击“全部显示”命令；如果想退出“自动筛选”模式，则可以单击菜单“数据”——“筛选”——再次单击“自动筛选”命令。
７．复制和删除自动筛选后的数据
有时需要将自动筛选出的结果复制到另一处或另外一个工作表中，单击自动筛选出数据的任意一个单元格，按ＣＴＲＬ＋Ａ组合键，单击鼠标右键，选择“复制”；选择想要粘贴到的区域单击鼠标右键，再选择“粘贴”。当复制自动筛选数据列表中数据时，只有可见的行被复制；同样，选择并删除自动筛选出的结果时，自动筛选中被隐藏的行也将不受影响。
２６．５．２ 使用高级筛选
Excel高级筛选功能是自动筛选的升级，它可以将自动筛选的定制格式改变自定义设置。高级筛选不但包含了自动筛选的所有功能，还可以设置更多的筛选条件，它的功能也更加优于自动筛选。高级筛选能够提供以下功能：
可以设置更复杂的筛选条件
可以将筛选出的结果存放在不同的位置
可以指定计算的筛选条件
可以筛选出不重复的记录项
１．设置高级筛选的条件区域
“高级筛选”与“自动筛选”不同，它要求在数据列表以外的区域内单独设定用户所需的筛选条件。如果用户选择在原数据表中显示筛选结果，当某些数据行被隐藏时可能会影响到用户定义的筛选条件的显示。因此，通常把这些条件区域放置在数据列表的上面或下面。
一个“高级筛选”的条件区域至少要包含现行，第一行是列标题，列标题应和数据列表中的标题匹配，建议采用“复制”、“粘贴”命令将数据列表中的标题粘贴到条件区域的顶行；第二行必须由筛选条件构成。条件区域并不需要含有数据列表中的所有列的标题，与筛选过程无关的列可以不使用。
２．两列之间运用“关系与”条件
以图所示的数据列表为例，运用“高级筛选”功能将“性别”为“男”且“绩效系数”为“１．００”的数据筛选出来，并复制到数据列表的下方单元格Ａ26所在的区域中，请参照如下步骤。
步骤１．在数据列表的顶端插入若干新行来放置条件区域。
步骤２．创建条件区域如图所示。
| 性别 | 绩效系数 | |||||||
| 男 | ||||||||
| 1.00 | ||||||||
| 工号 | 姓名 | 性别 | 籍贯 | 出生日期 | 入职日期 | 月工资 | 绩效系数 | 年终奖金 |
| 535353 | 林达 | 男 | 哈尔滨 | 1978-5-28 | 2003-6-20 | 4750 | 0.50 | 4,275 |
| 626262 | 贾丽丽 | 女 | 成都 | 1983-6-5 | 2003-6-13 | 2750 | 0.95 | 4,703 |
| 727272 | 赵睿 | 男 | 杭州 | 1974-5-25 | 2003-6-14 | 2750 | 1.00 | 4,950 |
| 424242 | 师丽莉 | 男 | 广州 | 1977-5-8 | 2003-6-11 | 4750 | 0.60 | 5,130 |
| 323232 | 岳恩 | 男 | 南京 | 1983-12-9 | 2003-6-10 | 4250 | 0.75 | 5,738 |
| 131313 | 李勤 | 男 | 成都 | 1975-9-5 | 2003-6-17 | 3250 | 1.00 | 5,850 |
| 414141 | 郝尔冬 | 男 | 北京 | 1980-1-1 | 2003-6-4 | 3750 | 0.90 | 6,075 |
| 313131 | 朱丽叶 | 女 | 天津 | 1971-12-17 | 2003-6-3 | 3250 | 1.10 | 6,435 |
| 212121 | 白可燕 | 女 | 山东 | 1970-9-28 | 2003-6-2 | 2750 | 1.30 | 6,435 |
| 929292 | 师胜昆 | 男 | 天津 | 1986-9-28 | 2003-6-16 | 3750 | 1.00 | 6,750 |
| 525252 | 郝河 | 男 | 广州 | 1969-5-12 | 2003-6-12 | 3250 | 1.20 | 7,020 |
| 121212 | 艾思迪 | 女 | 北京 | 1966-5-4 | 2003-6-1 | 3250 | 1.20 | 7,020 |
| 232323 | 张祥志 | 男 | 桂林 | 1989-12-3 | 2003-6-18 | 3250 | 1.30 | 7,605 |
| 919191 | 岳凯 | 男 | 南京 | 1977-6-23 | 2003-6-9 | 3250 | 1.30 | 7,605 |
| 828282 | 孙丽星 | 男 | 成都 | 1966-12-5 | 2003-6-15 | 3750 | 1.20 | 8,100 |
| 616161 | 艾利 | 女 | 厦门 | 1980-10-22 | 2003-6-6 | 4750 | 1.00 | 8,550 |
| 818181 | 李克特 | 男 | 广州 | 1988-11-3 | 2003-6-8 | 3750 | 1.30 | 8,775 |
步骤３．单击数据列表内的任意单元格，单击菜单“数据”——单击“筛选”——鼠标指向“高级筛选”并单击它，出现“高级筛选”对话框。
步骤４．在“高级筛选”对话框内，将光标定位到“条件区域”的引用编辑框内，鼠标选中条件区域Ａ1：B2。
步骤５．“方式”选项按钮选择“将筛选结果复制到其他位置”，在“复制到”的引用编辑框内输入Ａ２６，也就是将高级筛选结果复制到的区域。
步骤６．单击“确定”按钮完成。
３．两列之间运用“关系或”条件
以上面的图所示的数据列表为例，运用“高级筛选”功能将“性别”为“男”或“绩效系数”为“１．００”的数据筛选出来，并复制到数据列表的下方Ａ２６所在的区域中，请参照两列之间运用“关系与”条件的步骤，只是设置条件区域的结构和范围略有不同。
Excel根据以下规则解释“高级筛选”条件区域中的条件：
同一行中的条件之间的关系是“与”。
不同行中的条件之间的关系是“或”。
条件区域中的空白单元格表示筛选出该列的所有数据。
４．在一列中使用３个“关系或”条件
以图所示的数据列表为例，运用“高级筛选”功能将“姓名”中，姓氏为“师”、“李”、“郝”的人员记录筛选出来，这时，应将“姓名”标题列入条件区域，并在标题下面的３行中输入“师”、“李”、郝。
５．同时使用“关系与”和“关系或”
要对图所示的数据列表同时使用“关系与”和“关系或”的高级筛选条件，即显示“顾客”为“天津大宇”、“宠物垫”产品的“销售额总计”大于１００的记录；或者显示“顾客”为“上海嘉华”、“雨伞”产品的“销售额总计”小于４００的记录；或者显示“顾客”为“南京万通”的所有记录，可以参照图进行设置。
６．高级筛选中通配符的运用
数据列表高级筛选的功能运用中，使用文本条件可以使用通配符。
星号表示可以与任意多的字符相匹配。
问号表示只能与单个的字符相匹配。
文本条件的实例
|
条件设置
|
筛选效果
|
|
=“=天津”
|
文本中只等于“天津”字符的所有记录
|
|
天
|
以“天”开头的所有文本的记录
|
|
<>D*
|
包含除了字符D开头的任何文本的记录
|
|
>=M
|
包含以M至Z字符开头的文本的记录
|
|
*天*
|
文本中包含“天”字符的记录
|
|
Ch*
|
以Ch开头并包含e的文本记录
|
|
C*e
|
包含以C开头，以e结尾的文本记录
|
|
=”=C*e”
|
包含以C开头，以e结尾的文本记录
|
|
C?e
|
第1个字符是C，第3个字符是e的文本记录
|
|
=”=a?e”
|
长度为3，并以字符C开头，以字符e结尾的文本记录
|
|
<>*f
|
包含不以字符f结尾的文本的记录
|
|
=”=???”
|
包含3个字符的记录
|
|
<>????
|
不包含4个字符的记录
|
|
<>*w*
|
不包含字符W的记录
|
|
~?
|
以？号开头的文本记录
|
|
*~?*
|
包含？号的文本记录
|
|
~*
|
以*号开头的文本记录
|
|
=
|
记录为空
|
|
<>
|
任何非空记录
|
７．使用计算条件
所谓“计算条件”指的是不仅将某列的值与某个常数进行简单比较的条件，而且该条件还必须在数据列表中参与测试，筛选出符合条件的数据。使用计算条件可以使高级筛选功能更强大。图展示了一个运用计算条件进行高级筛选的例子。
| FALSE | FALSE | 1 | ||||||
| 顾客 | 身份证 | 产品 | 总计 | |||||
| 北京高洁 | 360320198105121511 | Good*Eats | 302 | |||||
| 天津刘坤 | 306320198009201512 | GokS | 530 | |||||
| 上海花花 | 325156198202251511 | Good*Treats | 223 | |||||
| 天津杨鑫豪 | 360320198005121000 | GBIES | 363 | |||||
| 南京肖炜 | 306320198010201512 | Cookies | 478 | 筛选结果： | ||||
| 四川宋炜 | 360320198405121511 | Milk | 191 | |||||
| 杭州张林波 | 360320198705121511 | Bread | 684 | 顾客 | 身份证 | 产品 | 总计 | |
| 重庆李冉 | 306320198003201512 | GdS | 614 | 天津刘坤 | 306320198009201512 | GokS | 530 | |
| 北京高洁 | 360320198703251511 | Good*Eats | 380 | 天津杨鑫豪 | 360320198005121000 | GBIES | 363 | |
| 北京高洁 | 360320198605121511 | Bread | 120 | 天津毕春艳 | 306320198009101512 | Gookies | 48 | |
| 上海花花 | 360320198505121511 | Milk | 174 | 天津毕春艳 | 306320198009101512 | Gookies | 715 | |
| 天津毕春艳 | 306320198009101512 | Gookies | 48 | 天津刘坤 | 306320198009201512 | GokS | 561 | |
| 天津毕春艳 | 306320198009101512 | Gookies | 715 | 天津刘坤 | 306320198009201512 | GokS | 746 | |
| 天津刘坤 | 306320198009201512 | GokS | 561 | 天津杨鑫豪 | 360320198005121000 | GBIES | 275 | |
| 天津杨鑫豪 | 360320198005121000 | Cake | 468 | |||||
| 天津刘坤 | 306320198009201512 | GokS | 746 | |||||
| 上海朱殷 | 360320198311211511 | GIRDS | 752 | |||||
| 上海花花 | 360320198221121511 | Produce | 399 | |||||
| 天津杨鑫豪 | 360320198005121000 | Cookies | 746 | |||||
| 上海花花 | 360320198110121511 | Coke | 903 | |||||
| 天津杨鑫豪 | 360320198005121000 | GBIES | 275 | |||||
| 上海花花 | 306320198311211512 | Bread | 277 | |||||
| 天津毕春艳 | 306320198009101512 | Milk | 734 |
要求在数据列表中将“顾客”列中含有“天津”的，在１９８０年出生且“产品”列中第一个字母为Ｇ最后一个字母为Ｓ的产品数据筛选出来，在其他区域显示。
单元格Ａ２包含下面的公式：
＝ISNUMBER（FIND（“天津”，A5））也可以写成=ISNUMBER（FIND（“天津”，（顾客）））
公式通过在“客户”列中寻找“天津”并做出数值判断。
单元格Ｂ２包含下面公式：
＝MID（B5，7，4）=“1980”
公式通过在“身份证”列中第7个字符开始截取4位字符来判断“1980”的存在。
单元格Ｃ２包含下面的公式：
=COUNTIF（C5，“G*S”）也可以写成=COUNTIF（（产品），“G*S”）
公式通过在“产品”列中对包含“Ｇ*S”即第一个字母为G最后一个字母为S的产品计数，来判断是否第一个字母为G最后一个字母为S。
注意：条件区域是Ａ１：Ｃ２，没有使用数据列表中的标题，而是使用空白标题。Excel高级筛选功能在使用计算条件时允许使用空白字段或创建一个新的字段标题，而不允许使用与数据列表中同名的字段标题。
使用计算条件时要注意以下两点：
１）使用数据列表中首行数据来创建计算条件的公式，数据引用要使用相对引用而不能使用绝对引用。
２）如果计算公式引用到数据列表外的同一单元格的数据，公式中要使用绝对引用而不能使用相对引用。
８．利用高级筛选选择不重复的记录
“高级筛选”对话框中的“选择不重复的记录”选项对已经指定的筛选区域又附加了新的筛选条件，它将删除重复的行。面对数据量较大的重复数据时，使用高级筛选的“选择不重复的记录”功能无疑是最佳的选择。
如图所示的数据列表中存在着大量的重复数据。
使用高级筛选的“选择不重复的记录”功能，可以快速将重复的筛除，并将符合筛选条件且不重复的记录复制到指定的区域，可以参照以下步骤来实现。
| 部门名称 | 姓名 | 考勤日期 | 星期 | 实出勤 | 加班小时 | 刷卡时间 |
| 一厂充绒 | 王海霞 | 2006-06-29 | 四 | 8 | 3 | 07:32,19:46 |
| 一厂充绒 | 王海霞 | 2006-06-29 | 四 | 8 | 3 | 07:32,19:46 |
| 一厂充绒 | 王焕军 | 2006-06-29 | 四 | 8 | 3 | 06:56,19:52 |
| 一厂充绒 | 王焕军 | 2006-06-29 | 四 | 8 | 3 | 06:56,19:52 |
| 一厂充绒 | 王焕军 | 2006-06-29 | 四 | 8 | 3 | 06:56,19:52 |
| 一厂充绒 | 王焕军 | 2006-06-29 | 四 | 8 | 3 | 06:56,19:52 |
| 一厂充绒 | 王利娜 | 2006-06-29 | 四 | 8 | 3 | 07:32,19:45 |
| 一厂充绒 | 王利娜 | 2006-06-29 | 四 | 8 | 3 | 07:32,19:45 |
| 一厂充绒 | 王利娜 | 2006-06-29 | 四 | 8 | 3 | 07:32,19:45 |
| 一厂充绒 | 王利娜 | 2006-06-29 | 四 | 8 | 3 | 07:32,19:45 |
| 一厂充绒 | 王瑞霞 | 2006-06-29 | 四 | 8 | 3 | 07:26,19:58 |
| 一厂充绒 | 王瑞霞 | 2006-06-29 | 四 | 8 | 3 | 07:26,19:58 |
| 一厂充绒 | 王瑞霞 | 2006-06-29 | 四 | 8 | 3 | 07:26,19:58 |
| 一厂充绒 | 王瑞霞 | 2006-06-29 | 四 | 8 | 3 | 07:26,19:58 |
| 一厂充绒 | 王闪闪 | 2006-06-29 | 四 | 8 | 3 | 07:47,19:47 |
| 一厂充绒 | 王闪闪 | 2006-06-29 | 四 | 8 | 3 | 07:47,19:47 |
| 一厂充绒 | 王淑香 | 2006-06-29 | 四 | 8 | 3 | 07:54,20:01 |
| 一厂充绒 | 王淑香 | 2006-06-29 | 四 | 8 | 3 | 07:54,20:01 |
| 一厂充绒 | 王淑香 | 2006-06-29 | 四 | 8 | 3 | 07:54,20:01 |
| 一厂充绒 | 王淑香 | 2006-06-29 | 四 | 8 | 3 | 07:54,20:01 |
| 一厂充绒 | 王文丽 | 2006-06-29 | 四 | 8 | 3 | 07:45,19:46 |
步骤１．单击数据列表中的任意单元格。
步骤２．单击菜单“数据”——单击“筛选”——鼠标指向“高级筛选”并单击它。
步骤３．在弹出的“高级筛选”对话框中勾选“选择不重复的记录”复选框。
步骤４．“方式”选择“将筛选结果复制到其他位置”，将光标定位到“复制到”引用编辑框内，单击Ａ101单元格。
步骤５．单击“确定’按钮完成。
２６．６创建分类汇总
分类汇总是Excel中最常用的功能之一，它能够快速地以某一个字段为分类项，对数据列表中的数值字段进行各种统计计算，如求和、计数、平均值、最大值、最小值、乘积等。
２６．６．１ 创建简单的分类汇总
以图所示的表格为例，如果希望在数据列表中计算每个科目名称的费用发生额合计，可以参照以下步骤。
| 月 | 日 | 凭证号数 | 科目编号 | 科目名称 | 摘要 | 借方 |
| 04 | 21 | 现-0105 | 550116 | 办公费 | 文具 | 207.00 |
| 04 | 30 | 现-0130 | 550116 | 办公费 | 护照费 | 1,000.00 |
| 04 | 30 | 现-0152 | 550116 | 办公费 | ARP用C盘 | 140.00 |
| 03 | 27 | 现-0169 | 550116 | 办公费 | 打印纸 | 85.00 |
| 办公费 汇总 | 1,432.00 | |||||
| 办公费 平均值 | 358.00 | |||||
| 办公费 平均值 | 358.00 | |||||
| 04 | 04 | 现-0032 | 550102 | 差旅费 | 差旅费 | 3,593.26 |
| 03 | 06 | 现-0037 | 550102 | 差旅费 | 差旅费 | 474.00 |
| 05 | 23 | 现-0087 | 550102 | 差旅费 | 差旅费 | 26,254.00 |
| 05 | 23 | 现-0088 | 550102 | 差旅费 | 差旅费 | 3,510.00 |
| 05 | 23 | 现-0088 | 550102 | 差旅费 | 差旅费 | 5,280.00 |
| 05 | 23 | 现-0088 | 550102 | 差旅费 | 差旅费 | 282.00 |
| 差旅费 汇总 | 39,393.26 | |||||
| 差旅费 平均值 | 6,565.54 | |||||
| 差旅费 平均值 | 6,565.54 | |||||
| 04 | 30 | 现-0141 | 550123 | 交通工具费 | 出租车费 | 35.00 |
| 01 | 30 | 现-0149 | 550123 | 交通工具费 | 出租车费 | 18.00 |
| 01 | 30 | 现-0149 | 550123 | 交通工具费 | 出租车费 | 186.00 |
| 01 | 30 | 现-0158 | 550123 | 交通工具费 | 出租车费 | 10.00 |
| 01 | 30 | 现-0160 | 550123 | 交通工具费 | 出租车费 | 15.00 |
| 03 | 27 | 现-0163 | 550123 | 交通工具费 | 出租车费 | 43.50 |
| 交通工具费 汇总 | 307.50 | |||||
| 交通工具费 平均值 | 51.25 | |||||
| 交通工具费 平均值 | 51.25 |
注意：使用分类汇总功能以前，必须要对数据列表中需要分类汇总的字段进行排序，图所示的数据列表已经对“科目名称”字段排序。
步骤１．单击数据列表中的任意单元格。
步骤２．单击菜单“数据”——单击“分类汇总”，打开“分类汇总”对话框，在“分类字段”的下拉列表中选择“科目名称”，“汇总方式”下拉列表中选择“求和”，“选定汇总项”下拉列表中勾选“借方”项，并勾选“汇总结果显示在数据下方”复选框。
步骤３．单击“确定”按钮后Excel会分析数据列表，运用SUBTOTAL函数插入指定的公式。
２６．６．２ 对某列字段使用一个以上的分类汇总方式
如果用户在图所示的数据列表中还要求知道每个“科目名称”的费用平均值，可以参照以下操作。
步骤１．单击分类汇总求和后的数据列表中的任意单元格。
步骤２．单击菜单“数据”——单击“分类汇总”，打开“分类汇总”对话框，在“汇总方式”下拉列表中选择“平均值”，取消“替换当前分类汇总”的勾选。
步骤３．单击“确定”按钮完成。
２６．６．３ 使用自动分布符
如果用户想将分类汇总后的数据列表按汇总项打印出来，使用“分类汇总”对话框中的“每组数据分页”选项，会使这一过程变得非常容易。当勾选了“每组数据分页”的复选框后，Excel就可以将每组数据单独打印在一页上。
２６．６．４ 取消或替换当前的分类汇总
如果用户想取消分类汇总，只需打开“分类汇总”对话框，单击“全部删除”按钮即可。如果想替换当前的分类汇总，则要在“分类汇总”对话框中勾选“替换当前分类汇总”复选框。
２６．7 Excel列表功能
Excel列表功能是Excel ２００３中新增加的特性。列表可以自动扩展列表区域；可以排序、筛选；可以自动求和、极值、平均值等又不用输入任何公式，只需选择一下，可以随时转换为区域。极大地方便了用户管理和分析Excel工作表中的多组相关数据。
将某一区域指定为列表后，用户可方便地管理和分析列表中的数据而不必理会列表之外的其他数据。例如，只使用列表中所包含的数据
您可筛选列、甚至只使用列表中包含的数据创建数据透视表。］
用户可以在数据列表中设置多个列表，从而可以更加灵活地根据需要将数据划分为易于管理的不同数据集。］
２６．７．１创建列表
要创建如图所示的列表，可以参照以下步骤。
步骤１．单击数据列表中的任意一个单元格，单击菜单“数据”——单击“列表”——鼠标指向“创建列表”并单击它，出现“创建列表”对话框。
步骤２．单击“确定”按钮完成对列表的创建，现在的列表被一个蓝色的边框所包围，用户可以清楚地看到列表的轮廓。
步骤３．选中列表中的单元格时，会自动显示“列表”工具栏，单击“列表”工具栏的“切换汇总行”，可以为列表中的“订单金额”添加汇总函数。
要将列表转换为标准区域，可以在菜单栏上依次单击“数据”——“列表”——“转换为区域”命令。
注意：Excel无法在已经设置共享的工作簿中创建列表。若要创建列表，必须先撤销该工作簿的共享。
２６．７．２ 在列表中添加数据
列表的最后一行包含一个带星号的空行，用户只要在这个空行中输入数据，就可以向列表中添加数据行了。要想在列表中插入行或列，可以在列表中单击鼠标右键——插入——行或列。要想在列表中删除行或列，同样在列表中单击鼠标右键——删除——行或列。
２６．７．３ 在列表中添加汇总函数
要想在指定的列表中添加汇总函数，可以单击列表中的汇总行，汇总行的单元格钭显示下拉列表，用户可以自己选择汇总的类型，如求和、平均值、计数等。
２６．７．４ 设计列表
有Excel中创建列表时，使用列表中的功能及设计，可以更方便地识别和修改列表中的内容。
默认情况下，标题行为列表中的所有列启用自动筛选功能。自动筛选允许用户快速筛选、排序数据。
列表周围的深蓝色边框清晰地区分出组成列表的单元格区域。
包含星号的行称为扩展行。在此行中输入的数据将自动添加到列表中并扩展列表的边框，本行将自动继承上一行的数据格式和公式。
可以为列表添加汇总行。单击汇总行中的单元格时，将显示汇总函数下拉列表。
通过拖动列表边框右下角的调整手柄，可调整列表大小。
２６．７．５ 列表与SharePoint服务器
如果用户使用微软的SharePoint服务，可以把Excel列表发布到SharePoint站点上，还可以时刻保持本地和服务器数据的同步。选择“数据”——“列表”——“发布列表”命令，即可将Excel列表发布到SharePoint站点上。
列表可以与Windows SharePoint Services中的列表兼容，列表发布到SharePoint网站后，用户可以创建一个自定义SharePoint列表。如果发布列表或导出现有SharePoint列表时选择链接列表，则可先脱机编辑该列表，稍后再将更改同步到SharePoint列表。
通过与Microsoft Windows SharePoint Services进行集成，使用Excel列表与其他用户共享数据变得更加容易。只要拥有SharePoint网站的网站地址和创作权限，用户就可共享列表以供其他人员查看、编辑和更新该列表。如果选择将Excel中的列表链接到SharePoint网站上的列表，则可与SharePoint网站同步更改以便其他用户查看更新数据。
２６．７．６ 用列表定义动态名称
一提到动态的名称，多数用户想到的是利用offset+counta函数组合来定义动态的名称。然而，列表的一个重要的功能就是在包含星号的行插入数据后，整个列表将向下自动扩展。运用“列表”的这个功能，在定义名称时选择整个列表区域，随着列表本身的拓展，定义的名称引用区域也随着拓展，从而定义的名称也就变为动态的名称了。
以图所示的列表为例，运用列表定义名称可以参照以下步骤。
步骤１．选择列表区域，但不包括含有星号的行。
步骤２．单击菜单“插入”——名称——鼠标指向“指定”并单击它，出现“指定名称”对话框。
步骤３．在“指定名称”对话框取消“最左列”复选框的勾选，单击“确定”按钮完成。
转载请注明：数据分析 » 在数据列表中简单分析数据_Excel 数据分析师
