☰

使用 Excel 汇总贷方和借方类别列以及单个值列

tag-icon • tag-icon microsoft-excel pivot-table
使用 Excel 汇总贷方和借方类别列以及单个值列

我正在制作一份个人工作表，总结我在山寨币领域的冒险经历。我想记录以下内容：

  • 我在每个交易所存入了多少真钱
  • 我钱包里的当前山寨币数量
  • 我当前持有的山寨币的平均价格
  • 每笔交易的当前余额（即实际余额）

我会做这样的事（在此处操作 SQL)：

  • 每个操作只有一行，标识其 ID 号以及发生的时间；
  • [借方]/[贷方]栏表示钱从哪里来/到哪里去；
  • [数量] 列将保存 [信用] 货币的数量
  • [价值] 栏始终为金钱
  • [描述] 列仅用于说明

在此处输入图片描述

我的最终目标是： 在此处输入图片描述

在哪里：

  • [蓝色] 来自数据透视表的列
  • [橙色] 列需要手动输入，或者使用 VLOOKUP 查找其他地方
  • [绿色] 列是标题公式

我很难想出一个解决方案，因为我不知道什么可以等同于联盟在 Excel 中，因为我需要以不同的方式计算那些贷方/借方列。

你们能给我指明正确的方向吗？

答案1

我将您的数据复制到名为data：

  1. 使用“数据”>“获取和转换数据”>“从表/范围”创建名为“数据”的查询
  2. 复制在步骤 1 中创建的查询（右键单击，复制）以创建名为“debit”的查询
  3. 将借记查询中的 DEBIT 列重命名为 Account
  4. 从借记查询中删除了 CREDIT 列
  5. 借记查询中数量和价值的符号反转
  6. 在借记查询中添加了一个名为 Type 的列，其值为 DEBIT
  7. 将数据查询复制为名为“credit”的查询
  8. 从信用查询中删除了借方列
  9. 将信用查询中的 CREDIT 列重命名为 Account
  10. 在信用查询中添加了一个名为 Type 的列，其值为 CREDIT
  11. 使用主页>合并>附加查询>将查询附加为新查询将借记和贷记查询附加在一起（这实现了您的UNION）
  12. 将新的附加查询重命名为 DebitCredit
  13. 在 DebitCredit 查询中添加了一个名为 Rate 的自定义列，并使用以下公式：if [Type] = "CREDIT" then [Value]/[Qty] else null

生成的查询（左侧列出了各个查询）：

在此处输入图片描述

使用“主页”>“关闭并加载”将查询以正确的格式放回数据透视表中，以便跨帐户进行数据透视和轻松聚合。

如果使用 Ctrl+T 将数据区域转换为表格并使用“表设计”选项卡对其进行命名data，则可以在 Power Query 编辑器中的高级编辑器中使用它来创建查询data：

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Date", type datetime}, {"Exchange", type text}, {"DEBIT", type text}, {"CREDIT", type text}, {"Qty", type number}, {"Value", Int64.Type}})
in
    #"Changed Type"

这是为了创建debit（不可否认可能更干净一些）：

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Date", type datetime}, {"Exchange", type text}, {"DEBIT", type text}, {"CREDIT", type text}, {"Qty", type number}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "type", each "DEBIT"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CREDIT"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DEBIT", "ACCOUNT"}, {"Qty", "QTY_ORIG"}, {"Value", "VALUE_ORIG"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "qty", each [QTY_ORIG]*-1),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Value", each [VALUE_ORIG]*-1),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"QTY_ORIG", "VALUE_ORIG"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Order #", "Date", "Exchange", "ACCOUNT", "qty", "Value", "type"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"qty", "Qty"}, {"type", "Type"}, {"ACCOUNT", "Account"}})
in
    #"Renamed Columns1"

这将创建credit：

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order #", Int64.Type}, {"Date", type datetime}, {"Exchange", type text}, {"DEBIT", type text}, {"CREDIT", type text}, {"Qty", type number}, {"Value", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"DEBIT"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"CREDIT", "ACCOUNT"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Type", each "CREDIT"),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"ACCOUNT", "Account"}})
in
    #"Renamed Columns1"

这是为了创建DebitCredit：

let
    Source = Table.Combine({credit, debit}),
    #"Added Custom" = Table.AddColumn(Source, "Rate", each if [Type] = "CREDIT" then [Value]/[Qty] else null)
in
    #"Added Custom"

你可以阅读这里了解如何使用高级编辑器（您可以将上面的查询粘贴到其中）从头开始创建查询。

如您所见，数据透视表计算正确，没有太多麻烦：

在此处输入图片描述

至少我认为是这样的！不过和你的桌子不匹配 :)

您可以考虑仅将 CREDIT/DEBIT 作为每行的属性来组织源数据，并避免一些麻烦。

相关内容