Excel 조건부 서식 7개: 납기 지난 3행과 텍스트·비율 조건 검산하기
납기일이 지난 행을 칠했는데 다른 행까지 같은 색이 되는 경우에는 먼저 적용 범위와 수식의 행 번호를 함께 봐야 합니다. 이번 실습은 거래 5행의 납기·재고·중복 이름을 분리하고, 텍스트와 비율 예제까지 같은 파일에서 확인합니다. 특정 회사에서 검증한 경력이나 시간 절감 효과를 근거로 내세우지 않습니다.
대표 이미지는 본문의 가상 거래 5행과 기준일 2026-06-29에서 납기 지난 2·4·6행이라는 기대 대상을 요약한 설명용 도해입니다. AI로 제작했으며 실제 Excel 화면이나 실행 검증 기록이 아닙니다. 그림의 노란 강조는 설명용 표시이며 본문 규칙은 연한 빨강 채우기입니다.
첨부 practice-104.xlsx에는 실제 조건부 서식 규칙 7개가 들어 있습니다. 색만 손으로 넣은 샘플이 아니라 입력이 바뀌면 규칙이 다시 평가되는 구조입니다. Microsoft Excel 앱에서 직접 조작한 기록은 없으며, 파일 구조·수식 값·LibreOffice 렌더를 확인한 범위를 아래에 구분합니다.
1. 날짜가 바뀌어도 같은 답을 비교할 입력
행강조 A1:E6의 가상 자료를 사용하고 H2를 날짜 2026-06-29로 고정합니다. TODAY를 쓰면 열어 본 날짜에 따라 정답이 바뀌기 때문에 이번 검산에서는 사용하지 않습니다.
| 거래처 | 담당자 | 주문수량 | 재고 | 납기일 |
|---|---|---|---|---|
| 한빛상사 | 김대리 | 120 | 8 | 2026-06-25 |
| 다온유통 | 이과장 | 60 | 45 | 2026-07-02 |
| 세종물산 | 박사원 | 200 | 5 | 2026-06-28 |
| 미래테크 | 김대리 | 30 | 120 | 2026-07-10 |
| 한빛상사 | 정대리 | 90 | 0 | 2026-06-26 |
조건부 서식은 셀 값에 따라 형식을 정하는 규칙을 사용합니다. F열의 일반 수식으로 납기 판정을 먼저 읽고, 그 논리와 실제 행의 색이 맞는지 비교하는 방식으로 진행합니다.
2. A2:E6의 납기 강조와 D2:D6의 부족 재고
납기 규칙의 적용 범위는 $A$2:$E$6, 수식은 다음과 같습니다. 참인 행을 연한 빨강 채우기로 표시합니다.
=AND(ISNUMBER($E2),$E2<$H$2)E열은 고정하고 행 번호는 상대 참조로 둡니다. 범위의 첫 행이 2이므로 수식도 $E2에서 시작합니다. H2는 모든 행이 같은 기준일을 보도록 완전히 고정합니다. F2의 확인용 일반 수식은 다음과 같으며 F6까지 채워져 있습니다.
=IF(AND(ISNUMBER(E2),E2<$H$2),"지남","유지")F2·F4·F6만 ‘지남’이고 이에 대응하는 A2:E2·A4:E4·A6:E6이 강조 대상입니다. 수식의 $E2를 $E$2로 잘못 고정하면 모든 행이 첫 납기일을 보게 됩니다. 그것을 색 우선순위 문제로 오인하지 말고 먼저 참조를 복구합니다.
재고는 D2:D6에서 아래 조건에 굵은 갈색 글씨를 지정했습니다. 행 채우기와 다른 속성을 쓰도록 구성했지만, 이번 LibreOffice 렌더에서는 겹친 글꼴 서식이 함께 표시되지 않았습니다. 파일의 규칙과 G열 판정을 확인하되 Excel에서의 겹친 서식 표시는 별도로 확인해야 합니다.
=AND(ISNUMBER(D2),D2<10)D2=8, D4=5, D6=0이 대상이고 G열 판정은 ‘부족’입니다. 숫자 10은 미만 조건에 포함되지 않습니다. 빈 셀을 재고 0이라고 단정하지 않도록 숫자 여부도 검사합니다.
3. 중복 이름과 주문량 막대를 별도로 읽기
A2:A6에는 다음 규칙을 적용해 중복 거래처를 파란색 굵은 글씨로 표시합니다.
=COUNTIF($A$2:$A$6,A2)>1A2와 A6의 한빛상사가 대상입니다. 중복 거래처는 거래가 중복 입력됐다는 뜻과 같지 않습니다. 날짜나 거래 번호 확인 없이 두 행 중 하나를 삭제하지 마세요.
C2:C6에는 데이터 막대가 있습니다. 이 가상 입력의 최소는 30, 최대는 200입니다. 막대 길이는 주문량 비교용이며 부족 재고나 납기 지연을 판정하지 않습니다. 색조·아이콘 규칙은 이 첨부에 넣지 않았습니다. 모든 표시가 구현돼 있다고 기대하지 않고 실제 7개 규칙을 점검합니다.
4. 텍스트 ‘지연’과 ‘재검토’는 의미가 아니라 문자열 검사
텍스트강조 A2:A7에는 배송 지연, 입금 완료, 지연 확인 중, 정상 처리, 지 연, DELAY가 있습니다. 규칙 =ISNUMBER(SEARCH("지연",A2))는 A2와 A4를 노란색으로 표시합니다. D2:D7의 재검토·승인·보류·재검토 요청·완료·재 검토에는 =ISNUMBER(SEARCH("재검토",D2))를 적용해 D2와 D5를 표시합니다.
SEARCH는 찾는 문자열이 없으면 오류를 반환합니다. ISNUMBER로 감싸서 위치 숫자를 찾은 경우만 참으로 사용합니다. ‘지 연’과 ‘재 검토’는 중간 공백 때문에 이번 문자열과 다릅니다. ‘지연 없음’을 입력하면 지연이라는 글자가 있어 강조되므로, 색만 보고 실제 지연 상태라고 결론 내리면 안 됩니다.
각 범위 옆의 일반 수식 판정 C열·F열과 고정 기대값 B열·E열을 비교합니다. 이 검산은 문자열 대응을 확인하며 문장의 의미를 이해하는 분류기가 아닙니다.
5. 80% 미만과 빈칸을 분리하기
숫자강조 A2:A7은 65%, 80%, 95%, 40%, 빈칸, 100%이고 기준 D2는 80%입니다. A2:A7의 규칙은 다음과 같습니다.
=AND(ISNUMBER(A2),A2<$D$2)A2와 A5만 강조됩니다. 80%는 미만이 아니고 빈 셀은 숫자가 아니므로 제외됩니다. '65%처럼 텍스트로 저장된 비율도 이 실습의 숫자 계약 밖입니다. 색이 안 보인다고 임계값을 바꾸기 전에 입력 자료형을 확인하세요.
6. 규칙 관리자에서 복구할 순서와 화면 검증 한계
첨부 사본의 규칙 관리자에서 현재 시트의 적용 범위·수식·서식 속성을 함께 확인합니다. 행강조 4개, 텍스트강조 2개, 숫자강조 1개입니다. 상대 참조는 선택 범위의 각 셀에 맞춰 조정됩니다. 첫 행을 3으로 옮겼는데 수식은 계속 2행을 보고 있지 않은지 확인합니다.
H2만 6월 26일로 바꾸면 ‘기준일보다 이전’인 6월 25일의 2행만 납기 강조 대상입니다. 같은 날짜인 6행은 제외됩니다. H2를 6월 29일로 되돌려 최초 2·4·6행을 재확인합니다. 필요 없는 규칙을 지울 때에는 해당 규칙 한 개를 선택하고, 원본 데이터나 다른 시트 전체 규칙을 일괄 삭제하지 않습니다.
저장 파일의 적용 범위·실제 규칙·일반 수식 결과와 LibreOffice 시트 화면을 확인했습니다. Microsoft Excel의 규칙 관리자 메뉴, 화면색 및 붙여넣기 이후 동작까지 실행 검증한 것은 아닙니다. 업무 사본에서는 먼저 최초 대상 셀과 변경 후 대상 셀을 육안 대조하고, 문제가 생기면 보존한 사본의 규칙으로 되돌립니다.
본문과 같은 입력으로 연습하기
실습 XLSX에는 본문에서 지정한 셀과 수식이 들어 있습니다. 검산 자료 ZIP에는 같은 XLSX와 입력·수식 CSV, 실습 설명, README 및 오프라인 Python 검산 코드가 있습니다. 원본 업무 파일을 덮어쓰지 말고 사본으로 연습하세요. Microsoft Excel 앱의 실제 실행 결과로 제공하는 파일은 아닙니다.
공식 자료
Use conditional formatting to highlight information in Excel | Microsoft Support
SEARCH function | Microsoft Support
출처 원문 수집일: S1 2026-09-24; S2 2026-09-24.
작성자 소개
