我有一张大表，有 1300 多行，最多有 AS 列。该表包含多组职位编号，我使用条件格式在每个职位的每组行之间添加一条线。因此，第 4 行到第 18 行是职位 21-3166，第 19 行到第 25 行是职位 21-3167。我在第 17 行和第 18 行之间添加了一条线，以便直观地看到职位的划分。
现在，我在一行中有一个公式，它指示上面的行是否有不同的工作编号，如果有，则将单元格值设为 1，如果没有，则将其设为 -1。这样，条件格式就会查找不同的行（1 vs -1）并在两者之间画一条线。我使用偏移函数，这样当数据排序不同时，它就会继续查看该排序中正上方或正下方的行。
每项工作都有许多里程碑，编号为 1、2、3 等。例如，21-3166 表示每个里程碑有 3 行，因此有 6 个里程碑。
有时我会过滤数据以仅显示特定数据（它会根据我当天的需求而变化），并且我会做一些事情，例如仅显示所有工作的里程碑 3。
我的问题是里程碑 3 不是工作的顶部或底部（第 4 至 18 行），因此每个工作之间的界线不再显示。
我如何制定 1 vs -1 列以仅查看新过滤的数据？我需要动态地改变我一整天看待事物的方式。这用于跟踪多达一百个不同的工作和每个 1-12 个里程碑。
我正在使用 Office 365 谢谢！
答案1
这是一个可能的解决方案。
使用如下公式创建一个辅助列：
=IF(SUBTOTAL(103,[@Job])=1,[@Job],"")
此公式表示“计算非隐藏文本值（由小计 103) 位于该列的当前行（以 表示@）Job。如果有 1 个非隐藏文本，则该行未隐藏，因此返回当前行的作业。如果没有非隐藏文本，则该行已隐藏，因此返回空字符串”。
将该列命名为“HiddenJob”。
现在将您的 ConditionalFormatter 列更改为：
=[@HiddenJob] <>
XLOOKUP(
1,
(ROW([HiddenJob]) < ROW([@Job])) * ([HiddenJob] <> ""),
[HiddenJob],
[@Job],
,
-1
)
(ROW([HiddenJob]) < ROW([@Job])) * ([HiddenJob] <> "")创建一个由 1 和 0 组成的数组。行号小于当前行且 HiddenJob 列不为空的数组为 1。
XLOOKUP-1从最后一个到第一个搜索所述数组（在末尾表示）并在找到 1 的地方停止。这是 HiddenJob 中当前行上方的第一个非空白行。
因此，不等式是比较当前行的作业编号是否等于当前行上方第一个非隐藏行的作业编号。
未经过滤时，当职位编号发生变化时，条件格式器列会显示 TRUE，如单元格 D11 所示：
当在单元格 D14 中筛选到 Milestone=2 时：
或者即使 Milestone = 1 或 3，变化也仅在单元格 D11 中：
为了避免混淆，您可以隐藏 HiddenJob 帮助列。