요즘 손해평가사 활동을 하면서 한 동안 등한시 했던 엑셀을 다시 활용하기 시작했다.

현장 조사 후 계약자에게 조사결과를 설명해야 하는데, 그 결과값을 엑셀을 활용하여 산출하게 된다.  물론 관련 엑셀파일은 농재협에서 대부분 제공하여 준다.

하지만 농재협이 제공한 엑셀파일은 현장에서 사용하기에 약간 부족한 면이 있어 약간 수정할 필요가 있다.

오늘 그 일환으로 인터넷 검색을 통해서 얻은 정보를 내 입맛에 맞게 각색해 정리한다.

특정 셀의 값을 시트 이름으로 자동 설정되도록 하는 방법을 인터넷에서 찾았다.

VBA 코드는 다음과 같다.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ActiveSheet.Name = ActiveSheet.Range("A3").Value
End Sub

위 코드를 엑셀 비쥬얼 베이직 에디터('Alt + F11'키를 동시 누르면 됨)에 입력하는 방법은 아래 그림을 대체한다.

코드 입력 후 엑셀파일을 저장시 파일형식을 아래처럼 'Excel 매크로 사용 통합 문서'로 해야 한다.

이렇게 만든 엑셀 파일이 제대로 작동하는지 확인해 보자.

위 사진은 A3셀에 '홍길동'을 입력한 상태로서 엔터키를 누르기 전의 모습이다.

엔터키를 누르면~

위 사진에서 보는 것 처럼 Sheet1의 이름이 A3셀 값인 '홍길동'으로 변했다.

만약 A3셀이 아닌 다른 셀의 값을 시트이름으로 하고 싶다면, VBA코드의 값을 변경하면 된다.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ActiveSheet.Name = ActiveSheet.Range("A3").Value
End Sub

아파트같은 공동주택은 각 세대별 개별개량기가 설치되어있다.

 상하수도요금 많이 나오든 적게 나오든 요금계산에 대한 오해가 없다. 그러나 일부 빌라 등의 공동주택(이하 '빌라'로 함)의 경우, 개별개량기가 설치되어 있지 않은 곳이 있다. 이런 경우 상하수도요금이 빌라 한동 전체 앞으로 고지된다. 그 고지된 금액을 바탕으로 각 세대별 요금을 배분한다. 말이 쉽지 이웃간 다툼이 종종 일어난다.

아래 그림의 사례를 설명해 보자면,

A계량기는 시군구 상하수도관리사업소에 설치한 공인된 계량기이다. 매월 검침 역시 공무원이 나와서 실시한다.

반면 B계량기는 시군구 상하수도관리사업소가 설치한 계량기가 아니라 건설업체, 개인 혹은 설비업체가 설치한 간이계량기로서 흔히 '고메다'라고 부른다. 따라서 시군구 상하수도관리사업소에서 이 계량기를 인정하지 않는다. 즉 이 계량기로 상하수도요금을 메기지 않는다는 것이다.

B계량기의 검침은 대개 '반장', '총무'가 매월 측정한다.  본 계량의 용도는 매월 검침량을 바탕으로 '상하수도요금 고지금액'을 각 세대별로 배분할 때 사용된다. 어떤 곳은 B계량기가 없는 빌라도 많다. 이럴 경우 상하수도요금 고지금액을 각 세대의 거주자 수로 그냥 배분하기도 한다.

상황이 이렇기 때문에 요금으로 인한 분란이 다소 있다.

이런 문제를 해결하는 방법으로 B의 수도계량기를 시군구 공인 계량기로 교체하는 것이다. 즉 상하수도관리사업소에 허가를 받으면 된다. 허가 요건사항은 시군구 조례를 보면 자세히 설명하고 있다.

주 요건사항으로는 계량기가 현관밖 복도에 설치되어야 하며, 계량기가 바닥과의 수평 유지 등이 있다. 어떤 빌라는 계량기가 화장실 안에 설치 된 곳이 있던데 이런 곳은 허가를 받기 힘들다. 요건을 충족할려면 비용이 많이 투입될 것이다.

지인의 부탁으로 빌라의 상하수도요금계산을 쉽게 할 수 있는 엑셀파일을 만들어 봤다.

빌라의 상수도시스템이 제각각(위에서 설명한 것 처럼)이기에 모든 상황을 반영할 수 없기에 아래 그림과 같은 상수도시스템에 적합하도록 엑셀파일을 만들었다.

요점

A계량기 : 시군구 공인된 상수도계량기로서 공무원이 매월 정해진 날짜에 검침한다. 기간 사용량을 바탕으로 상하수도요금을 산정하여 고지한다.

B계량기 : 시군구 비공인 상수도계량기로서 반장(총무)이 매월 정해진 날짜에 검침한다. 상하수도고지금액을 배분할 때 필요한 계량기다. 

이하 엑셀파일에 대한 설명이다.

상하수도요금은 자치단체별로 상이하므로 단계별 요금은 '상하수도요금고지서'의 뒷면을 참조하여 엑셀파일 '요율'시트에 내용을 넣어야 한다. 

참고로 노랑색 바탕부분만 입력하면 된다. 나머지는 자동 계산된다. 또한 '기본요금'은 없다는 조건으로 만들었다. 

엑셀파일 '월별 납부' 시트 역시 노랑색 부분만 입력하면 된다. 다만 '구경크기'는 한번만 입력해 놓으면 그만이다.

'자체확인' 구역의 각 호수별 전월 & 당월 '지침'은 위에서 설명한 B계량기의 검침량이다.

'상하수도 고지서  上' 의 전월 & 당월 '지침'은 상하수도관리사업소 공무원이 검침한 숫자로서, 그 검침량은 고지서에 나오니 그것을 입력하면 된다.

'자체확인'된 사용량 합계와 '상하수도 고지서 上'의 사용량 합계가 다를 수 있다.

반장의 검침일과 공무원의 검침일 다른 경우, 혹은 빌라 누수 등의 이유로 사용량 합계가 다른 경우가 있다. 사용량이 다르므로 요금도 차이가 발생하는데 이를 대비하여 '차액조정액'으로 배분하였다.

말로 설명하는 것보다 엑셀파일에 자세히 설명해 놓았기 때문에 이해하기 쉬울 것이다.

가급적 직관적으로 사용할 수 있도록 만들려고 노력했다.

상하수도요금계산 엑셀파일

약간 조잡한 느낌이 있으나, 혹시    본 파일을 필요로 하시는 분은 비밀댓글에 이메일 주소를 남기면 가급적 신속히 보내고자     한다.

레이의 VBA정복기9- 변수란 무엇인가1

[ 용어정리 ]

Dim : '선언하다'라는 의미로서 변수 등을 지정할 때 본 용어를 사용한다.

변수 : 아래 예문에서 나오는 'x,y,k'가 변수에 해당된다.

예를 들면 '1 + 2 =3'이라는 수식에서 1~3은 변수로서 말 그대로 변하는 숫자다.

As : 영어 단어로서 생각해 보면 '~~~로써'이다.

'x As Integer'를 그대로 해석해보면 '변수 x 는 정수로써'이다.

편의상 '~~~이다'로 이해하면 된다. 즉 '변수 x는 정수이다'로 이해하면 된다.

'

 Dim x As Integer 

위 내용을 바탕으로 Dim x As Integer를 해석하면, '변수 x를 정수로 선언하다'로 보면 된다.

예문1.

예문2.

위 두 예문의 차이점은 변수의 종류를 예문1에서는  Integer를, 예문2에서는 Double로 사용했다는 것이다.

여기서는 변수의 종류에 대해서 간단히 정리한다.

[ 변수의 종류 ]

 Integer 

정수형 데이터 형식으로서, '-32,768 ~ 32,767' 사이의 정수형 데이터가 사용범위이다.

소수점이 있는 데이터는 사용할 수 없다.

 Long 

역시 정수형 데이터 형식으로서, '-2,147,483,648 ~ 2,147,483,647' 사이의 정수형 데이터가 사용범위이다.

Integer와 마찬가지로 소수점이 있는 데이터는 사용할 수 없다.

 Single 

실수형 데이터 형식으로서, 비교적 작은 숫자를 사용할 수 있다(정확한 범위는 잘 모르겠다).

Integer와 Long변수에서는 사용할 수 없는 소수점이 있는 데이터를 사용할 수 있다.

 Double 

실수형 데이터 형식으로서, 큰 숫자를 사용할 수 있다.

소수점이 있는 데이터를 사용할 수 있는데 Single보다 더 큰 숫자가 들어갈 수 있다.

 String 

문자열 데이터 형식을 변수일 때 사용할 수 있다.

 Variant 

가변 데이터 형식으로서 정수형, 소수점이 있는 실수형, 문자형 데이터를 저장할 수 있다. 

위 변수 이외에는 몇가지가 더 있으나 이것들은 패스한다.

엑셀 함수 중에서 가장 유용한 함수가 Vlookup함수 아닐까 싶다.

그렇다면 Vlookup기능을 엑셀VBA에서도 구현 가능할까.....?

당연히 가능하다.

앞으로 엑셀VBA를 다시 배워볼 요량이기에 이 글은 메모 차원에서 발행한다.

위 조견표를 기준으로 해서 D3과 D4셀의 입력된 값에 따라 E3,E4에 해당 되는 값을 연결해 주는 함수가 Vlookup이다.

이를 VBA로 코딩하면 아래와 같이 나온다.

각각의 의미는 다른 예제를 통해서 서서히 익혀 나가보자.

지난 일요일은 돌아가신 아버님의 기제사가 있는 날이였다.

항상 그랬지만 제사에 필요한 지방과 축문 준비는 작은 할아버님께서 맡아 하셨다.

조상에 대한 예의를 표하는 일에는 기력이 약해졌다는 이유로, 사리판단력이 흐려졌다는 이유로 아랫사람에게 맡기시 않는 그런 분이시다.

조카의 기일에도 작은 할아버지에게 이런 준비를 맡기는 것은 차마 볼 수 없는 장면 중의 하나였다.

그렇다고 내가 간지달력을 판단할 수 없으니........기제사 며칠 전부터 인터넷 검색으로 지방과 축문 작성하는 방법을 찾아 보았다.

올해는 반드시 내가 직접 하리라는 마음으로!!!!

지방 쓰는 방법은 아주 쉬웠다.

그러나 축문은?

올해 연도 간지는 한번의 검색으로 알 수 있는데 월일의 간지는 그렇지 못했다.

한국천문연구원(국립기관)에서 월일의 간지를 조사해 봤다.

이 웹사이트는 양력일자에 해당하는 음력일자를 자동으로 정보를 제공해 준다.

한국천문연구원에서 제공하는 음력양력대조표

나는 위의 자료에서는 나오는 '음력간지'를 축문에 적용하면 되는 것으로 알았는데,

그러나 축문에 적용할 때에는 위의 음력간지를 그대로 적용하면 안된다는 것을 이내 알게 되었다.

즉 양력 2015년 11월 1일(음력 2015년 9월 20일)에 제사가 있는 경우,

음력 2015년 9월 20일을 나타내는 '을미년 병술월 신사일'이라는 간지를 그대로 사용해서 안된다는 것이다.

'을미년'과 '신사일'은 사용 가능하나 '병술월'은 사용하면 안된다.

축문의 글자 중에서 변하지 않는 글자들이 있는데 그 중 하나가 "삭(朔)"이다.

글자 "삭(朔)"은 '초하루'를 의미한다.

따라서 음력 9월 1일에 해당하는 일(日)간지를 사용해야 하는 것이다.

이를 공식화 하면 다음과 같다.

"해당 음력 연도 간지 + 해당 음력월의 초하루 일간지 + 해당 음력의 일간지"

음력 2015년 9월 초하루(1일)의 일간지는 임술(任戌)이 되는 것이므로,양력 2015년 11월 1일(음력 2015년 9월 20일)에 제사지낸다고 하면

"을미(乙未)년 임술(任戌)월 신사(辛巳)일"

이라고 해야한다.

그렇다면 왜 이렇게 복잡하게 만들었을까????

그것은 우리 조상이 제사에 대한 지극 정성 때문이다.

옛말에 '그 달에 제사가 있는다면 다른 집 초상도 가지 않는다.'고 할 만큼 몸과 마음을 정갈하게 유지한다는 의미이다.

우리나라 제사문화가 허례허식이 심하고 익히기 쉽지 않은게 사실이다.

그러나 나의 세대까지는 부모님과 조상님에게 배운 것처럼 따르고 이어나가고 싶다.

후세대들이 이런 문화를 따르느냐 마느냐는 그들의 판단에 맡길 뿐이다.

어쨋든 축문을 어떻게 하면 어렵지 않고 간단하게 작성할 수 없을까 생각하면서 여러 자료를 뒤적이다.

허접하게 엑셀프로그램을 만들었다. 물론 완전 처음부터 끝까지 나의 온전한 노력의 결과물이 아니다.

내가 어떤 파일을 모티브로 해서 잘못된 부분을 수정 보완했는지는 2~3일내에 공개할 예정이다.

허접이를 열기할 때는 반드시 매크로가 작동되도록 해야 한다.

그림1

. 첨부파일을 다운로드한 후 엑셀프로그램에서 파일을 연다.

다음은 위 그림1처럼 옵션버튼을 눌러준다.

그림2

2. 그러면 그림2처럼 1~2번 처럼 선택 혹은 클릭하면 정상적으로 파일이 작동될 준비는  다 된 것이다.

3. '입력'시트의 노랑색 부분에 데이타를 입력한 후 프린트를 하면 된다.

축문과 지방이 동시에 자동 형성된다.

혹시나 첨부파일의 내용을 수정하고자 하신다면, 비밀번호가 '1234'이오니 참고하시기 바랍니다.

2016년 3월 6일 기준으로 아래의 수정된 파일(지방 축문 VBA Rev 160306.xls)을 사용하시기 바랍니다.

지나가는 행인님께서 남겨주신 댓글을 통해 기존 파일에 오류가 있음 알게 되어 수정한 파일 입니다.

죄송하다는 말씀을 올립니다. 앞으로는 좀 더 신중하게 처리토록 하겠습니다.

[ 수정 내용 ]

- 증조부모 합동제사 : 기일날짜 바로 잡음

- 증조부 : 기일날짜 바로 잡음

- 조부모 합동제사 : 기일날짜 바로 잡음

- 조부 : 기일날짜 바로 잡음

2018년 2월 1일 기준으로 수정된 파일(지방 축문 VBA Rev 180201.xls)을 사용하시기 바랍니다.

댓글을 남겨주신 '손'님과 '태원'님 덕분에 오류를 알게 되어 수정했습니다.

지방 축문 VBA Rev 180201.xls

티스토리 블로그 운영한 지 5년째 입니다.

그 동안 수 많은 글을 작성하면서, 많은 것들을 얻고 삶의 흔적을 기록하고 저장하면서 간간히 지난 글들을 읽는 재미도 즐깁니다. 나의 행복론에서 언급했듯이 블로그는 이미 제 생활의 중요한 자리를 차지하는 몇 안되는 취미(?)로 정착했습니다.

요 근래 블로깅하면서, 블로그의 색깔이 많이 변했다는 느낌을 받았습니다.

예전에는 비중이 높지 않았던 여행관련 글이 상당히 증가하는 것을 발견하면서, 5년간의 블로그 역사 속에 글감의 주제 변화를 알아보고 싶었습니다.

간단한 엑셀 작업을 통해서 나타난 블로그 포스트 주제를 한 눈으로 파악해 봤습니다.

경영...시사에서 여행, 자연, 먹거리로 변화....

2008년에서 2009년에는 경영, 독서, 시사 분야 글이 대다수를 차지 했으나, 작년부터 대부분 여행 분야 글이 상당한 비중을 차지하고 있습니다.

이러한 변화의 원인, 당시의 상황, 의미를 시간을 좀 더 두고 차분히 정리해 볼 필요가 있을 듯 합니다.