Excel 사용은 광범위하며 Excel은 데이터 입력, 분석 및 조작을 위해 자주 사용하는 Office 응용 프로그램입니다. Microsoft Access는 소형 데스크탑 환경에서 관계형 데이터베이스 기능을 제공합니다. Access에 데이터 처리 기능이 많고 인터페이스가 사용자 친화적임에도 불구하고 Access를 사용하는 사람은 적습니다. Access에는 테이블 생성, 각 테이블에 대한 데이터 유형 지정 및 제한, 단순 및 고급 쿼리 생성, SQL 조작, 사용자 입력을 위한 양식 생성 등 관계형 데이터베이스 응용 프로그램의 모든 기능이 있습니다. Excel은 보다 친숙한 인터페이스일 뿐만 아니라 고급 차트 작성 기능과 확장된 피벗 테이블 기능 및 옵션을 제공합니다.
Excel과 Access의 통합을 통해 Access의 관계형 데이터베이스 인터페이스 및 환경과 결합된 Excel의 손쉬운 데이터 입력 및 고급 차트 작성 기능을 활용할 수 있습니다.
다음은 두 Office 응용 프로그램 간에 데이터를 교환하기 위한 몇 가지 유용한 팁입니다.
1) Access 테이블 구조 및 생성에 관련된 로직 이해
Access는 언급한 대로 관계형 데이터베이스입니다. 이는 Access 데이터베이스의 테이블이 모두 서로 관련되어 있음을 의미합니다. Access 데이터베이스의 각 레코드는 고유하게 식별할 수 있어야 합니다. 이것은 기본 키를 사용하여 수행됩니다. 이 기본 키는 일반적으로 하나의 필드로 구성되지만 경우에 따라 둘 이상의 필드가 조합될 수 있습니다. 이 기본 키는 필수이며 Access는 테이블의 기본 키로 사용되는 ID 열을 자동으로 추가합니다. Excel에서 기본 키는 테이블을 생성할 때 필수 요구 사항이 아니므로 사용자가 생략하는 경우가 많습니다.
이 상황은 아래에 나와 있으며 첫 번째 이미지는 데이터시트와 디자인 보기 모두에 있는 Access 테이블과 자동으로 추가되는 기본 키를 보여줍니다. 두 번째 이미지는 Excel 표와 데이터가 Excel에 입력될 가능성이 가장 높은 방법을 보여줍니다.
Excel 표의 데이터 입력이 올바르지 않습니다. 그러나 Excel을 데이터 입력 소프트웨어로 사용하고 나중에 Access로 표를 가져올 계획이라면(이는 Excel을 Access의 사용자 친화적인 양식으로 사용하는 것과 유사함) 처음부터 가장 좋습니다. Excel에서 스프레드시트를 설정할 때 Excel 표에 기본 키를 추가합니다.
이는 사용자가 기본 키 개념에 익숙해지고 방향을 잡을 수 있도록 하고 가능한 한 초기에 데이터 중복 및 중복을 제거하기 위한 것입니다. Excel에서 표를 만들 때 Excel에서 수행할 수 있을 만큼 간단합니다. 기본 키 열이 포함되지 않은 비교적 작은 테이블이 있고 Excel 테이블에 중복이나 중복이 없는 경우 이 열을 쉽게 추가할 수 있습니다. 간단히 테이블의 첫 번째 열에 있는 셀을 마우스 오른쪽 버튼으로 클릭하고 삽입, 왼쪽에 있는 테이블 열을 선택합니다. 그런 다음 이 열의 이름을 바꾸고 ID라고 합니다. 이 열을 채우려면 열의 첫 번째 셀을 선택하고 1을 입력한 다음 열의 두 번째 셀로 이동하여 2를 입력한 다음 위의 두 셀을 강조 표시하고 자동 채우기를 사용하여 아래로 끌어 나머지 열을 채웁니다. .
또한 기억해야 할 편리한 팁은 Access 테이블의 필드 이름이 Excel 테이블의 열 머리글과 동일하다는 것입니다.
Access에서 테이블의 데이터를 복사하여 Excel의 새 시트에 붙여넣을 수도 있습니다. 데이터 양이 적은 Access 테이블에 권장됩니다. 복사하려는 Access의 모든 행을 선택하고 홈 탭으로 이동하여 클립보드 그룹에서 복사를 선택하거나 CTRL-C를 누릅니다. 키보드에서.
그런 다음 Excel 통합 문서의 새 시트에서 CTRL-V 를 누르십시오. Excel 시트에 Access 테이블을 붙여넣으려면 키보드에서 Access 표는 제목이 있는 범위로 붙여넣고 범위에서 셀을 선택하고 CTRL-T 를 눌러 Excel 표로 변환할 수 있습니다. 당신의 키보드에. 내 테이블에 헤더가 있음 확인란이 선택되어 있는지 확인한 다음 확인을 눌러 범위를 Excel 테이블로 변환합니다.
위에서 언급 한 점에서 이어집니다. Access에서 정보는 주요 엔터티 또는 주제로 나뉩니다. 예를 들어, 소규모 상점용으로 설계된 Access 데이터베이스에는 제품, 주문, 고객 및 직원(지정된 관계 포함)이라는 별도의 테이블이 포함될 가능성이 높습니다. Excel에서는 Access에서는 별도의 테이블에 저장되어 있던 정보가 아래와 같이 하나의 Excel 테이블에 결합되어 있는 것을 볼 수 있습니다.
이제 언급했듯이 데이터가 Excel 테이블에 입력되는 방식은 잘못된 것이 아닙니다. 그러나 Excel과 Access가 통합될 경우 Excel은 데이터 입력 소프트웨어이고 Access는 쿼리 및 데이터베이스 기능을 제공합니다. 하나의 큰 표에 저장된 Excel의 정보를 4개의 작은 표로 나누어 아래와 같이 각 표를 별도의 시트에 배치하는 것이 가장 좋습니다. 또한 Access의 테이블에 있는 필드 이름을 Excel의 테이블에 있는 열 제목과 일치시켜 혼동을 피하고 일관성을 유지하는 것이 좋습니다.
2) Excel에서 중복 제거, 후속 테이블 디자인의 용이성 또는 소스 데이터를 Access로 통합
많은 데이터 행이 포함된 Excel 테이블에서 고유한 값을 한 눈에 보기 어려운 경우가 많습니다. Excel 통합 문서에서 수집한 정보를 사용하여 Access에서 효율적인 테이블을 디자인하기 위해 100개의 데이터 행이 포함된 Excel 테이블의 고유한 제품 수를 확인하려고 한다고 가정해 보겠습니다. 이 예의 Excel 통합 문서는 가상의 온라인 소매업체의 일부 데이터를 기반으로 합니다. 엑셀 테이블은 아래와 같이 상품이 판매된 날짜, 상품명, 판매 수량을 포함하고 있습니다.
먼저 원본 데이터를 그대로 유지한 다음 복사본에서 작업하기 위해 통합 문서의 테이블이 포함된 스프레드시트의 복사본을 만듭니다. Excel 표의 셀을 선택한 상태에서 상황에 맞는 표 도구 옵션을 활성화하려면 디자인 탭을 선택하고 도구 그룹에서 아래와 같이 중복 제거 옵션을 선택합니다.
중복 제거 대화 상자가 나타나면 Date_Of_Sale 및 Quantity를 선택 취소하고 확인을 클릭합니다. 제거된 중복 값의 수를 나타내는 메시지 상자가 팝업되어야 합니다. 확인을 클릭합니다.
Excel의 결과 테이블은 온라인 소매업체가 제안한 Access 데이터베이스에 대한 Access 테이블에 대한 정보를 수집하는 데 사용할 수 있습니다.
자세히 보기:Excel에서 고유 값을 다른 워크시트에 복사하는 방법(5가지 방법)
3) TRIM 함수()를 사용하여 불필요한 공백을 제거하고 밑줄을 사용하여 Access의 필드 이름과 Excel의 열 머리글 간의 일관성 유지
액세스 필드 이름은 선행 공백으로 시작할 수 없으므로 Excel에서 테이블의 열 머리글 이름에 TRIM 함수()를 사용하여 공백을 제거할 수 있습니다.
또한 결국 SQL Server나 Oracle과 같은 다른 데이터베이스로 데이터를 가져와야 한다고 가정해 보겠습니다. 따라서 통합 솔루션의 흐름은 Excel에 데이터를 입력한 다음 이 데이터를 Access로 내보낸 다음 Access 테이블을 SQL Server 또는 Oracle과 같은 엔터프라이즈 수준 데이터베이스로 가져오는 것입니다. 다른 데이터베이스는 필드 이름 사이의 공백을 잘 읽지 않습니다. 따라서 Excel 테이블의 제목 열 이름을 부르는 대신 제품 이름을 Name_of_Product라고 부르고 밑줄을 사용하여 다른 데이터베이스에 쉽게 통합할 수 있습니다. SQL은 또한 공백이 있는 이름보다 이러한 이름 지정 스타일의 테이블을 훨씬 더 잘 처리합니다.
Access에서 캡션 속성을 사용할 수 있으므로 테이블의 필드 이름이 Name_of_Product인 경우 캡션 또는 레이블을 디자인 보기에서 제품 이름으로 입력합니다. 따라서 데이터시트 보기 또는 양식에서는 Name of Product가 대신 사용되지만 기본 필드 이름에는 밑줄이 있습니다. 이는 주로 Access에서 데이터베이스의 프런트 엔드 뷰어를 위한 미적 목적입니다.
유사한 수치
- 런타임 오류 1004:범위 클래스의 PasteSpecial 메서드 실패
- Excel에서 값을 복사하여 붙여넣는 수식(예제 5개)
- Excel에서 링크를 붙여넣고 조옮김하는 방법(8가지 빠른 방법)
- 셀을 병합하여 Excel에서 복사 및 붙여넣기(2가지 방법)
- VBA를 사용하여 헤더 없이 보이는 셀만 복사하는 방법
4) Excel에서 Access로 데이터 가져오기 – 두 가지 주요 시나리오 처리
- 시나리오 1에서는 Access 데이터베이스가 완전히 비어 있고 Excel에서 데이터를 가져오고(Excel의 테이블은 ID로 설정되고 중복 및 중복이 최대한 감소됨) Excel을 다음과 같이 사용하려고 합니다. 데이터 입력 프로그램 또는 양식. Access 데이터베이스를 연 상태에서 외부 데이터 탭으로 이동하여 가져오기 및 연결 그룹에서 그림과 같이 Excel을 선택합니다.
외부 데이터 가져오기 – Excel 스프레드시트 마법사가 팝업되어야 합니다. 데이터가 포함된 Excel 통합 문서로 이동합니다. 연결 테이블을 만들어 데이터 원본에 연결 옵션을 선택합니다. 이렇게 하면 사용자가 Excel에 데이터를 입력할 수 있기 때문에 Excel에 데이터를 입력할 수 있습니다. Excel에서 이 데이터에 대한 모든 변경 사항은 Access에 반영되지만 Access에서는 아무 것도 편집할 수 없습니다. 데이터시트 보기의 데이터 입력 측면에서 Access에서는 거의 준비된 상태입니다. 확인을 클릭합니다.
Excel에서 테이블이 올바르게 설정되면 Access는 아래와 같이 첫 번째 행에 열 머리글이 포함되어 있는지 자동으로 감지하고 확인합니다.
다음을 클릭하면 연결 테이블 이름을 입력하고 마침을 클릭하도록 요청하는 프로세스의 마지막 단계로 이동합니다.
테이블이 연결되었다는 메시지가 팝업되어야 합니다.
이렇게 하면 Excel 데이터가 Access 환경에서 사용할 수 있는 추가 SQL 기능과 복잡한 쿼리 생성을 위한 식 작성기에 노출됩니다. 복잡한 쿼리의 결과는 나중에 Excel 대시보드에서 사용할 수 있습니다. Excel 데이터 입력 테이블과 연결된 테이블이 있는 Access 데이터베이스를 구성 목적으로 동일한 폴더나 디렉터리에 보관하는 것이 권장되지만 필수는 아닙니다.
- 시나리오 2에서 하나는 비어 있거나 채워진 Access 데이터베이스를 가지고 있고 다른 하나는 Excel 테이블을 가져오려고 하지만 연결하지 않으려고 합니다. 하나는 Access에서 편집하고 싶어합니다. 그런 식으로 나중에 Excel 시트에서 변경한 내용은 Access에 반영되지 않고 편집 및 쿼리를 위해 Access에 데이터가 완전히 저장됩니다. 이것은 일반적으로 테이블에 입력된 데이터 유형을 엄격하게 제어해야 할 때 선택되는 옵션입니다.
다시 Access 데이터베이스를 연 상태에서 외부 데이터 탭으로 이동하여 가져오기 및 연결 그룹에서 표시된 대로 Excel을 선택합니다.
이 경우 마법사가 나타나면 원본 데이터를 현재 데이터베이스의 새 테이블로 가져오기를 선택하여 현재 원본 Excel 테이블에 연결 해제된 Access에서 테이블 복사본을 만듭니다. 확인을 클릭합니다.
다음 화면에서 다음을 클릭하세요.
그런 다음 아래와 같이 가져오는 필드에 대한 정보를 지정하게 됩니다. 마법사를 사용하면 건너뛸 필드도 선택할 수 있습니다. Excel 표에는 Access의 다른 표에 실제로 있어야 하는 데이터가 포함될 수 있으므로 이러한 열을 현재 표로 가져오는 것을 방지할 수 있습니다.
그런 다음 Access에서 기본 키를 추가하거나 자신의 기본 키를 선택할 수 있습니다. 이 경우 Excel에서 테이블에 이미 지정된 ID 열이 있으므로 두 번째 옵션이 그림과 같이 선택되었습니다.
다음을 클릭하십시오.
그런 다음 Access에서 테이블 이름을 선택하고 마침을 클릭합니다.
가져오기 단계를 저장할 것인지 묻는 메시지가 표시됩니다. 지금은 닫기를 클릭하세요. The table is now in Access for further editing, entry, and manipulation and is unlinked to the Excel workbook.
5) Using Table Analyzer to normalize imported data and split the data into separate tables in Access
Later versions of Access, have the Table Analyzer tool, which helps users separate one table into many tables. This is especially useful if one did import the data from a big Excel table and one is struggling to separate the tables. One can launch this wizard as part of the import process and check the box, I would like a wizard to analyze my table after importing the data as shown below.
Or one can access it, if the tables are already imported as standalone Access tables, by going to Database Tools, in the Analyze group, choose Analyze Table.
The Table Analyzer wizard should be launched, which will guide you through the steps of creating separate tables.
Table Analyzer, however, does not always guess right, so use it with caution.
6) Importing data from Access into Excel and using VLOOKUP to link the data and populate columns
One can import Access tables into Excel worksheets. With an Excel workbook opened, go to the Data tab, in the Get External Data group, choose From Access as shown below.
Navigate to the Access database containing the tables, you want to import into your Excel spreadsheet.
Select the table one wants to import, in this case, the ProductT.
Click Ok.
Choose to Import the data as a Table, on the existing worksheet, choose cell A1, and then Click Ok.
The table is imported as an Excel table.
The same was done for the three other tables in the database, namely Orders, Employees, and Customers tables.
One now has an Orders table which looks like the one below.
This table with numbers in Customer_ID, Product_ID, and Employee_ID can be counter-intuitive and confusing for Excel users not used to the relational database theory and tables. Thus one could add three helper columns to the table called Actual_Customer, Actual_Product, and Actual_Employee as shown below.
In Cell F2, the following formula was used:
=VLOOKUP([@[Customer_ID]],Table_TablesToBeImportedIntoExcel.accdb5,2,FALSE)
Where, [@[Customer_ID]] refers to cell C2, which contains the lookup value, Table_TablesToBeImportedIntoExcel.accdb5 refers to the imported Customers table on the Customers sheet, (Excel gives this table a default name), highlight the data, but not the column headings, the same way one would for a normal VLOOKUP, the customer name is in column 2, and the type of lookup is an exact match.
Double click and send the formula down to populate the Actual_Customer column.
Do the same thing to populate the Actual_Product and Actual_Employee columns. So one can quickly repopulate Excel tables with actual text values as needed, using the VLOOKUP functionality in order to orientate the Excel users.
7) Using data already set up in a relational Access database, for Power Pivot manipulation
Power Pivot is a Business Intelligence tool available with Excel 2013 and later versions of Excel. It also requires data to be input in a relational manner. So, what you can do when you are importing Access tables into Excel (already normalized and designed using relational data logic), is to add the imported tables to the data model.
In a blank workbook, go to Data, get External data and choose from Access as for the case above. However, this time when asked to select a table, check the Enable selection of multiple tables option in the Select Table dialog box and select all the tables as shown below:
Click Ok.
Make sure the PivotTable Report option is selected, the Add this data to the Data Model should be checked because we imported multiple tables. If you only import one table at a time, you will need to check this option yourself. If you click on the drop-down arrow next to Properties, you should see that the relationships between the tables will also be imported.
Click Ok to create the Pivot TableReport, with the tables already added to the Data Model. The relationships should be visible in the PowerPivot for Excel Window, Diagram view. Thus the data is ready for further manipulation using Power Pivot functionality.
Download Files
Exchange-Data-Between-Access-and-Excel
결론
Excel and Access integration allow one tallow advantage of the combination of features in Excel and Access. The key to integrating Excel and Access successfully is understanding a little bit about relational database design basics.
Understanding a little bit of relational design and table structure will also set you in good stead with respect to taking advantage of Power Pivot and related Business Intelligence tools in the Excel environment.
Please feel free to comment and tell us if you integrate Excel and Access, whether or not you use Power Pivot, and any other tips for integrating Excel and Access, that you may have.
관련 기사
- Excel VBA:Copy Cell Value and Paste to Another Cell
- Excel VBA:Copy Range to Another Workbook
- [Fixed]:Right Click Copy and Paste Not Working in Excel (11 Solutions)
- Difference Between Paste and Paste Special in Excel
- How to Copy Alternate Rows in Excel (4 Ways)
- How to Apply VBA PasteSpecial and Keep Source Formatting in Excel