■ 목차
1. 엑셀 팁/질문 : 최소, 최대 값을 제외한 평균, 표준편차를 구하기 싶어요
2. 최소, 최대 값을 제외한 평균 구하기 - 원리 이해하기
3. Trimmean 함수로 최소, 최대 제외한 평균 구하기
4. 간단한 함수를 이용한 수식으로 구하기
5. Averagifs 함수로 최소, 최대 제외한 평균 구하기
6. 배열 수식 이용하기
** 핵심 요약
1. 엑셀 팁/질문 : 최소, 최대 값을 제외한 평균, 표준편차를 구하기 싶어요
- 데이터 중에서 최솟값과 최댓값을 제외하고 평균, 표준편차 등을 구하고 싶은데 어떻게 수식을 사용해야 할까요?
- 최소, 최댓값이 각 한 개인 경우에는 어떻게 Max, Min 함수 등을 사용해서 구할 수 있을 것 같은데
- 최소, 최댓값이 여러 개인 경우는 또 어떻게 해야 하는지? 생각만큼 쉽지 않은 것 같네요.
- 어떻게 간단하게 구할 수 있는 방법을 있을까요?
2. 최소, 최대 값을 제외한 평균 구하기 - 원리 이해하기
- 일반적으로 최소, 최댓값을 제외하고 평균을 구한다고 얘기를 하지만 엄격하게는 몇 가지 경우의 수가 있을 수 있습니다.
- 우선 어떤 경우에 해당하는지에 대해서 구분을 하신 후에 정확한 수식을 사용해야 할 것입니다.
- ① 최소, 최대의 중복된 값이 없기 때문에 최소, 최댓값은 각각 1개씩인 경우
- ② 최소, 최대 값이 중복된 값이 존재 - 1개 씩만 제외하고 평균을 구하고 싶은 경우
- ③ 최소, 최대 값이 중복된 값이 존재 - 최소, 최대 모두를 제외하고 평균을 구하고 싶은 경우
- 보통은 ①과, ②번째인 중복된 최소, 최대 값이 존재하더라도 1개씩만을 제외하고 평균, 표준편차 등을 구하고자
- 하는 경우가 많을 것 같은데 지금부터 어떤 함수를 사용하고 어떻게 구할 수 있는지에 대해서 알아보도록 하겠습니다.
- 1) 최소, 최대의 중복된 값이 없기 때문에 최소, 최댓값은 각각 1개씩인 경우
- 가장 심플한 경우입니다. 엑셀 함수를 조합한 간단한 수식이나 Trimmean이라는 함수를 이용해서 구할 수가 있습니다.
- ① Trimmean 함수로 최소, 최대 제외한 평균 구하기
- Trimmean 함수는 일정 비율로 데이터를 버리고(Trim = 잘라내다, 가지 치다) 평균(Mean)을 구해주는 함수입니다.
- 만약 10개의 데이터 중에서 최소, 최댓값을 각 1개씩 버린다면 2/10 ( = 20%)을 버리고 평균을 구할 수 있는 함수입니다.
- 가장 심플하게 구할 수 있는 방법입니다.
- ② 간단한 함수를 이용한 수식으로 구하기
- 이번에는 그냥 산수 문제입니다. "평균 = 전체 합계 / 전체 개수"이기 때문에
- 전체 합계에서 최소, 최대 값을 빼주고 전체 개수는 최소, 최대에 해당하는 2개를 빼주는 수식을 만들어 주는 방식입니다.
- 수식 : (전체 합계 - 최소 - 최대) / (전체 개수 - 2)로 Sum, Min, Max, Count 혹은 Counta 함수를 사용하게 됩니다.
- 참고로 Count함수는 숫자 데이터만의 개수를 Counta함수는 전체 데이터의 개수를 구해주는 함수로
- 숫자와 텍스트 데이터가 섞여 있는 경우에는 구분해줘야 하지만 이번 같은 모두 숫자 데이터를 취급하는 경우 구분 없이 사용하시면 됩니다.
- Counta의 "a = all = 모든 데이터의 개수"라고 이해를 하시면 쉽게 구분하실 수 있습니다.
- ③ Averagifs 함수로 최소, 최대 제외한 평균 구하기
- Averageifs 함수는 조건을 만족하는 값들에 대한 평균을 구하는 함수입니다.
- 최소, 최댓값을 모두 제거하고 평균을 구할 수 있습니다.
- 수식이 조금 복잡해지기 때문에 굳이 사용을 해야 하나 하지만 이렇게도 가능하다 정도로 이해해 주시기 바랍니다.
- Averageifs(평균범위, 조건 1 범위, 최솟값 아님 조건, 조건 2 범위, 최댓값 아님 조건) 이런 식으로 사용하게 됩니다.
- ④배열 수식 이용하기
- 조금 복잡한 수식입니다. 먼저 배열 수식에 대한 사전 지식이 있으셔야 사용이 가능한 수식입니다.
- 배열 수식은 여러 개의 데이터인 배열값을 사용하는 수식으로 수식 마무리를 "Enter"가 아닌 "Ctrl + Shift + Enter"로 마무리하고
- 이것도 최신 버전의 엑셀 (엑셀 2021, MS 365 버전)에서는 "동적 배열"이라고 해서 그냥 "Enter"로 마무리하게 되면
- 엑셀이 자동으로 배열 수식을 인식하기 때문에 구분이 없어졌지만
- 최신버전이 아닌 경우는 "Ctrl + Shift + Enter"로 배열수식을 입력해야 합니다.
- 배열 수식에서 If 함수를 사용하여 만약 데이터가 최솟값이거나 최댓값인 경우에 공백( "" )을 반환합니다.
- 이렇게 최소, 최댓값이 제외된 배열을 만들고 이렇게 만들어진 배열의 평균을 구하는 방식입니다.
- 여기까지 설명을 드리고 나니 최소, 최댓값을 제외하고 평균을 구하는 거의 모든 방법이 다 나왔네요.
- 이 중에서 어떤 것은 최소, 최댓값이 2개 이상인 경우에는 원하지 않는 결과가 나올 수도 있기 때문에
- 수식을 꼼꼼히 들여다보고 구분해서 사용을 해줘야 합니다.
- 2) 최소, 최대 값이 중복된 값이 존재 - 1개 씩만 제외하고 평균을 구하고 싶은 경우
- Trimmean 함수 적용 : 사용 가능
- 간단한 수식 적용 : 사용 가능
- Averageifs 함수 적용 : 사용 불가 - 최소, 최댓값이 중복 제외됨
- 배열 수식 적용 : 사용 불가 - 최소, 최댓값이 중복 제외됨
- 3) 최소, 최소 값이 중복된 값이 존재 - 최소, 최대 값 모두를 제외하고 평균을 구하고 싶은 경우
- Trimmean 함수 적용 : 사용 불가
- 간단한 수식 적용 : 사용 불가
- Averageifs 함수 적용 : 사용 가능
- 배열 수식 적용 : 사용 가능
3. Trimmean 함수로 최소, 최대 제외한 평균 구하기
- Trimmean 함수는 가지를 치듯( = Trim )이 최소, 최대 값을 지정한 비율(%)만큼 제거를 하고 평균(Mean)을 구해주는 함수입니다.
- 그래서 10개의 숫자 중에서 2/10(20%)을 제거하게 되면 최소, 최댓값 각 1개씩 2개가 제거되고 제거된 나머지의 값들로 평균을 구할 수가 있습니다.
- 그래서 전체가 몇 개이고 상/하 쌍으로 1개씩 제거하기 위해서 몇 %를 지정해야 하는지를 정확하게 알고 있는 경우에 사용이 가능한 함수입니다.
- 또한 지정한 개수만큼만 제거를 해주기 때문에 최소, 최댓값이 중복이 되어도 필요한 개수만큼만 제거하는 것이 가능합니다.
- 이러한 함수의 특성을 이해하고 필요한 경우에 사용해 주시기 바랍니다.
- 아래 예제를 통해서 전체 10개의 숫자에서 최소, 최대 1개씩을 제거하고 평균을 구해 보도록 하겠습니다.
- 1) Trimmean 함수 입력 : =TRIMMEAN(
- 수식입력을 위한 등호( " = " )를 먼저 입력하고 함수 Trimmean을 키보드로 직접 키인(key-in)하여 입력합니다.
- 그리고 함수의 인수를 입력하기 위하여 괄호 열기까지 입력합니다.
- 2) Trimmean 함수의 첫 번째 인수 범위 선택하기 : =TRIMMEAN(B2:B11,
- 첫 번째 인수는 대상이 되는 데이터의 전체 범위(B2:B11)입니다.
- B2:B11의 셀 범위를 마우스를 이용해서 드래그하여 선택을 하게 되면
- 선택되는 범위의 셀 주소를 엑셀이 자동으로 첫 번째 인수인 셀주소로 입력을 해주게 됩니다.
- 그리고 두 번째 인수 입력을 위하여 인수구분자인 쉼표 ( " , " )를 키보드로 입력합니다.
- 이렇게 인수와 인수를 구분해 주는 쉼표를 엑셀에서는 "인수 구분자"라고 합니다.
- 3) Trimmean 함수의 첫 번째 인수 입력하기 : =TRIMMEAN(B2:B11,2/10)
- 두 번째 인수는 계산에서 제외할 데이터의 범위로 비율(퍼센트)로 지정을 해주게 되면
- 가지를 치듯이(Trim) 최소, 최대 양끝의 데이터를 지정한 비율만큼 제거 해주게 되는 방식입니다.
- 여기서 우리는 전체 데이터가 10개 이고 최소, 최대 값 각 1개씩 2개의 데이터를 제거하기 위해서
- 2/10과 같이 심플하게 분수형식으로 입력을 하도록 하겠습니다.
- 함수 안에 함수를 사용하는 방식이랑 수식 안에 수식을 사용하는 방식은 엑셀에서는 모두 가능하니 참조 바랍니다.
- 그리고 함수입력을 마무리하기 위해 괄호닫기를 키보드로 입력하고
- 수식입력 마무리를 "Enter"키를 눌러서 완성합니다.
- 최종적으로 10개의 데이터 중에서 양끝(최소, 최댓값) 각 1개씩 20%를 제외하고 나머지의 데이터로 평균을 구하는 결과를 얻을 수 있습니다.
- 혹시나 데이터의 개수가 너무 많아서 총 몇 개인지를 잘 모르는 경우는
- ① "상태표시줄"의 간단한 통계 데이터 확인
- 데이터 범위를 마우스로 모두 선택하게 되면 엑셀화면의 우측하단에 간단한 통계 데이터가 표시되는데
- 여기에서 "개수"를 확인할 수 있습니다.
- "상태표시줄"에 표시되는 데이터는 "상태표시줄"에서 마우스 우클릭으로 사전에 지정을 해줘야 합니다.
- ② Count, Counta 함수로 개수 확인하기
- Count 함수는 숫자 데이터만의 개수를 Counta함수는 모든 데이터의 개수를 확인할 수 있는 함수입니다.
- 함수를 이용해서 간단하게 전체 개수를 확인하는 것인 가능합니다.
- 적용 수식 : = COUNTA(B2:B11)
4. 간단한 함수를 이용한 수식으로 구하기
- 그냥 수학 문제라고 보시면 됩니다.
- 최소, 최대 값 각 1개씩을 제거하고 평균을 구하는 경우이고
- 이때 평균의 정의(총합계 / 전체 개수)를 이용해서 총합계에서는 최소, 최대 값을 각 한 개씩 빼주고
- 총개수에서는 전체 개수에서 2개(최소, 최댓값 각 1개씩)를 빼는 형식으로 엑셀 함수를 이용해서 수식을 만드는 방법입니다.
- 모양은 좀 빠지지만 가장 심플하고도 직관적인 방식으로 종종 사용을 하게 됩니다.
- 적용 수식 : =(SUM(B2:B11)-MIN(B2:B11)-MAX(B2:B11))/(COUNTA(B2:B11)-2)
5. Averagifs 함수로 최소, 최대 제외한 평균 구하기
- Averageif 함수는 한 개의 조건을 만족하는 숫자들의 평균을 구할 수 있는 함수입니다.
- 그리고 Averageifs 함수의 경우는 여러 개의 조건을 만족하는 숫자들의 평균을 구할 수가 있습니다.
- 우리는 최소, 최대에 해당하는 값들을 제외한( = 2개의 조건) 숫자들의 평균을 구하기 위하여 Averageifs 함수를 사용할 수 있습니다.
- 즉, 최솟값이 아닌(조건 1) 숫자와 최댓값이 아닌(조건 2) 모든 숫자의 평균을 구하게 되고
- 이 경우 최소, 최대 값이 중복되어 여러 개인 경우 모두 평균계산에서 제외되게 됩니다.
- 그래서 이전의 2가지 방법 (Trimmean 함수, 간단한 수식)과 경우에 따라 조금 다른 결괏값이 나오게 됩니다.
- 적용 수식 : =AVERAGEIFS(B2:B11, B2:B11, "<>"&MIN(B2:B11), B2:B11, "<>"&MAX(B2:B11))
6. 배열 수식 이용하기
- 다음은 배열 수식을 이용하는 방식입니다.
- 초보자분들에게는 조금 어려운 방법이기는 하지만 이런 것도 할 수 있구나 정도로 봐 두시 바랍니다.
- 최신 엑셀버전 (엑셀 2021, MS 365)가 아닌 경우에는 동적배열을 사용할 수 없기 때문에
- 배열 수식 입력 마무리는 "Enter"키가 아닌 "Ctrl + Shift + Enter"키로 해주시기 바랍니다.
- 적용 수식 : {=AVERAGE(IF(((B2:B11=MIN($B$2:$B$11))+(B2:B11=MAX($B$2:$B$11))), "", B2:B11))}
- 원리에 대해서 간단하게 설명을 드리면
- ① If 함수로 최소, 최대 값이 아닌 경우는 숫자를 그대로 가져오고
- 최소, 최대 값에 해당하는 경우는 공백 ( "", Null)을 가져와서 최소, 최댓값을 모두 제거해 줍니다.
- ② 그리고 이렇게 최소, 최대 값이 제거된 숫자들의 평균을 Average함수를 이용해 배열 수식으로 구해주는 방식인데
- 여기서 중요한 것은 최솟값 혹은 최댓값인 경우의 OR 조건의 배열 수식을 만들어 주는 방법입니다.
- 배열 수식에서 And 조건은 2개의 조건을 "곱하기"를 해주고 Or 조건의 경우는 2개의 조건을 "더하기"를 해주면 됩니다.
- 그래서 "(최솟값이 아닌 경우) + (최댓값이 아닌 경우)"로 최소, 최댓값을 모두 제거하는 배열 조건식을 만들 수 있습니다.
- 설명이 좀 어려운 부분이 있는데 배열 수식에 대한 기본 지식이 조금 필요한 부분이어서 그렇습니다.
- 조금 어렵다면 이런 것도 있구나 정도로 이해를 해주시고 배열 수식은 꼭 나중에 공부를 해보시기를 추천드립니다.
- 이 경우에도 중복된 최소, 최대 값이 있다면 모두 제거된 평균값을 구할 수 있는 수식에 해당됩니다.
- 최소, 최댓값을 제거한 평균을 구하는 다양한 방법을 알아봤습니다.
- 만약 최소, 최댓값을 제거한 표준편차를 구한다면 Average 함수 대신에 Stdev 함수를 사용해서 구해주시면 되는데
- 이경우 Stdev 함수의 경우 Trimmean, Averageifs 함수와 같은 확장된 함수가 없기 때문에
- 조금 힘들지만 배열 수식을 활용해야 합니다. ㅠㅠ
- 아니면 If함수를 사용하여 최소, 최대가 제거된 숫자 리스트를 다시 만들어서 계산을 하는 방식을 적용할 수 있습니다.
- 어느 것 하나가 정답일 수는 없습니다. 그리고 여기서 얘기드린 방법 이외에 더 다양한 방법이 있을 수 있습니다.
- 그렇게 실력이 키워가는 곳이 바로 엑셀이니깐요. ^^
- 오늘도 수고 많으셨습니다.
** 핵심 요약 : 최소, 최대 값을 제외한 평균, 표준편차 등 구하기
1. 엑셀 팁/질문 : 최소, 최대 값을 제외한 평균, 표준편차를 구하기 싶어요
- 데이터 중에서 최솟값과 최댓값을 제외하고 평균, 표준편차 등을 간단하게 구할 수 있는 방법은?
2. 최소, 최대 값을 제외한 평균 구하기 - 원리 이해하기
- 여러 가지 경우가 있을 수 있기 때문에 구분해서 정확한 수식을 사용해야 한다.
- ① 최소, 최대의 중복된 값이 없기 때문에 최소, 최댓값은 각각 1개씩인 경우
- ② 최소, 최대 값이 중복된 값이 존재 - 1개 씩만 제외하고 평균을 구하고 싶은 경우
- ③ 최소, 최대 값이 중복된 값이 존재 - 최소, 최대 모두를 제외하고 평균을 구하고 싶은 경우
- 1) 최소, 최대의 중복된 값이 없기 때문에 최소, 최댓값은 각각 1개씩인 경우
- ① Trimmean 함수로 최소, 최대 제외한 평균 구하기
- ② 간단한 함수를 이용한 수식으로 구하기
- ③ Averagifs 함수로 최소, 최대 제외한 평균 구하기
- ④배열 수식 이용하기
- 2) 최소 최대 값이 중복된 값이 존재 - 1개 씩만 제외하고 평균을 구하고 싶은 경우
- Trimmean 함수 적용 : 사용 가능
- 간단한 수식 적용 : 사용 가능
- Averageifs 함수 적용 : 사용 불가 - 최소, 최댓값이 중복 제외됨
- 배열 수식 적용 : 사용 불가 - 최소, 최댓값이 중복 제외됨
- 3) 최대 최소 값이 중복된 값이 존재 - 최대, 최소 모두를 제외하고 평균을 구하고 싶은 경우
- Trimmean 함수 적용 : 사용 불가
- 간단한 수식 적용 : 사용 불가
- Averageifs 함수 적용 : 사용 가능
- 배열 수식 적용 : 사용 가능
3. Trimmean 함수로 최소, 최대 제외한 평균 구하기
- Trimmean 함수는 가지를 치듯이 최소, 최대 값의 지정한 비율(%)만큼 제거를 하고 평균(Mean)을 구해주는 함수이다.
- 지정한 개수만큼만 제거를 해주기 때문에 최소, 최댓값이 중복이 되어도 필요한 개수만큼만 제거하는 것이 가능하다.
- 1) Trimmean 함수 입력 : =TRIMMEAN(
- 2) Trimmean 함수의 첫 번째 인수 범위 선택하기 : =TRIMMEAN(B2:B11,
- 3) Trimmean 함수의 첫 번째 인수 범위 선택하기 : =TRIMMEAN(B2:B11,2/10)
4. 간단한 함수를 이용한 수식으로 구하기
- 그냥 수학 문제이다. 평균의 정의(총합계 / 전체 개수)를 이용해서 총합계에서는 최소, 최대 값을 각 한 개씩 빼주고
- 총개수에서는 전체 개수에서 2개를 빼는 형식으로 엑셀 함수를 이용해서 수식을 만드는 방법이다.
- 적용 수식 : =(SUM(B2:B11)-MIN(B2:B11)-MAX(B2:B11))/(COUNTA(B2:B11)-2)
5. Averagifs 함수로 최소, 최대 제외한 평균 구하기
- Averageifs 함수는 여러 개의 조건을 만족하는 숫자들의 평균을 구할 수가 있다.
- 최소, 최대 값이 중복되어 여러 개인 경우 모두 평균계산에서 제외되게 된다.
- 적용 수식 : =AVERAGEIFS(B2:B11, B2:B11, "<>"&MIN(B2:B11), B2:B11, "<>"&MAX(B2:B11))
6. 배열 수식 이용하기
- 최신 엑셀버전 (엑셀 2021, MS 365)가 아닌 경우에는 동적배열을 사용할 수 없기 때문에
- 배열 수식 입력 마무리는 "Enter"키가 아닌 "Ctrl + Shift + Enter"키로 해줘야 한다.
- 적용 수식 : {=AVERAGE(IF(((B2:B11=MIN($B$2:$B$11))+(B2:B11=MAX($B$2:$B$11))), "", B2:B11))}
- ① If 함수로 최소, 최대 값이 아닌 경우에 숫자를 그대로 가져오고
- 최소, 최대 값에 해당하는 경우는 공백 ( "", Null)을 가져와서 최소, 최댓값을 모두 제거해 준다.
- ② 그리고 이렇게 최소, 최대 값이 제거된 숫자들의 평균을 Average함수를 이용해서 배열 수식으로 구해주는 방식이다.
- 배열 수식에서 And 조건은 두 조건을 "곱하기"를 해주고 Or 조건의 경우는 두 조건을 "더하기"를 해준다.
- 그래서 "(최솟값이 아닌 경우) + (최댓값이 아닌 경우)"로 최소, 최대 값을 모두 제거하는 조건식을 만들 수 있다.
[세상의 모든 엑셀 (엑셀팁, 질문)] - 999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
999. 세상의 모든 엑셀(엑셀팁, 질문) 목차
[세상의 모든 엑셀 (엑셀팁, 질문)] - 001. (엑셀 팁, 질문) 누적 합계 구하기 - 절대참조/상대참조, 빠른 분석 도구 [세상의 모든 엑셀 (엑셀팁, 질문)] - 002. (엑셀 팁, 질문) 날짜 칸 나누어 입력하기
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글