Các hàm tính gộp trong Power Pivot

Lưu ý:  Chúng tôi muốn cung cấp cho bạn nội dung trợ giúp mới nhất ở chính ngôn ngữ của bạn, nhanh nhất có thể. Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Vui lòng cho chúng tôi biết ở cuối trang này rằng thông tin có hữu ích với bạn không? Dưới đây là bài viết bằng Tiếng Anh để bạn tham khảo dễ hơn.

Các hàm tính gộp là một cách thu gọn, tóm tắt, hoặc nhóm dữ liệu. Khi bạn bắt đầu với các dữ liệu thô từ bảng hoặc các nguồn dữ liệu, dữ liệu thường là phẳng, có nghĩa là có rất nhiều chi tiết, nhưng nó đã chưa được tổ chức hoặc được nhóm trong bất kỳ cách nào. Thiếu này của tóm tắt hoặc cấu trúc có thể gây khó khăn để khám phá các mẫu hình trong dữ liệu. Một phần quan trọng của lập mô hình dữ liệu là xác định phép tính gộp đơn giản hóa, tóm tắt, hoặc tóm tắt các mẫu hình trong câu trả lời cho câu hỏi kinh doanh cụ thể.

Hàm tính gộp phổ biến nhất, chẳng hạn như các cách sử dụng Trung bình, đếm, DISTINCTCOUNT, MAX, MINhoặc tổng có thể tạo trong một số đo tự động bằng cách sử dụng tự động tính tổng. Các loại phép tính gộp, chẳng hạn như AVERAGEX, COUNTX, COUNTROWShoặc SUMXtrả về một bảng, và yêu cầu một công thức được tạo bằng cách dùng Biểu thức phân tích dữ liệu (DAX).

Tìm hiểu các Hàm tính gộp trong Power Pivot

Chọn Nhóm cho Phép tính gộp

Khi bạn tính gộp dữ liệu, bạn nhóm dữ liệu theo thuộc tính, chẳng hạn như sản phẩm, giá cả, vùng hoặc ngày rồi xác định một công thức hoạt động trên tất cả dữ liệu trong nhóm. Ví dụ: khi bạn tạo một tổng cho một năm, bạn đang tạo một phép tính gộp. Nếu sau đó bạn tạo một tỷ lệ của năm này so với năm trước và trình bày các dữ liệu đó ở dạng tỷ lệ phần trăm thì đó là một loại phép tính gộp khác.

Quyết định về cách nhóm dữ liệu được dựa trên nhu cầu kinh doanh. Ví dụ: các phép tính gộp có thể trả lời các câu hỏi sau đây:

Đếm   Đã có bao nhiêu giao dịch trong một tháng?

Giá trị trung bình    Doanh số bán các giá trị trung độ trong tháng này, nhân viên bán hàng?

Tối thiểu và tối đa giá trị    Nhóm quận bán hàng nào đã là đầu năm về đơn vị đã bán?

Để tạo một phép tính trả lời những câu hỏi này, bạn phải có dữ liệu chi tiết chứa các số cần đếm hoặc tính tổng và dữ liệu số phải liên quan theo cách nào đó với các nhóm mà bạn sẽ sử dụng để sắp xếp kết quả.

Nếu dữ liệu đã không chứa giá trị mà bạn có thể dùng để nhóm, chẳng hạn như một thể loại sản phẩm hoặc tên của khu vực địa lý nơi có cửa hàng, bạn có thể giới thiệu về nhóm dữ liệu của bạn bằng cách thêm các thể loại. Khi bạn xây dựng nhóm trong Excel, bạn phải theo cách thủ công nhập hoặc chọn Nhóm bạn muốn sử dụng trong số các cột trong trang tính của bạn. Tuy nhiên, trong một hệ thống quan hệ, cấu trúc phân cấp chẳng hạn như các thể loại sản phẩm thường được lưu trữ trong một bảng khác với thực tế bảng hoặc giá trị bảng. Thường là thể loại bảng được nối kết với dữ liệu thực tế bằng một số loại khóa. Ví dụ, giả sử bạn tìm thấy rằng dữ liệu của bạn chứa ID sản phẩm, nhưng không phải tên của sản phẩm hoặc thể loại của họ. Để thêm thể loại vào một trang tính Excel phẳng, bạn cần phải sao chép vào cột có chứa các tên thể loại. Với Power Pivot, bạn có thể nhập bảng thể loại sản phẩm vào mô hình dữ liệu của bạn, tạo mối quan hệ giữa các bảng với dữ liệu số và danh sách thể loại sản phẩm và rồi dùng các thể loại để nhóm dữ liệu. Để biết thêm thông tin, hãy xem tạo một mối quan hệ giữa các bảng.

Chọn Hàm cho Phép tính gộp

Sau khi bạn đã xác định và thêm các nhóm cần dùng, bạn phải quyết định sẽ dùng những hàm toán học nào để thực hiện việc tính gộp. Thông thường thuật ngữ tính gộp được dùng như một từ đồng nghĩa cho các phép tính toán học hoặc phép tính thống kế được dùng trong các hàm tính gộp, chẳng hạn như tính tổng, trung bình, tối thiểu hoặc đếm. Tuy nhiên, Power Pivot cho phép bạn tạo các công thức tùy chỉnh để tính gộp, ngoài những phép tính gộp có trong cả Power Pivot và Excel.

Ví dụ: với cùng một tập giá trị và nhóm được sử dụng trong các ví dụ trước, bạn có thể tạo các phép tính gộp tuỳ chỉnh trả lời các câu hỏi sau đây:

Đếm xem đã lọc   Bao nhiêu giao dịch của bạn đã có trong một tháng, ngoại trừ giai đoạn bảo trì cuối tháng?

Tỷ lệ sử dụng giá trị trung bình theo thời gian    Tỷ lệ phần trăm tăng trưởng hoặc sụt giảm doanh số so với cùng kỳ năm ngoái là gì?

Giá trị tối thiểu và tối đa grouped    Nhóm quận bán hàng nào được xếp hạng hàng đầu cho mỗi thể loại sản phẩm, hoặc cho mỗi bán hàng khuyến mãi?

Thêm Phép tính gộp vào Công thức và PivotTable

Khi bạn có một ý tưởng tổng quát về cách nhóm dữ liệu của mình sao cho có ý nghĩa và bạn có các giá trị mà bạn muốn làm việc với chúng, bạn có thể quyết định sẽ dựng một PivotTable hay tạo các phép tính trong bảng. Power Pivot mở rộng và cải thiện khả năng ban đầu của Excel để tạo những phép tính gộp như cộng tổng, đếm hoặc tính trung bình. Bạn có thể tạo các phép tính gộp tùy chỉnh trong Power Pivot trong cửa sổ Power Pivot hoặc trong một vùng PivotTable của Excel.

  • Trong một cột được tính toán, bạn có thể tạo các hàm tính gộp đưa vào tài khoản ngữ cảnh hàng hiện tại để truy xuất những hàng liên quan từ một bảng khác, và sau đó tính tổng, đếm, hoặc trung bình các giá trị trong các hàng liên quan.

  • Trong một số đo, bạn có thể tạo phép tính gộp động sử dụng bộ lọc đã xác định trong công thức và bộ lọc được quy định bởi thiết kế của PivotTable và lựa chọn của slicer, đầu đề cột và hàng đầu đề. Số đo bằng cách dùng các hàm tính gộp chuẩn có thể tạo trong Power Pivot bằng cách sử dụng tự động tính tổng hoặc bằng cách tạo công thức. Bạn cũng có thể tạo các số đo ngầm bằng cách dùng các hàm tính gộp chuẩn trong một PivotTable trong Excel.

Thêm Nhóm vào PivotTable

Khi thiết kế PivotTable, bạn kéo các trường biểu thị nhóm, danh mục hoặc phân cấp vào phần cột và hàng của PivotTable để nhóm dữ liệu. Sau đó, bạn kéo các trường chứa giá trị số vào vùng giá trị để chúng có thể được đếm, tính trung bình hoặc tính tổng.

Nếu bạn thêm thể loại vào PivotTable nhưng dữ liệu thể loại không liên quan đến dữ liệu thực tế, bạn có thể nhận được lỗi hoặc kết quả riêng. Thường Power Pivot sẽ cố gắng sửa vấn đề, tự động phát hiện và gợi ý mối quan hệ. Để biết thêm thông tin, hãy xem làm việc với mối quan hệ trong Pivottable.

Bạn cũng có thể kéo các trường vào Bộ cắt để chọn các nhóm dữ liệu nhất định để xem. Bộ cắt cho phép bạn nhóm, phân loại và lọc kết quả trong PivotTable một cách tương tác.

Làm việc với Nhóm trong Công thức

Bạn cũng có thể sử dụng các nhóm và danh mục để tính gộp dữ liệu được lưu trữ trong bảng bằng cách tạo mối quan hệ giữa các bảng rồi tạo công thức sử dụng các mối quan hệ đó để tra cứu giá trị liên quan.

Nói cách khác, nếu bạn muốn tạo một công thức nhóm các giá trị theo một danh mục, trước tiên bạn phải sử dụng một mối quan hệ để kết nối bảng chứa dữ liệu chi tiết và các bảng chứa các danh mục rồi xây dựng công thức.

Để biết thêm thông tin về cách tạo công thức sử dụng tra cứu, xem tra cứu trong công thức Power Pivot.

Sử dụng Bộ lọc trong Phép tính gộp

Tính năng mới trong Power Pivot là khả năng để áp dụng bộ lọc cho cột và bảng dữ liệu, không chỉ trong giao diện người dùng và trong PivotTable hoặc biểu đồ, nhưng cũng trong các công thức rất mà bạn dùng để tính tổng hợp. Bộ lọc có thể dùng trong công thức trong cột được tính toán và trong s.

Ví dụ: trong các hàm tính gộp DAX mới, thay vì chỉ định các giá trị để tính tổng hoặc đếm, bạn có thể chỉ định toàn bộ một bảng làm đối số. Nếu bạn không áp dụng bất kỳ bộ lọc nào vào bảng đó, hàm tính gộp sẽ hoạt động với tất cả các giá trị trong cột được chỉ định của bảng. Tuy nhiên, trong DAX bạn có thể tạo bộ lọc động hoặc tĩnh trên bảng đó để phép tính gộp hoạt động với tập con dữ liệu khác tùy thuộc vào điều kiện bộ lọc và ngữ cảnh hiện tại.

Bằng cách kết hợp điều kiện và bộ lọc trong công thức, bạn có thể tạo các phép tính gộp thay đổi tùy thuộc vào giá trị được cung cấp trong công thức hoặc thay đổi tùy thuộc vào lựa chọn tiêu đề hàng và tiêu đề cột trong PivotTable.

Để biết thêm thông tin, hãy xem Lọc dữ liệu trong công thức.

So sánh Hàm Tính gộp trong Excel và Hàm Tính gộp trong DAX

Bảng sau đây liệt kê một số hàm tính gộp tiêu chuẩn do Excel cung cấp, và đưa ra các nối kết tới việc thực hiện các hàm này trong Power Pivot. Phiên bản DAX của những hàm này có hành vi rất giống với phiên bản Excel, chỉ có một vài khác biệt nhỏ trong cú pháp và các xử lý một số loại dữ liệu nhất định.

Các Hàm Tính gộp Chuẩn

Hàm

Sử dụng

GIÁ TRỊ TRUNG BÌNH

Trả về giá trị trung bình (trung bình số học) của tất cả các số trong một cột.

AVERAGEA

Trả về giá trị trung bình (trung bình số học) của tất cả các giá trị trong một cột. Xử lý các giá trị văn bản và không phải số.

ĐẾM SỐ

Đếm số lượng giá trị số trong một cột.

HÀM COUNTA

Đếm số lượng giá trị trong một cột không trống.

MAX

Trả về giá trị số lớn nhất trong một cột.

MAXX

Trả về giá trị lớn nhất từ một tập biểu thức được đánh giá qua một bảng.

HÀM MIN

Trả về giá trị số nhỏ nhất trong một cột.

MINX

Trả về giá trị nhỏ nhất từ một tập biểu thức được đánh giá qua một bảng.

TÍNH TỔNG

Thêm tất cả các số vào một cột.

Các Hàm Tính gộp trong DAX

DAX bao gồm các hàm tính gộp cho phép bạn chỉ định một bảng sẽ thực hiện phép tính gộp. Do vậy, thay vì chỉ thêm hoặc tính trung bình các giá trị trong một cột, các hàm này cho phép bạn tạo một biểu thức xác định linh hoạt dữ liệu để tính gộp.

Bảng sau đây liệt kê các hàm tính gộp có sẵn trong DAX.

Hàm

Sử dụng

AVERAGEX

Tính trung bình một tập biểu thức được đánh giá qua một bảng.

COUNTAX

Đếm một tập biểu thức được đánh giá qua một bảng.

COUNTBLANK

Đếm số lượng giá trị trống trong một cột.

COUNTX

Đếm tổng số hàng trong một bảng.

ĐẾM HÀNG

Đếm số hàng được trả về từ hàm bảng xếp chồng, chẳng hạn như hàm bộ lọc.

SUMX

Trả về tổng của một tập biểu thức được đánh giá qua một bảng.

Điểm khác biệt giữa Hàm Tính gộp trong DAX và Excel

Mặc dù các hàm này có cùng một tên như phiên bản Excel của họ, họ sử dụng công cụ phân tích trong bộ nhớ Power Pivotvà có được ghi lại để làm việc với bảng và cột. Bạn không thể sử dụng công thức DAX trong sổ làm việc Excel và ngược lại. Họ có thể chỉ được dùng trong cửa sổ Power Pivot và trong Pivottable dựa trên dữ liệu Power Pivot . Ngoài ra, mặc dù các hàm có tên giống nhau, hành vi có thể hơi khác một chút. Để biết thêm thông tin, hãy xem các chủ đề tham chiếu hàm riêng lẻ.

Cách cột được đánh giá trong một phép tính gộp cũng khác với cách Excel xử lý phép tính gộp. Sau đây là ví dụ minh hoạ.

Giả sử bạn muốn nhận tổng các giá trị trong cột Thành tiền trong bảng Doanh số bán hàng, vì vậy bạn tạo công thức sau đây:

=SUM('Sales'[Amount])

Trong trường hợp đơn giản nhất hàm này sẽ được các giá trị từ một cột chưa được lọc và kết quả là giống như trong Excel, luôn chỉ cộng các giá trị trong cột số tiền. Tuy nhiên, trong Power Pivot, công thức được hiểu là "Lấy giá trị trong số tiền cho mỗi hàng của bảng Sales, và sau đó cộng các giá trị riêng lẻ. Power Pivot đánh giá từng hàng theo đó phép tính gộp được thực hiện tính một giá trị vô hướng đơn cho mỗi hàng, và sau đó thực hiện một phép tính gộp trên những giá trị. Do đó, kết quả của công thức có thể khác nhau nếu bộ lọc đã được áp dụng cho bảng, hoặc nếu các giá trị được tính toán dựa trên các hàm tính gộp có thể được lọc. Để biết thêm thông tin, hãy xem ngữ cảnh trong công thức DAX.

Hàm Hiển thị Thời gian Thông minh

Ngoài các hàm tính gộp bảng được mô tả trong phần trước, DAX có các hàm tính gộp làm việc với ngày tháng và thời gian bạn xác định, cung cấp dựng sẵn thời gian thông minh. Các hàm này sử dụng phạm vi ngày để lấy giá trị liên quan và tổng hợp các giá trị. Bạn cũng có thể so sánh các giá trị nằm trong phạm vi ngày.

Bảng sau đây liệt kê các hàm hiển thị thời gian thông minh có thể sử dụng cho phép tính gộp.

Hàm

Sử dụng

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Tính một giá trị vào cuối giai đoạn cụ thể theo lịch.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

Tính một giá trị vào cuối giai đoạn theo lịch trước giai đoạn cho sẵn.

TOTALMTD

TOTALYTD

TOTALQTD

Tính một giá trị trong quảng cách bắt đầu vào ngày đầu tiên của giai đoạn và kết thúc vào ngày cuối cùng trong cột ngày được chỉ định.

Các hàm khác trong phần hàm thời gian thông minh (Hàm thời gian thông minh) là hàm mà có thể dùng để truy xuất ngày hoặc phạm vi tùy chỉnh ngày tháng để dùng trong phép tính gộp. Ví dụ, bạn có thể dùng hàm DATESINPERIOD để trả về một phạm vi ngày, và dùng đó tập hợp ngày tháng là một đối số hàm khác để tính tổng hợp tùy chỉnh cho chỉ những ngày.

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×