배열수식에서요
average 함수랑 median 함수는 왜 꼭 IF를 써야지 두가지 조건이 가능해 지는 건가요?
예를 들어서
시나공 책 118 PAGE 문제 2번에서요
=MEDIAN(($C$18:$C$33=G4)*($E$18:$E$33)) 이렇게 하면 답과 같은 답이 안나오고
=MEDIAN(IF($C$18:$C$33=G4,$E$18:$E$33)) 이렇게 해야 답이 나오는데
왜 MEDEAN에서는 *로 조건을 연결시켜지지 않는 것인가요?
배열수식에 대한 이해가 조금 필요할 듯 싶습니다.
우선 MEDIAN(($C$18:$C$33=G4)*($E$18:$E$33)) 이 수식은 논리적으로 오류가 있는 함수입니다.
$C$18:$C$33의 각각의 값과 G4의 값을 비교하여 같은 값에 해당하는 행의 $E$18:$E$33 부분에 대한 값만을 MEDIAN의 값으로 집어넣어야 합니다. 그런데 질문자 님이 작성하진 이 수식은 $E$18:$E$33의 모든 부분을 MEDIAN의 비교대상으로 설정하셨기 때문에 이런 문제가 발생하는 것입니다.
즉, $C$18:$C$33=G4 이 수식은 같으면 논리값 TRUE를 반환하고 다르면 논리값 FALSE를 반환합니다. 그러나 이 논리값은 뒤에 있는 $E$18:$E$33 의 각각의 대응값이 곱해서서 숫자값으로 대치됩니다.
문제는 바로 여기서 발생하게 되죠. G4와 같은 데이터(논리값이 TRUE인 데이터)만 대상으로 한 숫자의 중간값을 구해야 하는데 질문자님이 작성한 수식에서는 논리값이 FALSE가 나와도 $E$18:$E$33 범위의 대응값과 곱해짐으로 해서 숫자 0으로 변경이 되는 현상이 발생합니다. 그러므로 정확한 중간값을 구할 수 없는 것이죠.
예를 들어 1, 2, 3, 4, 5, 6이란 숫자가 있고 이에 대응하는 논리값이 다음과 같다고 합시다.
1,2,3-> True
4,5,6-> False
원래는 1, 2, 3의 중간값 2를 구하려 했는데, 4, 5, 6이 모두 0으로 되는 바람에, 1, 2, 3, 0, 0, 0의 중간값인
0과 1의 중간값인 0.5가 출력되게 되죠.
따라서, 이런 현상을 해결하기 위해서 책과 같이 MEDIAN(IF($C$18:$C$33=G4,$E$18:$E$33)) 이런 수식으로 작성하셔야 하는 것입니다. 이렇게 작성하면, TRUE이면 그에 대응하는 $E$18:$E$33 범위의 한 값이 나올 것이고, FALSE이면 그냥 논리값 FALSE로 출력될 것입니다.
이때 MEDIAN함수는 숫자만의 중간값을 계산하지 논리값은 무시하여 계산하므로 정확한 값이 출력되는 것입니다.
MEDIAN함수에 대한 부분은 함수사전에서 찾아서 다시한번 읽어보세요. 특히 인수가 논리값이나, 텍스트, 빈셀일 경우는 계산에서 제외된다는 사실도 확인해 보세요.
이것만 확실히 아신다면, 계산문제가 훨씬 쉬워질 거예요.~
-
*2011-08-07 23:32:03
배열수식에 대한 이해가 조금 필요할 듯 싶습니다.
우선 MEDIAN(($C$18:$C$33=G4)*($E$18:$E$33)) 이 수식은 논리적으로 오류가 있는 함수입니다.
$C$18:$C$33의 각각의 값과 G4의 값을 비교하여 같은 값에 해당하는 행의 $E$18:$E$33 부분에 대한 값만을 MEDIAN의 값으로 집어넣어야 합니다. 그런데 질문자 님이 작성하진 이 수식은 $E$18:$E$33의 모든 부분을 MEDIAN의 비교대상으로 설정하셨기 때문에 이런 문제가 발생하는 것입니다.
즉, $C$18:$C$33=G4 이 수식은 같으면 논리값 TRUE를 반환하고 다르면 논리값 FALSE를 반환합니다. 그러나 이 논리값은 뒤에 있는 $E$18:$E$33 의 각각의 대응값이 곱해서서 숫자값으로 대치됩니다.
문제는 바로 여기서 발생하게 되죠. G4와 같은 데이터(논리값이 TRUE인 데이터)만 대상으로 한 숫자의 중간값을 구해야 하는데 질문자님이 작성한 수식에서는 논리값이 FALSE가 나와도 $E$18:$E$33 범위의 대응값과 곱해짐으로 해서 숫자 0으로 변경이 되는 현상이 발생합니다. 그러므로 정확한 중간값을 구할 수 없는 것이죠.
예를 들어 1, 2, 3, 4, 5, 6이란 숫자가 있고 이에 대응하는 논리값이 다음과 같다고 합시다.
1,2,3-> True
4,5,6-> False
원래는 1, 2, 3의 중간값 2를 구하려 했는데, 4, 5, 6이 모두 0으로 되는 바람에, 1, 2, 3, 0, 0, 0의 중간값인
0과 1의 중간값인 0.5가 출력되게 되죠.
따라서, 이런 현상을 해결하기 위해서 책과 같이 MEDIAN(IF($C$18:$C$33=G4,$E$18:$E$33)) 이런 수식으로 작성하셔야 하는 것입니다. 이렇게 작성하면, TRUE이면 그에 대응하는 $E$18:$E$33 범위의 한 값이 나올 것이고, FALSE이면 그냥 논리값 FALSE로 출력될 것입니다.
이때 MEDIAN함수는 숫자만의 중간값을 계산하지 논리값은 무시하여 계산하므로 정확한 값이 출력되는 것입니다.
MEDIAN함수에 대한 부분은 함수사전에서 찾아서 다시한번 읽어보세요. 특히 인수가 논리값이나, 텍스트, 빈셀일 경우는 계산에서 제외된다는 사실도 확인해 보세요.
이것만 확실히 아신다면, 계산문제가 훨씬 쉬워질 거예요.~
-
*2011-08-08 14:00:091편 엑셀 113 페이지 설명입니다."
평균값이 186,503이 나왔어요.
if문을 입력할때 if(B11:B29=B3,G11:G29)처럼 작성하는 것은 조건이 거짓일 경우 "반환할 값"이 생략 (if(B11:B29=B3,G11:G29,"반환할 값"))된 것으로, if(B11:B29=B3,G11:G29,"")과 같이 조건의 값이 거짓일 때 이론적으로 아무값도 없는 NULL값을 반환 하라는 뜻입니다. NULL값이 반환되면 아무것도 없는 것이기 떄문에 AVERAGE 함수의 배열요소에서 제외 됩니다. 그런데 만약 배열 수식을 AVERAGE(IF(B11:B29=B3,G11:G29,0)이나 AVERAGE((B11:B29=B3)*(G11:G29)와 같이 작성하면 조건이 거짓일때 0이 AVERAGE함수로 포함되어 평균이 계산 되는 것입니다. 즉 "=AVERAGE(550000.1650000,4000000,165000,770000)" 와 "=AVERAGE(550000.1650000,4000000,165000,770000,0...)"의 차이겠죠. 같은 이유로 MIN, MEDIAN,SMALL 등의 함수도 반드시 IF문과 함께 사용해야 합니다.
좋은 하루 되세요.