학습(공부)하는 블로그 :: '반복 행 또는 열 합계 구하기' 태그의 글 목록
 

 

Notice»

Recent Post»

Recent Comment»

Recent Trackback»

03-29 00:00

 

'반복 행 또는 열 합계 구하기'에 해당되는 글 1

  1. 2015.01.15 MOD 함수를 이용한 반복 행 또는 열 합계 구하기
 
반응형


앞서 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



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



반응형
: