主页 / computer / 问题 / 1788724
Accepted
dav
Asked: 2023-06-13 04:48:54 +0800 CST

通过分离交易创建复式记账表

  • 772

我有一个 Excel 表，其中的会计交易记录存储在一行中：

日期 至账户 来自帐户 数量 类型
2023-06-01 2192 2281 100.00 美元 转移

我正在尝试将该单行转换为复式记账交易，如下所示：

日期 至账户 来自帐户 数量 类型
2023-06-01 2192 $-100.00 费用
2023-06-01 2281 100.00 美元 收入

我有数千个条目，需要定期更新（每月多次），因此我正在寻找一种系统的方法来重复该过程。到目前为止，我找到的唯一解决方案是复制记录，然后使用辅助列根据行是原始行还是副本来对数据进行分区。我愿意使用 Excel、Power Query、SSIS 或 SQL 解决方案（数据最终将存储在 SQL Server 数据库中）。我想避免使用 VBA。

microsoft-excel

2 个回答

  1. Power Query 可以很容易地做到这一点。将数据放入表中以用作源，或者可以使用外部数据源，例如 CSV 或 SQL。

    然而，您以与您的帖子类似的格式将数据导入 PQ，然后您需要确保“更改类型”步骤为每列设置合适的数据类型。删除不需要的“类型”列。

    我发现此时将“金额”重命名为“原始金额”很有用，这样我以后就可以重复使用该列名称。选择“原始金额”和“日期”，然后转到“转换”>“逆透视列”>“其他列”。

    这是在只有一行的情况下获得两行的关键步骤。此后，您需要添加一堆条件列，以将“至帐户”值放入名为“至帐户”的列中，其他列为空，反之亦然。

    新的“金额”列稍微棘手一些。您可以执行多个步骤并删除中间列，或者从头开始编写自定义公式，或者介于两者之间 - 添加一个条件列，用于查找“To”/“From”，并在两种情况下返回“Amount”列。然后在编辑栏中，只需在[原始金额]前面插入“-1 *”来编辑此部分的“从一半”。

    同样，为类型（收入或费用）添加条件列

    将金额列更改为货币数据类型（如果您愿意，也可以选择小数，但固定小数通常很有用）。删除不需要的列，以便仅保留所需的列（因此删除原始金额、属性和值）。

    关闭并加载 - 一切顺利！您的 M 代码（在高级编辑器中）最终将如下所示：

    let
        Source = Excel.CurrentWorkbook(){[Name="StartingTable"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"To Account", type text}, {"From Account", type text}, {"Amount", Int64.Type}, {"Type", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Type"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Amount", "Original Amount"}}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Original Amount", "Date"}, "Attribute", "Value"),
        #"Added To Account" = Table.AddColumn(#"Unpivoted Other Columns", "To Account", each if [Attribute] = "To Account" then [Value] else null),
        #"Added From Account" = Table.AddColumn(#"Added To Account", "From Account", each if [Attribute] = "From Account" then [Value] else null),
        #"Added Amount" = Table.AddColumn(#"Added From Account", "Amount", each if [Attribute] = "To Account" then [Original Amount] else -1 * [Original Amount]),
        #"Changed Type1" = Table.TransformColumnTypes(#"Added Amount",{{"Amount", Currency.Type}}),
        #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Type", each if [Attribute] = "To Account" then "Expense" else "Revenue"),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Date", "To Account", "From Account", "Amount", "Type"})
    in
        #"Removed Other Columns"
    • 1
  2. Best Answer

    这是使用 Power Query 的另一种方法：

    • 在原表中添加索引列
    • 从原始表创建两个表
      • “学分”
        • 将“来自帐户”条目转换为空
        • 将“金额”设为负数
        • 选择“类型”：费用
      • “借方”
        • 将“至帐户”条目更改为空
        • 选择“类型”：收入
    • 然后
      • 重新组合两个表
      • 按索引列排序
      • 删除索引列

    还可以包括基于帐号的决策以决定类型以及金额是否应该为负数。

    let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Transactions"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Date", type date}, {"To Account", Int64.Type}, {"From Account", Int64.Type}, 
        {"Amount", Currency.Type}, {"Type", type text}}),

    #"Add Index Column" = Table.AddIndexColumn(#"Changed Type","Index"),

    Credits = Table.TransformColumns(#"Add Index Column",{
        {"From Account", each null, Int64.Type},
        {"Amount", each -_, Currency.Type},
        {"Type", each "Expense", type text}}),

    Debits = Table.TransformColumns(#"Add Index Column",{
        {"To Account", each null, Int64.Type},
        {"Type", each "Revenue", type text}}),

    Results = Table.Combine({Credits,Debits}),
    #"Sorted Rows" = Table.Sort(Results,{{"Index", Order.Ascending}}),
    #"Remove Index Column" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
 
in
    #"Remove Index Column"

    在此输入图像描述

    • 1

相关问题