Microsoft Excel

MOS Excel Expert 완전 대비 - 조건부 서식

D13 2025. 4. 13. 19:17
728x90
반응형
SMALL

1. 조건부 서식 기본 개념

기능 목적
기능 목적 조건에 따라 셀의 서식을 자동으로 적용
주로 사용하는 곳 성적표, 보고서, 일정표, 재무 분석 등
대표 형식 숫자, 텍스트, 날짜, 중복, 상위/하위 항목, 사용자 정의 수식 등

 

 

2. 조건부 서식 종류별 완전 설명 + 문제

A. 셀 강조 규칙 (Highlight Cells Rules)

규칙 설명 실전 팁
크다/작다 입력값 기준 비교 수식과 다르게 간편함
사이에 있음 범위 설정 가능 60~80점 같은 시험 점수
텍스트 포함 "지각", "미납" 등 포함 여부 SEARCH 함수로도 가능
날짜 오늘, 어제, 다음 주 등 기준 날짜 자주 출제됨
중복/고유 동일 데이터 식별 중복 사번, 이메일 등

 

 

📝 예시 문제 A

 
문제 A-1 (기초)
"성적" 열에서 80점 미만인 셀을 붉은 배경, 흰색 글씨로 표시하세요.

✔ 풀이 방법:

  1. "성적" 열(예: B2:B7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [셀 강조 규칙] → [작다]**를 선택합니다.
  3. 80을 입력하고 서식 버튼을 클릭합니다.
  4. 서식에서 채우기 탭을 선택해 붉은 배경을 설정하고, 글꼴 탭에서 흰색 글씨로 설정합니다.
  5. 확인을 클릭하여 적용합니다.

 
문제 A-2 (기초)
"납기일" 열에서 오늘 이후 날짜인 셀을 파란색 테두리로 표시하세요.

✔ 풀이 방법:

  1. "납기일" 열(예: C2:C7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [새 규칙]**을 선택합니다.
  3. **[수식을 사용하여 서식을 지정할 셀 결정]**을 클릭하고, 아래 수식을 입력합니다:
  4. 복사편집
    =C2>TODAY()
  5. 서식 버튼을 클릭하고, [테두리] 탭에서 파란색 외곽선을 설정합니다.
  6. 확인을 클릭하여 적용합니다.

 
문제 A-3 (중급)
"상태" 열에 "지각"이라는 단어가 포함된 셀을 노란색 배경으로 강조하세요.

✔ 풀이 방법:

  1. "상태" 열(예: D2:D7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [셀 강조 규칙] → [텍스트 포함]**을 선택합니다.
  3. **"지각"**을 입력하고, 서식을 클릭합니다.
  4. 채우기 탭에서 노란색 배경을 설정하고 확인을 클릭합니다.

 
문제 A-4 (기출 스타일)
고객 목록에서 이메일 주소가 중복된 항목을 강조하세요.

✔ 풀이 방법:

  1. 이메일 주소가 있는 열(예: E2:E7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [셀 강조 규칙] → [중복값]**을 선택합니다.
  3. 중복된 값을 강조할 스타일을 선택하고 확인을 클릭합니다.
반응형

B. 상위/하위 규칙 (Top/Bottom Rules)

규칙 설명 예시
상위 N개 상위 몇 개 값 강조 매출 상위 3개 제품
하위 % 전체 중 하위 퍼센트 하위 10% 성적
평균 기준 평균보다 큼/작음 이상, 미달 제품 필터링

 

 

📝 예시 문제 B

문제 B-1 (중급)
"매출" 열에서 상위 5개 제품을 굵은 글씨, 연두색 배경으로 강조하세요.

✔ 풀이 방법:

  1. "매출" 열(예: F2:F7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [상위/하위 규칙] → [상위 항목]**을 선택합니다.
  3. 상위 5개를 선택하고, 서식 버튼을 클릭합니다.
  4. 글꼴 탭에서 굵은 글씨를 선택하고, 채우기 탭에서 연두색 배경을 설정합니다.
  5. 확인을 클릭하여 적용합니다.

 
 
문제 B-2 (기출)
"평균 점수" 열에서 평균보다 낮은 항목을 회색 글씨로 표시하세요.

✔ 풀이 방법:

  1. "평균 점수" 열(예: G2:G7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [셀 강조 규칙] → [평균보다 작음]**을 선택합니다.
  3. 서식 버튼을 클릭하여 글꼴 탭에서 회색 글씨로 설정합니다.
  4. 확인을 클릭하여 적용합니다.

 
문제 B-3 (기출 스타일)
"수익률" 열에서 하위 10% 항목을 진한 빨간 배경으로 강조하세요.

✔ 풀이 방법:

  1. "수익률" 열(예: H2:H7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [하위 %]**를 선택합니다.
  3. **하위 10%**를 선택하고, 서식 버튼을 클릭합니다.
  4. 채우기 탭에서 진한 빨간 배경을 설정하고 확인을 클릭합니다.

 

C. 시각화 서식 (데이터 막대, 색조, 아이콘)

종류 설명 시험포인트
데이터 막대 값의 크기를 막대로 표시 셀 내용 숨김 가능 여부 주의
색조 값 크기에 따라 색 점진적 변화 고/저 값의 색상 선택 가능
아이콘 집합 조건에 따라 아이콘 표시 조건 설정에서 "아이콘만 표시" 옵션 자주 등장

 

SMALL

📝 예시 문제 C

문제 C-1 (기출)
"총점" 열에 데이터 막대를 적용하여 점수에 따라 막대 길이가 달라지도록 하세요.

✔ 풀이 방법:

  1. "총점" 열(예: I2:I7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [데이터 막대]**를 선택합니다.
  3. 원하는 색상이나 스타일을 선택하여 적용합니다.

 
 
문제 C-2 (고급)
"실적" 열에 대해 **빨강(최저) → 노랑(중간) → 초록(최고)**으로 색 조정을 하세요.

✔ 풀이 방법:

  1. "실적" 열(예: J2:J7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [색조] → [3색 스케일]**을 선택합니다.
  3. 색상을 빨강, 노랑, 초록으로 설정하여 적용합니다.

 
문제 C-3 (기출)
"증감률" 열에 아이콘 집합을 적용하여 다음과 같이 표시하세요:

  • 10% 이상: ▲
  • -10% 이하: ▼
  • 그 외: ●

✔ 풀이 방법:

  1. "증감률" 열(예: K2:K7)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [아이콘 집합]**을 선택합니다.
  3. 아이콘 집합에서 **"기타 규칙"**을 선택하고, 규칙 편집에서 조건을 설정합니다:
    • 10% 이상: ▲
    • -10% 이하: ▼
    • 그 외: ●
  4. 설정 후 확인을 클릭하여 적용합니다.

 

D. 수식을 사용한 조건부 서식 (사용자 정의 수식)

기능 설명 포인트
자유로운 논리식 =A2>100, =A2="지각" 등 행 전체 서식에 자주 활용
날짜 조건 =TODAY()-A2>=7 등 TODAY(), NOW() 등
텍스트 검색 =SEARCH("연체", A2) 또는 =ISNUMBER(...) 부분 문자열 포함 조건

 

📝 예시 문제 D

문제 D-1 (기출)
"상태" 열이 "불합격"일 경우 해당 행 전체에 회색 배경을 적용하세요.

✔ 풀이 방법:

  1. 상태 열을 포함한 전체 범위(A2:E100)을 선택합니다. (여기서 A2:E100은 예시로, 실제 범위에 맞게 선택)
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [새 규칙]**을 선택합니다.
  3. 새 규칙 창에서 **[수식을 사용하여 서식을 지정할 셀 결정]**을 클릭합니다.
  4. 수식 입력란에 아래 수식을 입력합니다:
    • =$C2="불합격"
      =$C2      : 상태 열의 "불합격"을 기준으로 하며, 2행부터 시작되는 범위에서 "불합격"을 찾습니다.
  5. 서식 버튼을 클릭하여, 채우기 탭에서 회색 배경을 설정하고 확인을 클릭합니다.
  6. 확인을 클릭하여 규칙을 적용합니다.

 
 
문제 D-2 (고급)
"마감일"이 7일 이상 지난 경우 셀을 진한 주황색으로 강조하세요.

✔ 풀이 방법:

  1. "마감일" 열(예: A2:A100)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [새 규칙]**을 선택합니다.
  3. 새 규칙 창에서 **[수식을 사용하여 서식을 지정할 셀 결정]**을 클릭합니다.
  4. 수식 입력란에 아래 수식을 입력합니다:
    • =TODAY()-A2>=7
      **TODAY()**는 오늘 날짜를 반환하며, A2는 마감일이 입력된 셀입니다. 오늘 날짜와 마감일의 차이가 7일 이상이면 조건이 맞춰집니다.
  5. 서식 버튼을 클릭하고 채우기 탭에서 진한 주황색 배경을 선택합니다.
  6. 확인을 클릭하여 적용합니다.

 
문제 D-3 (기출)
"비고" 열에 "경고"라는 텍스트가 포함된 경우 빨간 배경 처리하세요.

✔ 풀이 방법:

  1. "비고" 열(예: D2:D100)을 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [새 규칙]**을 선택합니다.
  3. 새 규칙 창에서 **[수식을 사용하여 서식을 지정할 셀 결정]**을 클릭합니다.
  4. 수식 입력란에 아래 수식을 입력합니다:
    • =ISNUMBER(SEARCH("경고",D2))
      **SEARCH("경고", D2)**는 "경고"라는 텍스트를 "비고" 열에서 찾고, ISNUMBER 함수는 텍스트가 발견되면 숫자(위치 값)를 반환합니다. 즉, "경고"라는 단어가 포함되면 조건이 참이 됩니다.
  5. 서식 버튼을 클릭하여 채우기 탭에서 빨간 배경을 설정하고 확인을 클릭합니다.

 
 
문제 D-4 (기출)
"지출액"이 예산(B열)보다 많은 경우 강조 표시하세요.

✔ 풀이 방법:

  1. 지출액 열(C열)과 예산 열(B열) 범위(C2:C100과 B2:B100)를 선택합니다.
  2. 상단 메뉴에서 **[홈] → [조건부 서식] → [새 규칙]**을 선택합니다.
  3. 새 규칙 창에서 **[수식을 사용하여 서식을 지정할 셀 결정]**을 클릭합니다.
  4. 수식 입력란에 아래 수식을 입력합니다:
    • C2>B2
      C2 > B2      는 "지출액"이 예산보다 많은 경우에만 해당 셀을 강조합니다.
  5. 서식 버튼을 클릭하고 원하는 배경색 또는 글꼴 색상을 설정하여 강조 표시를 합니다.
  6. 확인을 클릭하여 적용합니다.
728x90

3. 실전 종합 연습 문제 (시험 스타일)

💯 실전 문제 E-1

다음 조건에 따라 조건부 서식을 적용하세요:

  • "진행상태" 열이 "완료"이면 행 전체 녹색 배경
  • "납기일"이 오늘보다 과거이면 빨간 테두리
  • "매출" 상위 3개 항목 굵은 글씨, 진한 파랑색
  • "할인율"이 20% 이상이면 ▲ 아이콘 표시

✔ 풀이 방법:

  1. 진행상태 열(예: B2:B100) 선택 → [수식을 사용하여 서식을 지정할 셀 결정] → 수식:
    • =$B2="완료"
    • 서식에서 녹색 배경을 설정합니다.
  2. 납기일 열(예: C2:C100) 선택 → [수식을 사용하여 서식을 지정할 셀 결정] → 수식:
    • =C2<TODAY()
    • 서식에서 빨간 테두리를 설정합니다.
  3. 매출 열(예: D2:D100) 선택 → [상위/하위 규칙] → 상위 3개 항목 → 굵은 글씨, 진한 파랑색 설정
  4. 할인율 열(예: E2:E100) 선택 → [아이콘 집합] → ▲ 아이콘 (20% 이상)

💯 실전 문제 E-2

다음 조건을 충족하도록 조건부 서식을 설정하세요:

  • "이름" 열에 "김"으로 시작하는 사람은 셀 색 노란색
  • =LEFT(A2,1)="김"
  • "총점"이 0점인 경우 셀 텍스트 색 빨강
  • "출결상태" 열이 비어 있는 셀은 회색 배경 처리
  • =ISBLANK(D2)

✔ 풀이 방법:

  1. 이름 열(예: A2:A100) 선택 → [수식을 사용하여 서식을 지정할 셀 결정] → 수식:
    • =LEFT(A2,1)="김"
    • 서식에서 노란색 배경을 설정합니다.
       
  2. 총점 열(예: F2:F100) 선택 → [셀 강조 규칙] → 평균보다 작음 선택 → 서식에서 빨간 글씨 설정
  3. 출결상태 열(예: G2:G100) 선택 → [수식을 사용하여 서식을 지정할 셀 결정] → 수식:
    • =ISBLANK(G2)
    • 서식에서 회색 배경을 설정합니다.

 

💯 실전 문제 E-3 (기출 응용)

"월별 매출" 데이터를 기반으로 다음 조건을 적용하세요:

  • 가장 높은 월은 녹색 글씨, 가장 낮은 월은 붉은 배경
  • "증가율"이 -10% 이하면 ▼ 아이콘, 10% 이상이면 ▲ 아이콘
  • "계약 만료일"이 30일 이내면 행 전체 주황색 배경
  • =DATEDIF(TODAY(),F2,"d")<=30

✔ 풀이 방법:

  1. 월별 매출 열(예: A2:A100) 선택 → [상위/하위 규칙] → 가장 높은 값은 녹색 글씨, 가장 낮은 값은 붉은 배경 설정
  2. 증가율 열(예: B2:B100) 선택 → [아이콘 집합] → ▼ 아이콘 (-10% 이하), ▲ 아이콘 (10% 이상) 설정
  3. 계약 만료일 열(예: C2:C100) 선택 → [수을 사용하여 서식을 지정할 셀 결정] → 수식:
    • = DATEDIF(TODAY(), C2, "d")<=30
    • 서식에서 주황색 배경을 설정합니다.

 

정리 요약표 (암기용)

조건부 서식 유형 자주 출제 난이도
셀 강조 규칙 O ★☆☆☆☆
상위/하위 규칙 O ★★☆☆☆
색조/아이콘 O ★★☆☆☆
수식 사용 ★★★★☆ ★★★★☆

 

728x90
반응형
LIST