해당 월까지의 계획과 실적의 합계 구하기

1. 문제

아래와 같이 월별 계획과 실적이 입력되어 있는데, B3셀에 월을 입력하면 해당 월까지의 합계가 B6셀과 C6셀에 표시하려고 합니다.

 

현재 수식은 3월이기 때문에 D6,F6,H6셀의 합계로 되어 있는데,

이것이 월에 따라서 개수가 달라지고, 계획은 계획끼리 더해져야 하는 것입니다.

 

해당월까지의 합계구하기.xlsx

0.01MB

 

 

2. 해법 1

 

가. Mod, Column 함수

Mod함수를 이용해 나머지가 홀수 또는 짝수인 것의 셀 주소를 더하면 됩니다.

B7셀에 =MOD(COLUMN(D6:AA6),2)이라고 입력하고 엔터키, 이전 버전의 경우는 Ctrl + Shift + Enter 키를 누르면

 

0,1이 반복되면서 B열부터 시작했기 때문에 11월 실적 열인 Y열에서 끝납니다.

 

 

이제 계획이 0이므로 계획은 0인 것만 더하면 됩니다.

 

나. Offset 함수

Offset 함수의 구문은 

OFFSET(reference, rows, cols, [height], [width])

로서

첫번째 인수 reference는 참조 범위, rows는 떨어진 행 수, cols는 떨어진 행 수,

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

 

Offset함수를 이용해 D6셀부터 월*2개의 범위를 가져오면 됩니다.

따라서, 수식은

=offset(d6,,,,b3*2)

가 됩니다.

 

위 수식에서 D6:AA6대신 offset(d6,,,,b3*2)를 입력하면 됩니다.

 

따라서, 수식은 

=MOD(COLUMN(OFFSET(D6,,,,B3*2)),2)

이 됩니다.

 

이제 3월까지의 계획은 나머지가 0인 것의 합계를 구하면 됩니다.

 

따라서, 수식은

=SUM(IF(MOD(COLUMN(OFFSET($D$6,,,,$B$3*2)),2)=0,OFFSET($D$6,,,,$B$3*2)))

가 됩니다.

 

나머지가 0일 때 OFFSET($D$6,,,,$B$3*2)의 값은 100, 120, 110이 되므로, 이들의 합계가 330이 되는 것입니다.

 

실적의 계는 B7셀의 채우기 핸들을 C7셀까지 끌어서 수식을 복사한 다음 나머지만 1로 수정하면 됩니다.

 

50, 130, 120의 계는 300이므로 맞습니다.

 

3을 4로 바꿔보면

 

120과 90을 더하면 되므로

450, 390 맞습니다.

 

3. 해법 2

 

가. Filter 함수

Filter 함수의 구문은

=FILTER(array,include,[if_empty])

입니다.

 

여기서, array는 필터로 값을 반환할 범위,

include는 필터 조건식,

if_empty는 값이 없을 경우의 값, 다시 말해 if_errror일 때의 값과 같습니다.

 

나. Take 함수

Take 함수의 구문은

=TAKE(array, rows,[columns])

입니다.

 

첫번째 인수는 array 배열이고, rows는 가져올 행 수, columns는 가져올 열 수입니다.

 

 

다. 해당 월까지의 계획과 실적의 합계 수식

 

(1) 해당 월까지의 계획 값 구하기

수식은 

=TAKE(FILTER($D$6:$AA$6,$D$5:$AA$5=B5),,$B$3)

입니다.

Filter 함수로 D5:AA5셀 값이 B5, 다시 말해 계획인 것의 D6:AA6셀 값을 가져오는데,

Take 함수로 B3셀의 값이 월의 개수만큼 가져오는 것입니다.

 

B3셀의 값이 4이기 때문에 계획에 해당하는 값 100, 120, 110, 120을 가져옵니다.

 

이제 Sum만 하면 되므로

= 다음에 Sum(를 입력하고, 마지막에 )로 닫아주면 됩니다.

4월까지의 계획의 합계 450 맞습니다.

 

B8셀의 채우기 핸들을 C8셀까지 끌면 390 맞는 값이 구해집니다.

 

다른 셀 주소는 모두 $가 붙어 있어서 고정인데, B5셀만 C5셀로 실적으로 바뀌기 때문입니다.

 

B3셀의 4를 3으로 바꿔도 맞습니다.

 

해당월까지의 합계구하기(완성).xlsx

0.01MB