📑 목차
상품 수가 늘어나면 현재고를 직접 더하고 빼는 방식은 금방 틀어집니다. 같은 상품을 여러 번 입고하거나 반품·출고 내역이 쌓이면 어느 거래에서 숫자가 달라졌는지 찾기도 어렵습니다.
가장 관리하기 쉬운 방법은 상품목록과 입출고내역을 분리하는 것입니다. 거래는 한 줄씩 기록하고 상품목록에서는 SUMIFS로 입고·출고를 합산하면 현재고, 안전재고, 부족수량과 발주 상태까지 자동으로 표시할 수 있습니다.
재고관리표는 시트 2개로 만들기
| 시트 | 입력 항목 | 역할 |
|---|---|---|
| 상품목록 | 상품코드·상품명·기초재고·안전재고 | 입고·출고·현재고·부족수량 자동 계산 |
| 입출고내역 | 날짜·구분·상품코드·수량·단가 | 모든 재고 변동을 한 줄씩 기록 |
두 시트를 각각 선택한 뒤 Ctrl+T를 눌러 ‘표’로 만들면 거래 행을 추가할 때 수식과 서식이 자동 확장됩니다. 시트명과 열 제목은 수식에서 사용하므로 띄어쓰기까지 통일하세요.
상품목록 시트 열 구성
| 열 | 항목 | 입력·계산 |
|---|---|---|
| A | 상품코드 | 중복 없는 코드 직접 입력 |
| B | 상품명 | 직접 입력 |
| C | 기초재고 | 관리 시작일 실사 수량 |
| D·E | 누적입고·누적출고 | SUMIFS 자동 합산 |
| F | 현재고 | 기초+입고-출고 |
| G | 안전재고 | 상품별 최소 보유수량 |
| H·I | 부족수량·상태 | 수식 자동 표시 |
입출고내역 시트 입력 방법
A열 날짜, B열 구분, C열 상품코드, D열 상품명, E열 수량, F열 단가, G열 금액 순서로 만듭니다. B열 구분은 ‘입고’와 ‘출고’만 선택할 수 있도록 데이터 유효성 검사 목록을 설정합니다.
C2의 상품코드를 기준으로 D2 상품명을 자동 불러오려면 다음 XLOOKUP 수식을 사용합니다.
거래금액 G2는 수량과 단가를 곱합니다.
구버전 Excel 주의: XLOOKUP은 일부 구버전에서 사용할 수 없습니다. Excel 2016·2019 등에서 호환성 문제가 있으면 IFERROR와 VLOOKUP 조합을 사용하세요.
1단계｜상품별 누적입고 SUMIFS 수식
상품목록 A2에 상품코드가 있고 입출고내역의 B열이 구분, C열이 상품코드, E열이 수량이면 D2의 누적입고는 다음과 같습니다.
SUMIFS는 합계 범위를 먼저 쓰고 ‘상품코드가 A2와 같음’, ‘구분이 입고임’이라는 두 조건을 적용합니다. 수식을 아래 행으로 복사할 수 있도록 상품코드 열만 고정했습니다.
2단계｜상품별 누적출고 SUMIFS 수식
E2의 누적출고는 마지막 조건만 ‘출고’로 바꿉니다.
반품입고와 반품출고를 별도 구분으로 관리한다면 각 항목을 SUMIFS로 계산해 현재고 수식에 더하거나 빼는 방식이 가장 명확합니다.
3단계｜현재고 자동 계산
상품목록 F2에서 기초재고 C2와 누적입고 D2를 더하고 누적출고 E2를 뺍니다.
음수 재고를 화면에서 0으로 숨기면 입력 오류를 발견하기 어렵습니다. 현재고는 실제 계산값을 그대로 표시하고, 음수일 때 ‘재고 오류’ 경고를 띄우는 편이 좋습니다.
4단계｜안전재고 부족수량 표시
G2에 상품별 안전재고를 입력하고 H2에서 현재고가 안전재고보다 부족한 수량만 계산합니다.
예를 들어 현재고가 20개, 안전재고가 30개라면 부족수량은 10개입니다. 현재고가 충분하면 음수 대신 0이 표시됩니다.
5단계｜정상·발주 필요·품절 자동 표시
I2에 현재고 상태를 표시합니다. 현재고가 0 이하이면 품절, 안전재고보다 적으면 발주 필요, 그 외에는 정상입니다.
조건부 서식에서 I열 값이 ‘품절’이면 빨간색, ‘발주 필요’이면 주황색, ‘정상’이면 초록색으로 지정하면 우선 발주 상품을 빠르게 찾을 수 있습니다.
기간별 입고·출고만 계산하기
월별 재고 흐름이 필요하면 시작일 K1과 종료일 L1을 추가 조건으로 넣습니다. 다음은 A2 상품의 기간 내 입고량입니다.
입출고내역 A열의 날짜가 문자이면 결과가 0으로 나올 수 있습니다. 날짜 셀을 숫자 형식으로 바꿨을 때 45000대 같은 일련번호가 보여야 실제 날짜값입니다.
상품코드 중복과 입력 오류 막기
상품코드 중복 확인
상품목록 J2에 다음 수식을 넣으면 같은 코드가 두 번 이상 등록됐을 때 경고할 수 있습니다.
존재하지 않는 코드 확인
입출고내역에서 상품목록에 없는 코드가 입력됐는지 확인합니다.
상품코드 열에 데이터 유효성 검사 목록을 적용하면 오타 자체를 줄일 수 있습니다. 바코드를 사용하더라도 앞자리 0이 사라지지 않도록 상품코드 열은 텍스트 형식으로 지정하세요.
재고금액과 총 재고자산 계산
상품목록 J열에 기준단가가 있다면 K2의 재고금액은 현재고와 기준단가를 곱합니다.
전체 상품의 재고금액은 아래처럼 합산합니다.
주의: 이 금액은 입력한 기준단가를 사용한 관리용 값입니다. 이동평균법·선입선출법 등 회계상 재고평가와 매출원가 계산은 회계정책과 세무 기준에 맞는 별도 장부가 필요합니다.
재고 수량이 안 맞을 때 점검표
| 증상 | 주요 원인 | 해결 |
|---|---|---|
| 합계가 0 | 코드·구분 띄어쓰기 불일치 | 목록 선택 방식으로 통일 |
| 일부 거래 누락 | 수식 범위 밖에 새 행 추가 | Ctrl+T 표 또는 전체 열 참조 |
| 음수 재고 | 입고 누락·출고 중복 | 거래내역과 실사 대조 |
| #N/A | 상품코드 미등록 | 상품목록 코드 확인 |
| 코드 앞자리 0 삭제 | 숫자 형식으로 저장 | 코드 열을 텍스트로 지정 |
자주 묻는 질문
Q1. 입고와 출고를 한 시트에 기록해도 되나요?
네. 구분 열에 입고·출고를 선택하게 하고 SUMIFS로 각각 합산하면 한 시트에서 관리하는 편이 검색과 수정에 유리합니다.
Q2. 반품은 어떻게 기록하나요?
고객 반품은 반품입고, 공급처 반품은 반품출고처럼 별도 구분을 만들고 현재고 수식에서 각각 더하거나 빼는 방식이 명확합니다.
Q3. 현재고가 음수이면 0으로 표시해도 되나요?
권장하지 않습니다. 음수는 입고 누락이나 출고 중복을 발견하는 중요한 신호이므로 실제 값과 ‘재고 오류’ 경고를 함께 표시하는 것이 좋습니다.
Q4. XLOOKUP에서 #N/A가 표시돼요.
입력한 상품코드가 상품목록에 없거나 코드 앞뒤에 공백이 있을 수 있습니다. 두 시트의 코드 형식을 텍스트로 통일하고 공백을 확인하세요.
Q5. 파일이 느려지면 어떻게 하나요?
전체 열 참조 대신 실제 데이터 범위나 Excel 표의 구조적 참조를 사용하고, 오래된 거래는 연도별 파일로 분리하면 계산량을 줄일 수 있습니다.