6. 필터로 골라보기( 기본 / 숫자/색 기준), 바꿔주기(bad데이터), 그룹 데이터 만들기, 자주하는 작업

sql와 엑셀 필터

SQL의 Select / From / Where 절 중, Where절에 해당하는 것이, 엑셀의 필터이다.

필터를 이용해서, 자료를 보는 것만이 아니라, 데이터를 골라낼 수 있어야한다.

자동필터 - 데이터 골라보기

자동필터는 홈탭 > 편집캡션 > 정렬 및 필터 > 필터  / 또는 /  데이터탭 > 필터에 위치해있고, 단축키는 [Ctrl + shift + L ]이다.
필터를 달 데이터에 속하는 하나의 셀을 클릭한 상태에서, 적용시켜야한다.

자동필터를 적용하면, 첫 행에, 역삼각형의 필터가 적용되고, 가려볼 데이터를 선택한 뒤 확인을 누르면 해당 데이터만 볼 수 있다.

실습 - 필터로 데이터 골라보기

필터를 달고, 직위 열의 역삼각형을 눌러보자. 해당열의 중복없는 데이터를 관찰할 수 있다.

모두선택을 체크하여 풀고 ,  과장만 선택해서 데이터를 확인해보자.

필터링 하고 있는 열에는, 역삼각형이 --> 와이파이같은 모양의 아이콘으로 바껴있다.

다시 직위 필터에서, 필터를 해제하자.

이번에는 교통비의 필터를 보자. 빈셀은 (필드 값 없음)으로 뜬다.

이번에는 숫자로만 구성된 기본급 필터를 열고, [숫자필터]를 알아보자. 가장 많이 쓰는 것은 [해당범위] 이다.

숫자필터 > 해당범위를 이용해서, 100만원보다 크고, 200만원보다 작은 기본급을 가진 사람만 필터링 해보자.

숫자필터 > 상위항목 을 이용해서, 상위의  항목(순위) 또는 %(퍼센트)를 알아보자.
 

먼저, 기본급의 상위 10 보자.

다음으로, 상위 20%를 보자. 총 데이터가 35개이므로, 상위 20%는 (35*0.2=7) 7개만 보여질 것이다.

이번에는 기본급에서 특정 셀들만 셀서식-채우기(셀 색)을 입힌 뒤,  필터> 색기준 필터를 이용해보자.

색기준 필터에 들어가면, 셀서식으로 입힌 색과 채우기없음으로 구분이 된다.

보고싶은 데이터만 셀 서식을 입혀두고, 원하는 데이터만 가져올 수 있다.

자동필터2 - Bad 데이터 바꿔주기

빈 셀과 비유효값을 정리해보자.

비유효한값의 예로는,, 숫자로만 구성된 열에, 한글로 표시된 것을 가려내는 것이다.
필터에서, 숫자 데이터 중 한글을 직접 보고 bad데이터를 필터링해서 골라낸 다음, 그것을 전체선택하여 ctrl+enter로 일괄 입력해주면 된다.
빈셀의 경우, (필드값없음)을 필터링해서 똑같은 방식으로 해주면 된다.

실습

필터로, 기본급 열에, bad데이터인 백만원만 골라내보자.

골라낸 bad데이터를 전체 선택해서, 숫자로 바꾸어 ctrl+enter로 일괄 입력하자

교통비라인에서, 빈셀을 필터링하여, 0으로 바꾸어 입력해보자.

자동필터3 - 계급 데이터 만들기

실습을 참고하자.

실습

1. 설계 : 그룹화하고싶은 특정열(기본급 열)을 보고, 나눌 구간을 생각하자.
기본급을   [100미만] /[100~ 200]/  [200~300] / [300 이상]으로 나누어보자.

2. 그룹 데이터를 만들 새로운 열을 추가하자. 기본급을 기준으로 나눌 것이기 때문에,  기본급열 옆에 추가하자.( 기본급 그룹 열)
- 열 추가 단축키 : [ctrl + space ]로  열 선택 ->  [ ctrl + (+)] 로 열 추가

3. 그룹화대상 열을 숫자필터 > [보다 작음/해당범위/보다 큼]로 필터링하여  각 그룹의 데이터들을 뽑아낸뒤, 추가한 열에 각 그룹명을 적어주자.
***해당범위만으로 보다작음을 사용하고 싶다면, 2개조건 중 1개는 빈칸으로 선택해라.

먼저, 100만원 미만 그룹만 뽑아낸 뒤, 추가열을 전체선택하여 그룹명을 ctrl+enter로 일괄 적어주자.

100~200/ 200이상 ~300미만/ 300이상 구간도 똑같이 해준다.

4. 그룹화한 각 구간은 피벗테이블 등 데이터를 구간별로 집계할 때 이용한다.
- 차후에 다루자.

자동필터 적용후 많이 하는 작업 정리

2. 시트행 삭제는 셀만 선택하더라도, 해당 행을 삭제해주는 기능이다.
3. 필터링된,  값만 복사해서 붙여놓고 싶을 때는,  [이동옵션]을 이용해서, [화면에 보이는 셀만]을 선택한 다음 복사해줘야한다.

실습

필터링 된 자료 중에, 시트 행 삭제( 따로 행선택 안하고, 한 셀만 선택하더라도, 해당 행 삭제)를 해보자.
시트행삭제는 [ alt+7]에 추가했었다.  홈탭> 셀> 삭제 > [시트행 삭제]에 위치해있다.

100만원 이하인사람만 필터링하자(그룹데이터는 100만원 미만 이라서, 새로 필터링 따로 해줘야한다)

삭제하고 싶은 셀들을 선택(첫행 선택하면 안됨)한 다음,  시트행 삭제 [alt+7]을 통해, 행 선택없이 바로 해당 행들을 삭제해보자.

|

100만원 이하의 데이터들(행들)이 다 사라졌다.

이제 필터링한 데이터들 중, 해당 행들만 복사해서 다른시트에 [값만 붙여넣기]를 해보자.

직위 중 대리들만 필터링 한 뒤, 모든 데이터들을 선택한 다음, 다른 시트에 옮겨보자.

새로운 시트에서, [ctrl+ alt+ v]를 이용해 값만 붙혀넣자.

이 때, 새로운 시트에서는, 눈치껏 첫행은 남겨두고 붙히자...

안전장치를 해보자.
사실 복사한 데이터들의 [행]을 보게 되면, 필터링 되어 띄엄띄엄 되어있다.
이 때, 중간중간 데이터들이 숨어있게 되어, 복붙시, 다른 값들도 가져오는 경우가 있다.
필터링후 복사할 데이터 선택 -> 필터링 된 값을 Ctrl+c로 복사하기 전에, 항상 [이동옵션]-[화면에 보이는 셀만]을 선택 한뒤, -> Ctrl + c로 복사하자.
이동옵션은 홈탭> 편집>찾기및 선택> 이동옵션에 있다. 단축키 [alt+07]에도 지정되어 있다.