时间戳

首页 > 解决方案 > 如何使用 VBA 创建公式来引用单元格的动态偏移范围？

excel - 如何使用 VBA 创建公式来引用单元格的动态偏移范围？

问题描述

这是一个非常奇怪的特定需求，也是我完成新宏套件所需的最后一件事。

示例图像

注意：表格顶部的“---”代表同一份报告可以追溯到几个月前

正如您在上面链接的图片中看到的，我有两个突出显示的部分。我需要将 G 列设为上一份报告数字中 E 和 F 的总和。因为每天都会添加一组新的数据，所以我不能引用特定的单元格，它必须是动态的。这里更大的问题是我的客户数量会经常变化。它只会上升，并且始终保持相同的顺序；即使失去了客户，他们也会留在同一地点的报告中。

我关于如何完成这项工作的唯一理论是：

  1. 找到客户 A 的倒数第二个实例，并根据右侧的偏移单元定义一个 rng。我的问题是——据我所知——即使一直填写该公式也只会给我一个值。
  2. 添加=SUM((INDIRECT(ADDRESS(ROW()-5,COLUMN()-2))):(INDIRECT(ADDRESS(ROW()-5,COLUMN()-1))))到空白单元格。我的问题是偏移量中的 -5 可以更改，即使通过空白单元格的数量来定义它也会在新客户第一次出现时导致错误。

任何见解将不胜感激。如果您有任何澄清问题，请告诉我；我很乐意根据需要回答/编辑原始帖子。

标签： excelvba

解决方案

它可能可以通过实际预先计算范围来进一步优化，但天真的版本是：

=SUMIFS([Outstanding Mail],[Date],LOOKUP([@Date]-1,[Date]),[Customer],[@Customer])
+SUMIFS([Outstanding Faxes],[Date],LOOKUP([@Date]-1,[Date]),[Customer],[@Customer])

这取决于您的日期已排序这一事实，并且LOOKUP返回不大于提供的值的最后一个值，因此[@Date]-1它会查找小于提供的日期的最大日期。不适用于未排序的范围。

推荐阅读