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. 행 > 2입니다. 왜냐하면 모든 숫자는 2보다 큰 행만 고려할 때 얻을 수 있는 리터럴 텍스트 "Ending"보다 작기 때문입니다.
2. 셀이 비어 있지 않습니다.

참고: 스크린샷의 N열에 표시된 것처럼 도우미 열에는 수식이 필요하지 않습니다. 조건부 서식의 수식이 어떻게 작동하는지 확인할 수 있도록 그냥 거기에 두었습니다.