엑셀 재고관리표를 만들 때 상품별 입고와 출고를 한 표에 계속 더하고 빼는 방식은 처음에는 간단하지만, 기록이 쌓이면 어떤 내역 때문에 현재고가 달라졌는지 확인하기 어려워집니다.
입출고가 계속 발생한다면 입출고기록과 재고현황을 나눠 관리하는 편이 편합니다. 입출고 내역은 날짜별로 계속 쌓고, 재고현황에서는 상품별 총입고와 총출고를 자동으로 집계한 뒤 기초재고를 반영해 현재고를 계산하는 방식입니다.
이 글에서는 상품목록 → 입출고기록 → 재고현황 순서로 구성하고, 기초재고 + 총입고 - 총출고 방식으로 현재고가 자동계산되도록 만들어봅니다.
예제는 Excel과 Google Sheets에서 모두 사용할 수 있는 기본 수식과 기능을 중심으로 구성합니다.
먼저 어떤 구조로 재고를 관리할지 정한 뒤 실제 표와 수식을 만들어보겠습니다.
엑셀 재고관리표 기본 구성｜입출고기록과 재고현황 분리
재고관리표를 무조건 여러 시트로 나눠야 하는 것은 아닙니다. 상품 수와 입출고 빈도에 따라 한 표로 관리해도 되는 경우와 기록을 분리하는 편이 좋은 경우가 다릅니다.
한 표로 관리해도 되는 경우
상품 종류가 적고 입출고가 자주 발생하지 않으며 과거 입출고 내역보다 현재 수량만 확인하면 되는 경우라면 한 표에 기초재고·입고·출고·현재고를 두는 방식도 사용할 수 있습니다.
| 상품명 | 기초재고 | 입고 | 출고 | 현재고 |
|---|---|---|---|---|
| A상품 | 10 | 100 | 20 | 90 |
다만 이 구조에서는 입출고가 여러 번 발생했을 때 언제 몇 개가 들어오고 나갔는지 기록을 남기기 어렵습니다.
입출고기록과 재고현황을 분리해야 하는 경우
입고와 출고가 계속 발생한다면 입출고기록은 거래 내역처럼 계속 누적하고 재고현황은 그 기록을 집계하는 방식이 관리하기 쉽습니다.
이번 예제 파일은 세 개의 시트로 구성합니다.
| 시트 | 역할 |
|---|---|
| 상품목록 | 재고를 관리할 상품명을 등록 |
| 입출고기록 | 날짜별 입고·출고 내역 입력 |
| 재고현황 | 기초재고·총입고·총출고·현재고 확인 |
이렇게 분리하면 현재고 숫자가 맞지 않을 때 입출고기록으로 돌아가 원인을 찾기도 쉽습니다.
입출고 관리대장 만들기
입출고 관리대장은 재고 계산의 기준 데이터입니다. 날짜, 상품명, 구분, 수량 네 가지를 기본 항목으로 두면 복잡하지 않으면서 상품별 입고와 출고를 집계할 수 있습니다.
| 날짜 | 상품명 | 구분 | 수량 |
|---|---|---|---|
| 2026-09-01 | A상품 | 입고 | 100 |
| 2026-09-02 | A상품 | 출고 | 20 |
| 2026-09-02 | B상품 | 입고 | 50 |
| 2026-09-03 | B상품 | 출고 | 5 |
| 2026-09-03 | C상품 | 입고 | 30 |
| 2026-09-03 | A상품 | 출고 | 10 |
날짜·상품명·입고·출고 수량 입력 방법
날짜에는 실제 입출고가 발생한 날짜를 입력하고, 상품명은 상품목록에 등록한 이름을 사용합니다. 구분은 입고 또는 출고 중 하나를 선택하고 수량은 양수로 입력합니다.
예를 들어 A상품 20개가 출고됐다면 수량에 -20을 입력하는 것이 아니라 구분을 출고로 선택하고 수량에는 20을 입력하는 방식입니다.
이렇게 하면 입고와 출고를 같은 수량 열에서 관리하면서도 SUMIFS의 조건으로 각각 따로 합산할 수 있습니다.
드롭다운으로 상품명·입고·출고 입력값 통일하기
상품명을 매번 직접 입력하면 A상품과 A 상품처럼 조금씩 다른 값이 생길 수 있습니다. 사람 눈에는 같은 상품처럼 보여도 수식에서는 다른 값으로 처리되어 합계가 나뉠 수 있습니다.
그래서 실제 파일에서는 상품명은 상품목록을 기준으로 선택하고, 구분은 입고 / 출고 두 값 중에서 선택하도록 드롭다운을 적용했습니다.
드롭다운 목록을 처음부터 만드는 방법이나 목록을 추가·수정하는 과정은 엑셀·구글시트 드롭다운 목록 만드는 방법에서 자세히 확인할 수 있습니다.
입력값을 일정하게 유지해두면 이후 상품별 집계 수식도 안정적으로 사용할 수 있습니다.
입출고기록이 준비되면 재고현황 시트에서 상품별 입고와 출고를 각각 합산합니다.
SUMIFS로 상품별 총입고·총출고 자동계산
재고현황은 상품명, 기초재고, 총입고, 총출고, 현재고 순서로 구성합니다.
| 상품명 | 기초재고 | 총입고 | 총출고 | 현재고 |
|---|---|---|---|---|
| A상품 | 10 | 100 | 30 | 80 |
| B상품 | 5 | 50 | 5 | 50 |
| C상품 | 0 | 30 | 0 | 30 |
SUMIFS로 총입고 계산하기
A5에 있는 상품의 입고 수량만 합하려면 상품명과 구분 두 조건을 동시에 확인해야 합니다.
=IF(A5="","",SUMIFS('입출고기록'!$D$5:$D$1004,'입출고기록'!$B$5:$B$1004,A5,'입출고기록'!$C$5:$C$1004,"입고"))앞의 IF는 상품명이 없는 행에서는 계산 결과를 표시하지 않기 위한 부분입니다. 실제 집계는 SUMIFS가 담당합니다.
이 수식은 입출고기록의 D열 수량 중에서 B열 상품명이 현재 행의 상품명과 같고 C열 구분이 입고인 값만 더합니다.
예제에서 A상품의 입고 기록은 100개이므로 총입고는 100으로 계산됩니다.
SUMIFS로 총출고 계산하기
총출고도 구조는 동일하고 마지막 조건만 "출고"로 바꾸면 됩니다.
=IF(A5="","",SUMIFS('입출고기록'!$D$5:$D$1004,'입출고기록'!$B$5:$B$1004,A5,'입출고기록'!$C$5:$C$1004,"출고"))A상품은 9월 2일 20개, 9월 3일 10개가 출고됐으므로 총출고는 30입니다.
SUMIFS의 조건 범위와 여러 조건을 사용하는 기본 원리는 엑셀·구글시트 SUMIF·SUMIFS 함수 사용법에서 별도로 확인할 수 있습니다.
현재고 자동계산｜기초재고 + 총입고 - 총출고
상품별 총입고와 총출고가 계산됐다면 현재고 자동계산은 복잡한 함수가 필요하지 않습니다.
=IF(A5="","",B5+C5-D5)B열이 기초재고, C열이 총입고, D열이 총출고라면 첫 상품 행인 5행의 현재고는 다음과 같이 계산됩니다. IF는 상품명이 없는 행에서는 결과를 표시하지 않기 위한 부분입니다.
현재고 = 기초재고 + 총입고 - 총출고
A상품 = 10 + 100 - 30
A상품 현재고 = 80실제 제작한 파일에서도 A상품은 기초재고 10개, 총입고 100개, 총출고 30개로 현재고가 80개로 계산됩니다.
기초재고 중복 계산 방지
재고관리를 시작하는 시점에 이미 보유하고 있는 수량이 있다면 재고현황의 기초재고에 입력합니다.
예를 들어 관리 시작 시점에 A상품을 10개 가지고 있다면 기초재고에는 10을 입력합니다. 이 10개를 입출고기록에도 다시 최초 입고로 입력하면 같은 재고가 두 번 더해지므로 둘 중 한 방식만 사용해야 합니다.
이번 예제와 배포 파일에서는 기초재고 열을 별도로 사용하는 방식으로 통일했습니다.
현재고 계산 구조 자체는 간단하지만 실제 값이 맞지 않을 때는 수식보다 입력 기록을 먼저 확인하는 편이 빠릅니다.
특히 수식이 정상인데 현재고만 다른 경우에는 아래 순서로 확인하면 원인을 찾기 쉽습니다.
현재고가 맞지 않을 때 확인할 항목
현재고가 맞지 않는다고 바로 SUMIFS 수식을 수정하면 오히려 정상 수식을 망가뜨릴 수 있습니다. 먼저 원본인 입출고기록이 정확한지 확인합니다.
- 상품명이 같은 값으로 입력됐는지 확인
- 입고와 출고 구분이 바뀌지 않았는지 확인
- 수량을 잘못 입력하지 않았는지 확인
- 누락된 입출고 기록이 없는지 확인
- 기초재고가 중복 반영되지 않았는지 확인
- 마지막으로 SUMIFS의 집계 범위를 확인
상품명 표기 차이 확인
예를 들어 입출고기록에 A상품과 A 상품이 섞여 있으면 SUMIFS는 서로 다른 조건값으로 처리합니다.
상품명이 비슷한 경우가 많거나 옵션이 많은 경우에는 상품명만 직접 입력하기보다 상품목록을 만들어 드롭다운으로 선택하거나 별도의 상품코드를 함께 사용하는 방법이 더 안정적입니다.
SUMIFS 수식 범위 확인
예를 들어 수식이 D5:D20까지만 집계하도록 되어 있는데 새 거래가 21행 이후에 입력되면 새 수량은 합계에 포함되지 않습니다.
이번 예제 파일은 제목과 안내행 아래 실제 입력 영역인 입출고기록 5행부터 1004행까지 집계 범위로 잡았습니다. 데이터가 훨씬 많아질 경우에는 무조건 전체 열을 참조하기보다 실제 데이터 규모에 맞게 범위를 확장하는 방식도 사용할 수 있습니다.
입력 영역과 자동계산 영역 구분하기
재고관리표를 처음 만들 때는 수식이 맞는지만 확인하기 쉽지만 실제로 계속 사용하려면 사용자가 입력하는 곳과 자동으로 계산되는 곳을 구분해두는 것이 중요합니다.
직접 입력 셀과 수식 셀 구분
이번 파일에서는 직접 입력하는 항목과 자동계산 항목을 다음처럼 구분했습니다.
| 구분 | 항목 |
|---|---|
| 직접 입력 | 상품명, 기초재고, 날짜, 입고·출고 구분, 수량 |
| 자동계산 | 총입고, 총출고, 현재고 |
자동계산 셀을 실수로 덮어쓰지 않도록 입력 영역과 계산 영역의 배경을 다르게 구분해두면 유지하기 편합니다. 실제 배포 파일도 이 구조로 만들어 두었습니다.
또 현재고가 0보다 작아지는 경우에는 입력 누락이나 출고 과다 여부를 확인할 수 있도록 해당 현재고 셀이 표시되도록 조건부서식을 적용했습니다.
조건에 따라 셀이나 행을 강조하는 방법은 엑셀·구글시트 조건부서식 사용법에서 자세히 볼 수 있습니다.
상품이 많다면 상품코드 추가
상품 종류가 몇 개뿐이라면 상품명만으로도 관리할 수 있습니다. 하지만 비슷한 상품명이 많거나 색상·사이즈 같은 옵션이 늘어나면 상품코드를 함께 관리하는 편이 구분하기 쉽습니다.
기본 재고관리표부터 상품코드를 반드시 넣을 필요는 없고, 상품명이 헷갈리기 시작하는 시점에 확장하면 됩니다.
Excel·Google Sheets에서 재고관리표 만들 때 확인할 점
이 글에서 사용하는 SUMIFS와 기본 현재고 계산식은 Excel과 Google Sheets에서 모두 사용할 수 있습니다.
Google Sheets에서도 같은 계산 방식으로 재고관리표를 만들 수 있습니다. 상품명과 입고·출고를 일정하게 입력하기 위한 드롭다운과 데이터 유효성 검사도 사용할 수 있지만, Excel과 Google Sheets는 메뉴 위치와 설정 화면이 다르므로 사용하는 프로그램에 맞춰 설정하면 됩니다.
재고관리표 만드는 순서 정리
| 순서 | 작업 내용 |
|---|---|
| 1 | 상품목록에 관리할 상품명 등록 |
| 2 | 입출고기록에 날짜·상품명·입고 또는 출고·수량 입력 |
| 3 | 재고현황에 기초재고 입력 |
| 4 | SUMIFS로 상품별 총입고·총출고 집계 |
| 5 | 기초재고 + 총입고 - 총출고로 현재고 계산 |
이 구조로 만들어두면 새로운 입출고 내역을 추가할 때마다 상품별 총입고와 총출고를 다시 집계할 수 있고, 현재고도 같은 방식으로 계속 관리할 수 있습니다.
재고 숫자가 맞지 않을 때는 수식부터 바꾸기보다 상품명 → 입고·출고 구분 → 수량 → 누락 기록 → 기초재고 → 수식 범위 순서로 확인하는 것이 좋습니다.
재고관리표 엑셀 양식 다운로드
위에서 설명한 구조를 직접 만들지 않고 바로 사용하려면 재고관리표 파일을 이용할 수 있습니다. 상품목록에 상품명을 등록하고 입출고기록을 입력하면 총입고·총출고와 현재고가 자동으로 계산됩니다. 아래 버튼을 누르면 모이의 정보창고 자료실에서 Excel 파일을 다운로드할 수 있습니다.
재고관리표 엑셀 파일 받기제공 파일은 Excel .xlsx 형식이며, 상품목록·입출고기록·재고현황 3개 시트로 구성되어 있습니다.
