Question

我有一个表格来追踪部分转移交易。这些数据按日期记录为数据表,有一人获得积极信贷,另一人是负信用。

Column
1 - Date
2 - Name
3 - Hours (Positive)
4 - Name
5 - Hours (Negative)

我希望有一个Pivot表,该表有一份单一的雇员名单和一份显示所涉期间净平衡的数字。

I tried to insert a Field (Field1) in the Pivot Table as a Calculated field of Hours (Negative Hours) and Hours2 (Positive Hours). But it treats the Carlos in the negative hours as a different person from Carlos with positive hours rather than creating a sum of Carlos net hours.
Basic Data

Expected Result

Answer 1

您可以开脱表格,然后开张,特别是如果您有<条码>365/代码>。

我指定了数据表<代码>Shifts,你可以使用这一公式来公布数据。它将适应<代码>Person-Hours的任何编号:

=LET(
    persCols, SEQUENCE(, (COLUMNS(Shifts) - 1) / 2, 2, 2),
    hrsCols, SEQUENCE(, (COLUMNS(Shifts) - 1) / 2, 3, 2),
    persons, TOCOL(CHOOSECOLS(Shifts, persCols)),
    hrs, TOCOL(CHOOSECOLS(Shifts, hrsCols)),
    VSTACK({"Person", "Hours"}, HSTACK(persons, hrs))
)

This results in a table:

And you can use the regular Excel pivot table wizard to create the result table in your desired format:

然而,也可通过电文,在Windows Excel 2010+和Microsoft 365(Windows或Ma)上提供。

这一解决办法还将针对任何数量的个人-人类配对栏。

使用权力

Select some cell in your Data Table
Data => Get&Transform => from Table/Range
When the PQ Editor opens: Home => Advanced Editor
Make note of the Table Name in Line 2
Paste the M Code below in place of what you see
Change the Table name in line 2 back to what was generated originally.
Read the comments and explore the Applied Steps to understand the algorithm

let

//Change next lines to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Shifts"]}[Content],


//can have multiple person-hour pairs after the first "Date" column
    personCols = List.Alternate(List.RemoveFirstN(Table.ColumnNames(Source),1),1,1,1),
    hoursCols = List.Alternate(List.RemoveFirstN(Table.ColumnNames(Source),1),1,1,0),
    typeSets = {{"Date", type date}} & List.Transform(personCols, each {_, type text}) & List.Transform(hoursCols, each {_, type number}),

//Set the data types
    #"Changed Type" = Table.TransformColumnTypes(Source, typeSets),

//Unpivot the data columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),

//Remove now uneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Date", "Attribute"}),

//add a "shifted" column to facilitate matching the person with their hours
    #"Add Shifted Values" = Table.FromColumns(
        Table.ToColumns(#"Removed Columns") &
        {List.RemoveFirstN(#"Removed Columns"[Value],1) & {null}},
        type table[Person=text, Hours=number]),

//Remove unneeded rows
    #"Removed Alternate Rows" = Table.AlternateRows(#"Add Shifted Values",1,1,1),

//Group by Person and SUM the hours for each
    #"Grouped Rows" = Table.Group(#"Removed Alternate Rows", {"Person"}, {{"Sum", each List.Sum([Hours]), type number}}),

//Sort result in alphabetical order
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Person", Order.Ascending}})
in
    #"Sorted Rows"

友情链接