Excel에서 Access로 데이터 이동

이 문서에서는 Microsoft Excel과 Access를 함께 사용할 수 있도록 Excel에서 Access로 데이터를 이동하고 데이터를 관계형 테이블로 변환하는 방법을 보여 줍니다. 요약하면 Access는 데이터를 캡처, 저장, 쿼리 및 공유하는 데 가장 적합하고 Excel은 데이터 계산, 분석 및 시각화에 가장 적합합니다.

Access 또는 Excel을 사용하여 데이터 관리와 Excel에서 Access를 사용하는 10가지 이유라는 두 개의 문서에서는 특정 작업에 가장 적합한 프로그램과 Excel과 Access를 함께 사용하여 실용적인 솔루션을 만드는 방법을 설명합니다.

Excel에서 Access로 데이터를 이동할 때 프로세스에는 기본 세 가지 단계가 있습니다.

1단계: Excel에서 Access로 데이터 가져오기

데이터 가져오기는 데이터를 준비하고 클린하는 데 시간을 할애하면 훨씬 더 원활하게 진행될 수 있는 작업입니다. 데이터를 가져오는 것은 새 집으로 이사하는 것과 같습니다. 이사하기 전에 소유물을 클린하고 정리하면 새 집에 정착하는 것이 훨씬 쉬워집니다.

가져오기 전에 데이터 정리

Access로 데이터를 가져오기 전에 Excel에서 다음을 수행하는 것이 좋습니다.

• 원자가 아닌 데이터(즉, 한 셀에 여러 값이 들어있음)가 포함된 셀을 여러 열로 변환합니다. 예를 들어, "기술" 열에 "C# 프로그래밍", "VBA 프로그래밍", "웹 디자인"과 같은 여러 기술 값이 포함된 셀은 각각 하나의 기술 값만 포함된 별도의 열로 나누어야 합니다.
• TRIM 명령을 사용하여 선행, 후행 및 여러 개의 포함된 공백을 제거합니다.
• 인쇄되지 않는 문자를 제거합니다.
• 맞춤법 및 문장 부호 오류를 찾아 수정합니다.
• 중복 행 또는 중복 필드를 제거합니다.
• 데이터 열에 혼합 형식, 특히 텍스트로 서식이 지정된 숫자 또는 숫자로 서식이 지정된 날짜가 포함되어 있지 않은지 확인합니다.

자세한 내용은 다음 Excel 도움말 topics를 참조하세요.

• 데이터를 정리하는 10가지 주요 방법
• 고유 값 필터링 또는 중복 값 제거
• 텍스트로 저장된 숫자를 숫자 서식으로 변환
• 텍스트로 저장된 날짜를 날짜 서식으로 변환

가져올 때 가장 적합한 데이터 형식 선택

Access에서 가져오기 작업을 수행하는 동안 수동 개입이 필요한 변환 오류가 거의 발생하지 않도록 올바른 선택을 해야 합니다. 다음 표에는 Excel에서 Access로 데이터를 가져올 때 Excel 숫자 형식과 Access 데이터 형식이 변환되는 방식이 요약되어 있으며, 스프레드시트 가져오기 마법사에서 선택할 수 있는 최상의 데이터 형식에 대한 몇 가지 팁이 제공됩니다.

Excel 숫자 서식	Access 데이터 형식	메모	모범 사례
텍스트	텍스트, 메모	Access 텍스트 데이터 형식은 영숫자 데이터를 최대 255자까지 저장합니다. Access 메모 데이터 형식은 최대 65,535자의 영숫자 데이터를 저장합니다.	데이터가 잘리지 않도록 하려면 메모 를 선택합니다.
숫자, 백분율, 분수, 지수	number	Access에는 필드 크기 속성(바이트, 정수, 긴 정수, 단일, 이중, 10진수)에 따라 달라지는 하나의 숫자 데이터 형식이 있습니다.	데이터 변환 오류를 방지하려면 Double을 선택합니다.
Date	날짜	Access와 Excel은 모두 동일한 날짜 번호를 사용하여 날짜를 저장합니다. Access의 날짜 범위는 -657,434년(서기 100년 1월 1일)에서 2,958,465년(서기 9999년 12월 31일)로 더 큽니다. Access에서는 1904 날짜 시스템(Macintosh용 Excel에서 사용됨)을 인식하지 못하기 때문에 혼동을 피하기 위해 Excel 또는 Access에서 날짜를 변환해야 합니다. 자세한 내용은 날짜 시스템, 형식 또는 두 자리 연도 해석 변경 및 Excel 통합 문서의 데이터 가져오기 또는 연결을 참조하세요.	날짜를 선택합니다.
Time	시간	Access 및 Excel은 모두 동일한 데이터 형식을 사용하여 시간 값을 저장합니다.	일반적으로 기본값인 시간을 선택합니다.
통화, 회계	통화	Access에서 통화 데이터 형식은 소수점 아래 네 자리까지 정밀도로 데이터를 8바이트 숫자로 저장하며, 재무 데이터를 저장하고 값 반올림을 방지하는 데 사용됩니다.	일반적으로 기본값인 통화를 선택합니다.
부울	예/아니요	Access에서는 모든 Yes 값에 대해 -1을 사용하고 모든 No 값에 대해 0을 사용하는 반면, Excel에서는 모든 TRUE 값에 대해 1을 사용하고 모든 FALSE 값에 대해 0을 사용합니다.	예/아니요를 선택하면 기본 값이 자동으로 변환됩니다.
하이퍼링크	하이퍼링크	Excel 및 Access의 하이퍼링크에는 클릭하여 따라갈 수 있는 URL 또는 웹 주소가 포함되어 있습니다.	하이퍼링크를 선택합니다. 그렇지 않으면 Access에서 기본적으로 텍스트 데이터 형식을 사용할 수 있습니다.

데이터가 Access에 있으면 Excel 데이터를 삭제할 수 있습니다. 원본 Excel 통합 문서를 삭제하기 전에 먼저 백업하는 것을 잊지 마세요.

자세한 내용은 Access 도움말 항목 Excel 통합 문서의 데이터 가져오기 또는 연결을 참조하세요.

간편한 방법으로 데이터 자동 추가

Excel 사용자가 겪는 일반적인 문제는 동일한 열의 데이터를 하나의 큰 워크시트에 추가하는 것입니다. 예를 들어 Excel에서 시작했지만 이제는 여러 작업 그룹 및 부서의 파일을 포함하도록 성장한 자산 추적 솔루션이 있을 수 있습니다. 이 데이터는 다른 워크시트와 통합 문서 또는 다른 시스템의 데이터 피드인 텍스트 파일에 있을 수 있습니다. Excel에서 유사한 데이터를 추가하는 사용자 인터페이스 명령이나 쉬운 방법은 없습니다.

가장 좋은 방법은 스프레드시트 가져오기 마법사를 사용하여 데이터를 쉽게 가져오고 한 테이블에 추가할 수 있는 Access를 사용하는 것입니다. 또한 많은 데이터를 하나의 테이블에 추가할 수 있습니다. 가져오기 작업을 저장하고, 예약된 Microsoft Outlook 작업으로 추가하고, 매크로를 사용하여 프로세스를 자동화할 수도 있습니다.

2단계: 테이블 분석 마법사를 사용하여 데이터 정규화

언뜻 보기에는 데이터를 정규화하는 프로세스를 단계별로 진행하는 것이 어려운 작업처럼 보일 수 있습니다. 다행히 테이블 분석기 마법사를 사용하면 Access에서 테이블을 정규화하는 프로세스가 훨씬 쉬워졌습니다.

1. 선택한 열을 새 테이블로 끌어 자동으로 관계 만들기

2. 단추 명령을 사용하여 테이블 이름을 바꾸고, 기본 키를 추가하고, 기존 열을 기본 키로 만들고, 마지막 작업을 실행 취소합니다

이 마법사를 사용하여 다음을 수행할 수 있습니다.

• 테이블을 더 작은 테이블 집합으로 변환하고 테이블 간에 기본 및 외래 키 관계를 자동으로 만듭니다.
• 고유 값이 포함된 기존 필드에 기본 키를 추가하거나 일련 번호 데이터 형식을 사용하는 새 ID 필드를 만듭니다.
• 계단식 업데이트를 통해 참조 무결성을 적용하기 위한 관계를 자동으로 만듭니다. 연속 삭제는 데이터를 실수로 삭제하는 것을 방지하기 위해 자동으로 추가되지 않지만 나중에 쉽게 연속 삭제를 추가할 수 있습니다.
• 새 테이블에서 중복되거나 중복된 데이터(예: 서로 다른 두 개의 전화번호를 사용하는 동일한 고객)를 검색하고 원하는 대로 업데이트합니다.
• 원래 테이블을 백업하고 이름에 "_OLD"를 추가하여 이름을 바꿉니다. 그런 다음 원본 테이블을 기반으로 하는 기존 폼이나 보고서가 새 테이블 구조에서 작동하도록 원래 테이블 이름으로 원래 테이블을 다시 구성하는 쿼리를 만듭니다.

자세한 내용은 테이블 분석기를 사용하여 데이터 정규화를 참조하세요.

3단계: Excel에서 데이터에 연결

Access에서 데이터를 정규화하고 원본 데이터를 재구성하는 쿼리 또는 테이블을 만든 후에는 Excel에서 Access 데이터에 연결하기만 하면 됩니다. 이제 데이터는 Access에 외부 데이터 원본으로 지정되어 있으므로 외부 데이터 원본을 찾고, 로그온하고, 액세스하는 데 사용되는 정보의 컨테이너인 데이터 연결을 통해 통합 문서에 연결할 수 있습니다. 연결 정보는 통합 문서에 저장되며 ODC(Office 데이터 연결) 파일(.odc 파일 이름 확장명) 또는 데이터 원본 이름 파일(.dsn 확장명)과 같은 연결 파일에 저장할 수도 있습니다. 외부 데이터에 연결한 후 Access에서 데이터가 업데이트될 때마다 Access에서 Excel 통합 문서를 자동으로 새로 고치거나 업데이트할 수도 있습니다.

자세한 내용은 외부 데이터 원본에서 데이터 가져오기(파워 쿼리)를 참조하세요.

Access에 데이터 가져오기

이 섹션에서는 데이터를 정규화하는 다음 단계를 안내합니다. 판매원 및 주소 열의 값을 가장 중요한 부분으로 나누고, 관련 주제를 자체 테이블로 분리하고, Excel에서 Access로 해당 테이블을 복사하여 붙여넣고, 새로 만든 Access 테이블 간에 주요 관계를 만들고, Access에서 간단한 쿼리를 만들고 실행하여 정보를 반환하는 등의 단계를 수행합니다.

정규화되지 않은 형식의 예제 데이터

다음 워크시트의 판매원 열 및 주소 열에 원자성이 아닌 값이 포함되어 있습니다. 두 열을 둘 이상의 개별 열로 분할해야 합니다. 이 워크시트에는 영업 사원, 제품, 고객 및 주문에 대한 정보도 포함되어 있습니다. 또한 이 정보는 주제별로 별도의 테이블로 더 나누어야 합니다.

판매원	주문 ID	주문 날짜	제품 ID	수량	가격	고객 이름	주소	전화
Li, Yale	2349	3/4/09	C-789	3	$7.00	동남 상사 ㈜	7007 Cornell St Redmond, WA 98199	425-555-0201
Li, Yale	2349	3/4/09	C-795	6	$9.75	동남 상사 ㈜	7007 Cornell St Redmond, WA 98199	425-555-0201
Adams, Ellen	2350	3/4/09	대-2275	2	$16.75	Adventure Works	1025 Columbia Circle 커클랜드, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	F-198	6	$5.25	Adventure Works	1025 Columbia Circle 커클랜드, WA 98234	425-555-0185
Adams, Ellen	2350	3/4/09	B-205	1	$4.50	Adventure Works	1025 Columbia Circle 커클랜드, WA 98234	425-555-0185
Hance, Jim	2351	3/4/09	C-795	6	$9.75	극동 무역	2302 Harvard Ave Bellevue, WA 98227	425-555-0222
Hance, Jim	2352	3/5/09	대-2275	2	$16.75	Adventure Works	1025 Columbia Circle 커클랜드, WA 98234	425-555-0185
Hance, Jim	2352	3/5/09	D-4420	3	$7.25	Adventure Works	1025 Columbia Circle 커클랜드, WA 98234	425-555-0185
Koch, Reed	2353	3/7/09	대-2275	6	$16.75	동남 상사 ㈜	7007 Cornell St Redmond, WA 98199	425-555-0201
Koch, Reed	2353	3/7/09	C-789	5	$7.00	동남 상사 ㈜	7007 Cornell St Redmond, WA 98199	425-555-0201

가장 작은 부분의 정보: 원자 데이터

이 예제의 데이터를 사용하여 Excel에서 텍스트를 열로 지정 명령을 사용하여 셀의 "원자성" 부분(예: 주소, 구/군/시, 시/도 및 우편 번호)을 개별 열로 구분할 수 있습니다.

다음 표에서는 모든 값을 원자성으로 만들기 위해 분할된 후의 동일한 워크시트의 새 열을 보여 줍니다. 판매원 열의 정보는 성과 이름 열로 분할되었고 주소 열의 정보는 주소, 구/군/시, 시/도 및 우편 번호 열로 분할되었습니다. 이 데이터는 "첫 번째 정규 형식"입니다.

성	이름	주소	구/군/시	상태	우편 번호
Li	예일	2302 Harvard Ave	수원	WA	98227
Adams	엘렌	1025 컬럼비아 서클	성대	WA	98234
핸스	김병재	2302 Harvard Ave	수원	WA	98227
코흐	리드	7007 Cornell St Redmond	레드몬드	WA	98199

Excel에서 데이터를 구성된 주제로 나누기

다음에 나오는 여러 예제 데이터 테이블은 영업 사원, 제품, 고객 및 주문에 대한 테이블로 분할된 Excel 워크시트의 동일한 정보를 보여줍니다. 테이블 디자인은 최종 설계는 아니지만 올바른 방향으로 진행되고 있습니다.

판매원 테이블에는 영업 직원에 대한 정보만 들어 있습니다. 각 레코드에는 고유한 ID(SalesPerson ID)가 있습니다. SalesPerson ID 값은 주문 테이블에서 주문을 영업 사원에 연결하는 데 사용됩니다.

영업 사원
영업 사원 ID	성	이름
101	Li	예일
103	Adams	엘렌
105	핸스	김병재
107	코흐	리드

제품 테이블에는 제품에 대한 정보만 들어 있습니다. 각 레코드에는 고유한 ID(제품 ID)가 있습니다. 제품 ID 값은 제품 정보를 주문 정보 테이블에 연결하는 데 사용됩니다.

제품
제품 ID	가격
대-2275	16.75
B-205	4.50
C-789	7.00
C-795	9.75
D-4420	7.25
F-198	5.25

고객 테이블에는 고객에 대한 정보만 포함됩니다. 각 레코드에는 고유한 ID(고객 ID)가 있습니다. 고객 ID 값은 고객 정보를 주문 테이블에 연결하는 데 사용됩니다.

고객
고객 ID	이름	주소	구/군/시	상태	우편 번호	전화
1001	극동 무역	2302 Harvard Ave	수원	WA	98227	425-555-0222
1003	Adventure Works	1025 컬럼비아 서클	성대	WA	98234	425-555-0185
1005	동남 상사 ㈜	7007 Cornell St	레드몬드	WA	98199	425-555-0201

주문 테이블에는 주문, 영업 사원, 고객 및 제품에 대한 정보가 포함됩니다. 각 레코드에는 고유한 ID(주문 ID)가 있습니다. 이 테이블의 일부 정보는 주문 세부 정보가 포함된 추가 테이블로 분할하여 주문 테이블에 고유 주문 ID, 주문 날짜, 영업 사원 ID 및 고객 ID 등 4개의 열만 포함되도록 해야 합니다. 여기에 표시된 테이블은 아직 주문 세부 정보 테이블로 분할되지 않았습니다.

주문
주문 ID	주문 날짜	SalesPerson ID	고객 ID	제품 ID	수량
2349	3/4/09	101	1005	C-789	3
2349	3/4/09	101	1005	C-795	6
2350	3/4/09	103	1003	대-2275	2
2350	3/4/09	103	1003	F-198	6
2350	3/4/09	103	1003	B-205	1
2351	3/4/09	105	1001	C-795	6
2352	3/5/09	105	1003	대-2275	2
2352	3/5/09	105	1003	D-4420	3
2353	3/7/09	107	1005	대-2275	6
2353	3/7/09	107	1005	C-789	5

제품 ID 및 수량과 같은 주문 세부 정보가 Orders 테이블 밖으로 이동되고 Order Details라는 테이블에 저장됩니다. 9개의 주문이 있으므로 이 테이블에 9개의 레코드가 있는 것이 합리적입니다. 주문 테이블에는 주문 정보 테이블에서 참조되는 고유한 ID(주문 ID)가 있습니다.

주문 테이블의 최종 디자인은 다음과 같습니다.

주문
주문 ID	주문 날짜	SalesPerson ID	고객 ID
2349	3/4/09	101	1005
2350	3/4/09	103	1003
2351	3/4/09	105	1001
2352	3/5/09	105	1003
2353	3/7/09	107	1005

주문 정보 테이블에는 고유 값이 필요한 열이 없으므로(즉, 기본 키가 없음) 일부 또는 모든 열에 "중복" 데이터가 포함되어도 괜찮습니다. 그러나 이 테이블의 두 레코드는 완전히 동일해서는 안 됩니다(이 규칙은 데이터베이스의 모든 테이블에 적용됨). 이 테이블에는 17개의 레코드가 있어야 하며 각 레코드는 개별 주문의 제품에 해당합니다. 예를 들어, 주문 2349에서 3개의 C-789 제품이 전체 주문의 두 부분 중 하나를 구성합니다.

따라서 주문 정보 테이블은 다음과 같이 표시됩니다.

주문 세부 정보
주문 ID	제품 ID	수량
2349	C-789	3
2349	C-795	6
2350	대-2275	2
2350	F-198	6
2350	B-205	1
2351	C-795	6
2352	대-2275	2
2352	D-4420	3
2353	대-2275	6
2353	C-789	5

Excel에서 Access로 데이터 복사 및 붙여넣기

이제 영업 사원, 고객, 제품, 주문 및 주문 세부 정보에 대한 정보가 Excel에서 별도의 주제로 나뉘어졌으므로 해당 데이터를 Access에 직접 복사하여 테이블이 될 수 있습니다.

Access 테이블 간의 관계 만들기 및 쿼리 실행

데이터를 Access로 이동한 후에는 테이블 간에 관계를 만든 다음 다양한 주제에 대한 정보를 반환하는 쿼리를 만들 수 있습니다. 예를 들어 099년 3월 5일과 99년 3월 8일 사이에 입력된 주문에 대한 주문 ID와 영업 사원 이름을 반환하는 쿼리를 만들 수 있습니다.

또한 양식과 보고서를 만들어 데이터 입력과 판매 분석을 쉽게 수행할 수 있습니다.

추가 지원

언제든지 Excel 기술 커뮤니티의 전문가에게 문의하거나 커뮤니티에서 지원을 받을 수 있습니다.