소프트웨어 엑셀 사용자 정의 함수와 내장 함수 활용
2019.09.13 10:59
안녕하세요? 추석을 맞이하여 즐거운 시간 보내시고 계신가요? 본가에 안가 시간이 좀 남아서...
질문 글에 대해 올린 답글인데요. 재미 있는 주제인 것 같아서 정리해봅니다.
잊고 살던 엑셀 본능?이 깨어나는 것 같아서 잠깐 오버한다는... ^^; (엑셀 초보/중급?을 위한 설명입니다.)
질문 글; https://windowsforum.kr/12142002 <- 사용자 정의 함수가 필요한 상황입니다. 사람손으로 하기에는...
1. 엑셀 함수란?
- http://erunjoy.com/156
- http://phylab.yonsei.ac.kr/board.php?board=excel&indexorder=2&body_only=y&button_view=n&command=body&no=10
2. 엑셀 내장 함수들
- http://www.malgum.com/board/study/board_view.asp?page=1&num=199#
- https://namu.wiki/w/Microsoft%20Excel/%ED%95%A8%EC%88%98%20%EB%AA%A9%EB%A1%9D
3. 사용자 정의 함수란?
- https://mainia.tistory.com/1180
- https://kiyoo.tistory.com/207
4. 사용자 정의 함수 예 (질문글에 대한 해법이 될 수 있는 한 예제입니다.)
- 엑셀5부터 VBA를 포함하였는데, 크게 나눠보면 매크로와 사용자 정의 함수에서 사용할 수 있습니다.
- 25년 전쯤인데 그때는 간단한 것도 도움말 참고해 가면서 많은 시간을 투자했던 기억이 납니다.^^;
- 지금은 인터넷에 스크립트 소스들이 넘처나서 잘 가공만 하면 쉽게 결과에 도달 할 수 있는 것 같습니다.
[답글 엑셀 파일에 대한 설명, 위 질문글 링크를 한번 정독하심이...] -> 추가로 첨부 파일 확인; Word Cluster.zip
- 폴더 구조; 부모 폴더(예; Word Cluster) 안에 10년 단위로 분류된 폴더가 있고 그 내부에 각 년도가 있고 년도 내부에 특정 월의 폴더들이 있고 그 월 폴더 내부에 텍스트 파일이 수십개 모여 있는 구조 입니다. 년도 까지는 명시이고 하부 월 폴더 부터는 랜덤으로 올 수 있다고 하십니다.
- 첨부 파일은 매크로(사용자 정의 함수)가 들어 있어서 content 포함으로 open 하셔야 합니다.
- 예제 엑셀 파일 설명; 첨부파일에서는 공개되었던 텍스트 파일만 반복적으로 포함시켜서 결과가 다양하게 나오지는 않습니다.
- 아래는 별도로 제공 받은 샘플 데이터에서 나온 결과들 입니다.
[A1셀] =LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)
윗윗쪽 스샷의 빨간 네모 상자 안을 보시면 엑셀 파일의 상대 위치를 알 수 있습니다.
사용자 정의 함수에서 이용할 폴더 경로인데, 혹시 이해 안되실까 해서, 해당 폴더에 이 엑셀 파일을 두면 경로를 자동으로 추출할 수 있도록 내장함수를 조합하였습니다. 엑셀 파일을 다른 경로에 두거나, 필요시에는 직접 경로를 입력하시면 됩니다. 예) D:\TEST\
[A2:A50셀] 10년 단위로 년도를 묶는 부모 폴더명을 기입하였습니다.
[B2:B50셀] 각 년도를 기입하였습니다.
[C1:J1셀] 카운팅할 단어 입니다.
[C2:J50셀] C2에 사용자 정의 함수를 이용한 식을 만들고 자동 채우기 하였습니다.
[C2셀] =CountInSubFolder($A$1 & $A2 & "\" & $B2,C$1)
사용자 정의 함수 이름은 CountInSubFolder 이고 2개의 인수가 필요합니다. 앞쪽은 년도까지 명시한 전체 폴더 경로, 뒷쪽은 찾을 단어입니다.
앞쪽 전체 경로는 [A1셀] + [A2:A50셀] + [B2:B50셀] 을 조합하여 완성 하였습니다. 결과 예) d:\Word Cluster\1971-1979\1971\
주소 표시에서 $가 붙어 있으면 절대참조로 자동채우기에서 변하지 않습니다. 주소 입력후 F4를 반복하여 누르면 형태의 변화가 반복됩니다.
alt + F11 를 눌러 함수를 보면 아래와 같습니다. (짧은 스크립트로 많은 일을 시킬수 있다는...)
- CountInSubFolder 함수는 지정 폴더의 서브 폴더를 조회, 그안의 파일들의 목록을 얻어, 각 파일에 대해 CountInTextFile 함수를 적용합니다.
- CountInTextFile 함수는 해당 텍스트 파일에서 주어진 문자열이 나타난 횟수를 카운팅하여 결과 값을 돌려 줍니다.
5. 제가 이 문제에 대해 접근한 방법은... 구글 검색입니다. ^__^ (VBA 기초는 되어 있어야 하지만...)
- 두가지 키워드로 구글 검색하였습니다. 그 중 참고한 페이지 입니다.
-> vbs 서브 폴더에서 파일 목록; https://foco85.tistory.com/47
-> vbs word count in text file; https://stackoverflow.com/questions/37895412/search-number-of-times-a-string-occurs-in-text-file
--- 문자열을 카운팅하는 방법은 여러 가지가 있을 것 같은데 질문하신 분은 이정도면 되겠다 하시어 여기서 멈췄습니다.
--- 정확한 카운팅을 위해서는 몇가지 고려해야 할 사항들이 있어서, 수정의 편의를 위해 카운팅 함수는 분리하였습니다.
엑셀을 잘 배워 두면 써먹을데가 많은 것 같습니다. 특히 총무, 재무 등 숫자를 많이 다루는 회사원에게는 필수라 할 수 있겠네요.
완벽히 잘할 필요까지는 없을 것 같고(공부 시간 많이 필요...), 구글 신의 도움을 받아 구슬을 꿸수 있을 정도면 좋을 것 같습니다.
모든 윈포 회원님들 안전운전 하기고, 과음, 과식은 피하시고 건강하고 후유증 없는 추석되시길 바랍니다. ^^
댓글 [6]
-
라연 2019.09.13 19:33
-
지후빠 2019.09.16 01:31
넵 댓글 감사합니다.^^ 이곳이 윈도우 포럼이라 인기 없을줄 알고 올린 글입니다.
그나마 라연님이 댓글을 주시어, 무플은 면했습니다. ㅎ__ㅎ
생산성면에 있어서는 엑셀만 한 것이 없는 것 같습니다.^&^
-
ehdwk 2019.09.16 17:52
수고하셨습니다. -
파풍초 2019.09.17 14:25
지후빠님! 수고해 주셔서 감사합니다.
덕분에 큰 짐을 덜었는데, 이렇게 자세하게 설명햏 주시니 너무 감사합니다.
늘 강건하시길 기원드립니다.
-
지후빠 2019.09.18 10:55
넵 감사합니다. ㅎ_ㅎ
-
자공 2019.10.12 19:49
수고하셧습니다. ^^
번호 | 제목 | 글쓴이 | 조회 | 추천 | 등록일 |
---|---|---|---|---|---|
[공지] | 강좌 작성간 참고해주세요 | gooddew | - | - | - |
4449 | 기 타| [오토핫키 v1] 경로, 이름 ,드라이브명 등등 추출 함수 [2] | 청락은복 | 816 | 8 | 03-06 |
4448 | 소프트웨어| 부팅항목숨기기,보이기-VHDman Lite0.0.8 [3] | 카멜 | 526 | 10 | 03-06 |
4447 | 윈 도 우| 시스템 보호 설정 우클릭 메뉴 [13] | 와로 | 1239 | 27 | 03-04 |
4446 | 소프트웨어| Bcd 항목 1회성 부팅하기-VHDmanLite [7] | 카멜 | 722 | 15 | 03-04 |
4445 | 소프트웨어| Bcd부팅항목 메뉴 숨기기 [3] | 카멜 | 434 | 9 | 03-04 |
4444 | 소프트웨어| 드라이버 백업&자동 설치 - WinNTSetup [17] | 무월 | 1530 | 31 | 03-03 |
4443 | 소프트웨어| 오프라인 윈도우 드라이버 백업 [9] | 카멜 | 1453 | 26 | 02-29 |
4442 | 윈 도 우| DISM 나의 윈도우 문제점 복구 [8] | 마로94 | 1292 | 13 | 02-28 |
4441 | 윈 도 우| 윈도우 장치 드라이버 추출(백업) 명령어 [25] | 컨피그 | 2573 | 64 | 02-23 |
4440 | 기 타| 컴퓨터 구매의 질문이 종종 보여서 나름의 팁을 얘기 합니다. [8] | Boss | 1893 | 5 | 02-19 |
4439 | 윈 도 우| 이 번 업데이트로 엣지에 추가된 사이드바 버튼 제거 [7] | 고고추 | 2117 | 9 | 02-16 |
4438 | 윈 도 우| cmd 관리자 권한 실행과 특수문자 경로 문제 [9] | 메리아 | 1065 | 12 | 02-14 |
4437 | 윈 도 우| 측면 버튼에 등록된 돋보기 기능... 기본값으로 복원 [2] | 사가르마타 | 1046 | 0 | 02-10 |
4436 | 윈 도 우| 윈11 탐색기 메뉴삭제 [5] | 진실 | 2355 | 19 | 02-06 |
4435 | 윈 도 우| 윈도우 탐색기 갤러리 안보이게 하는 방법 [1] | 사가르마타 | 1548 | 2 | 02-05 |
4434 | 기 타| 한 드라이브에 윈도우(Atlas OS)까지 2개 설치하는 법(?) (... | 누군가 | 1129 | 0 | 02-04 |
4433 | 기 타| 2024 새로운 시작, Rainmeter, 위젯, 바탕화면, 퍼포먼스 [16] | 모네곰 | 2470 | 38 | 02-01 |
4432 | 윈 도 우| 윈도우10~11 알고리즘에 영향을 끼치는 이미지 파일 삭제 ... [2] | risystem | 2275 | 4 | 01-23 |
4431 | 윈 도 우| Windows11 트레이 아이콘 내맘대로... [9] | 메인보드 | 3795 | 15 | 01-07 |
4430 | 기 타| 파워쉘로 WOL 수행 [3] | JR.오펜하이 | 1444 | 9 | 01-06 |
시간내서 한번 따라해 봐냐겠네요...물론 잘 안되겠지만...
글 삭제 안하실거죠? ㅎㅎ
즐겨찾기 해 놔야겠네요...^^
남은 추석 연휴 잘 보내세요~ 감사합니다..