※ 오늘 Daily Tip은 Crocii(최천군)님이 제공해주신 내용입니다.
엑셀에 VBA가 탑재되자 어느 분이 이렇게 비유하셨다고 합니다. “공룡이 날개를 달았다.” 적절한
표현입니다. 게다가 그 날개 달린 공룡에게는 똑똑한 동반자도 있습니다. 오늘은 날개 달린 공룡과
액세스의 입맞춤 씬 입니다.
엑셀의 데이터를 액세스와 공유하려면 어떤 방법이 있을까요? 두 가지 방법과 사용 예제를 소개하겠습니다.
엑셀에서 아래와 같은 데이터가 있습니다. 아시는 분은 눈치 챘겠지만 왼쪽의 노란 세 줄은 수식을 넣어서
자동화 했습니다. 날짜, 권, 번호가 바뀌는 행에서만 수작업으로 입력하면 그 밑으로는 거래처에 데이터가
있는지 여부에 따라 데이터가 자동 생성 되겠지요.
또 아래와 같이 조건부 서식을
설정하여 날짜와 권이 바뀔 때 오렌지 색으로 바뀌어 쉽게 알아 볼 수 있도록 했습니다.
이제 남은 일은 위의 데이터를
이용하여 일계표 또는 월계표 등의 자료로 변환되도록 자동화 해야겠지요. 즉 현금 매출, 외상 매출이
각각 얼마씩 이고 어느 업체에서 수금한 외상이 얼마이고 남은 외상은 업체마다 얼마씩 이다.. 라는
정보일 것 입니다.
물론 간단한 사용 예제이고 엑셀만으로도 가능한 작업입니다만 액세스의 DBE 즉 데이터베이스 엔진을 사용하면
보다 복잡한 작업도 수월하게 실행할 수 있습니다.
<데이터베이스에 엑셀 파일
연결하기>
<1> 액세스를 열고 새로 만들기를 클릭하여 적당한 폴더에 새 데이터베이스를
만듭니다. 아래 예제는 ‘C:\오튜’ 라는 폴더에 만들었습니다. 엑셀 파일도 같은 장소에
있습니다.
<2>
테이블 새로 만들기를 클릭한 후, 테이블 연결을 선택하고 확인
단추를
클릭합니다.
<3>
연결 대화상자가 나오면
파일형식을 Microsoft Excel (*.xls)로 바꾸고 데이터원본이
있는 엑셀파일을 찾아서 클릭합니다.
<4> 아래와 같은
대화상자가 나옵니다.
<5> 데이터 원본에
이름을 정의 하셨다면 왼쪽 위 라디오 버튼 중 “이름 붙인 범위 표시”를 선택하셔도
좋습니다.
<6> 위의 예제는 워크시트 표시를 선택하였고 오른쪽 리스트박스에 각 시트의 이름이 나열됩니다. 이중 데이터 원본이 있는 시트를 선택하고 다음을 클릭합니다.
<7> 첫 행에 열
머리글이 있으므로 상단의 체크박스에 체크 합니다. 다음을 클릭하여 테이블 이름을 별도로
작성하셔도 좋고 마침을 클릭하셔도 좋습니다. 마침을 클릭하면 현재 시트의
이름이 테이블 이름이 됩니다.
<8> 성공적으로 연결되면 '○○○ 테이블을 ○○○ 파일에 연결했습니다.'
라는 메시지가 나오면 확인 단추를 클릭합니다.
<9> 아래와 같이
엑셀에 연결된 테이블이 생성되었음을 확인할 수 있습니다.
<10> 이제 엑셀과
액세스의 데이터는 상호 연동 되기 때문에 엑셀에서 데이터 추가, 수정, 삭제하면 그대로 액세스로
반영되며 또한 액세스에서 작업 하여도 엑셀로 바로 반영이 됩니다.
<연결된 엑셀 파일을
이용하여 쿼리 사용하기>
<1> 이제 생성된 테이블을 쿼리를 통해 원하는 데이터로 변환시키는 작업을 합니다.
왼쪽의 빨간 박스에 있는 쿼리 탭을 클릭하여 디자인 보기에서 새 쿼리
만들기를 선택한 후 확인 단추를 클릭합니다.
<2> 위와 같은
대화상자가 나오면 매출일보를 더블클릭 하거나 추가 단추를 클릭하여 매출일보 테이블을
꺼냅니다.
<3> 위와 같이
매출일보 에서 거래처, 현금, 외상, 외상수금
항목을 더블클릭 하거나 밑으로 끌어 놓아 생성시킬 필드를 설정합니다. 그리고 아래쪽 디자인눈금에서
마우스 오른쪽 버튼을 클릭하여 ∑ 합계 라는 항목을 클릭합니다.
<4> 이 쿼리에서 남은 외상이 얼마인지 알기 위해 계산 필드를 생성해 줄 수도 있고
월별로 계산 시켜 줄 수도 있습니다. 쿼리를 저장합니다.
<5> 이제 이
DBE를 통해 가공된 데이터를 다시 엑셀로 불러 들입니다.
<엑셀에서 새 쿼리 만들기>
<1> 엑셀에서 새 시트를 선택하고 데이터 메뉴의 외부데이터 가져오기에
새 쿼리 만들기 순으로 클릭합니다.
<2> 아래와 같이
'MS Access Database*'를 선택합니다.
<3> 해당 MDB파일을
찾아 선택하고 확인 단추를 클릭합니다.
<4> 아래와 같이
테이블과 쿼리가 모두 목록으로 나타납니다. 해당쿼리를 클릭하고 [ > ] 버튼을 클릭하여
나타낼 필드를 선택합니다.
<5> 다음 버튼이
활성화 되면 확인이 나올 때 까지 클릭하여 확인 단추를 클릭합니다.
<6> 위와 같이
데이터가 생성 됩니다. 데이터 범위 아무 곳이나 마우스 오른쪽 버튼을 클릭하여 팝업 메뉴 중 하단의
데이터 범위 등록정보(A)를 클릭합니다.
<7> 하단의 인접한 열에 수식 자동 채우기를 체크 합니다.
<8> 아래와 같이
수식을 작성하면 데이터의 추가 삭제에 따라 수식도 자동으로 추가 삭제 되며 데이터에 서식을 설정하면
자동으로 서식도 변경 됩니다.
물론 위의 설정 옵션에 따라 데이터 또한 실시간 변경 됩니다.
[참고]
오늘 Daily Tip에 사용된 예제파일은 [www.officetutor.co.kr]-[배움터]-[자료실]에
'데일리팁-엑셀과 액세스 연동 샘플' 파일을 다운로드 받아 실습해보실 수 있습니다.
|
OfficeTutor 365