☰

Excel 按日期对转置的列进行分组

tag-icon • tag-icon microsoft-excel worksheet-function pivot-table power-query
Excel 按日期对转置的列进行分组

我无法将数据转置为我想要的格式。我在一列中记录了工资期结束日期，在另一列中记录了员工姓名，我想将其格式化为这样一种格式：每个列标题都是工资期日期，行是该期间工资单上的员工。

这是现在格式化的数据：

pay period   name
3/6/2015     John Smith
3/6/2015     Jane Smith
4/5/2015     John Smith
4/5/2015     Jane Smith
4/5/2015     Joe Smith
4/19/2015    John Smith
4/19/2015    Jane Smith
4/19/2015    Joe Smith
5/3/2015     John Smith
5/3/2015     Jane Smith
5/3/2015     Joe Smith
5/3/2015     Julie Smith

以下是我希望的格式：

3/6/2015    4/5/2015    4/19/2015   5/3/2015
John Smith  John Smith  John Smith  John Smith
Jane Smith  Joe Smith   Joe Smith   Joe Smith
            Jane Smith  Jane Smith  Jane Smith
                                    Julie Smith

我尝试使用 Get & Transform/Power Query 通过 Pivot Columns 转置数据，使用薪资期列中的名称创建新列，将“名称”作为值列，并关闭聚合。我没有得到我想要的格式，而是将薪资期日期作为列标题，然后在第 2 行中显示一个单元格，显示“错误”。我检查了列类型以验证“薪资期”的类型为日期，“名称”的类型为文本。

我不确定我是否只是在进行查询，还是应该以其他方式进行。非常感谢任何帮助。

答案1

我也希望有更多数据透视表选项。这里有一个 vba 子程序可以为您执行此转置。这只是基础知识，可以更强大。这不会对您的日期或姓名进行排序，而是按原样获取它们。它会添加一个工作表并从当前活动工作表中获取数据。

Sub Transpose()

Dim dateDict As Object
Dim numRows As Long
Dim rowPos As Long
Dim dateVal As String

Dim currSheet As Worksheet
Set currSheet = ActiveSheet

Set dateDict = CreateObject("Scripting.Dictionary")
numRows = currSheet.Range("A1").CurrentRegion.Rows.Count
rowPos = 2
Do While rowPos <= numRows
  dateVal = currSheet.Cells(rowPos, 1).Value2
  If dateDict.exists(dateVal) Then
  Else
    dateDict.Add dateVal, 0
  End If
  rowPos = rowPos + 1
Loop

Dim mySheet As Worksheet
Set mySheet = ThisWorkbook.Worksheets.Add

Dim i As Long
Dim newPos As Long
Dim Column As Long
Column = 0
For Each myKey In dateDict
  newPos = 2
  rowPos = 2
  Column = Column + 1
  mySheet.Cells(1, Column).Value2 = myKey
  Do While rowPos <= numRows
    If currSheet.Cells(rowPos, 1).Value2 = myKey Then
      mySheet.Cells(newPos, Column).Value2 = currSheet.Cells(rowPos, 2).Value2
      newPos = newPos + 1
    End If
    rowPos = rowPos + 1
  Loop
Next

End Sub

相关内容