Keval拥有超过5,000名学生的表格,分布在22个学习课程中。在此表的每一行中,每个学生的姓名上均标有该学生的注册课程。 Keval希望在另一个工作表上,将课程名称放在单元格A1中,然后从列表的第3行开始,以Excel显示该课程中的所有学生。他想知道是否有办法用公式来做到这一点。

解决此问题的最佳方法将在很大程度上取决于源数据的组织方式。如果只有两列(学生姓名和课程名称),则可能甚至不需要使用第二个工作表。相反,您可以使用Excel的筛选功能来获取。只需过滤包含课程名称的列的内容,就可以轻松地将显示内容限制为仅显示在课程中的那些学生。

如果必须使信息出现在第二个工作表上,则最好使用数组公式来提取学生。将所需的路线放入单元格A1中,然后将以下公式放入单元格A3中:

=IF(COUNTIF(Sheet1!$B$1:$B$5000,$A$1)<ROW()-2,"", INDEX(Sheet1!$A$1:$A$5000,SMALL(IF(Sheet1!$B$1:$B$5000=$A$1, ROW(Sheet1!$B$1:$B$5000)),ROW()-2)))

请记住,这是一个公式,您需要通过按Ctrl + Shift + Enter在单元格中输入它。将公式向下复制到足够容纳您最大类别的单元格中。该公式还假定源数据在第1到5000行中;如果不是这种情况,则需要修改公式以反映适当的范围。

另一种方法是根据学生/课程列表创建数据透视表。您需要做的就是确保课程名称和学生姓名字段都在数据透视表的“行”区域中。将课程名称字段放在第一位,将学生姓名字段放在第二位,最后将得到所有课程的列表,其中每个课程名称下都有学生。

如果您的源数据具有与其相关的其他信息(例如成绩,地址,书籍分配等),那么您可能需要考虑使用实际的数据库程序,例如Access。您将能够使用优于Excel中的工具来操纵和提取数据。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(12347)适用于Microsoft Excel 2007和2010。您可以在下面的Excel的较旧菜单界面中找到此技巧的版本: