英文:
Excel: pivot ungroup date and compare data by days
问题
我已经基本了解了在Excel中如何对数据进行分组和取消分组的操作，但有一件事情不太清楚：
我有一个表格如下：
| 日期 | 登录 |
|---|---|
| 01/01/2021 | 550 |
| 02/01/2021 | 687 |
| 03/01/2021 | 781 |
| --- | --- |
| 01/03/2021 | 478 |
| 02/03/2021 | 925 |
| --- | --- |
我想将其转换为数据透视表，以便比较不同月份中相同日期的登录次数；所以我的理想输出应该是
| 日 | 一月 | 二月 | 三月 |
|---|---|---|---|
| 01 | 550 | 717 | 478 |
| 02 | 687 | 397 | 925 |
| 03 | 781 | 714 | 397 |
| --- | --- | --- | --- |
但是，如果我使用数据透视表的分组属性，日期会针对它们特定的月份，所以我不仅有31行，而是有与原始表格一样多的行
| 日 | 一月 | 二月 | 三月 |
|---|---|---|---|
| 01-一月 | 550 | ||
| 02-一月 | 687 | ||
| 03-一月 | 781 | ||
| --- | --- | --- | --- |
| 01-三月 | 478 | ||
| 02-三月 | 925 | ||
| --- | --- | --- | --- |
有人可以帮助我吗？
谢谢
英文:
I have pretty much understood how group and ungroup data works in excel but one thing is not very clear:
I have a table like this:
| Date | Login |
|---|---|
| 01/01/2021 | 550 |
| 02/01/2021 | 687 |
| 03/01/2021 | 781 |
| --- | --- |
| 01/03/2021 | 478 |
| 02/03/2021 | 925 |
| --- | --- |
I would like to turn this into a pivot in order to compare the number of login same days in different months; so my ideal output should be
| Day | Jan | Feb | March |
|---|---|---|---|
| 01 | 550 | 717 | 478 |
| 02 | 687 | 397 | 925 |
| 03 | 781 | 714 | 397 |
| --- | --- | --- | --- |
But, if I use the grouping proprieties of pivot table the days refears to their specific months so I don't have only 31 rows but I have as many rows as the original table
| Day | Jan | Feb | March |
|---|---|---|---|
| 01-jan | 550 | ||
| 02-jan | 687 | ||
| 03-jan | 781 | ||
| --- | --- | --- | --- |
| 01-mar | 478 | ||
| 02-mar | 925 | ||
| --- | --- | --- | --- |
Can anyone help me please?
Thank you
答案1
得分: 1
以下是翻译好的部分：
- 使用 Power Query 从表/范围中提取数据
- 添加自定义列，使用公式
=Date.MonthName([Date]) - 添加自定义列，使用公式
=Date.Day([Date]) - 右键单击删除原始日期列
- 选择月份名称列，进行转换，选择 "旋转列"，选择 "登录" 列作为值，并使用默认的求和操作
- 文件 -> 关闭和加载，将其返回到 Excel 作为表格或数据透视表。需要时右键单击刷新。
以下是示例代码，可粘贴到 Power Query 的高级编辑器中：
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Month", each Date.MonthName([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Day", each Date.Day([Date])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Date"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Month]), "Month", "Login", List.Sum)
in
#"Pivoted Column"
如果您需要进一步的帮助，请告诉我。
英文:
You could bring the data in powerquery with data ... from table/range
add column .. custom column ... with formula
=Date.MonthName([Date])
add column .. custom column ... with formula
=Date.Day([Date])
Right click, remove, the original date column
Click select the month name column, transform ... pivot column ... choose Login column for values and use default sum operation
File .. close and load ... to get it back to excel as a table or as a pivot table. Right click refresh when needed
Sample code below, which can be pasted in PowerQuery into home...advanced editor
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Month", each Date.MonthName([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Day", each Date.Day([Date])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Date"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Month]), "Month", "Login", List.Sum)
in #"Pivoted Column"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论