1일1함수(9) : NOT함수 (5행마다 셀의 색깔을 자동으로 바꾸기)

오늘 배울 함수는 NOT함수입니다.

인수로 주어진 수식이나 참조가 TRUE이면 FALSE를, FALSE이면 TRUE를 반환합니다.

NOT(FALSE) = TRUE
NOT(1+1=2) = FALSE

재미있는 것은, 숫자를 넣어도 값이 나온다는 것입니다. 숫자 0은 논리값 FALSE로 인식하고, 그외의 숫자들은 TRUE로 인식하므로

NOT(0) = TRUE
NOT(1) = FALSE
NOT(-4) = FALSE

가 됩니다. 0이 FALSE를 대신할 수 있다는 것은 이전의 포스트에서도 썼습니다만, 엑셀의 다른 함수에서도 다 적용됩니다. 예를 들어, VLOOKUP함수의 맨 마지막에 FALSE를 쓰면 주어진 값과 똑같은 값을 찾으라는 것이지요. FALSE대신에 0을 넣어도 같은 결과가 나옵니다. 즉,

VLOOKUP(B2,$C$2:$D$11,2,FALSE)
VLOOKUP(B2,$C$2:$D$11,2,0)

위 두 개의 수식은 똑같은 결과값을 가집니다.


그럼 지금까지 배운 몇가지 함수를 이용하여 재미있는 조건부서식을 만들어 봅시다.

행이 여러개가 있을때, 보기 좋도록 하기위해 값이 들어있는 셀주변에 테두리선을 긋고, 5행마다 굵은 선을 긋거나 색을 바꿔칠하거나 합니다. 그런데 표를 다 만들어서 예쁘게 해놓았는데 데이타가 또 늘어나면 테두리 그리고 5행마다 색을 바꾸는 것을 또 해야합니다.
그래서 오늘은 조건부서식을 이용하여, 행을 입력할때마다 자동으로 테두리선이 그어지도록하고, 5행마다 색을 다르게하는 방법을 배우려 합니다.

일단 A2셀부터 D셀의 맨끝행(1048576행)까지 선택합니다. (A2셀에 커서를 놓고 CTRL+SHIFT+오른쪽화살표하면 A2:D2범위가 선택되고, 거기서 컨트롤키와 쉬프트키를 떼지 않은 상태로 아래쪽 화살표하면 맨 아래행까지 선택됩니다. 이전 글에서 설명하였지만, 쉬프트키는 범위선택, 컨트롤과 화살표키는 값이 있는 셀의 끝까지 이동하는 명령입니다)

홈 - 스타일 - 조건부서식 - 새규칙 으로 들어가서 "수식을 사용하여 서식을 지정할 셀 결정"을 선택하고, 다음 수식을 입력합니다.

=NOT(ISBLANK($A2))

이 수식이 조건부서식에 들어갔으므로, A2셀이 빈칸이면 FALSE값이, A2셀이 빈칸이 아니면 TRUE값이 반환됩니다. 조건부서식이란 주어진 수식이 TRUE값일때 해당 서식을 적용하는 것이므로, A2셀이 빈칸이 아니면 지금부터 지정하는 서식이 적용되게 됩니다.

주의할점은, $A$2 이 아니고 $A2 이라는 것입니다. 만약 $A$2으로 입력하면 어떤 결과가 나오는지 한번 해 보세요 ^^;

"서식"을 클릭하여 윤곽선을 지정해 줍니다.

그 다음에 확인-확인-확인하시면 테두리가 만들어져 있습니다.

이제 그 다음 행부터 A열에 값을 넣어보세요. 자동으로 테두리가 만들어집니다. 단, A열에 값을 넣어야만 만들어집니다. B,C열에 값을 넣었을때는 만들어지지 않습니다. (수식에 A2를 입력했다는 것을 생각하세요)


이제 5행마다 색을 바꾸는 것을 해보겠습니다.

이번에는 A3셀에서부터 D열의 끝행까지 선택합니다.  아까는 A2셀부터였는데 왜 지금은 A3셀이냐고하면, 2행은 제목행이기 때문에 5행마다 바꾸는 범위에 포함시키면 안되기 때문입니다.

아까와 마찬가지로, 홈 - 스타일 - 조건부서식 - 새규칙 으로 들어가서 "수식을 사용하여 서식을 지정할 셀 결정"을 선택하고, 다음 수식을 입력합니다.

=MOD(ROW()-2,10)>=5

이전의 글에서도 한번 사용한 적이 있는 MOD함수와 ROW함수를 사용하였습니다.
현재 셀의 행번호에서 2를 더한 수를 10으로 나누어서, 그 나머지가 5이상(5~9)인 경우에 지정된 서식을 적용하라는 수식입니다. 따라서, 이제부터 지정할 서식은 3~7행, 13~17행...에 적용되게 될 것입니다.

"서식"을 클릭하여 들어가서 "채우기"에서 배경색을 적당한 것으로 선택합니다.

그 다음에 확인-확인-확인하시면 5행마다 셀의 색깔이 칠해져 있습니다.
그런데, 잠깐, 13행부터는 입력된 값이 없는데도 불구하고 색이 칠해져 있습니다. 확인해보면 마지막행(1048576행)까지 색이 칠해져 있습니다. 아까 입력한 수식을 곰곰 생각해보면, 5행마다 색을 칠하라는 조건은 주었지만 셀값이 비어있을때는 하지 말라는 조건을 주지 않았기 때문입니다.

홈 - 스타일 - 조건부서식 - 규칙관리로 들어가면 위처럼 규칙이 두 개가 보입니다. 그 중에서 위에 있는 것이 5행마다 색을 칠하는 조건입니다.  선택하고 "규칙편집"을 클릭합니다.

아까 입력한 수식을 지우고, 다음 수식으로 바꿉니다.

=AND(NOT(ISBLANK($A3)),MOD(ROW()+2,10)>=5)

아까 입력한 수식을 AND함수를 이용해 셀이 비어있는지를 확인하는 수식을 첨가했습니다. 즉, 해당셀(A3)이 비어있지 않아야한다는 조건과, 해당 행번호에 2를 더한 값을 10으로 나눈 나머지가 5이상이어야한다는 두 가지 조건을 다 갖추어야만 지정된 서식을 적용하게 됩니다.

확인-확인-확인하고 나오면 드디어 완성되었습니다!


참고로, 개인적으로는 셀의 색깔보다 굵은 선 테두리를 많이 썼었는데, 조건부 서식에서는 굵은 선 테두리를 지정할 수 없습니다. 이점이 심히 안타깝습니다. 5행마다 셀의 색깔을 바꾸지 않고 굳이 굵은 선 테두리를 적용하고 싶다면 매크로를 이용하여야합니다.

네이버 지식인에 다른 분이 올려놓은 다음 매크로를 한번 연구해 보세요.

Sub Macro1()
 Dim i As Integer, intLast As Integer 
  intLast = Range("A1048576").End(xlUp).Row
   For i = 5 To intLast Step 5
    Cells(i, 1).Resize(, 5).Borders(4).Weight = 3
   Next i
End Sub

2009/03/28 - [컴퓨터/엑셀] - 알아두면 쓸모있는 엑셀 팁 몇개
2009/03/28 - [컴퓨터/엑셀] - 1일1함수(8) : IS 함수 (셀의 유형확인하기)
2009/03/28 - [컴퓨터/엑셀] - 1일1함수(7) : AND 함수 OR 함수 (논리곱과 논리합)
2009/03/17 - [컴퓨터/엑셀] - 하루에 엑셀 함수를 하나씩 익히자.(1) MOD함수를 이용해 한 줄 건너 노란색으로 칠하기
2009/02/16 - [컴퓨터/엑셀] - [엑셀] if의 논리검사 항목에 상수가 들어갈때 논리값