Làm việc với các Bảng Pivot trong Microsoft Excel

PivotTable là một trong những tính năng mạnh mẽ nhất của Microsoft Excel. Chúng cho phép một lượng lớn dữ liệu được phân tích và tóm tắt chỉ trong vài cú click chuột. Trong bài viết này, chúng tôi khám phá PivotTable, hiểu chúng là gì và tìm hiểu cách tạo và tùy chỉnh chúng.

Lưu ý: Bài viết này được viết bằng Excel 2010 (Beta). Khái niệm về PivotTable đã thay đổi rất ít qua nhiều năm, nhưng phương thức tạo một cái đã thay đổi trong gần như mỗi lần lặp lại của Excel. Nếu bạn đang sử dụng phiên bản Excel không phải là 2010, hãy chờ đợi các màn hình khác với các màn hình bạn thấy trong bài viết này.

Một ít lịch sử

Trong những ngày đầu của chương trình bảng tính, Lotus 1-2-3 đã cai trị con gà trống. Sự thống trị của nó đã hoàn thiện đến mức mọi người nghĩ rằng thật lãng phí thời gian để Microsoft bận tâm phát triển phần mềm bảng tính của riêng họ (Excel) để cạnh tranh với Lotus. Chuyển tiếp nhanh đến năm 2010 và sự thống trị của Excel trên thị trường bảng tính lớn hơn Lotus từng có, trong khi số lượng người dùng vẫn chạy Lotus 1-2-3 đang gần bằng không. Làm sao chuyện này lại xảy ra? Điều gì đã gây ra một sự đảo ngược mạnh mẽ của vận may?

Các nhà phân tích công nghiệp đặt nó xuống hai yếu tố: Thứ nhất, Lotus quyết định rằng nền tảng GUI mới lạ mắt này có tên là Windows Windows, là một mốt nhất thời sẽ không bao giờ tắt. Họ đã từ chối tạo phiên bản Windows của Lotus 1-2-3 (dù sao cũng được vài năm), dự đoán rằng phiên bản phần mềm DOS của họ là tất cả mọi người cần. Microsoft, tự nhiên, đã phát triển Excel dành riêng cho Windows. Thứ hai, Microsoft đã phát triển một tính năng cho Excel mà Lotus không cung cấp trong 1-2-3, cụ thể là PivotTable. Tính năng PivotTable, độc quyền cho Excel, được coi là hữu ích đến mức đáng kinh ngạc đến mức mọi người sẵn sàng tìm hiểu toàn bộ gói phần mềm mới (Excel) thay vì gắn bó với chương trình (1-2-3) không có. Tính năng này, cùng với sự đánh giá sai về thành công của Windows, là hồi chuông báo tử cho Lotus 1-2-3 và khởi đầu cho sự thành công của Microsoft Excel.

Hiểu PivotTable

Vậy PivotTable là gì, chính xác?

Nói một cách đơn giản, PivotTable là bản tóm tắt một số dữ liệu, được tạo để cho phép phân tích dễ dàng dữ liệu đã nói. Nhưng không giống như một bản tóm tắt được tạo thủ công, Excel PivotTable có thể tương tác. Khi bạn đã tạo một tài khoản, bạn có thể dễ dàng thay đổi nó nếu nó không cung cấp những hiểu biết chính xác về dữ liệu của bạn mà bạn đang hy vọng. Trong một vài lần nhấp, bản tóm tắt có thể được xoay vòng theo chiều cao - được xoay theo cách các tiêu đề cột trở thành tiêu đề hàng và ngược lại. Có rất nhiều điều có thể được thực hiện, quá. Thay vì cố gắng mô tả tất cả các tính năng của PivotTable, chúng tôi chỉ đơn giản là trình diễn chúng

Dữ liệu bạn phân tích bằng PivotTable không thể chỉ là bất kì dữ liệu - nó phải là nguyên dữ liệu, trước đây chưa được xử lý (chưa được xử lý) - thường là một danh sách sắp xếp nào đó. Một ví dụ về điều này có thể là danh sách các giao dịch bán hàng trong một công ty trong sáu tháng qua.

Kiểm tra dữ liệu hiển thị dưới đây:

Lưu ý rằng đây là không phải dữ liệu thô. Trong thực tế, nó đã là một bản tóm tắt của một số loại. Trong ô B3, chúng ta có thể thấy 30.000 đô la, dường như là tổng doanh số của James Cook trong tháng một. Vậy đâu là dữ liệu thô? Làm thế nào chúng ta đến con số 30.000 đô la? Danh sách ban đầu của các giao dịch bán hàng mà con số này được tạo ra từ đâu? Rõ ràng là ở đâu đó, một người nào đó đã gặp rắc rối khi đối chiếu tất cả các giao dịch bán hàng trong sáu tháng qua vào bản tóm tắt mà chúng ta thấy ở trên. Bạn cho rằng điều này mất bao lâu? Một tiếng? Mười?

Có lẽ là có, vâng. Bạn thấy đấy, bảng tính ở trên thực sự là không phải một PivotTable. Nó được tạo thủ công từ dữ liệu thô được lưu trữ ở nơi khác và thực sự phải mất vài giờ để biên dịch. Tuy nhiên, nó chính xác là loại tóm tắt có thể được tạo bằng PivotTable, trong trường hợp đó sẽ chỉ mất vài giây. Hãy cùng tìm hiểu

Nếu chúng ta theo dõi danh sách ban đầu của các giao dịch bán hàng, nó có thể trông giống như thế này:

Bạn có thể ngạc nhiên khi biết rằng, bằng cách sử dụng tính năng PivotTable của Excel, chúng tôi có thể tạo một bản tóm tắt doanh số hàng tháng tương tự như ở trên trong vài giây, chỉ với vài cú click chuột. Chúng ta có thể làm điều này - và nhiều hơn nữa!

Cách tạo PivotTable

Trước tiên, đảm bảo rằng bạn có một số dữ liệu thô trong bảng tính trong Excel. Danh sách các giao dịch tài chính là điển hình, nhưng nó có thể là danh sách mọi thứ: Chi tiết liên hệ của nhân viên, bộ sưu tập CD của bạn hoặc số liệu tiêu thụ nhiên liệu cho đội xe của công ty bạn.

Vì vậy, chúng tôi bắt đầu Exceliên và chúng tôi tải một danh sách như vậy

Khi chúng tôi mở danh sách trong Excel, chúng tôi đã sẵn sàng bắt đầu tạo PivotTable.

Nhấp vào bất kỳ một ô nào trong danh sách:

Sau đó, từ Chèn bấm vào tab PivotTable biểu tượng:

Các Tạo PivotTable hộp xuất hiện, hỏi bạn hai câu hỏi: PivotTable mới của bạn nên dựa trên dữ liệu nào và nên tạo nó ở đâu? Vì chúng tôi đã nhấp vào một ô trong danh sách (ở bước trên), toàn bộ danh sách xung quanh ô đó đã được chọn cho chúng tôi ($ A $ 1: $ G $ 88 trên Thanh toán tờ, trong ví dụ này). Lưu ý rằng chúng ta có thể chọn một danh sách trong bất kỳ khu vực nào khác của bất kỳ bảng tính nào khác, hoặc thậm chí một số nguồn dữ liệu ngoài, chẳng hạn như bảng cơ sở dữ liệu Access hoặc thậm chí bảng cơ sở dữ liệu MS-SQL Server. Chúng tôi cũng cần chọn xem chúng tôi có muốn PivotTable mới của mình được tạo trên một Mới bảng tính hoặc trên một hiện có một. Trong ví dụ này, chúng tôi sẽ chọn một Mới một:

Bảng tính mới được tạo cho chúng tôi và PivotTable trống được tạo trên bảng tính đó:

Một hộp khác cũng xuất hiện: Danh sách trường PivotTable. Danh sách trường này sẽ được hiển thị bất cứ khi nào chúng tôi nhấp vào bất kỳ ô nào trong PivotTable (ở trên):

Danh sách các trường trong phần trên cùng của hộp thực sự là tập hợp các tiêu đề cột từ bảng dữ liệu thô ban đầu. Bốn hộp trống ở phần dưới của màn hình cho phép chúng tôi chọn cách chúng tôi muốn PivotTable của mình để tóm tắt dữ liệu thô. Cho đến nay, không có gì trong các hộp đó, vì vậy PivotTable trống. Tất cả những gì chúng ta cần làm là kéo các trường xuống từ danh sách trên và thả chúng vào các hộp bên dưới. PivotTable sau đó được tạo tự động để phù hợp với hướng dẫn của chúng tôi. Nếu chúng tôi hiểu sai, chúng tôi chỉ cần kéo các trường trở lại nơi chúng đến và / hoặc kéo Mới các lĩnh vực xuống để thay thế chúng.

Các Giá trị hộp được cho là quan trọng nhất trong bốn. Trường được kéo vào hộp này biểu thị dữ liệu cần được tóm tắt theo một cách nào đó (bằng cách tính tổng, tính trung bình, tìm mức tối đa, tối thiểu, v.v.). Nó hầu như luôn luôn số dữ liệu. Một ứng cử viên hoàn hảo cho ô này trong dữ liệu mẫu của chúng tôi là trường / cột Số tiền ăn tối. Hãy kéo trường đó vào Giá trị cái hộp:

Lưu ý rằng (a) trường Số lượng tiền trong danh sách các trường hiện đã được đánh dấu và Tổng Sum của Số tiền đã được thêm vào Giá trị hộp, chỉ ra rằng cột số lượng đã được tính tổng.

Nếu chúng tôi kiểm tra chính PivotTable, chúng tôi thực sự tìm thấy tổng của tất cả các giá trị Số tiền trong các bảng dữ liệu thô:

Chúng tôi đã tạo PivotTable đầu tiên của chúng tôi! Tiện dụng, nhưng không đặc biệt ấn tượng. Có vẻ như chúng ta cần một cái nhìn sâu sắc hơn về dữ liệu của mình hơn thế.

Đề cập đến dữ liệu mẫu của chúng tôi, chúng tôi cần xác định một hoặc nhiều tiêu đề cột mà chúng tôi có thể sử dụng để phân chia tổng số này. Ví dụ: chúng tôi có thể quyết định rằng chúng tôi muốn xem tóm tắt dữ liệu của chúng tôi nơi chúng tôi có tiêu đề hàng cho mỗi nhân viên bán hàng khác nhau trong công ty chúng tôi và tổng cộng cho mỗi người. Để đạt được điều này, tất cả những gì chúng ta cần làm là kéo trường của Sal Salersonerson vào Nhãn hàng cái hộp:

Hiện nay, cuối cùng, mọi thứ bắt đầu trở nên thú vị! PivotTable của chúng tôi bắt đầu có hình dạng .

Với một vài lần nhấp, chúng tôi đã tạo ra một bảng mà phải mất một thời gian dài để làm thủ công.

Vậy chúng ta có thể làm gì khác? Chà, theo một nghĩa nào đó, PivotTable của chúng tôi đã hoàn tất. Chúng tôi đã tạo một bản tóm tắt hữu ích về dữ liệu nguồn của chúng tôi. Những thứ quan trọng đã được học! Đối với phần còn lại của bài viết, chúng tôi sẽ kiểm tra một số cách có thể tạo các PivotTable phức tạp hơn và các cách mà các PivotTable đó có thể được tùy chỉnh.

Đầu tiên, chúng ta có thể tạo một hai-bảng chiều. Chúng ta hãy làm điều đó bằng cách sử dụng Phương thức thanh toán trên mạng. Đơn giản chỉ cần kéo Phương thức thanh toán trên mạng vào tiêu đề Nhãn cột cái hộp:

Trông như thế này:

Bắt đầu nhận rất mát mẻ!

Hãy làm cho nó một số ba-bảng chiều. Một cái bàn như vậy có thể trông như thế nào? Được rồi để xem…

Kéo cột / tiêu đề của Gói Gói vào Bộ lọc báo cáo cái hộp:

Chú ý nơi nó kết thúc .

Điều này cho phép chúng tôi lọc báo cáo của chúng tôi dựa trên gói mà kỳ nghỉ lễ được mua. Ví dụ: chúng ta có thể thấy sự cố của nhân viên bán hàng so với phương thức thanh toán cho tất cả các các gói, hoặc, với một vài lần nhấp, hãy thay đổi nó để hiển thị bảng phân tích tương tự cho gói của Sun Sunekekers:

Và vì vậy, nếu bạn nghĩ về nó đúng cách, PivotTable của chúng tôi giờ là ba chiều. Hãy tiếp tục tùy chỉnh

Nếu nó bật ra, nói rằng chúng ta chỉ muốn nhìn thấy kiểm tra và thẻ tín dụng giao dịch (nghĩa là không có giao dịch tiền mặt), sau đó chúng tôi có thể bỏ chọn mục Mặt tiền Cash trên các tiêu đề cột. Nhấp vào mũi tên thả xuống bên cạnh Nhãn cột, và bỏ chọn

Chúng ta hãy xem những gì trông giống như bạn Như bạn có thể thấy, Cash Cash đã biến mất.

Định dạng

Đây rõ ràng là một hệ thống rất mạnh mẽ, nhưng cho đến nay kết quả trông rất đơn giản và nhàm chán. Để bắt đầu, những con số mà chúng tôi tổng hợp không giống như số tiền - chỉ là những số cũ đơn giản. Hãy khắc phục điều đó.

Một sự cám dỗ có thể là làm những gì chúng ta đã từng làm trong những trường hợp như vậy và chỉ cần chọn toàn bộ bảng (hoặc toàn bộ bảng tính) và sử dụng các nút định dạng số tiêu chuẩn trên thanh công cụ để hoàn thành định dạng. Vấn đề với cách tiếp cận đó là nếu bạn từng thay đổi cấu trúc của PivotTable trong tương lai (có khả năng là 99%), thì các định dạng số đó sẽ bị mất. Chúng ta cần một cách sẽ làm cho chúng (bán) vĩnh viễn.

Đầu tiên, chúng tôi xác định vị trí mục nhập Sum Sum của Số tiền trong mục Giá trị và bấm vào nó Một menu xuất hiện. Chúng tôi chọn Cài đặt trường giá trị từ menu:

Các Cài đặt trường giá trị hộp xuất hiện.

Nhấn vào Định dạng số nút và tiêu chuẩn Hộp định dạng ô xuất hiện:

Từ thể loại danh sách, chọn (nói) Kế toán, và giảm số vị trí thập phân xuống 0. Nhấp vào được một vài lần để quay lại PivotTable

Như bạn có thể thấy, các số đã được định dạng chính xác dưới dạng số tiền.

Trong khi chúng ta đang ở chủ đề định dạng, hãy định dạng toàn bộ PivotTable. Có một số cách để làm điều này. Hãy sử dụng một đơn giản

Nhấn vào Công cụ / Thiết kế PivotTable chuyển hướng:

Sau đó thả xuống mũi tên ở dưới cùng bên phải của Kiểu PivotTable danh sách để xem một bộ sưu tập lớn các kiểu dựng sẵn:

Chọn bất kỳ cái nào hấp dẫn và xem kết quả trong PivotTable của bạn:

Sự lựa chọn khác

Chúng tôi có thể làm việc với ngày là tốt. Bây giờ thông thường, có rất nhiều, rất nhiều ngày trong danh sách giao dịch, chẳng hạn như ngày chúng tôi bắt đầu. Nhưng Excel cung cấp tùy chọn để nhóm các mục dữ liệu lại với nhau theo ngày, tuần, tháng, năm, v.v. Hãy xem cách thực hiện.

Trước tiên, hãy xóa cột Phương thức thanh toán trên mạng ra khỏi cột Nhãn cột hộp (chỉ cần kéo nó trở lại danh sách trường) và thay thế nó bằng cột Ngày đặt trước Ngày đặt trước:

Như bạn có thể thấy, điều này làm cho PivotTable của chúng tôi ngay lập tức trở nên vô dụng, cung cấp cho chúng tôi một cột cho mỗi ngày xảy ra giao dịch - một bảng rất rộng!

Để khắc phục điều này, nhấp chuột phải vào bất kỳ ngày nào và chọn Nhóm… từ menu ngữ cảnh:

Hộp nhóm xuất hiện. Chúng tôi chọn Tháng và bấm OK:

Voila! Một nhiều bảng hữu ích hơn:

(Ngẫu nhiên, bảng này gần như giống hệt với bảng được hiển thị ở đầu bài viết này - bản tóm tắt doanh số ban đầu được tạo thủ công.)

Một điều thú vị khác cần lưu ý là bạn có thể có nhiều hơn một bộ tiêu đề hàng (hoặc tiêu đề cột):

Có vẻ như thế này .

Bạn có thể làm điều tương tự với các tiêu đề cột (hoặc thậm chí các bộ lọc báo cáo).

Giữ mọi thứ đơn giản trở lại, hãy xem cách vẽ tính trung bình các giá trị, thay vì các giá trị tổng.

Đầu tiên, nhấp vào Sum Sum của Số tiền, và chọn Cài đặt trường giá trị từ menu ngữ cảnh xuất hiện:

bên trong Tóm tắt trường giá trị bằng danh sách trong Cài đặt trường giá trị hộp, chọn Trung bình cộng:

Trong khi chúng ta ở đây, hãy thay đổi Tên tùy chỉnh, từ mức trung bình của số tiền đối với một số thứ ngắn gọn hơn. Nhập vào một cái gì đó giống như trung bình đỉnh điểm:

Nhấp chuột được, và xem nó trông như thế nào Lưu ý rằng tất cả các giá trị thay đổi từ tổng cộng thành trung bình và tiêu đề bảng (ô trên cùng bên trái) đã thay đổi thành Trung bình Hồi quy:

Nếu chúng ta thích, chúng ta thậm chí có thể có tổng, trung bình và tổng (đếm = có bao nhiêu doanh số) trên tất cả trên cùng một PivotTable!

Dưới đây là các bước để có được một cái gì đó tương tự (bắt đầu từ một PivotTable trống):

Kéo Salesperson vào trong Nhãn cột
Kéo trường Số tiền vào trường xuống Giá trị hộp ba lần
Đối với trường Số tiền đầu tiên, hãy thay đổi tên tùy chỉnh của nó thành Định dạng Tổng và định dạng số thành Kế toán (0 chữ số thập phân)
Đối với trường Số tiền thứ hai, hãy đổi tên tùy chỉnh của nó thành Giá trị trung bình, chức năng của nó thành Trung bình cộng và nó là định dạng số để Kế toán (0 chữ số thập phân)
Đối với trường Số tiền thứ ba, hãy đổi tên thành Trường Count Count và chức năng của nó thành Đếm
Kéo tự động tạo lĩnh vực từ Nhãn cột đến Nhãn hàng

Đây là những gì chúng tôi kết thúc với:

Tổng, trung bình và tính trên cùng một PivotTable!

Phần kết luận

Có rất nhiều, rất nhiều tính năng và tùy chọn khác cho PivotTable được tạo bởi Microsoft Excel - quá nhiều thứ để liệt kê trong một bài viết như thế này. Để bao quát đầy đủ tiềm năng của PivotTable, sẽ cần một cuốn sách nhỏ (hoặc một trang web lớn). Những người đọc dũng cảm và / hoặc táo bạo có thể khám phá PivotTable khá dễ dàng: Chỉ cần nhấp chuột phải vào mọi thứ và xem những tùy chọn nào có sẵn cho bạn. Ngoài ra còn có hai tab ruy băng: Tùy chọn / Công cụ PivotTable và Thiết kế. Sẽ không có vấn đề gì nếu bạn mắc lỗi - thật dễ dàng để xóa PivotTable và bắt đầu lại - một khả năng người dùng DOS cũ của Lotus 1-2-3 không bao giờ có.

Nếu bạn đang làm việc trong Office 2007, bạn có thể muốn xem bài viết của chúng tôi về cách tạo PivotTable trong Excel 2007.

Chúng tôi đã bao gồm một sổ làm việc Excel mà bạn có thể tải xuống để thực hành các kỹ năng PivotTable của mình. Nó nên hoạt động với tất cả các phiên bản Excel từ 97 trở đi.

Tải xuống Workbook Excel thực hành của chúng tôi