■ 목차
1. 엑셀 팁/질문 : 홀수행만 합계를 자동으로 구하고 싶습니다.
2. 홀수행 합계 구하기
3. Sumproduct 함수로 홀수행 합계 구하기
4. Sum 함수의 배열 수식으로 짝수행 합계 구하기
5. Sumif 함수로 간단하게 합계 구하기
** 핵심 요약
1. 엑셀 팁/질문 : 홀수행만 합계를 자동으로 구하고 싶습니다.
- 홀수행이나 짝수행 혹은 1, 2, 3번째, … 처럼 특정 간격단위로 합계를 구할 수 있을 까요?
2. 홀수행 합계 구하기
- 아래 예제와 같이 데이터가 상당히 규칙적으로 정리가 되어 있는 경우 홀수행이나 짝수행의 합계를 구할 수 있는 경우가 있습니다.
- 사실 아래 예제와 같은 경우라면 Sumif 함수를 이용하면 훨씬 간단하고 명확하게 합계를 구할 수 있겠지만
- 오늘은 홀수행, 짝수행에 대한 합계를 구하는 방법에 대해서 확인을 해보도록 하겠습니다.
- 우선 첫번째로 행번호를 알 수 있는 엑셀의 함수로는 Row 함수가 있습니다. Row(A1)처럼 사용하고 결과값으로 숫자 1 ( = 1행)을 확인할 수 있습니다.
- 그리고 짝수인지 홀수인지 알수알 수 있는 방법은 몇 가지가 있는데 나머지를 구해주는 Mod 함수와 홀수 여부를 알 수 있는 Isodd 함수와 짝수여부를 알 수 있는 Iseven 함수를 사용할 수 있습니다.
- 마지막으로 합계를 구해줘야 하는데 그냥 간단한 Sum 함수로는 구하기가 어렵습니다.
- 조건이 들어가는 문제이기 때문에 한 번의 수식으로 구하기 위해서는 반드시 배열 형식으로 입력하여 합계를 계산하는 것이 필요하며
- 이런 경우 배열 인수 사용이 가능한 Sumproduct 함수를 사용하거나 Sum 함수를 배열수식 형식으로 입력을 해줘야 합니다.
- 다시 한 번 얘기를 드리는데 아래와 같은 예제이거나 아래와 같은 예제가 아니더라도 Sumif 함수 사용을 적극 추천드립니다. ㅠㅠ
- 마지막에 Sumif 함수로 간단하게 구하는 부분도 같이 포함하여 비교가능하도록 하겠습니다.
- 오늘은 교육 목적상 그냥 홀수행을 찾아서 합계를 구해주는 방식으로 먼저 설명을 드리겠습니다.
3. Sumproduct 함수로 홀수행 합계구하기
- Sumproduct 함수는 배열로 입력된 데이터의 합계 구해주는 함수입니다.
- 즉 범위를 1개 입력하게 되면 해당 범위의 모든 숫자를 더해주는 함수역할을 하고
- 범위를 2개 입력하게되면 범위 내의 각각의 위치의 숫자끼리 곱해서 전체를 더해주는 역할을 하게 됩니다.
- 수식 : = SUMPRODUCT( ISTODD( ROW( D3:D8 ) )* D3:D8 )와 같이 적용을 하고 단계별로 설명을 드리겠습니다.
- ROW(D3:D8) = { 3 ; 4 ; 5 ; 6 ; 7 ; 8 } : Row 함수를 D3:D9까지 적용하게 되면 { 3 ; 4 ; 5 ; 6 ; 7 ; 8 }이라는 행번호로 이루어진 배열값을 얻을 수 있습니다.
- ISODD( ROW ( D3:D8 ) ) = ISODD( { 3 ; 4 ; 5 ; 6 ; 7 ; 8 } ) = { TRUE ; FALSE ; TRUE ; FALSE ; TRUE ; FALSE } = { 1 ; 0 ; 1 ; 0 ; 1 ; 0 }의 배열값을 가지게 됩니다.
- 그리고 Isodd 함수를 다시 적용하게 되면 홀수 행은 참(True = 1), 짝수 행은 거짓 (False = 0)으로 이루어지 배열값을 가지게 됩니다.
- 이렇게 구해진 값과 매출 값을 배열로 곱하기를 하게 되면 아래와 같이 홀수 행(True = 1)은 매출 자기 값을 짝수 행(False = 0)은 0의 값을 가지게 되고
- 이렇게 홀수 행의 값만으로 이루어진 배열값을 Sumproduct 함수가 합계를 구하게 되고 최종 홀수행의 합계를 구하게 되는 원리입니다.
- 상세하게 단계별로 계산식을 풀어주면
- SUMPRODUCT ( ISTODD ( ROW ( D3:D8 ) )* D3:D8 )
- = SUMPRODUCT ( ISODD ( {3 ; 4 ; 5 ; 6 ; 7 ; 8 } ) * D3:D8 )
- = SUMPRODUCT ( { 1 ; 0 ; 1 ; 0 ; 1 ; 0 } * D3:D8 )
- = SUMPRODUCT ( { 1 ; 0 ; 1 ; 0 ; 1 ; 0 } * { 100 ; 200 ; 110 ; 210 ; 120 ; 220 } )
- = SUMPRODUCT ( {100 ; 0 ; 110 ; 0 ; 120 ; 0} )
- = (100 + 0 + 110 + 0 + 120 + 0 ) = 330
- 참조고 위의 예제와 같이 수식에서 실제 계산되는 상세내용을 보고 싶다면 "수식 입력줄"에서 해당 셀주수나 함수 수식을 마우스로 선택하시어 "F9" 키를 눌러주시면 수식에서 선택된 부분의 계산된 값을 미리 볼 수 있습니다.
- 배열에 대한 지식이 없으시다면 조금 이해하기 어려운 부분이 있습니다. ㅠㅠ
- Row 함수로 행번호를 찾아서 Isodd 함수로 홀수 여부를 확인합니다. 그래서 홀수행인 경우 True = 1의 값을 가지게 되고 짝수행은 False = 0의 값이 됩니다.
- 홀수행 True = 1에 해당하는 매출 값만을 Sumproduct 함수로 합계를 해서 최종적으로 홀수행의 합계를 구하게 되는 원리입니다.
- 어려우신 분들은 그냥 넘어가셔도 괜찮습니다. 우선 배열에 대한 내용부터 학습이 필요한 부분이라고 생각이 됩니다.
- 배열도 좀 어려운 개념이기 때문에 천천히 배우셔도 상관없습니다.
4. Sum 함수의 배열 수식으로 짝수행 합계 구하기
- 배열 수식으로 가장 많이 사용하는 함수가 Sum함수입니다.
- 조건별로 합계를 구하는 수식으로 많이들 사용을 하시고 이 번 내용도 배열, 배열 수식에 대한 기초 지식을 요구하는 내용입니다.
- 그리고 배열 수식의 입력방법은 수식을 입력하고 보통 "Enter"로 마무리하지만 배열 수식의 경우 "Ctrl + Shift + Enter"로 마무리를 하고
- 아래 표와 같이 " { } "로 수식이 감싸지는 형태로 표시가 됩니다.
- 내용은 Sumproduct 함수와 동일합니다. 다만 Sum함수로 배열 형식으로 계산하기 위해서 "배열 수식"형태로 입력을 해준 것입니다.
- 그리고 여기는 홀수, 짝수행에 대한 계산을 해줬는데 만약 1, 2, 3행 별로 각각 합계를 구한다면 Mod 함수를 조건으로 사용을 하시면 됩니다.
- 즉 Mod(D3:D8,2) =1 (홀수), = 0 (짝수) ⇒ Mod(D3:D8, 3) = 1 (첫 번째 행), = 2 ( 두 번째 행), = 0 (세 번째 행)과 같은 형태입니다.
5. Sumif 함수로 간단하게 합계 구하기
- 다음은 현재와 같은 상황에서 Sumif 함수를 활용하여 간단하게 합계를 구하는 방법입니다.
- 아래와 같이 아주 간단하게 합계를 구할 수 있습니다. 그리고 절대참조/상대참조만 주의하신다면 심지어 수식 복사로 나머지도 적용이 가능합니다.
- 아. 근데 저는 홀수행, 짝수행으로 합계를 해야 하는데요? 아니면 저는 1, 2, 3열로 합계를 해야 하는데요? 그럼 칼럼하나 추가해서 홀짝을 넣거나 1, 2, 3을 넣어서 계산을 하시고 숨겨 주세요.
- 조금 모양새는 빠지지만 원하는 답을 충분히 얻을 수 있습니다.
- "=Row()"와 같이 Row 함수를 아무 인수 없이 사용을 하게 되면 현재 셀의 행번호를 구할 수 있습니다.
- 어떻게 어렵고 복잡한가요? 원하는 값이 구해지지 않는가요? 그러면 사용을 안 할 이유가 없을 듯합니다. ^^
** 핵심 요약 : 홀수행 합계 구하기 (ft, 한 칸 건너 합계 구해보자)
1. 엑셀 팁/질문 : 홀수행만 합계를 자동으로 구하고 싶습니다.
- 홀수행이나 짝수행 혹은 1, 2, 3번째, … 처럼 특정 간격단위로 합계를 구할 수 있을까?
2. 홀수행 합계 구하기
- 우선 Row 함수로 행번호를 확인하고 홀수행인지 짝수행인지 알 수 있도로 Isodd 함수를 사용한다.
- 그리고 한 번의 수식으로는 홀수행, 짝수행의 조건을 바탕으로 합계를 구하기 위해서는 배열형태로 구해야 하고 어렵다.
- 배열 행태로는 Sumproduct 함수를 사용하거나 Sum함수의 배열수식을 활용한다.
- Sumif 함수가 가능해 보이면 그냥 Sumif함수를 사용하자.
3. Sumproduct 함수로 홀수행 합계 구하기
- Row 함수로 행번호를 찾아서 Isodd 함수로 홀수 여부를 확인한다.
- 그래서 홀수행인 경우 True = 1, 짝수행인 경우 False = 0의 값을 가지게 된다.
- 홀수행 True = 1에 해당하는 매출 값만을 Sumproduct 함수로 합계를 구하는 방법이다.
- 배열에 대한 지식이 요구되며 어렵다.
4. Sum 함수의 배열 수식으로 짝수행 합계 구하기
- 기본적인 수식의 구성 및 원리는 Sumproduct 함수와 동일하다
- 다만 Sum함수로 배열 수식을 적용하기 위해 마지막에 "Ctrl + Shift + Enter"로 마무리해줘야 한다.
5. Sumif 함수로 간단하게 합계 구하기
- 가장 간단하게 홀수행, 짝수행 합계를 구할 수 있다.
- 필요하면 홀수행, 짝수행을 구분하는 칼럼을 하나 삽입해도 괜찮다.
[세상의 모든 엑셀 (엑셀팁, 질문)] - 999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
[세상의 모든 엑셀 (엑셀팁, 질문)] - 001. (엑셀 팁, 질문) 누적 합계 구하기 - 절대참조/상대참조, 빠른 분석 도구 [세상의 모든 엑셀 (엑셀팁, 질문)] - 002. (엑셀 팁, 질문) 날짜 칸 나누어 입력하기
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글