엑셀 - 현장/공장에서 사용하는 수불대장, 관리시트

안녕하세요,

 

오늘 배워볼 실전 엑셀은 현장/공장에서 자주 사용하는 수불대장, 관리시트 만들기 입니다.

출처 - 엑셀시트

요약

• 동일한 품목 목록을 가진 두 시트(예: “재고_전”, “재고_후”)를 비교할 때는 VLOOKUP보다 SUMIF로 시트별 수량 합계를 구한 뒤 차이를 빼는 방식이 훨씬 안정적입니다.
• 비교 전용 시트에서 입고수량 = SUMIF(전시트), 현재수량 = SUMIF(후시트), 차이 = 현재–입고를 계산하면 품목별 증감이 한눈에 보입니다.
• 여기에 IF와 조건부 서식을 더하면 “일치 / 부족 / 초과” 상태를 자동 표시하는 재고 확인용·입출고 검증용 관리 시트로 바로 활용할 수 있습니다.

(근거: SUMIF는 “범위 + 조건 + 합계범위” 구조로 동작하며, 같은 품목이 여러 줄 있어도 전부 더해 주기 때문에, 한 번만 찾는 VLOOKUP보다 재고·입출고 데이터에 적합합니다.)

---

1. 어떤 상황에서 쓰는지 먼저 정리

질문 캡처처럼 보통 이런 상황입니다.

• 시트1 : 이전 버전 BOM / 출고 예정표 / 지난달 재고
• 시트2 : 실제 사용 실적 / 최신 재고
• 시트3 : 두 시트를 비교해서
  • 품목별 수량이 같은지
  • 다르다면 몇 EA 차이 나는지
    를 자동으로 보여주고 싶음

단순히 한 줄씩 눈으로 맞춰보면 사람이 틀리기 쉽고,
품목이 수십·수백 개만 돼도 사실상 수작업은 불가능합니다.
그래서 품목을 키로 잡고 시트별 수량을 합산 → 차이 계산 구조로 가져가면 관리가 굉장히 편해집니다.

---

2. 시트 구조 예시

예시로 다음과 같이 가정해 보겠습니다.

• Sheet1 : “이전 재고”
  • A열: 품목명
  • B열: 수량
• Sheet2 : “현재 재고”
  • A열: 품목명
  • B열: 수량
• Sheet3 : “비교표(관리용)”
  • A열: 품목명 목록
  • B열: 이전 재고 합계
  • C열: 현재 재고 합계
  • D열: 차이(현재–이전)
  • E열: 상태(일치/부족/초과)

A열 품목 목록은
① 한쪽 시트에서 복사해서 붙여넣고
② 필요하면 중복제거(데이터–중복 제거)로 정리해 둡니다.

---

3. SUMIF로 시트별 수량 합계 구하기

3-1. 이전 재고 합계 (B열)

Sheet3의 B2 셀에 다음 수식을 입력합니다.

=SUMIF(Sheet1!$A:$A, $A2, Sheet1!$B:$B)

의미는 다음과 같습니다.

• Sheet1!$A:$A : 품목명이 있는 범위
• $A2 : 지금 비교하고 있는 품목명
• Sheet1!$B:$B : 실제로 더할 수량 범위

즉, “Sheet1의 A열에서 A2와 같은 품목을 모두 찾아서, 그 행의 B열 수량을 합계해라”는 뜻입니다.
같은 품목이 여러 줄로 반복돼 있어도 전부 더해 주기 때문에, 출고·입고 이력처럼 행이 쌓이는 데이터에 아주 잘 맞습니다.

이 수식을 B열 아래로 쭉 복사하면,
비교시트 A열에 있는 모든 품목에 대해 이전 재고 합계가 자동으로 채워집니다.

---

3-2. 현재 재고 합계 (C열)

C2 셀에는 거의 동일한 방식으로 현재 재고를 불러옵니다.

=SUMIF(Sheet2!$A:$A, $A2, Sheet2!$B:$B)

역시 아래로 복사하면 품목별 현재 재고 합계가 C열에 채워집니다.

---

4. 차이와 상태 표시까지 한 번에

4-1. 단순 차이 값 (D열)

D2 셀에는 다음처럼 차이를 계산합니다.

=C2 - B2

• 양수(+)면 현재 재고가 이전보다 많음
• 음수(–)면 현재 재고가 줄어든 것
• 0이면 동일한 수량입니다.

---

4-2. 보기 좋은 상태 메시지 (E열)

실무에서는 단순 숫자보다 “일치 / 부족 / 초과” 같은 글자가 더 직관적입니다.
E2에 다음과 같은 수식을 넣어 볼 수 있습니다.

=IF(D2=0,
     "일치",
     IF(D2>0,
        D2 & " EA 초과",
        ABS(D2) & " EA 부족"
     )
)

• D2가 0이면 → 일치
• D2가 양수면 → “n EA 초과”
• D2가 음수면 → “n EA 부족”

이렇게 하면 시트3 한 줄만 봐도
“어떤 품목이 몇 EA 차이 나는지” 바로 파악할 수 있습니다.

원한다면 여기서 한 단계 더 나가,
조건부 서식으로 E열에서 "부족"이 들어간 셀만 빨간색 같은 규칙을 주면
월말 재고 점검이나 입출고 검증 회의 때 매우 보기 좋은 관리 시트가 완성됩니다.

---

5. 왜 VLOOKUP 대신 SUMIF가 더 좋은가?

질문 원문에는 “IF + VLOOKUP 쓰면 될 것 같은데 어떤 함수가 좋냐”는 고민이 있었는데,
재고·입출고 데이터라면 SUMIF 쪽이 구조적으로 유리합니다.

1. 동일 품목이 여러 줄일 때
   • VLOOKUP은 첫 번째로 찾은 행 하나만 가져옵니다.
   • SUMIF는 조건에 맞는 모든 행을 합계하므로,
     일자별 입고/출고가 여러 행으로 쌓이는 구조일 때 실제 수량과 맞습니다.
2. 비교 시트 재사용
   • SUMIF 구조로 만들어두면,
     월별 시트만 바꿔도 같은 비교 시트를 재활용할 수 있습니다.
   • 예를 들어 Sheet_2024_01, Sheet_2024_02 식으로 시트 이름만 바꿔주면
     동일한 패턴의 관리표를 계속 복사해서 쓸 수 있습니다.
3. 수식 가독성
   • IF(VLOOKUP(…)=…,"일치","불일치")처럼 중첩된 구조보다
   • SUMIF로 두 합계 열을 만들어 놓고 차이 = C-B로 비교하는 편이
     나중에 수정·검토할 때 훨씬 읽기 쉽습니다.

---

6. 입출고·재고 관리 시트로 확장하는 팁

• 입고 시트 / 출고 시트를 따로 두고
  • 입고합계 : SUMIF(입고!…)
  • 출고합계 : SUMIF(출고!…)
  • 재고 = 입고합계 – 출고합계
    구조로 만들면 기본적인 창고 관리 수준까지 커버됩니다.
• 월말에 실제 창고를 실사한 값(실재고)을 또 다른 시트에 적고,
  위에서 설명한 비교 시트를 이용해 장부재고 vs 실재고 차이를 한 번에 검증할 수 있습니다.

---

이 방식대로 한 번 예제 파일만 만들어 두면,
이후에는 품목 목록만 복사해 오고 시트 이름만 바꿔도
어떤 프로젝트·라인의 자재든 “이전 vs 현재” 차이를 자동으로 검증하는 관리 시트를 빠르게 만들어낼 수 있습니다.
실제 현장에서 재고·입출고 점검할 때 매우 유용하게 쓰이니,
한 번 직접 셀에 수식을 입력해 보면서 손에 익혀 두시는 것을 추천드립니다.