☰

合并 Excel 中的两个表（包括 INDIRECT 函数）并显示新表数据

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 office365
合并 Excel 中的两个表（包括 INDIRECT 函数）并显示新表数据

动态变量解释

数据在每个月末提取并以YYYY.MM.DD -- Monthly Data Obtained.xlsxExcel 计算日期范围的格式存储。

=TEXT(EOMONTH((EOMONTH(TODAY(),-1)),-1),"yyyy.mm.dd")&" -- Monthly Data Obtained.xlsx"
=TEXT(EOMONTH(TODAY(),-1),"yyyy.mm.dd")&" -- Monthly Data Obtained.xlsx"

2021.08.31 -- 月度数据获取.xlsx

2021.09.30 -- 月度数据获取.xlsx

然后我可以通过间接方式调用其中一个表；

=INDIRECT("'["&TEXT(EOMONTH((EOMONTH(TODAY(),-1)),-1),"yyyy.mm.dd")&" -- Monthly Data Obtained.xlsx"&"]Sheet1'!Table1")

合并两个表

每个表的第一列是唯一的主键，但是一个表可能有几个成员，而另一个表没有。对于新表，我想只包括两个文件中的成员，并且我希望计算两个数据列以查看该月的数据。每日和每周是我根据新数据添加的一些基本总和；

  • =[@Total]/TEXT(EOMONTH((EOMONTH(TODAY(),-1)),-1),"dd")- 将总数除以上一个月的天数。
  • =[@[Daily Avg]]*7 - 每天 7 点开始。

问题

  1. 我如何才能合并两个表并实现 INDIRECT 函数以仅显示members两个文件中都存在的所有人？
  2. 计算上述成员的价值差异？

答案1

您可以使用 Power Query 从文件中获取表格。您可以通过多种方式执行此操作，现在您已经了解了该工具，您可以对其进行一些研究。

我将通过一个示例来说明拥有这两个表后可以做什么。在我的工作簿中，我将它们命名为 Table32 和 Table33，但您可以轻松更改它。

您可能想要更改或添加代码的其他方面——这只是一些可能性的示例。

在此处输入图片描述

使用 Power Query

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

//Read in first table and get date from column 2 name
    Source = Excel.CurrentWorkbook(){[Name="Table32"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Member", type text}, {Table.ColumnNames(Source){1}, Int64.Type}}),
    dt1 = Date.From(Table.ColumnNames(Source){1}),

//read in second table and get date from column 2 name
    Source1 = Excel.CurrentWorkbook(){[Name="Table33"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source1,{{"Member", type text}, {Table.ColumnNames(Source1){1}, Int64.Type}}),
    dt2 = Date.From(Table.ColumnNames(Source1){1}),

//calculate number of days
    days = Duration.TotalDays(dt2-dt1),

//join the two tables only with names from both
    join = Table.Join(#"Changed Type","Member",#"Changed Type1","Member",JoinKind.Inner),

//compute the total by subtracting the earlier dated column from the later
//then remove the dated columns
    #"Added Custom" = Table.AddColumn(join, "Total", each [2021.09.30]-[2021.08.31]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"2021.08.31", "2021.09.30"}),

//Add Daily column = Total/number of days between the dates
//and Weekly = 7*Daily
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Daily", each [Total]/days),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Weekly", each [Daily]*7),

//set data types and reorder the columns
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom2",{{"Daily", Currency.Type}, {"Weekly", Currency.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Member", "Total", "Weekly", "Daily"})
in
    #"Reordered Columns"

在此处输入图片描述

  • 大部分代码（但不是全部）是由 UI 生成的
  • 一些代码特别引用了两个表中的日期列标题。
  • 当然可以编辑代码，让它引用特定位置的列，而不是硬编码的名称
  • 我的数学计算结果与您的屏幕截图不同。也许您计算天数的方式不同？我使用两列日期标题的差异来计算。对于您的示例，那将是30，对于 Peter，我计算每日为90/30=3，而您显示为2.89。但是一旦我理解了您使用的规则，数学计算就很容易改变。

相关内容