Tính toán giá trị trong một PivotTable

Để tính toán giá trị trong PivotTable, bạn có thể sử dụng bất cứ hoặc tất cả các loại phương pháp tính toán sau đây:

• Hàm tổng hợp trong các trường giá trị    Dữ liệu trong khu vực giá trị tóm tắt dữ liệu nguồn cơ sở trong PivotTable. Chẳng hạn như dữ liệu nguồn sau đây:

• Tạo báo cáo PivotTable và PivotChart sau đây. Nếu bạn tạo PivotChart từ dữ liệu trong PivotTable, các giá trị trong PivotChart đó sẽ phản ánh tính toán trong báo cáo PivotTable liên kết.

• Trong PivotTable, trường cột Tháng cung cấp các mục Tháng Ba và Tháng Tư. Trường hàng Khu vực cung cấp các mục Phía Bắc, Phía Nam, Phía Đông và Phía Tây. Giá trị tại giao điểm của cột Tháng Tư và hàng Phía Bắc là tổng doanh thu bán hàng từ các bản ghi trong dữ liệu nguồn có giá trị Tháng là Tháng Tư và giá trị Khu vực là Phía Bắc.

• Trong PivotChart, trường Khu vực có thể là một trường thể loại hiển thị Phía Bắc, Phía Nam, Phía Đông và Phía Tây dưới dạng thể loại. Trường Tháng có thể là một trường chuỗi hiển thị các mục Tháng Ba, Tháng Tư và Tháng Năm dưới dạng chuỗi được thể hiện trong chú giải. Trường Giá trị có tên Tổng Doanh thu có thể chứa các đánh dấu dữ liệu thể hiện tổng doanh thu cho mỗi khu vực trong từng tháng. Ví dụ: một đánh dấu dữ liệu, theo vị trí trên trục đứng (giá trị), sẽ thể hiện tổng doanh thu trong Tháng Tư của khu vực Phía Bắc.

• Để tính toán trường giá trị, hàm tổng hợp sau đây sẵn dùng cho tất cả các kiểu dữ liệu nguồn, ngoại trừ dữ liệu nguồn Xử lý Phân tích Trực tuyến (OLAP).

  Hàm	Tóm tắt
  Sum	Tổng các giá trị. Đây là hàm mặc định cho dữ liệu số.
  Count	Số lượng các giá trị dữ liệu. Hàm tổng hợp Count hoạt động giống như hàm COUNTA. Count là hàm mặc định cho dữ liệu không phải là số.
  Average	Tính trung bình của các giá trị.
  Max	Giá trị lớn nhất.
  Min	Giá trị nhỏ nhất.
  Product	Tích của các giá trị.
  Đếm Số	Số lượng giá trị dữ liệu là số. Hàm tổng hợp Đếm Số hoạt động giống hàm COUNT.
  StDev	Ước tính độ lệch chuẩn của một tập hợp, khi mẫu là một tập hợp con của toàn bộ tập hợp.
  StDevp	Độ lệch chuẩn của một tập hợp, khi tập hợp là tất cả dữ liệu cần tóm tắt.
  Var	Ước tính phương sai của một tập hợp, khi mẫu là một tập hợp con của toàn bộ tập hợp.
  Varp	Phương sai của một tập hợp, khi tập hợp là tất cả dữ liệu cần được tóm tắt.

• Tính toán tùy chỉnh    tính toán tùy chỉnh hiển thị giá trị dựa trên các mục hoặc ô khác trong vùng dữ liệu. Ví dụ: bạn có thể hiển thị giá trị trong trường dữ liệu Tổng Doanh thu dưới dạng phần trăm doanh thu Tháng Ba hoặc dưới dạng tổng hiện có của các mục trong trường Tháng.

  Các hàm sau đây sẵn dùng cho các tính toán tùy chỉnh trong trường giá trị.

  Hàm	Kết quả
  Không Tính toán	Hiển thị giá trị được nhập vào trường.
  % Tổng Cuối	Hiển thị giá trị dưới dạng tỷ lệ phần trăm trên tổng cuối của tất cả các giá trị hoặc điểm dữ liệu trong báo cáo.
  % Tổng Cột	Hiển thị tất cả các giá trị trong từng cột hoặc chuỗi dưới dạng tỷ lệ phần trăm trên tổng của cột và chuỗi.
  % Tổng Hàng	Hiển thị giá trị trong mỗi hàng hoặc thể loại dưới dạng tỷ lệ phần trăm trên tổng của hàng hoặc thể loại.
  % Của	Hiển thị giá trị dưới dạng tỷ lệ phần trăm trên giá trị của Mục cơ sở trong Trường cơ sở.
  % Tổng Hàng Cha mẹ	Tính toán giá trị như sau: (giá trị của mục) / (giá trị của mục cha mẹ trong các hàng)
  % Tổng Cột Cha mẹ	Tính toán giá trị như sau: (giá trị của mục) / (giá trị của mục cha mẹ trong các cột)
  % Tổng Cha mẹ	Tính toán giá trị như sau: (giá trị của mục) / (giá trị của mục cha mẹ trong Trường cơ sở đã chọn)
  Chênh lệch	Hiển thị giá trị dưới dạng chênh lệch với giá trị của Mục cơ sở trong Trường cơ sở.
  % Chênh lệch	Hiển thị giá trị dưới dạng chênh lệch phần trăm với giá trị của Mục cơ sở trong Trường cơ sở.
  Tổng Hiện có trong	Hiển thị giá trị các mục liên tiếp trong Trường cơ sở dưới dạng tổng hiện có.
  % Tổng Hiện có trong	Tính toán giá trị của các mục liên tiếp trong Trường cơ sở được hiển thị dưới dạng phần trăm tổng hiện có.
  Xếp Nhỏ nhất đến Lớn nhất	Hiển thị thứ hạng các giá trị đã chọn trong một trường cụ thể, liệt kê mục nhỏ nhất trong trường là 1 và mỗi giá trị lớn hơn sẽ có giá trị thứ hạng cao hơn.
  Xếp Lớn nhất đến Nhỏ nhất	Hiển thị thứ hạng các giá trị đã chọn trong một trường cụ thể, liệt kê mục lớn nhất trong trường là 1 và mỗi giá trị nhỏ hơn sẽ có giá trị thứ hạng cao hơn.
  Chỉ mục	Tính toán giá trị như sau: ((giá trị trong ô) x (Tổng Cuối của Tổng Cuối)) / ((Tổng Cuối của Hàng) x (Tổng Cuối của Cột))

• Công thức    Nếu hàm tổng hợp và tính toán tùy chỉnh không cung cấp kết quả bạn muốn, bạn có thể tạo công thức của riêng mình trong trường tính toán và các mục tính toán. Ví dụ: bạn có thể thêm một mục tính toán với công thức cho tiền hoa hồng bán hàng, có thể khác nhau cho mỗi khu vực. Sau đó, báo cáo sẽ tự động bao gồm tiền hoa hồng trong tổng phụ và tổng cuối.

Các tính toán và tùy chọn sẵn dùng trong báo cáo tùy thuộc vào việc dữ liệu nguồn đến từ cơ sở dữ liệu OLAP hay nguồn dữ liệu không phải OLAP.

• Tính toán dựa trên dữ liệu nguồn OLAP    Đối với PivotTable được tạo từ OLAP cube, các giá trị tóm tắt được tính toán sẵn trên máy chủ OLAP trước khi Excel hiển thị kết quả. Bạn không thể thay đổi cách tính các giá trị được tính toán sẵn này trong PivotTable. Ví dụ: bạn không thể thay đổi hàm tổng hợp được dùng để tính toán các trường dữ liệu hoặc tổng phụ hay thêm các trường tính toán hoặc mục tính toán.

  Ngoài ra, nếu máy chủ OLAP cung cấp các trường tính toán, gọi là thành viên tính toán, bạn sẽ thấy các trường này trong Danh sách Trường PivotTable. Bạn cũng sẽ thấy mọi trường tính toán và mục tính toán được tạo bởi macro đã được viết trong Visual Basic for Applications (VBA) và lưu trữ trong sổ làm việc của bạn, tuy nhiên, bạn sẽ không thể thay đổi các trường hoặc mục này. Nếu bạn cần thêm các kiểu tính toán, hãy liên hệ với người quản trị cơ sở dữ liệu OLAP.

  Đối với dữ liệu nguồn OLAP, bạn có thể bao gồm hoặc loại trừ giá trị của các mục bị ẩn khi tính toán tổng phụ và tổng cuối.

• Tính toán dựa trên dữ liệu nguồn không phải OLAP    Trong PivotTable dựa trên các kiểu dữ liệu ngoài khác hoặc dữ liệu trang tính, Excel sử dụng hàm tổng hợp Tổng để tính toán trường giá trị có chứa dữ liệu số và hàm tổng hợp Đếm để tính toán trường dữ liệu có chứa văn bản. Bạn có thể chọn hàm tổng hợp khác, chẳng hạn như Trung bình, Lớn nhất hoặc Nhỏ nhất để tiếp tục phân tích và tùy chỉnh dữ liệu của mình. Bạn cũng có thể tạo công thức của riêng mình có sử dụng các thành phần của báo cáo hoặc dữ liệu trang tính khác bằng cách tạo trường tính toán hoặc mục tính toán trong một trường.

Bạn chỉ có thể tạo công thức trong báo cáo dựa trên dữ liệu nguồn không phải OLAP. Bạn không thể sử dụng công thức trong báo cáo dựa trên cơ sở dữ liệu OLAP. Khi sử dụng công thức trong báo cáo PivotTable, bạn nên biết về các quy tắc cú pháp công thức và hành vi công thức sau đây:

• Thành phần công thức PivotTable    Trong các công thức bạn tạo cho trường tính toán và mục tính toán, bạn có thể sử dụng toán tử và biểu thức như khi bạn thực hiện trong các công thức trang tính khác. Bạn có thể sử dụng hằng số và tham chiếu dữ liệu từ báo cáo, tuy nhiên, bạn không thể sử dụng tham chiếu ô hoặc tên đã xác định. Bạn không thể sử dụng các hàm trang tính yêu cầu tham chiếu ô hoặc tên đã xác định làm đối số, đồng thời, bạn không thể dùng hàm mảng.

• Tên trường và mục    Excel sử dụng tên trường và mục để xác định các thành phần đó trong báo cáo ở công thức của bạn. Trong ví dụ sau đây, dữ liệu trong dải ô C3:C9 đang sử dụng tên trường Sản phẩm bơ sữa. Mục tính toán trong trường Loại ước tính doanh số cho sản phẩm mới dựa trên doanh số Sản phẩm bơ sữa có thể dùng công thức như =Sản phẩm bơ sữa* 115%.

  

  Lưu ý: Trong báo cáo PivotChart, tên trường được hiển thị trong danh sách trường PivotTable, đồng thời, người dùng có thể xem tên mục trong từng danh sách thả xuống của từng trường. Đừng nhầm lẫn những tên này với những tên bạn thấy trong các mẹo về biểu đồ, những tên đó phản ánh tên điểm dữ liệu và chuỗi.

• Các công thức hoạt động trên tổng cộng, không phải bản ghi riêng lẻ    Công thức cho các trường tính toán hoạt động trên tổng dữ liệu cơ sở của mọi trường trong công thức. Ví dụ: công thức trường tính toán =Doanh số * 1,2 nhân tổng doanh thu cho mỗi loại và khu vực với 1,2; công thức không nhân từng doanh thu riêng lẻ với 1,2, rồi tính tổng các kết quả nhân.

  Công thức cho các mục tính toán hoạt động trên bản ghi riêng lẻ. Ví dụ: công thức mục tính toán =Sản phẩm Bơ sữa*115% nhân từng doanh thu riêng lẻ của Sản phẩm Bơ sữa với 115 %, rồi kết quả nhân được tổng hợp cùng nhau trong khu vực Giá trị.

• Khoảng cách, số và ký hiệu trong tên    Trong tên bao gồm nhiều trường, các trường có thể được sắp xếp theo mọi thứ tự. Trong ví dụ trên, các ô C6:D6 có thể là 'Tháng Tư Phía Bắc' hoặc 'Phía Bắc Tháng Tư'. Sử dụng dấu nháy đơn quanh tên có nhiều từ hoặc bao gồm số hoặc ký hiệu.

• Tổng    Công thức không thể tham chiếu đến các tổng (chẳng hạn như Tổng Tháng Ba, Tổng Tháng Tư và Tổng Cuối trong ví dụ).

• Tên trường trong tham chiếu mục    Bạn có thể bao gồm tên trường trong một tham chiếu đến một mục. Tên mục phải nằm trong dấu ngoặc vuông — ví dụ: Khu vực[Phía Bắc]. Sử dụng định dạng này để tránh lỗi #NAME? khi hai mục trong hai trường khác nhau của báo cáo có cùng tên. Ví dụ: nếu báo cáo có một mục có tên là Thịt trong trường Loại và một mục khác có tên là Thịt trong trường Thể loại thì bạn có thể tránh lỗi #NAME? bằng cách tham chiếu đến mục như Loại[Thịt] và Thể_loại[Thịt].

• Tham chiếu đến mục theo vị trí    Bạn có thể tham chiếu đến một mục theo vị trí của mục đó trong báo cáo như hiện đang được sắp xếp và hiển thị. Loại[1] là Sản phẩm bơ sữa và Loại[2] là Hải sản. Mục được tham chiếu theo cách này có thể thay đổi bất cứ khi nào vị trí của các mục thay đổi hoặc các mục khác nhau được hiển thị hoặc ẩn. Các mục bị ẩn không được đếm trong chỉ mục này.

  Bạn có thể sử dụng vị trí tương đối để tham chiếu đến các mục. Vị trí được xác định tương đối với mục tính toán có chứa công thức. Nếu Phía Nam là khu vực hiện tại, Khu_vực[-1] là Phía Bắc; nếu Phía Bắc là khu vực hiện tại, Khu_vực[+1] là Phía Nam. Ví dụ: một mục tính toán có thể sử dụng công thức =Khu_vực[-1] * 3%. Nếu vị trí mà bạn cung cấp nằm trước mục đầu tiên hoặc sau mục cuối cùng trong trường, công thức sẽ cho kết quả lỗi #REF! lỗi.

Để sử dụng công thức trong PivotChart, bạn tạo công thức trong PivotTable liên kết, nơi bạn có thể thấy các giá trị riêng lẻ cấu thành dữ liệu của bạn, rồi bạn có thể xem các kết quả bằng đồ họa trong PivotChart.

Ví dụ: PivotChart sau đây cho thấy doanh số cho mỗi nhân viên bán hàng theo từng khu vực:

Để xem doanh số sẽ ra sao nếu tăng thêm 10 phần trăm, bạn có thể tạo trường tính toán trong PivotTable liên kết sử dụng công thức như =Doanh số * 110%.

Kết quả ngay lập tức xuất hiện trong PivotChart, như được hiển thị trong biểu đồ sau đây:

Để xem đánh dấu dữ liệu riêng biệt cho doanh số tại khu vực phía Nam trừ chi phí vận chuyển 8 phần trăm, bạn có thể tạo mục tính toán trong trường Khu vực với công thức như =Phía Bắc – (Phía Bắc * 8%).

Biểu đồ kết quả sẽ trông như thế này:

Tuy nhiên, mục tính toán được tạo trong trường Nhân viên bán hàng sẽ xuất hiện dưới dạng một chuỗi được biểu thị trong chú giải và xuất hiện trong biểu đồ dưới dạng điểm dữ liệu trong mỗi thể loại.