英文:
How to pull out cells out of a table to create a new table?
问题
如何从表格中提取出单元格(Names)及其对应的单元格(Ages),以创建一个新的表格?
我有下面的姓名和年龄列:
我想要提取以下内容:
这样我的最终列将如下所示,分别是Names_final和Ages_final:
英文:
How can I pull out cells (Names) and their corresponding cells (Ages) out of a table to create a new one?
I have the column of names and ages below:
I'd like to pull out the following:
So that my final column will be as it is below in Names_final and Ages_final
答案1
得分: 2
使用FILTER
和COUNTIFS
:
=FILTER(A2:B9, COUNTIFS(D2:D4, A2:A9) = 0)
答案2
得分: 2
排除两列中的匹配项
- 重要的是
FILTER
函数。 - 使用
COUNTIFS
,您可以这样做:
=FILTER(A2:B12,COUNTIFS(D2:D4,A2:A12,E2:E4,B2:B12)=0,"")
- 使用双重
XMATCH
,您可以这样做:
=FILTER(A2:B12,NOT(ISNUMBER(XMATCH(A2:A12,D2:D4)*XMATCH(B2:B12,E2:E4))),"")
- 要使其对
COUNTIFS
版本更灵活,您可以这样做:
=LET(md,A2:B12,mnc,1,mac,2,ed,D2:E4,enc,1,eac,2,
mn,INDEX(md,,mnc),ma,INDEX(md,,mac),
en,INDEX(ed,,enc),ea,INDEX(ed,,eac),
f,COUNTIFS(en,mn,ea,ma)=0,
FILTER(md,f,""))
使用以下变量命名逻辑:
m - 匹配
e - 排除
d - 数据
n - 名称
a - 年龄
c - 列
f - 过滤
- 对于
XMATCH
版本,只需不同的过滤器:
f,NOT(ISNUMBER(XMATCH(mn,en)*XMATCH(ma,ea))),
英文:
Exclude Matches in Two Columns
-
The star of the show is the
FILTER
function. -
Using
COUNTIFS
, you could do:
=FILTER(A2:B12,COUNTIFS(D2:D4,A2:A12,E2:E4,B2:B12)=0,"")
- Using a double
XMATCH
, you could do:
=FILTER(A2:B12,NOT(ISNUMBER(XMATCH(A2:A12,D2:D4)*XMATCH(B2:B12,E2:E4))),"")
- To make it more flexible for the
COUNTIFS
version, you could do:
=LET(md,A2:B12,mnc,1,mac,2,ed,D2:E4,enc,1,eac,2,
mn,INDEX(md,,mnc),ma,INDEX(md,,mac),
en,INDEX(ed,,enc),ea,INDEX(ed,,eac),
f,COUNTIFS(en,mn,ea,ma)=0,
FILTER(md,f,""))
with the following variable naming logic:
m - match
e - exclusion
d - data
n - name
a - age
c - column
f - filter
- The same for the
XMATCH
version requires only a different filter:
f,NOT(ISNUMBER(XMATCH(mn,en)*XMATCH(ma,ea))),
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论