매번 수기로 재고 파악하느라 지치셨나요? 엑셀로 똑똑한 재고관리 시스템을 직접 만들어보면 어떨까요?
복잡한 프로그램 없이 엑셀의 기본 기능만으로도 충분히 강력한 재고관리 시스템을 구축할 수 있습니다. 제가 차근차근 알려드릴게요!
1단계: 기본 시트 3가지 준비하기
가장 먼저, 재고관리에 필요한 3개의 시트를 만들어 주세요. 각 시트의 역할은 다음과 같습니다.
1. **입출고기록**: 모든 입고와 출고 내역을 차곡차곡 기록하는 곳입니다. 데이터의 기반이 되죠.
2. **제품마스터**: 우리가 취급하는 모든 제품의 목록을 정리해두는 곳입니다. 기준 정보 역할을 합니다.
3. **재고현황**: 제품별 현재 재고를 자동으로 계산해서 한눈에 보여주는 최종 보고서입니다.
각 시트의 첫 행에 다음과 같이 항목을 입력해 보세요.
* **입출고기록 시트**: `날짜`, `구분`(입고/출고), `제품코드`, `제품명`, `수량`
* **제품마스터 시트**: `제품코드`, `제품명`, `규격`, `기초재고`
* **재고현황 시트**: `제품코드`, `제품명`, `기초재고`, `총 입고`, `총 출고`, `현재고`
2단계: 입출고 내역과 제품 정보 입력하기
이제 기본 데이터를 채울 차례입니다. ‘제품마스터’ 시트에 관리할 모든 제품의 정보를 쭉 입력해주세요. 제품코드는 중복되지 않는 고유한 값으로 만드는 것이 중요합니다.
그 다음, ‘입출고기록’ 시트에는 실제 발생한 입고, 출고 내역을 날짜순으로 기록합니다. 처음에는 몇 개의 샘플 데이터라도 입력해두면 수식을 테스트하기 편리합니다.
3단계: 핵심 함수로 재고 자동 계산하기
가장 중요한 단계입니다! ‘재고현황’ 시트에서 함수를 사용해 자동으로 재고가 계산되도록 만들어 보겠습니다.
1. **제품 정보 가져오기 (`VLOOKUP`)**: ‘재고현황’ 시트의 `제품코드`를 기준으로 ‘제품마스터’ 시트에서 `제품명`과 `기초재고`를 자동으로 불러옵니다. `B2` 셀에 아래 수식을 입력하고 아래로 쭉 채워주세요.
* `=VLOOKUP(A2, 제품마스터!A:D, 2, FALSE)`
2. **총 입고/출고량 계산하기 (`SUMIF`)**: ‘입출고기록’ 시트를 참조하여 각 제품의 총 입고량과 총 출고량을 계산합니다.
* 총 입고 (`D2` 셀): `=SUMIF(입출고기록!C:C, A2, 입출고기록!E:E)`
* 총 출고 (`E2` 셀): `=SUMIF(입출고기록!C:C, A2, 입출고기록!E:E)`
* 어? 수식이 똑같죠? ‘입출고기록’ 시트의 ‘구분’ 항목을 활용해 수정해야 합니다. 아래처럼 `SUMIFS` 함수를 사용하면 더 정확해요.
* **총 입고 (D2 셀 수정)**: `=SUMIFS(입출고기록!E:E, 입출고기록!C:C, A2, 입출고기록!B:B, “입고”)`
* **총 출고 (E2 셀 수정)**: `=SUMIFS(입출고기록!E:E, 입출고기록!C:C, A2, 입출고기록!B:B, “출고”)`
3. **현재고 계산하기**: 이제 모든 준비가 끝났습니다. 간단한 사칙연산으로 현재 재고를 구합니다.
* 현재고 (`F2` 셀): `=C2+D2-E2` (기초재고 + 총 입고 – 총 출고)
이제 ‘입출고기록’ 시트에 데이터를 추가할 때마다 ‘재고현황’ 시트의 숫자가 실시간으로 바뀌는 것을 볼 수 있습니다!
4. 데이터 유효성 검사로 실수 줄이기
사람이 직접 데이터를 입력하다 보면 오타가 발생하기 쉽습니다. ‘입출고기록’ 시트에서 데이터 유효성 검사 기능을 활용하면 이런 실수를 막을 수 있습니다.
* **구분 열**: ‘입고’, ‘출고’만 선택해서 입력할 수 있도록 목록을 만들어 보세요. [데이터] -> [데이터 유효성 검사] -> [설정] 탭에서 [제한 대상]을 ‘목록’으로 바꾸고, [원본]에 `입고,출고` 라고 입력하면 됩니다.
* **제품명 열**: ‘제품마스터’에 있는 제품명만 선택할 수 있도록 목록으로 만들면, 없는 제품을 입력하는 실수를 방지할 수 있습니다.
자주 묻는 질문 (FAQ)
Q1: 재고가 마이너스로 표시되는데, 어떻게 하죠?
A: 현재고가 음수(-)로 표시된다면, 출고량이 입고량보다 많다는 의미입니다. 대부분 ‘입출고기록’ 시트의 데이터 입력 실수일 가능성이 높습니다. 날짜, 수량, 제품명이 정확한지 다시 한번 확인해 보세요. 조건부 서식을 사용해 음수 값을 빨간색으로 표시하면 눈에 잘 띄어 관리하기 편합니다.
Q2: 새로운 제품이 추가되면 어떻게 해야 하나요?
A: 아주 간단합니다. 먼저 ‘제품마스터’ 시트 맨 아래에 새로운 제품 정보를 추가하세요. 그다음 ‘재고현황’ 시트에도 새 제품코드를 입력하고, 위에서 만들어 둔 수식 셀을 아래로 끌어 복사해주면 자동으로 계산이 적용됩니다. 엑셀의 ‘표(Table)’ 기능을 사용하면 이 과정이 더욱 편리해집니다.
Q3: VLOOKUP 함수가 너무 어려워요. 더 쉬운 방법은 없나요?
A: VLOOKUP 함수는 처음엔 조금 헷갈릴 수 있습니다. 만약 최신 버전의 엑셀(Office 365, Excel 2021 등)을 사용하신다면 훨씬 직관적인 XLOOKUP 함수를 추천합니다. `=XLOOKUP(찾을 값, 찾을 범위, 반환할 범위)` 구조로 훨씬 이해하기 쉽고 강력한 기능을 제공합니다.
이제 여러분만의 재고관리 시스템을 만들 준비가 되셨나요? 오늘 배운 내용을 바탕으로 직접 만들어보고, 업무 효율을 높여보세요! 더 궁금한 점이 있다면 언제든지 댓글로 질문해주세요
🏷️ 태그: #엑셀재고관리 #엑셀입출고관리 #재고관리양식 #엑셀자동화 #엑셀팁 #VLOOKUP #SUMIF #재고관리프로그램 #초보엑셀 #업무자동화 #소상공인필수템 #스마트워크
