엑셀(EXCEL)함수 CONCATENATE, SUBSTITUTE, IF에 대해 알아보자

 

 

엑셀(EXCEL) 함수 CONCATENATE, SUBSTITUTE, IF가

어떻게 사용되는지에 대해 알아보자

 

 

엑셀(EXCEL)에서는 함수를 사용하냐 안 하냐가 작업의 효율을 크게 좌우합니다.

일일이 계산하거나, 직접 눈으로 또는 손으로 확인해 가며 셀을 채워나가도 되지만 그 방법은 상당한 시간이 걸릴 것입니다.

그래서 엑셀에는 함수라는 것이 있습니다. 셀의 인수를 사용하여 여러 가지 계산법으로 다양한 값을 도출해 낼 수 있습니다.

함수라는 것이 처음에는 조금 어렵고 헷깔릴 수 있으나 몇 번 해보면 어떻게 사용하는지 감이 오실 겁니다.

 

이번 포스트에서는 다음 엑셀 함수에 대해서 알아보고 사용 예제까지 알아보도록 하겠습니다.

사용할 함수

1. CONCATENATE : 여러 셀의 텍스트를 결합합니다.

2. SUBSTITUTE : 특정 문자열을 대체합니다.

3. IF : 조건에 따라 값을 반환합니다.

 

엑셀에 개인 정보를 셀마다 각각 '이름, 도. 시, 동, 휴대폰 번호, 개인 정보의 텍스트들을 결합, 개인 정보의 텍스트 간의 빈칸(공백) 제거를 입력하고 출력할 것입니다.

 

셀 A, B, C, D, E까지는 수기 입력, 셀 F, G는 셀 A, B, C, D, E의 텍스트 결합과 공백 제거한 것을 출력할 것입니다.

 

이름, 도, 시, 동, 휴대폰번호는 값이 있는 셀도 있고 없는 셀도 있고, 각 셀의 텍스트들이 띄어쓰기(공백)가 있는 것도 있고 없는 것도 있습니다.

이렇게 엑셀에 입력이 되어있으면 나중에 다른 사람이 봤을 때는 가독성이 많이 떨어질 수 있습니다.

그럼 이제부터 이 개인 정보 텍스트들을 함수를 이용하여 보기 좋게 출력해 보도록 해보겠습니다.

 

 

 

 

일단 휴대폰 번호가 적힌 셀 값들을 숫자에서 텍스트 모드로 전환하겠습니다. 

숫자값으로 놔두면 맨 앞에 '0'번인 전화번호가 없는 값으로 인식해 셀에서 보이지 않게 됩니다.

 

 

 

 

E2번 셀을 마우스 오른쪽 클릭으로 콘텍스트메뉴를 띄웁니다.

'셀 서식'을 선택 클릭합니다.

 

'범주'에서 '텍스트'를 선택하고 '확인'버튼을 선택 클릭합니다.

그럼 '01011112222'라는 휴대폰 번호가 있으면 입력 시 '1011112222'라고 입력될 것이 '01011112222'라고 내가 원한 전화번호 텍스트값으로 입력됩니다.

 

* CONCATENATE 사용 예제

 

CONCATENATE 함수를 사용하여 각각의 다른 셀들의 텍스트 값을 합쳐보겠습니다.

셀 F2칸을 선택하고 엑셀의 위쪽 입력칸 왼쪽에 fx를 클릭하여 '함수 마법사'창을 출력시킵니다.

'함수 검색'에서 'CONCATENATE'를 입력하고 '검색'버튼을 클릭합니다.

'CONCATENATE'이 검색되었으면 하단에 '확인'버튼을 선택 클릭합니다.

 

 

그러면 검색된 'CONCATENATE'함수의 함수 인수를 입력하는 창이 출력됩니다.

Text1, Text2, Text3,...으로 계속하여 순차적으로 입력하면 각각의 인수의 텍스트값들이 하나의 텍스트로 합쳐지게 됩니다.

 

여기서는 

Text1	A2
Text2	" - "
Text3	B2
Text4	" - "
Text5	C2
Text6	" - "
Text7	D2
Text8	" - "
Text9	E2

이렇게 각 텍스트 값에 셀 위치와 " - " 값을 교차해 가며 넣어줍니다.

 

그럼 아래와 같은 함수식이 정의됩니다.

=CONCATENATE(A2, " - ", B2, " - ", C2, " - ", D2, " - ", E2)

 

 

 

그럼 위의 함수식이 적용되어 셀 F2에는 

곽 철용 -  - 서울특별시 - 반포동 - 01057156952

 

이렇게 A부터 E까지의 셀들의 텍스트들이 한 줄의 텍스트로 이어져서 출력됩니다.

 

 

현재는 위 F2의 값만 출력됩니다. 그래서 셀 F2의 칸에 오른쪽 아래에 작은 네모난 점을 클릭하여 F11까지 드래그합니다.

그럼 나머지 행의 텍스트 값들도 F2의 함수식이 적용되어 F열에 텍스트값들이 각 셀에 합쳐져서 출력됩니다.

 

개인 정보가 없는 8, 9, 10, 11행은 " -  -  -  -" 텍스트를 기본으로 출력됩니다.

 

이 상태로는 F2~F7행은 띄어쓰기 문법이 안 맞고 보기 불편한 텍스트 값들의 합을 가지게 되고 나머지 F8~F11까지의 공란들도 깔끔하지 않게 됩니다.

 

그럼 띄어쓰기가 난무하는 위 값들을 정리하기 위해 아래 SUBSTITUTE함수를 사용해 정리를 해보겠습니다.

 

 

 

* SUBSTITUTE 사용 예제

 

 

 

 

그럼 F열의 띄어쓰기 또는 공란이 불규칙하게 출력된 것을 SUBSTITUTE함수로 G열에서 정리해 보도록 하겠습니다.

셀 G2칸에 클릭하여 포커스를 두고 fx를 클릭하여 '함수 마법사'를 엽니다.

그리고 'SUBSTITUTE'함수를 검색하고 선택 후 '확인'버튼을 클릭합니다.

 

이 SUBSTITUTE함수는 특정 문자열을 대체하는 식이 있습니다.

 

Text : F2 - F2 셀의 텍스트 값에서
Old_text : " " - 공백(SPACE)을

New_text : "" - 없앱니다.

 

이렇게 입력하고 설명이 됩니다.

 

곽철용--서울특별시-반포동-01057156952

확인을 누르면 F열에 있던 불규칙한 공백들이 위 셀과 같이 하나도 남기 없이 한 줄로 붙은 텍스트로 출력됩니다.

 

 

 

* IF 사용 예제

 

이때 개인 정보가 없는 F8~F11은 "----"로 출력이 되는데 이 또한 보기에 좋지 않습니다.

그래서 "----"이 문자열이면 '(정보 없음)'으로 출력시키고 아니면 개인 정보를 공백 없이 출력하기로 하겠습니다. 

이때는 'IF'함수로 문자열을 확인하여 필요한 문자열을 출력시키겠습니다.

'함수 마법사'로 IF'함수를 검색하여 인수를 넣고 확인버튼으로 간단히 출력하면 좋겠지만 인수 넣는 방식이 조금 헷갈리실 수 있습니다.

이 함수는 제가 직접 풀어써드리겠습니다

=IF(SUBSTITUTE(F2, " ", "") = "----", "(정보없음)", SUBSTITUTE(F2, " ", ""))

위와 같이 G2~G11까지의 셀에 함수식을 적용시킵니다.

 

이는 F2의 텍스트 " "(공백)을 모두 없애고 그 결과 텍스트값이 "----"와 같음(TRUE)일 때는, "(정보없음)"이 출력되고 "----"이 텍스트 문장이 없을 때는 개인 정보가 있는 것이니 F열의 텍스트 중 공백을 지우고 출력하면 됩니다.

그럼 위와 같은 결과값이 출력됩니다.

 

 

이렇게 세 가지 함수를 섞어서 어떨 때 사용하는지에 대해서 예제를 통해 알아보도록 하였습니다.

물론 이 방법 외에도 여러 가지의 방법으로 함수들을 사용하실 수 있을 것입니다.

 

그럼 오늘 포스팅은 여기에서 마무리 짓겠습니다.

오늘도 즐거운 하루 보내세요^^