| |
우선, 전반적으로 시트 설계를 좀 고쳤습니다.
1) 기초데이터 시트에서 작성일을 연, 월, 일을 따로 입력하는 것보다
17-12-30 등과 같이 날짜 형식으로 입력하는 것이
여러가지 처리에 더 편리합니다.
특히 연도가 금년일 경우에는 그냥 12-3 만 입력해도
2017년 12월 3일로 인식하여 받아들입니다.
이렇게 날짜를 입력하면 해당 날짜를 년월일로 표시하도록
셀 서식을 적용하였습니다.
- 데이터 유효성검사를 적용하여
셀을 선택하면 날짜 입력 방법에 대한 설명이 나타나고
잘못된 날짜를 입력하면 오류 메시지가 나타나도록
하였습니다.
2) 기초데이터 시트에서 작성일의 연도와 월을 입력하면,
해당월의 만년력이 자동으로 표시되게 하였습니다.
- 이 기능 구현을 위해 보조수식이 필요했기 때문에
기초데이터 시트의 A7:A9, A14, A19, A24, A29, A34 셀 등에
각각 보조수식을 입력했고
이 셀들에는 글꼴 색 하얀색을 적용하여 숨겼습니다.
- 요일별 날짜가 표시되는 노란 음영의 셀들에는
수식과 사용자정의 셀 서식을 적용하여 유효한 날짜만 표시됩니다.
3) 이에 따라, 원본 시트의 52행 이하에 있는
날짜별 매출 집계표 역시
자동으로 1일부터 말일까지의 집계가 나타나도록
수식을 수정하였습니다.
- 역시 이 기능의 효율적 구현을 위해
원본시트의 I열에 보조수식을 사용하였고
I열은 '열 숨기기' 기능으로 감추었습니다.
4) 기초데이터에 거래처별 총매출수량을 누계할 수 있게 해달라고 하셨는데
자료를 살펴본 바 기본메뉴와 기타메뉴의 단가가 다를 수 있기 때문에
단순히 수량 합계로는 별 의미가 없을 것 같습니다.
따라서, '누적수량'을 '이월매출액'과 '누적매출총액'으로
분류하여 표시하도록 하였습니다.
이월매출액은 전월까지의 누적매출총액을 받아온 것이고
누적매출총액은 거기에 이번 달 매출총액을 더한 것입니다.
5) 수식 참조에서 군데 군데
=IF(E35=0,"",E35)와 같은 식을 사용하셨는데
그냥 간단히 =E35 라고 쓰고
셀 서식을 #로 지정하면 값이 0일 때 빈 셀처럼 보입니다.
6) 마찬가지로 =IF(E35="",0,E35)과 같은 형태의 수식도
사용하셨는데, 역시 그냥 =E35 라고만 써도
E35가 빈 셀이면 0이라고 표시됩니다.
7) 원본 시트에 보면 D34:G38 영역에
기타 메뉴를 추가로 쓸 수 있는 칸은 4줄인데
83행~86행 및 L31:P33의 집계 란은 3줄만 설계하셨네요.
역시 4줄을 사용할 수 있도록 수정하였습니다.
==================
주문하신대로 거래처명을 등록하거나 삭제할 때
시트를 추가하거나 삭제하는 기능은
해당 기능을 구현하는 것은 어렵지 않으나
이미 등록한 거래처를 중복 등록하려 하거나
순서대로 밀착 등록하지 않고 중간에 공백을 띄우거나 할 경우
오류가 발생할 수 있기 때문에
그에 대한 대비책이 필요하게 됩니다.
1) 이 기능을 위해 M열을 삽입하여 보조수식을 입력하고
M열은 숨기기로 하였습니다.
(이 데이터는 VBA에서 자동 입력됨)
2) 거래처를 추가하거나 삭제하기 위해 K열을 선택하면
Worksheet_SelectionChange 이벤트를 가로채서
사용자정의 폼이 뜹니다. 이 폼에서 거래처 추가등록이나 삭제를 합니다.
3) 거래처를 추가하려면 위쪽 박스에 거래처명과 기본단가를 입력하고
파란색 버튼을 누릅니다.
자동으로 K열에 거래처 목록이 추가되고
해당 시트가 복사되어 배치됩니다.
주의할 것은 이 때 파일찾기 상자가 나타나는 수가 있는데
[취소]를 눌러야 합니다.
4) 거래처를 삭제하려면 아래쪽 콤보상자에서 삭제할 거래처를 선택하고
빨간색 버튼을 누릅니다.
해당 거래처의 시트를 삭제한다는 안내 창이 나오면 [예]
해당 시트가 열리면서 이 시트를 삭제할 거냐고 물으면 [예]
두 번 다 긍정으로 대답하면 먼저 시트가 삭제되고
거래처 목록에서도 삭제됩니다.
만일 두 번의 안내 창에서 한 개라도 [아니오]를 선택하면
거래처는 삭제되지 않습니다.
5) 거래처 추가에서 이미 목록에 있는 거래처를 추가하겠다고 하면
오류 메시지가 뜹니다.
기본단가만 수정할 거냐고 물었을 때 [예]를 선택하면
해당 거래처의 기본단가가 수정됩니다.
6) 거래처를 추가하거나 삭제함에 따라
N:P열의 통계 수식도 자동으로 복사 생성되거나 삭제됩니다.
==============================
월별로 별도의 파일(워크북)을 관리하고
매월 매출누계가 이월 합산되도록 해달라는 주문에 의해
1) 기초데이터 시트의 C6 셀에 작성일을 수정하면
연도나 월이 바뀌는 경우를 자동으로 감지하여
새로운 파일로 만들어 줍니다.
2) 그러기 위해서 우선 날짜 변경을 위해 C6 셀을 선택할 때
Worksheet_SelectionChange 이벤트를 가로채서
현재의 파일을 저장합니다.
3) 새로운 달의 매출전표를 작성하기 위해서
각 거래처별 달력의 매출현황표를 모두 삭제하고
자동으로 파일명을 바꾸어 저장합니다.
자동으로 명명되는 파일명의 끝부분 4자리 숫자가 연도와 월을 나타냅니다.
예를 들면 '예찬매출관리1712.xlms'는 2017년 12월의 매출전표입니다.
4) 기초데이터 시트의 거래처 목록에 이월매출액 항목을
직전월의 매출관리 파일에서 읽어옵니다.
5) 즉 통합문서를 월마다 하나씩 복사하는 것은
사용자(싸모님)께서 기초데이터 시트의 작성일을 수정하는 것만으로
VBA가 알아서 파일을 관리해 줍니다.
이 때 복사하여 생성되는 새 달의 통합문서에는
당연히 원본 통합문서의 VBA코드가 유전인자처럼 복사되지요. - 마지막 질문의 답변
==========================================
현재 통합문서의 기본 틀
즉, [원본], [기초데이터], [매출합계], [START], [END] 등의
시트 배치 순서나 시트 이름은 바꾸지 않는 것이 좋습니다.
만일 바꾸게 된다면
VBA 코드를 공부해서 수정해야 합니다.
마찬가지로 시트의 구조를 수정하게 되면
역시 오류가 발생할 수 있습니다.
따라서 VBA 코드 안에는
전체 통합문서의 구조(시트 복사, 삭제, 이름변경 등)를
사용자가 바꾸지 못하도록 Workbook.Protect 코드가 들어가 있습니다.
(물론 프로그램이 거래처 시트를 추가하거나 삭제할 때에는
임시로 Unprotect 했다가 다시 Protect함)
마찬가지로 모든 워크시트 역시 Protect되어 있고
달력의 날짜별 매출현황과 비고란만 셀보호를 해제해 두어서
사용자가 입력이 가능하고
그 외의 셀들은 입력이 불가능하게 되어 있습니다.
물론 셀의 삽입이나 삭제도 불가
=================================
그렇다고 시트보호나 통합문서 보호에 암호를 걸어놓은 것은 아니므로
앞에 설명한 것처럼 시트의 숨겨진 열에 보조수식을 분석하여 공부하시려면
검토 메뉴의 시트보호해제를 하시면 됩니다.
통합문서 보호 역시 파일 - 정보 메뉴의 통합문서 보호에서 해제하거나 보호할 수 있습니다. |
|
엑셀에 대한 질문과
답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
