Excel：如果大於或小於上一列/行的最後一個非空白值，則如何反白顯示值

Question

我有一個包含 3 列的車輛資料電子表格來記錄里程表。

1. 維修服務或加油期間的里程表
2. 里程表在開始一次旅行的
3. 里程表在結尾一次旅行的

由於人們沒有提交文件或取消了旅行，數據行之間存在差距。所以空白單元格是不可避免的。

我想使用條件格式來突出顯示開始里程表是較少的比上次旅行的結尾里程表。這將需要跳過空白單元格並找到記錄了結束里程表的最後一次行程。

我在Google表格中收到了幫助，可以使用以下公式執行此操作，但該公式在 Excel 中不起作用。

=(K9 > +sort(L$7:L8, not(isblank(L$7:L8)) * row(L$7:L8), false)) * not(isblank(K9)) * (+sort(L$7:L8, not(isblank(L$7:L8)) * row(L$7:L8), false) <> "Beginning")

有人可以幫忙嗎？

試算表樣本

Answer 1

您想找出目前行上方 L 列的最後一個非空白嗎？以下是您應放入第 2 行的公式：

=LOOKUP(2,1/($L$1:$L1<>""),$L$1:$L1)

請注意，第一個$L$1對於列和行都是絕對的，但第二個$L1對於行是相對的，因此它會在您複製/貼上時更新。第 3 行中的公式（當您透過表格複製/貼上/填充時）將自動為：

=LOOKUP(2,1/($L$1:$L2<>""),$L$1:$L2)

---

現在您知道如何執行此操作，您可以將條件格式套用於範圍內的值K2:K1000或資料末尾的任何位置：

=AND(ROW()>2,NOT(ISBLANK(K2)),K2<LOOKUP(2,1/($L$1:$L1<>""),$L$1:$L1))

我們必須新增兩個排除項：

1. Row > 2 因為任何數字都會小於文字文字“Ending”，這是當您只考慮 2 以上的行時所得到的結果。
2. 該單元格不是空白的。

注意：您不需要在輔助列中新增任何公式，如螢幕截圖中的 N 列所示。我只是將其留在那裡，以便您可以看到條件格式中的公式是如何工作的。