英文:
In Excel, How would I attach, or concatenate single attributes together when there are multiple
问题
我想将每个属性附加到一行的“名称”中。每一行可以有一个或多个属性。如果有多个属性，我想创建一个新行，将第2、第3或第4个属性附加到第一列的名称中。
我试图做的是将一个看起来像这样的表格转换为一个单列，看起来像这样。
| 名称 |
|---|
| X101 |
| Y201 |
| Y202 |
| Z302 |
| Z303 |
我希望用 Microsoft Power Automate 或 Python 通过 Pandas 数据框来实现这个目标。目前，我只需要弄清楚我想做的事情是否可以通过 Excel 实现。
目前我正在使用嵌套的 IF 语句，当第一个或第二个属性为空时，将属性连接到“名称”列。然而，这排除了一个“名称”有多个属性的可能性。
非常感谢！
英文:
I want to attach each attribute to a "name" in a single row. each row could have one or many attributes. If there are more than one attributes, I want to create a new line with the 2nd, 3rd, or 4th attributes attached to the names in the first column.
What I am trying to do is take a table that looks like this.
| name | Attr_1 | Attr_2 | Attr_3 |
|---|---|---|---|
| X | 101 | ||
| Y | 201 | 202 | |
| Z | 302 | 303 |
I want to convert it into a single column that looks like this.
| name |
|---|
| X101 |
| Y201 |
| Y202 |
| Z302 |
| Z303 |
I am looking to do this with Microsoft Power Automate, or python via Pandas data frame eventually. For now, I just need to figure out if what I am trying to do is possible through Excel.
Currently I am using nested IF statements to concatenate an attribute to the "name" column when the first or second attribute is blank. However, this leaves out the possibility of a "name" having multiple attributes.
Many thanks!
答案1
得分: 1
=TOCOL(IF(B2:D4<>"",A2:A4&" - "&B2:D4,NA()),3)
或者使用FILTER：
=FILTER(TOCOL(A2:A4&" - "&B2:D4),TOCOL(B2:D4)<>"")
英文:
Use TOCOL and CONCATENATE unless empty then return error:
=TOCOL(IF(B2:D4<>"",A2:A4&" - "&B2:D4,NA()),3)
Or Using FILTER:
=FILTER(TOCOL(A2:A4&" - "&B2:D4),TOCOL(B2:D4)<>"")
答案2
得分: 0
使用Power Query：
右键单击“name”列，解除其他列的行列
移除“Attribute”列
合并“name”和“Value”列
英文:
With Power Query:
Right click on "name" column, Unpivot other columns
Remove "Attribute" column
Merge "name" and "Value" columns
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论