英文:
Transpose multiple key-value pairs in single rows to multiple rows
问题
这似乎是一个愚蠢简单的事情，但我一直在努力找出一种功能性的方法来做到这一点。
我在Excel中有这种格式的数据：
1 a b
2 c
3 a c d
希望它看起来像这样：
1 a
1 b
2 c
3 a
3 c
3 d
英文:
This seems like a foolishly simple thing to do but I've been struggling with figuring out a way to do it functionally.
I have data in Excel in this format:
1 a b
2 c
3 a c d
And would like to look like so:
1 a
1 b
2 c
3 a
3 c
3 d
答案1
得分: 1
以下是翻译好的部分：
如 Isolated 在评论中提到的，使用 Power Query 将是实现数据解标准化的最简单方法，也许如果您正在使用 MS365，您还可以尝试使用新的动态溢出数组公式。
使用 Power Query：
• 选择数据表中的某个单元格，
• 数据 选项卡 => 获取和转换 => 从表/区域，
• 打开 PQ 编辑器时：开始 => 高级编辑器，
• 注意所有 2 个表的名称，
• 在您所见之处，粘贴以下 M 代码。
let
Source = Excel.CurrentWorkbook(){[Name="Datatbl"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
#"Removed Columns"
• 在将其导入 Excel 之前，将表格名称从 Datatbl 更改为 Outputtbl，请注意，您需要从 开始 选项卡中选择 --> 关闭并加载 --> 关闭并加载到 --> 根据您的喜好选择要么 现有工作表 要么 新工作表。
在 Excel 中的输出：
使用 Excel 公式 --> 仅适用于 MS365
• 如果您使用 MS365 并且在写入时正在使用当前渠道，那么
=LAMBDA(array,
LET(x,IF(array="",NA(),array),
DROP(REDUCE(0,TOCOL(TAKE(x,,1)&"|"&DROP(x,,1),3),
LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,"|"))),1)))(A13:D15)
• 还请注意：您可以在 名称管理器 中使用具有友好名称的 自定义、可重用的公式，将上述公式放在其中，将 定义名称 为 UNPIVOT。
因此，从 公式 选项卡，单击 定义名称 --> 输入名称为 UNPIVOT，并将上述公式放在 引用 中，作用域为“工作簿”。
=LAMBDA(array,
LET(x,IF(array="",NA(),array),
DROP(REDUCE(0,TOCOL(TAKE(x,,1)&"|"&DROP(x,,1),3),
LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,"|"))),1)))
语法是
=UNPIVOT(array)
其中数组为 A13:D15。
英文:
As mentioned above in comments by Isolated, using Power Query will be the most easiest way to accomplish unpivoting the data, perhaps if you are using MS365 you can try using the new dynamic spill array formulas as well.
Using Power Query :
• Select some cell in your Data Table,
• Data Tab => Get&Transform => From Table/Range,
• When the PQ Editor opens: Home => Advanced Editor,
• Make note of all the 2 Tables Names,
• Paste the M Code below in place of what you see.
let
Source = Excel.CurrentWorkbook(){[Name="Datatbl"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
#"Removed Columns"
• Change the Table name from Datatbl to as Outputtbl before importing it back into Excel, note that you need to select from Home tab --> Close & Load --> Close & Load To --> Select either Existing Worksheet or New Worksheet as per your preferences.
Output In Excel :
Using Excel Formulas --> Exclusively Applicable To MS365
• If you are using MS365 and when writing if you are in Current Channel then,
=LAMBDA(array,
LET(x,IF(array="",NA(),array),
DROP(REDUCE(0,TOCOL(TAKE(x,,1)&"|"&DROP(x,,1),3),
LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,"|")))),1)))(A13:D15)
• Also Note : You can use a custom, reusable formula with a friendly name by placing the above formula in the Name Manager - with Define Name as UNPIVOT.
So from Formulas tab, Click on Define Name --> Enter the name as UNPIVOT and place the above formula as in refers to, scope will be Workbook
=LAMBDA(array,
LET(x,IF(array="",NA(),array),
DROP(REDUCE(0,TOCOL(TAKE(x,,1)&"|"&DROP(x,,1),3),
LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,"|")))),1)))
The Syntax is
=UNPIVOT(array)
Where array is A13:D15
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论