한 셀에 형식이 다른 값이 입력되어 있을 때 구분해서 계산하기

안녕하세요.

 

엑셀에 데이터를 입력할 때 한 셀에 하나의 데이터를 입력해야 합니다.

하나의 셀에 형식이 다르거나 내용이 다른 값을 입력하면 안됩니다.

예를 들어 [B2] 셀에 앞에는 정수 값이 입력되어 있고, 괄호 안에 백분율 값이 입력되어 있습니다.

문서를 작성하다 보면 이렇게 만드는 경우가 많이 있습니다.

이렇게 작성했을 때 문제는 앞 부분의 정수 부분만 계산을 해야 하거나, 괄호 안의 백분율 부분만 계산을 해야할 때 두 값을 구분하기 어렵다는 것입니다.

 

한 셀에 입력된 다른 형식 데이터를 분리하기 위해 [텍스트 나누기] 기능을 이용하는 방법과 함수를 이용하는 방법으로 나누어 소개하겠습니다.

 

실습 파일 다운로드

한 셀에 형식이 다른 값이 입력되어 있을 때 구분해서 계산하기_실습.xlsx

0.01MB

완성 파일 다운로드

한 셀에 형식이 다른 값이 입력되어 있을 때 구분해서 계산하기_완성.xlsx

0.01MB

 

텍스트 나누기 기능 이용

텍스트 나누기 기능을 이용하면 현재 입력된 데이터 형태가 없어지게 됩니다.

그래서 시트를 복사해 두도록 하겠습니다.

시트 탭에서 Sheet1 에서 키보드 <Ctrl>을 누르고 오른쪽으로 끌어서 놓습니다.

Sheet1을 선택합니다.

[B2:B3] 셀 범위를 선택합니다.

[데이터]-[데이터 도구]-[텍스트 나누기]를 선택합니다.

단축키는 <Alt, A, E>입니다.

[텍스트 마법사] 1단계에서는 기본값으로 '구분 기호로 분리됨'이 선택된 상태에서 [다음]으로 넘어 갑니다.

[텍스트 마법사] 2단계에서는 구분 기호를 지정해야 합니다.

'구분 기호' 항목에서 '기타'를 체크하고 여는 괄호를 입력합니다.

기본값으로 체크되어 있던 '탭'은 체크를 해제해도 되고, 안해도 됩니다.

입력된 데이터 중 탭이 없기 때문인데, 만약 탭이 입력되었다면 체크를 해제해야 합니다.

[텍스트 마법사] 3단계에서는 나누어진 각 열의 데이터 서식을 지정하는데, 첫번째 정수 부분은 기본값인 '일반'으로 두면 알아서 정수 형식으로 지정됩니다.

두번째 열인 백분율 부분은 닫는 괄호가 포함되어 텍스트 나누기를 마친 후 추가 작업을 해야 됩니다.

한 셀에 입력되어 있던 데이터가 두개의 열로 분리 되었습니다.

[B4] 셀에 커서를 두고 자동 합계를 선택하면 정수 부분 합계를 구할 수 있습니다.

자동 합계 단축키는 <Alt + =>로 표시되어 있는데, 대부분 실행되지 않습니다.

이유는 운영체제와 엑셀 간의 문제 때문입니다.

대신에 단축키 <Alt, H, U, S>를 눌러 실행할 수 있습니다.

 

함수를 이용해 값구하기

Sheet1 (2) 시트를 선택합니다.

[C2] 셀에 커서를 두고 =fi 를 입력합니다.

표시된 함수 목록에서 FIND를 더블 클릭해서 나타내고 수식을 작성합니다.

=FIND("(",B2)

FIND 함수는 여는 괄호가 있는 위치를 알려 줍니다.

여기에 LEFT 함수를 이용해 앞자리 정수만 잘라내도록 하고, VALUE 함수를 이용해 데이터 형식을 숫자로 바꿉니다.

=VALUE(LEFT(B2,FIND("(",B2)-1))

[C3] 셀로 채우기를 한 뒤 자동 합계를 실행해서 합계를 구합니다.

 

배열 수식을 이용하면 따로 C열에 값을 구한 뒤 계산하지 않고 한 셀에 결과값을 구할 수 있습니다.

[B5] 셀에 커서를 두고 수식을 작성합니다.

배열 수식은 수식을 완성할 때 <Ctrl + Shift + Enter>를 눌러야 합니다.

=SUM(VALUE(LEFT(B2:B3,FIND("(",B2:B3)-1)))

 

필요하신 분들께 도움이 되길 바랍니다.