알면 유용한 엑셀 기능 3가지

알면 유용한 엑셀 기능 3가지를 소개해 드리도록 하겠습니다. 

 

 

1. 서식 복사

 

아래와 같은 표에서 표의 양식을 통일하고 싶을 때가 있습니다. 

이때, 일일이 각 셀에 색을 채우기로 설정하고 있지 않으셨나요? 

서식 복사 기능을 사용하면 이런 문제가 바로 해결됩니다. 

서식 복사 기능은 한마디로 셀의 내용이 아니라, 디자인을 복사하는 기능이라고 할 수 있습니다. 

그럼 사용 방법을 함께 알아볼까요? 

 

 

먼저 서식 복사를 할 표를 선택하고 블럭지정을 해 주세요! 

 

위와 같이 [홈] 탭에서 [서식 복사] 를 클릭합니다

 

마지막으로 오른쪽 표 전체를 드래그하여 선택해 주면 끝!! 

아래 첨부된 사진에 보이는 것처럼 왼쪽 표와 완전 똑같은 디자인이 적용된 걸 확인할 수 있습니다. 

어때요 참 간단하죠? 

왼쪽에 적용되어 있던 표의 색깔, 정렬 기준 등 모든 서식이 한꺼번에 적용되었습니다. 

 

 

2. 엑셀 스파크라인 기능(엑셀 2010 이상)

두번째로 소개해 드릴 기능은 '스파크라인'  기능 입니다. 

스파크라인은 지금까지 '차트' 기능을 이용하여 삽입하셨던 큰 그래프가 아닌,

미니 그래프라고 할 수 있죠.

 

 

스파크라인을 추가할 셀을 선택한 다음, [삽입] - [스파크라인] 에서 [꺾은선형], [열], [승패] 중 하나를 선택합니다.

꺾은선형 - 꺾은선그래프
열 - 막대그래프
승패 - 양수는 위쪽 파란색, 음수는 아래쪽 빨간색

이라고 보면 됩니다.

 

 

'스파크라인 만들기' 창에서 다음과 같이 설정합니다.

① 원하는 데이터 선택 : 스파크라인의 원본 데이터 범위 선택(가로 1줄 또는 세로 1줄이어야 함)
② 스파크라인을 배치할 위치 선택 : 기본적으로 현재 선택된 셀로 나옵니다.

마지막으로 아래쪽의 '확인' 버튼을 클릭하면 스파크라인이 만들어집니다.

위에 만든 스파크라인은 꺾은선형, 열 스파크라인입니다. 

데이터 변화의 '추이' 를 한 셀에 쉽게 나타낼 수 있는 장점이 있습니다. 

 

 

3. 이름 관리자 

 

이름이란?? 

 

엑셀에서 '이름'이란 셀, 범위 등에 붙이는 이름표라고 할 수 있습니다.

셀이나 범위의 복잡한 주소 대신 이름표를 붙이면 복잡한 수식을 알아보기 쉽도록 할 수 있습니다.

어떤 회사에서 영업사원에게 판매금액의 5%를 인센티브로 지급한다고 할 때 일반적으로 아래 그림과 같이 판매금액에 인센티브 지급율을 곱해서 계산합니다.

=C8*$C$4 

 

=C8*$C$4 수식에서 '인센티브 지급율' 5%가 입력된 [$C$4] 범위 대신 '인센티브율'이라는 이름을 만들어서 다음과 같이 계산할 수 있습니다. 훨씬 알아보기 쉬운 수식이 됩니다.

=C14*인센티브율

 

 

 

엑셀 이름 만들기

 

위에서 사용한 이름 '인센티브율'을 어떻게 만드는지 알아보도록 하겠습니다. 

이름을 만드는 방법은 몇가지가 있지만 2가지 정도만 살펴보겠습니다. 

 

 

1) [이름 상자]에서 만들기

 

엑셀 시트의 왼쪽 상단에 있는 '이름 상자'에서 만들 수 있습니다.

 

① [C4]셀을 선택하고

②이름 상자에 '인센티브율'이라고 입력하면 이름이 만들어 집니다.

이제 [C4] 라는 셀 주소 대신 다음과 같이 '인센티브지급율'이라는 이름을 수식에서 사용할 수 있습니다.

=C14*인센티브율

 

 

2) [이름 정의] 메뉴로 만들기

 

① 리본 메뉴에서 [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 누르면 [새 이름] 대화상자가 나옵니다.

② 대화상자에서 [이름] 필드에 '인센티브지급율'을 입력해 주세요

③ [참조 대상]의 오른쪽 버튼을 클릭하여 [C4] 셀을 지정하면 그림과 같이 =Sheet1!$C$4 이 입력됩니다.

④ <확인> 버튼을 누르면 이름 완성.

 

이름이 만들어지고  마지막으로 [이름 관리자] 대화상자가 나타납니다.

이름이 정상적으로 만들어 졌는지 확인하고 <닫기> 버튼을 누릅니다.

[이름관리자]에서는 이름 새로만들기, 이미 만들어진 이름 편집, 삭제 등을 할 수 있습니다.

 

 

 

3-1 엑셀 이름 사용하기

 

지금까지 만든 이름이 어떻게 사용되는지 한번 알아보도록 하겠습니다. 

 

먼저 이름을 정의할 부분을 드래그해서 선택하고 수식 왼쪽에 있는 이름상자에 이름을 넣고 엔터.

(이 때 블록을 잡을 때는 제일 윗줄 제목에 해당하는 부분은 포함하지 않고, 숫자 데이터만 있는 부분을 선택합니다.)

 

 

 

 

만약 이름을 정의하고자 하는 데이터가 표 형식으로 여러줄이 되어 있다면, 

한꺼번에 이름정의할 수도 있습니다.

이름을 정의할 부분을 드래그로 선택합니다.

이 때는 제일 윗줄의 텍스트가 이름제목으로 선택되기 때문에 텍스트를 포함해서 선택합니다.

 

그리고 [수식탭 - 선택영역]에서 만들기 클릭.

이름으로 첫행을 사용할 것이므로 [첫행 체크] 후 확인 클릭

 

 

 

이렇게 이름을 정의한 뒤에는 이름상자에서 정의한 이름을 클릭하면 이름이 정의된 셀들이 반전되어 나타납니다.

 

 

 

 

또는 [수식탭 - 이름관리자]를 클릭해서 정의된 이름들과 이름이 참조하고 있는 셀들을 확인할 수도 있습니다.

 

 

 

이름은 꼭 셀을 참조해야 하는 것은 아니고, 

일정한 상수를 입력할 수도 있습니다.

 

[수식탭 - 이름관리자]를 클릭하고 새로만들기를 선택해서 새로운 이름을 정의해 보겠습니다.

 

 

 

 

 

참조대상 부분에서 등호(=)를 먼저 적고, 상수를 적어 주세요.

 

이제 쉽게 계산해 보도록 하겠습니다.

 

D2셀에 기본급+호봉급+교통비를 입력합니다.  

 

 

 

 

이렇게 이름을 이용해서 계산하는 경우 계산해야 하는 항목이 여러가지이고 복잡할 때 직관적으로 계산하고 이해할 수 있는 장점이 있습니다.

또 상수로 입력해 놓은 부분이 수정될 때 이름관리자에서 상수를 수정해 주면 계산이 자동으로 변경되기 때문에 간단히 수정할 수 있습니다. 

 

 

 

 

 

이름정의에는 상수 뿐 아니라 텍스트도 사용할 수 있습니다. 

회사명이나 고유명사를 이름정의로 등록해 놓으면 간단하게 사용할 수 있어요.

 

서울특별시 종로구 혜화동 90번지를 "주소"라고 이름정의 해 놓으면 입력셀에 =주소 만 입력하면 됩니다. 

 

 

 

 

 

 

 

 

참고로 한 시트에서 작성된 이름은 그 파일의 어느 시트에서나 사용 가능 합니다. 

 

 

 

마지막으로 수식에 대해서도 이름 정의를 사용할 수 있습니다.