Hướng dẫn tạo Data model và DAX measure trong Excel (có Dataset thực hành)

marketing foundation

TM Data School – Với sự tích hợp của Power Query, Power Pivot và khả năng viết các biểu thức DAX (Data Analysis Expressions), Excel không còn chỉ là một công cụ giúp quản lý hiệu suất (Productivity tools) mà đã trở thành một công cụ mạnh mẽ không kém gì Power BI trong việc xử lý và phân tích dữ liệu. Khai thác những tính năng này trong Excel thế nào? 

Trong bài viết này, TM Data School sẽ hướng dẫn bạn cách tạo Data Model và viết DAX Measure trong Excel, giúp bạn khai thác tối đa tiềm năng của Excel trong công việc phân tích dữ liệu nhé!

1. Hướng dẫn tạo Data Model trong Excel

1.1. Data Model là gì? 

Data Model là một cấu trúc dữ liệu cho phép bạn tích hợp và quản lý nhiều bảng dữ liệu khác nhau trong Excel. Nó cho phép tạo mối quan hệ giữa các bảng, giúp bạn dễ dàng thực hiện các phân tích phức tạp và truy xuất thông tin một cách hiệu quả.

Các thành phần chính trong Data Model bao gồm:

  • Entities (Thực thể): Là các đối tượng mà chúng ta muốn biểu diễn trong Data Model và thường được đại diện bằng bảng. Ví dụ: khách hàng, đơn hàng, sản phẩm, hoặc danh mục.
  • Attributes (Thuộc tính): Xuất hiện dưới dạng các cột trong bảng cụ thể. Ví dụ: mã sản phẩm và tên sản phẩm (đối với thực thể Sản phẩm), mã danh mục và tên danh mục (đối với thực thể Danh mục).
  • Records (Bản ghi): Được hiển thị dưới dạng các hàng trong mỗi bảng. Một ví dụ về bản ghi là dữ liệu của một sản phẩm cụ thể (mã sản phẩm, tên sản phẩm, danh mục) được lưu trữ trong bảng Sản phẩm.
  • Relationships (Mối quan hệ): Xác định sự liên kết giữa các thực thể. Mối quan hệ này có thể là One-One, One-many, hoặc many-many. 

Ví dụ: Một sản phẩm có thể nằm trong nhiều đơn hàng (one – many), nhưng một đơn hàng chỉ có một người mua duy nhất (one – one)

  • Primary Key (Khóa chính): Là một định danh duy nhất cho mỗi bản ghi trong bảng hiện tại. Nó là một cột hoặc một nhóm cột có giá trị duy nhất cho mỗi hàng. Ví dụ: khóa chính của bảng Sản phẩm là product_id và của bảng Danh mục là category_id.
  • Foreign Key (Khóa ngoại): Là một cột hoặc một nhóm cột trỏ đến khóa chính trong bảng khác — chẳng hạn product_id và category_id. Nó tạo ra mối liên kết giữa hai bảng (Sản phẩm và Danh mục) cho phép thông tin từ bảng này (Sản phẩm) được liên kết và liên hệ với thông tin từ bảng khác (Danh mục).

Để hiểu rõ hơn về Data Model và cách thức hoạt động, bạn có thể tham khảo bài viết: Data modeling là gì? Cách xây dựng Data model trong Power BI

Demo Data model

Demo Data model – Slide khóa học Data System with SQL 

1.2. Các bước tạo Data Model trong Excel

Bước 1: Chuẩn bị dữ liệu 

Để giúp bạn dễ theo dõi và thực hành, TM Data School sẽ cung cấp cho bạn 1 bộ Dataset về một cửa hàng bán xe đạp bao gồm 8 bảng: 

  • Sheet 1 – Bảng Brand: Chứa thông tin về các hãng sản xuất
  • Sheet 2 – Bảng Stock: Chứa thông tin về số lượng sản phẩm trong kho
  • Sheet 3 – Bảng Staff: Chứa thông tin về nhân viên của cửa hàng
  • Sheet 4 – Bảng Product: Chứa thông tin về sản phẩm
  • Sheet 5 – Bảng Order_Details: Chứa thông tin về các đơn hàng bán ra
  • Sheet 6 – Bảng Customers: Chứa thông tin về khách hàng
  • Sheet 7 – Bảng Category: Chứa thông tin về các danh mục sản phẩm
  • Sheet 8 – Bảng Store: Chứa thông tin về từng cửa hàng

Tải Data set thực hành tại đây

Trước tiên, chúng ta sẽ cần tạo thêm một bảng Calendar chứa thông tin ngày tháng như hình dưới đây:  

 tạo thêm một bảng Calendar

Việc tạo bảng Calendar riêng trong Data model sẽ giúp: 

  • Phân tích dữ liệu theo ngày, tháng, quý, năm hay tính toán các chỉ số như YTD, MTD, QTD, YOY với DAX Measure. 
  • Giúp cung cấp các thuộc tính thời gian bổ sung cho dữ liệu như ngày trong tuần, tên tháng, quý, năm tài chính nhờ vào việc thiết lập mối quan hệ giữa bảng Calendar và các bảng dữ liệu khác dựa trên cột thời gian. 
  • Đảm bảo tất cả các phép toán và phân tích liên quan đến thời gian đều nhất quán và chính xác.

Để tạo bảng, bạn hãy làm theo các bước hướng dẫn sau:

Mở file Dataset đã tải về, chọn tab Data. Tiếp tục chọn Get Data > From Other Sources > Blank Query.

Blank Query

Cửa sổ Power Query Editor sẽ hiện ra, bạn tiếp tục chọn Advanced Editor.

Advanced Editor

Tại đây, chúng ta sẽ sử dụng một đoạn code (Power Query M formula) để tự động tạo bảng Calendar thay vì nhập thủ công dữ liệu ngày tháng. 

Power Query M formula ngôn ngữ lập trình được sử dụng trong Power Query của Excel và Power BI để truy xuất, chuyển đổi và tải dữ liệu (ETL). M code cho phép viết các truy vấn phức tạp để xử lý dữ liệu một cách tự động và linh hoạt. Bạn có thể tìm hiểu thêm về Power Query M formula language tại đây

Vì bài viết không đi sâu vào Power Query M formula trong Excel nên TM Data School sẽ cung cấp sẵn cho bạn đoạn code để tạo bảng tại đây. Paste đoạn code để tạo bảng Calendar vào Advanced Editor như hình minh họa dưới đây, sau đó chọn Done.

Power Query M formula

Thay thế StartDate và EndDate bằng ngày bắt đầu và kết thúc mà bạn mong muốn. Ở đây chúng ta sẽ đặt StartDate: 01/01/2016, EndDate: 12/31/2018, và FYStartMonth: 1. Tiếp tục chọn Invoke

Thay thế StartDate và EndDate

Đóng và tải bảng Calendar vào Excel bằng cách chọn Close & Load.

Close & Load

Bảng Calendar của bạn sẽ xuất hiện trong Excel và sẵn sàng sử dụng để xây dựng Data Model.

Bảng Calendar của bạn sẽ xuất hiện trong Excel

Đọc thêm: 5 hàm phổ biến nhất trong Excel cho phân tích dữ liệu

Bước 2: Tạo Data Model

2.1 . Bật tính năng Pivot table trong Excel 

Trước khi bắt đầu, bạn cần đảm bảo rằng tính năng Power Pivot đã được bật trong Excel. 

Đây là công cụ cho phép bạn xử lý và phân tích dữ liệu từ nhiều bảng khác nhau một cách hiệu quả. Cụ thể hơn Pivot Table cho phép: 

  • Quản lý dữ liệu lớn: Power Pivot cho phép bạn làm việc với lượng dữ liệu lớn hơn so với khả năng của bảng tính Excel thông thường.
  • Tạo mối quan hệ giữa các bảng: Bạn có thể dễ dàng tạo và quản lý các mối quan hệ giữa các bảng dữ liệu khác nhau, giúp phân tích dữ liệu một cách tổng thể và chính xác.
  • Sử dụng các công cụ phân tích mạnh mẽ: Power Pivot cung cấp các công cụ phân tích nâng cao, bao gồm khả năng viết các biểu thức DAX (Data Analysis Expressions).

Đọc thêm: Hướng dẫn cách tạo Dashboard với Excel Pivot Table

Để bật tính năng Power Pivot, truy cập vào tab File > Options > Add-ins. Ở phía dưới, trong mục Manage, chọn COM Add-ins và nhấn Go

Bật tính năng Pivot table trong Excel

Đánh dấu chọn Microsoft Power Pivot for Excel và nhấn OK.

Bật tính năng Pivot table trong Excel
2.2. Thêm dữ liệu vào Data Model 

Trước khi thêm dữ liệu vào Data Model hãy đảm bảo rằng các bảng dữ liệu của bạn đã được định dạng đúng trong Excel. Mỗi bảng nên có tiêu đề cột rõ ràng và không có các hàng trống.

Đọc thêm: Data Cleaning là gì? Hướng dẫn các bước làm sạch dữ liệu

Để thêm bảng vào Data model, chọn bảng muốn thêm, sau đó chọn Power Pivot > Add to Data Model

Thêm dữ liệu vào Data Model

Có thể thấy, bảng mới đã được thêm vào Data Model, tiếp tục lặp lại bước này cho tất cả các bảng dữ liệu muốn thêm vào Data Model.

Thêm dữ liệu vào Data Model
2.3. Thiết lập quan hệ giữa các bảng dữ liệu trong Data Model

Sau khi đã thêm các bảng vào Data Model, trong cửa sổ Power Pivot, chọn Diagram View để xem tất cả các bảng dữ liệu dưới dạng sơ đồ.

Thiết lập quan hệ giữa các bảng dữ liệu trong Data Model

Để tạo mối quan hệ giữa các bảng, kéo và thả các cột từ bảng này sang bảng khác. 

Ví dụ, kéo cột product_id từ bảng Product sang cột product_id trong bảng Order_Details để thiết lập mối quan hệ giữa 2 bảng này. 

Thiết lập quan hệ giữa các bảng dữ liệu trong Data Model

Để xác định được mối quan hệ giữa các bảng bạn sẽ cần có kiến thức về Primary Key & Foreign key, mối quan hệ giữa các bảng. Bạn có thể tìm hiểu thêm kỹ hơn về những kiến thức này trong khóa Data Visualization & Analytics with Excel

Tạo Data Model trên Excel

Tạo Data Model trên Excel – Slide khóa học Data Visualization & Analytics with Excel

Sau khi liên kết các bảng ta sẽ có được Data Model như hình dưới đây. 

Tạo Data Model trên Excel

Đọc thêm: Cách xây dựng Data model trong Power BI

2.4. Test Data Model

Sau khi tạo Data Model, chúng ta sẽ cần test xem liệu Data Model có đang hoạt động hay không. Bạn có thể test bằng cách sử dụng PivotTable để tính toán và kiểm tra kết quả phân tích dữ liệu với PivotTable có khớp với dữ liệu gốc của bạn hay không. 

Để tạo PivotTable, ở trong cửa sổ Power Pivot for Excel, chọn PivotTable

Test Data Model

Chọn New Worksheet > OK

Test Data Model

Trong PivotTable Field List, bạn sẽ thấy tất cả các bảng và các cột từ Data Model của mình. 

Test Data Model

Kéo các trường (fields) từ các bảng vào các khu vực Rows, Columns, Values, và Filters để thiết lập PivotTable theo cách bạn mong muốn.

Ví dụ: Muốn check xem mỗi năm các cửa hàng bán được bao nhiêu sản phẩm theo từng category:

  • Kéo Year từ bảng Calendar vào khu vực Rows.
  • Kéo Category Name từ bảng Category vào khu vực Columns.
  • Kéo Quantity từ bảng Order Details vào khu vực Values để tính tổng số lượng sản phẩm đã bán.
  • Kéo thêm Store Name từ bảng Store vào khu vực Rows để chia nhỏ theo từng cửa hàng

Ta sẽ được kết quả như hình dưới đây: 

Test Data Model

Ta sẽ lấy một giá trị và so sánh với giá trị ở bảng dữ liệu gốc xem có đúng kết quả hay không. 

Ví dụ: Trong năm 2016, có 248 sản phẩm thuộc Category Children Bicycles bán được ở cửa hàng Baldwin Bikes. Filter và tính toán dữ liệu ở bảng gốc, chúng ta cũng ra được con số 248 sản phẩm. Có thể thấy Data Model vừa liên kết đang hoạt động và trả về kết quả đúng. 

Bạn có thể test thêm với một vài ví dụ khác để đảm bảo rằng các giá trị tổng hợp trong PivotTable khớp với dữ liệu gốc của bạn.

Test Data Model

Đọc thêm: Hướng dẫn sử dụng Pivot Table trong Google Sheet để phân tích dữ liệu nhanh chóng, hiệu quả hơn

2. Tạo hàm DAX measures trong Excel

2.1. DAX Measure là gì

DAX Measure là các công thức được sử dụng trong Power Pivot, Power BI, và các công cụ phân tích dữ liệu khác của Microsoft để tính toán và phân tích dữ liệu. Cụ thể, DAX Measure thường được sử dụng để thực hiện các phép toán tổng hợp như tổng, trung bình, đếm, min, max và các phép toán phức tạp khác.

Ví dụ: Trong Dataset mẫu chúng ta có một bảng Order_Details chứa thông tin về số lượng và giá của các sản phẩm trong các đơn hàng. 

DAX Measure là gì

Chúng ta có thể sử dụng DAX Measure dưới đây để tính tổng doanh thu từ tất cả các đơn hàng.

TotalSales = SUMX (Order_Details, Order_Details[quantity] * RELATED (Product [list_price]))

Trong phần tiếp theo TM sẽ hướng dẫn bạn chi tiết cách tạo DAX Measure. 

Đọc thêm: DAX là gì? Tổng quan và cách tạo hàm DAX trong Power BI

2.2. Cách tạo DAX Measure trong Excel

Chọn bảng dữ liệu mà bạn muốn tạo Measure. Ở đây, chúng ta sẽ tạo DAX Measure chọn bảng Order_Details. Chọn Power Pivot > Measures > New Measure.

Cách tạo DAX Measure trong Excel

Trong hộp thoại Measure

  • Trong ô Measure name, nhập tên cho Measure là TotalSales.
  • Trong ô Formula, nhập công thức DAX để tính tổng doanh thu, bạn có thể sử dụng hàm SUMX như sau:

= SUMX (Order_Details, Order_Details[quantity] * RELATED (Product [list_price]))

  • Chọn định dạng cho kết quả (ví dụ: Currency cho tiền tệ).
  • Nhấn OK để tạo Measure.
Cách tạo DAX Measure trong Excel

Vậy là chúng ta đã thành công tạo một DAX Measure để tính tổng doanh thu. 

Để kiểm tra tính chính xác của DAX Measure TotalSales, chúng ta sẽ tạo thêm một cột Sales trong bảng Order_Details mà không sử dụng DAX, sau đó so sánh kết quả giữa hai phương pháp.

Trong cửa sổ Power Pivot for Excel, chọn Add Column để thêm cột mới. Đổi tên cho cột thành Sales và nhập công thức để tính giá trị:  

=Order_Details[quantity]*Order_Details[list_price] 

Trong đó: 

  • Order_Details[quantity] là cột quantity trong bảng Order_Details
  • Order_Details[list_price] là cột list_price trong bảng Order_Details 

Cột Sales sẽ được tính bằng cách lấy số lượng sản phẩm x đơn giá từng sản phẩm. Và tổng doanh thu sẽ bằng tổng các giá trị của cột Sales. 

Cách tạo DAX Measure trong Excel

Giờ hãy thử tạo PivotTable với 2 kết quả được tính bằng 2 cách để kiểm tra kết quả. 

Có thể thấy rằng kết quả vẫn giống hệt nhau bất kể phương pháp nào mà chúng ta sử dụng để tính doanh thu.

Cách tạo DAX Measure trong Excel

Lưu ý: Excel không sử dụng được hết tất cả các hàm DAX, nhưng vẫn hỗ trợ phần lớn các hàm DAX cơ bản và nâng cao thường được sử dụng trong Power BI như:

  • Aggregation functions: SUM, AVERAGE, COUNT, MIN, MAX
  • Logical functions: IF, AND, OR, NOT
  • Date and Time functions: YEAR, MONTH, DAY, DATE, TODAY
  • Mathematical functions: ABS, CEILING, FLOOR, ROUND
  • Text functions: CONCATENATE, LEFT, RIGHT, MID, LEN
  • Statistical functions: STDEV.P, VAR.P
  • Information functions: ISBLANK, ISNUMBER, ISERROR

Bạn có thể tham khảo thêm về DAX trong khóa học Data Analysis with Power BI | Tableau của TM Data School. 

Cách tạo DAX Measure trong Excel

Hướng dẫn tạo column mới với DAX – Slide khóa học Data Analysis with Power BI | Tableau

3. Tạm kết

Excel không còn chỉ là một công cụ giúp quản lý hiệu suất (Productivity tools) mà đã trở thành một công cụ mạnh mẽ trong việc xử lý và phân tích dữ liệu. Sử dụng được Data Model và DAX trong Excel giúp bạn khai thác tối đa tiềm năng của dữ liệu, cho phép bạn thực hiện các phép tính phức tạp, tạo ra các báo cáo động và trực quan hóa dữ liệu một cách linh hoạt.

Tìm hiểu thêm về cách trực quan hóa dữ liệu và xây dựng báo cáo tự động trên Excel với khóa học Data Visualization & Analytics with Excel

khóa học Data Visualization & Analytics with Excel

Nếu bạn muốn tìm hiểu sâu hơn về DAX và các ứng dụng của nó trong Power BI, cũng như nâng cao tư duy “đọc số” để đưa ra quyết định, hãy tham khảo ngay khóa học Data Analysis with Power BI | Tableau – Phân tích dữ liệu cho quyết định chiến lược của TM Data School!

Khóa học Data Analysis with Power BI | Tableau - Phân tích dữ liệu cho quyết định chiến lược
Tagged: