Excel là một công cụ mạnh mẽ để phân tích dữ liệu và tự động hóa sau khi xử lý các tập hợp dữ liệu lớn. Bạn có thể dành thời gian đáng kể để phân tích hàng tấn dữ liệu bằng hàm VLOOKUP, INDEX-MATCH, SUMIF, v.v...
Nhờ Excel Data Model, bạn có thể tiết kiệm thời gian quý báu thông qua các báo cáo dữ liệu tự động. Tìm hiểu xem bạn có thể gán mối quan hệ giữa hai bảng dễ dàng như thế nào bằng cách sử dụng Data Model và minh họa về mối quan hệ đó trong bài viết sau đây.
Các yêu cầu cơ bản
Bạn sẽ cần Power Pivot và Power Query (Get & Transform) để hoàn thành một số tác vụ trong khi tạo Excel Data Model. Đây là cách bạn có thể có được những tính năng này trong Excel workbook của mình:
Cách tải Power Pivot
1. Excel 2010: Bạn cần tải xuống add-in Power Pivot từ Microsoft, sau đó cài đặt nó cho chương trình Excel trên máy tính.
2. Excel 2013: Phiên bản Office Professional Plus của Excel 2013 bao gồm Power Pivot. Tuy nhiên, bạn cần phải kích hoạt nó trước khi sử dụng lần đầu. Đây là cách thực hiện:
- B1: Nhấp vào File trên ribbon của Excel workbook.
- B2: Sau đó nhấp vào Options để mở Excel Options.
- B3: Bây giờ, hãy nhấp vào Add-ins.
- B4: Chọn COM Add-ins bằng cách nhấp vào menu drop-down của hộp Manage.
- B5: Nhấp vào Go và sau đó chọn hộp kiểm cho Microsoft Power Pivot for Excel.
Cách tải Power Query (Get & Transform)
1. Excel 2010: Bạn có thể tải xuống add-in Power Query từ Microsoft. Sau khi cài đặt, Power Query sẽ hiển thị trên ribbon.
2. Excel 2013: Bạn cần kích hoạt Power Query bằng cách làm theo các bước tương tự mà bạn vừa làm để khiến cho Power Pivot hoạt động trong Excel 2013.
3. Excel 2016 trở lên: Bạn có thể tìm Power Query (Get & Transform) bằng cách chuyển đến tab Data trên Excel ribbon.
Tạo Data Model bằng cách nhập dữ liệu vào Excel Workbook
Đối với hướng dẫn này, bạn có thể lấy dữ liệu mẫu được định dạng trước từ Microsoft:
- Tải Dữ liệu sinh viên mẫu (chỉ dữ liệu) | Dữ liệu sinh viên mẫu (mô hình hoàn chỉnh)
Bạn có thể nhập cơ sở dữ liệu với nhiều bảng liên quan từ nhiều nguồn như Excel workbook, Microsoft Access, trang web, SQL Server, v.v... Sau đó, bạn cần định dạng tập dữ liệu để Excel có thể sử dụng nó. Dưới đây là các bước mà bạn có thể thử:
1. Trong Excel 2016 và các phiên bản mới hơn, hãy nhấp vào tab Data và chọn New Query.
2. Bạn sẽ tìm thấy một số cách để nhập dữ liệu từ các nguồn bên ngoài hoặc bên trong. Chọn một cách phù hợp với bạn.
3. Nếu sử dụng phiên bản Excel 2013, hãy nhấp vào Power Query trên ribbon và sau đó chọn Get External Data để chọn dữ liệu nhập.
4. Bạn sẽ thấy hộp Navigator nơi bạn cần chọn những bảng nào mình cần nhập. Bấm vào hộp kiểm Select multiple items to pick several tables for import.
5. Nhấp vào Load để hoàn tất quá trình nhập.
6. Excel sẽ tạo Data Model cho bạn bằng các bảng này. Bạn có thể xem tiêu đề cột bảng trong danh sách PivotTable Fields.
Bạn cũng có thể sử dụng các hàm Power Pivot như cột được tính toán, KPI, phân cấp, trường được tính toán và tập dữ liệu đã lọc từ Excel Data Model. Vì mục đích này, bạn sẽ cần tạo Data Model từ một bảng. Bạn có thể thử các bước sau:
1. Định dạng dữ liệu theo mô hình bảng bằng cách chọn tất cả các ô chứa dữ liệu rồi nhấp Ctrl + T.
2. Bây giờ, hãy chọn toàn bộ bảng và sau đó nhấp vào tab Power Pivot trên ribbon.
3. Từ phần Tables, nhấp vào Add to Data Model.
Excel sẽ tạo mối quan hệ bảng giữa các dữ liệu liên quan từ Data Model. Đối với điều này, cần có các mối quan hệ khóa chính và khóa ngoài trong các bảng đã nhập.
Excel sử dụng thông tin mối quan hệ từ bảng đã nhập làm nền tảng để tạo kết nối giữa các bảng trong Data Model.
Xây dựng mối quan hệ giữa các bảng trong Data Model
Bây giờ, bạn đã có Data Model trong Excel workbook của mình, bạn sẽ cần xác định mối quan hệ giữa các bảng để tạo báo cáo có ý nghĩa. Bạn cần chỉ định một mã định danh trường duy nhất hoặc khóa chính cho mỗi bảng, như Semester ID, Class Number, Student ID, v.v...
Tính năng Diagram View của Power Pivot sẽ cho phép bạn kéo và thả các trường đó để xây dựng mối quan hệ. Làm theo các bước sau để tạo liên kết bảng trong Excel Data Model:
1. Trên ribbon của Excel workbook, bấm vào menu Power Pivot.
2. Bây giờ, hãy nhấp vào Manage trong phần Data Model. Bạn sẽ thấy trình chỉnh sửa Power Pivot như được hiển thị bên dưới:
3. Bấm vào nút Diagram View nằm trong phần View của tab Power Pivot Home. Bạn sẽ thấy các tiêu đề cột trong bảng được nhóm theo tên bảng.
4. Giờ đây, bạn có thể kéo và thả mã nhận dạng trường duy nhất từ bảng này sang bảng khác. Sau đây là sơ đồ mối quan hệ giữa 4 bảng của Excel Data Model:
Sau đây mô tả mối liên kết giữa các bảng:
- Bảng Students | Student ID đến bảng Grades | Student ID
- Bảng Semesters | Semester ID đến bảng Grades | Semester
- Bảng Classes | Class Number đến bảng Grades | Class ID
5. Bạn có thể tạo mối quan hệ bằng cách chọn một cặp cột giá trị duy nhất. Nếu có bất kỳ bản sao nào, bạn sẽ thấy lỗi sau:
6. Bạn sẽ thấy dấu sao (*) ở một bên và số (1) ở bên kia trong chế độ xem sơ đồ (Diagram View) của các mối quan hệ. Nó định nghĩa rằng mối quan hệ một cái này với nhiều cái khác tồn tại giữa các bảng.
7. Trên trình chỉnh sửa Power Pivot, hãy nhấp vào tab Design rồi chọn Manage Relationships để biết trường nào tạo kết nối.
Tạo PivotTable bằng Excel Data Model
Bây giờ, bạn có thể tạo PivotTable hoặc PivotChart để trực quan hóa dữ liệu của mình từ Excel Data Model. Một Excel workbook có thể chỉ chứa một Data Model, nhưng bạn có thể tiếp tục cập nhật các bảng.
Bởi vì dữ liệu thay đổi theo thời gian, bạn có thể tiếp tục sử dụng cùng một mô hình và tiết kiệm thời gian khi làm việc với cùng một tập dữ liệu. Bạn sẽ thấy mình tiết kiệm được nhiều thời gian hơn khi làm việc trên dữ liệu với hàng nghìn hàng và cột. Để tạo báo cáo dựa trên PivotTable, hãy làm theo các bước sau:
1. Trên trình chỉnh sửa Power Pivot, hãy nhấp vào tab Home.
2. Trên ribbon, nhấp vào PivotTable.
3. Chọn New Worksheet hoặc Existing Worksheet.
4. Chọn OK. Excel sẽ thêm một PivotTable hiển thị bảng điều khiển Field List ở bên phải.
Chúc bạn thực hiện thành công!
Nguồn: quantrimang.com
