Exceller's Home > 사용자 제공 자료

'무지개타고'님의 통계강좌

- 최초 작성일 : 2008-06-26
- 최종 수정일 : 2008-06-26

- 강좌 읽음수 : 3,516회
- 자료 작성자 : 무지개타고 (조석현님)

- 자료 편집자 :

강좌 제목 : 통계로 세상보기 - (64) 유사도(?) 검색

 

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

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


예제 파일 내려받기


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

31

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

32

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)),"")

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

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

33

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번 까지만 중첩해 사용할 있기에 이를 피하고자 이름정의 기능을 사용했다. 위 방식은 검색어를 낱글자로 나눈 후 목록과 비교하는 방식이다. 즉 검색어 "친구와함께"를 "친", "구", "와", "함", "께" 라는 낱글로 구분해 이 모두에 대해 검색하는 방식이다. 그러다 보니, "친朴"도 튀어나오고 "김구라"도 붙어 나온다.

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

34

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,분할)),검색범위)))

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

35

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

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

검색범위 : =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(분할))


Previous

Next

Copyright ⓒ Since 2008 아이엑셀러 닷컴. All rights reserved.

이 페이지의 저작권은 컨텐츠 제공자에게 있습니다.

본 강좌의 내용은 필자 개인의 견해이며, 아이엑셀러 닷컴의 공식 견해와 일치하지 않을 수도 있습니다.