怎样统计出缺失的工具 已解决谢谢

jerrike

1. =REGEXP(XLOOKUP(C3,标准!A:A,标准!B:B,"表无"),"("&TEXTJOIN("|",1,TEXTSPLIT(S3,","))&")|,",2)

复制代码

=REGEXP(XLOOKUP(C2,标准!A:A,标准!B:B,"表无"),"("&TEXTJOIN("|",1,UNIQUE(TEXTSPLIT(S2,","),1))&")|,",2)

zgller1

=UNIQUE(TEXTSPLIT(TEXTJOIN(",",1,S3,LOOKUP(1,0/(标准!$A$1:$A$8=C3),标准!$B$1:$B$8)),","),1,1)

wgreen

=IFERROR(UNIQUE(VSTACK(TEXTSPLIT(S7,,","),TEXTSPLIT(VLOOKUP(C7,标准!$A$1:$B$8,2,FALSE),,",")),,TRUE),"")

1786024734

1. =SUBSTITUTES(FILTER(标准!B:B,标准!A:A=C2,""),TEXTSPLIT(S2,","),)

复制代码

zpy2

select  *, regreplace(',,|,$|^,','', regreplace(replace(已领工器具,',','|'),'',(select f02 from Sheet2 where f01 like a.工单名称))) 缺少 from Sheet1 a;

ericxzhou

Python+Pandas:
import pandas as pd

f = pd.ExcelFile('pandas exercise/sample374.xlsx')
df = pd.read_excel(f, 'Sheet1', usecols='a,c,s,v')
df1 = pd.read_excel(f, '标准', header=None)
d = dict(zip(df1[0], df1[1].str.split(',')))
for t in df.itertuples():
    if t[2] in d:
        df.iloc[t[0], 3] = ','.join(set(d[t[2]]) - set(t[3].split(',')))

df.fillna('')