엑셀 처리량 상한 있는 비율 배분, IF로 안 되는 이유

2026-08-28

회사가 둘일 때는 맞던 배분 수식이, 회사를 넷으로 늘리자 합계가 잔여와 안 맞습니다. 어떤 회사는 처리량 상한을 넘겨서 나오고, 어떤 회사는 0이 나옵니다. IF를 하나 더 끼워 넣어도 다른 데가 틀어집니다.

먼저 안 되는 것부터 말씀드립니다. 기존 IF 틀을 유지한 채 회사만 늘리는 방식은 되지 않습니다. 회사가 둘일 때 수식이 도는 건 구조가 단순해서입니다. T사가 상한을 넘치면 넘친 몫이 갈 곳이 H사 하나뿐이라 IF 한 번으로 끝납니다. 넷이 되면 T사에서 넘친 몫을 나머지 셋에 가중치 비율로 다시 나눠야 하고, 그 과정에서 U사가 또 상한을 넘치면 남은 둘에게 또 나눠야 합니다. 이건 조건 분기가 아니라 되풀이(iteration)입니다. 넘치는 순서의 경우의 수가 회사 n개일 때 n!로 늘기 때문에, IF를 아무리 중첩해도 담기지 않습니다. 엑셀 옵션의 반복 계산을 켜서 순환참조로 푸는 방법도 있지만, 수렴 여부가 눈에 안 보이고 파일을 남에게 넘기면 옵션이 꺼진 채로 조용히 틀린 값을 냅니다. 권하지 않습니다.

대신 되풀이를 열로 펼치면 회사가 몇 개든 같은 수식 하나로 끝납니다. 라운드 배분입니다.

A열에 회사명(2~5행), B열에 로그인시간(가중치), C열에 처리량 상한을 둡니다. N1에 나눠야 할 잔여를 넣습니다.

N1: =전체유입량-K사 처리량
D2:D5: 0        (배정 누계 시작값, 값으로 직접 입력)
E1: =$N$1-SUM(D$2:D$5)
E2: =IF($C2-D2<=0,0,MIN($C2-D2,IFERROR(E$1*$B2/SUMPRODUCT(($C$2:$C$5-D$2:D$5>0)*$B$2:$B$5),0)))
F2: =D2+E2

E2와 F2를 5행까지 채운 다음, E1:F5를 통째로 복사해 G1, I1, K1 세 곳에 붙여넣습니다. 상대참조가 두 칸씩 밀리면서 앞 라운드의 누계를 자동으로 물어옵니다. 한 라운드마다 최소 한 곳이 상한을 채우므로 4개사는 4라운드에서 반드시 끝납니다. L2:L5가 각 사 최종값입니다.

쓰시던 분기 조건은 바깥에 두면 그대로 삽니다. =IF(전체포기율=0,C2,L2)

실무에서 걸리는 함정을 짚어드립니다.

첫째, 가중치가 텍스트면 전 회사가 0이 됩니다. 로그인시간이 "1:30"처럼 문자로 들어와 있으면 SUMPRODUCT가 그 값을 0으로 셉니다. 분모가 0이 되고 IFERROR가 이를 받아 전부 0을 냅니다. 오류 표시가 안 뜨기 때문에 배분이 실패한 게 아니라 "배분할 게 없었다"로 보입니다. =ISNUMBER(B2)를 옆 칸에 걸어 먼저 확인하십시오.

둘째, 상한 칸의 빈칸과 0은 다릅니다. C열이 비어 있으면 0으로 읽히고, 그 회사는 첫 라운드부터 포화로 판정돼 배분에서 통째로 빠집니다. 합계는 여전히 잔여와 맞기 때문에 검산으로도 안 걸립니다. 빈칸을 0으로 채울지, 상한 없음으로 볼지 먼저 정하고 채워 두십시오.

셋째, 반올림은 중간 라운드에서 하면 안 됩니다. 라운드마다 ROUND를 걸면 오차가 누적돼 최종 합계가 잔여와 몇 원씩 어긋납니다. 반올림은 맨 끝에서 한 번만 하고, 마지막 회사만 잔여 - 나머지 세 곳의 반올림 합으로 맞추면 총액이 정확히 떨어집니다.

넷째, 라운드 수가 회사 수보다 적으면 잔여가 남습니다. 회사를 다섯 곳으로 늘리셨다면 라운드도 하나 더 붙여야 합니다. 마지막에 =$N$1-SUM(L2:L5)를 검산 칸으로 두십시오. 여기가 0이 아니면 라운드가 모자라거나, 전 회사 상한의 합이 잔여보다 작다는 뜻입니다. 후자라면 수식 문제가 아니라 애초에 다 못 받는 상황이므로, 남는 몫을 어디로 보낼지 사람이 정해야 합니다.

마지막으로 하나만 더 말씀드립니다. 수식이 값을 냈다는 것은 맞았다는 증거가 아닙니다. 배분표는 오류 표시 없이 틀리는 게 대부분이라, 합계 검산 칸 하나를 만들어 두고 그 칸이 0인지를 매번 보는 습관이 수식 자체보다 오래 갑니다.