TOCOI+DROP+FILTER+COUNTIF筛选只住一个人的学生宿舍
- 场景再现
在 Excel表中有30多列数据，以班级为字段名，以每列数据以：楼号+教室号+姓名命名，现在学校盘查宿舍，要求找出只有一个人住的宿舍。我们用TOCOI+DROP+FILTER+COUNTIF分步求出。
- 解决办法
2.1 TOCOL+DROP列数据转行数据
为了方便计算，我们需要把sheet1中的所有数据转为一列，起点为A列A2单元格，终点为AM列最后一个非空单元格。在Sheet2的A1单元格应用下面公式：
| 💡 | =TOCOL(DROP(Sheet1!A:AM,1),1) |
TOCOL(Sheet1!A:Z,1) 的作用是将 Sheet1 中 A 到 AM列的所有非空单元格转换为一列。如果你想让它从第二行开始（即忽略第一行），可以使用DROP函数：TOCOL(DROP(Sheet1!A:Z,1),1)，其功能是：将 A 到AM列的整个范围作为数组，并删除前 1 行。得到以下数据。
2.2 FILTER+COUNTIF过滤出只有一个人的宿舍
接下来用FILTER进行筛选，条件是用COUNTIF计算宿舍号（每个单元格前5位）出现次数为1的行记录使用下面公式：
| 💡 | =FILTER(A:A,COUNTIF(A:A,LEFT(A:A,5)&"*")=1) |
LEFT(A:A,5)&"*" 表示在提取的前5位字符后面加上通配符 *，即以宿舍号为条件来计算相同宿舍号的单元格的数量，当数量为1时返回的是True，就可以通过FILTER筛选出来。
2.3 公式合并
还可以用LET设置变量X,整合2.1和2.2的两个公式，实现一键求出所有符合要求的数据。公式如下：
| 💡 | =LET( x, TOCOL(DROP(Sheet1!A:AM,1),1), FILTER(x, COUNTIF(x, LEFT(x,5) & "*") = 1) ) |
x：存储从Sheet1的A:AM列提取的所有非空单元格内容；
LEFT(x,5) & "*"：取x中每个元素的前5位作为筛选条件；
COUNTIF(x, ...) = 1：判断x中是否有且仅有1个条目以前5位开头；
FILTER(x, ...)：筛选出符合条件的唯一值。
这样整合后，一行公式就能直接处理x变量生成的数据了。
- 写在最后
本次案例通过将多列数据转换为单列，再利用宿舍号（前5位字符）的出现次数，筛选出唯一出现的记录，从而找出只有一个人住的宿舍。最后通过LET可以把前一公式的结果引用到下一公式中，进一步简化了计算流程，逻辑变得更加清晰。
如果想筛选出有2个人的宿舍就可把COUNTIF中最后的数字1改为2即可，这样不仅可以查到宿舍号，还可以查到宿舍里住的人，为后期调整宿舍人数提供了便利。
WPS寻令官