엑셀 문자열 합치기 EXCEL 텍스트 합치기

오늘은 엑셀에서 유용하게 활용이 가능한

텍스트 합치기 및 문자열 합치기 기능에 대해서

살펴보도록 할께요.

현존 하는 프로그램 중에 가장 잘 만들었다고

평가받는 프로그램 중에 하나가 엑셀인데

기능들 하나 하나를 알아갈 때마다 정말

절로 박수가 나옵니다.

자 그럼 시작해볼께요

제가 원하는 그림은 이렇습니다.

각 열에 위와 같은 문자열이 있고요 (서식은 텍스트)

이를 D 열에 합쳐서 보여주고 싶은겁니다.

당연히 함수를 써야겠죠?

 CONCATENATE 라는 함수를 사용합니다.

함수 조건식은

=CONCATENATE(문자열1, 문자열2,문자열3....) 식으로 조합하면 됩니다.

D1열에

=CONCATENATE(A2,B2,C2) 를 입력했어요.

결과는 A2,B2,C2 가 합쳐져서 "엑셀문자열합치기" 라는 문장이 완성이 되었습니다.

그런데 띄어쓰기가 없으니 보기가 안 좋죠 ?

띄어쓰기는 각 문자열 사이에 " " 를 넣어줍니다.

=CONCATENATE(A2," ",B2," ",C2)

결과를 보면 아래와 같이 나옵니다.

띄어쓰기 대신 다른 기호나 문자를 넣고 싶다하면

" " 에 띄어쓰기 대신 다른 문자나 기호를 넣어주시면 됩니다.

엑셀 문자열 합치기를 할 열이 제가 한 예시처럼

3개면 뭐 중간 중간 띄어쓰기 넣는게 큰 문제는 안되는데

텍스트 합치기를 할 열이 많을 경우엔

일일이 타이핑 하시지 마시고

일정 포맷을 만든 후

복사 - 붙여넣기 후 텍스트의 절대위치값만 변경하는 꼼수도 있습니다.

" ",B2," ",C2," ",B2," ",C2," ",B2," ",C2

요런식으로 복사해서 쭉 늘어트린 담에

" ",B2," ",C2," ",B3," ",C3," ",B4," ",C4

숫자만 변경을 하는 방법으로 하면 간단하겠죠?^^

엑셀은 정말 잘 만들어진 프로그램입니다.

거기에 사용자들의 꼼수가 더해지면

더없이 완벽해지는 프로그램이기 때문에

여러모로 유용하게 활용하여 보시면 좋겠네요^^

엑셀 셀 서식이 너무 많습니다 오류 해결 방법은 ?

엑셀 작업 중에, 이리저리 다른 문서에 있는 자료나 텍스트 등을

복사해서 문서를 만들어 가다 보면

난데 없이 아래와 같이 뜰 때가 있습니다.

바로

"셀 서식이 너무 많습니다." 

라는 경고창입니다.

오늘은 이에 대한 해법에 대해서 살펴보도록 할께요.

먼저 문제가 되는 엑셀문서를 살펴보도록 하죠.

문제가 나타나는 문서의 "스타일" 부분에서

아래 화살표를 클릭합니다.

그럼 해당 문서에 포함된 스타일들이 나오는데

많아도...

너무~~~ 많네요.

아래와 같이 말이죠.

셀 서식이 너무 많습니다 오류는,

바로 아래와 같이

문서에 포함된 스타일(셀서식)이 과도하게

많아서 발생된 문제입니다.

해결방법은 ?

불필요한 서식을 지우면 됩니다.

근데 문제는 문서 내에 해당 서식을 사용하는 경우도 있을 테고

무턱대고 다 지울수도 없습니다.

또 이게 한번에 지울수가 없어서

일일이 아래처럼 마우스 우클릭 - 삭제로 해야하는데

어느세월에 할까요 ?

구글링에서 해답을 찾았습니다.

RemoveStyles.xlam

먼저 첨부 파일을 다운받아 주시고요, (엑셀 2010 이상 버전에 사용 가능)

1. 문제가 되는 엑셀 문서 열기

2. 첨부 파일 실행

3. 매크로 포함 클릭

4. 버튼 확인 (위 첨부를 실행하면 메뉴에 자동 추가됩니다)

Remove Styles 클릭

숫자 50109는

해당 문서에 포함된 스타일 갯수를 의미합니다.

5. 얼마의 시간 후 작업 완료되면

스타일 쪽에 보니 , 그 많았던 스타일들이 싹 사라졌네요^^;

자, 간단히 해결되셨나요 ?^^;

엑셀 연속 데이터 채우기 자동채우기 더하기 빼기 방법 (증가, 감소)

엑셀에서 편리한 기능 중 하나가 바로 연속 데이터 채우기 기능입니다.

숫자든 텍스트든 일정 형태의 일련번호 양식이 있다면 자동으로 증가하게 되는데요,

일반적으로는 위에처럼 셀 선택 후 아래 귀퉁이 부분을

잡고 쭉 드래그를 하면 셀복사 및 엑셀 연속 데이터 채우기 기능을

사용할 수 있도록 조그만 단추가 나옵니다.

연속 데이터 채우기를 선택하면 +1씩 증가하게 되죠

그렇다면 자동채우기 더하기(증가)를  +1 이 아닌 +2 씩 하고 싶을때는 ?

아래와 같이 셀에 증가하고 싶은 숫자를 적습니다.

1->3 이니깐 +2 죠 ?

그렇다면 이 2개의 셀을 선택한 상태에서

마찬가지로 아래 귀퉁이를 잡고 쭉 드래그 합니다.

반대로, 연속 데이터 채우기 빼기(감소) 는 어떻게 할까요 ?

바로 위 예에서와 동일하게 감소할 숫자를 미리 셀에 채워놓고

두 셀을 잡고 드래그 하면 됩니다.

아래 처럼요~

아래는 -1 을 하고 싶을때고요~

이거는 -2씩 감소시키고 싶을 때입니다.

엑셀 연속 데이터 채우기(자동채우기) 기능은 상당히 유용하므로

잘 기억해두었다고 사용하시면 좋겠네요

엑셀 vlookup 함수 활용 방법

오늘은 엑셀 노가다 작업을 할 때 정말 유용하게, 또 흔하게 사용할 수 있는 엑셀 vlookup 함수에 대해서

살펴보도록 할께요.

먼저 vlookup 함수가 하는 일에 대해서 알아야겠죠 ?

위 그림에서 정의를 살펴보면 이렇습니다. 배열의 첫번째 열에서 값을 검색하고, 검색한 값이 위치한 행의 다른 열의 데이터 값을 반환해 줍니다. 잘 이해가 가시나요 ?

예를 들어서 설명해보죠.

1. 아래 과일:가격 으로 이루어진 표(배열) 가 있습니다.

2. 저는 짬이 안 되므로, 팀원들에게 메일을 돌려서 신청 과일과 갯수 등에 대한 정보를 받아서 취합하는 업무를 합니다.

사람들은 메일로 신청을 하겠죠 ?

메일을 돌릴 때에는 취합 때 편하게 하기 위해서 표 형식으로 작성을 부탁하면 좋겠지요.

아래 처럼요 ~

3. 저는 메일을 취합하여 아래와 같이 엑셀을 정리 합니다. 

각각이 신청한 과일에 따라 총합을 내는 일은 이게 몇명이라면 뭐 수작업이 훨씬 빠릅니다만,

이게 10개를 넘어서 100개 1000개로 가면 사람이 할 수 있는 일이 아니죠 ㅋ

4. 자 그러면 저는 1번에서 본 과일 가격표에서 자동으로 단가를 매칭하여 총합까지 계산되도록 할 계획입니다.

이때 쓰는 것이 바로 vlookup 입니다. 1번 과일가격표에서 내가 지정한 셀의 값(과일)이 항목이 있는지 찾고, 그에 대한 단가를 반환하는 것이 되겠죠.

자, 상황이 이해가 되셨나요 ?

5. 저는 3번 그림의 C2셀에 아래와 같이 함수를 작성하였습니다.

B2 -> 내가 찾을 값 (사과)

Sheet1!$A:$B -> sheet1 에 있는 과일가격표의 배열 정의

2 -> Sheet1!$A:$B 에서 B2값을 찾았으면 해당 값이 있는 2번째 열의 셀 값을 반환

False -> B2의 값과 정확하게 일치할 경우만 값을 반환 (True : 유사 일치 가능)

어떤가요 ? 엑셀 vlookup 활용 방법에 대해서 살펴보았는데요, 그렇게 어렵지 않죠 ?

엑셀 if함수 (조건함수 엑셀 값이 없으면?)

엑셀에서 유용하게 활용하면 좋은 기능 중 하나가 바로 함수 입니다. 지난 포스팅에서도 잠시 텍스트 추출 하는 함수들에 대해서 살펴보았었는데요. 오늘은 엑셀 조건함수 중 하나인 IF함수에 대해서 살짝 정리해보도록 할께요.

엑셀 IF함수 란 ?

말 그대로 IF = 만약에 라는 뜻입니다.  내가 원하는 셀의 값에 대한 조건 (판별, 비교 등등) 을 걸고 싶을 때

이용합니다.

함수식은 아래와 같습니다.

=IF(조건,조건이 참일 때 출력할 결과 값, 조건이 거짓일 때 출력할 결과 값)

쉬운 예를 하나 들어보겠습니다.

A셀에 있는 값들에 대해서 B셀들에 제가 원하는 결과를 출력해 보려고 합니다.

ex1 ) A열의 값이 10과 같거나 크면 "10보다 크다" 라고 B열에 출력, 10보다 작으면 "10보다 작네" 라고 출력

B2 에 다음과 같이 함수를 작성합니다. 앞서 말씀드렸던 함수식을 이용합니다.

=IF(조건,조건이 참일 때 출력할 결과 값, 조건이 거짓일 때 출력할 결과 값)

=IF(A2>=10,"10보다크네","10보다 작네")

조건 : A2>=10 -> 10보다 큰지를 판별하기 위한 조건 설정

참일 때 출력 값 : "" 을 붙이는 것은 텍스트를 넣기 위한 식별 부호입니다. 예를들어 참인 경우 다른 셀 값을 넣겠다라고 하면 다른 셀 주소 값을 입력하시면 됩니다. =IF(A2>= 10, C2, B2) 이런식으로요.

* 결과

ex1-1) 위와 같이 함수를 사용하여 결과값을 보면 A2값이 10과같거나 크다로 설정을 했는데, 10인 경우 따로 표기를 할 수가 없습니다. 이런 경우 중첩 IF문을 사용합니다.

함수 로직은 이렇습니다.

1) 첫번째 IF문에서 A2=10 인지 판별

2) 참이면, "10이네" 출력

3) 거짓이면, 두번째 IF문 조건을 넣어 10 초과/미만을 판별

함수식 : =IF(A2=10,"10이네", IF(A2>10,"10보다크네","10보다 작네"))

위에 IF문은 제가 예를 들은 결과를 위하여 거짓 결과값 출력 부분에 사용한 것이며, 참 결과값 출력 부분에도 모두 사용이 가능합니다.

* 결과

이따금 엑셀에서 한 셀에 여러 텍스트 중에 필요한 테스트만 뽑아내는 작업이 필요한 경우가 있습니다.

저는 오늘 날짜 셀에서 필요 텍스트를 뽑아와야 하는 작업이 있어서 찾아본 내용을 정리해 보았습니다.

대표적으로는 엑셀에서 텍스트 추출 함수로, Left, Right, Mid 함수가 있습니다.

* LEFT

왼쪽에서부터 원하는 자릿수만큼 텍스트를 추출

=LEFT(추출할 셀,뽑아올 텍스트숫자)

여기서 숫자에는 공백도 모두 포함이 됩니다.,

*Right

오른쪽에서부터 원하는 자릿수만큼 텍스트를 추출

=RIGHT(추출할 셀,뽑아올 텍스트숫자)

일반 텍스트와는 달리 날짜 형식으로 셀서식이 이루어진 경우 위의 함수를 이용하시면 안됩니다.

해결 방법은 아래에 따로 설명할께요.  일단은 엑셀 텍스트 추출 함수인 (left,right, mid) 만 보겠습니다.

=LEFT(A2,7) 결과 값

* MID 함수

기준값을 정해 주고, 기준값으로부터 특정 자릿수까지 텍스트를 추출하는 함수

=MID(추출할 셀,기준값 텍스트 위치,뽑아올 텍스트숫자)

예를 들어 2016-01-01 에서 01-01 만 추출하고 싶은 경우

01-01 의 첫자리가 위치한 자릿수를 기준값으로 합니다. 첫글자가 0이니 앞에서부터 세어보면

6번째가 되겠네요.

=MID(A2,6,5) 처리를 해서 결과를 보면 아래와 같이 01-01 만 추출이 가능합니다.

엑셀 텍스트 추출, 형식이 다른 셀 내용 일부만 뽑아 내기 함수인 left,right,mid 는 텍스트형식일 때만, 원하는 글자를 뽑아올 수 있고, 날짜 형식을 위와 같이 적용하면 이상한 숫자만 추출이 됩니다.

그 이유는 셀값이 날짜 형식으로 되어 있지만, 이를 텍스트로 변환하여 보면 숫자로 되어 있기 때문입니다.

left,right, mid 는 텍스트를 뽑아오기 때문에 변환된 숫자값만 가져오게 되는 것이죠.

때문에 날짜 형식에서 일부 월이나 날짜를 추출할 경우엔 아래와 같은 함수를 사용하시면 됩니다.

=TEXT(A2, "yyyy-mm") 식으로 사용하시면 됩니다.

yyyy: 연도

mm : 월

dd : 일

엑셀 소문자 대문자 자동변환 (upper, lower)

엑셀은 정말 알면 알수록 업무할 때 편한 기능들이 수두룩 합니다.  대문자 소문자가 섞인 문자들을 비교할 때는 아무래도 한 편으로 몰아서 정렬을 해 주어야 정확한 비교가 가능하기 때문에 일련의 작업들이 필요로 됩니다.

* 엑셀 소문자 대문자 자동변환 기능 활용

UPPER

해당 셀을 영문자를 모두 대문자로 변환하는 함수입니다.

셀 란에 =upper(셀값)  으로 사용을 합니다.

<결과>

반대로 대문자 소문자 자동변환은 LOWER 기능을 활용합니다. 마찬가지로 셀값에 =lower(셀값) 을 입력합니다.

<결과>

<최종 결과 화면>

간단하죠 ? 함수 사용만으로 노가다 작업을 충분히 없앨 수 있기 때문에, 엑셀 함수는 잘 알아두었다가 활용하여 보시기 바랍니다.