엑셀 SMALL 함수: 하위 순위·동점 품목·조건별 계산
세 번째로 작은 값과 정확히 세 개의 품목을 고르는 일은 다릅니다. 단가 450인 품목이 두 개라면 SMALL은 450을 두 번 반환할 수 있고, 그 값을 첫 일치 조회에 넣으면 같은 품목명이 반복됩니다. 가상 재고 여섯 행에서 값·동점 품목·조건 입력을 따로 검산하겠습니다.
도해의 하위 순위와 동점 예제를 실습 파일의 재고·조건·편차순위 시트에서 대조하세요. 동점을 없애려고 원본 단가에 작은 수를 더하지 않습니다.
80·300·450·450이 나오는 가상 입력
재고 A4:F10의 단가는 실제 판매 가격이 아닌 합성 숫자입니다.
| ID | 가상 품목 | 가상 단가 | 등급 |
|---|---|---|---|
| P01 | 노트 | 1200 | A |
| P02 | 펜 | 450 | C |
| P03 | 파일 | 450 | C |
| P04 | 클립 | 80 | C |
| P05 | 마우스패드 | 900 | B |
| P06 | 케이블 | 300 | B |
-
C5:C10의 여섯 숫자와 F열 ISNUMBER를 확인합니다. 숫자처럼 보이는 문자 450을 숫자 입력과 동일하게 취급하지 않습니다.
-
A15:A20의 k=1~6에 대해 B15의
=SMALL($C$5:$C$10,A15)를 아래로 확인합니다. 결과는 80·300·450·450·900·1200입니다. -
C열 첫 일치 품목과 D열 동점 분리 품목을 비교합니다. k=3·4에서 C는 펜·펜, D는 펜·파일이어야 합니다.
Microsoft SMALL 설명은 데이터 집합에서 k번째 작은 값을 반환한다고 설명합니다. 이 값만으로 어떤 행을 선택할지는 정해지지 않습니다.
첫 일치 조회가 펜을 두 번 반환하는 이유
재고 C17:C18은 INDEX·MATCH의 첫 일치 조회입니다. 두 행 모두 찾는 값이 450이므로 앞에 있는 펜이 나옵니다. 수식 오류가 아니라 동점 처리 요구가 부족한 결과입니다.
E5의 아래 수식을 E10까지 적용하면 “자기 값보다 작은 행 수 + 현재 행까지 같은 값의 개수”가 됩니다. 가상 입력의 E5:E10은 6·3·4·1·5·2입니다.
=COUNTIF($C$5:$C$10,"<"&C5)+COUNTIF($C$5:C5,C5)D열은 이 고유 순번에서 k를 조회해 동점 펜과 파일을 원본 행 순서대로 구분합니다. 이 규칙은 고정 ID가 아니며 원본 순서를 바꾸면 동점 순서도 달라집니다. 실제 업무가 다른 동점 기준을 요구하면 날짜·우선순위·ID 등 명시적인 기준을 추가해야 합니다.
같은 표를 직접 만들려면 재고!A15:A20에 숫자 1~6을 넣고, 아래 두 식을 각각 C15와 D15에 입력한 뒤 20행까지 아래로 복사합니다. B15:B20에는 앞 절의 SMALL 수식이 있어야 합니다.
C15: 단가가 처음 일치하는 품목
=INDEX($B$5:$B$10,MATCH(B15,$C$5:$C$10,0))D15: 고유 순번이 일치하는 품목
=INDEX($B$5:$B$10,MATCH(A15,$E$5:$E$10,0))C열은 B15의 단가를 찾고 D열은 A15의 순위 k를 찾습니다. D15:D20의 기대 순서는 클립·케이블·펜·파일·마우스패드·노트입니다. k=4인 18행에서 MATCH(A18,$E$5:$E$10,0)은 상대 위치 3을 반환하고, B5:B10의 세 번째 품목인 파일을 가져옵니다. 시트 행 번호 7을 반환하는 것이 아닙니다. Microsoft MATCH · INDEX 설명
이 두 조회식은 기본 여섯 단가가 모두 숫자이고 k가 1~6이라는 전제입니다. k에 빈칸·문자·7을 넣는 입력 검사는 다음 절의 조건 시트에서 따로 수행합니다. 여기서는 IFERROR로 조회 실패를 빈칸으로 가리지 않습니다.
조건에 맞는 숫자 개수부터 세기
조건 A·B열은 등급과 k, C열은 해당 등급의 숫자 단가 수입니다. H:M에는 여섯 원본 각각이 등급과 숫자 검사를 통과할 때만 단가를 남깁니다. 이 첨부는 FILTER가 없는 보조셀 방식으로 입력 범위를 눈으로 검산하도록 만들었습니다.
등급 C의 숫자는 450·450·80 세 개입니다. C·k=2와 C·k=3의 결과는 모두 450이고, C·k=4는 k 범위 초과입니다. 없는 등급 Z·k=1은 대상 없음입니다. B·k=1은 300입니다.
D열은 k가 숫자인지, 1 이상의 정수인지, 대상이 있는지, 범위 안인지 순서대로 확인합니다. k=0·1.5는 양의 정수, 문자 two·빈칸은 숫자 입력입니다. E열은 D가 계산일 때만 SMALL(H5:M5,B5)를 실행합니다. 오류를 0으로 바꾸면 실제 단가 0과 혼동하므로 사유를 남깁니다.
조건 시트의 보조셀부터 결과까지 직접 연결하기
조건!A5에 C, B5에 숫자 2를 입력합니다. H5:M5는 원본 여섯 품목을 가로로 펼친 보조셀입니다. H5에 아래 식을 넣습니다.
=IF(AND('재고'!$D$5=$A5,ISNUMBER('재고'!$C$5)),'재고'!$C$5,"")H5를 오른쪽으로 복사하기만 하면 고정 참조 $D$5·$C$5가 그대로 남습니다. I5에서는 두 원본 주소를 $D$6·$C$6, J5는 $D$7·$C$7, K5는 $D$8·$C$8, L5는 $D$9·$C$9, M5는 $D$10·$C$10으로 바꿉니다. 등급 입력 $A5는 여섯 식에서 같습니다. 기대 내용은 빈 표시·450·450·80·빈 표시·빈 표시입니다. 제외 값을 0으로 바꾸면 SMALL의 작은 값에 0이 섞이므로 빈 문자열을 유지합니다.
C5: 조건에 맞는 숫자 개수
=SUMPRODUCT(('재고'!$D$5:$D$10=A5)*ISNUMBER('재고'!$C$5:$C$10))D5: k와 대상 개수 검사
=IF(NOT(ISNUMBER(B5)),"숫자 입력",IF(OR(B5<1,B5<>INT(B5)),"양의 정수",IF(C5=0,"대상 없음",IF(B5>C5,"k 범위 초과","계산"))))E5: 검사 통과 후 계산
=IF(D5<>"계산",D5,SMALL(H5:M5,B5))A5=C·B5=2이면 C5=3, D5=계산, E5=450입니다. B5만 4로 바꾸면 D5와 E5는 k 범위 초과입니다. B5를 1로 하고 A5를 Z로 바꾸면 대상 없음입니다. 각 실험 후 C·2로 복원하세요. 다른 시험 행으로 확장할 때 C5:E5와 H5:M5를 아래로 복사하면 등급과 k의 행만 함께 이동합니다. 원본 단가 자체에 오류 값이 있는 경우는 이 숫자/텍스트 예제의 범위 밖이며 원본 오류를 먼저 확인합니다.
본문의 보조셀 방식은 FILTER나 동적 배열을 사용하지 않으며 각 셀에서 Enter로 입력합니다. Microsoft의 SMALL 적용 대상에는 Excel 2016·2019·2021·2024와 Microsoft 365가 포함됩니다. 수식은 쉼표 구분자 기준이며 환경에서 세미콜론을 요구하면 인수 구분자를 맞춥니다.
세 번째 편차 이하인데 네 행이 남는 경우
편차순위의 가상 목표는 모두 100, 실제는 90·95·105·110입니다. 방향편차는 -10·-5·5·10, ABS는 10·5·5·10입니다. 세 번째로 작은 절대편차 B7은 10이며, 10 이하의 행 수 B8은 4입니다.
따라서 “세 번째 값 이하”라는 기준은 정확히 세 행을 고르는 규칙이 아닙니다. 정해진 행 수가 필요하면 동점 처리 순서를 먼저 합의하고 그 순번으로 선택해야 합니다. 값의 경계를 몰래 변경해 네 번째 행을 제외하지 마세요.
조건과 데이터형을 바꾸는 실패·복구 검산
대상 숫자 개수보다 큰 k를 요구하는 오류부터 재현합니다. 사본에서 클립의 등급 재고 D8을 C에서 B로 바꾸면 조건 C의 숫자 수는 2입니다. C·k=2는 여전히 450이지만 C·k=3은 k 범위 초과이고 B·k=1은 80으로 달라집니다. D8을 C로 되돌려 숫자 수 3·B 최솟값 300으로 복구되는지 확인합니다.
펜 단가 C6을 문자형 450으로 바꾸면 F6은 FALSE이고 조건 C의 숫자 수는 2입니다. C·k=3은 보류됩니다. 원본 근거에 따라 숫자 450을 다시 입력해야 복구됩니다. 데이터형이 섞인 동안 재고의 동점 분리 E열을 일반적인 순위 정답으로 읽지 마세요. E열은 기본 여섯 숫자 입력을 위한 예제이며 조건 시트의 숫자 검사와 목적이 다릅니다.
조건 B5를 2에서 1로 바꾸는 반례에서는 결과 E5가 80으로 바뀌어 고정 기대 450과의 G5 대조가 FALSE입니다. 입력 변경 실험이므로 정상적인 차이입니다. B5를 2로 복구하면 다시 450·TRUE입니다.
상위 순위도 필요한 경우 LARGE의 상위값과 동점 품목 검산을 비교하세요. 같은 여섯 품목의 450 동점과 첫 일치 조회를 다루므로 SMALL 예제와 순위 방향을 바꿔 대조할 수 있습니다.
보이는 행과 전체 범위의 차이
SMALL이 참조하는 범위에는 숨기거나 필터로 화면에서 안 보이게 만든 원본도 남습니다. 이 첨부의 등급 조건은 H:M의 명시적 값 검사이며 화면에서 보이는 행만 고르는 기능은 아닙니다. 데이터 행을 늘리면 재고 범위·조건 보조셀·숫자 수 계산을 함께 확장해야 합니다. 값의 순위, 동점 품목, 입력 유효성을 각각 확인한 뒤 원본 재고에 적용하세요.
SMALL은 데이터 집합에서 k번째로 작은 값을 반환합니다.
COUNTIF는 조건을 만족하는 셀의 개수를 셉니다.
출처 원문 수집일: S1 2026-09-24; S2 2026-09-24.
작성자 소개
