Category»

Notice»

Statistics Graph
티스토리 모바일 블로그

 
 


앞서 ROW함수와 COLUMN함수의 쓰임새에 대해 알아보았습니다.


이번에는 반복되는 행 또는 열의 합계를 구하는 방법을 알아보겠습니다.

아래 예제는 인터넷 상에서 구한 파일인데 제가 임의로 변경한 것입니다.




아래 예제를 보시면 K열에 출력될 합계 금액은 F열과 H열, 그리고 J열의 숫자를 모두 합하는 것이죠.

이와 같이 규칙적으로 반복되는 열의 합계 구하는 방법을 알아볼거고요,




15행에 들어갈 합계는 5행부터 14행까지 범위 중 홀수열의 합계이고, 16행에 들어갈 합계는 짝수열의 합계죠.

이 합계 역시 행과 열만 다르다는 것 뿐이지 원리는 같겠죠.




그럼 우선 K열에 들어갈 수식부터 알아보겠습니다.


가장 쉬운 수식은 아래와 같죠.


=F5+H5+J5


더해야 할 셀 주소를 일일히 찾아서 더하는 방법이죠.

지금은 데이터가 세 개 밖에 없어 편리하겠지만 더해야 할 데이터가 많으면 곤란하겠죠.




두 번째 방법은 아래와 같이 SUM 함수를 사용한 방법으로 약간은 진보한 것이죠.


=SUM(F5,H6,J6)


그런데 이 수식 역시 더할 데이터가 많아지면 곤란해지기는 마찬가지입니다.




세 번째 방법은 SUMPRODUCT 함수를 활용하는 방법으로 수식은 아래와 같습니다.


=SUMPRODUCT((MOD(COLUMN($E7:$J7),2)=0)*$E7:$J7)


MOD 함수는 나눗셈의 나머지를 나타내는 함수로 다음과 같은 형식으로 사용합니다.


=MOD(숫자, 나눌수)

예를 들면 =MOD(13,3)의 결과는 1이 됩니다. 13을 3으로 나눈 나머지 값이기 때문이죠.


다시 수식을 보겠습니다.


E7셀에서 J7셀의 범위에서 우리가 구해야 할 값은 F열과 H열, J열이죠. 

해당 셀의 COLUMN 값은 6, 8, 10이 됩니다. 따라서 COLUMN() 값이 짝수인 경우만 더하면 되는 거죠.

이것을 수식으로 표현하면 아래와 같이 되죠


MOD(COLUMN($E7:$J7),2)=0


다음은 SUMPRODUCT 함수를 응용해야 하는데요,

SUMPRODUCT 함수는 본래 두 인수의 곱의 합을 구하는 함수입니다만 여기와 같이 특정 조건식에 활용하는 경우도 있습니다.


=SUMPRODUCT(조건1*조건2*……*합할범위)


이 형식은 자주 사용하는 식이니 꼭 기억해두시기 바랍니다.


따라서 원 식은 E7셀에서 J7셀 중  셀 COLUMN 값을 2로 나눈 나머지가 0인 셀만 더하면 되는거죠.

이해가 가셨나요....




또 다른 방법은 SUM 함수와 배열수식을 활용하는 방법입니다.


{=SUM(IF(MOD(COLUMN($E8:$J8),2)=0,$E8:$J8,0))}


수식은 SUMPRODUCT 함수를 사용한 것과 비슷하지만 약간 다르죠.

수식 좌우에 {} 표시가 있습니다.(이 표시가 배열수식이라는 것을 알려주는 부호입니다.)


SUM 함수를 사용하는 배열수식은 다음과 같은 형식을 가집니다.


=SUM(IF(조건,더할범위, 0))


수식을 해석하면 조건에 맞는 곳은 더하고, 그렇지 않는 경우에는 0으로 처리하라는 거죠.

만약, 조건이 두 가지 이상일 경우에는 앞서 본 SUMPRODUCT 함수를 이용하면 되겠죠.


배열수식은 반드시 수식을 마감하면서 그냥 Enter 키를 누르면 안됩니다.

Shift 키와 Ctrl 키를 누른 상태에서 Enter 키를 눌러야 합니다.(꼭 기억하세요.)

그렇지 않으면 결과가 0으로 환원되어 버립니다.




15행과 16행에 들어가는 수식도 위와 같은 경우로 따져보면,

F15셀과 F16셀에 아래와 같이 수식을 입력하는 방법이 가장 쉽지만 자료의 양이 많을 경우 곤란하다고 했죠.




다음은 SUMPRODUCT 함수와 행의 값을 반환하는 ROW 함수를 사용한 것이 위와는 다른 점이죠.

그리고 15행과 16행에서 MOD 함수의 결과값이 1과 0으로 다른 점을 잘 이해하시기 바랍니다.




마지막으로 배열수식을 활용한 예입니다.

여기서도 MOD 함수의 결과값이 1과 0으로 다른 점을 잘 이해하시죠.




일반적으로 조건이 여러 개일 경우에는 SUMPRODUCT 함수를 이용하고, 조건이 하나밖에 없을 경우에는 SUM 함수를 이용한 배열수식을 활용한다는 점을 기억해두시기 바랍니다.


배열수식에서도 2개 이상의 조건을 처리할 수는 있습니다.

IF 함수 안에 조건문을 (조건1*조건2*……) 형식으로 하면 되겠죠.


예제 파일을 첨부합니다.


반복행합계구하기.xlsx



도움이 되셨다면 공감 꾸~욱 부탁드립니다!!!



저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

1. 합계와 곱 구하기

 

  1) SUM 함수

○ 개요 : 범위 또는 인수의 합을 구하는 함수

○ 구문 =SUM(Number1, [Number2], ……)

○ 인수

Number1 : 필수 요소, 합계를 구하려는 첫째 숫자·셀 참조·범위

▷ [Number2], …… : 선택 요소, 합계를 구하려는 추가 숫자·셀 참조·범위, 최대 255개 지정 가능

 

  2) PRODUCT 함수

○ 개요 : 인수의 곱을 구하는 함수

○ 구문 =PRODUCT(Number1, [Number2], ……)

○ 인수

Number1 : 필수 요소, 곱을 구하려는 첫째 숫자·셀 참조·범위

▷ [Number2], …… : 선택 요소, 곱을 구하려는 추가 숫자·셀 참조·범위, 최대 255개 지정 가능

 

  3) SUMPRODUCT 함수

○ 개요 : 배열·범위의 대응되는 값끼리 곱해서 그 합을 구하는 함수

○ 구문 =SUMPRODUCT(Array1, [Array2], [Array3], ……)

○ 인수

Array1 : 필수 요소, 곱한 후 더할 값이 들어 있는 첫 번째 배열 인수

▷ [Array2], [Array3], …… : 선택 요소, 곱한 후 더할 겂이 들어 있는 배열 인수, 최대 255개 지정 가능

 

<사용 예시>

- 업체별 납품량의 합계(=SUM)

- 불량개수=납품량×불량률

- 청구금액=실납품수×단가×(100-할인율)%

- 할인액 합계=Σ(실납품수×단가×할인율)

 

 

 

2. 조건에 맞는 셀의 합계 구하기

 

  1) SUMIF 함수

○ 개요 : 지정한 조건에 맞는 범위의 값을 더하는 함수

○ 구문 =SUMIF(Range, Criteria, [Sum_range])

○ 인수

 Range : 지정한 조건에 맞는 지 검사할 셀 범위

▷ Criteria : 더할 셀의 조건을 지정하는 수, 식, 텍스트

▷ Sum_range : 합을 구할 실재 셀 범위, 생략 시 Range 범위의 셀을 계산

 

  2) SUMIFS 함수

○ 개요 : 지정한 조건에 맞는 범위의 값을 더하는 함수

○ 구문 =SUMIFS(Sum_range, Criteria_range1, Criteria1, [Criteria_range2, Criteria2], ……)

○ 인수

 Sum_range : 합계를 계산할 범위인 한 개 이상의 셀

▷ Criteria_range1 : 관련 조건에 맞는지 검토할 첫 번째 범위

▷ Criteria1 : Criteria_range1에서 찾을 조건

▷ Criteria_range2, Criteria2, …… : 추가로 지정할 범위 및 조건

 

<사용 예시>

- SUMIF는 조건식이 한 개일 경우에 사용하고, SUMIFS는 조건식이 두 개 이상일 경우에 사용

- 아래 부서별 합계의 D16셀은 부서(B4:B13)에서 구하고자하는 조건인 B16셀을 찾아서 09-26(월)에 있는 데이터 값을 더하라는 것.(부서는 절대참조, 부서별과 합계는 상대참조임을 확인)

- SUMIFS 함수 식을 해석하면 범위(F4:F13)에 있는 데이터 중 부서는 B19와 같고, 지역은 C19와 같은 데이터만 더하라는 것.(여기도 절대참조, 상대참조를 적절히 사용한 것을 확인)

- 절대참조나 상대참조를 사용하는 이유는 수식을 오른쪽으로 복사해서 사용하기 위함임. 그렇지 않으면 일일이 데이터 범위를 수정해줘야 하는 불편이 따름.

 

 

 

 

3. 함수로 자리수 제한하기

 

  1) ROUND 함수

○ 개요 : 숫자를 지정한 자릿수로 반올림하는 함수

○ 구문 =ROUND(Number, Num_digits)

○ 인수

Number : 반올림할 숫자

▷ Num_digits : 반올림하여 소수점 아래 나타낼 자리수

 

  2) ROUNDDOWN, ROUNDUP 함수

○ 개요 : 숫자를 지정한 자릿수로 강제로 올리거나 내리는 함수

○ 구문

=ROUNDDOWN(Number, Num_digits)

=ROUNDUP(Number, Num_digits)

○ 인수 : ROUND 함수와 동일

 

<사용 예시>

- 금액은 납품수량에 단가를 곱한 것인데, 각 함수마다 다름을 알 수 있다.

- ROUND 함수는 나머지 자리수가 500이상일 경우에는 반올림을 하고, 그 미만일 경우에는 버린다.

- ROUNDUP 함수는 나머지 자리수가 있으면 무조건 위로 1을 올린다.

- ROUNDDOWN 함수는 나머지 자리수가 있으면 무조건 버린다.

 

 

저작자 표시 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
 

티스토리 툴바