더불어사는 세상

엑셀 22(다중센선택 및 특정글자 찾기 및 바꾸기(없애기)하이퍼링크제거, 셀나누기 등

하단(엑셀 2007 표)는 국민주택, 민영주택 및 민간건설중형국민주택에 대해서 주택청약종합저축 또는 청약예금금리표입니다. 금리가 2.5%대에서 1.4%대까지 있으며, 1년 납부시 이자(세전수령액, 세후이자액)이 나와 있습니다. 셀분리전과 셀분리후로 나뉘어져 있지만 셀 분리전에 가공되지 않은 데이터를 가공해서 우측과 같이 꾸며보도록 하겠습니다.

여기에 사용된 기능으로는 다양하며, 다중센선택 및 특정글자 찾기 및 바꾸기(없애기)하이퍼링크제거, 셀나누기등이 적용이 되었습니다. 또한 단축키를 사용해서 빠르게 작업하는 방법에 대해서도 알아보겠습니다.

◆ 가공전의 표와 가공 후의 표 비교(은행별 주택청약종합저축 금리표) 

■ 엑셀에서 다중셀 선택해서 복사, 붙여넣기

엑셀에서는 CTRL키를 이용해서 한개의 행 또는 열, 셀만을 다중선택해서 복사 > 붙여놓기를 할 수가 있습니다. 바로 하단이 그 예이며 좌측의 9열,13열, 16열에 있는 셀들을 컨트롤키를 이용해서 선택후 복사 붙여넣기 한 결과(우측)입니다. 하는 방법으로는 간단한데 [CTRL + 해당셀 하나하나 마우스로 선택 >  복사(단축키/ CTRL+C) > 붙여놓고자 하는 셀 선택 > 붙여넣기(단축키 CTRL+V]입니다.  

■ 엑셀에서 특정글자 삭제하기 : 찾기 및 바꾸기 이용

아래는 은행별 주택청약종합저축 금리비교 표입니다. 하단 엑셀데이터에서 [세전수령액]과 [세전이자액]이라는 단어를 전체삭제하는 방법입니다.

[찾기단축키/ctrl+f > 찾을 내용(세전수령액) > 바꿀내용(공란) > 모두바꾸기]

위와 같이 [찾기 및 바꾸기]를 이용하면 해당 글자 전체(세전수령액)를 삭제할 수가 있습니다. 마찬가지로 [세전이자액]이라는 단어도 같은 방법으로 삭제가 가능합니다.

■ 엑셀 하이퍼링크 한꺼번에 제거(해제하기) : [alt + F11(키보드) > cells.hyperlinks.delete]을 순서대로 하시면 됩니다

하단에서 좌측의 은행은 전체 하이퍼링크가 적용이 되어있습니다. 이를 제거하기 위해서 은행을 하나하나 선택해서 하이퍼링크를 제거(해제)를 하려면 시간이 많이 소요됩니다. 이를 한꺼번에 제거하는 방법이 바로[visual basic]을 이용하는 방법입니다. 아래와 같이 좌측의 하이퍼링크가 전체 해제되어 우측처럼 결과가 나타납니다.

■ 엑셀에서 셀나누기(분리하기)

아래와 같이 은행별 주택청약종합저축, 청약예금금리표가 있습니다. 좌측의 셀에 은행별 [금리, 세전수령액, 세후이자액]이 한 셀에 다 들어 있습니다. 이를 셀을 분리하여(셀은 나누어서) 한셀, 한셀에 넣는 작업을 해보겠습니다. 바로 셀나누기, 셀분리하기 기능입니다.

[분리하고자 하는 셀 선택 > 데이터메뉴 > 텍스트나누기 > 3단계중 1단계(너비가일정함) > 2단계]

* 셀 분리는 구분기호로도 분리할 수 있고 너비로도 분리가 가능합니다. 하단은 구분기호가 없고 너비가 일정하기 때문에 너비로 분리하겠습니다.

[2단계 : 화살표를 선택해서 좌우로 이동해서 셀 분리 > 다음 ]

[3단계 : 다음 > 마침]

하단과 같이 은행별 주택청약저축과 청약예금의 [금리, 세전수령액, 세후이자액]이 셀분리 되었습니다. 엑셀에서 셀분리 빠르고도 간편하게 이용이 가능합니다. 좌측(셀분리전)과 우측(셀분리/나누기)의 비교모습입니다.  

■ 셀분리 결과 표

엑셀 19(엑셀의 셀병합, 분할종류/병합후 가운데맞춤, 전체병합, 셀 분할)

엑셀(excel 2007, 2010)에서 하단과 같은 데이터가 있을 경우 셀 병합을 해보도록 하겠습니다. 하단은 은행별 주택청약저축 금리입니다. 우리은행, 하나은행, 농협, 우리은행의(주택청약종합저축, 주택청약저축)의 금리가 2.5%로 동일함을 볼 수가 있습니다.

엑셀에서 셀병합은 간단하게 [원하는 셀 선택 > 셀 병합메뉴(아이콘)클릭]을 하시면 됩니다. 엑셀에서 셀 병합은 아래와 같이 4가지로 구분이 됩니다. [병합하고 가운데 맞춤, 전체병함, 셀 병합, 셀 분할]입니다. 하나하나의 기능에 대해서 살펴보겠습니다. 

◆ 엑셀의 셀 병합의 종류(4가지) 

◆ 셀 병합하고 가운데 맞춤

하단에서 보시는 것 처럼 해당 범위를 선택하고 [셀 병합하고 가운데 맞춤]을 클릭시에 좌측의 [우리은행]이란 단어만 하나 나타나고 나머지 단어는 삭제가 됩니다. 우측에 보시는 것 처럼 셀이 병합되면서 가운데에 우리은행글자만 생성이 된것을 확인할 수 있습니다.  

◆ 셀 병합

두번째는 셀 병합입니다. 이 셀 병합을 할 경우 위의 [병합하고 가운데 맞춤]과 동일하게 셀은 합쳐지는데 다만 글자가 좌측 상단의 중앙에 위치한 것을 볼 수가 있습니다.

◆ 셀 전체병합

셀 전체병합의 경우에는 셀의 행들이 하나로 병합이 됩니다. 즉, 좌측 1행의 텍스트(단어)들만 나타나면서 나머지 행들의 단어는 삭제가 됩니다. 여기에서 전체라는 의미는 [행의 전체]를 의미하며, 행의 전체를 병합하되 첫 행의 텍스트만 남겨라라는 의미입니다.

◆ 셀 분할

셀 분할은 병합되지 않은 셀은 분할할 수가 없습니다. 기존에 병합을 했던 셀을 다시 분할(나누기)하는 것입니다. 다만 기존에 병합하면서 삭제가 되었던 글자는 다시 복구가 되지 않습니다. 마찬가지로 병합 후 있던 나머지 단어(하단/금융기관명)만 그대로 나타납니다.

엑셀 17(행과 열을 바꿔서 복사,붙여넣기/은행의 단기고금리 수시입출 MMDA상품 예)

수시입출식 통장이면서 고금리를 제공하는 상품으로 MMDA상품이 있습니다. 종금,증권의 고금리수시입출식통장이 CMA라면, 은행의 고금리 수시입출금통장으로는 MMDA가 있습니다. 저축성예금이지만 보통예금과 같이 각종 결제와 이체 수시입출금이 가능한 장점이 있습니다. 예금자보호법에 의해서 최대 5천만원까지 보호가 됩니다.

아울러 MMDA상품의 특징으로는 부자들의 재테크 상품으로 적합한데 대부분의 상품이 1천만원에서 1억원 이상까지 가입이 가능하기 때문에 어느정도 목돈이 필요하며, 목돈굴리기 상품으로 적합하다 할 수 있습니다. 단점은 500만원 미만의 소액(물론 서민에게는 큰돈일 수 있지만)이거나 법인으로 예치하는 기간이 7일미만인 경우에는 금리가 낮습니다.

● 은행별 고금리 수시입출식 통장 MMDA상품 금리(1억원 1년 예치시 세전금리)

● 엑셀에서 복사/붙여넣기를 할 때 행과 열 바꾸기

엑셀에서 복사 후 붙여넣기를 할 경우 행과 열을 바꾸기할 수가 있습니다. 일반적으로 엑셀에서 데이터의 종류는 열머리글을 중심으로 열로 정리를 합니다. 하지만 경우에 따라서는 행을 기준으로도 정렬을 할 필요가 있습니다. 이 경우 유용하게 사용할 수 있는 기능입니다.

☞ 선택하여 붙여넣기

[복사할 셀 범위 선택 > 복사하기 단축키(CTRL + C) > 마우스오른쪽 클릭 > 선택하여 붙여넣기]

☞ 행과 열 바꿔서 복사/붙여넣기

[선택하여 붙여넣기 > 행열바꿈 > 확인]을 순서대로 하시면 아래의 우측과 같이 행과 열이 바껴서 복사된 겻을 볼 수가 있습니다.

엑셀 15(셀안의 텍스트 구분기호[공백]으로 나누기/분리하기/저축은행 정기예금금리표)

엑셀 14번(셀안의 텍스트를 기호로 구분하기(바로가기)는 지난시간에 말씀 드렸습니다. 기호로 텍세트가 일정 규칙으로 구분이 되어 있다면 텍스트를 기준으로 나눌수가 있습니다. 아울러 하단과 같이 저축은행별 정기예금금리표에서 중간에 일정 공간이 규칙적으로 삽입이 되어 있다면 이를 기준으로 해서 텍스트(문자)를 분리할 수 있습니다. 즉 한 셀을 두 셀로 나눌수가 있습니다.

저축은행의 정기예금금리(비교)는 대아저축은행의 정기예금금리가 2.5%로 가장 높습니다. 일반적으로 나이드신 분들, 저축으로 목돈을 굴리고자 하시는 분들은 은행보다는 금리가 조금이라도 높은 저축은행 이용을 많이 합니다. 조그마한 금리차이이지만 수십년을 조그마한 이자라도 높일 수 있다면 나중에는 큰 차이가 날 것입니다.

● 저축은행 정기예금 금리 표(중간에 공백/공란으로 구분되어 있는 셀)

● 구분기호로 분리하기

* 데이터안에 공란이 있을 경우 2가지로 구분해서 나눌수가 있습니다. 첫번째는 구분기호(공백)으로 분리하기, 두분째는 너비로 구분하기 입니다. 먼저 구분기호로 분리하는 법에 대해서 살펴보겠습니다.

1. 열항목 추가하기

먼저 나누어서 삽입할 열항목(B열)을 추가합니다. 그 후에 [나누고자 하는 셀데이터(A1:A7) 선택 > 텍스트나누기]

2. 텍스트마법사 이용하기

3단계중 1단계(원본데이터 형식/구분기호로 분리하기) > 3단계중 2단계(구분기호/공백) > 3단계(열 데이터서식/일반)을 순서대로 하시면 됩니다.

3. 셀안의 텍스트가 구분기호(공백)으로 분리된 결과표

엑셀 12(글자/텍스트 맞춤기능, 들여쓰기, 내어쓰기, 세로쓰기, 회전시키기)

직장생활 중에 건강관리는 필수입니다. 사무실에서 근무할 경우 계속 자리에 앉아있다 보면 뱃살이 늘어나가 다리나 허벅지에 근력이 줄어듭니다. 나이가 들어갈 수록 뱃살은 빠지고 근력은 증가시켜야 건강하게 생활할 수 있습니다. 몸 관리를 하지 못하면 비만하게 되고 40대 이상 부터는 다양한 성인병(콜레스테롤, 고지혈증, 고혈압, 심장질환, 당뇨병 등)이 올 수가 있습니다. 점심시간에 짬을 내서 공원산책등을 하면서 스트레스도 날리고 건강도 챙기시고......오늘도 화이팅입니다.

● 글자(텍스트)맞춤기능(위, 중간, 아래맞춤)

하단과 같이 엑셀의 각 셀에 데이터값이 있을 경우 글자를 위에맞춤, 중간맞춤, 아래맞춤을 할 수가 있습니다.[데이터범위 선택 > 원하는 맞춤 선택하기]를 하면 됩니다. 하단부위의 왼쪽맞춤, 중간맞춤, 오른쪽 맞춤기는도 있습니다.

● 글자맞춤(왼쪽, 중간, 오른쪽 맞춤)

* 맞춤그룹의 각각의 맞춤메뉴(아이콘)을 클릭하시면 됩니다.

● 들여쓰기, 내어쓰기

엑셀에서 들여쓰기와 내어쓰기의 경우 셀 한칸에서 텍스트를 셀의 테두리에서 얼마만큼 띄어쓰기를 할 것인지를 결정을 합니다. 즉, 셀 안에서 왼쪽, 오른쪽 여백주기와 같은 기능입니다. 하단은 외쪽 정렬이 되어 있는데이터를 내어쓰기(두번클릭, 내어씨기 2)를 한 결과입니다.

● 셀서식 대화상자를 통한 들여쓰기 확인

셀서식대화상자는 단축키 [ctrl + !]입니다. ctrl + ! 를 클릭하면 아래와 같이 셀 서식 대화상자 창이 생성이 되는데 여기에서 [맞춤 > 들여쓰기(2)]를 확인해 보면 2로 되어 있습니다. 셀 안에서 왼쪽 여백주기가 2로 되었다는 의미입니다.

● 글자/텍스트 세로쓰기, 시계방향, 반대방향 회전시키기

아래에 보시는 것 처럼 셀안의 텍스트를 엘셀에서 세로쓰기, 시계방향각도(글자회전)등의 효과도 간단하게 몇번의 클릭으로 설정이 가능합니다.

엑셀 11(데이터의 중복값제거하기)

신입직원이 입사를 한지 6개월이 되어갑니다. 나름대로 잘 적응을 하고 있습니다. 상사가 지시한 사항에 대해서 꼼꼼하게 메모해서 보고일자 기한을 지켜서 일을 처리해나갑니다. 신입사원 생활을 잘 해나가는 비결이 상사의 눈밖에 나지 않아야 합니다. 그러기 위해서 상사가 지시한 사항을 정확하면서도 빠르게 처리를 해야 합니다. 그 기초에는 메모하는 습관도 포함이 됩니다.

엑셀에서 하단과 같은 자료가 있습니다. 학교별, 학과와 성명, 주소가 나와있습니다. 필요한 자료가 학과와 성명은 필요없고 학교에 우편을 보내기 위해서 주소가 필요한 경우에는 중복값을 찾아서 없애야 합니다. 즉, 중복값 제거 기능입니다. 중복값을 제거하면 한글이나 MS WORD를 통해서 쉽게 우편라벨(레이블)봉투작업을 할 수가 있습니다.

● 엑셀 원본 데이터(중복값)

● 중복값제거하기

하단과 같이 엑셀의 데이터범위안에 마우스를 클릭한 후 [데이터 > 데이터도구탭 > 중복된항목제거]를 하면....

● 중복된 항목 제거

[제거하고자 하는 데이터열 선택(학교) > 내 데이터에 글머리표시 > 확인]을 하면 중복된 값이 제거되고 대표값하나만 나타납니다. 만약 [내 데이터에 글머리표시]에 체크를 하지 않고 중복값제거를 할 경우 열머리글이 사라지게 됩니다. 따라서 체크를 한 상황에서 제거를 해야 합니다.

● 중복된 값 제거 결과

아래에 보시는 것 처럼 A학교의 중복된 값 2개중에 1개와 C학교의 중복된 값 3개 중에 2개가 없어지고 각 1개씩만 대표값이 나타났습니다.

엑셀 8(데이터의 전범위 또는 끝행, 끝열 선택하기, 범위 지정하기)

직장생활 중 10여년이 흘렀습니다. 진급하는 것과 월급이 오르는 것이 가장 큰 의미 중 하나일 것 입니다. 또 하나는 상사를 잘 만나는 것입니다. 하지만 저희같은 경우는 일정기간 후 타지역으로 전보발령을 받기 때문에 좋은 직장상사만을 기대할 수는 없습니다. 요즘 구세대가 많이 퇴직을 해서 그래도 많이 좋아지긴 했습니다. 직장의 음주문화도 많이 건전해 졌습니다.

변하지 않는 것이 있다면 회사는 전문가를 요구하고 있다는 것입니다. 저같은 경우에는 기사와 기술사자격이 있기 때문에 어느정도는 인정받습니다. 하지만 주위에서 기술사만 3개를 가지고 있는 경우도 있어서 늘 끊임없이 자기개발을 해야 합니다.

한글이나 엑셀, 파워포인트를 활용한 문서작업이 많습니다. 조금이라도 더 많이 연습을 하는 사람이 더 많이 알게됩니다. 오늘은 excel에서 데이터선택, 시트(sheet)선택하는 방법에 대해서 살펴보겠습니다.

● 엑셀에서 데이터범위 선택하기

[원하는 셀 클릭 > 마우스로 선택하고자 하는 범위 이동] 

● shift를 이용한 데이터(셀)범위 선택

[shift를 누른 상태에서 →,←,↑,↓]컴퓨터 자판의 화살표를 누르면 한칸씩 이동되며 원하는 범위를 선택할 수 있습니다. 하지만 셀 하나를 선택하기 위해서 컴퓨터 자판을 한번씩 클릭을 해야 합니다. 셀이 많은 경우는 불편합니다. 오히려 마우스로 드래그 해서 선택하는 것이 편리합니다. 

● 데이터 행 전체, 열 전체, 데이터 전범위선택하는 단축키, 기능키

[ctrl, shift를 누른 상태에서 →,↓] 화살표를 선택하면 하단과 같이 엑셀데이터가 중간에 끊기지 않는 범위의 전체 행과 전체 열이 선택이 됩니다.

● 데이터의 행끝, 열끝 찾아기기 단축키 ctrl

현재 데이터셀에서 행의 끝과 열의 끝이 몇번째인가를 확인하는 단축키는 [ctrl을 누른 상태에서 →]를 하면 행의 끝을 찾아가고,↓를 누르면 열의 끝을 찾아갑니다. 즉, 데이터양이 얼마나 많은 가를 간단하게 확인 할 수 있습니다.

하단과 같은 엑셀 2007에서 데이터 시트(sheet)가 있습니다. 이를 M열을 기준으로 정렬을 해보겠습니다. [정렬하고자 하는 데이터의 범위내(M열)에 마우스포인터 위치 > 텍스트 오름차순 정렬]

● M열기준 텍스트 오름차순정렬

하단과 같이 M열을 기준으로 A,B,C,D학교순으로 정렬이 된 것을 볼 수가 있습니다. 만약 내림차순으로 정렬하면 어떻게 될까요?

● 내림차순으로 정렬

하단과 같이 내림차순으로 정렬이 되기는 했지만 열머리글도 정렬이 되어서 최고 하단으로 내려와 버렸습니다. 일반적으로 정렬할 경우에는 열머리글이나 행머리글은 변하지 최고의 첫열에 있어야 합니다. 그렇다면 열머리글이 첫열(첫행)에 올 수 있도록 어떻게 정렬을 해야 할까요?

● [홈탭 > 편집그룹 > 정렬필터] 사용하지 않기

이때는 하단과 같이 홈탭에 나와있는 정렬필터를 사용하지 않고 [데이터그룹 > 정렬 및 필터 > 정렬]를 사용해야 합니다.

● 하단과 같이 [데이터 > 정렬 및 필터 > 정렬]사용해서 셋팅하기

● 내 데이터에 (열)머리글 표시하기

하단과 같이 [정렬]창에서 [내 데이터에 머리글표시]에 체크를 해야 합니다. 이렇게 체크해서 선책 후에 정렬을 할 경우에는 열머리글을 표시하고 즉, 열머리글을 정렬에 포함하지 않은 절대값으로 하고 정렬을 하가는 것입니다. 이렇게 정렬을 하면 열머리글이 최하단으로 이동을 하지 않습니다. 그리고  이렇게 그 파일에서 한번 설정을 해놓으면 다른 시트(SHEET)에서 정렬을 해도 열머리글이 고정되고 그 후에 값들이 정렬이 됩니다.