구글 시트 발주수량 자동 계산｜10명이 함께 써도 기준이 안 흔들리는 설계

여러 파일의 열을 맞춘 뒤 정상행, 중복행, 오류행으로 자동 분리합니다.

구글 시트에서 발주수량을 자동 계산하려면 수식 하나를 만드는 것보다 먼저 ‘입력 → 기준표 → 계산 → 검토 → 대시보드’를 분리해야 합니다. 여러 사람이 같은 파일을 쓰는 순간부터 문제는 계산식 자체가 아니라 기준값 변경, 예외 처리, 잘못된 입력, 시즌별 단가 교체, 누가 무엇을 수정했는지 추적하는 운영 구조로 바뀝니다.

특히 구매·발주 업무는 품목별 포장단위, 최소발주수량, 시즌 단가, 안전재고, 현재고, 미입고 수량이 동시에 얽히기 때문에 “필요수량 = 목표재고 – 현재고” 같은 한 줄 수식으로 끝내면 금방 무너집니다. 아래 구조로 만들면 소수 사용자부터 10명 안팎의 공동 사용까지 비교적 안정적으로 확장할 수 있습니다.

Table of Contents

Toggle

먼저 결론: 발주 자동계산 시트는 5개 층으로 나누는 게 안전합니다

구역	역할	사람이 수정?	자동화 핵심
입력	품목, 현재고, 예상수요, 미입고 등 원본	예	필수값·형식 검증
기준표	단가, 포장단위, 최소발주수량, 안전재고	관리자만	시즌·거래처·품목별 기준 분리
계산	권장 발주수량, 단가, 발주금액	아니오	수식·조회·반올림 규칙
검토	기준 누락, 비정상 수량, 신규 품목	검토 후 처리	자동 확정 금지 항목 분리
대시보드	총 발주금액, 부족 품목, 검토 건수	아니오	운영자가 볼 숫자만 요약

핵심은 입력과 기준을 섞지 않는 것입니다. 단가표를 입력 시트 옆에 붙여두거나 담당자마다 개인 수식을 쓰게 하면 처음에는 빠르지만, 사람이 늘어날수록 결과가 서로 달라집니다.

1. 발주수량 계산식보다 먼저 업무 규칙을 문장으로 적습니다

자동화 전에 현재 사람이 판단하는 규칙을 먼저 적어야 합니다. 예를 들어 아래처럼 문장으로 정리합니다.

현재고가 안전재고보다 낮으면 발주 후보로 본다.
이미 발주했지만 아직 입고되지 않은 수량은 가용재고에 포함한다.
필요수량은 포장단위의 배수로 올림한다.
최소발주수량보다 작으면 최소발주수량을 적용한다.
단가표에 없는 신규 품목은 자동 계산하지 않고 검토로 보낸다.
SS/FW처럼 시즌별 단가가 바뀌면 해당 시즌 기준표를 사용한다.

이 문장들이 확정되지 않은 상태에서 수식부터 만들면 “계산은 되는데 실제 발주는 못 하는 시트”가 됩니다. 자동화에서 가장 비싼 오류는 수식 오류보다 업무 규칙을 잘못 코드화한 오류입니다.

2. 기준표는 단가표가 아니라 ‘업무 규칙 데이터베이스’로 만듭니다

기준표에는 최소한 다음 열을 두는 편이 좋습니다.

필드	예시	용도
품목코드	SKU-001	품목 식별 기준
시즌	FW26	시즌별 단가 선택
적용시작일	2026-08-01	단가 유효기간
적용종료일	2027-01-31	단가 유효기간
단가	12,500	발주금액 계산
포장단위	12	12개 단위 올림
최소발주수량	24	MOQ 적용
안전재고	30	재고 부족 판단
사용여부	Y	폐기 기준 비활성화

단가만 관리하면 나중에 포장단위나 MOQ가 바뀔 때 또 다른 시트를 만들어야 합니다. 기준표를 처음부터 “계산에 필요한 규칙 모음”으로 잡으면 유지보수가 훨씬 쉬워집니다.

3. 권장 발주수량은 ‘필요량 → 포장단위 → 최소수량’ 순서로 계산합니다

가장 단순한 예를 들어보겠습니다.

현재고: 18개
미입고 발주수량: 12개
목표재고: 70개
포장단위: 12개
최소발주수량: 24개

먼저 가용재고를 계산합니다.

가용재고 = 현재고 + 미입고수량
18 + 12 = 30

필요수량은 40개입니다.

필요수량 = 목표재고 - 가용재고
70 - 30 = 40

하지만 12개 포장단위라면 40개를 그대로 발주할 수 없습니다. 48개로 올려야 합니다. Google Sheets에서는 구조에 따라 다음과 같은 방식으로 계산할 수 있습니다.

=MAX(최소발주수량, CEILING(MAX(0, 목표재고-가용재고), 포장단위))

이 예에서는 48개가 권장 발주수량이 됩니다. 필요수량이 10개라면 포장단위로 12개가 나오지만 MOQ가 24개이므로 최종 권장수량은 24개가 됩니다.

여기에 리드타임, 예상 판매량, 예약출고, 입고예정일 등이 붙으면 계산식은 더 복잡해집니다. 그래서 계산식을 셀마다 직접 작성하기보다 각 규칙을 별도 열로 분리해 중간값을 확인할 수 있게 만드는 편이 안전합니다.

4. 단가 조회는 ‘못 찾으면 빈칸’이 아니라 ‘검토 필요’로 보내야 합니다

자동화 시트에서 가장 위험한 패턴은 조회 실패를 0원이나 빈칸으로 처리하는 것입니다. 담당자가 놓치면 0원 단가로 금액이 계산되거나 발주 대상에서 빠질 수 있습니다.

예를 들어 품목·시즌·수량구간을 기준으로 단가를 찾는다면, 실제 시트 구조에 맞춰 FILTER와 INDEX를 조합할 수 있습니다.

=IFERROR(
  INDEX(
    FILTER(단가기준!$E$2:$E,
      단가기준!$A$2:$A=A2,
      단가기준!$B$2:$B=B2,
      단가기준!$C$2:$C<=C2,
      단가기준!$D$2:$D>=C2
    ),1
  ),
  "검토필요"
)

중요한 건 함수 종류가 아니라 실패했을 때의 행동입니다. 자동 계산이 불가능한 행을 검토 시트로 모으는 구조를 만들어야 합니다.