1. 최근 5년간 시군구 총인구수 자료 만들기
먼저 국가통계포털(https://kosis.kr/index/index.do)에 접속한 후 국내 통계 - 주제별 통계, 인구 > 인국총조사 > 인구부문 > 총조사인구(2015년 이후) > 전수부문 > 전수기본표에서 '인구, 가구 및 주택 - 읍면동, 시군구를 누릅니다.
그러면 기본값이 2021년도만 데이터를 보여주므로 콤보 상자 버튼을 누른 후 최근 5년으로 변경하고, 오른쪽 위 '다운로드' 버튼을 눌러 엑셀로 다운로드합니다.
그리고 파일을 열면 파일 형식과 확장명이 일치하지 않는다고 하면서 열 것인지를 묻는데, 예를 클릭합니다. 이것은 파일 형식은 xlsx인데, 확장명은 xls라 그런 것입니다.
연 다음 확장명을 xlsx로 바꾸는 것이 좋습니다. 다른 이름으로 저장 시 파일명에 .xls가 있어서 그것까지 파일명으로 되므로 지워야 합니다.
내용을 살펴보니 총인구뿐만 아니라 내국인-계, 내국인-남자 등 항목이 너무 많습니다. 총인구만 남겨두고 지우겠습니다.
Ctrl + Shift + L키 또는 홈 탭 > 편집 그룹 > 정렬 및 필터 명령을 누른 후 다시 필터를 누릅니다. 그러면 아래와 같이 2행에 필터가 걸립니다.
항목에서 총인구만 남겨두고 나머지는 지우기 위해 항목오른쪽의 필터 콤보 상자 버튼을 누른 후 '총인구(명)만 체크를 해제하고 확인 버튼을 누릅니다.
그러면 총인구를 제외한 나머지 항목이 모두 표시됩니다.
A4셀을 클릭한 후 Shift + Ctrl + End키를 눌러 작업 영역의 오른쪽 아래 끝으로 이동합니다. 그러면 아래와 같이 데이터의 끝으로 이동됩니다.
이제 데이터 영역에서 마우스 오른쪽 버튼을 누른 후 삭제 메뉴를 클릭합니다. 행을 나타내는 숫자 부분을 누르면 그 행만 선택되므로 행 숫자 부분을 누르면 안 됩니다.
행 삭제 메뉴를 누르면 '시트 행 전체를 삭제할 것인지' 묻는데 확인 버튼을 누니다.
데이터가 모두 지워졌습니다.
항목을 누른 후 모두 선택을 누릅니다.
이번에는 행정구역별에서 읍부, 면부, 동부를 지웁니다.
행정구역별의 필터 콤보 상자 버튼을 누른 후 모두 선택을 눌러 선택을 모두 해제한 후, 동부, 면부, 읍부에만 체크하고 확인 버튼을 누릅니다.
그러면 읍부, 면부, 동부에 해당하는 데이터만 표시됩니다. 이번에는 4행을 클릭한 후 Shift + Ctrl + ↓키를 눌러
데이터의 맨 아래줄로 이동합니다. 그리고, 마우스 오른쪽 버튼을 누른 후 행 삭제 메뉴를 눌러 데이터를 모두 삭제합니다.
그리고, 행정구역별에서 모두 선택을 누릅니다.
2. 조건부 서식 적용하기
가. 상위 10% 규칙 적용
H열을 클릭해서 2021년에 해당하는 데이터를 선택한 후 홈 탭 > 스타일 > 조건부 서식 명령을 누른 후 상위/하위 규칙 > 상위 10%를 클릭합니다.
그러면 먼저 조건부 서식이 적용되면서 상위 10% 창에 10%, 적용할 서식으로 '진한 빨강 텍스트가 있는 연한 빨강 채우기'가 표시됩니다. 확인 버튼을 누릅니다.
이제 2021년 오른쪽의 콤보 상자 버튼을 누른 후 색 기준 필터를 누릅니다. 그러면 셀 색 기준 필터와 글꼴 색 기준 필터가 표시되는데 적용된 조건부 서식이므로 아무거나 눌러도 됩니다.
조건부 서식이 될 경우 조건에 맞는 데이터가 뚝뚝 떨어져 있어 찾기 어려운데 이때 유용한 것이 색 기준 필터입니다.
나, 합계, 숫자 개수 구하기
H283셀에 커서를 놓고 자동 합계를 눌러 합계를 구하는데 이상하게 더할 범위가 일부만 선택됩니다. 이 때는 선택된 윗부분의 오른쪽 네모 점(채우기 핸들)을 클릭한 후 위로 끌면 됩니다.
그러면 H60만 H3으로 수정됩니다. 엔터 키를 눌러 수식을 완성합니다.
완성된 수식은 =SUBTOTAL(9,H3:H282)으로, sum이 아니라 subtotal함수가 사용됐고, function_num으로 합계에 해당하는 9가 사용됐습니다.
H284셀에 커서를 놓고 숫자 개수를 구해보겠습니다.
자동 합계 오른쪽의 콤보 상자 버튼을 누른 후 숫자 개수를 클릭합니다.
마찬가지로 범위가 이상하게 설정되는데 아래 네모 점을 한 줄 위로 올리고, 위 네모 점도 위로 끌어 범위를 조정합니다. 아래 네모 점에 커서를 놓으면 네 방향 화살표가 표시되는데 이 때는 위로 이동할 수 없고, 양 방향 화살표 상태가 돼야 범위를 조정할 수 있습니다.
엔터 키를 눌러 수식을 완성합니다. 그런데 수식의 결괏값이 너무 큽니다. 이것은 숨겨진 행을 포함해서 세서 그렇습니다.
이 때는 Subtotal 또는 Aggregate함수를 이용해 숫자 개수를 세야 합니다.
subtotal함수를 사용할 때는 function_num으로 2를 선택한 다음 범위를 마우스로 끌어서 지정해야 합니다.
완성된 수식은 =SUBTOTAL(2,H3:H264)이고, 결괏값은 27입니다.
Aggregate 함수를 사용할 때는 function_num은 2로 같은데, options로 숨겨진 행을 무시하는 2, 3, 5 또는 7중 하나를 선택하고, 범위를 지정해야 합니다.
완성된 수식은 =AGGREGATE(2,7,H3:H264) 입니다.
'Excel' 카테고리의 다른 글
| 엑셀에서 전체 범위 선택하기 - 단축키 (0) | 2023.03.14 |
|---|---|
| 조건부 서식과 색 기준 필터(2) (2) | 2023.03.01 |
| CountIfs는 *이 적용되나, SumProduct는 안됩니다. (0) | 2023.02.27 |
| 숫자로만 된 날짜+시간을 날짜, 시간 형식으로 바꾸기 (0) | 2023.02.26 |
| 색 기준 필터, 고급 필터, sum+if 배열 함수 (0) | 2023.02.23 |