엑셀을 이용하면 일반적인 계산기로는 할 수 없는 여러 개의 구문이 합쳐진 복합적인 수식을 쉽게 처리할 수 있습니다. 특히 함수를 사용하면 복잡한 계산식을 더욱 쉽게 처리할 수 있습니다. 더 나아가 엑셀에서는 조건문을 지원하는데, 조건문을 사용할 수 있다는 것이야말로 엑셀을 이용하는 가장 큰 이유가 될 것입니다.

조건문을 활용하면 데이터를 분석할 때 특정 값의 유효성을 검사할 수도 있습니다. 이 글에서는 셀서식에 조건문이 합쳐진 조건부서식에 대하여 알아보도록 하겠습니다.

조건부서식이란?

조건부서식은 엑셀과 같은 스프레드시트에서 자주 사용되는 기능입니다. 조건부서식은 셀서식에 조건문이 합쳐진 기능으로 조건에 따라 셀의 서식을 바꿀 수 있도록 해주는 기능입니다.

엑셀로 작업을 하다보면 특정한 상황에서만 셀서식을 변경해야 하는 경우가 자주 발생합니다. 이 때 조건부서식을 이용하면 편리합니다. 조건부서식을 이용하면 사용자가 설정한 조건이 충족되는 경우에만 특정 셀의 서식이 자동으로 변경되게끔 할 수 있습니다.

예를 들어, 가계부를 작성한다고 했을 때, 자산 총액이 100만 원 미만으로 떨어지는 경우에 한하여 해당 셀의 배경색을 빨간색으로 자동 변경할 수 있습니다. 이외에도 조건부서식은 매우 다양한 곳에서 사용될 수 있습니다.

조건부서식 사용 방법

엑셀의 조건부서식은 상단 홈 리본 메뉴의 조건부서식 항목에서 사용할 수 있습니다. 국산 스프레드시트 프로그램인 한셀에서는 서식 리본 메뉴의 조건부서식 항목을 사용합니다.

조건부서식이 자주 사용되는 데이터 형식으로는 가계부가 있습니다. 가계부의 목적은 자산을 관리하는 것이기 때문에 가계부는 사용자의 재무건전성에 문제가 생기는 경우 즉시 알려주어야 하는 필요성있습니다. 따라서 가계부에서 조건부서식이 자주 사용되는 것입니다.

우선 다음과 같은 데이터가 있다고 가정하겠습니다. 이 데이터는 가계부의 일부입니다.

가계부에 입력되는 가장 기본적인 데이터는 자산의 현황과 수입과 지출의 내역입니다. 위 데이터는 자산의 현황을 나타내고 있는 매우 간단한 예제입니다.

은행에 개설되어 있는 모든 통장의 잔액이 입력되어 있고, 이 잔액을 모두 합친 것이 자산 총액 영역에 자동으로 계산되어 나타납니다. 여기에서 가장 중요한 것은 자산 총액입니다. 자산 총액이 일정 수준 이하로 떨어지는 경우 사용자에게 경고 메시지를 보낼 수 있어야 제대로된 가계부의 역할을 수행할 수 있을 것입니다.

위 데이터를 이용하여 매우 간단한 조건부서식을 만들어보겠습니다. 사용자는 자산 총액이 1,000만 원 아래로 떨어질 경우에만 자산 총액 셀의 배경색을 빨간색으로 바꾸려고 합니다. 이렇게 함으로써 자산 총액이 기준 미만으로 떨어졌다는 것을 쉽게 인식할 수 있을 것입니다.

우선 서식을 변경하고자 하는 셀을 선택한 후 상단의 조건부서식 항목을 클릭합니다. 여기에서 셀 강조 규칙의 보다 작음을 선택하면 됩니다.

조건부서식 대화상자에서 본인이 원하는 액수를 입력한 후 적용할 서식 목록 중에서 연한 빨강 채우기를 선택하면 됩니다. 이 글에서는 1,000만 원 미만으로 떨어지는 경우에만 서식을 지정하고자 하므로 값 입력 칸에는 ￦10,000,000 을 입력하면 됩니다. 자동으로 자산 총액 셀이 빨간색으로 바뀌는 것을 확인할 수 있습니다. 현재 입력된 값이 1,000만 원 미만이기 때문에 자동으로 배경색이 바뀌는 것입니다.

그렇다면 자산 총액이 1,000만 원 이상으로 올라가는 경우 셀서식이 다시 원상복귀 되는지 확인해보겠습니다. 우선 조건부서식 적용을 완료하기 위하여 대화상자의 확인 버튼을 누른 후 다음 절차를 진행합니다.

자산 총액은 모든 은행 통장 잔액을 모두 더한 값과 같습니다. 따라서 자산 총액 셀의 수식은 다음과 같습니다.

=SUM(C6:C11)

자산 총액 셀은 수식으로 이루어져 있기 때문에 이 셀의 값을 변경하기 위해서는 아래쪽에 있는 개별 자산 항목을 변경해야 합니다. 자산 총액 셀의 조건부서식이 제대로 작동하는지 확인하기 위하여 자산 총액을 1,000만 원 이상으로 만들어보겠습니다. KB스타트통장의 잔액을 100만 원에서 1,000만 원으로 변경합니다. 그러면 자산 총액이 1,000만 원 이상으로 되면서 빨간 배경색도 자동으로 사라지는 것을 확인할 수 있습니다. 이렇게 셀서식이 자동으로 변경되면 조건부서식이 제대로 작동하고 있는 것입니다.

지금까지 조건부서식을 이용하는 방법에 대하여 알아보았습니다. 조건부서식은 가계부 이외에도 매우 다양한 곳에서 사용될 수 있습니다. 지금부터는 조건부서식을 이용할 수 있는 여러 경우에 대하여 알아보도록 하겠습니다.

예제 1 – 같은 열에 중복되는 값이 있는 경우 빨간색으로 표시

첫 번째 예제는 같은 열에 중복되는 값이 있는 경우 배경색을 빨간색으로 변경하는 예제입니다.

필자는 얼마 전까지 공군 헌병으로 근무를 했었는데, 병장 때에는 경계 병력의 근무 스케쥴을 편성하는 업무를 병행했었습니다. 원래 이 업무는 아래아한글에서 수작업으로 이루어졌으나, 필자는 한셀의 조건부서식을 이용하여 편리하게 근무를 편성하였습니다. 이때 사용하였던 조건부서식이 같은 열에 중복되는 값이 있을 경우 해당 셀의 배경을 빨간색으로 표시하는 것이었습니다.

한셀에서 같은 열에 중복되는 값이 있을 때 해당되는 각각의 셀 배경을 빨간색으로 표시하기 위해서는 직접 수식을 입력해야 했습니다. 그러나 엑셀에서는 기본적으로 내장된 수식을 사용하여 매우 간단하게 기능을 구현할 수 있습니다. 다음과 같이 데이터가 입력되어 있는 경우 같은 열에 있는 중복 데이터를 찾는 조건부서식을 이용할 수 있습니다.

위의 예제 데이터가 초소에 배치되는 병력을 표시한 것이라면 같은 타임에는 같은 이름이 있을 수 없습니다. 따라서 위 데이터에는 조건부서식을 지정하여 같은 값이 입력되는 경우 초소 근무 편성 담당자가 이 사실을 한 눈에 알아차릴 수 있도록 해야 합니다.

서식을 지정하고자 하는 셀을 선택한 후 조건부서식 메뉴에서 셀 강조 규칙의 중복 값 항목을 선택합니다. 여기에서 지정해야 하는 셀은 조건부서식을 지정하고자 하는 타임-1 의 모든 셀입니다. 따라서 C4 부터 C10 까지 선택하면 됩니다.

조건부서식 대화상자에서 적용할 서식을 연한 빨강 채우기로 선택하면 중복되는 값의 셀 배경이 빨간색으로 바뀌는 것을 확인할 수 있습니다. 여기에서는 황민식 이라는 값이 중복되었으므로 황민식이 입력된 모든 값의 셀이 빨간색으로 바뀌었습니다. 실제로 근무 편성을 하는 중이었다면 중대한 오류가 발생한 것입니다.

근무 편성 담당자는 한 눈에 오류를 확인할 수 있기 때문에 근무를 쉽게 수정할 수 있을 것입니다. 만약 조건부서식을 사용하지 않았거나 아래아한글에서 근무 편성표를 작성하고 있었다면 오류가 발생했다는 사실을 쉽게 알아차리지 못 했을 것입니다.

조건부서식이 잘 적용되는지 확인한 후 조건부서식 적용을 완료하기 위하여 대화상자의 확인 버튼을 누릅니다.

이 글에서는 타임-1 열에 있는 셀에 대해서만 조건부 서식을 적용하였습니다. 타임-2 타임-3 타임-4 열에 대해서는 아직 조건부서식을 적용하지 않았습니다. 타임-1 열에 조건부서식을 적용했던 그 방법을 그대로 타임-2 타임-3 타임-4 의 모든 열에도 조건부서식을 적용하면 됩니다.

예제 2 – 조건이 충족되면 같은 행에 있는 모든 셀의 서식을 바꾸기

엑셀에서 기본적으로 제공하고 있는 조건부서식은 해당되는 셀 한 개에만 적용됩니다. 따라서 이번에 설명할 조건인 충족되는 셀의 같은 행에 있는 모든 셀의 서식을 바꾸는 방법은 조금 복잡합니다. 사용자가 직접 수식을 입력해야 합니다.

지금부터는 아래의 데이터를 계속 활용하도록 하겠습니다. 아래 데이터는 기지를 출입하는 모든 민간인의 인적사항을 기록하는 엑셀 파일의 예시입니다. 지금은 모든 출입 절차가 RFID로 이루어지기 때문에 출입현황을 엑셀에 수동으로 입력하는 일은 없겠으나 이 글에서는 조건부서식 적용하는 방법을 설명하기 위하여 아래의 데이터를 사용하도록 하겠습니다.

일반적으로 기지 출입과 관련된 엑셀 서식은 위와 같습니다. 그런데 아직 값이 입력되지 않은 셀에 대해서도 모두 서식이 지정되어 있습니다. 이것은 실수가 아니라 일부러 이렇게 만들어놓은 것입니다. 그 이유는 출입 관련 데이터는 무한대로 계속 늘어날 수 있기 때문입니다. 쉽게 말하면 기지 출입자가 어느 특정한 날에만 있는 것이 아니라 매일 존재하고 기지가 존재하는 한 계속해서 존재할 것이기 때문입니다.

현재 값이 입력되어 있는 셀에만 서식이 지정되어 있는 경우에는 또 다른 출입자가 발생하게 되면 해당 셀에 대해서도 셀 서식을 다시 지정해야 합니다. 이러한 번거로움을 피하기 위하여 처음부터 서식을 모든 셀에 지정해놓은 것입니다.

이 방법에 큰 문제가 있는 것은 아닙니다. 어짜피 나중에는 모두 입력될 공간이기 때문에 미리 서식을 설정해놓는다고 해서 문제가 되는 것은 아닙니다. 또한 엑셀에서는 빈 셀에 서식만 지정된 것을 데이터가 없는 것으로 간주하기 때문에 용량이 늘어나는 것도, 인쇄가 되는 것도 아닙니다.

그러나 위 예제와 같은 모양새는 데이터를 입력할 때 미관상 보기 좋지 않기 때문에 이 글에서는 이 데이터 테이블에 조건부서식을 적용하여 사용자가 더욱 편리하게 문서를 작성할 수 있도록 해줄 것입니다. 조건부서식을 적용하기 위하여 가장 먼저 상단 타이틀 부분을 제외하고 모든 셀의 서식을 지웁니다. 그러면 아래와 같은 모양이 될 것입니다.

이제 데이터가 입력될 때마다 자동으로 각 행에 서식이 지정되도록 해보겠습니다. 즉, 한 개의 셀만 조건을 충족시키면 그 셀이 있는 행의 모든 셀에 대하여 서식이 자동으로 지정되는 것입니다. 여기에서는 데이터가 입력될 때 테두리 서식이 지정될 수 있도록 할 것입니다. 이렇게 하면 번거롭게 서식을 일일이 지정할 필요가 없어질 것이며 미관상 보기도 좋아집니다.

타이틀을 제외하고 데이터가 시작되는 부분인 5행부터 엑셀에서 사용할 수 있는 최대의 행인 1048576행까지 범위 지정합니다. B5:H16 셀 영역을 선택한 후 Ctrl+Shift+↓ 를 누르면 엑셀의 마지막 행까지 선택할 수 있습니다. 그 결과는 다음 스크린샷과 같습니다.

위 스크린샷과 같이 모든 셀을 선택한 후 조건부서식 메뉴에서 새 규칙 항목을 선택합니다. 다른 항목과는 달리 새 규칙 항목을 사용하면 사용자가 직접 조건문을 입력할 수 있습니다.

이 글에서는 특정 셀이 조건문을 만족하는 경우 같은 행에 있는 모든 셀의 서식을 변경할 것인데, 엑셀에서는 이러한 것을 기본적으로 제공하고 있지 않으므로 사용자가 직접 조건문을 작성해야 하는 것입니다. 조건문을 입력하는 것이 그렇게 어렵지는 않습니다.

새 규칙 대화상자가 나타나는데 여기에서 가장 아래에 있는 수식을 사용하여 서식을 지정할 셀 결정을 선택합니다. 이 항목을 선택하면 사용자가 직접 서식이 적용될 셀을 지정할 수 있습니다. 위 데이터에 추가할 조건부서식은 어느 한 셀에 내용이 입력되는 경우 같은 행의 모든 셀에 테두리가 만들어지게 하는 것입니다. 어느 한 셀에 입력되는 경우이므로 엑셀 함수 중에서 OR 함수를 사용하면 될 것이며, 데이터의 유무를 판단하기 위해서는 LEN 함수를 사용하면 될 것입니다. 그래서 다음과 같은 수식이 만들어집니다.

=OR(LEN($B5)>0, LEN($C5)>0, LEN($D5)>0, LEN($E5)>0, LEN($F5)>0, LEN($G5)>0, LEN($H5)>0)

여기에서 주의할 점은 셀 위치를 지정할 때 $ 를 사용하여 열은 고정시키만 행은 고정시키지 않는다는 점입니다. 열만 고정시키는 이유는 조건부서식은 각 셀에 대하여 적용되는 것인데, 조건을 충족시키는 셀이 위치한 행의 모든 셀에 서식을 적용하기 위해서는 행 번호만 이동시켜야지 열 번호는 이동시키면 안 되기 때문입니다. 쉽게 말해서 B5 C5 D5 E5 F5 G5 H5 셀 모두 B5 C5 … 등의 셀만 참고할 뿐이고 B6 C6 … 등의 셀은 B6 C6 … 셀만 참고합니다. 만약 열을 고정시키지 않는다면 C5 셀에서 조건을 확인할 때 B5 가 아닌 C5 셀의 값을 참조할 것입니다.

위 수식을 수식란에 모두 입력한 후 서식 버튼을 눌러 테두리 속성을 지정합니다. 조건부서식을 지정할 때에는 각 셀에 대해서 각각 서식을 지정하는 것이므로 안쪽 테두리를 지정하는 옵션은 없습니다. 따라서 테두리 옵션은 윤곽선을 선택하면 됩니다.

조건부서식과 관련된 모든 옵션을 지정한 후 확인 버튼을 누르면 해당 셀에 조건부서식이 지정됩니다. 값이 입력된 행에만 테두리가 나타나는 것을 확인할 수 있으며, 새로운 데이터를 입력하면 그 행에 자동으로 테두리가 만들어지는 것을 확인할 수 있습니다. 조건부서식이 성공적으로 적용되었으므로 이제부터는 새로운 데이터를 입력할 때마다 서식을 지정해야 하는 번거로운 작업을 하지 않아도 됩니다.

엑셀은 이와 같은 방법으로 업무의 효율성을 높이는데 큰 도움이 될 수 있습니다. 요즘 직장에서도 엑셀을 다루는 능력이 뛰어난 인재를 원하는 추세입니다. 왜냐하면 엑셀을 잘 활용하면 저비용으로 고효율의 업무를 할 수 있기 때문입니다.

지금까지 값이 입력되면 같은 행에 있는 모든 셀에 테두리 속성을 적용하는 방법에 대하여 알아보았습니다. 이외에도 특정 셀에 값이 입력되는 경우 같은 행 모든 셀의 서식을 변경시키는 방법은 여러 곳에 응용될 수 있습니다.

위의 예제 데이터를 예로 들면, 출영 시간에 값이 입력되는 경우 그 행 전체를 빨간색으로 표시한다든지 하는 등의 처리를 할 수 있을 것입니다. 엑셀을 이용하면 아래아한글에서는 할 수 없었던 작업들을 매우 간단하게 처리할 수 있습니다. 바로 이것이 엑셀의 강력함입니다.

마무리

이 글에서는 엑셀의 조건부서식에 대하여 알아보았습니다. 이 글에서 소개한 두 개의 예제는 매우 간단한 것이고, 이것 이외에도 조건부서식을 이용한 응용 사례는 매우 많습니다. 이 글에서 모든 것을 소개하고 싶지만 그렇게 한다면 글의 양이 매우 많아지기 때문에 일단은 여기까지만 설명하기로 하겠습니다.

엑셀에서 조건부서식을 적용할 때에는 기본적으로 내장된 수식을 사용할 수 있다고 언급했었습니다. 이 글에서는 이것에 대해서 자세하게 설명하지는 않았지만, 내장된 수식은 사용 방법이 매우 간단하기 때문에 직접 모든 옵션을 활용해보는 것을 권장합니다. 직접 이러한 것들을 해보면 어느 순간 조건부서식을 마스터하게 될 것입니다. 다음 글에서 더욱 자세하게 조건부서식을 설명할 수 있을 것으로 생각하고 이 글은 여기에서 마무리하도록 하겠습니다.