☰

Excel：计算不同组的运行余额

tag-icon • tag-icon microsoft-excel microsoft-excel-2016 r
Excel：计算不同组的运行余额

我想计算不同组的流动余额。为了说明，我的表格包含以下列和数据/信息：

帐户 交易金额 创建于 流动余额
帐户 A 100 2021 年 3 月 19 日 100
帐户 A 200 2021 年 3 月 20 日 300
帐户 A 300 2021 年 3 月 21 日 600
帐户 A (400) 2021 年 3 月 22 日 200
帐户 B 50 2021 年 3 月 19 日 50
帐户 B 100 2021 年 3 月 20 日 150
帐户 B 150 2021 年 3 月 21 日 300
帐户 B (200) 2021 年 3 月 22 日 100
帐户 C 10 2021 年 3 月 19 日 10
帐户 C 20 2021 年 3 月 20 日 三十
帐户 C 三十 2021 年 3 月 21 日 60
帐户 C (40) 2021 年 3 月 22 日 20

目前我手动计算“流动余额”列。也就是说，对于每个帐户（例如：帐户 A），流动余额 = 之前的流动余额 +（或 -）Txn_Amount。

除了手动执行此操作之外，还有谁有更好的想法吗？

如果有人能用 R 提出解决方案，那也是可行的。谢谢。

答案1

这也可以通过 Power Query 来实现，可在 Windows Excel 2010+ 和 Excel 365（Windows 或 Mac）中使用

使用 Power Query

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

M 代码

let

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

//Group by "Acct_msisdn"
//  then add a running balanc column to each subtable
    #"Grouped Rows" = Table.Group(Source, {"Acct_Msisdn"}, {
        {"Running Balance", (t)=> let 
            rb=List.Generate(
                ()=>[rb=t[Txn_Amount]{0}, idx=0],
                each [idx]<Table.RowCount(t),
                each [rb= [rb]+t[Txn_Amount]{[idx]+1}, idx = [idx]+1],
                each [rb]),
            addCol= Table.FromColumns(
                Table.ToColumns(t) & {rb},
                Table.ColumnNames(Source) & {"Running Balance"}
            )
            in addCol}}),

//expand the subtable
// then set the data types
    #"Expanded Running Balance" = Table.ExpandTableColumn(#"Grouped Rows", "Running Balance", 
        {"Txn_Amount", "created_on", "Running Balance"}, 
        {"Txn_Amount", "created_on", "Running Balance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Running Balance",{
        {"Acct_Msisdn", type text}, 
        {"Txn_Amount", Currency.Type}, 
        {"created_on", type date}, 
        {"Running Balance", Currency.Type}})
in
    #"Changed Type"

数据表
在此处输入图片描述

带有运行余额的结果表
在此处输入图片描述

答案2

使用求和函数功能。

假设您的数据列表的左上角位于单元格中，A1则您的余额列位于其D第一个计算值的列中，位于单元格中D2。以下公式可以在单元格中使用D2

=SUMIF(A$2:A2,A2,B$2:B2)

将单元格复制D2到列中的后续单元格D，这将保持列中标识的不同帐户的运行余额A。

该配方的工作原理是使用绝对寻址和相对寻址。它不需要按帐户对行进行分组（就像您的示例数据中那样）。无论数据行的顺序如何，它都可以工作。

请小心标记列中的帐户A。如果您错误标记了帐户，例如，您在一行中将“帐户 A”写成了“帐户 A”，那么列中该行的值B将不会包含在帐户 A 的任何流动余额中，但您会为任何错误标记的行创建一个名为“帐户 A”的流动余额。更难识别的是“帐户”和“A”之间有双空格的错误标记。

为了避免成为此类数据输入错误的无意识受害者，我会考虑如何检查所有预期账户的余额总和是否与列中的值总和一致B。但这是另一个问题。

相关内容