인류 역사에 있어서 0은 인류가 획기적으로 발전 할 수 있게 만든 가장 위대한 발명 중의 하나라고 합니다.
0은 아무것도 없음을 의미하는데 인도에서는 "영원히 존재함"을 뜻한다고 합니다.
엑셀에서도 0의 존재는 의미가 큽니다. 왜나하면 이 0 때문에 통계적인 오차를 만들어 낼 수 있기 때문입니다.
임플란트 잘하는 치과 병원의 오류
위와 같이 임플란트 잘하는치과병원의 임플란트가격표에서 최소값과 평균값을 구하려고 하는데 세라믹은 취급하지 않아서 가격이 0으로 표시 되어 있습니다.
이런 경우 E4, E5 셀과 같이 일반적인 최소값과 평균을 구하는 함수를 사용하여 =MIN(C4:C8), =AVERAGE(C4:C8) 계산을 하게되면 각각 H4, H5 셀 값과 같이 0원과 1,220,000원이 되게 됩니다.
그러면 임플란트 잘하는치과병원에 가서 "가장 싼 임플란트 해주세요"하면 공짜로 해줘야 한다는 결론이 납니다.
오류가 발생한 것이죠.
0을 제외하고 계산을 해야하는데 0이 들어가는 바람에 두 계산 모두 틀린 값이 나오게 됩니다.
0을 제외하고 최소값을 구하는 방법 SMALL, COUNTIF
위의 E6 셀의 내용과 같이 0을 제외하고 최소값을 구하기 위해서는 SMALL 함수와 COUNTIF 함수가 사용됩니다.
=SMALL(C4:C8),COUNTIF(C4:C8,0)+1)과 같이 입력하면 H6과 같이 0을 제외한 값 중의 최소값 골드 임플란트 가격1,200,000원을 얻을 수 있습니다.
마감시간 쫓겨서 바빠 죽겠는데 방법만 알면 되는 분은 어서 빨리 창닫기 눌러서 하던 일 마져 하러 가고 원리를 이해하고 MINIFS 함수에 대해 알고 싶다면 아래로......
엑셀 SMALL 함수 사용 방법
SMALL 함수는 범위 내에서 K번째 수를 구하는 함수로 =SMALL(ARRAY, K)와 같이 인수를 사용할 수 있습니다.
- ARRAY : 배열 또는 셀 집합 범위
- K : 몇 번째를 지정합니다.
집합 내에 0을 제외한 최소값은 0의 갯수보다 한개 더 많은 번째 수 입니다. 예를들면 집합 내에 0이 4개 있으면 최소값은 5(4+1)번째로 작은 수가 됩니다.
다시말해 0을 제외한 최소값을 구하는 SMALL 함수의 K값은 K = 0의 갯수+1 가 되며 집합내의 0의 개수는 COUNTIF 함수<=COUNTIF(범위, 0)>로 간단히 구할 수가 있습니다.
OFFICE 365나 2019버전 이상을 사용하고 있다면 위와 같이 SMALL, COUNTIF 함수 둘을 사용하지 않고 자체적으로 제공하는 함수인 MINIFS 함수를 사용할 수도 있습니다.
생소한 MINIFS 함수 사용 방법을 알아 보기 전에 조건을 만족하는 값의 평균을 구하는 AVERAGEIF, AVERAGEIFS 함수를 알고나면 MINIFS 함수는 간단해 집니다.
0을 제외하고 평균을 구하는 방법 AVERAGEIF, AVERAGEIFS
0을 제외하고 평균을 구하는 방법은 간단합니다. 엑셀에서 함수를 제공하고 있기 때문이죠. 바로 AVERAGEIF 함수 입니다.
E7셀과 같이 =AVERAGEIF(C4:C8,"<>0") 입력하면 G7셀과 같이 0을 제외한 4개의 가격에 대한 평균값을 구하게 됩니다.
AVERAGEIF 함수 사용 방법
AVERAGEIF 함수는 이름에서 알 수 있듯이 조건을 만족하는 평균값을 구하는 함수로 =AVERAGEIF(Range, Criteria, Average_range) 와 같이 인수를 사용할 수 있습니다.
- Range : 조건을 계산할 셀 범위
- Criteria : 평균을 구할 셀의 조건을 정의, 숫자, 함수식, 텍스트 등의 여러 원하는 조건을 넣을 수 있습니다.
- Average_range : 조건을 만족하는 range값에 대응하여 실제 평균을 구하는데 사용할 셀의 범위로 생략 가능하며 생략하면 Range에서 계산을 하게 됩니다.
AVERAGEIFS 함수 사용 방법
AVERAGEIFS 함수는 S가 붙었으니 여러 조건을 만족하는 평균값을 구하는 함수로=AVERAGEIFS(Average_range, Criteria_range1, Criteria1,.......)와 같이 인수를 사용할 수 있습니다.
- Average_range : 조건을 만족하는 Criterai_range값에 대응하여 실제 평균을 구하는데 사용할 셀의 범위로 생략 할 수 없습니다.
- Criteria_range1 : 조건을 계산할 셀 범위
- Criteria1 : 평균을 구할 셀의 조건
AERAGEIF 함수와 헷갈리면 안되는 것이 인수 순서가 다른데 AVERAGEIFS 함수는 조건을 계산할 범위 Average_range가 먼저 옵니다. 위의 인수 설명에서 같이 생략할 수가 없고 또 조건을 여러 개 붙일 수 있다보니 계산 범위가 제일 앞에 오고 다음으로 Criteria_range1, Criteria1, Criteria_range2, Criteria2 .....과 같이 여러 조건의 순서로 짝을 맞추기 위해서 입니다.
조건 범위와 계산 범위가 같다면 위의 임플란트 잘하는치과병원을 예를들면 =AVERAGEIFS(C4:C8,C4:C8,"<>0")와 같이 범위를 같은 범위를 두 번 지정해 주면 됩니다.
0을 제외하고 최소값 구하는 방법 MINIFS
MINIFS 함수 사용 방법
AVERAGEIFS 함수 사용 방법을 안다면 MINIFS 함수는 설명 할 것도 없습니다. 똑같기 때문입니다.
굳이 설명하자면 =MINIFS(Average_range, Criteria_range1, Criteria1,.......)와 같이 인수를 사용할 수 있습니다.
- Min_range : 조건을 만족하는 Criterai_range값에 대응하여 실제 최소값을 구하는데 사용할 셀의 범위로 생략 할 수 없습니다.
- Criteria_range1 : 조건을 계산할 셀 범위
- Criteria1 : 최소값을 구할 셀의 조건
E8셀과 같이 =MINIFS(C4:C8,C4:C8,"<>0")와 같이 입력하면 H8셀의 값을 얻을 수 있습니다.
최근댓글