FILTER·SORT·UNIQUE로 대기 품목 목록을 뽑아 정렬하는 방법
대기 품목 목록을 만든 뒤 수량을 합산했는데 원본의 대기 합계와 다르면, 목록의 뜻부터 확인해야 합니다. 같은 SKU가 출고 행과 대기 행에 동시에 있으면 고유 품목 목록만으로는 두 상태를 구분할 수 없습니다. 이 글에서는 원래 예제의 일곱 행을 그대로 쓰고 대기인 행, 대기인 고유 품목, 그 품목의 모든 행을 따로 검산합니다.
필요한 것은 FILTER·SORT·UNIQUE를 사용할 수 있는 Excel 환경과 연습 사본입니다. 첨부는 가상 데이터만 사용하며 외부 연결과 매크로가 없습니다. 여기서 얻는 것은 작업 목록과 그 목록의 정확성을 확인하는 방법입니다. 재고 실사나 출고 시스템을 대신하는 자료는 아닙니다. Microsoft Excel 앱은 실행하지 않았으며, 저장된 수식의 LibreOffice 재계산과 Python 독립 계산을 구분해 제공했습니다.
1. 일곱 행 원본과 정답을 먼저 고정하기
원본 A1:D8에 아래 표가 들어 있습니다. SKU-11은 대기 상태가 두 행이고, SKU-12는 출고와 대기가 각각 한 행입니다. 중복 자체가 오류는 아닙니다. 목록을 만들기 전에 어떤 행을 포함할지 결정합니다.
| 행 | 예시코드 | 예시상태 | 예시구역 | 예시수량 |
|---|---|---|---|---|
| 2 | SKU-11 | 대기 | 동 | 4 |
| 3 | SKU-12 | 출고 | 서 | 2 |
| 4 | SKU-11 | 대기 | 남 | 1 |
| 5 | SKU-13 | 대기 | 동 | 3 |
| 6 | SKU-12 | 대기 | 서 | 5 |
| 7 | SKU-14 | 보류 | 동 | 2 |
| 8 | SKU-15 | 대기 | 남 | 6 |
손으로 확인한 대기 행은 2·4·5·6·8행입니다. 따라서 대기 행 수는 5, 수량은 4+1+3+5+6=19입니다. 고유 코드는 SKU-11·SKU-12·SKU-13·SKU-15 네 개입니다. 출고 행이 있다는 이유로 SKU-12 전체를 빼면 안 됩니다. 6행의 대기 수량 5가 함께 빠지기 때문입니다.
2. 먼저 대기 행을 추출한 뒤 중복을 제거하기
목록 A2의 식은 다음과 같습니다. A2:A6에는 원본 행 순서대로 SKU-11, SKU-11, SKU-13, SKU-12, SKU-15가 표시되어야 합니다.
=FILTER('원본'!A2:A8,'원본'!B2:B8="대기","해당 없음")FILTER는 지정한 조건에 따라 데이터 범위를 필터링합니다. 추출 범위 A2:A8과 조건 범위 B2:B8의 높이를 일치시킨 것이 중요합니다. 한쪽만 9행까지 늘리거나 머리글을 포함하면 같은 행끼리 판정한다는 전제가 깨집니다. 새 행을 추가하는 실제 표에서는 두 범위를 함께 바꾸고 아래 고정 예제와는 별도로 검산하세요.
목록 B2에는 아래 식이 저장되어 있습니다. 기대 결과는 처음 등장한 순서의 SKU-11, SKU-13, SKU-12, SKU-15입니다. UNIQUE의 기본 사용은 목록이나 범위에서 고유한 값 목록을 반환합니다. 이 예제에서는 exactly_once를 참으로 설정하지 않습니다. 두 번 등장한 SKU-11도 하나의 대기 품목으로 남겨야 하기 때문입니다.
=UNIQUE(FILTER('원본'!A2:A8,'원본'!B2:B8="대기","해당 없음"))
3. 정렬 결과를 별도의 정답 열과 비교하기
목록 C2의 전체 식입니다. 이 글의 최종 목록은 C2:C5이고, D2:D5에는 앞서 손으로 정한 정답 네 개를 별도로 넣었습니다.
=SORT(UNIQUE(FILTER('원본'!A2:A8,'원본'!B2:B8="대기","해당 없음")))SORT는 범위나 배열의 내용을 정렬하며 정렬 방향을 생략하면 오름차순을 사용합니다. 가상 코드가 같은 길이의 SKU-숫자 두 자리 형식이므로 기대 순서는 11·12·13·15입니다. 실제 코드가 SKU-2와 SKU-11처럼 자릿수가 섞여 있다면 문자 정렬 순서와 숫자 부분의 크기 순서를 같은 것으로 보지 마세요. 이 실습은 그 코드 정규화까지 수행하지 않습니다.
E2에는 =IF(C2=D2,"일치","확인")이 들어 있고 E5까지 채워져 있습니다. E2:E5 네 칸이 모두 일치인지 봅니다. C2:C5를 자기 자신과 비교하거나, 정답 D열을 C열 수식으로 연결하면 오답도 통과하므로 정답은 고정 문자열로 유지합니다. 이 고정 네 행 검사는 위 가상 원본!A2:D8 예제에 한정됩니다. 입력 행 수가 바뀌면 정답 행 수도 새로 정해야 합니다.
4. 대기 수량 19와 품목 전체 수량 21을 구분하기
목록 B9는 대기 행 수 5, B10은 대기 행 수량 19, B11은 전체 행 수량 23입니다. B10의 수식은 상태를 직접 조건으로 사용합니다.
=SUMIF('원본'!B2:B8,"대기",'원본'!D2:D8)반면 B12에는 의도적으로 다른 질문을 하는 식이 있습니다. 아래 식은 정렬 목록에 속한 품목의 모든 행을 더합니다. 결과 21은 대기 수량 정답이 아닙니다.
=SUMPRODUCT('원본'!D2:D8,--(COUNTIF(C2:C5,'원본'!A2:A8)>0))SKU-12가 목록에 있으므로 이 식에는 원본 3행의 출고 수량 2도 포함됩니다. 계산은 4+2+1+3+5+6=21입니다. 여기에 보류 품목 SKU-14의 수량 2까지 포함한 전체는 23입니다. 서로 다른 세 합계를 같은 결과라고 비교하면 잘못된 수정에 시간을 쓰게 됩니다. 목록의 코드 존재 여부와 행의 상태 조건을 혼동하지 않는 것이 이 검산의 핵심입니다.
5. 조건이 없거나 공백이 끼어드는 반례 확인하기
경계 B2는 없는 상태인 검사대기를 찾습니다. FILTER의 세 번째 인수로 지정한 해당 없음 한 칸이 나옵니다. 이 문구는 실제 SKU가 아니며 품목 수에 포함하지 않습니다. 조건 결과가 없는 상황을 1개 품목으로 세지 않도록 원본 조건 건수도 함께 확인하세요.
경계 B3은 상태가 대기이면서 구역이 동인 행을 고릅니다. 결과는 B3:B4의 SKU-11, SKU-13입니다. 두 조건의 행별 논리 배열을 곱한 식이며, 전체 범위를 단일 참/거짓으로 줄이는 AND 함수로 대체하지 않습니다.
=SORT(UNIQUE(FILTER('원본'!A2:A8,('원본'!B2:B8="대기")*('원본'!C2:C8="동"),"해당 없음")))사본에서 원본 B6을 대기 뒤에 공백 한 칸이 붙은 값으로 바꾸면 정확히 대기인 조건과 달라집니다. 목록에서 SKU-12가 빠지고 대기 수량은 14가 됩니다. 경계 B7:B9는 6행을 제외했을 때의 목록 SKU-11·SKU-13·SKU-15를 원본을 훼손하지 않고 보여 주는 비교입니다. B6의 뒤 공백을 제거해 정확한 대기로 되돌리면 네 품목과 수량 19로 복구되어야 합니다.
또 다른 변형으로 B4만 비우면 SKU-11은 여전히 B2에 남아 있으므로 고유 목록은 그대로입니다. 대기 행 수는 4, 수량은 18이 됩니다. 고유 목록이 같다고 원본 데이터도 같아졌다는 뜻은 아닙니다. B4를 대기로 복구한 뒤 목록·행 수·수량을 모두 대조하세요.
6. 스필 영역 문제와 수식 문제를 분리하기
세 수식은 A2, B2, C2의 시작 셀에만 들어 있습니다. 아래 결과 칸은 수식이 펼쳐지는 영역이므로 각 칸에 다른 메모를 입력하지 않습니다. Excel에서 스필 영역이 막혔다는 오류가 보이면 시작 수식이 요구하는 범위와 기존 셀 내용을 확인합니다. 다른 업무 메모가 있으면 먼저 안전한 위치로 옮기고 다시 계산합니다. 주변 데이터를 무조건 지워서 오류를 없애지 마세요.
#NAME?나 지원되지 않는 함수가 표시되면 목록이 틀렸다고 코드 자체를 고치기 전에 사용하는 Excel의 함수 지원 여부를 확인합니다. 첨부에는 위 동적 배열 식만 있으며 별도의 대체식은 없습니다. 빈 결과를 숨기려고 전체 식에 IFERROR를 덮으면 범위 불일치나 입력 오류까지 가려질 수 있습니다. 없는 조건은 FILTER의 세 번째 인수로 다루고 다른 오류는 원인을 확인합니다.
7. 검산 범위와 실제 업무 적용 한계
ZIP의 verify_contract.py는 원본 일곱 행을 읽어 필터·고유 목록·정렬·합계를 독립 계산합니다. 조건 없음, 동 구역의 교집합, 뒤 공백, 중복 대기 한 행 제거와 복구도 검사합니다. 실제 XLSX의 입력·수식·저장된 결과 캐시와 이 정답을 대조하므로 파일 이름만 맞는 첨부로 대체할 수 없습니다.
이 검산에서 Microsoft Excel의 스필 UI 오류를 직접 발생시키지는 않았습니다. 첨부의 가상 목록!C2:C5는 LibreOffice 계산값을 검산한 것이며 Microsoft Excel 앱에서 얻은 결과는 아닙니다. 코드 대소문자, 누락 수량, 음수·소수 수량, 실제 상태 코드 정규화는 별도의 업무 계약입니다. 그런 입력을 넣기 전에 무엇을 포함할지 정하고, 원본 보존 사본에서 행 수와 수량 검산까지 다시 수행하세요.
본문과 같은 입력으로 연습하기
실습 XLSX에는 본문에서 지정한 셀과 수식이 들어 있습니다. 검산 자료 ZIP에는 같은 XLSX와 입력·수식 CSV, 실습 설명, README 및 오프라인 Python 검산 코드가 있습니다. 원본 업무 파일을 덮어쓰지 말고 사본으로 연습하세요. Microsoft Excel 앱의 실제 실행 결과로 제공하는 파일은 아닙니다.
공식 자료
FILTER function | Microsoft Support
UNIQUE function | Microsoft Support
SORT function | Microsoft Support
출처 원문 수집일: S1 2026-09-24; S2 2026-09-24; S3 2026-09-24.
작성자 소개
