论文部分内容阅读
〔关键词〕 计算机教学；Excel;数据表；组合框；数组 公式；联动查询
〔中图分类号〕 G434〔文献标识码〕 C
〔文章编号〕 1004—0463（2011）07（B）—00９0—０２
MicrosoftExcel是一款集成了最优秀的数据计算与分析功能的软件，使用它用户完全可以按照自己的思路来创建电子表格，并进行各种数据处理和统计分析等操作。本文以“某单位人员工资表”为例，使用Excel中的组合框、数组公式、函数、多维引用来实现多表联动查询功能。
一、建立“原始数据表”
在进行联动查询前应保证有一张工作表中记录了需查询的原始数据，并将此工作表命名为“数据源表”，图1所示数据信息为本例所建立的数据源表。
二、设计“分析查询表”样式
用户可以根据自己的查询需要在一张新的工作表中设计“分析查询表样式”，本例使用了提示文字和组合框来设计分析查询表，设计好的表样式参见“图2-分析查询表样式”。
1.组合框的工作原理
在图2所示的分析查询表中，如果选定D2单元格，就会在单元格右侧出现一个倒三角箭头，单击箭头，会出现如图2所示的下拉列表，单击其中任何一项，该项的内容就被输入到D2单元格中。
2.组合框的实现方法
首先选定D2单元格，单击菜单“数据”→“有效性”，在“数据有效性”对话框的“设置”选项卡中，在“允许”下拉列表框中选择“序列”项，在“来源”框中输入‘工程部、设计室、后勤部’，注意项目与项目之间以半角的逗号进行分隔。
三、多表联动查询功能的实现
1.设计思路解析
首先选定D2单元格，并单击D2单元格右侧的倒三角箭头，在出现的下拉列表中选择任意一项，本例选择的是“后勤部”， 部门一经选择，就会从“数据源表”中抽取所有 “后勤部”人员的基本信息显示在“分析查询表”下方设计的“各部门工资统计表”中；除显示人员基本信息外，还会在E2单元格中显示该部门的人数统计信息，联动查询结果参见“图3-联动查询功能实现”所示。
2.多表联动查询功能实现方法解析
在Excel中普通公式是返回单个数值，即使在引用时引用了多个数值。而数组公式是一组或多组值进行多重计算，并且返回一个或多个结果。数组公式需要扩于花括号{}中，而且这个花括号手动添加无效，它是在输入公式后按组合键自动输入的。本例中抽取数据源表中人员信息并进行显示就是使用了数组公式来实现的。
（1）从“数据源表”中抽取人员信息的实现方法
抽取人员信息参见“图3联动查询功能实现”中A6∶G12单元格显示内容。
数组公式：
在A6单元格中输入{=IF($D$2=“”,数据源!A3&“”,INDEX(数据源!A∶A,SMALL(IF(数据源!$B$3∶$B$14=$D$2,ROW(数据源!A$3∶A$14),999),ROW(1∶1)))&“”) }数组公式，使用填充句柄为其它单元格进行公式填充。注意：数组公式两边的花括号{}是在输入公式后按组合键自动输入的。
公式解析如图４。
① 如果D2单元格为空，则将“数据源表”中从A3到G14单元格的内容全部显示；否则进行第②部分函数计算。
② 返回“数据源表”中第A列及由第③部分函数计算得出的行的值。（注：列值A∶A会随数据填充而变化）
③ 从第④部分函数计算得出的单元格的行号中获取第n个最小值,n值随函数ROW(1∶1)发生变化，其中ROW(1∶1)函数用于取得第1行的行号。（注：ROW(1∶1)函数中的数字会随数据填充后发生变化，例，ROW(2∶2)、ROW(3∶3) 其作用是分别取第2、第3行的行号）
④ 使用“分析统计表”中D2单元格中的值与“数据源表”中部门列（B3～B14）进行条件判断，若相等，返回相应单元格的行号，否则，返回数字999。
⑤ 返回“数据源表”中A$3∶A$14单元格区域的行号。（注：A$3∶$14列号会随数据填充而变化）
（２）对抽取的信息进行人数统计的实现方法
显示内容参见图3 “联动查询功能实现”中E2单元格显示的内容。在E2单元格显示的部门信息是根据组合框选定的内容进行变化的。本例D2组合框选择的是“后勤部”所以E2单元格显示的是“后勤部”人员信息的统计情况。
公式：
在E2单元格，输入公式=IF(D2=“”,“”,CONCATENATE(D2,“共有”,COUNTIF(B6∶B11,D2),“人”))。
公式解析：
① 如果D2单元格中显示的内容为空，则E2单元格内容也为空；否则将进行后续第②部分的函数计算。
② 将D2单元格中显示的内容与“共有”两个字以及第③部分统计出的单元格个数和“人”字合并为一个文本字符串。
③ 在B6∶B11数据区域中统计与D2这一条件单元格内容相同的单元格个数。
Excel中大量的公式函数可以实现许多方便的功能，给使用者工作带来方便。不过Excel所提供的函数单独使用难度不大，但我们把这些有限的功能不断的排列组合以后就会创造出很多新的用法，所以我们应该灵活地运用Excel各种公式、函数以解决复杂的数据分析和管理问题，让Excel真正成为我们工作得心应手的工具。
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文
〔中图分类号〕 G434〔文献标识码〕 C
〔文章编号〕 1004—0463（2011）07（B）—00９0—０２
MicrosoftExcel是一款集成了最优秀的数据计算与分析功能的软件，使用它用户完全可以按照自己的思路来创建电子表格，并进行各种数据处理和统计分析等操作。本文以“某单位人员工资表”为例，使用Excel中的组合框、数组公式、函数、多维引用来实现多表联动查询功能。
一、建立“原始数据表”
在进行联动查询前应保证有一张工作表中记录了需查询的原始数据，并将此工作表命名为“数据源表”，图1所示数据信息为本例所建立的数据源表。
二、设计“分析查询表”样式
用户可以根据自己的查询需要在一张新的工作表中设计“分析查询表样式”，本例使用了提示文字和组合框来设计分析查询表，设计好的表样式参见“图2-分析查询表样式”。
1.组合框的工作原理
在图2所示的分析查询表中，如果选定D2单元格，就会在单元格右侧出现一个倒三角箭头，单击箭头，会出现如图2所示的下拉列表，单击其中任何一项，该项的内容就被输入到D2单元格中。
2.组合框的实现方法
首先选定D2单元格，单击菜单“数据”→“有效性”，在“数据有效性”对话框的“设置”选项卡中，在“允许”下拉列表框中选择“序列”项，在“来源”框中输入‘工程部、设计室、后勤部’，注意项目与项目之间以半角的逗号进行分隔。
三、多表联动查询功能的实现
1.设计思路解析
首先选定D2单元格，并单击D2单元格右侧的倒三角箭头，在出现的下拉列表中选择任意一项，本例选择的是“后勤部”， 部门一经选择，就会从“数据源表”中抽取所有 “后勤部”人员的基本信息显示在“分析查询表”下方设计的“各部门工资统计表”中；除显示人员基本信息外，还会在E2单元格中显示该部门的人数统计信息，联动查询结果参见“图3-联动查询功能实现”所示。
2.多表联动查询功能实现方法解析
在Excel中普通公式是返回单个数值，即使在引用时引用了多个数值。而数组公式是一组或多组值进行多重计算，并且返回一个或多个结果。数组公式需要扩于花括号{}中，而且这个花括号手动添加无效，它是在输入公式后按
（1）从“数据源表”中抽取人员信息的实现方法
抽取人员信息参见“图3联动查询功能实现”中A6∶G12单元格显示内容。
数组公式：
在A6单元格中输入{=IF($D$2=“”,数据源!A3&“”,INDEX(数据源!A∶A,SMALL(IF(数据源!$B$3∶$B$14=$D$2,ROW(数据源!A$3∶A$14),999),ROW(1∶1)))&“”) }数组公式，使用填充句柄为其它单元格进行公式填充。注意：数组公式两边的花括号{}是在输入公式后按
公式解析如图４。
① 如果D2单元格为空，则将“数据源表”中从A3到G14单元格的内容全部显示；否则进行第②部分函数计算。
② 返回“数据源表”中第A列及由第③部分函数计算得出的行的值。（注：列值A∶A会随数据填充而变化）
③ 从第④部分函数计算得出的单元格的行号中获取第n个最小值,n值随函数ROW(1∶1)发生变化，其中ROW(1∶1)函数用于取得第1行的行号。（注：ROW(1∶1)函数中的数字会随数据填充后发生变化，例，ROW(2∶2)、ROW(3∶3) 其作用是分别取第2、第3行的行号）
④ 使用“分析统计表”中D2单元格中的值与“数据源表”中部门列（B3～B14）进行条件判断，若相等，返回相应单元格的行号，否则，返回数字999。
⑤ 返回“数据源表”中A$3∶A$14单元格区域的行号。（注：A$3∶$14列号会随数据填充而变化）
（２）对抽取的信息进行人数统计的实现方法
显示内容参见图3 “联动查询功能实现”中E2单元格显示的内容。在E2单元格显示的部门信息是根据组合框选定的内容进行变化的。本例D2组合框选择的是“后勤部”所以E2单元格显示的是“后勤部”人员信息的统计情况。
公式：
在E2单元格，输入公式=IF(D2=“”,“”,CONCATENATE(D2,“共有”,COUNTIF(B6∶B11,D2),“人”))。
公式解析：
① 如果D2单元格中显示的内容为空，则E2单元格内容也为空；否则将进行后续第②部分的函数计算。
② 将D2单元格中显示的内容与“共有”两个字以及第③部分统计出的单元格个数和“人”字合并为一个文本字符串。
③ 在B6∶B11数据区域中统计与D2这一条件单元格内容相同的单元格个数。
Excel中大量的公式函数可以实现许多方便的功能，给使用者工作带来方便。不过Excel所提供的函数单独使用难度不大，但我们把这些有限的功能不断的排列组合以后就会创造出很多新的用法，所以我们应该灵活地运用Excel各种公式、函数以解决复杂的数据分析和管理问题，让Excel真正成为我们工作得心应手的工具。
注：本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文