엑셀로 데이터 베이스 다루기 II

1. 텍스트 나누기

 데이터를 입력하다 보면 한 셀에 여러 데이터를 한꺼번에 입력하는 경우가 있습니다. 이를 일일이 다시 분리하여 각 셀에 하나씩 입력하려면 힘든 작업이 될 것입니다. 이때 엑셀에서 자동으로 텍스트를 나누어 각 셀에 입력해주는 기능이 있습니다.

 

 

 

[그림 1] 포장 박스 재고관리

 [그림 1]과 같이 포장 박스 재고 관리 시트가 있습니다. 내용 중에서 박스 크기 부분을 보시면 '가로(cm)*세로(cm)*높이(cm)'로 한 셀에 데이터가 입력되어 있는 것을 확인하실 수 있습니다.

이럴 때 가로, 세로, 높이를 각 셀에 하나씩 자동으로 분리되어 입력되도록 해 보겠습니다.

 

먼저 C열 우측에 2개의 열을 추가합니다. D열 전체를 클릭한 후 Ctrl 키와 + 키를 동시에 2번 클릭하여 열을 추가합니다. 이는 세로와 높이가 입력될 셀을 만들기 위해서입니다.

[그림 2] C열 옆에 2개의 열 추가 모습

 마우스로 C3셀부터 C7셀을 드래그합니다. 

데이터 메뉴에서 데이터 도구 그룹의 텍스트 나누기를 클릭합니다.

[그림 3] 텍스트 마법사 대화상자 - 1단계

그러면 [그림 3]과 같이 텍스트 마법사 대화 상자가 출력됩니다. 텍스트 마법사 대화 상자에서 '구분 기호로 분리됨'을 선택하고 다음 버튼을 클릭합니다.

 

[그림 4] 텍스트 마법사 대화상자 - 2단계

 [그림 4]와 같이 텍스트 마법사 대화 상자 - 3단계 중 2단계 창이 출력됩니다.

그러면, [그림 5]와 같이 구분 기호의 기타에 v 체크 표시를 하고 사각형 안에 '*'를 입력합니다.

[그림 5] 텍스트 마법사 대화상자(2단계)에서 구분 기호 입력 모습

 다음 버튼을 클릭합니다. 그러면 텍스트 마법사 대화 상자 3단계 창이 뜨는데 바로 마침 버튼을 클릭하여 대화 상자를 닫습니다.

[그림 6] 확인 메시지 창

 그러면 [그림 6]과 같은 확인 메시지가 출력됩니다. 메시지 창의 확인 버튼을 클릭합니다.

[그림 7] 텍스트 나누기 완성 모습

 [그림 7]과 같이 한 셀에 입력되어 있던 가로, 세로, 높이 값들이 자동으로 추가한 열에 나누어 입력된 것을 확인할 수 있습니다.

 

 

 

2. 필터와 SUBTOTAL 함수 사용하기

 엑셀에는 필터를 사용하여 데이터들을 간단하게 정리할 수 있는 기능이 있습니다. 또한 함수를 이용하여 정리된 데이터들을 이용하여 원하는 값을 얻을 수 있습니다.

 

 우선, 이전에 사용했던 1분기 실적 현황 시트를 이용하여 필터와 SUBTOTAL 함수에 대해 알아보겠습니다.

 

[그림 8] 1분기 실적 현황

 [그림 8]의 1분기 실적 현황 장부에서 데이터가 입력되어 있는 임의의 셀에 마우스 클릭을 합니다.

데이터 메뉴를 클릭하고 정렬 및 필터 그룹에서 필터 아이콘을 클릭합니다. 그러면 3행의 목록들 옆에 역삼각형 모양이 있는 버튼이 생성됩니다. ([그림 9] 참조)

 

[그림 9] 1분기 실적 현황에 필터 적용한 모습

B3 셀에 있는 필터 버튼을 클릭하면 [그림 10]과 같은 메뉴가 뜹니다.

 

[그림 10] 직위 항목의 필터 버튼 클릭시 생성된 메뉴

B열은 직위를 입력한 열입니다. 메뉴([그림 10])를 보시면 직위로 입력된 모든 데이터가 열거되어 있는 것을 보실 수 있습니다. 그럼 여기서 '(모두 선택)' 좌측에 v 체크를 해제하고 '대리' 항목에만 v 체크 표시를 하고 확인 버튼을 누릅니다.

 

[그림 11] 직위가 대리인 경우만 표시된 모습

그러면 [그림 11]과 같이 직위가 대리인 설까치와 마동탁만 표시된 것을 보실 수 있습니다. 이와 같이 필터를 사용하면 입력된 데이터를 간단히 원하는 데이터들로 묶어 확인할 수 있습니다. 

또한, SUBTOTAL 함수를 사용하면 합계나 수량 등을 자동으로 파악할 수 있습니다.

 

 이제 1분기 실적 현황 시트에 추가로 SUBTOTAL 함수를 사용해 보겠습니다.

 

[그림 12] SUBTOTAL 함수를 사용하여 실적 합계 및 달성률 평균 계산

 [그림 12]에서 보시는 것과 같이 I열에 SUBTOTAL 함수를 이용하여 실적 합계와 달성률 평균을 구하는 식을 입력했습니다.

I2셀에는 '=SUBTOTAL(9, D4:D10)'를 입력하였고, I3셀에는 '=SUBTOTAL(1, E4:E10)'을 입력하였습니다.

여기서 SUBTOTAL 구문은 다음과 같습니다.

 

      SUBTOTAL(인수, 범위)

 

인수 : 어떤 작업을 할 것인지를 정하는 것으로 다음 표에 정리했습니다.

인수 번호	함수 유형	함수 처리
1	AVERAGE	평균
2	COUNT	수치가 입력된 셀 개수
3	COUNTA	텍스트가 입력된 셀 개수
4	MAX	최대값
5	MIN	최소값
6	PRODUCT	곱하기
7	STDEV	표본 표준 편차
8	STDEVP	표준 편차
9	SUM	합계
10	VAR	표본 분산
11	VARP	분산

 

범위 : SUBTOTAL 함수로 계산할 범위를 말합니다.

 

추가한 I2셀과 I3셀의 표시 형식을 보기 좋게 바꿔보겠습니다.

우선 I2셀을 마우스로 선택한 후 홈 메뉴에서 표시 형식 그룹의 쉼표 스타일을 클릭합니다.

그리고, I3셀을 마우스로 선택한 후 홈 메뉴에서 표시 형식 그룹의 백분율 스타일을 클릭합니다.

 

[그림 13] 홈 메뉴

 

[그림 14] SUBTOTAL 함수를 사용하여 실적 합계 및 달성률 평균 계산 - 보기형식 변경

 그러면 [그림 14]와 같이 I2셀의 값은 세 자리마다 콤마가 입력되는 형식으로 표시되며, I3셀은 소수가 아닌 백분율 형식으로 표시된 것을 보실 수 있습니다.

 이제 직위의 필터 버튼을 클릭하여 대리를 선택하면 I2셀과 I3셀 값이 어떻게 되는지 확인해 보겠습니다.

 

[그림 15] 필터를 사용하여 대리 직원을 선택했을때 SUBTOTAL 함수값의 변경 모습

 I2셀과 I3셀 값을 보시면 [그림 14]에서의 값과 달라진 것을 확인하실 수 있습니다. [그림 15]의 I2셀과 I3셀 값은 필터를 사용하여 대리 직원의 값만을 표시하게 하므로 SUBTOTAL 함수는 화면에 표시된 값에 대해서만 계산을 수행한 것입니다.

이와 같이 필터를 사용하여 원하는 대상만 표시해 놓으면 SUBTOTAL 함수는 해당 대상에 대한 값만을 이용하여 계산을 합니다.