상위 몇번째까지 합계 구하기 - SUM, LARGE, ROW, INDIRECT 함수

안녕하세요.

 

실습 파일 다운로드

상위 몇번째까지 합계 구하기_실습.xlsx

0.01MB

완성 파일 다운로드

상위 몇번째까지 합계 구하기_완성.xlsx

0.01MB

 

지점별 거래내역 집계표가 있습니다.

[E3] 셀에 거래 금액이 큰 지점 1, 2, 3번째의 합계를 나타내려고 합니다.

함수를 이용해 나타낼 수도 있고, 자동 필터를 이용해 값을 구할 수도 있습니다.

이 강좌에서는 함수를 이용해 구하는 방법을 소개합니다.

 

SUM 함수와 LARGE 함수 이용

SUM 함수와 LARGE 함수를 이용해 값을 구합니다.

[E3] 셀에 =sum( 을 입력하고 단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

Number1 인수에 large( )를 입력하고 수식입력줄에서 large 글자를 선택해 LARGE 함수 인수 대화상자가 표시되도록 합니다.

Array 인수에 [C3:C22] 셀 범위를 지정합니다.

K 인수에 1을 입력합니다.

수식입력줄에서 SUM 글자를 선택해 SUM 함수 인수 대화상자로 전환합니다.

Number1 인수에 표시된 수식을 복사해서 Number2 인수와 Number3 인수에 붙여넣습니다.

Number2 인수의 LARGE 함수 K 인수는 2로 수정하고, Number3 인수의 LARGE 함수 K 인수는 3으로 수정합니다.

[확인]을 눌러 결과를 구합니다.

완성된 수식입니다.

=SUM(LARGE(C3:C22,1),LARGE(C3:C22,2),LARGE(C3:C22,3))

 

ROW 함수와 INDIRECT 함수 이용

여기에서 조금 더 나아가 보겠습니다.

완성된 수식을 보면 같은 수식이 반복되고 있습니다.

 

지금은 세번째까지 합계를 구하지만 두번째 또는 네번째까지 합계를 구하려면 수식에서 반복되는 부분을 줄이거나 늘려 구할 수 있습니다.

이렇게 원하는 번째까지 구하도록 수식을 수정합니다.

 

[E2] 셀에 3이 입력되어 있는데, [E2] 셀에 입력된 번째까지 합계를 구하려고 합니다.

ROW 함수와 INDIRECT 함수를 이용합니다.

 

ROW 함수는 셀 범위를 입력하면 범위의 행 번호를 알려주는 함수입니다.

어떤 식으로 동작하는지 확인하기 위해 [G3] 셀에 ROW 함수로 수식을 써 보겠습니다.

 

[G3] 셀에 커서를 두고 =row(1:3)을 입력합니다.

 <Ctrl + Shift + Enter>를 눌러 수식을 완성합니다.

수식 완성 결과가 숫자 1만 나오는 경우도 있을텐데, 제대로 실행된 것입니다.

 

ROW 함수 Reference 인수에 셀 범위를 지정하면 배열 수식이 되어 세로 방향으로 값이 나열되어 나타날 수 있습니다.

 

1:3의 의미는 1행부터 3행까지 범위를 의미하고, A1:A3 이라고 입력해도 결과는 똑같습니다.

 

[G3] 셀에 수식을 작성할 때는 직접 1:3으로 입력했지만, [E2] 셀에 입력된 숫자만큼 셀 범위를 지정하게 하려면 INDIRECT 함수를 써야 합니다.

 

INDIRECT 함수 Ref_text 인수에 텍스트를 입력하면 셀 주소로 결과를 돌려줍니다.

수식에서 "1:3"이라고 입력하면 문자 형식이 되는데, INDIRECT("1:3")로 입력하면 1행부터 3행까지를 나타내는 셀 범위가 됩니다.

 

[H3] 셀에 =row( 를 입력하고 단축키 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

Reference 인수에 indirect( )를 입력한 뒤 수식입력줄에서 indirect 글자를 선택해 INDIRECT 함수 인수 대화상자가 표시되도록 합니다.

Ref_text 인수에 "1:" & E2를 입력합니다.

<Ctrl + Shift>를 누른 상태에서 [확인]을 누릅니다.

배열 수식이기 때문에 <Ctrl>키와 <Shift>키를 동시에 누르고 [확인]을 눌러야 합니다.

실행된 결과가 [H2] 셀에 1만 나와도 제대로 동작한 것입니다.

화면에 표시되지 않을 뿐이지 실제 결과값은 1, 2, 3이 나타난 겁니다.

 

[E5] 셀에 수식을 작성해 보겠습니다.

 

[E5] 셀에 커서를 두고 =sum( 을 입력하고 <Shift + F3>을 눌러 함수 인수 대화상자를 나타냅니다.

Number1 인수에 =large( )를 입력한 뒤 수식입력줄에서 large 글자를 선택해 LARGE 함수 인수 대화상자가 표시되도록 합니다.

Array 인수에 [C3:C22] 셀 범위를 지정합니다.

K 인수에 row( )를 입력한 뒤 수식입력줄에서 row 글자를 선택해 ROW 함수 인수 대화상자가 표시되도록 합니다.

Reference 인수에 indirect( )를 입력한 뒤 수식입력줄에서 indirect 글자를 선택해 INDIRECT 함수 인수 대화상자가 표시되도록 합니다.

Ref_text 인수에 "1:" & E2를 입력합니다.

<Ctrl + Shift>키를 누른 상태에서 [확인]을 누릅니다.

완성된 수식입니다.

=SUM(LARGE(C3:C22,ROW(INDIRECT("1:" & E2))))

지금은 [E3] 셀에 구한 값과 똑같은 결과가 [E5] 셀에 나타나 있는데, [E2] 셀 값을 4로 바꾸면 [E5] 셀은 네번째 큰 값까지 합계를 구합니다.

 

 

필요하신 분들께 도움이 되길 바랍니다.