사람들이 잘 모르는 엑셀 팁(Tip) 알아보기 : 비교연산자를 이용해 값 비교하기

 

 

 

다른 인터넷이나 워드프로세서에서 데이터를 복사하거나 텍스 형식으로 지정된 셀에 숫자 값을 입력하면 숫자는 텍스트 형식이 되고, 텍스트 형식의 숫자는 함수를 사용하여 계산할 수 없다. 텍스트 형식의 숫자를 계산하는 방법에 대해 알아 본다.

 

 

각 지점 별 매출집계표.xlsx

0.01MB

 

 

(1) 첨부파일을 다운로드하여 불러오기를 한다.

 

 

 

 

(2) 첨부 파일을 열면 상기 화면과 같은 집계표를 확인할 수 있다. 집계표에서 [D9]셀을 선택하고 수식 입력줄의 소식을 살펴보면 아무런 문제가 없는데 계산결과가 제대로 나오지 않는 것을 확인할 수 있다. 이것은 값이 텍스트 형식으로 입력되었기 때문이다.

 

 

=SUM(D5:D8) 수식 결과 이해 하기

 

 

SUM 함수는 합계를 구하는 대표적인 함수이지만 숫자 값만 계산이 가능하다. 텍스트 형식으로 입력된 숫자는 계산에서 제외되기 때문에 SUM 함수의 결과가 제대로 나오지 않는 것이다. 올바른 결과를 얻고자 한다면 참조 범위의 숫자 값을 숫자로 변환하거나 계산 과정에서 변하는 작업을 해야 한다.

 

 

 

 

(3) 올바른 계산 결과 값을 얻기 위해 [D9] 셀에서 수식을 다음과 같이 수정한다.

 

 

=D2+D3+D4+D5

 

 

=D2+D3+D4+D5 수식 결과 이해하기

 

 

더하기 연산자(+)는 숫자 뿐만 아니라 텍스트 형식으로 입력된 숫자도 숫자 형식으로 변환하여 계산한다. 하지만 참조할 셀이 많은 경우에는 수식 작성이 복잡해지는 단점이 있다.

 

 

 

 

(4) 곱하기 연산을 이용하여 계산 결과를 얻기 위해서 [E9] 셀에 수식을 다음과 같이 수정한다.

 

 

=SUMPRODUCT(1*(E5:E8))

 

 

=SUMPRODUCT(1*(E5:E8)) 수식 이해하기

 

 

SUMPRODUCT 함수는 같은 크기의 행과 열을 연산하는 함수로 SUM 함수에서 치리하지 못하는 범위의 연산 작업에 사용한다. [E5:E8] 범위에 1을 곱하면 곱하기 연산이 일어나게 되는데 더하기 연산(+) 등 산술 연산자를 이용한 모든 연산은 텍스트 형 숫자를 숫자로 변환하여 계산하기 때문에 편리하다. 1을 곱하는 계산은 결국 자기 자신의 값을 얻기 때문에 이 수식은 [E5:E8] 범위의 값을 숫자로 변환한 다음 SUMPRODUCT 함수로 합계를 구하는 작업을 한 것이다. 이 수식을 SUMPRODUCT 함수 대신, SUM 함수를 이용할 경우 곱하기 연산자 계산은 배열수식을 이용해야 하므로 배열 계산을 함수 자체적으로 하는 SUMPRODUCT 함수를 사용하는 것이 편리하다.

 

 

 

 

(5) 마이너스 연산자를 이용하도 올바른 결과값을 얻을 수 있다. [F9] 셀에 다음과 같이 수식을 입력한다.

 

 

= SUMPRODUCT(--F5:F8)

 

 

= SUMPRODUCT(--F5:F8) 수식 이해하기

 

 

이 수식은 =SUMPRODUCT(1*(E5:E8))의 수식과 같지만 1을 곱하는 대신 마이너스(-)를 범위 앞에서 두 번 사용한다. 마이너스(-)를 숫자 앞에 붙이는 것은 -1을 곱하는 연산과 같다. 그러므로 마이너스(-)를 두 번 붙이는 것은 -1*-1=1과 같은 결과값을 얻을 수 있다. =SUMPRODUCT(1*(E5:E8))의 수식과 = SUMPRODUCT(--F5:F8) 수식은 모두 같은 작업이므로 텍스트 형식의 숫자를 올바른 숫자 형식으로 변환할 때 편리하게 사용할 수 있다.