Excel로 재고 추적기 만드는 방법: 단계별 가이드

Excel에서 재고 추적기를 만들려면 두 개의 테이블을 만드세요. 첫 번째 테이블은 품목 ID, 이름, 단가, 기초 재고, 재주문 시점 열을 포함하여 품목을 나열합니다. 두 번째 테이블은 날짜, 품목 ID, 유형(In 또는 Out), 수량과 함께 모든 변동 내역을 기록합니다. 그런 다음 SUMIFS를 사용하여 입고량과 출고량을 합산하고, 이를 빼서 현재고를 구합니다.
재주문 시점 이하인 품목을 표시하는 상태 열을 추가하고, 조건부 서식을 사용하여 해당 행을 강조 표시합니다. 상단의 간단한 요약은 총 재고 가치와 재주문이 필요한 품목 수를 보여줍니다.
이 가이드는 테이블 구조부터 수식과 알림에 이르기까지 각 단계를 다룹니다. 또한 소규모 사무용품 비품실을 예로 든 전체 예시와 내보낸 데이터에서 추적기를 더 빠르게 만드는 방법도 살펴봅니다. 이 단계들은 2026년 11월 18일에 확인한 Microsoft 지원 페이지를 바탕으로 합니다.
재고 추적기의 역할
재고 추적기는 언제든 다음 세 가지 질문에 대한 답을 제공합니다:
- 각 품목을 얼마나 보유하고 있는가? 이는 현재고를 의미합니다.
- 그 가치는 얼마인가? 이는 단가를 기준으로 한 재고 가치입니다.
- 무엇을 주문해야 하는가? 이는 재주문 시점 이하인 품목들입니다.
훌륭한 추적기는 이력도 기록합니다. 수량이 잘못된 것처럼 보일 때, 변동 로그를 역으로 스크롤하여 재고가 언제 입고되거나 출고되었는지 확인할 수 있습니다.
Excel 재고 추적기는 중소규모 재고 관리에 적합합니다. 비품실, 소규모 매장, 사무용품, 예비 부품, 행사 장비 등에 잘 맞습니다. 여러 위치에 걸쳐 수천 개의 품목을 추적해야 하는 단계가 되면, 일반적으로 전용 재고 관리 시스템을 사용하는 것이 더 좋습니다.
추적기 계획하기: 두 개의 테이블
수식을 입력하기 전에 재고 추적기를 어떻게 구성할지 결정하세요. 많은 단순한 추적기는 사람들이 수동으로 덮어쓰는 단일 수량 열이 있는 하나의 테이블만 사용합니다. 이는 금방 망가집니다. 숫자가 언제, 왜 변경되었는지 아무도 알 수 없기 때문입니다.
더 나은 설계는 품목 목록과 변동 내역을 분리하는 것입니다:
| 테이블 | 행당 기준 | 주요 열 |
|---|---|---|
| Items | 제품 또는 부품 | 품목 ID, 이름, 카테고리, 단가, 기초 재고, 재주문 시점 |
| Movements | 재고 변동 | 날짜, 품목 ID, 유형(In 또는 Out), 수량, 비고 |
Items 테이블은 거의 변경되지 않는 고정 정보를 담고 있습니다. Movements 테이블은 매일 늘어납니다. Items 테이블의 수식은 Movements 테이블을 읽어 현재 재고를 계산합니다.
이 설계는 세 가지 장점이 있습니다. 숫자를 덮어쓸 필요가 없습니다. 모든 변경 사항에 날짜가 기록됩니다. 그리고 나중에 3월에 재고가 얼마나 출고되었는지와 같은 질문에 답할 수 있습니다.
또한 재고 추적기를 더 안전하게 공유할 수 있게 해줍니다. 직원들은 Movements 테이블에 행만 추가하면 됩니다. 수식이 포함된 Items 테이블은 잠금 상태로 유지할 수 있습니다.
각 테이블을 별도의 시트에 배치하세요. 수식을 쉽게 읽을 수 있도록 시트 이름을 "Items"와 "Movements"로 지정합니다.
품목 목록을 테이블로 만들기
Items 시트부터 시작합니다. 1행에 헤더를 입력합니다: 품목 ID, 이름, 카테고리, 단가, 기초 재고, 재주문 시점. 그런 다음 품목당 한 행씩 입력합니다.
다음으로, 범위를 Excel 테이블로 변환합니다. Microsoft는 그 이유를 다음과 같이 설명합니다. "관련된 데이터 그룹을 더 쉽게 관리하고 분석하기 위해 셀 범위를 Excel 테이블로 변환할 수 있습니다." 테이블을 만들려면 다음 단계를 따르세요:
- 데이터에서 아무 셀이나 선택합니다.
- 홈 > 표 서식을 선택합니다.
- 테이블 스타일을 선택합니다.
- 머리글 포함 확인란이 선택되어 있는지 확인한 다음 확인을 선택합니다.
테이블 디자인 탭에서 테이블 이름을 "Items"로 지정합니다. 다른 시트의 변동 로그에도 동일하게 수행하고 이름을 "Movements"로 지정합니다.
테이블은 두 가지 방식으로 도움이 됩니다. 행을 추가하면 자동으로 확장됩니다. 그리고 테이블 열의 수식은 자동으로 아래로 채워집니다. Microsoft는 한 셀에 입력된 수식이 "해당 테이블 열의 다른 모든 셀에 즉시 적용된다"고 설명합니다.
품목 ID는 A-101과 같이 짧고 고유하게 유지하세요. 이름은 맞춤법이 달라질 수 있으므로 이름 대신 품목 ID를 모든 곳에 사용하십시오.
변동 로그에 드롭다운 추가하기
입력 오류는 재고 수량이 어긋나는 가장 큰 원인입니다. 드롭다운 목록을 사용하면 대부분의 오류를 방지할 수 있습니다.
Microsoft는 그 목적을 한 줄로 설명합니다. "데이터 유효성 검사를 사용하여 사용자가 셀에 입력하는 데이터 유형이나 값을 드롭다운 목록처럼 제한할 수 있습니다." 유형(Type) 열의 경우 다음 단계를 따르세요:
- Movements 테이블에서 유형(Type) 열을 선택합니다.
- 데이터 > 데이터 유효성 검사를 선택합니다.
- 설정 탭의 제한 대상 상자에서 목록을 선택합니다.
- 원본 상자에 In,Out을 입력합니다.
- 드롭다운 표시가 선택되어 있는지 확인한 다음 확인을 선택합니다.
품목 ID(Item ID) 열에도 동일하게 수행하되, 원본이 Items 테이블의 품목 ID 열을 가리키도록 합니다. 이제 사람들은 존재하는 품목만 선택할 수 있습니다.
드롭다운은 입력 속도도 높여줍니다. 비품실 담당자는 품목과 유형을 선택한 다음 수량을 입력하여 몇 초 만에 입고를 기록할 수 있습니다.
수량(Quantity) 열을 제한할 수도 있습니다. 제한 대상 상자에서 정수를 선택하고 최소값을 1로 설정합니다. 이렇게 하면 빈 칸이나 음수 입력을 차단할 수 있습니다.
SUMIFS로 현재고 계산하기
이제 Items 테이블에 계산 열을 추가합니다. SUMIFS는 여러 조건을 만족하는 숫자를 합산합니다. Microsoft의 구문은 다음과 같습니다:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
"Stock in"이라는 열을 추가합니다. 첫 번째 셀에 다음을 입력합니다:
=SUMIFS(Movements[Quantity],Movements[Item ID],[@[Item ID]],Movements[Type],"In")
이렇게 하면 품목 ID가 행과 일치하고 유형이 In인 Movements 테이블의 모든 수량을 합산합니다. 테이블은 모든 품목에 대해 수식을 자동으로 아래로 채웁니다.
동일한 수식으로 "Stock out" 열을 추가하되, "In"을 "Out"으로 변경합니다.
그런 다음 "On hand"를 추가합니다:
=[@[Opening stock]]+[@[Stock in]]-[@[Stock out]]
마지막으로 "Stock value"를 추가합니다:
=[@[On hand]]*[@[Unit cost]]
수식을 작성한 후 한 품목을 수동으로 확인해 보세요. Movements 테이블을 해당 품목으로 필터링하고, In 및 Out 행을 합산한 다음, 그 결과를 On hand 열과 비교합니다.
재주문 품목 표시하기
재고 추적기는 무엇을 해야 할지 알려줄 때 가장 유용합니다. 다음 수식으로 "Status" 열을 추가합니다:
=IF([@[On hand]]<=[@[Reorder point]],"Reorder","OK")
이제 각 행에 Reorder 또는 OK가 표시됩니다. 주문이 도착하는 데 걸리는 시간과 그 동안 사용하는 양을 기준으로 재주문 시점을 설정하세요.
조건부 서식을 사용하여 Reorder 행이 눈에 띄게 만듭니다:
- Status 열을 선택합니다.
- 홈 > 조건부 서식 > 셀 강조 규칙 > 텍스트 포함을 선택합니다.
- Reorder를 입력하고 연한 빨강과 같은 채우기 색상을 선택합니다.
- 확인을 선택합니다.
Microsoft는 텍스트 포함 대화 상자가 나타나면 "적용할 서식 옵션을 입력하고 확인을 클릭한다"고 설명합니다. 강조 표시는 재고가 변경될 때마다 업데이트됩니다.
주문을 하기 전에 Status 열을 정렬하거나 필터링하여 Reorder 행만 볼 수도 있습니다.
XLOOKUP으로 품목 세부 정보 찾기
변동 로그에는 품목 ID가 표시되므로 읽기가 어려울 수 있습니다. 조회 열을 추가하면 각 변동 내역 옆에 품목명이 표시됩니다.
Microsoft는 XLOOKUP을 "행 기준으로 테이블이나 범위에서 항목을 찾는" 방법으로 설명합니다. 구문은 다음과 같습니다:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Movements 테이블에 "Name" 열을 추가하고 다음을 입력합니다:
=XLOOKUP([@[Item ID]],Items[Item ID],Items[Name],"Unknown ID")
존재하지 않는 ID를 입력하면 셀에 "Unknown ID"가 표시됩니다. 이를 통해 오류를 쉽게 찾아낼 수 있습니다.
Microsoft는 "Excel 2016 및 Excel 2019에서는 XLOOKUP을 사용할 수 없다"고 명시하고 있습니다. 해당 버전에서는 INDEX와 MATCH를 사용하거나, 첫 번째 열에 품목 ID가 있는 VLOOKUP을 사용하세요.
상단에 요약 추가하기
재고 추적기의 마지막 부분은 요약입니다. 간단한 요약은 스크롤하지 않고도 누구나 재고 상태를 확인할 수 있도록 도와줍니다. Items 테이블 위나 별도의 "Summary" 시트에 몇 개의 셀을 추가하세요.
다음 수식을 사용합니다:
- 총 재고 가치:
=SUM(Items[Stock value]) - 재주문할 품목 수:
=COUNTIF(Items[Status],"Reorder") - 이번 달 출고 수량:
=SUMIFS(Movements[Quantity],Movements[Type],"Out",Movements[Date],">="&DATE(2026,11,1))
COUNTIF는 간단한 패턴을 따릅니다. Microsoft의 표현을 빌리자면, "어디를 찾을 것인가?"와 "무엇을 찾을 것인가?"를 묻는 것입니다.
카테고리별 재고 가치를 나타내는 막대형 차트를 추가할 수도 있습니다. 요약 범위를 선택하고 삽입 > 차트를 선택합니다. 요약은 중요하게 다룰 3~5개의 숫자만 포함하여 작게 유지하세요.
실제 적용 예시: 사무용품 비품실
다음은 실제로 사용되는 재고 추적기의 전체 예시입니다. 소규모 사무실에서 5개의 소모품 품목을 추적합니다. 11월 이전의 Items 테이블은 다음과 같습니다:
| 품목 ID | 이름 | 단가 | 기초 재고 | 재주문 시점 |
|---|---|---|---|---|
| A-101 | 노트 A5 | $2.50 | 120 | 50 |
| A-102 | 볼펜 (상자) | $6.00 | 40 | 20 |
| B-201 | 복사지 (묶음) | $4.20 | 80 | 30 |
| B-202 | 토너 카트리지 | $45.00 | 12 | 5 |
| C-301 | 배송용 상자 (중) | $0.80 | 300 | 100 |
11월 한 달 동안 Movements 테이블에 다음과 같은 변동 사항이 기록됩니다. 노트: 출고 85개 및 입고 50개. 볼펜: 출고 25개. 복사지: 출고 60개 및 입고 40개. 토너: 출고 9개. 상자: 출고 180개.
수식이 실행된 후 계산된 열은 다음과 같이 표시됩니다:
| 품목 ID | 입고량 | 출고량 | 현재고 | 재고 가치 | 상태 |
|---|---|---|---|---|---|
| A-101 | 50 | 85 | 85 | $212.50 | OK |
| A-102 | 0 | 25 | 15 | $90.00 | Reorder |
| B-201 | 40 | 60 | 60 | $252.00 | OK |
| B-202 | 0 | 9 | 3 | $135.00 | Reorder |
| C-301 | 0 | 180 | 120 | $96.00 | OK |
이 예시는 변동 로그가 왜 중요한지도 보여줍니다. 복사지는 60묶음으로 문제가 없어 보이지만, 로그를 보면 한 달 동안 60묶음이 출고되었습니다. 이 속도가 유지된다면 복사지는 몇 주 안에 재주문 시점에 도달할 것입니다. 단일 수량 열만 사용했다면 이러한 추세를 파악할 수 없었을 것입니다.
한 행을 수동으로 확인해 보겠습니다. 노트는 120개로 시작하여 50개가 늘고 85개가 줄었으므로, 120 + 50 − 85 = 85가 됩니다. 이는 현재고 열과 일치합니다.
요약에는 총 재고 가치 $785.50와 재주문할 품목 2개가 표시됩니다. 펜과 토너는 모두 재주문 시점 이하이므로 이번 주 주문 대상에 포함됩니다.
AI를 활용하여 Excel에서 재고 추적기 만들기
수동으로 추적기를 구축하려면 1~2시간이 걸립니다. 가장 시간이 오래 걸리는 부분은 대개 데이터 작업입니다. 포스(POS) 시스템에서 내보낸 데이터를 정리하고, 품목명을 일치시키고, 중복된 ID를 수정하는 작업 등입니다.
AI 도구를 사용하면 이러한 정리와 분석 작업을 대신 처리할 수 있습니다. Powerdrill Bloom을 사용하여 3단계로 진행하는 방법은 다음과 같습니다.
1단계: Excel, CSV 또는 PDF 파일 업로드하기
재고 내보내기 파일, 공급업체 목록 또는 지난달 실사 시트를 업로드합니다. Excel AI Assistant는 스프레드시트 파일과 직접 연동됩니다.
품목 목록과 변동 내역이 있다면 별도의 파일이더라도 두 가지를 모두 포함하여 업로드하세요.
2단계: 자연어로 추적기 생성 요청하기
필요한 사항을 설명합니다. 예를 들어 "현재고, 재고 가치, 그리고 수량이 20개 이하인 품목에 대한 재주문 표시가 포함된 재고 추적기를 만들어 줘"라고 입력합니다. 그리고 이번 주에 주문해야 하는 품목 목록을 나열해 달라고 요청하세요.
어떤 카테고리에 가장 많은 자금이 묶여 있는지와 같은 후속 질문을 할 수도 있습니다.
3단계: 결과 검토 및 내보내기
몇 가지 품목의 현재고 수량을 마지막 실사 수량과 대조하여 확인합니다. 그런 다음 정리된 테이블을 다시 Excel로 내보내거나, 분석 결과를 팀을 위한 간단한 보고서나 슬라이드로 변환합니다.
이는 재고 추적기 업데이트가 지연되었을 때 유용합니다. 판매 시스템에서 매월 내보낸 데이터를 통해 실제로 출고된 수량을 확인하고, 이를 로그와 비교할 수 있습니다.
일일 업데이트를 위해서는 두 개의 테이블 설계를 유지하세요. 빠른 정리나 월간 검토가 필요할 때 AI 단계를 활용하십시오.
피해야 할 흔한 실수들
재고 추적기에서 발생하는 대부분의 문제는 수식이 아니라 사람들이 데이터를 입력하는 방식에서 비롯됩니다. 다음은 Excel 재고 추적기에서 가장 자주 발생하는 문제들입니다.
- 수량 덮어쓰기. 대신 모든 변경 사항을 새로운 변동 내역으로 기록하세요.
- ID 대신 이름 사용하기. "펜"과 "펜 상자"가 서로 다른 두 품목이 될 수 있습니다. 고유한 ID를 사용하세요.
- 기초 재고 누락. 시작 수량이 없으면 모든 현재고 수치가 잘못됩니다.
- 단위 혼용. 동일한 품목에 대해 상자 단위와 개별 수량 단위를 혼용하지 말고 하나만 추적하세요.
- 정기 실사 미실시. 매월 또는 매 분기마다 추적기와 실제 재고를 비교해 보세요.
- 재주문 시점 미검토. 사용량이나 공급업체의 리드 타임이 변경되면 재주문 시점을 업데이트하세요.
- 로그 행 삭제. 실수를 바로잡기 위해 변동 내역을 삭제하지 마세요. 대신 비고와 함께 수정 항목을 추가하십시오.
- 수식 위에 직접 입력. 아무도 수식을 숫자로 대체하지 못하도록 Items 시트를 보호하세요.
선반에 너무 오래 머물러 있는 품목을 찾으려면 장기 미적체 재고를 파악하는 방법 가이드를 참조하세요. 수요 예측에 대해서는 재고 및 수요 예측을 위한 AI 도구 목록을 확인해 보십시오.
재고 데이터가 정리되지 않은 내보내기 파일 형태로 되어 있다면, Powerdrill Bloom을 사용하여 즉시 사용 가능한 추적기로 깔끔하게 정리해 보세요.
자주 묻는 질문
Excel에서 간단한 재고 추적기를 만들려면 어떻게 해야 하나요?
품목 ID, 이름, 단가, 기초 재고, 재주문 시점이 포함된 Items 테이블을 만듭니다. 각 변동 사항을 기록하는 Movements 테이블을 추가합니다. 그런 다음 SUMIFS를 사용하여 입고량, 출고량, 현재고를 계산합니다.
Excel에서 현재고를 계산하는 수식은 무엇인가요?
현재고는 기초 재고에 입고량을 더하고 출고량을 뺀 값과 같습니다. SUMIFS를 사용하여 변동 로그에서 각 품목의 In 및 Out 수량을 합산합니다.
Excel에서 재고 부족을 표시하려면 어떻게 해야 하나요?
현재고가 재주문 시점 이하일 때 "Reorder"를 반환하는 IF 수식으로 Status 열을 추가합니다. 그런 다음 조건부 서식을 사용하여 해당 셀을 강조 표시합니다.
재고 추적기는 얼마나 자주 업데이트해야 하나요?
변동 사항이 발생할 때마다, 또는 최소 하루에 한 번 기록하세요. 그런 다음 매월 또는 매 분기마다 추적기와 실제 재고를 비교하고, 차이가 있는 부분은 조정 항목을 입력하여 수정합니다.
Excel에서 여러 위치의 재고를 추적할 수 있나요?
네, 가능합니다. Movements 테이블에 위치(Location) 열을 추가하고, SUMIFS에 위치를 두 번째 조건으로 추가합니다. 그러면 각 품목의 위치별 현재고 수치를 얻을 수 있습니다.
출처: Microsoft 지원, Excel 테이블 개요 · Microsoft 지원, 셀에 데이터 유효성 검사 적용 · Microsoft 지원, SUMIFS 함수 · Microsoft 지원, XLOOKUP 함수 · Microsoft 지원, 조건부 서식을 사용하여 Excel에서 정보 강조 표시 · Microsoft 지원, Microsoft Excel에서 COUNTIF 함수 사용.