109 엑셀 offset함수의 재발견

109_엑셀 offset함수로 떨어진 값의 평균 구하기 – 함수 중급 (36)

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

함수 사용법

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

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

109연습파일.xlsx

109연습파일_완성본.xlsx

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일의 최근 날짜 영역을 따로 셀로 만들어 주면 최근 평균을 다이나믹하게 구하실 수 있습니다.

    

   기존 숫자 부분을 셀로 대체하고 셀에다가 최근 날짜를 입력 합니다.

   

    

    

   이제 셀의 숫자를 바꾸어 주기만 하면 최신 평균을 구할 수 있습니다.

   

    

    

   5로 바꾸었더니 5일치 평균이 구해 졌습니다.

   

   OFFSET의 인수가 참 많지만 다 쓸려고 하지 말고 최대한 간단하게 쓸 수 있는 방법을 찾으세요. 행,열 모든 범위를 포함해야 하는 경우는 드물며 그럴 경우는 대부분 꼭 OFFSET함수를 써야해야만 하는 상황이 아니랍니다. OFFSET함수가 처음 보면 어렵게 느껴지는 것은 5개나 되는 인수 숫자 때문이지만 나머지 2개는 생략가능하고 떨어진 곳의 셀의 값만을 구한다고 생각하세요. 그러면 필요한 인수가 "기준, 밑으로, 우로" 이렇게 3개만 있으면되요