조건부 서식 - 둘 이상 조건에 맞는 줄에 색칠하기

한 가지 조건을 만족하는 경우 채우기에 대해서는 아래 글에서 다뤘는데,
https://lsw3210.tistory.com/entry/%EC%97%91%EC%85%80-%EB%B0%B0%EC%9A%B0%EA%B8%B036-%EC%A1%B0%EA%B1%B4%EB%B6%80-%EC%84%9C%EC%8B%9D5-%EC%88%98%EC%8B%9D%EC%9D%84-%EC%82%AC%EC%9A%A9%ED%95%B4-%EC%85%80-%EC%A7%80%EC%A0%95

엑셀 배우기(36) - 조건부 서식(5) - 수식을 사용해 셀 지정

 
두 가지 이상 조건을 만족하는 경우에 대해서는 다루지 않아 다뤄보고자 합니다.
 

 

1. 지점명과 이름이 일치하는 행에 색 채우기

지점명이 A이고, 이름이 홍길동인 행의 A열부터 D열까지 채우기를 해보겠습니다.
 
A열부터 D열까지 선택한 다음

 
홈 탭, 조건부 서식 명령을 누른 다음 새 규칙을 선택합니다.

 
새 서식 규칙 창에서 '수식을 사용하여 서식을 지정할 셀 결정'을 클릭하고,

 
두 가지 조건을 만족해야 하므로 And 함수를 이용해 두 가지 조건을 입력합니다.

수식 입력란에 =AND(A1="A",B1="홍길동") 라고 입력하고, 오른쪽 아래 서식 버튼을 누릅니다.

수식은 범위의 첫 줄을 기준으로 작성하므로 A1이 되어야 하며, A2가 되도록 하려면 적용할 범위를 두 번째 줄부터 지정해야 합니다.

 
채우기 탭을 클릭한 후 원하는 색을 선택하고 확인 버튼을 누릅니다.

 
그러면, 다시 새 서식 규칙 창으로 돌아오는데, 확인 버튼을 누르면

A열부터 D열까지 채우기가 안되고, A열에만 색 채우기가 되고 있습니다.

 
이것은 조건식에서 수식에 사용되는 A열과 B열이 셀이 A열에서 D열로 이동하더라도 바뀌면 안되는데, 바뀌어서 그런 것이므로

A와 B를 절대 참조 형식으로 바꾸기 위해 $를 앞애 붙여 $A와 $B로 수정해야 합니다.
 
조건부 서식 - 규칙 관리를 누른 후, 조건부 서식 규칙 관리자 창에서 규칙 편집을 누릅니다.

 
A와 B를 $A, $B로 수정하고 확인 버튼을 누릅니다. 

 
(주의사항)
이 때 화살표키를 누르면 아래와 같이 +$B$1이 입력되는 등 수식이 이상해지므로 마우스로 입력할 위치를 클릭하고 입력해야 합니다. 그러나, 백스페이스키는 문제없습니다.

 
이제 A열부터 D열까지 색이 채워졌습니다.

 
 

2. 지점명과 이름이 동일한 데이터가 2개 이상인 경우 색 채우기

지점명과 이름이 같은 것이 입력될 때 색을 채워보겠습니다.

위와 다른 점은 위는 첫 번째도 색이 채워졌는데, 이번에는 두 번째 이후에만 색이 채운다는 점입니다.
 

가. 규칙 지우기

먼저 조건부 서식에서 규칙 지우기를 하는데,
A열에서 D열까지 선택된 상태라면 '선택한 셀의 규칙 지우기'를 누르고, 그렇지 않다면 '시트 전체에서 규칙 지우기'를 클릭합니다.

 
그러면, 아래와 같이 적용됐던 규칙이 지워져서 채우기가 없어졌습니다.

 

나. 수식 1(실패)

조건부 서식에서 수식 입력 란에
=countif($a$1:$a1&$b$1:$b$1,$a1&$b1)>1 라고 입력합니다.

이 수식은 &를 이용해서 A1셀부터 A열의 현재 행까지와 B1셀에서 B열 현재 행까지를 결합한 후 현재 줄의 A열과 B열을 결합한 개수가 1보다 큰 경우라는 조건식입니다.
 
그런데 아래와 같이 수식에 문제가 있다고 합니다. 

 
countif의 인수로는 & 결합을 사용할 수 없는 듯합니다.
 
 

다. 수식 2(성공)

수식에
=SUM(IF(($A$1:$A1)&($B$1:$B1)=$A1&$B1,1,0))>1 라고 Sum과 If함수를 이용해서 배열 수식으로 입력하면

A열 1행부터 1행(현재 행)까지와 B열 1행부터 1행(현재 행)까지를 결합(&)한 값과 A1셀(A열 현재 행)과 B1셀(B열 현재 행)을 결합한 값이 같으면 1이고, 아니면 0으로 반환하도록 한 다음
Sum을 해서 1보다 큰 경우로 하면

 
조건에 맞는 두 번째 것에 색칠이 되기는 하는데 빈 셀인 경우도 색칠이 되는 문제점이 있습니다.

 
따라서, A열과 B열이 공백이 아닌 경우 조건을 추가해서

=SUM(IF((($A$1:$A1)&($B$1:$B1)=$A1&$B1)*(LEN($A1)>0)*(LEN($B1)>0),1,0))>1
라고 하면 되기는 하는데 수식이 너무 복잡하니다.

 
 
 

라. 수식3(실패)

And조건이므로 if 함수 안에 And함수를 이용해서 입력하는데, And함수는 인수를 ,(쉼표)로 구분해서 입력하므로 *(곱하기)를 ,(쉼표)로 바꿔서 

=SUM(IF(AND((($A$1:$A1)&($B$1:$B1)=$A1&$B1),(LEN($A1)>0),(LEN($B1)>0)),1,0))>1 라고
입력하고 확인 버튼을 누르면

 
수식은 문제없다고 통과하는데 아래 화면에서 다시 확인 버튼을 누르면

 
조건부 서식이 적용되는 셀이 하나도 없습니다.

 
이것은 And함수의 결괏값이 배열로 반환돼야 하는데, 단일값으로 반환돼서 그렇습니다.

다시 말해 위 수식에서 AND((($A$1:$A1)&($B$1:$B1)=$A1&$B1),(LEN($A1)>0),(LEN($B1)>0)) 부분만 복사해서

F1셀에 =을 입력한 다음 붙여 넣고 엔터키를 누르면 True라고 값이 반환되고,

 
F1셀의 채우기 핸들을 F2셀로 끌면  배열 수식으로 값이 반환되지 않기 때문에 F2셀에만 False라는 값이 반환됩니다.

그러나,  G1셀에 =(($A$1:$A1)&($B$1:$B1)=$A1&$B1)*(LEN($A1)>0)*(LEN($B1)>0)라고 배열 수식으로 입력한 다음 엔터키를 누르고(Microsoft 365에서는 엔터키만 눌러도 되나, 이전 버전은 Ctrl+Shift+Enter키를 눌러야 합니다),
 
G1셀의 채우기 핸들을 G2셀로 끌면 G2셀과 G3셀에 값이 배열 형식으로 반환됩니다. 다시 말해 G2셀과 G3셀에 파란색 선이 둘러싸여져 있습니다.

 
다시 말해 And함수는 배열 형태로 값을 반환하지 않고, 단일 값만을 반환하기 때문에 안되는 것입니다.
 
 

마. 수식4(실패)

중복된 데이터의 개수를 세는 것이고, 조건이 여러 개이므로
CountIfs 함수를 사용할 수 있습니다.
 
커서를 A2셀에 두고도 수식을 작성할 수 있는데, 이때는 수식 입력 후 적용 대상을 수정해야 합니다.

 
위 화면에서 새 규칙을 누릅니다.
=countifs($a$1:$a1,$a1,$b$1:$b1,$b1,len($a1),">0",len($b1),">0")>1
라고 입력하고, 서식 버튼을 누른 후 채우기를 황토색으로 정하고, 확인 버튼을 누르면 아래와 같이 에러가 발생합니다.

 

바. 수식5(성공)

왜 그런가 하고, ,len($a1),">0",len($b1),">0" 부분을 삭제하고 확인 버튼을 누르니 에러가 없어집니다.

 
적용 대상이 =$A$2라고 표시되는데, 그 아래와 같이 =$A:$D로 수정하고 확인 버튼을 누르면

 
8행과 9행이 채우기가 돼야 하는데, 한 줄 아래인 9행과 10행이 채워져 있습니다.

 
따라서, 조건부 서식에서 규칙 관리를 눌러 수식을 확인해보니
$A$1:$A1이라고 혼합참조로 입력한 두번째 1이 1048576으로 바뀌어 있는데,
 
이유는 A2셀에 커서가 있는 상태에서 입력하면서 A1셀을 기준으로 수식을 작성하고 2행이 아닌 1행부터 적용하다보니 2행에서 1행으로 셀이 이동하고, 수식에 있는 1행이 한바퀴 돌아 1048576이 된 것 입니다.

 
(해결책)
먼저 적용 대상을 A열에서 D열로 지정하고 A1셀을 기준으로 수식을 입력하거나,

A2셀에서 수식을 작성할 것이면 A2셀 기준으로 수식을 작성하고, 적용 대상도 2행부터 지정해야 합니다.

 
수식에 있는 1048576을 모두 1로 수정해서
=COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1)>1 로 수정하고 확인 버튼을 누르면

 
위와는 달리 8행과 9행에만 색이 채워져 있습니다. 

 
또한 sum과 if 배열 수식을 이용할 때는 len($a1),">0",len($b1),">0" 라고 A열과 B열에 값이 있는 경우만으로 한정하는 조건을 넣었어야 하는데,
 
CountIfs 수식에서는 len($a1),">0",len($b1),">0" 가 없더라도 공백인 줄이 2개 이상인 경우에는 색이 칠해지지 않는 장점이 있습니다.
 

조건부 서식-두가지 조건 만족(완성).xlsx

0.01MB