Excel 计算值连续出现的次数并用特定计算替换

Question

我有超过 100,000 行的 Excel 数据。只有一列包含 0 或大于 0 的值。

当值为 0 时，我想计算该列中连续出现的 0 的次数，直到达到更高的值。假设我开始遍历并发现样本值为 24，然后我停在那里并计算到目前为止 0 的总数。假设零的总数为 24，直到为 4。我将 1 除以 4，然后将所有 0 替换为 0.25。如果总数为 10，则必须将 1 除以 10，然后 0.1 将替换所有零，直到找到为止。

然后，当值再次达到 0 时，同样的过程继续进行。然后我再次计算 0，并进行与上述相同的计算。

之前的数据：

以下是所需内容的示例：

非常感谢您的帮助。

Answer 1

您可以在 Power Query 中执行此操作，可在 Windows Excel 2010+ 和 Office 365 中使用

使用 Power Query

• 在数据表中选择一些单元格
• Data => Get&Transform => from Table/Range
• 当 PQ 编辑器打开时：Home => Advanced Editor
• 记下表格姓名在第 2 行
• 将下面的 M 代码粘贴到您所看到的位置
• 将第 2 行的表名改回最初生成的表名。
• 阅读评论并探索Applied Steps以了解算法

M 代码

let

//Change table name in next line to your actual table name
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],

//set the data type
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"duration_in_days", Int64.Type}}),

//group by each group of 0's plus the non-zero entry
    //add an index column
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

    //add a custom column that copies over a number from Index only if the data is non-zero
    #"Added Custom" = Table.AddColumn(#"Added Index", "grps", each if [duration_in_days]=0 then null else [Index]),

    //fill up to create a group for each sequence
    #"Filled Up" = Table.FillUp(#"Added Custom",{"grps"}),

//Group by the grouping sequence
    group = Table.Group(#"Filled Up","grps",{

        //generate a LIST according to you divide/by rules, adding the last entry at the end
        {"duration_in_days", each 
            let 
                cnt = Table.RowCount(_),
                newList = if List.RemoveItems([duration_in_days],{0}) = {}
                    then {} //List.Repeat({null}, cnt)
                    else  List.Repeat({1/(cnt-1)},cnt-1) & {List.Last([duration_in_days])}
                in newList, type list}
        }),

//remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(group,{"grps"}),

//expand the list
    #"Expanded duration_in_days" = Table.ExpandListColumn(#"Removed Columns", "duration_in_days"),

//The blank "rows" will just be the last row if there was no terminating non-zero number
    #"Removed Blank Rows" = Table.SelectRows(#"Expanded duration_in_days", each 
        not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),

//set the data type
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Blank Rows",{{"duration_in_days", type number}})
in
    #"Changed Type1"