엑셀 데이터 가져오기와 정리 방법

다른 시스템에서 가져온 데이터가 바로 쓸 수 있는 상태인 경우는 드물어요. 고객 데이터를 내보내 보면 이름만 필요한데 성명 전체가 들어 있거나, 한 열에 두 가지 정보가 붙어 있거나, 같은 지역이 세 가지 방식으로 적혀 있거나, 같은 고객이 두 번 나오기도 해요. 더 곤란한 건 멀쩡해 보이는 숫자가 사실은 텍스트로 저장되어 있어서, 아무 오류 없이 합계가 틀리게 나오는 경우예요. 이 글에서는 CSV 파일을 안전하게 가져오고, 숫자 열을 하나하나 확인하고, 빠른 채우기(Flash Fill), 텍스트 나누기(Text to Columns), 찾기 및 바꾸기(Find and Replace), 중복된 항목 제거(Remove Duplicates)로 데이터를 정리하는 방법을 배워요.

CSV 파일 가져오기

CSV(쉼표로 구분된 값) 파일은 일반 텍스트 파일이에요. 한 줄에 레코드가 하나씩 있고, 필드 사이는 쉼표로 구분해요. 이 글에서 쓰는 고객 데이터는 다음과 같아요.

성명,주,주문 수,전화번호
Rosa Alvarez,Calif.,12,555-0142
Grant Okafor,NY,7,555-0198
Priya Shah,Calif.,9,555-0176
Priya Shah,Calif.,9,555-0176
Tom Becker,TX,4,555-0123

쉼표가 엑셀에게 각 열이 어디서 끝나는지 알려 줘요. 파일을 여는 방법은 두 가지예요.

바로 열기. 파일을 두 번 클릭하거나 파일 > 열기로 여세요. 엑셀이 쉼표를 기준으로 줄을 나누고, 값마다 형식을 추측해요. 빠르긴 하지만 이 추측 때문에 데이터가 바뀔 수 있어요. 07024 같은 코드나 우편 번호는 숫자 7024가 되고, 3-4 같은 값은 날짜로 바뀌기도 해요.

텍스트/CSV에서(From Text/CSV) 가져오기. 미리 보기를 먼저 보여 주고, 형식을 추측하는 방식도 직접 정할 수 있어요.

1. 가져오기를 시작하세요

   새 시트나 빈 시트에서 데이터› then 데이터 가져오기 및 변환› then 텍스트/CSV에서를 클릭하세요. 파일을 고르고 가져오기를 클릭하세요.

2. 미리 보기를 확인하세요

   미리 보기 창이 열려요. 위쪽의 세 가지 설정을 확인하세요. 파일 원본(File Origin)은 문자 인코딩이에요. 보통 UTF-8이고, 그래야 한글과 악센트가 있는 문자가 깨지지 않아요. 구분 기호(Delimiter)는 이 파일의 경우 쉼표예요. 마지막은 데이터 형식 검색(Data Type Detection)이에요. 앞자리가 0인 코드가 들어 있는 열이 있다면 데이터 형식을 검색하지 않음을 고르세요. 그러면 모든 값이 적힌 그대로 텍스트로 들어와요. 진짜 숫자 열은 다음 섹션에서처럼 직접 변환하면 돼요.

3. 데이터를 로드하세요

   로드를 클릭하세요. 엑셀이 새 시트에 데이터를 엑셀 표로 넣어 줘요. 먼저 모양을 더 다듬어야 한다면 데이터 변환을 클릭해서 파워 쿼리 편집기(Power Query Editor)를 여세요.

이렇게 가져온 데이터는 원본 파일과 연결된 상태로 남아요. 새로 내보낸 파일이 기존 파일을 대신하면 데이터› then 쿼리 및 연결› then 모두 새로 고침(Ctrl+Alt+F5)으로 새 버전을 불러올 수 있어요. 매주 같은 정리 작업을 반복한다면 그 작업을 파워 쿼리에서 하세요. 새로 고칠 때마다 같은 단계가 다시 실행돼요.

숫자가 진짜 숫자인지 확인하기

웹 페이지, PDF, 다른 시스템에서 붙여 넣은 데이터에는 텍스트로 저장된 숫자가 섞여 있는 경우가 많아요. 겉보기엔 평범하지만 SUM과 AVERAGE는 이런 값을 아무 경고 없이 건너뛰어요. 데이터를 가져오면 바로 숫자 열을 모두 확인하세요.

고객 목록의 성명 열 뒤에 이름 열을 하나 넣고, Rosa의 이름을 직접 입력해 두었어요. 주문 수 열을 보세요.

fx

	A	B	C	D	E	F
1	성명	이름	주	주문 수	전화번호
2	Rosa Alvarez	Rosa	Calif.	12	555-0142
3	Grant Okafor		NY	7	555-0198
4	Priya Shah		Calif.	9	555-0176
5	Priya Shah		Calif.	9	555-0176
6	Tom Becker		TX	4	555-0123

다음 세 가지로 열에 진짜 숫자가 들어 있는지 빠르게 확인할 수 있어요.

• 맞춤. 맞춤을 따로 바꾸지 않았다면 숫자는 셀 오른쪽에, 텍스트는 왼쪽에 붙어요. 텍스트로 저장된 숫자에는 셀 왼쪽 위에 작은 초록색 삼각형이 표시되는 경우가 많아요.
• 상태 표시줄. D2:D6을 선택하고 창 아래쪽을 보세요. 개수: 5인데 합계: 16이에요. 개수는 다섯 셀을 모두 세지만, 합계는 진짜 숫자인 12와 4만 더해요.
• 수식. =COUNT(D2:D6)은 숫자만 세서 2를, =COUNTA(D2:D6)은 채워진 셀을 모두 세서 5를 돌려줘요. 숫자 열에서 두 값이 다르면 어딘가에 텍스트로 저장된 값이 있는 거예요.

D7fx=SUM(D2:D6)

	A	B	C	D	E	F
1	성명	이름	주	주문 수	전화번호
2	Rosa Alvarez	Rosa	Calif.	12	555-0142
3	Grant Okafor		NY	7	555-0198
4	Priya Shah		Calif.	9	555-0176
5	Priya Shah		Calif.	9	555-0176
6	Tom Becker		TX	4	555-0123
7				16

고치려면 D3:D5를 선택하세요. 선택 영역 옆에 경고 단추가 나타나요. 단추를 클릭하고 숫자로 변환(Convert to Number)을 고르세요. 값이 오른쪽으로 옮겨 가고, =SUM(D2:D6)이 41을 돌려줘요.

텍스트 뽑아내고 나누기

빠른 채우기로 패턴 채우기

빠른 채우기는 직접 입력한 예시에서 패턴을 찾아 나머지를 채워요. B2에 이미 패턴이 있어요. Rosa Alvarez의 이름은 Rosa예요.

1. 예시를 하나 입력하세요

   예시는 이미 B2에 들어 있어요. 내 데이터에서 할 때는 원본 데이터 바로 옆 열에 첫 행의 결과를 직접 입력하세요.

2. Ctrl+E를 누르세요

   B3을 클릭하고 Ctrl+E를 누르거나 데이터› then 데이터 도구› then 빠른 채우기를 클릭하세요. 엑셀이 Grant, Priya, Priya, Tom을 채워 줘요. 단축키를 쓰지 않아도 되는 경우가 많아요. 두 번째 이름을 입력하기 시작하면 엑셀이 열 전체의 미리 보기를 회색으로 보여 줘요. Enter를 누르면 그대로 채워져요.

   B3fx

   	A	B	C	D	E	F
   1	성명	이름	주	주문 수	전화번호
   2	Rosa Alvarez	Rosa	Calif.	12	555-0142
   3	Grant Okafor	Grant	NY	7	555-0198
   4	Priya Shah	Priya	Calif.	9	555-0176
   5	Priya Shah	Priya	Calif.	9	555-0176
   6	Tom Becker	Tom	TX	4	555-0123

3. 결과를 확인하세요

   빠른 채우기는 추측해서 채우기 때문에 열을 한번 훑어보세요. 가운데 이름이 있거나 이름이 두 단어인 경우에 가장 자주 틀려요. 결과가 이상하면 올바른 값을 덮어 입력하세요. 빠른 채우기가 그 값을 두 번째 예시로 삼아 아래 셀을 다시 채워요.

빠른 채우기는 수식이 아니라 값을 입력해요. 나중에 성명이 바뀌어도 이름은 바뀌지 않아요. 원본 데이터가 계속 바뀐다면 수식을 쓰세요. =LEFT(A2,FIND(" ",A2)-1)은 모든 버전의 엑셀에서 Rosa를 돌려주고, Microsoft 365와 Excel 2024에서는 =TEXTBEFORE(A2," ")로도 같은 결과를 얻을 수 있어요.

텍스트 나누기로 열 나누기

텍스트 나누기는 열의 모든 셀을 쉼표, 공백, 대시 같은 구분 기호에서 나눠요. E열의 전화번호를 국번과 번호로 나눠 볼게요.

1. 자리를 비우고 데이터를 선택하세요

   텍스트 나누기는 나눈 조각을 오른쪽 열에 쓰고, 거기 있던 내용을 덮어써요. F열은 비어 있으니 괜찮아요. E2:E6을 선택하세요.

2. 구분 기호를 고르세요

   데이터› then 데이터 도구› then 텍스트 나누기를 클릭하세요. 마법사에서 구분 기호로 분리됨을 고르고 다음을 클릭하세요. 구분 기호에서 탭의 체크를 해제하고, 기타에 체크한 다음 옆 상자에 대시(-)를 입력하세요. 데이터 미리 보기에 번호가 둘로 나뉜 모습이 보여요. 다음을 클릭하세요.

3. 앞자리 0을 지키세요

   마지막 단계에서 데이터 미리 보기의 두 번째 열을 클릭하고 열 데이터 서식에서 텍스트를 고르세요. 기본값인 일반으로 두면 엑셀이 0142를 숫자 142로 바꿔요. 대상이 =$E$2인지 확인하고 마침을 클릭하세요.

4. 새 열에 이름을 붙이세요

   E1에 국번, F1에 번호를 입력하세요.

값 통일하고 중복 제거하기

여러 표기를 한 번에 바꾸기

주 열을 보면 다른 주는 두 글자 약어로 적혀 있는데, 캘리포니아만 Calif.로 적혀 있어요. 찾기 및 바꾸기를 쓰면 한 번에 모두 고칠 수 있어요.

1. 검색 범위를 한 열로 좁히세요

   C열 머리글을 클릭하세요. 셀이 선택되어 있으면 찾기 및 바꾸기는 선택 영역 안에서만 찾기 때문에 다른 열의 값은 안전해요.

2. 바꾸기를 여세요

   Ctrl+H를 누르거나 홈› then 편집› then 찾기 및 선택› then 바꾸기를 클릭하세요. 찾을 내용에 Calif., 바꿀 내용에 CA를 입력하세요.

3. 셀 전체가 같은 경우만 바꾸세요

   옵션을 클릭하고 전체 셀 내용 일치에 체크하세요. 체크하지 않으면 더 긴 값 안에 들어 있는 같은 글자까지 바뀌어요.

4. 모두 바꾸세요

   모두 바꾸기를 클릭하세요. 엑셀이 3개 항목을 바꿨다고 알려 줘요. 확인을 클릭한 다음 닫기를 클릭하세요.

완전히 똑같은 중복 행 제거하기

Priya Shah가 모든 열의 내용이 똑같은 채로 두 번 나와요. 중복된 항목 제거는 똑같은 행 가운데 첫 번째 행만 남기고 나머지를 지워요.

1. 중복된 항목 제거를 여세요

   데이터 안의 아무 셀이나 클릭하고 데이터› then 데이터 도구› then 중복된 항목 제거를 클릭하세요. 엑셀이 목록 전체를 선택해요.

2. 비교할 열을 고르세요

   머리글 행이 데이터로 취급되지 않도록 내 데이터에 머리글 표시는 체크된 그대로 두세요. 열은 모두 체크된 상태로 두세요. 체크한 열의 값이 모두 같아야만 중복 행으로 봐요. 확인을 클릭하세요.

3. 결과를 읽으세요

   엑셀이 중복 값 1개를 찾아 제거했고 고유한 값 4개가 남았다고 알려 줘요. 두 번째 Priya Shah 행이 사라지고 그 아래 행이 위로 올라와요.

모든 단계를 마친 목록이에요.

D6fx=SUM(D2:D5)

	A	B	C	D	E	F
1	성명	이름	주	주문 수	국번	번호
2	Rosa Alvarez	Rosa	CA	12	555	0142
3	Grant Okafor	Grant	NY	7	555	0198
4	Priya Shah	Priya	CA	9	555	0176
5	Tom Becker	Tom	TX	4	555	0123
6				32

자주 하는 실수

• 앞자리 0이 사라지는 경우. CSV를 바로 열거나 일반 서식으로 텍스트를 나누면 07024가 7024가 돼요. 데이터 형식을 검색하지 않음으로 가져오거나, 텍스트 나누기에서 그 열을 텍스트로 지정하세요.
• 비어 있지 않은 열로 나누기. 텍스트 나누기는 오른쪽 셀을 덮어써요. 먼저 빈 열을 삽입하세요.
• 형식을 확인하기 전에 합계를 믿기. 텍스트로 저장된 숫자가 섞인 열은 오류 없이 더 작은 합계가 나와요. 합계를 믿기 전에 COUNT와 COUNTA를 비교해 보세요.
• 완전히 똑같지 않은 중복. 뒤에 공백이 붙은 Priya Shah나 전화번호 형식이 다른 행은 완전히 같은 중복이 아니라서, 중복된 항목 제거가 두 행을 모두 남겨요. 먼저 TRIM으로 공백을 정리하고 형식을 통일한 다음 중복을 제거하세요.
• 하나뿐인 원본을 바로 정리하기. 원본 파일을 따로 두거나, 시작하기 전에 시트를 복사해 두세요. 그래야 비교도 하고 처음부터 다시 할 수도 있어요.

연습

연습

실제로 내보낸 데이터 정리하기

1. 정리가 안 된 목록을 하나 찾으세요. 앱이나 웹사이트에서 내보낸 CSV 파일이나 이메일에서 붙여 넣은 신청자 명단이면 좋아요. 마땅한 게 없다면 이 글의 CSV 텍스트를 메모장에 붙여 넣고 customers.csv로 저장하세요.
2. 데이터› then 데이터 가져오기 및 변환› then 텍스트/CSV에서로 가져오세요. 구분 기호를 확인하고 데이터 형식을 검색하는 방식을 고르세요.
3. 상태 표시줄이나 COUNT와 COUNTA로 숫자 열을 모두 확인하고, 텍스트로 저장된 숫자를 변환하세요.
4. 빠른 채우기로 텍스트 열에서 이름이나 이메일 주소의 도메인처럼 일부분을 뽑아내세요.
5. 두 가지 정보가 붙어 있는 열 하나를 텍스트 나누기로 나누세요. 앞자리 0이 있다면 지켜야 해요.
6. 찾기 및 바꾸기로 들쭉날쭉한 값을 하나 통일한 다음, 완전히 똑같은 중복 행을 제거하세요.

실력 점검

• CSV 파일을 가져오면서 엑셀이 데이터 형식을 검색하는 방식을 고를 수 있나요?
• 숫자처럼 보이는 열이 텍스트로 저장되어 있는지 확인하고 숫자로 변환할 수 있나요?
• 빠른 채우기로 한 열에서 패턴을 뽑아 다른 열에 채울 수 있나요?
• 텍스트 나누기로 한 열을 여러 열로 나누면서 앞자리 0을 지킬 수 있나요?
• 찾기 및 바꾸기로 값을 통일하고 완전히 똑같은 중복 행을 제거할 수 있나요?