실무에서 가장 많이 사용되는 엑셀 - ⅶ

 

엑셀 실무에서 가장 많이 사용되는 함수와 기능: 일곱 번째 글

이번 글에서는 조건부 서식과 **데이터 유효성 검사(Data Validation)**를 활용하여 데이터를 관리하고 시각적으로 강조하는 방법을 다룹니다. 이 두 기능은 실무에서 데이터의 품질을 유지하고, 중요한 정보를 빠르게 식별하는 데 매우 유용합니다. 다양한 예제를 통해 이해를 돕겠습니다.

1. 조건부 서식

조건부 서식은 특정 조건을 만족하는 셀에 자동으로 서식을 적용하여 데이터를 강조하는 기능입니다. 이를 통해 데이터의 패턴을 한눈에 파악할 수 있습니다.

반응형

조건부 서식 적용 방법:

1. 데이터를 선택합니다.
2. 상단 메뉴에서 홈 > 조건부 서식 > 새 규칙을 클릭합니다.
3. "수식을 사용하여 서식을 결정" 옵션을 선택하고 원하는 수식을 입력합니다.
4. 서식(글꼴, 색상 등)을 설정한 후 확인을 누릅니다.

예시 1: 값 비교

• 상황: 재고 수량이 10보다 적은 제품을 빨간색으로 강조.
• 데이터:
  

  제품명	재고 수량
  스마트폰	5
  노트북	12
  태블릿	8

   
• 수식: =$B2<10
• 결과: 재고 수량이 10 미만인 스마트폰과 태블릿 행이 빨간색으로 표시됩니다.

예시 2: 다중 조건 적용

• 상황: 재고가 10 이상이고 배송 가능 상태인 제품을 녹색으로 강조.
• 데이터:
  

  제품명	재고 수량	배송 가능 여부
  스마트폰	5	불가능
  노트북	12	가능
  태블릿	8	가능

   
• 수식: =AND($B2>=10, $C2="가능")
• 결과: 노트북 행이 녹색으로 강조 표시됩니다.

728x90

2. 데이터 유효성 검사(Data Validation)

데이터 유효성 검사는 셀에 입력될 수 있는 값을 제한하거나 규칙을 설정하여 데이터의 품질을 유지하는 기능입니다.

데이터 유효성 검사 설정 방법:

1. 셀 또는 범위를 선택합니다.
2. 상단 메뉴에서 데이터 > 데이터 유효성 검사를 클릭합니다.
3. 팝업 창에서 원하는 조건(숫자, 텍스트, 날짜 등)을 설정합니다.
4. 필요하면 입력 메시지와 오류 메시지를 추가합니다.

예시 1: 숫자 범위 제한

• 상황: 직원들의 근무 시간 입력 시, 최소 40시간 이상만 허용.
• 설정 방법:
  • 조건: 숫자 > "40 이상"
• 입력 메시지: "근무 시간은 최소 40시간 이상이어야 합니다."
• 오류 메시지: "잘못된 값입니다."

예시 2: 드롭다운 목록 생성

• 상황: 제품 카테고리를 선택할 수 있도록 드롭다운 목록 제공.
• 데이터:
  • 카테고리 목록: 스마트폰, 노트북, 태블릿
• 설정 방법:
  • 조건: 목록 > "스마트폰, 노트북, 태블릿"
• 결과: 셀 클릭 시 드롭다운 메뉴가 나타나며 선택 가능.

3. 고급 데이터 유효성 검사

엑셀에서는 기본적인 데이터 제한 외에도 사용자 지정 수식을 사용하여 더 복잡한 유효성 검사를 설정할 수 있습니다.

예시 1: 고유 값만 허용

• 상황: 직원 ID는 중복될 수 없도록 설정.
• 수식: =COUNTIF($A$2:$A$100, A2)=1
• 결과: 중복된 값이 입력되면 오류 메시지가 표시됩니다.

예시 2: 특정 패턴의 텍스트만 허용

• 상황: 이메일 주소 형식만 입력 가능하도록 설정.
• 수식: =ISNUMBER(SEARCH("@", A2))
• 결과: "@" 기호가 포함되지 않은 텍스트는 입력 불가.

SMALL

4. 데이터 유효성 검사와 조건부 서식 결합

두 기능을 함께 사용하면 데이터의 품질을 유지하면서 시각적으로 강조할 수 있습니다.

예시:

• 상황: 직원들의 근무 시간이 기준(40시간 이상)을 만족하지 않을 경우 빨간색으로 표시.

1. 데이터 유효성 검사로 "40 이상" 제한 설정.
2. 조건부 서식에서 =$B2<40 수식을 적용하여 기준 미달인 셀을 빨간색으로 강조.