본 강좌는 아별닷컴의 주인장 오주원에 의해 작성되었습니다. |
본 강좌에 설명된 내용은 저작권법에 의해 보호 받고 싶고, 개발자의 창의성(아이디어)은 꼭 존중받았으면 좋겠습니다. |
http://www.abyul.com/zbxe/73522 |
|
작성일 : 2010-02-19 1:30 AM |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
아 별 함 수 |
|
|
|
|
|
|
|
|
|
|
|
아별이 만든 사용자 정의 함수(UDF : User Defined Function) |
이름을 엑신 함수 변경할까하다가.. 어쩐지 촌스러운 냄새가 나서 아별 함수로 다시 되돌렸습니다. ^.^ |
업무를 하다가 엑셀에 왜 이런 함수가 없을까해서 만든 함수들입니다. |
|
|
|
|
오늘은 아별 함수 중 텍스트에 관련된 함수에 대해서 설명 드리겠습니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
오늘 설명드린 함수들은 아별툴을 설치하면 사용할 수 있으며, |
|
|
|
|
아래 주소에서 최신 버전을 다운 받으시면 됩니다. |
|
|
|
|
|
http://www.abyul.com/zbxe/abT_Down |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
텍스트에 관련된 함수는 아래 3개가 있습니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
splitText |
|
|
|
|
|
|
|
|
|
|
대상셀의 텍스트를 구분자를 이용해서 텍스트 나누기 해줍니다. |
|
|
|
=splitText( 대상 셀, 구분자, 몇번째 요소인지 ) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
대상셀 : 구분자를 기준으로 나누고자 하는 원본 텍스트가 있는 셀 |
|
|
|
|
구분자 : 텍스트를 나누기 위해 기준이 되는 구분 텍스트 |
|
|
|
|
|
몇번째 요소 : 숫자를 입력하면 해당 번째 요소를 갖고 옵니다. |
|
|
|
|
|
생략하면 구분자를 기준으로 여러 요소로 나뉘어진 배열을 리턴합니다. |
|
|
|
배열로 리턴시엔 Ctrl+Shift+Enter로 입력. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ex) splitText( A1, ";",3) : A1셀에 있는 텍스트를 ;로 나눴을때 3번째 요소 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
betweenText |
|
|
|
|
|
|
|
|
|
|
대상셀의 텍스트에서 시작문자와 끝문자 사이의 문자열을 갖고 옵니다. |
|
|
=betweenText( 대상셀, 시작문자, 끝문자, 몇번째 요소인지 ) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
대상셀 : 중간 문자열을 갖고오고자 하는 원본 텍스트가 들어있는 셀 |
|
|
|
|
시작문자 : 찾는 문자열 앞에 있는 문자. 가로안의 문자를 갖고 올 경우 "(" |
|
|
|
끝문자 : 찾는 문자열 뒤에 있는 문자. 가로안의 문자를 갖고 올 경우 ")" |
|
|
|
몇번째 요소 : 숫자를 입력하면 해당 번째 요소를 갖고 옵니다. |
|
|
|
|
|
생략하면 첫번째 요소를 갖고 옵니다. |
|
|
|
|
|
|
|
"결산(結算) 보고(報告)" 에서 "報告"를 갖고 오고 싶을때 2를 씁니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ex) betweenText( A1, "(",")") : A1셀에 있는 셀값에서 (와 ) 사이의 값을 갖고옵니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
combineText |
|
|
|
|
|
|
|
|
|
|
대상 셀 영역의 셀 값을 모두 합쳐줍니다. |
|
|
|
|
|
|
=combineText( 대상 셀 영역, 구분자 ) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
대상 셀 영역 : 셀 내용들을 하나로 합치고 싶은 셀 영역입니다. ex) A2:A30 |
|
|
|
구분자 : 셀 값들을 하나로 합할때 구분자로 구분해줍니다. 생략가능합니다. |
|
|
|
|
맨 마지막 구분자는 생략됩니다. A,B,C, 되면 구려서 A,B,C 됩니다. |
|
|
|
ex) combineText( A1:A5, ",") : A1부터 A5셀에 있는 셀값을 ,를 구분자로 모두 합쳐줍니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
각각의 함수를 예제를 통해서 자세히 알아봅니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
splitText( 대상 셀, 구분자, 몇번째 요소인지 ) |
대상셀에 있는 텍스트를 구분자를 이용해서 텍스트 나누기 해줍니다. |
|
|
|
|
|
|
|
|
|
|
|
|
엑셀의 기본 기능인 텍스트 나누기를 함수로 구현한 것입니다. |
|
|
|
|
전표 입력시 적요란에 아래와 같이 입력하도록했는데, |
|
|
|
|
|
월별로 업데이트할때마다 텍스트 나누기 하기도 귀찮아서 만들었습니다. ^-^; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
국책_우주개발_세미나_임대비 |
|
|
|
|
|
|
|
국책_나개발_비품_책상 |
|
|
|
|
|
|
|
|
국책_우주개발_사무용품_몽땅연필 |
|
|
|
|
|
|
|
산학_우리개발_국내여비_출장식대 |
|
|
|
|
|
|
|
국책_우주개발_기타잡비_타이어 |
|
|
|
|
|
|
|
산학_우리개발_세미나_다과비 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
예제를 살펴보면 "_"를 구분자로 해서 4개 항목씩 구분되는군요. |
|
|
|
|
아래와 같은 수식을 입력하고 수식 자동 채우기하면 한방에 끝~ ㅎㅎ |
|
|
|
|
=splittext($C68,"_",COLUMN(A$1)) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
국책 |
우주개발 |
세미나 |
임대비 |
|
|
|
|
|
|
|
국책 |
나개발 |
비품 |
책상 |
|
|
|
|
|
|
|
국책 |
우주개발 |
사무용품 |
몽땅연필 |
|
|
|
|
|
|
|
산학 |
우리개발 |
국내여비 |
출장식대 |
|
|
|
|
|
|
|
국책 |
우주개발 |
기타잡비 |
타이어 |
|
|
|
|
|
|
|
산학 |
우리개발 |
세미나 |
다과비 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
그러다가.. |
|
|
|
|
|
|
|
|
|
몇번째 요소라는 숫자가 자동으로 증가하게 하기 위한 COLUMN(A$1) 조차도 귀찮아져서 |
|
|
"몇번째 요소" 인수를 생략시 배열을 리턴하게 만들었습니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
가로로 길게해서 4개의 셀을 선택해놓고, (예제의 결과값들이 4개 항목이라서 4개를 선택한겁니다.) |
|
=splittext(C68,"_") 를 입력하고 Ctrl+Shift+Enter를 치면 선택했던 4개 셀에 해당 항목이 채워집니다. |
|
그 아래쪽은 라인 단위로 수식을 복사하면 되겠죠? ㅎㅎ |
|
|
|
|
|
=splittext(C68,"_") |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
국책 |
우주개발 |
세미나 |
임대비 |
|
|
|
|
|
|
|
국책 |
나개발 |
비품 |
책상 |
|
|
|
|
|
|
|
국책 |
우주개발 |
사무용품 |
몽땅연필 |
|
|
|
|
|
|
|
산학 |
우리개발 |
국내여비 |
출장식대 |
|
|
|
|
|
|
|
국책 |
우주개발 |
기타잡비 |
타이어 |
|
|
|
|
|
|
|
산학 |
우리개발 |
세미나 |
다과비 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
"구분자"에 문자열이 아닌 숫자를 넣으면, 문자 길이로 텍스트 나누기 해줍니다. |
|
길이가 일정한 텍스트를 일정 길이 단위로 나누고 싶을 때 유용하게 사용됩니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
영희철수민희지영맹꽁냥냥 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
영희 |
철수 |
민희 |
지영 |
맹꽁 |
냥냥 |
|
|
|
|
|
=SPLITTEXT2(C107,2) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
민희 |
|
|
|
|
|
|
|
|
|
|
=SPLITTEXT2(C107,2,3) |
← 2개 글자씩 나눈 결과 중 3번째 요소를 갖고 왔다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
참고로.. |
|
|
|
|
|
|
|
|
|
텍스트 나눈 결과 값의 중복없는 결과를 만들어주는 SPLITTEXT2 함수도 있습니다. |
|
|
|
|
|
|
|
|
|
|
|
|
사과,포도,사과,배,꿩,배 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
사과 |
포도 |
배 |
꿩 |
|
|
|
|
|
|
|
=SPLITTEXT2(C119,",") |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
betweenText( 대상셀, 시작문자, 끝문자, 몇번째 요소인지 ) |
대상셀에 있는 텍스트에서 시작문자와 끝문자 사이의 문자열을 갖고 옵니다. |
|
|
|
|
|
|
|
|
|
|
|
|
이 함수는 최신가요 파일명에서 가수명을 뽑아내다가 만들게 되었습니다. ^-^;; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
(소녀시대) Oh! |
|
|
|
|
|
|
|
|
|
(2AM) 죽어도 못 보내 |
|
|
|
|
|
|
|
|
(씨엔블루) 외톨이야 |
|
|
|
|
|
|
|
|
(현아) Change (Feat. 용준형 From Beast) |
|
|
|
|
|
|
(2AM) 웃어 줄 수 없어서 미안하다 |
|
|
|
|
|
|
|
(씨야&다비치&티아라) 원더우먼 |
|
|
|
|
|
|
|
(가인&조권) 우리 사랑하게 됐어요. |
|
|
|
|
|
|
|
(이승기) 사랑이 술을 가르쳐 (Feat. 백찬 From 8eight) |
|
|
|
|
|
(티아라) 처음처럼 |
|
|
|
|
|
|
|
|
|
(포맨) 못해 (Feat. 美) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
아래의 수식 한방이면 가수이름 갖고 오기 성공~ ㅎㅎ |
|
|
|
|
|
=betweentext(C131,"(",")") |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
소녀시대 |
|
|
|
|
|
|
|
|
|
|
2AM |
|
|
|
|
|
|
|
|
|
|
씨엔블루 |
|
|
|
|
|
|
|
|
|
|
현아 |
|
|
|
|
|
|
|
|
|
|
2AM |
|
|
|
|
|
|
|
|
|
|
씨야&다비치&티아라 |
|
|
|
|
|
|
|
|
가인&조권 |
|
|
|
|
|
|
|
|
|
이승기 |
|
|
|
|
|
|
|
|
|
|
티아라 |
|
|
|
|
|
|
|
|
|
|
포맨 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
두번째 가로로 둘러싸인 내용이 궁금하다면.. 아래 수식으로 한방에~ 유후~ ㅎ |
|
|
|
=betweentext(C131,"(",")",2) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Feat. 용준형 From Beast |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Feat. 백찬 From 8eight |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Feat. 美 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
참고로.. |
|
|
|
|
|
|
|
|
|
|
시작문자와 끝문자가 단일 문자가 아닌 문자열이어도 됩니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
003. 씨엔블루 - 외톨이야.mp3 |
|
|
|
|
|
|
|
006. 씨야&다비치&티아라 - 원더우먼.mp3 |
|
|
|
|
|
|
009. 티아라 - 처음처럼.mp3 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
위 파일명에서 노래 제목을 갖고와 보겠습니다. |
|
|
|
|
|
|
=betweentext(C175," - ",".mp3") |
너무 쉽죠? ^-^;; |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
외톨이야 |
|
|
|
|
|
|
|
|
|
|
원더우먼 |
|
|
|
|
|
|
|
|
|
|
처음처럼 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
combineText( 대상 셀 영역, 구분자 ) |
대상 셀 영역의 셀 값을 모두 합쳐줍니다. |
|
|
|
|
|
|
|
|
|
|
|
|
엑셀의 기본 기능으로 텍스트를 합칠때는 & 연산자를 사용합니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
젠쟝 |
이 넘의 |
엑셀질~ |
퇴근 |
하고 |
싶다 |
|
|
|
|
|
|
|
|
=" 퇴근" |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
젠쟝 이 넘의 엑셀질~ 퇴근하고 싶다 |
|
|
|
|
|
|
|
=C193&D193&E193&F193&G193&H193 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
엑셀 1~2년 쓰게 되면 PHONETIC 함수를 알게 되지요. |
|
|
|
|
|
원래는 윗주를 합해주는 함수지만, 윗주가 없으면 셀 내용을 합쳐줍니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
젠쟝 이 넘의 엑셀질~하고 싶다 |
|
|
|
|
|
|
|
=PHONETIC(C193:H193) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
문제는 PHONETIC함수는 수식으로 되어 있는 셀값을 갖고 오지 못한다는 것이요. |
|
|
예제의 "퇴근"이 수식(="퇴근")으로 입력되어 있어 엉뚱한 결과가 되었습니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
그래서 만든 함수가 COMBINETEXT함수 입니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
젠쟝 이 넘의 엑셀질~ 퇴근하고 싶다 |
|
|
|
|
|
|
|
=combineText(C193:H193) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
이에 더해서 구분자로 각 셀 내용을 구분해주는 추가 기능을 넣었구요. |
|
|
|
젠쟝~ 이 넘의~ 엑셀질~~ 퇴근~하고~ 싶다 |
|
|
|
|
|
|
=combineText(C193:H193,"~") |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
참고로 중복항목을 제거해주는 COMBINETEXT2함수도 있습니다. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
사과 |
포도 |
사과 |
배 |
꿩 |
배 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
사과,포도,배,꿩 |
|
|
|
|
|
|
|
|
|
=COMBINETEXT2(C219:H219,",") |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|