☰

Excel 查找行中总和大于条件的单元格

tag-icon • tag-icon microsoft-excel
Excel 查找行中总和大于条件的单元格

我需要一些优雅的方法（不像屏幕截图中的方法），来计算一行中所有单元格的总和，并找到总和超过某个特定值的时刻。当找到总和更大的单元格时，我需要识别该单元格并将其用于其他目的。在示例中，您可以看到公式的作用，但这个公式需要针对每个新数据进行扩展，因此不可能像这样维护。

Excel 中的示例
（点击图片放大）

答案1

一般来说，我发现在添加数据时添加列的表格中解决此类问题会更​​困难。首先对数据进行规范化（即堆叠发票数量和产品），然后对堆叠的数据进行计算会容易得多。

我从您的数据中规范化了几行（只需使用选择性粘贴/转置），因此每个产品/发票/数量都有一行。

我将数字条件放在不同的表中，然后使用 VLOOKUP 将数字条件添加到主表中的每一行。

主数据表和标准表如下所示这

接下来我提到这个帖子使用 PowerQuery 在主表上创建运行总和计算。

M函数fnRunSum定义如下：

(tbl as table, sumcolumn as text, rowindex as number, product as number) =>
let
    #"Filtered Rows" = Table.SelectRows(tbl, each ([Product] = product)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [ID] <= rowindex),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{sumcolumn, "Temp"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {}, {{"RunningTotal", each List.Sum([Temp]), type number}}),
   RunningTotal = Record.Field(#"Grouped Rows"{0},"RunningTotal")
in
    RunningTotal

成功创建运行总和后，我添加了一个条件列，以指示某一行的运行总和是否高于该行的标准。如果是，我将该行标记为 1，否则标记为 0。

输出查询定义：

let
    Source = reference_values,
    #"Added Custom" = Table.AddColumn(Source, "RunSum", each fnRunSum(Source,"Quantity",[ID],[Product])),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "RunSumGreaterThanCriteria", each if [RunSum] > [Number Criteria] then 1 else 0)
in
    #"Added Conditional Column"

然后，我将查询结果加载到名为“输出”的工作表中。该数据如下所示这。

然后我使用 MINIFS 获取我感兴趣的产品的所有 1 标记行的最小索引。MINIFS 公式如下所示这。

最后，我希望当任何数据更改或添加时，“第一张发票过去标准”能够刷新。为了实现这一点，当主表上的任何内容发生更改时，查询“输出”需要刷新。因此，我将其添加到主表后面的 VBA 中，我将其称为“数据”：

Private Sub Worksheet_Change(ByVal Target As Range)
    wsOutput.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
End Sub

相关内容