DAY1. 고급필터, 조건부서식, 보호기능 및 페이지
1. 고급필터
조건이 1개 이상이며, AND, OR을 이용하여 원하는 조건에 맞는 값만 구하고자 할 때 사용
조건식으로는 1)일반식과 2) 논리식이 있음
일반식의 경우, 조건을 작성 시 표 내 제목과 일치하여야 함
OR 조건의 경우 행을 다르게 작성, AND 조건의 경우 같은 행에 작성함
1) 일반식
예를 들어, 이름이 '이'로 시작하거나 나이가 23살 이상인 사람을 구하고자 한다면
OR 조건이기 때문에 각기 다른 행에 작성하여야 하며, 아래의 경우 일반식으로 작성하였기 때문에 표의 제목과 일치하게
조건 제목을 작성해야 한다.
| 이름 | 나이 |
| 이* | |
| >=23 |
2) 논리식
논리식의 경우, 결과값이 TRUE 또는 FALSE로 나오는 것이 특징이며, 표 제목과 조건 제목이 일치하면 안된다는 점에서
일반식과 큰 차이를 보인다.
예를 들어 이름이 '이'로 시작하거나 나이가 23살인 사람을 구하고자 한다면 다음과 같이 작성할 수 있다.
| 조건1 | 조건2 |
| =LEFT(C3,1)="이" | |
| =C4>=23 |
조건식으로 작성할 경우, 셀의 기준은 값이 적혀있는 가장 첫 행을 기준으로 시작한다.
그리고 논리식의 경우 두 조건을 한 번에 묶어서 작성할 수도 있다.
다음과 같이 AND, OR 을 사용하여 두 식을 콤마(,)로 구분하여 한 번에 묶어 사용할 수 있다.
| 조건1 |
| =OR(LEFT(C3,1)="이",C4>=23) |
여기까지가 고급필터의 조건 설정의 기본 구조이며, 응용버전을 풀기위한 부가 정보는 아래와 같다.
1) 일반식에서 함수 앞에 부등호 사용하고자 할 때 따옴표("")와 앰퍼샌드(&)사용
예를 들어 RANK 함수를 이용해서 평균 이상 넘는 등급만 구하고자 하는 조건을 일반식으로 작성하고 싶다면 일반식에서는 다음과 같이 작성해야 한다.
| 등급 |
| =">="&AVERAGE(전체범위) |
2) 문자열에서 숫자를 추출해서 비교하고자 할 때 -> VALUE 함수 사용
엑셀을 하다가 오류가 뜨면, 식에 문제가 없는데 간혹 오류가 발생하여 애먹는 경우가 있다.
이 경우, 종종 문자열에서 숫자를 추출하여 숫자값과 비교하는 경우, 혹은 숫자를 문자로 치환해서 비교해야하는 경우
변환할 함수를 누락한 경우가 종종 있다.
마찬가지로, 엑셀에서 문자열 사이의 숫자, 혹은 기호와 숫자가 섞여 사용된 경우 함수로 숫자만을 추출했다해서 컴퓨터가 숫자로 인식하는 것은 아니다. 이 경우에는 해당 숫자를 컴퓨터가 '숫자'로 인식할 수 있도록 변환을 해주어야 하는데, 그 함수가 바로 VALUE이다.
사용법은 전혀 어렵지 않다. VALUE(숫자로 치환할 값) 만 넣어주면 끝이다.
예를 들어, 주민번호에서 앞 1자리만 가져온 뒤, 해당 숫자가 8보다 큰지 비교하고자 한다면 다음과 같이 작성해야 한다는 것이다.
=VALUE(LEFT(C3,1))>8
3) 특정 문자열을 찾고 싶을때 - FIND 함수
문자열의 첫번째, 중간, 마지막이라면 LEFT, MID, LIGHT 함수를 사용하면 쉽지만, 문자열에 '김'이 들어가는 것 모두 포함과 같이 위치가 불특정한 문자를 문자열 내에서 찾아야 한다면 어떤 함수를 써야 할지 막막할 것이다.
이처럼, 문자열 내 위치가 불특정한 문자를 찾고자 할때 사용하는 함수가 바로 FIND 함수다.
=FIND(찾는 문자, 찾을문자열, 시작위치) 순으로 작성되며, 문자열의 첫번째부터 시작하고자 한다면 시작위치는 공란으로 비워두면 된다.
그렇다면, '김'이 들어간 모든 문자열을 찾고 싶다면 어떻게 해야할까?
=FIND("김", 셀번호, ) 이렇게 입력하면 된다. 있다면 TRUE 없다면 FALSE가 뜰 것이다.
4) 논리식에서 날짜를 작성할 때는 따옴표("") 사용하자
날짜가 YYYY-MM-DD 이상 이렇게 조건을 작성하고자 할 때, "YYYY-MM-DD"로 작성해야 한다.
2. 조건부 서식
조건부 서식은 내가 원하는 조건에 맞는 값들에 대해서만 서식을 적용하고자 할 때 사용한다.
예를 들어, 고급 필터에서와 같이 나이가 23살 이상이고 이름이 김씨로 시작하는 사람에 대해서만 전체 데이터 중에서 강조해서 보고 싶다면, 조건부 서식을 이용하면 되는 것이다.
조건부 서식은 고급 필터와 다르게 범위를 잡을 시 표 제목까지 잡으면 안된다.
'조건을 걸 범위'에 대해서만 잡아야 한다. 제목까지 잡을 경우, 간혹 제목에도 서식이 걸리는 경우가 있다.
또한, 조건부 서식은 범위를 지정할 때 반드시 위 -> 아래로 지정해야 한다. 역순으로 지정 시 오류가 있다.
조건부 서식을 수식으로 조건을 작성할 경우 시작은 '='으로 시작한다.
조건은 AND 와 OR을 사용하여 여러 조건을 묶을 수 있다.
1) 홀/짝 비교, 배수를 확인하고 싶을땐 MOD 함수
MOD 함수를 사용하면 해당 숫자의 홀/짝 여부와 배수 여부를 확인할 수 있다.
홀짝여부를 구분하고자 할때는 2로 나눈 몫이 1이면 홀수, 0이면 짝수로 구분한다.
배수여부는 원하는 배수로 나눈 뒤 0이면 TRUE 그렇지 않으면 FALSE로 확인한다.
2) 두 날짜의 차이값을 알고 싶을 땐 DAYS 함수
DAYS 함수는 급여계산 시 사용하는 함수로, 두 날짜간의 차를 보여준다.
365일이 아닌 360일로 보여준다는 점이 특징이다.
마지막 날짜가 처음에 시작날짜가 두 번째에 온다는 점에서 헷갈리지 말자.
=DAYS(마지막 날짜, 시작 날짜)
3) 여러 조건에 맞는 셀 개수 구하기 COUNTIF 함수
한가지 조건이 아닌 여러 조건에 부합하는 값만 구하고 싶을땐 COUNTIF를 쓴다.
여기서 특징은 조건에 해당하는 부분은 따옴표("")로 묶어 주어야 한다는 것이다.
=COUNTIF(범위, "조건")
4) 오늘 날짜 구하기 TODAY()
오늘날짜를 구한 뒤, 년, 월,일만 따로 구하고 싶을때가 있다.
그럴 경우 TODAY 함수 앞에 YEAR, MONTH, DAY를 사용하면 되나, 간혹 TODAY에서는 정확한 날짜값이 나왔는데 YEAR, MONTH, DAY 함수를 쓰면서 값이 바뀌어버리는 경우가 있다. 이 경우 셀서식(CTRL +1)에 들어가서 숫자로 서식을 바꿔주면 정확한 값이 나온다.
3. 보호기능 및 페이지
셀 보호 확인하는 법 -> 셀 서식 - [보호]
기본적으로 모든 셀은 보호가 체크된 상태이다.
1) 시트 보호하기 [검토]-[시트보호] : 시트 내의 내용을 수정하지 못하도록 막음
2) 통합 문서보호 [검토]-[시트통합] : 시트 삽입, 삭제, 시트 이름 바꾸기를 수정하지 못하게 막음
3) 인쇄 시 특정 행 반복하게 하기 : [페이지 레이아웃]-[인쇄제목]-[시트]-[반복행 입력]
4) 눈금선, 행 & 열, 머리글 인쇄 : [인쇄제목]-[시트]
5) 인쇄 시 열 우선, 오류값 공백 설정 : [인쇄제목]-[시트]-[페이지순서]-[열우선]
[인쇄제목]-[시트]-[셀오류표시]-[공백]