코리 tips

나는 일상 업무에서 엑셀을 자주 쓴다. 특히 만들어진 데이터베이스를 활용해서 각종 통계값 등을 도출해 내는 일이 많다. 그러다 보니 정렬과 필터 같은 기능을 자주 사용한다. 이렇게 자주 사용하는 기능을 단축키로 쓸 수 있다면 작업 효율을 높일 수 있을 것이다.

엑셀에서 단축키를 활용하는 방법은 크게 세 가지가 있다.

1. 리본 바로가기 사용하기(키 팁 배지, Alt 사용)

2. 지정된 단축키 찾아서 쓰기(Ctrl키 조합)

3. 나만의 단축키 만들어 쓰기(빠른 실행 도구 모음)

엑셀에서 단축키, 즉 마우스를 쓰지않고 키보드로 각종 기능을 작동시키는 방법은 위와 같이 크게 3가지가 있다. 각 방법마다 장단점이 있다.

1. 리본 바로가기 사용하기(키 팁 배지)

  - 장점 : 단축키를 외울 필요가 없다. 기본 기능이므로 개인설정이 불필요해서 모든 컴퓨터에서 작동된다.

  - 단점 : 단축키가 길다.

2. 지정된 단축키 찾아서 쓰기

  - 장점 : 단축키가 간단하다. 기본 기능이므로 개인설정이 불필요해 모든 컴퓨터에서 작동된다.

  - 단점 : 외우기가 어렵다. (너무 많다)

3. 나만의 단축키 만들어 쓰기(빠른 실행 도구 모음)

  - 장점 : 내가 자주 쓰는 기능만 간단한 단축키로 만들 수 있다.

  - 단점 : 개인설정이므로 설정하지 않은 컴퓨터에는 사용할 수 없다.

오늘은 "1. 리본 바로가기 사용하기(키 팁 배지)"에 대해 알아보고자 한다.

<그림1. 리본 바로가기 사용하기(키 팁 배지)>

<그림2. 리본 바로가기(키 팁 배지)를 활용한 필터, 정렬하기1>

- 정렬 : Alt H S U

- 필터 : Alt H S F

<그림3. 리본 바로가기(키 팁 배지)를 활용한 필터, 정렬하기2>

- <그림2>와 같이 진행하면 위와 같은 결과물을 만날 수 있다. 왼쪽은 필터가 걸린 모습이고, 오른쪽은 정렬을 위한 대화창이 타나난 모습니다.

자주 사용하는 정렬과 필터를 리본 바로가기(키 탭 배지)로 사용하는 방법은 <그림2>와 같다. 매번 정렬과 필터를 걸기 위해 키보드에서 손을 때고 마우스를 집어들 필요가 없어진 것이다. 리본 바로가기(키 탭 배지) 단축키가 길어 보일 수 있지만, Alt 키만 누르면 찾아가는 경로를 보여주니 외울 필요가 없다. 자주 사용하는 기능은 손가락이 스스로 외우는 신기한 효과도 경험할 수 있다.

<사진1. 나만의 리본 바로가기(키 탭 배지)>

- 자주 사용하는 리본 바로가기(키 탭 배지)를 모니터에 붙여 놓으면 조금 빠르게 사용할 수 있다.

- 정렬 단축키 : Alt H S U

- 필터 단축키 : Alt H S F

- 틀고정 단축키 : Alt W F F

- 모두지우기 단축키 : Alt H E A

리본 바로가기(키 탭 배지)는 엑셀의 모든 기능을 단축키, 즉 마우스 조작 없이 키보드로 접근 할 수 있도록 도와준다. 조금 더 편리하고 효율적인 엑셀작업을 위해 <사진1>과 같이 나만의 리본 바로가기를 만들어 모니터에 붙여놓으면 활용도가 높다.

2018.01.17. 코리

관련된 다른 글 보러가기

[엑셀] 중복값 제거와 텍스트 나누기

[엑셀] 입력값 제한하기 (유효성검사)

[한글] 엑셀에서 복사한 한글 표 배경색 바꾸기

[엑셀] 불필요 공백, 점, 글자 지우기 (ctrl + h)

엑셀에서 중복값을 제거하는 방법과 텍스트를 나누는 방법에 대해 포스팅하고자 한다.

예제파일도 함께 포스팅한다.

엑셀 중복데이터 삭제와 텍스트 나누기.xlsx

중복값 제거는 선택된 영역 내에서 중복값을 찾아 하나만 남기고 나머지는 지우는 기능이다. 그리고 텍스트 나누기를 셀에 입력된 값을 일정한 규칙에 따라 나눠주는 기능이다.

이 두 가지 기능은 엑셀에서 쉽게 사용할 수 있음에도 불구하고 잘 모르거나 사용하지 않는 경향이 있다. 나 역시 그랬다. 이 기능을 알고 쓴지는 그리 오래되지 않았다. 그럼 어떻게 사용하는 것이고 어떤 결과를 가져오는 것인지 알아보자.

※ 본 포스팅에서 사용된 이름, 부서, 직급 등은 랜덤(임의방식)으로 작성된 것으로써 동일한 조건을 가진 사람이 있다면 100% 우연에 의한 것임을 밝혀둔다.

<그림1. 작업 설명>

오늘 하고자 하는 작업은 <그림1>의 오른쪽 표와 같이 2017년 실적을 직급별 및 부서별 합계를 구하는 것이다. 직급별 합계의 경우, 피벗테이블로도 구할 수 있지만 여기서는 같은 sheet 상에서 합을 구하기 위한 작업을 하고자 한다. 그리고 부서별 합계를 구하기에 앞서 "이름-부서명"으로 구성된 셀의 값에서 부서명을 분리해내는 작업이 필요하겠다.

1. 중복값 제거

<그림2. 중복값 제거1>

- 중복값을 제거하는 것은 필터를 눌렀을 때 나오는 결과와 같은 것을 만들어 내는 작업이다.

<그림3. 중복값 제거2>

- 직급열(B열)에서 바로 중복값 제거를 실행하면 필요한 데이터가 지워지므로, 별도의 공간에 값을 복사한 후 실행해야 한다.

- "데이터 - 중복된 항목제거"를 차례로 클릭한다.

- 엑셀은 중복된 항목 제거를 "시트에서 중복된 항목을 삭제합니다."라고 정의하고 있다.

<그림4. 중복값 제거3>

- 이 대화창은 내가 작업 중인 sheet 상에 내가 선택한 영역이외에 다른 데이터가 있는 경우, 범위를 제대로 선택했는지 확인시켜주는 메시지이다. 내가 선택한 영역에서 작업을 하고자 하면 "현재 선택 영역으로 정렬"을 클릭하면 된다.

- 이 대화창은 필터, 정렬 등 다른 곳에서도 볼 수 있다.

<그림5. 중복값 제거4>

- 중복된 값이 있는 열이 "직급" 밖에 없어 하나만 보이지만, 열이 여러 개인 경우 다중으로 선택하여 중복된 값을 제거할 수 있다.

<그림6. 중복값 제거5>

- 중복을 제거한 결과, 총 14개 데이터 중 10개가 삭제되고, 4개의 고유한 값만 남게 된다.

- 이렇게 중복된 값이 "삭제"되기 때문에, <그림3>에서 별도의 공간에 값을 복사한 것이다.

2. 텍스트 나누기

<그림7. 텍스트 나누기1>

- 이름열(A열)의 값을 이름과 부서명으로 나누고자 한다.

- 이름열에 입력된 값이 구조가 "이름-부서명"이므로, "-" 기호를 기준으로 텍스트를 나누는 작업을 해보자.

- 텍스트 나누기를 하면 하나의 셀이 두 개로 나뉘게 되므로, 나눠지는 데이터가 들어갈 공간을 녹색 상자와 같이 미리 확보를 하고 진행해야 한다.

- 나누고자 하는 셀의 범위를 주황 상자와 같이 선택한 후, "데이터 - 텍스트 나누기"를 차례로 클릭한다.

<그림8. 텍스트 나누기2>

- 텍스트를 나누는 기준은 "구분기호"와 "너비"가 있다.

- "이름-부서명"의 구조로 되어 있어 구분기호(-)를 기준으로 나누면 되므로 "구분기호로 분리됨"을 선택한다. "구분기호로 분리됨"을 선택하면 구분기호(-)는 사라지고, 구분기호(-) 앞뒤의 텍스트가 별도의 셀로 분리된다.

- "너비가 일정함"을 선택하면 위 녹색 실선과 같이 동일한 너비로 텍스트를 나눌 수 있다. 이 경우, 이름이 모두 3글자이면 사용 가능하지만, 이름이 2글자 또는 4글자인 경우가 포함되어 있으면 사용해서는 안 된다.

<그림9. 텍스트 나누기3>

- 구분기호를 "기타"를 선택한 후 "-"를 입력한다.

- 대화상자 아랫부분에서 어떻게 분리되는지 미리보기를 제공한다. 구분기호(-)는 사라지게 됨을 보여주고 있다.

- 다음을 클릭하면 데이터 서식을 지정할 수 있는데, 여기에서 "마침"을 클릭해도 결과물이 도출된다.

<그림10. 텍스트 나누기4>

- 셀 값을 바꾸기 전에 확인하는 대화창이 보인다. "취소"를 누르면 데이터 나누기 작업 전체가 취소된다.

<그림11. 텍스트 나누기5 및 최종 결과>

- "이름-부서명"의 데이터가 이름과 부서명으로 분리되었다. 부서명이 입력될 공간이 필요하기에 <그림7>에서 열 추가가 필요하다고 한 것이다.

- 부서명은 위에서 살펴본 중복값 제거를 활용해서 오른쪽 아래와 같이 부서명을 정리하고, sumif 함수로 직급별 및 부서별 2017년 실적을 정리했다.

위에서 설명한 것과 같이 중복값 제거와 텍스트 나누기는 상당히 쉽다. 그럼에도 이런 기능이 있는지 잘 모르는 경우가 다반사이다. 중복값을 제거하기 위해 피벗테이블을 만든 후 거기서 생성된 중복 제거된 값을 복사해 와서 쓰는 경우를 봤다. 또한, 텍스트를 나누기 위해 left 함수와 right 함수를 적절히 사용해서 작업하는 경우도 봤다. 이 두 가지 기능을 잘 사용하면 짧게는 몇 분, 길게는 수 십분이나 작업시간을 줄여 퇴근을 일찍 할 수 있는 것이다. 간단하고도 도움이 되는 기능이니 꼭 기억하고 자주 사용하도록 하자.

2018.01.16. 코리.

관련된 다른 글 보기

[엑셀] 입력값 제한하기 (유효성검사)

[한글] 엑셀에서 복사한 한글 표 배경색 바꾸기

[엑셀] 불필요 공백, 점, 글자 지우기 (ctrl + h)

엑셀 유효성 검사에 대해 포스팅하고자 한다.

"유효성 검사"라는 용어가 어렵게 느껴지지만, "입력값 제한하기"라고 표현하면 무슨 말인지 조금 친숙하게 다가온다.

이 기능은 셀에 입력하는 데이터 형식 또는 값을 제한 할 수 있는 기능이다. 예를 들어, "00월 실적보고서"에서 "00"란에 숫자 1부터 12까지만 입력되도록 제한하는 것이다. 이렇게 함으로써 "13월 실적보고서 "라고 적는 실수를 미연에 방지할 수 있다. 또한 파일을 여러 부서에 배포하여 작성할 때, 입력부서마다 개성 있게 입력된 값으로 파일이 취합되면, 정리하는데 많은 시간이 걸린다. <그림1>에서처럼 파일을 작성하는 사람에 따라 부서명을 빨간 상자처럼 입력하면, 파일관리자는 파란 상자처럼 다시 정리해야 하는 번거로움이 생기는 것이다. 이런 불필요한 업무를 줄여 퇴근시간을 당겨주는 것이 유효성 검사 기능이다.

※ 본 포스팅에서 사용된 이름, 부서, 직급 등은 랜덤(임의방식)으로 작성된 것으로써 동일한 조건을 가진 사람이 있다면 100% 우연에 의한 것임을 밝혀둔다.

<그림1. 팀별 2017년 실적을 부서별로 작성 후 수합한 파일>

- 부서명은 "영업1팀, 영업2팀, 영업3팀, 영업4팀, 신규사업팀, 해외영업팀"으로 구분되어 있는데, 각 팀별 자료입력자가 자기부서 이름을 "1팀, 영업팀, 영2, 신사팀, 해영" 등으로 입력하면 실적 정리가 안 된다. 그러면 파일관리자는 파란 상자처럼 부서명 작업을 다시 해야 하는 번거로움이 생긴다.

<그림2. 유효성 검사 적용 방법1>

- 유효성 검사를 적용할 셀을 선택(빨간상자)한 후 "데이터 - 데이터 유효성 검사 - 데이터 유효성 검사"를 차례로 찾아 들어간다.

- 엑셀은 데이터 유효성 검사를 "잘못된 데이터를 셀에 입력하는 것을 방지합니다."라고 정의한다.

<그림3. 유효성 검사 적용 방법2>

- "데이터 유효성"이라는 대화창의 제한 대상 기본 설정 값은 "모든 값" 이므로, 입력 제한이 없는 상태이다.

- 제한 대상을 설정함으로써 잘못된 데이터 입력을 막을 수 있다.

<그림4. 유효성 검사 적용 방법3>

- 제한 대상은 정수, 소수점, 목록, 날짜, 시간, 텍스트길이, 사용자지정 등으로 나눠져 있으며, 사용 목적에 맞는 것을 선택하면 된다.

- 여기서는 특정부서명만 입력하도록 제한할 것이므로 "목록"을 선택한다.

<그림5. 유효성 검사 적용 방법4>

- 제한 대상이 목록으로 입력되면, 목록을 만들어줘야 한다. 아래 원본 란에 목록을 콤마(,)로 구분하여 입력한다.

- 목록을 드롭다운으로 보여줄 것이기 때문에 "드롭다운 표시" 기능은 자동 선택되어 진다.

<그림6. 유효성 검사 적용 방법5>

- 설정을 완료하면 녹색 상자처럼 드롭다운 버튼이 생기고, 그걸 누르면 위에서 입력한 목록이 생성된다. 데이터 입력자들은 부서명을 입력하지 않고 목록에서 선택하면 되는 것이다.

<그림7. 유효성 검사 적용 방법6>

- 목록에서 선택하지 않고 직접 입력 할 수도 있는데, 목록에 없는 값을 입력하게 되면 위와 같은 안내 메시지를 만날 수 있으며 입력되지 않는다.

- 브라보!

<그림8. 유효성 검사 적용 방법7>

- <그림5>에서 목록을 콤마(,)를 이용해 입력하는 방법 이외에도 sheet 어딘가에 또는 다른 sheet에 목록을 입력하고, 그 영역을 선택하는 방법도 있다.

설명된 바와 같이 생각보다 간단하다. 하지만 이 간단한 작업을 통해 자료입력자들은 예상되는 실수를 미연에 방지할 수 있고, 파일관리자는 불필요한 정리 작업을 하지 않아도 되기에 퇴근시간을 당길 수 있다. 특히 하나의 파일에 많은 사람들이 작업을 하고 취합하여 정리하는 경우, 이 작업은 필수적이다. 입력되지 말아야 하는 데이터를 입력 단계에서 제한함으로써 불필요한 문의(전화)를 하지 않게 되어 서로의 업무효율을 높일 수 있다. 그리고 데이터베이스 관리 차원에서도 꼭 필요한 작업이다. <그림1>의 빨간 상자와 같이 부서명이 중구난방으로 입력되면, 부서명으로 통계(sumif, countif, vlookup, 피벗테이블 등) 작성이 불가능해진다. 일관성 있는 데이터 관리를 위해 꼭 필요한 작업이니 기억하고 활용하도록 하자.

2018.01.13. 코리.

관련된 다른 글 보기

[한글] 엑셀에서 복사한 한글 표 배경색 바꾸기

[한글] 문서 비교하기

[엑셀] 불필요 공백, 점, 글자 지우기 (ctrl + h)

[업무tips] 퍼센트(%)와 퍼센트포인트(%p)

[업무tips] 글자 수 세기

[윈도우] 윈도우10에서 예전 그림판 사용하기

문서 작업을 하다보면 불필요한 공백이나, 점, 또는 글자를 지워야 하는 경우가 종종있다.

예를 들어 사람 이름이 나열된 파일이 있는데, 이름이 모두 "홍 길 동"처럼 각 글자가 띄어쓰기 되어 있는 경우이다. 이름이 몇 개 안된다면 직접 지우면 되겠지만, 이름이 수 백, 수 천 개라면 이야기가 달라진다.

<그림1. 불필요 공백 지우기>

이유는 잘 모르겠지만, 사람 이름을 작업할 때 <그림1>과 같이 공백을 넣어서 작업하는 사람이 있다. "홍 길동"과 같이 성과 이름을 띄우는 사람도 가끔 있다. 이런 파일을 취합하여 이름 사이의 글자를 지우고 싶을 때 사용하면 유용한 단축키가 "ctrl +h"이다.

"ctrl+h"를 누르면 "찾기 및 바꾸기"라는 대화창이 뜨게 된다. 원하는 내용을 찾아서 다른 것으로 바꿔주는 기능이다. 여기서는 "공백"을 찾아서 "공백이 아닌 것으로" 바꿔주는 역할을 하게된다.

<그림2. 불필요 공백 지우기>

방법은 쉽다.

1. 공백을 찾고 싶은 영역을 선택한다. <그림2>에서 김정숙부터 박진만까지 선택하였다.

2. ctrl + h 를 눌러준다. <그림2>에서와 같이 "찾기 및 바꾸기" 대화창이 뜬다.

3. "찾을 내용"에는 스페이스바를 한번 톡! 하고 눌러 띄어쓰기를 표현해 주고,

4. "바꿀 내용"에는 아무 것도 입력하지 않는다.

5. "모두 바꾸기"를 눌러준다.

공백 한 칸(띄어쓰기 한 번)을 "아무것도 없는 것"으로 바꾸라는 명령인 것이다.

<그림3. 불필요 공백 지우기>

<그림2>에서 이름 사이에 공백을 2칸씩 뒀더니, 총 28개 항목이 바뀌었다는 결과를 보여준다.

"김vv정vv숙"을 "김정숙"으로 바꿨으니 이름 하나 당 공백 4칸이 사라진 것이다.

공백을 지웠으니, 이번에는 단어를 지워보자.

<그림1>의 "전공" 정보에서 "학과"라는 글자를 지워보자. 방법은 위에서 안내된 공백지우는 것과 동일하다.

<그림4. 불필요 단어 지우기>

<그림5. 불필요 단어 지우기>

<그림5>에서 "학과" 7개가 사라졌다. 그래서 총 7개 항목이 바뀌었다는 결과를 보여준다.

마지막으로 특정 글자로 끝나는 단위 뒤에 다른 단어를 붙이는 것도 할 수 있다.

<그림1>의 졸업연도를 보면 "숫자+년"으로 구성되어 있는 것을 볼 수 있다. "2012년"을 "2012년 2월" 등으로 모두 바꿔주고 싶다면, "년"을 찾아 "년 2월"로 바꿔주라는 명령을 하면 쉽게 해결할 수 있다.

<그림6. 글자 붙이기>

위에서 언급한 동일한 방법으로 ctrl + h를 눌러 관련 정보를 입력하면 글자 붙이기도 쉽게 완료 할 수 있다. "2006년"을 "서기 2006"년과 같이 글자 앞에 다른 글자를 붙이려면 <그림6>에서 "찾을 내용"에 "2", "바꿀 내용"에 "서기 2"라고 입력하면 바꿀 수 있다.

<그림7. 글자 붙이기>

하지만, 글자 찾아 바꾸기를 실행함에 있어 주의해야 할 점이 있다. 찾아 바꾸고자 하는 글자의 위치가 중요하다. <그림6>에서 "2012학년도"라고 적힌 셀이 있는데 위와 같이 찾아 바꾸기를 실행하면 "2012학년 2월도"라는 대참사가 생긴다.

공백 지우기는 쉽게 할 수 있지만, 글자를 바꾸는 것은 많은 가능성을 열어주고 실행해야 한다. 한 번 바꾼 후 저장해버리면 되돌릴 수 없다는 것을 기억하자

2017.12.27. 코리.