Question

excel怎么找出缺少的数字？

学号1-42，E列填写的是已经有的学号，我要通过公式，找到没有的学号
横向在一个空格内显示，比如 21 24 31

行数量大于42，所以会有空格

Answer 1

上图附带行列号，写明源数据，注明条件，需要达到的效果，文字作为辅助说明，图示才是最好的提问内容。

大概猜测你的意思写的，不知道你要的是不是这种，

公式：

=TEXTJOIN(" ",TRUE,IF(COUNTIF($E:$E,ROW(1:42)),"",ROW(1:42)))

数组公式，同时按下Ctrl+Shift+Enter结束输入。

追问

是的是的，但是我出来的是这样的不是横向出来的，请问是什么原因

追答

循环引用了，如果你要在E1输入这个公式，要么在选项里面开启迭代计算，或者在E列里面剔除E1单元格，就是把公式中的$E:$E替换成E$2:$E$100，区域大小自行修改一下就好，只要不包含公式输入单元格即可。

追问

我看这个ROW的函数是（1：42）代表1-42行是吗，但实际我有146行，除了42个学号之外，一定是有空格的，我剔除了E1我显示的还是显示0，我在首行插入了新的一列，在CI的单元格中输入公式

追答

ROW(1:42)是指1、2、3……41、42，这42个数字，你学号不是1:42号嘛，至于你E列有多少行在$E$2:$E$200这里修改就好了。COUNTIF($E:$E,ROW(1:42))这个的意思是在E列里面依次查找1到42这42个数据，并返回他的个数，没有的就返回0，那在这个外面套一个if函数，把有的学号不显示，把结果为0（即没有该学号）的数据给他显示出来。然后用TEXTJOIN函数串联起来。
刚才你的公式还成功了，怎么现在又找不到函数了，你用的是Excel还是WPS表格，Excel需要2016以上版本，不然没有TEXTJOIN这个函数。

追问

目前是2016版本的，我明白你的意思，Excel是这样的，但是WPS就是正常了，不管了，谢谢你

追答

说啦，版本不够，没有TEXTJOIN这个函数，如果你只是用于验证不需要时时刻刻显示这个结果，那么只用这一段公式，IF(COUNTIF($E:$E,ROW(1:42)),"",ROW(1:42))，然后鼠标点到公式编辑栏里面，按下F9，回车，就会出现结果，然后你下次需要的时候再次输入这个公式在按F9就好了。

追问

再次感谢

Answer 2

1
为我们的现有数据，假定是现有的员工情况，我们需要找出哪些工号的资料是缺少。如果一个一个地数将会很浪费时间，而且还可能会数漏。
2
在G列输入连续的工号：先输入GZ001、GZ002，然后选中GZ001和GZ002单元格区域，鼠标光标移至单元格区域右下角，光标变为黑色十字时往下拖拉，得出连续的工号。
3
在H2单元格输入公式=IFERROR(VLOOKUP(G2,$A$2:$A$19,1,FALSE),"")，同样方式往下拖拉公式，得出结果，空白位置的就是缺少的工号资料。

4
IFERROR：如果表达式是一个错误，则返回指定的数据，否则返回表达式自身计算出来的值。
在I2单元格输入公式=IFERROR(VLOOKUP(G2,$A$2:$A$19,1,FALSE),"缺少")，可以看到得出的结果。
5
除了用上面步骤所示两个函数来计算查找，其实也可以直接用VLOOKUP函数。在H2单元格输入公式=VLOOKUP(G2,$A$2:$A$19,1,FALSE)，同样是拖拉得出所有数据，缺少的就会得出错误值#N/A，看这些错误值也可以找到缺少的数据。

Answer 3

F2:

=IF(ISNUMBER(VLOOKUP(E2,A:A,1,0)),"已有学号","没有的学号")

追问

我想要一个横向的查找函数
用公式来显示出没有填写到E列的学号
案例如图

学号是这样填写进去的，也会有空格的，因为行数量大于42
然后我需要在学号的上面一个空格内，罗列出没有填写的学号，例：21 24 。。。。

追答

这样？

Answer 4

在一个单元格显示公式：（需要EXCEL2019以上版本）

=TEXTJOIN(" ",TRUE,IF(COUNTIF(E:E,ROW(1:42))=0,ROW(1:42),""))
数组公式，需要按组合键（CTRL+SHIFT+回车）完成公式；
如果在一行的多个单元格中显示，每个单元格显示一个，公式：
=SMALL(IF(COUNTIF($E:$E,ROW(1:42))=0,ROW(1:42),""),COLUMN(A1))
数组公式，需要按组合键（CTRL+SHIFT+回车）完成公式后，右拉。

追问

我现在输入公式后是这样的

追答

你的EXCEL版本太低，不支持TEXTJOIN函数。
可以用多个单元格显示的公式，在一行中显示。

Answer 5

不清楚你是不是想要这样的结果。

追问

是这个结果，但是我公式和你一样，但是显示的结果不是你这样的

追答

Maybe you need Ctrl+shift+enter

追问

我也是这样操作的，光标放在公式最后，按下Ctrl+shift+enter

但是

是这样的结果

追答

你版本不支持textjoin函数