엑셀탐구영역

 

안녕하세요?
이번 포스팅에서는 데이터 정리에 자주 쓰이는 엑셀의 신기한 기능들을 소개하려 합니다. 먼저 보고서 작성 시 유용한 기능들을 알아보겠습니다.

 

셀 선택 및 이동

데이터가 많은 경우 필요한 셀 범위를 선택하거나 데이터의 끝쪽으로 이동할 때 오랜 시간 드래그를 하거나 스크롤을 내려야 할 때가 있습니다. 이를 빠르게 수행할 수 있는 방법은 아래와 같습니다.

 

연속된 셀 범위의 가장 왼쪽/오른쪽/아래쪽/위쪽으로 이동

• 셀 경계선 더블클릭
• Ctrl + 방향키

이때 연속된 셀 범위란 빈 셀 없이 이어져 있는 범위를 말합니다. 예를 들어 데이터로 이루어진 표의 A1 셀에서 그 열의 가장 아래쪽 셀로 이동하고 싶다면 A1 셀과 A2 셀사이 경계선을 더블클릭하거나 A1 셀을 선택한 상태에서 Ctrl + ↓ 을 누르면 됩니다.

 

셀 선택

• Ctrl+Shift+방향키: 원하는 방향으로 연속 셀 선택
• Ctrl+* 또는 Ctrl+A: 연속 범위 모든 셀 선택 가능
• 이름 상자: 빈 셀 포함하여 선택 가능
• 이름 상자로 선택 후 빈 셀 제외하려면 [찾기 및 선택] - [이동 옵션] - [상수]

차트를 만들 때, 셀 서식을 적용할 때 등 보고서 작성을 할 때 셀 선택은 필수적입니다. 위의 기능들을 사용하면 드래그 없이도 넓은 범위의 데이터를 빠르게 선택할 수 있습니다.

세미나 때 '0’이 입력된 셀을 제외하고 선택하는 방법을 질문하신 분이 계셨는데, 아직 방법을 찾지 못하였습니다ㅠㅠ 혹시 방법을 아시는 분이 계시다면 댓글로 남겨주시면 감사하겠습니다.

 

데이터 편하게 보기

데이터가 많을 경우 아래 방법을 활용하여 데이터를 효율적으로 읽을 수 있습니다.

 

틀 고정
표가 아래로/옆으로 길 경우 아래쪽 데이터를 보기 위해 스크롤을 하면 머리글을 볼 수 없는 경우가 생깁니다. 이때 [보기] - [틀 고정] 기능을 활용하면 표의 첫 행이나 첫 열, 또는 선택한 셀 기준 왼쪽 열과 위쪽 행을 고정해둘 수 있습니다.

 

숨기기

• Ctrl+9: 행 숨기기
• Ctrl+0: 열 숨기기

현재 필요 없는 데이터를 삭제하는 대신 숨기기 기능을 사용할 수 있습니다. 숨긴 행/열에서 마우스 오른쪽 클릭을 통해 숨기기를 취소할 수 있습니다.

 

같은 통합 문서의 시트 한번에 보기
[보기] - [새 창] 기능을 사용하면 같은 통합 문서의 창을 여러 개 띄울 수 있습니다. 그리고 여러 개의 창을 만든 상태에서 [보기] - [모두 정렬] 기능을 사용하면 각각의 창을 모두 한 화면에 띄울 수 있습니다. 이 때 각 창에서 서로 다른 시트를 선택하면 여러 개의 시트를 한 화면에서 확인할 수 있습니다.

 

수식 분석

수식을 쓸 때 다양한 셀을 참조하는 경우 수식이 제대로 작성이 되었는지 확인하기 어려운 경우가 많습니다. 이때 [수식] - [참조되는 셀 추적] 또는 [참조하는 셀 추적] 기능을 사용하면 연결선을 통해 수식의 참조를 시각적으로 확인할 수 있습니다. 확인 후에는 [수식] - [연결선 제거]를 통해 연결선을 제거할 수 있습니다.

수식 복사

데이터가 적다면 수식을 하나만 쓰고 채우기 핸들(셀 우측 하단에 마우스를 뒀을 때 뜨는 십자가 모양)을 드래그하여 수식을 복사할 수 있지만, 넓은 데이터 범위에 수식을 복사하려고 하면 드래그를 하는데 오랜 시간이 걸립니다. 이때 아래의 방법을 활용할 수 있습니다.

 

연속 범위일 때 수식 복사
수식을 적용할 열의 가장 윗부분에 수식을 작성하고, 그 셀에서 채우기 핸들을 더블클릭하면 연속된 범위까지 수식이 복사됩니다. 예를 들어 A1:A30 범위에 데이터가 있고 B1:B30 범위에 수식을 적용하고 싶다면 B1 셀에 수식을 입력한 후 채우기 핸들을 더블클릭하면 B1:B30에 수식이 적용됩니다.

 

연속 범위가 아닐 때 수식 복사
위의 방법은 A1:A30 범위 중 A10 셀이 비어있다면 B1:B9 까지만 수식이 적용됩니다. 이처럼 빈 셀이 포함된 범위에서 수식을 복사하려면 먼저 수식을 적용하고자하는 셀을 모두 선택한 상태에서 수식을 입력하고 Ctrl+Enter를 누르면 선택한 셀에 수식을 복사할 수 있습니다.

 

앞에서는 보고서를 쓸 때 주로 사용할 수 있는 기능을 다루었다면 지금부터는 보고서 외의 다양한 엑셀 작업에서도 유용하게 사용할 수 있는 기능을 소개하고자 합니다.

 

배열 수식

위 그림과 같이 추 무게와 각 추의 개수가 적혀있는 표가 있을 때 추를 모두 더하면 몇 그램을 만들 수 있을까요? 표의 옆에 각 행의 (추 무게)X(개수) 를 계산한 후 SUM 함수를 써서 구하는 방법도 있지만, 하나의 수식으로도 구할 수 있습니다. 답을 구하고자 하는 셀에 SUM(B3:B11, C3:C11)을 입력한 후 Ctrl+Shift+Enter를 치면 위 수식 양 옆에 중괄호가 생기면서 배열 수식이 만들어지게 됩니다. 이는 SUM(B3 * C3, B4 * C4, …, B11 * C11)과 같은 의미로, 처음에 구하고자 했던 답을 도출할 수 있게 해줍니다.

배열 수식 대신 SUMPRODUCT(B3:B11, C3:C11)이라는 수식으로도 같은 답을 얻을 수 있습니다.

 

빠른 실행 도구, 리본 메뉴 설정

자주 쓰는 기능은 빠른 실행 도구 또는 새로운 탭에 추가할 수 있습니다.

먼저 빠른 실행 도구를 설정하려면 빠른 실행 도구의 맨 오른쪽 화살표를 누른 후 [기타 명령]에 들어가면 됩니다.

그리고 왼쪽에서 자주 사용하는 명령이 있다면 빠른 실행 도구 모음에 추가할 수 있습니다.
또한 리본 메뉴도 수정이 가능합니다. 빠른 실행 도구 모음을 설정했던 창에서 ‘리본 사용자 지정’ 탭에 들어가면 리본 메뉴의 탭과 그룹을 수정할 수 있습니다.

텍스트 표시

텍스트를 입력했는데 의도했던 것과 다르게 표시되는 경우, 아래의 방법을 사용할 수 있습니다.

 

전화번호
하이픈 없이 01011112222 같은 전화번호를 입력하면 엑셀에서 숫자로 인식하여 앞의 0을 생략하게 됩니다. 해결 방법은 다음과 같습니다.

• 셀 서식(Ctrl+1) - 사용자 서식 - 0##########
• 맨 앞에 작은따옴표 입력

분수
1/2와 같은 분수를 입력하면 01월 02일이라는 날짜로 표시되게 됩니다. 이때 0 1/2를 입력하면 1/2로 표시할 수 있습니다.

 

빠른 채우기

B열에서 학과만 추출하려면 어떤 방법을 사용해야 할까요? 학과의 단어 수가 모두 같다면 LEFT 함수를 사용할 수 있겠지만, 단어 수가 다르기 때문에 사용하기가 어렵습니다. 이때 빠른 채우기 기능을 사용하면 간편하게 추출을 할 수 있습니다. 표의 맨 위 빈 칸에 '건설환경공학부’를 입력한 후 [데이터] - [빠른 채우기] (또는 Ctrl+E) 를 사용하면 아래의 빈 칸에도 학과명이 채워지게 됩니다.

 

엑셀 기능 소개는 여기까지입니다. 포스팅에서 설명한 기능들을 직접 엑셀에서 써보시는 것도 좋을 것 같습니다:) 읽어주셔서 감사합니다!

Written with StackEdit.