2018.01.29

중요한 데이터를 한번에 강조하는 엑셀 조건부 서식 사용법

JD Sartain | PCWorld
마이크로소프트 엑셀조건부 서식은 훌륭한 자동 기능이다. 셀 값이나 셀 내 공식 값을 기준으로 해당 셀의 서식을 지정하는데, 예를 들면 스프레드시트에서 5,000달러를 초과하는 매출 총액을 전부 노란색으로 강조 표시할 수 있다. 또는, 올해 이전 날짜는 모두 진한 녹색 글꼴을 사용하게 한다거나 1만 2,000이 넘는 모든 중복 값을 모양 또는 평가 아이콘으로 표시할 수 있다. 사용자와 필요에 따라 방법은 무궁무진하다. 미리 설정된 서식 이외에도 스스로의 맞춤형 서식 규칙을 만들 수 있다.

이 기능의 가장 큰 장점은 스프레드시트를 확인하거나 다른 사람에게 보여줄 때 스냅샷을 빠르게 제공한다는 점이다. 값을 기준으로 서식이 정해지므로 데이터를 업데이트해 주기만 하면 된다.

A. 해당 값 조건을 충족하는 셀의 서식 지정
1. 매출 스프레드시트를 열어 2017년의 첫 4개월(1월, 2월, 3월, 4월)에 열 몇 명의 이름과 매출 총액을 입력한다.
2. 매출 총액의 1월 열을 강조 표시한다.
3. [홈] > [조건부 서식] > [셀 강조 규칙] > [보다 큼]을 선택한다.
4. [보다 큼] 대화 상자가 표시된다. [다음 값보다 큰 셀의 서식 지정] 밑의 필드 상자에 숫자(이 예시의 경우 5,000)를 입력한 후 [확인]을 클릭한다.
5. B열의 매출 총액 중 5,000달러가 넘는 것은 모두 진한 녹색 글꼴에 연한 녹색으로 강조 표시되는 것을 확인한다.


6. 위의 방법을 다시 이용해 5,000달러 미만의 셀을 강조 표시한 후 서식 시정한다.


7. 값에 적용되는 다른 옵션으로는 두 값 사이에 있는 셀 서식 지정, 특정 값과 같은 셀의 서식 지정, 중복 또는 고유 값을 포함하는 셀의 서식 지정 등이 있다. 3월 열과 4월 열의 강조 표시된 셀을 확인해 보라.


B. 특정 텍스트를 포함하는 셀의 서식 지정
조건부 서식 기능을 이용해 특정 텍스트(문자, 단어, 구 등)를 강조할 수도 있다.

1. 이름의 첫 열(A)을 강조 표시한다.
2. [홈] > [조건부 서식] > [셀 강조 규칙] > [텍스트 포함]을 선택한다.
3. [텍스트 포함] 대화 상자가 표시된다. [다음 텍스트를 포함하는 셀의 서식 지정] 밑의 필드 상자에 강조하고자 하는 텍스트(예: 홍길동)를 입력한 후 [확인]을 클릭한다.
4. 홍길동 이름은 모두 진한 빨강 글꼴에 연한 빨강으로 강조 표시되는 것을 확인한다.


C. 특정 범위의 날짜를 포함하는 셀의 서식 지정
1. F열에 날짜를 입력한다.
2. [홈]> [조건부 서식]>[셀 강조 규칙]>[발생 날짜]를 선택한다.
3. [발생 날짜] 대화 상자가 표시된다. [다음 발생 날짜를 포함하는 셀의 서식 지정] 아래의 필드 상자에 오늘, 어제, 내일, 이번 달, 다음 달 등의 날짜 범위를 입력한 다음 [확인]을 클릭한다.
4. 4월의 모든 날짜가 이제 진한 빨강 글꼴에 연한 빨강으로 강조 표시되는 것을 볼 수 있다.


D. 상위/하위 규칙
다음은 상위/하위 규칙이다. 상위 10개 항목, 상위 10%, 하위 10개 항목, 하위 10%, 평균 초과, 평균 미만 항목에 대해 서식을 지정할 수 있다. 상위 및 하위 백분율과 상위 및 하위 항목이 무엇인지는 이름만 들어도 알 수 있을 것이다. 강조 표시된 범위마다 최대값 또는 최소값, 최대 백분율과 최소 백분율의 서식이 지정된다.

1. B열을 강조 표시한다.
2. [홈] > [조건부 서식] > [셀 강조 규칙] > [상위/하위 규칙] > [상위 10개 항목]을 선택한다.
3. [상위 10개 항목] 대화 상자가 표시된다. 기본값이 10으로 지정되어 있고 적용할 서식을 묻는다. [진한 빨강 텍스트가 있는 연한 빨강 채우기]를 선택한 다음 [확인]을 클릭한다.
4. 다음으로는 C열을 강조 표시한 후 위의 방법에 따라 하위 10개 항목의 서식을 지정한다.


5. D열을 강조 표시한다.
6. [홈] > [조건부 서식] > [셀 강조 규칙] > [상위/하위 규칙] > [상위 10%]를 선택한다.
7. [상위 10%] 대화 상자가 표시된다. 기본값이 10으로 지정되어 있고 적용할 서식을 묻는다. [진한 노랑 텍스트가 있는 노랑 채우기]를 선택한 다음 [확인]을 클릭한다.
8. 다음으로는 E열을 강조 표시한 후 위의 방법에 따라 하위 10%의 서식을 지정한다.
참고: 상위 및 하위 항목에는 1에서 9999 사이의 아무 숫자나 입력할 수 있다. 상위 및 하위 백분율에는 1에서 999 사이의 아무 숫자나 입력할 수 있다.


9. 평균 초과 및 평균 미만 옵션은 다음과 같이 계산된다. 강조 표시된 열의 값을 평균 내어 “평균” 값을 결정한다. B 열에서 평균은 $3231이다. C열에서 평균은 5523.50이다. 이 값들은 =AVERAGE(B2:B13) 및/또는 =AVERAGE(C2:C13) 명령을 사용해 확인할 수 있다.
10. B열은 평균 초과 서식에, C열은 평균 미만 서식에 강조 표시한다.
11. 위의 방법에 따라 각 열에 대한 서식을 선택한다.


E. 데이터 막대와 색조
데이터 막대와 색조는 스프레드시트에 의미 있는 시각적 효과를 더할 수 있는 서식 도구이다. 예를 들어, 데이터 막대는 값을 비교한다. 당연히 가장 긴 막대는 최대값을 나타내고 가장 짧은 막대는 최소값을 나타낸다. 반대로, 색조는 최대값, 최소값, 중간 값 사이의 범위를 보여 준다.

1. B열을 강조 표시한 후 [조건부 서식] > [데이터 막대]에서 그라데이션 채우기 데이터 막대를 선택한다.
2. C열을 강조 표시한 후 [조건부 서식] > [데이터 막대]에서 단색 채우기 데이터 막대를 선택한다.
3. 데이터 막대가 각 셀 내에서 수평 막대 차트 기능을 하는 것을 볼 수 있다. 데이터 막대를 더 잘 보려면 열 너비를 확장한다.
4. D열을 강조 표시한 후 [조건부 서식] > [색조]에서 색조를 선택한 다음 E열을 강조 표시한 후 다른 색조를 선택한다.
5. 숫자가 달라질 때 색상이 어떻게 변하는지 유의해 본다. D열(3월)과 E열(4월) 간의 차이도 유의해 본다.


아이콘 집합 역시 최대값, 최소값, 중간 값의 범위를 보여 준다.

1. 예를 들면, B열을 강조 표시하고 [홈] > [조건부 서식] > [아이콘 집합] > [모양]을 선택한 다음 목록 상 첫 점 집합을 선택한다. 목록 상의 숫자를 평균 내서 1000대와 2000대 숫자들은 모두 빨간 점으로 표시하고 3000대와 4000대 숫자들은 노란 점, 5000대 숫자들은 녹색 점으로 표시한 것을 볼 수 있다.
2. C열과 D열을 강조 표시한 후 나머지 세 개의 집합 가운데 하나에서부터 한 가지의 아이콘 집합을 선택한다. 아이콘 집합 내에서 값들이 아이콘 세 개, 아이콘 네 개, 아이콘 다섯 개로 어떻게 묶이는지 차이점을 유의해서 본다.


참고: 이 그룹에 있는 모든 아이콘 집합(방향, 모양, 표시, 점수)은 3단계, 4단계, 5단계의 서식을 제공한다. 이는 다시 최대값, 최소값, 중간 값을 결정한다. 필요에 더욱 정확히 맞도록 서식 단계를 쉽게 변경할 수 있다.

여기까지 왔으면 이제 정의한 규칙을 이용해 조건부 서식 기능 전체를 사용자 정의할 수 있다. 이러한 규칙은 엑셀의 “단계별 설명” 메뉴를 통해 쉽게 만들 수 있는데, 다음 기사에 다루도록 한다. editor@itworld.co.kr  


2018.01.29

중요한 데이터를 한번에 강조하는 엑셀 조건부 서식 사용법

JD Sartain | PCWorld
마이크로소프트 엑셀조건부 서식은 훌륭한 자동 기능이다. 셀 값이나 셀 내 공식 값을 기준으로 해당 셀의 서식을 지정하는데, 예를 들면 스프레드시트에서 5,000달러를 초과하는 매출 총액을 전부 노란색으로 강조 표시할 수 있다. 또는, 올해 이전 날짜는 모두 진한 녹색 글꼴을 사용하게 한다거나 1만 2,000이 넘는 모든 중복 값을 모양 또는 평가 아이콘으로 표시할 수 있다. 사용자와 필요에 따라 방법은 무궁무진하다. 미리 설정된 서식 이외에도 스스로의 맞춤형 서식 규칙을 만들 수 있다.

이 기능의 가장 큰 장점은 스프레드시트를 확인하거나 다른 사람에게 보여줄 때 스냅샷을 빠르게 제공한다는 점이다. 값을 기준으로 서식이 정해지므로 데이터를 업데이트해 주기만 하면 된다.

A. 해당 값 조건을 충족하는 셀의 서식 지정
1. 매출 스프레드시트를 열어 2017년의 첫 4개월(1월, 2월, 3월, 4월)에 열 몇 명의 이름과 매출 총액을 입력한다.
2. 매출 총액의 1월 열을 강조 표시한다.
3. [홈] > [조건부 서식] > [셀 강조 규칙] > [보다 큼]을 선택한다.
4. [보다 큼] 대화 상자가 표시된다. [다음 값보다 큰 셀의 서식 지정] 밑의 필드 상자에 숫자(이 예시의 경우 5,000)를 입력한 후 [확인]을 클릭한다.
5. B열의 매출 총액 중 5,000달러가 넘는 것은 모두 진한 녹색 글꼴에 연한 녹색으로 강조 표시되는 것을 확인한다.


6. 위의 방법을 다시 이용해 5,000달러 미만의 셀을 강조 표시한 후 서식 시정한다.


7. 값에 적용되는 다른 옵션으로는 두 값 사이에 있는 셀 서식 지정, 특정 값과 같은 셀의 서식 지정, 중복 또는 고유 값을 포함하는 셀의 서식 지정 등이 있다. 3월 열과 4월 열의 강조 표시된 셀을 확인해 보라.


B. 특정 텍스트를 포함하는 셀의 서식 지정
조건부 서식 기능을 이용해 특정 텍스트(문자, 단어, 구 등)를 강조할 수도 있다.

1. 이름의 첫 열(A)을 강조 표시한다.
2. [홈] > [조건부 서식] > [셀 강조 규칙] > [텍스트 포함]을 선택한다.
3. [텍스트 포함] 대화 상자가 표시된다. [다음 텍스트를 포함하는 셀의 서식 지정] 밑의 필드 상자에 강조하고자 하는 텍스트(예: 홍길동)를 입력한 후 [확인]을 클릭한다.
4. 홍길동 이름은 모두 진한 빨강 글꼴에 연한 빨강으로 강조 표시되는 것을 확인한다.


C. 특정 범위의 날짜를 포함하는 셀의 서식 지정
1. F열에 날짜를 입력한다.
2. [홈]> [조건부 서식]>[셀 강조 규칙]>[발생 날짜]를 선택한다.
3. [발생 날짜] 대화 상자가 표시된다. [다음 발생 날짜를 포함하는 셀의 서식 지정] 아래의 필드 상자에 오늘, 어제, 내일, 이번 달, 다음 달 등의 날짜 범위를 입력한 다음 [확인]을 클릭한다.
4. 4월의 모든 날짜가 이제 진한 빨강 글꼴에 연한 빨강으로 강조 표시되는 것을 볼 수 있다.


D. 상위/하위 규칙
다음은 상위/하위 규칙이다. 상위 10개 항목, 상위 10%, 하위 10개 항목, 하위 10%, 평균 초과, 평균 미만 항목에 대해 서식을 지정할 수 있다. 상위 및 하위 백분율과 상위 및 하위 항목이 무엇인지는 이름만 들어도 알 수 있을 것이다. 강조 표시된 범위마다 최대값 또는 최소값, 최대 백분율과 최소 백분율의 서식이 지정된다.

1. B열을 강조 표시한다.
2. [홈] > [조건부 서식] > [셀 강조 규칙] > [상위/하위 규칙] > [상위 10개 항목]을 선택한다.
3. [상위 10개 항목] 대화 상자가 표시된다. 기본값이 10으로 지정되어 있고 적용할 서식을 묻는다. [진한 빨강 텍스트가 있는 연한 빨강 채우기]를 선택한 다음 [확인]을 클릭한다.
4. 다음으로는 C열을 강조 표시한 후 위의 방법에 따라 하위 10개 항목의 서식을 지정한다.


5. D열을 강조 표시한다.
6. [홈] > [조건부 서식] > [셀 강조 규칙] > [상위/하위 규칙] > [상위 10%]를 선택한다.
7. [상위 10%] 대화 상자가 표시된다. 기본값이 10으로 지정되어 있고 적용할 서식을 묻는다. [진한 노랑 텍스트가 있는 노랑 채우기]를 선택한 다음 [확인]을 클릭한다.
8. 다음으로는 E열을 강조 표시한 후 위의 방법에 따라 하위 10%의 서식을 지정한다.
참고: 상위 및 하위 항목에는 1에서 9999 사이의 아무 숫자나 입력할 수 있다. 상위 및 하위 백분율에는 1에서 999 사이의 아무 숫자나 입력할 수 있다.


9. 평균 초과 및 평균 미만 옵션은 다음과 같이 계산된다. 강조 표시된 열의 값을 평균 내어 “평균” 값을 결정한다. B 열에서 평균은 $3231이다. C열에서 평균은 5523.50이다. 이 값들은 =AVERAGE(B2:B13) 및/또는 =AVERAGE(C2:C13) 명령을 사용해 확인할 수 있다.
10. B열은 평균 초과 서식에, C열은 평균 미만 서식에 강조 표시한다.
11. 위의 방법에 따라 각 열에 대한 서식을 선택한다.


E. 데이터 막대와 색조
데이터 막대와 색조는 스프레드시트에 의미 있는 시각적 효과를 더할 수 있는 서식 도구이다. 예를 들어, 데이터 막대는 값을 비교한다. 당연히 가장 긴 막대는 최대값을 나타내고 가장 짧은 막대는 최소값을 나타낸다. 반대로, 색조는 최대값, 최소값, 중간 값 사이의 범위를 보여 준다.

1. B열을 강조 표시한 후 [조건부 서식] > [데이터 막대]에서 그라데이션 채우기 데이터 막대를 선택한다.
2. C열을 강조 표시한 후 [조건부 서식] > [데이터 막대]에서 단색 채우기 데이터 막대를 선택한다.
3. 데이터 막대가 각 셀 내에서 수평 막대 차트 기능을 하는 것을 볼 수 있다. 데이터 막대를 더 잘 보려면 열 너비를 확장한다.
4. D열을 강조 표시한 후 [조건부 서식] > [색조]에서 색조를 선택한 다음 E열을 강조 표시한 후 다른 색조를 선택한다.
5. 숫자가 달라질 때 색상이 어떻게 변하는지 유의해 본다. D열(3월)과 E열(4월) 간의 차이도 유의해 본다.


아이콘 집합 역시 최대값, 최소값, 중간 값의 범위를 보여 준다.

1. 예를 들면, B열을 강조 표시하고 [홈] > [조건부 서식] > [아이콘 집합] > [모양]을 선택한 다음 목록 상 첫 점 집합을 선택한다. 목록 상의 숫자를 평균 내서 1000대와 2000대 숫자들은 모두 빨간 점으로 표시하고 3000대와 4000대 숫자들은 노란 점, 5000대 숫자들은 녹색 점으로 표시한 것을 볼 수 있다.
2. C열과 D열을 강조 표시한 후 나머지 세 개의 집합 가운데 하나에서부터 한 가지의 아이콘 집합을 선택한다. 아이콘 집합 내에서 값들이 아이콘 세 개, 아이콘 네 개, 아이콘 다섯 개로 어떻게 묶이는지 차이점을 유의해서 본다.


참고: 이 그룹에 있는 모든 아이콘 집합(방향, 모양, 표시, 점수)은 3단계, 4단계, 5단계의 서식을 제공한다. 이는 다시 최대값, 최소값, 중간 값을 결정한다. 필요에 더욱 정확히 맞도록 서식 단계를 쉽게 변경할 수 있다.

여기까지 왔으면 이제 정의한 규칙을 이용해 조건부 서식 기능 전체를 사용자 정의할 수 있다. 이러한 규칙은 엑셀의 “단계별 설명” 메뉴를 통해 쉽게 만들 수 있는데, 다음 기사에 다루도록 한다. editor@itworld.co.kr  


X