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

Quan trọng:  Bài viết này là dịch máy, hãy xem tuyên bố miễn trừ trách nhiệm. Bạn hãy tìm phiên bản tiếng Anh của bài viết này tại đây để tham khảo.

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 dữ liệu thô từ các bảng hoặc các nguồn dữ liệu khác, dữ liệu thường phẳng, có nghĩa là có rất nhiều chi tiết nhưng chưa được sắp xếp hoặc nhóm theo bất kỳ cách nào. Việc thiếu tính tổng hợp hoặc cấu trúc này có thể khiến khó phát hiện các mẫu trong dữ liệu. Một phần quan trọng của việc lập mẫu dữ liệu là xác định các phép tính gộp giúp đơn giản hóa, tóm tắt hoặc tổng hợp các mẫu để đáp ứng nhu cầu 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:

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

Trung bình    Doanh số bán hàng trung bình theo người bán hàng trong tháng này là bao nhiêu?

Giá trị cực tiểu và cực đại    Năm quận bán hàng dẫn đầu về lượng sản phẩm bán được là những quận nào?

Để 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:

Tổng đã lọc   Có bao nhiêu giao dịch 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à bao nhiêu?

Giá trị tối thiểu và tối đa đã nhóm    Quận bán hàng nào được xếp hạng hàng đầu cho từng danh mục sản phẩm hoặc cho từng chương trình 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 tính, bạn có thể tạo các phép tính gộp có tính đến ngữ cảnh hàng hiện tại để truy xuất hàng liên quan từ bảng khác rồi tính tổng, đếm hoặc tính 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 của thể loại đó không liên quan đến dữ liệu thực tế, thì bạn có thể gặp lỗi hoặc nhận được kết quả kỳ lạ. Thông thường Power Pivot sẽ cố gắng khắc phục vấn đề, bằng cách tự động phát hiện và gợi ý các mối quan hệ. Để biết thêm thông tin, hãy xem Làm việc với các Mối quan hệ trong PivotTables.

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 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

AVERAGE

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ố.

COUNT

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

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.

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.

SUM

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.

COUNTROWS

Đế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 lấy giá trị từ một cột chưa lọc đơn lẻ và kết quả sẽ giống như kết quả trong Excel, vốn luôn luôn chỉ cộng tổng các giá trị trong cột Thành tiền. Tuy nhiên, trong Power Pivot, công thức được diễn giải là "Lấy giá trị trong cột Thành tiền cho mỗi hàng trong bảng Doanh số, sau đó cộng tổng những giá trị đơn lẻ này. Power Pivot đánh giá từng hàng được thực hiện thao tác tính gộp và tính toán một giá trị vô hướng đơn lẻ cho mỗi hàng, sau đó thực hiện việc tính gộp trên những giá trị đó. Vì vậy, kết quả của công thức có thể sẽ khác nếu đã áp dụng bộ lọc cho bảng, hoặc nếu các giá trị được tính toán dựa trên các phép tính gộp khác mà phép tính đó có thể đã được lọc. Để biết thêm thông tin, hãy xemNgữ 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 đã mô tả trong phần trước, DAX có các hàm tính gộp hoạt động với ngày và giờ mà bạn chỉ định, để cung cấp tính năng thời gian thông minh tích hợp sẵn. Các hàm này sử dụng phạm vi ngày để nhận giá trị liên quan và tính gộp giá trị. Bạn cũng có thể so sánh các giá trị giữa các 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 Hiển thị Thời gian Thông minh (Hàm Hiển thị Thời gian Thông minh) là các hàm có thể dùng để truy xuất ngày hoặc phạm vi ngày tuỳ chỉnh để sử dụng trong phép tính gộp. Ví dụ: bạn có thể sử dụng hàm DATESINPERIOD để trả về một phạm vi ngày và sử dụng tập ngày đó làm đối số cho một hàm khác để tính phép tính gộp tuỳ chỉnh chỉ cho các ngày đó.

Ghi chú: Tuyên bố miễn trừ trách nhiệm Dịch Máy: Bài viết này do một hệ thống máy tính dịch mà không có sự can thiệp của con người. Microsoft cung cấp những bản dịch máy này để giúp người dùng không nói tiếng Anh hiểu nội dung về các sản phẩm, dịch vụ và công nghệ của Microsoft. Do bài viết này được dịch máy nên có thể có các lỗi về từ vựng, cú pháp hoặc ngữ pháp.

Phát triển các kỹ năng 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.

×