生成一个带条件的表格。

huangapple go评论66阅读模式
英文:

Generate a table with condition

问题

我正在使用Excel制作表格。这是Sheet1中我的表格的示例:

A B C D
al id id id
df id desc desc
df id id desc
df id id id
ff desc id desc
ff desc id desc
al id id id
al id id desc
mn desc desc desc
mn desc desc desc
ff desc id desc

在这个表格中,目标是比较列A中的重复值,并得到包含列A、B、C和D的表格。然后,我需要同时比较列B、C和D,并创建一个新列,如果它们全部匹配,则将1放入该列,否则放入0。对于前面的表格,这是我得到的内容:

A B
al 0
df 0
ff 1
mn 1

这保存在名为Sheet2的另一个工作表中。

现在,我想将Sheet1中在Sheet2中为0的所有信息保存在另一个工作表Sheet3中。例如,在Sheet3中应该有:

A B C D
al id id id
al id id id
al id id desc
df id desc desc
df id id desc
df id id id

看起来,我想要在Sheet3中按行将Sheet1中列A的所有值都放在一起。

如何做到这一点?

最好的问候。

英文:

I'm working on a table with Excel. Here is an example of my table in Sheet1:

A B C D
al id id id
df id desc desc
df id id desc
df id id id
ff desc id desc
ff desc id desc
al id id id
al id id desc
mn desc desc desc
mn desc desc desc
ff desc id desc

In this table, the goal was to compare column A with duplicate values and you will get a table of columns A B C, and D. With that table, I have to compare de columns B C and D at once. Later, I have to create a new column where I have to put 1 if they all match, and 0 otherwise. For the previous table, this is what I get:

A B
al 0
df 0
ff 1
mn 1

This is saved in another sheet called Sheet2.

Now, I want to save in another Sheet, called Sheet3, all the information of Sheet1 where in Sheet2 there is a 0. For example, in Sheet3 there should be:

A B C D
al id id id
al id id id
al id id desc
df id desc desc
df id id desc
df id id id

How it looks, I want all of the values for column A in Sheet1, together by rows in Sheet3.

How can I do that?

Best regard.

答案1

得分: 1

Here is the translation of the provided content:

根据下面的截图,我使用了以下公式。

<kbd>G1</kbd> 单元格公式-

=HSTACK(UNIQUE(A1:A11),
BYROW(UNIQUE(A1:A11),
LAMBDA(x,IF(ROWS(UNIQUE(FILTER($B$1:$D$11,$A$1:$A$11=x)))&gt;1,0,1)))

<kbd>J1</KBD> 单元格公式。

=LET(x,FILTER(UNIQUE(A1:A11),BYROW(UNIQUE(A1:A11),LAMBDA(x,IF(ROWS(UNIQUE(FILTER($B$1:$D$11,$A$1:$A$11=x)))&gt;1,0,1)))=0),
y,REDUCE(&quot;&quot;,x,LAMBDA(a,b,VSTACK(a,FILTER(A1:D11,A1:A11=b)))),DROP(y,1))

生成一个带条件的表格。

英文:

As per below screenshot I have used the following formulas.

<kbd>G1</kbd> cell formula-

=HSTACK(UNIQUE(A1:A11),
BYROW(UNIQUE(A1:A11),
LAMBDA(x,IF(ROWS(UNIQUE(FILTER($B$1:$D$11,$A$1:$A$11=x)))&gt;1,0,1))))

<kbd>J1</KBD> cell formula.

=LET(x,FILTER(UNIQUE(A1:A11),BYROW(UNIQUE(A1:A11),LAMBDA(x,IF(ROWS(UNIQUE(FILTER($B$1:$D$11,$A$1:$A$11=x)))&gt;1,0,1)))=0),
y,REDUCE(&quot;&quot;,x,LAMBDA(a,b,VSTACK(a,FILTER(A1:D11,A1:A11=b)))),DROP(y,1))

生成一个带条件的表格。

答案2

得分: 1

I used the following formula to generate your initial table:

=LET(x,UNIQUE(A1:A11),HSTACK(x,--MAP(x,LAMBDA(y,ROWS(UNIQUE(FILTER(B1:D11,A1:A11=y)))=1)))

To generate your desired output in a 2nd table, simply use:

Formula in F6:

=SORT(FILTER(A1:D11,NOT(VLOOKUP(A1:A11,F1#,2,0)))
英文:

Again, as per previous answers to your questions here and here, I used the following formula to generate your initial table:

=LET(x,UNIQUE(A1:A11),HSTACK(x,--MAP(x,LAMBDA(y,ROWS(UNIQUE(FILTER(B1:D11,A1:A11=y)))=1))))

To generate your desired output in a 2nd table just simple use:

生成一个带条件的表格。

Formula in F6:

=SORT(FILTER(A1:D11,NOT(VLOOKUP(A1:A11,F1#,2,0))))

huangapple
  • 本文由 发表于 2023年4月11日 14:58:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/75983173.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定