统计两个表里的不重复单号的个数，如遇到特殊字符则忽略不统计

zhiwen20190717

统计出表1和表2里“单号”列不重复单号的总个数，即一个单号同时出现在表1和表2里，只计数1次,如果遇到“/”或者“*”或者“N/A”则忽略不计.

wp231957

=LET(a,VSTACK(A2:A12,表2!B2:B11),COUNTA(UNIQUE(FILTER(a,(LEFT(a,1)="X")+(LEFT(a,1)="Y")))))

weiguidun

#NA版

1. =COUNTA(DROP(UNIQUE(SORT(IFNA(VSTACK(表1!A2:A12,表2!B2:B11),"-"))),3))

复制代码

NA版

1. =LET(a,VSTACK(表1!A2:A12,表2!B2:B11),COUNTA(UNIQUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(a,"*",""),"N/A",""),"/","")))-1)

复制代码

FIlter版

1. =LET(a,VSTACK(表1!A2:A12,表2!B2:B11),COUNTA(UNIQUE(FILTER(a,(a<>"*")*(a<>"/")*(a<>"N/A")))))

复制代码

hxj119

1. =REDUCE(0,UNIQUE(VSTACK(表1!A2:A12,表2!B2:B11)),LAMBDA(x,y,x+N(LEN(y)=4)))

复制代码

wgreen

#在这里=REDUCE(0,UNIQUE(VSTACK(A2:A12,表2!B2:B11),,FALSE),LAMBDA(x,y,IF((y="*")+(y="/")+(y="N/A"),x,x+1)))快速回复#

mykitami

=LET(a,VSTACK(表1!A2:A999,表2!B2:B999),UNIQUE(FILTER(a,LEN(a)=4)))

博爱风雨同舟

=LET(A,TOCOL(REDUCE(0/0,{"表1","表2"},LAMBDA(X,Y,IF(INDIRECT(Y&"!A1:C1")="单号",OFFSET(INDIRECT(Y&"!"&ADDRESS(1,COLUMN(A:C),1)),ROW(1:12),),X))),3),SUM(SIGN(MMULT(--(UNIQUE(A)={"*","/","N/A",0}),ROW(1:4)^0)=0)))

博爱风雨同舟

任意添加表格，不要刻意去引用区域就可得到。