떨어진 값을 곱한 후 합계 구하기

1. 문제

D열의 값과 O열의 월에 해당하는 값, 다시 말해 2024/01은 E열, 2024/02는 F열의 값을 곱한 다음 그 합을 구해서 P열에 입력하는 것입니다.

 

떨어진 값 곱하기의 합 구하기.xlsx

0.02MB

 

P5열에 예시로 든 수식은 =(D5*E5)+(D6*E6)+(D7*E7)+(D8*E8)+(D9*E9)로 전형적인 SumProduct 함수의 사용 예입니다.

 

2. SumProduct 함수

SumProduct 함수의 구문은 

=SUMPRODUCT(array1, [array2], [array3],...)으로

곱할 값들의 범위 또는 배열을 1개 이상 입력하는 것입니다.

 

위와 같은 경우 array1은 D5:D9가 되고, array2는 E5:E9가 됩니다.

 

P4셀에 =SUMPRODUCT(D5:D9,E5:E9)라고 입력하면

=(D5*E5)+(D6*E6)+(D7*E7)+(D8*E8)+(D9*E9)의 값 326,555가 구해집니다.

 

 

3. 떨어진 범위의 값 곱하기

 

가. Offset 함수

1월은 위와 같이 구할 수 있지만, 아래로 끌면 2월의 값이 구해지지 않으므로

내려갈 때 오른쪽으로 이동하도록 해야 하는데 그때 사용할 수 있는 함수가 Offset 함수입니다.

 

Offset 함수의 구문은 

OFFSET(reference, rows, cols, [height], [width])으로서

referce는 참조 셀,

 

rows는 이동할 행수로 내려갈 때는 1,2,3..., 올라갈 때는 -1,-2,-3... 이 되고,

이동하지 않으려고 하면 0 또는 입력을 하지 않으면 됩니다.

 

cols는 이동할 열수로 오른쪽으로 이동할 때는 1,2,3..., 왼쪽으로 이동할 때는 -1,-2,-3...이 되고,

이동하지 않으려고 하면 rows와 마찬가지로 0 또는 입력을 하지 않으면 됩니다.

 

height부터는 대괄호([ ]) 안에 입력되어 있으므로 생략가능한 인수인데,

height는 범위의 높이, width는 범위의 너비입니다.

 

예를 들어 D5셀을 기준으로 E5셀에서 E9셀을 지정하려면

=offset(d5,,1,5)라고 입력하면 됩니다.

 

Q5셀에 위 수식을 입력했더니, Q9셀까지 E열의 값이 채워졌고,

값의 테두리에 파란 선이 생깁니다.

 

그러나, 위와 같이 동적 배열을 지원하지 않는 경우는 

먼저 값이 들어갈 범위인 C1셀에서 C5셀까지를 선택한 후

=offset(a1,,1,5)라고 입력한 후 CSE(Ctrl + Shift + Enter) 키를 눌러야 합니다.

 

그러면 수식 앞뒤에 중괄호 표시가 생깁니다.

 

나. 월에 따른 곱한 값의 합계 구하기(실패)

월이 한 칸씩 내려가므로, cols를 1씩 증가시키면 됩니다.

 

따라서, 행값을 구하는 row함수를 이용해 5행이므로 -4를 해주면 됩니다.

수식은 =OFFSET(D5,,ROW(O5)-4,5)라고 하면 됩니다.

 

값을 살펴보니 값이 동일하게 잘 표시됩니다.

 

이제 SumProduct의 수식에서

E5:E9 대신에 OFFSET(D5,,ROW(O5)-4,5)를 입력하면 되는데,

D5셀은 고정이어야 하므로 $D$5로 절대 참조 형식으로 바꾸고, O5는 내려갈 때마다 바뀌어야 하니 절대 참조형식으로 하지 않아도 됩니다.

 

그런데 값을 살펴보니 #VALUE!가 표시됩니다.

 

이상해서 ROW()-4를 마우스로 범위를 잡은 후 수식에 마우스 커서를 올려놓으니 값 1이 맞습니다. 다른 점은 중괄호로 쌓여 있으므로 배열이라는 점입니다.

 

 

다. 월에 따른 곱한 값의 합계 구하기(성공)

그러면 Row가 아니라 범위 또는 배열의 행수를 구하는 Rows 함수를 이용하면 되지 않을까 해서

=SUMPRODUCT($D$5:$D$9,OFFSET($D$5,,ROWS($O$1:O5)-4,5))라고 입력하니 됩니다.

SumProduct 함수의 첫 번째 인수인 D5:D9와 rows안의 인수인 범위중 첫 번째는 고정돼야 하므로 절대 참조 형식으로 하고, rows안의 두 번째 범위는 상대 참조 형식으로 해서 내려갈 때마다 O6, O7등으로 변경될 수 있도록 해야 합니다.

 

마찬가지로 ROWS($O$1:O5)-4를 선택한 후 마우스 커서를 올려놓으면 1이 구해지는데 이번에는 중괄호 표시가 없습니다.

 

겉으로 보기에는 row(O5)-4나 rows($o$1:o5)-4나 1인데 배열인 1은 안되나 봅니다.

 

미묘한 함수의 차이를 느꼈습니다. 

엑셀이 어느 때는 잘 처리해 주다가도 엄격할 때는 엄격하네요.

 

라. 아래 셀에 수식 채우기

P4셀 오른쪽 아래 네모를 마우스로 더블 클릭하면 아래와 같이 P9셀까지 수식이 자동으로 채워집니다.

 

F열에 1,2,3,4,5를 채우고 P6셀을 보니 (50000*1 + 2000*2 + 2500*3 + 3000*4 + 6111*5) = 104,555 가 잘 구해졌습니다.

 

떨어진 값 곱하기의 합 구하기(완성).xlsx

0.02MB

 

이외에도 다양한 수식으로 구할 수 있을 겁니다.

 

sumproduct안에 배열 수식을 넣어서

=SUMPRODUCT(($D$5:$D$9)*($E$3:$M$3=O5)*($E$5:$M$9))라고 해도 되고,

 

Sum과 if함수를 이용해

=SUM($D$5:$D$9*IF($E$3:$M$3=O5,$E$5:$M$9))라고 해도 됩니다.

 

마찬가지로 위 수식도 동적 배열을 지원하지 않으면 CSE키로 입력해야 합니다.