[Excel] 6. 엑셀로 능력있는 직장인이 되는 법. 재고(자산) 관리용 현황판 만들기!

안녕하세요! @gbgg 입니다. 능력있는 직장인이 되는법! 엑셀 자동화 강의 여섯번째 시간입니다!

지난 강의에서 강의 내용에 대해 피드백 많이 주셨더라구요! 우선 감사의 말씀 드립니다. 꾸벅

이번 강의까지는 생각해둔 것이 있어서 진행하고 엑셀에서 함수 다루는 부분부터 업무용에 맞게

진행하려고 합니다. 기본 함수부터 고급함수까지 다뤄보고 VBA로 넘어가는게 맞을것 같아요!

오늘은 [Excel] 4. 엑셀로 능력있는 직장인이 되는 법. 간편한 UI 만들기!

에서 진행했던 부분을 활용하여 진행해보겠습니다.

오늘은 매크로 기능 보다는 저장되어있는 재고 DB를 가지고 일종의 장부?를 작성할것이니

데이터 시트만 따라하시면 됩니다!

오늘 재고 데이터를 가지고 구할 통계들은 다음과 같습니다.

• 전일재고
• 입고(당일입고, 월간누계)
• 출고(당일출고, 월간누계)
• 금일재고

쌓인 데이터를 통해 날짜만 바꾸면 해당 요일에 맞는 재고상황을 보여주도록 구현해보려고 합니다.

UI를 통해 입출고시킨 데이터들을 날짜만 바꿔 현황을 볼 수 있는 시트를 만들어보겠습니다.

오늘 강의는 엑셀 함수부분이 주를 이루기 때문에 비교적 간단(?)할것 같습니다.

---

기본 셋팅

이러한 형태의 데이터 베이스를 기준으로 셋팅합니다.

입출고시간은 연도와 월, 일로 되어있으며 물품종류는 자유입니다.

오늘은 입/출고 만 구현할 것이기 때문에 D열에는 입고, 출고 자료만 입력합니다.

마지막으로 재고관리용이기 때문에 수량을 입력합니다.

(코인관리나 가계부 등으로 사용하실 목적이라면 수량 대신 금액으로, 물품 종류를 코인 종류로, 입고는 매수 출고는 매도 이런식으로 하셔도 됩니다.)

자 현황판을 디자인 해 보겠습니다. 물품은 A부터 C 시리즈까지 총 3개의 물품으로 구성해보겠습니다.

어떤 현황을 표시할 것인지 얼추 감이 오시나요?

현재 재고상황과 금일 어떤 입출고건이 있었는지, 전일 재고는 몇개인지를 한 눈에 표시합니다.

자 이제 차근차근 완성시켜보겠습니다!

날짜 조절버튼

이왕 자동화 하는거 날짜도 버튼으로 바뀌게하면 좋겠죠?

개발도구 - 삽입 - 버튼을 사용하여 두 개의 버튼을 만들어봅시다.

▲ ▼ 를 사용하여 만들어보았습니다.

이제 버튼에 능력을 부여하러 가보겠습니다. 코드보기 를 클릭합니다.

모듈을 생성합니다. 하나의 모듈에 여러가지 명령을 작성할 수 있습니다.

생성한 모듈에 다음 코드를 붙여넣으시면 됩니다.

Sub 증가()

Sheets("현황판").Range("A1") = Sheets("현황판").Range("A1").Value + 1

End Sub

Sub 감소()

Sheets("현황판").Range("A1") = Sheets("현황판").Range("A1").Value - 1

End Sub

두 개의 명령을 추가하였습니다. 하나는 증가(+1) 하나는 감소(-1) 입니다.

현재 날짜 값이 들어가 있는 셀 A1은 정수 값이기 때문에 Value로 호출하여 증감시켜주면 됩니다.

자 여기까지 완성하셨나요? 결과 확인은 최종적으로 현황판을 살펴볼 때 다시 확인해보겠습니다!

엑셀 함수

오늘은 지긋지긋(?)한 코드 대신 엑셀 함수로 구현할 것입니다.

우선 간단히 개념먼저 정리해볼까요?

• 전일재고 = 전일까지의 총 입고 수량 - 전일까지의 총 출고수량
• 당일입고, 당일출고 = 현재 날짜로 기록되어있는 입/출고 수량의 합계
• 월간누계 = 현재 날짜의 월 기준 총 입/출고 수량
• 금일재고 = 현재 날짜까지의 총 입고수량 - 현재 날짜까지의 총 출고수량

으으 머리아픕니다... 이 부분은 구현하면서 다시 설명드리겠습니다.

먼저 금일에 입고된 A시리즈 항목을 살펴보겠습니다.

2018년 01월 03일 A시리즈로 입고된 수량이 총 300 + 20 개입니다.

이 데이터들을 통해 함수로 필요한 부분만 뽑아보겠습니다.

지금부터 약간 머리가 아파지니 차근차근 잘 따라오세요!

A시리즈의 당일입고 칸에 다음 함수를 붙여넣어 봅시다.

$ 절대 값 기호가 들어가있어 햇갈리실것 같아 절대값 푼 함수와 어디에 절대값을 넣어야 하는지 설명드리겠습니다.

=SUMIFS(DATA!E2:E1000,DATA!B2:B1000,"="&A1,DATA!C2:C1000,B5,DATA!D2:D1000,"입고")

우선 절대값을 넣는 이유는 항목마다 함수를 일일이 내용바꾸기는 귀찮기 때문에

긁어서 한번에 적용시킬 수 있도록 구현하려고 고정되어야 할 값들만 고정하는 것입니다.

가장 먼저 DATA 시트에 있는 데이터 값들은 가르키는 위치가 고정되어야겠죠?

날짜도 마찬가지입니다. 현황판에 고정되어있기 때문에 날짜가 들어가있는 A1 셀도 절대값을 넣습니다.

그렇다면 현황판에 있는 B5셀은 아래로 긁을 때 다음 물품값을 받아야 하기 때문에 절대값을 넣으면 안되겠죠?

B5셀 부분을 제외한 나머지에는 F4키를 사용하여 절대값 부호를 넣어줍시다.

이 부분은 추후에 자세히 설명해드리겠습니다.

귀찮으신 분들은 다음 코드를 복사해서 사용하세요! ^_^

=SUMIFS(DATA!$E$2:$E$1000,DATA!$B$2:$B$1000,"="&$A$1,DATA!$C$2:$C$1000,B5,DATA!$D$2:$D$1000,"입고")

SUMIFS 함수가 사용되었습니다.

이 함수는 조건에 맞는 셀을 모두 찾아 선택한 열의 정수값들을 모두 더하는 함수입니다.

SUMIF(합을 구하려는 값이 들어있는 범위, 조건1을 적용시킬 셀 범위, 조건1, 조건범위2,조건2,조건범위3,조건3.....)

• 합을 구하려는 값이 들어있는 범위는 수량이 들어가있는 범위 즉 E열이 됩니다.

• 조건을 적용시킬 셀 범위는 예를들어 같은 날짜값만 찾는다 하면 날짜값이 표시되어있는 범위를 넣고 조건에는 날짜값을 넣으면 되겠죠?

함수에 대해서는 다시 차근차근 설명드릴 예정이니 걱정안하셔도 됩니다!

자 아래그림처럼 입력하셨나요?

헉...헉... 가장쉬운 입고 부분을 먼저 진행했는데요, 출고는 입고 함수에서 글씨 하나만 바꾸면 됩니다!

마지막에 입고 라고 되어있는 부분을 '출고'로 변경하여 적용시켜봅시다.

=SUMIFS(DATA!$E$2:$E$1000,DATA!$B$2:$B$1000,"="&$A$1,DATA!$C$2:$C$1000,B5,DATA!$D$2:$D$1000,"출고")

다음 그림과 같이 적용됩니다. 출고값이 잘 나오죠?

자 이제 금일재고와 전일재고를 넣어보겠습니다.

금일재고도 간단합니다. DATA!$B$2:$B$1000,"="&$A$1 이 부분 기억나시나요?

= 부분을 <=로 바꿔주시면 됩니다. 왜냐하면 현재 날짜를 포함하여 그 이전 날짜에 있는 값들을

모두 읽어와야 하기 때문이죠. DATA!$B$2:$B$1000,"<="&$A$1 입니다. 간단하죠?

그렇다면 전일재고는? 위 금일재고에서 날짜 하나만 빼면 됩니다. DATA!$B$2:$B$1000,"<="&$A$1 - 1

금일재고부터 적용시켜봅시다.

금일재고는 '총 입고 - 총 출고' 이기 때문에 입고 부분 함수와 출고부분 함수를 복사해서 쓰시면 됩니다.

=SUMIFS(DATA!$E$2:$E$1000,DATA!$B$2:$B$1000,"<="&$A$1,DATA!$C$2:$C$1000,B5,DATA!$D$2:$D$1000,"입고") - SUMIFS(DATA!$E$2:$E$1000,DATA!$B$2:$B$1000,"<="&$A$1,DATA!$C$2:$C$1000,B5,DATA!$D$2:$D$1000,"출고")

전일재고는 '전일까지의 총 입고 - 전일까지의 총 출고' 이기 때문에 날짜 부분에 -1만 해주시면 됩니다.

=SUMIFS(DATA!$E$2:$E$1000,DATA!$B$2:$B$1000,"<="&$A$1 - 1,DATA!$C$2:$C$1000,B5,DATA!$D$2:$D$1000,"입고") - SUMIFS(DATA!$E$2:$E$1000,DATA!$B$2:$B$1000,"<="&$A$1 - 1,DATA!$C$2:$C$1000,B5,DATA!$D$2:$D$1000,"출고")

전일재고가 38이었고 당일 입고가 320, 당일출고가 138이면

38 + 320 - 138 이니까 220이 맞죠?

올바르게 재고가 나오는 것을 확인할 수 있습니다.

다음은 월간누계 입니다.

이해를 위해 따로 칸을 만들어 작성해보았습니다.

아래쪽에 '월초' 칸을 만들어 함수를 넣어보았습니다.

=DATE(YEAR(A1),MONTH(A1),1)

A1에 있는 YEAR(연도) 값과 MONTH(월) 값과 1 이라는 숫자를 합쳐보았습니다.

연도값과 월 값만 유지하고 일 값에 무조건 1을 넣으면 해당연월의 1일이 언제인지 알 수 있겠죠?

이 값을 가지고 월간누계를 만들어보겠습니다.

월간누계는 현재 연월의 월초(2018년 1월 01일) ~ 현재연월 (2018년 1월 03일)이 되겠죠?

그렇다면 조건을 하나 더 추가해주면 되겠네요!

현재연월 : DATA!$B$2:$B$1000,"<="&$A$1

현재월초 : DATA!$B$2:$B$1000,">="&$B$19

=SUMIFS(DATA!$E$2:$E$1000,DATA!$B$2:$B$1000,"<="&$A$1,DATA!$B$2:$B$1000,">="&$B$19,DATA!$C$2:$C$1000,B5,DATA!$D$2:$D$1000,"입고")

위 코드를 붙여넣어봅시다. 아래 그림처럼 값이 잘 나오나요?

월간 출고누계는 '입고'부분을 '출고'로 바꿔주기만 하면 됩니다.

=SUMIFS(DATA!$E$2:$E$1000,DATA!$B$2:$B$1000,"<="&A1,DATA!$B$2:$B$1000,">="&B19,DATA!$C$2:$C$1000,B5,DATA!$D$2:$D$1000,"출고")

와! 드디어 끝났습니다! 이제 긁어볼까요?

(빈누님의 GIF만드는 법) 감사합니다!

여기까지 따라오느라 고생 많으셨습니다! 이제 3개의 품목에 모두 적용되었습니다.

---

결과

2018년 1월 2일 현황판

2018년 1월 3일 현황판

---

결과 움짤

날짜만 변경하면 재고 항목들이 바뀌는 것을 확인하실 수 있습니다.

이 처럼 현황판을 미리 제작해두어 함수를 설정해두면 간편하게 현재 재고상황을(또는 자금상황)

한 눈에 확인해 볼 수 있습니다.

현황판을 결재해야 할 양식에 맞춰 제작해두면 버튼 클릭만으로 쉽게 출력하여 칼퇴할 수 있겠죠?

엑셀 자동화

이번 포스팅에서는 재고 현황판을 자동화시켜 날짜만 변경하면 값들이 조건에 맞게 바뀌도록

함수를 통해 구현해보았는데요!

(재고관리기준) 입고와 출고를 UI를 만들어 쉽게 할 수 있도록 구현 시킨 뒤

이렇게 현황판을 만들어두면 정말 정말 간편하게 현 상황을 알 수 있고 DATA가 계속 기록되기 때문에

현황판과 실제 재고상황이 맞지 않으면 바로 확인할 수 있는 장점들이 있습니다.

오늘 강의에서는 재고관리를 기준으로 제작하였지만 몇몇 글씨만 바꾸면 가계부나 여러 목적으로
사용 가능한 관리 방법입니다.

많은 도움을 드릴 수 있었으면 좋겠네요!

지난 강의에서 피드백 해주신 덕분에 엑셀 기초 강의도 포스팅할 계획을 짜 두었습니다.

너무 제 기준에서 포스팅 한 것 같아 다른 분들이 보실 때 어려움을 느끼셨을 것 같아 죄송한 마음밖에 안드네요 ㅠㅠ

혹시 강의에 추가되었으면 하는 기능이 있다거나

조언해주실 부분이 있으시다면 댓글로 거침없이 피드백 주세요! 쓴소리 좋아합니다!

능력있는 직장인이 되기 위해 엑셀로 전산프로그램을 만들 때 까지 한번 열심히 달려봅시다!

다음 포스팅에서 뵙겠습니다!

---

지난 포스팅

[Excel] 1. 능력있는 직장인이 되는 법. 엑셀 자동화란?

[Excel] 2. 능력있는 직장인이 되는 법. 다른 파일 불러오기!

[Excel] 3. 능력있는 직장인이 되는 법. 물품 입고시키기!

[Excel] 4. 엑셀로 능력있는 직장인이 되는 법. 간편한 UI 만들기!

[Excel] 5. 엑셀로 능력있는 직장인이 되는 법. 로그인 + 접속기록 만들기!

---