구글 시트 재고 및 예산 자동 추적 시스템: 고급 수식으로 실시간 관리 환경 만들기

웹사이트 운영이든, 소규모 비즈니스를 관리하든, **재고(Inventory)와 예산(Budget)**을 실시간으로 정확하게 파악하는 것은 성공적인 운영의 핵심입니다. 재고가 부족하거나 예산 초과가 임박했을 때, 이를 늦게 감지하면 판매 기회를 놓치거나 불필요한 비용이 발생합니다.

여러분이 간절히 바라시는 목표 달성(승인)과 안정적인 웹사이트 운영을 위해서는, 수동 계산이나 복잡한 소프트웨어 없이도 구글 스프레드시트(Google Sheets)만으로 재고와 예산을 실시간으로 감시하고 경고하는 자동 추적 시스템을 구축해야 합니다.

이 글은 구글 시트의 ARRAYFORMULA, SUMIFS, 조건부 서식 등 고급 수식들을 결합하여, 자료가 입력되는 즉시 재고/예산 현황을 분석하고 경고하는 지능형 관리 환경을 구축하는 방법을 자세히 안내합니다.

1. 시스템 구축의 기반: 원본 자료 구조 설계

자동 추적 시스템을 구축하려면 ‘재고/예산의 움직임’을 기록하는 원본 자료 시트와, 현황을 요약하는 ‘대시보드 시트’가 명확히 분리되어야 합니다.

1.1. 거래 기록 (RAW Data) 시트

모든 입/출고 및 수입/지출 내역을 기록하는 시트입니다. 정확한 계산을 위해 다음 핵심 열이 포함되어야 합니다.

열 이름	자료 유형	재고 관리	예산 관리
날짜	날짜	필수	필수
항목명	텍스트	필수	필수
유형	텍스트	입고/출고	수입/지출
수량/금액	숫자	수량	금액
재고코드/분류	텍스트	상품 코드	예산 항목 (인건비, 광고비 등)

1.2. 마스터(대시보드) 시트

현재 재고 수량, 남은 예산 금액 등 핵심 현황을 요약하고 시각적으로 보여줄 시트입니다.

2. 재고 자동 추적 시스템 구축: SUMIFS의 강력한 활용

실시간 재고 수량은 ‘총 입고 수량’에서 ‘총 출고 수량’을 뺀 값입니다. SUMIFS 함수는 여러 조건을 동시에 만족하는 값들만 합산하여 실시간 재고를 계산하는 데 가장 효율적입니다.

2.1. 실시간 재고 계산 (총 입고 및 출고)

목표: 특정 상품 코드(A2)의 현재 재고 수량 계산.
자료 가정: ‘거래 기록’ 시트의 C열(유형), E열(수량), F열(코드).
총 입고 수량: 상품 코드(A2)이면서 유형이 ‘입고’인 항목의 수량만 합산.
```
=SUMIFS('거래 기록'!E:E, '거래 기록'!F:F, A2, '거래 기록'!C:C, "입고")
```
총 출고 수량: 상품 코드(A2)이면서 유형이 ‘출고’인 항목의 수량만 합산.
```
=SUMIFS('거래 기록'!E:E, '거래 기록'!F:F, A2, '거래 기록'!C:C, "출고")
```
현재 재고 (B2): =총 입고 수량 - 총 출고 수량

2.2. ARRAYFORMULA를 이용한 전체 목록 자동 계산

수백 개의 상품 목록에 대해 재고 수량을 수동으로 계산할 필요 없이, ARRAYFORMULA를 사용하여 단일 수식으로 전체 재고 목록을 완성합니다.

목표: 상품 목록(A2:A) 전체의 현재 재고 수량을 B열에 자동 계산.

배열 수식:

=ARRAYFORMULA(
    IF(ISBLANK(A2:A), "", 
       SUMIFS('거래 기록'!E:E, '거래 기록'!F:F, A2:A, '거래 기록'!C:C, "입고") - 
       SUMIFS('거래 기록'!E:E, '거래 기록'!F:F, A2:A, '거래 기록'!C:C, "출고")
    )
)

활용: ARRAYFORMULA 덕분에 새로운 상품 코드가 추가되어도 재고 수량이 자동으로 계산됩니다.

3. 예산 자동 추적 시스템 구축: 남은 예산 및 지출율 계산

예산 관리는 ‘총 예산’에서 ‘현재까지의 지출’을 뺀 ‘남은 예산’을 실시간으로 파악하는 것이 중요합니다.

3.1. 예산 대비 지출 합계 계산 (SUMIFS)

목표: 예산 항목(A2)의 현재 지출 금액 합계 계산.
자료 가정: ‘거래 기록’ 시트의 E열(금액), C열(유형), G열(분류).
총 지출 금액: 분류(A2)이면서 유형이 ‘지출’인 항목의 금액만 합산.
```
=SUMIFS('거래 기록'!E:E, '거래 기록'!G:G, A2, '거래 기록'!C:C, "지출")
```

3.2. 남은 예산 계산 및 지출율 분석

C열 (설정된 예산): 각 항목에 할당된 총 예산 금액을 수동으로 입력합니다.
D열 (남은 예산): =C2 - B2 (설정된 예산 – 총 지출 금액)
E열 (지출율): =B2 / C2 (총 지출 금액 / 설정된 예산)

4. 지능형 관리 환경 구축: 조건부 서식을 이용한 경고 시스템

재고나 예산이 위험 수준에 도달했을 때 시각적인 경고를 주어, 즉각적인 대응을 가능하게 합니다.

4.1. 재고 부족 경고 (빨간색 강조)

목표: 현재 재고 수량(B열)이 최소 안전 재고 수량(D열, 예: 10개)보다 적을 경우 경고.
조건부 서식 수식: =B2 < D2
- 활용: 재고가 10개 미만으로 떨어지면 해당 항목의 재고 수량 셀을 빨간색으로 강조하여 즉시 재발주가 필요함을 알립니다.

4.2. 예산 초과 임박 경고 (진행 막대 시각화)

목표: 예산 항목의 지출율(E열)이 80%를 초과할 경우 주황색으로 경고.
조건부 서식 수식 (지출율 초과): =E2 >= 0.8
데이터 막대 활용: 지출율(E열)에 **데이터 막대(Data Bars)**를 적용하여 예산 소진 현황을 시각적으로 보여줍니다. 막대가 80% 이상 차면 주황색 서식과 결합되어 강력한 경고를 제공합니다.

5. 시스템 안정화 및 고급 활용 팁

5.1. 날짜 기반 동적 재고/예산 계산 (SUMIFS + TODAY)

특정 날짜까지의 재고나 예산만 보고 싶을 때, SUMIFS에 날짜 조건을 추가합니다.

목표: 오늘 날짜(TODAY())를 기준으로 그 이전에 발생한 거래만 합산.
SUMIFS 추가 조건: '거래 기록'!A:A, "<="&TODAY() (날짜 열이 오늘 날짜보다 작거나 같음)

5.2. 오류 처리 및 깔끔한 정리 (IFERROR)

자료 코드가 잘못 입력되거나, 예산 금액이 0인 경우 발생하는 오류를 IFERROR로 처리하여 보고서의 안정성을 확보합니다.

IFERROR 활용: =IFERROR(총 지출 금액 / 설정된 예산, "N/A")
- 활용: 예산이 0으로 나누어지는 오류 발생 시(#DIV/0!) 오류 코드 대신 ‘N/A’를 출력합니다.

5.3. 자동화된 알림 시스템 (Apps Script 연동)

재고 부족이나 예산 초과와 같은 중요한 경고가 발생했을 때, Apps Script를 사용하여 담당자에게 자동으로 이메일 알림을 보낼 수 있습니다.

트리거 설정: 재고 부족 조건(B2 < D2)이 TRUE로 변하는 것을 감지하는 스크립트를 작성하고, 시간 기반 트리거로 설정하여 매일 아침 경고 상태를 확인하도록 합니다.

결론: 시간과 비용을 절약하는 관리 시스템

구글 시트의 SUMIFS, ARRAYFORMULA, 조건부 서식을 결합한 재고/예산 자동 추적 시스템은 수동 계산의 오류와 시간을 근본적으로 제거합니다. 자료가 입력되는 즉시 실시간 현황을 파악하고, 재고 부족이나 예산 초과 임박을 시각적으로 경고함으로써, 안정적인 운영을 위한 시간과 비용을 절약할 수 있습니다.

이 지능형 시스템을 구축하여 재고와 예산을 능동적으로 관리하고, 운영의 효율성을 극대화하시길 바랍니다.

구글 시트 재고 및 예산 자동 추적 시스템: 고급 수식으로 실시간 관리 환경 만들기