[엑셀] 랜덤 낱말 만들기

살다보면 임의의 낱말 목록이 필요할 경우가 있다.

나의 경우를 보자면 데이터베이스를 테스트하기 위한 데이터 집합 등이 예가 된다.

필요에 따라서는 데이터 크기가 커지게 되는데 이럴 경우 일일이 데이터를 타이핑하는 것은 시간이 많이 걸린다.

하지만 컴퓨터의 힘을 빌어 일부 자동화를 할 수 있다면 지혜로운 방법이 될 수 있다.

여기서는 엑셀을 이용하는 법을 정리한다.

1) 로우 데이터를 획득한다.

인터넷에는 이미 충분하게 데이터가 널려있다. 데이터를 만들기 전에 인터넷에서 찾아보자. 바퀴를 재발명할 필요는 없지 않는가? 만약 찾을 수 없다면 자동화해서 만들 수 있는 데이터를 이용한다.

나의 경우는 사전 데이터를 이용했다. 구글에서 dictionary.txt 라는 검색어로 검색하면,

http://www.math.sjsu.edu/~foster/dictionary.txt

가 처음으로 나타났다.

해당 데이터를 다운 받는다. 사이트에 다운 받으려면 로딩하는 시간 때문에 많이 걸리므로 나의 경우는 wget 유틸리티를 이용해서 받았다.

> wget http://www.math.sjsu.edu/~foster/dictionary.txt

마찬가지로 curl 유틸로도 할 수 있다. (git 을 설치하면 윈도우에서도 가능해지더라)

> curl -O http://www.math.sjsu.edu/~foster/dictionary.txt

cf) wget과 curl에 대한 설명은 wget vs curl: How to Download Files Using wget and curl 을 참고하세요.

2) 엑셀에서 

절대 메모장에서 dictionary.txt를 열어보려 하지마라. EditPlus라면 모를까. 열었다면 잠시 시스템이 멈춘듯한 느낌을 받을 수 있을 것이다. 이 글을 쓸 때 이 파일의 길이는 349,900였다.

그리고 엑셀도 2007 이전 버전을 사용한다면 말리고 싶다. 엑셀 포맷의 설계 당시 최대 열의 개수를 65,536개^[각주:1]이기 때문에 잘리게 된다. 엑셀 2007에서는 1,048,576까지 가능하다.

엑셀에서 해당 텍스트를 열면 '텍스트 마법사' 창이 뜨는데 그냥 '마침' 버튼을 누른다.

내가 어떻게 단어가 349,900개인줄 알았을까?

위의 그림을 보았다면 '셀의 제일 아래까지 스크롤 해보면 되지'라고 생각할 수 있다. 반은 맞고 반은 틀리다.

Row의 개수가 많아지만 스크롤하는 것이 우선 힘들다. 마우스 팝업메뉴에서 '위쪽', '아래쪽'을 선택한다면 좋은 접근이다.

Tip) 나의 경우는 (1,1)에서 Ctrl + ↓ 를 눌렀다. 그러면 (연속된 데이터로 구성되어 있다면) 데이터가 있는 끝에서 멈춰준다. (좌우도 마찬가지이다) 위로 올라가는 것도 동일하게 Ctrl + ↑ 를 누르면 된다.

3) 데이터를 랜덤으로 추려보자.

단어는 알파벳 순으로 되어 있는 것 같다. 문제는 내가 원하는 것은 이중 임의의 800개의 단어를 추리는 것이다.

349,900 / 800 = 437.375이 되므로 각 단어에 1~437의 랜덤 숫자를 부여하면, 랜덤 알고리즘이 유사한 확률로 나온다면  대략 800개의 그룹을 만드는 것이 가능하다.

잘 이해가 안될 수 있으니 아래를 따라가 보면 도움이 될 것 같다.

3-1) 단어에 랜덤 숫자 부여하기

엑셀에서 난수 값 생성을 할 수 있는 함수에는 RAND, RANDBEETWEEN이라는 두 가지가 있다.

여기서는 후자를 사용할 것이다. 왜냐하면 원하는 범위의 값 생성에 적합하기 때문이다.

Tip) 한동안 나를 괴롭혔던 기능중에 하나는 엑셀의 자동완성이다. 평균을 구하기 위해 AVERAGE 7글자를 다 쳐야만 하는 줄 알고 있었기 때문이다. 사실 자동완성 기능은 2글자만 쳐도 나온다.

직관적으로 목록에서 위/아래 화살표로 목록까지 선택은 되었다. 그렇다면 여기서 셀에 함수 이름을 넣고 싶다면 어떻게 해야 하나? 나는 엔터를 때때로 눌러보고 실패를 하다 결국 자동완성 기능을 포기 했다. 하지만 검색을 해보니 비즈폼에서 더블 클릭을 하면 선택 가능하다고 나와 있었다. 하지만 손을 키보드에서 마우스를 넘어가는 것은 속도를 줄이는 결과를 가져온다.

이 결과에 대한 답은 후배 황석현이 두 번의 시행착오 끝에 알려주었다. 답은. Tab 키였다.
나중에 구글 검색에 'tab' 키워드를 추가하여 검색 해보았다. 엑셀 2013 - 함수 입력의 4가지 방법라는 글에서 다루고 있었는데, '(' 넣는 것도 된다고 했으나 나는 열린 괄호로 함수명 입력을 못했다. (무엇인가 옵션 설정이 필요한 것인가?)

우찌하였던, 1부터 437까지 숫자 중에 임의의 숫자를 선택하는 함수는

=RANDBETWEEN(1, 437)

이다.

하나의 셀에는 아래와 같이 임의의 숫자가 선택이 되었다.

이제 모든 수많은 데이터에 언제 저 함수를 쳐넣을 것인가?

3-2) 자동완성기능 이용하여 전체 로우에 적용하기

힌트는 제목에 있다. 바로 '자동완성기능'이다. 마우스로 셀의 오른쪽 아래 끝을 선택하여 드래그 해본 사람은 어떤 기능인줄 알고 있을 것이다. 하지만 지금은 무려 로우가 349,900개이다.

따라서 마우스로 하려고 하지말고 키보드의 도움을 받아야 할 때이다.

바도 자동완성기능의 단축키인, Ctrl-D 이다.

우선은 적용할 셀을 선택해야하는데 팁은 아래와 같다.

 i) A열에서 Ctrl + ↓ 을 눌러 A349900로 간다.

 ii) →를 눌러 한칸 오른쪽으로 같다. (B열)

 iii) Shift + Ctrl + ↑를 눌르면 끝. (B1~B349900 이 선택됨)

 iv) 그리고, Ctrl-D

3-3) 정렬기능을 이용하여 같은 값으로 그룹핑하기

이제 A열에는 단어가, B열에는 그룹 번호가 부여되었다.

B열을 기준으로 정렬을 하면 임의의 숫자끼리 묶을 수 있다.

전체 선택하여(단축키, Ctrl-A) B열을 대상으로 정렬을 한다. 아래 그림참고

정렬을 해보면, B열에는 정렬이 안된 것 같이 보인다. 왜냐면 정렬을 한 이후에 랜덤값이 바뀌었기 때문이다.

하지만 그루핑이 되었다는 것은 800번쯤 가보면 a -> z -> a로 넘어가는 곳이 있다.

abrachia > zygomaticum > abbotvillage

사실 처음 데이터는 aaaa였으니 무엇인가 순서가 바뀌었음을 알 수 있다.

이제 349,900개중 임의의 약 800개(첫번째 그룹의 데이터는 780행 였기는 하지만)를 추릴 수 있었다.

Tip.) 시행착오를 해보니 

349,900 / 800 = 437.375에서 버림을 해서 437로 할 것이 아니라 올림을 해서 438로 하면 그룹당 개수를 늘릴 수 있었다.

(물론 이 랜덤은 말그대로 랜덤이므로 결과를 장담할 수는 없다. 1~2회 정도 반복해보면 원하는 데이터를 구할 수 있을 것이다.)

=RANDBETWEEN(1,438) 로 했을 때 첫 번째 그룹의 데이터 크기는 801이었다. (물론 바뀔 수 있다.)

1. Excel specifications and limits : http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx [본문으로]