설찬범의 파라다이스
글쓰기와 닥터후, 엑셀, 통계학, 무료프로그램 배우기를 좋아하는 청년백수의 블로그
엑셀강좌 (3)
엑셀 조건부서식 완벽가이드
반응형



  조건부 서식이란?


  엑셀 조건부 서식은 말 그대로 조건에 따라 서식을 바꾸는 기능입니다. 서식은 글씨 크기나 셀 색 등을 말합니다. 예를 들어, 학생들의 시험 점수를 나열한 표가 있다고 합시다. 시험 합격 점수는 80점입니다. 여러분은 시험에 합격한 점수만 셀 색을 빨간색으로 바꿔서 누가 합격했는지 더 쉽게 알아보고자 합니다. 엑셀은 데이터와 숫자를 다루는 프로그램이지만 이런 디자인적인 측면은 실생활과 실제 업무에서 매우 중요합니다.


조건부 서식을 실행하는 방법




  조건부 서식은 엑셀 '홈' 리본 중간에 '조건부 서식'이라는 아이콘으로 존재합니다.



조건부 서식에는 어떤 종류가 있나?




  조건부 서식에는 셀 강조 규칙, 상위/하위 규칙, 데이터 막대, 색조, 아이콘 집합이 기본적으로 있으며 여기에 임의의 규칙을 만들 수 있습니다.






1. 셀 강조 규칙


  셀 강조 규칙은 범위 내에서 조건에 맞는 셀들만 골라서 색이나 셀 배경색을 바꾸는 기능입니다.


1) 보다 큼




  '보다 큼'은 말 그대로 제시한 숫자보다 큰 셀을 강조하는 기능입니다. 날짜도 사용 가능합니다.


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 셀 강조 규칙 > '보다 큼'을 누릅니다

  ○ 기준이 될 숫자와 강조할 서식을 선택합니다

  ○ 확인을 누르면 숫자보다 큰 셀은 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



2)  보다 작음




  '보다 작음'은 말 그대로 제시한 숫자보다 작은 셀을 강조하는 기능입니다. 날짜도 사용 가능합니다.



  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 셀 강조 규칙 > '보다 작음'을 누릅니다

  ○ 기준이 될 숫자와 강조할 서식을 선택합니다

  ○ 확인을 누르면 숫자보다 작은 셀은 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



3) 다음 값의 사이에 있음




  '다음 값의 사이에 있음'은 제시한 두 값 사이 값을 지닌 셀을 강조하는 기능입니다. 날짜도 사용 가능합니다.


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 셀 강조 규칙 > '다음 값의 사이에 있음'을 누릅니다

  ○ 기준이 될 두 숫자와 강조할 서식을 선택합니다

  ○ 확인을 누르면 두 숫자 사이의 셀은 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



4) 같음




  '같음'은 제시한 숫자와 같은 값을 지닌 셀을 강조하는 기능입니다.


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 셀 강조 규칙 > '같음'을 누릅니다

  ○ 기준이 될 숫자와 강조할 서식을 선택합니다

  ○ 확인을 누르면 기준 숫자와 내용이 같은 셀은 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



5) 텍스트 포함




  '텍스트 포함'은 제시한 글자가 들어간 값을 지닌 셀을 강조하는 기능입니다. 제시한 글자가 그대로 있을 필요는 없으며, 포함만 되어도 적용됩니다. (예 : '가'를 제시하면 '가나다'를 쓴 셀도 강조됩니다.) 텍스트뿐 아니라 숫자와 특수문자도 가능합니다.


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 셀 강조 규칙 > '텍스트 포함'을 누릅니다

  ○ 원하는 글자와 강조할 서식을 선택합니다

  ○ 확인을 누르면 글자를 포함한 셀은 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



6) 발생 날짜




  '발생 날짜'는 기준에 맞는 날짜 셀을 강조하는 기능입니다. 기준에는 어제, 오늘, 지난 주, 지난 달 등이 있습니다.


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 셀 강조 규칙 > '발생 날짜'를 누릅니다

  ○ 원하는 조건과 강조할 서식을 선택합니다

  ○ 확인을 누르면 조건에 맞는 셀은 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



7) 중복 값




  '중복 값'은 말 그대로 범위 내에서 여러 번 등장하는 값을 강조하는 기능입니다. 날짜와 텍스트도 적용됩니다.


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 셀 강조 규칙 > '중복 값'을 누릅니다

  ○ 강조할 서식을 선택합니다. '중복' 대신 '고유'를 선택하면 중복이 없는 셀을 강조합니다.

  ○ 확인을 누르면 중복되는 셀은 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.








2. 상위/하위 규칙


  상위/하위 규칙은 데이터 높은 값과 낮은 값을 강조합니다. 데이터가 크기순으로 배열되지 않아도 자동으로 큰 수와 작은 수를 찾아서 강조합니다.


1) 상위 10개 항목




  '상위 10개 항목'은 범위 내에서 상위 n개의 항목을 강조하는 기능입니다. 상위 10개라고 표시되어 있지만 꼭 10개일 필요는 없고, 임의대로 바꿀 수 있습니다. 상위에 속하는 셀이 여럿일 경우, 여러분이 선택한 개수보다 더 많이 강조될 수 있습니다.


 실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 상위/하위 규칙 > '상위 10개 항목'을 누릅니다

  ○ 강조할 상위 데이터 개수와 서식을 선택합니다

  ○ 확인을 누르면 상위 N개 셀이 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



2) 상위 10%




  '상위 10%'는 범위 내에서 상위 n%에 속하는 항목을 강조하는 기능입니다. 상위 10%라고 표시되어 있지만 꼭 10%일 필요는 없고, 임의대로 바꿀 수 있습니다. 상위에 속하는 셀이 여럿일 경우, 여러분이 선택한 개수보다 더 많이 강조될 수 있습니다.


 실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 상위/하위 규칙 > '상위 10% 항목'을 누릅니다

  ○ 강조할 상위 데이터 퍼센트와 서식을 선택합니다

  ○ 확인을 누르면 상위 N% 셀이 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



3) 하위 10개 항목




  '하위 10개 항목'은 범위 내에서 하위 n개의 셀을 강조하는 기능입니다. 하위 10개라고 표시되어 있지만 꼭 10개일 필요는 없고, 임의대로 바꿀 수 있습니다. 하위에 속하는 셀이 여럿일 경우, 여러분이 선택한 개수보다 더 많이 강조될 수 있습니다.


 실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 상위/하위 규칙 > '하위 10개 항목'을 누릅니다

  ○ 강조할 하위 데이터 개수와 서식을 선택합니다

  ○ 확인을 누르면 하위 N개 셀이 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



4) 하위 10%




  '하위 10%'는 범위 내에서 하위 n%에 속하는 셀을 강조하는 기능입니다. 하위 10%라고 표시되어 있지만 꼭 10%일 필요는 없고, 임의대로 바꿀 수 있습니다. 하위에 속하는 셀이 여럿일 경우, 여러분이 선택한 개수보다 더 많이 강조될 수 있습니다.


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 상위/하위 규칙 > '하위 10%'를 누릅니다

  ○ 강조할 하위 데이터 퍼센트와 서식을 선택합니다

  ○ 확인을 누르면 하위 N% 셀이 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



5) 평균 초과




  '평균 초과'는 범위 내 숫자들의 평균보다 큰 셀을 강조하는 기능입니다. 


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 상위/하위 규칙 > '평균 초과'를 누릅니다

  ○ 강조할 서식을 선택합니다

  ○ 확인을 누르면 평균보다 큰 셀이 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.



6) 평균 미만




  '평균 미만'은 범위 내 숫자들의 평균보다 작은 셀을 강조하는 기능입니다.



  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 상위/하위 규칙 > '평균 미만'을 누릅니다

  ○ 강조할 서식을 선택합니다

  ○ 확인을 누르면 평균보다 작은 셀이 강조되었음을 볼 수 있습니다.

  ※ '적용할 서식'에서 '사용자 지정 서식...'을 누르면 여러분이 원하는 서식을 적용할 수 있습니다.








3. 데이터 막대




  데이터 막대 기능을 사용하면 셀 내부에 가로 막대기가 생깁니다. 이 가로 막대기는 수치가 클수록 길어지며 셀 내부를 더 많이 차지합니다.


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 데이터 막대를 누릅니다

  ○ 그라데이션 채우기와 단색 채우기가 있습니다. 원하는 옵션을 고릅니다.

  ○ 클릭하면 셀에 데이터 막대가 생기는 것을 볼 수 있습니다..

  ※ '기타 규칙'을 누르면 여러 기능을 바꿀 수 있습니다.

  ※ 기본적으로 데이터 막대는 범위 내 최대값이 최대 막대길이가 되도록 설정되어 있습니다. '기타 규칙'에 들어가면 막대 최소값과 최대값을 바꿀 수 있습니다.

  ※ '기타 규칙'에서는 막대 색, 테두리, 방향과 데이터가 음수일 때의 막대 표시를 설정할 수 있습니다.




4. 색조




  '색조'는 데이터의 크기에 따라 셀 색을 달리하는 기능입니다.



  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 색조에 들어갑니다

  ○ 여러 색조 중 하나를 고릅니다. 

  ※ 위로 갈수록 큰 데이터, 아래로 갈수록 작은 데이터입니다.

  ○ 클릭하면 셀들이 데이터 크기에 맞게 색이 바뀝니다.

  ※ '기타 규칙'을 누르면 여러분이 마음껏 색을 고를 수 있습니다.

  ※ '기타 규칙'에서는 최소값과 최대값 기준을 바꿀 수 있습니다.

  ※ 서식 스타일에는 '두 가지 색조'와 '세 가지 색조'가 있습니다. 두 가지 색조는 값이 커질수록/작아질수록 변할 색을 고릅니다. 세 가지 색조는 값이 커질수록/중간일수록/작아질수록 변할 색을 고릅니다.




5. 아이콘 집합




  '아이콘 집합'은 데이터의 크기에 따라 다른 기호를 셀에 삽입하는 기능입니다.


  실행방법




  ○ 조건부 서식을 적용하기 원하는 범위를 선택합니다

  ○ 조건부 서식 > 아이콘 집합에 들어갑니다

  ○ 여러 아이콘 세트 중 하나를 고릅니다.

  ※ 왼쪽일수록 큰 데이터, 오른쪽일수록 작은 데이터입니다.

  ○ 클릭하면 데이트 크기에 맞게 셀에 아이콘이 나타납니다.

  ※ '기타 규칙'을 누르면 여러분이 아이콘을 바꿀 수 있습니다.

  ※ '기타 규칙'에서는 아이콘마다 나타날 크기 기준, 비율 기준을 바꿀 수 있습니다.

  ※ '아이콘만 표시'에 체크하면 데이터 대신 아이콘만 나타납니다.




여러 규칙 만들기


  ※ 이미 조건부서식을 적용한 셀에도 다른 조건부서식을 적용할 수 있습니다.




  ※  '규칙 관리'에 들어가면 여러 규칙들을 한눈에 볼 수 있으며 삭제, 추가가 가능합니다.



반응형

'엑셀' 카테고리의 다른 글

엑셀 단축키 모음 (기능 위주로, 중요한 것만)  (0) 2018.03.07
  Comments,     Trackbacks
엑셀 할머니 외전 - WEEKDAY 함수 (+CHOOSE)
반응형





민호한테는 말 안 했지만,

알고 싶은 사람들이 있을까 봐

할미가 따로 수업을 준비했어요.






예전 글에서 봤다시피

날짜에서 요일을 알고 싶을 때는

TEXT 함수를 이용하는 것이 제일 쉽다고 했어요.






WEEKDAY함수는 쓰기가 조금

복잡해서요.






하지만 WEEKDAY도 엄연한 엑셀 함수.

알아둬서 나쁠 건 없겠지요.








WEEKDAY 함수란?


 =WEEKDAY( 날짜 일련번호, 반환유형(생략가능) )


날짜 일련번호를 넣으면 요일을 정수로 반환

(1: 일요일, 2:월요일, 3:화요일, 4:수요일, 5:목요일, 6:금요일, 7:토요일)




WEEKDAY 함수는 날짜에 해당하는 요일을 반환하는 함수예요.

요일은 기본적으로 1(일)부터 7(토)까지의 정수 형태로 반환하죠.

WEEKDAY 함수 첫 부분에는 날짜 일련번호를, 두 번째에는 반환유형을 넣어요.

두 번째는 생략 가능하고요.



날짜 일련번호


◇ 일련번호는 1900년 1월 1일부터 1이예요. (그 전 날짜는 쓸 수 없어요)




◇ 날짜 셀로 입력할 수 있답니다.




반환 유형


◇ 반환 유형값은 어느 요일을 1로 할지 정하는 곳이라고 보면 돼요.

◇ 생략하면 1이 일요일이 된답니다.


반환 유형값에 따른 반환 유형

1 (아니면 생략) : 1이 일요일

2 : 1이 월요일

3 : 0이 월요일 (일요일이 6)

11 : 1이 월요일

12 : 1이 화요일

13 : 1이 수요일

14 : 1이 목요일

15 : 1이 금요일

16 : 1이 토요일

17 : 1이 일요일








CHOOSE 함수와 같이 요일 표시하기



보시다시피 WEEKDAY 함수는  반환값이 정수라서 불편하죠.







이때 WEEKDAY 함수와 함께 쓰는 함수가 CHOOSE함수입니다.





= CHOOSE(기준이 될 값, 값이 1일 때의 반환값, 값이 2일 때의 반환값, ....)


기준값에 맞는 반환값을 반환하는 함수





CHOOSE함수는 입력한 인수에 따라 미리 만들어준 목록에 맞는 값을 반환합니다.





CHOOSE 함수 첫째 인수는 입력값입니다. 두 번째 인수는 입력값이 1일 때 반환할 값, 세 번째 인수는 입력값이 2일 때 반환할 값...이죠. 반환할 값은 254개까지 설정 가능합니다. 지금은 7개면 족하지만 말입니다.





=CHOOSE(WEEKDAY(셀주소), "일요일", "월요일", "화요일", ... , "토요일")


CHOOSE 함수 안에 WEEKDAY 함수를 중첩시킨 다음,

각 값에 맞게 반환할 요일 텍스트를 써넣읍시다.

WEEKDAY 함수 반환유형을 정하지 않았으니 1이 일요일이겠죠.

텍스트에 큰따옴표("") 붙이는 거 잊지 마시고요.



보세요. 요일이 나왔죠.




네이버 달력과 비교해 봅시다.

요일이 일치하는군요.




자동 채우기로 나머지 날짜들의 요일도 한 번에 알아낼 수 있죠.





도움이 되셨나요?

앞으로도 많은 엑셀 이야기를 남길 테니

즐겨찾기와 덧글 부탁드려요.




반응형
  Comments,     Trackbacks
엑셀 할머니 1화 - 상대참조와 절대참조
반응형

 

 

어느 늦은 밤...

 

 

 

 

 

민호 "동아리 부부장에 괜히 들어갔나... 이런 밤까지 명세서를 작성해야 하다니..."

 

 

 

  좋아. 다음 주 정기공연 티켓 예상판매급액을 구해야 하네...

  한 사람당 티켓 값은 5000원. 멤버들이 친구들한테 나눠준다면서 가져갔으니 멤버들이 가져간 숫자에 값을 곱하면 되겠지.

 

 

 

  첫 셀에 곱하기 수식을 쓰고,

 

 

밑으로 당기면 자동 채우기가 돼서...

 

 

 

엥? 숫자가 왜 이러지?

 

 

 

뭐야! 가격 셀 주소까지 내려갔네. 여기는 내려갈 필요 없는데.

 

 

 

 

 

  이래서 엑셀이 어렵다니까. 꼭 필요할 때 실수를 해요. 일일이 곱하거나 그냥 숫자를 넣을 수밖에...

 

 

"민호야!"

 

 

 

 

엥? 누가 날 부른 것 같은데...

 

 

 

 

민호야! 나다!

 

 

 

 

헉! 귀, 귀신? 그런데 어디서 본 것 같은데...

 

 

 

 

 

 

민호야! 네 증조할머니다.

 

 

 

 

 

맞다! 증조할머니다. 어릴 적에 돌아가셨는데...

할머니가 여기는 웬일로...

 

 

 

 

웬일이긴. 민호가 엑셀을 못 쓴다고 해서 도와주려고 찾아왔단다.

 

 

 

 

 

 

할머니가 살아계실 땐 엑셀이 있지도 않았을 것 같은데요?

 

 

 

 

 

무슨 말이냐. 저승에서 똑똑한 사람들을 만나면서 많이 배웠지.

 

 

 

 

 

그, 그런가요...

 

 

 

 

 

 

 

 

 

아무튼 지금 자동 채우기에서 셀 주소가 불필요하게 변해서 고민이지?

 

 

 

 

 

아, 네! 자동 채우기로 내리니까

앞에 있는 셀 주소도 같이 내려가네요. 저건 내려갈 필요가 없는데...

 

 

 

 

에잉. 민호는 상대참조절대참조부터 알아야겠구나.

 

 

 

 

상대참조와 절대참조요?

 

 

 

 

그래. 방금 자동 채우기로 채우면서 셀 주소가 같이 바뀌었지?

그렇게 수식이 있는 위치가 바뀌면

참조하는 셀 주소도 바뀌는 방식을 상대참조라고 하지.

 

 

 

그렇군요. 지금은 상대참조가 안 생겨야 좋을 텐데요.

 

 

 

 

그럼 상대참조를 절대참조로 바꾸면 된단다.

절대참조는 수식이 있는 위치가 바뀌어도

참조하는 셀 주소가 바뀌지 않아.

 

 

 

 

정말요? 절대참조만 할 줄 알면 계산할 셀 위치를 고정해두고 자동 채우기를 할 수 있겠네요. 상대참조는 절대참조로 어떻게 바꾸죠?

 

 

 

 

자동 채우기 전 맨 첫 번째 셀로 가렴.

 

 

 

 

 

여기서 안 바뀌었으면 하는 셀 주소에 $을 넣으렴.

행 주소와 열 주소 앞에 각각 하나씩 넣어야 해.

 

 

 

이렇게요? 그 다음 자동 채우기를 하면...

 

 

 

 

우와! 셀 주소가 정말 바뀌지 않네요!

 

 

 

헤헤. 저승에 있는 할미보다 엑셀을 모르면 어떡하니.

또 하나 알려주리?

 

 

 

네, 네! 알려주세요!

 

 

 

 

 

 

 

일일이 $를 넣기가 귀찮으면 셀 주소를 드래그하고

F4를 누르면 절대참조로 바뀐단다. 단축키지.

 

 

 

 

 

 

알겠어요. 고맙습니다. 증조할머니.

 

 

 

 

혹시 행이나 열, 둘 중 하나면 절대참조로 바꾸고 싶으면

그 주소 앞에만 $를 넣으려무나.

$A4라든가 B$2처럼 말이다.

이러면 $가 앞에 붙은 쪽만 바뀌지 않는단다.

 

 

 

할머니의 요점정리

 

상대참조 : 수식이 있는 셀이 바뀌면 참조하는 셀도 바뀜

절대참조 : 수식이 있는 셀이 바뀌어도 참조하는 셀이 바뀌지 않음

혼합참조 : 한 수식에 상대참조와 절대참조가 혼합

반응형
  Comments,     Trackbacks