Nội dung
Các bảng Pivot là một trong những công cụ mạnh mẽ nhất trong Excel. Chúng cho phép bạn phân tích và tổng hợp các bản tóm tắt khác nhau của một lượng lớn dữ liệu chỉ với một vài cú nhấp chuột. Trong bài viết này, chúng ta sẽ làm quen với bảng tổng hợp, hiểu chúng là gì, tìm hiểu cách tạo và tùy chỉnh chúng.
Bài viết này được viết bằng Excel 2010. Khái niệm về PivotTables không thay đổi nhiều trong những năm qua, nhưng cách bạn tạo chúng hơi khác trong mỗi phiên bản Excel mới. Nếu bạn có phiên bản Excel không phải 2010, thì hãy chuẩn bị rằng ảnh chụp màn hình trong bài viết này sẽ khác với những gì bạn thấy trên màn hình.
Một chút về lịch sử
Trong những ngày đầu của phần mềm bảng tính, quả bóng quy tắc Lotus 1-2-3. Sự thống trị của nó đã hoàn toàn đến mức những nỗ lực của Microsoft để phát triển phần mềm của riêng mình (Excel) để thay thế cho Lotus dường như là một sự lãng phí thời gian. Bây giờ nhanh chóng chuyển tiếp đến năm 2010! Excel thống trị bảng tính nhiều hơn so với mã Lotus từng làm trong lịch sử của nó và số người vẫn sử dụng Lotus gần bằng không. Làm sao điều này xảy ra được? Lý do của sự thay đổi kịch tính như vậy là gì?
Các nhà phân tích xác định hai yếu tố chính:
- Đầu tiên, Lotus quyết định rằng nền tảng GUI mới có tên Windows này chỉ là một mốt nhất thời sẽ không tồn tại lâu. Họ từ chối xây dựng phiên bản Windows Lotus 1-2-3 (nhưng chỉ trong một vài năm), dự đoán rằng phiên bản DOS của phần mềm của họ sẽ là tất cả những gì người tiêu dùng cần. Microsoft đã tự nhiên phát triển Excel đặc biệt cho Windows.
- Thứ hai, Microsoft đã giới thiệu một công cụ trong Excel được gọi là PivotTables mà không có sẵn trong Lotus 1-2-3. PivotTables, dành riêng cho Excel, tỏ ra hữu ích đến mức mọi người có xu hướng gắn bó với bộ phần mềm Excel mới hơn là tiếp tục với Lotus 1-2-3, bộ phần mềm không có chúng.
PivotTables, cùng với việc đánh giá thấp sự thành công của Windows nói chung, đã đóng vai trò khai tử cho Lotus 1-2-3 và mở ra thành công của Microsoft Excel.
Bảng tổng hợp là gì?
Vì vậy, cách tốt nhất để mô tả PivotTables là gì?
Nói một cách dễ hiểu, bảng tổng hợp là bản tóm tắt của một số dữ liệu, được tạo ra để tạo điều kiện thuận lợi cho việc phân tích dữ liệu này. Không giống như các tổng được tạo theo cách thủ công, Excel PivotTables có tính tương tác. Sau khi được tạo, bạn có thể dễ dàng sửa đổi chúng nếu chúng không đưa ra hình ảnh mà bạn mong đợi. Chỉ với một vài cú nhấp chuột, tổng số có thể được lật để các tiêu đề cột trở thành tiêu đề hàng và ngược lại. Bạn có thể làm nhiều việc với bảng tổng hợp. Thay vì cố gắng mô tả bằng lời tất cả các tính năng của bảng tổng hợp, việc chứng minh nó trong thực tế sẽ dễ dàng hơn…
Dữ liệu bạn phân tích bằng PivotTables không thể là ngẫu nhiên. Nó phải là dữ liệu thô thô, giống như một danh sách nào đó. Ví dụ, đó có thể là danh sách doanh số bán hàng của công ty trong sáu tháng qua.
Nhìn vào dữ liệu được hiển thị trong hình dưới đây:
Lưu ý rằng đây không phải là dữ liệu thô thô, vì nó đã được tóm tắt. Trong ô B3, chúng ta thấy $ 30000, có thể là tổng kết quả mà James Cook đã làm vào tháng Giêng. Sau đó dữ liệu gốc ở đâu? Con số $ 30000 đến từ đâu? Danh sách ban đầu của doanh số mà tổng số hàng tháng này được lấy từ đâu? Rõ ràng là ai đó đã làm rất tốt trong việc tổ chức và sắp xếp tất cả dữ liệu bán hàng trong sáu tháng qua và biến nó thành bảng tổng số mà chúng ta thấy. Bạn nghĩ nó mất bao lâu? Giờ? Mười giờ?
Thực tế là bảng trên không phải là bảng tổng hợp. Nó được làm thủ công từ dữ liệu thô được lưu trữ ở nơi khác và mất ít nhất vài giờ để xử lý. Chỉ một bảng tóm tắt như vậy có thể được tạo bằng cách sử dụng bảng tổng hợp chỉ trong vài giây. Hãy tìm ra cách…
Nếu chúng ta quay trở lại danh sách bán hàng ban đầu, nó sẽ giống như sau:
Bạn có thể ngạc nhiên rằng từ danh sách giao dịch này với sự trợ giúp của bảng tổng hợp và chỉ trong vài giây, chúng tôi có thể tạo báo cáo bán hàng hàng tháng trong Excel, mà chúng tôi đã phân tích ở trên. Vâng, chúng tôi có thể làm điều đó và hơn thế nữa!
Làm thế nào để tạo một bảng tổng hợp?
Trước tiên, hãy đảm bảo rằng bạn có một số dữ liệu nguồn trong trang tính Excel. Danh sách các giao dịch tài chính là điển hình nhất xảy ra. Trên thực tế, đó có thể là danh sách bất cứ thứ gì: chi tiết liên hệ của nhân viên, bộ sưu tập đĩa CD hoặc dữ liệu tiêu thụ nhiên liệu của công ty bạn.
Vì vậy, chúng tôi khởi động Excel… và tải một danh sách như vậy…
Sau khi chúng tôi đã mở danh sách này trong Excel, chúng tôi có thể bắt đầu tạo một bảng tổng hợp.
Chọn bất kỳ ô nào từ danh sách này:
Sau đó trên tab chèn (Chèn) chọn lệnh bảng tổng hợp (Bảng tổng hợp):
Một hộp thoại sẽ xuất hiện Tạo PivotTable (Tạo bảng tổng hợp) với hai câu hỏi dành cho bạn:
- Dữ liệu nào cần sử dụng để tạo bảng tổng hợp mới?
- Đặt bảng xoay ở đâu?
Vì trong bước trước, chúng ta đã chọn một trong các ô danh sách, nên toàn bộ danh sách sẽ được tự động chọn để tạo bảng tổng hợp. Lưu ý rằng chúng ta có thể chọn một phạm vi khác, một bảng khác và thậm chí một số nguồn dữ liệu bên ngoài như bảng cơ sở dữ liệu Access hoặc MS-SQL. Ngoài ra, chúng ta cần chọn vị trí đặt bảng tổng hợp mới: trên trang tính mới hoặc trên một trong những trang hiện có. Trong ví dụ này, chúng tôi sẽ chọn tùy chọn - Bảng tính mới (sang trang tính mới):
Excel sẽ tạo một trang tính mới và đặt một bảng tổng hợp trống trên đó:
Ngay sau khi chúng tôi nhấp vào bất kỳ ô nào trong bảng tổng hợp, một hộp thoại khác sẽ xuất hiện: Danh sách trường PivotTable (Các trường bảng tổng hợp).
Danh sách các trường ở đầu hộp thoại là danh sách tất cả các tiêu đề từ danh sách ban đầu. Bốn vùng trống ở cuối màn hình cho phép bạn cho PivotTable biết cách bạn muốn tóm tắt dữ liệu. Miễn là những khu vực này trống, cũng không có gì trong bảng. Tất cả những gì chúng ta phải làm là kéo các tiêu đề từ khu vực trên cùng xuống các khu vực trống bên dưới. Đồng thời, bảng tổng hợp được tạo tự động theo hướng dẫn của chúng tôi. Nếu chúng tôi làm sai, chúng tôi có thể xóa các tiêu đề khỏi khu vực dưới cùng hoặc kéo những người khác để thay thế chúng.
Khu vực Các giá trị (Ý nghĩa) có lẽ là quan trọng nhất trong bốn. Tiêu đề nào được đặt trong khu vực này xác định dữ liệu nào sẽ được tóm tắt (tổng, trung bình, tối đa, tối thiểu, v.v.) Đây hầu như luôn là các giá trị số. Một ứng cử viên xuất sắc cho một vị trí trong lĩnh vực này là dữ liệu dưới tiêu đề Số tiền (Chi phí) của bảng gốc của chúng tôi. Kéo tiêu đề này vào khu vực Các giá trị (Giá trị):
Xin lưu ý rằng tiêu đề Số tiền hiện được đánh dấu bằng dấu kiểm và trong khu vực Các giá trị (Giá trị) một mục đã xuất hiện Tổng số tiền (Trường số lượng Số tiền), chỉ ra rằng cột Số tiền tóm tắt.
Nếu chúng ta nhìn vào bảng tổng hợp, chúng ta sẽ thấy tổng của tất cả các giá trị từ cột Số tiền bảng gốc.
Vì vậy, bảng tổng hợp đầu tiên của chúng tôi đã được tạo! Thuận tiện, nhưng không phải là đặc biệt ấn tượng. Chúng tôi có thể muốn nhận được nhiều thông tin hơn về dữ liệu của chúng tôi hiện có.
Hãy chuyển sang dữ liệu ban đầu và cố gắng xác định một hoặc nhiều cột có thể được sử dụng để chia tổng này. Ví dụ: chúng ta có thể tạo bảng tổng hợp của mình theo cách mà tổng số tiền bán hàng được tính cho từng người bán riêng lẻ. Những thứ kia. các hàng sẽ được thêm vào bảng tổng hợp của chúng tôi với tên của từng nhân viên bán hàng trong công ty và tổng số tiền bán hàng của họ. Để đạt được kết quả này, chỉ cần kéo tiêu đề người bán hàng (đại diện bán hàng) đến khu vực Nhãn hàng (Dây):
Nó trở nên thú vị hơn! PivotTable của chúng tôi đang bắt đầu hình thành…
Xem những lợi ích? Trong một vài cú nhấp chuột, chúng tôi đã tạo một bảng mà có thể mất một thời gian dài để tạo theo cách thủ công.
Ta còn làm gì khác được nữa? Vâng, theo một nghĩa nào đó, bảng tổng hợp của chúng tôi đã sẵn sàng. Chúng tôi đã tạo một bản tóm tắt hữu ích về dữ liệu gốc. Thông tin quan trọng đã nhận được! Trong phần còn lại của bài viết này, chúng ta sẽ xem xét một số cách để tạo PivotTable phức tạp hơn, cũng như tìm hiểu cách tùy chỉnh chúng.
Thiết lập PivotTable
Đầu tiên, chúng ta có thể tạo một bảng tổng hợp hai chiều. Hãy làm điều này bằng cách sử dụng tiêu đề cột Phương thức thanh toán (Phương thức thanh toán). Chỉ cần kéo tiêu đề Phương thức thanh toán đến khu vực Nhãn cột (Cột):
Chúng tôi nhận được kết quả:
Trông rất tuyệt!
Bây giờ chúng ta hãy tạo một bảng ba chiều. Một cái bàn như vậy sẽ như thế nào? Hãy xem nào…
Kéo tiêu đề Đóng gói (Phức hợp) đến khu vực bộ lọc báo cáo (Bộ lọc):
Lưu ý xem anh ấy đang ở đâu…
Điều này cho chúng tôi cơ hội để lọc báo cáo trên cơ sở "Khu phức hợp kỳ nghỉ nào đã được thanh toán." Ví dụ: chúng tôi có thể xem bảng phân tích theo người bán và theo phương thức thanh toán cho tất cả các khu phức hợp hoặc trong một vài cú nhấp chuột, thay đổi chế độ xem của bảng tổng hợp và chỉ hiển thị bảng phân tích tương tự cho những người đã đặt hàng khu phức hợp người đi tìm nắng.
Vì vậy, nếu bạn hiểu điều này một cách chính xác, thì bảng xoay của chúng ta có thể được gọi là ba chiều. Hãy tiếp tục thiết lập…
Nếu đột nhiên chỉ thanh toán bằng séc và thẻ tín dụng (tức là thanh toán không dùng tiền mặt) được hiển thị trong bảng tổng hợp, thì chúng tôi có thể tắt hiển thị tiêu đề tiền mặt (Tiền mặt). Đối với điều này, bên cạnh Nhãn cột nhấp vào mũi tên xuống và bỏ chọn hộp trong menu thả xuống tiền mặt:
Hãy xem bảng tổng hợp của chúng ta trông như thế nào bây giờ. Như bạn thấy, cột tiền mặt biến mất khỏi cô ấy.
Định dạng PivotTables trong Excel
PivotTables rõ ràng là một công cụ rất mạnh, nhưng cho đến nay kết quả trông hơi đơn giản và nhàm chán. Ví dụ, những con số chúng tôi cộng lại trông không giống như số tiền - chúng chỉ là những con số. Hãy sửa lỗi này.
Thật hấp dẫn để làm những gì bạn đã quen trong tình huống như vậy và chỉ cần chọn toàn bộ bảng (hoặc toàn bộ trang tính) và sử dụng các nút định dạng số tiêu chuẩn trên thanh công cụ để đặt định dạng mong muốn. Vấn đề với cách tiếp cận này là nếu bạn thay đổi cấu trúc của bảng tổng hợp trong tương lai (điều này xảy ra với khả năng xảy ra là 99%), thì định dạng sẽ bị mất. Những gì chúng ta cần là một cách để làm cho nó (gần như) vĩnh viễn.
Đầu tiên, chúng ta hãy tìm mục nhập Tổng số tiền in Các giá trị (Giá trị) và nhấp vào nó. Trong menu xuất hiện, hãy chọn mục Cài đặt trường giá trị (Tùy chọn trường giá trị):
Một hộp thoại sẽ xuất hiện Cài đặt trường giá trị (Tùy chọn trường giá trị).
Ấn nút Định dạng số (Định dạng Số), một hộp thoại sẽ mở ra. Format Cells (định dạng ô):
Từ danh sách Phân loại (Định dạng số) chọn Kế toán (Tài chính) và đặt số chữ số thập phân thành XNUMX. Bây giờ nhấn một vài lần OKđể quay lại bảng tổng hợp của chúng tôi.
Như bạn có thể thấy, các con số được định dạng là số tiền đô la.
Trong khi chúng ta đang ở đó với định dạng, hãy thiết lập định dạng cho toàn bộ PivotTable. Có nhiều hướng khác nhau để làm điều đó. Chúng tôi sử dụng cái đơn giản hơn…
Nhấn vào Công cụ PivotTable: Thiết kế (Làm việc với PivotTables: Constructor):
Tiếp theo, mở rộng menu bằng cách nhấp vào mũi tên ở góc dưới bên phải của phần Kiểu PivotTable (Kiểu PivotTable) để xem bộ sưu tập phong phú các kiểu nội tuyến:
Chọn bất kỳ kiểu phù hợp nào và xem kết quả trong bảng tổng hợp của bạn:
Cài đặt PivotTable khác trong Excel
Đôi khi bạn cần lọc dữ liệu theo ngày tháng. Ví dụ, trong danh sách giao dịch của chúng tôi có rất nhiều ngày. Excel cung cấp một công cụ để nhóm dữ liệu theo ngày, tháng, năm, v.v. Hãy xem nó được thực hiện như thế nào.
Đầu tiên hãy xóa mục nhập. Phương thức thanh toán từ khu vực Nhãn cột (Cột). Để thực hiện việc này, hãy kéo nó trở lại danh sách tiêu đề và ở vị trí của nó, hãy di chuyển tiêu đề Ngày đặt (ngày đặt phòng):
Như bạn có thể thấy, điều này tạm thời làm cho bảng tổng hợp của chúng tôi trở nên vô dụng. Excel đã tạo một cột riêng cho mỗi ngày mà giao dịch được thực hiện. Kết quả là, chúng tôi có một bàn rất rộng!
Để 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 từ menu ngữ cảnh NHÓM (Tập đoàn):
Hộp thoại nhóm sẽ xuất hiện. Chúng tôi chọn Tháng (Tháng) và nhấp vào OK:
Thì đấy! Bảng này hữu ích hơn nhiều:
Nhân tiện, bảng này gần giống với bảng được hiển thị ở đầu bài viết, trong đó tổng doanh thu được tổng hợp theo cách thủ công.
Có một điểm rất quan trọng khác mà bạn cần biết! Bạn có thể tạo không phải một, mà là một số cấp độ của tiêu đề hàng (hoặc cột):
… Và nó sẽ trông như thế này…
Điều tương tự cũng có thể được thực hiện với các tiêu đề cột (hoặc thậm chí bộ lọc).
Hãy quay lại dạng ban đầu của bảng và xem cách hiển thị số trung bình thay vì tổng.
Để bắt đầu, hãy nhấp vào Tổng số tiền và từ menu xuất hiện, hãy chọn Cài đặt trường giá trị (Tùy chọn trường giá trị):
Danh sách Tóm tắt trường giá trị bằng (Hoạt động) trong hộp thoại Cài đặt trường giá trị (Tùy chọn trường giá trị) chọn Trung bình (Trung bình):
Đồng thời, trong khi chúng ta ở đây, hãy thay đổi Tên tùy chỉnh (Tên tùy chỉnh) với Trung bình của số tiền (Trường số lượng Số tiền) thành một thứ gì đó ngắn hơn. Nhập vào trường này một cái gì đó như Trung bình:
Ấn Bản OK và hãy xem chuyện gì xảy ra. Lưu ý rằng tất cả các giá trị đã thay đổi từ tổng thành trung bình và tiêu đề bảng (trong ô phía trên bên trái) đã thay đổi thành Trung bình:
Nếu bạn muốn, bạn có thể nhận ngay số tiền, trung bình và số lượng (doanh số bán hàng) được đặt trong một bảng tổng hợp.
Dưới đây là hướng dẫn từng bước về cách thực hiện việc này, bắt đầu với một bảng tổng hợp trống:
- Kéo tiêu đề người bán hàng (đại diện bán hàng) đến khu vực Nhãn cột (Cột).
- Kéo tiêu đề ba lần Số tiền (Chi phí) cho khu vực Các giá trị (Giá trị).
- Đối với trường đầu tiên Số tiền thay đổi tiêu đề thành Tổng số: (Số tiền) và định dạng số trong trường này là Kế toán (Tài chính). Số vị trí thập phân là số không.
- Lĩnh vực thứ hai Số tiền tên trung bìnhe, thiết lập hoạt động cho nó Trung bình (Trung bình) và định dạng số trong trường này cũng thay đổi thành Kế toán (Tài chính) không có chữ số thập phân.
- Đối với trường thứ ba Số tiền đặt tiêu đề Đếm và một cuộc phẫu thuật cho anh ta - Đếm (Số lượng)
- Trong tạp chí Nhãn cột Trường (Cột) được tạo tự động Σ Giá trị (Σ Giá trị) - kéo nó vào khu vực Nhãn hàng (Dòng)
Đây là những gì chúng ta sẽ kết thúc với:
Tổng số tiền, giá trị trung bình và số lần bán hàng - tất cả trong một bảng tổng hợp!
Kết luận
Bảng tổng hợp trong Microsoft Excel chứa rất nhiều tính năng và cài đặt. Trong một bài báo nhỏ như vậy, họ thậm chí còn không bao quát hết được. Sẽ cần một cuốn sách nhỏ hoặc một trang web lớn để mô tả đầy đủ tất cả các khả năng của bảng tổng hợp. Những người đọc táo bạo và ham hiểu biết có thể tiếp tục khám phá bảng tổng hợp. Để thực hiện việc này, chỉ cần nhấp chuột phải vào hầu hết bất kỳ phần tử nào của bảng tổng hợp và xem những chức năng và cài đặt nào đang mở. Trên Ribbon, bạn sẽ tìm thấy hai tab: Công cụ PivotTable: Tùy chọn (phân tích) và Thiết kế (Người xây dựng). Đừng sợ mắc lỗi, bạn luôn có thể xóa PivotTable và bắt đầu lại. Bạn có một cơ hội mà những người dùng DOS và Lotus 1-2-3 đã lâu chưa từng có.