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.

Trước khi bạn bắt đầu, hãy quyết định xem liệu bạn có muốn có trường tính toán hoặc mục tính toán trong một trường hay không. Sử dụng trường tính toán khi bạn muốn sử dụng dữ liệu từ một trường khác trong công thức của mình. Sử dụng mục tính toán khi bạn muốn công thức của mình sử dụng dữ liệu từ một hoặc nhiều mục cụ thể trong một trường.

Đối với các mục tính toán, bạn có thể nhập các công thức khác nhau theo từng ô. Ví dụ: nếu một mục tính toán có tên là OrangeCounty có công thức =Oranges * 0,25 qua tất cả các tháng, bạn có thể thay đổi công thức thành =Oranges *0,5 cho Tháng Sáu, Tháng Bảy và Tháng Tám.

Nếu bạn có nhiều mục hoặc công thức được tính toán, bạn có thể điều chỉnh thứ tự tính toán.

Thêm trường tính toán

1. Bấm vào PivotTable.

   Thao tác này sẽ hiển thị Công cụ PivotTable, thêm các tab Phân tích và Thiết kế.

2. Trên tab Phân tích, trong nhóm Tính toán, bấm vào Trường, Mục & Bộ, rồi bấm vào Trường Tính toán.

   

3. Trong hộp Tên, nhập tên cho trường.

4. Trong hộp Công thức, nhập công thức cho trường.

   Để sử dụng dữ liệu từ một trường khác trong công thức, bấm vào trường đó trong hộp Trường, rồi bấm vào Chèn Trường. Ví dụ: để tính toán khoản tiền hoa hồng 15 % trên mỗi giá trị trong trường Doanh số, bạn có thể nhập = Doanh số * 15%.

5. Bấm vào Thêm.

Thêm mục tính toán vào trường

1. Bấm vào PivotTable.

   Thao tác này sẽ hiển thị Công cụ PivotTable, thêm các tab Phân tích và Thiết kế.

2. Nếu các mục trong trường được nhóm lại, trên tab Phân tích trong nhóm Nhóm, bấm vào Huỷ nhóm.

   

3. Bấm vào trường bạn muốn thêm mục tính toán.

4. Trên tab Phân tích, trong nhóm Tính toán, bấm vào Trường, Mục & Bộ, rồi bấm Mục Tính toán.

   

5. Trong hộp Tên, nhập tên cho mục tính toán.

6. Trong hộp Công thức, nhập công thức cho mục.

   Để sử dụng dữ liệu từ một mục trong công thức, bấm vào mục trong danh sách Mục, rồi bấm vào Chèn Mục (mục này phải đến từ cùng một trường với mục tính toán).

7. Bấm vào Thêm.

Nhập các công thức khác nhau theo từng ô cho các mục tính toán

1. Bấm vào ô mà bạn muốn thay đổi công thức.

   Để thay đổi công thức cho một số ô, nhấn giữ phím CTRL, rồi bấm vào các ô bổ sung.

2. Trong thanh công thức, nhập các thay đổi cho công thức.

Điều chỉnh thứ tự tính toán cho nhiều mục hoặc công thức tính toán.

1. Bấm vào PivotTable.

   Thao tác này sẽ hiển thị Công cụ PivotTable, thêm các tab Phân tích và Thiết kế.

2. Trên tab Phân tích, trong nhóm Tính toán, bấm vào Trường, Mục & Bộ, rồi bấm vào Thứ tự Giải quyết.

   

3. Bấm vào một công thức, rồi bấm vào Di chuyển Lên hoặc Di chuyển Xuống.

4. Tiếp tục cho đến khi các công thức được sắp xếp theo thứ tự bạn muốn tính toán.

Bạn có thể hiển thị danh sách tất cả các công thức được sử dụng trong PivotTable hiện tại.

1. Bấm vào PivotTable.

   Thao tác này sẽ hiển thị Công cụ PivotTable, thêm các tab Phân tích và Thiết kế.

2. Trên tab Phân tích, trong nhóm Tính toán, bấm vào Trường, Mục & Bộ, rồi bấm vào Liệt kê Công thức.

   

Trước khi bạn chỉnh sửa công thức, hãy xác định xem công thức đó nằm trong trường tính toán hay mục tính toán. Nếu công thức nằm trong mục tính toán, hãy đồng thời xác định xem công thức đó có phải là công thức duy nhất cho mục tính toán hay không.

Đối với các mục tính toán, bạn có thể chỉnh sửa công thức riêng lẻ cho các ô cụ thể của mục tính toán. Ví dụ: nếu một mục tính toán có tên là OrangeCalc có công thức =Oranges * 0,25 qua tất cả các tháng, bạn có thể thay đổi công thức thành =Oranges *0,5 cho Tháng Sáu, Tháng Bảy và Tháng Tám.

Xác định xem công thức nằm trong trường tính toán hay mục tính toán

1. Bấm vào PivotTable.

   Thao tác này sẽ hiển thị Công cụ PivotTable, thêm các tab Phân tích và Thiết kế.

2. Trên tab Phân tích, trong nhóm Tính toán, bấm vào Trường, Mục & Bộ, rồi bấm vào Liệt kê Công thức.

   

3. Trong danh sách công thức, tìm công thức bạn muốn thay đổi được liệt kê tại Trường Tính toán hoặc Mục Tính toán.
   
   Khi có nhiều công thức cho một mục tính toán, công thức mặc định đã được nhập khi mục được tạo sẽ có tên mục tính toán trong cột B. Đối với các công thức bổ sung cho mục tính toán, cột B chứa cả tên mục tính toán và tên mục giao nhau.

   Ví dụ: bạn có thể đặt công thức mặc định cho mục tính toán có tên là Mục_của_Tôi và một công thức khác cho mục được xác định là Doanh số Tháng Một của Mục_của_Tôi. Trong PivotTable, bạn sẽ tìm thấy công thức này trong ô Doanh số của hàng Mục_của_tôi và cột Tháng Một.

4. Tiếp tục với một trong các phương pháp chỉnh sửa sau đây.

Chỉnh sửa công thức của trường tính toán

1. Bấm vào PivotTable.

   Thao tác này sẽ hiển thị Công cụ PivotTable, thêm các tab Phân tích và Thiết kế.

2. Trên tab Phân tích, trong nhóm Tính toán, bấm vào Trường, Mục & Bộ, rồi bấm vào Trường Tính toán.

   

3. Trong hộp Tên, chọn trường tính toán mà bạn muốn thay đổi công thức.

4. Trong hộp Công thức, chỉnh sửa công thức.

5. Bấm vào Sửa đổi.

Chỉnh sửa một công thức đơn cho mục tính toán

1. Bấm vào trường có chứa mục tính toán.

2. Trên tab Phân tích, trong nhóm Tính toán, bấm vào Trường, Mục & Bộ, rồi bấm Mục Tính toán.

   

3. Trong hộp Tên, chọn mục tính toán.

4. Trong hộp Công thức, chỉnh sửa công thức.

5. Bấm vào Sửa đổi.

Chỉnh sửa một công thức riêng lẻ cho một ô cụ thể của mục tính toán

1. Bấm vào ô mà bạn muốn thay đổi công thức.

   Để thay đổi công thức cho một số ô, nhấn giữ phím CTRL, rồi bấm vào các ô bổ sung.

2. Trong thanh công thức, nhập các thay đổi cho công thức.

   Mẹo: Nếu bạn có nhiều mục hoặc công thức được tính toán, bạn có thể điều chỉnh thứ tự tính toán. Để biết thêm thông tin, xem mục Điều chỉnh thứ tự tính toán cho nhiều mục hoặc công thức tính toán.

1. Xác định xem công thức nằm trong trường tính toán hay mục tính toán.

   Trường tính toán xuất hiện trong Danh sách Trường PivotTable. Mục tính toán xuất hiện dưới dạng mục trong các trường khác.

2. Thực hiện một trong những thao tác sau:

   • Để xóa trường tính toán, bấm vào vị trí bất kỳ trong PivotTable.

   • Để xóa mục tính toán, trong PivotTable, bấm vào trường có chứa mục bạn muốn xóa.

     Thao tác này sẽ hiển thị Công cụ PivotTable, thêm các tab Phân tích và Thiết kế.

3. Trên tab Phân tích, trong nhóm Tính toán, bấm vào Trường, Mục & Bộ, rồi bấm vào Trường Tính toán hoặc Mục Tính toán.

   

4. Trong hộp Tên, chọn trường hoặc mục bạn muốn xóa.

5. Bấm vào Xóa.