학교에서 업무를 하다 보면 최종 데이터에 원하는 내용이 있는지 확인하거나, 원하는 내용만 특별한 색으로 바꿔서 확인하고 싶을 때가 있다. 원본 데이터를 바꾸지 않고, 원본 데이터 위에 원하는 내용이 포함된 것만 찾아서 색을 칠하게 하면, 한눈에 내용을 파악할 수 있어서 편리하다.
예를 들면 성적이 90점 이상인 학생은 노란색으로 50점 미만인 학생은 초록색으로 칠하게 하거나, 이름이 홍길동인 학생만 색칠 하게 할 수도 있다. 수행평가 점수를 부여한 경우 최저점 보다 낮게 점수가 부여된 학생만 빨간색으로 바뀌게 하면, 수행평가 점수의 오류도 쉽게 확인할 수 있다.
여러 가지 조건을 조합해서 색을 칠하게 할 수도 있다. 예를 들면 1차평가는 90점 이상이면서 총점이 80점 이상인 학생 등등...
조건부 서식만 잘 활용하면 이런것들을 간단하게 해결할 수 있다. 조건부 서식은 사용자가 지정한 조건에 해당하는 셀의 서식을 원하는 형식으로 바꿔 줄 수 있는 기능이다.
지금부터 조건부 서식에 대해서 기초부터 알아보기로 하자.
아래 링크에서 적용된 조건부 서식 결과를 확인하고, 노란색 셀에 직접 값을 입력해서 테스트 해 보자. 가능하면 사본 복사를 해가서 직접 조작해 보기 바란다.
https://docs.google.com/spreadsheets/d/1Bi_xjYusupSF-eh_2j45gwJ2cHWYkzE5iq-87pKe_2g/edit?usp=sharing
<조건부 서식 실행>
조건부 서식 실행은 메뉴에서 서식-조건부서식을 선택하고 적용할 수 있다.
조건부 서식 규칙이 열리면 3가지만 설정해 주면 된다.
첫 번째는 조건부 서식이 적용될 범위를 선택해 준다.(이게 제일 중요하다. 범위 설정이 제대로 되지 않으면 그 다음 부터는 의미가 없다.) 두 번째는 적용될 서식의 조건을 골라 준다. 마지막으로 세 번째는 어떤 색으로 셀서식을 바꿔 줄건지 설정해 준다. 위 그림처럼 선택하면 A1:E14 범위에 있는 성적표에서 점수가 입력이 안된 셀(비어있는 셀)을 찾아 색을 기본값으로 바꾸라는 조건부 서식이 된다.
<주의> 참고로 조건부 서식을 수정하거나 삭제 하고 싶으면 서식이 적용된 범위에 셀을 선택하고 조건부 서식을 실행시켜야 한다. 그렇지 않으면 이미 적용된 조건부 서식이 나타나지 않는다. 필자도 처음에는 이걸 잘 몰라서, 적용한 조건부 서식이 사라졌다는 착각을 하곤 했다.
<조건부 서식 기본>
1. 쉬운 것부터 해보자.
점수가 입력되지 않은 빈칸만 찾아서 셀 색 변환하기
조건부 서식에서 A6:E19 범위(아래 그림에 굵은선 안쪽 범위)를 선택하고, 형식규칙을 비어있음으로 선택하고 확인을 누르면 아래 그림처럼 비었있는 셀들은 모두 색이 변하게 할 수 있다. 점수가 입력되지 않은 학생을 확인할 때 유용하게 사용할 수 있다. 맨 아래칸까지 범위를 설정해 놓았기 때문에 14번 학생칸은 모두 색이 칠해져 있다.
형식규칙에서 비어있지 않음을 비롯한 다양한 형식 규칙을 선택해서 적용할 수 있다.
2. 점수와 관련된 셀 변환하기
예를 들어 학생 점수 중 60보다 큰 점수만 찾아서 색을 변화 시켜 보자.
형식규칙에서 초과를 누르고 60을 입력해 주면, 60보다 큰 점수만 색을 바꿔 준다. 단 주의할 점은 이렇게 하면 총점뿐만 아니라 1차평가나, 2차평가에도 60점 보다 큰 점수가 있으면 색이 바뀐다. 따라서 총점부분만 색이 바뀌게 하려면 범위 설정이 중요하다. 범위를 성적표 전체가 아니라, 총점 부분만 선택해 주면 총점 색만 바뀌게 할 수 있다.
범위를 아래 그림처럼 변경하면 된다.
3. 원하는 이름만 찾아서 색 변화 시키기
이름을 검색할 필요가 있을 때가 많다. 이런 경우에는 조건부 서식 형식 규칙에서 문자와 관련된 규칙을 사용하면 된다. 시작텍스트를 김 으로 하면 김씨성을 가진 학생들을 찾아 색을 바꿀 수 있다. 시작텍스트에 홍길동을 적으면 홍길동 학생만 색을 바꿀 수 있다.
또는 일치하는 문자로 바꾸면 정확하게 이름이 맞는 경우에만 색을 바꿀 수 있다. 찾는 학생이름만 색을 바꾸는게 아니라 찾는 학생이름이 있는 행을 다 바꾸고 싶다면 맞춤수식을 사용해야 한다. 이건 아래쪽에 설명되어 있다.
4. 조건부 서식에 들어가서 일일이 찾을 학생이름을 입력하는 것 보다는 학생이름을 입력할 수 있는 셀하나를 정해 놓고, 그 셀에 이름을 입력하면 찾는 학생의 색이 바뀌게 하면 더 편리하다.
노란색 셀에 이름을 입력하면 범위에서 그 이름이 포함된 셀 색을 바뀌게 하면 된다. 그러려면 형식규칙에서 이름을 직접 입력하는 것이 아니라 이름이 입력된 셀주소를 알려 줘야 한다. 이때 주의할 점이 있다. 노란색 셀이 G61 에 있다면 반드시 형식규칙에는 =$G$61이라고 절대참조로 입력해 줘야 한다. 그럼 지정된 범위에 있는 셀들을 =$G$61 셀과 하나씩 비교해 가면서 일치하는 셀 색을 바꾸게 된다.
<주의> 필자도 처음에는 =G61이라고 상대참조로 쓰고 원하는 결과가 나오지 않아 한참 고생했다. 반드시 절대참조를 사용해야 함을 잊지 말자.
5. 같은 방법으로 일정 점수 이상인 값들도 찾아서 색을 바꿀 수 있다. 숫자 대신 참조할 셀(노란색 칸 셀)을 절대 참조로 입력해 주면 된다. 노란색 셀($G$80)에 입력된 값보다 크거나 같은 셀을 찾아 기본값 색으로 변경
<조건부 서식 – 맞춤 수식>
기본적인 형식규칙보다 다양한 규칙을 적용하고 싶으면 맞춤수식을 이용해서 직접 규칙을 입력해 주면 된다. 맞춤수식을 이용하면 좀 더 다양한 것들을 할 수 있다.
1. 이름이 포함된 행 전체 색 칠하기
조건부 서식 기본에서는 이름과 같은 셀만 색을 변경 할 수 있었다. 이번에는 성적표에서 이름이 포함된 행 전체 색을 변경하는 방법에 대해 알아보자. 형식규칙에서 맞춤수식을 선택하고 수식을 입력해 보자.
=$B6=$G$4 라고 입력하면 된다. 범위는 A6 부터지만 우리는 이름이 같은 사람을 찾아야 하기 때문에 $A6이 아니라 이름이 있는 =$B6을 입력해야 한다. 이는 이름이 있는 B열을 $G$4에 입력된 이름 값과 비교해 가면서 범위내에서 입력된 이름과 같은 행 색을 모두 변경하라는 의미다.
입력된 수식값을 잘 기억하자. $위치를 더 쓰거나, 빼 먹으면 제대로 작동하지 않는다.
<참고> 이 경우 이름이 입려되지 않고 지워져 있으면 범위내에 이름이 입력되지 않은 셀들의 색이 모두 바뀌는 문제가 생긴다. 이걸 예방하려면 맞춤 수식을 if 문을 넣어서 아래처럼 바꿔 주면 된다.
=if($G$4=“”,,$B6=$G$4)
이는 만약 $G$4 에 아무것도 입력되어 있지 않으면 아무것도 하지말고, 입력되어 있을 때 이름과 비교하라는 의미다.
2. 총점이 입력된 점수보다 크면 그 행을 찾아서 모두 색을 바꿔라.
위 이름 검색과 거의 같다. 단 총점만 비교해서 해당하는 행을 모두 색칠해야 하기 때문에 맞춤수식에 보면 처음 비교하는 값이 $E25 즉 총점이 있는 열만 비교한다는 것을 알 수 있다. 노란색 셀은 $G$23
3. 여러 가지 조건 동시에 비교하기
맞춤수식에서 AND 나 OR을 이용하면 한가지 조건이 아니라 여러 조건을 종합해서 색을 바꿀 수 있다. 맞춤 서식에 아래 조건을 입력하면 된다.
가) AND(첫번째 조건,두번째 조건) : 2가지 조건을 모두 만족하는 경우
예를 들면 1차평가 점수가 30점 초과 이면서 총점이 60점 초과인 학생의 색을 바꾸는 경우 맞춤수식에
=AND($C46 > $G$44, $E46 > $G$45) 라고 입력하면 된다.
나) OR (첫번째 조건,두번째 조건) : 2가지 조건 중 1개라도 만족하는 경우
1차평가 점수가 30점 초과이거나 또는 총점이 60점 초과인 학생. 맞춤서식에서 AND만 OR로 바꿔 주면 된다. 두 조건 중 1개만 만족하면 그 행의 색을 변경한다.
=OR($C46 > $G$44, $E46 > $G$45)
<조건부 서식 – 응용>
1. 시간표나 시험감독표에서 이름 확인하기
시간표나 시험감독표에서 검색한 이름이 있는지 확인하기, 이름을 입력하는 셀에 검색할 이름을 입력하면 범위에서 같은 이름을 가진 셀만 모두 색칠해 줌.
검색할 이름이 지워져 있으면 빈칸이 모두 색칠되어 짐. 그걸 막으려면 맞춤서식을 '=if($G$5="",,A6=$G$5) 로 바꾸면 됨. 이렇게 하면 검색할 이름이 없으면 색 칠하지 않음
맞춤 서식에서 A6대신 $A6을 쓰면 A열에서 노란셀에 입력된 이름과 같은 사람을 찾아 그 행 전체 색을 칠해 버린다. 여기에서는 각각의 셀을 모두 비교해야 하기 때문에 맞춤수식에 $A6 이 아니라 A6이라고 적었다.
2. 전체명단 비교하기
조건부 서식에서 형식규칙을 절대참조로 하지 않고 상대참조로 하면 적용된 범위 셀이 위치를 옮길 때 마다 형식규칙의 셀도 함께 옮겨지게 된다. 따라서 아래와 같이 처음 명단과 비교할 명단이 같은지 다른지를 확인할 수 있다.
형식규칙에 $D$27이 아니라 상대참조인 D27임에 주의 하자.
3. 여러 가지 조건 이용하여 검색하기
AND나 OR은 함께 사용할 수도 있다. 논리표현을 함께 사용하면 훨씬 더 복잡한 것도 처리가 가능하다.
1반 학생 중 1차 평가가 30점 넘거나, 아니면 총점이 60점 넘는 학생
=AND($A49=$H$47,OR($D49 > $H$48,$F49 > $H$49))
4. 조건부 서식은 같은 범위에 중복해서 여러개 적용할 수도 있다.
만약 이때는 맨 위쪽에 있는 조건부 서식이 제일 먼저 반영되고, 그 다음 차례대로 그 아래쪽에 있는 내용들이 반영된다. 따라서 같은 조건이 겹치는 경우에는 조건부서식 규칙 순서를 잘 바꿔 줘야 한다.
잘 활용하면 다양한 적용이 가능하다.
조건부서식에 대해 정리해 보았다. 학교에서 성적표나 일람표에 적용하면 오류를 줄일 수 있다. 나올수 없는 수행평가 점수에 색을 칠한다거나, 점수가 입력안 된 학생들만 색을 칠하게 할 수도 있고, 조건에 맞는 학생들만 색을 칠해 빠르게 확인할 수도 있을 것이다.
<추가>
만약 짝수줄만 교차색상으로 바꾸고 싶을 때는
맞춤수식에 =iseven(row()) 이라고 적으면 된다.
