[ 응용 ] 조건 선택해서 조건에 일치하는 부분 강조하기, 평균 이상 또는 이하 선택해서 강조하기

엑셀 드롭다운 목록과 조건부 서식을 사용하여

아래 그림과 같이 

드롭다운 목록에 있는 조건을 만족하는 셀을 강조하는 방법입니다.

 

C열의 TRUE / FALSE는 참고사항입니다.

드롭다운 목록을 만드는 방법은 아래 링크를 참고하시면 됩니다.

※ 드롭다운 목록 만드는 법

 

728x90

 사용된 함수

 

사용된 함수는 

조건을 검증하는 IF() 함수와

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

 

 함수식 구현하기

 

셀에 색을 칠해 강조하기 위해서는 조건부 서식을 사용해야 하고

조건부 서식을 사용하기 위해서는

반환값이 TRUE 또는 FALSE 둘 중 하나여야 합니다.

※ 조건부 서식에 대한 기본 개념

 

우선 드롭다운 목록은 D1셀에 있으므로

D1셀의 값이 "평균 이상"인지 확인을 합니다.

D1 = "평균 이상"

 

만약 평균이상이라면 TRUE를 반환할 수 있도록 

IF() 함수의 두 번째 인자에 아래와 같이 함수식을 작성합니다.

B2>AVERAGE(B2:B19)

B2의 값이 B2:B19의 평균보다 큰 지 점검한 후 

크다면 TRUE가 반환되고

작다면 FALSE가 반환됩니다.

 

만약 D1셀의 값이 평균 이상이 아니라면

즉, 평균 이하라면 TRUE를 반환할 수 있도록 

IF 함수의 세 번째 인자에 아래와 같이 함수식을 작성합니다.

B2<AVERAGE(B2:B19)

 

우선 지금까지의 전체 함수식은 이렇습니다.

=IF(D1="평균 이상", B2>AVERAGE(B2:B19),  B2<AVERAGE(B2:B19))

 

드롭다운 목록에서 평균 이상이 검색되면

B2가 평균보다 큰가를 확인합니다.

드롭다운 목록에서 평균 이상이 검색되지 않으면,

즉 평균 이하라면

B2가 평균보다 작은지 검색합니다.

 

반응형

 참조 설정하기

 

우선 D1셀의 값은 채우기를 했을 때 값이 변하지 않으므로 

절대참조로 해줍니다.

 

B2값은 아래로 변하므로 B만 절대참조를 해줍니다.

 

AVERAGE() 함수에 있는 범위는 변하지 않는 범위이므로

모두 절대참조를 해줍니다.

 

최종 함수식은 다음과 같습니다.

=IF($D$1="평균 이상", $B2>AVERAGE($B$2:$B$19),  $B2<AVERAGE($B$2:$B$19))

 

 조건부 서식 설정하기

 

이제 이 함수식을 조건부 서식에 넣으면 됩니다.

 

아래 그림과 같이 조건부 서식을 지정할 범위를 선택한 다음

조건부 서식 → 새 규칙을 눌러줍니다.

 

그다음

수식을 사용하여 서식을 지정할 셀 결정을 선택한 다음

만들어 둔 함수식을 복사해서 넣습니다.

그리고 서식을 눌러줍니다.

 

그다음 서식을 결정합니다.

저는 색을 채우는 것으로 결정했습니다.

 

이다음 확인을 눌러주면

조건부 서식이 지정되고,

드롭다운 목록에서 값을 선택하게 되면

선택된 값에 따라 강조되는 셀이 바뀌게 됩니다.

여기를 방문하시면 더 많은 엑셀 관련 자료를 확인할 수 있습니다.

엑셀 공부하기
VBA 공부하기