엑셀(excel) TIP 92 - (병합된 셀 중복된 항목제거, 단축키로 데이터 전범위선택, 보험사별 연금저축수익률표/예)
아래은 엑셀의 데이터입니다. 금융감독원을 통해서 조회한 [생명보험회사별 연금저축 중 금리연동형의 연도별 연간수익률과 연평균수익률]을 조회한 표입니다. 현재 판매가 되고 있는 것도 있고 판매가 중지가 된 상품도 있습니다. 여기에서 조회하면 아래와 같이 보험회사별로 판매하고 있는 연금저축 479개항목이 표출이 됩니다. 이 중에 보험회사별로 하나의 대표상품만을 나타내도록 하겠습니다.
엑셀(EXCEL)에서 데이터 값들이 아래와 같이 중복될 경우 특정 열 항목을 지정해서 중복된값을 삭제하고 대표값 1개만은 추출을 할 수가 있습니다. 아래는 479개의 상품을 보험회사별로 1개씩만 추출을 해서 19개의 상품만을 추출한 결과값입니다.
● 중복된 항목(보험회사명)을 제거한 결과값
중복된 항목의 엑셀의 데이터 시트
아래에서 보시는 것처럼 A열의 값들이 [보험사 명]으로 중복이 되어 있습니다. 이 화면에서는 보이지 않지만 19개사의 총 479개의 상품이 있습니다. 이 대 '상품명'이 아닌 '보험회사명'에서 '중복된 값 제거하기'를 해보도록 하겠습니다. 즉, 보험회사별로 중복된 회사를 제거하고 1개씩만 추출하는 작업입니다.
아래와 같이 전체데이터 범위(또는 데이터값이 있는 어느 한셀)를 선택해서 중복된 항목을 제거를 하게 되면 아래와 같이 '경고문구 / 이 작업을 수행하려면 병합하려는 셀의 크기가 동일해야 합니다'라는 문구가 나타납니다. 즉, 중복된 항목이 제거가 되지 않습니다. 이 경우에는 하단에서처럼 병합된 셀이 있기 때문입니다.(파란색 네모/보험사명, 상품명, 상품유형) 따라서 병합된 셀을 '셀을 분할'한 후에 작업을 하면 됩니다. 또는 아래와 같이 병합된 셀 이외의 셀을 선택해서 작업하면 됩니다.
병합된 셀(열머리글) 선택하지 않고 데이터 전범위 단축키로 선택하기
아래와 같이 병합된 행(1,2행)은 선택하지 않고 3행부터 끝까지 선택을 하면 됩니다. 엑셀에서 전체범위선택 단축키는 CTRL+SHIFT + →,↓입니다. 아래에서 하는 방법으로 [3행 행머리글 선택(빨간네모) > CTRL+SHIFT, ↓]를 하면 데이터의 전범위가 선택이 됩니다. 이 상태에서 '중복된 항목제거'를 하면 됩니다.
중복된 항목 제거하기 : 중복제거할 데이터 전 범위 선택 > 중복된항목 제거 > 모두선택 취소 > 원하는 열머리값 체크(열 A) > 확인
아래와 같이 전체 479개의 중복항목에서 19개의 고유한 값만 나타낸다는 문구가 나타납니다. 즉 '보험사'명이 중복된 479개 중에서 460개가 중복이 되어 있어서 이 값을 제거를 했습니다.
중복된 항목 제거된 실제 엑셀의 데이터 값(SHEET)
| 보험사명 | 상품명 | 상품유형 | 직전 3년 연간수익률(%) | 연평균수익률(%) | ||
| 2014년 | 2015년 | 2016년 | ||||
| 한화 | 연금저축 하이드림연금보험 | 금리연동형 | 4.02 | 3.96 | 3.81 | 4.59 |
| ABL | 연금저축 나이스플랜연금보험(60세) | 금리연동형 | 4.37 | 3.51 | 2.6 | 4.29 |
| 삼성 | 연금저축골드연금보험(공시이율형) | 금리연동형 | 3.87 | 3.02 | 2.53 | 4.2 |
| 흥국 | 연금저축 신세기연금보험(3개월) | 금리연동형 | 3.58 | 3 | 2.74 | 3.2 |
| 교보 | 연금저축베스트라이프연금보험 | 금리연동형 | 3.84 | 3.24 | 2.95 | 4.13 |
| 신한 | 연금저축 참알찬연금보험 | 금리연동형 | 3.91 | 3.78 | 3.52 | 4.14 |
| 현대라이프 | 연금저축 마이라이프 연금보험 | 금리연동형 | 3.11 | 3.69 | 3.73 | 4.43 |
| KB | 연금저축KB프라임연금보험 | 금리연동형 | 3.97 | 2.88 | 2.42 | 2.85 |
| DGB | 연금저축 라이프플랜보험 | 금리연동형 | 3.24 | 2.66 | 2.6 | 3.31 |
| KDB | 연금저축 노후사랑연금보험 | 금리연동형 | 3.49 | 3.54 | 3.59 | 3.98 |
| 미래에셋 | 연금저축OK연금보험 | 금리연동형 | 3.61 | 2.79 | 2.58 | 3.79 |
| IBK연금 | 연금저축 IBK연금보험 | 금리연동형 | 1.33 | 1.38 | 1.2 | 0.74 |
| 농협 | 트리플에이연금공제 | 금리연동형 | 4.1 | 3.9 | 3.79 | 5.21 |
| 교보라이프 | 무배당라이프플래닛e연금저축보험 | 금리연동형 | -3.97 | 0.34 | 1.51 | 0.72 |
| ING | 연금저축 세테크플랜 연금보험 | 금리연동형 | 1.55 | 1.4 | 1.18 | 0.85 |
| 하나 | (무)행복knowhow연금저축보험 | 금리연동형 | -30.43 | -4.12 | -2.91 | |
| 동부 | 연금저축 강력추천 연금보험 | 금리연동형 | 2.51 | 2.55 | 2.35 | 2.84 |
| 메트라이프 | 연금저축 하이라이프연금보험 | 금리연동형 | 3.96 | 3.64 | 3.66 | 3.91 |
| 동양 | 연금저축수호천사한아름연금보험 | 금리연동형 | 3.57 | 4.05 | 4.22 | 4.25 |
시트(SHEET)값 예쁘게 꾸미기
중복된 항목을 제거하고 글꼴색, 셀바탕, 글꼴 등에 다양한 설정을 해서 예쁘게 꾸민 최종 표입니다.
