英文:
New column with two comparisons before
问题
你的问题是你想使用最简单的函数（如VLOOKUP）来创建一个新的列，根据条件比较列B、C和D，并根据条件的满足与否在新列E中放置1或0。你已经尝试了一个COUNTIFS函数，但它返回了所有值为"Yes"。问题在于COUNTIFS函数的条件不正确，它只考虑了与第一个行相同的条件。
你可以使用以下公式来实现你的需求：
=IF(SUMPRODUCT(($A$2:$A$19=A2)*($B$2:$B$19=B2)*($C$2:$C$19=C2)*($D$2:$D$19=D2))>1, 0, 1)
将此公式放置在E2单元格中，然后拖动填充单元格以应用到其他行。这将会根据条件比较列B、C和D，并在新列E中放置1或0，以满足你的要求。
英文:
I am working in a Excel table. This is the info that I have:
A B C D
At ID ID ID
At ID ID ID
At ID ID ID
At ID DESC ID
Ag ID ID ID
Ag ID ID ID
Ag ID ID ID
Ag ID ID DESC
B ID ID ID
B ID ID ID
C ID - DESC ID - DESC ID - DESC
C ID - DESC ID - DESC ID - DESC
C ID - DESC ID - DESC ID - DESC
CS ID ID ID
CS ID ID ID
Ca ID - DESC ID - DESC ID - DESC
Ca ID ID ID
Ca ID ID ID
Ca ID ID ID
Ce ID ID ID
Ce ID ID ID
Ce ID ID ID
Ce ID ID DESC
Ce ID ID DESC
I want to create a new column with this conditions previously. Firstly, I want to get all the info that it has equals names. Then, there are to compare, by rows, the following columns, B C and D. For that, if they are all the same, it would 1 on it, otherwise 0.
This is an example.
First, we have At in the column A. We get all the info.
At ID ID ID
At ID ID ID
At ID ID ID
At ID DESC ID
Then, we have to compare, B2=B3, B2=B4, B3=B4. Same thing with the others columns. How the value of B4 is distinct of other, then in the new column E, it would be a 0.
Other example. For value "B" in column A. We get all the info:
B ID ID ID
B ID ID ID
As they are all the same, in the new column E it would be a 1.
How can I do that? I need it to be done with the simplest functions, if possible, such as VLOOKUP.
This is the output that I want:
A B C D E
At ID ID ID 0
At ID ID ID 0
At ID ID ID 0
At ID DESC ID 0
B ID ID ID 1
B ID ID ID 1
I did that:
=IF(COUNTIFS(A:A;$A$2;B:B;$B$2;C:C;$C$2;D:D;$D$2)>1; "Yes"; "No")
But I get all values as "Yes". What I am doing wrong?
Best regard.
答案1
得分: 1
=IF(ROWS(UNIQUE(FILTER(tblData[[A]:[D]],[A]=[@A])))=1,"yes","no")
英文:
If you have Excel 365 you can use this formula:
=IF(ROWS(UNIQUE(FILTER(tblData[[A]:[D]],[A]=[@A])))=1,"yes","no")
It counts the unique rows per Column A value - if there is only one unique row it is a valid section.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论