메뉴
실무 위키›응용 공식›생산계획과 부품 구성표로 자재 필요수량 구하기

생산계획과 부품 구성표로 자재 필요수량 구하기

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

제품별 생산계획에 부품 소요량을 곱해 공통 자재의 필요수량을 합산합니다. BOM(부품 구성표)으로 자재 소요량을 계산할 수 있습니다.

예제파일 다운로드
생산계획과 부품 구성표로 자재 필요수량 구하기 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=SUMPRODUCT ( ( 부품 범위 = 자재 ) * 소요량 범위 * SUMIFS ( 생산수량 범위, 계획 제품 범위, 구성 제품 범위 ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
부품 범위필수부품 구성표에서 자재명이 입력된 범위입니다. 같은 제품·부품 조합이 두 줄이면 두 행이 모두 계산돼 필요수량에 두 번 더해집니다.
자재필수필요수량을 구할 자재명이 입력된 셀입니다. 부품 범위와 글자가 정확히 같은 이름만 합산합니다.
소요량 범위필수완제품 한 개를 만드는 데 필요한 부품 수량 범위입니다. 부품 범위와 구성 제품 범위의 행 수를 같게 맞춥니다. 같은 부품은 모든 행에서 같은 단위로 적습니다. 단위가 섞여도 오류 없이 숫자만 더해집니다.
생산수량 범위필수생산계획에 적힌 제품별 생산수량 범위입니다. 계획 제품 범위와 행 수 및 순서를 같게 맞춥니다.
계획 제품 범위필수생산할 제품명이 입력된 범위입니다. 같은 제품을 여러 행에 적으면 해당 생산수량을 모두 합산합니다.
구성 제품 범위필수부품 구성표의 각 행에 제품명이 입력된 범위입니다. 생산계획에는 있지만 부품 구성표에 행이 없는 제품은 오류 없이 빠져 필요수량이 실제보다 적게 계산됩니다.
I2=SUMPRODUCT(($B$2:$B$9=H2)*$C$2:$C$9*SUMIFS($F$2:$F$4,$E$2:$E$4,$A$2:$A$9))
A
B
C
D
E
F
G
H
I
J
1
제품
부품
소요량
제품
생산수량
자재
필요수량
2
제품A
모터
1
제품A
120
모터
280
3
제품A
볼트
4
제품B
80
볼트
1,060
4
제품A
케이스
1
제품C
50
케이스
170
5
제품B
모터
2
패널
80
6
제품B
볼트
6
7
제품B
패널
1
8
제품C
볼트
2
9
제품C
케이스
1
10
볼트는 제품A 120개에 4개씩, 제품B 80개에 6개씩, 제품C 50개에 2개씩 들어가 모두 1,060개가 필요합니다.
이 공식이 사용하는 함수
SUMPRODUCT 함수 SUMIFS 함수

동작 원리

SUMIFS 함수로 부품 구성표의 각 행에 생산수량을 붙이고, 소요량을 곱한 뒤 SUMPRODUCT 함수로 선택한 자재의 수량만 더합니다.

예제 시트에서 모터의 필요수량을 구하는 I2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

구성표의 각 제품에 생산수량을 붙입니다

구성표의 제품 열을 조건으로 넣으면 제품A의 세 행에는 120, 제품B의 세 행에는 80, 제품C의 두 행에는 50이 붙습니다.

=SUMIFS ( $F$2:$F$4, $E$2:$E$4, $A$2:$A$9 )
= {120;120;120;80;80;80;50;50}
2

생산수량에 부품 소요량을 곱합니다

각 제품의 생산수량에 제품 한 개당 소요량을 곱하면 구성표 각 행의 자재 수량이 됩니다.

=$C$2:$C$9 * SUMIFS ( $F$2:$F$4, $E$2:$E$4, $A$2:$A$9 )
= {120;480;120;160;480;80;100;50}
3

선택한 자재의 수량만 합산합니다

모터가 있는 두 행의 120과 160만 남겨 더하면 필요수량은 280개입니다.

=SUMPRODUCT ( ( $B$2:$B$9 = H2 ) * $C$2:$C$9 * SUMIFS ( $F$2:$F$4, $E$2:$E$4, $A$2:$A$9 ) )
= 280
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료