설찬범의 파라다이스
글쓰기와 닥터후, 엑셀, 통계학, 무료프로그램 배우기를 좋아하는 청년백수의 블로그
컴활 (2)
하루 30분) 엑셀 고급필터
반응형




  고급 필터는 자료를 조건에 맞게 걸러내는 기능입니다. 원래 표에서 원하는 부분만 남길 수도 있고 다른 곳에 표를 다시 만들 수도 있습니다. 원하는 부분만 남기면 나머지는 사라지지 않고 생략됩니다. 이번 포스팅은 다른 곳에 표를 만들어 보겠습니다.

 

  고급 필터 쓰는 법 간단하게



1) 조건식을 빈 곳에 쓴다.

2) 데이터 리본 - [정렬 및 필터] - [고급]을 누른다.

3) 목록 범위는 자료로 정한다.

4) 조건 범위는 1에서 쓴 조건식으로 정한다.

5) 바라는 곳을 복사 위치로 한다.

(‘다른 장소에 복사를 선택했다면)

6) 확인을 누른다.

(자세한 건 예제와 함께)



 

주의. 목록 범위는 자료 제목도 들어가야 합니다.

 


조건식 쓰는 법 - 간단하게



1) 조건을 가리는 항목을 쓴다.

2) 그 밑에 조건을 쓴다.

3)

(자세한 건 예제와 함께)



시작하기 전에 예제파일 받아가세요!


하루 30분 고급필터.xlsx




우리만의 약속



첫째. 예제 파일로 한 번씩 예제 연습하기

둘째. 아리송하면 복습하기

셋째. 부담 없이 잔잔히 즐기기



* 이 포스팅은 엑셀2016을 기반으로 썼습니다.




예제 1) ‘중간고사50 이상인 행을 표시할 것



중간고사

>=50

 


 

예제 2) ‘성별이 여자인 행을 표시할 것




성별

여자



 

 

조건이 여럿일 때



여러 조건이 있다면 AND인지 OR인지 잘 판단해야 합니다.

 

AND : 여러 조건을 동시에 만족해야 함

OR : 여러 조건 중 하나라도 만족하면 됨

 

성별이 여자고 점수가 50점 이상은 여자와 50점 이상을 모두 만족해야 하므로 AND입니다. ‘성별이 여자거나 점수가 50점 이상은 둘 중 하나만 만족하면 조건에 맞으므로 OR입니다.

 

외우자. AND는 같은 줄에 OR은 다른 줄에!

 




예제 3) ‘성별이 남자고 나이50 이하인 행을 표시할 것




성별 나이

남자 <=50



 


예제 4) ‘성별이 남자거나 나이50 이하인 행을 표시할 것



      성별    나이

남자  

              <=50



 

 

원하는 열만 나타내고 싶을 때



바라는 제목들을 입력하고 복사 위치로 지정한다.

 


예제 5) ‘중간고사70점 이상인 행을 이름, 나이, 등록일만 표시할 것



① 미리 원하는 항목을 써둔다



② '복사 위치'를 이 항목들로 한다.





 

예제 6) ‘이름가 들어가는 행만 표시할 것



조건

=FIND("", B3)>=1



 

이름이 아니라 조건이죠?

  고급 필터 조건에 함수나 계산값을 쓰고 싶으면 원본 자료에 없는 제목을 쓰거나 비워야 합니다!

 



참고. 제목을 비워도 조건 범위는 빈칸까지 넣어야 합니다.

 

 

예제 7) ‘등록일7월인 행을 표시할 것



조건

=MONTH(E3)=7



 

 

예제 8) ‘등록일2월이거나 10월인 행을 표시할 것



                           조건 1                     조건 2

=MONTH(E3)=2

                                                              =MONTH(E3)=10

 


 

예제 9) ‘등록일의 일이 10 미만인 행을 표시할 것



조건

=DAY(E3)<10

 


 

예제 10) ‘성별이 남자가 아닌 행을 표시할 것



~가 아닌 조건은 <>가 좋습니다.

 

성별

<>남자

 


 

예제 11) ‘기말고사기말고사평균 이상인 행을 표시할 것


조건

=H3>AVERAGE($H$3:$H$22)

 





 

예제 12) ‘중간고사’, ‘기말고사가 전부 70 이상인 행을 표시할 것



 

첫 번째 방법

중간고사  기말고사

>=70    >=70

 

두 번째 방법

COUNTIF 함수는 조건에 맞는 수를 구하는 함수입니다.

70 이상을 만족하는 점수가 둘인 행을 찾게 합시다.

 

조건

=COUNTIF(G3:H3, ">=70")=2





예제 13) ‘기말고사중간고사보다 높은 행을 표시할 것



조건

=G3<H3

 


 

예제 14) ‘회원번호세 번째 글자가 6 이상인 행을 표시할 것



 

조건

=MID(F3, 3, 1)*1>=6

 



참고. LEFT, MID, RIGHT 함수는 글자를 텍스트로 빼내는 함수입니다. 텍스트를 숫자로 바꾸기 위해 *1을 해줍니다.

 


예제 15) ‘기말고사상위 5등을 표시할 것



LARGE 함수를 응용합니다.

LARGE는 범위에서 ~번째로 큰 값을 반환합니다.

 

조건

=H3>=LARGE($H$3:$H$22, 5)

 



참고. LARGE 함수의 반대는 SMALL입니다.

SMALL은 범위에서 ~번째로 작은 값을 반환합니다.

 

 

여러 조건 한 셀에 쓰기



여러 조건은 나누어 써야 쉽지만, 시험에서 한 줄, 한 셀에 다 쓰라고 시키는 때도 있습니다. 이땐 ANDOR 함수를 써야 합니다.

 



예제 16) ‘성별이 여자고 중간고사30점 미만인 행을 표시할 것(조건은 셀 하나에 쓸 것)




조건

=AND(C3="여자", G3<30)

 



예제 17) ‘나이30 미만이거나 50 초과면서, '중간고사''기말고사'가 모두 80 이하인 행을 표시할 것(조건은 셀 하나에 쓸 것)



조건

=AND(OR(D3<30,D3>50),AND(G3<=80,H3<=80))

 



참고. 조건이 복잡할수록 큰 틀부터 짜맞춥시다.

반응형

'엑셀 > 하루 30분 엑셀카페' 카테고리의 다른 글

하루 30분) 엑셀 조건부 서식  (1) 2018.07.02
  Comments,     Trackbacks
하루 30분) 엑셀 조건부 서식
반응형



  수많은 자료 중에서 원하는 자료만 색을 바꾸거나 굵은 글씨로 만들 수 있다면 얼마나 좋을까요. 한눈에 들어와서 보기도 좋고 쓰기도 좋을 겁니다. 엑셀 조건부 서식은 실용성도 좋지만 컴활 자격증 시험에서 물어보기도 하니 꼭 알아둬야 하겠죠.

 

  엑셀은 이미 여러 조건부 서식을 지원합니다. ~보다 높은 숫자나 상위 몇 퍼센트를 강조하는 식이죠. 기본 내용은 예전 포스트에서도 설명했으니 이번엔 수식을 직접 입력해서 조건부 서식 고수가 되어 봅시다.

 


시작하기 전에 예제 파일 받아가세요!


조건부서식-엑셀카페.xlsx




우리만의 약속


첫째. 예제 파일로 한 번씩 예제 연습하기

둘째. 아리송하면 복습하기

셋째. 부담 없이 잔잔히 즐기기



* 이 포스팅은 엑셀2016을 기반으로 썼습니다.

* 밑 조건식을 그대로 붙여넣기 하면 작동하지 않았습니다. 까닭은 모르지만 참고만 하시고 손수 쓰시기 바랍니다.

 



범위 정하기



  조건부 서식 규칙을 만들기 전에 조건을 적용할 셀들을 지정합니다.

드래그로 범위를 정합니다.

 



주의. 제목 셀은 범위에 넣지 않습니다!

 


조건부 서식 시작하기




  범위를 정하고 홈 리본에 있는 [조건부 서식]을 클릭합니다.

[새 규칙]을 누르고 수식을 사용하여 서식을 적용할 셀 결정을 누릅니다.




  밑에 입력창이 하나 생깁니다. 이곳에 규칙을 입력합니다.


  '서식'에서는 규칙에 맞는 셀에 어떤 서식을 줄지 정합니다. 이번 포스트에서는 전부 하늘색으로 셀을 칠하겠습니다.



 

 



예제 1) 나이가 50세 이상인 사람의 행을 칠할 것



=($D3>=50)





주의. 규칙을 입력할 때 방향키를 누르면 셀 주소가 나타납니다. 입력 커서는 마우스로만 움직입시다!

 

 




예제 2) 남자인 행만 칠할 것



=($C3="남자")



 

참고. C열이 남자가 아닌 행은 조건식이 뭘까요? <>를 씁니다.


=($C3<>"남자")

 


 

예제 3) 회원번호 첫 글자가 A인 사람의 행을 칠할 것



엑셀 LEFT 함수는 셀 텍스트를 왼쪽부터 추출하는 함수입니다.

(LEFT 함수의 첫 인수는 셀 주소둘째 인수는 추출할 글자 수입니다)

 

=(LEFT($F3,1)="a")





참고. 셀 텍스트를 오른쪽부터 추출하는 함수는 뭘까요?

, 맞습니다. RIGHT 함수입니다.

 

 

예제 4) 회원번호 두 번째 글자가 5 이상인 행을 칠할 것



  셀 텍스트를 중간부터 추출하는 함수는 MID입니다.


MID(셀 주소, 추출을 시작할 글자, 추출할 글자 수)

(이 경우에는 셀 주소, 2, 1이겠죠)

 

=(MID($F3, 2, 1)*1>=5)



 

잠깐! ‘*1’이 있죠?

  LEFT, MID RIGHT 함수는 셀 글자를 텍스트로 추출하는 함수입니다. 아무리 숫자를 빼냈어도 텍스트 상태기 때문에 다른 숫자와 비교할 수가 없습니다. 따라서 *1을 해서 숫자로 바꿔야 제대로 서식이 적용됩니다.

 

 

예제 6) 행 번호가 짝수/홀수인 행을 칠할 것



행 번호를 반환하는 함수는 ROW입니다.

ISEVEN 함수는 인수가 짝수면 TRUE를 반환하고

ISODD 함수는 인수가 홀수면 TRUE를 반환합니다.

 

행 번호가 짝수인 행을 칠할 땐

=ISEVEN(ROW())

행 번호가 홀수인 행을 칠할 땐

=ISODD(ROW())



 

참고. 열 번호를 반환하는 함수는 COLUMN입니다.

 

 

예제 7) 행 번호가 3의 배수인 행을 칠할 것



MOD 는 나눗셈 후 나머지를 구하는 함수입니다.

3으로 나누어 나머지가 0이면 3의 배수겠죠?

 

=MOD(ROW(), 3)=0



 




예제 8) 등록일이 8월인 행을 칠할 것



  날짜에서 달수를 추출하는 함수는 MONTH입니다.

 

=MONTH($E3)=8



 

예제 9) 등록 날짜가 15일 이상인 행을 칠할 것



날짜에서 날짜를 추출하는 함수는 DAY입니다.

 

=DAY($E3)>=15



 

 

예제 10) 점수가 상위 10등인 행을 칠할 것



LARGE 함수는 범위에서 ~등인 값을 반환합니다. 이 함수를 응용합시다.

 

=$G3>=LARGE($G$3:$G$22,10)

해석 : 점수 범위에서 10등인 값 이상인 점수 행을 색칠





예제 11) 점수가 평균보다 높은 행을 칠할 것



평균을 구하는 함수는 AVERAGE입니다.

 

=$G3>AVERAGE($G$3:$G$22)



 


예제 12) 여자면서 나이가 40세 이상인 행을 칠할 것



이제 조건이 두 가지입니다.

두 조건을 만족하는 조건부 서식을 쓰는 법은 둘입니다.

 

첫째, AND 함수 이용

둘째, * 이용

 

=AND($C3="여자", $D3>=40)

=($C3="여자")*($D3>=40)



 

참고. AND 함수는 두 조건이 모두 맞을 때만 TRUE를 반환합니다.

 

참고. *AND처럼 TRUE*TRUE일 때만 TRUE를 반환합니다.

반응형

'엑셀 > 하루 30분 엑셀카페' 카테고리의 다른 글

하루 30분) 엑셀 고급필터  (0) 2018.07.03
  Comments,     Trackbacks