■ 목차
1. 엑셀 팁/질문 : 0을 제외한 최소, 평균 구하기
2. 0을 제외한 최소, 평균값 구하는 3가지 방법
3. 찾기/바꾸기로 0 값 없애기 - 불필요한 데이터라면 0을 찾아서 없애 주기
4. Small, Large 함수와 Countif 함수로 최솟값 구하기 (평균은 불가)
5. 배열 수식 사용하기 - 최소값, 평균값 모두 가능
** 핵심 요약
1. 엑셀 팁/질문 : 0을 제외한 최소, 평균 구하기
- 숫자 0을 제외하고 최소값과 평균을 구할 수 있을까요?
- 0을 무시하고 나머지 값들로만 최소값을 그리고 평균을 수식으로 구하고 싶습니다.
2. 0을 제외한 최소, 평균값 구하는 3가지 방법
- 우선 0도 숫자이고 데이터이기 때문에 같이 포함되어 계산이 되는 것이 맞지만 제외된 값으로 최솟값과 평균값을 구하는 3가지 방법에 대해서 간략하게 설명을 드리겠습니다.
- 정석적인 방법 2가지와 약간 꼼수(?) 느낌이나는 방법 1가지입니다. ^^
- 1) 찾기/바꾸기로 0 값 없애기 - 불필요한 데이터라면 0을 찾아서 없애 주기
- 2) Small, Large 함수와 Countif 함수로 최소값 구하기 ( 평균값은 불가)
- 3) 배열 수식 사용하기 - 최소값, 평균값 모두 가능
- 3가지 방법 정도가 가능한 것 같습니다.
- 그리고 내용 설명을 시작하기 전에 공백, 0 값과 Null (데이터 없음)의 차이를 우리는 알고 갈 필요가 있을 듯 합니다.
- 보통 우리가 말하는 공백(스페이스 바로 입력)이라고 하는 것은 눈에는 보이지 않지만 엑셀에서는 하나의 문자로 취급하는 "데이터"입니다.
- 눈에 보이지 않는 공백, 줄바꾸기 모두 하나의 문자입니다. 그래서 글자수를 헤아려주는 Len 함수를 사용하게 되면 1개라고 분명하게 표시를 해줍니다.
- 그리고 숫자 0은 글자 그대로 숫자 값이고 공백이나 Null과도 다른 데이터가 존재하는 숫자의 값입니다.
- 반면에 "Null"이라고 하는 것은 영어로 "없는, 무효의"라는 의미로 "아무것도 존재하지 않는 상태"입니다.
- 수식으로 공백은 =" " (큰따옴표 사이에 스페이스바로 한 칸 띄움) 표시하고 Null은 = "" (큰따옴표 두개를 붙임)로 표시를 하게됩니다. 엑셀에서 많이 사용하는 표현입니다.
- 숫자 0 : 숫자 형태의 데이터 값
- 공백 : 스페이스바로 입력, 눈에 보이지 않지만 문자 데이터, 수식 표현 : = " " (큰따옴표 사이에 스페이스바로 한 칸 띄움)
- NULL : 값없음, 수식 표현 : = "" (큰따옴표 두 개를 붙임)
3. 찾기/바꾸기로 0 값 없애기 - 불필요한 데이터라면 0을 찾아서 없애 주기
- 노력이 조금 들어가지만 가장 정석적인 방법입니다.
- 불필요한 아니 잘못된 데이터라면 찾기/바꾸기로 0 값을 없애주는 것이 맞습니다.
- 그렇게 정상적인 데이터 값으로 수정을 하게되면 최솟값, 평균은 Min, Average 함수를 그냥 사용을 하시면 됩니다.
- 근본적인 원인을 제거하고 문제를 풀어주는 문제 풀이의 정석입니다. ^^
- 바꾸기 메뉴의 단축키 Ctrl + H를 사용하시면 간단하게 0은 제거가 가능합니다. "바꿀 내용"의 칸에는 아무 것도 입력을 하지 않고 바꾸기 기능을 사용하시면 되겠습니다.
4. Small, Large 함수와 Countif 함수로 최솟값 구하기 (평균은 불가)
- 조금 꼼수(?) 느낌이 나는 방식입니다.
- 확장성이 조금 떨어지지만 분명히 훌륭한 아이디어의 문제 풀이 방법입니다.
- 원리는 먼저 ① Countif 함수로 숫자 0이 몇 개 인지를 헤아려주고
- ② Small 함수로 (Countif 함수 + 1) 번째 작은 값을 찾아 주는 방식입니다.
- 즉 범위 내에 0이 2개이면 3번째 작은 값을, 0이 3개이면 4번째 작은 값을 찾아 주는 방식입니다. ^^
- 수식 표현 : = SMALL(C3:C7, COUNTIF(C3:C7,0)+1)
- Large 함수로도 구현이 가능한데 이때는 ① Countif 함수로 0 보다 큰 값이 몇 개인지 헤아려 주고
- ② Large 함수로 (Countif 개수) 번째 큰 값을 찾아주는 방식입니다.
- 0 보다 큰 함수가 3개이면 3번째 큰 값을 찾아 주게 됩니다.
- 수식 표현 : = LARGE(C3:C7, COUNTIF(C3:C7,">0"))
5. 배열 수식 사용하기 - 최솟값, 평균값 모두 가능
- 배열 수식을 사용하는 방식은 첫 번째 찾기/바꾸기 방식과 원리는 동일합니다.
- 다만 배열 수식으로 구현을 한다는 것만 다르고 그래서 배열 수식에 대한 경험과 지식이 요구됩니다. ^^
- 1) 배열 수식 사용하기 - 최소값 구하기
- 구현하는 방법은 ① If 함수로 "판매 수량"이 0인 경우에는 Null (값없음, "" )으로 바꾸어 줍니다.
- 이를 일반 수식으로 구현하게 되면 If(C3 = 0, "", C3)이 되지만
- 배열 수식으로 구현을 하게 되면 If(C3:C7 = 0, "", C3:C7)과 같이 적용할 수 있습니다.
- 배열 수식이라는 게 여러 셀의 범위를 한꺼번에 수식으로 적용할 수 있다고 보시면 됩니다.
- ② 그리고 이렇게 바꾼 데이터를 Min 함수로 최솟값을 구해주면 됩니다.
- 배열 수식은 항상 "Ctrl + Shift + Enter"로 마지막에 수식을 입력해줘야 하고 이렇게 입력된 배열 수식은 중괄호( "{ }" )로 배열 수식임이 표시되게 됩니다.
- 수식 표현 : {=MIN(IF(C3:C7=0,"",C3:C7))}
- 2) 배열 수식 사용하기 - 평균 구하기
- 평균은 동일한 방식인데 Min 함수 대신에 Avearge 함수를 사용해 주시면 됩니다.
- 수식 표현 : {=AVERAGE(IF(C3:C7=0,"",C3:C7))}
- 배열 수식은 참 재미있는 기능인 것 같습니다. 좀 있어 보이기도 하고
- 실제로 엑셀에서 함수와 VBA 프로그래밍의 중간쯤 역할을 하면서 엑셀을 공부 좀 해보고 싶게 만드는 기능인 것 같습니다.
- 항상 문제풀이는 문제만 잘 이해하시면 90% 해결이 되었다고 보시면 됩니다. 나머지는 엑셀 기본 지식과 "창의력"이 필요하고
- "창의력"이라고 하는 것도 반드시 "나"의 창의력일 필요는 없습니다. 인터넷이라고 하는 좋은 것 있잖습니까? ^^
- 오늘은 여기까지 하겠습니다. 수고 많으셨습니다.
** 핵심 요약 : 0을 제외한 최소, 평균 구하기 - Small, Countif 함수 - 배열수식
1. 엑셀 팁/질문 : 0을 제외한 최소, 평균 구하기
- 0을 무시하고 나머지 값들로만 최솟값, 평균을 구하고 싶습니다.
2. 0을 제외한 최소, 평균값 구하는 3가지 방법
- 1) 찾기/바꾸기로 0 값 없애기 - 불필요한 데이터라면 0을 찾아서 없애 주기
- 2) Small, Large 함수와 Countif 함수로 최소값 구하기 ( 평균값은 불가)
- 3) 배열 수식 사용하기 - 최소값, 평균값 모두 가능
- 숫자 0 : 숫자 형태의 데이터 값
- 공백 : 스페이스바로 입력, 눈에 보이지 않지만 문자 데이터, 수식 표현 : = " " (큰따옴표 사이에 스페이스바로 한 칸 띄움)
- NULL : 값없음, 수식 표현 : = "" (큰따옴표 두 개를 붙임)
3. 찾기/바꾸기로 0 값 없애기 - 불필요한 데이터라면 0을 찾아서 없애 주기
- 노력이 조금 들어가지만 가장 정석적인 방법이다.
- 바꾸기 단축키 Ctrl + H를 사용, "바꿀 내용"의 칸에는 아무것도 입력하지 않는다.
4. Small, Large 함수와 Countif 함수로 최솟값 구하기 (평균은 불가)
- ① Countif 함수로 숫자 0이 몇 개 인지를 헤아려주고
- ② Small 함수로 (Countif 함수 + 1) 번째 작은 값을 찾아 주는 방식이다.
- Large 함수로도 구현이 가능한데 이때는 ① Countif 함수로 0 보다 큰 값이 몇 개인지 헤아려 주고
- ② Large 함수로 (Countif 개수) 번째 큰 값을 찾아주는 방식이다.
5. 배열 수식 사용하기 - 최솟값, 평균값 모두 가능
- ① If 함수로 "판매 수량"이 0인 경우 Null (값없음, "" )으로 바꾸어 준다.
- ② 그리고 이렇게 바꾼 데이터를 Min/Average 함수로 최솟값, 평균을 구해주면 된다.
- 수식 표현 : {=MIN(IF(C3:C7=0,"",C3:C7))}
- 수식 표현 : {=AVERAGE(IF(C3:C7=0,"",C3:C7))}
[세상의 모든 엑셀 (엑셀팁, 질문)] - 999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
[세상의 모든 엑셀 (엑셀팁, 질문)] - 001. (엑셀 팁, 질문) 누적 합계 구하기 - 절대참조/상대참조, 빠른 분석 도구 [세상의 모든 엑셀 (엑셀팁, 질문)] - 002. (엑셀 팁, 질문) 날짜 칸 나누어 입력하기
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글