엑셀 떨어진 셀 평균 - egsel tteol-eojin sel pyeong-gyun

1행, 3행, 5행, 7행, 9행, ... 이렇게 두칸 간격으로 떨어진 셀들을 뽑아서 어떤 작업을 할 수 있습니다. (예를들어 MOD 함수를 사용하여 행을 2로 나누었을 때 나머지가 1인 행들만 뽑아낼 수 있습니다.)

이렇게 일정한 간격으로 떨어진 셀들을 뽑아낼 때 MOD 함수를 사용하면 편리합니다.

MOD(피제수, 제수)

피제수 ÷ 제수 를 하여 나머지를 반환합니다.

피제수 : 나누어지는 수

제수 : 나누는 수

사용 예)

3÷2의 몫은 1 나머지는 1 따라서 MOD(3, 2)는 1

3.14÷2의 몫은 1 나머지는 1.14 따라서 MOD(3.14, 2)는 1.14

5÷2의 몫은 2 나머지는 1 따라서 MOD(5, 2)는 1

일정 간격 떨어진 셀들의 합계를 구하는 방법

위 표를 보면 하나의 물품에 3가지 명목(원가, 부가세, 운송비)의 가격이 붙어 있습니다. 이 때 총 운송비를 구하는 방법을 알아보겠습니다.

먼저 이 표에서 가격들이 일정한 패턴을 가지며 나열되어 있습니다. 그리고 운송비에 해당하는 가격만 뽑아내는 것은 수열의 규칙을 찾는 것과 같은데 운송비에 해당하는 행번호를 보면 차례대로 4, 7, 10의 3행 간격으로 나열되어 있습니다.

이 패턴은 3칸 간격으로 되어 있으므로 3으로 나누었을 때 나머지가 1이 되는 셀들입니다.

이 성질을 이용해서 더하려는 값들만 추출할 수 있습니다.

① {=SUM((MOD(ROW(D2:D10), 3)=1)*D2:D10)}

위 수식은 운송비(주황색)에 해당하는 값들만 더하는 수식입니다.

② =SUM(D2:D10)은 모든 값들을 다 더하는 수식입니다.

① 수식은 ②수식에서 붉은 부분을 추가하여 곱한 후 배열수식(CTRL+SHIFT +Enter)으로 만든 수식입니다.

그러면 붉은 부분이 무엇을 뜻하는지와 배열수식에 대해서 이해하면 됩니다.

배열 수식에 대한 설명은 위 링크를 참조하세요. 그리고 붉은 부분을 설명하겠습니다.

배열수식으로 만든 수식이므로 ROW(D2:D10)은 D2:D10 범위에서 순서대로 행의 값을 반환합니다. 반환된 행의 값들은 (2,3,4,5,6,7,8,9,10)이 됩니다.

반환된 행의 값 각각에 대해서 MOD(행의 값, 3)=1을 계산합니다. 이 수식은 행의 값을 3으로 나누어서 나머지가 1이 되면 TRUE를 반환합니다. (4, 7, 10 행입니다.)

만약 3으로 나누어 나머지가 1이 아니면 FALSE를 반환합니다. (TRUE는 숫자 1, FALSE는 숫자 0이라고 생각하면 됩니다.)

따라서 셀 범위 D2:D10에서의 합을 구할 때 MOD(행의 값, 3)=1이 TRUE를 반환하는 행(4, 7, 10)에 대해서만 합을 구하게 됩니다. 왜냐면 다음과 같이 False 값을 곱한 값은 0이되고 True 값을 곱한 행(운송비에 해당하는 행들)은 값의 변화가 없기 때문이죠. 

아직 입력되지 않은 항목이 0으로 표기된 다음과 같은 자료에서 전체 평균을 구하면

당연히 값이 있는 부분의 평균만 구한 것보다 평균이 적게 나온다.

이럴 때 '0이 아닌 값만' 평균을 내려면 AVERAGEIF()함수를 사용한다.

같은 방법으로 100보다 큰 값만 평균을 구할 수도 있다.

반응형

공유하기

게시글 관리

'엑셀' 카테고리의 다른 글

OFFSET 은 직역하면 "떨어진" 정도가 되겠네요. 말 그대로 OFFSET함수는 내가 특정 기준을 정하고 어느 거리만큼 떨어진 셀의 데이터 값을 구하는 함수입니다. 응용하기에 따라 활용도가 무지 높으며 함수의 논리도 그리 어렵지 않으니 한번만 이해 하시면 활용 하는 데에는 문제가 없습니다.

함수 사용법

=OFFSET(기준 영역, 아래로 떨어진 거리, 우로 떨어진 거리, 아래로 포함할 행의 수, 우로 포함할 열의 수) 는 기준 셀에서부터 떨어진 곳의 데이터나 데이터 영역을 반환 합니다. 여기서 마지막 두 인수는 옵션입니다.

마지막 두 인수를 생략 시 OFFSET 함수는 기준 영역으로부터 떨어진 곳의 셀 하나의 데이터를 반환합니다.

1. OFFESET 함수 이해하기

   OFFSET 함수는 주로 시트의 자동화에 COUNTA함수와 많이 쓰입니다. COUNTA함수로 항상 변하는 데이터의 열이나 셀의 데이터의 전체 개수를 구한 다음 OFFSET 함수로 원하는 곳의 데이터를 구하는 것 입니다. 먼저 COUNTA함수를 잠시 복습하면서 시작 하겠습니다.

    

   지금 A열의 데이터의 개수를 구합니다.

   

    

    

   배송일 즉 머리글 까지 포함되니 총 9개의 데이터가 있습니다.

   

    

    

   마지막 배송일을 구하는 OFFSET 함수 입니다.

   

    

   잠시 생각해 봅시다. OFFEST의 기준 데이터에 배송일 즉 A1이 잡혔습니다.

   마지막 배송일은 머리글로부터 몇 칸 내려 가야할까요? COUNTA로 구한 값에서 머리글만 빼면 됩니다.

   열 이동은 없으니 0이 인수가 들어 갑니다

   마지막 두 범위 인수는 생략 하였습니다.

    

   한가지 팁을 드리면 OFFSET의 첫번째 인수로 범위 지정이 가능하나 한번도 쓸 일이 없었습니다. 다른 더 쉬운 방법의 함수로 대체가 가능 하기 때문입니다.

    

   마지막 배송일이 구해 졌습니다.

   

    

    

   이렇게 다른 함수와 같이 쓰게 되면 항상 업데이트 된 최신 정보를 보여 줍니다.

   

    

    

   마지막 낙찰 가격도 같은 방법으로 구하 실 수 있습니다.

   

    

    

   네 D셀에서 수식을 복사하였더니 서식 까지 복사 되었습니다.

   

    

    

   이때 날짜 서식을 숫자 서식으로 바꿔 주기만 하면 간단히 해결 됩니다.

   

    

    

    

    

2. 범위 선택

   OFFSET함수는 일정 거리 떨어진 셀의 값만을 구하거나 영역의 값을 구할 수 있습니다. 이때 영역은 행 열 둘다 사용되는 경우는 잘 없고 거의 열의 범위 값을 계산 하거나 행의 범위 값을 계산하는데 사용합니다. 범위를 행 이동으로 하시면 마지막 인수를 넣지 않으셔도 됩니다.

    

   AVERAGE 함수와 같이 써서 항상 마지막 3일의 낙찰 가격을 구하는 수식입니다.

   

    

   여기서 마지막 인수 3만 그전의 함수에서 추가 되었습니다.

   COUNTA(B:B)-3은 낙찰 가격의 아래에서 3번째 값입니다.

   아래에서 3번재 셀을 포함 행으로 아래로 3칸의 데이터 영역이 최근 3일의 낙찰 가격 입니다.

    

   마지막 3일의 평균입니다.

   

    

    

   역시 같은 함수에서 3만 5로 바꾸어 주면 마지막 5일의 평균이 됩니다.

   

    

    

   마지막 5일의 평균 이죠?

   

    

    

    

    

3. 숫자 대신 셀을 참조하여 원하는 영역 바꾸기

   엑셀은 응용을 잘하면 몇 개의 함수로 정말 데이터 베이스 처럼 활용할 수도 있습니다. 다만 데이터가 커지면 느려지는게 문제이긴 하지만요. 위의 수식에서 3일이나 5일의 최근 날짜 영역을 따로 셀로 만들어 주면 최근 평균을 다이나믹하게 구하실 수 있습니다.