[DAX Tutorial] Cách sử dụng hàm Aggregation trong Excel

Trong kỷ nguyên phân tích dữ liệu lớn hiện nay, việc xử lý hàng triệu dòng dữ liệu trên Excel bằng các công thức thông thường đã không còn tối ưu. Đó là lý do Power Pivot và ngôn ngữ DAX (Data Analysis Expressions) ra đời. Một trong những nhóm hàm nền tảng, mạnh mẽ và được sử dụng nhiều nhất chính là hàm Aggregation trong Excel. Bài viết này sẽ giúp bạn làm chủ nhóm hàm Aggregation, hiểu rõ cú pháp và từng bước ứng dụng chúng trực quan qua các dự án phân tích thực tế.

Hàm Aggregation trong Excel là gì?

Hàm Aggregation trong Excel (đặc biệt là trong môi trường Power Pivot và DAX) là nhóm hàm dùng để tính toán trên một cột hoặc một bảng dữ liệu nhằm trả về một giá trị đơn lẻ (như tổng, trung bình, giá trị lớn nhất, nhỏ nhất hoặc số lượng phần tử).

Khác với các hàm Excel truyền thống hoạt động trên các ô đơn lẻ, các hàm DAX Aggregation hoạt động trực tiếp trên toàn bộ cấu trúc cột của mô hình dữ liệu. Dưới đây là cú pháp và cách sử dụng chi tiết của các hàm Aggregation phổ biến nhất: COUNT, DISTINCTCOUNT, SUM, AVERAGE, MAX, và MIN.

Đây cũng là điểm khác biệt quan trọng giữa Excel truyền thống và các công cụ BI hiện đại. Nếu muốn hiểu sâu hơn về cách xây dựng mô hình dữ liệu và tư duy phân tích báo cáo, bạn có thể tìm hiểu thêm về các khóa học Excel nâng cao và phân tích dữ liệu tại Starttrain.

Hàm SUM

Hàm SUM dùng để tính tổng tất cả các giá trị số trong một cột được chỉ định.

Cú pháp: SUM(<cột>)
Tham số:
- cột: Tên của cột chứa các giá trị số mà bạn muốn tính tổng. Tên cột phải được đặt trong dấu ngoặc vuông và đi kèm tên bảng, ví dụ: TenBang[TenCot].
Lưu ý: Hàm SUM chỉ hoạt động trên các cột chứa dữ liệu số. Nếu cột chứa các giá trị trống (blank) hoặc ký tự không phải số, hàm sẽ tự động bỏ qua các dòng đó.

Hàm AVERAGE

Hàm AVERAGE tính trung bình cộng của tất cả các giá trị số trong một cột dữ liệu.

Cú pháp: AVERAGE(<cột>)
Tham số:
- cột: Cột chứa các giá trị số cần tính trung bình.
Lưu ý: Các ô trống (blank) hoặc giá trị logic (TRUE/FALSE) trong cột sẽ không được tính vào cả tử số (tổng) lẫn mẫu số (số lượng dòng) của phép chia trung bình. Tuy nhiên, các dòng có giá trị bằng 0 vẫn được tính bình thường.

Hàm COUNT

Hàm COUNT được dùng để đếm số lượng ô có chứa giá trị số trong một cột cụ thể.

Cú pháp: COUNT(<cột>)
Tham số:
- cột: Cột chứa dữ liệu cần đếm.
Lưu ý: Hàm COUNT trong DAX chỉ đếm các ô chứa số, ngày tháng hoặc chuỗi văn bản đại diện cho số. Nếu ô trống hoặc chứa giá trị lỗi, hàm sẽ không đếm dòng đó.

Hàm DISTINCTCOUNT

Hàm DISTINCTCOUNT dùng để đếm số lượng giá trị duy nhất (không trùng lặp) trong một cột dữ liệu. Đây là hàm cực kỳ quan trọng trong phân tích báo cáo để tính số lượng khách hàng thực tế, số đơn hàng độc bản, v.v.

Cú pháp: DISTINCTCOUNT(<cột>)
Tham số:
- cột: Cột chứa các giá trị cần đếm phần tử duy nhất.
Lưu ý: Khác với hàm DISTINCT trong DAX (trả về một bảng), DISTINCTCOUNT trả về một số nguyên cụ thể. Hàm này tính cả giá trị trống (blank) như một giá trị duy nhất nếu trong cột đó xuất hiện ô trống.

Hàm MAX

Hàm MAX giúp tìm ra giá trị lớn nhất trong một cột dữ liệu hoặc so sánh giữa hai biểu thức scalar.

Cú pháp: MAX(<cột>) hoặc MAX(<biểu_thức_1>, <biểu_thức_2>)
Tham số:
- cột: Cột chứa dữ liệu cần tìm giá trị lớn nhất.
- biểu_thức_1, biểu_thức_2: Hai biểu thức tính toán trả về giá trị đơn lẻ để so sánh trực tiếp.
Lưu ý: Hàm hoạt động trên cả dữ liệu số, ngày tháng lẫn văn bản (sắp xếp theo bảng chữ cái). Ô trống sẽ bị bỏ qua.

Hàm MIN

Ngược lại với MAX, hàm MIN giúp xác định giá trị nhỏ nhất trong một cột hoặc giữa hai biểu thức đơn lẻ.

Cú pháp: MIN(<cột>) hoặc MIN(<biểu_thức_1>, <biểu_thức_2>)
Tham số:
- cột: Cột chứa dữ liệu cần tìm giá trị nhỏ nhất.
- biểu_thức_1, biểu_thức_2: Hai biểu thức so sánh trực tiếp.
Lưu ý: Tương tự như MAX, hàm MIN bỏ qua các ô trống và hoạt động được trên nhiều kiểu dữ liệu khác nhau.

Tham khảo ngay: 20+ hàm Excel nâng cao giúp giảm 50% thời gian làm báo cáo

Các bước sử dụng hàm Aggregation trong Excel

Để giúp bạn hình dung rõ nét cách vận hành của các hàm Aggregation trong thực tế doanh nghiệp, chúng ta sẽ cùng đi qua một dự án phân tích bộ dữ liệu bán hàng.

Bước 1: Chuẩn bị Data Model và xây dựng các mối quan hệ (Relationships)

Điều kiện tiên quyết bắt buộc khi viết mã DAX trong Excel là bạn phải đưa tất cả các bảng dữ liệu liên quan vào Data Model và thiết lập liên kết giữa chúng. Nếu không tạo quan hệ, các hàm Aggregation trong Excel sẽ không thể tính toán chính xác khi phân tích chéo giữa các bảng.Dưới đây là sơ đồ mối quan hệ (Diagram View) trong Power Pivot của dự án bán hàng này:

Nhìn vào sơ đồ, chúng ta có các bảng sau:

Bảng Dimension (Bảng danh mục): Calendar (Lịch), Product (Sản phẩm), Promotion (Khuyến mãi), Returns (Hàng trả lại), Fulfillment (Vận chuyển).
Bảng Fact (Bảng chứa số liệu giao dịch): OrderHeader (Thông tin chung đơn hàng), OrderLine (Chi tiết từng dòng sản phẩm trong đơn hàng).
Các bảng được liên kết với nhau bằng các mối quan hệ một – nhiều (1 – *). Ví dụ: Product[ProductID] liên kết với OrderLine[ProductID], Calendar[DateKey] liên kết với OrderHeader[OrderDateTime], …

Bước 2: Tạo Pivot Table để hiển thị kết quả

Sau khi đã hoàn thiện mô hình dữ liệu, chúng ta tiến hành tạo một Pivot Table trống từ Data Model để làm nơi chứa và hiển thị các chỉ số tính toán (Measure) mà chúng ta sắp viết.

Bước 3: Đếm tổng số khách hàng duy nhất (Total Customer) bằng DISTINCTCOUNT

Yêu cầu thực tế: Chúng ta muốn biết có bao nhiêu khách hàng thực sự đã mua hàng.

Trong bảng OrderHeader, một khách hàng (CustomerID) có thể mua hàng nhiều lần và phát sinh nhiều hóa đơn khác nhau. Nếu dùng hàm COUNT thông thường, chúng ta sẽ đếm trùng lặp và ra con số ảo. Vì vậy, ta bắt buộc phải dùng DISTINCTCOUNT để chỉ giữ lại các mã khách hàng duy nhất.
Cách thực hiện:
- Trên thanh công cụ, vào tab Power Pivot -> chọn Measures -> nhấn New Measure.
- Tại hộp thoại hiện ra, chọn bảng chứa Measure là Calendar (hoặc bảng bất kỳ phù hợp).
- Đặt tên Measure là Total Customer.
- Nhập công thức DAX vào ô Formula: =DISTINCTCOUNT(OrderHeader[CustomerID])
- Thiết lập định dạng bên dưới: Chọn Category là Number, Format là Whole Number và tích chọn Use 1000 separator (,) để dễ đọc dữ liệu.

Nhấn OK, chúng ta đã có chỉ số khách hàng duy nhất đầu tiên.

Bước 4: Đếm tổng số đơn hàng đã bán (Total Order) bằng DISTINCTCOUNT

Yêu cầu thực tế: Đếm tổng số lượng hóa đơn (đơn hàng) đã được xuất ra trong hệ thống.

Tương tự như trên, một đơn hàng có thể chứa nhiều dòng sản phẩm khác nhau (nằm trong bảng OrderLine hoặc lặp lại trong các bảng liên kết). Để đếm đúng số lượng đơn hàng thực tế phát sinh, ta sẽ đếm số lượng mã đơn hàng (OrderID) không trùng lặp từ bảng OrderHeader.
Cách thực hiện:
- Tạo một Measure mới đặt tên là Total Order.
- Viết công thức: =DISTINCTCOUNT(OrderHeader[OrderID])
- Thiết lập định dạng hiển thị là số nguyên (Whole Number) giống bước trên.

Bước 5: Tính tổng số lượng sản phẩm bán ra (Total Qty) bằng hàm SUM

Yêu cầu thực tế: Tính toán tổng số lượng sản phẩm vật lý đã được bán đi để phục vụ việc quản lý kho bãi.

Số lượng sản phẩm nằm ở từng dòng chi tiết của đơn hàng thuộc bảng OrderLine (cột Qty). Ta chỉ cần cộng dồn toàn bộ cột này lại là ra tổng sản lượng tiêu thụ.
Cách thực hiện:
- Tạo Measure mới tên là Total Qty.
- Viết công thức: =SUM(OrderLine[Qty])
- Định dạng hiển thị kiểu số nguyên có dấu phẩy phân cách hàng nghìn.

Khi đưa 3 Measure vừa tạo vào Pivot Table, bạn sẽ nhận được bảng kết quả trực quan như sau:Dựa trên hình ảnh, hệ thống ghi nhận có 20.836 khách hàng mua hàng (Total Customer), thực hiện 45.000 đơn hàng (Total Order) và tiêu thụ 328.035 sản phẩm (Total Qty).

Bước 6: Tính tổng doanh thu bán hàng (Total Revenue) bằng hàm SUM

Yêu cầu thực tế: Chỉ số quan trọng nhất của mọi doanh nghiệp – tổng số tiền thu về từ các đơn hàng.

Cách thực hiện:

Tạo Measure mới tên là Total Revenue.
Viết công thức cộng tổng cột doanh thu đơn dòng (LineRevenue$) trong bảng chi tiết đơn hàng: =SUM(OrderLine[LineRevenue$])
Định dạng kiểu số (Number), làm tròn không lấy chữ số thập phân (Decimal places: 0) và sử dụng dấu phân cách hàng nghìn.
Kết quả trả về cho chỉ số này là 44.837.769 USD.

Bước 7: Tính chi phí vận chuyển trung bình (AVG Ship Cost) bằng hàm AVERAGE

Yêu cầu thực tế: Doanh nghiệp cần đo lường xem trung bình mỗi lượt vận chuyển hàng hóa tiêu tốn bao nhiêu chi phí logistics để tối ưu hóa biên lợi nhuận.Chi phí vận chuyển của từng chuyến hàng được lưu tại cột ShipCost$ thuộc bảng Fulfillment. Hàm AVERAGE sẽ tự động lấy tổng chi phí của cột này chia cho tổng số dòng giao dịch vận chuyển hợp lệ.

Cách thực hiện:

Tạo Measure mới tên là AVG Ship Cost.
Nhập công thức DAX: =AVERAGE(Fulfillment[ShipCost$])
Thiết lập định dạng: Chọn Category là Number, Format là Decimal Number, làm tròn về 0 chữ số thập phân (Decimal places: 0) để số liệu gọn gàng hơn.
Sau khi nhấn OK và đưa vào bảng Pivot Table, bạn sẽ thấy cột giá trị hiển thị kết quả làm tròn là 11 USD (như hình bên dưới):

Bước 8: Tìm doanh thu dòng lớn nhất và nhỏ nhất bằng hàm MAX và MIN

Yêu cầu thực tế: Xác định xem giá trị của một dòng sản phẩm đơn lẻ cao nhất là bao nhiêu và thấp nhất là bao nhiêu trong toàn bộ lịch sử bán hàng.Cách thực hiện tính giá trị cao nhất:

Tạo Measure mới tên là Max Line Revenue.
Nhập công thức: =MAX(OrderLine[LineRevenue$])
Định dạng hiển thị kiểu số nguyên.
Cách thực hiện tính giá trị thấp nhất:
Tạo Measure mới tên là Min Line Revenue.
Nhập công thức: =MIN(OrderLine[LineRevenue$])
Định dạng hiển thị kiểu số nguyên.
Sau khi tính toán, kết quả hiển thị thực tế trên Pivot Table lần lượt là:Mức doanh thu dòng cao nhất (Max Line Revenue): 1.552 USD.
Mức doanh thu dòng thấp nhất (Min Line Revenue): 1 USD (được làm tròn từ giá trị thực tế là 1.4 USD như hiển thị ở thanh Formula bar).

Các lưu ý khi dùng các hàm Aggregation trong Excel

Để tránh gặp phải những lỗi sai phổ biến và tối ưu hóa hiệu suất tính toán khi xây dựng mô hình dữ liệu trong Excel, bạn cần đặc biệt lưu ý các điểm sau:

Xử lý ô trống (Blank) trong mô hình dữ liệu

Các hàm Aggregation trong DAX có cơ chế xử lý ô trống rất thông minh:SUM và AVERAGE sẽ tự động bỏ qua hoàn toàn các dòng chứa giá trị BLANK(), không tính chúng vào phép toán. Điều này giúp phép chia trung bình của AVERAGE không bị kéo tụt xuống bởi các dòng không phát sinh dữ liệu.

COUNT sẽ không đếm các ô trống.

Tuy nhiên, DISTINCTCOUNT sẽ đếm giá trị BLANK() như một danh mục riêng biệt nếu cột đó tồn tại ít nhất một ô trống. Do đó, hãy làm sạch dữ liệu nguồn trước khi đưa vào Data Model để tránh việc bị dư ra một đơn vị đếm không mong muốn.

Sự khác nhau giữa hàm Aggregation thông thường và hàm Iterator (hàm có đuôi X)

Excel Power Pivot cung cấp hai nhóm hàm tổng hợp:Hàm thông thường (SUM, AVERAGE, MAX, MIN…): Tính toán trực tiếp trên toàn bộ một cột dữ liệu dựa trên các bộ lọc đang hiện hành của Pivot Table.

Hàm Iterator (SUMX, AVERAGEX, MAXX, MINX…): Thực hiện tính toán theo cơ chế duyệt từng dòng (Row-by-Row). Hàm sẽ tính toán biểu thức bạn viết cho từng dòng trước, sau đó mới tiến hành gộp (tổng hợp) kết quả lại ở bước cuối.

Ví dụ: Thay vì tạo cột phụ lấy Qty * UnitPrice rồi dùng SUM, bạn có thể dùng =SUMX(OrderLine, OrderLine[Qty] * OrderLine[UnitPrice]) để tiết kiệm bộ nhớ cho file Excel.

Định dạng hiển thị (Formatting)

Như bạn thấy trong ví dụ về Min Line Revenue, giá trị thực tế của ô là 1.4 nhưng khi đưa vào Pivot Table, nếu bạn định dạng là Whole Number thì Excel sẽ tự động làm tròn thành 1. Hãy luôn kiểm tra kỹ định dạng số, số lượng chữ số thập phân sau dấu phẩy để đảm bảo báo cáo gửi lên cấp trên không bị sai lệch số liệu thực tế.

Tổng kết

Việc làm chủ nhóm hàm Aggregation trong Excel thông qua công cụ Power Pivot và ngôn ngữ viết DAX là bước chuyển dịch quan trọng giúp bạn nâng cao năng lực phân tích từ cơ bản lên chuyên nghiệp. Thay vì bị giới hạn bởi số dòng của các hàm truyền thống, giờ đây bạn có thể dễ dàng quản trị, liên kết và tổng hợp hàng triệu bản ghi dữ liệu chỉ với vài công thức cơ bản như SUM, AVERAGE, COUNT, hay DISTINCTCOUNT.

243