엑셀 - 조건부 서식을 이용해서 정확하지 않은 값을 골라내는 방법
제가 업무를 하다 보면, 숫자로 정리를 해야 되는 경우가 있습니다. 다음과 같은 경우가 해당되죠..
그냥 단순히 케이스 1, 2...7에 해당하는 수를 더하여 총개수 란에 적은 거라면 그냥 총개수에 SUM 함수를 사용하면 되는데.... 그런게 아니고 '총개수' 란에 있는 숫자는 정확한 숫자이고, 케이스 1, 2,...7 에 있는 숫자가 정확한지 몰라서 틀린 경우 다시 확인을 해야 하는 경우 상당히 귀찮습니다.
가장 간단한 방법은 위와 같이 SUM 함수를 이용해서 케이스 1, 2...7의 값을 다 더하고, 그 값을 총개수 부분과 비교하는 거겠지만 번거롭고 시간이 많이 걸립니다.
위와 같은 경우는 몇 줄 안되니 금방 할 수 있겠지만, 수백줄, 수천줄이 되게 되면 일일이 확인하는 건 시간도 많이 걸리고 번거롭기도 합니다. sum 함수를 위한 별도의 셀도 필요하구요...
이 경우 조건부 서식을 사용하면 편하게 틀린 부분만을 확인할 수 있습니다.
먼저, 틀렸는지 맞았는지를 나타낼 셀을 블록을 잡습니다.
저는 그냥 '총개수'의 셀들을 전부 블록 잡았습니다.
그 다음 조건부서식의 새규칙을 선택합니다.
먼저, 조건부 서식의 새규칙에서 수식을 사용하여 서식을 지정할 셀 결정 부분을 선택합니다.
그 다음 수식을 입력하고, 그 아래의 서식을 눌러서 셀 서식을 설정합니다. 저는 그냥 초록색으로 채우기만으로 서식을 설정했습니다.
위의 수식은 총개수($D3)에서 케이스 1, 2...7 의 값의 합(SUM($E3:$K3)을 뺀 값이 0이 아닌 경우 초록색 채우기로 셀 서식을 변경하라는 것입니다.
수식의 셀 좌표는 첫번째 행을 기준으로 해야 하며, 주의해야 하는 부분은 셀을 지정할 때 $D3와 같은 형식으로 기재해야지 $D$3와 같은 형식으로 기재하면 안 됩니다.(조건부 서식이 블록을 잡은 셀들을 따라 내려가면서 뒤에 숫자가 변경되어야 해서 그렇습니다.)
이렇게 하고, 확인 누르고 적용을 시키면 다음과 같이 됩니다.
총개수와 케이스의 값의 합이 다른 경우에, 총개수의 셀들의 색이 변해서 금방 알아볼 수 있습니다.
문자는 어떻게 할가요?
위와 같이 "가"를 제외한 문자가 기재되었거나 공란이 있는 셀을 강조하는 수식은 다음과 같습니다.
문자를 할 때에는 따옴표로 "가" 와 같이 기재해야 한다는 점을 주의해야 합니다.
그럼 여러가지를 조합하는 경우에는 어떻게 될까요? 위의 표에서 가와 같거나 공란인 경우에 셀을 강조해 다음과 같이 보이게 하려면 어떻게 해야 할 까요?
이 경우에는 다음과 같이 OR를 사용해서 수식을 작성합니다.
다음과 같은 경우는 어떻게 할 까요? 다음은 총개수에는 "가" 가 기재되고, 케이스 1에는 1이 기재된 경우에 셀을 강조하는 겁니다.
이 경우에는 AND 문을 사용해서 다음과 같이 수식을 작성하면 됩니다.
위의 예에서는 총개수 열과 케이스 1 열이 모두 색이 변하게 하였는데, 색이 변하는 건 어떤 셀들을 블록을 잡았는지에 따라 결정되는 거니.. 자신이 강조하고자 하는 셀과 수식에 사용하는 셀은 관련성이 없습니다.
이번에는 위와 같이 셀 중에서 2차라는 부분이 포함되어 있는 경우에만 서식이 바뀌도록 하는 방법을 알아보겠습니다.
이 경우에는 다음과 같이 하면 됩니다.
만약 그냥 "2차"라는 글자만 있는 셀을 찾는다고 한다면
= ($I1="2차")
이렇게만 넣으면 되지만, 이렇게 하면 '2차(다시)' 와 같이 기재된 셀의 서식은 변경할 수 없게 됩니다.
마찬가지로 MID, RIGHT 함수를 이용해서 셀의 일부 글자만 비교를 해서 원하는 셀의 서식만 수정할 수 있습니다.
다만, 위의 방법은 항상 특정한 글자가 일정한 위치에 있다는 걸 전제로 하는 건데요... 위치에 상관없이 그 글자를 포함하기만 하면 셀 서식이 변경되도록 하는 방법은 잘 모르겠습니다.
아시는 분 계시면 알려주시면 감사드리겠습니다.
컴퓨터 활용 목차
이 글을 공유합시다
