안녕하세요, 아메리칸느입니다.
시세지도/시세트래킹을 엑셀로 하면서 엑셀 활용에 어려움을 겪는 분들이 있는 것 같아서 글을 쓰게 되었습니다.
일단 저의 상태를 먼저 말씀드리자면 월부에서 사용하는 매크로나 임보 작성에 대한 노하우는 없는 상태입니다.
그러나 제가 알고 있는 몇 가지 팁으로 좀 더 시간을 아끼실 수 있을 것 같습니다.
어떤 팁인지 키워드로 정리하면 다음과 같습니다.
2. # 틀고정
3. # 특정 행/열 숨김버튼 생성
4. # 엔터치면 옆칸으로 넘어가기(=엔터치면 다음 행으로 넘어가는 현상 방지)
5. # 오늘 날짜 입력
6. # 푸버리지 파일에서 연식만 따두기
7. # 전고점 대비 목표매수 % 설정
8. # 목표매수가 설정
그럼 시작해볼까요?
시세지도/시세트래킹 올리는 과정을 처음부터 시작해보면서 위에 말씀드린 팁을 버무려보겠습니다.
예제파일은 24년 동대문구 푸버리지 파일입니다.
푸르니푸님 자료에서 단지를 추출합니다.
링크: https://cafe.naver.com/wecando7/11134729
저는 이 조건으로 추출했습니다.
동대문구의 모든 단지가 추출되었죠?
이제 작업의 편의를 위해, #틀고정과 행/열 숨김버튼을 생성하겠습니다. 저는 원본파일을 훼손하기 싫어서, 별도의 시트에 복사하여 그림과 같이 만들었습니다. (단지추출버튼이 있는 1,2행을 없앰)
보이시죠? 셀A1에 단지명이 들어가 있습니다. 여기서 저는 세대(H1셀) 기준으로 틀고정 되도록 하겠습니다.
(H1과 대각선에 있는) i2 셀 클릭-(상단 메뉴 바의)보기-창-틀고정-틀고정
오타 아닙니다. 틀고정 밑에 또 틀고정 메뉴가 있습니다.
네이버부동산 링크 옆으로 여러분이 넣고 싶은 항목이 있다면(예. 전고점, 전저점, 전고점 시기, 전저점 시기 등) 추가하여 정보를 편하게 기입하기 위한 목적으로 틀고정 합니다.
이제 #행/열 숨김 버튼을 생성해볼까요? 행/열 숨김버튼은 행렬이 많아졌을 때, 자유롭게 숨기기/숨김해제 할 수 있는 기능입니다. 버튼 하나로!
숨기고 싶은 행을 선택해보겠습니다. 이번에는 전용(m2), 전용면적을 숨겨보겠습니다.
F, G열 클릭-(상단 메뉴바의) 데이터-개요-그룹
이 경로로 클릭하면, 다음과 같은 숨김버튼이 생깁니다.
현재는 숨기기 해제로 인해 -버튼이지만, 버튼을 클릭하면 +상태로 변경되며 숨김처리가 됩니다.
혹 숨김처리 버튼을 해제하고 싶다면 아까의 메뉴경로에서 그룹해제를 선택하면 됩니다.
F, G열 클릭-(상단 메뉴바의) 데이터-개요-그룹해제
지금까지 푸버리지 파일에서 약간의 편의를 더했다면, 이제부턴 시세지도/시세트래킹에 활용할 수 있는 항목입니다.
4. 아무래도 전고점, 전저점, 그리고 매매가의 흐름을 알기 위해 데이터를 입력한 날짜 등이 필요하겠죠? V열 옆으로 이런 항목을 입력할 수 있습니다. 그런데 셀 w1에 값을 입력하고 엔터를 치면, 셀 w2로 넘어가서 불편합니다.
이럴 때, W1셀을 클릭한 상태에서 Shift+Space (행 선택 단축키)를 누릅니다.
1행이 전체 선택되며 W1만 밝게 흰색으로 보이실 겁니다. 거기서 #값을 입력하고 엔터치면 옆칸인 X1셀로 넘어갑니다. 마찬가지로 네이버 호가 입력시에도 이렇게 행을 선택하고 엔터치면 항상 옆으로 칸이 넘어가서 편리하니, 단축키를 잘 기억해두시면 좋습니다.
[추가 Tip] 세로로 열 선택하고 싶다면 단축키: Ctrl+Space
이제 파일이 다음과 같이 만들어졌습니다.
5. 여기서 한 번 더 고민이 되실 것 같습니다. 데이터 입력일을 매번 숫자, 하이픈 넣어서 입력해야 하나요??
데이터 입력일도 단축키가 있습니다. Ctrl+; 입니다. 두 키를 같이 눌러주면, #오늘 날짜가 한번에 입력됩니다.
6. 시세지도에 올릴 단계를 생각하면, 단지의 #입주연도를 별도로 따두는 과정도 필요할 것 같네요.
예제에서 입주연도가 있는 B열을 인용하는 함수를 씁니다.
B2의 연도를 Z2에 나타나게 하려면, Z2 셀을 클릭한 후
=LEFT(B2, 2)
를 입력합니다. 아래의 캡쳐보시면 수식과 출력값이 보입니다.
어떤 수식을 썼는지 의미를 이해해야 더 기억하기 쉽겠죠?
=왼쪽에서부터 몇 글자 끌어와봐(B2셀의 왼쪽에서부터, 2글자만) 이란 의미입니다.
만약 여러분이 3글자, 4글자로 끌어오고 싶다면,
=left(B2, 3)
=left(B2, 4)
이렇게 적용할 수 있습니다.
[추가 tip] 만약 오른쪽부터 글자를 끌어오고 싶다면?
위의 left자리에 right를 써주면 됩니다. 예를 들면
=right(B2, 2)
가운데부터 글자를 끌어오고 싶다면, mid함수가 있습니다.
7. 시세트래킹에서 #전고점 대비 목표매수%를 설정해야겠죠?
예제파일에 임의로 값을 넣었습니다. 하락률 설정은 (너나위님의 내마중 강의를 인용하여) 마이너스 %로 표현했습니다.)
이제 목표매수%가 자동 설정되도록 해보겠습니다. 목표매수%는 구간을 설정해야 합니다.
예를 들면, 0, 0.1, 0.15, 0.2, 0.25, 0.3 이런 식으로 구간을 설정해야 합니다. 현재의 시세트래킹 시트가 아닌 별도의 시트에 다음과 같은 표를 만듭니다.
LOOK UP 함수를 이용해서 아래의 내용을 적용하려고 위의 표를 만들었습니다.
[A2셀에 있는]0 다음으로 큰 숫자인 [A3셀에 있는]0.1 이 보일 때 까지는 [B2셀에 있는] 0.1로 나타내라.
[A3셀에 있는]0.1 다음으로 큰 숫자인 [A4셀에 있는]0.15 가 보일 때 까지는 [B3셀에 있는] 0.15로 나타내라.
[A4셀에 있는]0.15 다음으로 큰 숫자인 [A5셀에 있는]0.2 가 보일 때 까지는 [B4셀에 있는] 0.2로 나타내라.
[A5셀에 있는]0.2 다음으로 큰 숫자인 [A6셀에 있는]0.25가 보일 때 까지는 [B5셀에 있는] 0.25로 나타내라.
는 의미입니다. 헷갈릴 수 있으니 다른 말로 설명해볼까요?
0이상 0.1미만이면, 0.1출력
0.1이상 0.15미만이면 0.15출력
0.15이상 0.2미만이면 0.2출력
0.2이상 0.25미만이면 0.25 출력입니다.
하락률이 7%라면, 목표매수%는 전고점에서 10%빠진 값이 되어야 하고
하락률이 17%라면, 목표매수%는 전고점에서 20%빠진 값이 되어야 하니까요.
이제 LOOKUP을 위해 표를 작성했으니, 아까의 시세트래킹 시트로 넘어가겠습니다.
AB2셀에 캡쳐와 같은 수식을 입력합니다.
=LOOKUP(ABS(AA2), [아까의 표를 드래그])
입니다.
이 함수의 의미를 해석해볼까요?
=찾아와봐(AA2셀의 절대값을, Sheet3의 A2:A7사이에서 찾아서 B2:B7에 있는 값으로)
이 때, Sheet3의 “Sheet3!A2:B7”에는 $를 걸어주어야 합니다.
왜냐하면 수식을 아래로 드래그할 때, 자동으로 Sheet3!A2:B7의 2와 7이, 3, 8로 4,9로 늘어나니까요.
그래서 수식을 밑으로 옆으로 드래그해도 바뀌지 않도록 고정되게하는 $표시가 필요합니다.
달러 표시는 (수식 안에서) A2 위에 드래그 후, F4키를 한번 눌러줍니다.
(두번, 세번 누르면, 행에만 또는 열에만 걸리도록 변합니다. 네번 누르면, 모든 $가 없어지는 상태로 옵니다.)
아까의 표에 드래그 할 때, $ 표시가 붙어있어야 앞으로 오류없이 값이 반영되니 주의 부탁드립니다.
최종 적용되는 수식은 다음과 같습니다.
=LOOKUP(ABS(AA2), Sheet3!$A$2:$B$7)
8. 이제 #목표매수가를 설정해볼까요?
7번에서 만든 목표매수 %를 기준으로 목표매수가를 계산하게 수식을 걸어줍니다.
여기는 수학과 같습니다. 전고점의 가격에서 20%빠진 가격을 곱해보라는 수식을 걸어야하므로,
=전고점*(1-목표매수%) 입니다.
이제 파일에서 수식을 어떻게 거는지 살펴볼까요?
=X2*(1-AB2)
캡쳐를 보시면, 전고점이 있는 X2셀에 곱하기 (1-목표매수%)
입니다. 셀x2의 80%가격이 되어야 하므로 1-목표매수%입니다.
그럼 전고점인 8억의 80%인 6.4가 계산되어 나오는 모습을 볼 수 있습니다.
아무래도 글로 설명하다보니 한계가 있는 것 같아서 예제파일 첨부합니다.
Sheet 2, Sheet 3는 제가 작업한 파일이며,
연습 시트에 단지가 추출된 처음의 상태가 있으니 연습시트에서 연습해보시면 될 것 같습니다.
그 외에
# (푸버리지에서) 단지의 모든 평형추출 후, abcde타입의 같은 평형 세대수만 모아보기
# 교통, 환경, 학군 등의 등급 매기는 로직
# 시세트래킹 시 입력한 단지만 추출
# 시세지도에 올릴 양식으로 데이터 만들기
이 가능할 것 같습니다. 다음 글은 이 주제로 올려보겠습니다.
좋은 글을 남겨주신 멤버에게 댓글은 큰 힘이 됩니다. 응원 댓글로 감사함을 나눠주세요. 😀
댓글 0
무지개베이글 : 좋은 글 감사합니다!!
바다유리 : 어메리칸느님, 감사합니다~^^ 아직 컴퓨터 익숙하지 않아 바로 적용은 못하고 있으나 일단 저장해두고 앞으로 잘 활용해볼께요~ 그리고 승투 응원합니다~^^