수많은 자료 중에서 원하는 자료만 색을 바꾸거나 굵은 글씨로 만들 수 있다면 얼마나 좋을까요. 한눈에 들어와서 보기도 좋고 쓰기도 좋을 겁니다. 엑셀 조건부 서식은 실용성도 좋지만 컴활 자격증 시험에서 물어보기도 하니 꼭 알아둬야 하겠죠.

  엑셀은 이미 여러 조건부 서식을 지원합니다. ~보다 높은 숫자나 상위 몇 퍼센트를 강조하는 식이죠. 기본 내용은 예전 포스트에서도 설명했으니 이번엔 수식을 직접 입력해서 조건부 서식 고수가 되어 봅시다.

시작하기 전에 예제 파일 받아가세요!

조건부서식-엑셀카페.xlsx

우리만의 약속

첫째. 예제 파일로 한 번씩 예제 연습하기

둘째. 아리송하면 복습하기

셋째. 부담 없이 잔잔히 즐기기

* 이 포스팅은 엑셀2016을 기반으로 썼습니다.

* 밑 조건식을 그대로 붙여넣기 하면 작동하지 않았습니다. 까닭은 모르지만 참고만 하시고 손수 쓰시기 바랍니다.

범위 정하기

  조건부 서식 규칙을 만들기 전에 조건을 적용할 셀들을 지정합니다.

드래그로 범위를 정합니다.

주의. 제목 셀은 범위에 넣지 않습니다!

조건부 서식 시작하기

  범위를 정하고 홈 리본에 있는 [조건부 서식]을 클릭합니다.

[새 규칙]을 누르고 ‘수식을 사용하여 서식을 적용할 셀 결정’을 누릅니다.

  밑에 입력창이 하나 생깁니다. 이곳에 규칙을 입력합니다.

  '서식'에서는 규칙에 맞는 셀에 어떤 서식을 줄지 정합니다. 이번 포스트에서는 전부 하늘색으로 셀을 칠하겠습니다.

예제 1) 나이가 50세 이상인 사람의 행을 칠할 것

=($D3>=50)

주의. 규칙을 입력할 때 방향키를 누르면 셀 주소가 나타납니다. 입력 커서는 마우스로만 움직입시다!

예제 2) 남자인 행만 칠할 것

=($C3="남자")

참고. C열이 ‘남자’가 아닌 행은 조건식이 뭘까요? <>를 씁니다.

=($C3<>"남자")

예제 3) 회원번호 첫 글자가 A인 사람의 행을 칠할 것

엑셀 LEFT 함수는 셀 텍스트를 왼쪽부터 추출하는 함수입니다.

(LEFT 함수의 첫 인수는 셀 주소, 둘째 인수는 추출할 글자 수입니다)

=(LEFT($F3,1)="a")

참고. 셀 텍스트를 오른쪽부터 추출하는 함수는 뭘까요?

네, 맞습니다. RIGHT 함수입니다.

예제 4) 회원번호 두 번째 글자가 5 이상인 행을 칠할 것

  셀 텍스트를 중간부터 추출하는 함수는 MID입니다.

MID(셀 주소, 추출을 시작할 글자, 추출할 글자 수)

(이 경우에는 셀 주소, 2, 1이겠죠)

=(MID($F3, 2, 1)*1>=5)

잠깐! 왜 ‘*1’이 있죠?

  LEFT, MID RIGHT 함수는 셀 글자를 텍스트로 추출하는 함수입니다. 아무리 숫자를 빼냈어도 텍스트 상태기 때문에 다른 숫자와 비교할 수가 없습니다. 따라서 *1을 해서 숫자로 바꿔야 제대로 서식이 적용됩니다.

예제 6) 행 번호가 짝수/홀수인 행을 칠할 것

행 번호를 반환하는 함수는 ROW입니다.

ISEVEN 함수는 인수가 짝수면 TRUE를 반환하고

ISODD 함수는 인수가 홀수면 TRUE를 반환합니다.

행 번호가 짝수인 행을 칠할 땐

=ISEVEN(ROW())

행 번호가 홀수인 행을 칠할 땐

=ISODD(ROW())

참고. 열 번호를 반환하는 함수는 COLUMN입니다.

예제 7) 행 번호가 3의 배수인 행을 칠할 것

MOD 는 나눗셈 후 나머지를 구하는 함수입니다.

3으로 나누어 나머지가 0이면 3의 배수겠죠?

=MOD(ROW(), 3)=0

예제 8) 등록일이 8월인 행을 칠할 것

  날짜에서 달수를 추출하는 함수는 MONTH입니다.

=MONTH($E3)=8

예제 9) 등록 날짜가 15일 이상인 행을 칠할 것

날짜에서 날짜를 추출하는 함수는 DAY입니다.

=DAY($E3)>=15

예제 10) 점수가 상위 10등인 행을 칠할 것

LARGE 함수는 범위에서 ~등인 값을 반환합니다. 이 함수를 응용합시다.

=$G3>=LARGE($G$3:$G$22,10)

해석 : 점수 범위에서 10등인 값 이상인 점수 행을 색칠

예제 11) 점수가 평균보다 높은 행을 칠할 것

평균을 구하는 함수는 AVERAGE입니다.

=$G3>AVERAGE($G$3:$G$22)

예제 12) 여자면서 나이가 40세 이상인 행을 칠할 것

이제 조건이 두 가지입니다.

두 조건을 만족하는 조건부 서식을 쓰는 법은 둘입니다.

첫째, AND 함수 이용

둘째, * 이용

=AND($C3="여자", $D3>=40)

=($C3="여자")*($D3>=40)

참고. AND 함수는 두 조건이 모두 맞을 때만 TRUE를 반환합니다.

참고. *도 AND처럼 TRUE*TRUE일 때만 TRUE를 반환합니다.