有点让人头疼的跨表提取数据

zhiwen20190717

表里的“单号”，是最终想要的结果，提取规则是依据“编号”对应的“单号”，将B表里“编号”对应的"单号"数据从A表里减掉（去除）。

zhiwen20190717

上传一下附件

zhiwen20190717

已上传附件

剑指E

.               

zhiwen20190717

剑指E 发表于 2024-4-29 12:58
先说明你的excel版本是？？？

本人使用的是WPS

ericxzhou

Python+Pandas:

import pandas as pd

df = pd.read_excel('Sample226.xlsx', 'A表')
df1 = pd.read_excel('Sample226.xlsx', 'B表')
df['单号1'] = df['单号'].str.split(',')
for i,v in df.iterrows():
  lst = v['单号1']
  lst1 = list(df1['单号'][df1['编号'].str.contains(v['编号'])])
  lst2 = [i for i in lst if i not in lst1]
  df.iloc[i, 2] = ','.join(lst2)
df.drop('单号1', axis=1, inplace=True)
print(df)

part98

1. =LET(s,TEXTSPLIT(VLOOKUP(A2,A表!A2:C7,3,0),,","),TEXTJOIN(",",,IF(ISNA(MATCH(s,B表!D2:D10,0)),s,"")))

复制代码

mykitami

=VLOOKUP(A2,HSTACK(A表!$A$2:$A$7,MAP(A表!$A$2:$A$7,A表!$C$2:$C$7,LAMBDA(m,n,LET(a,REDUCE(n&",",FILTER(B表!$D$2:$D$10,ISNUMBER(FIND(m,B表!$A$2:$A$10)))&",",LAMBDA(x,y,SUBSTITUTE(x,y,))),REPLACE(a,LEN(a),1,))))),2,)

M365