웹용 Excel에서는 시트 이름을 참조할 때 '예제_기록'!$B$5:$B$304처럼 시트 이름 뒤에 느낌표를 붙입니다.
수식은 B열 전체에서 A5 칸의 코드와 같은 줄을 찾고 그 줄에 해당하는 C열의 입고수량만 모아서 합산합니다. 출고합계(F열) 역시 같은 방식으로 출고수량 칸(D열)을 지정해 더합니다. 코드가 같은 줄만 골라 더하므로 품목명이 같아도 코드가 다르면 다른 품목으로 분리되어 계산됩니다.
마지막으로 현재고(G열)는 =IF($A5="","",$D5+$E5-$F5) 수식으로 기초재고에 입고합계를 더하고 출고합계를 빼서 최종 수량을 나타냅니다.
이 수식은 예제_품목 시트 기준입니다. 실사용_품목 시트는 같은 수식이 실사용_기록 시트를 가리킵니다.
4. 내 데이터를 넣기 전에 알아 둘 입력 규칙
아래부터 품목, 기록은 예제_와 실사용_이 붙은 시트를 함께 부르는 말입니다. 첨부된 수식 없는 실습 시작 파일만 시트 이름이 그대로 품목, 기록입니다. 연습이 끝나면 실사용 시트에 내 데이터를 적으면 됩니다.
연노랑 칸: 직접 입력하는 칸
연하늘 칸: 수식으로 자동 계산되는 칸 (직접 고치지 않습니다)
품목 시트에는 품목코드, 품목명, 단위, 기초재고를 적고 기록 시트에는 날짜, 품목코드, 입고수량 또는 출고수량을 적습니다. 한 줄에는 입고나 출고 중 하나만 적습니다.
기초재고는 처음 시작할 때 창고에 있던 수량입니다. 이 수량은 기록 시트에 입고로 또 적지 않습니다. 기록 시트에 다시 적으면 입고합계에 한 번 더 더해져 수량이 이중으로 계산됩니다.
5. 잘못 적었을 때 일어나는 일
장부를 쓰다 보면 칸을 비우거나 엉뚱한 값을 넣는 날이 꼭 있습니다. 양식이 이런 실수를 어떻게 받아 주는지 봅니다.
품목코드를 깜빡하고 비워 두면
실사용_품목 시트에서 품목코드를 비운 채 품목명과 기초재고만 적으면 오른쪽 확인 칸에 "품목코드 입력"이라는 안내가 뜨고 3행에 주의 문구가 표시됩니다.
목록에 없는 엉뚱한 코드를 적으면
실사용_기록 시트의 품목코드 칸에 등록되지 않은 코드(예: ZZZ)를 직접 입력하면 웹용 Excel의 데이터 유효성 검사 안내창이 뜨면서 입력 자체가 거부됩니다. 품목 시트에 등록되지 않은 코드는 기록 시트에 적을 수 없다는 뜻입니다.
재고가 모자라거나 입고와 출고를 한 줄에 같이 적으면
이 두 가지 상황은 웹용 Excel 화면으로 직접 확인하지 못했고 LibreOffice Calc에서만 확인했습니다. 이 글의 실습은 위 단계만으로 따라 할 수 있습니다.
아래 확인 칸 설명과 문구 표는 양식에 들어 있는 규칙을 정리한 것입니다. 웹용 Excel에서 직접 확인한 확인 칸 문구는 "품목코드 입력", "날짜 입력", "입고 또는 출고 수량 입력"이고, 나머지 문구는 확인하지 못했습니다.
기록 시트 F열(확인)은 줄마다 입력 상태를 검사해 문제가 있으면 빨간 줄과 짧은 문구로 알려 줍니다. 문제가 있는 줄이 하나라도 있으면 품목 시트 맨 위(3행)에 주의 문구가 나옵니다. 품목 시트에서도 품목코드 없이 품목명·단위·기초재고만 적은 줄은 확인 칸에 "품목코드 입력"이 나옵니다.
입력 상태
확인 칸 문구
품목코드 없음
품목코드 선택
목록에 없는 코드
등록되지 않은 품목코드
입고·출고를 둘 다 적음
입고·출고 중 하나만 입력
입고·출고를 둘 다 비움
입고 또는 출고 수량 입력
문자
수량은 숫자
0, 음수, 소수
수량은 1 이상의 정수
날짜 없음
날짜 입력
키보드로 직접 입력하면 입력 제한이 잘못된 값을 막아 줍니다. 다른 곳에서 복사해 붙여넣을 때 입력 제한이 어떻게 적용되는지는 이 글에서 확인하지 못했습니다. 그래서 붙여넣은 값은 확인 칸이 마지막 안전망 역할을 합니다. 붙여넣은 뒤에는 확인 칸을 한 번 살펴보세요.
6. 원리 정리와 실사용 시작하기
핵심 원리는 단순합니다.
출고가 늘어난 만큼 현재고가 줄어듭니다. 중간에 다른 품목의 입출고가 섞여 있어도 수식은 코드가 같은 줄만 골라 합산하고 기초재고는 처음 한 번만 계산에 들어갑니다.
감이 잡혔다면 실사용_품목과 실사용_기록 시트로 옮겨 관리할 물품과 기초재고를 적고 시작하세요. 예제_ 시트는 나중에 계산을 다시 확인할 때 쓰도록 남겨 두면 됩니다.
이 양식이 하지 않는 것
재고 금액, 원가, 판매·이익 계산
바코드, 여러 창고, 반품·폐기 자동 처리
매크로
모든 품목을 합친 총재고: 단위(묶음, 자루, 개)가 달라 더하지 않습니다.
품목은 최대 20개, 기록은 최대 300건입니다. 수식 범위가 여기까지이며 20번째 품목(24행)과 300번째 기록(304행)은 LibreOffice와 웹용 Excel에서 확인했습니다. 웹용 Excel에서는 304행에 입고 3을 적자 24행 품목의 입고합계가 3, 현재고가 8(기초재고 5 + 3)로 계산됐습니다.
현재고 음수 표시는 지금까지의 합계만 보고 알려 줍니다. 특정 날짜 시점에 재고가 모자랐는지는 검사하지 않습니다.
웹용 Excel에서 직접 확인한 것은 예제 계산, 값 바꾸기, 품목코드 누락 경고, 잘못된 코드 거부, 20번째 품목·300번째 기록 계산입니다. 잘못된 코드 거부는 파일을 다시 열기 전에 확인했습니다. 다시 연 뒤에는 시트와 수식이 그대로 남아 있는 것까지만 확인했고 입력 제한 거부는 다시 시험하지 않았습니다. 입출고를 한 줄에 같이 적는 경우와 음수 현재고 화면은 웹용 Excel에서 따로 확인하지 못했고 LibreOffice에서만 확인했습니다. 메뉴 이름은 화면 언어와 버전에 따라 다를 수 있으니 화면에 보이는 이름을 따라 주세요.
