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

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

오늘은 [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로 호출하여 증감시켜주면 됩니다.

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

엑셀 함수

오늘은 어려운 vba코드 대신 엑셀 함수로 구현할 것입니다.

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

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

일반적으로 재고 관리를 할 때 필수로 사용하는 부분만 추가하였습니다.

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

먼저 금일에 입고된 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,"출고")