求excel高手帮忙,数据处理问题,谢谢!!! 100
目标:
1.把几张表上没有用到的编号在另外一张可用编号表单上列出来方便取用
2.其他表单上新增或删减自动更新到可用编号表单上
最好是不要好几列去比较了 展开
2018-05-17 · 知道合伙人软件行家
好在只有几张工作表上,如果多,虽难度不大,但比较繁琐点。
因为没有你的工作簿具体数据说明,现以已有3个工作表,编号1~20为例说明之。自己根据实际数据来参照处理。
如图,在Sheet1~Sheet3中用了一部分编号,Sheet4中列出1~20中尚未使用的编号。
图1,Sheet1~Sheet3中用了一的编号
现在在Sheet4中列出未使用的编号,
A2=IFERROR(SMALL(IF((COUNTIF(Sheet1!A$2:A$20,ROW($1:$20))+COUNTIF(Sheet2!A$2:A$20,ROW($1:$20))+COUNTIF(Sheet3!A$2:A$20,ROW($1:$20)))=0,ROW($1:$20)),ROW(1:1)),"")
同时按Ctrl+Shift+Enter三键输入数组公式,下拉到出现空白单元格。
说明:
公式中各工作表的统计范围可比实际数据适当多些行,为便于编写公式,可按最多行数的工作表书写,复制COUNTIF部分后,只要修改其实的工作表名称,依次修改即可。简单地可以直接用整列,但强烈不建议这么做,这样会增加无谓的资源消耗。
有多少个工作表,就有多少个COUNTIF部分相加。明白原理后,公式并不难理解。用COUNTIF统计1~20在各个工作表中是否用了,用了就得到结果1,没用过的为0,所有的相加后,剩下就在对应位置为0,再small依次取得这些编号。
对于你的贴图,用我提供的公式作出对应的修改就可以啊:
=IFERROR(SMALL(IF((COUNTIF(表单1!A$2:A$500,ROW($1:$500))+COUNTIF(表单2!A$2:A$500,ROW($1:$500)))=0,ROW($1:$500)),ROW(1:1)),"")