当前位置：文江博客话题详情

在 Excel 中动态透视多列

发布于 2025-01-13 06:43:19 字数 1165 浏览 1 评论 0原文

我有以下数据，我希望将其透视以生成右侧的表格。（请注意，此数据是手动输入的，可能存在错误）

我不确定旋转是否是解决此问题的正确方法，但如果我只有一列，它将达到预期的效果。

源数据已经应用了一系列转换来简化到这个阶段，但我现在正在研究尝试实现右侧的表格。

我很高兴我可以创建一系列自定义列，我希望该表能够动态工作，这样如果用户要创建不同的剂量描述符，则会动态生成一个新列。再次为了简单起见，我仅使用 NOAEL、DNEL 和 ADI 作为剂量描述符，但存在其他剂量描述符，例如 TUL。

我将更新我在这里取得的任何进展，但不幸的是，我只是在尝试这样做时感到困惑。

数据：

CAS 编号

77-92-9
77-92-9
106-24-1
106-24-1
106-24-1
7632-00-3
107-41-5
107-88-1
107-88-1
107-88-1
497-19-8
497-19-9
1300-72-7
99-76-3

化学名称

citric acid/ 2-Hydroxypropane-1,2,3-tricarboxylic acid
citric acid/ 2-Hydroxypropane-1,2,3-tricarboxylic acid
GERANIOL
GERANIOL
GERANIOL
Sodium nitrite
2-Methyl-2,4-pentanediol
1,3-Butylene glycol
1,3-Butylene glycol
1,3-Butylene glycol
sodium carbonate
sodium carbonate
Sodium xylenesulfonate
Methyl 4-hydroxybenzoate

剂量描述符

NOAEL
DNEL
NOAEL
DNEL
ADI
DNEL
DNEL
NOAEL
DNEL
ADI
ADI
NOAEL
NOAEL
DNEL

原文

I have the following data which I wish to Pivot to produce the table on the right. (Note this data has been manually entered and it's possible there are errors)

I'm unsure if Pivoting is the right way to go about this but it would achieve the desired effect if I only had one column.

The source data has already had a series of transformations applied to simplify to this stage however I am now studying trying to achieve the table on the right.

I appreciate I could create a series of custom columns by I would like this table to work dynamically such that if a user were to create a different Dose Descriptor a new column would dynamically be generated. For simplicity again I have only used NOAEL, DNEL and ADI as the dose descriptors but others e.g. TUL exist.

I will update any progress I make here but unfortunately have only got confused trying to do this.

Data:

CAS Number

77-92-9
77-92-9
106-24-1
106-24-1
106-24-1
7632-00-3
107-41-5
107-88-1
107-88-1
107-88-1
497-19-8
497-19-9
1300-72-7
99-76-3

Chemical Name

citric acid/ 2-Hydroxypropane-1,2,3-tricarboxylic acid
citric acid/ 2-Hydroxypropane-1,2,3-tricarboxylic acid
GERANIOL
GERANIOL
GERANIOL
Sodium nitrite
2-Methyl-2,4-pentanediol
1,3-Butylene glycol
1,3-Butylene glycol
1,3-Butylene glycol
sodium carbonate
sodium carbonate
Sodium xylenesulfonate
Methyl 4-hydroxybenzoate

Dose Descriptor

NOAEL
DNEL
NOAEL
DNEL
ADI
DNEL
DNEL
NOAEL
DNEL
ADI
ADI
NOAEL
NOAEL
DNEL

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

欲拥i 2025-01-20 06:43:19

使用数据将数据加载到 powerquery 中，从表/范围 [x] 标题中

单击选择前 4 列，右键单击，取消透视其他列

添加列...自定义列...使用公式

= [Dose] & " " & [Attribute]

右键单击并删除剂量和属性列

单击选择自定义列并转换...数据透视列..

使用VALUE作为值列，高级，不聚合

文件..关闭并加载

示例代码：

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"CAS Number", type text}, {"Chemical name", type text}, {"Dose", type text}, {"Oral", Int64.Type}, {"Inhale", Int64.Type}, {"Dermal", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"No.", "CAS Number", "Chemical name", "Dose"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Dose] & " " &[Attribute]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Dose", "Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value")
in  #"Pivoted Column"

Load data into powerquery using data ... from table/range [x]headers

Click select the first 4 columns, right click, unpivot other columns

Add column ... custom column ... with formula

= [Dose] & " " & [Attribute]

right click and remove dose and attribute columns

click select the custom column and transform ... pivot column ..

use VALUE as the values column, advanced, don't aggregate

file .. close and load

sample code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"CAS Number", type text}, {"Chemical name", type text}, {"Dose", type text}, {"Oral", Int64.Type}, {"Inhale", Int64.Type}, {"Dermal", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"No.", "CAS Number", "Chemical name", "Dose"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Dose] & " " &[Attribute]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Dose", "Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value")
in  #"Pivoted Column"

回复收藏 0 原文

~没有更多了~