이번에는 실무에서 조건부 서식을 어떤 식으로 사용하는지 소개하려 합니다.
이 방법은 ITQ 시험에도 매번 등장하는 문제기도 하지만, 실제 데이터를 확인할 때 유용한 방법이니 꼭 아셨으면 합니다.
예제 파일은 3차까지의 엑셀 시험 정리한 표입니다. 여기서 합계 250점 미만의 학생은 재수강을 해야 하므로 걸러내려 합니다.
이전에 한 예제와 비슷하죠. 가장 손쉽게 할 수 있는 방법은 "조건부 서식 > 셀 강조 규칙 > 보다 작음"을 통해서 진행할 수 있습니다. 일단 진행 전에 기준값인 D4셀의 값을 확인해 보겠습니다.
D4셀을 클릭하여 선택하고 수식 입력줄의 값을 보면 250임을 확인할 수 있습니다. 표현은 250점(이상)으로 되어 있지만 실제 값은 250이죠. 이 얘기는 "점(이상)"은 실제 작성한 것이 아니고 셀 서식 표시 형식을 변경해서 숫자 250을 250점(이상)으로 보이게 표현했다는 것입니다. 결국 값은 숫자라는 것이고 숫자라면 참조를 통해 계산이 가능하다는 것입니다.
그래서 이번에는 기준값을 입력하지 않고 참조를 통해서 진행해 보겠습니다.
- 조건부 서식을 적용할 합계 범위(G7:G18)를 클릭 드래그하여 선택합니다.
- 조건부 서식 > 셀 강조 규칙 > 보다 작음을 클릭합니다. => 보다 작은 대화상자가 나옵니다.
- 기준값을 250으로 작성해도 되지만, 지금은 참조로 작성해도 됩니다. 왜냐하면 D4셀의 실제 값이 문자가 아니라 숫자기 때문입니다. 만약 문자라면 당연히 숫자의 크기를 비교할 수 없으므로 참조하면 안 됩니다. 그렇다면 어쩔 수 없이 기준값을 이전처럼 숫자로 입력해야 합니다.
D4를 클릭하여 참조합니다. => 절대 참조됩니다.
(참조가 가능하다면 값을 입력하기보다 참조하는 것이 좋습니다. 오타의 확률도 없어지고 기준값을 변경해야 한다면 D4셀만 바꾸면 되기 때문입니다.) - 적용할 서식을 선택하거나 작성합니다.
- 확인 버튼을 클릭하여 선택한 범위에 서식을 적용합니다.
250 미만만 빨강 계열 서식으로 잘 적용되었습니다.
이렇게 참조를 통해 조건부 서식을 적용하면 통과 기준을 240으로 수정하고 싶을 때 "조건부 서식 > 규칙 관리"로 들어갈 필요가 없습니다. 그냥 D4셀의 값을 240으로 변경하면 자동 적용됩니다.
결과, 우측 그림처럼 D4셀의 값이 바뀌면 자동으로 조건부 서식이 변경되는 것이 확인됩니다. 조건부 서식의 규칙은 유지한 채 조건의 기준값만 바꾼 것이죠.
이렇게 조건부 서식의 기준값을 참조할 수 있다는 것도 꼭 기억해 주세요.
서론이 길어졌는데, 하고 싶은 얘기는 지금부터입니다.
실제 우리가 찾고자 하는 것은 합계가 240점 미만을 찾는 것은 아닙니다. 240점 미만 학생 이름을 찾는 것이죠. 즉 강조된 합계의 셀을 보고 좌로, 좌로, 좌로... 찾아 이름까지 가야 하는 것이죠.
그림처럼 실제 찾고자 하는 것은 "남궁만, 이연생"이라는 것입니다.
지금이야 학생 수도 몇 안 되고 시험도 3차까지 밖에 없어 그다지 어렵지 않지만, 만약 시험이 8차였다면,
그림 같은 결과가 되어 이름 확인이 어려워집니다. 물론 화면을 축소하거나 행 머리글의 번호를 확인하여 찾을 수도 있겠지만, 직관적이진 않죠. 뭐가 됐든 한 가지 작업을 더 해야 한다는 것이고 실수할 확률이 높아지게 됩니다.
그런데 만약 합계 셀에만 서식을 적용하는 것이 아니라 적용된 셀의 행 전체에 서식을 적용하게 되면,
그림처럼 바로 실제 찾고자 "남궁만, 이연생"을 찾을 수 있다는 것이죠. 학생이 아무리 많아도, 시험의 차수가 아무리 많아도 문제 될 소지가 없어집니다.
그래서 실무에서는 이렇게 조건부 서식을 적용할 때는 기본이 전체 행을 강조하는 방식을 사용합니다.
왜 이런 방식을 사용하는지를 이해했다면, 어떻게 해야 하는지를 생각해 보겠습니다.
지금까지 알아본 "셀 강조 규칙과 상위/하위 규칙"에서는 기준값과 자기 자신(합계)의 셀 값을 비교하고 서식을 변경했습니다. 하지만 번호 열, 이름 열, 1차, 2차, 3차 열 모두 자기 자신의 셀 값과 기준값을 비교하면 안 됩니다. 남궁만과 240을 비교하는 것이 아니라 남궁만의 합계 값과 240을 비교해야 한다는 것입니다. 즉, 각 행의 셀들은 모두 G열의 값을 참조하여 기준값과 비교해야 한다는 것이 됩니다.
7행의 모든 값은 G7의 셀 값을, 8행의 모든 값은 G8의 셀 값을, 9행의 모든 값은 G9의 셀 값을, 10행의 모든 값은 G10의 셀 값을... 참조하여 기준값인 240과 비교하고, 서식은 자기 자신을 변경해야 한다는 결론에 이르게 됩니다. 복잡해 지죠. 일단 지금까지 적용한 조건부 서식을 지우겠습니다.
그림의 순으로 적용한 조건부 서식을 삭제합니다.
이제 조건부 서식을 다시 적용하여 전체 행 강조를 하겠습니다. 하지만 자기 자신의 셀 값과 기준값을 비교하려는 것이 아니기 때문에 "셀 강조 규칙"과 "상위/하위 규칙"은 사용할 수 없습니다. 그렇다면 규칙을 내가 원하는 형태로 만들어 줘야 합니다. 새 규칙을 만드는 것이죠.
- 표 전체에 셀 서식을 적용해야 하므로 표 레코드 모든 범위(B7:G18)를 클릭 드래그하여 선택합니다.
- 홈 탭 > 조건부 서식 > 새 규칙을 클릭합니다. => 새 서식 규칙 대화상자가 나타납니다.
- 규칙 유형에서 "수식을 사용하여 서식을 지정할 셀 결정"을 클릭합니다. => 아래 규칙 설명 편집 영역이 바뀝니다.
- 이제 가장 중요한 부분입니다. 수식을 사용하려면 수식 입력줄이 필요한데 그 역할을 하는 것인 바로 "다음 수식이 참인 값의 서식 지정(O):"입니다.
- 수식의 시작은 "="부터였습니다. "="를 입력합니다. => =
- 비교할 값은 "합계 값"이니 시작 행인 7행은 모두 G7셀의 값으로 비교해야 합니다. G7셀을 클릭하여 참조합니다. $G$7로 절대 참조됩니다.
그런데 여기서 이번 강좌의 핵심이 나옵니다. 비교할 값이 절대 참조되면 다음 행도 그다음 행도 모두 G7셀의 값인 252와 기준값인 240을 비교하게 될 것입니다. 원하는 결과가 아니겠죠. 모두 같은 결과를 반환하게 될 것입니다. 다음 행은 G8, 그다음 행은 G9, 그다음은 G10...을 기준값인 240과 비교해야 합니다. 즉 열이 고정되고 행이 변하는 혼합 참조가 돼야 한다는 것입니다. 혼합 참조를 위해 F4키를 반복해서 누르고 $G7로 만들어 줍니다. => =$G7 - 비교할 값은 기준값 미만이어야 하니 "<"를 입력합니다. => =$G7<
- 마지막으로 기준값 D4셀을 클릭하여 참조합니다. 여기도 마찬가지 절대 참조됩니다. 기준값은 변경되면 안 됩니다. 만약 혼합 참조나 상대 참조되면 B7셀의 조건부 서식이 적용된 후 C7셀로 이동해서 조건부 서식을 적용하려 할 때 기준값 역시 D4에서 H4 이동하게 되서 기준값이 없어지게 됩니다. 기준값은 반드시 절대 참조야 돼야 합니다. => =$G7<$D$4
- 새 규칙의 조건이 완성되었습니다. 서식 버튼을 클릭하고 원하는 서식을 만들어 줍니다.
- 최종 확인 버튼을 클릭하고 새 규칙을 선택한 범위(B7:G18)에 적용합니다.
조건에 맞은 행 전체가 서식이 적용된 결과를 얻을 수 있습니다.
이번 강좌에서 조건부 서식의 새 규칙을 만드는 것은 사실 별로 어려운 것은 없습니다. 하지만 역시 여기서도 "절대 참조"와 "혼합 참조"의 사용이 등장합니다. 이 개념이 부족하면 어렵게 느껴질 수밖에 없습니다. 잘 이해가 안 된다면 이전 강좌 중 "참조와 자동 채우기"편 5개의 강좌를 다시 확인하시고 상대, 절대, 혼합 참조의 개념을 반드시 정립하시기를 바랍니다. 앞으로도 계속 나올 것이니 원리를 파악하지 못하면 계속 어려울 수밖에 없습니다.
의견글 입니다.
()
