Question

excel中如何查找某一区域数据中缺少的自然数？

如M、P、S、V列中，有随机混乱排列的自然数1-3000，如何查找其中缺失的自然数？

或如何将其中的数据提取到另一列（如Y列）并按顺序排列？

Answer 1

若那些数字在M1：M20中，后面的PSV列也是按20行算，任意单元格中输入
=SMALL(IF(MMULT(COUNTIF(OFFSET(M$1:M$20,,{0,3,6,9}),ROW($1:$3000)),{1;1;1;1})=0,ROW($1:$3000)),ROW(A1))
同时按下CTRL+SHIFT+回车，输入数组公式，用自动填充柄将这个公式下拉，就行了。
如果不是20行，请将公式中的M$1:M$20相应修改。
如果区域中夹杂的其它列中不会出现数字，公式还可以相应简化。

追问

大神，膜拜！
但是还是想问一下，如果区域中夹杂的其它列中不会出现数字，公式应如何简化？

Answer 2

1. 先把数据调整为一列，如A列。

2. 升序排列

3. 在B1列输入公式：=A2-A1-1

4. B列出来的数据表示前后缺少的数据个数。
   

Answer 3

可用以下方法：
一、用“分列”功能。选中需要分列的单元格，点菜单“数据”——“分列”，根据实际情况选择“分隔符号”或“固定宽度”分列——“下一步”，在“其他”中输入分隔符号或点标尺将数据切割为4段，在“目标区域”输入存放目标数据第一个数的单元格区域地址，最后按“完成”。
二、用公式函数。可以用mid函数处理。
示例：假定原数据为“12-34-56-78”形式的数据，要求将按“-”分隔的数据分拆到后面的4个单元格。=trim(mid(substitute($a1,"-",rept(" ",99)),(column(a1)-1)*99+1,99)) 右拉填充。

追问

大哥，是我没说清楚，还是你没看懂题。
这几列中本列已经是自然数了，不需要再分列了，而是要查找其中缺少的数值。