엑셀 함수로 재고 관리 자동화하는 방법 — 입출고·현재고·선입선출 수식 설계

엑셀만으로도 품목별 현재 재고수량 자동 계산, 입출고 이력 정리, 재고부족 알림까지는 함수로 충분히 자동화할 수 있습니다. 다만 선입선출(FIFO) 방식의 정확한 출고 원가 계산은 SUMIFS·VLOOKUP 같은 기본 함수만으로 완전히 자동화하기 어렵고, 로트(입고 단위)별 잔여수량을 추적하는 도우미 열을 따로 설계해야 근사적으로 구현할 수 있습니다. 이 글은 소규모 사업자·1인 셀러가 실제로 쓸 수 있는 재고 시트 구조와 함수 설계 방법을 정리했습니다.

전제조건 — 어떤 상황에 적합한가

아래에서 다루는 방식은 모든 사업 규모에 맞는 것은 아닙니다. 취급 품목(SKU) 수가 많지 않고, 입력하는 사람이 한두 명이며, 하루 입출고 건수가 수십 건을 넘지 않는 경우에 적합합니다. 이 조건을 넘어서면 아래에서 설명하는 함수 구조도 파일이 무거워지고 오류가 늘어나는데, 그 시점의 판단 기준은 이 글의 마지막 절에서 다룹니다.

또한 이 글은 세금계산서·매입매출 신고와 연결되는 회계처리 방법을 다루지 않습니다. 여기서 설명하는 선입선출 계산은 재고 수량과 출고 원가를 파악하기 위한 실무적 근사치이며, 정식 세무 신고용 재고자산 평가 방법이 필요하다면 세무사 등 전문가의 확인을 받아야 합니다.

재고 시트 구조 설계

엑셀 재고관리가 자주 꼬이는 가장 큰 이유는 입고·출고·현재고를 한 표 안에 뒤섞어 관리하기 때문입니다. 아래처럼 거래 기록(입출고 로그) 시트와 품목마스터 시트를 분리하면 함수 설계가 훨씬 단순해집니다.

• 품목마스터 시트: 품목코드, 품목명, 재주문 기준수량 등 품목별로 한 번만 정하면 되는 정보.
• 입출고 기록 시트: 거래가 발생할 때마다 한 줄씩 추가하는 로그. 품목코드, 구분(입고/출고), 날짜, 수량, 입고단가(입고일 때만)를 기록합니다.
• 현재고 요약 시트: 품목마스터를 기준으로 SUMIFS 결과를 가져와 품목별 현재 수량을 한눈에 보여주는 표.

아래는 이해를 돕기 위한 가상 예시입니다. “무선이어폰 케이스”라는 품목 하나를 8월 한 달간 입출고했다고 가정한 기록입니다.

이 표에서 “구분” 열은 반드시 “입고”·”출고” 두 값만 들어가야 SUMIFS 조건이 정확히 맞습니다. 데이터 탭의 데이터 유효성 검사에서 목록 방식을 선택하고 원본에 “입고,출고”를 지정해 드롭다운으로 입력하게 하면, 담당자가 “임고”처럼 오타를 내서 합계가 틀리는 사고를 막을 수 있습니다.

핵심 함수로 입출고·현재고 관리하기

현재 재고수량 — SUMIFS

현재 재고수량은 품목별 입고 수량 합계에서 출고 수량 합계를 빼면 됩니다. SUMIFS는 여러 조건을 동시에 만족하는 값만 더하는 함수입니다.

=SUMIFS(수량범위,품목코드범위,"CASE-001",구분범위,"입고") - SUMIFS(수량범위,품목코드범위,"CASE-001",구분범위,"출고")

위 예시 표에 대입하면 입고 합계는 500+300=800, 출고 합계는 180+250+200=630이므로 현재 재고수량은 170입니다. 품목코드를 셀 참조로 바꿔두면(예: 현재고 요약 시트의 A열에 품목코드를 나열) 품목이 늘어나도 수식을 복사만 하면 됩니다.

품목 정보 조회 — VLOOKUP·XLOOKUP

입출고 기록에 품목코드만 입력하고, 품목명이나 재주문 기준수량은 품목마스터 시트에서 자동으로 불러오면 입력 실수가 줄어듭니다. 엑셀 2019 이하를 포함한 모든 버전에서는 VLOOKUP을 씁니다.

=VLOOKUP(A2,품목마스터!$A:$D,2,FALSE)

마지막 인수를 FALSE(정확히 일치)로 지정해야 품목코드가 정확히 같은 행만 찾습니다. 엑셀 365·엑셀 2021 이상을 쓴다면 XLOOKUP이 더 편합니다. 열 번호를 세지 않고 찾을 범위와 반환할 범위를 각각 지정하며, 찾는 값이 기준 열보다 왼쪽에 있어도 동작합니다.

=XLOOKUP(A2,품목마스터!A:A,품목마스터!B:B)

XLOOKUP은 엑셀 2016·2019(비구독형 영구 라이선스)에서는 지원되지 않으므로, 회사·거래처 PC의 엑셀 버전이 섞여 있다면 VLOOKUP으로 통일하는 편이 파일 호환성 문제를 줄입니다.

선입선출(FIFO) 출고 원가 — 근사 계산과 한계

선입선출은 먼저 들어온 재고(로트)부터 먼저 나간다고 가정하고 출고 원가를 계산하는 방식입니다. 문제는 SUMIFS나 VLOOKUP은 “합계”나 “단순 조회”는 잘하지만, “이 출고가 어느 입고 로트에서 몇 개씩 빠져나갔는지”를 순서대로 배분하는 계산은 기본 제공 함수 하나로 처리되지 않는다는 점입니다. 실무에서는 입고 로트마다 잔여수량을 계산하는 도우미 열을 추가해서 근사적으로 구현합니다.

입고 기록 행마다 아래 수식으로 “이 로트가 현재까지 얼마나 남아 있는지”를 계산할 수 있습니다.

=MAX(0, MIN(이_로트_입고수량, 이_로트까지의_누적입고수량 - 기준시점까지의_총누적출고수량))

위 가상 예시로 확인해 보면, 1차 입고(500개, 8/1)는 그 로트까지의 누적입고 500, 8/29 기준 총 누적출고 630을 대입하면 MIN(500, 500-630)=MIN(500,-130)이 되어 결과는 0 — 즉 1차 로트는 완전히 소진됐습니다. 2차 입고(300개, 8/15)는 그 로트까지의 누적입고 800, 총 누적출고 630을 대입하면 MIN(300, 800-630)=170으로, 2차 로트에 170개가 남아 있다는 계산이 나옵니다. 이는 앞서 SUMIFS로 구한 현재 재고수량 170과도 일치합니다.

다만 이 방식에도 한계가 있습니다. 로트별 “잔여수량”까지는 계산되지만, 특정 출고 한 건이 정확히 어느 로트에서 몇 개씩, 얼마의 단가로 빠져나갔는지는 출고 건마다 순서대로 수동 배분하거나 별도의 배분 로직을 추가로 설계해야 합니다. 예를 들어 8/29 출고 200개는 1차 로트 잔여 70개(1,200원)와 2차 로트 중 130개(1,350원)로 나뉘어 나간 것인데, 이 배분을 자동으로 계산하려면 누적합·순위 함수를 조합한 여러 겹의 수식이 필요해지고, 품목·거래 건수가 늘수록 파일이 무겁고 오류에 취약해집니다. SKU가 몇 개뿐이고 거래 빈도가 낮다면 이 정도 보조열로 충분하지만, 정밀한 매출원가를 매 거래마다 자동 산출해야 한다면 이 지점부터는 엑셀 수식보다 재고관리 프로그램이나 Power Query·VBA를 검토하는 편이 현실적입니다.

재고부족·재주문 알림 — IF와 조건부 서식

품목마스터에 품목별 재주문 기준수량을 정해두면, 현재 재고수량과 비교해 알림 문구를 표시할 수 있습니다.

=IF(현재재고셀<=재주문기준셀,"재주문 필요","충분")

문구뿐 아니라 시각적으로도 눈에 띄게 하려면 조건부 서식을 함께 씁니다. 홈 탭 > 조건부 서식 > 셀 강조 규칙에서 "보다 작음"을 선택하고 재주문 기준 셀을 참조로 지정하면, 기준 이하로 떨어진 품목의 셀이 자동으로 색이 바뀝니다. 수식을 몰라도 재고 요약 시트를 열자마자 부족한 품목을 바로 알아볼 수 있습니다.

함수 비교표 — 상황별로 어떤 함수를 쓰나

한계와 전용 프로그램 전환 시점

위 구조는 품목 수가 적고 입력자가 한두 명일 때는 무료로 충분히 쓸 수 있는 방법입니다. 하지만 몇 가지 구조적인 한계는 분명히 알고 시작하는 것이 좋습니다.

• 동시편집 충돌: 같은 파일을 여러 사람이 동시에 열어 수정하면 나중에 저장한 내용이 앞의 수정을 덮어쓰는 일이 생깁니다. 클라우드 공유로 일부 완화되지만 근본적으로 해결되지는 않습니다.
• SKU가 늘수록 유지관리 부담 증가: 품목이 많아질수록 SUMIFS·VLOOKUP 범위가 커지고 파일이 느려지며, 수식을 복사하는 과정에서 참조 범위가 어긋나는 실수도 늘어납니다.
• 거래 단위 감사 이력 부재: 누가 언제 어떤 셀을 고쳤는지 엑셀 자체에는 기록이 남지 않습니다. 수정 이력을 추적하려면 별도 관리가 필요합니다.
• 정밀한 FIFO 원가 자동화의 한계: 앞서 설명했듯 출고 건별 로트 배분과 매출원가 산출은 기본 함수만으로는 근사치에 머무릅니다.

이런 조건이 여러 개 동시에 해당하고 몇 달째 이어진다면, 엑셀을 더 다듬기보다 전용 프로그램으로 전환할 시점인지 판단해볼 필요가 있습니다. 공동 입력자 수, 입출고 빈도, 재고 오차 발생 빈도, SKU·거래처 수를 기준으로 전환 시점을 구체적으로 짚어본 글은 엑셀 재고관리, 언제 프로그램으로 바꿔야 할까에서 다룹니다.

출처와 확인일(2026-09-24): 이 글의 함수 문법은 Microsoft 공식 지원 문서를 직접 열람해 확인했습니다 — SUMIFS 함수, VLOOKUP 함수, XLOOKUP 함수(엑셀 2016·2019 비구독형에서는 미지원, 엑셀 365·2021 이상에서 지원됨을 확인), 드롭다운 목록 만들기(데이터 유효성 검사), 조건부 서식으로 정보 강조하기. 본문의 입출고 수량·단가·품목명은 이해를 돕기 위한 가상 예시이며 실제 거래 데이터가 아닙니다. 회사·거래처의 엑셀 버전에 따라 XLOOKUP 사용 가능 여부가 다르니, 공유 파일이라면 먼저 버전을 확인하시기 바랍니다.

---

함께 보면 좋은 글

• 엑셀 재고관리, 언제 프로그램으로 바꿔야 할까 — 전환 시점 체크리스트
• 재고관리 프로그램·SaaS 비교 — 이카운트 외 실제 요금·기능 정리
• 여러 쇼핑몰 채널 재고·주문 동기화하는 법 — API 연동형과 엑셀 일괄업로드형 비교