excel - 如何使用电子表格中前一行条目的条件格式突出显示更改？

Question

我正在尝试构建一个条件格式规则，其中当前行突出显示电子表格中前一行条目的更改（随着时间的推移可能有多个修订）。

经过一番研究，我发现以下公式返回具有相同 DASID # 的前一行（因此进行了编辑）。DASID 是对电子表格 A 列中事件 ID 的结构化引用。

这有效并返回具有相同 DAS ID 的前一行 #：

{=大（如果（DASID = A1389，行（DASID）-最小（行（DASID））+ 1），2）}

但是，因为我将此设置为条件格式规则，所以需要动态计算单元格位置。

所以更换：

DASID = A1389

和：

DASID = (ADDRESS( ROW(), 1)) 计算结果为 $A$1389
{=LARGE( IF( DASID = (ADDRESS( ROW(),1)), ROW(DASID) - MIN(ROW(DASID))+ 1),2)}

但是，替换公式会生成 #NUM！Excel 中的错误。我也尝试过用 Indirect() 封装，但没有成功。

间接（地址（行（），1））

一旦这工作，我将评估不同的列以识别更改并应用条件格式。查看我的公式的修复或使用更好的方法来突出显示具有相同 ID 值的上一个条目的更改。

编辑： Excel Snap，其中事件日期在行之间发生了变化

CSV 数据集（行 # 由 Excel 创建，但在数据集中显示条目彼此不“靠近”）

Row#,DailyActivitySheetID, Created, Modified, EventDate, Status
5,2982,11/24/2018,11/25/2018,4/6/2019,Awaiting
1286,2982,11/24/2018,2/21/2019 2019 年 5 月 2 日，等待中

标签： excel

Answer 1

似乎有几个步骤。

首先，您可能想要创建一个动态范围（命名范围将使您的最终公式语法更容易）来定义先前 DAS Id 的目标区域。有很多网站解释了如何做到这一点（使用OFFSET()or INDEX()，一个就是这个：https ://www.excel-easy.com/examples/dynamic-named-range.html 。

因此，假设您的数据从 A2 开始并且没有空白行，您可以添加一个命名范围，例如，DASIDs在其“引用到”窗口中使用此公式：

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-1,1)

请注意，这-1是要排除您要输入数据的最底部的单元格。

接下来，使用以下公式添加第二个命名范围，该范围引用最后一行 DASID（在本例中，我称之为LastDASID）：

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,1,1)

再次，-1防止公式偏移超出您的数据。

要阅读列表中的最后一项，可以使用一个众所周知的公式SUMPRODUCT()（这里有一个解释http://www.exceluser.com/formulas/last-item-in-list.htm）。

因此，您可以使用以下公式添加第三个命名范围，称为MatchedDASIDRow：

=SUMPRODUCT(MAX(行(DASIDs)*(LastDASID=DASIDs)))

因此，最后一步是在规则类型为的单元格 A2 中添加条件格式规则，Use a formula to determine which cells to format然后选择您想要的任何格式。规则描述基本上只是前三个步骤的合并：

=匹配DASIDRow=ROW(A2)

只需将条件格式拖到数据区域的所有行和列中，就可以了。

从评论更新

我认为您可能会更好地选择 VBA 解决方案，因为您的评论暗示了一个非常复杂的分析要求。但是，可以使用公式来执行此操作。诀窍在于找到先前数据的结束位置和新数据的开始位置。如果您的数据是按修改日期顺序输入的，您可以通过修改日期列区分范围。

首先获取修改列的动态范围。我使用了一个命名范围AllModifiedRange，并使用了以下公式：

=OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$C),1)

接下来定义一个公式，该公式标识修改日期在今天之前的最后一行数据。我PriorModifiedsLastRow用公式调用了这个命名范围：

=SUMPRODUCT(MAX(ROW(AllModifiedRange)*(TODAY()<>AllModifiedRange)))

这使您能够在今天之前定义 DASID 的范围。PriorDASIDs使用公式调用的命名范围：

=OFFSET(Sheet1!$A$1,0,0,PriorModifiedsLastRow,1)

现在您只需要像以前一样使用一个公式来检查该行是否与最后一行匹配。IsPriorLastRow另一个用公式调用的命名范围：

=ROW(Sheet1!$A2)=SUMPRODUCT(MAX(ROW(PriorDASIDs)*(Sheet1!$A2=PriorDASIDs)))

但是，这将突出显示最后的任何 DASID。您将需要进行第二次测试以确定 DASID 是否存在于今天修改的范围内。

所以首先定义那个范围。我使用了另一个命名范围，TodaysModifiedRange公式如下：

=OFFSET(Sheet1!$A$1,PriorModifiedsLastRow,0,COUNTA(Sheet1!$A:$A)-PriorModifiedsLastRow,1)

然后运行测试。IsInTodaysModifieds使用公式调用的命名范围：

=NOT(ISNA(匹配(Sheet1!$A2,TodaysModifiedRange,0)))

因此，您的条件格式公式只查看两个测试是否都解析为 true：

=AND(IsPriorLastRow,IsInTodaysModifieds)

正如我所说，这确实取决于按日期排序的修改范围，如果您在同一天输入相同的 DASID 两次，算法将失败。我个人会去 VBA，但如果您希望继续使用 Excel 公式并想要更大程度的复杂性，那么您需要仔细考虑您的步骤并以结构化的方式开发公式。