Excel: как выделить значение, если оно больше или меньше последнего непустого значения предыдущего столбца/строки

Question

У меня есть электронная таблица данных об автомобиле с тремя столбцами для записи показаний одометра.

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. Строка > 2, потому что любое число будет меньше буквального текста «Окончание», который вы получите, если будете рассматривать только строки выше 2.
2. Ячейка не пустая.

Примечание: Вам не нужны никакие формулы в вспомогательном столбце, как показано в столбце N на снимке экрана. Я просто оставил это там, чтобы вы могли видеть, как работает формула в условном форматировании.