英文:
Comparing columns in Google sheets to find common values (number of) and unique values (number of)
问题
I want to make the two tables on the right using the information in the table on the left.
第一个表格显示有多少人具有相同的颜色值。
例如，有多少人是红色和蓝色的。
第二个表格排除了那些拥有两种相同颜色的人，只关注唯一的人。
例如，有多少人是红色但不是蓝色的（反之亦然）。
我正在努力找出要使用的公式...以及是否需要中间步骤。
我尝试过Vlookup，但不确定在这种情况下是否需要它。我不熟悉所有的Google表格公式选项，也无法从Google搜索或YouTube搜索中找到适用于这种情况的解决方法。
表格链接：
Google Sheets
英文:
I've got a big dataset so made a smaller example to explain.
Basically I want to make the two tables on the right using the information in the table on the left.
The first table shows how many people have common values for color.
Eg. How many people are in Red and Blue.
The second table excludes the people who have the two colors in common and only looks at the unique ones.
Eg. How many people who are in Red but not in Blue (and vice versa).
I'm struggling to figure out the formulas to use...and whether I need an interim step or not.
I've tried Vlookup but not sure it's what I need in this case. Am not familiar with all the google sheet formula options and couldn't find something that helped with this particular case from googling or searching youtube.
Link to sheet:
Google Sheets
答案1
得分: 1
Green在column_B中有尾随空格字符，您可能需要修复（在截图中突出显示）- 在应用这些公式到单元格
F3和F12之前，请清除范围F3:I6和F12:I15 - 几个公式导出的输出值与您手动输入的期望输出不同，您可能需要仔细检查
F3 公式：
=makearray(counta(E3:E6),counta(F2:I2),lambda(r,c,if(index(E3:E6,r)=index(F2:I2,,c),, let(Σ,filter(A:A,B:B=index(E3:E6,r)),counta(ifna(filter(Σ,xmatch(Σ,filter(A:A,B:B=index(F2:I2,,c))))))))))
F12 公式：
=makearray(counta(E3:E6),counta(F2:I2),lambda(r,c,if(index(E3:E6,r)=index(F2:I2,,c),, let(Σ,filter(A:A,B:B=index(F2:I2,,c)),counta(filter(Σ,iserror(xmatch(Σ,filter(A:A,B:B=index(E3:E6,r))))))))))
英文:
You may try:
Greenhas trailing space character incolumn_Bwhich you may want to fix (highlighted in screenshot)- Clear the ranges
F3:I6&F12:I15prior to applying these formulas in CellsF3&F12 - couple of formula-derived output values are different from your manually entered expected output; which you may need to double check
F3 formula:
=makearray(counta(E3:E6),counta(F2:I2),lambda(r,c,if(index(E3:E6,r)=index(F2:I2,,c),,
let(Σ,filter(A:A,B:B=index(E3:E6,r)),counta(ifna(filter(Σ,xmatch(Σ,filter(A:A,B:B=index(F2:I2,,c))))))))))
F12 formula:
=makearray(counta(E3:E6),counta(F2:I2),lambda(r,c,if(index(E3:E6,r)=index(F2:I2,,c),,
let(Σ,filter(A:A,B:B=index(F2:I2,,c)),counta(filter(Σ,iserror(xmatch(Σ,filter(A:A,B:B=index(E3:E6,r))))))))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论