티스토리 뷰
제 업무와 직접적인 연관은 없다보니 실무에 실제 활용 가능할지는 모르겠지만, 제가 작성한 문서를 자신에 스타일에 맞도록 활용하거나, 연습을 하실 분들이 계실 것 같아서 작성해보았습니다.. 그리고 이번에 만든 자재 실재고와 예상 재고 관리 시트는, 3개의 파트로 나눠서 포스팅할 생각이고, 마지막은 종합 관리 항목으로 만들어볼 생각입니다.
실무에 사용해본 것은 아니기 때문에 오류가 있을 수도 있는데, 이런 점은 알려주시면 감사드립니다. 그리고 수식도 더 좋은 수식이 있을 수도 있겠지만, 제가 자주 사용하는 수식으로 작성하다보니 눈에 거슬리는 수식이 있을 수도 있으니 참고부탁드립니다.
실재고 및 예상 재고 관리 문서에 대한 간략 설명
크게 3가지 요소를 기록할 수 있습니다.
첫 번째는 이월 재고 수량을 기입할 수 있도록 했습니다. 이월 재고이기 때문에, 재고 카운트가 시작되는 시작일인 1일 자에 기입하시면 될 것 같습니다.
두 번째는 입고 수량과 출고 수량을 기입할 수 있습니다. 입고 및 출고 수량은 1일 단위로 관리되기 때문에, 1개의 셀에 누적 합산해 기록합니다.
세 번째는 입고 예정 수량과 출고 예정 수량을 기입할 수 있습니다. 이 항목 또한 1개의 셀에 누적 합산을 하면 되며, D + 1(다음 날) 부터 입력된 수량을 카운트합니다. 그렇기 때문에, D - DAY(오늘) 자를 포함한 이전 기록은 카운트하지 않습니다.
크게 2가지 요소를 관리(수량 카운트) 합니다.
첫 번째는 실재고 수량입니다. 이름에서부터 알 수 있듯이, D-DAY(오늘)을 포함한 실재고의 입고, 출고 수량을 카운트하며, 이전 자료에 대해서도 카운트 수량은 포함합니다. 그리고 실재고 수량이기 때문에, D + 1(다음 날) 이후의 재고 수량은 가상 수량이라 판단하고 계산하지 않습니다.
두 번째는 예상 재고 수량입니다. 예상되는 입고, 출고 수량이기 때문에, D-DAY(오늘)을 포함한 이전 자료는 계산하지 않습니다. 오늘 자까지 제외한 이유는, 오늘 입고 수량과, 실재고 수량을 카운트하게 되면, 중복 카운트가 되기 때문입니다.
수식 작성 첫 번째 - 입고 예정 수량 셀 주소 값 만들기
위에서도 언급 드렸듯이 과거 ~ 오늘까지를 실재고 수량으로, 다음날 ~ 미래 수량을 예상 입고, 출고 현황으로 관리하도록 했습니다. 여기서 실재고 수량은 오늘 까지의 기준이기 때문에, Today() 함수를 사용해 기준을 잡을 수 있습니다.
예상 입고, 출고 수량은 오늘 이후인 D + 1(다음 날) 부터 카운트될 필요가 있기 때문에, 다음 날의 기준을 잡아 줄 수 있도록 미리 주소 값을 만들어 두었습니다. 제가 사용한 방법보다 더 쉬운 방법도 있으리라 생각하지만, 제 선에선 이 정도가 최선이었던 것 같습니다.
입고 예정 수량 셀주소 값 예시 : = ADDRESS( MATCH( TODAY(), C6:C37,0) + 6, 9, 2 )
출고 예정 수량 셀주소 값 예시 : = ADDRESS( MATCH( TODAY(), C6:C37,0) + 6,10,2 )
MATCH(TODAY(), C6:C37,0) + 6을 먼저 설명드리겠습니다. 오늘 날짜를 불러오기 위해 TODAY() 함수를 사용했습니다. 예로 2019-01-05 일이라고 할 때, C6 : C37 범위에서 일치하는 값(옵션 0)을 찾게 됩니다. 아래 예제에선 C11 셀이 일치하는 값이고, MATCH는 C6을 1로 판단하기 때문에, C11은 6가 됩니다. 하지만 제가 원하는 것은 D+1(다음 날)의 주소 값입니다. 여기선 2019-01-06는 C12에 존재하기 때문에 5 + 6을 진행해 11로 만들어 줄 필요가 있습니다.
ADDRESS( 11, 9, 2 )는 11번째 행, 9번째 열 값을 2 열값은 상대주소로, 행 값은 절대주소($)로 표시되도록 합니다. 따라서 결과는 I$12 입니다. 그리고 출고 예정 수량의 경우는 열 값만 10으로 변경된 J$12 입니다.
직장인이다보니 이런류의 문서를 만들기 위해 시간을 사용하기엔 조금 벅찬감이 있긴한데, 그래도 만들고 보니 잼있고 뿌듯했던 것 같습니다.. 그리고 요즘은 전산 시스템이 워낙 잘되어있으니 이런 관리 문서를 만들어도 활용도가 낮을 것이라 생각하지만, 그래도 도움이 되시는 분들이 계실 것이라 생각하고, 시간이 되는한 관리문서를 계속해서 만들어보고, 포스팅도 해나갈 생각입니다.
실 재고 수량과 예상 재고 수량의 수식은 2번째 포스팅인 예상 재고 수량 계산하기#2를 참고해주시길 바랍니다.
이번 포스팅을 마지막까지 읽어주셔서 감사드립니다.
'엑셀 응용' 카테고리의 다른 글
| 엑셀 기초 강좌, 납품 수량 관리 시트 만들기#2 (엑셀 IF, ADDRESS 함수, 조건부 서식 사용) (0) | 2019.03.18 |
|---|---|
| 엑셀 교육, 납품 수량 관리 시트 만들기#1 (시트 기능 설명) (0) | 2019.03.18 |
| 자재 관리 요약 표지 만들기#4 (INDIRECT, ADDRESS, IFERROR 엑셀 함수 활용) (0) | 2019.01.09 |
| 자재 관리 요약 표지 만들기#3 (IFERROR, INDEX, MATCH 엑셀 함수 활용) (0) | 2019.01.08 |
| 엑셀로 자재 실재고 및 예상 재고 수량 계산하기#2 (IF, SUM, INDIRECT 함수 활용) (0) | 2019.01.06 |
| 엑셀에서 조건에 맞는 셀 또는 줄 전체에 서식 지정하기 (0) | 2019.01.03 |
| 엑셀에서 중복 값 입력 안되도록 하기(데이터 유효성 검사) (0) | 2019.01.01 |
| 엑셀에서 대,중,소분류 목록 만들기 (이름 정의, 데이터 유효성 검사 활용) (0) | 2018.12.31 |