• 최초 작성일: 2008-06-26
  • 최종 수정일: 2008-06-26
  • 조회수: 6,116 회
  • 작성자: 무지개타고
  • 강의 제목: 유사 단어 검색을 구현하다

엑셀러 권현욱

들어가기 전에

'통계'라고 하면 여러분은 어떤 생각이 드시나요? 저는 개인적으로 좋은 기억보다 그렇지 않은 기억이 많습니다만, 최근 들어 통계를 좀 더 공부해야겠다는 생각을 많이 하고 있습니다.

이번 시간에 함께 할 주제는 '무지개타고'님의 재미있는 통계이야기입니다. '무지개타고'님은 '통계로 세상보기'라는 블로그(https://onrainbow.tistory.com/)를 운영하고 있기도 합니다. 특유의 위트와 재미가 있는 통계 강의에 빠져보시기 바랍니다.


  • 이 페이지의 저작권은 콘텐츠 제공자에게 있습니다.
  • 본문 내용은 필자 개인의 견해이며 아이엑셀러 닷컴 공식 의견과 일치하지 않을 수도 있습니다.

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

로딩 중...

방법은 여러 가지가 있을 것이다. 물론 가장 수월한 방법은 지난 번처럼 다른 사람에게 떠넘기기(^^). 그것이 힘들다면 품 좀 팔아야 하겠다. 만약 찾을 단어가 '친구'라고 해보자(검색어 입력 셀: 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에서는 If 함수를 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(분할))

문득 인터넷 검색은 어떤 방식을 이용하는지 살짝 궁금하기도 하지만 더이상 궁금해 하지 않기로 했다. 아무래도 정신 건강에 해로울 것 같아서다.