INDIRECT함수 활용하기 거의 끝판왕

질문은...

" 안녕하세요

첨부한 파일에 value err 가 계속나는데 어떻게 수정해야 할지 모르겠습니다.

1.err상단 날짜 기간동안의 동남권 데이터 평균을 구하고 싶습니다.

2.err상단 날짜 수식도 바르게 된건지 확인 부탁드립니다(우측 2019/2/25일기준 84일전부터 2019/2/25까지의 기간을 표시한 것입니다.

 

답

작성하신 함수를 보니 averageifs 함수의 인수를 잘못 이해하신것 같아요

index와 match를 쓰신 부분의 결과값은 동남권 값들의 배열이에요

averageifs의 첫번째 인수는 범위로 지정해야 하거든요

 

함수를 하나로 나타내기 어려워서 여러 단계로 나눠서 계산 했어요

물론 하나로 합칠수도 있지만, 너무 길어서 복잡하니 나눠서 설명 드립니다~

(첨부파일 참조)

첫번째, match함수로 해당 열 찾기

=MATCH(Sheet3!$B$5,Sheet2!$1:$1,0)  

이렇게 하면 sheet2에서 동남권의 열을 찾을수 있어요

여기서는 37이에요

 

두번째, 해당 열을 address함수를 이용해 셀주소로 바꾸기

위에서 계산한 37은 그냥 숫자일 뿐이므로, 이 숫자를 셀주소(열주소)로 바꿔줘야 해요

=ADDRESS(1,C1,4,1)

여기서 C1이 37이에요...4와 1은 옵션이구요

그러니까...A열=1, B열=2로 봤을 때 37번째 열을 가져와야 하는데,

address의 첫번째 인수은 row값이구요..

위 함수의 결과는 AK1이 되요, 여기서 1행은 address함수에 입력한 인수 중 첫번째 인수인 1이에요

 

세번째, 셀주소를 범위로 바꾸기 위해 substitute함수로 불필요한 부분 지우기

averageifs의 첫번째 인수는 범위에요

예를 들어..averageifs(A:A,...와 같은 식이죠

그러니까 위에서 계산한 AK1을 AK:AK로 바꿔야 하고, 이를 위해

=SUBSTITUTE(C2&":"&C2,1,"") 이렇게 썼어요

여기서 C2에 들어있는 값은 AK1이고, 위 함수를 말로 풀어보면 AK1:AK1을 만들어서 그 중에 1을 ""(공백)으로 바꿔라..

 

네번째, 해당셀 범위를 averageifs함수 안에서 사용하기 위해 INDIRECT 함수로 간접범위 지정하기에요

위에서 계산한 AK:AK라는 값은 C3셀에 있어요

그래서 함수를 =averageifs("sheet2!"&C3,....)라고 쓰면 계산 자체가 되지 않아요

averageifs의 첫번째 인수는 범위인데, 문자열이 들어왔기 때문이에요

이 문자열을 진짜 셀주소(범위)로 바꾸려면 INDIRECT함수를 이용해야 해요

=AVERAGEIFS(INDIRECT("Sheet2!"&C3),Sheet2!$A:$A, ">=" & --LEFT($C$4,10),Sheet2!$A:$A, "<=" & --RIGHT(C4,10))

함수는 위와 같이 썼고, sheet2!AK:AK를 문자가 아닌, 해당 주소를 인식하게 되어있어요

 

마지막으로...

left와 right 함수 앞에 --를 쓴 것은, 

left와 right 함수는 결과를 text로 반환하는데, 1을 곱하면 숫자가 되거든요(물론 텍스트 형식의 숫자만 그런거에요)

-1을 두번 곱하면 1을 곱한것과 같으니까, --라고 쓴거구요

이렇게 한 이유는 sheet2의 A열에 있는 날짜데이터가 숫자형태로 되어있기 때문이에요

물론 숫자로 되어 있어야 비교연산자도 동작하게 되구요..

 

첨부파일 참고하세요

특정기간 평균 구하기.xlsx

1.15MB