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 phân tích một lượng lớn dữ liệu và tóm tắt chỉ trong vài cú click chuột. Trong bài này, chúng tôi khám phá PivotTables, 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 pháp tạo một PivotTable đã 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à năm 2010, hãy mong đợi các màn hình khác với những 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 các chương trình bảng tính, Lotus 1-2-3 đã cai trị. Sự thống trị của nó đã quá hoàn chỉnh đến mức mọi người nghĩ rằng đó là một sự lãng phí thời gian để Microsoft làm phiền phát triển phần mềm bảng tính của riêng họ (Excel) để cạnh tranh với Lotus. Flash về phía trước đến năm 2010 và sự thống trị của Excel đối với thị trường bảng tính lớn hơn Lotus bao giờ hết, 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 sự đảo ngược đáng kể của vận may?
Các nhà phân tích ngành 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ạ này được gọi là "Windows" là một mốt đi qua mà sẽ không bao giờ cất cánh. Họ từ chối tạo ra một phiên bản Windows của Lotus 1-2-3 (trong một vài năm, dù sao), dự đoán rằng phiên bản DOS của họ về phần mềm là tất cả những ai 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 PivotTables, độc quyền đối với Excel, được cho là đáng kinh ngạc đến nỗi 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 một chương trình (1-2-3) mà không có nó. Một tính năng này, cùng với sự đánh giá sai lệch về thành công của Windows, là cái chết cho Lotus 1-2-3, và sự khởi đầu của sự thành công của Microsoft Excel.
Hiểu về PivotTable
Vậy PivotTable là gì, chính xác?
Nói một cách đơn giản, PivotTable là một bản tóm tắt của một số dữ liệu, được tạo ra để 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 PivotTables 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 thông tin chi tiết chính xác về dữ liệu mà bạn đang hy vọng. Trong một vài cú nhấp chuột, bản tóm tắt có thể được "xoay vòng" - được xoay theo cách mà các tiêu đề cột trở thành các đề mục hàng và ngược lại. Còn rất nhiều thứ nữa có thể được thực hiện. Thay vì cố gắng mô tả tất cả các tính năng của PivotTable, chúng tôi sẽ chỉ đơn giản là trình bày chúng…
Dữ liệu mà bạn phân tích bằng cách sử dụng PivotTable không thể chỉ bất kì dữ liệu - nó phải là thô tục dữ liệu, trước đây chưa được xử lý (chưa được khai báo) - thường là danh sách một số loại. 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 được hiển thị bên dưới:
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, mà rõ ràng là tổng doanh thu của James Cook trong tháng Giêng. Vậy dữ liệu thô ở đâu? Làm cách nào chúng tôi đạt đến con số 30.000 đô la? Danh sách giao dịch bán hàng ban đầu mà con số này được tạo 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 tóm tắt mà chúng tôi thấy ở trên. Bạn cho rằng điều này mất bao lâu? Một giờ? Mười?
Có lẽ, có. Bạn thấy đấy, bảng tính ở trên thực sự không phải một PivotTable. Nó được tạo ra thủ công từ dữ liệu thô được lưu trữ ở nơi khác, và nó đã thực sự mất một vài giờ để biên dịch. Tuy nhiên, đó chính là loại tóm tắt có thể được tạo bằng cách sử dụng PivotTable, trong trường hợp đó, nó sẽ chỉ mất vài giây. Hãy tìm hiểu cách…
Nếu chúng tôi theo dõi danh sách giao dịch bán hàng ban đầu, nó có thể trông giống như sau:
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 bản tóm tắt bán hàng hàng tháng tương tự như tóm tắt ở 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 thế nữa!
Cách tạo PivotTable
Trước tiên, hãy đảm bảo rằng bạn có một số dữ liệu thô trong một trang 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 đĩa CD 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 Excel … và chúng tôi tải danh sách như vậy …
Nhấp vào bất kỳ ô nào trong danh sách:
Sau đó, từ Chèn, nhấp vào 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 sẽ dựa trên dữ liệu nào và nó sẽ được tạo ở đâu? Vì chúng tôi đã nhấp vào một ô trong danh sách (trong 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 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ỳ vùng nào khác của bất kỳ trang 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í một bảng cơ sở dữ liệu MS-SQL Server. Chúng ta cũng cần phải chọn xem chúng ta có muốn tạo PivotTable mới trên một Mới trang tính hoặc trên hiện tại một. Trong ví dụ này, chúng ta sẽ chọn Mới một:
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 ta nhấp vào bất kỳ ô nào trong PivotTable (ở trên):
Danh sách các trường ở 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ừ trang tính 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 chúng tôi 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 để khớp với các hướng dẫn của chúng tôi. Nếu chúng tôi sai, chúng tôi chỉ cần kéo các trường trở về vị trí của chúng và / hoặc kéo Mới 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 đại diện cho dữ liệu cần được tóm tắt theo cách nào đó (bằng cách tính tổng, lấy trung bình, tìm tối đa, tối thiểu, v.v.). Nó hầu như luôn luôn số học dữ liệu. Một ứng cử viên hoàn hảo cho hộp này trong dữ liệu mẫu của chúng tôi là trường / cột "Số lượng". Hãy kéo trường đó vào Giá trị cái hộp:
Lưu ý rằng (a) trường “Số tiền” trong danh sách các trường hiện được chọn, và “Tổng số tiền” đã được thêm vào Giá trị hộp, chỉ ra rằng cột số tiền đã được tổng kết.
Nếu chúng ta kiểm tra chính PivotTable, chúng ta thực sự tìm thấy tổng của tất cả các giá trị “Amount” từ trang tính dữ liệu 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 của chúng tôi và tổng số cho mỗi người bán hàng. Để đạt được điều này, tất cả những gì chúng ta cần làm là kéo trường “Người bán hàng” 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ậy chúng ta có thể làm gì khác? Vâng, theo một nghĩa nào đó, PivotTable của chúng ta đã 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ẽ xem xét một số cách mà PivotTable có thể được tạo 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 hai-bảng chiều. Hãy làm điều đó bằng cách sử dụng "Phương thức thanh toán" làm tiêu đề cột. Chỉ cần kéo tiêu đề "Phương thức thanh toán" vào Nhãn cột cái hộp:
Bắt đầu để có được rất mát mẻ!
Hãy biến nó thành số babảng hai chiều. Cái bàn như thế có thể trông như thế nào? Được rồi để xem…
Kéo cột / tiêu đề "Gói" vào Bộ lọc báo cáo cái hộp:
Đ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 kỳ nghỉ" đã được mua. Ví dụ: chúng tôi có thể xem bảng phân tích về nhân viên bán hàng so với phương thức thanh toán cho tất cả các gói, hoặc, với một vài lần nhấp chuột, thay đổi nó để hiển thị cùng một sự cố cho gói "Sunseekers":
Nếu nó quay ra, nói rằng, chúng tôi chỉ muốn xem kiểm tra và thẻ tín dụng giao dịch (ví dụ: không có giao dịch tiền mặt), sau đó chúng tôi có thể bỏ chọn mục "Tiền mặt" từ các tiêu đề cột. Nhấp vào mũi tên thả xuống bên cạnh Nhãn cộtvà "Tiền mặt" chưa được xử lý:
Định dạng
Điều nà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, các con số mà chúng tôi tổng hợp không giống như số tiền đô la - chỉ là số cũ đơn giản. Hãy khắc phục điều đó.
Một cám dỗ có thể là làm những gì chúng ta thường làm trong những hoàn cảnh 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ố chuẩn trên thanh công cụ để hoàn thành việc định dạng. Vấn đề với cách tiếp cận đó là nếu bạn 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 mà sẽ làm cho chúng (bán) vĩnh viễn.
Trước tiên, chúng tôi xác định mục nhập "Tổng số tiền" trong 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ừ trình đơn:
Các Cài đặt trường giá trị hộp xuất hiện.
Nhấn vào Định dạng số và tiêu chuẩn Ô Định dạng Ô xuất hiện:
Từ thể loại danh sách, chọn (nói) Kế toánvà thả số vị trí thập phân xuống 0. Nhấp được một vài lần để quay lại PivotTable…
Trong khi chúng tôi đang ở trong 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 cách đơn giản…
Nhấn vào Công cụ / thiết kế PivotTable chuyển hướng:
Sau đó thả mũi tên xuống dưới cùng bên phải của Kiểu PivotTable danh sách để xem một bộ sưu tập phong phú các kiểu dựng sẵn:
Sự lựa chọn khác
Chúng tôi cũng có thể làm việc với ngày tháng. Thông thường, có rất nhiều, nhiều ngày trong một danh sách giao dịch như danh sách 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 với nhau theo ngày, tuần, tháng, năm, v.v. Hãy xem cách thực hiện điều này.
Trước tiên, hãy xóa cột "Phương thức thanh toán" khỏi Nhãn cột (chỉ cần kéo nó trở lại danh sách trường) và thay thế nó bằng cột “Ngày được đặt trước”:
Để khắc phục điều này, hã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 sẽ xuất hiện. Chúng tôi chọn Tháng và nhấn OK:
Thì đấy! A nhiều bảng hữu ích hơn:
Một điều thú vị khác cần lưu ý là bạn có thể có nhiều nhóm tiêu đề hàng (hoặc tiêu đề cột):
Giữ lại mọi thứ đơn giản, hãy xem cách vẽ trung bình các giá trị, thay vì các giá trị tổng hợp.
Đầu tiên, nhấp vào “Tổng 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ị theo danh sách trong Cài đặt trường giá trị hộp, chọn Trung bình cộng:
Trong khi chúng tôi ở đây, hãy thay đổi Tên tùy chỉnh, từ “Mức trung bình của số tiền” đến một cái gì đó ngắn gọn hơn một chút. Nhập một cái gì đó như "Tr.bình":
Nhấp chuột đượcvà xem nó trông như thế nào. Lưu ý rằng tất cả các giá trị thay đổi từ tổng số tổng cộng thành mức trung bình và tiêu đề bảng (ô trên cùng bên trái) đã thay đổi thành “Tr.bình”:
Dưới đây là các bước để có được một cái gì đó như thế tại chỗ (bắt đầu từ một PivotTable trống):
- Kéo "Nhân viên bán hàng" vào Nhãn cột
- Kéo trường “Số tiền” xuống Giá trị hộp ba lần
- Đối với trường “Số tiền” đầu tiên, hãy đổi tên tùy chỉnh thành “Tổng số” 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 thành “Trung bình”, chức năng của nó thành Trung bình cộng và đó 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, đổi tên thành “Đếm” và chức năng của nó thành Đếm
-
Kéo tạo tự động
Image trường từ Nhãn cột đến Nhãn hàng
Dưới đây là những gì chúng tôi kết thúc với:
Phần kết luận
Có rất nhiều tính năng và tùy chọn cho PivotTable được tạo bởi Microsoft Excel - quá nhiều để 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, một cuốn sách nhỏ (hoặc một trang web lớn) sẽ được yêu cầu. Người đọc dũng cảm và / hoặc geeky có thể khám phá PivotTables dễ dàng hơn nữa: 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 ribbon: Công cụ / tùy chọn PivotTable và Thiết kế. Việc bạn mắc lỗi là không quan trọng - thật dễ dàng để xóa PivotTable và bắt đầu lại - khả năng người dùng DOS cũ của Lotus 1-2-3 chưa 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 sổ làm việc Excel mà bạn có thể tải xuống để thực hành kỹ năng PivotTable của mình. Nó sẽ hoạt động với tất cả các phiên bản Excel từ 97 trở đi.
Tải xuống Sổ thực hành Excel của chúng tôi
