Cách tạo mô hình dữ liệu trong Excel: Hướng dẫn toàn diện

Mô hình dữ liệu cho phép chúng ta nạp, tích hợp và phân tích dữ liệu từ nhiều nguồn, giúp việc nhìn nhận dữ liệu linh hoạt và sâu sắc hơn. 

Xuyên suốt hướng dẫn này, chúng ta sẽ khám phá nhiều cách tạo mô hình dữ liệu trong Excel, bao gồm dùng các bảng sẵn có, nhập bảng mới và tận dụng các công cụ như Power Query, Power Pivot và PivotChart.   

Bắt đầu thôi!

Cách tạo mô hình dữ liệu trong Excel

Dưới đây là các bước tổng quát để tạo mô hình dữ liệu trong Excel:

1. Định dạng dữ liệu của bạn thành các bảng.
2. Tạo mối quan hệ giữa các bảng.
3. Sử dụng Power Query để nạp và biến đổi dữ liệu.
4. Dùng Power Pivot cho mô hình dữ liệu nâng cao.
5. Tận dụng PivotTable và PivotChart để trực quan hóa dữ liệu.

Khi nào nên dùng mô hình dữ liệu trong Excel

Tạo mô hình dữ liệu trong Excel đặc biệt hữu ích trong một số tình huống phổ biến như làm sạch dữ liệu, tích hợp và biến đổi dữ liệu, cũng như phân tích dữ liệu phức tạp. Dưới đây là một số lợi ích chính.

• Hỗ trợ làm sạch dữ liệu: Mô hình dữ liệu giúp làm sạch và chuẩn hóa các tập dữ liệu khác nhau để đảm bảo tính thống nhất và nhất quán. Điều này rất quan trọng khi xử lý dữ liệu từ nhiều nguồn cần trộn và tiền xử lý. 
• Thực hiện/giải quyết tích hợp và biến đổi dữ liệu: Khi có mô hình dữ liệu, toàn bộ dữ liệu từ bất kỳ nguồn nào khác đều có thể được tích hợp và biến đổi dễ dàng, người dùng dữ liệu sẽ có một góc nhìn hợp nhất, dễ xử lý và phân tích.
• Nâng cao trực quan hóa dữ liệu: Mô hình dữ liệu hỗ trợ phát triển các trực quan hóa nâng cao như PivotChart và báo cáo Power View. 
• Cải thiện bảo mật và quản lý dữ liệu: Với mô hình dữ liệu, có thể kiểm soát quyền truy cập đến các phần khác nhau của dữ liệu. Bạn sẽ dễ dàng quản lý quyền và dữ liệu trong mô hình để giảm rủi ro truy cập trái phép và rò rỉ dữ liệu.
• Tinh gọn báo cáo dữ liệu: Thông qua mô hình dữ liệu, việc báo cáo có thể được tự động hóa hoàn toàn, từ đó hỗ trợ quy trình báo cáo và ra quyết định. Báo cáo và dashboard có thể điều chỉnh động khi có dữ liệu mới, đảm bảo mọi bên liên quan luôn có thông tin cập nhật.
• Xử lý tập dữ liệu lớn: Mô hình dữ liệu có thể làm việc với các tập dữ liệu lớn trong Excel, vượt qua các hạn chế của trang tính truyền thống.
• Cho phép phân tích dữ liệu phức tạp: Khả năng tạo mối quan hệ giữa nhiều bảng dữ liệu cho phép thực hiện các phân tích nâng cao mà các hàm Excel thông thường khó đáp ứng.

Cách tạo mô hình dữ liệu bằng cách nhập dữ liệu

Trong phần này, chúng ta sẽ đi qua quy trình tạo mô hình dữ liệu trong Excel bằng cách nhập các tập dữ liệu mẫu. Chúng ta sẽ dùng hai tập dữ liệu mẫu: Order và Product. Tôi đã tạo một kho GitHub công khai có tên Excel-Data-Model để bạn tải dữ liệu và thực hành theo hướng dẫn.

Bước đầu tiên là nhập dữ liệu vào Excel. Mở một sổ làm việc Excel mới và nhấp vào thẻ Data. Dưới mục Get Data, chọn Get Data > From File > From Excel Workbook.

Nhập dữ liệu vào Excel. Ảnh: Tác giả

Chọn sổ làm việc Order.xlsx và nhấp Import. Cửa sổ Navigator sẽ mở ra: Chọn bảng Sheet1.

Cửa sổ Navigator trong Excel. Ảnh: Tác giả

Nhấp tùy chọn Load To để mở cửa sổ Import Data. Trong hộp thoại, chọn Only Create Connection và đánh dấu Add this data to the Data Model. Nhấp OK.

 

Cửa sổ Import Data. Ảnh: Tác giả

Lặp lại các bước trên để nạp trang Product. Sau khi hoàn tất hai tác vụ này, sổ làm việc Excel của bạn sẽ như sau:

 

Ngăn Queries & Connections. Ảnh: Tác giả

Chúc mừng! Bạn đã nhập cả hai trang tính vào tệp Excel. Bước tiếp theo là thiết lập mối quan hệ giữa các bảng đã nhập. Để làm điều này, vào thẻ Data Tools và nhấp Relationships.

Thẻ Data Tools. Ảnh: Tác giả 

Một cửa sổ mới sẽ xuất hiện.

Cửa sổ Manage Relationships. Ảnh: Tác giả

Nhấp New để tạo mối quan hệ. Hộp thoại Create Relationship sẽ mở. Xác định mối quan hệ như minh họa bên dưới. 

Cửa sổ Create Relationship. Ảnh: Tác giả

Nhấp OK. Mối quan hệ đã được thiết lập và bạn có thể xác nhận trong thẻ Connections.

Ngăn Queries & Connections. Ảnh: Tác giả

Giai đoạn tiếp theo là tạo PivotTable. Vào thẻ Insert và nhấp PivotTable. Sau đó, trong hộp thoại PivotTable, chọn Use this workbook’s Data Model.

Hộp thoại PivotTable. Ảnh: Tác giả

Chọn vị trí cho PivotTable. Nhấp OK. PivotTable sẽ mở và bạn có thể xem các trường trong PivotTable Fields.

PivotTable Fields. Ảnh: Tác giả

Để xây dựng PivotTable, thực hiện bước sau.

Trong ngăn PivotTable Fields, kéo các trường vào khu vực Rows, Columns, Values và Filters:

• Rows: ProductName
• Values: Quantity

PivotTable thu được sẽ như sau:

Ngăn PivotTable Fields. Ảnh: Tác giả

Bạn có thể tùy chỉnh và định dạng PivotTable theo nhu cầu.

Cách tạo mô hình dữ liệu bằng Power Query 

Ở phần này, chúng ta sẽ dùng dữ liệu lưu trong tệp Excel có tên Sample_Data. Tệp này cũng có trong liên kết ở phần trước. Có hai bảng trong trang Data với các bản ghi như sau.

Các bảng Excel để nối. Ảnh: Tác giả

Chúng ta sẽ xây dựng mô hình dữ liệu ngay trong trang tính Excel hiện có. Bắt đầu bằng cách chọn bất kỳ ô nào trong bảng Customer ID and Name rồi nhấn Ctrl + T.

Chọn một ô. Ảnh: Tác giả

Một hộp thoại Create Table sẽ mở như dưới đây.

Hộp thoại Create table. Ảnh: Tác giả

 Nhấp OK và bạn sẽ có bảng sẵn sàng. 

PivotTable trong Excel. Ảnh: Tác giả

Giờ hãy đặt tên cho bảng này. Vào cửa sổ ngữ cảnh Table Design và dưới mục Table Name, đổi tên bảng thành CustomerPQ. Lưu ý bạn có thể đặt tên khác.

Cửa sổ ngữ cảnh Table Design. Ảnh: Tác giả

Bây giờ bảng này được đặt là CustomerPQ.

Cửa sổ ngữ cảnh Table Design. Ảnh: Tác giả

Lặp lại quy trình cho bảng thứ hai. Lần này, sau khi tạo bảng, đặt tên là PurchasePQ.

Hai bảng trong Excel sẵn sàng cho mô hình dữ liệu. Ảnh: Tác giả

Bây giờ bảng hoạt động là PurchasePQ. Vào Data và chọn From Table Range. Trang sau sẽ xuất hiện.

Mở Power Query Editor. Ảnh: Tác giả

Để tạo kết nối và thêm bảng Purchase PQ vào Data Model, nhấp vào menu thả xuống của Close & Load và chọn Close & Load To…  

Đóng Power Query Editor. Ảnh: Tác giả

Một cửa sổ Import Data sẽ mở, cần được điền như sau:

Cửa sổ Import Data. Ảnh: Tác giả 

Sau khi hoàn tất các bước trên, bạn sẽ thấy hộp Queries & Connections.

Hộp Queries & Connections. Ảnh: Tác giả

Bây giờ, thêm bảng CustomerPQ vào kết nối. Để làm vậy, nhấp vào bất kỳ ô nào của bảng, vào Data và chọn From Table Range. Trang sau sẽ xuất hiện.

Các bảng sẵn sàng cho mô hình dữ liệu Excel. Ảnh: Tác giả

Lặp lại quy trình bạn đã thực hiện cho bảng PurchasePQ và điền cửa sổ Import Data như dưới đây:

Cửa sổ Import Data. Ảnh: Tác giả 

 Nhấp OK. Thẻ Queries & Connections giờ sẽ hiển thị cả hai bảng.

Queries and Connections. Ảnh: Tác giả

Giờ chúng ta sẵn sàng thiết lập mối quan hệ. Trong thẻ Data, vào phần Data Tools và nhấp Manage Data Model.

Data Tools. Ảnh: Tác giả

Thao tác này sẽ mở Power Pivot for Excel.

Power Pivot for Excel. Ảnh: Tác giả

Để tạo mối quan hệ giữa hai bảng, nhấp Diagram View.

Ngăn View. Ảnh: Tác giả

 Màn hình sẽ mở như dưới đây. 

Diagram View. Ảnh: Tác giả

Bạn có thể thấy có hai bảng nhưng chưa được nối. Để tạo mối quan hệ, vào thẻ Design và nhấp Create Relationship.

Thẻ Design. Ảnh: Tác giả

Một cửa sổ mới sẽ mở. Điền các chi tiết như dưới đây:

Cửa sổ Create a Relationship. Ảnh: Tác giả

Nhấp OK. Bạn sẽ thấy mối quan hệ đã được thiết lập.

Cửa sổ hiển thị mối quan hệ đã thiết lập. Ảnh: Tác giả 

Lưu Power Pivot bằng nút lưu hoặc Ctrl + S, rồi đóng thẻ. Kết quả sau đó hiển thị như sau.

Hộp thoại Enable Content. Ảnh: Tác giả

Nhấp Enable Content để tạo kết nối. Bây giờ kết nối đã được thiết lập. Chúng ta có thể dùng PivotTable. Để làm vậy, vào Insert, nhấp PivotTable và chọn From Data Model.

Ngăn PivotTable. Ảnh: Tác giả

Bạn sẽ thấy hộp thoại như sau:

Thẻ PivotTable from Data Model. Ảnh: Tác giả

Nhấp New Worksheet rồi OK. Một trang mới tên Sheet1 sẽ được tạo.

Trang PivotTable. Ảnh: Tác giả 

Trong PivotTable Fields, bạn có thể thấy các bảng.

Các nhãn dưới PivotTable Fields. Ảnh: Tác giả

Chọn Customer Name trong bảng CustomerPQ. Tiếp theo dưới PurchasePQ, chọn Purchase. PivotTable Fields sẽ trông như sau:

Chọn mục trong PivotTable Fields. Ảnh: Tác giả 

Điều này sẽ tạo PivotTable trong trang, trông như dưới đây:

Kết quả PivotTable. Ảnh: Tác giả

Tuyệt vời! Bằng cách này, bạn có thể dùng Power Query để tạo Data Model và dùng Power Pivot để tạo báo cáo tóm tắt.

Cách tạo mô hình dữ liệu trực tiếp trong Power Pivot

Trong phần này, bạn sẽ dùng Power Pivot để tạo mô hình dữ liệu và chuẩn bị báo cáo tóm tắt. 

Bắt đầu bằng cách vào trang Power Pivot trong dữ liệu đã nói ở trên và tạo các bảng cho hai bản ghi của chúng ta.

Các bản ghi sẽ sử dụng. Ảnh: Tác giả 

Chúng ta đã xem cách tạo bảng ở các phần trước, nên hãy làm theo các bước đó để tạo hai bảng.

Điểm khác biệt là chúng ta sẽ đặt tên mới cho các bảng này để phân biệt với phần trước: 

• Đặt tên bảng đầu tiên là CustomerPP.
• Đặt tên bảng thứ hai là PurchasePP.

Dữ liệu Các bảng tạo từ bản ghi. Ảnh: Tác giả

Hai bảng đã được tạo và bước tiếp theo là thêm chúng vào mô hình dữ liệu. Để làm vậy, vào Power Pivot, và chọn Add to Data Model.

Add to Data Model dưới PowerPivot. Ảnh: Tác giả

Một cửa sổ Power Pivot for Excel sẽ mở ra, trông như sau.

Cửa sổ Power Pivot for Excel. Ảnh: Tác giả 

 

Lưu và đóng cửa sổ này. Lặp lại quy trình tương tự cho bảng CustomerPP để thêm vào mô hình dữ liệu.

Cửa sổ Power Pivot for Excel. Ảnh: Tác giả

Sau khi các bảng này đã được thêm vào mô hình dữ liệu, bạn có thể thiết lập mối quan hệ giữa chúng bằng Power Pivot. Để làm vậy, nhấp vào Diagram View.

Diagram View. Ảnh: Tác giả

Tiếp theo, vào thẻ Design và nhấp Create Relationship.

Thẻ Create a Relationship. Ảnh: Tác giả

Một cửa sổ mới sẽ mở. Điền chi tiết như dưới đây:

Tạo mối quan hệ giữa hai bảng. Ảnh: Tác giả

 Nhấp OK. Bạn sẽ thấy mối quan hệ đã được thiết lập.

 

Mối quan hệ đã được thiết lập. Ảnh: Tác giả

Từ đây, việc tạo PivotTable rất dễ như chúng ta đã làm ở phần trước. Kết quả cuối cùng sẽ như thế này.

Tóm tắt PivotTable. Ảnh: Tác giả

Làm theo các bước trên, bạn có thể dùng Power Pivot để tạo Data Model và thực hiện phân tích tóm tắt.

Cách tạo mô hình dữ liệu với PivotTable hoặc PivotChart

Sau khi đã tạo PivotTable, việc chèn PivotChart rất dễ. Với PivotTable (Sheet 1) được chọn, vào thẻ Insert.

Tóm tắt PivotTable. Ảnh: Tác giả

Nhấp PivotChart, chọn loại biểu đồ và nhấp OK.

Tùy chọn Insert Chart. Ảnh: Tác giả

Việc này sẽ tạo PivotChart như sau.

Trực quan hóa PivotChart. Ảnh: Tác giả

Bạn có thể tùy chỉnh PivotChart bằng cách:

• Điều chỉnh các thành phần biểu đồ như tiêu đề, chú giải và nhãn trục, và
• Định dạng biểu đồ để cải thiện giao diện và khả năng đọc.

Tuyệt, bạn đã dùng PivotTable để tạo trực quan hóa đầu tiên.

Những câu hỏi và vấn đề thường gặp về mô hình dữ liệu Excel

Dưới đây là một số câu hỏi và vấn đề thường gặp với mô hình dữ liệu Excel:

Tôi nên làm gì nếu sự cố mô hình dữ liệu khiến Microsoft Excel không mở được sổ làm việc?

Sự cố này có thể xảy ra do hỏng mô hình dữ liệu hoặc vấn đề tương thích. Dưới đây là các bước khắc phục:

1. Mở ở Chế độ An toàn (Safe Mode): Thử mở Excel ở Safe Mode bằng cách giữ phím Ctrl khi khởi động Excel. Thao tác này đôi khi có thể bỏ qua lỗi.
2. Kiểm tra cập nhật: Đảm bảo Excel đã được cập nhật bằng cách vào File > Account > Update Options.
3. Sửa chữa Office: Dùng công cụ sửa chữa Office. Vào Control Panel > Programs and Features, chọn bản cài Office của bạn và chọn Change > Repair.
4. Tắt Add-in: Vào File > Options > Add-ins và tắt tất cả add-in. Khởi động lại Excel và thử mở sổ làm việc.
5. Trích xuất dữ liệu: Nếu bạn không thể mở sổ làm việc, hãy thử dùng công cụ khôi phục dữ liệu hoặc liên hệ bộ phận hỗ trợ Microsoft để được trợ giúp nâng cao.

Làm thế nào để khắc phục lỗi trong mô hình dữ liệu?

Để khắc phục lỗi trong mô hình dữ liệu:

1. Kiểm tra kiểu dữ liệu: Đảm bảo kiểu dữ liệu trong các cột dùng cho mối quan hệ là nhất quán.
2. Xác thực mối quan hệ: Kiểm tra rằng tất cả mối quan hệ được định nghĩa đúng.
3. Rà soát phép tính: Kiểm tra cột tính toán và measure để phát hiện lỗi.
4. Làm mới dữ liệu: Đảm bảo tất cả kết nối dữ liệu được cập nhật.
5. Thông báo lỗi: Xem các thông báo lỗi trong cửa sổ Power Pivot để xác định vấn đề cụ thể.

Bằng cách làm theo các bước này, bạn có thể quản lý và khắc phục sự cố mô hình dữ liệu trong Excel một cách hiệu quả, đảm bảo phân tích dữ liệu trơn tru và hiệu quả. Microsoft Support cũng là nguồn hữu ích để xử lý sự cố. 

Tổng kết

Cảm ơn bạn đã hoàn thành hướng dẫn! Bạn đã học một khái niệm quan trọng trên hành trình làm chủ phân tích dữ liệu bằng Excel. Việc tạo mô hình dữ liệu sẽ nâng cao đáng kể năng lực phân tích dữ liệu của bạn và tổ chức. Nó cũng giúp bạn quản lý hiệu quả các bảng và bản ghi dữ liệu khác nhau. 

Để học thêm, hãy cân nhắc khám phá các nguồn sau:   

• Introduction to Excel: Hướng dẫn toàn diện giúp bạn bắt đầu với Excel, bao quát các chức năng và tính năng cơ bản.
• Data Analysis in Excel: Học các kỹ thuật và công cụ phân tích dữ liệu trong Excel để nâng cao kỹ năng phân tích.
• Excel Fundamentals: Khóa học bao quát các khái niệm và thao tác nền tảng trong Excel để xây dựng nền tảng vững chắc.
• Data Visualization in Excel: Khám phá cách tạo các trực quan hóa ấn tượng và tương tác bằng công cụ tích hợp của Excel.
• Introduction to Power Query in Excel: Hiểu cách dùng Power Query để nhập, làm sạch và biến đổi dữ liệu hiệu quả trong Excel.