如何用Excel创建学位授予院校综合就业评分并筛选TOP25？

一、先过滤掉非目标院校数据 #

首先剔除不在100所大学列表内的记录，确保后续计算只针对目标范围：

方法1：用FILTER函数（Excel 365/2021及以上版本） #

假设：

• 原始数据在Sheet1的A-D列（A=学位授予院校，B=招聘院校，C=大学，D=排名）
• 100所大学的名单存在Sheet2的A2:A101区域

在新工作表（比如Sheet3）的A1单元格输入以下公式，直接提取符合条件的记录：

=FILTER(Sheet1!A:D, ISNUMBER(MATCH(Sheet1!C:C, Sheet2!A:A, 0)))

如果需要同时限定学位授予院校也属于100所大学列表，把公式改成：

=FILTER(Sheet1!A:D, ISNUMBER(MATCH(Sheet1!A:A, Sheet2!A:A, 0)) * ISNUMBER(MATCH(Sheet1!C:C, Sheet2!A:A, 0)))

方法2：旧版Excel用高级筛选 #

1. 在Sheet2的空白单元格（比如B1）输入标题「大学」，下方粘贴100所大学名单
2. 回到Sheet1，点击「数据」->「高级」
3. 选择「将筛选结果复制到其他位置」，列表区域选A:D，条件区域选Sheet2的B1:B101，复制到指定空白区域，确定即可完成过滤

二、计算综合就业安置评分 #

根据需求，推荐两种评分逻辑（排名值100为最高，评分越高代表就业质量越好）：

逻辑1：总分（该院校毕业生被顶尖招聘院校录取的排名累加值） #

在Sheet3的E2单元格输入公式，下拉填充到所有行：

=SUMIFS(Sheet3!D:D, Sheet3!A:A, Sheet3!A2)

如果要直接生成唯一院校+对应总分的列表，用以下公式（Excel 365/2021）：

=HSTACK(UNIQUE(Sheet3!A:A), SUMIF(Sheet3!A:A, UNIQUE(Sheet3!A:A), Sheet3!D:D))

逻辑2：平均评分（该院校毕业生被招聘院校的平均排名） #

同理，平均评分公式：

=AVERAGEIFS(Sheet3!D:D, Sheet3!A:A, Sheet3!A2)

生成唯一院校+平均分列表：

=HSTACK(UNIQUE(Sheet3!A:A), AVERAGEIF(Sheet3!A:A, UNIQUE(Sheet3!A:A), Sheet3!D:D))

三、筛选前25名院校 #

方法1：动态筛选（Excel 365/2021） #

直接对上面的总分/平均分列表按评分降序排序，取前25条：

=TAKE(SORT(HSTACK(UNIQUE(Sheet3!A:A), SUMIF(Sheet3!A:A, UNIQUE(Sheet3!A:A), Sheet3!D:D)), 2, -1), 25)

把SUMIF换成AVERAGEIF即可用平均分筛选。

方法2：旧版Excel手动操作 #

1. 把唯一院校列表和对应的评分复制到新区域
2. 选中评分列，点击「数据」->「排序」，选择降序
3. 直接选取前25行数据即可

更简便的替代方案：数据透视表 #

1. 完成第一步的数据过滤后，选中Sheet3的A-D数据区域
2. 点击「插入」->「数据透视表」，放置在新工作表
3. 行区域拖入「学位授予院校」，值区域拖入「排名」，然后修改值字段设置为「求和」或「平均值」
4. 点击数据透视表的「排序」按钮，按求和/平均值降序排列，直接取前25行即可

内容的提问来源于stack exchange，提问作者anon