VBA강좌

엑셀을 이용하다 보면 일정한 목록에서 원하는 자료를 찾아 불러오기를 상당히 많이 한다. 아마도 합 구하는 것 다음으로 많이 하지 않을까 싶다. 아래와 같은 자료에서 특정 단어가 포함된 자료를 불러오는 경우를 생각해보자.

방법은 여러 가지가 있을 것이다. 물론 가장 수월한 방법은 저번처럼... 넘에게 떠넘기기(^^). 이것이 힘들다면... 역시 품 좀 팔아야 한다. 만약 찾을 대상이 "친구"라면(검색어 입력 셀 : c1 ),

B7 셀 :
=IF(COUNTIF($I$2:$I$29,"*"&$C$1&"*")>=ROW($A1),INDEX($I$2:$I$29,MATCH(LARGE(N(ISNUMBER(FIND($C$1,$I$2:$I$29)))*$H$2:$H$29,ROW($A1)),N(ISNUMBER(FIND($C$1,$I$2:$I$29)))*$H$2:$H$29,0)),"")

조금 복잡하다. 그리고 배열수식이다.
그런데 이런 경우는 어떻게 될까? 찾을 대상이 "친구와함께"라고 한다면... "친구"로 검색된 자료 어디에도 "친구와함께"라고 기재되어 있지 않으므로 결과는 당연히 아무 것도 없다. 그렇지만 검색어는 "친구와함께"라 했어도, "친구"가 포함된 자료 정도는 불러와 준다면 얼마나 좋을까?

그래서 수작(?)을 부려봤다.

C7 셀 :
=IF(SUM(N(MMULT(임시결과_낱글,단위벡터)>0))>=ROW($A1),LARGE(MMULT(임시결과_낱글,단위벡터),ROW($A1))/검색어길이,"")

D7 셀 :
=IF(SUM(N(MMULT(임시결과_낱글,단위벡터)>0))>=ROW($A1),INDEX(검색범위,MATCH(LARGE(MMULT(임시결과_낱글,단위벡터)*POWER(EXP(1),LN(ROWS(검색범위)))+검색순번,ROW($A1)),MMULT(임시결과_낱글,단위벡터)*POWER(EXP(1),LN(ROWS(검색범위)))+검색순번,0)),"")

수식이 좀 길다. 그리고 역시나 배열수식이다. 엑셀2007은 모르겠고, 엑셀2003 에서는 함수를 7번 까지만 중첩해 사용할 있기에 이를 피하고자 이름정의 기능을 사용했다. 위 방식은 검색어를 낱글자로 나눈 후 목록과 비교하는 방식이다. 즉 검색어 "친구와함께"를 "친", "구", "와", "함", "께" 라는 낱글로 구분해 이 모두에 대해 검색하는 방식이다. 그러다 보니, "친朴"도 튀어나오고 "김구라"도 붙어 나온다.

음... 그리 실효성 있어 보이지 않는다. 그래서 좀더 수작(?)을 부려봤다.

E7 셀 :
=IF(SUM(N(MMULT(임시결과_어순,단위벡터)>0))>=ROW($A1),LARGE(MMULT(임시결과_어순,단위벡터),ROW($A1))/검색어길이,"")

F7 셀 :
=IF(SUM(N(MMULT(임시결과_어순,단위벡터)>0))>=ROW($A1),INDEX(검색범위,MATCH(LARGE(MMULT(임시결과_어순,단위벡터)*POWER(EXP(1),LN(ROWS(검색범위)))+검색순번,ROW($A1)),MMULT(임시결과_어순,단위벡터)*POWER(EXP(1),LN(ROWS(검색범위)))+검색순번,0)),"")

물론 배열수식이다. 이 방식은 검색어를 순차적으로 완성하는 쪽으로 생각해 봤다. 즉 검색어 "친구와함께"를 "친", "친구", "친구와", "친구와함", "친구와함께" 의 조합을 찾아서 결과를 보여준다.

수식을 곰곰히 봤다면 알겠지만, '낱글'과 '어순'에 사용된 두 수식은 거의 동일한 구조다. 단지 이름 정의한 '임시결과_낱글'과 '임시결과_어순'에서 Mid 함수 내 인수의 순서가 다를 뿐이다.

임시결과_낱글 =N(ISNUMBER(FIND(TRANSPOSE(MID(검색어,분할,1)),검색범위)))
임시결과_어순 =N(ISNUMBER(FIND(TRANSPOSE(MID(검색어,1,분할)),검색범위)))

그런데 이 방식도 의도한 만큼 딱딱 맞아 보이진 않는다. 저 넘의 "친朴"이 계속 따라 붙고 있으니... 그리고 검색어 작성에 제약도 있고...

이렇게 수작(?)을 부리다 보니, 문득 인터넷 검색은 어떤 방식을 이용하는지 살짝 궁금하다는 생각이 들기도 하지만 더이상 궁금해 하지 않기로 했다. 아무래도 정신 건강에 해로워 보이니...

그 외의 이름은 다음과 같이 정의되어 있으니 참고하시기 바란다.

검색범위 : =OFFSET(Sheet1!$I$1,1,0,COUNTA(Sheet1!$I:$I)-1,1)
검색순번 : =ROW(INDIRECT("1:"&ROWS(검색범위)))
검색어 : =Sheet1!$C$1
검색어길이 : =LEN(Sheet1!$C$1)
단위벡터 : =분할/분할
분할 : =ROW(INDIRECT("1:"&검색어길이))
상삼각행렬 : =N(분할<=TRANSPOSE(분할))