Ch07_데이터 관리-정렬과 필터링

학습목표

이 장을 학습한 후에는 다음 작업을 수행할 수 있다.

• TXT 및 CSV 파일의 구조를 이해할 수 있다.
• 텍스트 파일을 Power Query로 가져올 수 있다.
• 가져온 열의 데이터 형식을 정확하게 지정할 수 있다.
• 외부 데이터 연결을 새로 고칠 수 있다.
• 하나 이상의 열을 기준으로 데이터를 정렬할 수 있다.
• 사용자 지정 목록과 셀 서식을 기준으로 정렬할 수 있다.
• 자동 필터와 사용자 지정 필터를 사용할 수 있다.
• 조건 범위를 작성하여 고급 필터를 실행할 수 있다.
• 필터 결과를 다른 위치에 복사하고 고유한 레코드를 추출할 수 있다.

실습 파일

• 데이터 형식: UTF-8 탭 구분 TXT
• 데이터 수: 머리글 1행 + 판매 데이터 180행
• 필드 수: 17개
• 데이터 기간: 2026년 1월 2일～6월 30일

CH07_외부데이터_판매내역_180건.txt

0.02MB

필드	내용	데이터형식
판매ID	판매 데이터 식별 코드	텍스트
주문일자	상품 주문 날짜	날짜
요일	주문 요일	텍스트
지역	서울, 부산, 대구 등	텍스트
지점	지역별 판매 지점	텍스트
담당자	판매 담당자	텍스트
고객등급	일반, 실버, 골드, VIP	텍스트
제품분류	노트북, 태블릿, 모니터 등	텍스트
상품명	판매 상품 이름	텍스트
결제수단	신용카드, 계좌이체 등	텍스트
판매수량	판매한 상품 수량	정수
단가	상품 한 개의 가격	정수
할인율	판매 시 적용한 할인율	백분율
매출액	할인율을 반영한 판매금액	정수
반품여부	예 또는 아니요	텍스트
만족도	고객 만족도 1～5점	정수
배송일수	주문 후 배송까지 걸린 일수	정수

---

Section01. 외부 데이터 가져오기

1. 외부 데이터란?

외부 데이터는 현재 작업 중인 Excel 통합 문서 외부에 저장된 데이터를 의미한다. 대표적인 외부 데이터 원본은 다음과 같다.

• 텍스트 파일: .txt, .csv
• 다른 Excel 통합 문서: .xlsx
• 데이터베이스: Access, SQL Server
• 웹 페이지
• XML 및 JSON 파일
• PDF 문서
• SharePoint 및 온라인 서비스
• 특정 폴더에 저장된 여러 파일

Excel 365에서는 Power Query를 이용하여 다양한 외부 데이터에 연결할 수 있다. Power Query는 외부 데이터를 가져오고, 정리하고, 변환한 후 Excel 워크시트나 데이터 모델에 적재하는 기능이다. 

2. TXT와 CSV 파일

TXT 파일

TXT 파일은 문자로 구성된 일반 텍스트 파일이다. 여러 필드가 포함된 데이터에서는 탭, 쉼표, 세미콜론 등의 구분 기호를 사용한다.

이번 장의 실습 파일은 각 필드를 탭(Tab)으로 구분하였다.

판매ID    주문일자    요일    지역    지점    담당자
S0001     2026-01-02  금      대전    유성점  강도윤

CSV 파일

CSV는 Comma-Separated Values의 약자로, 일반적으로 각 필드를 쉼표로 구분한다.

판매ID,주문일자,요일,지역,지점,담당자
S0001,2026-01-02,금,대전,유성점,강도윤

구분 기호의 중요성

Excel이 구분 기호를 정확하게 인식하지 못하면 전체 데이터가 하나의 열에 나타날 수 있다.

탭	탭 키로 필드를 구분한다.
쉼표	CSV에서 주로 사용한다.
세미콜론	일부 국가나 시스템에서 사용한다.
공백	일정한 공백으로 필드를 구분한다.
사용자 지정	파이프 기호 | 등 특정 문자를 사용한다.

💻 3. 실습 1: TXT 파일 가져오기

문제

제공된 CH07_외부데이터_판매내역_180건.txt 파일을 Excel로 가져오시오.

실행 과정

1. Excel에서 새 통합 문서를 연다.
2. 리본 메뉴에서 데이터 탭을 선택한다.
3. 데이터 가져오기 및 변환 그룹에서 텍스트/CSV에서를 선택한다.
4. 제공된 TXT 파일을 선택한다.
5. 가져오기를 선택한다.
6. 미리 보기 창에서 다음 항목을 확인한다.
7. 데이터가 정상적으로 분리되었는지 확인한다.
8. 로드를 선택한다.

Excel 365에서는 데이터 → 텍스트/CSV에서를 이용하여 텍스트 파일에 연결할 수 있다. 미리 보기 화면에서 바로 로드하거나, 데이터 변환을 선택하여 Power Query 편집기에서 정리할 수 있다.

4. 로드와 데이터 변환

TXT 파일을 선택하면 미리 보기 창에서 다음 기능을 사용할 수 있다.

로드	데이터를 바로 새 워크시트에 삽입한다.
데이터 변환	Power Query 편집기에서 데이터를 정리한 후 삽입한다.
로드 대상	표, 피벗 테이블, 데이터 모델, 연결 전용 등의 적재 위치를 지정한다.

단순한 데이터는 로드를 사용할 수 있지만, 데이터 형식 지정이나 불필요한 행 제거가 필요하면 데이터 변환을 선택하는 것이 적절하다.

💻 5. 실습 2: 데이터 형식 지정하기

문제

TXT 파일을 다시 가져오고 Power Query 편집기에서 각 열의 데이터 형식을 지정하시오.

실행 과정

1. 데이터 → 텍스트/CSV에서를 선택한다.
2. TXT 파일을 선택한다.
3. 미리 보기 창에서 데이터 변환을 선택한다.
4. Power Query 편집기에서 각 열의 데이터 형식을 확인한다.
5. 다음과 같이 데이터 형식을 지정한다.

판매ID	텍스트
주문일자	날짜
요일	텍스트
지역～결제수단	텍스트
판매수량	정수
단가	정수
할인율	백분율
매출액	정수
반품여부	텍스트
만족도	정수
배송일수	정수

1. 홈 → 닫기 및 로드 → 닫기 및 다음으로 로드를 선택한다.
2. 표, 새 워크시트를 선택하여 데이터를 가져온다.
3. 워크시트 이름을 외부데이터로 변경한다.
4. 표 이름을 판매내역으로 변경한다.

데이터 형식을 지정하는 이유

데이터 형식이 잘못 지정되면 다음 문제가 발생할 수 있다.

• 날짜가 텍스트로 인식되어 날짜순으로 정렬되지 않는다.
• 숫자가 텍스트로 인식되어 합계나 평균을 계산할 수 없다.
• 할인율이 일반 숫자로 처리되어 백분율 계산이 달라진다.
• 숫자 필터 대신 텍스트 필터가 표시된다.

6. 실습 3: 외부 데이터 새로 고침

외부 데이터 가져오기는 단순 복사와 다르다. 원본 파일과 Excel 통합 문서 사이에 연결 정보를 유지할 수 있다.

문제

TXT 파일을 가져온 후 연결된 데이터를 새로 고치시오.

실행 과정

1. 가져온 표 내부의 셀을 선택한다.
2. 데이터 → 모두 새로 고침을 선택한다.
3. 또는 표 내부에서 마우스 오른쪽 단추를 눌러 새로 고침을 선택한다.

알아두기

• 원본 TXT 파일의 위치나 이름이 변경되면 새로 고침 오류가 발생할 수 있다.
• 원본 파일의 열 이름을 변경하면 Power Query 단계에서 오류가 발생할 수 있다.
• TXT 파일의 데이터가 변경되어도 Excel 데이터가 자동으로 변경되는 것은 아니다.
• 최신 내용을 반영하려면 새로 고침을 실행하여야 한다.

---

Section 02. 데이터 정렬하기

1. 정렬의 개념

정렬은 데이터를 지정한 기준에 따라 일정한 순서로 재배치하는 기능이다.

데이터	오름차순	내림차순
텍스트	가나다순, A→Z	역가나다순, Z→A
숫자	작은 값→큰 값	큰 값→작은 값
날짜	오래된 날짜→최근 날짜	최근 날짜→오래된 날짜
사용자 지정	사용자가 지정한 순서	사용자가 지정한 역순
서식	지정 색·아이콘을 위로	지정 색·아이콘을 아래로

정렬은 값뿐만 아니라 셀 색, 글꼴 색, 조건부 서식 아이콘을 기준으로도 실행할 수 있다. 다중 정렬은 최대 64개 열까지 수준을 추가할 수 있다.

2. 정렬할 때 지켜야 할 규칙

1. 데이터의 각 열에는 고유한 머리글을 작성한다.
2. 데이터 중간에 빈 행이나 빈 열을 만들지 않는다.
3. 병합된 셀을 사용하지 않는다.
4. 하나의 열에는 같은 형식의 데이터를 입력한다.
5. 데이터 전체가 함께 이동하도록 표 내부의 셀을 선택한다.
6. 특정 열만 따로 선택하여 정렬하지 않는다.
7. 원래 순서로 복원할 수 있도록 판매ID와 같은 고유 식별자를 유지한다.

정렬은 조건에 맞지 않는 데이터를 숨기는 기능이 아니라, 실제 행의 배치 순서를 변경하는 기능이다.

---

 3. 실습 1: 단일 항목 정렬 

💻 문제 1

매출액이 큰 판매 기록부터 표시하시오.

실행 과정

1. 매출액 열의 셀을 하나 선택한다.
2. 데이터 → 내림차순 정렬을 선택한다.
3. 가장 높은 매출액이 첫 번째 행에 표시되는지 확인한다.

💻 문제 2

주문일자가 오래된 날짜부터 표시되도록 정렬하시오.

실행 과정

1. 주문일자 열의 셀을 선택한다.
2. 데이터 → 오름차순 정렬을 선택한다.
3. 2026년 1월 데이터가 위쪽에 나타나는지 확인한다.

---

4. 실습 2: 여러 항목 정렬

💻 문제

다음 순서로 데이터를 정렬하시오.

1. 지역: 가나다순
2. 고객등급: VIP → 골드 → 실버 → 일반
3. 매출액: 큰 값에서 작은 값

실행 과정

1. 표 안의 셀을 선택한다.
2. 데이터 → 정렬을 선택한다.
3. 내 데이터에 머리글 표시가 선택되어 있는지 확인한다.
4. 첫 번째 정렬 수준을 지정한다.

지역

셀 값

가나다순

 

• 수준 추가를 선택한다.
• 두 번째 정렬 수준을 지정한다.

고객등급

셀 값

사용자 지정 목록

• 사용자 지정 목록에 다음 순서를 입력한다.

VIP
골드
실버
일반

1. 다시 수준 추가를 선택한다.
2. 세 번째 정렬 수준을 지정한다.

매출액

셀 값

내림차순

• 확인을 선택한다.

정렬 수준의 우선순위

정렬 대화상자의 위쪽 수준이 먼저 적용된다.

지역
 └─ 고객등급
     └─ 매출액

따라서 먼저 지역별로 그룹화되고, 같은 지역 안에서 고객등급이 정렬되며, 같은 고객등급 안에서 매출액이 정렬된다. 사용자 지정 목록을 이용하면 가나다순이 아닌 업무상 우선순위에 따라 데이터를 정렬할 수 있다.

---

5. 실습 3: 원래 순서로 복원하기

💻문제

여러 기준으로 정렬한 데이터를 처음 판매ID 순서로 복원하시오.

실행 과정

1. 판매ID 열의 셀을 선택한다.
2. 데이터 → 오름차순 정렬을 선택한다.
3. S0001부터 S0180까지 순서대로 표시되는지 확인한다.

정렬 작업은 여러 번 실행하면 이전 배열 상태를 잃을 수 있다. 원본 순서를 복원하여야 하는 데이터에는 순번이나 고유 식별자 열을 반드시 포함하는 것이 좋다.

---

Section 03. 데이터 필터링

1. 필터의 개념

필터는 조건에 맞는 데이터만 표시하고, 조건에 맞지 않는 행은 일시적으로 숨기는 기능이다. 정렬과 달리 원본 데이터의 순서를 변경하지 않는다.

구분	정렬	필터
목적	데이터의 순서 변경	조건에 맞는 데이터만 표시
행 위치	변경됨	변경되지 않음
제외 데이터	그대로 표시됨	일시적으로 숨겨짐
해제 결과	자동으로 원래 순서가 되지 않음	모든 행이 다시 표시됨

---

2. 자동 필터

필터 설정

1. 데이터 범위 안의 셀을 선택한다.
2. 데이터 → 필터를 선택한다.
3. 각 열 머리글에 필터 화살표가 나타나는지 확인한다.

단축키는 다음과 같다.

Ctrl + Shift + L

Excel 표에는 머리글에 필터 단추가 자동으로 표시된다. 일반 범위에서는 데이터 → 필터를 선택하여야 한다.

---

3. 실습 1: 항목 선택 필터

💻 문제: 지역이 부산인 판매 기록만 표시하시오.

실행 과정

1. 지역 열의 필터 단추를 선택한다.
2. 모두 선택을 해제한다.
3. 부산만 선택한다.
4. 확인을 선택한다.

확인 사항

• 지역 열의 필터 단추 모양이 변경된다.
• 부산 이외의 지역 데이터는 삭제되지 않고 숨겨진다.
• 행 번호가 연속적이지 않게 표시된다.

---

4. 실습 2: 여러 열에 필터 적용하기

문제: 다음 조건을 모두 만족하는 판매 기록을 표시하시오.

• 지역: 부산
• 매출액: 2,000,000원 이상
• 반품여부: 아니요

실행 과정

1. 지역 열에서 부산을 선택한다.
2. 매출액 열에서 숫자 필터 → 크거나 같음을 선택한다.
3. 조건값으로 2000000을 입력한다.
4. 반품여부 열에서 아니요를 선택한다.

조건 해석

지역="부산"
AND 매출액>=2000000
AND 반품여부="아니요"

서로 다른 열에 적용한 자동 필터 조건은 기본적으로 AND 조건으로 결합된다.

---

5. 실습 3: 사용자 지정 자동 필터

💻 문제: 매출액이 1,000,000원 이상이고 3,000,000원 이하인 판매 기록을 표시하시오.

실행 과정

1. 매출액 열의 필터 단추를 선택한다.
2. 숫자 필터 → 사용자 지정 필터를 선택한다.
3. 첫 번째 조건을 지정한다.

크거나 같음  1000000

1. 그리고를 선택한다.
2. 두 번째 조건을 지정한다.

작거나 같음  3000000

• 확인을 선택한다.

Excel의 사용자 지정 필터에서는 두 조건을 모두 만족하는 그리고, 두 조건 중 하나 이상을 만족하는 또는를 선택할 수 있다.

---

6. 실습 4: 텍스트와 날짜 필터

💻 문제 1: 특정 단어를 포함하는 상품

상품명에 모니터가 포함된 판매 기록만 표시하시오.

1. 상품명 열의 필터 단추를 선택한다.
2. 텍스트 필터 → 포함을 선택한다.
3. 모니터를 입력한다.
4. 확인을 선택한다.

💻 문제 2: 특정 기간의 데이터

2026년 3월에 주문된 데이터만 표시하시오.

1. 주문일자 열의 필터 단추를 선택한다.
2. 날짜 목록에서 2026년 → 3월을 선택한다.
3. 확인을 선택한다.

와일드카드

문자	의미	예
*	글자 수와 관계없이 여러 문자	*모니터*
?	임의의 한 문자	탭 ?로*
~	*, ? 자체를 찾을 때 사용	~*

---

7. 실습 5: 상위 항목 필터

문제: 매출액이 큰 상위 10개의 판매 기록을 표시하시오.

실행 과정

1. 매출액 열의 필터 단추를 선택한다.
2. 숫자 필터 → 상위 10을 선택한다.
3. 다음과 같이 설정한다.

방향	상위
개수	10
기준	항목

1. 확인을 선택한다.
2. 매출액을 내림차순으로 정렬하여 결과를 확인한다.

상위 10 필터와 상위 10% 필터는 서로 다르다. 항목은 지정한 개수만 추출하고, %는 전체 데이터 중 지정한 비율을 추출한다.

---

Section 04. 고급 필터

1. 고급 필터의 개념

고급 필터는 워크시트에 별도의 조건 범위를 작성하고 이를 이용하여 데이터를 추출하는 기능이다.

자동 필터와 비교하면 다음과 같다.

구분	자동필터	고급필터
조건 입력	필터 메뉴	워크시트의 조건 범위
복잡한 AND·OR 조건	제한적	가능
결과 복사	별도 복사 필요	다른 위치에 직접 복사 가능
고유 레코드	제한적	고유 레코드만 추출 가능
조건 변경 시 갱신	필터 메뉴에서 변경	다시 실행하여야 함

고급 필터는 조건 범위를 별도의 셀에 작성하여야 하며, 조건값을 변경하여도 결과가 자동으로 갱신되지 않는다.

---

2. 조건 범위 작성 규칙

같은 행의 조건: AND

지역	매출액	반품여부
부산	>=1000000	아니요

조건의 의미는 다음과 같다.

지역="부산"
AND 매출액>=1000000
AND 반품여부="아니요"

다른 행의 조건: OR

지역	매출액	반품여부
부산	>=1000000	아니요
서울	>=3000000	아니요

조건의 의미는 다음과 같다.

(지역="부산" AND 매출액>=1000000 AND 반품여부="아니요")
OR
(지역="서울" AND 매출액>=3000000 AND 반품여부="아니요")

같은 필드에 범위 조건 사용

1,000,000원 이상 3,000,000원 이하처럼 한 열에 두 조건을 적용하려면 조건 범위에 같은 머리글을 두 번 작성한다.

매출액	매출액
>=1000000	<=3000000

---

3. 실습 1: AND 조건 고급 필터

문제: 다음 조건을 모두 만족하는 데이터를 필터링하시오.

• 지역: 부산
• 고객등급: VIP
• 매출액: 1,000,000원 이상
• 반품여부: 아니요

조건 범위

빈 공간에 다음 조건을 작성한다.(오타없이 작성)

지역	고객등급	매출액	반품여부
부산	VIP	>=1000000	아니요

실행 과정

1. 데이터 범위 안의 셀을 선택한다.
2. 데이터 → 고급을 선택한다.
3. 현재 위치에 필터를 선택한다.
4. 목록 범위에 전체 데이터 범위를 지정한다.
5. 조건 범위에 위에서 작성한 머리글과 조건행을 지정한다.
6. 확인을 선택한다.

✨ 엑셀에서 표(테이블)만 빠르게 선택하는 방법

테이블 내부의 아무 셀이나 선택한 후 다음 단축키를 사용한다.

Ctrl + A 한 번	테이블의 데이터 영역만 선택
Ctrl + A 두 번	머리글을 포함한 테이블 전체 선택
Ctrl + A 세 번	워크시트 전체 선택

---

4. 실습 2: AND와 OR 결합

문제

다음 두 조건 그룹 중 하나를 만족하는 판매 기록을 추출하시오.

• 부산이면서 매출액이 1,000,000원 이상이고 반품하지 않은 기록
• 서울이면서 매출액이 3,000,000원 이상이고 반품하지 않은 기록

조건 범위

지역	매출액	반품여부
부산	>=1000000	아니요
서울	>=3000000	아니요

실행 과정

1. 조건 범위를 작성한다.
2. 데이터 → 고급을 선택한다.
3. 목록 범위와 조건 범위를 지정한다.
4. 현재 위치에 필터를 선택한다.
5. 확인을 선택한다.

---

5. 실습 3: 결과를 다른 위치에 복사하기

문제

부산 또는 서울 지역의 VIP 고객 판매 기록을 새 위치에 추출하시오. 결과에는 판매ID, 주문일자, 지역, 고객등급, 상품명, 매출액만 표시하시오.

조건 범위

지역	고객등급
부산	VIP
서울	VIP

복사할 위치의 머리글

판매ID

주문일자

지역

고객등급

상품명

매출액

실행 과정

1. 조건 범위를 작성한다.
2. 결과를 표시할 빈 영역에 필요한 머리글을 입력한다.
3. 데이터 → 고급을 선택한다.
4. 다른 장소에 복사를 선택한다.
5. 목록 범위를 지정한다.
6. 조건 범위를 지정한다.
7. 복사 위치에 결과 머리글 범위를 지정한다.
8. 확인을 선택한다.

---