Chương 14. Phân tích dữ liệu với các PivotTable

BNTT · 16/7/08

Phỏng dịch từ cuốn Formulas and Functions with Microsoft Office Excel 2007 của Paul McFedries

Phần III: Xây dựng các mô hình kinh doanh

Chương 13: Phân tích dữ liệu với các Table
Chương 14: Phân tích dữ liệu với các PivotTable
Chương 15: Sử dụng các công cụ tạo mô hình kinh doanh của Excel

---------------------------------------------------------------------------------------

PHẦN III - Xây dựng các mô hình kinh doanh

Chương 14 - Phân tích dữ liệu với các PivotTable

Các Table và những cơ sở dữ liệu bên ngoài có thể chứa hàng trăm, thậm chí hàng ngàn record. Phân tích những dữ liệu này có thể là một cơn ác mộng nếu không có các công cụ phù hợp. Để giúp bạn, Excel cung cấp một công cụ phân tích dữ liệu mạnh mẽ gọi là PivotTable. Công cụ này cho phép bạn tổng hợp hàng trăm record trong một bảng ngắn gọn. Sau đó bạn có thể xử lý kiểu trình bày (layout) của bảng này để có những cái nhìn khác nhau về dữ liệu của bạn. Chương này giới thiệu với bạn các PivotTable và minh họa cho bạn nhiều cách khác nhau để sử dụng chúng với dữ liệu của riêng bạn.

Bởi vì cuốn sách này nói về các công thức và hàm trong Excel, nên tôi sẽ không đi sâu vào chi tiết việc xây dựng và tùy biến các PivotTable. Thay vào đó, tôi sẽ tập trung vào những việc mà bạn có thể thực hiện với những phép tính PivotTable cài đặt sẵn và những phép tính PivotTable tùy ý.

Nếu muốn nghiên cứu sâu hơn về PivotTable cùng những khả năng tuyệt vời của nó, các bạn xem ở đây:
PivotTable & PivotChart 2007 - Từ căn bản đến nâng cao

Bạn có thể tải về bảng tính với những ví dụ trong chương này tại đây: Example Files

BNTT · 16/7/08

Chương 14 - Phân tích dữ liệu với PivotTable

14.1. PivotTable là gì?

Để hiểu các PivotTable, bạn cần xem cách chúng tương thích với những tính năng phân tích dữ liệu khác của Excel như thế nào. Phân tích dữ liệu có nhiều mức độ phức tạp.

Mức độ đơn giản nhất là việc tìm kiếm và truy xuất thông tin cơ bản. Ví dụ, bạn có một cơ sở dữ liệu liệt kê các đại lý bán hàng và doanh số của từng vùng, và bạn muốn biết doanh thu tính theo vùng của một đại lý đã xác định.

Mức độ phức tạp tiếp theo bao gồm việc tìm kiếm và những hệ thống tìm kiếm phức tạp hơn, trong đó dùng những tiêu chuẩn lọc và kỹ thuật trích lọc thông tin đã nói đến ở chương 13: "Phân tích dữ liệu với các Table". Rồi bạn áp dụng những công thức tính tổng con và những công thức bảng (cũng đã nói ở chương 13) để tìm câu trả lời cho câu hỏi của mình. Ví dụ, giả sử rằng mỗi đại lý cấp II là một phần của một đại lý cấp I nào đó, và bạn muốn biết tổng doanh thu của đại lý cấp I này chẳng hạn. Bạn có thể dùng cách tính tổng từng đại lý cấp II, hoặc thiết lập một tiêu chuẩn lọc cho tất cả những đại lý cấp II thuộc đại lý cấp I đó, và dùng hàm DSUM() để có kết quả. Để có thêm thông tin chi tiết hơn, như là tổng doanh thu của một đại lý cấp I trong quý II, bạn chỉ cần thêm các điều kiện thích hợp vào tiêu chuẩn lọc.

Mức độ kế tiếp của phân tích dữ liệu áp dụng một câu hỏi cho nhiều biến. Ví dụ, nếu một công ty có 4 đại lý cấp I, và bạn muốn biết doanh thu của từng đại lý cấp I theo từng quý là bao nhiêu so với tổng doanh thu của cả công ty. Có một giải pháp, là thiết lập bốn vùng tiêu chuẩn khác nhau và sử dụng bốn hàm DSUM() khác nhau. Nhưng nếu như công ty này có hàng chục đại lý cấp I? Thậm chí là hàng trăm? Lý tưởng nhất là bạn phải tìm cách để tổng hợp thông tin dữ liệu vào một bảng doanh số, mỗi đại lý cấp I nằm trong một hàng, và mỗi quý trong một cột. Đó chính xác là những gì PivotTable làm, và như bạn sẽ thấy trong chương này, bạn có thể tạo ra những PivotTable cho riêng bạn chỉ với vài cú nhấn chuột.

BNTT · 16/7/08

14.1. PivotTables là gì?

14.1.1. Các PivotTable làm việc như thế nào

Trong trường hợp đơn giản nhất, các PivotTable tổng hợp dữ liệu trong một field (được gọi là một data field) và phân chia nó theo dữ liệu của một field khác. Từng giá trị duy nhất trong field thứ hai (gọi là row field) trở thành từng tiêu đề hàng. Ví dụ, hình 14.1 minh họa một Table các đại lý bán hàng. Với một PivotTable, bạn có thể tổng hợp các con số trong field Sales (data field) và chia nhỏ nó ra theo Region (row field). Hình 14.2 minh họa kết quả của PivotTable. Chú ý rằng Excel sẽ dùng 4 mục chính trong field Region (East, West, Midwest, và South) là các tiêu đề hàng.

Hình 14.1. Danh sách các đại lý bán hàng

Hình 14.2. Một PivotTable minh họa tổng doanh thu theo vùng

Bạn có thể chia nhỏ thêm dữ liệu của mình bằng cách xác định thêm một field thứ ba (gọi là column field) để sử dụng làm các tiêu đề cột. Hình 14.3 minh họa kết quả của một PivotTable với 4 mục riêng biệt của field Quarter (1st, 2nd, 3rd, và 4th) được sử dụng để tạo ra các cột.

Hình 14.3. Một PivotTable minh họa doanh thu của các vùng theo từng quý

Tính năng quan trọng nhất trong PivotTable là tính năng trục xoay (pivoting). Ví dụ, nếu bạn muốn một kiểu xem khác vể dữ liệu của mình, bạn có thể rê cái column field lên vùng row field (trong khu vực PivotTable Field List ở bên phải màn hình), như minh họa ở hình 14.4. Như bạn có thể thấy, kết quả là bảng này thể hiện mỗi vùng như là các hàng mục chính, với các quý trở thành các hàng mục con, chia theo từng khu vực.

Hình 14.4. Bạn có thể di chuyển row field hay column field để xoay dữ liệu và có một kiểu xem khác

BNTT · 16/7/08

14.1. PivotTables là gì?

14.1.2. Một vài thuật ngữ sử dụng trong PivotTable

PivotTable có những thuật ngữ riêng của chúng. Danh sách sau đây là một số thuật ngữ mà bạn cần biết:

Data source — (Nguồn dữ liệu) Là dữ liệu gốc. Bạn có thể dùng một dãy, một Table, hoặc dữ liệu được nhập vào, dữ liệu từ một nguồn ở bên ngoài.
Field — Là một hạng mục dữ liệu, như là Region, Querter, hay là Sales... Bởi vì đa số các PivotTable được dẫn xuất từ các Table hay các cơ sở dữ liệu, nên môt PivotTable Field tương tự như môt field (một cột) trong một Table hay một cơ sở dữ liệu.
Label — Là một phần tử trong một field.
Row Field — Là một field với một tập hợp riêng biệt các giá trị text, số hoặc ngày tháng, dùng làm các tiêu đề hàng trong PivotTable. Trong ví dụ ở bài trước, Region là một Row Field.
Column Field — Là một field với một tập hợp riêng biệt các giá trị text, số hoặc ngày tháng, dùng làm các tiêu đề cột trong PivotTable. Trong PivotTable thứ hai, ở hình 4.3, Quarter là một Column Field.
Report filter — Là một field với một tập hợp riêng biệt các giá trị text, số hoặc ngày tháng, dùng để lọc cách xem PivotTable. Ví dụ, bạn có thể dùng field Sales Rep như là một Report Filter. Khi bạn chọn một đại lý bán hàng khác, bảng sẽ được lọc lại để chỉ thấy các dữ liệu của đại lý này.
PivotTable items — Là các mục từ danh sách nguồn đã dùng, như là các tên hàng, tên cột, và các tên trang.
Data field — Là một field chứa dữ liệu mà bạn muốn tổng hợp trong bảng.
Data area — Phần bên trong của bảng, là nơi hiển thị những dữ liệu đã tổng hợp.
Layout — Cách sắp xếp các field và các item trong PivotTable.

BNTT · 16/7/08

Chương 14 - Phân tích dữ liệu với PivotTable

14.2. Tạo một PivotTable

Trong các phiên bản trước của Excel, bạn tạo một PivotTable bằng cách điền thông tin vào một số hộp thoại của chức năng PivotTable Wizard đưa ra. Nhiều người khi thấy các hộp thoại này thì hoảng sợ, do đó họ thường không bao giờ tiến xa hơn một hoặc hai bước đầu trong tiến trình thực hiện.

Excel 2007 thay đổi tất cả các điều này bằng cách chỉ hiển thị một hộp thoại (nếu bạn sử dụng một Table hay một dãy cục bộ để làm dữ liệu nguồn) và đặt tất cả các tùy chọn cũng như các thiết lập lên thanh Ribbon để bạn có thể chọn chúng sau khi đã có PivotTable.

14.2.1. Tạo một PivotTable từ một Table hoặc một dãy

Nguồn dữ liệu thông thường nhất cho các PivotTable là một Table của Excel, mặc dù bạn cũng có thể sử dụng dữ liệu được thiết lập là một dãy bình thường. Và bạn có thể sử dụng bất kỳ Table hoặc dãy nào để tạo PivotTable, nhưng để thích hợp nhất cho PivotTable, nguồn phải có hai thành phần chính sau đây:

Có ít nhất một field chứa dữ liệu có thể kết nhóm. Nghĩa là, field này có chứa dữ liệu với một số những giá trị text, giá trị số hoặc ngày tháng riêng biệt và có giới hạn. Trong bảng tính đã minh họa ở hình 14.1, field Region rất hoàn hảo cho một PivotTable bởi vì cho dù nó có hàng chục mục, nhưng chỉ có 4 giá trị riêng biệt: East, West, Midwest, và South.
Mỗi field trong danh sách phải có một tiêu đề.

Hình 14.5 minh họa một bảng mà tôi sẽ dùng làm ví dụ để chỉ cho bạn cách tạo một PivotTable. Đây là một danh sách các đơn đặt hàng đã được đặt từ những phản hồi của khách hàng sau một chiến dịch tiếp thị ba tháng. Mỗi record trình bày ngày đặt hàng (Date), sản phẩm được đặt hàng (Product - gồm có 4 loại: Printer stand, Glare filter, Mouse pad, và Copy holder), số lượng (Quantity) và số tiền đã đặt cọc (Net $), loại khuyến mãi (Promotional) được khách hàng chọn (1 Free with 10 - mua 10 tặng 1, hay Extra Discount - chiết khấu đặc biệt), và hình thức quảng cáo mà khách hàng đã trả lời vào (Direct mail - qua thư trực tiếp, Magazine - qua tạp chí, hay Newspaper - qua báo chí).

Hình 14.5. Một bảng các đơn đặt hàng mà chúng ta cần để tổng hợp với một PivotTable

Còn tiếp một bài...

BNTT · 16/7/08

14.2.1. Tạo một PivotTabe từ một Table hoặc một dãy

(Tiếp theo bài trước)

Đây là các bước để tổng hợp một Table hay một dãy với một PivotTable:

Nhấn vào bên trong Table hoặc dãy.
Cách bạn tiến hành bước tiếp theo này phụ thuộc vào loại dữ liệu mà bạn muốn tổng hợp:
- Nếu bạn đang làm việc với một Table, bạn chọn Design, Summarize with PivotTable.
- Nếu bạn đang làm việc với một bảng hay một dãy, bạn chọn Insert và rồi nhấn vào nửa phần trên của nút PivotTable.
Trong hộp thoại Create PivotTable xuất hiện ra (xem hình 14.6), có lẽ bạn đã thấy tên Table hoặc địa chỉ của dãy đang nằm sẵn trong hộp Select a Table or Range. Nếu không có, bạn nhập hoặc chọn một tên Table hay dãy.

Hình 14.6. Sử dụng hộp thoại Create PivotTable để xác định Table hoặc dãy được dùng làm dữ liệu nguồn, cũng như vị trí của PivotTable
Chọn nơi bạn muốn xuất hiện báo cáo PivotTable:
- New Worksheet — Nhấn tùy chọn này để Excel tạo một trang tính (worksheet) mới cho PivotTable.
- Existing Worksheet — Nhấn tùy chọn này và rồi dùng hộp Location range để nhập vào hoặc chọn ô mà bạn muốn PivotTable xuất hiện. (Ô mà bạn chỉ định sẽ là ô trên cùng bên trái của một PivotTable).
Nhấn OK. Excel sẽ tạo một khung sườn cho PivotTable, hiển thị PivotTable Field List nằm ở bên phải màn hình và thêm hai tab của PivotTable Tools: Options và Design trên thanh Ribbon, như minh họa ở hình 14.7.

Hình 14.7. Excel bắt đầu tạo một khung sườn cho báo cáo PivotTable[/I]
Thêm field mà bạn muốn xuất hiện trong báo cáo. Excel cho bạn hai cách để làm điều này:
- Trong danh sách Choose Fields to Add to Report, nhấn để kích hoạt check-box bên cạnh cái field mà bạn muốn thêm. Nếu bạn kích hoạt chechk-box của một field có giá trị là số, Excel sẽ thêm nó vào vùng Values; nếu bạn kích hoạt check-box của một field có giá trị là text, Excel sẽ thêm nó vào vùng Row Labels.
- Nhấn và kéo một field, rồi thả nó vào bên trong vùng mà bạn muốn field này xuất hiện.
  
  Mẹo nhỏ:
  
  Nếu bạn muốn sử dụng một field làm một cột của PivotTable, bạn chọn nó để thêm nó vào trong vùng Row Labels, rồi sau đó nhấn và kéo nó, thả vào vùng Column Labels. Bạn cũng có thể nhấn và kéo trực tiếp một field vào trong vùng Column Labels.
  
  Nếu bạn sử dụng một nguồn dữ liệu rất lớn, Excel có thể phải mất một thời gian dài để cập nhật PivotTable khi bạn thêm vào một field. Trong trường hợp này, bạn nhấn kích hoạt check-box Defer Layout Update để yêu cầu Excel không cập nhật PivotTable khi bạn thêm vào các field. Khi bạn đã bố trí xong khung sườn cho PivotTable, bạn nhấn Update.
  
  Nhấp chuột vào đây để mở rộng...
Lập lại bước 5 để thêm tất cả các field mà bạn muốn bao gồm trong báo cáo. Khi bạn thêm vào một field, Excel sẽ cập nhật lại báo cáo PivotTable. Ví dụ, hình 14.8 minh họa báo cáo với các field Quantity và Product đã thêm vào.

Hình 14.8. Báo cáo PivotTable với Product được thêm vào ở vùng Row Labels và Quantity được thêm vào vùng Values.

BNTT · 16/7/08

14.2. Tạo một PivotTable

14.2.2. Tạo một PivotTable từ một cơ sở dữ liệu bên ngoài

Excel có thể tạo một PivotTable ngay cả khi dữ liệu nguồn của bạn nằm trong một cơ sở dữ liệu bên ngoài (ví dụ, một cơ sở dữ liệu Access hay SQL Server). Nếu bạn có dữ liệu bên ngoài kết nối với hệ thống của mình, bạn có thể sử dụng một trong những kết nối này để làm nguồn dữ liệu. Còn không, bạn có thể tạo nhanh một kết nối mới. Sau đây là các bước để thực hiện:

Chọn Insert rồi nhấn vào nửa phần trên của nút PivotTable, Excel sẽ hiển thị hộp thoại Create PivotTable.
Nhấn vào Use an External Data Source.
Nhấn vào Choose Connection. Excel hiển thị hộp thoại Existing Connections.
Nếu bạn thấy kết nối mà bạn muốn dùng, bạn chọn nó và nhảy đến bước 10.
Nhấn vào New Source để khởi động Data Connection Wizard.
Chọn loại dữ liệu bạn muốn và nhấn Next.
Xác định nguồn dữ liệu. Việc này phụ thuộc vào loại dữ liệu. Với SQL Server, bạn phải xác định Server Name và Log On Credentials; với một nguồn dữ liệu ODBC, Access chẳng hạn, bạn phải xác định tập tin dữ liệu.
Chọn cơ sở dữ liệu và bảng bạn muốn dùng, rồi nhấn Next.
Nhấn Finish để hoàn tất Data Connection Wizard.
Lập lại từ bước 3 đến bước 6 trong bài trước để hoàn tất PivotTable.

Ghi chú:

Bạn cũng có thể tạo trực tiếp một PivotTable khi bạn nhập dữ liệu từ một nguồn bên ngoài. Trong nhóm Get External Data của tab Data, chọn loại nguồn dữ liệu bạn muốn nhập vào và rồi theo những hướng dẫn trên màn hình. Khi bạn gặp hộp thoại Import Data, bạn nhấn để kích hoạt tùy chọn PivotTable và rồi nhấn OK.

BNTT · 17/7/08

14.2. Tạo một PivotTable

14.2.3. Làm việc với PivotTable và tùy biến một PivotTable

Như tôi đã đề cập đến ở bài đầu, tôi sẽ tập trung vào những công thức và phép tính trên PivotTable trong chương này. Danh sách sau đây sẽ đưa bạn lướt nhanh qua một số thao tác cơ bản với Pivottable mà bạn nên biết. Bạn hãy nhớ rằng trong hầu hết các trường hợp, trước khi thực hiện điều gì, bạn phải nhấn vào bên trong một PivotTable để bật tab Options và tab Design (là hai tab con của tab PivotTable Tools trên thanh Ribbon). Đây là danh sách:

Chọn toàn bộ PivotTable — Chọn Options, Select, Entire PivotTable.
Chọn một thành phần trong PivotTable — Chọn toàn bộ PivotTable, rồi chọn Options, Select. Trong danh sách PivotTable xổ xuống, nhấn vào thành phần mà bạn muốn chọn: Labels and Values, Values, hoặc Labels.
Định dạng PivotTable — Chọn tab Design và rồi chọn một kiểu định dãng trong thư viện PivotTable Styles.
Đổi tên của PivotTable — Chọn tab Options và sửa lại tên trong text box PivotTable Name.
Sắp xếp PivotTable — Nhấn chọn bất kỳ row field hoặc column field, chọn Options, rồi nhấn vào Sort A to Z hoặc Sort Z to A. (Nếu field chứa ngày tháng, nhấn Sort Oldest to Newest hoặc Sort Newest to Oldest.)
Cập nhật lại dữ liệu trong PivotTable — Chọn tab Options và rồi nhấn vào nửa trên của nút Refresh.
Lọc dữ liệu trong PivotTable — Nhấn vào kéo một field vào trong khung Report Filter , mở danh sách lọc xố xuống, và rồi nhấn vào một mục trong danh sách.
Nhóm các dữ liệu PivotTable theo ngày tháng hoặc theo dữ liệu số — Chọn field muốn tạo nhóm, chọn Options, Group Field để mở hộp thoại Grouping, và rồi chọn loại nhóm mà bạn muốn dùng. Ví dụ, với các field ngày tháng, bạn có thể nhóm theo tháng, theo quý, hoặc theo năm.
Nhóm các dữ liệu PivotTable theo các mục trong field — Chọn các mục mà bạn muốn đưa vào nhóm trong field. Rồi nhấn Options, Group Selection.
Bỏ một field ra khỏi PivotTable — Nhấn và kéo field muốn gỡ bỏ ra khỏi khung PivotTable Field List.
Xóa sạch PivotTable — Chọn Options, Clear, Clear All.

BNTT · 17/7/08

Chương 14 - Phân tích dữ liệu với PivotTable

14.3. Làm việc với các Subtotal của PivotTable

Bạn đã thấy rằng Excel thêm các Grand Total vào PivotTable cho các row field và column field. Tuy nhiên, Excel cũng hiển thị các Subtotal cho field là outer field (là field xếp ở trên trong row area hoặc column area) của một PivotTable có nhiều field trong row area hoặc column area (các "area" này là từng ô vuông nằm trong bảng bên phải màn hình, là nơi mà bạn thấy có tên là Row Labels và Column Labels). Ví dụ, trong hình 14.9, bạn thấy có hai field trong row area: Product (Copy holder, Glare filter...) và Promotion (1 Free with 10 và Extra Discount). Product là outer field, nên Excel hiển thị Subtotal cho field đó.

Hình 14.9. Khi bạn thêm nhiều field vào trong row area hoặc column area, Excel hiển thị Subtotal cho các outer field

Ba bài ngắn tiếp theo đây sẽ hướng dẫn bạn cách xử lý các Grand Total và các Subtotal.

BNTT · 17/7/08

14.3. Làm việc với các Subtotal của PivotTable

14.3.1. Làm ẩn các Grand Total trong PivotTable

Để gỡ bỏ các Grand Total ra khỏi PivotTable, bạn theo các bước sau:

Chọn một ô ở bên trong PivotTable.
Chọn Options và nhấn nút Options (không phải là cái mũi tên drop-down). Excel hiển thị hộp thoại PivotTable Options.
Nhấn tab Totals & Filters.
Bỏ kích hoạt ở hai check-box Show Grand Totals for Rows và Show Grand Totals for Columns.
Nhấn OK.

14.3.2. Làm ẩn các Subtotal trong PivotTable

Các PivotTable có nhiều hàng và nhiều cột hiển thị các Subtotal cho mỗi field ngoại trừ field nằm trong cùng (là field gần với vùng dữ liệu nhất). Để gỡ bỏ các Subtotal, bạn theo các bước sau:

Chọn một ô trong hàng (hoặc cột) chứa các tên field.
Chọn Options, Field Settings. Excel hiển thị hộp thoại Field Settings.
Nhấn None trong nhóm Subtotals.
Nhấn OK. Excel gỡ bỏ các Subtotal ra khỏi PivotTable.

14.3.3. Tùy biến phép tính của Subtotal

Phép tính của Subtotal mà Excel áp dụng vào một field thì giống như phép tính mà nó dùng ở vùng dữ liệu. (Xem bài sau để biết cách thay đổi phép tính trong vùng dữ liệu.) Tuy nhiên, bạn có thể thay đổi phép tính này, thêm các phép tính khác, và thậm chí thêm một Subtotal cho field nằm trong cùng. Bạn chọn field mà bạn muốn làm việc với nó, chọn Options, Field Settings, và áp dụng bất kỳ một phương pháp nào sau đây:

Để thay đổi một phép tính cho Subtotal, bạn nhấn tùy chọn Custom trong nhóm Subtotals, chọn một trong những hàm tính toán (Sum, Count, Average...) trong danh sách Select One or More Functions và rồi nhấn OK.
Để thêm vài phép tính Subtotal nữa, bạn nhấn tùy chọn Custom trong nhóm Subtotals, rồi nhấn chọn bất kỳ hàm tính toán nào bạn muốn trong danh sách Select One or More Functions và nhấn OK. (Khi đã nhấn chọn một hàm mà bạn lại muốn bỏ ra, bạn nhấn vào hàm đó thêm một lần nữa).

BNTT · 17/7/08

Chương 14 - Phân tích dữ liệu với PivotTable

14.4. Thay đổi phép tính tổng hợp của Data Field

Mặc định, Excel sử dụng hàm SUM làm phép tính tổng hợp các Data Field. Cho dù SUM là hàm thường được dùng nhất trong PivotTable, không có nghĩa là chỉ có mỗi mình nó. Và thực tế, Excel cung cấp 11 hàm tổng hợp sau đây:

Sum — Tính tổng các giá trị của dữ liệu
Count — Hiển thị tổng số các giá trị của dữ liệu
Average — Tính trung bình các giá trị của dữ liệu
Max — Trả về giá trị lớn nhất của dữ liệu
Min — Trả về giá trị nhỏ nhất của dữ liệu
Product — Tính tích các giá trị của dữ liệu
Count Numbers — Hiển thị tổng số các giá trị là số của dữ liệu
StdDev — Tính độ lệch chuẩn theo một mẫu của các giá trị của dữ liệu
StdDevp — Tính độ lệch chuẩn theo một tập hợp của các giá trị của dữ liệu
Var — Tính phương sai theo một mẫu của các giá trị của dữ liệu
Varp — Tính phương sai theo một tập hợp của các giá trị của dữ liệu

Để thay đổi phép tính tổng hợp cho Data Field, bạn theo các bước sau:

Chọn một ô trong Data Field hoặc chọn nhãn của Data Field
Chọn Options, Field Settings để hiển thị hộp thoại Value Field Settings
Trong danh sách Summarize Value Field By, nhấn vào phép tính tổng hợp mà bạn muốn dùng
Nhấn OK. Excel sẽ thay đổi cách tính cho Data Field

BNTT · 18/7/08

14.4. Thay đổi phép tính tổng hợp của Data Field

14.4.1. Sử dụng phép tính tổng hợp theo sự chênh lệch (Difference Summary Calculation)

Khi bạn phân tích dữ liệu kinh doanh, việc tổng hợp toàn bộ dữ liệu như tổng các mặt hàng đã bán, tổng số đơn đặt hàng, lợi nhuận trung bình... thì thường luôn hữu ích. Ví dụ, báo cáo PivotTable minh họa ở hình 14.10 tổng hợp dữ liệu các hóa đơn trong khoảng thời gian hai năm. Đối với mỗi khách hàng trong Row Filed, chúng ta thấy tổng các hóa đơn được phân chia theo ngày tháng của nó, mà trong trường hợp này đã được kết nhóm theo năm (2006 và 2007).

Hình 14.10. Một báo cáo PivotTable minh họa tổng số hóa đơn của khách hàng theo từng năm

Tuy nhiên, việc so sánh một phần này với một phần khác của dữ liệu cũng hữu ích không kém. Ví dụ, trong PivotTable được minh họa ở hình 14.10, sẽ rất hữu dụng nếu ta so sánh tổng hóa đơn của mỗi khách hàng trong năm 2007 so với năm 2006.

Trong Excel, bạn có thể sử dụng hai loại phép tính tổng hợp theo sự chênh lệch của PivotTable sau đây:

Difference From — Phép tính chênh lệch này so sánh hai mục giá trị số và tính toán sự chênh lệch giữa chúng.
% Difference From — Phép tính chênh lệch này so sánh hai mục giá trị số và tính phần trăm chênh lệch giữa chúng.

Trong mỗi trường hợp, bạn phải xác định một base field (trường cơ sở) — là field mà bạn muốn Excel thực hiện phép tính chênh lệch — và base item (mục cơ sở) — là mục bên trong base field mà bạn muốn dùng làm cơ sở của phép tính chênh lệch. Ví dụ, trong PivotTable minh họa ở hình 14.10, Order Date sẽ là base field và 2006 sẽ là base item.

Để thiết lập một phép tính chênh lệch, bạn theo các bước sau:

Chọn một ô ở bên trong Data Field
Chọn Options, Field Settings để hiển thị hộp thoại Value Field Settings
Trong danh sách Summarize Value Field By, chọn phép tính tổng hợp mà bạn muốn dùng
Nhấn tab Show Values As
Trong danh sách Show Values As, nhấn chọn Difference From hoặc % Difference From
Trong danh sách Base Field, chọn field mà bạn muốn dùng làm base field
Trong danh sách Base Item, chọn mục mà bạn muốn dùng làm base item
Nhấn OK. Excel cập nhật lại PivotTable với phép tính chênh lệch

Hình 14.11 minh họa bản báo cáo PivotTable đã thấy ở hình 14.10 đã được cập nhật với phép tính Difference From :

Hình 14.11. Báo cáo PivotTable ở hình 14.10 được áp dụng phép tính Difference From

------------------------------------------------

Chuyển đổi qua lại giữa hai phép tính chênh lệch

Dưới đây là một macro VBA dùng để chuyển đổi báo cáo PivotTable ở hình 14.11 qua lại giữa phép tính Difference From và phép tính % Difference From:

Sub ToggleDifferenceCalculations()

' Xét Data Field đâu tiên
With Selection.PivotTable.DataFields(1)

' Phép tính hiện tại có phải là Difference From ?
If .Calculation = xlDifferenceFrom Then

' Nếu đúng thì đổi nó sang % Difference From
.Calculation = xlPercentDifferenceFrom
.BaseField = "Years"
.BaseItem = "2006"
.NumberFormat = "0.00%"

Else

' Nếu không, đổi nó sang Difference From
.Calculation = xlDifferenceFrom
.BaseField = "Years"
.BaseItem = "2006"
.NumberFormat = "$#,##0.00"

End If

End With

End Sub

BNTT · 18/7/08

14.4. Thay đổi phép tính tổng hợp của Data Field

14.4.2. Sử dụng phép tính tổng hợp theo phần trăm (Percentage Summary Calculation)

Khi bạn cần so sánh các kết quả trong PivotTable, nếu chỉ xem xét các phép tính tổng hợp cơ bản thì không phải lúc nào cũng có được một sự so sánh chính xác. Ví dụ, bạn xem báo cáo PivotTable ở hình 14.12 dưới đây, báo cáo này trình bày tổng giá trị các hóa đơn đã thanh toán (doanh số) của các đại lý bán hàng khác nhau, phân loại theo từng quý. Trong quý IV, đại lý Margaret Peacock đạt doanh số là $64,429, trong khi đại lý Robert King chỉ đạt được $16,951. Bạn không thể nói rằng đại lý thứ nhất là nhà kinh doanh tốt gấp 4 lần đại lý thứ hai, bởi vì lĩnh vực bán hàng hoặc khách hàng của mỗi đại lý thì hoàn toàn khác nhau. Một cách tốt hơn để phân tích các con số này là so sánh các con số ở quý IV với một giá trị cơ sở nào đó, với quý I chẳng hạn. Những con số này đều giảm trong cả hai trường hợp, nhưng một lần nữa, những giá trị chênh lệch thô này chẳng cho bạn biết thêm điều gì. Vậy, điều bạn nên làm là tính sự chênh lệch theo phần trăm của mỗi đại lý và sau đó so sánh chúng với sự chênh lệch phần trăm trong Grand Total.

Hình 14.12 - PivotTable minh họa tổng doanh số theo từng quý của các đại lý bán hàng

Tương tự, doanh số thô của mỗi đại lý trong một quý nào đó chỉ cho bạn biết được một cách khái quát về những doanh số của mỗi đại lý đã có được so với các đại lý khác. Nếu bạn muốn so sánh chúng, bạn cần chuyển đổi các doanh số đó thành phần trăm của tổng doanh số mỗi quý.

Khi bạn muốn sử dụng các phần trăm trong việc phân tích dữ liệu, bạn có thể dùng phép tính tổng hợp theo phần trăm của Excel (Excel’s percentage calculations) để xem các mục dữ liệu dưới dạng là tỷ lệ phần trăm so với một vài mục khác, hay là tỷ lệ phần trăm trong một hàng, một cột hiện hành, hoặc là tỷ lệ phần trăm trong toàn bộ PivotTable. Excel có bốn phép tính tổng hợp theo phần trăm:

% Of — Phép tính này trả về tỷ lệ phần trăm của mỗi giá trị tương ứng với một base item đã được chọn. Nếu bạn sử dụng phép tính này, bạn phải chọn một base field và một base item.
% Of Row — Phép tính này trả về tỷ lệ phần trăm của mỗi giá trị trong một hàng tương ứng với Grand Total của hàng đó.
% Of Column — Phép tính này trả về tỷ lệ phần trăm của mỗi giá trị trong một cột tương ứng với Grand Total của cột đó.
% Of Total — Phép tính này trả về tỷ lệ phần trăm của mỗi giá trị trong PivotTable tương ứng với Grand Total của toàn bộ PivotTable.

Để thiết lập một phép tính tổng hợp theo phần trăm, bạn theo các bước sau:

Chọn một ô ở bên trong Data Field
Chọn Options, Field Settings để hiển thị hộp thoại Value Field Settings
Trong danh sách Summarize Value Field By, chọn phép tính tổng hợp mà bạn muốn dùng
Nhấn tab Show Values As
Trong danh sách Show Values As, nhấn chọn % Of, % Of Row, % Of Column hoặc % of Total
Nếu bạn chọn % Of, bạn chọn trong danh sách Base filed một field mà bạn muốn dùng làm base field, rồi chọn trong danh sách Base item một field mà bạn muốn dùng làm base item
Nhấn OK. Excel cập nhật lại PivotTable với phép tính tổng hợp theo phần trăm

Hình 14.13 minh họa bản báo cáo PivotTable đã thấy ở hình 14.12 đã được cập nhật với phép tính % Of:

Hình 14.13. Báo cáo PivotTable ở hình 14.12 được áp dụng phép tính % Of

Mẹo nhỏ:

Nếu bạn muốn dùng một macro VBA để thiết lập một phép tính tổng hợp theo phần trăm cho một Data Field, bạn hãy xác lập thuộc tính Calculation của đối tượng PivotField là một trong các hằng số sau: xlPercentOf, xlPercentOfRow, xlPercentOfColumn, hoặc xlPercentOfTotal.

Khi bạn chọn Normal trong danh sách Show Values As, Excel sẽ định dạng Data Field theo kiểu General, do đó bạn sẽ mất hết những kiểu định dạng số mà bạn đã áp dụng. Bạn có thể phục hồi lại kiểu định dạng của bạn bằng cách nhấp vào bên trong Data Field, rồi chọn Options, Field Setting, chọn tiếp Number Format, và chọn một kiểu định dạng trong hộp thoại Format Cells. Hoặc bạn có thể sử dụng một macro để phục hồi lại kiểu định dạng số của bạn. Ví dụ macro sau đây:
Sub ReapplyCurrencyFormat()

With Selection.PivotTable.DataFields(1)

.NumberFormat = "$#,##0.00"
End With
End Sub

BNTT · 19/7/08

14.4.3. Sử dụng phép tính tổng hợp theo lũy kế (Running Total Summary Calculation)

Khi bạn thiết lập một ngân sách, bạn thường có những mục tiêu kinh doanh không chỉ cho mỗi tháng, mà còn có những mục tiêu tích lũy khi năm tài chính tiến triển. Ví dụ, có thể bạn có những mục tiêu kinh doanh cho tháng thứ nhất và cho tháng thứ hai, nhưng cũng có thể bạn có mục tiêu kinh doanh cho cả hai tháng, hoặc ba tháng, bốn tháng... Tổng lũy kế (hay là tổng tích lũy) của những con số này thường được gọi là tổng hợp theo lũy kế (running total summary), và đây là một công cụ phân tích rất hay. Ví dụ, nếu bạn có được tổng lũy kế của một ngân sách cho sáu tháng liên tiếp, bạn có thể thực hiện các điều chỉnh cho các kế hoạch tiếp thị, quảng cáo, chiết khấu cho khách hàng, v.v...

Báo cáo PivotTable có sẵn phép tính tổng hợp theo lũy kế này. Chỉ cần bạn lưu ý rằng, Running Total chỉ áp dụng cho những base field thuộc loại có thể cộng lũy kế từ record này tới record tiếp theo. Thường là nó được áp dụng cho một field chứa các giá trị ngày tháng, nhưng bạn vẫn có thể áp dụng cho các loại field thích hợp khác.

Để thiết lập một phép tính tổng hợp theo lũy kế, bạn theo các bước sau:

Chọn một ô ở bên trong Data Field
Chọn Options, Field Settings để hiển thị hộp thoại Value Field Settings
Trong danh sách Summarize Value Field By, chọn phép tính tổng hợp mà bạn muốn dùng
Nhấn tab Show Values As
Trong danh sách Show Values As, nhấn chọn Running Total In
Chọn trong danh sách Base filed một field mà bạn muốn dùng làm base field
Nhấn OK. Excel cập nhật lại PivotTable với phép tính tổng hợp theo lũy kế

Hình 14.14 minh họa bản báo cáo PivotTable đã được cập nhật với phép tính Running Total In áp dụng cho field Order Date (đã được nhóm theo tháng):

Hình 14.14. Báo cáo PivotTable được áp dụng phép tính tổng hợp theo lũy kế

Mẹo nhỏ:

Nếu bạn sử dụng nhiều phép tính tổng hợp cho một PivotTable, bạn sẽ phải quay lại giá trị Normal trong danh sách Show Value As sau khi thực hiện những thay đổi. Điều đó làm cho bạn phải mất nhiều lần nhấn chuột, và cũng có thể bạn sẽ thấy phiền phức khi cứ phải thường xuyên lập đi lập lại thủ tục này. Bạn có thể tiết kiệm thời gian bằng cách sử dụng một macro VBA để tự động trả PivotTable về kiểu Normal, bằng cách xác lập thuộc tính Calculation của đối tượng PivotField là xlNoAdditionalCalculation. Ví dụ macro sau đây:
Sub ResetCalculationToNormal()

With Selection.PivotTable.DataFields(1)

.Calculation = xlNoAdditionalCalculation
End With
End Sub

BNTT · 19/7/08

14.4. Thay đổi phép tính tổng hợp của Data Field

14.4.4. Sử dụng phép tính tổng hợp theo Index (Index Summary Calculation)

PivotTable rất tuyệt vời trong chuyện làm cho một số lượng lớn các dữ liệu tương đối khó hiểu trở thành một báo cáo tổng kết ngắn gọn, dễ hiểu. Như bạn đã thấy trong các phần trước, một phép tính tổng hợp bình thường không đem lại cho chúng ta kết quả tốt nhất về việc phân tích dữ liệu. Một trường hợp điển hình cho điều này là khi bạn cố gắng xác định dữ liệu nào là quan trọng (một cách tương đối) dựa vào các kết quả trong Data Field.

Ví dụ, bạn xem báo cáo PivotTable minh họa trong hình 14.15. Báo cáo này trình bày doanh thu từ bốn mặt hàng (Copy holder, Glare filter, Mouse pad và Printer stand) được phân chia theo những loại hình quảng cáo (direct mail, magazine, và newspaper). Có nghĩa là, khách hàng sẽ đặt mua một món hàng nào đó sau khi xem một loại hình quảng cáo nào đó, và báo cáo này tổng hợp lại doanh thu của mỗi mặt hàng bán được từ một loại hình quảng cáo.

Hình 14.15. Báo cáo PivotTable minh họa doanh thu của các mặt hàng được phân chia theo loại hình quảng cáo

Ví dụ, bạn có thể thấy rằng có 1,012 miếng Mouse pad được bán ra nhờ việc quảng cáo trên báo (newspaper), nhưng cũng qua việc quảng cáo trên báo thì chỉ bán được 562 cái Copy holder. Liệu điều này sẽ cho ra kết luận là chỉ nên quảng cáo Mouse pad trên báo? Hay là, tổ hợp Mouse pad / newspaper thì có phần nào đó quan trọng hơn tổ hợp Copy holder / newspaper ?

Có thể bạn sẽ trả lời là Có cho cả hai câu hỏi trên. Nhưng sự thật thì không đúng như vậy. Để có được câu trả lời đúng, bạn cần xem xét tổng số Mouse pad bán được, tổng số Copy holder bán được, tổng số hàng bán được qua việc quảng cáo trên báo, và tổng số hàng bán được qua cả 3 loại hình quảng cáo. Đây là một vấn đề khá phức tạp, nhưng PivotTable có thể làm được dùm bạn, bằng phép tính tổng hợp Index. Phép tính Index trả về weighted average - (trung bình gia trọng) của mỗi ô trong Data Field của PivotTable, với công thức sau đây:

(Cell Value) * (Grand Total) / (Row Total) * (Column Total)

Trong kết quả của phép tính Index, ô nào có giá trị càng cao thì ô đó càng "quan trọng" so với toàn bộ kết quả. Sau đây là các bước để bạn thiết lập phép tính tổng hợp theo Index trong PivotTable:

Chọn một ô ở bên trong Data Field
Chọn Options, Field Settings để hiển thị hộp thoại Value Field Settings
Trong danh sách Summarize Value Field By, chọn phép tính tổng hợp mà bạn muốn dùng
Nhấn tab Show Values As
Trong danh sách Show Values As, nhấn chọn Index In
Nhấn OK. Excel cập nhật lại PivotTable với phép tính tổng hợp theo Index

Hình 14.16 minh họa bản báo cáo PivotTable về doanh thu của các mặt hàng dựa vào hình thức quảng cáo mà bạn đã thấy ở hình 14.15, sau khi cập nhật với phép tính Index In. Bạn sẽ thấy ở đây, tổ hợp Mouse pad / newspaper chỉ đạt chỉ số 0.9 (chỉ số thấp thứ hai) trong khi tổ hợp Copy holder / newspaper đạt chỉ số 1.17 (chỉ số cao nhất), hay nói cách khác, việc quảng cáo Copy Holder qua báo chí mang lại hiệu quả tốt nhất.

Hình 14.16. Báo cáo PivotTable được áp dụng phép tính tổng hợp theo Index

BNTT · 19/7/08

Chương 14 - Phân tích dữ liệu với PivotTable

14.5. Tự tạo các phép tính PivotTable

Excel có 11 hàm cài sẵn cho phép bạn tạo một PivotTable mạnh mẽ và hữu dụng, nhưng chúng không bao hàm hết mọi khả năng phân tích dữ liệu. Ví dụ, bạn có một báo cáo PivotTable sử dụng hàm Sum để tổng hợp doanh thu từ các hóa đơn của các đại lý bán hàng. Báo này tốt rồi, nhưng bây giờ bạn lại muốn thưởng thêm cho đại lý nào có doanh số vượt qua một con số nào đó thì sao? Bạn có thể sử dụng hàm GETPIVOTDATA() để tạo các công thức bình thường cho việc xét một đại lý có được thưởng hay không và thưởng bao nhiêu (chẳng hạn sẽ là một phần trăm nào đó trong doanh số).

Tuy nhiên, điều này không tiện cho lắm, vì nếu bạn tăng thêm số đại lý, bạn sẽ cần có thêm công thức, hoặc nếu bạn bỏ bớt một đại lý ra khỏi danh sách, công thức liên quan đến đại lý này sẽ báo lỗi... Và quan trọng hơn hết, việc tạo ra một PivotTable là để thực hiện ít phép tính trong bảng tính hơn, chứ không phải có nhiều phép tính hơn.

Vậy, giải pháp cho trường hợp này là tận dụng tính năng Calculated Field của Excel. Một Calculated Field là một Data field được tạo ra từ một công thức. Ví dụ, giả sử rằng PivotTable tổng hợp hóa đơn đã nói ở trên có một field là Extended Price, và bạn muốn thưởng 5% doanh số cho đại lý nào đạt được tối thiểu 75% doanh số đã đề ra, bạn tạo một Calculated Field có công thức sau đây:

=IF('Extended Price' >= 75000, 'Extended Price' * 0.05, 0)

Thêm một vấn đề nữa, là khi các tiêu đề của hàng hoặc cột mà bạn sử dụng không có trong danh sách của PivotTable, bạn phải làm sao? Ví dụ, giả sử bạn có một số sản phẩm đã được phân thành các hạng mục như sau: Beverages, Condiments, Confections, và Dairy Products. Và giả sử thêm rằng, những hạng mục này lại được phân nhóm vào các bộ phận (division), ví dụ như: Beverages và Condiments nằm trong Division A; Confections và Dairy Products thuộc về Division B... Nếu như trong số các tên hàng và tên cột trong PivotTable của bạn không có cái nào có tên là Division, thì làm thế nào để bạn thấy được kết quả mà PivotTable tổng hợp theo từng Division ?

Giải pháp ở đây là tạo thêm những nhóm mới cho mỗi Division: Bạn chọn các hạng mục sẽ đưa vào từng Division, rồi chọn Options, Group Selection, và tiếp tục như vậy với các hạng mục khác. Làm như vậy thì cũng được, tuy nhiên, Excel cho bạn một giải pháp tốt hơn: Calculated Item. Một Calculated Item là một mục mới nằm trong một hàng hoặc một, chứa những giá trị được tạo ra bởi một công thức. Ví dụ, bạn có thể tạo ra một mục mới có tên là Division A bằng công thức tự tạo sau đây:

=Beverages + Condiments

Trước khi đi vào chi tiết về cách tạo ra một Calculated Field hay một Calculated Item, bạn nên biết sơ qua về một số hạn chế của chúng:

Bạn không thể sử dụng tham chiếu đến một ô, địa chỉ của một dãy, hoặc tên dãy như là các toán hạng trong các công thức tính toán tự tạo này.
Bạn cũng không thể sử dụng các Subtotal của PivotTable, các tổng của hàng, tổng của cột, hoặc các Grand Total như là các toán hạng trong các công thức tính toán tự tạo này.
Trong một Calculated Field, mặc định Excel sử dụng phép tính Sum (tính tổng) khi bạn tham chiếu đến một field khác trong công thức. Tuy nhiên, điều này có thể sẽ gây ra lỗi. Ví dụ, giả sử bạn có một bảng kê các hóa đơn, trong đó có một field là Unit Price (đon giá) và một field là Quantity (số lượng). Bạn có thể nghĩ đơn giản là bạn có thể tạo một Calculated Field trả về tổng số tiền với công thức sau:

= Unit Price * Quantity

Nó không sai, nhưng bởi vì Excel sẽ coi toán hạng Unit Price như là tổng của các giá trị trong field Unit Price, cho nên nó sẽ không thể tự thêm vào chính nó được.
Với môt Calculated Item, công thức tự tạo không thể tham chiếu đến những mục nằm trong bất kỳ field nào ngoại trừ field chứa Calculated Item.
Bạn không thể tạo một Calculated Item trong một PivotTable đã có phân nhóm. Bạn phải rã nhóm tất cả các field trong PivotTable trước khi tạo một Calculated Item.
Bạn không thể dùng Calculated Item để lọc dữ liệu.
Bạn không thể chèn một Calculated Item vào một PivotTable mà trong đó có một field được sử dụng nhiều hơn một lần.
Bạn không thể chèn một Calculated Item vào một PivotTable mà trong đó có sử dụng các phép tính tổng hợp Average, StdDev, StdDevp, Var, hoặc Varp.

BNTT · 19/7/08

14.5. Tự tạo các phép tính PivotTable

14.5.1. Tạo một Calculated Field

Đây là các bước để chèn một Calculated Field vào một vùng dữ liệu của PivotTable:

Nhấn vào bất kỳ ô nào trong vùng dữ liệu của PivotTable
Chọn Options, Formulas, Calculated Field. Excel hiển thị hộp thoại Insert Calculated Field.
Nhập một tên cho Calculated Field vào trong khung Name.
Nhập công thức mà bạn muốn sử dụng cho Calculated Field vào khung Formula.
Nếu bạn cần sử dụng một tên field trong công thức, hãy đặt con trỏ vào nơi bạn muốn có tên field, nhấn chọn tên field trong danh sách ở khung Fields bên dưới, rồi nhấn Insert Field.
Nhấn Add, rồi nhấn OK. Excel sẽ chèn Calculted Field vào trong PivotTable.

Hình 14.17 minh họa một hộp thoại Insert Calculated Field đã được nhập hoàn chỉnh các mục, với một field mới có tên là Bonus xuất hiện trong PivotTable (ở cột C). Công thức dùng để tạo ra Calculated Field này như sau (bạn xem lại bài 14.5 để hiểu tại sao tôi dùng công thức này):

=IF('Extended Price' >= 75000, 'Extended Price' * 0.05, 0)

Hình 14.17. Báo cáo PivotTable với một Calculated Field có tên là Bonus được chèn vào

Trong hình trên, bạn sẽ thấy trong hộp thoại Insert Calculated Field không có nút Add mà lại có nút Modify, là do tôi chụp màn hình sau khi tôi đã có Calculated Field Bonus. Và đây cũng là điều mà tôi sẽ nói tiếp sau đây:

Nếu bạn muốn thay đổi công thức cho một Caculated Field, bạn nhấn vào bất kỳ ô nào bên trong PivotTable, gọi hộp thoại Insert Calculated Field ra như khi tạo nó. Bạn chọn tên của Calculated Field mà bạn muốn sửa công thức, và sau khi đã sửa lại công thức trong khung Formula, bạn nhấn Modify, rồi nhấn OK.
Bạn cũng thực hiện những thao tác như trên khi muốn xóa một Calculated Field. Nhưng sau khi chọn một Calculated Field trong khung Name, thì bạn nhấn Delete, rồi nhấn OK.

Trong hình 14.17, bạn lưu ý rằng hàng Grand Total cũng bao gồm cả tổng cộng của field Bonus. Và lưu ý kỹ thêm chút, bạn sẽ thấy cái tổng cộng này không đúng! Và chuyện này là bình thường trong trường hợp đó là một Calculated Field. Vấn đề là do Excel không tính tổng của một Calculated Field bằng cách cộng những giá trị trong đó lại với nhau. Mà thay vào đó, Excel áp dụng công thức của Calculated Field vào cho cả ô Grand Total của field mà bạn đã dùng làm tham chiếu trong công thức. Mà cụ thể là, bởi vì công thức của Calculated Field Bonus có biểu thức 'Extended Price' >= 75000, nghĩa là, Extended Price là tham chiếu của công thức này, và do đó, Excel áp dụng công thức cho cả Grand Total của field Extended Price. Và bởi vì Grand Total của field Extended Price lớn hơn 75000, nên Excel cũng "thưởng" luôn cho nó 5%, và đó chính là con số mà bạn thấy trong Grand Total của Bonus.

BNTT · 19/7/08

14.5. Tự tạo các phép tính PivotTable

14.5.2. Tạo một Calculated Item

Đây là các bước để chèn một Calculated Item vào một hàng hay một cột của PivotTable:

Nhấn vào bất kỳ ô nào trong một hàng hoặc một cột của PivotTable mà bạn muốn chèn vào một Calculated Item.
Chọn Options, Formulas, Calculated Item. Excel hiển thị hộp thoại Insert Calculated Item in "Field" (Field là tên của field có chứa ô bạn đã nhấn vào ở bước 1).
Nhập một tên cho Calculated Item vào trong khung Name.
Nhập công thức mà bạn muốn sử dụng cho Calculated Item vào khung Formula.
- Nếu bạn cần sử dụng một tên field trong công thức, hãy đặt con trỏ vào nơi bạn muốn có tên field, nhấn chọn tên field trong danh sách ở khung Fields, rồi nhấn Insert Field.
- Nếu bạn cần sử dụng một field item trong công thức, hãy đặt con trỏ vào nơi bạn muốn chèn field item, nhấn chọn một mục trong danh sách ở khung Items bên dưới, rồi nhấn Insert Item.
Nhấn Add.
Lập lại từ bước 3 đến bước 5 để thêm những Calculated Item khác, nếu có.
Nhấn OK. Excel sẽ chèn Calculted Item vào trong cột hoặc hàng mà bạn đã chọn ở bước 1.

Hình 14.18 minh họa một hộp thoại Insert Calculated Item đã được nhập hoàn chỉnh các mục, với ba mục mới có tên là Division A, Division B và Division C được thêm vào trong PivotTable. Công thức dùng để tạo ra 3 Calculated Item này như sau (bạn xem lại bài 14.5 để hiểu tại sao tôi dùng công thức này):

Division A: = Beverage + Condiments

Division B: = Confections + 'Dairy Products'

Division C: = 'Grains/Cereals' + 'Meat/Poultry' + Produce + Seafood

Hình 14.18. Báo cáo PivotTable với 3 Calculated Item được thêm vào

Để sửa hoặc xóa một Calculated Item, bạn làm như cách làm với một Calculated Field.

Khi bạn đã chèn thêm một Calculated Item vào trong một field, Excel sẽ nhớ mục này. Về mặt kỹ thuật, thì nó trở thành một dữ liệu nguồn cho PivotTable. Do đó, nếu bạn sử dụng field này trong một PivotTable khác có chung một dữ liệu nguồn với PivoitTable hiện tại, Excel cũng sẽ bao gồm luôn cả những Calculated Item tạo thêm này trong PivotTable mới. Nếu bạn không thích chuyện đó, chỉ có một cách là dùng bộ lọc cho field, nghĩa là bạn nhấn cho xổ xuống danh sách các mục trong field và bỏ dấu chọn ở những check-box bên trái mỗi Calculated Item này.

BNTT · 20/7/08

Chương 14 - Phân tích dữ liệu với PivotTable

14.6. Sử dụng kết quả của PivotTable trong công thức của bảng tính

Bạn phải làm gì khi bạn muốn đưa một kết quả của PivotTable vào trong một công thức thông thường? Ví dụ, muốn đưa một tham chiếu dẫn tới một ô trong vùng dữ liệu của PivotTable? Điều này có thể làm được, nhưng nó chỉ có tác dụng nếu PivotTable đó là "tĩnh" và không bao giờ thay đổi. Còn trong đa số trường hợp thì tham chiếu dẫn tới một ô trong vùng dữ liệu của PivotTable sẽ không làm việc bởi vì các giá trị của PivotTable thay đổi mỗi khi bạn xoay dữ liệu, lọc dữ liệu, kết nhóm, làm mới PivotTable, v.v...

Nếu bạn muốn đưa một kết quả PivotTable vào trong một công thức, và muốn kết quả đó vẫn chính xác ngay cả khi bạn xử lý PivotTable, bạn hãy sử dụng hàm GETPIVOTDATA() của Excel. Hàm này sử dụng Data field của PivotTable và một hoặc nhiều cặp field/item để xác định chính xác giá trị bạn muốn sử dụng. Đây là cú pháp của nó:

GETPIVOTDATA (data_field, pivot_table [, field1, item1]...])

Lưu ý rằng bạn phải luôn luôn nhập các đối số field1, item1, field2, item2... theo từng cặp. Trong trường hợp bạn bỏ qua cặp đối số này, GETPIVOTDATA() sẽ trả về Grand Total của PivotTable. Bạn có thể nhập đến 128 cặp field/item vào trong hàm, và điều này có thể làm cho bạn nghĩ rằng bạn phải mất thời gian với GETPIVOTDATA() quá nhiều so với giá trị bạn nhận được? Không phải như vậy, vì bạn sẽ hiếm khi phải tự nhập hàm này bằng tay. Theo mặc định, Excel được cấu hình để tự động tạo cú pháp thích hợp cho GETPIVOTDATA(), nghĩa là bạn chỉ cần bắt đầu công thức của bạn, và đến chỗ mà bạn cần có một giá trị PivotTable, bạn chỉ việc chọn giá trị đó, (dĩ nhiên là nhấn chọn ở trong PivotTable) và Excel chèn hàm GETPIVOTDATA vào với đầy đủ cú pháp dùm cho bạn.

Ví dụ, xem hình 14.19, bạn có thể thấy rằng tôi bắt đầu công thức ở ô F5, rồi nhấn vào ô B5 trong PivotTable, và Excel tự động tạo cho tôi hàm GETPIVOTDATA().

Hình 14.19. Khi bạn nhập một công thức, nhấn vào một ô trong Data Field của PivotTable,
Excel sẽ tự động tạo hàm GETPIVOTDATA() tương ứng cho bạn

Nếu Excel không tự động tạo hàm GETPIVOTDATA(), là do chức năng này đã bị tắt. Bạn mở nó lên lại bằng cách chọn Formulas trong Excel Options, và kích hoạt check-box Use GetPivotData Functions for PivotTable References.

Bạn cũng có thể dùng một VBA procedure để điều chỉnh chức năng tự động chèn hàm GETPIVOTDATA tắt và mở, bằng cách thiết lập property của Application.GenerateGetPivotData là True hay False, như ví dụ sau đây:

Sub ToggleGenerateGetPivotData()

With Application

.GenerateGetPivotData = Not .GenerateGetPivotData
End With
End Sub

------------------------------------ Hết chương 14 ------------------------------------

Chương 14. Phân tích dữ liệu với các PivotTable

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường