在EXCEL表格中，如何用公式在一行数据（大于10列）中拉出0-9中没有的数值（如图实例）？

Question

如图所示：求公式，在N1中找出A1到M1数据中“0-9”中没有的数值为05679；然后使用公式下拉能得到N2-N8的数值。如图谢谢。

Answer 1

N1输入

=CONCAT(IF(ISERROR(MATCH({0,1,2,3,4,5,6,7,8,9},A1:M1,0)>0),{0,1,2,3,4,5,6,7,8,9},""))

Ctrl+Shift+回车

下拉

Answer 2

你好，你要的公式如下

N1=CONCAT(IF(ISNUMBER(MATCH(ROW($1:$10)-1,A1:M1,0)),"",ROW($1:$10)-1))

公式是涉及数组，输完公式后 用ctrl+shift+enter三键结束

请及时采纳答案！

Answer 3

=IF(COUNTIF(A1:M1,0)=0,0,"")

&IF(COUNTIF(A1:M1,1)=1,0,"")
&IF(COUNTIF(A1:M1,2)=2,0,"")
&IF(COUNTIF(A1:M1,3)=3,0,"")
&IF(COUNTIF(A1:M1,4)=4,0,"")
&IF(COUNTIF(A1:M1,5)=5,0,"")
&IF(COUNTIF(A1:M1,6)=6,0,"")
&IF(COUNTIF(A1:M1,7)=7,0,"")
&IF(COUNTIF(A1:M1,8)=8,0,"")
&IF(COUNTIF(A1:M1,9)=9,0,"")

Answer 4

=MID(SUM(IFERROR(SMALL(IF(FREQUENCY(IF(COUNTIF(A1:M1,{0,1,2,3,4,5,6,7,8,9})=0,{0,1,2,3,4,5,6,7,8,9}),ROW($1:$9)-1)>0,ROW($1:$10)-1),ROW($1:$9))*10^-ROW($1:$9),)),3,9)——数组。本回答被提问者采纳

Answer 5

N1输入

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0123456789",A1,),B1,),C1,),D1,),E1,),F1,),G1,),H1,),I1,),J1,),K1,),L1,),M1,)

下拉