[Excel Tutorial 4] Cách tạo Data Model trong Excel

Trong kỷ nguyên dữ liệu lớn, việc xử lý hàng triệu dòng dữ liệu từ nhiều nguồn khác nhau trên Excel thường là cơn ác mộng đối với dân văn phòng. Các hàm dò tìm truyền thống như VLOOKUP, INDEX hay MATCH liên tục làm đơ, treo máy. Để giải quyết triệt để vấn đề này, Microsoft đã tích hợp một công cụ cực kỳ mạnh mẽ mang tên Data Model. Vậy Data Model trong Excel là gì và làm thế nào để ứng dụng công cụ này vào việc chuẩn hóa báo cáo? Hãy cùng khám phá chi tiết trong bài viết dưới đây.

Data Model trong Excel là gì?

Data Model trong Excel là một tính năng mạnh mẽ cho phép bạn tích hợp, kết nối và xây dựng mối quan hệ giữa nhiều bảng dữ liệu khác nhau từ nhiều nguồn (Excel, SQL Server, Access, Web, …) thành một cơ sở dữ liệu quan hệ thống nhất nằm ngay bên trong một file Excel hiện hành.Thay vì gộp tất cả dữ liệu vào một bảng duy nhất bằng các hàm truy vấn (VLOOKUP, HLOOKUP, …), Data Model giữ các bảng độc lập nhưng liên kết chúng thông qua các trường thông tin chung (Key).

Lợi ích vượt trội của Data Model:

Vượt giới hạn số dòng: Excel thông thường chỉ giới hạn tối đa 1.048.576 dòng. Tuy nhiên, khi đưa dữ liệu vào Data Model, bạn có thể xử lý và phân tích các tệp dữ liệu lên đến hàng triệu, thậm chí hàng chục triệu dòng nhờ công nghệ nén xVelocity của Microsoft.
Tối ưu dung lượng file: Giảm thiểu tối đa việc lặp lại dữ liệu thừa, giúp file nhẹ hơn và vận hành mượt mà hơn.
Xây dựng PivotTable đa chiều: Bạn có thể kéo các trường từ nhiều bảng khác nhau vào cùng một Pivot Table để phân tích mà không cần viết bất kỳ một hàm liên kết nào.

Các thành phần trong Data Model

Để xây dựng và làm chủ một Data Model chuyên nghiệp, bạn cần hiểu rõ 3 thành phần cốt lõi cấu thành nên nó:

Tables (Các bảng dữ liệu):
- Bảng Fact: Chứa các giao dịch thực tế, số liệu đo lường (ví dụ: Bảng doanh thu, Bảng lịch sử bán hàng, …). Bảng này thường chứa nhiều dòng và các giá trị ở khóa ngoại có thể lặp lại nhiều lần.
- Bảng Dimension: Chứa thông tin chi tiết dùng để phân loại (ví dụ: Danh mục khách hàng, Danh mục sản phẩm, Danh mục khu vực, …). Mỗi đối tượng trong bảng này chỉ xuất hiện duy nhất một lần (chứa khóa chính – Primary Key).
Relationships (Các mối quan hệ): Là các đường liên kết được thiết lập giữa hai bảng thông qua cột chung (chứa dữ liệu tương đồng). Mối quan hệ phổ biến nhất trong Excel Data Model là mối quan hệ một – nhiều.
Measures & Calculated Columns (Các chỉ số đo lường): Được tạo ra bằng ngôn ngữ công thức DAX (Data Analysis Expressions) để tính toán các chỉ số phức tạp trực tiếp trên mô hình dữ liệu.

Các loại Data Model phổ biến

Sơ đồ hình sao (Star Schema)

Đây là mô hình phổ biến và được khuyến nghị nhất khi làm việc với Excel Data Model. Trong Star Schema, bảng Fact sẽ kết nối trực tiếp với các bảng Dimension xung quanh thông qua các mối quan hệ một – nhiều. Mô hình này cực kỳ trực quan, dễ quản lý và cho tốc độ truy vấn PivotTable nhanh nhất.

Sơ đồ bông tuyết (Snowflake Schema)

Đây là một dạng biến thể của sơ đồ hình sao, nơi các bảng Dimension tiếp tục được chia nhỏ và kết nối với các bảng Dimension phụ khác (ví dụ: Bảng Doanh thu nối với bảng Sản phẩm, bảng Sản phẩm lại tiếp tục nối với bảng Nhóm ngành hàng). Mô hình này giúp giảm thiểu tối đa việc trùng lặp dữ liệu nhưng sẽ làm cấu trúc mô hình trở nên phức tạp hơn và có thể ảnh hưởng nhẹ đến hiệu năng xử lý.

Cách tạo Data Model trong Excel

Để xây dựng một Data Model trong Excel hoàn chỉnh, bạn hãy thực hiện theo quy trình chuẩn hóa gồm 4 giai đoạn dưới đây:

Bước 1: Import dữ liệu vào Excel

Mở một file Excel mới. Trên thanh Ribbon, truy cập vào tab Data → chọn Get Data.

Chọn nguồn dữ liệu phù hợp với nhu cầu của bạn (ví dụ: Chọn From File → From Excel Workbook để lấy dữ liệu từ một file Excel khác).
Trỏ đường dẫn về địa chỉ thư mục chứa file nguồn của bạn và nhấn Import.

Bước 2: Chuẩn hóa dữ liệu bằng Power Query (Transform Data)

Đây là bước bắt buộc để đảm bảo tính toàn vẹn dữ liệu (Data Integrity). Bạn không nên nạp trực tiếp dữ liệu thô vào mô hình mà cần qua bước xử lý và làm sạch.

Tại cửa sổ Navigator hiện ra, tick chọn các bảng dữ liệu cần sử dụng → chọn Transform Data.

Lúc này, Excel sẽ load toàn bộ dữ liệu từ file đã import vào giao diện Power Query Editor.
Kiểm tra và làm sạch:
- Xem qua các bảng, kiểm tra kỹ kiểu dữ liệu của từng cột và xử lý các dòng bị lỗi.
- Lưu ý : Các cột dùng để kết nối giữa các bảng dữ liệu (ví dụ: cột Mã khách hàng ở bảng Fact và bảng Dimension) bắt buộc phải có cùng kiểu dữ liệu với nhau. Nếu lệch kiểu dữ liệu, hệ thống sẽ báo lỗi và không thể tiến hành modeling.

Bước 3: Nạp dữ liệu vào Data Model

Tại tab Home của giao diện Power Query, nhấp vào mũi tên bên dưới nút Close & Load → chọn Close & Load To…
Tại hộp thoại Import Data xuất hiện, thiết lập cấu hình như sau:
- Tick chọn vào mục: Add this data to the Data Model.
- Phần chọn cách hiển thị: Chọn Only create connection (để Excel chỉ tạo liên kết ngầm, tránh ghi đè hàng triệu dòng dữ liệu trực tiếp ra trang tính gây nặng file).
- Nhấn OK.

Bước 4: Thiết lập mối quan hệ trong Power Pivot

Trên thanh công cụ, chuyển sang tab Power Pivot → chọn chức năng Manage. (Nếu chưa thấy tab này, bạn vào File → Options → Add-ins → chọn COM Add-ins ở mục Manage → Go → Tick chọn Microsoft Power Pivot for Excel).
Lúc này, cửa sổ quản lý cơ sở dữ liệu Power Pivot sẽ hiện ra, hiển thị toàn bộ các bảng dữ liệu đã nạp.
Để dễ dàng thiết lập liên kết, tại tab Home, hãy chuyển sang chế độ hiển thị Diagram View.

Bạn có thể tự do kéo, sắp xếp và di chuyển các bảng cho dễ nhìn, dễ quản lý nhằm quan sát cấu trúc mô hình một cách rõ ràng nhất.
Nối các mối quan hệ: Click giữ chuột vào trường khóa (ví dụ: ID Sản phẩm) ở bảng Dimension và kéo thả trực tiếp vào trường tương ứng ở bảng Fact.

Các kỹ thuật xử lý lỗi và quản lý mối quan hệ

Trong quá trình kéo thả thiết lập mô hình, người làm dữ liệu rất dễ gặp phải các vấn đề phát sinh. Dưới đây là các kỹ thuật xử lý từ các chuyên gia:

Sửa lỗi kết nối hoặc kéo nhầm trường: Nếu kéo nhầm hoặc kết nối bị lỗi, bạn chỉ cần double-click trực tiếp vào đường mối quan hệ vừa tạo. Bảng Edit Relationship sẽ hiện ra để bạn chỉnh sửa chọn lại cột khóa chính xác.
Quản lý tập trung qua Design: Thay vì thao tác trên sơ đồ, bạn có thể chuyển qua tab Design → chọn chức năng Manage Relationships. Một bảng quản lý danh sách toàn bộ các mối quan hệ hiện có sẽ xuất hiện. Tại đây, bạn chọn mối quan hệ cần chỉnh sửa rồi nhấn Edit, hoặc nhấn Delete để xóa bỏ.

Tạo quan hệ không cần kéo thả: Cũng tại tab Design, bạn có thể click vào chức năng Create Relationship để thiết lập mối quan hệ mới bằng cách chọn tên bảng và tên cột tương ứng trong bảng menu chọn lựa.
Xử lý mối quan hệ Inactive (Đường nét đứt):
- Nguyên tắc của Data Model là không cho phép tồn tại 2 mối quan hệ Active (đang hoạt động) cùng lúc giữa hai bảng.
- Ví dụ thực tế: Bạn có bảng Doanh thu (chứa hai cột Ngày đặt hàng và Ngày giao hàng) kết nối với bảng Danh mục ngày (Calendar Table). Bạn chỉ có thể thiết lập 1 mối quan hệ kích hoạt (Active – nét liền) cho cột Ngày đặt hàng. Liên kết còn lại nối với cột Ngày giao hàng sẽ tự động chuyển sang chế độ Inactive (đường nét đứt).
- Bạn có thể tạo được nhiều mối quan hệ Inactive. Khi cần tính toán theo các mối quan hệ inactive này trong PivotTable, bạn cần sử dụng hàm DAX kết hợp với hàm USERELATIONSHIP() để kích hoạt tạm thời liên kết nét đứt đó.

[FAQ] Một số câu hỏi thường gặp liên quan đến Data Model trong Excel

Data Model trong Excel có giới hạn dung lượng hay số dòng không?

Về lý thuyết, Data Model không giới hạn số dòng dữ liệu nạp vào như các bảng Excel truyền thống (1 triệu dòng). Giới hạn duy nhất của Data Model phụ thuộc hoàn toàn vào cấu hình phần cứng máy tính của bạn (đặc biệt là dung lượng RAM) và phiên bản Excel bạn đang sử dụng (phiên bản Excel 64-bit sẽ cho phép xử lý lượng dữ liệu lớn hơn nhiều so với bản 32-bit).

Tại sao tôi không thể tạo mối quan hệ giữa hai bảng?

Lỗi này thường xuất phát từ hai nguyên nhân phổ biến:

Lệch kiểu dữ liệu: Cột liên kết ở bảng A định dạng là Text, nhưng cột liên kết ở bảng B lại định dạng là Number. Bạn cần vào Power Query để đồng bộ lại kiểu dữ liệu.
Không có cột chứa giá trị duy nhất (Unique Values): Để tạo mối quan hệ một – nhiều, bắt buộc một trong hai bảng (bảng Dimension) phải có cột khóa chứa các giá trị duy nhất, không trùng lặp. Nếu cả hai bảng đều chứa các giá trị trùng lặp ở cột liên kết, Excel sẽ báo lỗi trùng khóa và không cho phép kết nối.

Có thể chia sẻ file Excel chứa Data Model cho người khác xem được không?

Có. Khi bạn gửi file Excel, toàn bộ dữ liệu được nén trong Data Model và cấu trúc mối quan hệ sẽ đi kèm theo file. Người nhận chỉ cần mở file là có thể xem và tương tác với các báo cáo PivotTable đa chiều bình thường, ngay cả khi họ không có quyền truy cập vào các file nguồn ban đầu (trừ trường hợp họ muốn nhấn “Refresh” để cập nhật dữ liệu mới từ nguồn).

Kết luận

Sử dụng Data Model trong Excel là một bước nhảy vọt giúp bạn nâng cấp tư duy làm việc với dữ liệu từ thủ công lên chuyên nghiệp. Bằng cách kết nối trực tiếp các bảng thông qua các mối quan hệ logic, bạn không chỉ phá vỡ giới hạn 1 triệu dòng truyền thống mà còn giải phóng máy tính khỏi sự trì trệ của các hàm tính toán nặng nề.

Nếu bạn muốn học cách xử lý dữ liệu, xây dựng dashboard và tối ưu báo cáo thực tế bằng Excel, hãy tham khảo Khóa học phân tích dữ liệu bằng Excel dành cho người mới bắt đầu đến nâng cao tại Starttrain nhé!

747