목표 달성 소요일수 구하기 (2) - SubTotal 함수

3. 해법 2 - SubTotal 함수 이용

달성 소요 일수 계산(완성)1.xlsx

0.01MB

 

1편은 여기를 참고 바랍니다.

 

가. SubTotal 함수

SubTotal 함수의 구문은

SUBTOTAL(function_num,ref1,[ref2],...)

로서

 

function_num에 따라 합계, 평균, 숫자의 개수 등 다양한 값을 구할 수 있습니다.

Function_num(숨겨진 행 포함)	Function_num(숨겨진 행 무시)	함수
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

 

 

아래와 같은 숫자의 합을 구할 때 Sum을 사용할 수도 있고, Subtotal(9,을 사용할 수도 있습니다.

값이 같은지 비교하기 위해 그룹 1과 그룹 2의 숫자를 같게 했습니다.

나. Sum 함수와 비교

(1) Sum 함수 이용 합계

A12셀에 셀 포인터를 두고, 홈 탭에서 자동 합계 명령을 누르면

 

자동으로 Sum 함수를 이용한 수식이 들어갑니다.

 

엔터 키를 누르면 합계가 구해집니다.

 

(2) SubTotal 함수 이용

직접 수식을 입력해야 합니다.

 

=sub까지 입력하면 sub가 포함된 함수 3개가 표시되는데, SUBTOTAL을 마우스로 더블 클릭하거나, 화살표 키로 이동한 후 탭 키를 눌러 SubTotal을 입력합니다.

 

그러면 친절하게 function_num이 표시되는데, 합계를 구할 것이므로 9를 누릅니다.

 

그러면 9가 입력되는데, 다음에 ,(쉼표)를 넣고, 더할 범위를 선택하고 (괄호를 닫고) 엔터 키를 누릅니다.

 

그러면 같은 값 851이 구해졌습니다.

 

(3) Sum과 SubTotal(9,의 차이점

 

(가) SubTotal(9, 을 이용한 수식

H3셀에

=SUBTOTAL(9,OFFSET(I3,,,1,COLUMN(I3:AP3)))

라고 입력하면 #분산!라고 표시되는데, 이것은 배열 값이 옆 열까지 표시돼야 하는데, 이미 값이 있어서 분산 에러가 발생하는 것입니다.

 

수식을 끌어서 선택하면 그 위에 수식의 값이 표시되는데, 0이 계속되다가, 2,8,11이 표시되는데, 0,2,6,5의 누계값입니다.

 

Offset 함수의 인수는 (reference, rows, cols, [height], [width])이며

위 수식에서 reference는 i3셀이고, rows와 cols는 생략했기 때문에 0이고, 자기 자신을 가리키고,

height와 width는 높이와 너비인데, 높이는 1, width는 column 함수를 이용해 1부터 34까지를 지정한 것입니다.

 

(나) Sum을 이용한 수식

H3셀에

=SUM(OFFSET(I3,,,1,COLUMN(I3:AP3)-COLUMN(H3)))

라고 입력하면 배열 값이 표시되는 것이 아니라 합계 712만이 표시됩니다.

 

여기서 712는 A1셀부터 자기 위치의 1행까지의 누계값을 모두 합한 값입니다.

 

 

(4) 첫 번째로 판매목표를 넘어간 위치 확인하기

위 수식 뒤에 >f3을 추가해서

=SUBTOTAL(9,OFFSET(I3,,,1,COLUMN(I3:AP3)-COLUMN(H3)))>F3

라고 하면 

누계값이 F3보다 큰지를 True와 False로 표시합니다.

 

H3셀의 채우기 핸들을 끌어서 H4셀에 수식을 복사한 다음

수식 전체를 선택하고 F9키를 누르면 30번째부터 True로 바뀝니다.

다시 말해 30번째에 160을 넘는다는 것입니다.

 

Esc키를 눌러 원래대로 수식을 돌립니다.

Shift 키를 누른 상태에서 i4셀부터 AL4셀까지 끌면 누계 값이 171로 160을 넘어간 것과 숫자가 30개임을 알 수 있습니다.

 

위 수식에서 COLUMN(I3:AP3)-COLUMN(H3)라고 한 것은 i3셀의 열 수가 9이기 때문에 1로 만들기 위해 H3셀의 열 수인 8을 뺀 것입니다.

 

(5) Match 함수로 첫 번째 True값의 위치 찾기

위에서는 눈으로 보고 30번째라는 것을 알았는데,

Match 함수를 이용해 True값을 찾을 수 있으면 이 때는 정확한 값이기 때문에 세 번째 인수로 0을 사용합니다.

 

그러면 수식은

=MATCH(TRUE,SUBTOTAL(9,OFFSET(I4,,,1,COLUMN(I4:AP4)-COLUMN(H4)))>F4,0)

이 됩니다.

 

값 30이 잘 구해졌습니다.

 

이제 H4셀의 수식을 H3셀에 복사해서 붙이는데,

H3셀의 경우는 True가 없기 때문에

IfError함수와 결합해서, Error일 때는 "목표 미달"이라고 표시하겠습니다.

 

그러면 수식은 

=IFERROR(MATCH(TRUE,SUBTOTAL(9,OFFSET(I3,,,1,COLUMN(I3:AP3)-COLUMN(H3)))>F3,0),"목표 미달")

이 됩니다.

 

다시 H3셀의 채우기 핸들을 더블 클릭해서 맨 아래줄까지 수식을 복사하면 완성입니다.

 

그런데 2019에서 해보니 SubTotal(9,... 라고 수식을 만들어도 위와 같이 배열로 값이 표시되지 않기 때문에 사용할 수 없으며 Microsoft 365에서만 가능한 기능입니다.

 

아래 글을 참고 바랍니다.

https://support.microsoft.com/ko-kr/office/%EB%8F%99%EC%A0%81-%EB%B0%B0%EC%97%B4-%EC%88%98%EC%8B%9D-%EB%B0%8F-%EB%B6%84%EC%82%B0-%EB%B0%B0%EC%97%B4-%EB%8F%99%EC%9E%91-205c6b06-03ba-4151-89a1-87a7eb36e531

동적 배열 수식 및 분산 배열 동작 - Microsoft 지원

 

달성 소요 일수 계산(완성)2.xlsx

0.01MB