问题描述
例如,我有一个 xls,其中:
- A 列包含具有属性 A 的项目列表
- B 列包含具有 B 属性的项目列表
我需要以下内容:
- C 列是 A 联合 B(A 和 B 的唯一项)
- D 列是 A 交点 B(A 和 B 的公共项)
- E 列是 A 减去 B(A 中的项目但 B 中没有的项目)
- F 列是 B 减去 A(B 中的项目但 A 中没有的项目)
使用 SQL 或 Python 对元素列表进行设置操作似乎很容易.但是如何在 xls 中做到这一点?
注意:它应该是一个自动化,复制粘贴和点击次数最少.例如,我不想将 A 复制粘贴到 B 下方,然后消除重复项"得到 A 联合 B.
Intersection (In A & B): =IFNA(VLOOKUP(B2,$A$2:$B$42,1,FALSE),";")
联合(在 A 或 B 中):=IFS(A2,A2,B2,B2)
请注意,IFS
仅适用于 Office 2019 及更新版本.>
A - B(仅在 A 中):=IF(NOT(IFNA(MATCH(A2,$B$2:$B$42,0),FALSE)),IF(A2,A2,"";),"")
B - A(仅在 B 中):=IF(NOT(IFNA(MATCH(B2,$A$2:$A$42,0),FALSE)),IF(B2,B2,"";),"")
(交换字母)
For example, I have an xls where :
- column A has list of items with property A
- column B has list of items with property B
I need the following :
- column C which is A union B (unique items of both A & B)
- column D which is A intersection B (common items of A & B)
- column E which is A minus B (items in A but not in B)
- column F which is B minus A (items in B but not in A)
Set operations on a list of elements seem to be easy with SQL or Python. But how to do it in xls?
Note : It should be an automation with minimal copy-pastes and clicks. For example, I dont want to copy-paste A below B, then "eliminate duplicates" to get A union B.
Intersection (In A & B): =IFNA(VLOOKUP(B2,$A$2:$B$42,1,FALSE),"")
Union (In A or B): =IFS(A2,A2,B2,B2)
Note that IFS
is only in Office 2019 and newer editions.
A - B (Only in A): =IF(NOT(IFNA(MATCH(A2,$B$2:$B$42,0),FALSE)),IF(A2,A2,""),"")
B - A (Only in B): =IF(NOT(IFNA(MATCH(B2,$A$2:$A$42,0),FALSE)),IF(B2,B2,""),"")
(Swap the letters)
这篇关于我们如何在 MS Excel 中执行常见的集合操作(并集、交集、减号)?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!