Excel 에서 가장 낮은 성적을 제외하고 평균 또는 총점을 구하는 방법은 무엇인가요？
Excel 에서 성적 또는 점수 목록을 다룰 때, 학생의 최종 성적을 계산하기 위해 가장 낮은 점수 하나 또는 여러 개를 제외한 후 나머지 값들의 평균 또는 합계를 구해야 할 경우가 있습니다. 이는 교육 현장에서 흔히 요구되는 방식으로, 학생들이 이상치나 불공정한 상황을 고려해 최악의 성과를 제외할 수 있도록 하기 위함입니다. 이러한 작업을 수작업으로 수행하면 특히 대규모 데이터셋이나 자주 변경되는 계산에서는 매우 번거로울 수 있습니다. 다행히도 Excel 은 간단한 수식부터 대량 처리를 위한 VBA 자동화까지 다양한 유연한 방법을 제공합니다。
수식을 사용하여 가장 낮은 성적을 제외하고 평균 또는 합계 구하기
VBA 코드 - 가장 낮은 성적 또는 가장 낮은 n 개의 성적을 자동으로 제외하고 합계 또는 평균 계산하기
수식을 사용하여 가장 낮은 성적을 제외하고 평균 또는 합계 구하기
데이터 행 또는 목록에서 가장 낮은 값 또는 가장 낮은 n 개의 값을 제외한 후 나머지 숫자들에 대해 평균 또는 합계와 같은 계산을 수행하려는 경우, Excel 의 기본 제공 수식이 실용적인 해결책을 제공합니다。 이러한 수식 기반 해결책은 처리할 행의 수가 적당하거나， 투명성과 조정 용이성을 위해 수식 방식을 선호할 때 특히 유용합니다。
아래에서는 합계 및 평균 방법에 대해 각각 설명하며， 유연한 적용을 위한 자세한 지침과 실용적인 팁을 제공합니다。
숫자의 합계를 구하되 가장 낮은 값 또는 가장 낮은 N 개의 값을 제외하기：
각 행 또는 목록의 합계를 계산하면서 가장 낮은 값을 생략하려면 다음 방법을 사용하세요：
1. 첫 번째 행의 합계 결과를 표시할 빈 셀을 선택하세요(예: 데이터가 B2:H2 에 있으면 I2 셀)。 그런 다음 다음 수식을 입력하세요：
=SUM(B2:H2)-SMALL(B2:H2,1)2。 Enter 키를 누른 후Enter필요에 따라 채우기 핸들을 아래로 드래그하여 다른 행에도 이 수식을 적용하세요。 그러면 각 행의 모든 숫자를 합계하되 가장 낮은 값을 제외합니다。
참고용 스크린샷입니다：
참고 사항 및 팁：
- 가장 낮은 두 개， 세 개 또는 그 이상의 값을 제외하려면 추가적인SMALL결과를 빼서 수식을 확장할 수 있습니다。 예를 들어：
=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)=SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n)- 이 수식에서B2:H2는 합계를 구하려는 범위이며, 숫자 1, 2, 3 등은 제외할 n 번째로 작은 숫자를 지정합니다。n은 제거하려는 가장 낮은 성적의 개수에 따라 조정하세요。
- n이 전체 값의 개수보다 크지 않도록 주의하세요。 그렇지 않으면 오류나 원치 않는 결과가 발생할 수 있습니다。
- 이 수식은 행 단위로 독립적으로 작동합니다。 데이터가 행이 아닌 열로 구성되어 있다면 범위를 적절히 조정하세요。
- 데이터에 가장 낮은 숫자가 중복되어 있는 경우，SMALL(B2:H2,1)는 참조당 한 번만 해당 값을 제외합니다。 여러 개의 중복된 값을 제외하려면 위에서 보여준 것처럼 증가하는 k 값을 사용하여SMALL항을 반복하세요。
숫자의 평균을 구하되 가장 낮은 값 또는 가장 낮은 N 개의 값을 제외하기：
가장 낮은 값 또는 가장 낮은 n 개의 값을 무시하면서 평균을 계산하려면 아래 수식을 사용할 수 있습니다。 이 계산은 이상치로 인한 낮은 성적이 평균에 반영되지 않도록 해야 하는 성적 평가 체계에서 특히 유용합니다。
1. 평균 결과를 표시할 셀을 선택하세요(예: 성적이 B2:H2 에 있으면 J2 셀)。 그리고 다음 수식을 입력하세요：
=(SUM(B2:H2)-SMALL(B2:H2,1))/(COUNT(B2:H2)-1)2。 Enter 키를 누른 후 필요한 만큼 수식을 아래로 드래그하여 추가 행의 평균을 계산하세요。 각 행마다 해당 범위에서 가장 낮은 점수가 제외됩니다。
참고 사항 및 중요한 안내：
- 한 번에 하나 이상의 가장 낮은 점수를 제외하면서 평균을 계산하려면 추가적인SMALL항을 빼고 나누는 수를 그에 맞게 줄여 수식을 확장하세요：
=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2))/(COUNT(B2:H2)-2)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3))/(COUNT(B2:H2)-3)=(SUM(B2:H2)-SMALL(B2:H2,1)-SMALL(B2:H2,2)-SMALL(B2:H2,3)-...-SMALL(B2:H2,n))/(COUNT(B2:H2)-n)- 다시 말해，B2:H2는 평균을 계산할 범위이며，n은 계산에서 제외할 가장 낮은 값의 개수를 나타냅니다。
- 범위에 존재하는 숫자보다 더 많은 값을 빼려고 하면 수식은#NUM!오류를 반환하며， 이는 평균 계산을 위한 값이 부족함을 의미합니다。 항상n이 숫자 개수보다 작도록 확인하세요。
- 최종 결과에 영향을 줄 수 있으므로， 제외하기 전에 가장 낮은 값들이 계산에 필수적이거나 반드시 포함되어야 하는 값이 아닌지 반드시 다시 확인하는 것이 좋습니다。
- 매우 큰 데이터셋이나 동적으로 가장 낮은 n 개의 값을 제외해야 하는 경우에는 자동화된 방식이나 배열 수식을 고려하세요。
KUTOOLS AI 와 함께 엑셀의 마법을 경험하세요
- 스마트 실행： 간단한 명령어로 셀 작업을 수행하고， 데이터를 분석하며， 차트를 생성하세요。
- 사용자 지정 수식： 워크플로우를 간소화할 맞춤형 수식을 생성하세요。
- VBA 코딩： VBA 코드를 손쉽게 작성하고 적용하세요。
- 수식 해석： 복잡한 수식도 쉽게 이해하세요。
- 텍스트 번역： 스프레드시트 내에서 언어 장벽을 허물어 보세요。
VBA 코드 - 가장 낮은 성적 또는 가장 낮은 n 개의 성적을 자동으로 제외하고 합계 또는 평균 계산하기
대규모이거나 자주 변경되는 데이터셋을 다루거나, 여러 행에 걸쳐 가장 낮은 n 개의 성적을 자동으로 제외하고 합계 또는 평균을 계산해야 하는 경우 VBA 를 사용하면 반복 작업을 크게 단순화할 수 있습니다。 VBA 매크로를 통해 데이터 범위와 제외할 가장 낮은 성적 개수를 지정하면 코드가 모든 선택된 행을 한 번에 효율적으로 처리합니다。
이 자동화된 방식은 여러 반의 시트를 관리하는 교사나 수작업 수식 입력 및 잠재적 오류를 최소화하려는 사용자에게 특히 유용합니다。 아래 솔루션을 통해 제외할 개수와 대상 함수(합계 또는 평균)를 쉽게 조정할 수 있습니다。
시작하기 전에 매크로 실행은 직접 취소할 수 없으므로 워크북을 저장해 두세요。
1. 개발 도구>Visual Basic을 클릭하세요。Microsoft Visual Basic for Applications창에서삽입>모듈을 클릭한 후 다음 코드를 입력하세요：
Sub DropLowestNandCalculate()
Dim WorkRng As Range
Dim OutputRng As Range
Dim n As Integer
Dim FuncType As String
Dim i As Integer, j As Integer, k As Integer
Dim Arr() As Variant, TempArr() As Double
Dim RowSum As Double
Dim RowCount As Integer
Dim MinIdx() As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the score range (rows to process):", xTitleId, WorkRng.Address, Type:=8)
Set OutputRng = Application.InputBox("Select output cells (top-left for results):", xTitleId, WorkRng.Offset(0, WorkRng.Columns.Count).Cells(1, 1).Address, Type:=8)
n = Application.InputBox("Number of lowest grades to drop (n):", xTitleId, "1", Type:=1)
FuncType = Application.InputBox("Type 'SUM' to calculate total or 'AVG' to calculate average (not case sensitive):", xTitleId, "AVG", Type:=2)
For i = 1 To WorkRng.Rows.Count
Arr = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(WorkRng.Rows(i).Value))
RowCount = UBound(Arr)
ReDim TempArr(1 To RowCount)
For j = 1 To RowCount
TempArr(j) = Arr(j)
Next j
' Mark n lowest values as used by setting to very high number
For k = 1 To n
Dim MinVal As Double, MinPos As Integer
MinVal = Application.WorksheetFunction.Min(TempArr)
For j = 1 To RowCount
If TempArr(j) = MinVal Then
TempArr(j) = 1E+308
Exit For
End If
Next j
Next k
RowSum = 0
Dim ValidCount As Integer
ValidCount = 0
For j = 1 To RowCount
If TempArr(j) <> 1E+308 Then
RowSum = RowSum + Arr(j)
ValidCount = ValidCount + 1
End If
Next j
If UCase(FuncType) = "AVG" Then
If ValidCount = 0 Then
OutputRng.Cells(i, 1).Value = "N/A"
Else
OutputRng.Cells(i, 1).Value = RowSum / ValidCount
End If
Else
OutputRng.Cells(i, 1).Value = RowSum
End If
Next i
End Sub2. 코드를 추가한 후
실행 버튼을 클릭하거나F5키를 눌러 실행하세요。
3. 나타나는 프롬프트에 따라 진행하세요：
- 처리하고자 하는 성적 범위를 선택하세요(각 학생의 성적이 한 행에 있어야 합니다)。
- 목록 배치 영역의 상위 왼쪽 셀개를 선택하세요(행 개수에 따라 결과가 아래로 채워집니다)。
- 제외할 가장 낮은 성적의 개수를 입력하세요(예： 각 행에서 가장 낮은 성적만 제외하려면)1를 입력하세요)。
- 총점을 얻으려면(제외된 성적 제외)SUM을 입력하거나， 재계산된 평균을 얻으려면(제외된 성적 제외)AVG를 입력하세요。
매크로는 지정된 성적 영역의 각 행을 처리하고 선택한 대로 합계 또는 평균을 목록 배치 영역에 입력합니다。 한 행의 모든 성적이 제외되는 경우 오류를 방지하기 위해 결과는N/A로 표시됩니다。
- 입력 범위이 데이터 구조와 일치하는지 확인하세요(한 행에 한 학생의 성적이 있어야 합니다)。
- 숫자가 아닌 셀(예： 공백 또는 텍스트)은 기본적으로 무시됩니다。
- 이 VBA 코드는 전체 반의 반복적인 성적 계산을 크게 가속화하며， 제외할 성적 개수를 유연하게 조정할 수 있도록 지원합니다。
- 이러한 작업을 자주 수행한다면 이 매크로를 워크시트의 버튼에 할당하여 더욱 빠르게 접근할 수도 있습니다。
잘못된 출력이나 오류가 발생하면 범위가 정확히 지정되었는지， 그리고 「n」이 각 행의 사용 가능한 성적 총 개수보다 크지 않은지 다시 확인하세요。
가장 높은 점수와 가장 낮은 점수를 동시에 제외하거나， 행이 아닌 열을 처리하는 등 비슷한 자동화 요구사항이 있는 경우 VBA 코드 로직을 약간 수정할 수 있습니다。
최고의 Office 생산성 도구
| 🤖 | KUTOOLS AI 도우미： 다음을 기반으로 데이터 분석 혁신하기：지능형 실행 | 코드 생성| 사용자 지정 수식 생성 | 데이터 분석 및 차트 생성| 향상된 함수 호출… |
| 인기 기능:찾기， 강조 표시 또는 중복 표시 | 빈 행 삭제 | 데이터 손실 없이 열 결합 또는 셀 제거 | 공식을 사용하지 않는 반올림... | |
| 슈퍼 LOOKUP:다중 조건 VLookup | 다중 값 VLookup | 여러 시트에서 VLookup | 퍼지 매치.... | |
| 고급 드롭다운 목록:드롭다운 목록 빠르게 생성 | 종속형 드롭다운 목록 | 다중 선택 드롭다운 목록.... | |
| 열 관리자:특정 수의 열 추가|열 이동|숨겨진 열의 표시 상태 전환|범위 및 열 비교... | |
| 주요 기능:그리드 포커스 | 디자인 보기 |향상된 수식 표시줄 | 워크북 및 시트 관리자 | 자원 라이브러리(자동 텍스트)| 날짜 선택기 | 워크시트 병합 | 암호화/셀 해독 | 목록으로 이메일 보내기 | 슈퍼 필터 | 특수 필터(굵은 글꼴이 있는 셀 필터링/기울임꼴/취소선。。。) 。。。 | |
| 상위 15 도구 모음:12 텍스트도구(텍스트 추가,특정 문자 삭제, ...)| 50+차트유형(간트 차트, ...)| 40+ 실용적인수식(생일을 기준으로 나이 계산, ...)| 19 삽입도구(QR 코드 삽입,경로에서 그림 삽입, ...)| 12 변환도구(단어로 변환하기,환율 변환, ...)| 7 병합 및 분할도구(고급 행 병합,셀 분할, ...)|그 외 더 많은 기능 |
Kutools for Excel 로 Excel 역량을 한 단계 업그레이드하고 전례 없는 효율성을 경험하세요。Kutools for Excel 는 생산성과 저장 시간을 향상시키는 300 개 이상의 고급 기능을 제공합니다。가장 필요한 기능을 지금 바로 확인하세요。。。
Office Tab 가 Office 에 탭 인터페이스를 제공하여 작업을 훨씬 쉽게 만들어 줍니다
- Word, Excel, PowerPoint 에서 탭 기반 편집 및 읽기 기능을 활성화합니다, Publisher, Access, Visio 및 Project 에서도 사용 가능합니다。
- 새 창이 아닌 동일한 창의 새 탭에서 여러 문서를 열고 생성할 수 있습니다。
- 50% 만큼 생산성을 높이고 매일 수백 번의 마우스 클릭을 줄여줍니다！
모든 Kutools 애드인。 하나의 설치 프로그램
Kutools for Office스위트 번들은 Excel, Word, Outlook 및 PowerPoint 용 애드인과 Office Tab Pro 를 포함하며， 다양한 Office 앱을 사용하는 팀에 이상적입니다。
- 올인원 스위트— Excel， Word， Outlook 및 PowerPoint 애드인 + Office Tab Pro
- 하나의 설치 프로그램， 하나의 라이선스— 몇 분 안에 설정 완료(MSI 지원)
- 함께 사용할수록 더 효과적입니다— Office 앱 전반에서 생산성 향상
- 30 일간 모든 기능 무료 체험— 등록이나 신용카드 필요 없음
- 최고의 가성비— 개별 애드인 구매 대비 절약