엑셀 초보도 따라 하는 “수불기록 파일(피벗 활용)” 만들기 — 입력은 쉽고, 재고는 자동 집계

• 핵심 구조는 시트 2장입니다. 보관장소(수불기록)(원장)에 입·출고를 한 줄씩만 기록하고, 품목전체piv(피벗 요약)에서 재고를 자동 집계합니다.
• 운영은 “입력 → 새로고침(Refresh)” 2단계로 끝: 원장에 추가/수정 후 피벗 새로 고침(Alt+F5 / 전체 Ctrl+Alt+F5) 하면 요약이 즉시 업데이트됩니다.
• 초보에게 가장 쉬운 안정화 포인트는 ‘원장을 표(Table)로 만들기(Ctrl+T)’ 입니다. 표로 만들면 범위가 자동으로 잡혀서, 행이 늘어나도 피벗 운영이 훨씬 단순해집니다.

---

 

재고관리를 엑셀로 하다 보면 대부분 여기서 막힙니다.

• “품목이 늘어날 때마다 표를 다시 만들기 귀찮다”
• “수량 합계 수식이 꼬인다”
• “창고별 재고를 보려면 시트가 끝없이 늘어난다”
• “새 품목이 들어오면 마스터/보관장소/입고장 다 손봐야 한다”

그래서 제가 추천하는 방식은 ‘원장(수불기록)과 요약(피벗)을 분리’ 하는 것입니다.
첨부 파일도 같은 방향으로 재설계되어 있어요.

• 보관장소(수불기록) 시트: 실제로 사람이 입력/수정하는 거래 원장
• 품목전체piv 시트: 원장을 기반으로 자동 집계되는 재고 요약(피벗테이블)

이 구조의 장점은 명확합니다.
원장에만 정확히 쓰면, 요약은 새로고침 한 번으로 자동 업데이트가 됩니다.

---

1) 시트 설계의 핵심: “수불은 한 줄 = 한 건” 원칙

첨부 파일의 보관장소(수불기록)은 실무에서 가장 안전한 컬럼 구성을 갖고 있습니다. 대표적으로 아래처럼 운영하면 초보도 실수 확률이 확 떨어집니다.

• 날짜
• 품목코드
• 종류(입고/출고/주문 등)
• 품목(대분류)
• 품명 / 규격 / 단위
• 입고장소 / 출고장소
• 입출고(+/- 기록) ← 여기가 핵심
• 비고

여기서 정말 중요한 룰 2가지만 기억하면 됩니다.

 

룰 A) “입출고(+/- 기록)”은 입고는 +, 출고는 -

• 입고: +20
• 출고: -5
  이렇게 부호까지 포함해서 기록하면, 피벗은 단순히 “합계”만 내도 현재 재고가 됩니다.

룰 B) 창고이동은 2줄로 쓰면 가장 깔끔함(초보용)

• A창고에서 -10 (출고)
• B창고로 +10 (입고)
  한 줄로 이동을 표현하려고 하면 초보는 필드가 꼬이기 쉽습니다. 2줄 방식이 실수 방지에 압도적으로 유리합니다.

---

2) 초보가 반드시 해야 할 1단계: 원장 범위를 “표(Table)”로 만들기

피벗이 잘 굴러가려면 원장 범위가 흔들리면 안 됩니다.
가장 쉬운 고정 방법이 Ctrl+T로 ‘표(Table)’로 만드는 것입니다.

(따라하기)

1. 보관장소(수불기록)에서 데이터 범위 아무 셀 클릭
2. Ctrl + T
3. “머리글 포함” 체크 → 확인

이렇게 하면 좋은 점:

• 데이터가 아래로 늘어나도 표가 자동으로 범위를 잡아줌
• 피벗 원본이 “표”를 물고 있으면 운영이 안정적
  (초보가 가장 많이 하는 실수인 “피벗 원본 범위 누락”을 크게 줄입니다)

---

3) 2단계: 피벗테이블 시트(품목전체piv) 만들기

피벗 생성은 엑셀 공식 흐름 그대로 가면 됩니다.

(따라하기)

1. 원장 표(Table) 안의 아무 셀 클릭
2. 삽입(Insert) > 피벗 테이블(PivotTable)
3. 위치는 새 시트 선택 → 시트명을 품목전체piv로 지정

피벗 필드 배치(추천 예시)

• 행(ROWS): 품목코드 → 입고장소(또는 보관창고) → 품목 → 품명 → 규격 → 단위
• 값(VALUES): 입출고(+/- 기록)을 합계(SUM) 로
• 결과 컬럼은 요약(=현재고)처럼 보이게 됩니다.

이렇게 구성하면, 원장에 새 거래를 추가할수록 품목전체piv는 품목별/창고별 재고 현황으로 계속 정리됩니다.

 

---

4) 운영의 전부: “입력 → 새로고침” 10초 루틴

원장에 행을 추가/편집했으면, 이제 끝입니다.

• 피벗테이블에서 우클릭 > 새로 고침
• 또는 단축키:
  • Alt + F5: 해당 피벗 새로고침
  • Ctrl + Alt + F5: 통합문서 전체 새로고침

참고로 엑셀 버전에 따라 “자동 새로고침” 기능이 소개되기도 하지만, 특정 채널/버전(예: Insider) 중심으로 안내되는 경우가 있어 실무에선 ‘수동 새로고침 루틴’이 가장 안정적입니다.

 

---

5) 초보 실수 TOP3와 예방 팁

실수 1) 품목코드는 같은데 품명이 다르게 입력됨

• 해결: 품목코드 기준으로 품명/규격/단위를 복사해서 쓰기, 또는 데이터 유효성/드롭다운으로 통제

실수 2) 출고인데 +로 입력

• 해결: 종류=출고면 음수 룰을 팀 규칙으로 박아두기(비고에 “출고는 반드시 -” 메모 고정)

실수 3) 피벗이 새 행을 못 읽음(범위 누락)

• 해결: 원장을 표(Table) 로 만들고 피벗 원본을 표로 연결(이 글의 2단계가 정답)

---

6) 이 설계가 “VBA 없이도” 강한 이유(결론)

이 방식은 어렵게 자동화 매크로를 만들지 않아도, 실무 운영이 됩니다.

• 사람은 원장만 입력한다(단순)
• 보고서는 피벗이 자동 요약한다(강력)
• 변경/추가 후에는 Refresh 한 번이면 끝(안정)

즉, 엑셀 초보에게 중요한 “실수 방지 + 유지보수 쉬움 + 확장성” 3가지를 동시에 잡는 설계입니다.

 

엑셀 잘 사용하는 가장 중요한 방법은 초긱 설계입니다. 입력된 내용을 두번 입력 하지 않도록 설계하고 엑셀에 있는 기본 기능으로 자동화 하세요!!

 

재고관리(입출고).xlsx

0.02MB