안녕하세요 콩 한쪽도 나누는 투자자 목부장입니다!
커뮤니티-재테크 QnA 게시판을 돌다가
게리롱 튜터님의 시세 트래킹 양식을 사용하는데
1000개 이상 단지를 입력하다 보니
더 이상 세로변환이 안된다는 글을 보았습니다.
저도 1000개 이상 입력을 하면서..(그 당시 이 양식으로 시세조사를..)
게리롱 튜터님이 어떻게 함수를 설정하였을까?
또 1000개 이상은 일일히 해야하나? 라는 생각이 있었지만
구글링을 통해서 1000개 이상 만드는 방법을 알게 되어
이렇게 공유를 하게 되었습니다.
우선 원본 자료 및 글은 링크 타고 보시면 좋을 것 같습니다.
https://cafe.naver.com/wecando7/9165829
우선 첨부 파일로 올린 자료는
2000개 까지는 해놓았습니다만…
혹시 더 필요하시면 아래의 설명글 참조 해주세요!
우선 파일을 열게 되면 아래의 시트를 한번 보시면
처음에 원자료 /세로변환/ 추출 /추출 결과/결과값등 탭이 있습니다.
거기서 마우스 우클릭
숨기기 취소 칸이 나오게 됩니다. 여기서 숨기기 취소 클릭!!
변환이라는 시트가 숨겨져 있습니다. 여기서 확인을 누르면 변환이라는 시트가
아래 사진처럼 뿅하고 나타났습니다.
저희는 여기서 변환이라는 시트에서 작업을 할 예정입니다. ㅎㅎ
여기서 스트 보호가 걸려있어서 저희가 편집할 수 없는데요…
변환 시트-마우스 우클릭-시트 보호 해제까지 가야 저희가 원하는 편집을 할 수 있습니다.
마우스를 열심히 내리다보면 1001번까지 수식이 들어가있는 걸로 확인이 됩니다.
그렇기에 저희가 1000개 까지 밖에! 트래킹을 못하는 거죠… ㅠㅠ
그렇다면 여기서 단순히 드래그 하면 늘어나는가? 그건또 아닙니다. ㅠㅠ
자 그렇다면 여기서 부터는 어려울 수 있는 엑셀함수를 이해하여야 하지만
매우 복잡하고 어려운 일이기에 간단히 설명 드리자면!!
알파벳 a 부터 z 까지 26가지 문자를 ASCII코드를 이용해서
함수로 변환시켜 행/열을 변환하는건데요.
ASCII 문자열 일부인데 숫자와 각 알바벳이 1대1로 매칭이 됩니다.
저희는 이걸 이용해야 하지만 본질이 아니기에…. 궁금하시면 구글에 검색해보세요.. ㅎㅎ
이제 본격적으로 가장 중요한!!! 반복작업을 할 것입니다!
=INDIRECT("원자료!"&"AL"&CHAR((ROW()-923))&COLUMN())
이게 함수 입니다.
의미는
이렇게 원자료 시트의 ALZ의 열의 2번째 행을 가져 오라는 뜻입니다!!
저희는 딱 여기서 2가지를 바꿀 겁니다.
형광팬으로 표시했는데요.
문자열 AL 과 923이라는 숫자를 변경할 겁니다. 어떻게?
(손으로요…. 제가 함수를 이용해서 변환하려고 해보았지만 실패해서 혹시 엑셀 박사님 계시면 함수 알려주세요!)
알파벳 A 부터 Z까지 총 26개의 알파벳이 있는데요.
그렇기에 우리는 어떤 행이 되더라도 65와 90사이에 숫자가 나와야 합니다.
즉, 1001행 이나 3054행이나 16660행 모두 65와 90이 나와야 합니다.
즉 1001-936=65가 되는 거죠 3054-2964=90 등
따라서 저희는 반복작업으로 특정 행에 올때마다 26씩 더하면 됩니다.
923→946→972 쭉쭉 나아가시면 됩니다.
이제 두번째 변경해야하는 건 현재 표시한 “AL”인데요.
여기는 다음 알파벳을 입려하면 됩니다.
즉, A→B→C→…→Z→AA→….→ZZ→AAA→AAB→…→ZZZ→AAAA 이렇게요…
여기는 어렵지 않죠…!! ㅎㅎ
이제는 거의 다왔습니다…
아래의 점을 클릭 후 드래크 하면 됩니다. 오른쪽으로 한번 드래그하고 마우스 떼고
다시 점 클릭후 밑으로 쭉
밑으로 무한정 내리시면 안되고 26번째 칸 까지 내리시면 됩니다.
그런데 26번째 칸을 어떻게 아냐고요….?
이렇게 붙이게 된다면
이런식으로 #REF 표시가 뜨면 실패/ 성공하면 위처럼 보이기 때문에 #REF 쪽에서 다시 같은 작업을 반복하면 됩니다.
여기서 중요한건 게리롱님이 1001에서 끊었기에 26개 알파벳 끝나는 지점이 1013 지점이니 참고하시면 됩니다.
이렇게 함수를 늘리시면 1000개 이상 단지도 트래킹 가능합니다!!
2000개 까지는 제가 늘렸지만 혹시 더 필요하시면 위의 방식을 활용하시면 됩니다!!
ZIP 파일 밖에 안 올라가서 집으로 압축 했습니다!
★시세트래킹_v4_게리롱_공유용 2000개 버전.zip
다운로드
댓글
