필터-복사-붙여넣기 그만: 엑셀 FILTER·SORT·UNIQUE로 목록을 자동 추출하고 데이터 흐름 구축하기

서울 지점의 거래 목록을 따로 보고 매출이 큰 순서로 정렬하려면, 조건에 맞는 행을 고르는 작업과 정렬하는 작업을 구분하면 수식을 검산하기 쉽습니다. 이 예제는 가상 거래 8건에서 서울 4건을 추출하고, 매출 순서와 중복 없는 지점 목록을 확인합니다. 실제 회사 데이터나 작성자의 업무 경력을 근거로 한 사례는 아닙니다.

1. 원본 8행과 출력 위치

원본 시트 A1:E9에 아래 표를 입력합니다. 날짜는 Excel이 인식하는 날짜 값, 수량과 매출은 숫자로 넣습니다. 추출 시트 B2가 지점 입력 셀이며 처음에는 서울을 입력합니다.

원본 순서를 유지한 추출 결과는 추출!A6:E13, 매출순 추출 결과는 G6:K13, 중복 없는 지점은 M6:M13에 펼쳐지도록 준비합니다. 거래가 최대 8건이므로 A6과 G6부터 8행을 비워 둡니다. 건수 확인 셀은 출력 영역과 겹치지 않는 B21에 둡니다. 기존 값이 있으면 지우기 전에 다른 위치에 보관하세요.

첨부 파일은 원본과 추출 시트로 구성돼 있습니다. 노란 입력 셀과 수식 출력 셀을 구분했으며, 원본 A2:E9와 정렬용 보조 범위 H2:L9는 서로 겹치지 않습니다.

2. FILTER로 지점이 같은 행 추출

Microsoft의 FILTER 문서는 지정한 조건에 맞는 데이터를 배열로 반환하는 기능과, 결과가 없을 때 표시할 세 번째 인수를 설명합니다. 이 예제에서는 지점이 없을 때 해당 없음, 입력 셀이 비었을 때 지점 입력으로 구분합니다.

추출!A6에 다음 수식을 입력합니다.

=FILTER(원본!A2:E9,(원본!B2:B9=$B$2)*($B$2<>""),IF($B$2="","지점 입력","해당 없음"))

첫 번째 조건은 원본의 지점과 B2가 같은지를 확인합니다. 두 번째 조건은 B2가 비어 있지 않은지 확인합니다. 두 조건을 곱해 둘 다 맞는 행만 남깁니다. 세 번째 인수의 IF는 행 배열을 감싸지 않고, 반환할 행이 하나도 없을 때 쓸 메시지만 정합니다.

B2가 서울이면 원본 2·4·6·8행의 거래가 순서대로 표시되어야 합니다. 추출 E6:E9의 매출은 180000, 135000, 640000, 196000입니다. 이 네 금액의 합계는 1151000입니다. 수량 합계는 2+3+2+4=11입니다.

일치하지 않는 지점은 전체 거래가 아니라 해당 지점의 결과 없음으로 처리합니다. 메시지가 나왔다는 이유만으로 원본 파일이 비었거나 수식이 고장 났다고 판단하지 않습니다.

3. SORT 보조 범위로 매출 내림차순 만들기

조건에 맞는 행이 없을 때는 목록 대신 메시지 한 셀만 반환됩니다. 이 메시지에 다섯 번째 열 기준 정렬을 적용하지 않도록, 여기서는 원본 8행을 먼저 정렬한 뒤 같은 지점 조건을 적용합니다. 원본 표 자체를 수동으로 재정렬하지 않습니다.

원본!H1:L1에 원본과 같은 머리글을 넣고 H2에 다음 수식을 입력합니다.

=SORT(A2:E9,5,-1)

Microsoft의 SORT 문서에서 정렬 순서 -1은 내림차순을 뜻합니다. 이 표의 다섯 번째 열은 매출이므로 보조 배열 H2:L9가 매출 내림차순으로 만들어집니다. 전체 원본이 아니라 매출 열만 따로 정렬하면 날짜·품목과 금액의 대응이 깨지므로 다섯 열을 함께 참조합니다.

이제 추출!G6에는 정렬된 보조 배열에서 같은 지점을 고르는 수식을 입력합니다. 보조 배열의 지점 열은 I열입니다.

=FILTER(원본!H2:L9,(원본!I2:I9=$B$2)*($B$2<>""),IF($B$2="","지점 입력","해당 없음"))

서울일 때 K6:K9의 금액은 640000, 196000, 180000, 135000이어야 합니다. 각 금액 옆의 품목도 모니터·허브·키보드·마우스 순서인지 확인합니다. 목록 두 곳의 행 수와 매출 합계가 같고 순서만 달라야 합니다. 같은 금액의 거래끼리 어떤 순서를 따를지는 이 예제의 검산 기준에 포함하지 않습니다.

4. UNIQUE는 전체 원본의 지점 목록

추출!M6에 다음 수식을 넣습니다.

=UNIQUE(원본!B2:B9)

Microsoft의 UNIQUE 문서는 범위에서 중복 없는 값을 반환하는 기능을 설명합니다. 이 예제의 고유 지점은 서울·부산·대전, 총 3개입니다. 이 수식은 전체 원본을 참조하므로 B2를 부산으로 바꿔도 고유 지점 목록은 서울 한 곳이나 부산 한 곳으로 줄지 않습니다.

여기서 중복 제거하는 대상은 지점 이름 목록입니다. 원본의 거래 행을 지우는 것이 아니며, 같은 지점에 거래가 여러 건 있는 것은 정상입니다. 지점 목록에 새로운 이름이 보이면 철자·앞뒤 공백이 다른 값인지 원본을 대조합니다. 수식을 넣기 전에 임의로 공백을 모두 삭제해 서로 다른 식별자를 합치지 않습니다.

5. 서울·부산·없는 지점·빈 입력을 차례로 검산

추출!B21의 건수는 목록과 같은 등호 조건으로 계산합니다. 별표가 입력됐을 때 와일드카드 개수와 정확 일치 목록이 서로 다른 결과를 내지 않도록, 아래처럼 같은 비교식을 사용합니다.

=IF(B2="","지점 입력",SUMPRODUCT((원본!B2:B9=B2)*1))

부산 매출 합계는 410000입니다. 대구는 원본에 없는 지점이므로 결과 없음이 정상입니다. 별표는 이 비교식에서 원본 지점과 대조하는 문자이며 모든 지점을 뜻하는 입력으로 사용하지 않습니다. 각 시험 뒤 B2를 서울로 복원해 4건과 원래 매출순으로 돌아오는지 확인합니다.

수치와 행 목록은 첨부의 가상 입력으로 계산한 기대값입니다. 제작 과정에서는 Microsoft Excel 앱을 실행하지 않았습니다. 파일을 연 뒤 설치된 Excel에서 이 표의 조건 변경과 복원을 확인하고 실제 업무 자료에 적용하세요.

6. 증상별 원인과 복구

서울이 4건 대신 3건으로 나오면 원인을 함수 고장으로 단정하지 않습니다. 가능한 원인은 지점 값의 공백·철자 차이 또는 입력 범위의 행 누락입니다. 원본 B2:B9의 네 서울 행과 FILTER의 입력·조건 범위를 대조해 어느 행이 빠졌는지 먼저 찾으세요. 지점 값을 바꾸거나 범위를 늘리기 전에 원본과 수식 사본을 보관하고, 수정 뒤 같은 서울 4행이 돌아오는지 확인합니다.

#SPILL! 오류가 표시되면 출력 영역의 값·수식·병합 셀을 확인합니다. A6:E13이나 G6:K13 안에 합계나 메모를 넣으면 결과 공간과 충돌할 수 있습니다. 먼저 기존 자료를 옮기고 다시 계산합니다. 출력 셀 일부를 수정하는 대신 수식의 시작 셀이나 원본 입력을 수정합니다.

#CALC!가 나오면 FILTER의 빈 결과 인수를 빠뜨렸는지 확인합니다. 이 예제에서 대구를 넣었을 때는 해당 없음 한 셀이 나와야 합니다. 그것을 정상 거래 1건으로 세지 않습니다. 표의 첫 번째 머리글 행은 추출 입력에 포함하지 않고 A2:E9만 사용합니다.

수식을 지원하지 않는 환경에서는 함수명이 인식되지 않을 수 있습니다. Microsoft 문서의 적용 대상을 확인하고, 지원 환경에서 파일을 열거나 Power Query 등 별도 방법으로 같은 입력·출력을 검산합니다. 기존 배열 입력 방식으로 바꾸기만 하면 FILTER가 추가된다고 안내하지 않습니다.

원본 10행에 새 거래를 붙여도 고정 범위 A2:E9에는 포함되지 않습니다. 행을 늘릴 때는 원본 목록, 정렬 보조 범위, 두 FILTER의 입력·조건 범위, UNIQUE, 건수 수식과 출력 공간을 함께 바꿉니다. 원본 범위가 늘었는데 조건 범위는 8행 그대로인 상태로 두지 않습니다. 첨부의 고정 8행 예제는 새 행 자동 확장을 보장하지 않습니다.

7. 목록·개수·합계의 역할을 구분하기

FILTER의 결과는 조건에 맞는 거래 행 목록이고 SORT는 그 목록의 순서를 바꾸는 데 쓰며 UNIQUE는 중복 없는 지점 이름을 보는 데 씁니다. 단순 합계나 평균을 구하려면 해당 집계 함수를 별도로 선택합니다. 조회 함수가 여러 거래의 합계를 자동으로 계산한다고 설명하지 않습니다.

이 예제에는 LET 변수에 표를 대입한 뒤 변수[열]을 Excel 표처럼 사용하는 구문을 넣지 않았습니다. 실제 시트 범위와 보조 배열의 좌표가 드러나므로 입력과 결과를 직접 대조할 수 있습니다. 속도나 업무 경력에 관한 검증되지 않은 주장을 결과의 근거로 삼지 않습니다.

공식 문서와 확인 범위

• FILTER 함수: 조건 배열과 빈 결과 표시.

• SORT 함수: 정렬 기준과 내림차순 옵션.

• UNIQUE 함수: 중복 없는 값 목록.

출처 원문 수집일: S1 2026-09-23; S2 2026-09-23; S3 2026-09-23. 실제 계산 기대값은 본문의 가상 거래에서 산출했으며 문서의 예제 수치를 가져온 것은 아닙니다.

작성자 소개

최지원

엑셀·VBA·파이썬·AI 업무 자동화의 사용 절차와 실습 자료를 정리합니다.

WP 게시물 작성자의 글 보기