1976s

이번 2023년이 시작하여 2022년에 사용한 재고관리 파일을 정리하고 2023년에는 2022년에 사용하면서 미흡하거나 추가되었으면 하는 부분을 합쳐서 새롭게 만들기로 했습니다. 이 비닐 판매업을 시작한 지는 어느덧 2년이 넘어가는데 파일 변경은 7번째네요. 처음보다는 많은 부분이 좋아지고 변경되었지만 이번에는 사용자 편의를 더 많이 추가하였습니다.

v6(왼쪽), v7(light:오른쪽) bete

 

v7버전

v7 최종 버전

위의 사진은 입력 폼인으로 보시는 봐와 같이 검은 모양(Dark Thema)의 모습으로 만들었습니다.

 

보시기에는 복잡한 것 같지만 하나하나 보면 쉽고 많은 것을 넣었습니다. 지금까지 입력하면서 귀찮고 간단한 부분을 중복, 입력해야 하는 것과 숫자 한글의 입력 시 흐름이 끝나는 부분을 고려하여 v7버전에서는 이점 개선하였습니다.

밑에 개선된 부분을 간략히 적어 보겠습니다.

 

전체화면에서 입력폼 시작 화면(왼쪽), 분활화면에서 입력폼 시작 화면(오른쪽)

1920 x 1080 화면에서 분할 작업 시 사이즈에 맞게 만들었습니다. (작업에 있어 분할 화면을 많이 사용해서 여기에 맞게 제작하였습니다. )

 

v7버전에 새로운 기능 - 간략 설명

 

1. '주문번호'는 '쿠팡'과 '스마트스토어'로 나누며 '스마트스토어'는 날짜가 주문번호에 있어 그 날짜를 '구매일(주문일)'에 넣을 수 있습니다.(구매일 옆에 참고 날짜가 표시됩니다.) 물론 수동으로 기입도 되고 주문일 위의 버튼을 눌러 자동으로 기입도 됩니다. '쿠팡'이면 버튼 클릭 시 구매일 오늘 날짜가 기입됩니다.
2. '주문번호'를 입력 후 'x'버튼이 나오면 중복으로 'X' 버튼을 클릭시 리셋되기도 합니다.
3. '주문번호'의 위 버튼은 '쿠팡'등에서 묶음 주문 시 사용하는 버튼(스마트스토어는 고유번호가 다릅니다,)으로 '쿠팡'의 주문번호 입력하면 그전에 입력된 부분을 검색하여 같으면  그 정보를 불러와 각각의 입력란에 자동으로 기입됩니다.
4. '제품번호'의 버튼은 제작 시 등록하는 버튼으로 제작(999)로 입력되며 판매채널 역시 제작으로 표시됩니다.(물론 수동으로 압력해도 됩니다.). '제작'시에는 기존의 제품이 아니라서 '추가'란을 만들어 결제 금액에서 추가 금액을 빼는 원리입니다.
5. '박스가격'란은 v7버전부터 있는 입력란으로 박스 포장 시 어떤 박스를 사용하느냐에 따라 더하게 되는 가격입니다. 추가 옆 박스 가격과 함께 '더하기 할당 연산자'로 입력됩니다.
6. '수량'에서 '묶음'버튼은 단으로 판매되는 제품을 계산하려는 버튼입니다. '묶음'버튼 눌려졌을 때 옆 참고사항란 옆에 '묶음'이라는 아이콘이 생깁니다.
7. 검색 입력란 옆의 코드부분(ptDB46)은 각 회사만의 시트와 버전을 표시한 란입니다. 이 표시 버전 시트에 값을 읽어 계산됩니다.
8. 배송비 부분의 밑에 금액은 참고용이기도 하며 버튼이기도 합니다. 배송비 입력 폼에 수동으로 입력해도 되며 밑의 금액을 눌러 입력해도 됩니다. 잘 사용하지 않는 부분의 금액은 잘 잃어버리는 관계로 참고 용의 목적도 있으며 6번의 라디오 버튼에서 수동 입력 부분이 에러가 많아 수동이면서 금액 버튼으로 대체하였습니다.
9. 입력 폼의 중앙에 위치한 리스트 박스를 클릭하면 수정 모드로 변경되며 수정 모드에서 '계산하기' 버튼을 눌렸을 때에는 '수정 모드(계산됨)'으로 변경됩니다. 직관적으로 가시성이 높은 색을 사용하여 구분이 쉽게 하였습니다.
10. 구매자연락처 옆 '비닐 스토리'와 '비닐 팩토리'가 표시되어 이 입력 폼에서 어느 회사 부분의 입력인지 알 수 있도록 하였습니다. 물론 상단의 버튼도 있지만 입력 시 잘 보이지가 않아 중앙에 잘 볼 수 있도록 참고 이미지를 넣었습니다.
11. '고객정보검사/적용' 버튼 옆에 사각형은 재구매 고객으로 이 사각형이 나타나면 '고객정보검사/적용' 버튼을 재구매 고객의 정보가 각 입력란에 자동 입력 됩니다.
12. '고객 정보 검사/적용'버튼의 옆에 박스 안 숫자는 지금 입력하는 사람의 중복 개수를 표시한 것입니다.('재구매'죠)
13. '배송시작일'과 '배송완료일'의 위 버튼은 날짜기입을 도와주는 버튼으로 오늘과 1일, 2일, 3일 후를 기입됩니다.
14. 계산결과로 잘못된 정보를 쉽게 알수 있게 하였습니다.

---

 

v7 상세 설명

1. 구매일

1번의 설명으로 ①번의 주문번호는 스마트스토어(주문 날짜가 주문번호에 있습니다.) 번호를 이용하여 주문 날짜를 표시하고 버튼(④) 클릭 시 자동으로 입력되게 했습니다. 나머지 쇼핑몰은 송장번호와 관계없이 버튼(④) 클릭 시 오늘 날짜가 입력되도록 하였습니다.

 

2,3. 주문번호검사 및 대입

2,3번의 설명으로는 주문번호를 입력하면 중복인지 검사가 되며 중복일 경우에는 'X'(①) 버튼이 생기면서 중복인 것을 알려 줍니다. 스마트스토어는 각각의 고유번호로 되어 있으며 쿠팡은 묶음 주문 시 같은 주문번호가 되어 있습니다. 이때 한 번 더 입력이 귀찮아서 주문번호 위의 버튼(②)을 만들어 클릭 시 각각의 정보를 각 입력란에 입력됩니다.

'OverLap'(②) 버튼 클릭 시 입력되는 부분으로는 판매채널(a), 구매일, 구매자명, 구매자 ID, 구매자 전화번호, 수취인, 수취인 전화번호 1, 수취인 전화번호 2, 배송지, 메모 가 입력됩니다.

 

4. 제작

 

4번의 설명은 상품번호 위의 버튼(ⓐ)를 클릭 시 '상품번호(b)'가 '999'로 입력되며 '판매채널'도 '제작'으로 변경됩니다. 이때 '추가(k)'란 은 판매금액에서 차감되는 금액입니다. (제작은 시트에 없는 것으로 실제 판매금액을 시트 단위로는 계산이 되지 않아 추가로 만들었습니다. '총판매금액 - (추가+배송+기타)' 이런 식을 계산됩니다.)

위의 사진에서 보면
(결제금액 + 배송비) - (업체 배송비 + 박스 가격 + 추가[만드는 금액]) = 순이익

610,000+3000 - 613,000

2900+3300+590,000 = 596,200

613,000 - 596,200 = 16,800

 

이 됩니다.

 

5. 박스가격 - 더하기 할당 연산자로 하기

 

5번 설명은 위의 사진에서와 같이 저희 회사의 박스의 가격을 미리 적어두었습니다. 그 버튼을 눌렸을 시 박스 가격에서 눌렸는 박스 금액이 더해지도록 하였습니다. 하나의 품목을 박스 2개도 사용할 수 있고, 3개도 사용할 때도 있어서 계산기로 한 작업 이제 여기서도 할 수 있게 하였습니다.

예로) 3300 에서 '중(1650)'을 눌렸을 시 '박스가격(m)에 4950 이 적혀집니다.

x = 3300

y = 1650

x + y = x 이런 식입니다.

 

6. '단'가격과 '권'가격을 구분

 

왼쪽: 기본 계산부분, 오른쪽: 묶음(단)으로계산한다는 부분

 

비닐에서는 1단이 보통 10권으로 이루어져 있습니다.

즉) 1단 = 10권

 

6번 설명은 인터넷에서는 1권 가격으로 보통 판매를 하고 있어 계산 자체가 권으로 이루어져 있습니다. 그렇지만 최근에 몇몇 품목은 단으로 판매를 시작하여 계산이 틀려질 수 있어 버튼을 추가하여 버튼 눌렀을 시 '단'으로 계산되도록 하였습니다. 물론, 취소도 가능합니다.

 

7. 시트 버전

 

7번 설명으로 계산의 기준이 되는 시트를 선택하는 부분입니다. 회사가 2개인 관계로 각 회사마다 가격의 기준이 다르며 분기 또는 년마다 변경되는 가격에 매번 다르게 입력되는 것을' 시트 버전 & 번호'로 관리가 됩니다. 물론 시트가 변경되면 기존 입력된 부분이 모두 변경되는 예전 버전과 달리 미리 계산된 값만 저장되는 방식이라 시트 변경 후에 값의 변동이 없이 적용됩니다. 또는 변경된 부분을 다른 시트에 넣고 시트 버전을 달리할 수도 있습니다. (중요 ★★)

 

8. 배송비, 업체배송비

v6 배송비 입력부분(라디오 버튼)

v7 배송비 입력부분 (반자동 부분)

8번의 설명은 '배송비', '업체 배송비'로 v6버전에서는 라디오 버튼으로 제작하였습니다.(위 사진 참조) 하지만 배송비가 박스에서도 설명했듯이 1박스일 수도 있고 2박스, 3박스일 수도 있습니다. 각각의 배송비가 추가되지만 예전 라디오 버튼으로 했을 시 입력하기가 쉽지 않았습니다.(물론 기타로 배송비 부분을 수동으로 적을 수 있게도 하였지만) 예전 버전(v6) 보다는 작업에 흐름을 깨지지 않게 작업하는 것이 중요하여 수동 입력으로 변경했습니다.(탭탭탭 버튼) 그 후 밑에 금액을 클릭 시 해당 입력란에 대입하도록 하여 더 쉽게 할 수 있게 만들었습니다.(참고용으로도 사용가능)

즉,

1. 수동 입력입니다.
2. 밑의 가격 부분 클릭 시 해당 입력란에 해당 숫자가 대입됩니다.

입력하고 탭(Tab)으로 넘기는 흐름이 깨지 않게 만들었습니다.

 

9. 가시성(입력모드, 수정모드, 수정계산 후 모드)

9번의 설명으로는 보통 입력 모드, 수정 모드, 수정(계산 후 모드) 이렇게 표시되도록 하였습니다. 기존 입력에서 '수정 모드'인지 '입력 모드'인지 몰라 또는 쉽게 어느 모드인지 보이지가 않았던 문제를 가시성 좋게 만들었습니다.

즉,

• 없으면 -  입력모드
• 노랑줄 -  수정모드
• 붉은 줄 -  수정모드(수정함) - 수정모드에서 계산버튼릉 눌렸을 때

 

10. 회사구분 가시성 좋게

10번 설명은 구매자연락처 옆 '비닐 스토리'와 '비닐 팩토리'가 표시되어 이 입력 폼에서 어느 회사 부분의 입력인지 알 수 있도록 하였습니다. 물론 상단의 버튼도 있지만 입력 시 잘 보이지가 않아 중앙에 잘 볼 수 있도록 참고 이미지를 넣었습니다.

 

11. 재구매

11번 설명은, 위 사진에서의 사각형만 있는 부분은 재구매이면 나타나는 부분입니다. 이때 '고객정보검사/적용' 버튼을 클릭하면 해당 구매자의 정보를 가져와 해당란에 넣습니다. 이때 구매자 정보가 있는 시트를 읽어 드립니다.

 

12. 몇 번째 재구매

12번 설명은 '고객정보검사/적용' 버튼 옆에 있는 숫자는 이 고객이 과거 몇 번인가 구매를 하였는지 보이는 숫자입니다. 즉 단골인지 알 수 있는 부분입니다.

 

13. 배송시작일/완료일 날짜 기입

13번 설명으로는 배송 시작이 이 입력 폼에 기입할 날이기 때문에 보통 오늘의 날짜를 기입합니다. 그것을 쉽게 하기 위한 버튼과 '배송 종료일'의 버튼은 시작일에서 1~3일 후를 입력됩니다. (지금까지 경험을 보니 보통 1일 걸리고 늦으면 2일 정도 거리더군요) 물론 수동으로 입력도 가능합니다.

 

14. 계산 결과

녹색(이익생김), 붉은색(손해)

14번 설명으로 이 기능은 계산 후 결과 값을 표시하는 것입니다. 쇼핑몰 3~4개 이상 관리하면서 상품 또한 하나의 쇼핑몰의 제품이 40~100개 정도 되니 하루에 10개씩만 해도 한두 달은 쉽게 걸립니다. 그것을 관리가 쉽지 않아 주문 왔을 때 이것이 제대로 되어 있는지 알 수 있는 방법으로 계산 후 어디가 수정해야 하는지 알려주는 부분입니다.

참고로 '스마트스토어'랑 쿠팡, 위메프, G마켓, 옥션 등 각각의 수수료가 달라 순이익이 얼마 나는지 알 수도 있습니다.
또한 프로모션이나 광고 등의 가격 조정에서 쉽게 알 수 있는 부분이기도 합니다.

계산 완료 시 '아이콘'이 생겨 버그나 잘못 저장되는 부분을 방지하였습니다.(나름 중요 ★)

 

계산 저장 시트

저장시트는 수식으로 입력되는 부분이 아닌 값만 들어가는 시트입니다. 예전에 만들었는 엑셀은 수식으로 되어 있어 기본 시트가 변경되면 모든 값이 변경되어 낭패가 되곤 했습니다. 이 점을 방지하고자 값만을 저장하게 만들었습니다.

 

위의 사진에서와 같이 왼쪽에서는 VBA로 계산된 값을 저장되며 오른쪽에서는 수식으로 계산되게 하였습니다.

하지만 이 또한 불안하기도 하고 프로그램상 오류도 나곤 해서 중복 검사하도록 하였습니다.

쿠팡 수수료 식입니다. 예를 들어,

<판매 수수료>	<배송비>
{(상품단가*개별 상품 수수료)의 반올림값*구매수량}*VAT + 배송비 수수료(배송비*3.3%)	(배송비*3.3%)의 반올림값
ex) 상품가 1,700원 25개 구매, 유료배송비 2,500원, 판매수수료 10.8% 주문건인 경우 ={(1,700*0.108)의 반올림값*25}*1.1+(2,500*0.033) =(184*25)*1.1+82.5 =5060+82.5 =5142.5 =5143	ex) 배송비 2,500원 =(2,500*0.03)의 반올림+Vat반올림 =75.0+(75*0.1)의 반올림 =75+8 =83

그 후 검사열을 추가하였습니다.

if(A=A,B=B,C=C...,"","NO")

 

또한 누계도 넣어 시트만 보고도 몇 번째 구매자인지 알 수 있습니다. (물론 같은 묶은 구매도 1개로 치부됩니다. 이 방식은 날짜를 사용합니다.) 

IF, COUNTIFS문 사용

 

판매자 전용 메모(추가)

입력 폼에 '판매자 전용 메모'란을 만들어 이 구매자의 메모를 적을 수 있게 만들었습니다. 저처럼 블랙리스트의 사람을 구분할 수 있고 좋은 사람이나 박스 운영, 반품 등 많은 부분을 적을 수 있는 부분입니다.

 

구매자 정보 시트,

구매자의 정보에 따라 재구매 시트 또한 저장됩니다. '누게'란에...

 

대시보드

대시보드로 처음에는 많은 것을 넣었습니다. 하지만 결국 구매자 찾기와 총판매금액, 거래처 지급금액, 순수익, 판매량 정도만 남았네요. 중앙에 판매량 정도 차트가 남고 사이드에 월 판매 정도 배치했습니다

전체 색상을 '블랙 & 화이트'로 만들었고 월 단위로 저장되도록 하였습니다.

 

전체 흐름

전체적으로 '입력 > 저장 > 월단위 저장 > 정렬 > 필터' 이렇게 작동됩니다.

 

 

마무리

이번 버전은 2년 넘게 사용하면서 이런 부분이 있었으면 하는 부분을 넣고 만들어본 엑셀 파일입니다. 노하우까지는 아니지만 사용해 보면서 불편한 것은 최대한 많은 회의 후 만들었습니다. 좀 더 나은 저 좀 더 좋은으로 가다 보니 기타 등등이 많이 붙었네요.

VBA 말고도 만들어 보려고도 했지만 결국에는 VBA로 만들었네요. 파이썬이나 WEB으로도 생각은 했지만 일이 너무 많아 접었습니다.

 

 

reference

 

엑셀 출고 관리 '비닐 v7' 추가

---