엑셀 재고 관리 수식 오류 해결과 Data Validation으로 데이터 잠그기

엑셀 재고 관리 파일의 수식 오류는 함수 자체보다 입력된 데이터에서 비롯되는 경우가 대부분입니다. SKU 코드를 잘못 입력하거나 공백이 끼면 SUMIFS가 0을 반환하고, 조회 함수는 #N/A를 띄우며, 숫자가 텍스트로 서식 지정되면 #VALUE!가 발생합니다. Data Validation으로 SKU 열을 잠그고 Protect Sheet로 수식 셀을 보호하면 이런 오류 대부분을 근본부터 막을 수 있습니다.

이 글에서는 데이터를 잠그는 방법, 각 오류 코드를 정확히 읽는 법, 그리고 파일을 실제로 운영할 때 필요한 요령(시트 보호, 바코드 스캐너 연결, 여러 창고 관리, 새 회계연도로 자료 이월)을 자세히 다룹니다. 아직 파일을 만들지 않았다면 먼저 4개 시트로 엑셀 재고 관리 파일 만드는 법을 참고하세요.

1. 파일 구조 간단 정리

4개 시트 구성대로 파일을 만들었다면, 뒤에 나오는 수식의 참조 열은 아래 표와 일치합니다. 열 이름을 다르게 지정했다면 수식의 셀 주소를 그에 맞게 바꿔야 합니다.

데이터는 한 방향으로 흐릅니다. Catalog 시트가 Inbound와 Outbound 시트에 드롭다운 목록을 제공하고, 두 시트가 SUMIFS를 통해 Summary 시트로 수치를 보냅니다. 보관 장소가 두 곳 이상이라면 Storage Location 열을 채우기 전에 체계적인 창고 정리 원칙에 따라 위치 코드 규칙부터 통일하세요.

2. Data Validation으로 데이터 잠그기

SKU를 잘못 입력하는 것이 집계표에 심각한 오차가 생기는 가장 큰 원인입니다. 공백 하나가 더 들어가거나 거래 기록에서 즉석으로 새 코드를 만들면, SUMIFS가 그 행을 아무 경고 없이 건너뛰기 때문입니다. Data Validation은 오타나 임의로 만든 코드가 들어갈 여지를 차단해, 입력할 때마다 상품 목록에 이미 등록된 코드를 고르도록 강제합니다. SKU 코드 설계와 목록 표준화는 상품 목록 만들기 단계에서 다룹니다.

SKU 드롭다운 목록 설정하기

1. Inbound와 Outbound 시트의 SKU Code 열 전체를 선택합니다.
2. 도구 모음에서 Data 탭 > Data Validation을 선택합니다.
3. Allow에서 List를 고릅니다.
4. Source에 참조 수식을 입력합니다: =Catalog!$A$2:$A$100（상품 목록을 이미 엑셀 표로 만들었다면 =INDIRECT("Table_Catalog[SKU Code]")）.
5. Error Alert 탭으로 옮겨 Stop 스타일을 고르고 메시지를 입력합니다: "목록에 없는 SKU입니다. 다시 확인해 주세요!". OK를 눌러 마칩니다.

상품 목록에서 중복 코드 차단하기

거래 시트의 드롭다운을 잠그는 것만으로는 문제의 절반만 해결됩니다. 상품 목록 자체에 같은 SKU가 두 줄 있으면, 뒤따르는 모든 조회 수식이 원인도 모른 채 틀어지기 때문입니다. 상품 목록 시트에서 SKU Code 열을 선택하고 Data 탭 > Data Validation > Allow에서 Custom을 고른 뒤 수식 =COUNTIF($A:$A, A1)<=1을 입력하세요. 이미 입력된 코드를 다시 넣는 순간 엑셀이 막아 줍니다.

3. 자동화 수식과 오류 처리 방법

입출고 파일이 자동으로 돌아가게 하는 핵심 함수는 세 가지입니다. SUMIFS(조건에 맞는 수치 합산), XLOOKUP(SKU로 상품명 조회), 그리고 IF와 조건부 서식의 조합(재고 부족 경고)입니다. 이 세 함수보다 중요한 것은 수식이 예상 밖의 결과를 낼 때 오류 코드를 제대로 읽는 능력입니다. 대부분의 사용자가 바로 이 지점에서 포기하고 수기 기록으로 돌아가기 때문입니다.

핵심 수식 세 가지

• 기말 재고 — Summary 시트 G2 셀: =D2+E2-F2 (기초 재고 + 입고 합계 − 출고 합계).
• 입고 합계 — E2 셀: =SUMIFS(Inbound!$F:$F, Inbound!$C:$C, A2)로, Inbound 시트 C열의 SKU가 A2 셀과 일치하는 행의 F열 수량을 모두 더합니다.
• 출고 합계 — F2 셀: =SUMIFS(Outbound!$F:$F, Outbound!$C:$C, A2). 같은 원리로 Outbound 시트에서 가져옵니다.
• 상품명 조회 — Inbound 시트 D2 셀: =XLOOKUP(C2, Catalog!$A:$A, Catalog!$B:$B, "이름 없음", 0). XLOOKUP이 없는 Excel 2019/2016에서는 =IFERROR(VLOOKUP(C2, Catalog!$A:$E, 2, FALSE), "이름 없음")을 쓰세요.
• 상태 경고 — I2 셀: =IF(G2<=0, "재고 없음", IF(G2<=H2, "재주문 필요", "안전")). 그다음 조건부 서식 > 셀 강조 규칙 > 텍스트 포함으로 상태별 색을 지정합니다.

세 함수 외에 더 넓은 함수 목록이 필요하다면 엑셀 재고 관리 글에 정리되어 있습니다.

자주 보는 오류 코드 세 가지 읽기

• #N/A 오류: 조회 함수(XLOOKUP, VLOOKUP)가 상품 목록에서 해당 SKU를 찾지 못한 경우입니다. 수식을 IFERROR(수식, "대체 값")으로 감싸거나, 그 코드가 목록에 등록되어 있는지 확인하세요.
• #VALUE! 오류: 계산식이 공백이나 문자가 든 셀을 더하거나 빼려 한 경우입니다. 입출고 시트의 수량 셀이 모두 텍스트(Text)가 아니라 숫자(Number) 서식인지 확인하세요.
• #REF! 오류: 행, 열 또는 시트가 삭제되어 수식이 참조를 잃은 경우입니다. 구조가 되는 열은 바로 삭제하지 말고, 실수로 지웠다면 즉시 Ctrl + Z로 되돌리세요.
• 입고가 있었는데도 SUMIFS가 0을 반환할 때: 가장 흔한 원인은 두 시트의 데이터 서식이 다르거나, 한쪽 SKU 끝에 공백이 붙어 있는 경우입니다. 비교 전에 =TRIM()으로 문자열을 정리하세요.

4. 오차 관리와 파일 보호

스프레드시트 위험에 관해 널리 인용되는 연구에 따르면, 중대형 스프레드시트 대부분에는 사람이 만든 오류가 들어 있습니다. 꼭 필요한 방어선은 두 가지입니다. 수치를 정기적으로 대조하는 것, 그리고 직원이 실수로 수식을 망가뜨리지 못하도록 파일을 잠그는 것입니다.

재고 수치 3자 대조

• 장부 재고(Book Inventory): 입출고 증빙을 바탕으로 엑셀 파일에서 계산된 수량입니다.
• 실물 재고(Physical Inventory): 창고 선반에서 직접 센 수량입니다.
• 가용 재고(Available Inventory): 실물 재고에서 이미 주문이 들어와 포장을 기다리는 수량을 뺀 값입니다.

고가 상품군에는 주 단위나 월 단위로 순환 재고조사(Cycle Counting)를 적용해, 연말 전수 조사를 기다리지 말고 차이가 생기는 즉시 찾아내세요.

수식 보호를 위한 시트 잠그기

자동 수식이 들어 있는 셀에 직원이 실수로 숫자를 덮어쓰는 것이 계산 열 전체가 깨지는 흔한 원인입니다. 잠그는 방법:

1. 시트 전체를 선택하고 Ctrl + 1을 누른 뒤 Protection 탭에서 Locked 체크를 해제합니다.
2. 수식이 든 열만 따로 선택하고(집계 시트의 D, E, F, G, I열) Ctrl + 1을 누른 뒤 Protection 탭에서 Locked를 체크합니다.
3. Review 탭 > Protect Sheet를 선택하고 비밀번호를 설정해, 잠기지 않은 셀에만 입력할 수 있게 합니다.

여기에 OneDrive나 SharePoint를 더하면 실시간 공동 작업과 자동 버전 기록이 가능해져, 문제가 생겼을 때 빠르게 복구할 수 있습니다. 여러 사람이 사내망으로 파일을 저장하며 서로 덮어쓰는 상황에서 특히 유용합니다.

5. 운영 고급 요령

아래 작업들은 보통 몇 달 실제로 운영한 뒤, 사용자가 늘고 거래량이 많아질 때 필요해집니다.

• 날짜를 올바르게 입력하기: 전표를 만들 때 Ctrl + ; 단축키로 오늘 날짜를 고정값으로 넣으세요. 기록에 =TODAY()를 직접 쓰면 파일을 다시 열 때마다 날짜가 바뀌어 예전 전표까지 모두 틀어집니다.
• 한 파일로 여러 창고 관리하기: 상품 목록·입고·출고 시트에 "창고 코드" 열을 추가한 뒤, SUMIFS에 두 조건(SKU Code와 창고 코드)을 함께 걸어 지점별로 집계하세요.
• 유통기한 관리하기: 입고 시트에 "유통기한" 열을 추가하고 DATEDIF와 조건부 서식을 함께 써서, 30일 미만 남았거나 이미 지난 로트를 자동으로 표시하게 하세요.
• 출고 전표 자동 인쇄하기: 표준 출고 전표 양식대로 "전표인쇄" 시트를 하나 만들고, XLOOKUP이나 FILTER로 특정 전표 번호의 품목만 인쇄 영역에 불러오세요.
• 새 회계연도로 자료 이월하기: 연말에 파일 사본을 새로 만들고, 지난해 "Closing Stock" 열을 복사해 새해 "Opening Stock" 열에 값으로 붙여넣기(Paste Special as Values)한 뒤, 두 입출고 시트의 지난 거래 행을 비웁니다.
• 바코드 스캐너 연결하기: USB나 블루투스 방식의 일반 바코드 리더는 키보드처럼 동작하므로, 포장지의 코드를 스캔하면 선택된 셀에 SKU가 입력되고 Enter까지 자동으로 눌립니다. 별도 소프트웨어가 필요 없습니다. 엑셀 셀에 적는 대신 스캔으로 재고를 실시간 차감하고 싶다면, 그때가 창고 관리 시스템(WMS)이 필요한 시점입니다.
• 여러 명이 함께 입력하도록 공유하기: 파일을 OneDrive나 SharePoint에 올리고 AutoSave를 켜면, 권한을 받은 구성원이 Excel for Web에서 동시에 입력해도 서로 덮어쓰지 않습니다.
• 구글 스프레드시트에서 쓰기: SUMIFS, IF, IFERROR와 Data Validation은 구글 스프레드시트에서도 동일하게 작동해, 플랫폼을 바꿔도 충돌이 없습니다.

6. 한계가 소프트웨어인가, 공간인가?

파일이 한계에 다다랐음을 보여주는 구체적인 신호(SKU 수, 하루 주문량, 로트와 유통기한 관리 필요)와 엑셀 대 WMS 소프트웨어 비교표는 엑셀 재고 관리 글에서 다룹니다. 아래에서는 다른 상황, 즉 문제가 도구가 아니라 물건을 둘 물리적 공간일 때를 이야기합니다.

실제로 창고 관리의 병목은 스프레드시트가 아니라 보관 공간이 모자라서 생기는 경우가 많습니다. 사무실이나 집이 물건으로 들어차면, 엑셀에는 재고가 있다고 나와도 담당자가 상품을 찾지 못합니다.

연말 대규모 세일처럼 계절에 따라 재고가 크게 출렁이거나, 3~5년짜리 전통적인 창고 임대 계약을 아직 약속하기 어렵다면, 유연한 완충 공간을 찾는 것이 압박을 더는 가장 효과적인 방법입니다.

밖으로 옮겨야 할 재고가 몇 세제곱미터인지 모르시겠다면, 짐 사진을 찍어 MyStorage의 AI 사이즈 추정기로 확인해 보세요:

호찌민시의 온라인 판매자와 중소기업을 위해 MyStorage는 셀프스토리지와 온도가 관리되는 미니 창고를 제공합니다. 개인 보관 유닛은 1m³부터 10m³ 이상까지 월 단위로 빌릴 수 있으며, 전통적인 창고처럼 장기 계약에 묶이지 않고 사업 주기에 맞춰 늘리거나 줄일 수 있습니다.

규모별 재고 관리 도구 비용

관리 도구에 드는 비용은 상품 수와 관리할 창고 수에 따라 올라갑니다:

위 소프트웨어 가격은 일반적인 시장 참고가입니다. 소프트웨어가 아니라 물리적 공간이 부족한 것이 문제라면 MyStorage 개인 창고 요금표를 보거나 내 크기에 맞는 정확한 요금을 확인하세요.

보관 방식, 크기, 온도 옵션을 선택하면 오늘의 MyStorage 요금을 바로 확인하고 혜택과 함께 예약할 수 있습니다:

정리

탄탄한 엑셀 재고 관리 파일은 네 시트를 제대로 구성하는 데서 끝나지 않고, 세 겹의 보호가 더 필요합니다. 입력 단계에서 잘못된 코드를 막는 Data Validation, 포기하지 않고 스스로 고칠 수 있도록 오류 코드를 정확히 이해하는 것, 그리고 함께 쓰는 사람들이 서로의 데이터를 망가뜨리지 않도록 하는 시트 잠금과 클라우드 저장입니다. 이 세 가지를 갖추면 사업 규모가 정말로 커질 때까지 엑셀 파일로 충분히 버틸 수 있습니다.

걸림돌이 도구가 아니라 물건을 둘 공간이라면, 스프레드시트에 MyStorage의 셀프스토리지를 더하거나 MyStorage에 문의해 알맞은 유닛 크기를 상담받으세요.

자주 묻는 질문